$function$</create>
</function>
+ <table>
+ <name>create_subfield_u</name>
+ <drop>DROP TABLE IF EXISTS subfield_u</drop>
+ <create>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</create>
+ </table>
<!-- sample reports entry
<report>
</report>
-->
+<!-- adding sample pie chart report
+ <report>
+ <name>current_holds_by_state</name>
+ <tag>holds</tag>
+ <iteration>0</iteration>
+ <display>pie_chart</display>
+ <report_title>Holds by Current State (7 days)</report_title>
+ <heading>Captured.Fulfilled.Canceled.Targeted.Waiting</heading>
+ <chart_labels>Hold Type.Count</chart_labels>
+ <query> SELECT
+ (SELECT COUNT(*) FROM action.hold_request WHERE fulfillment_time IS NULL AND capture_time > NOW() - '7 days'::INTERVAL AND cancel_time IS NULL),
+ (SELECT COUNT(*) FROM action.hold_request WHERE fulfillment_time > NOW() - '7 days'::INTERVAL AND capture_time IS NOT NULL AND cancel_time IS NULL),
+ (SELECT COUNT(*) FROM action.hold_request WHERE fulfillment_time IS NULL AND cancel_time > NOW() - '7 days'::INTERVAL),
+ (SELECT COUNT(*) FROM action.hold_request WHERE fulfillment_time IS NULL AND capture_time IS NULL AND cancel_time IS NULL AND current_copy IS NOT NULL),
+ (SELECT COUNT(*) FROM action.hold_request WHERE fulfillment_time IS NULL AND capture_time IS NULL AND cancel_time IS NULL AND current_copy IS NULL)
+ </query>
+ </report>
+
+-->
+
<!-- CIRC REPORTS -->
<report>
<name>circ_count</name>
<report_title>Copies by Location</report_title>
<tag>assets</tag>
<iteration>0</iteration>
- <heading>Copy Count.Library.Circ Library</heading>
+ <heading>Copy Count.Location.Circ Library</heading>
<query>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</query>
</report>
<tag>assets</tag>
<iteration>0</iteration>
<heading>Collision Count</heading>
- <query>SELECT COUNT(id) FROM m_asset_copy_legacy WHERE x_migrate = TRUE AND barcode LIKE 'x_%'</query>
+ <query>SELECT COUNT(id) FROM m_asset_copy_legacy WHERE x_migrate = TRUE AND barcode ~* '^x_'</query>
<note>Incumbent collisions are those where the migrated barcodes collide with existing barcodes in the database.</note>
</report>
<tag>assets</tag>
<iteration>1</iteration>
<heading>Collision Count</heading>
- <query>SELECT COUNT(id) FROM m_asset_copy WHERE barcode LIKE 'x_%'</query>
+ <query>SELECT COUNT(id) FROM m_asset_copy WHERE barcode ~* '^x_'</query>
<note>Incumbent collisions are those where the migrated barcodes collide with existing barcodes in the database.</note>
</report>
<tag>assets</tag>
<iteration>0</iteration>
<heading>Collision List</heading>
- <query>SELECT ac.barcode FROM m_asset_copy_legacy ac WHERE ac.barcode LIKE 'x_%' ORDER BY 1 LIMIT 20</query>
+ <query>SELECT ac.barcode FROM m_asset_copy_legacy ac WHERE ac.barcode ~* '^x_' AND ac.x_migrate = TRUE ORDER BY 1 LIMIT 20</query>
<note>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.</note>
</report>
<tag>assets</tag>
<iteration>1</iteration>
<heading>Collision List</heading>
- <query>SELECT ac.barcode FROM m_asset_copy ac WHERE ac.barcode LIKE 'x_%' ORDER BY 1 LIMIT 20</query>
+ <query>SELECT ac.barcode FROM m_asset_copy ac WHERE ac.barcode ~* '^x_' ORDER BY 1 LIMIT 20</query>
<note>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.</note>
</report>
<tag>assets</tag>
<iteration>0</iteration>
<heading>Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters</heading>
- <query>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</query>
+ <query>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</query>
</report>
<report>
<tag>assets</tag>
<iteration>1</iteration>
<heading>Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters</heading>
- <query>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</query>
+ <query>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</query>
</report>
<report>
</report>
<report>
+ <name>bibswovolumesanduri</name>
+ <report_title>Bibliographic Records Without Volumes And Recognized URI</report_title>
+ <tag>bibs</tag>
+ <iteration>0</iteration>
+ <heading>Service.Domain.Count</heading>
+ <query>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;;
+ </query>
+ <note>This list is built from known services and domains. If you have records for electronic resources that are not here please let us know.</note>
+ </report>
+
+ <report>
<name>bibswuri</name>
<report_title>Bibliographic Records With 856$9s</report_title>
<tag>bibs</tag>
<tag>actors</tag>
<iteration>0</iteration>
<heading>Count of Users.Active</heading>
- <query>SELECT COUNT(id), active::TEXT FROM m_actor_usr_legacy GROUP BY 2</query>
+ <query>SELECT COUNT(id), active::TEXT FROM m_actor_usr_legacy WHERE x_migrate = TRUE GROUP BY 2</query>
</report>
<report>
<tag>actors</tag>
<iteration>0</iteration>
<heading>Collision Count</heading>
- <query>SELECT COUNT(acard.id) FROM m_actor_card_legacy acard WHERE barcode LIKE 'x_%' AND x_migrate = TRUE</query>
+ <query>SELECT COUNT(acard.id) FROM m_actor_card_legacy acard WHERE barcode ~* '^x_' AND x_migrate = TRUE</query>
</report>
<report>
<tag>actors</tag>
<iteration>1</iteration>
<heading>Collision Count</heading>
- <query>SELECT COUNT(acard.id) FROM m_actor_card acard WHERE barcode LIKE 'x_%'</query>
+ <query>SELECT COUNT(acard.id) FROM m_actor_card acard WHERE barcode ~* '^x_'</query>
</report>
<report>
<tag>actors</tag>
<iteration>0</iteration>
<heading>Collision List</heading>
- <query>SELECT acard.barcode FROM m_actor_card_legacy acard WHERE acard.barcode LIKE 'x_' AND acard.x_migrate = TRUE ORDER BY 1 LIMIT 20</query>
+ <query>SELECT acard.barcode FROM m_actor_card_legacy acard WHERE acard.barcode ~* '^x_' AND acard.x_migrate = TRUE ORDER BY 1 LIMIT 20</query>
<note>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.</note>
</report>
<tag>actors</tag>
<iteration>1</iteration>
<heading>Collision List</heading>
- <query>SELECT acard.barcode FROM m_actor_card acard WHERE acard.barcode LIKE 'x_%' ORDER BY 1 LIMIT 20</query>
+ <query>SELECT acard.barcode FROM m_actor_card acard WHERE acard.barcode ~* '^x_%' ORDER BY 1 LIMIT 20</query>
<note>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.</note>
</report>
<iteration>0</iteration>
<report_title>Action Triggers Setup for Notices</report_title>
<heading>ID.Active.Owner.Name</heading>
- <query>SELECT ed.id, ed.active, aou.shortname, ed.name
- FROM m_action_trigger.event_definition ed
+ <query>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));
<report_title>Count of Notices Run with State</report_title>
<heading>Count of Notices.State.ID.Owner.Name</heading>
<query>SELECT COUNT(ate.id), ate.state, ed.id, aou.shortname, ed.name
- FROM m_action_trigger.event_definition ed
+ FROM action_trigger.event_definition ed
JOIN actor.org_unit aou ON aou.id = ed.owner
- JOIN m_action_trigger.event ate ON ate.event_def = ed.id
+ 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;