4 <drop>DROP FUNCTION IF EXISTS find_cmm(BIGINT)</drop>
6 CREATE OR REPLACE FUNCTION find_cmm(circ_id BIGINT)
7 RETURNS SETOF INTEGER[]
16 SELECT circ_lib FROM action.circulation WHERE id = circ_id INTO aou;
17 SELECT target_copy FROM action.circulation WHERE id = circ_id INTO ac;
18 SELECT usr FROM action.circulation WHERE id = circ_id INTO au;
20 FOR r IN SELECT buildrows FROM action.find_circ_matrix_matchpoint(aou,ac,au,FALSE)
30 <name>create_subfield_u</name>
31 <drop>DROP TABLE IF EXISTS subfield_u</drop>
32 <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>
35 <!-- sample reports entry
37 <name>name of report</name>
38 <iteration value='0' /> which version, the first version to run is 0, first backup is 1, then so on
39 <tag>money</tag> arbitrary tags, each should go in separate tag
40 <report_title>Migrated Billings</report_title> title used in the asciidoc output
41 <heading column='1'>Number of Billings.Migrated.Total</heading> period delimited
42 <query>SELECT COUNT(id),x_migrate::TEXT,SUM(amount) FROM m_money_billing_legacy GROUP BY 2;</query> query itself, will be replaced
43 <note>Arbitrary note that can be included in the entries.</note>
47 <!-- adding sample pie chart report
49 <name>current_holds_by_state</name>
51 <iteration>0</iteration>
52 <display>pie_chart</display>
53 <report_title>Holds by Current State (7 days)</report_title>
54 <heading>Captured.Fulfilled.Canceled.Targeted.Waiting</heading>
55 <chart_labels>Hold Type.Count</chart_labels>
57 (SELECT COUNT(*) FROM action.hold_request WHERE fulfillment_time IS NULL AND capture_time > NOW() - '7 days'::INTERVAL AND cancel_time IS NULL),
58 (SELECT COUNT(*) FROM action.hold_request WHERE fulfillment_time > NOW() - '7 days'::INTERVAL AND capture_time IS NOT NULL AND cancel_time IS NULL),
59 (SELECT COUNT(*) FROM action.hold_request WHERE fulfillment_time IS NULL AND cancel_time > NOW() - '7 days'::INTERVAL),
60 (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),
61 (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)
69 <name>circ_count</name>
71 <iteration>0</iteration>
72 <report_title>Migrated Circulations</report_title>
73 <heading>Circulation Status.Count of Circs</heading>
74 <query>SELECT 'Closed Circulations', COUNT(id) FROM m_action_circulation_legacy WHERE xact_finish IS NOT NULL AND x_migrate
75 UNION ALL SELECT 'Open Circulations', COUNT(id) FROM m_action_circulation_legacy WHERE xact_finish IS NULL AND x_migrate</query>
79 <name>circ_count</name>
81 <iteration>1</iteration>
82 <report_title>Open Circulations</report_title>
83 <heading>Circulation Status.Count of Circs</heading>
84 <query>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</query>
88 <name>circ_by_orgunit</name>
90 <iteration>0</iteration>
91 <report_title>Circulations by Org Unit</report_title>
92 <heading>Circulations Count.Org Unit</heading>
93 <query>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</query>
97 <name>circ_by_orgunit</name>
99 <iteration>1</iteration>
100 <report_title>Circulations by Org Unit</report_title>
101 <heading>Circulations Count.Org Unit</heading>
102 <query>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</query>
106 <name>circs_by_duration</name>
108 <iteration>0</iteration>
109 <report_title>Migrated Circulations by Duration, Fine and Max Fine</report_title>
110 <heading>Count of Circs.Duration.Fine.Max Fine</heading>
111 <query>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</query>
115 <name>circs_by_duration</name>
117 <iteration>1</iteration>
118 <report_title>Circulations by Duration, Fine and Max Fine</report_title>
119 <heading>Count of Circs.Duration.Fine.Max Fine</heading>
120 <query>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</query>
124 <name>circs_by_usrgroup</name>
126 <iteration>0</iteration>
127 <report_title>Circulations by Rules and Patron Group</report_title>
128 <heading>Count of Circs.Duration.Fine.Max Fine.User Group.Matchpoints</heading>
129 <query>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</query>
133 <name>circs_by_usrgroup</name>
135 <iteration>1</iteration>
136 <report_title>Circulations by Rules and Patron Group</report_title>
137 <heading>Count of Circs.Duration.Fine.Max Fine.User Group.Matchpoints</heading>
138 <query>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</query>
142 <name>circs_by_circmod</name>
144 <iteration>0</iteration>
145 <report_title>Circulations by Rules and Circulation Modifier</report_title>
146 <heading>Count of Circs.Duration.Fine.Max Fine.Circulation Modifier.Matchpoints</heading>
147 <query>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</query>
151 <name>circs_by_circmod</name>
153 <iteration>1</iteration>
154 <report_title>Circulations by Rules and Circulation Modifier</report_title>
155 <heading>Count of Circs.Duration.Fine.Max Fine.Circulation Modifier.Matchpoints</heading>
156 <query>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
157 GROUP BY 2, 3, 4, 5, 6 ORDER BY 2, 3, 4, 5, 6</query>
161 <name>circs_by_orgunit</name>
163 <iteration>0</iteration>
164 <report_title>Circulations by Rules and Org Unit</report_title>
165 <heading>Count of Circs.Duration.Fine.Max Fine.Library Branch</heading>
166 <query>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</query>
170 <name>circs_by_orgunit</name>
172 <iteration>1</iteration>
173 <report_title>Circulations by Rules and Org Unit</report_title>
174 <heading>Count of Circs.Duration.Fine.Max Fine.Library Branch</heading>
175 <query>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</query>
179 <name>non_cat_circs</name>
181 <iteration>0</iteration>
182 <report_title>Non-Cataloged Circulation</report_title>
183 <heading>Circulations Count.Migrated</heading>
184 <query>SELECT COUNT(id), x_migrate::TEXT FROM m_action_non_cataloged_circulation_legacy GROUP BY 2</query>
188 <name>non_cat_circs</name>
190 <iteration>1</iteration>
191 <report_title>Non-Cataloged Circulation</report_title>
192 <heading>Circulations Count</heading>
193 <query>SELECT COUNT(id) FROM m_action_non_cataloged_circulation</query>
197 <name>in_house</name>
199 <iteration>0</iteration>
200 <report_title>In House Use</report_title>
201 <heading>In House Use Records.Migrated</heading>
202 <query>SELECT COUNT(id), x_migrate::TEXT FROM m_action_in_house_use_legacy GROUP BY 2</query>
206 <name>in_house</name>
208 <iteration>1</iteration>
209 <report_title>In House Use</report_title>
210 <heading>In House Use Records</heading>
211 <query>SELECT COUNT(id) FROM m_action_in_house_use</query>
215 <name>circs_missing_rules</name>
217 <iteration>1</iteration>
218 <report_title>Circs Missing Rules</report_title>
219 <heading>Count.Field Missing</heading>
220 <query>SELECT COUNT(id), 'Duration Rule Value' FROM m_action_circulation WHERE duration IS NULL
221 UNION ALL SELECT COUNT(id), 'Recurring Fine Rule Value' FROM m_action_circulation WHERE recurring_fine IS NULL
222 UNION ALL SELECT COUNT(id), 'Max Fine Rule Value' FROM m_action_circulation WHERE max_fine IS NULL
223 UNION ALL SELECT COUNT(id), 'Duration Rule' FROM m_action_circulation WHERE duration_rule IS NULL
224 UNION ALL SELECT COUNT(id), 'Recurring Fine Rule' FROM m_action_circulation WHERE recurring_fine_rule IS NULL
225 UNION ALL SELECT COUNT(id), 'Max Fine Rule' FROM m_action_circulation WHERE max_fine_rule IS NULL
230 <name>circ_open_by_item_status</name>
232 <iteration>0</iteration>
233 <report_title>Status of Currently Circulating Items</report_title>
234 <heading>Count.Status</heading>
235 <query>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</query>
238 <!-- HOLDS REPORTS -->
243 <iteration>0</iteration>
244 <report_title>Migrated and Non-Migrated Holds</report_title>
245 <heading>Hold Type.Hold Count.Migrated</heading>
246 <query>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</query>
250 <name>holds_bytype</name>
252 <iteration>0</iteration>
253 <report_title>Migrated Holds By Type</report_title>
254 <heading>Hold Type.Hold Count</heading>
255 <query>SELECT hold_type as "Hold Type", COUNT(id) FROM m_action_hold_request_legacy WHERE x_migrate = TRUE GROUP BY 1</query>
259 <name>transit_open_by_item_status</name>
261 <iteration>0</iteration>
262 <report_title>Transit Copy Records and Status of Linked Items</report_title>
263 <heading>Count.Status</heading>
264 <query>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</query>
268 <name>transit_copies_by_status</name>
270 <iteration>0</iteration>
271 <report_title>Status of Items with Count of Open In Transits</report_title>
272 <heading>Count.Status.Count of Open Transits</heading>
273 <query>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</query>
277 <name>hold_copies_by_status</name>
279 <iteration>0</iteration>
280 <report_title>Captured Holds with Status of Items</report_title>
281 <heading>Count of Captured Hold.Status of Item</heading>
282 <query>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</query>
286 <name>hold_depth</name>
288 <iteration>0</iteration>
289 <report_title>Depth of Unfilled Holds</report_title>
290 <heading>Count.Depth</heading>
291 <query>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</query>
294 <!-- ASSET REPORTS -->
297 <name>m_asset_copy_count</name>
298 <report_title>Count of Copies by Library</report_title>
300 <iteration>0</iteration>
301 <heading>Copy Count.Library</heading>
302 <query>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</query>
306 <name>m_asset_copy_count_non_migrated</name>
307 <report_title>Non-Migrated Count of Copies by Library</report_title>
309 <iteration>0</iteration>
310 <heading>Copy Count.Library</heading>
311 <query>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</query>
315 <name>m_asset_copies_by_status</name>
316 <report_title>Copies by Status</report_title>
318 <iteration>0</iteration>
319 <heading>Copy Count.Status</heading>
320 <query>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</query>
324 <name>m_asset_circ_mod_copies_count</name>
325 <report_title>Copies by Circulation Modifier</report_title>
327 <iteration>0</iteration>
328 <heading>Copy Count.Circulation Modifier</heading>
329 <query>SELECT COUNT(ac.id), ac.circ_modifier FROM m_asset_copy_legacy ac WHERE ac.x_migrate = TRUE GROUP BY 2 ORDER BY 2</query>
333 <name>m_asset_copy_notes</name>
334 <report_title>Copy Notes</report_title>
336 <iteration>0</iteration>
337 <heading>Note Count.Public</heading>
338 <query>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</query>
342 <name>m_asset_copy_notes</name>
343 <report_title>Copy Notes</report_title>
345 <iteration>1</iteration>
346 <heading>Note Count.Public</heading>
347 <query>SELECT COUNT(acnote.id), acnote.pub::TEXT FROM m_asset_copy_note acnote GROUP BY 2 ORDER BY 2</query>
351 <name>m_asset_vols_by_lib</name>
352 <report_title>Volumes by Library</report_title>
354 <iteration>0</iteration>
355 <heading>Volume Count.Library</heading>
356 <query>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</query>
360 <name>m_asset_vols_by_lib</name>
361 <report_title>Volumes by Library</report_title>
363 <iteration>1</iteration>
364 <heading>Volume Count.Library</heading>
365 <query>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</query>
369 <name>m_asset_cops_by_loc_and_org</name>
370 <report_title>Copies by Location</report_title>
372 <iteration>0</iteration>
373 <heading>Copy Count.Location.Circ Library</heading>
374 <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>
378 <name>m_asset_cops_w_loc_one</name>
379 <report_title>Copies with a Location of Stacks</report_title>
381 <iteration>0</iteration>
382 <heading>Barcode</heading>
383 <query>SELECT barcode FROM m_asset_copy_legacy WHERE location = 1 AND x_migrate</query>
387 <name>m_asset_no_barcode</name>
388 <report_title>Items Without Barcodes</report_title>
390 <iteration>0</iteration>
391 <heading>Assigned Barcode</heading>
392 <query>SELECT barcode FROM m_asset_copy_legacy WHERE barcode ~* 'no_barocde' AND x_migrate</query>
396 <name>m_asset_barcode_patterns</name>
397 <report_title>Common Barcode Starting Patterns</report_title>
399 <iteration>0</iteration>
400 <heading>Count of Barcodes (greater than 10).Left 60% of Characters</heading>
401 <query>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</query>
405 <name>m_asset_barcode_collisions</name>
406 <report_title>Copy Barcode Collisions</report_title>
408 <iteration>0</iteration>
409 <heading>Collision Count</heading>
410 <query>SELECT COUNT(id) FROM m_asset_copy_legacy WHERE x_migrate = TRUE AND barcode ~* '^x_'</query>
411 <note>Incumbent collisions are those where the migrated barcodes collide with existing barcodes in the database.</note>
415 <name>m_asset_barcode_collisions</name>
416 <report_title>Copy Barcode Collisions</report_title>
418 <iteration>1</iteration>
419 <heading>Collision Count</heading>
420 <query>SELECT COUNT(id) FROM m_asset_copy WHERE barcode ~* '^x_'</query>
421 <note>Incumbent collisions are those where the migrated barcodes collide with existing barcodes in the database.</note>
425 <name>m_asset_barcode_collisions_shortlist</name>
426 <report_title>Copy Barcode Collisions (first 20)</report_title>
428 <iteration>0</iteration>
429 <heading>Collision List</heading>
430 <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>
431 <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>
435 <name>m_asset_barcode_collisions_shortlist</name>
436 <report_title>Copy Barcode Collisions (first 20)</report_title>
438 <iteration>1</iteration>
439 <heading>Collision List</heading>
440 <query>SELECT ac.barcode FROM m_asset_copy ac WHERE ac.barcode ~* '^x_' ORDER BY 1 LIMIT 20</query>
441 <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>
445 <name>m_asset_barcode_collision_patterns</name>
446 <report_title>Common Copy Barcode Collision Patterns</report_title>
448 <iteration>0</iteration>
449 <heading>Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters</heading>
450 <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>
454 <name>m_asset_barcode_collision_patterns</name>
455 <report_title>Common Copy Barcode Collision Patterns</report_title>
457 <iteration>1</iteration>
458 <heading>Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters</heading>
459 <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>
463 <name>m_asset_stat_cats</name>
464 <report_title>Copy Statistical Categories</report_title>
466 <iteration>0</iteration>
467 <heading>Stat Cat Count.Library.Statistical Category</heading>
468 <query>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</query>
472 <name>m_asset_stat_cats</name>
473 <report_title>Copy Statistical Categories</report_title>
475 <iteration>1</iteration>
476 <heading>Stat Cat Count.Library.Statistical Category</heading>
477 <query>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</query>
481 <name>m_asset_stat_cat_entries</name>
482 <report_title>Copy Stat Cat User Entries</report_title>
484 <iteration>0</iteration>
485 <heading>Copy Stat Count.Library.Statistical Category</heading>
486 <query>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</query>
490 <name>m_asset_stat_cat_entries</name>
491 <report_title>Copy Stat Cat User Entries</report_title>
493 <iteration>1</iteration>
494 <heading>Copy Stat Count.Library.Statistical Category</heading>
495 <query>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.
496 id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3</query>
500 <name>m_asset_copy_tags</name>
501 <report_title>Copy Tags</report_title>
503 <iteration>0</iteration>
504 <heading>Tag Count.Copy Tag Type.Copy Tag Label.Staff Note.Public</heading>
505 <query>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</query>
509 <name>m_asset_copy_alerts</name>
510 <report_title>Copy Alerts</report_title>
512 <iteration>0</iteration>
513 <heading>Alert Count.Alert Type</heading>
514 <query>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</query>
517 <!-- MONEY REPORTS -->
520 <name>m_money_billing_voided</name>
521 <report_title>Bills Voided And Not</report_title>
523 <iteration>0</iteration>
524 <heading>Count.Voided.Sum.Migrated</heading>
525 <query>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</query>
529 <name>m_money_billing_voided</name>
530 <report_title>Bills Voided And Not</report_title>
532 <iteration>1</iteration>
533 <heading>Count.Voided.Sum</heading>
534 <query>SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM m_money_billing a GROUP BY 2 ORDER BY 2, 3</query>
538 <name>m_money_billing_by_type</name>
539 <report_title>Bills by Type</report_title>
541 <iteration>0</iteration>
542 <heading>Count.Billing Type.Migrated</heading>
543 <query>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</query>
547 <name>m_money_billing_by_type</name>
548 <report_title>Bills by Type</report_title>
550 <iteration>1</iteration>
551 <heading>Count.Billing Type</heading>
552 <query>SELECT COUNT(a.id), a.billing_type FROM m_money_billing a GROUP BY 2 ORDER BY 2</query>
556 <name>m_money_cash_payment</name>
557 <report_title>Cash Payments</report_title>
559 <iteration>0</iteration>
560 <heading>Count.Voided.Sum.Migrated</heading>
561 <query>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</query>
565 <name>m_money_cash_payment</name>
566 <report_title>Cash Payments</report_title>
568 <iteration>1</iteration>
569 <heading>Count.Voided.Sum</heading>
570 <query>SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM m_money_cash_payment a GROUP BY 2 ORDER BY 2</query>
574 <name>m_money_check_payment</name>
575 <report_title>Check Payments</report_title>
577 <iteration>0</iteration>
578 <heading>Count.Voided.Sum.Migrated</heading>
579 <query>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</query>
583 <name>m_money_forgive_payment</name>
584 <report_title>Forgive Payments</report_title>
586 <iteration>0</iteration>
587 <heading>Count.Voided.Sum.Migrated</heading>
588 <query>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</query>
592 <name>m_money_forgive_payment</name>
593 <report_title>Forgive Payments</report_title>
595 <iteration>1</iteration>
596 <heading>Count.Voided.Sum</heading>
597 <query>SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM m_money_forgive_paymen a GROUP BY 2 ORDER BY 2</query>
601 <name>m_money_goods_payment</name>
602 <report_title>Goods Payments</report_title>
604 <iteration>0</iteration>
605 <heading>Count.Voided.Sum.Migrated</heading>
606 <query>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</query>
610 <name>m_money_work_payment</name>
611 <report_title>Work Payments</report_title>
613 <iteration>0</iteration>
614 <heading>Count.Voided.Sum.Migrated</heading>
615 <query>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</query>
619 <name>m_money_credit_card_payment</name>
620 <report_title>Credit Card Payments</report_title>
622 <iteration>0</iteration>
623 <heading>Count.Voided.Sum.Migrated</heading>
624 <query>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</query>
628 <name>m_money_credit_payment</name>
629 <report_title>Credit Payments</report_title>
631 <iteration>0</iteration>
632 <heading>Count.Voided.Sum.Migrated</heading>
633 <query>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</query>
637 <!-- BIBS REPORTS -->
640 <name>bibs_loaded</name>
641 <report_title>Loaded Bibliographic Records</report_title>
643 <iteration>0</iteration>
644 <heading>Count</heading>
645 <query>SELECT COUNT(bre.id) FROM m_biblio_record_entry_legacy bre WHERE id IN (SELECT id FROM biblio.record_entry) ORDER BY 1</query>
649 <name>bibs_loaded</name>
650 <report_title>Loaded Bibliographic Records</report_title>
652 <iteration>1</iteration>
653 <heading>Count</heading>
654 <query>SELECT COUNT(bre.id) FROM m_biblio_record_entry bre WHERE id IN (SELECT id FROM biblio.record_entry) ORDER BY 1</query>
658 <name>bibswovolumes</name>
659 <report_title>Bibliographic Records Without Volumes</report_title>
661 <iteration>0</iteration>
662 <heading>Count</heading>
663 <query>SELECT COUNT(id) FROM m_biblio_record_entry where id NOT IN (SELECT DISTINCT record FROM m_asset_call_number)</query>
664 <note>These records would not have been loaded but many may be deduplicated against incumbent records.</note>
668 <name>bibswovolumesanduri</name>
669 <report_title>Bibliographic Records Without Volumes And Recognized URI</report_title>
671 <iteration>0</iteration>
672 <heading>Service.Domain.Count</heading>
673 <query>SELECT '3M','ebook.3m.com', COUNT(*) FROM subfield_u WHERE value ~* 'ebook.3m.com' GROUP BY 1, 2 UNION ALL
674 SELECT 'Axis 360','axis360.baker-taylor.com', COUNT(*) FROM subfield_u WHERE value ~* 'axis360.baker-taylor.com' GROUP BY 1, 2 UNION ALL
675 SELECT 'Book Flix','bookflix.digital.scholastic.com', COUNT(*) FROM subfield_u WHERE value ~* 'bookflix.digital.scholastic.com' GROUP BY 1, 2 UNION ALL
676 SELECT 'Book Flix','bkflix.grolier.com', COUNT(*) FROM subfield_u WHERE value ~* 'bkflix.grolier.com' GROUP BY 1, 2 UNION ALL
677 SELECT 'Comics Plus','library.comicsplusapp.com', COUNT(*) FROM subfield_u WHERE value ~* 'library.comicsplusapp.com' GROUP BY 1, 2 UNION ALL
678 SELECT 'Ebrary','site.ebrary.com', COUNT(*) FROM subfield_u WHERE value ~* 'site.ebrary.com' GROUP BY 1, 2 UNION ALL
679 SELECT 'Freading','freading.com', COUNT(*) FROM subfield_u WHERE value ~* 'freading.com' GROUP BY 1, 2 UNION ALL
680 SELECT 'Hoopla','hoopladigital.com', COUNT(*) FROM subfield_u WHERE value ~* 'hoopladigital.com' GROUP BY 1, 2 UNION ALL
681 SELECT 'Infobase','avod.infobase.com', COUNT(*) FROM subfield_u WHERE value ~* 'avod.infobase.com' GROUP BY 1, 2 UNION ALL
682 SELECT 'Learning Express','learningexpresslibrary.com', COUNT(*) FROM subfield_u WHERE value ~* 'learningexpresslibrary.com' GROUP BY 1, 2 UNION ALL
683 SELECT 'Missouri Overdrive','molib2go.org', COUNT(*) FROM subfield_u WHERE value ~* 'molib2go.org' GROUP BY 1, 2 UNION ALL
684 SELECT 'netLibrary','netLibrary.com', COUNT(*) FROM subfield_u WHERE value ~* 'netLibrary.com' GROUP BY 1, 2 UNION ALL
685 SELECT 'OneClickDigital','oneclickdigital.com', COUNT(*) FROM subfield_u WHERE value ~* 'oneclickdigital.com' GROUP BY 1, 2 UNION ALL
686 SELECT 'Overdrive','overdrive.com', COUNT(*) FROM subfield_u WHERE value ~* 'overdrive.com' GROUP BY 1, 2 UNION ALL
687 SELECT 'ProQuest','ebookcentral.proquest.com', COUNT(*) FROM subfield_u WHERE value ~* 'ebookcentral.proquest.com' GROUP BY 1, 2 UNION ALL
688 SELECT 'RB Digital','rbdigital.com', COUNT(*) FROM subfield_u WHERE value ~* 'rbdigital.com' GROUP BY 1, 2 UNION ALL
689 SELECT 'U.S. Government Sites','.gov', COUNT(*) FROM subfield_u WHERE value ~* '\.gov' GROUP BY 1,2;;
691 <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>
695 <name>bibswuri</name>
696 <report_title>Bibliographic Records With 856$9s</report_title>
698 <iteration>0</iteration>
699 <heading>Count</heading>
700 <query>SELECT COUNT(id) FROM m_biblio_record_entry where id IN (SELECT record FROM asset.call_number WHERE label ~* '##URI##')</query>
705 <report_title>Bibliographic Records with Adjusted Fixed Fields</report_title>
707 <iteration>0</iteration>
708 <heading>Count.Original Search Format.New Search Format</heading>
709 <query>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</query>
713 <name>bibs_notes</name>
714 <report_title>Bib Record Notes</report_title>
716 <iteration>0</iteration>
717 <heading>Count.Migrated</heading>
718 <query>SELECT COUNT(b.id), b.x_migrate::TEXT FROM m_biblio_record_note_legacy b GROUP BY 2</query>
722 <name>bibs_notes</name>
723 <report_title>Bib Record Notes</report_title>
725 <iteration>1</iteration>
726 <heading>Count</heading>
727 <query>SELECT COUNT(b.id) FROM m_biblio_record_note b</query>
731 <name>bibs_peers</name>
732 <report_title>Peer Bib Copies</report_title>
734 <iteration>0</iteration>
735 <heading>Count.Migrated</heading>
736 <query>SELECT COUNT(b.id), b.x_migrate::TEXT FROM m_biblio_peer_bib_copy_map_legacy b GROUP BY 2</query>
740 <name>bibs_peers</name>
741 <report_title>Peer Bib Copies</report_title>
743 <iteration>1</iteration>
744 <heading>Count</heading>
745 <query>SELECT COUNT(b.id) FROM m_biblio_peer_bib_copy_map b</query>
749 <name>bibs_parts</name>
750 <report_title>Monograph Parts</report_title>
752 <iteration>0</iteration>
753 <heading>Count.Migrated</heading>
754 <query>SELECT COUNT(b.id), b.x_migrate::TEXT FROM m_biblio_monograph_part_legacy b GROUP BY 2</query>
758 <name>bibs_parts</name>
759 <report_title>Monograph Parts</report_title>
761 <iteration>1</iteration>
762 <heading>Count</heading>
763 <query>SELECT COUNT(b.id) FROM m_biblio_monograph_part b</query>
767 <name>bib_merges</name>
768 <report_title>Bibliographic Merge Count</report_title>
770 <iteration>0</iteration>
771 <heading>Records Merged.Incumbent Records Merged Into</heading>
772 <query>SELECT SUM(array_length(records,1)), COUNT(*) FROM groups</query>
775 <!-- ACTORS REPORTS -->
778 <name>usrsbyorg</name>
779 <report_title>Migrated Patrons by Home Org</report_title>
781 <iteration>0</iteration>
782 <heading>Count.Library</heading>
783 <query>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</query>
787 <name>nonmigusrsbyorg</name>
788 <report_title>Non-Migrated Patrons by Home Org</report_title>
790 <iteration>0</iteration>
791 <heading>Count.Library</heading>
792 <query>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</query>
796 <name>usrsbypgt</name>
797 <report_title>Migrated Patrons by Permission Group</report_title>
799 <iteration>0</iteration>
800 <heading>Count.Permission Group</heading>
801 <query>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</query>
805 <name>active_usrs</name>
806 <report_title>Patron by Active Status</report_title>
808 <iteration>0</iteration>
809 <heading>Count of Users.Active</heading>
810 <query>SELECT COUNT(id), active::TEXT FROM m_actor_usr_legacy WHERE x_migrate = TRUE GROUP BY 2</query>
814 <name>active_usrs</name>
815 <report_title>Patron Cards</report_title>
817 <iteration>1</iteration>
818 <heading>Count of Users</heading>
819 <query>SELECT COUNT(id) FROM m_actor_usr</query>
823 <name>active_usr_barcodes</name>
824 <report_title>Patron Barcodes by Active Status</report_title>
826 <iteration>0</iteration>
827 <heading>Count of Barcodes.Active.Migrated</heading>
828 <query>SELECT COUNT(id), active::TEXT, x_migrate::TEXT FROM m_actor_card_legacy GROUP BY 2, 3</query>
832 <name>active_usr_barcodes</name>
833 <report_title>Patron Barcodes by Active Status</report_title>
835 <iteration>1</iteration>
836 <heading>Count of Barcodes.Active</heading>
837 <query>SELECT COUNT(id), active::TEXT FROM m_actor_card GROUP BY 2</query>
841 <name>usr_barcode_patterns</name>
842 <report_title>Common Barcode Starting Patterns</report_title>
844 <iteration>0</iteration>
845 <heading>Count of Barcodes (greater than 10).Left 60% of Characters</heading>
846 <query>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</query>
850 <name>usr_barcode_patterns</name>
851 <report_title>Common Barcode Starting Patterns</report_title>
853 <iteration>1</iteration>
854 <heading>Count of Barcodes (greater than 10).Left 60% of Characters</heading>
855 <query>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</query>
859 <name>usr_barcode_collisions</name>
860 <report_title>Patron Barcode Collisions</report_title>
862 <iteration>0</iteration>
863 <heading>Collision Count</heading>
864 <query>SELECT COUNT(acard.id) FROM m_actor_card_legacy acard WHERE barcode ~* '^x_' AND x_migrate = TRUE</query>
868 <name>usr_barcode_collisions</name>
869 <report_title>Patron Barcode Collisions</report_title>
871 <iteration>1</iteration>
872 <heading>Collision Count</heading>
873 <query>SELECT COUNT(acard.id) FROM m_actor_card acard WHERE barcode ~* '^x_'</query>
877 <name>usr_barcode_collision_shortlist</name>
878 <report_title>Patron Barcode Collisions (first 20)</report_title>
880 <iteration>0</iteration>
881 <heading>Collision List</heading>
882 <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>
883 <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>
887 <name>usr_barcode_collision_shortlist</name>
888 <report_title>Patron Barcode Collisions (first 20)</report_title>
890 <iteration>1</iteration>
891 <heading>Collision List</heading>
892 <query>SELECT acard.barcode FROM m_actor_card acard WHERE acard.barcode ~* '^x_%' ORDER BY 1 LIMIT 20</query>
893 <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>
897 <name>usr_barcode_collision_patterns</name>
898 <report_title>Common Patron Barcode Collision Patterns</report_title> a.x_migrate
900 <iteration>0</iteration>
901 <heading>Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters</heading>
902 <query>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</query>
906 <name>usr_barcode_collision_patterns</name>
907 <report_title>Common Patron Barcode Collision Patterns</report_title> a.x_migrate
909 <iteration>1</iteration>
910 <heading>Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters</heading>
911 <query>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</query>
915 <name>usr_addressses_status</name>
916 <report_title>Patron Addresses</report_title>
918 <iteration>0</iteration>
919 <heading>Count</heading>
920 <query>SELECT COUNT(aua.id) FROM m_actor_usr_address_legacy aua WHERE aua.x_migrate = TRUE</query>
924 <name>usr_addressses_status</name>
925 <report_title>Patron Addresses</report_title>
927 <iteration>1</iteration>
928 <heading>Count</heading>
929 <query>SELECT COUNT(aua.id) FROM m_actor_usr_address aua</query>
933 <name>usr_addresses_pending</name>
934 <report_title>Patron Addresses by Pending Status</report_title>
936 <iteration>0</iteration>
937 <heading>Count of Addresses.Pending</heading>
938 <query>SELECT COUNT(aua.id), pending::TEXT FROM m_actor_usr_address_legacy aua WHERE aua.x_migrate = TRUE GROUP BY 2</query>
942 <name>usr_addresses_pending</name>
943 <report_title>Patron Addresses by Pending Status</report_title>
945 <iteration>1</iteration>
946 <heading>Count of Addresses.Pending</heading>
947 <query>SELECT COUNT(aua.id), pending::TEXT FROM m_actor_usr_address aua GROUP BY 2</query>
951 <name>usr_messages</name>
952 <report_title>Patron Messages</report_title>
954 <iteration>0</iteration>
955 <heading>Count.Deleted.Migrated</heading>
956 <query>SELECT COUNT(aum.id), deleted::TEXT, x_migrate::TEXT FROM m_actor_usr_message_legacy aum GROUP BY 2, 3</query>
960 <name>usr_messages</name>
961 <report_title>Patron Messages</report_title>
963 <iteration>1</iteration>
964 <heading>Count.Deleted</heading>
965 <query>SELECT COUNT(aum.id), deleted::TEXT FROM m_actor_usr_message_legacy aum GROUP BY 2</query>
969 <name>usr_notes</name>
970 <report_title>Patron Notes</report_title>
972 <iteration>0</iteration>
973 <heading>Count.Public.Migrated</heading>
974 <query>SELECT COUNT(aun.id), pub::TEXT, x_migrate::TEXT FROM m_actor_usr_note_legacy aun GROUP BY 2, 3</query>
978 <name>usr_notes</name>
979 <report_title>Patron Notes</report_title>
981 <iteration>1</iteration>
982 <heading>Count.Public</heading>
983 <query>SELECT COUNT(aun.id), pub::TEXT FROM m_actor_usr_note aun GROUP BY 2</query>
987 <name>usr_stat_cats</name>
988 <report_title>Patron Statistical Categories</report_title>
990 <iteration>0</iteration>
991 <heading>Stat Cat Count.Library.Statistical Category.Migrated</heading>
992 <query>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</query>
996 <name>usr_stat_cats</name>
997 <report_title>Patron Statistical Categories</report_title>
999 <iteration>1</iteration>
1000 <heading>Stat Cat Count.Library.Statistical Category</heading>
1001 <query>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</query>
1005 <name>usr_stat_cat_entries</name>
1006 <report_title>Patron Stat Cat User Entries</report_title>
1008 <iteration>0</iteration>
1009 <heading>Patron Stat Count.Library.Statistical Category.Migrated</heading>
1010 <query>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</query>
1014 <name>usr_stat_cat_entries</name>
1015 <report_title>Patron Stat Cat User Entries</report_title>
1017 <iteration>1</iteration>
1018 <heading>Patron Stat Count.Library.Statistical Category</heading>
1019 <query>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</query>
1023 <name>usr_thresholds</name>
1024 <report_title>Patron Thresholds</report_title>
1026 <iteration>0</iteration>
1027 <heading>Pateron Group.Org Unit.Penalty.Threshold</heading>
1028 <query>SELECT pgt.name, aou.shortname, sp.name, p.threshold FROM permission_grp_penalty_threshold p
1029 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
1030 ORDER BY 2, 1, 3</query>
1034 <name>usr_settings</name>
1035 <report_title>Patron Settings</report_title>
1037 <iteration>0</iteration>
1038 <heading>Count.Setting.Value</heading>
1039 <query>SELECT COUNT(*), name, 'User''s Phone' FROM m_actor_usr_setting WHERE name IN ('opac.default_phone') GROUP BY 2, 3
1040 UNION ALL SELECT COUNT(*), name, value FROM m_actor_usr_setting WHERE name IN ('opac.hold_notify') GROUP BY 2, 3
1041 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
1042 WHERE a.name IN ('opac.default_pickup_location','opac.default_search_location') GROUP BY 2, 3 ORDER BY 2, 3;</query>
1045 <!-- ACQUISITIONS REPORTS -->
1047 <name>fund_count</name>
1048 <iteration>0</iteration>
1050 <report_title>Migrated Funds</report_title>
1051 <heading>Number of Funds.Migrated</heading>
1052 <query>SELECT COUNT(id), x_migrate::TEXT FROM m_acq_fund_legacy GROUP BY 2;</query>
1056 <name>fund_count</name>
1057 <iteration>1</iteration>
1059 <report_title>Migrated Funds</report_title>
1060 <heading>Number of Funds</heading>
1061 <query>SELECT COUNT(id) FROM m_acq_fund;</query>
1065 <name>invoice_count</name>
1066 <iteration>0</iteration>
1068 <report_title>Migrated Invoices</report_title>
1069 <heading>Number of Invoices.Migrated</heading>
1070 <query>SELECT COUNT(id), x_migrate::TEXT FROM m_acq_invoice_legacy GROUP BY 2;</query>
1074 <name>invoice_count</name>
1075 <iteration>1</iteration>
1077 <report_title>Migrated Invoices</report_title>
1078 <heading>Number of Funds</heading>
1079 <query>SELECT COUNT(id) FROM m_acq_invoice;</query>
1082 <!-- SERIALS REPORTS -->
1084 <name>serials_mfhd_count</name>
1086 <iteration>0</iteration>
1087 <report_title>Migrated Serial MFHDs</report_title>
1088 <heading>Number of MFHDs</heading>
1089 <query>SELECT COUNT(id) FROM m_serial_record_entry</query>
1092 <!-- DEDUPE REPORTS -->
1095 <name>dedupe_explain</name>
1097 <file>dedupe_process.asciidoc</file>
1101 <name>dedupe_bib_groups</name>
1103 <iteration>0</iteration>
1104 <report_title>Scoring and Bib Record Groups</report_title>
1105 <heading>Count.Bib Record Groups</heading>
1106 <query>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)
1107 UNION ALL SELECT (COUNT(DISTINCT incoming_bib)), 'Incoming Bibs With Matches Found' FROM bib_matches
1108 UNION ALL SELECT (COUNT(bre.id) - (SELECT COUNT(DISTINCT incoming_bib) FROM bib_matches)), 'Incoming Bibs With No Match'
1109 FROM biblio.record_entry bre WHERE bre.deleted IS FALSE AND bre.id IN (SELECT eg::BIGINT FROM bib_id_map)
1110 UNION ALL SELECT COUNT(DISTINCT incoming_bib), 'Incoming Bibs Being Merged into Incumbent' FROM bib_matches WHERE incumbent_bib_score >= incoming_bib_score
1111 UNION ALL SELECT COUNT(id), 'Incumbent Bibs With Higher Scores to Incoming' FROM bib_matches WHERE incumbent_bib_score > incoming_bib_score
1112 UNION ALL SELECT COUNT(id), 'Incumbent Bibs With Equal Scores to Incoming' FROM bib_matches WHERE incumbent_bib_score = incoming_bib_score
1113 UNION ALL SELECT COUNT(id), 'Incumbent Bibs With Lower Scores to Incoming' FROM bib_matches WHERE incumbent_bib_score < incoming_bib_score
1118 <name>dedupe_format_count</name>
1120 <iteration>0</iteration>
1121 <report_title>Count of Items Matching By Format</report_title>
1122 <heading>Count.Format(s)</heading>
1123 <query>SELECT COUNT(id), search_formats FROM bib_matches GROUP BY 2 ORDER BY 2;</query>
1127 <name>dedupe_score_ranges</name>
1129 <iteration>0</iteration>
1130 <report_title>Count of Items Matching By Format</report_title>
1131 <heading>Lowest Record Score.Largest Record Score.Record Set</heading>
1132 <query>SELECT MIN(incumbent_bib_score), MAX(incumbent_bib_score), 'Incumbent Records' FROM bib_matches
1133 UNION ALL SELECT MIN(incoming_bib_score), MAX(incoming_bib_score), 'Incoming Records' FROM bib_matches ;
1139 <name>dedupe_sample_set</name>
1141 <iteration>0</iteration>
1142 <report_title>Sample of 20 Matching Dedupe Record Sets</report_title>
1143 <heading>Bib Being Merged Into.Bib Being Merged</heading>
1144 <query>SELECT incumbent_bib, incoming_bib FROM bib_matches WHERE incumbent_bib_score >= incoming_bib_score LIMIT 20 ;
1148 <!-- NOTICES REPORTS -->
1151 <name>notices_overview</name>
1153 <iteration>0</iteration>
1154 <report_title>Action Triggers Setup for Notices</report_title>
1155 <heading>ID.Active.Owner.Name</heading>
1156 <query>SELECT ed.id, ed.active::TEXT, aou.shortname, ed.name
1157 FROM action_trigger.event_definition ed
1158 JOIN actor.org_unit aou ON aou.id = ed.owner
1159 WHERE ed.owner IN (SELECT DISTINCT home_ou FROM m_actor_usr)
1160 OR ed.owner IN (SELECT DISTINCT parent_ou FROM actor.org_unit WHERE id in (SELECT DISTINCT home_ou FROM m_actor_usr));
1165 <name>notices_count</name>
1167 <iteration>0</iteration>
1168 <report_title>Count of Notices Run with State</report_title>
1169 <heading>Count of Notices.State.ID.Owner.Name</heading>
1170 <query>SELECT COUNT(ate.id), ate.state, ed.id, aou.shortname, ed.name
1171 FROM action_trigger.event_definition ed
1172 JOIN actor.org_unit aou ON aou.id = ed.owner
1173 JOIN action_trigger.event ate ON ate.event_def = ed.id
1174 WHERE ed.owner IN (SELECT DISTINCT home_ou FROM m_actor_usr)
1175 OR ed.owner IN (SELECT DISTINCT parent_ou FROM actor.org_unit WHERE id in (SELECT DISTINCT home_ou FROM m_actor_usr))