X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=blobdiff_plain;f=mig-xml%2Fevergreen_full_system.xml;fp=mig-xml%2Fevergreen_full_system.xml;h=0000000000000000000000000000000000000000;hp=e972f6a9e1ce2df755c3b826cca9f91773d0b78c;hb=155eb9eac077ca803f75d1295e584e7012e1b883;hpb=69588457ab8f70fbb77af29cc0653933d24ed2ac diff --git a/mig-xml/evergreen_full_system.xml b/mig-xml/evergreen_full_system.xml deleted file mode 100644 index e972f6a..0000000 --- a/mig-xml/evergreen_full_system.xml +++ /dev/null @@ -1,519 +0,0 @@ - - - - - circ_count - circs - 0 - 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 - - - - circ_by_orgunit - circs - 0 - 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 - - - - circs_by_duration - circs - 0 - Circulations by Duration, Fine and Max Fine Rules - 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 - - - - circs_by_usrgroup - circs - 0 - Circulations by Rules and Patron Group - Count of Circs.Duration.Fine.Max Fine.User Group - SELECT COUNT(acirc.id), acirc.duration_rule, acirc.recurring_fine_rule, acirc.max_fine_rule, pgt.name FROM action.circulation acirc JOIN actor.usr au ON au.id = acirc.usr JOIN permission.grp_tree pgt ON pgt.id = au.profile - GROUP BY 2, 3, 4, 5 ORDER BY 2, 3, 4, 5 - - - - circs_by_circmod - circs - 0 - Circulations by Rules and Circulation Modifier - Count of Circs.Duration.Fine.Max Fine.Circulation Modifier - SELECT COUNT(acirc.id), acirc.duration_rule, acirc.recurring_fine_rule, acirc.max_fine_rule, ac.circ_modifier FROM action.circulation acirc JOIN asset.copy ac ON ac.id = acirc.target_copy - GROUP BY 2, 3, 4, 5 ORDER BY 2, 3, 4, 5 - - - - 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 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 - SELECT COUNT(id) FROM action.non_cataloged_circulation - - - - in_house - circs - 0 - In House Use - In House Use Records - SELECT COUNT(id) FROM action.in_house_use - - - - circs_missing_rules - circs - 0 - 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 - - - - - - - holds - holds - 0 - Holds - Hold Type.Hold Count - SELECT 'Closed Holds', COUNT(id) FROM action.hold_request WHERE (expire_time::TIMESTAMP < now()) OR cancel_time IS NOT NULL OR fulfillment_time IS NOT NULL UNION ALL SELECT 'Open Holds', COUNT(id) FROM action.hold_request WHERE (expire_time IS NULL OR expire_time::TIMESTAMP > now()) AND cancel_time IS NULL AND fulfillment_time IS NULL - - - - holds_bytype - holds - 0 - Holds By Type - Hold Type.Hold Count - SELECT hold_type as "Hold Type", COUNT(id) FROM action.hold_request 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 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 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 - - - - 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 action.hold_request) GROUP BY 2 ORDER By 2 - - - - - - asset.copy_count - Count of Copies by Library - assets - 0 - Copy Count.Library - SELECT COUNT(ac.id), aou.name FROM asset.copy ac JOIN actor.org_unit aou ON aou.id = ac.circ_lib GROUP BY 2 ORDER BY 2 - - - - asset.deleted_copies - Deleted Copies - assets - 0 - Copy Count.Deleted - SELECT COUNT(ac.id), ac.deleted::TEXT FROM asset.copy ac GROUP BY 2 - - - - asset.copies_by_status - Copies by Status - assets - 0 - Copy Count.Status - SELECT COUNT(ac.id), cs.name FROM asset.copy ac JOIN config.copy_status cs ON cs.id = ac.status GROUP BY 2 ORDER BY 2 - - - - asset.circ_mod_copies_count - Copies by Circulation Modifier - assets - 0 - Copy Count.Circulation Modifier - SELECT COUNT(ac.id), ac.circ_modifier FROM asset.copy ac GROUP BY 2 ORDER BY 2 - - - - asset.copy_notes - Copy Notes - assets - 0 - Note Count.Public - SELECT COUNT(acnote.id), acnote.pub::TEXT FROM asset.copy_note acnote GROUP BY 2 ORDER BY 2 - - - - asset.copy_notes - Copy Notes - assets - 0 - Note Count.Public - SELECT COUNT(acnote.id), acnote.pub::TEXT FROM asset.copy_note acnote GROUP BY 2 ORDER BY 2 - - - - asset.vols_by_lib - Volumes by Library - assets - 0 - 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 - - - - asset.vols_by_lib - Volumes by Library - assets - 0 - 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 - - - - asset.cops_by_loc_and_org - Copies by Location - assets - 0 - Copy Count.Library.Circ Library - SELECT COUNT(ac.id), acl.name, aou.name FROM asset.copy 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 ORDER BY 2, 3 - - - - asset.barcode_lengths - Barcode Lengths by Library - assets - 0 - Count of Barcode.Barcode Length.Library - SELECT COUNT(ac.id), LENGTH(ac.barcode), aou.name FROM asset.copy ac JOIN actor.org_unit aou ON aou.id = ac.circ_lib GROUP BY 2, 3 ORDER BY 3, 2 - - - - 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 ac_sc JOIN actor.org_unit aou ON aou.id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 - - - - 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 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 - 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 map JOIN 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.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 map JOIN 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 - 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 - - - - - - money.billing_voided - Bills Voided And Not - money - 0 - Count.Voided.Sum - SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM money.billing a GROUP BY 2 ORDER BY 2 - - - - money.billing_voided - Bills Voided And Not - money - 0 - Count.Voided.Sum - SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM money.billing a GROUP BY 2 ORDER BY 2, 3 - - - - money.billing_by_type - Bills by Type - money - 0 - Count.Billing Type - SELECT COUNT(a.id), a.billing_type FROM money.billing a GROUP BY 2 ORDER BY 2 - - - - money.billing_by_type - Bills by Type - money - 0 - Count.Billing Type - SELECT COUNT(a.id), a.billing_type FROM money.billing a GROUP BY 2 ORDER BY 2 - - - - money.cash_payment - Cash Payments - money - 0 - Count.Voided.Sum - SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM money.cash_payment a GROUP BY 2 ORDER BY 2 - - - - money.cash_payment - Cash Payments - money - 0 - Count.Voided.Sum - SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM money.cash_payment a GROUP BY 2 ORDER BY 2 - - - - money.check_payment - Check Payments - money - 0 - Count.Voided.Sum - SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM money.check_payment a GROUP BY 2 ORDER BY 2 - - - - money.forgive_payment - Forgive Payments - money - 0 - Count.Voided.Sum - SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM money.forgive_payment a GROUP BY 2 ORDER BY 2 - - - - - bibswovolumes - Bibliographic Records Without Volumes - bibs - 0 - Count - SELECT COUNT(id) FROM biblio.record_entry where id NOT IN (SELECT DISTINCT record FROM asset.call_number) AND deleted IS FALSE - - - - bibs_notes - Bib Record Notes - bibs - 0 - Count - SELECT COUNT(b.id) FROM biblio.record_note b - - - - bibs_peers - Peer Bib Copies - bibs - 0 - Count - SELECT COUNT(b.id) FROM biblio.peer_bib_copy_map b - - - - bibs_parts - Monograph Parts - bibs - 0 - Count - SELECT COUNT(b.id) FROM biblio.monograph_part b - - - - - - usrsbyorg - Patrons by Home Org - actors - 0 - Count.Library.Deleted - SELECT COUNT(au.id), aou.name, au.deleted::TEXT FROM actor.usr au JOIN actor.org_unit aou ON aou.id = au.home_ou GROUP BY 2, 3 ORDER BY 2, 3 - - - - usrsbypgt - Patrons by Permission Group - actors - 0 - Count.Permission Group - SELECT COUNT(au.id), pgt.name FROM actor.usr au JOIN permission.grp_tree pgt ON pgt.id = au.profile GROUP BY 2 ORDER BY 2 - - - - active_usrs - Patrons by Active Status - actors - 0 - Count of Users.Active - SELECT COUNT(id), active::TEXT FROM actor.usr GROUP BY 2 - - - - active_usr_barcodes - Patron Barcodes by Active Status - actors - 0 - Count of Barcodes.Active - SELECT COUNT(id), active::TEXT FROM actor.card GROUP BY 2 - - - - usr_barcode_lengths - Barcode Lengths by Library - actors - 0 - Count of Barcode.Barcode Length.Library - SELECT COUNT(acard.id), LENGTH(acard.barcode), aou.name FROM actor.card acard JOIN actor.usr au ON au.id = acard.usr JOIN actor.org_unit aou ON aou.id = au.home_ou GROUP BY 2, 3 ORDER BY 3, 2 - - - - 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 actor.card acard GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2 - - - - usr_addressses_status - Patron Addresses by Valid Status - actors - 0 - Count.Valid - SELECT COUNT(aua.id), valid::TEXT FROM actor.usr_address aua GROUP BY 2 - - - - usr_addresses_pending - Patron Addresses by Pending Status - actors - 0 - Count of Addresses.Pending - SELECT COUNT(aua.id), pending::TEXT FROM actor.usr_address aua GROUP BY 2 - - - - usr_messages - Patron Messages - actors - 0 - Count.Deleted - SELECT COUNT(aum.id), deleted::TEXT FROM actor.usr_message aum GROUP BY 2 - - - - usr_notes - Patron Notes - actors - 0 - Count.Public - SELECT COUNT(aun.id), pub::TEXT FROM actor.usr_note aun GROUP BY 2 - - - - usr_stat_cats - Patron Statistical Categories - actors - 0 - 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 - - - - usr_stat_cat_entries - Patron Stat Cat User Entries - actors - 0 - 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 - - - - - fund_count - 0 - acq - Funds - Number of Funds - SELECT COUNT(id) FROM acq.fund; - - - - invoice_count - 0 - acq - Invoices - Number of Funds - SELECT COUNT(id) FROM acq.invoice; - - - -