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 <!-- sample reports entry
32 <name>name of report</name>
33 <iteration value='0' /> which version, the first version to run is 0, first backup is 1, then so on
34 <tag>money</tag> arbitrary tags, each should go in separate tag
35 <report_title>Migrated Billings</report_title> title used in the asciidoc output
36 <heading column='1'>Number of Billings.Migrated.Total</heading> period delimited
37 <query>SELECT COUNT(id),x_migrate::TEXT,SUM(amount) FROM money_billing_legacy GROUP BY 2;</query> query itself, will be replaced
38 <note>Arbitrary note that can be included in the entries.</note>
44 <name>circ_count</name>
46 <iteration>0</iteration>
47 <report_title>Migrated Circulations</report_title>
48 <heading>Circulation Status.Count of Circs</heading>
49 <query>SELECT 'Closed Circulations', COUNT(id) FROM action_circulation_legacy WHERE xact_finish IS NOT NULL AND x_migrate
50 UNION ALL SELECT 'Open Circulations', COUNT(id) FROM action_circulation_legacy WHERE xact_finish IS NULL AND x_migrate</query>
54 <name>circ_count_unmigrated</name>
56 <iteration>0</iteration>
57 <report_title>Open Un-migrated Circulations</report_title>
58 <heading>Circulation Status.Count of Circs</heading>
59 <query>SELECT 'No Matching User', COUNT(id) FROM action_circulation_legacy WHERE xact_finish IS NULL AND x_migrate = FALSE AND usr IS NULL
60 UNION ALL SELECT 'No Matching Item', COUNT(id) FROM action_circulation_legacy WHERE xact_finish IS NULL AND x_migrate = FALSE AND target_copy IS NULL </query>
64 <name>circ_count</name>
66 <iteration>1</iteration>
67 <report_title>Open Circulations</report_title>
68 <heading>Circulation Status.Count of Circs</heading>
69 <query>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</query>
71 <!-- intent is to add a report by circulated and item status
73 <name>circ_count_by_item_status</name>
75 <iteration>0</iteration>
76 <report_title>Open Circulations</report_title>
77 <heading>Circulation Status.Migrated.Count of Circs</heading>
78 <query>SELECT 'Closed Circulations', x_migrate::TEXT, COUNT(id) FROM action_circulation_legacy WHERE xact_finish IS NOT NULL GROUP BY 2 UNION ALL SELECT 'Open Circulations', x_migrate::TEXT, COUNT(id) FROM action_circulation_legacy WHERE xact_finish IS NULL GROUP BY 2</query>
82 <name>circ_by_orgunit</name>
84 <iteration>0</iteration>
85 <report_title>Circulations by Org Unit</report_title>
86 <heading>Circulations Count.Migrated.Org Unit</heading>
87 <query>SELECT COUNT(acirc.id), acirc.x_migrate::TEXT, aou.name FROM action_circulation_legacy acirc JOIN actor.org_unit aou ON aou.id = acirc.circ_lib WHERE acirc.xact_finish IS NULL GROUP BY 2, 3</query>
91 <name>circ_by_orgunit</name>
93 <iteration>1</iteration>
94 <report_title>Circulations by Org Unit</report_title>
95 <heading>Circulations Count.Org Unit</heading>
96 <query>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</query>
100 <name>circs_by_duration</name>
102 <iteration>0</iteration>
103 <report_title>Migrated Circulations by Duration, Fine and Max Fine</report_title>
104 <heading>Count of Circs.Duration.Fine.Max Fine.Migrated</heading>
105 <query>SELECT COUNT(id), duration_rule, recurring_fine_rule, max_fine_rule, x_migrate::TEXT FROM action_circulation_legacy WHERE x_migrate = TRUE GROUP BY 2, 3, 4, 5</query>
109 <name>circs_by_duration</name>
111 <iteration>1</iteration>
112 <report_title>Circulations by Duration, Fine and Max Fine</report_title>
113 <heading>Count of Circs.Duration.Fine.Max Fine</heading>
114 <query>SELECT COUNT(id), duration_rule, recurring_fine_rule, max_fine_rule FROM action_circulation GROUP BY 2, 3, 4 ORDER BY 2, 3, 4</query>
118 <name>circs_by_usrgroup</name>
120 <iteration>0</iteration>
121 <report_title>Circulations by Rules and Patron Group</report_title>
122 <heading>Count of Circs.Duration.Fine.Max Fine.User Group.Matchpoints</heading>
123 <query>SELECT COUNT(acirc.id), acirc.duration_rule, acirc.recurring_fine_rule, acirc.max_fine_rule, pgt.name, x.buildrows FROM 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 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>
127 <name>circs_by_usrgroup</name>
129 <iteration>1</iteration>
130 <report_title>Circulations by Rules and Patron Group</report_title>
131 <heading>Count of Circs.Duration.Fine.Max Fine.User Group.Matchpoints</heading>
132 <query>SELECT COUNT(acirc.id), acirc.duration_rule, acirc.recurring_fine_rule, acirc.max_fine_rule, pgt.name, x.buildrows FROM 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 action_circulation acirc) x ON x.id = acirc.id GROUP BY 2, 3, 4, 5, 6 ORDER BY 2, 3, 4, 5, 6</query>
136 <name>circs_by_circmod</name>
138 <iteration>0</iteration>
139 <report_title>Circulations by Rules and Circulation Modifier</report_title>
140 <heading>Count of Circs.Duration.Fine.Max Fine.Circulation Modifier.Matchpoints</heading>
141 <query>SELECT COUNT(acirc.id), acirc.duration_rule, acirc.recurring_fine_rule, acirc.max_fine_rule, ac.circ_modifier, x.buildrows FROM 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 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>
145 <name>circs_by_circmod</name>
147 <iteration>1</iteration>
148 <report_title>Circulations by Rules and Circulation Modifier</report_title>
149 <heading>Count of Circs.Duration.Fine.Max Fine.Circulation Modifier.Matchpoints</heading>
150 <query>SELECT COUNT(acirc.id), acirc.duration_rule, acirc.recurring_fine_rule, acirc.max_fine_rule, ac.circ_modifier, x.buildrows FROM 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 action_circulation acirc) x ON x.id = acirc.id
151 GROUP BY 2, 3, 4, 5, 6 ORDER BY 2, 3, 4, 5, 6</query>
155 <name>circs_by_orgunit</name>
157 <iteration>0</iteration>
158 <report_title>Circulations by Rules and Org Unit</report_title>
159 <heading>Count of Circs.Duration.Fine.Max Fine.Library Branch</heading>
160 <query>SELECT COUNT(acirc.id), acirc.duration_rule, acirc.recurring_fine_rule, acirc.max_fine_rule, aou.name FROM 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>
164 <name>circs_by_orgunit</name>
166 <iteration>1</iteration>
167 <report_title>Circulations by Rules and Org Unit</report_title>
168 <heading>Count of Circs.Duration.Fine.Max Fine.Library Branch</heading>
169 <query>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</query>
173 <name>non_cat_circs</name>
175 <iteration>0</iteration>
176 <report_title>Non-Cataloged Circulation</report_title>
177 <heading>Circulations Count.Migrated</heading>
178 <query>SELECT COUNT(id), x_migrate::TEXT FROM action_non_cataloged_circulation_legacy GROUP BY 2</query>
182 <name>non_cat_circs</name>
184 <iteration>1</iteration>
185 <report_title>Non-Cataloged Circulation</report_title>
186 <heading>Circulations Count</heading>
187 <query>SELECT COUNT(id) FROM action_non_cataloged_circulation</query>
191 <name>in_house</name>
193 <iteration>0</iteration>
194 <report_title>In House Use</report_title>
195 <heading>In House Use Records.Migrated</heading>
196 <query>SELECT COUNT(id), x_migrate::TEXT FROM action_in_house_use_legacy GROUP BY 2</query>
200 <name>in_house</name>
202 <iteration>1</iteration>
203 <report_title>In House Use</report_title>
204 <heading>In House Use Records</heading>
205 <query>SELECT COUNT(id) FROM action_in_house_use</query>
209 <name>circs_missing_rules</name>
211 <iteration>1</iteration>
212 <report_title>Circs Missing Rules</report_title>
213 <heading>Count.Field Missing</heading>
214 <query>SELECT COUNT(id), 'Duration Rule Value' FROM action_circulation WHERE duration IS NULL
215 UNION ALL SELECT COUNT(id), 'Recurring Fine Rule Value' FROM action_circulation WHERE recurring_fine IS NULL
216 UNION ALL SELECT COUNT(id), 'Max Fine Rule Value' FROM action_circulation WHERE max_fine IS NULL
217 UNION ALL SELECT COUNT(id), 'Duration Rule' FROM action_circulation WHERE duration_rule IS NULL
218 UNION ALL SELECT COUNT(id), 'Recurring Fine Rule' FROM action_circulation WHERE recurring_fine_rule IS NULL
219 UNION ALL SELECT COUNT(id), 'Max Fine Rule' FROM action_circulation WHERE max_fine_rule IS NULL
224 <name>circ_open_by_item_status</name>
226 <iteration>0</iteration>
227 <report_title>Open Circulation and Status of Linked Items</report_title>
228 <heading>Count.Status</heading>
229 <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 action_circulation) GROUP BY 2 ORDER BY 2</query>
232 <!-- HOLDS REPORTS -->
237 <iteration>0</iteration>
238 <report_title>Migrated Holds</report_title>
239 <heading>Hold Type.Hold Count.Migrated</heading>
240 <query>SELECT 'Closed Holds', COUNT(id), x_migrate::TEXT FROM 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 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>
244 <name>holds_bytype</name>
246 <iteration>0</iteration>
247 <report_title>Migrated Holds By Type</report_title>
248 <heading>Hold Type.Hold Count.Migrated</heading>
249 <query>SELECT hold_type as "Hold Type", COUNT(id), x_migrate::TEXT FROM action_hold_request_legacy GROUP BY 1, 3</query>
253 <name>transit_open_by_item_status</name>
255 <iteration>0</iteration>
256 <report_title>Transit Copy Records and Status of Linked Items</report_title>
257 <heading>Count.Status</heading>
258 <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 action_transit_copy) AND atc.dest_recv_time IS NULL GROUP BY 2 ORDER BY 2</query>
262 <name>transit_copies_by_status</name>
264 <iteration>0</iteration>
265 <report_title>Status of Items with Count of Open In Transits</report_title>
266 <heading>Count.Status.Count of Open Transits</heading>
267 <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 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</query>
271 <name>hold_copies_by_status</name>
273 <iteration>0</iteration>
274 <report_title>Captured Holds with Status of Items</report_title>
275 <heading>Count of Captured Hold.Status of Item</heading>
276 <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 action_hold_request) GROUP BY 2 ORDER By 2</query>
280 <name>hold_depth</name>
282 <iteration>0</iteration>
283 <report_title>Depth of Unfilled Holds</report_title>
284 <heading>Count.Depth</heading>
285 <query>SELECT COUNT(ahr.id), ahr.selection_depth FROM action.hold_request ahr WHERE ahr.id IN (SELECT id FROM 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>
288 <!-- finding title matched holds where there are duplicates, this will table match for legacy but column won't be present for all -->
290 <name>duplicate_title_matching_holds</name>
292 <iteration>0</iteration>
293 <report_title>Holds by Title Matchs</report_title>
294 <heading>Count.Number of Matches</heading>
295 <query>SELECT COUNT(*), x_title_matches FROM action_hold_request_legacy WHERE TO_DATE(l_expire_date,'MM/DD/YYYY') > NOW() GROUP BY 2 ORDER BY 2</query>
299 <name>duplicate_title_matching_holds_w_zero</name>
301 <iteration>0</iteration>
302 <report_title>Sample of Holds with No Matches</report_title>
303 <heading>Borrower Barcode.Title.Request Date</heading>
304 <query>SELECT l_borrower_id, l_title, l_request_date FROM action_hold_request_legacy WHERE TO_DATE(l_expire_date,'MM/DD/YYYY') > NOW() AND x_title_matches = 0 AND x_migrate LIMIT 20</query>
308 <name>duplicate_title_matching_holds_w_greater_than_1</name>
310 <iteration>0</iteration>
311 <report_title>Sample of Holds with More Than One Title Match</report_title>
312 <heading>Borrower Barcode.Title.Request Date</heading>
313 <query>SELECT l_borrower_id, l_title, l_request_date FROM action_hold_request_legacy WHERE TO_DATE(l_expire_date,'MM/DD/YYYY') > NOW() AND x_title_matches > 1 AND x_migrate LIMIT 20</query>
316 <!-- ASSET REPORTS -->
319 <name>asset_copy_count</name>
320 <report_title>Count of Copies by Library</report_title>
322 <iteration>0</iteration>
323 <heading>Copy Count.Library.Migrated</heading>
324 <query>SELECT COUNT(ac.id), aou.name, ac.x_migrate::TEXT FROM asset_copy_legacy ac JOIN actor.org_unit aou ON aou.id = ac.circ_lib GROUP BY 2, 3 ORDER BY 2, 3</query>
328 <name>asset_deleted_copies</name>
329 <report_title>Deleted Copies</report_title>
331 <iteration>0</iteration>
332 <heading>Copy Count.Deleted.Migrated</heading>
333 <query>SELECT COUNT(ac.id), ac.deleted::TEXT, ac.x_migrate::TEXT FROM asset_copy_legacy ac GROUP BY 2, 3</query>
337 <name>asset_copies_by_status</name>
338 <report_title>Copies by Status</report_title>
340 <iteration>0</iteration>
341 <heading>Copy Count.Status.Migrated</heading>
342 <query>SELECT COUNT(ac.id), cs.name, ac.x_migrate::TEXT FROM asset_copy_legacy ac JOIN config.copy_status cs ON cs.id = ac.status GROUP BY 2, 3 ORDER BY 2, 3</query>
346 <name>asset_precat</name>
347 <report_title>Precats (AKA Copies Without Bibs)</report_title>
349 <iteration>0</iteration>
350 <heading>Copy Count.Migating</heading>
351 <query>SELECT COUNT(ac.id), ac.x_migrate::TEXT FROM asset_copy_legacy ac WHERE ac.x_bre_id = -1 GROUP BY 2</query>
355 <name>asset_circ_mod_copies_count</name>
356 <report_title>Copies by Circulation Modifier</report_title>
358 <iteration>0</iteration>
359 <heading>Copy Count.Circulation Modifier.Migrated</heading>
360 <query>SELECT COUNT(ac.id), ac.circ_modifier, ac.x_migrate::TEXT FROM asset_copy_legacy ac GROUP BY 2, 3 ORDER BY 2, 3</query>
364 <name>asset_copy_notes</name>
365 <report_title>Copy Notes</report_title>
367 <iteration>0</iteration>
368 <heading>Note Count.Public.Migrated</heading>
369 <query>SELECT COUNT(acnote.id), acnote.pub::TEXT, acnote.x_migrate::TEXT FROM asset_copy_note_legacy acnote GROUP BY 2, 3 ORDER BY 2, 3</query>
373 <name>asset_copy_notes</name>
374 <report_title>Copy Notes</report_title>
376 <iteration>1</iteration>
377 <heading>Note Count.Public</heading>
378 <query>SELECT COUNT(acnote.id), acnote.pub::TEXT FROM asset_copy_note acnote GROUP BY 2 ORDER BY 2</query>
382 <name>asset_vols_by_lib</name>
383 <report_title>Volumes by Library</report_title>
385 <iteration>0</iteration>
386 <heading>Volume Count.Library.Migrated</heading>
387 <query>SELECT COUNT(acn.id), aou.name, acn.x_migrate::TEXT FROM asset_call_number_legacy acn JOIN actor_org_unit_legacy aou ON aou.id = acn.owning_lib GROUP BY 2, 3 ORDER BY 2, 3</query>
391 <name>asset_vols_by_lib</name>
392 <report_title>Volumes by Library</report_title>
394 <iteration>1</iteration>
395 <heading>Volume Count.Library</heading>
396 <query>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</query>
400 <name>asset_cops_by_loc</name>
401 <report_title>Copies by Location</report_title>
403 <iteration>0</iteration>
404 <heading>Copy Count.Library.Migrated</heading>
405 <query>SELECT COUNT(ac.id), acl.name, ac.x_migrate::TEXT FROM asset_copy_legacy ac JOIN asset.copy_location acl ON acl.id = ac.location GROUP BY 2, 3 ORDER BY 2, 3</query>
409 <name>asset_cops_by_loc_and_org</name>
410 <report_title>Copies by Location</report_title>
412 <iteration>0</iteration>
413 <heading>Copy Count.Library.Circ Library.Migrated</heading>
414 <query>SELECT COUNT(ac.id), acl.name, aou.name, ac.x_migrate::TEXT FROM 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 GROUP BY 2, 3, 4 ORDER BY 2, 3, 4</query>
418 <name>asset_cops_w_loc_one</name>
419 <report_title>Copies with a Location of Stacks</report_title>
421 <iteration>0</iteration>
422 <heading>Barcode</heading>
423 <query>SELECT barcode FROM asset_copy_legacy WHERE location = 1 AND x_migrate</query>
427 <name>asset_barcode_lengths</name>
428 <report_title>Barcode Lengths by Library</report_title>
430 <iteration>0</iteration>
431 <heading>Count of Barcode.Barcode Length.Library</heading>
432 <query>SELECT COUNT(ac.id), LENGTH(ac.barcode), aou.name FROM asset_copy_legacy ac JOIN actor.org_unit aou ON aou.id = ac.circ_lib WHERE ac.x_migrate = TRUE GROUP BY 2, 3 ORDER BY 3, 2</query>
436 <name>asset_barcode_patterns</name>
437 <report_title>Common Barcode Starting Patterns</report_title>
439 <iteration>0</iteration>
440 <heading>Count of Barcodes (greater than 10).Left 60% of Characters</heading>
441 <query>SELECT COUNT(ac.id), LEFT(ac.barcode,(ROUND(LENGTH(ac.barcode)*.6))::INT) FROM asset_copy_legacy ac WHERE ac.x_migrate = TRUE GROUP BY 2 HAVING COUNT(ac.id) > 10 ORDER BY 2</query>
445 <name>asset_barcode_incumbent_collisions</name>
446 <report_title>Copy Barcode Incumbent Collisions</report_title>
448 <iteration>0</iteration>
449 <heading>Collision Count</heading>
450 <query>SELECT COUNT(id) FROM asset_copy_legacy WHERE x_migrate = TRUE AND barcode ~* 'collision' and barcode ~* 'incumbent'</query>
451 <note>Incumbent collisions are those where the migrated barcodes collide with existing barcodes in the database.</note>
455 <name>asset_barcode_incumbent_collisions</name>
456 <report_title>Copy Barcode Incumbent Collisions</report_title>
458 <iteration>1</iteration>
459 <heading>Collision Count</heading>
460 <query>SELECT COUNT(id) FROM asset_copy WHERE barcode ~* 'collision' and barcode ~* 'incumbent'</query>
461 <note>Incumbent collisions are those where the migrated barcodes collide with existing barcodes in the database.</note>
465 <name>asset_barcode_internal_collisions</name>
466 <report_title>Copy Barcode Internal Collisions</report_title>
468 <iteration>0</iteration>
469 <heading>Collision Count</heading>
470 <query>SELECT COUNT(id) FROM asset_copy_legacy WHERE x_migrate = TRUE AND barcode ~* 'collision' and barcode ~* 'internal'</query>
471 <note>Internal collisions are those where the migrated barcodes have conflicts within their list of barcodes.</note>
475 <name>asset_barcode_internal_collisions</name>
476 <report_title>Copy Barcode Internal Collisions</report_title>
478 <iteration>1</iteration>
479 <heading>Collision Count</heading>
480 <query>SELECT COUNT(id) FROM asset_copy WHERE barcode ~* 'collision' and barcode ~* 'internal'</query>
481 <note>Internal collisions are those where the migrated barcodes have conflicts within their list of barcodes.</note>
485 <name>asset_barcode_collisions_shortlist</name>
486 <report_title>Copy Barcode Collisions (first 20)</report_title>
488 <iteration>0</iteration>
489 <heading>Collision List</heading>
490 <query>SELECT ac.barcode FROM asset_copy_legacy ac WHERE ac.barcode ~* 'collision' ORDER BY 1 LIMIT 20</query>
491 <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>
495 <name>asset_barcode_collisions_shortlist</name>
496 <report_title>Copy Barcode Collisions (first 20)</report_title>
498 <iteration>1</iteration>
499 <heading>Collision List</heading>
500 <query>SELECT ac.barcode FROM asset_copy ac WHERE ac.barcode ~* 'collision' ORDER BY 1 LIMIT 20</query>
501 <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>
505 <name>asset_barcode_collision_patterns</name>
506 <report_title>Common Copy Barcode Collision Patterns</report_title>
508 <iteration>0</iteration>
509 <heading>Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters</heading>
510 <query>SELECT COUNT(ac.id), LEFT(ac.barcode,(ROUND(LENGTH(ac.barcode)*.6))::INT) FROM asset_copy_legacy ac WHERE barcode ~* 'collision' GROUP BY 2 HAVING COUNT(ac.id) > 10 ORDER BY 2</query>
514 <name>asset_barcode_collision_patterns</name>
515 <report_title>Common Copy Barcode Collision Patterns</report_title>
517 <iteration>1</iteration>
518 <heading>Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters</heading>
519 <query>SELECT COUNT(ac.id), LEFT(ac.barcode,(ROUND(LENGTH(ac.barcode)*.6))::INT) FROM asset_copy ac WHERE barcode ~* 'collision' GROUP BY 2 HAVING COUNT(ac.id) > 10 ORDER BY 2</query>
523 <name>asset_stat_cats</name>
524 <report_title>Copy Statistical Categories</report_title>
526 <iteration>0</iteration>
527 <heading>Stat Cat Count.Library.Statistical Category</heading>
528 <query>SELECT COUNT(ac_sc.id), aou.name, ac_sc.name FROM 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>
532 <name>asset_stat_cats</name>
533 <report_title>Copy Statistical Categories</report_title>
535 <iteration>1</iteration>
536 <heading>Stat Cat Count.Library.Statistical Category</heading>
537 <query>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</query>
541 <name>asset_stat_cat_entries</name>
542 <report_title>Copy Stat Cat User Entries</report_title>
544 <iteration>0</iteration>
545 <heading>Copy Stat Count.Library.Statistical Category</heading>
546 <query>SELECT COUNT(map.id), aou.name, ac_sc.name FROM asset_stat_cat_entry_copy_map_legacy map JOIN 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>
550 <name>asset_stat_cat_entries</name>
551 <report_title>Copy Stat Cat User Entries</report_title>
553 <iteration>1</iteration>
554 <heading>Copy Stat Count.Library.Statistical Category</heading>
555 <query>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.
556 id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3</query>
560 <name>asset_copy_tags</name>
561 <report_title>Copy Tags</report_title>
563 <iteration>0</iteration>
564 <heading>Tag Count.Copy Tag Type.Copy Tag Label.Staff Note.Public</heading>
565 <query>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</query>
569 <name>asset_copy_alerts</name>
570 <report_title>Copy Alerts</report_title>
572 <iteration>0</iteration>
573 <heading>Alert Count.Alert Type</heading>
574 <query>SELECT COUNT(*), cat.name FROM asset_copy_alert aca JOIN config.copy_alert_type cat ON cat.id = aca.alert_type GROUP BY 2</query>
577 <!-- MONEY REPORTS -->
580 <name>money_billing_voided</name>
581 <report_title>Bills Voided And Not</report_title>
583 <iteration>0</iteration>
584 <heading>Count.Voided.Sum.Migrated</heading>
585 <query>SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM money_billing_legacy a GROUP BY 2, 4 ORDER BY 2, 4</query>
589 <name>money_billing_voided</name>
590 <report_title>Bills Voided And Not</report_title>
592 <iteration>1</iteration>
593 <heading>Count.Voided.Sum</heading>
594 <query>SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM money_billing a GROUP BY 2 ORDER BY 2, 3</query>
598 <name>money_billing_by_type</name>
599 <report_title>Bills by Type</report_title>
601 <iteration>0</iteration>
602 <heading>Count.Billing Type.Migrated</heading>
603 <query>SELECT COUNT(a.id), a.billing_type, a.x_migrate::TEXT FROM money_billing_legacy a GROUP BY 2, 3 ORDER BY 2, 3</query>
607 <name>money_billing_by_type</name>
608 <report_title>Bills by Type</report_title>
610 <iteration>1</iteration>
611 <heading>Count.Billing Type</heading>
612 <query>SELECT COUNT(a.id), a.billing_type FROM money_billing a GROUP BY 2 ORDER BY 2</query>
616 <name>money_cash_payment</name>
617 <report_title>Cash Payments</report_title>
619 <iteration>0</iteration>
620 <heading>Count.Voided.Sum.Migrated</heading>
621 <query>SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM money_cash_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4</query>
625 <name>money_cash_payment</name>
626 <report_title>Cash Payments</report_title>
628 <iteration>1</iteration>
629 <heading>Count.Voided.Sum</heading>
630 <query>SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM money_cash_payment a GROUP BY 2 ORDER BY 2</query>
634 <name>money_check_payment</name>
635 <report_title>Check Payments</report_title>
637 <iteration>0</iteration>
638 <heading>Count.Voided.Sum.Migrated</heading>
639 <query>SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM money_check_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4</query>
643 <name>money_forgive_payment</name>
644 <report_title>Forgive Payments</report_title>
646 <iteration>0</iteration>
647 <heading>Count.Voided.Sum.Migrated</heading>
648 <query>SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM money_forgive_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4</query>
652 <name>money_forgive_payment</name>
653 <report_title>Forgive Payments</report_title>
655 <iteration>1</iteration>
656 <heading>Count.Voided.Sum</heading>
657 <query>SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM money_forgive_paymen a GROUP BY 2 ORDER BY 2</query>
661 <name>money_goods_payment</name>
662 <report_title>Goods Payments</report_title>
664 <iteration>0</iteration>
665 <heading>Count.Voided.Sum.Migrated</heading>
666 <query>SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM money_goods_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4</query>
670 <name>money_work_payment</name>
671 <report_title>Work Payments</report_title>
673 <iteration>0</iteration>
674 <heading>Count.Voided.Sum.Migrated</heading>
675 <query>SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM money_work_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4</query>
679 <name>money_credit_card_payment</name>
680 <report_title>Credit Card Payments</report_title>
682 <iteration>0</iteration>
683 <heading>Count.Voided.Sum.Migrated</heading>
684 <query>SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM money_credit_card_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4</query>
688 <name>money_credit_payment</name>
689 <report_title>Credit Payments</report_title>
691 <iteration>0</iteration>
692 <heading>Count.Voided.Sum.Migrated</heading>
693 <query>SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM money_credit_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4</query>
697 <!-- BIBS REPORTS -->
700 <name>bibs_loaded</name>
701 <report_title>Loaded Bibliographic Records</report_title>
703 <iteration>0</iteration>
704 <heading>Count</heading>
705 <query>SELECT COUNT(bre.id) FROM biblio_record_entry_legacy bre WHERE id IN (SELECT id FROM biblio.record_entry) ORDER BY 1</query>
709 <name>bibs_loaded</name>
710 <report_title>Loaded Bibliographic Records</report_title>
712 <iteration>1</iteration>
713 <heading>Count</heading>
714 <query>SELECT COUNT(bre.id) FROM biblio_record_entry bre WHERE id IN (SELECT id FROM biblio.record_entry) ORDER BY 1</query>
718 <name>bibswovolumes</name>
719 <report_title>Bibliographic Records Without Volumes</report_title>
721 <iteration>0</iteration>
722 <heading>Count</heading>
723 <query>SELECT COUNT(id) FROM biblio_record_entry where id NOT IN (SELECT DISTINCT record FROM asset_call_number)</query>
724 <note>These records would not have been loaded.</note>
728 <name>bibswuri</name>
729 <report_title>Bibliographic Records With 856$9s</report_title>
731 <iteration>0</iteration>
732 <heading>Count</heading>
733 <query>SELECT COUNT(id) FROM biblio_record_entry where id IN (SELECT record FROM asset.call_number WHERE label ~* '##URI##')</query>
738 <report_title>Bibliographic Records with Adjusted Fixed Fields</report_title>
740 <iteration>0</iteration>
741 <heading>Count.Original Search Format.New Search Format</heading>
742 <query>SELECT COUNT(*), ARRAY_TO_STRING(x_search_format,','), ARRAY_TO_STRING(x_after_search_format,',') FROM biblio_record_entry_legacy WHERE x_migrate AND x_after_search_format IS NOT NULL GROUP BY 2, 3 ORDER BY 3,2</query>
746 <name>bibs_notes</name>
747 <report_title>Bib Record Notes</report_title>
749 <iteration>0</iteration>
750 <heading>Count.Migrated</heading>
751 <query>SELECT COUNT(b.id), b.x_migrate::TEXT FROM biblio_record_note_legacy b GROUP BY 2</query>
755 <name>bibs_notes</name>
756 <report_title>Bib Record Notes</report_title>
758 <iteration>1</iteration>
759 <heading>Count</heading>
760 <query>SELECT COUNT(b.id) FROM biblio_record_note b</query>
764 <name>bibs_peers</name>
765 <report_title>Peer Bib Copies</report_title>
767 <iteration>0</iteration>
768 <heading>Count.Migrated</heading>
769 <query>SELECT COUNT(b.id), b.x_migrate::TEXT FROM biblio_peer_bib_copy_map_legacy b GROUP BY 2</query>
773 <name>bibs_peers</name>
774 <report_title>Peer Bib Copies</report_title>
776 <iteration>1</iteration>
777 <heading>Count</heading>
778 <query>SELECT COUNT(b.id) FROM biblio_peer_bib_copy_map b</query>
782 <name>bibs_parts</name>
783 <report_title>Monograph Parts</report_title>
785 <iteration>0</iteration>
786 <heading>Count.Migrated</heading>
787 <query>SELECT COUNT(b.id), b.x_migrate::TEXT FROM biblio_monograph_part_legacy b GROUP BY 2</query>
791 <name>bibs_parts</name>
792 <report_title>Monograph Parts</report_title>
794 <iteration>1</iteration>
795 <heading>Count</heading>
796 <query>SELECT COUNT(b.id) FROM biblio_monograph_part b</query>
800 <name>bib_merges</name>
801 <report_title>Bibliographic Merge Count</report_title>
803 <iteration>0</iteration>
804 <heading>Records Merged.Incumbent Records Merged Into</heading>
805 <query>SELECT SUM(array_length(records,1)), COUNT(*) FROM groups</query>
809 <name>bib_merges_searchformat</name>
810 <report_title>Bibliographic Merges By Search Format</report_title>
812 <iteration>0</iteration>
813 <heading>Count.Item Type</heading>
814 <query>DROP TABLE IF EXISTS temp_unnest; CREATE TABLE temp_unnest AS (SELECT UNNEST(records) FROM groups); SELECT COUNT(record), search_format_str FROM dedupe_batch WHERE record IN (SELECT * FROM temp_unnest) GROUP BY 2 ORDER BY 2</query>
818 <name>bibs_not_loaded</name>
819 <report_title>Bibliographic Records That Failed to Load</report_title>
821 <iteration>0</iteration>
822 <heading>Title.Author.Control Number</heading>
823 <query>SELECT LEFT(ARRAY_TO_STRING(oils_xpath( '//*[@tag="245"]/*[@code="a"]/text()', a.marc),''),50) AS "Title",
824 LEFT(ARRAY_TO_STRING(oils_xpath( '//*[@tag="100"]/*[@code="a"]/text()', a.marc),''),50) AS "Author",
825 ARRAY_TO_STRING(oils_xpath( '//*[@tag="001"]/text()', a.marc),'') AS "001"
826 FROM biblio_record_entry a LEFT JOIN biblio.record_entry b ON b.id = a.id WHERE b.id IS NULL AND a.x_migrate
831 <!-- ACTORS REPORTS -->
834 <name>usrsbyorg</name>
835 <report_title>Patrons by Home Org</report_title>
837 <iteration>0</iteration>
838 <heading>Count.Library.Deleted.Migrated</heading>
839 <query>SELECT COUNT(au.id), aou.name, au.deleted::TEXT, au.x_migrate::TEXT FROM actor_usr_legacy au JOIN actor.org_unit aou ON aou.id = au.home_ou GROUP BY 2, 3, 4 ORDER BY 2, 3, 4</query>
843 <name>usrsbypgt</name>
844 <report_title>Patrons by Permission Group</report_title>
846 <iteration>0</iteration>
847 <heading>Count.Permission Group.Migrated</heading>
848 <query>SELECT COUNT(au.id), pgt.name, au.x_migrate::TEXT FROM actor_usr_legacy au JOIN permission.grp_tree pgt ON pgt.id = au.profile GROUP BY 2, 3 ORDER BY 2, 3</query>
852 <name>active_usrs</name>
853 <report_title>Patron by Active Status</report_title>
855 <iteration>0</iteration>
856 <heading>Count of Users.Active.Migrated</heading>
857 <query>SELECT COUNT(id), active::TEXT, x_migrate::TEXT FROM actor_usr_legacy GROUP BY 2, 3</query>
861 <name>active_usrs</name>
862 <report_title>Patrons by Active Status</report_title>
864 <iteration>1</iteration>
865 <heading>Count of Users.Active</heading>
866 <query>SELECT COUNT(id), active::TEXT FROM actor_usr GROUP BY 2</query>
870 <name>active_usr_barcodes</name>
871 <report_title>Patron Barcodes by Active Status</report_title>
873 <iteration>0</iteration>
874 <heading>Count of Barcodes.Active.Migrated</heading>
875 <query>SELECT COUNT(id), active::TEXT, x_migrate::TEXT FROM actor_card_legacy GROUP BY 2, 3</query>
879 <name>active_usr_barcodes</name>
880 <report_title>Patron Barcodes by Active Status</report_title>
882 <iteration>1</iteration>
883 <heading>Count of Barcodes.Active</heading>
884 <query>SELECT COUNT(id), active::TEXT FROM actor_card GROUP BY 2</query>
888 <name>usr_barcode_lengths</name>
889 <report_title>Barcode Lengths by Library</report_title>
891 <iteration>0</iteration>
892 <heading>Count of Barcode.Barcode Length.Library</heading>
893 <query>SELECT COUNT(acard.id), LENGTH(acard.barcode), aou.name FROM actor_card_legacy acard JOIN actor_usr_legacy au ON au.id = acard.usr JOIN actor.org_unit aou ON aou.id = au.home_ou WHERE acard.x_migrate = TRUE GROUP BY 2, 3 ORDER BY 3, 2</query>
897 <name>usr_barcode_lengths</name>
898 <report_title>Barcode Lengths by Library</report_title>
900 <iteration>1</iteration>
901 <heading>Count of Barcode.Barcode Length.Library</heading>
902 <query>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</query>
906 <name>usr_barcode_patterns</name>
907 <report_title>Common Barcode Starting Patterns</report_title>
909 <iteration>0</iteration>
910 <heading>Count of Barcodes (greater than 10).Left 60% of Characters</heading>
911 <query>SELECT COUNT(acard.id), LEFT(acard.barcode,(ROUND(LENGTH(acard.barcode)*.6))::INT) FROM actor_card_legacy acard WHERE acard.x_migrate = TRUE GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2</query>
915 <name>usr_barcode_patterns</name>
916 <report_title>Common Barcode Starting Patterns</report_title>
918 <iteration>1</iteration>
919 <heading>Count of Barcodes (greater than 10).Left 60% of Characters</heading>
920 <query>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</query>
924 <name>usr_barcode_incoming_collisions</name>
925 <report_title>Incoming Patron Barcode Collisions</report_title>
927 <iteration>0</iteration>
928 <heading>Collision Count</heading>
929 <query>SELECT COUNT(acard.id) FROM actor_card_legacy acard WHERE barcode ~* 'collision' AND x_migrate = TRUE</query>
933 <name>usr_barcode_incoming_collisions</name>
934 <report_title>Incoming Patron Barcode Collisions</report_title>
936 <iteration>1</iteration>
937 <heading>Collision Count</heading>
938 <query>SELECT COUNT(acard.id) FROM actor_card acard WHERE barcode ~* 'collision'</query>
942 <name>usr_barcode_incumbent_collisions</name>
943 <report_title>Incumbent Patron Barcode Collisions</report_title>
945 <iteration>0</iteration>
946 <heading>Collision Count</heading>
947 <query>SELECT COUNT(acard.id) FROM actor_card_legacy acard WHERE barcode ~* '^x_' AND x_migrate = TRUE</query>
951 <name>usr_barcode_incumbent_collisions</name>
952 <report_title>Incumbent Patron Barcode Collisions</report_title>
954 <iteration>1</iteration>
955 <heading>Collision Count</heading>
956 <query>SELECT COUNT(acard.id) FROM actor_card acard WHERE barcode ~* '^x_'</query>
960 <name>usr_barcode_collision_shortlist</name>
961 <report_title>Patron Barcode Collisions (first 20)</report_title>
963 <iteration>0</iteration>
964 <heading>Collision List</heading>
965 <query>SELECT acard.barcode FROM actor_card_legacy acard WHERE (acard.barcode ~* 'collision' OR acard.barcode ~* '^x_') AND acard.x_migrate = TRUE ORDER BY 1 LIMIT 20</query>
966 <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>
970 <name>usr_barcode_collision_shortlist</name>
971 <report_title>Patron Barcode Collisions (first 20)</report_title>
973 <iteration>1</iteration>
974 <heading>Collision List</heading>
975 <query>SELECT acard.barcode FROM actor_card acard WHERE (acard.barcode ~* 'collision' OR acard.barcode ~* '^x_') ORDER BY 1 LIMIT 20</query>
976 <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>
980 <name>usr_barcode_collision_patterns</name>
981 <report_title>Common Patron Barcode Collision Patterns</report_title> a.x_migrate
983 <iteration>0</iteration>
984 <heading>Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters</heading>
985 <query>SELECT COUNT(acard.id), LEFT(acard.barcode,(ROUND(LENGTH(acard.barcode)*.6))::INT) FROM 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>
989 <name>usr_barcode_collision_patterns</name>
990 <report_title>Common Patron Barcode Collision Patterns</report_title> a.x_migrate
992 <iteration>1</iteration>
993 <heading>Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters</heading>
994 <query>SELECT COUNT(acard.id), LEFT(acard.barcode,(ROUND(LENGTH(acard.barcode)*.6))::INT) FROM actor_card acard WHERE (acard.barcode ~* 'collision' OR acard.barcode ~* '^x_') GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2</query>
998 <name>usr_addressses_status</name>
999 <report_title>Patron Addresses by Valid Status</report_title>
1001 <iteration>0</iteration>
1002 <heading>Count.Valid.Migrated</heading>
1003 <query>SELECT COUNT(aua.id), valid::TEXT, x_migrate::TEXT FROM actor_usr_address_legacy aua GROUP BY 2, 3</query>
1007 <name>usr_addressses_status</name>
1008 <report_title>Patron Addresses by Valid Status</report_title>
1010 <iteration>1</iteration>
1011 <heading>Count.Valid</heading>
1012 <query>SELECT COUNT(aua.id), valid::TEXT FROM actor_usr_address aua GROUP BY 2</query>
1016 <name>usr_addresses_pending</name>
1017 <report_title>Patron Addresses by Pending Status</report_title>
1019 <iteration>0</iteration>
1020 <heading>Count of Addresses.Pending.Migrated</heading>
1021 <query>SELECT COUNT(aua.id), pending::TEXT, x_migrate::TEXT FROM actor_usr_address_legacy aua GROUP BY 2, 3</query>
1025 <name>usr_addresses_pending</name>
1026 <report_title>Patron Addresses by Pending Status</report_title>
1028 <iteration>1</iteration>
1029 <heading>Count of Addresses.Pending</heading>
1030 <query>SELECT COUNT(aua.id), pending::TEXT FROM actor_usr_address aua GROUP BY 2</query>
1034 <name>usr_messages</name>
1035 <report_title>Patron Messages</report_title>
1037 <iteration>0</iteration>
1038 <heading>Count.Deleted.Migrated</heading>
1039 <query>SELECT COUNT(aum.id), deleted::TEXT, x_migrate::TEXT FROM actor_usr_message_legacy aum GROUP BY 2, 3</query>
1043 <name>usr_messages</name>
1044 <report_title>Patron Messages</report_title>
1046 <iteration>1</iteration>
1047 <heading>Count.Deleted</heading>
1048 <query>SELECT COUNT(aum.id), deleted::TEXT FROM actor_usr_message_legacy aum GROUP BY 2</query>
1052 <name>usr_notes</name>
1053 <report_title>Patron Notes</report_title>
1055 <iteration>0</iteration>
1056 <heading>Count.Public.Migrated</heading>
1057 <query>SELECT COUNT(aun.id), pub::TEXT, x_migrate::TEXT FROM actor_usr_note_legacy aun GROUP BY 2, 3</query>
1061 <name>usr_notes</name>
1062 <report_title>Patron Notes</report_title>
1064 <iteration>1</iteration>
1065 <heading>Count.Public</heading>
1066 <query>SELECT COUNT(aun.id), pub::TEXT FROM actor_usr_note aun GROUP BY 2</query>
1070 <name>usr_stat_cats</name>
1071 <report_title>Patron Statistical Categories</report_title>
1073 <iteration>0</iteration>
1074 <heading>Stat Cat Count.Library.Statistical Category.Migrated</heading>
1075 <query>SELECT COUNT(au_sc.id), aou.name, au_sc.name, au_sc.x_migrate::TEXT FROM 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>
1079 <name>usr_stat_cats</name>
1080 <report_title>Patron Statistical Categories</report_title>
1082 <iteration>1</iteration>
1083 <heading>Stat Cat Count.Library.Statistical Category</heading>
1084 <query>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</query>
1088 <name>usr_stat_cat_entries</name>
1089 <report_title>Patron Stat Cat User Entries</report_title>
1091 <iteration>0</iteration>
1092 <heading>Patron Stat Count.Library.Statistical Category.Migrated</heading>
1093 <query>SELECT COUNT(map.id), aou.name, au_sc.name, map.x_migrate::TEXT FROM actor_stat_cat_entry_usr_map_legacy map JOIN 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>
1097 <name>usr_stat_cat_entries</name>
1098 <report_title>Patron Stat Cat User Entries</report_title>
1100 <iteration>1</iteration>
1101 <heading>Patron Stat Count.Library.Statistical Category</heading>
1102 <query>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</query>
1106 <name>usr_thresholds</name>
1107 <report_title>Patron Thresholds</report_title>
1109 <iteration>0</iteration>
1110 <heading>Pateron Group.Org Unit.Penalty.Threshold</heading>
1111 <query>SELECT pgt.name, aou.shortname, sp.name, p.threshold FROM permission_grp_penalty_threshold p
1112 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
1113 ORDER BY 2, 1, 3</query>
1117 <name>usr_settings</name>
1118 <report_title>Patron Settings</report_title>
1120 <iteration>0</iteration>
1121 <heading>Count.Setting.Value</heading>
1122 <query>SELECT COUNT(*), name, 'User''s Phone' FROM actor_usr_setting WHERE name IN ('opac.default_phone') GROUP BY 2, 3
1123 UNION ALL SELECT COUNT(*), name, value FROM actor_usr_setting WHERE name IN ('opac.hold_notify') GROUP BY 2, 3
1124 UNION ALL SELECT COUNT(*), a.name, aou.shortname FROM actor_usr_setting a JOIN actor.org_unit aou ON aou.id = REPLACE(a.value,'"','')::INTEGER
1125 WHERE a.name IN ('opac.default_pickup_location','opac.default_search_location') GROUP BY 2, 3 ORDER BY 2, 3;</query>
1128 <!-- ACQUISITIONS REPORTS -->
1130 <name>fund_count</name>
1131 <iteration>0</iteration>
1133 <report_title>Migrated Funds</report_title>
1134 <heading>Number of Funds.Migrated</heading>
1135 <query>SELECT COUNT(id), x_migrate::TEXT FROM acq_fund_legacy GROUP BY 2;</query>
1139 <name>fund_count</name>
1140 <iteration>1</iteration>
1142 <report_title>Migrated Funds</report_title>
1143 <heading>Number of Funds</heading>
1144 <query>SELECT COUNT(id) FROM acq_fund;</query>
1148 <name>invoice_count</name>
1149 <iteration>0</iteration>
1151 <report_title>Migrated Invoices</report_title>
1152 <heading>Number of Invoices.Migrated</heading>
1153 <query>SELECT COUNT(id), x_migrate::TEXT FROM acq_invoice_legacy GROUP BY 2;</query>
1157 <name>invoice_count</name>
1158 <iteration>1</iteration>
1160 <report_title>Migrated Invoices</report_title>
1161 <heading>Number of Funds</heading>
1162 <query>SELECT COUNT(id) FROM acq_invoice;</query>
1165 <!-- SERIALS REPORTS -->
1167 <name>serials_mfhd_count</name>
1169 <iteration>0</iteration>
1170 <report_title>Migrated Serial MFHDs</report_title>
1171 <heading>Number of MFHDs</heading>
1172 <query>SELECT COUNT(id) FROM serial_record_entry</query>
1175 <!-- DEDUPE REPORTS -->
1178 <name>dedupe_explain</name>
1180 <file>dedupe_process.asciidoc</file>
1184 <name>dedupe_bib_groups</name>
1186 <iteration>0</iteration>
1187 <report_title>Scoring and Bib Record Groups</report_title>
1188 <heading>Count.Bib Record Groups</heading>
1189 <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)
1190 UNION ALL SELECT (COUNT(DISTINCT incoming_bib)), 'Incoming Bibs With Matches Found' FROM bib_matches
1191 UNION ALL SELECT (COUNT(bre.id) - (SELECT COUNT(DISTINCT incoming_bib) FROM bib_matches)), 'Incoming Bibs With No Match'
1192 FROM biblio.record_entry bre WHERE bre.deleted IS FALSE AND bre.id IN (SELECT eg::BIGINT FROM bib_id_map)
1193 UNION ALL SELECT COUNT(DISTINCT incoming_bib), 'Incoming Bibs Being Merged into Incumbent' FROM bib_matches WHERE incumbent_bib_score >= incoming_bib_score
1194 UNION ALL SELECT COUNT(id), 'Incumbent Bibs With Higher Scores to Incoming' FROM bib_matches WHERE incumbent_bib_score > incoming_bib_score
1195 UNION ALL SELECT COUNT(id), 'Incumbent Bibs With Equal Scores to Incoming' FROM bib_matches WHERE incumbent_bib_score = incoming_bib_score
1196 UNION ALL SELECT COUNT(id), 'Incumbent Bibs With Lower Scores to Incoming' FROM bib_matches WHERE incumbent_bib_score < incoming_bib_score
1201 <name>dedupe_format_count</name>
1203 <iteration>0</iteration>
1204 <report_title>Count of Items Matching By Format</report_title>
1205 <heading>Count.Format(s)</heading>
1206 <query>SELECT COUNT(id), search_formats FROM bib_matches GROUP BY 2 ORDER BY 2;</query>
1210 <name>dedupe_score_ranges</name>
1212 <iteration>0</iteration>
1213 <report_title>Count of Items Matching By Format</report_title>
1214 <heading>Lowest Record Score.Largest Record Score.Record Set</heading>
1215 <query>SELECT MIN(incumbent_bib_score), MAX(incumbent_bib_score), 'Incumbent Records' FROM bib_matches
1216 UNION ALL SELECT MIN(incoming_bib_score), MAX(incoming_bib_score), 'Incoming Records' FROM bib_matches ;
1222 <name>dedupe_sample_set</name>
1224 <iteration>0</iteration>
1225 <report_title>Sample of 20 Matching Dedupe Record Sets</report_title>
1226 <heading>Bib Being Merged Into.Bib Being Merged</heading>
1227 <query>SELECT incumbent_bib, incoming_bib FROM bib_matches WHERE incumbent_bib_score >= incoming_bib_score LIMIT 20 ;
1231 <!-- NOTICES REPORTS -->
1234 <name>notices_overview</name>
1236 <iteration>0</iteration>
1237 <report_title>Action Triggers Setup for Notices</report_title>
1238 <heading>ID.Active.Owner.Name</heading>
1239 <query>SELECT ed.id, ed.active, aou.shortname, ed.name
1240 FROM action_trigger.event_definition ed
1241 JOIN actor.org_unit aou ON aou.id = ed.owner
1242 WHERE ed.owner IN (SELECT DISTINCT home_ou FROM actor_usr)
1243 OR ed.owner IN (SELECT DISTINCT parent_ou FROM actor.org_unit WHERE id in (SELECT DISTINCT home_ou FROM actor_usr));
1248 <name>notices_count</name>
1250 <iteration>0</iteration>
1251 <report_title>Count of Notices Run with State</report_title>
1252 <heading>Count of Notices.State.ID.Owner.Name</heading>
1253 <query>SELECT COUNT(ate.id), ate.state, ed.id, aou.shortname, ed.name
1254 FROM action_trigger.event_definition ed
1255 JOIN actor.org_unit aou ON aou.id = ed.owner
1256 JOIN action_trigger.event ate ON ate.event_def = ed.id
1257 WHERE ed.owner IN (SELECT DISTINCT home_ou FROM actor_usr)
1258 OR ed.owner IN (SELECT DISTINCT parent_ou FROM actor.org_unit WHERE id in (SELECT DISTINCT home_ou FROM actor_usr))