X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=blobdiff_plain;f=mig-xml%2Fevergreen_staged_report.xml;fp=mig-xml%2Fevergreen_staged_report.xml;h=0000000000000000000000000000000000000000;hp=050df4ae01d0ac0f7b420767162c327347e255fe;hb=155eb9eac077ca803f75d1295e584e7012e1b883;hpb=69588457ab8f70fbb77af29cc0653933d24ed2ac diff --git a/mig-xml/evergreen_staged_report.xml b/mig-xml/evergreen_staged_report.xml deleted file mode 100644 index 050df4a..0000000 --- a/mig-xml/evergreen_staged_report.xml +++ /dev/null @@ -1,1182 +0,0 @@ - - - find_cmm - DROP FUNCTION IF EXISTS find_cmm(BIGINT) - - CREATE OR REPLACE FUNCTION find_cmm(circ_id BIGINT) - RETURNS SETOF INTEGER[] - LANGUAGE plpgsql - AS $function$ - DECLARE - aou INTEGER; - ac INTEGER; - au INTEGER; - r INTEGER[]; - BEGIN - SELECT circ_lib FROM action.circulation WHERE id = circ_id INTO aou; - SELECT target_copy FROM action.circulation WHERE id = circ_id INTO ac; - SELECT usr FROM action.circulation WHERE id = circ_id INTO au; - - FOR r IN SELECT buildrows FROM action.find_circ_matrix_matchpoint(aou,ac,au,FALSE) - LOOP - RETURN NEXT r; - END LOOP; - RETURN; - END - $function$ - - - - create_subfield_u - DROP TABLE IF EXISTS subfield_u - 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 -
- - - - - - - - circ_count - circs - 0 - Migrated Circulations - Circulation Status.Count of Circs - 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 - - - - circ_count - circs - 1 - Open Circulations - 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.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 - - - - circ_by_orgunit - circs - 1 - Circulations by Org Unit - Circulations Count.Org Unit - 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 - - - - circs_by_duration - circs - 0 - Migrated 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 m_action_circulation_legacy WHERE x_migrate = TRUE GROUP BY 2, 3, 4 - - - - circs_by_duration - circs - 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 m_action_circulation GROUP BY 2, 3, 4 ORDER BY 2, 3, 4 - - - - circs_by_usrgroup - circs - 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 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 - - - - circs_by_usrgroup - circs - 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 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 - - - - circs_by_circmod - circs - 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 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 - - - - circs_by_circmod - circs - 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 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 - - - - circs_by_orgunit - circs - 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 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 - - - - circs_by_orgunit - circs - 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 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 - - - - non_cat_circs - circs - 0 - Non-Cataloged Circulation - Circulations Count.Migrated - SELECT COUNT(id), x_migrate::TEXT FROM m_action_non_cataloged_circulation_legacy GROUP BY 2 - - - - non_cat_circs - circs - 1 - Non-Cataloged Circulation - Circulations Count - SELECT COUNT(id) FROM m_action_non_cataloged_circulation - - - - in_house - circs - 0 - In House Use - In House Use Records.Migrated - SELECT COUNT(id), x_migrate::TEXT FROM m_action_in_house_use_legacy GROUP BY 2 - - - - in_house - circs - 1 - In House Use - In House Use Records - SELECT COUNT(id) FROM m_action_in_house_use - - - - circs_missing_rules - circs - 1 - Circs Missing Rules - Count.Field Missing - 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 - - - - - circ_open_by_item_status - circs - 0 - 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 - - - - - - holds - holds - 0 - 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 - - - - holds_bytype - holds - 0 - Migrated Holds By Type - 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 - - - - transit_open_by_item_status - holds - 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 m_action_transit_copy) AND atc.dest_recv_time IS NULL GROUP BY 2 ORDER BY 2 - - - - transit_copies_by_status - holds - 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 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 - - - - hold_copies_by_status - holds - 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 m_action_hold_request) GROUP BY 2 ORDER By 2 - - - - hold_depth - holds - 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 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 - - - - - - m_asset_copy_count - Count of Copies by Library - assets - 0 - 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_copy_count_non_migrated - Non-Migrated Count of Copies by Library - assets - 0 - 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 - - - - m_asset_copies_by_status - Copies by Status - assets - 0 - 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 - - - - m_asset_circ_mod_copies_count - Copies by Circulation Modifier - assets - 0 - 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 - - - - m_asset_copy_notes - Copy Notes - assets - 0 - 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 - - - - m_asset_copy_notes - Copy Notes - assets - 1 - Note Count.Public - SELECT COUNT(acnote.id), acnote.pub::TEXT FROM m_asset_copy_note acnote GROUP BY 2 ORDER BY 2 - - - - m_asset_vols_by_lib - Volumes by Library - assets - 0 - 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 - - - - m_asset_vols_by_lib - Volumes by Library - assets - 1 - Volume Count.Library - 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 - - - - m_asset_cops_by_loc_and_org - Copies by Location - assets - 0 - Copy Count.Location.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 - - - - m_asset_cops_w_loc_one - Copies with a Location of Stacks - assets - 0 - Barcode - SELECT barcode FROM m_asset_copy_legacy WHERE location = 1 AND x_migrate - - - - m_asset_no_barcode - Items Without Barcodes - assets - 0 - Assigned Barcode - SELECT barcode FROM m_asset_copy_legacy WHERE barcode ~* 'no_barocde' AND x_migrate - - - - 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 m_asset_copy_legacy ac WHERE ac.x_migrate = TRUE GROUP BY 2 HAVING COUNT(ac.id) > 10 ORDER BY 2 - - - - m_asset_barcode_collisions - Copy Barcode Collisions - assets - 0 - Collision Count - SELECT COUNT(id) FROM m_asset_copy_legacy WHERE x_migrate = TRUE AND barcode ~* '^x_' - Incumbent collisions are those where the migrated barcodes collide with existing barcodes in the database. - - - - m_asset_barcode_collisions - Copy Barcode Collisions - assets - 1 - Collision Count - SELECT COUNT(id) FROM m_asset_copy WHERE barcode ~* '^x_' - Incumbent collisions are those where the migrated barcodes collide with existing barcodes in the database. - - - - m_asset_barcode_collisions_shortlist - Copy Barcode Collisions (first 20) - assets - 0 - Collision List - SELECT ac.barcode FROM m_asset_copy_legacy ac WHERE ac.barcode ~* '^x_' AND ac.x_migrate = TRUE 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. - - - - m_asset_barcode_collisions_shortlist - Copy Barcode Collisions (first 20) - assets - 1 - Collision List - SELECT ac.barcode FROM m_asset_copy ac WHERE ac.barcode ~* '^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. - - - - 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 m_asset_copy_legacy ac WHERE barcode ~* '^x_' AND ac.x_migrate = TRUE GROUP BY 2 HAVING COUNT(ac.id) > 10 ORDER BY 2 - - - - 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 m_asset_copy ac WHERE barcode ~* '^x_' GROUP BY 2 HAVING COUNT(ac.id) > 10 ORDER BY 2 - - - - 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 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 - - - - 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 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 - - - - 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 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 - - - - 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 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 - - - - 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 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 - - - - m_asset_copy_alerts - Copy Alerts - assets - 0 - Alert Count.Alert Type - 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 - - - - - - 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 m_money_billing_legacy a GROUP BY 2, 4 ORDER BY 2, 4 - - - - m_money_billing_voided - Bills Voided And Not - money - 1 - Count.Voided.Sum - SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM m_money_billing a GROUP BY 2 ORDER BY 2, 3 - - - - 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 m_money_billing_legacy a GROUP BY 2, 3 ORDER BY 2, 3 - - - - m_money_billing_by_type - Bills by Type - money - 1 - Count.Billing Type - SELECT COUNT(a.id), a.billing_type FROM m_money_billing a GROUP BY 2 ORDER BY 2 - - - - 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 m_money_cash_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 - - - - m_money_cash_payment - Cash Payments - money - 1 - Count.Voided.Sum - SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM m_money_cash_payment a GROUP BY 2 ORDER BY 2 - - - - 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 m_money_check_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 - - - - 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 m_money_forgive_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 - - - - m_money_forgive_payment - Forgive Payments - money - 1 - Count.Voided.Sum - SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM m_money_forgive_paymen a GROUP BY 2 ORDER BY 2 - - - - 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 m_money_goods_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 - - - - 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 m_money_work_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 - - - - 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 m_money_credit_card_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 - - - - 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 m_money_credit_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 - - - - - - - bibs_loaded - Loaded Bibliographic Records - bibs - 0 - Count - SELECT COUNT(bre.id) FROM m_biblio_record_entry_legacy bre WHERE id IN (SELECT id FROM biblio.record_entry) ORDER BY 1 - - - - bibs_loaded - Loaded Bibliographic Records - bibs - 1 - Count - SELECT COUNT(bre.id) FROM m_biblio_record_entry bre WHERE id IN (SELECT id FROM biblio.record_entry) ORDER BY 1 - - - - bibswovolumes - Bibliographic Records Without Volumes - bibs - 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 but many may be deduplicated against incumbent records. - - - - bibswovolumesanduri - Bibliographic Records Without Volumes And Recognized URI - bibs - 0 - Service.Domain.Count - 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;; - - This list is built from known services and domains. If you have records for electronic resources that are not here please let us know. - - - - bibswuri - Bibliographic Records With 856$9s - bibs - 0 - Count - SELECT COUNT(id) FROM m_biblio_record_entry where id IN (SELECT record FROM asset.call_number WHERE label ~* '##URI##') - - - - bibsff - Bibliographic Records with Adjusted Fixed Fields - 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 m_biblio_record_entry_legacy WHERE x_migrate AND x_after_search_format IS NOT NULL GROUP BY 2, 3 ORDER BY 3,2 - - - - bibs_notes - Bib Record Notes - bibs - 0 - Count.Migrated - SELECT COUNT(b.id), b.x_migrate::TEXT FROM m_biblio_record_note_legacy b GROUP BY 2 - - - - bibs_notes - Bib Record Notes - bibs - 1 - Count - SELECT COUNT(b.id) FROM m_biblio_record_note b - - - - bibs_peers - Peer Bib Copies - bibs - 0 - Count.Migrated - SELECT COUNT(b.id), b.x_migrate::TEXT FROM m_biblio_peer_bib_copy_map_legacy b GROUP BY 2 - - - - bibs_peers - Peer Bib Copies - bibs - 1 - Count - SELECT COUNT(b.id) FROM m_biblio_peer_bib_copy_map b - - - - bibs_parts - Monograph Parts - bibs - 0 - Count.Migrated - SELECT COUNT(b.id), b.x_migrate::TEXT FROM m_biblio_monograph_part_legacy b GROUP BY 2 - - - - bibs_parts - Monograph Parts - bibs - 1 - Count - SELECT COUNT(b.id) FROM m_biblio_monograph_part b - - - - bib_merges - Bibliographic Merge Count - bibs - 0 - Records Merged.Incumbent Records Merged Into - SELECT SUM(array_length(records,1)), COUNT(*) FROM groups - - - - - - usrsbyorg - 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 - 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 - Migrated Patrons by Permission Group - actors - 0 - 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 - - - - active_usrs - Patron by Active Status - actors - 0 - Count of Users.Active - SELECT COUNT(id), active::TEXT FROM m_actor_usr_legacy WHERE x_migrate = TRUE GROUP BY 2 - - - - active_usrs - Patron Cards - actors - 1 - Count of Users - SELECT COUNT(id) FROM m_actor_usr - - - - active_usr_barcodes - Patron Barcodes by Active Status - actors - 0 - Count of Barcodes.Active.Migrated - SELECT COUNT(id), active::TEXT, x_migrate::TEXT FROM m_actor_card_legacy GROUP BY 2, 3 - - - - active_usr_barcodes - Patron Barcodes by Active Status - actors - 1 - Count of Barcodes.Active - SELECT COUNT(id), active::TEXT FROM m_actor_card GROUP BY 2 - - - - usr_barcode_patterns - Common Barcode Starting Patterns - 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 m_actor_card_legacy acard WHERE acard.x_migrate = TRUE GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2 - - - - usr_barcode_patterns - Common Barcode Starting Patterns - 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 m_actor_card acard GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2 - - - - usr_barcode_collisions - Patron Barcode Collisions - actors - 0 - Collision Count - SELECT COUNT(acard.id) FROM m_actor_card_legacy acard WHERE barcode ~* '^x_' AND x_migrate = TRUE - - - - usr_barcode_collisions - Patron Barcode Collisions - actors - 1 - Collision Count - SELECT COUNT(acard.id) FROM m_actor_card acard WHERE barcode ~* '^x_' - - - - usr_barcode_collision_shortlist - Patron Barcode Collisions (first 20) - actors - 0 - Collision List - SELECT acard.barcode FROM m_actor_card_legacy acard WHERE acard.barcode ~* '^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. - - - - usr_barcode_collision_shortlist - Patron Barcode Collisions (first 20) - actors - 1 - Collision List - SELECT acard.barcode FROM m_actor_card acard WHERE acard.barcode ~* '^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. - - - - usr_barcode_collision_patterns - Common Patron Barcode Collision Patterns a.x_migrate - 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 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 - - - - usr_barcode_collision_patterns - Common Patron Barcode Collision Patterns a.x_migrate - 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 m_actor_card acard WHERE (acard.barcode ~* 'collision' OR acard.barcode ~* '^x_') GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2 - - - - usr_addressses_status - Patron Addresses - actors - 0 - Count - SELECT COUNT(aua.id) FROM m_actor_usr_address_legacy aua WHERE aua.x_migrate = TRUE - - - - usr_addressses_status - Patron Addresses - actors - 1 - Count - SELECT COUNT(aua.id) FROM m_actor_usr_address aua - - - - usr_addresses_pending - Patron Addresses by Pending Status - actors - 0 - 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 - - - - usr_addresses_pending - Patron Addresses by Pending Status - actors - 1 - Count of Addresses.Pending - SELECT COUNT(aua.id), pending::TEXT FROM m_actor_usr_address aua GROUP BY 2 - - - - usr_messages - Patron Messages - actors - 0 - Count.Deleted.Migrated - SELECT COUNT(aum.id), deleted::TEXT, x_migrate::TEXT FROM m_actor_usr_message_legacy aum GROUP BY 2, 3 - - - - usr_messages - Patron Messages - actors - 1 - Count.Deleted - SELECT COUNT(aum.id), deleted::TEXT FROM m_actor_usr_message_legacy aum GROUP BY 2 - - - - usr_notes - Patron Notes - actors - 0 - Count.Public.Migrated - SELECT COUNT(aun.id), pub::TEXT, x_migrate::TEXT FROM m_actor_usr_note_legacy aun GROUP BY 2, 3 - - - - usr_notes - Patron Notes - actors - 1 - Count.Public - SELECT COUNT(aun.id), pub::TEXT FROM m_actor_usr_note aun GROUP BY 2 - - - - usr_stat_cats - Patron Statistical Categories - 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 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 - - - - usr_stat_cats - Patron Statistical Categories - actors - 1 - Stat Cat Count.Library.Statistical Category - 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 - - - - usr_stat_cat_entries - Patron Stat Cat User Entries - actors - 0 - Patron Stat Count.Library.Statistical Category.Migrated - 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 - - - - usr_stat_cat_entries - Patron Stat Cat User Entries - actors - 1 - Patron Stat Count.Library.Statistical Category - 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 - - - - usr_thresholds - Patron Thresholds - actors - 0 - Pateron Group.Org Unit.Penalty.Threshold - SELECT pgt.name, aou.shortname, sp.name, p.threshold FROM permission_grp_penalty_threshold p - JOIN actor.org_unit aou ON aou.id = p.org_unit JOIN permission.grp_tree pgt ON pgt.id = p.grp JOIN config.standing_penalty sp ON sp.id = p.penalty - ORDER BY 2, 1, 3 - - - - usr_settings - Patron Settings - actors - 0 - Count.Setting.Value - 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; - - - - - fund_count - 0 - acq - Migrated Funds - Number of Funds.Migrated - SELECT COUNT(id), x_migrate::TEXT FROM m_acq_fund_legacy GROUP BY 2; - - - - fund_count - 1 - acq - Migrated Funds - Number of Funds - SELECT COUNT(id) FROM m_acq_fund; - - - - invoice_count - 0 - acq - Migrated Invoices - Number of Invoices.Migrated - SELECT COUNT(id), x_migrate::TEXT FROM m_acq_invoice_legacy GROUP BY 2; - - - - invoice_count - 1 - acq - Migrated Invoices - Number of Funds - SELECT COUNT(id) FROM m_acq_invoice; - - - - - serials_mfhd_count - serials - 0 - Migrated Serial MFHDs - Number of MFHDs - SELECT COUNT(id) FROM m_serial_record_entry - - - - - - dedupe_explain - dedupe - dedupe_process.asciidoc - - - - dedupe_bib_groups - dedupe - 0 - Scoring and Bib Record Groups - Count.Bib Record Groups - SELECT COUNT(id), 'Total Bibs Being Evaluated' FROM biblio.record_entry WHERE deleted IS FALSE AND id IN (SELECT eg::BIGINT FROM bib_id_map) - UNION ALL SELECT (COUNT(DISTINCT incoming_bib)), 'Incoming Bibs With Matches Found' FROM bib_matches - UNION ALL SELECT (COUNT(bre.id) - (SELECT COUNT(DISTINCT incoming_bib) FROM bib_matches)), 'Incoming Bibs With No Match' - FROM biblio.record_entry bre WHERE bre.deleted IS FALSE AND bre.id IN (SELECT eg::BIGINT FROM bib_id_map) - UNION ALL SELECT COUNT(DISTINCT incoming_bib), 'Incoming Bibs Being Merged into Incumbent' FROM bib_matches WHERE incumbent_bib_score >= incoming_bib_score - UNION ALL SELECT COUNT(id), 'Incumbent Bibs With Higher Scores to Incoming' FROM bib_matches WHERE incumbent_bib_score > incoming_bib_score - UNION ALL SELECT COUNT(id), 'Incumbent Bibs With Equal Scores to Incoming' FROM bib_matches WHERE incumbent_bib_score = incoming_bib_score - UNION ALL SELECT COUNT(id), 'Incumbent Bibs With Lower Scores to Incoming' FROM bib_matches WHERE incumbent_bib_score < incoming_bib_score - ; - - - - dedupe_format_count - dedupe - 0 - Count of Items Matching By Format - Count.Format(s) - SELECT COUNT(id), search_formats FROM bib_matches GROUP BY 2 ORDER BY 2; - - - - dedupe_score_ranges - dedupe - 0 - Count of Items Matching By Format - Lowest Record Score.Largest Record Score.Record Set - SELECT MIN(incumbent_bib_score), MAX(incumbent_bib_score), 'Incumbent Records' FROM bib_matches - UNION ALL SELECT MIN(incoming_bib_score), MAX(incoming_bib_score), 'Incoming Records' FROM bib_matches ; - - - - - - dedupe_sample_set - dedupe - 0 - Sample of 20 Matching Dedupe Record Sets - Bib Being Merged Into.Bib Being Merged - SELECT incumbent_bib, incoming_bib FROM bib_matches WHERE incumbent_bib_score >= incoming_bib_score LIMIT 20 ; - - - - - - - notices_overview - notices - 0 - Action Triggers Setup for Notices - ID.Active.Owner.Name - 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)); - - - - - notices_count - notices - 0 - 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 - 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 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; - - - - - -