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 m_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 m_action_circulation_legacy WHERE xact_finish IS NOT NULL AND x_migrate
50 UNION ALL SELECT 'Open Circulations', COUNT(id) FROM m_action_circulation_legacy WHERE xact_finish IS NULL AND x_migrate</query>
54 <name>circ_count</name>
56 <iteration>1</iteration>
57 <report_title>Open Circulations</report_title>
58 <heading>Circulation Status.Count of Circs</heading>
59 <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>
63 <name>circ_by_orgunit</name>
65 <iteration>0</iteration>
66 <report_title>Circulations by Org Unit</report_title>
67 <heading>Circulations Count.Org Unit</heading>
68 <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>
72 <name>circ_by_orgunit</name>
74 <iteration>1</iteration>
75 <report_title>Circulations by Org Unit</report_title>
76 <heading>Circulations Count.Org Unit</heading>
77 <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>
81 <name>circs_by_duration</name>
83 <iteration>0</iteration>
84 <report_title>Migrated Circulations by Duration, Fine and Max Fine</report_title>
85 <heading>Count of Circs.Duration.Fine.Max Fine</heading>
86 <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>
90 <name>circs_by_duration</name>
92 <iteration>1</iteration>
93 <report_title>Circulations by Duration, Fine and Max Fine</report_title>
94 <heading>Count of Circs.Duration.Fine.Max Fine</heading>
95 <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>
99 <name>circs_by_usrgroup</name>
101 <iteration>0</iteration>
102 <report_title>Circulations by Rules and Patron Group</report_title>
103 <heading>Count of Circs.Duration.Fine.Max Fine.User Group.Matchpoints</heading>
104 <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>
108 <name>circs_by_usrgroup</name>
110 <iteration>1</iteration>
111 <report_title>Circulations by Rules and Patron Group</report_title>
112 <heading>Count of Circs.Duration.Fine.Max Fine.User Group.Matchpoints</heading>
113 <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>
117 <name>circs_by_circmod</name>
119 <iteration>0</iteration>
120 <report_title>Circulations by Rules and Circulation Modifier</report_title>
121 <heading>Count of Circs.Duration.Fine.Max Fine.Circulation Modifier.Matchpoints</heading>
122 <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>
126 <name>circs_by_circmod</name>
128 <iteration>1</iteration>
129 <report_title>Circulations by Rules and Circulation Modifier</report_title>
130 <heading>Count of Circs.Duration.Fine.Max Fine.Circulation Modifier.Matchpoints</heading>
131 <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
132 GROUP BY 2, 3, 4, 5, 6 ORDER BY 2, 3, 4, 5, 6</query>
136 <name>circs_by_orgunit</name>
138 <iteration>0</iteration>
139 <report_title>Circulations by Rules and Org Unit</report_title>
140 <heading>Count of Circs.Duration.Fine.Max Fine.Library Branch</heading>
141 <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>
145 <name>circs_by_orgunit</name>
147 <iteration>1</iteration>
148 <report_title>Circulations by Rules and Org Unit</report_title>
149 <heading>Count of Circs.Duration.Fine.Max Fine.Library Branch</heading>
150 <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>
154 <name>non_cat_circs</name>
156 <iteration>0</iteration>
157 <report_title>Non-Cataloged Circulation</report_title>
158 <heading>Circulations Count.Migrated</heading>
159 <query>SELECT COUNT(id), x_migrate::TEXT FROM m_action_non_cataloged_circulation_legacy GROUP BY 2</query>
163 <name>non_cat_circs</name>
165 <iteration>1</iteration>
166 <report_title>Non-Cataloged Circulation</report_title>
167 <heading>Circulations Count</heading>
168 <query>SELECT COUNT(id) FROM m_action_non_cataloged_circulation</query>
172 <name>in_house</name>
174 <iteration>0</iteration>
175 <report_title>In House Use</report_title>
176 <heading>In House Use Records.Migrated</heading>
177 <query>SELECT COUNT(id), x_migrate::TEXT FROM m_action_in_house_use_legacy GROUP BY 2</query>
181 <name>in_house</name>
183 <iteration>1</iteration>
184 <report_title>In House Use</report_title>
185 <heading>In House Use Records</heading>
186 <query>SELECT COUNT(id) FROM m_action_in_house_use</query>
190 <name>circs_missing_rules</name>
192 <iteration>1</iteration>
193 <report_title>Circs Missing Rules</report_title>
194 <heading>Count.Field Missing</heading>
195 <query>SELECT COUNT(id), 'Duration Rule Value' FROM m_action_circulation WHERE duration IS NULL
196 UNION ALL SELECT COUNT(id), 'Recurring Fine Rule Value' FROM m_action_circulation WHERE recurring_fine IS NULL
197 UNION ALL SELECT COUNT(id), 'Max Fine Rule Value' FROM m_action_circulation WHERE max_fine IS NULL
198 UNION ALL SELECT COUNT(id), 'Duration Rule' FROM m_action_circulation WHERE duration_rule IS NULL
199 UNION ALL SELECT COUNT(id), 'Recurring Fine Rule' FROM m_action_circulation WHERE recurring_fine_rule IS NULL
200 UNION ALL SELECT COUNT(id), 'Max Fine Rule' FROM m_action_circulation WHERE max_fine_rule IS NULL
205 <name>circ_open_by_item_status</name>
207 <iteration>0</iteration>
208 <report_title>Status of Currently Circulating Items</report_title>
209 <heading>Count.Status</heading>
210 <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>
213 <!-- HOLDS REPORTS -->
218 <iteration>0</iteration>
219 <report_title>Migrated and Non-Migrated Holds</report_title>
220 <heading>Hold Type.Hold Count.Migrated</heading>
221 <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>
225 <name>holds_bytype</name>
227 <iteration>0</iteration>
228 <report_title>Migrated Holds By Type</report_title>
229 <heading>Hold Type.Hold Count</heading>
230 <query>SELECT hold_type as "Hold Type", COUNT(id) FROM m_action_hold_request_legacy WHERE x_migrate = TRUE GROUP BY 1</query>
234 <name>transit_open_by_item_status</name>
236 <iteration>0</iteration>
237 <report_title>Transit Copy Records and Status of Linked Items</report_title>
238 <heading>Count.Status</heading>
239 <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>
243 <name>transit_copies_by_status</name>
245 <iteration>0</iteration>
246 <report_title>Status of Items with Count of Open In Transits</report_title>
247 <heading>Count.Status.Count of Open Transits</heading>
248 <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>
252 <name>hold_copies_by_status</name>
254 <iteration>0</iteration>
255 <report_title>Captured Holds with Status of Items</report_title>
256 <heading>Count of Captured Hold.Status of Item</heading>
257 <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>
261 <name>hold_depth</name>
263 <iteration>0</iteration>
264 <report_title>Depth of Unfilled Holds</report_title>
265 <heading>Count.Depth</heading>
266 <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>
269 <!-- ASSET REPORTS -->
272 <name>m_asset_copy_count</name>
273 <report_title>Count of Copies by Library</report_title>
275 <iteration>0</iteration>
276 <heading>Copy Count.Library</heading>
277 <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>
281 <name>m_asset_copy_count_non_migrated</name>
282 <report_title>Non-Migrated Count of Copies by Library</report_title>
284 <iteration>0</iteration>
285 <heading>Copy Count.Library</heading>
286 <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>
290 <name>m_asset_copies_by_status</name>
291 <report_title>Copies by Status</report_title>
293 <iteration>0</iteration>
294 <heading>Copy Count.Status</heading>
295 <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>
299 <name>m_asset_circ_mod_copies_count</name>
300 <report_title>Copies by Circulation Modifier</report_title>
302 <iteration>0</iteration>
303 <heading>Copy Count.Circulation Modifier</heading>
304 <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>
308 <name>m_asset_copy_notes</name>
309 <report_title>Copy Notes</report_title>
311 <iteration>0</iteration>
312 <heading>Note Count.Public</heading>
313 <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>
317 <name>m_asset_copy_notes</name>
318 <report_title>Copy Notes</report_title>
320 <iteration>1</iteration>
321 <heading>Note Count.Public</heading>
322 <query>SELECT COUNT(acnote.id), acnote.pub::TEXT FROM m_asset_copy_note acnote GROUP BY 2 ORDER BY 2</query>
326 <name>m_asset_vols_by_lib</name>
327 <report_title>Volumes by Library</report_title>
329 <iteration>0</iteration>
330 <heading>Volume Count.Library</heading>
331 <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>
335 <name>m_asset_vols_by_lib</name>
336 <report_title>Volumes by Library</report_title>
338 <iteration>1</iteration>
339 <heading>Volume Count.Library</heading>
340 <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>
344 <name>m_asset_cops_by_loc_and_org</name>
345 <report_title>Copies by Location</report_title>
347 <iteration>0</iteration>
348 <heading>Copy Count.Library.Circ Library</heading>
349 <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>
353 <name>m_asset_cops_w_loc_one</name>
354 <report_title>Copies with a Location of Stacks</report_title>
356 <iteration>0</iteration>
357 <heading>Barcode</heading>
358 <query>SELECT barcode FROM m_asset_copy_legacy WHERE location = 1 AND x_migrate</query>
362 <name>m_asset_no_barcode</name>
363 <report_title>Items Without Barcodes</report_title>
365 <iteration>0</iteration>
366 <heading>Assigned Barcode</heading>
367 <query>SELECT barcode FROM m_asset_copy_legacy WHERE barcode ~* 'no_barocde' AND x_migrate</query>
371 <name>m_asset_barcode_patterns</name>
372 <report_title>Common Barcode Starting Patterns</report_title>
374 <iteration>0</iteration>
375 <heading>Count of Barcodes (greater than 10).Left 60% of Characters</heading>
376 <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>
380 <name>m_asset_barcode_collisions</name>
381 <report_title>Copy Barcode Collisions</report_title>
383 <iteration>0</iteration>
384 <heading>Collision Count</heading>
385 <query>SELECT COUNT(id) FROM m_asset_copy_legacy WHERE x_migrate = TRUE AND barcode LIKE 'x_%'</query>
386 <note>Incumbent collisions are those where the migrated barcodes collide with existing barcodes in the database.</note>
390 <name>m_asset_barcode_collisions</name>
391 <report_title>Copy Barcode Collisions</report_title>
393 <iteration>1</iteration>
394 <heading>Collision Count</heading>
395 <query>SELECT COUNT(id) FROM m_asset_copy WHERE barcode LIKE 'x_%'</query>
396 <note>Incumbent collisions are those where the migrated barcodes collide with existing barcodes in the database.</note>
400 <name>m_asset_barcode_collisions_shortlist</name>
401 <report_title>Copy Barcode Collisions (first 20)</report_title>
403 <iteration>0</iteration>
404 <heading>Collision List</heading>
405 <query>SELECT ac.barcode FROM m_asset_copy_legacy ac WHERE ac.barcode LIKE 'x_%' ORDER BY 1 LIMIT 20</query>
406 <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>
410 <name>m_asset_barcode_collisions_shortlist</name>
411 <report_title>Copy Barcode Collisions (first 20)</report_title>
413 <iteration>1</iteration>
414 <heading>Collision List</heading>
415 <query>SELECT ac.barcode FROM m_asset_copy ac WHERE ac.barcode LIKE 'x_%' ORDER BY 1 LIMIT 20</query>
416 <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>
420 <name>m_asset_barcode_collision_patterns</name>
421 <report_title>Common Copy Barcode Collision Patterns</report_title>
423 <iteration>0</iteration>
424 <heading>Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters</heading>
425 <query>SELECT COUNT(ac.id), LEFT(ac.barcode,(ROUND(LENGTH(ac.barcode)*.6))::INT) FROM m_asset_copy_legacy ac WHERE barcode LIKE 'x_%' GROUP BY 2 HAVING COUNT(ac.id) > 10 ORDER BY 2</query>
429 <name>m_asset_barcode_collision_patterns</name>
430 <report_title>Common Copy Barcode Collision Patterns</report_title>
432 <iteration>1</iteration>
433 <heading>Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters</heading>
434 <query>SELECT COUNT(ac.id), LEFT(ac.barcode,(ROUND(LENGTH(ac.barcode)*.6))::INT) FROM m_asset_copy ac WHERE barcode LIKE 'x_%' GROUP BY 2 HAVING COUNT(ac.id) > 10 ORDER BY 2</query>
438 <name>m_asset_stat_cats</name>
439 <report_title>Copy Statistical Categories</report_title>
441 <iteration>0</iteration>
442 <heading>Stat Cat Count.Library.Statistical Category</heading>
443 <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>
447 <name>m_asset_stat_cats</name>
448 <report_title>Copy Statistical Categories</report_title>
450 <iteration>1</iteration>
451 <heading>Stat Cat Count.Library.Statistical Category</heading>
452 <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>
456 <name>m_asset_stat_cat_entries</name>
457 <report_title>Copy Stat Cat User Entries</report_title>
459 <iteration>0</iteration>
460 <heading>Copy Stat Count.Library.Statistical Category</heading>
461 <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>
465 <name>m_asset_stat_cat_entries</name>
466 <report_title>Copy Stat Cat User Entries</report_title>
468 <iteration>1</iteration>
469 <heading>Copy Stat Count.Library.Statistical Category</heading>
470 <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.
471 id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3</query>
475 <name>m_asset_copy_tags</name>
476 <report_title>Copy Tags</report_title>
478 <iteration>0</iteration>
479 <heading>Tag Count.Copy Tag Type.Copy Tag Label.Staff Note.Public</heading>
480 <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>
484 <name>m_asset_copy_alerts</name>
485 <report_title>Copy Alerts</report_title>
487 <iteration>0</iteration>
488 <heading>Alert Count.Alert Type</heading>
489 <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>
492 <!-- MONEY REPORTS -->
495 <name>m_money_billing_voided</name>
496 <report_title>Bills Voided And Not</report_title>
498 <iteration>0</iteration>
499 <heading>Count.Voided.Sum.Migrated</heading>
500 <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>
504 <name>m_money_billing_voided</name>
505 <report_title>Bills Voided And Not</report_title>
507 <iteration>1</iteration>
508 <heading>Count.Voided.Sum</heading>
509 <query>SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM m_money_billing a GROUP BY 2 ORDER BY 2, 3</query>
513 <name>m_money_billing_by_type</name>
514 <report_title>Bills by Type</report_title>
516 <iteration>0</iteration>
517 <heading>Count.Billing Type.Migrated</heading>
518 <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>
522 <name>m_money_billing_by_type</name>
523 <report_title>Bills by Type</report_title>
525 <iteration>1</iteration>
526 <heading>Count.Billing Type</heading>
527 <query>SELECT COUNT(a.id), a.billing_type FROM m_money_billing a GROUP BY 2 ORDER BY 2</query>
531 <name>m_money_cash_payment</name>
532 <report_title>Cash Payments</report_title>
534 <iteration>0</iteration>
535 <heading>Count.Voided.Sum.Migrated</heading>
536 <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>
540 <name>m_money_cash_payment</name>
541 <report_title>Cash Payments</report_title>
543 <iteration>1</iteration>
544 <heading>Count.Voided.Sum</heading>
545 <query>SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM m_money_cash_payment a GROUP BY 2 ORDER BY 2</query>
549 <name>m_money_check_payment</name>
550 <report_title>Check Payments</report_title>
552 <iteration>0</iteration>
553 <heading>Count.Voided.Sum.Migrated</heading>
554 <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>
558 <name>m_money_forgive_payment</name>
559 <report_title>Forgive Payments</report_title>
561 <iteration>0</iteration>
562 <heading>Count.Voided.Sum.Migrated</heading>
563 <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>
567 <name>m_money_forgive_payment</name>
568 <report_title>Forgive Payments</report_title>
570 <iteration>1</iteration>
571 <heading>Count.Voided.Sum</heading>
572 <query>SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM m_money_forgive_paymen a GROUP BY 2 ORDER BY 2</query>
576 <name>m_money_goods_payment</name>
577 <report_title>Goods Payments</report_title>
579 <iteration>0</iteration>
580 <heading>Count.Voided.Sum.Migrated</heading>
581 <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>
585 <name>m_money_work_payment</name>
586 <report_title>Work Payments</report_title>
588 <iteration>0</iteration>
589 <heading>Count.Voided.Sum.Migrated</heading>
590 <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>
594 <name>m_money_credit_card_payment</name>
595 <report_title>Credit Card Payments</report_title>
597 <iteration>0</iteration>
598 <heading>Count.Voided.Sum.Migrated</heading>
599 <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>
603 <name>m_money_credit_payment</name>
604 <report_title>Credit Payments</report_title>
606 <iteration>0</iteration>
607 <heading>Count.Voided.Sum.Migrated</heading>
608 <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>
612 <!-- BIBS REPORTS -->
615 <name>bibs_loaded</name>
616 <report_title>Loaded Bibliographic Records</report_title>
618 <iteration>0</iteration>
619 <heading>Count</heading>
620 <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>
624 <name>bibs_loaded</name>
625 <report_title>Loaded Bibliographic Records</report_title>
627 <iteration>1</iteration>
628 <heading>Count</heading>
629 <query>SELECT COUNT(bre.id) FROM m_biblio_record_entry bre WHERE id IN (SELECT id FROM biblio.record_entry) ORDER BY 1</query>
633 <name>bibswovolumes</name>
634 <report_title>Bibliographic Records Without Volumes</report_title>
636 <iteration>0</iteration>
637 <heading>Count</heading>
638 <query>SELECT COUNT(id) FROM m_biblio_record_entry where id NOT IN (SELECT DISTINCT record FROM m_asset_call_number)</query>
639 <note>These records would not have been loaded but many may be deduplicated against incumbent records.</note>
643 <name>bibswuri</name>
644 <report_title>Bibliographic Records With 856$9s</report_title>
646 <iteration>0</iteration>
647 <heading>Count</heading>
648 <query>SELECT COUNT(id) FROM m_biblio_record_entry where id IN (SELECT record FROM asset.call_number WHERE label ~* '##URI##')</query>
653 <report_title>Bibliographic Records with Adjusted Fixed Fields</report_title>
655 <iteration>0</iteration>
656 <heading>Count.Original Search Format.New Search Format</heading>
657 <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>
661 <name>bibs_notes</name>
662 <report_title>Bib Record Notes</report_title>
664 <iteration>0</iteration>
665 <heading>Count.Migrated</heading>
666 <query>SELECT COUNT(b.id), b.x_migrate::TEXT FROM m_biblio_record_note_legacy b GROUP BY 2</query>
670 <name>bibs_notes</name>
671 <report_title>Bib Record Notes</report_title>
673 <iteration>1</iteration>
674 <heading>Count</heading>
675 <query>SELECT COUNT(b.id) FROM m_biblio_record_note b</query>
679 <name>bibs_peers</name>
680 <report_title>Peer Bib Copies</report_title>
682 <iteration>0</iteration>
683 <heading>Count.Migrated</heading>
684 <query>SELECT COUNT(b.id), b.x_migrate::TEXT FROM m_biblio_peer_bib_copy_map_legacy b GROUP BY 2</query>
688 <name>bibs_peers</name>
689 <report_title>Peer Bib Copies</report_title>
691 <iteration>1</iteration>
692 <heading>Count</heading>
693 <query>SELECT COUNT(b.id) FROM m_biblio_peer_bib_copy_map b</query>
697 <name>bibs_parts</name>
698 <report_title>Monograph Parts</report_title>
700 <iteration>0</iteration>
701 <heading>Count.Migrated</heading>
702 <query>SELECT COUNT(b.id), b.x_migrate::TEXT FROM m_biblio_monograph_part_legacy b GROUP BY 2</query>
706 <name>bibs_parts</name>
707 <report_title>Monograph Parts</report_title>
709 <iteration>1</iteration>
710 <heading>Count</heading>
711 <query>SELECT COUNT(b.id) FROM m_biblio_monograph_part b</query>
715 <name>bib_merges</name>
716 <report_title>Bibliographic Merge Count</report_title>
718 <iteration>0</iteration>
719 <heading>Records Merged.Incumbent Records Merged Into</heading>
720 <query>SELECT SUM(array_length(records,1)), COUNT(*) FROM groups</query>
723 <!-- ACTORS REPORTS -->
726 <name>usrsbyorg</name>
727 <report_title>Migrated Patrons by Home Org</report_title>
729 <iteration>0</iteration>
730 <heading>Count.Library</heading>
731 <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>
735 <name>nonmigusrsbyorg</name>
736 <report_title>Non-Migrated Patrons by Home Org</report_title>
738 <iteration>0</iteration>
739 <heading>Count.Library</heading>
740 <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>
744 <name>usrsbypgt</name>
745 <report_title>Migrated Patrons by Permission Group</report_title>
747 <iteration>0</iteration>
748 <heading>Count.Permission Group</heading>
749 <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>
753 <name>active_usrs</name>
754 <report_title>Patron by Active Status</report_title>
756 <iteration>0</iteration>
757 <heading>Count of Users.Active</heading>
758 <query>SELECT COUNT(id), active::TEXT FROM m_actor_usr_legacy GROUP BY 2</query>
762 <name>active_usrs</name>
763 <report_title>Patron Cards</report_title>
765 <iteration>1</iteration>
766 <heading>Count of Users</heading>
767 <query>SELECT COUNT(id) FROM m_actor_usr</query>
771 <name>active_usr_barcodes</name>
772 <report_title>Patron Barcodes by Active Status</report_title>
774 <iteration>0</iteration>
775 <heading>Count of Barcodes.Active.Migrated</heading>
776 <query>SELECT COUNT(id), active::TEXT, x_migrate::TEXT FROM m_actor_card_legacy GROUP BY 2, 3</query>
780 <name>active_usr_barcodes</name>
781 <report_title>Patron Barcodes by Active Status</report_title>
783 <iteration>1</iteration>
784 <heading>Count of Barcodes.Active</heading>
785 <query>SELECT COUNT(id), active::TEXT FROM m_actor_card GROUP BY 2</query>
789 <name>usr_barcode_patterns</name>
790 <report_title>Common Barcode Starting Patterns</report_title>
792 <iteration>0</iteration>
793 <heading>Count of Barcodes (greater than 10).Left 60% of Characters</heading>
794 <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>
798 <name>usr_barcode_patterns</name>
799 <report_title>Common Barcode Starting Patterns</report_title>
801 <iteration>1</iteration>
802 <heading>Count of Barcodes (greater than 10).Left 60% of Characters</heading>
803 <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>
807 <name>usr_barcode_collisions</name>
808 <report_title>Patron Barcode Collisions</report_title>
810 <iteration>0</iteration>
811 <heading>Collision Count</heading>
812 <query>SELECT COUNT(acard.id) FROM m_actor_card_legacy acard WHERE barcode LIKE 'x_%' AND x_migrate = TRUE</query>
816 <name>usr_barcode_collisions</name>
817 <report_title>Patron Barcode Collisions</report_title>
819 <iteration>1</iteration>
820 <heading>Collision Count</heading>
821 <query>SELECT COUNT(acard.id) FROM m_actor_card acard WHERE barcode LIKE 'x_%'</query>
825 <name>usr_barcode_collision_shortlist</name>
826 <report_title>Patron Barcode Collisions (first 20)</report_title>
828 <iteration>0</iteration>
829 <heading>Collision List</heading>
830 <query>SELECT acard.barcode FROM m_actor_card_legacy acard WHERE acard.barcode LIKE 'x_' AND acard.x_migrate = TRUE ORDER BY 1 LIMIT 20</query>
831 <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>
835 <name>usr_barcode_collision_shortlist</name>
836 <report_title>Patron Barcode Collisions (first 20)</report_title>
838 <iteration>1</iteration>
839 <heading>Collision List</heading>
840 <query>SELECT acard.barcode FROM m_actor_card acard WHERE acard.barcode LIKE 'x_%' ORDER BY 1 LIMIT 20</query>
841 <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>
845 <name>usr_barcode_collision_patterns</name>
846 <report_title>Common Patron Barcode Collision Patterns</report_title> a.x_migrate
848 <iteration>0</iteration>
849 <heading>Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters</heading>
850 <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>
854 <name>usr_barcode_collision_patterns</name>
855 <report_title>Common Patron Barcode Collision Patterns</report_title> a.x_migrate
857 <iteration>1</iteration>
858 <heading>Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters</heading>
859 <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>
863 <name>usr_addressses_status</name>
864 <report_title>Patron Addresses</report_title>
866 <iteration>0</iteration>
867 <heading>Count</heading>
868 <query>SELECT COUNT(aua.id) FROM m_actor_usr_address_legacy aua WHERE aua.x_migrate = TRUE</query>
872 <name>usr_addressses_status</name>
873 <report_title>Patron Addresses</report_title>
875 <iteration>1</iteration>
876 <heading>Count</heading>
877 <query>SELECT COUNT(aua.id) FROM m_actor_usr_address aua</query>
881 <name>usr_addresses_pending</name>
882 <report_title>Patron Addresses by Pending Status</report_title>
884 <iteration>0</iteration>
885 <heading>Count of Addresses.Pending</heading>
886 <query>SELECT COUNT(aua.id), pending::TEXT FROM m_actor_usr_address_legacy aua WHERE aua.x_migrate = TRUE GROUP BY 2</query>
890 <name>usr_addresses_pending</name>
891 <report_title>Patron Addresses by Pending Status</report_title>
893 <iteration>1</iteration>
894 <heading>Count of Addresses.Pending</heading>
895 <query>SELECT COUNT(aua.id), pending::TEXT FROM m_actor_usr_address aua GROUP BY 2</query>
899 <name>usr_messages</name>
900 <report_title>Patron Messages</report_title>
902 <iteration>0</iteration>
903 <heading>Count.Deleted.Migrated</heading>
904 <query>SELECT COUNT(aum.id), deleted::TEXT, x_migrate::TEXT FROM m_actor_usr_message_legacy aum GROUP BY 2, 3</query>
908 <name>usr_messages</name>
909 <report_title>Patron Messages</report_title>
911 <iteration>1</iteration>
912 <heading>Count.Deleted</heading>
913 <query>SELECT COUNT(aum.id), deleted::TEXT FROM m_actor_usr_message_legacy aum GROUP BY 2</query>
917 <name>usr_notes</name>
918 <report_title>Patron Notes</report_title>
920 <iteration>0</iteration>
921 <heading>Count.Public.Migrated</heading>
922 <query>SELECT COUNT(aun.id), pub::TEXT, x_migrate::TEXT FROM m_actor_usr_note_legacy aun GROUP BY 2, 3</query>
926 <name>usr_notes</name>
927 <report_title>Patron Notes</report_title>
929 <iteration>1</iteration>
930 <heading>Count.Public</heading>
931 <query>SELECT COUNT(aun.id), pub::TEXT FROM m_actor_usr_note aun GROUP BY 2</query>
935 <name>usr_stat_cats</name>
936 <report_title>Patron Statistical Categories</report_title>
938 <iteration>0</iteration>
939 <heading>Stat Cat Count.Library.Statistical Category.Migrated</heading>
940 <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>
944 <name>usr_stat_cats</name>
945 <report_title>Patron Statistical Categories</report_title>
947 <iteration>1</iteration>
948 <heading>Stat Cat Count.Library.Statistical Category</heading>
949 <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>
953 <name>usr_stat_cat_entries</name>
954 <report_title>Patron Stat Cat User Entries</report_title>
956 <iteration>0</iteration>
957 <heading>Patron Stat Count.Library.Statistical Category.Migrated</heading>
958 <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>
962 <name>usr_stat_cat_entries</name>
963 <report_title>Patron Stat Cat User Entries</report_title>
965 <iteration>1</iteration>
966 <heading>Patron Stat Count.Library.Statistical Category</heading>
967 <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>
971 <name>usr_thresholds</name>
972 <report_title>Patron Thresholds</report_title>
974 <iteration>0</iteration>
975 <heading>Pateron Group.Org Unit.Penalty.Threshold</heading>
976 <query>SELECT pgt.name, aou.shortname, sp.name, p.threshold FROM permission_grp_penalty_threshold p
977 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
978 ORDER BY 2, 1, 3</query>
982 <name>usr_settings</name>
983 <report_title>Patron Settings</report_title>
985 <iteration>0</iteration>
986 <heading>Count.Setting.Value</heading>
987 <query>SELECT COUNT(*), name, 'User''s Phone' FROM m_actor_usr_setting WHERE name IN ('opac.default_phone') GROUP BY 2, 3
988 UNION ALL SELECT COUNT(*), name, value FROM m_actor_usr_setting WHERE name IN ('opac.hold_notify') GROUP BY 2, 3
989 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
990 WHERE a.name IN ('opac.default_pickup_location','opac.default_search_location') GROUP BY 2, 3 ORDER BY 2, 3;</query>
993 <!-- ACQUISITIONS REPORTS -->
995 <name>fund_count</name>
996 <iteration>0</iteration>
998 <report_title>Migrated Funds</report_title>
999 <heading>Number of Funds.Migrated</heading>
1000 <query>SELECT COUNT(id), x_migrate::TEXT FROM m_acq_fund_legacy GROUP BY 2;</query>
1004 <name>fund_count</name>
1005 <iteration>1</iteration>
1007 <report_title>Migrated Funds</report_title>
1008 <heading>Number of Funds</heading>
1009 <query>SELECT COUNT(id) FROM m_acq_fund;</query>
1013 <name>invoice_count</name>
1014 <iteration>0</iteration>
1016 <report_title>Migrated Invoices</report_title>
1017 <heading>Number of Invoices.Migrated</heading>
1018 <query>SELECT COUNT(id), x_migrate::TEXT FROM m_acq_invoice_legacy GROUP BY 2;</query>
1022 <name>invoice_count</name>
1023 <iteration>1</iteration>
1025 <report_title>Migrated Invoices</report_title>
1026 <heading>Number of Funds</heading>
1027 <query>SELECT COUNT(id) FROM m_acq_invoice;</query>
1030 <!-- SERIALS REPORTS -->
1032 <name>serials_mfhd_count</name>
1034 <iteration>0</iteration>
1035 <report_title>Migrated Serial MFHDs</report_title>
1036 <heading>Number of MFHDs</heading>
1037 <query>SELECT COUNT(id) FROM m_serial_record_entry</query>
1040 <!-- DEDUPE REPORTS -->
1043 <name>dedupe_explain</name>
1045 <file>dedupe_process.asciidoc</file>
1049 <name>dedupe_bib_groups</name>
1051 <iteration>0</iteration>
1052 <report_title>Scoring and Bib Record Groups</report_title>
1053 <heading>Count.Bib Record Groups</heading>
1054 <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)
1055 UNION ALL SELECT (COUNT(DISTINCT incoming_bib)), 'Incoming Bibs With Matches Found' FROM bib_matches
1056 UNION ALL SELECT (COUNT(bre.id) - (SELECT COUNT(DISTINCT incoming_bib) FROM bib_matches)), 'Incoming Bibs With No Match'
1057 FROM biblio.record_entry bre WHERE bre.deleted IS FALSE AND bre.id IN (SELECT eg::BIGINT FROM bib_id_map)
1058 UNION ALL SELECT COUNT(DISTINCT incoming_bib), 'Incoming Bibs Being Merged into Incumbent' FROM bib_matches WHERE incumbent_bib_score >= incoming_bib_score
1059 UNION ALL SELECT COUNT(id), 'Incumbent Bibs With Higher Scores to Incoming' FROM bib_matches WHERE incumbent_bib_score > incoming_bib_score
1060 UNION ALL SELECT COUNT(id), 'Incumbent Bibs With Equal Scores to Incoming' FROM bib_matches WHERE incumbent_bib_score = incoming_bib_score
1061 UNION ALL SELECT COUNT(id), 'Incumbent Bibs With Lower Scores to Incoming' FROM bib_matches WHERE incumbent_bib_score < incoming_bib_score
1066 <name>dedupe_format_count</name>
1068 <iteration>0</iteration>
1069 <report_title>Count of Items Matching By Format</report_title>
1070 <heading>Count.Format(s)</heading>
1071 <query>SELECT COUNT(id), search_formats FROM bib_matches GROUP BY 2 ORDER BY 2;</query>
1075 <name>dedupe_score_ranges</name>
1077 <iteration>0</iteration>
1078 <report_title>Count of Items Matching By Format</report_title>
1079 <heading>Lowest Record Score.Largest Record Score.Record Set</heading>
1080 <query>SELECT MIN(incumbent_bib_score), MAX(incumbent_bib_score), 'Incumbent Records' FROM bib_matches
1081 UNION ALL SELECT MIN(incoming_bib_score), MAX(incoming_bib_score), 'Incoming Records' FROM bib_matches ;
1087 <name>dedupe_sample_set</name>
1089 <iteration>0</iteration>
1090 <report_title>Sample of 20 Matching Dedupe Record Sets</report_title>
1091 <heading>Bib Being Merged Into.Bib Being Merged</heading>
1092 <query>SELECT incumbent_bib, incoming_bib FROM bib_matches WHERE incumbent_bib_score >= incoming_bib_score LIMIT 20 ;
1096 <!-- NOTICES REPORTS -->
1099 <name>notices_overview</name>
1101 <iteration>0</iteration>
1102 <report_title>Action Triggers Setup for Notices</report_title>
1103 <heading>ID.Active.Owner.Name</heading>
1104 <query>SELECT ed.id, ed.active, aou.shortname, ed.name
1105 FROM m_action_trigger.event_definition ed
1106 JOIN actor.org_unit aou ON aou.id = ed.owner
1107 WHERE ed.owner IN (SELECT DISTINCT home_ou FROM m_actor_usr)
1108 OR ed.owner IN (SELECT DISTINCT parent_ou FROM actor.org_unit WHERE id in (SELECT DISTINCT home_ou FROM m_actor_usr));
1113 <name>notices_count</name>
1115 <iteration>0</iteration>
1116 <report_title>Count of Notices Run with State</report_title>
1117 <heading>Count of Notices.State.ID.Owner.Name</heading>
1118 <query>SELECT COUNT(ate.id), ate.state, ed.id, aou.shortname, ed.name
1119 FROM m_action_trigger.event_definition ed
1120 JOIN actor.org_unit aou ON aou.id = ed.owner
1121 JOIN m_action_trigger.event ate ON ate.event_def = ed.id
1122 WHERE ed.owner IN (SELECT DISTINCT home_ou FROM m_actor_usr)
1123 OR ed.owner IN (SELECT DISTINCT parent_ou FROM actor.org_unit WHERE id in (SELECT DISTINCT home_ou FROM m_actor_usr))