From: Rogan Hamby Date: Tue, 14 Jan 2020 16:17:43 +0000 (-0500) Subject: various simplifying of reports to make it more end user readable X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=commitdiff_plain;h=05b6c7bc5e8591947123a483d68111656f8246f8 various simplifying of reports to make it more end user readable --- diff --git a/mig-xml/evergreen_staged_report.xml b/mig-xml/evergreen_staged_report.xml index e42125a..289261c 100644 --- a/mig-xml/evergreen_staged_report.xml +++ b/mig-xml/evergreen_staged_report.xml @@ -51,16 +51,6 @@ - circ_count_unmigrated - circs - 0 - Open Un-migrated Circulations - Circulation Status.Count of Circs - 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 - - - circ_count circs 1 @@ -68,23 +58,14 @@ Circulation Status.Count of Circs 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 - + circ_by_orgunit circs 0 Circulations by Org Unit - Circulations Count.Migrated.Org Unit - 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 + Circulations Count.Org Unit + 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 @@ -101,8 +82,8 @@ circs 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 m_action_circulation_legacy WHERE x_migrate = TRUE GROUP BY 2, 3, 4, 5 + Count of Circs.Duration.Fine.Max Fine + 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 @@ -224,7 +205,7 @@ circ_open_by_item_status circs 0 - Open Circulation and Status of Linked Items + Status of Currently Circulating 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 m_action_circulation) GROUP BY 2 ORDER BY 2 @@ -235,7 +216,7 @@ holds holds 0 - Migrated Holds + Migrated and Non-Migrated Holds Hold Type.Hold Count.Migrated 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 @@ -245,8 +226,8 @@ holds 0 Migrated Holds By Type - Hold Type.Hold Count.Migrated - SELECT hold_type as "Hold Type", COUNT(id), x_migrate::TEXT FROM m_action_hold_request_legacy GROUP BY 1, 3 + Hold Type.Hold Count + SELECT hold_type as "Hold Type", COUNT(id) FROM m_action_hold_request_legacy WHERE x_migrate = TRUE GROUP BY 1 @@ -292,17 +273,17 @@ Count of Copies by Library assets 0 - Copy Count.Library.Migrated - 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 + Copy Count.Library + 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 - m_asset_deleted_copies - Deleted Copies + m_asset_copy_count_non_migrated + Non-Migrated Count of Copies by Library assets 0 - Copy Count.Deleted.Migrated - SELECT COUNT(ac.id), ac.deleted::TEXT, ac.x_migrate::TEXT FROM m_asset_copy_legacy ac GROUP BY 2, 3 + Copy Count.Library + 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 @@ -310,8 +291,8 @@ Copies by Status assets 0 - Copy Count.Status.Migrated - 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 + Copy Count.Status + 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 @@ -319,8 +300,8 @@ Copies by Circulation Modifier assets 0 - Copy Count.Circulation Modifier.Migrated - 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 + Copy Count.Circulation Modifier + SELECT COUNT(ac.id), ac.circ_modifier FROM m_asset_copy_legacy ac WHERE ac.x_migrate = TRUE GROUP BY 2 ORDER BY 2 @@ -328,8 +309,8 @@ Copy Notes assets 0 - Note Count.Public.Migrated - 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 + Note Count.Public + 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 @@ -346,8 +327,8 @@ Volumes by Library assets 0 - Volume Count.Library.Migrated - 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 + Volume Count.Library + 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 @@ -364,8 +345,8 @@ Copies by Location assets 0 - Copy Count.Library.Circ Library.Migrated - 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 + Copy Count.Library.Circ Library + 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 @@ -655,7 +636,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 0 Count 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. + These records would not have been loaded but many may be deduplicated against incumbent records. @@ -743,20 +724,29 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 usrsbyorg - Patrons by Home Org + Migrated Patrons by Home Org + actors + 0 + Count.Library + 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 + + + + nonmigusrsbyorg + Non-Migrated Patrons by Home Org actors 0 - Count.Library.Deleted.Migrated - 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 + Count.Library + 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 usrsbypgt - Patrons by Permission Group + Migrated Patrons by Permission Group actors 0 - Count.Permission Group.Migrated - 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 + Count.Permission Group + 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 @@ -764,17 +754,17 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 Patron by Active Status actors 0 - Count of Users.Active.Migrated - SELECT COUNT(id), active::TEXT, x_migrate::TEXT FROM m_actor_usr_legacy GROUP BY 2, 3 + Count of Users.Active + SELECT COUNT(id), active::TEXT FROM m_actor_usr_legacy GROUP BY 2 active_usrs - Patrons by Active Status + Patron Cards actors 1 - Count of Users.Active - SELECT COUNT(id), active::TEXT FROM m_actor_usr GROUP BY 2 + Count of Users + SELECT COUNT(id) FROM m_actor_usr @@ -871,20 +861,20 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 usr_addressses_status - Patron Addresses by Valid Status + Patron Addresses actors 0 - Count.Valid.Migrated - SELECT COUNT(aua.id), valid::TEXT, x_migrate::TEXT FROM m_actor_usr_address_legacy aua GROUP BY 2, 3 + Count + SELECT COUNT(aua.id) FROM m_actor_usr_address_legacy aua WHERE aua.x_migrate = TRUE usr_addressses_status - Patron Addresses by Valid Status + Patron Addresses actors 1 - Count.Valid - SELECT COUNT(aua.id), valid::TEXT FROM m_actor_usr_address aua GROUP BY 2 + Count + SELECT COUNT(aua.id) FROM m_actor_usr_address aua @@ -892,8 +882,8 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 Patron Addresses by Pending Status actors 0 - Count of Addresses.Pending.Migrated - SELECT COUNT(aua.id), pending::TEXT, x_migrate::TEXT FROM m_actor_usr_address_legacy aua GROUP BY 2, 3 + Count of Addresses.Pending + SELECT COUNT(aua.id), pending::TEXT FROM m_actor_usr_address_legacy aua WHERE aua.x_migrate = TRUE GROUP BY 2