$function$</create>
</function>
+ <table>
+ <name>create_subfield_u</name>
+ <drop>DROP TABLE IF EXISTS subfield_u</drop>
+ <create>CREATE UNLOGGED TABLE subfield_u AS SELECT UNNEST(oils_xpath( '//*[@tag="856"]/*[@code="u"]/text()', marc)) AS value FROM m_biblio_record_entry_legacy WHERE x_migrate</create>
+ </table>
<!-- sample reports entry
<report>
</report>
-->
+<!-- adding sample pie chart report
+ <report>
+ <name>current_holds_by_state</name>
+ <tag>holds</tag>
+ <iteration>0</iteration>
+ <display>pie_chart</display>
+ <report_title>Holds by Current State (7 days)</report_title>
+ <heading>Captured.Fulfilled.Canceled.Targeted.Waiting</heading>
+ <chart_labels>Hold Type.Count</chart_labels>
+ <query> SELECT
+ (SELECT COUNT(*) FROM action.hold_request WHERE fulfillment_time IS NULL AND capture_time > NOW() - '7 days'::INTERVAL AND cancel_time IS NULL),
+ (SELECT COUNT(*) FROM action.hold_request WHERE fulfillment_time > NOW() - '7 days'::INTERVAL AND capture_time IS NOT NULL AND cancel_time IS NULL),
+ (SELECT COUNT(*) FROM action.hold_request WHERE fulfillment_time IS NULL AND cancel_time > NOW() - '7 days'::INTERVAL),
+ (SELECT COUNT(*) FROM action.hold_request WHERE fulfillment_time IS NULL AND capture_time IS NULL AND cancel_time IS NULL AND current_copy IS NOT NULL),
+ (SELECT COUNT(*) FROM action.hold_request WHERE fulfillment_time IS NULL AND capture_time IS NULL AND cancel_time IS NULL AND current_copy IS NULL)
+ </query>
+ </report>
+
+-->
+
<!-- CIRC REPORTS -->
<report>
<name>circ_count</name>
</report>
<report>
- <name>circ_count_unmigrated</name>
- <tag>circs</tag>
- <iteration>0</iteration>
- <report_title>Open Un-migrated Circulations</report_title>
- <heading>Circulation Status.Count of Circs</heading>
- <query>SELECT 'No Matching User', COUNT(id) FROM m_action_circulation_legacy WHERE xact_finish IS NULL AND x_migrate = FALSE AND usr IS NULL
- UNION ALL SELECT 'No Matching Item', COUNT(id) FROM m_action_circulation_legacy WHERE xact_finish IS NULL AND x_migrate = FALSE AND target_copy IS NULL </query>
- </report>
-
- <report>
<name>circ_count</name>
<tag>circs</tag>
<iteration>1</iteration>
<heading>Circulation Status.Count of Circs</heading>
<query>SELECT 'Closed Circulations', COUNT(id) FROM m_action_circulation WHERE xact_finish IS NOT NULL UNION ALL SELECT 'Open Circulations', COUNT(id) FROM m_action_circulation WHERE xact_finish IS NULL</query>
</report>
-<!-- intent is to add a report by circulated and item status
- <report>
- <name>circ_count_by_item_status</name>
- <tag>circs</tag>
- <iteration>0</iteration>
- <report_title>Open Circulations</report_title>
- <heading>Circulation Status.Migrated.Count of Circs</heading>
- <query>SELECT 'Closed Circulations', x_migrate::TEXT, COUNT(id) FROM m_action_circulation_legacy WHERE xact_finish IS NOT NULL GROUP BY 2 UNION ALL SELECT 'Open Circulations', x_migrate::TEXT, COUNT(id) FROM m_action_circulation_legacy WHERE xact_finish IS NULL GROUP BY 2</query>
- </report>
--->
+
<report>
<name>circ_by_orgunit</name>
<tag>circs</tag>
<iteration>0</iteration>
<report_title>Circulations by Org Unit</report_title>
- <heading>Circulations Count.Migrated.Org Unit</heading>
- <query>SELECT COUNT(acirc.id), acirc.x_migrate::TEXT, aou.name FROM m_action_circulation_legacy acirc JOIN actor.org_unit aou ON aou.id = acirc.circ_lib WHERE acirc.xact_finish IS NULL GROUP BY 2, 3</query>
+ <heading>Circulations Count.Org Unit</heading>
+ <query>SELECT COUNT(acirc.id), aou.name FROM m_action_circulation_legacy acirc JOIN actor.org_unit aou ON aou.id = acirc.circ_lib WHERE acirc.xact_finish IS NULL AND x_migrate = TRUE GROUP BY 2</query>
</report>
<report>
<tag>circs</tag>
<iteration>0</iteration>
<report_title>Migrated Circulations by Duration, Fine and Max Fine</report_title>
- <heading>Count of Circs.Duration.Fine.Max Fine.Migrated</heading>
- <query>SELECT COUNT(id), duration_rule, recurring_fine_rule, max_fine_rule, x_migrate::TEXT FROM m_action_circulation_legacy WHERE x_migrate = TRUE GROUP BY 2, 3, 4, 5</query>
+ <heading>Count of Circs.Duration.Fine.Max Fine</heading>
+ <query>SELECT COUNT(id), duration_rule, recurring_fine_rule, max_fine_rule FROM m_action_circulation_legacy WHERE x_migrate = TRUE GROUP BY 2, 3, 4</query>
</report>
<report>
<name>circ_open_by_item_status</name>
<tag>circs</tag>
<iteration>0</iteration>
- <report_title>Open Circulation and Status of Linked Items</report_title>
+ <report_title>Status of Currently Circulating Items</report_title>
<heading>Count.Status</heading>
<query>SELECT COUNT(acirc.id), ccs.name FROM action.circulation acirc JOIN asset.copy ac ON ac.id = acirc.target_copy JOIN config.copy_status ccs ON ccs.id = ac.status WHERE acirc.xact_finish IS NULL AND acirc.checkin_time IS NULL AND acirc.id IN (SELECT id FROM m_action_circulation) GROUP BY 2 ORDER BY 2</query>
</report>
<name>holds</name>
<tag>holds</tag>
<iteration>0</iteration>
- <report_title>Migrated Holds</report_title>
+ <report_title>Migrated and Non-Migrated Holds</report_title>
<heading>Hold Type.Hold Count.Migrated</heading>
<query>SELECT 'Closed Holds', COUNT(id), x_migrate::TEXT FROM m_action_hold_request_legacy WHERE (expire_time::TIMESTAMP < now()) OR cancel_time IS NOT NULL OR fulfillment_time IS NOT NULL GROUP BY 3 UNION ALL SELECT 'Open Holds', COUNT(id), x_migrate::TEXT FROM m_action_hold_request_legacy WHERE (expire_time IS NULL OR expire_time::TIMESTAMP > now()) AND cancel_time IS NULL AND fulfillment_time IS NULL GROUP BY 3</query>
</report>
<tag>holds</tag>
<iteration>0</iteration>
<report_title>Migrated Holds By Type</report_title>
- <heading>Hold Type.Hold Count.Migrated</heading>
- <query>SELECT hold_type as "Hold Type", COUNT(id), x_migrate::TEXT FROM m_action_hold_request_legacy GROUP BY 1, 3</query>
+ <heading>Hold Type.Hold Count</heading>
+ <query>SELECT hold_type as "Hold Type", COUNT(id) FROM m_action_hold_request_legacy WHERE x_migrate = TRUE GROUP BY 1</query>
</report>
<report>
<query>SELECT COUNT(ahr.id), ahr.selection_depth FROM action.hold_request ahr WHERE ahr.id IN (SELECT id FROM m_action_hold_request) AND ahr.cancel_time IS NULL AND ahr.capture_time IS NULL AND ahr.fulfillment_time IS NULL GROUP BY 2 ORDER BY 2</query>
</report>
- <!-- finding title matched holds where there are duplicates, this will table match for legacy but column won't be present for all -->
- <report>
- <name>duplicate_title_matching_holds</name>
- <tag>holds</tag>
- <iteration>0</iteration>
- <report_title>Holds by Title Matchs</report_title>
- <heading>Count.Number of Matches</heading>
- <query>SELECT COUNT(*), x_title_matches FROM m_action_hold_request_legacy WHERE TO_DATE(l_expire_date,'MM/DD/YYYY') > NOW() GROUP BY 2 ORDER BY 2</query>
- </report>
-
- <report>
- <name>duplicate_title_matching_holds_w_zero</name>
- <tag>holds</tag>
- <iteration>0</iteration>
- <report_title>Sample of Holds with No Matches</report_title>
- <heading>Borrower Barcode.Title.Request Date</heading>
- <query>SELECT l_borrower_id, l_title, l_request_date FROM m_action_hold_request_legacy WHERE TO_DATE(l_expire_date,'MM/DD/YYYY') > NOW() AND x_title_matches = 0 AND x_migrate LIMIT 20</query>
- </report>
-
- <report>
- <name>duplicate_title_matching_holds_w_greater_than_1</name>
- <tag>holds</tag>
- <iteration>0</iteration>
- <report_title>Sample of Holds with More Than One Title Match</report_title>
- <heading>Borrower Barcode.Title.Request Date</heading>
- <query>SELECT l_borrower_id, l_title, l_request_date FROM m_action_hold_request_legacy WHERE TO_DATE(l_expire_date,'MM/DD/YYYY') > NOW() AND x_title_matches > 1 AND x_migrate LIMIT 20</query>
- </report>
-
<!-- ASSET REPORTS -->
<report>
<report_title>Count of Copies by Library</report_title>
<tag>assets</tag>
<iteration>0</iteration>
- <heading>Copy Count.Library.Migrated</heading>
- <query>SELECT COUNT(ac.id), aou.name, ac.x_migrate::TEXT FROM m_asset_copy_legacy ac JOIN actor.org_unit aou ON aou.id = ac.circ_lib GROUP BY 2, 3 ORDER BY 2, 3</query>
+ <heading>Copy Count.Library</heading>
+ <query>SELECT COUNT(ac.id), aou.name FROM m_asset_copy_legacy ac JOIN actor.org_unit aou ON aou.id = ac.circ_lib WHERE ac.x_migrate = TRUE GROUP BY 2 ORDER BY 2</query>
</report>
<report>
- <name>m_asset_deleted_copies</name>
- <report_title>Deleted Copies</report_title>
+ <name>m_asset_copy_count_non_migrated</name>
+ <report_title>Non-Migrated Count of Copies by Library</report_title>
<tag>assets</tag>
<iteration>0</iteration>
- <heading>Copy Count.Deleted.Migrated</heading>
- <query>SELECT COUNT(ac.id), ac.deleted::TEXT, ac.x_migrate::TEXT FROM m_asset_copy_legacy ac GROUP BY 2, 3</query>
+ <heading>Copy Count.Library</heading>
+ <query>SELECT COUNT(ac.id), aou.name FROM m_asset_copy_legacy ac JOIN actor.org_unit aou ON aou.id = ac.circ_lib WHERE ac.x_migrate = FALSE GROUP BY 2 ORDER BY 2</query>
</report>
<report>
<report_title>Copies by Status</report_title>
<tag>assets</tag>
<iteration>0</iteration>
- <heading>Copy Count.Status.Migrated</heading>
- <query>SELECT COUNT(ac.id), cs.name, ac.x_migrate::TEXT FROM m_asset_copy_legacy ac JOIN config.copy_status cs ON cs.id = ac.status GROUP BY 2, 3 ORDER BY 2, 3</query>
- </report>
-
- <report>
- <name>m_asset_precat</name>
- <report_title>Precats (AKA Copies Without Bibs)</report_title>
- <tag>assets</tag>
- <iteration>0</iteration>
- <heading>Copy Count.Migating</heading>
- <query>SELECT COUNT(ac.id), ac.x_migrate::TEXT FROM m_asset_copy_legacy ac WHERE ac.x_bre_id = -1 GROUP BY 2</query>
+ <heading>Copy Count.Status</heading>
+ <query>SELECT COUNT(ac.id), cs.name FROM m_asset_copy_legacy ac JOIN config.copy_status cs ON cs.id = ac.status WHERE ac.x_migrate = TRUE GROUP BY 2 ORDER BY 2</query>
</report>
<report>
<report_title>Copies by Circulation Modifier</report_title>
<tag>assets</tag>
<iteration>0</iteration>
- <heading>Copy Count.Circulation Modifier.Migrated</heading>
- <query>SELECT COUNT(ac.id), ac.circ_modifier, ac.x_migrate::TEXT FROM m_asset_copy_legacy ac GROUP BY 2, 3 ORDER BY 2, 3</query>
+ <heading>Copy Count.Circulation Modifier</heading>
+ <query>SELECT COUNT(ac.id), ac.circ_modifier FROM m_asset_copy_legacy ac WHERE ac.x_migrate = TRUE GROUP BY 2 ORDER BY 2</query>
</report>
<report>
<report_title>Copy Notes</report_title>
<tag>assets</tag>
<iteration>0</iteration>
- <heading>Note Count.Public.Migrated</heading>
- <query>SELECT COUNT(acnote.id), acnote.pub::TEXT, acnote.x_migrate::TEXT FROM m_asset_copy_note_legacy acnote GROUP BY 2, 3 ORDER BY 2, 3</query>
+ <heading>Note Count.Public</heading>
+ <query>SELECT COUNT(acnote.id), acnote.pub::TEXT FROM m_asset_copy_note_legacy acnote WHERE acnote.x_migrate = TRUE GROUP BY 2 ORDER BY 2</query>
</report>
<report>
<report_title>Volumes by Library</report_title>
<tag>assets</tag>
<iteration>0</iteration>
- <heading>Volume Count.Library.Migrated</heading>
- <query>SELECT COUNT(acn.id), aou.name, acn.x_migrate::TEXT FROM m_asset_call_number_legacy acn JOIN m_actor_org_unit_legacy aou ON aou.id = acn.owning_lib GROUP BY 2, 3 ORDER BY 2, 3</query>
+ <heading>Volume Count.Library</heading>
+ <query>SELECT COUNT(acn.id), aou.name FROM m_asset_call_number_legacy acn JOIN m_actor_org_unit_legacy aou ON aou.id = acn.owning_lib WHERE acn.x_migrate = TRUE GROUP BY 2 ORDER BY 2</query>
</report>
<report>
<heading>Volume Count.Library</heading>
<query>SELECT COUNT(acn.id), aou.name FROM m_asset_call_number acn JOIN actor.org_unit aou ON aou.id = acn.owning_lib GROUP BY 2 ORDER BY 2</query>
</report>
- <!--
- <report>
- <name>m_asset_cops_by_loc</name>
- <report_title>Copies by Location</report_title>
- <tag>assets</tag>
- <iteration>0</iteration>
- <heading>Copy Count.Library.Migrated</heading>
- <query>SELECT COUNT(ac.id), acl.name, ac.x_migrate::TEXT FROM m_asset_copy_legacy ac JOIN asset.copy_location acl ON acl.id = ac.location GROUP BY 2, 3 ORDER BY 2, 3</query>
- </report>
- -->
+
<report>
<name>m_asset_cops_by_loc_and_org</name>
<report_title>Copies by Location</report_title>
<tag>assets</tag>
<iteration>0</iteration>
- <heading>Copy Count.Library.Circ Library.Migrated</heading>
- <query>SELECT COUNT(ac.id), acl.name, aou.name, ac.x_migrate::TEXT FROM m_asset_copy_legacy ac JOIN asset.copy_location acl ON acl.id = ac.location JOIN actor.org_unit aou ON aou.id = ac.circ_lib GROUP BY 2, 3, 4 ORDER BY 2, 3, 4</query>
+ <heading>Copy Count.Location.Circ Library</heading>
+ <query>SELECT COUNT(ac.id), acl.name, aou.name FROM m_asset_copy_legacy ac JOIN asset.copy_location acl ON acl.id = ac.location JOIN actor.org_unit aou ON aou.id = ac.circ_lib WHERE ac.x_migrate = TRUE GROUP BY 2, 3 ORDER BY 2, 3</query>
</report>
<report>
<query>SELECT barcode FROM m_asset_copy_legacy WHERE location = 1 AND x_migrate</query>
</report>
- <!-- report>
- <name>m_asset_barcode_lengths</name>
- <report_title>Barcode Lengths by Library</report_title>
- <tag>assets</tag>
- <iteration>0</iteration>
- <heading>Count of Barcode.Barcode Length.Library</heading>
- <query>SELECT COUNT(ac.id), LENGTH(ac.barcode), aou.name FROM m_asset_copy_legacy ac JOIN actor.org_unit aou ON aou.id = ac.circ_lib WHERE ac.x_migrate = TRUE GROUP BY 2, 3 ORDER BY 3, 2</query>
- </report -->
-
<report>
<name>m_asset_no_barcode</name>
<report_title>Items Without Barcodes</report_title>
<tag>assets</tag>
<iteration>0</iteration>
<heading>Collision Count</heading>
- <query>SELECT COUNT(id) FROM m_asset_copy_legacy WHERE x_migrate = TRUE AND barcode LIKE 'x_%'</query>
+ <query>SELECT COUNT(id) FROM m_asset_copy_legacy WHERE x_migrate = TRUE AND barcode ~* '^x_'</query>
<note>Incumbent collisions are those where the migrated barcodes collide with existing barcodes in the database.</note>
</report>
<tag>assets</tag>
<iteration>1</iteration>
<heading>Collision Count</heading>
- <query>SELECT COUNT(id) FROM m_asset_copy WHERE barcode LIKE 'x_%'</query>
+ <query>SELECT COUNT(id) FROM m_asset_copy WHERE barcode ~* '^x_'</query>
<note>Incumbent collisions are those where the migrated barcodes collide with existing barcodes in the database.</note>
</report>
<tag>assets</tag>
<iteration>0</iteration>
<heading>Collision List</heading>
- <query>SELECT ac.barcode FROM m_asset_copy_legacy ac WHERE ac.barcode LIKE 'x_%' ORDER BY 1 LIMIT 20</query>
+ <query>SELECT ac.barcode FROM m_asset_copy_legacy ac WHERE ac.barcode ~* '^x_' AND ac.x_migrate = TRUE ORDER BY 1 LIMIT 20</query>
<note>This is a shortlist of copy barcode collisions that maxes out at 20. If there are more collisions we will need to run a custom report.</note>
</report>
<tag>assets</tag>
<iteration>1</iteration>
<heading>Collision List</heading>
- <query>SELECT ac.barcode FROM m_asset_copy ac WHERE ac.barcode LIKE 'x_%' ORDER BY 1 LIMIT 20</query>
+ <query>SELECT ac.barcode FROM m_asset_copy ac WHERE ac.barcode ~* '^x_' ORDER BY 1 LIMIT 20</query>
<note>This is a shortlist of patron barcode collisions that maxes out at 20. If there are more collisions we will need to run a custom report.</note>
</report>
<tag>assets</tag>
<iteration>0</iteration>
<heading>Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters</heading>
- <query>SELECT COUNT(ac.id), LEFT(ac.barcode,(ROUND(LENGTH(ac.barcode)*.6))::INT) FROM m_asset_copy_legacy ac WHERE barcode LIKE 'x_%' GROUP BY 2 HAVING COUNT(ac.id) > 10 ORDER BY 2</query>
+ <query>SELECT COUNT(ac.id), LEFT(ac.barcode,(ROUND(LENGTH(ac.barcode)*.6))::INT) FROM m_asset_copy_legacy ac WHERE barcode ~* '^x_' AND ac.x_migrate = TRUE GROUP BY 2 HAVING COUNT(ac.id) > 10 ORDER BY 2</query>
</report>
<report>
<tag>assets</tag>
<iteration>1</iteration>
<heading>Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters</heading>
- <query>SELECT COUNT(ac.id), LEFT(ac.barcode,(ROUND(LENGTH(ac.barcode)*.6))::INT) FROM m_asset_copy ac WHERE barcode LIKE 'x_%' GROUP BY 2 HAVING COUNT(ac.id) > 10 ORDER BY 2</query>
+ <query>SELECT COUNT(ac.id), LEFT(ac.barcode,(ROUND(LENGTH(ac.barcode)*.6))::INT) FROM m_asset_copy ac WHERE barcode ~* '^x_' GROUP BY 2 HAVING COUNT(ac.id) > 10 ORDER BY 2</query>
</report>
<report>
<iteration>0</iteration>
<heading>Count</heading>
<query>SELECT COUNT(id) FROM m_biblio_record_entry where id NOT IN (SELECT DISTINCT record FROM m_asset_call_number)</query>
- <note>These records would not have been loaded.</note>
+ <note>These records would not have been loaded but many may be deduplicated against incumbent records.</note>
+ </report>
+
+ <report>
+ <name>bibswovolumesanduri</name>
+ <report_title>Bibliographic Records Without Volumes And Recognized URI</report_title>
+ <tag>bibs</tag>
+ <iteration>0</iteration>
+ <heading>Service.Domain.Count</heading>
+ <query>SELECT '3M','ebook.3m.com', COUNT(*) FROM subfield_u WHERE value ~* 'ebook.3m.com' GROUP BY 1, 2 UNION ALL
+ SELECT 'Axis 360','axis360.baker-taylor.com', COUNT(*) FROM subfield_u WHERE value ~* 'axis360.baker-taylor.com' GROUP BY 1, 2 UNION ALL
+ SELECT 'Book Flix','bookflix.digital.scholastic.com', COUNT(*) FROM subfield_u WHERE value ~* 'bookflix.digital.scholastic.com' GROUP BY 1, 2 UNION ALL
+ SELECT 'Book Flix','bkflix.grolier.com', COUNT(*) FROM subfield_u WHERE value ~* 'bkflix.grolier.com' GROUP BY 1, 2 UNION ALL
+ SELECT 'Comics Plus','library.comicsplusapp.com', COUNT(*) FROM subfield_u WHERE value ~* 'library.comicsplusapp.com' GROUP BY 1, 2 UNION ALL
+ SELECT 'Ebrary','site.ebrary.com', COUNT(*) FROM subfield_u WHERE value ~* 'site.ebrary.com' GROUP BY 1, 2 UNION ALL
+ SELECT 'Freading','freading.com', COUNT(*) FROM subfield_u WHERE value ~* 'freading.com' GROUP BY 1, 2 UNION ALL
+ SELECT 'Hoopla','hoopladigital.com', COUNT(*) FROM subfield_u WHERE value ~* 'hoopladigital.com' GROUP BY 1, 2 UNION ALL
+ SELECT 'Infobase','avod.infobase.com', COUNT(*) FROM subfield_u WHERE value ~* 'avod.infobase.com' GROUP BY 1, 2 UNION ALL
+ SELECT 'Learning Express','learningexpresslibrary.com', COUNT(*) FROM subfield_u WHERE value ~* 'learningexpresslibrary.com' GROUP BY 1, 2 UNION ALL
+ SELECT 'Missouri Overdrive','molib2go.org', COUNT(*) FROM subfield_u WHERE value ~* 'molib2go.org' GROUP BY 1, 2 UNION ALL
+ SELECT 'netLibrary','netLibrary.com', COUNT(*) FROM subfield_u WHERE value ~* 'netLibrary.com' GROUP BY 1, 2 UNION ALL
+ SELECT 'OneClickDigital','oneclickdigital.com', COUNT(*) FROM subfield_u WHERE value ~* 'oneclickdigital.com' GROUP BY 1, 2 UNION ALL
+ SELECT 'Overdrive','overdrive.com', COUNT(*) FROM subfield_u WHERE value ~* 'overdrive.com' GROUP BY 1, 2 UNION ALL
+ SELECT 'ProQuest','ebookcentral.proquest.com', COUNT(*) FROM subfield_u WHERE value ~* 'ebookcentral.proquest.com' GROUP BY 1, 2 UNION ALL
+ SELECT 'RB Digital','rbdigital.com', COUNT(*) FROM subfield_u WHERE value ~* 'rbdigital.com' GROUP BY 1, 2 UNION ALL
+ SELECT 'U.S. Government Sites','.gov', COUNT(*) FROM subfield_u WHERE value ~* '\.gov' GROUP BY 1,2;;
+ </query>
+ <note>This list is built from known services and domains. If you have records for electronic resources that are not here please let us know.</note>
</report>
<report>
<query>SELECT SUM(array_length(records,1)), COUNT(*) FROM groups</query>
</report>
- <report>
- <name>bib_merges_searchformat</name>
- <report_title>Bibliographic Merges By Search Format</report_title>
- <tag>bibs</tag>
- <iteration>0</iteration>
- <heading>Count.Item Type</heading>
- <query>DROP TABLE IF EXISTS temp_unnest; CREATE TABLE temp_unnest AS (SELECT UNNEST(records) FROM groups); SELECT COUNT(record), search_format_str FROM dedupe_batch WHERE record IN (SELECT * FROM temp_unnest) GROUP BY 2 ORDER BY 2</query>
- </report>
+ <!-- ACTORS REPORTS -->
- <report>
- <name>bibs_not_loaded</name>
- <report_title>Bibliographic Records That Failed to Load</report_title>
- <tag>bibs</tag>
+ <report>
+ <name>usrsbyorg</name>
+ <report_title>Migrated Patrons by Home Org</report_title>
+ <tag>actors</tag>
<iteration>0</iteration>
- <heading>Title.Author.Control Number</heading>
- <query>SELECT LEFT(ARRAY_TO_STRING(oils_xpath( '//*[@tag="245"]/*[@code="a"]/text()', a.marc),''),50) AS "Title",
- LEFT(ARRAY_TO_STRING(oils_xpath( '//*[@tag="100"]/*[@code="a"]/text()', a.marc),''),50) AS "Author",
- ARRAY_TO_STRING(oils_xpath( '//*[@tag="001"]/text()', a.marc),'') AS "001"
- FROM m_biblio_record_entry a LEFT JOIN biblio.record_entry b ON b.id = a.id WHERE b.id IS NULL AND a.x_migrate
- </query>
+ <heading>Count.Library</heading>
+ <query>SELECT COUNT(au.id), aou.name FROM m_actor_usr_legacy au JOIN actor.org_unit aou ON aou.id = au.home_ou WHERE au.x_migrate = TRUE GROUP BY 2 ORDER BY 2</query>
</report>
-
- <!-- ACTORS REPORTS -->
-
<report>
- <name>usrsbyorg</name>
- <report_title>Patrons by Home Org</report_title>
+ <name>nonmigusrsbyorg</name>
+ <report_title>Non-Migrated Patrons by Home Org</report_title>
<tag>actors</tag>
<iteration>0</iteration>
- <heading>Count.Library.Deleted.Migrated</heading>
- <query>SELECT COUNT(au.id), aou.name, au.deleted::TEXT, au.x_migrate::TEXT FROM m_actor_usr_legacy au JOIN actor.org_unit aou ON aou.id = au.home_ou GROUP BY 2, 3, 4 ORDER BY 2, 3, 4</query>
+ <heading>Count.Library</heading>
+ <query>SELECT COUNT(au.id), aou.name FROM m_actor_usr_legacy au JOIN actor.org_unit aou ON aou.id = au.home_ou WHERE au.x_migrate = FALSE GROUP BY 2 ORDER BY 2</query>
</report>
<report>
<name>usrsbypgt</name>
- <report_title>Patrons by Permission Group</report_title>
+ <report_title>Migrated Patrons by Permission Group</report_title>
<tag>actors</tag>
<iteration>0</iteration>
- <heading>Count.Permission Group.Migrated</heading>
- <query>SELECT COUNT(au.id), pgt.name, au.x_migrate::TEXT FROM m_actor_usr_legacy au JOIN permission.grp_tree pgt ON pgt.id = au.profile GROUP BY 2, 3 ORDER BY 2, 3</query>
+ <heading>Count.Permission Group</heading>
+ <query>SELECT COUNT(au.id), pgt.name FROM m_actor_usr_legacy au JOIN permission.grp_tree pgt ON pgt.id = au.profile WHERE au.x_migrate = TRUE GROUP BY 2 ORDER BY 2</query>
</report>
<report>
<report_title>Patron by Active Status</report_title>
<tag>actors</tag>
<iteration>0</iteration>
- <heading>Count of Users.Active.Migrated</heading>
- <query>SELECT COUNT(id), active::TEXT, x_migrate::TEXT FROM m_actor_usr_legacy GROUP BY 2, 3</query>
+ <heading>Count of Users.Active</heading>
+ <query>SELECT COUNT(id), active::TEXT FROM m_actor_usr_legacy WHERE x_migrate = TRUE GROUP BY 2</query>
</report>
<report>
<name>active_usrs</name>
- <report_title>Patrons by Active Status</report_title>
+ <report_title>Patron Cards</report_title>
<tag>actors</tag>
<iteration>1</iteration>
- <heading>Count of Users.Active</heading>
- <query>SELECT COUNT(id), active::TEXT FROM m_actor_usr GROUP BY 2</query>
+ <heading>Count of Users</heading>
+ <query>SELECT COUNT(id) FROM m_actor_usr</query>
</report>
<report>
<query>SELECT COUNT(id), active::TEXT FROM m_actor_card GROUP BY 2</query>
</report>
- <!-- report>
- <name>usr_barcode_lengths</name>
- <report_title>Barcode Lengths by Library</report_title>
- <tag>actors</tag>
- <iteration>0</iteration>
- <heading>Count of Barcode.Barcode Length.Library</heading>
- <query>SELECT COUNT(acard.id), LENGTH(acard.barcode), aou.name FROM m_actor_card_legacy acard JOIN m_actor_usr_legacy au ON au.id = acard.usr JOIN actor.org_unit aou ON aou.id = au.home_ou WHERE acard.x_migrate = TRUE GROUP BY 2, 3 ORDER BY 3, 2</query>
- </report -->
-
- <!-- report>
- <name>usr_barcode_lengths</name>
- <report_title>Barcode Lengths by Library</report_title>
- <tag>actors</tag>
- <iteration>1</iteration>
- <heading>Count of Barcode.Barcode Length.Library</heading>
- <query>SELECT COUNT(acard.id), LENGTH(acard.barcode), aou.name FROM m_actor_card acard JOIN m_actor_usr au ON au.id = acard.usr JOIN actor.org_unit aou ON aou.id = au.home_ou GROUP BY 2, 3 ORDER BY 3, 2</query>
- </report -->
-
<report>
<name>usr_barcode_patterns</name>
<report_title>Common Barcode Starting Patterns</report_title>
<tag>actors</tag>
<iteration>0</iteration>
<heading>Collision Count</heading>
- <query>SELECT COUNT(acard.id) FROM m_actor_card_legacy acard WHERE barcode LIKE 'x_%' AND x_migrate = TRUE</query>
+ <query>SELECT COUNT(acard.id) FROM m_actor_card_legacy acard WHERE barcode ~* '^x_' AND x_migrate = TRUE</query>
</report>
<report>
<tag>actors</tag>
<iteration>1</iteration>
<heading>Collision Count</heading>
- <query>SELECT COUNT(acard.id) FROM m_actor_card acard WHERE barcode LIKE 'x_%'</query>
+ <query>SELECT COUNT(acard.id) FROM m_actor_card acard WHERE barcode ~* '^x_'</query>
</report>
<report>
<tag>actors</tag>
<iteration>0</iteration>
<heading>Collision List</heading>
- <query>SELECT acard.barcode FROM m_actor_card_legacy acard WHERE acard.barcode LIKE 'x_' AND acard.x_migrate = TRUE ORDER BY 1 LIMIT 20</query>
+ <query>SELECT acard.barcode FROM m_actor_card_legacy acard WHERE acard.barcode ~* '^x_' AND acard.x_migrate = TRUE ORDER BY 1 LIMIT 20</query>
<note>This is a shortlist of patron barcode collisions that maxes out at 20. If there are more collisions we will need to run a custom report. In some cases we may flag individual accounts to not migrate.</note>
</report>
<tag>actors</tag>
<iteration>1</iteration>
<heading>Collision List</heading>
- <query>SELECT acard.barcode FROM m_actor_card acard WHERE acard.barcode LIKE 'x_%' ORDER BY 1 LIMIT 20</query>
+ <query>SELECT acard.barcode FROM m_actor_card acard WHERE acard.barcode ~* '^x_%' ORDER BY 1 LIMIT 20</query>
<note>This is a shortlist of patron barcode collisions that maxes out at 20. If there are more collisions we will need to run a custom report. In some cases we may flag individual accounts to not migrate.</note>
</report>
<report>
<name>usr_addressses_status</name>
- <report_title>Patron Addresses by Valid Status</report_title>
+ <report_title>Patron Addresses</report_title>
<tag>actors</tag>
<iteration>0</iteration>
- <heading>Count.Valid.Migrated</heading>
- <query>SELECT COUNT(aua.id), valid::TEXT, x_migrate::TEXT FROM m_actor_usr_address_legacy aua GROUP BY 2, 3</query>
+ <heading>Count</heading>
+ <query>SELECT COUNT(aua.id) FROM m_actor_usr_address_legacy aua WHERE aua.x_migrate = TRUE</query>
</report>
<report>
<name>usr_addressses_status</name>
- <report_title>Patron Addresses by Valid Status</report_title>
+ <report_title>Patron Addresses</report_title>
<tag>actors</tag>
<iteration>1</iteration>
- <heading>Count.Valid</heading>
- <query>SELECT COUNT(aua.id), valid::TEXT FROM m_actor_usr_address aua GROUP BY 2</query>
+ <heading>Count</heading>
+ <query>SELECT COUNT(aua.id) FROM m_actor_usr_address aua</query>
</report>
<report>
<report_title>Patron Addresses by Pending Status</report_title>
<tag>actors</tag>
<iteration>0</iteration>
- <heading>Count of Addresses.Pending.Migrated</heading>
- <query>SELECT COUNT(aua.id), pending::TEXT, x_migrate::TEXT FROM m_actor_usr_address_legacy aua GROUP BY 2, 3</query>
+ <heading>Count of Addresses.Pending</heading>
+ <query>SELECT COUNT(aua.id), pending::TEXT FROM m_actor_usr_address_legacy aua WHERE aua.x_migrate = TRUE GROUP BY 2</query>
</report>
<report>
<iteration>0</iteration>
<report_title>Action Triggers Setup for Notices</report_title>
<heading>ID.Active.Owner.Name</heading>
- <query>SELECT ed.id, ed.active, aou.shortname, ed.name
- FROM m_action_trigger.event_definition ed
+ <query>SELECT ed.id, ed.active::TEXT, aou.shortname, ed.name
+ FROM action_trigger.event_definition ed
JOIN actor.org_unit aou ON aou.id = ed.owner
WHERE ed.owner IN (SELECT DISTINCT home_ou FROM m_actor_usr)
OR ed.owner IN (SELECT DISTINCT parent_ou FROM actor.org_unit WHERE id in (SELECT DISTINCT home_ou FROM m_actor_usr));
<report_title>Count of Notices Run with State</report_title>
<heading>Count of Notices.State.ID.Owner.Name</heading>
<query>SELECT COUNT(ate.id), ate.state, ed.id, aou.shortname, ed.name
- FROM m_action_trigger.event_definition ed
+ FROM action_trigger.event_definition ed
JOIN actor.org_unit aou ON aou.id = ed.owner
- JOIN m_action_trigger.event ate ON ate.event_def = ed.id
+ JOIN action_trigger.event ate ON ate.event_def = ed.id
WHERE ed.owner IN (SELECT DISTINCT home_ou FROM m_actor_usr)
OR ed.owner IN (SELECT DISTINCT parent_ou FROM actor.org_unit WHERE id in (SELECT DISTINCT home_ou FROM m_actor_usr))
GROUP BY 2,3,4;
projects / migration-tools.git / blobdiff