X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=blobdiff_plain;f=mig-xml%2Fevergreen_staged_report.xml;h=eb40428434583e7e005b0ef3231eba419eba38cc;hp=d3a4cf6872256a359d3c5e7e423ef55a372fdd12;hb=b25dcd79fad7b40f8facc6d63decad2fdad74b9b;hpb=43afa94e2a1e3f6a24a731b71ddf5b3e6d30ee0a
diff --git a/mig-xml/evergreen_staged_report.xml b/mig-xml/evergreen_staged_report.xml
index d3a4cf6..eb40428 100644
--- a/mig-xml/evergreen_staged_report.xml
+++ b/mig-xml/evergreen_staged_report.xml
@@ -34,7 +34,7 @@
money arbitrary tags, each should go in separate tag
Migrated Billings title used in the asciidoc output
Number of Billings.Migrated.Total period delimited
- SELECT COUNT(id),x_migrate::TEXT,SUM(amount) FROM money_billing_legacy GROUP BY 2; query itself, will be replaced
+ SELECT COUNT(id),x_migrate::TEXT,SUM(amount) FROM m_money_billing_legacy GROUP BY 2; query itself, will be replaced
Arbitrary note that can be included in the entries.
-->
@@ -46,8 +46,8 @@
0
Migrated Circulations
Circulation Status.Count of Circs
- SELECT 'Closed Circulations', COUNT(id) FROM action_circulation_legacy WHERE xact_finish IS NOT NULL AND x_migrate
- UNION ALL SELECT 'Open Circulations', COUNT(id) FROM action_circulation_legacy WHERE xact_finish IS NULL AND x_migrate
+ SELECT 'Closed Circulations', COUNT(id) FROM m_action_circulation_legacy WHERE xact_finish IS NOT NULL AND x_migrate
+ UNION ALL SELECT 'Open Circulations', COUNT(id) FROM m_action_circulation_legacy WHERE xact_finish IS NULL AND x_migrate
@@ -56,8 +56,8 @@
0
Open Un-migrated Circulations
Circulation Status.Count of Circs
- SELECT 'No Matching User', COUNT(id) FROM 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 action_circulation_legacy WHERE xact_finish IS NULL AND x_migrate = FALSE AND target_copy IS NULL
+ 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
@@ -66,7 +66,7 @@
1
Open Circulations
Circulation Status.Count of Circs
- SELECT 'Closed Circulations', COUNT(id) FROM action_circulation WHERE xact_finish IS NOT NULL UNION ALL SELECT 'Open Circulations', COUNT(id) FROM action_circulation WHERE xact_finish IS NULL
+ 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
@@ -84,7 +84,7 @@
0
Circulations by Org Unit
Circulations Count.Migrated.Org Unit
- SELECT COUNT(acirc.id), acirc.x_migrate::TEXT, aou.name FROM 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
+ 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
@@ -93,7 +93,7 @@
1
Circulations by Org Unit
Circulations Count.Org Unit
- SELECT COUNT(acirc.id), aou.name FROM action_circulation acirc JOIN actor.org_unit aou ON aou.id = acirc.circ_lib WHERE acirc.xact_finish IS NULL GROUP BY 2
+ SELECT COUNT(acirc.id), aou.name FROM m_action_circulation acirc JOIN actor.org_unit aou ON aou.id = acirc.circ_lib WHERE acirc.xact_finish IS NULL GROUP BY 2
@@ -102,7 +102,7 @@
0
Migrated Circulations by Duration, Fine and Max Fine
Count of Circs.Duration.Fine.Max Fine.Migrated
- SELECT COUNT(id), duration_rule, recurring_fine_rule, max_fine_rule, x_migrate::TEXT FROM action_circulation_legacy WHERE x_migrate = TRUE GROUP BY 2, 3, 4, 5
+ 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
@@ -111,7 +111,7 @@
1
Circulations by Duration, Fine and Max Fine
Count of Circs.Duration.Fine.Max Fine
- SELECT COUNT(id), duration_rule, recurring_fine_rule, max_fine_rule FROM action_circulation GROUP BY 2, 3, 4 ORDER BY 2, 3, 4
+ SELECT COUNT(id), duration_rule, recurring_fine_rule, max_fine_rule FROM m_action_circulation GROUP BY 2, 3, 4 ORDER BY 2, 3, 4
@@ -120,7 +120,7 @@
0
Circulations by Rules and Patron Group
Count of Circs.Duration.Fine.Max Fine.User Group.Matchpoints
- SELECT COUNT(acirc.id), acirc.duration_rule, acirc.recurring_fine_rule, acirc.max_fine_rule, pgt.name, x.buildrows FROM action_circulation_legacy acirc JOIN actor.usr au ON au.id = acirc.usr JOIN permission.grp_tree pgt ON pgt.id = au.profile JOIN (SELECT acirc.id, ARRAY_TO_STRING(find_cmm(acirc.id),',') AS buildrows FROM action_circulation_legacy acirc WHERE acirc.x_migrate = TRUE) x ON x.id = acirc.id WHERE acirc.x_migrate = TRUE GROUP BY 2, 3, 4, 5, 6 ORDER BY 2, 3, 4, 5, 6
+ SELECT COUNT(acirc.id), acirc.duration_rule, acirc.recurring_fine_rule, acirc.max_fine_rule, pgt.name, x.buildrows FROM m_action_circulation_legacy acirc JOIN actor.usr au ON au.id = acirc.usr JOIN permission.grp_tree pgt ON pgt.id = au.profile JOIN (SELECT acirc.id, ARRAY_TO_STRING(find_cmm(acirc.id),',') AS buildrows FROM m_action_circulation_legacy acirc WHERE acirc.x_migrate = TRUE) x ON x.id = acirc.id WHERE acirc.x_migrate = TRUE GROUP BY 2, 3, 4, 5, 6 ORDER BY 2, 3, 4, 5, 6
@@ -129,7 +129,7 @@
1
Circulations by Rules and Patron Group
Count of Circs.Duration.Fine.Max Fine.User Group.Matchpoints
- SELECT COUNT(acirc.id), acirc.duration_rule, acirc.recurring_fine_rule, acirc.max_fine_rule, pgt.name, x.buildrows FROM action_circulation acirc JOIN actor.usr au ON au.id = acirc.usr JOIN permission.grp_tree pgt ON pgt.id = au.profile JOIN (SELECT acirc.id, ARRAY_TO_STRING(find_cmm(acirc.id),',') AS buildrows FROM action_circulation acirc) x ON x.id = acirc.id GROUP BY 2, 3, 4, 5, 6 ORDER BY 2, 3, 4, 5, 6
+ SELECT COUNT(acirc.id), acirc.duration_rule, acirc.recurring_fine_rule, acirc.max_fine_rule, pgt.name, x.buildrows FROM m_action_circulation acirc JOIN actor.usr au ON au.id = acirc.usr JOIN permission.grp_tree pgt ON pgt.id = au.profile JOIN (SELECT acirc.id, ARRAY_TO_STRING(find_cmm(acirc.id),',') AS buildrows FROM m_action_circulation acirc) x ON x.id = acirc.id GROUP BY 2, 3, 4, 5, 6 ORDER BY 2, 3, 4, 5, 6
@@ -138,7 +138,7 @@
0
Circulations by Rules and Circulation Modifier
Count of Circs.Duration.Fine.Max Fine.Circulation Modifier.Matchpoints
- SELECT COUNT(acirc.id), acirc.duration_rule, acirc.recurring_fine_rule, acirc.max_fine_rule, ac.circ_modifier, x.buildrows FROM action_circulation_legacy acirc JOIN asset.copy ac ON ac.id = acirc.target_copy JOIN (SELECT acirc.id, ARRAY_TO_STRING(find_cmm(acirc.id),',') AS buildrows FROM action_circulation_legacy acirc WHERE acirc.x_migrate = TRUE) x ON x.id = acirc.id WHERE acirc.x_migrate = TRUE GROUP BY 2, 3, 4, 5, 6 ORDER BY 2, 3, 4, 5, 6
+ SELECT COUNT(acirc.id), acirc.duration_rule, acirc.recurring_fine_rule, acirc.max_fine_rule, ac.circ_modifier, x.buildrows FROM m_action_circulation_legacy acirc JOIN asset.copy ac ON ac.id = acirc.target_copy JOIN (SELECT acirc.id, ARRAY_TO_STRING(find_cmm(acirc.id),',') AS buildrows FROM m_action_circulation_legacy acirc WHERE acirc.x_migrate = TRUE) x ON x.id = acirc.id WHERE acirc.x_migrate = TRUE GROUP BY 2, 3, 4, 5, 6 ORDER BY 2, 3, 4, 5, 6
@@ -147,7 +147,7 @@
1
Circulations by Rules and Circulation Modifier
Count of Circs.Duration.Fine.Max Fine.Circulation Modifier.Matchpoints
- SELECT COUNT(acirc.id), acirc.duration_rule, acirc.recurring_fine_rule, acirc.max_fine_rule, ac.circ_modifier, x.buildrows FROM action_circulation acirc JOIN asset.copy ac ON ac.id = acirc.target_copy JOIN (SELECT acirc.id, ARRAY_TO_STRING(find_cmm(acirc.id),',') AS buildrows FROM action_circulation acirc) x ON x.id = acirc.id
+ SELECT COUNT(acirc.id), acirc.duration_rule, acirc.recurring_fine_rule, acirc.max_fine_rule, ac.circ_modifier, x.buildrows FROM m_action_circulation acirc JOIN asset.copy ac ON ac.id = acirc.target_copy JOIN (SELECT acirc.id, ARRAY_TO_STRING(find_cmm(acirc.id),',') AS buildrows FROM m_action_circulation acirc) x ON x.id = acirc.id
GROUP BY 2, 3, 4, 5, 6 ORDER BY 2, 3, 4, 5, 6
@@ -157,7 +157,7 @@
0
Circulations by Rules and Org Unit
Count of Circs.Duration.Fine.Max Fine.Library Branch
- SELECT COUNT(acirc.id), acirc.duration_rule, acirc.recurring_fine_rule, acirc.max_fine_rule, aou.name FROM action_circulation_legacy acirc JOIN actor.org_unit aou ON aou.id = acirc.circ_lib WHERE acirc.x_migrate = TRUE GROUP BY 2, 3, 4, 5 ORDER BY 2, 3, 4, 5
+ SELECT COUNT(acirc.id), acirc.duration_rule, acirc.recurring_fine_rule, acirc.max_fine_rule, aou.name FROM m_action_circulation_legacy acirc JOIN actor.org_unit aou ON aou.id = acirc.circ_lib WHERE acirc.x_migrate = TRUE GROUP BY 2, 3, 4, 5 ORDER BY 2, 3, 4, 5
@@ -166,7 +166,7 @@
1
Circulations by Rules and Org Unit
Count of Circs.Duration.Fine.Max Fine.Library Branch
- SELECT COUNT(acirc.id), acirc.duration_rule, acirc.recurring_fine_rule, acirc.max_fine_rule, aou.name FROM action_circulation acirc JOIN actor.org_unit aou ON aou.id = acirc.circ_lib GROUP BY 2, 3, 4, 5 ORDER BY 2, 3, 4, 5
+ SELECT COUNT(acirc.id), acirc.duration_rule, acirc.recurring_fine_rule, acirc.max_fine_rule, aou.name FROM m_action_circulation acirc JOIN actor.org_unit aou ON aou.id = acirc.circ_lib GROUP BY 2, 3, 4, 5 ORDER BY 2, 3, 4, 5
@@ -175,7 +175,7 @@
0
Non-Cataloged Circulation
Circulations Count.Migrated
- SELECT COUNT(id), x_migrate::TEXT FROM action_non_cataloged_circulation_legacy GROUP BY 2
+ SELECT COUNT(id), x_migrate::TEXT FROM m_action_non_cataloged_circulation_legacy GROUP BY 2
@@ -184,7 +184,7 @@
1
Non-Cataloged Circulation
Circulations Count
- SELECT COUNT(id) FROM action_non_cataloged_circulation
+ SELECT COUNT(id) FROM m_action_non_cataloged_circulation
@@ -193,7 +193,7 @@
0
In House Use
In House Use Records.Migrated
- SELECT COUNT(id), x_migrate::TEXT FROM action_in_house_use_legacy GROUP BY 2
+ SELECT COUNT(id), x_migrate::TEXT FROM m_action_in_house_use_legacy GROUP BY 2
@@ -202,7 +202,7 @@
1
In House Use
In House Use Records
- SELECT COUNT(id) FROM action_in_house_use
+ SELECT COUNT(id) FROM m_action_in_house_use
@@ -211,12 +211,12 @@
1
Circs Missing Rules
Count.Field Missing
- SELECT COUNT(id), 'Duration Rule Value' FROM action_circulation WHERE duration IS NULL
- UNION ALL SELECT COUNT(id), 'Recurring Fine Rule Value' FROM action_circulation WHERE recurring_fine IS NULL
- UNION ALL SELECT COUNT(id), 'Max Fine Rule Value' FROM action_circulation WHERE max_fine IS NULL
- UNION ALL SELECT COUNT(id), 'Duration Rule' FROM action_circulation WHERE duration_rule IS NULL
- UNION ALL SELECT COUNT(id), 'Recurring Fine Rule' FROM action_circulation WHERE recurring_fine_rule IS NULL
- UNION ALL SELECT COUNT(id), 'Max Fine Rule' FROM action_circulation WHERE max_fine_rule IS NULL
+ SELECT COUNT(id), 'Duration Rule Value' FROM m_action_circulation WHERE duration IS NULL
+ UNION ALL SELECT COUNT(id), 'Recurring Fine Rule Value' FROM m_action_circulation WHERE recurring_fine IS NULL
+ UNION ALL SELECT COUNT(id), 'Max Fine Rule Value' FROM m_action_circulation WHERE max_fine IS NULL
+ UNION ALL SELECT COUNT(id), 'Duration Rule' FROM m_action_circulation WHERE duration_rule IS NULL
+ UNION ALL SELECT COUNT(id), 'Recurring Fine Rule' FROM m_action_circulation WHERE recurring_fine_rule IS NULL
+ UNION ALL SELECT COUNT(id), 'Max Fine Rule' FROM m_action_circulation WHERE max_fine_rule IS NULL
@@ -226,7 +226,7 @@
0
Open Circulation and Status of Linked Items
Count.Status
- 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 action_circulation) GROUP BY 2 ORDER BY 2
+ 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
@@ -237,7 +237,7 @@
0
Migrated Holds
Hold Type.Hold Count.Migrated
- SELECT 'Closed Holds', COUNT(id), x_migrate::TEXT FROM 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 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
+ 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
@@ -246,7 +246,7 @@
0
Migrated Holds By Type
Hold Type.Hold Count.Migrated
- SELECT hold_type as "Hold Type", COUNT(id), x_migrate::TEXT FROM action_hold_request_legacy GROUP BY 1, 3
+ SELECT hold_type as "Hold Type", COUNT(id), x_migrate::TEXT FROM m_action_hold_request_legacy GROUP BY 1, 3
@@ -255,7 +255,7 @@
0
Transit Copy Records and Status of Linked Items
Count.Status
- SELECT COUNT(atc.id), ccs.name FROM action.transit_copy atc JOIN asset.copy ac ON ac.id = atc.target_copy JOIN config.copy_status ccs ON ccs.id = ac.status WHERE atc.id IN (SELECT id FROM action_transit_copy) AND atc.dest_recv_time IS NULL GROUP BY 2 ORDER BY 2
+ SELECT COUNT(atc.id), ccs.name FROM action.transit_copy atc JOIN asset.copy ac ON ac.id = atc.target_copy JOIN config.copy_status ccs ON ccs.id = ac.status WHERE atc.id IN (SELECT id FROM m_action_transit_copy) AND atc.dest_recv_time IS NULL GROUP BY 2 ORDER BY 2
@@ -264,7 +264,7 @@
0
Status of Items with Count of Open In Transits
Count.Status.Count of Open Transits
- SELECT COUNT(ac.id), ccs.name, SUM(CASE WHEN atc.id IS NULL THEN 0 ELSE 1 END) FROM asset.copy ac JOIN config.copy_status ccs ON ccs.id = ac.status LEFT JOIN (SELECT * FROM action.transit_copy WHERE id IN (SELECT id FROM action_transit_copy) AND dest_recv_time IS NULL) atc ON atc.target_copy = ac.id WHERE ac.id IN (SELECT id from asset_copy) GROUP BY 2 ORDER BY 2
+ SELECT COUNT(ac.id), ccs.name, SUM(CASE WHEN atc.id IS NULL THEN 0 ELSE 1 END) FROM asset.copy ac JOIN config.copy_status ccs ON ccs.id = ac.status LEFT JOIN (SELECT * FROM action.transit_copy WHERE id IN (SELECT id FROM m_action_transit_copy) AND dest_recv_time IS NULL) atc ON atc.target_copy = ac.id WHERE ac.id IN (SELECT id from m_asset_copy) GROUP BY 2 ORDER BY 2
@@ -273,7 +273,7 @@
0
Captured Holds with Status of Items
Count of Captured Hold.Status of Item
- SELECT COUNT(ahr.id), ccs.name FROM action.hold_request ahr JOIN asset.copy ac ON ac.id = ahr.current_copy JOIN config.copy_status ccs ON ccs.id = ac.status WHERE ahr.capture_time IS NOT NULL AND ahr.fulfillment_time IS NULL and ahr.cancel_time IS NULL AND ahr.id IN (SELECT id FROM action_hold_request) GROUP BY 2 ORDER By 2
+ SELECT COUNT(ahr.id), ccs.name FROM action.hold_request ahr JOIN asset.copy ac ON ac.id = ahr.current_copy JOIN config.copy_status ccs ON ccs.id = ac.status WHERE ahr.capture_time IS NOT NULL AND ahr.fulfillment_time IS NULL and ahr.cancel_time IS NULL AND ahr.id IN (SELECT id FROM m_action_hold_request) GROUP BY 2 ORDER By 2
@@ -282,7 +282,7 @@
0
Depth of Unfilled Holds
Count.Depth
- SELECT COUNT(ahr.id), ahr.selection_depth FROM action.hold_request ahr WHERE ahr.id IN (SELECT id FROM 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
+ 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
@@ -292,7 +292,7 @@
0
Holds by Title Matchs
Count.Number of Matches
- SELECT COUNT(*), x_title_matches FROM action_hold_request_legacy WHERE TO_DATE(l_expire_date,'MM/DD/YYYY') > NOW() GROUP BY 2 ORDER BY 2
+ 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
@@ -301,7 +301,7 @@
0
Sample of Holds with No Matches
Borrower Barcode.Title.Request Date
- SELECT l_borrower_id, l_title, l_request_date FROM action_hold_request_legacy WHERE TO_DATE(l_expire_date,'MM/DD/YYYY') > NOW() AND x_title_matches = 0 AND x_migrate LIMIT 20
+ 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
@@ -310,376 +310,376 @@
0
Sample of Holds with More Than One Title Match
Borrower Barcode.Title.Request Date
- SELECT l_borrower_id, l_title, l_request_date FROM action_hold_request_legacy WHERE TO_DATE(l_expire_date,'MM/DD/YYYY') > NOW() AND x_title_matches > 1 AND x_migrate LIMIT 20
+ 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
- asset_copy_count
+ m_asset_copy_count
Count of Copies by Library
assets
0
Copy Count.Library.Migrated
- SELECT COUNT(ac.id), aou.name, ac.x_migrate::TEXT FROM asset_copy_legacy ac JOIN actor.org_unit aou ON aou.id = ac.circ_lib GROUP BY 2, 3 ORDER BY 2, 3
+ 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
- asset_deleted_copies
+ m_asset_deleted_copies
Deleted Copies
assets
0
Copy Count.Deleted.Migrated
- SELECT COUNT(ac.id), ac.deleted::TEXT, ac.x_migrate::TEXT FROM asset_copy_legacy ac GROUP BY 2, 3
+ SELECT COUNT(ac.id), ac.deleted::TEXT, ac.x_migrate::TEXT FROM m_asset_copy_legacy ac GROUP BY 2, 3
- asset_copies_by_status
+ m_asset_copies_by_status
Copies by Status
assets
0
Copy Count.Status.Migrated
- SELECT COUNT(ac.id), cs.name, ac.x_migrate::TEXT FROM asset_copy_legacy ac JOIN config.copy_status cs ON cs.id = ac.status GROUP BY 2, 3 ORDER BY 2, 3
+ 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
- asset_precat
+ m_asset_precat
Precats (AKA Copies Without Bibs)
assets
0
Copy Count.Migating
- SELECT COUNT(ac.id), ac.x_migrate::TEXT FROM asset_copy_legacy ac WHERE ac.x_bre_id = -1 GROUP BY 2
+ SELECT COUNT(ac.id), ac.x_migrate::TEXT FROM m_asset_copy_legacy ac WHERE ac.x_bre_id = -1 GROUP BY 2
- asset_circ_mod_copies_count
+ m_asset_circ_mod_copies_count
Copies by Circulation Modifier
assets
0
Copy Count.Circulation Modifier.Migrated
- SELECT COUNT(ac.id), ac.circ_modifier, ac.x_migrate::TEXT FROM asset_copy_legacy ac GROUP BY 2, 3 ORDER BY 2, 3
+ 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
- asset_copy_notes
+ m_asset_copy_notes
Copy Notes
assets
0
Note Count.Public.Migrated
- SELECT COUNT(acnote.id), acnote.pub::TEXT, acnote.x_migrate::TEXT FROM asset_copy_note_legacy acnote GROUP BY 2, 3 ORDER BY 2, 3
+ 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
- asset_copy_notes
+ m_asset_copy_notes
Copy Notes
assets
1
Note Count.Public
- SELECT COUNT(acnote.id), acnote.pub::TEXT FROM asset_copy_note acnote GROUP BY 2 ORDER BY 2
+ SELECT COUNT(acnote.id), acnote.pub::TEXT FROM m_asset_copy_note acnote GROUP BY 2 ORDER BY 2
- asset_vols_by_lib
+ m_asset_vols_by_lib
Volumes by Library
assets
0
Volume Count.Library.Migrated
- SELECT COUNT(acn.id), aou.name, acn.x_migrate::TEXT FROM asset_call_number_legacy acn JOIN actor_org_unit_legacy aou ON aou.id = acn.owning_lib GROUP BY 2, 3 ORDER BY 2, 3
+ 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
- asset_vols_by_lib
+ m_asset_vols_by_lib
Volumes by Library
assets
1
Volume Count.Library
- SELECT COUNT(acn.id), aou.name FROM asset_call_number acn JOIN actor.org_unit aou ON aou.id = acn.owning_lib GROUP BY 2 ORDER BY 2
+ 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
- asset_cops_by_loc_and_org
+ m_asset_cops_by_loc_and_org
Copies by Location
assets
0
Copy Count.Library.Circ Library.Migrated
- SELECT COUNT(ac.id), acl.name, aou.name, ac.x_migrate::TEXT FROM 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
+ 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
- asset_cops_w_loc_one
+ m_asset_cops_w_loc_one
Copies with a Location of Stacks
assets
0
Barcode
- SELECT barcode FROM asset_copy_legacy WHERE location = 1 AND x_migrate
+ SELECT barcode FROM m_asset_copy_legacy WHERE location = 1 AND x_migrate
- asset_no_barcode
+ m_asset_no_barcode
Items Without Barcodes
assets
0
Assigned Barcode
- SELECT barcode FROM asset_copy_legacy WHERE barcode ~* 'no_barocde' AND x_migrate
+ SELECT barcode FROM m_asset_copy_legacy WHERE barcode ~* 'no_barocde' AND x_migrate
- asset_barcode_patterns
+ m_asset_barcode_patterns
Common Barcode Starting Patterns
assets
0
Count of Barcodes (greater than 10).Left 60% of Characters
- SELECT COUNT(ac.id), LEFT(ac.barcode,(ROUND(LENGTH(ac.barcode)*.6))::INT) FROM asset_copy_legacy ac WHERE ac.x_migrate = TRUE GROUP BY 2 HAVING COUNT(ac.id) > 10 ORDER BY 2
+ SELECT COUNT(ac.id), LEFT(ac.barcode,(ROUND(LENGTH(ac.barcode)*.6))::INT) FROM m_asset_copy_legacy ac WHERE ac.x_migrate = TRUE GROUP BY 2 HAVING COUNT(ac.id) > 10 ORDER BY 2
- asset_barcode_collisions
+ m_asset_barcode_collisions
Copy Barcode Collisions
assets
0
Collision Count
- SELECT COUNT(id) FROM asset_copy_legacy WHERE x_migrate = TRUE AND barcode LIKE 'x_%'
+ SELECT COUNT(id) FROM m_asset_copy_legacy WHERE x_migrate = TRUE AND barcode LIKE 'x_%'
Incumbent collisions are those where the migrated barcodes collide with existing barcodes in the database.
- asset_barcode_collisions
+ m_asset_barcode_collisions
Copy Barcode Collisions
assets
1
Collision Count
- SELECT COUNT(id) FROM asset_copy WHERE barcode LIKE 'x_%'
+ SELECT COUNT(id) FROM m_asset_copy WHERE barcode LIKE 'x_%'
Incumbent collisions are those where the migrated barcodes collide with existing barcodes in the database.
- asset_barcode_collisions_shortlist
+ m_asset_barcode_collisions_shortlist
Copy Barcode Collisions (first 20)
assets
0
Collision List
- SELECT ac.barcode FROM asset_copy_legacy ac WHERE ac.barcode LIKE 'x_%' ORDER BY 1 LIMIT 20
+ SELECT ac.barcode FROM m_asset_copy_legacy ac WHERE ac.barcode LIKE 'x_%' ORDER BY 1 LIMIT 20
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.
- asset_barcode_collisions_shortlist
+ m_asset_barcode_collisions_shortlist
Copy Barcode Collisions (first 20)
assets
1
Collision List
- SELECT ac.barcode FROM asset_copy ac WHERE ac.barcode LIKE 'x_%' ORDER BY 1 LIMIT 20
+ SELECT ac.barcode FROM m_asset_copy ac WHERE ac.barcode LIKE 'x_%' ORDER BY 1 LIMIT 20
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.
- asset_barcode_collision_patterns
+ m_asset_barcode_collision_patterns
Common Copy Barcode Collision Patterns
assets
0
Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters
- SELECT COUNT(ac.id), LEFT(ac.barcode,(ROUND(LENGTH(ac.barcode)*.6))::INT) FROM asset_copy_legacy ac WHERE barcode LIKE 'x_%' GROUP BY 2 HAVING COUNT(ac.id) > 10 ORDER BY 2
+ 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
- asset_barcode_collision_patterns
+ m_asset_barcode_collision_patterns
Common Copy Barcode Collision Patterns
assets
1
Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters
- SELECT COUNT(ac.id), LEFT(ac.barcode,(ROUND(LENGTH(ac.barcode)*.6))::INT) FROM asset_copy ac WHERE barcode LIKE 'x_%' GROUP BY 2 HAVING COUNT(ac.id) > 10 ORDER BY 2
+ 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
- asset_stat_cats
+ m_asset_stat_cats
Copy Statistical Categories
assets
0
Stat Cat Count.Library.Statistical Category
- SELECT COUNT(ac_sc.id), aou.name, ac_sc.name FROM asset_stat_cat_legacy ac_sc JOIN actor.org_unit aou ON aou.id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
+ SELECT COUNT(ac_sc.id), aou.name, ac_sc.name FROM m_asset_stat_cat_legacy ac_sc JOIN actor.org_unit aou ON aou.id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
- asset_stat_cats
+ m_asset_stat_cats
Copy Statistical Categories
assets
1
Stat Cat Count.Library.Statistical Category
- SELECT COUNT(ac_sc.id), aou.name, ac_sc.name FROM asset_stat_cat ac_sc JOIN actor.org_unit aou ON aou.id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
+ SELECT COUNT(ac_sc.id), aou.name, ac_sc.name FROM m_asset_stat_cat ac_sc JOIN actor.org_unit aou ON aou.id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
- asset_stat_cat_entries
+ m_asset_stat_cat_entries
Copy Stat Cat User Entries
assets
0
Copy Stat Count.Library.Statistical Category
- SELECT COUNT(map.id), aou.name, ac_sc.name FROM asset_stat_cat_entry_copy_map_legacy map JOIN asset_stat_cat_legacy ac_sc ON ac_sc.id = map.stat_cat JOIN actor.org_unit aou ON aou.id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
+ SELECT COUNT(map.id), aou.name, ac_sc.name FROM m_asset_stat_cat_entry_copy_map_legacy map JOIN m_asset_stat_cat_legacy ac_sc ON ac_sc.id = map.stat_cat JOIN actor.org_unit aou ON aou.id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
- asset_stat_cat_entries
+ m_asset_stat_cat_entries
Copy Stat Cat User Entries
assets
1
Copy Stat Count.Library.Statistical Category
- SELECT COUNT(map.id), aou.name, ac_sc.name FROM asset_stat_cat_entry_copy_map map JOIN asset_stat_cat ac_sc ON ac_sc.id = map.stat_cat JOIN actor.org_unit aou ON aou.
+ SELECT COUNT(map.id), aou.name, ac_sc.name FROM m_asset_stat_cat_entry_copy_map map JOIN m_asset_stat_cat ac_sc ON ac_sc.id = map.stat_cat JOIN actor.org_unit aou ON aou.
id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
- asset_copy_tags
+ m_asset_copy_tags
Copy Tags
assets
0
Tag Count.Copy Tag Type.Copy Tag Label.Staff Note.Public
- SELECT COUNT(map.id), tag.tag_type, tag.label, tag.staff_note, tag.pub FROM asset_copy_tag tag JOIN asset_copy_tag_copy_map map ON map.tag = tag.id GROUP BY 2,3,4,5 ORDER BY 2,3
+ SELECT COUNT(map.id), tag.tag_type, tag.label, tag.staff_note, tag.pub FROM m_asset_copy_tag tag JOIN m_asset_copy_tag_copy_map map ON map.tag = tag.id GROUP BY 2,3,4,5 ORDER BY 2,3
- asset_copy_alerts
+ m_asset_copy_alerts
Copy Alerts
assets
0
Alert Count.Alert Type
- SELECT COUNT(*), cat.name FROM asset_copy_alert aca JOIN config.copy_alert_type cat ON cat.id = aca.alert_type GROUP BY 2
+ SELECT COUNT(*), cat.name FROM m_asset_copy_alert aca JOIN config.copy_alert_type cat ON cat.id = aca.alert_type GROUP BY 2
- money_billing_voided
+ m_money_billing_voided
Bills Voided And Not
money
0
Count.Voided.Sum.Migrated
- SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM money_billing_legacy a GROUP BY 2, 4 ORDER BY 2, 4
+ SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM m_money_billing_legacy a GROUP BY 2, 4 ORDER BY 2, 4
- money_billing_voided
+ m_money_billing_voided
Bills Voided And Not
money
1
Count.Voided.Sum
- SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM money_billing a GROUP BY 2 ORDER BY 2, 3
+ SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM m_money_billing a GROUP BY 2 ORDER BY 2, 3
- money_billing_by_type
+ m_money_billing_by_type
Bills by Type
money
0
Count.Billing Type.Migrated
- SELECT COUNT(a.id), a.billing_type, a.x_migrate::TEXT FROM money_billing_legacy a GROUP BY 2, 3 ORDER BY 2, 3
+ SELECT COUNT(a.id), a.billing_type, a.x_migrate::TEXT FROM m_money_billing_legacy a GROUP BY 2, 3 ORDER BY 2, 3
- money_billing_by_type
+ m_money_billing_by_type
Bills by Type
money
1
Count.Billing Type
- SELECT COUNT(a.id), a.billing_type FROM money_billing a GROUP BY 2 ORDER BY 2
+ SELECT COUNT(a.id), a.billing_type FROM m_money_billing a GROUP BY 2 ORDER BY 2
- money_cash_payment
+ m_money_cash_payment
Cash Payments
money
0
Count.Voided.Sum.Migrated
- SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM money_cash_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4
+ SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM m_money_cash_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4
- money_cash_payment
+ m_money_cash_payment
Cash Payments
money
1
Count.Voided.Sum
- SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM money_cash_payment a GROUP BY 2 ORDER BY 2
+ SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM m_money_cash_payment a GROUP BY 2 ORDER BY 2
- money_check_payment
+ m_money_check_payment
Check Payments
money
0
Count.Voided.Sum.Migrated
- SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM money_check_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4
+ SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM m_money_check_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4
- money_forgive_payment
+ m_money_forgive_payment
Forgive Payments
money
0
Count.Voided.Sum.Migrated
- SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM money_forgive_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4
+ SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM m_money_forgive_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4
- money_forgive_payment
+ m_money_forgive_payment
Forgive Payments
money
1
Count.Voided.Sum
- SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM money_forgive_paymen a GROUP BY 2 ORDER BY 2
+ SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM m_money_forgive_paymen a GROUP BY 2 ORDER BY 2
- money_goods_payment
+ m_money_goods_payment
Goods Payments
money
0
Count.Voided.Sum.Migrated
- SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM money_goods_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4
+ SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM m_money_goods_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4
- money_work_payment
+ m_money_work_payment
Work Payments
money
0
Count.Voided.Sum.Migrated
- SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM money_work_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4
+ SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM m_money_work_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4
- money_credit_card_payment
+ m_money_credit_card_payment
Credit Card Payments
money
0
Count.Voided.Sum.Migrated
- SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM money_credit_card_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4
+ SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM m_money_credit_card_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4
- money_credit_payment
+ m_money_credit_payment
Credit Payments
money
0
Count.Voided.Sum.Migrated
- SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM money_credit_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4
+ SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM m_money_credit_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4
@@ -691,7 +691,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
bibs
0
Count
- SELECT COUNT(bre.id) FROM biblio_record_entry_legacy bre WHERE id IN (SELECT id FROM biblio.record_entry) ORDER BY 1
+ SELECT COUNT(bre.id) FROM m_biblio_record_entry_legacy bre WHERE id IN (SELECT id FROM biblio.record_entry) ORDER BY 1
@@ -700,7 +700,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
bibs
1
Count
- SELECT COUNT(bre.id) FROM biblio_record_entry bre WHERE id IN (SELECT id FROM biblio.record_entry) ORDER BY 1
+ SELECT COUNT(bre.id) FROM m_biblio_record_entry bre WHERE id IN (SELECT id FROM biblio.record_entry) ORDER BY 1
@@ -709,7 +709,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
bibs
0
Count
- SELECT COUNT(id) FROM biblio_record_entry where id NOT IN (SELECT DISTINCT record FROM asset_call_number)
+ SELECT COUNT(id) FROM m_biblio_record_entry where id NOT IN (SELECT DISTINCT record FROM m_asset_call_number)
These records would not have been loaded.
@@ -719,7 +719,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
bibs
0
Count
- SELECT COUNT(id) FROM biblio_record_entry where id IN (SELECT record FROM asset.call_number WHERE label ~* '##URI##')
+ SELECT COUNT(id) FROM m_biblio_record_entry where id IN (SELECT record FROM asset.call_number WHERE label ~* '##URI##')
@@ -728,7 +728,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
bibs
0
Count.Original Search Format.New Search Format
- SELECT COUNT(*), ARRAY_TO_STRING(x_search_format,','), ARRAY_TO_STRING(x_after_search_format,',') FROM biblio_record_entry_legacy WHERE x_migrate AND x_after_search_format IS NOT NULL GROUP BY 2, 3 ORDER BY 3,2
+ SELECT COUNT(*), ARRAY_TO_STRING(x_search_format,','), ARRAY_TO_STRING(x_after_search_format,',') FROM m_biblio_record_entry_legacy WHERE x_migrate AND x_after_search_format IS NOT NULL GROUP BY 2, 3 ORDER BY 3,2
@@ -737,7 +737,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
bibs
0
Count.Migrated
- SELECT COUNT(b.id), b.x_migrate::TEXT FROM biblio_record_note_legacy b GROUP BY 2
+ SELECT COUNT(b.id), b.x_migrate::TEXT FROM m_biblio_record_note_legacy b GROUP BY 2
@@ -746,7 +746,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
bibs
1
Count
- SELECT COUNT(b.id) FROM biblio_record_note b
+ SELECT COUNT(b.id) FROM m_biblio_record_note b
@@ -755,7 +755,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
bibs
0
Count.Migrated
- SELECT COUNT(b.id), b.x_migrate::TEXT FROM biblio_peer_bib_copy_map_legacy b GROUP BY 2
+ SELECT COUNT(b.id), b.x_migrate::TEXT FROM m_biblio_peer_bib_copy_map_legacy b GROUP BY 2
@@ -764,7 +764,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
bibs
1
Count
- SELECT COUNT(b.id) FROM biblio_peer_bib_copy_map b
+ SELECT COUNT(b.id) FROM m_biblio_peer_bib_copy_map b
@@ -773,7 +773,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
bibs
0
Count.Migrated
- SELECT COUNT(b.id), b.x_migrate::TEXT FROM biblio_monograph_part_legacy b GROUP BY 2
+ SELECT COUNT(b.id), b.x_migrate::TEXT FROM m_biblio_monograph_part_legacy b GROUP BY 2
@@ -782,7 +782,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
bibs
1
Count
- SELECT COUNT(b.id) FROM biblio_monograph_part b
+ SELECT COUNT(b.id) FROM m_biblio_monograph_part b
@@ -812,7 +812,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
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 biblio_record_entry a LEFT JOIN biblio.record_entry b ON b.id = a.id WHERE b.id IS NULL AND a.x_migrate
+ 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
@@ -825,7 +825,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
actors
0
Count.Library.Deleted.Migrated
- SELECT COUNT(au.id), aou.name, au.deleted::TEXT, au.x_migrate::TEXT FROM 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
+ 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
@@ -834,7 +834,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
actors
0
Count.Permission Group.Migrated
- SELECT COUNT(au.id), pgt.name, au.x_migrate::TEXT FROM actor_usr_legacy au JOIN permission.grp_tree pgt ON pgt.id = au.profile GROUP BY 2, 3 ORDER BY 2, 3
+ 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
@@ -843,7 +843,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
actors
0
Count of Users.Active.Migrated
- SELECT COUNT(id), active::TEXT, x_migrate::TEXT FROM actor_usr_legacy GROUP BY 2, 3
+ SELECT COUNT(id), active::TEXT, x_migrate::TEXT FROM m_actor_usr_legacy GROUP BY 2, 3
@@ -852,7 +852,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
actors
1
Count of Users.Active
- SELECT COUNT(id), active::TEXT FROM actor_usr GROUP BY 2
+ SELECT COUNT(id), active::TEXT FROM m_actor_usr GROUP BY 2
@@ -861,7 +861,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
actors
0
Count of Barcodes.Active.Migrated
- SELECT COUNT(id), active::TEXT, x_migrate::TEXT FROM actor_card_legacy GROUP BY 2, 3
+ SELECT COUNT(id), active::TEXT, x_migrate::TEXT FROM m_actor_card_legacy GROUP BY 2, 3
@@ -870,7 +870,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
actors
1
Count of Barcodes.Active
- SELECT COUNT(id), active::TEXT FROM actor_card GROUP BY 2
+ SELECT COUNT(id), active::TEXT FROM m_actor_card GROUP BY 2
@@ -897,7 +897,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
actors
0
Count of Barcodes (greater than 10).Left 60% of Characters
- SELECT COUNT(acard.id), LEFT(acard.barcode,(ROUND(LENGTH(acard.barcode)*.6))::INT) FROM actor_card_legacy acard WHERE acard.x_migrate = TRUE GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2
+ SELECT COUNT(acard.id), LEFT(acard.barcode,(ROUND(LENGTH(acard.barcode)*.6))::INT) FROM m_actor_card_legacy acard WHERE acard.x_migrate = TRUE GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2
@@ -906,7 +906,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
actors
1
Count of Barcodes (greater than 10).Left 60% of Characters
- SELECT COUNT(acard.id), LEFT(acard.barcode,(ROUND(LENGTH(acard.barcode)*.6))::INT) FROM actor_card acard GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2
+ SELECT COUNT(acard.id), LEFT(acard.barcode,(ROUND(LENGTH(acard.barcode)*.6))::INT) FROM m_actor_card acard GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2
@@ -915,7 +915,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
actors
0
Collision Count
- SELECT COUNT(acard.id) FROM actor_card_legacy acard WHERE barcode LIKE 'x_%' AND x_migrate = TRUE
+ SELECT COUNT(acard.id) FROM m_actor_card_legacy acard WHERE barcode LIKE 'x_%' AND x_migrate = TRUE
@@ -924,7 +924,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
actors
1
Collision Count
- SELECT COUNT(acard.id) FROM actor_card acard WHERE barcode LIKE 'x_%'
+ SELECT COUNT(acard.id) FROM m_actor_card acard WHERE barcode LIKE 'x_%'
@@ -933,7 +933,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
actors
0
Collision List
- SELECT acard.barcode FROM actor_card_legacy acard WHERE acard.barcode LIKE 'x_' AND acard.x_migrate = TRUE ORDER BY 1 LIMIT 20
+ SELECT acard.barcode FROM m_actor_card_legacy acard WHERE acard.barcode LIKE 'x_' AND acard.x_migrate = TRUE ORDER BY 1 LIMIT 20
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.
@@ -943,7 +943,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
actors
1
Collision List
- SELECT acard.barcode FROM actor_card acard WHERE acard.barcode LIKE 'x_%' ORDER BY 1 LIMIT 20
+ SELECT acard.barcode FROM m_actor_card acard WHERE acard.barcode LIKE 'x_%' ORDER BY 1 LIMIT 20
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.
@@ -953,7 +953,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
actors
0
Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters
- SELECT COUNT(acard.id), LEFT(acard.barcode,(ROUND(LENGTH(acard.barcode)*.6))::INT) FROM actor_card_legacy acard WHERE (acard.barcode ~* 'collision' OR acard.barcode ~* '^x_') AND acard.x_migrate = TRUE GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2
+ SELECT COUNT(acard.id), LEFT(acard.barcode,(ROUND(LENGTH(acard.barcode)*.6))::INT) FROM m_actor_card_legacy acard WHERE (acard.barcode ~* 'collision' OR acard.barcode ~* '^x_') AND acard.x_migrate = TRUE GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2
@@ -962,7 +962,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
actors
1
Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters
- SELECT COUNT(acard.id), LEFT(acard.barcode,(ROUND(LENGTH(acard.barcode)*.6))::INT) FROM actor_card acard WHERE (acard.barcode ~* 'collision' OR acard.barcode ~* '^x_') GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2
+ SELECT COUNT(acard.id), LEFT(acard.barcode,(ROUND(LENGTH(acard.barcode)*.6))::INT) FROM m_actor_card acard WHERE (acard.barcode ~* 'collision' OR acard.barcode ~* '^x_') GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2
@@ -971,7 +971,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
actors
0
Count.Valid.Migrated
- SELECT COUNT(aua.id), valid::TEXT, x_migrate::TEXT FROM actor_usr_address_legacy aua GROUP BY 2, 3
+ SELECT COUNT(aua.id), valid::TEXT, x_migrate::TEXT FROM m_actor_usr_address_legacy aua GROUP BY 2, 3
@@ -980,7 +980,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
actors
1
Count.Valid
- SELECT COUNT(aua.id), valid::TEXT FROM actor_usr_address aua GROUP BY 2
+ SELECT COUNT(aua.id), valid::TEXT FROM m_actor_usr_address aua GROUP BY 2
@@ -989,7 +989,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
actors
0
Count of Addresses.Pending.Migrated
- SELECT COUNT(aua.id), pending::TEXT, x_migrate::TEXT FROM actor_usr_address_legacy aua GROUP BY 2, 3
+ SELECT COUNT(aua.id), pending::TEXT, x_migrate::TEXT FROM m_actor_usr_address_legacy aua GROUP BY 2, 3
@@ -998,7 +998,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
actors
1
Count of Addresses.Pending
- SELECT COUNT(aua.id), pending::TEXT FROM actor_usr_address aua GROUP BY 2
+ SELECT COUNT(aua.id), pending::TEXT FROM m_actor_usr_address aua GROUP BY 2
@@ -1007,7 +1007,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
actors
0
Count.Deleted.Migrated
- SELECT COUNT(aum.id), deleted::TEXT, x_migrate::TEXT FROM actor_usr_message_legacy aum GROUP BY 2, 3
+ SELECT COUNT(aum.id), deleted::TEXT, x_migrate::TEXT FROM m_actor_usr_message_legacy aum GROUP BY 2, 3
@@ -1016,7 +1016,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
actors
1
Count.Deleted
- SELECT COUNT(aum.id), deleted::TEXT FROM actor_usr_message_legacy aum GROUP BY 2
+ SELECT COUNT(aum.id), deleted::TEXT FROM m_actor_usr_message_legacy aum GROUP BY 2
@@ -1025,7 +1025,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
actors
0
Count.Public.Migrated
- SELECT COUNT(aun.id), pub::TEXT, x_migrate::TEXT FROM actor_usr_note_legacy aun GROUP BY 2, 3
+ SELECT COUNT(aun.id), pub::TEXT, x_migrate::TEXT FROM m_actor_usr_note_legacy aun GROUP BY 2, 3
@@ -1034,7 +1034,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
actors
1
Count.Public
- SELECT COUNT(aun.id), pub::TEXT FROM actor_usr_note aun GROUP BY 2
+ SELECT COUNT(aun.id), pub::TEXT FROM m_actor_usr_note aun GROUP BY 2
@@ -1043,7 +1043,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
actors
0
Stat Cat Count.Library.Statistical Category.Migrated
- SELECT COUNT(au_sc.id), aou.name, au_sc.name, au_sc.x_migrate::TEXT FROM actor_stat_cat_legacy au_sc JOIN actor.org_unit aou ON aou.id = au_sc.owner GROUP BY 2, 3, 4 ORDER BY 2, 3, 4
+ SELECT COUNT(au_sc.id), aou.name, au_sc.name, au_sc.x_migrate::TEXT FROM m_actor_stat_cat_legacy au_sc JOIN actor.org_unit aou ON aou.id = au_sc.owner GROUP BY 2, 3, 4 ORDER BY 2, 3, 4
@@ -1052,7 +1052,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
actors
1
Stat Cat Count.Library.Statistical Category
- SELECT COUNT(au_sc.id), aou.name, au_sc.name FROM actor_stat_cat au_sc JOIN actor.org_unit aou ON aou.id = au_sc.owner GROUP BY 2, 3 ORDER BY 2, 3
+ SELECT COUNT(au_sc.id), aou.name, au_sc.name FROM m_actor_stat_cat au_sc JOIN actor.org_unit aou ON aou.id = au_sc.owner GROUP BY 2, 3 ORDER BY 2, 3
@@ -1061,7 +1061,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
actors
0
Patron Stat Count.Library.Statistical Category.Migrated
- SELECT COUNT(map.id), aou.name, au_sc.name, map.x_migrate::TEXT FROM actor_stat_cat_entry_usr_map_legacy map JOIN actor_stat_cat_legacy au_sc ON au_sc.id = map.stat_cat JOIN actor.org_unit aou ON aou.id = au_sc.owner GROUP BY 2, 3, 4 ORDER BY 2,3, 4
+ SELECT COUNT(map.id), aou.name, au_sc.name, map.x_migrate::TEXT FROM m_actor_stat_cat_entry_usr_map_legacy map JOIN m_actor_stat_cat_legacy au_sc ON au_sc.id = map.stat_cat JOIN actor.org_unit aou ON aou.id = au_sc.owner GROUP BY 2, 3, 4 ORDER BY 2,3, 4
@@ -1070,7 +1070,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
actors
1
Patron Stat Count.Library.Statistical Category
- SELECT COUNT(map.id), aou.name, au_sc.name FROM actor_stat_cat_entry_usr_map map JOIN actor_stat_cat au_sc ON au_sc.id = map.stat_cat JOIN actor.org_unit aou ON aou.id = au_sc.owner GROUP BY 2, 3 ORDER BY 2,3
+ SELECT COUNT(map.id), aou.name, au_sc.name FROM m_actor_stat_cat_entry_usr_map map JOIN m_actor_stat_cat au_sc ON au_sc.id = map.stat_cat JOIN actor.org_unit aou ON aou.id = au_sc.owner GROUP BY 2, 3 ORDER BY 2,3
@@ -1090,9 +1090,9 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
actors
0
Count.Setting.Value
- SELECT COUNT(*), name, 'User''s Phone' FROM actor_usr_setting WHERE name IN ('opac.default_phone') GROUP BY 2, 3
- UNION ALL SELECT COUNT(*), name, value FROM actor_usr_setting WHERE name IN ('opac.hold_notify') GROUP BY 2, 3
- UNION ALL SELECT COUNT(*), a.name, aou.shortname FROM actor_usr_setting a JOIN actor.org_unit aou ON aou.id = REPLACE(a.value,'"','')::INTEGER
+ SELECT COUNT(*), name, 'User''s Phone' FROM m_actor_usr_setting WHERE name IN ('opac.default_phone') GROUP BY 2, 3
+ UNION ALL SELECT COUNT(*), name, value FROM m_actor_usr_setting WHERE name IN ('opac.hold_notify') GROUP BY 2, 3
+ UNION ALL SELECT COUNT(*), a.name, aou.shortname FROM m_actor_usr_setting a JOIN actor.org_unit aou ON aou.id = REPLACE(a.value,'"','')::INTEGER
WHERE a.name IN ('opac.default_pickup_location','opac.default_search_location') GROUP BY 2, 3 ORDER BY 2, 3;
@@ -1103,7 +1103,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
acq
Migrated Funds
Number of Funds.Migrated
- SELECT COUNT(id), x_migrate::TEXT FROM acq_fund_legacy GROUP BY 2;
+ SELECT COUNT(id), x_migrate::TEXT FROM m_acq_fund_legacy GROUP BY 2;
@@ -1112,7 +1112,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
acq
Migrated Funds
Number of Funds
- SELECT COUNT(id) FROM acq_fund;
+ SELECT COUNT(id) FROM m_acq_fund;
@@ -1121,7 +1121,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
acq
Migrated Invoices
Number of Invoices.Migrated
- SELECT COUNT(id), x_migrate::TEXT FROM acq_invoice_legacy GROUP BY 2;
+ SELECT COUNT(id), x_migrate::TEXT FROM m_acq_invoice_legacy GROUP BY 2;
@@ -1130,7 +1130,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
acq
Migrated Invoices
Number of Funds
- SELECT COUNT(id) FROM acq_invoice;
+ SELECT COUNT(id) FROM m_acq_invoice;
@@ -1140,7 +1140,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
0
Migrated Serial MFHDs
Number of MFHDs
- SELECT COUNT(id) FROM serial_record_entry
+ SELECT COUNT(id) FROM m_serial_record_entry
@@ -1208,10 +1208,10 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
Action Triggers Setup for Notices
ID.Active.Owner.Name
SELECT ed.id, ed.active, aou.shortname, ed.name
- FROM action_trigger.event_definition ed
+ FROM m_action_trigger.event_definition ed
JOIN actor.org_unit aou ON aou.id = ed.owner
- WHERE ed.owner IN (SELECT DISTINCT home_ou FROM actor_usr)
- OR ed.owner IN (SELECT DISTINCT parent_ou FROM actor.org_unit WHERE id in (SELECT DISTINCT home_ou FROM actor_usr));
+ 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));
@@ -1222,11 +1222,11 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3
Count of Notices Run with State
Count of Notices.State.ID.Owner.Name
SELECT COUNT(ate.id), ate.state, ed.id, aou.shortname, ed.name
- FROM action_trigger.event_definition ed
+ FROM m_action_trigger.event_definition ed
JOIN actor.org_unit aou ON aou.id = ed.owner
- JOIN action_trigger.event ate ON ate.event_def = ed.id
- WHERE ed.owner IN (SELECT DISTINCT home_ou FROM actor_usr)
- OR ed.owner IN (SELECT DISTINCT parent_ou FROM actor.org_unit WHERE id in (SELECT DISTINCT home_ou FROM actor_usr))
+ JOIN m_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;