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=8ecc49f5d1e04e7449d8cae95d83d601d46d7348;hp=0000000000000000000000000000000000000000;hb=5d03cbc1b03b66811e4c8882b2dd14b6993a76bd;hpb=60e7459b06448fa223a7f84aa5e30814e2eae426 diff --git a/mig-xml/evergreen_full_system.xml b/mig-xml/evergreen_full_system.xml new file mode 100644 index 0000000..8ecc49f --- /dev/null +++ b/mig-xml/evergreen_full_system.xml @@ -0,0 +1,529 @@ + + + + + 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 + Count of Circs.Duration.Fine.Max Fine + SELECT COUNT(id), duration, recurring_fine, max_fine 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, acirc.recurring_fine, acirc.max_fine, 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, acirc.recurring_fine, acirc.max_fine, 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, acirc.recurring_fine, acirc.max_fine, 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; + + + + + serials_mfhd_count + serials + 0 + Serial MFHDs + Number of MFHDs + SELECT COUNT(id) FROM serial.record_entry + + + +