mig-xml/evergreen_staged_report.xml

   1 <reports_file>
   2     <function>
   3         <name>find_cmm</name>
   4         <drop>DROP FUNCTION IF EXISTS find_cmm(BIGINT)</drop>
   5         <create>
   6             CREATE OR REPLACE FUNCTION find_cmm(circ_id BIGINT)
   7                 RETURNS SETOF INTEGER[]
   8                 LANGUAGE plpgsql
   9             AS $function$
  10             DECLARE
  11                  aou     INTEGER;
  12                  ac      INTEGER;
  13                  au      INTEGER;
  14                  r       INTEGER[];
  15             BEGIN
  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;
  19
  20                 FOR r IN SELECT buildrows FROM action.find_circ_matrix_matchpoint(aou,ac,au,FALSE)
  21                     LOOP
  22                         RETURN NEXT r;
  23                     END LOOP;
  24                 RETURN;
  25             END
  26         $function$</create>
  27     </function>
  28
  29
  30 <!-- sample reports entry
  31     <report>
  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>
  39     </report>
  40 -->
  41
  42     <!-- CIRC REPORTS -->
  43     <report>
  44         <name>circ_count</name>
  45         <tag>circs</tag>
  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>
  51     </report>
  52
  53     <report>
  54         <name>circ_count_unmigrated</name>
  55         <tag>circs</tag>
  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>
  61     </report>
  62
  63     <report>
  64         <name>circ_count</name>
  65         <tag>circs</tag>
  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>
  70     </report>
  71 <!-- intent is to add a report by circulated and item status
  72     <report>
  73         <name>circ_count_by_item_status</name>
  74         <tag>circs</tag>
  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>
  79     </report>
  80 -->
  81      <report>
  82         <name>circ_by_orgunit</name>
  83         <tag>circs</tag>
  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>
  88     </report>
  89
  90      <report>
  91         <name>circ_by_orgunit</name>
  92         <tag>circs</tag>
  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>
  97     </report>
  98
  99      <report>
 100         <name>circs_by_duration</name>
 101         <tag>circs</tag>
 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>
 106     </report>
 107
 108      <report>
 109         <name>circs_by_duration</name>
 110         <tag>circs</tag>
 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>
 115     </report>
 116
 117      <report>
 118         <name>circs_by_usrgroup</name>
 119         <tag>circs</tag>
 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>
 124     </report>
 125
 126      <report>
 127         <name>circs_by_usrgroup</name>
 128         <tag>circs</tag>
 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>
 133     </report>
 134
 135      <report>
 136         <name>circs_by_circmod</name>
 137         <tag>circs</tag>
 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>
 142     </report>
 143
 144      <report>
 145         <name>circs_by_circmod</name>
 146         <tag>circs</tag>
 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>
 152     </report>
 153
 154      <report>
 155         <name>circs_by_orgunit</name>
 156         <tag>circs</tag>
 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>
 161     </report>
 162
 163      <report>
 164         <name>circs_by_orgunit</name>
 165         <tag>circs</tag>
 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>
 170     </report>
 171
 172      <report>
 173         <name>non_cat_circs</name>
 174         <tag>circs</tag>
 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>
 179     </report>
 180
 181      <report>
 182         <name>non_cat_circs</name>
 183         <tag>circs</tag>
 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>
 188     </report>
 189
 190      <report>
 191         <name>in_house</name>
 192         <tag>circs</tag>
 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>
 197     </report>
 198
 199      <report>
 200         <name>in_house</name>
 201         <tag>circs</tag>
 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>
 206     </report>
 207
 208      <report>
 209         <name>circs_missing_rules</name>
 210         <tag>circs</tag>
 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
 220         </query>
 221     </report>
 222
 223      <report>
 224         <name>circ_open_by_item_status</name>
 225         <tag>circs</tag>
 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>
 230     </report>
 231
 232     <!-- HOLDS REPORTS -->
 233
 234      <report>
 235         <name>holds</name>
 236         <tag>holds</tag>
 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 &lt; 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 &gt; now()) AND cancel_time IS NULL AND fulfillment_time IS NULL GROUP BY 3</query>
 241     </report>
 242
 243      <report>
 244         <name>holds_bytype</name>
 245         <tag>holds</tag>
 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>
 250     </report>
 251
 252      <report>
 253         <name>transit_open_by_item_status</name>
 254         <tag>holds</tag>
 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>
 259     </report>
 260
 261      <report>
 262         <name>transit_copies_by_status</name>
 263         <tag>holds</tag>
 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>
 268     </report>
 269
 270      <report>
 271         <name>hold_copies_by_status</name>
 272         <tag>holds</tag>
 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>
 277     </report>
 278
 279       <report>
 280         <name>hold_depth</name>
 281         <tag>holds</tag>
 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>
 286     </report>
 287
 288       <!-- finding title matched holds where there are duplicates, this will table match for legacy but column won't be present for all  -->
 289       <report>
 290         <name>duplicate_title_matching_holds</name>
 291         <tag>holds</tag>
 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>
 296     </report>
 297
 298       <report>
 299         <name>duplicate_title_matching_holds_w_zero</name>
 300         <tag>holds</tag>
 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>
 305     </report>
 306
 307       <report>
 308         <name>duplicate_title_matching_holds_w_greater_than_1</name>
 309         <tag>holds</tag>
 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>
 314     </report>
 315
 316     <!-- ASSET REPORTS -->
 317
 318     <report>
 319         <name>asset_copy_count</name>
 320         <report_title>Count of Copies by Library</report_title>
 321         <tag>assets</tag>
 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>
 325     </report>
 326
 327     <report>
 328         <name>asset_deleted_copies</name>
 329         <report_title>Deleted Copies</report_title>
 330         <tag>assets</tag>
 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>
 334     </report>
 335
 336     <report>
 337         <name>asset_copies_by_status</name>
 338         <report_title>Copies by Status</report_title>
 339         <tag>assets</tag>
 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>
 343     </report>
 344
 345     <report>
 346         <name>asset_precat</name>
 347         <report_title>Precats (AKA Copies Without Bibs)</report_title>
 348         <tag>assets</tag>
 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>
 352     </report>
 353
 354     <report>
 355         <name>asset_circ_mod_copies_count</name>
 356         <report_title>Copies by Circulation Modifier</report_title>
 357         <tag>assets</tag>
 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>
 361     </report>
 362
 363     <report>
 364         <name>asset_copy_notes</name>
 365         <report_title>Copy Notes</report_title>
 366         <tag>assets</tag>
 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>
 370     </report>
 371
 372     <report>
 373         <name>asset_copy_notes</name>
 374         <report_title>Copy Notes</report_title>
 375         <tag>assets</tag>
 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>
 379     </report>
 380
 381    <report>
 382         <name>asset_vols_by_lib</name>
 383         <report_title>Volumes by Library</report_title>
 384         <tag>assets</tag>
 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>
 388     </report>
 389
 390    <report>
 391         <name>asset_vols_by_lib</name>
 392         <report_title>Volumes by Library</report_title>
 393         <tag>assets</tag>
 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>
 397     </report>
 398     <!--
 399    <report>
 400         <name>asset_cops_by_loc</name>
 401         <report_title>Copies by Location</report_title>
 402         <tag>assets</tag>
 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>
 406     </report>
 407    -->
 408    <report>
 409         <name>asset_cops_by_loc_and_org</name>
 410         <report_title>Copies by Location</report_title>
 411         <tag>assets</tag>
 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>
 415     </report>
 416
 417    <report>
 418         <name>asset_cops_w_loc_one</name>
 419         <report_title>Copies with a Location of Stacks</report_title>
 420         <tag>assets</tag>
 421         <iteration>0</iteration>
 422         <heading>Barcode</heading>
 423         <query>SELECT barcode FROM asset_copy_legacy WHERE location = 1 AND x_migrate</query>
 424     </report>
 425
 426     <!-- report>
 427         <name>asset_barcode_lengths</name>
 428         <report_title>Barcode Lengths by Library</report_title>
 429         <tag>assets</tag>
 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>
 433     </report -->
 434
 435     <report>
 436         <name>asset_no_barcode</name>
 437         <report_title>Items Without Barcodes</report_title>
 438         <tag>assets</tag>
 439         <iteration>0</iteration>
 440         <heading>Assigned Barcode</heading>
 441         <query>SELECT barcode FROM asset_copy_legacy WHERE barcode ~* 'no_barocde' AND x_migrate</query>
 442     </report>
 443
 444     <report>
 445         <name>asset_barcode_patterns</name>
 446         <report_title>Common Barcode Starting Patterns</report_title>
 447         <tag>assets</tag>
 448         <iteration>0</iteration>
 449         <heading>Count of Barcodes (greater than 10).Left 60% of Characters</heading>
 450         <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>
 451     </report>
 452
 453     <report>
 454         <name>asset_barcode_incumbent_collisions</name>
 455         <report_title>Copy Barcode Incumbent Collisions</report_title>
 456         <tag>assets</tag>
 457         <iteration>0</iteration>
 458         <heading>Collision Count</heading>
 459         <query>SELECT COUNT(id) FROM asset_copy_legacy WHERE x_migrate = TRUE AND barcode ~* 'collision' and barcode ~* 'incumbent'</query>
 460         <note>Incumbent collisions are those where the migrated barcodes collide with existing barcodes in the database.</note>
 461     </report>
 462
 463     <report>
 464         <name>asset_barcode_incumbent_collisions</name>
 465         <report_title>Copy Barcode Incumbent Collisions</report_title>
 466         <tag>assets</tag>
 467         <iteration>1</iteration>
 468         <heading>Collision Count</heading>
 469         <query>SELECT COUNT(id) FROM asset_copy WHERE barcode ~* 'collision' and barcode ~* 'incumbent'</query>
 470         <note>Incumbent collisions are those where the migrated barcodes collide with existing barcodes in the database.</note>
 471     </report>
 472
 473     <report>
 474         <name>asset_barcode_internal_collisions</name>
 475         <report_title>Copy Barcode Internal Collisions</report_title>
 476         <tag>assets</tag>
 477         <iteration>0</iteration>
 478         <heading>Collision Count</heading>
 479         <query>SELECT COUNT(id) FROM asset_copy_legacy WHERE x_migrate = TRUE AND barcode ~* 'collision' and barcode ~* 'internal'</query>
 480         <note>Internal collisions are those where the migrated barcodes have conflicts within their list of barcodes.</note>
 481     </report>
 482
 483     <report>
 484         <name>asset_barcode_internal_collisions</name>
 485         <report_title>Copy Barcode Internal Collisions</report_title>
 486         <tag>assets</tag>
 487         <iteration>1</iteration>
 488         <heading>Collision Count</heading>
 489         <query>SELECT COUNT(id) FROM asset_copy WHERE barcode ~* 'collision' and barcode ~* 'internal'</query>
 490         <note>Internal collisions are those where the migrated barcodes have conflicts within their list of barcodes.</note>
 491     </report>
 492
 493     <report>
 494         <name>asset_barcode_collisions_shortlist</name>
 495         <report_title>Copy Barcode Collisions (first 20)</report_title>
 496         <tag>assets</tag>
 497         <iteration>0</iteration>
 498         <heading>Collision List</heading>
 499         <query>SELECT ac.barcode FROM asset_copy_legacy ac WHERE ac.barcode ~* 'collision' ORDER BY 1 LIMIT 20</query>
 500         <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>
 501     </report>
 502
 503     <report>
 504         <name>asset_barcode_collisions_shortlist</name>
 505         <report_title>Copy Barcode Collisions (first 20)</report_title>
 506         <tag>assets</tag>
 507         <iteration>1</iteration>
 508         <heading>Collision List</heading>
 509         <query>SELECT ac.barcode FROM asset_copy ac WHERE ac.barcode ~* 'collision' ORDER BY 1 LIMIT 20</query>
 510         <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>
 511     </report>
 512
 513     <report>
 514         <name>asset_barcode_collision_patterns</name>
 515         <report_title>Common Copy Barcode Collision Patterns</report_title>
 516         <tag>assets</tag>
 517         <iteration>0</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_legacy ac WHERE barcode ~* 'collision' GROUP BY 2 HAVING COUNT(ac.id) > 10 ORDER BY 2</query>
 520     </report>
 521
 522     <report>
 523         <name>asset_barcode_collision_patterns</name>
 524         <report_title>Common Copy Barcode Collision Patterns</report_title>
 525         <tag>assets</tag>
 526         <iteration>1</iteration>
 527         <heading>Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters</heading>
 528         <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>
 529     </report>
 530
 531     <report>
 532          <name>asset_stat_cats</name>
 533         <report_title>Copy Statistical Categories</report_title>
 534         <tag>assets</tag>
 535         <iteration>0</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_legacy ac_sc JOIN actor.org_unit aou ON aou.id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3</query>
 538     </report>
 539
 540     <report>
 541          <name>asset_stat_cats</name>
 542         <report_title>Copy Statistical Categories</report_title>
 543         <tag>assets</tag>
 544         <iteration>1</iteration>
 545         <heading>Stat Cat Count.Library.Statistical Category</heading>
 546         <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>
 547     </report>
 548
 549     <report>
 550         <name>asset_stat_cat_entries</name>
 551         <report_title>Copy Stat Cat User Entries</report_title>
 552         <tag>assets</tag>
 553         <iteration>0</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_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>
 556     </report>
 557
 558     <report>
 559         <name>asset_stat_cat_entries</name>
 560         <report_title>Copy Stat Cat User Entries</report_title>
 561         <tag>assets</tag>
 562         <iteration>1</iteration>
 563         <heading>Copy Stat Count.Library.Statistical Category</heading>
 564         <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.
 565 id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3</query>
 566     </report>
 567
 568     <report>
 569          <name>asset_copy_tags</name>
 570         <report_title>Copy Tags</report_title>
 571         <tag>assets</tag>
 572         <iteration>0</iteration>
 573         <heading>Tag Count.Copy Tag Type.Copy Tag Label.Staff Note.Public</heading>
 574         <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>
 575     </report>
 576
 577     <report>
 578          <name>asset_copy_alerts</name>
 579         <report_title>Copy Alerts</report_title>
 580         <tag>assets</tag>
 581         <iteration>0</iteration>
 582         <heading>Alert Count.Alert Type</heading>
 583                 <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>
 584     </report>
 585
 586     <!-- MONEY REPORTS -->
 587
 588     <report>
 589         <name>money_billing_voided</name>
 590         <report_title>Bills Voided And Not</report_title>
 591         <tag>money</tag>
 592         <iteration>0</iteration>
 593         <heading>Count.Voided.Sum.Migrated</heading>
 594         <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>
 595     </report>
 596
 597     <report>
 598         <name>money_billing_voided</name>
 599         <report_title>Bills Voided And Not</report_title>
 600         <tag>money</tag>
 601         <iteration>1</iteration>
 602         <heading>Count.Voided.Sum</heading>
 603         <query>SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM money_billing a GROUP BY 2 ORDER BY 2, 3</query>
 604     </report>
 605
 606     <report>
 607         <name>money_billing_by_type</name>
 608         <report_title>Bills by Type</report_title>
 609         <tag>money</tag>
 610         <iteration>0</iteration>
 611         <heading>Count.Billing Type.Migrated</heading>
 612         <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>
 613     </report>
 614
 615     <report>
 616         <name>money_billing_by_type</name>
 617         <report_title>Bills by Type</report_title>
 618         <tag>money</tag>
 619         <iteration>1</iteration>
 620         <heading>Count.Billing Type</heading>
 621         <query>SELECT COUNT(a.id), a.billing_type FROM money_billing a GROUP BY 2 ORDER BY 2</query>
 622     </report>
 623
 624     <report>
 625         <name>money_cash_payment</name>
 626         <report_title>Cash Payments</report_title>
 627         <tag>money</tag>
 628         <iteration>0</iteration>
 629         <heading>Count.Voided.Sum.Migrated</heading>
 630         <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>
 631     </report>
 632
 633     <report>
 634         <name>money_cash_payment</name>
 635         <report_title>Cash Payments</report_title>
 636         <tag>money</tag>
 637         <iteration>1</iteration>
 638         <heading>Count.Voided.Sum</heading>
 639         <query>SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM money_cash_payment a GROUP BY 2 ORDER BY 2</query>
 640     </report>
 641
 642     <report>
 643         <name>money_check_payment</name>
 644         <report_title>Check Payments</report_title>
 645         <tag>money</tag>
 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_check_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4</query>
 649     </report>
 650
 651     <report>
 652         <name>money_forgive_payment</name>
 653         <report_title>Forgive Payments</report_title>
 654         <tag>money</tag>
 655         <iteration>0</iteration>
 656         <heading>Count.Voided.Sum.Migrated</heading>
 657         <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>
 658     </report>
 659
 660     <report>
 661         <name>money_forgive_payment</name>
 662         <report_title>Forgive Payments</report_title>
 663         <tag>money</tag>
 664         <iteration>1</iteration>
 665         <heading>Count.Voided.Sum</heading>
 666         <query>SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM money_forgive_paymen a GROUP BY 2 ORDER BY 2</query>
 667     </report>
 668
 669     <report>
 670         <name>money_goods_payment</name>
 671         <report_title>Goods Payments</report_title>
 672         <tag>money</tag>
 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_goods_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4</query>
 676     </report>
 677
 678     <report>
 679         <name>money_work_payment</name>
 680         <report_title>Work Payments</report_title>
 681         <tag>money</tag>
 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_work_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4</query>
 685     </report>
 686
 687     <report>
 688         <name>money_credit_card_payment</name>
 689         <report_title>Credit Card Payments</report_title>
 690         <tag>money</tag>
 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_card_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4</query>
 694     </report>
 695
 696     <report>
 697         <name>money_credit_payment</name>
 698         <report_title>Credit Payments</report_title>
 699         <tag>money</tag>
 700         <iteration>0</iteration>
 701         <heading>Count.Voided.Sum.Migrated</heading>
 702         <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>
 703     </report>
 704
 705
 706     <!-- BIBS REPORTS -->
 707
 708     <report>
 709         <name>bibs_loaded</name>
 710         <report_title>Loaded Bibliographic Records</report_title>
 711         <tag>bibs</tag>
 712         <iteration>0</iteration>
 713         <heading>Count</heading>
 714         <query>SELECT COUNT(bre.id) FROM biblio_record_entry_legacy bre WHERE id IN (SELECT id FROM biblio.record_entry) ORDER BY 1</query>
 715     </report>
 716
 717     <report>
 718         <name>bibs_loaded</name>
 719         <report_title>Loaded Bibliographic Records</report_title>
 720         <tag>bibs</tag>
 721         <iteration>1</iteration>
 722         <heading>Count</heading>
 723         <query>SELECT COUNT(bre.id) FROM biblio_record_entry bre WHERE id IN (SELECT id FROM biblio.record_entry) ORDER BY 1</query>
 724     </report>
 725
 726     <report>
 727         <name>bibswovolumes</name>
 728         <report_title>Bibliographic Records Without Volumes</report_title>
 729         <tag>bibs</tag>
 730         <iteration>0</iteration>
 731         <heading>Count</heading>
 732         <query>SELECT COUNT(id) FROM biblio_record_entry where id NOT IN (SELECT DISTINCT record FROM asset_call_number)</query>
 733                 <note>These records would not have been loaded.</note>
 734     </report>
 735
 736     <report>
 737         <name>bibswuri</name>
 738         <report_title>Bibliographic Records With 856$9s</report_title>
 739         <tag>bibs</tag>
 740         <iteration>0</iteration>
 741         <heading>Count</heading>
 742         <query>SELECT COUNT(id) FROM biblio_record_entry where id IN (SELECT record FROM asset.call_number WHERE label ~* '##URI##')</query>
 743     </report>
 744
 745         <report>
 746                 <name>bibsff</name>
 747                 <report_title>Bibliographic Records with Adjusted Fixed Fields</report_title>
 748                 <tag>bibs</tag>
 749                 <iteration>0</iteration>
 750                 <heading>Count.Original Search Format.New Search Format</heading>
 751                 <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>
 752         </report>
 753
 754     <report>
 755         <name>bibs_notes</name>
 756         <report_title>Bib Record Notes</report_title>
 757         <tag>bibs</tag>
 758         <iteration>0</iteration>
 759         <heading>Count.Migrated</heading>
 760         <query>SELECT COUNT(b.id), b.x_migrate::TEXT FROM biblio_record_note_legacy b GROUP BY 2</query>
 761     </report>
 762
 763     <report>
 764         <name>bibs_notes</name>
 765         <report_title>Bib Record Notes</report_title>
 766         <tag>bibs</tag>
 767         <iteration>1</iteration>
 768         <heading>Count</heading>
 769         <query>SELECT COUNT(b.id) FROM biblio_record_note b</query>
 770     </report>
 771
 772     <report>
 773         <name>bibs_peers</name>
 774         <report_title>Peer Bib Copies</report_title>
 775         <tag>bibs</tag>
 776         <iteration>0</iteration>
 777         <heading>Count.Migrated</heading>
 778         <query>SELECT COUNT(b.id), b.x_migrate::TEXT FROM biblio_peer_bib_copy_map_legacy b GROUP BY 2</query>
 779     </report>
 780
 781     <report>
 782         <name>bibs_peers</name>
 783         <report_title>Peer Bib Copies</report_title>
 784         <tag>bibs</tag>
 785         <iteration>1</iteration>
 786         <heading>Count</heading>
 787         <query>SELECT COUNT(b.id) FROM biblio_peer_bib_copy_map b</query>
 788     </report>
 789
 790     <report>
 791         <name>bibs_parts</name>
 792         <report_title>Monograph Parts</report_title>
 793         <tag>bibs</tag>
 794         <iteration>0</iteration>
 795         <heading>Count.Migrated</heading>
 796         <query>SELECT COUNT(b.id), b.x_migrate::TEXT FROM biblio_monograph_part_legacy b GROUP BY 2</query>
 797     </report>
 798
 799     <report>
 800         <name>bibs_parts</name>
 801         <report_title>Monograph Parts</report_title>
 802         <tag>bibs</tag>
 803         <iteration>1</iteration>
 804         <heading>Count</heading>
 805         <query>SELECT COUNT(b.id) FROM biblio_monograph_part b</query>
 806     </report>
 807
 808     <report>
 809         <name>bib_merges</name>
 810         <report_title>Bibliographic Merge Count</report_title>
 811         <tag>bibs</tag>
 812         <iteration>0</iteration>
 813         <heading>Records Merged.Incumbent Records Merged Into</heading>
 814         <query>SELECT SUM(array_length(records,1)), COUNT(*) FROM groups</query>
 815     </report>
 816
 817     <report>
 818         <name>bib_merges_searchformat</name>
 819         <report_title>Bibliographic Merges By Search Format</report_title>
 820         <tag>bibs</tag>
 821         <iteration>0</iteration>
 822         <heading>Count.Item Type</heading>
 823         <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>
 824     </report>
 825
 826         <report>
 827         <name>bibs_not_loaded</name>
 828         <report_title>Bibliographic Records That Failed to Load</report_title>
 829         <tag>bibs</tag>
 830         <iteration>0</iteration>
 831         <heading>Title.Author.Control Number</heading>
 832         <query>SELECT LEFT(ARRAY_TO_STRING(oils_xpath( '//*[@tag="245"]/*[@code="a"]/text()', a.marc),''),50) AS "Title",
 833                                 LEFT(ARRAY_TO_STRING(oils_xpath( '//*[@tag="100"]/*[@code="a"]/text()', a.marc),''),50) AS "Author",
 834                                 ARRAY_TO_STRING(oils_xpath( '//*[@tag="001"]/text()', a.marc),'') AS "001"
 835                                 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
 836                 </query>
 837     </report>
 838
 839
 840     <!-- ACTORS REPORTS -->
 841
 842     <report>
 843         <name>usrsbyorg</name>
 844         <report_title>Patrons by Home Org</report_title>
 845         <tag>actors</tag>
 846         <iteration>0</iteration>
 847         <heading>Count.Library.Deleted.Migrated</heading>
 848         <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>
 849     </report>
 850
 851     <report>
 852         <name>usrsbypgt</name>
 853         <report_title>Patrons by Permission Group</report_title>
 854         <tag>actors</tag>
 855         <iteration>0</iteration>
 856         <heading>Count.Permission Group.Migrated</heading>
 857         <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>
 858     </report>
 859
 860     <report>
 861         <name>active_usrs</name>
 862         <report_title>Patron by Active Status</report_title>
 863         <tag>actors</tag>
 864         <iteration>0</iteration>
 865         <heading>Count of Users.Active.Migrated</heading>
 866         <query>SELECT COUNT(id), active::TEXT, x_migrate::TEXT FROM actor_usr_legacy GROUP BY 2, 3</query>
 867     </report>
 868
 869     <report>
 870         <name>active_usrs</name>
 871         <report_title>Patrons by Active Status</report_title>
 872         <tag>actors</tag>
 873         <iteration>1</iteration>
 874         <heading>Count of Users.Active</heading>
 875         <query>SELECT COUNT(id), active::TEXT FROM actor_usr GROUP BY 2</query>
 876     </report>
 877
 878     <report>
 879         <name>active_usr_barcodes</name>
 880         <report_title>Patron Barcodes by Active Status</report_title>
 881         <tag>actors</tag>
 882         <iteration>0</iteration>
 883         <heading>Count of Barcodes.Active.Migrated</heading>
 884         <query>SELECT COUNT(id), active::TEXT, x_migrate::TEXT FROM actor_card_legacy GROUP BY 2, 3</query>
 885     </report>
 886
 887     <report>
 888         <name>active_usr_barcodes</name>
 889         <report_title>Patron Barcodes by Active Status</report_title>
 890         <tag>actors</tag>
 891         <iteration>1</iteration>
 892         <heading>Count of Barcodes.Active</heading>
 893         <query>SELECT COUNT(id), active::TEXT FROM actor_card GROUP BY 2</query>
 894     </report>
 895
 896     <!-- report>
 897         <name>usr_barcode_lengths</name>
 898         <report_title>Barcode Lengths by Library</report_title>
 899         <tag>actors</tag>
 900         <iteration>0</iteration>
 901         <heading>Count of Barcode.Barcode Length.Library</heading>
 902         <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>
 903     </report -->
 904
 905     <!-- report>
 906         <name>usr_barcode_lengths</name>
 907         <report_title>Barcode Lengths by Library</report_title>
 908         <tag>actors</tag>
 909         <iteration>1</iteration>
 910         <heading>Count of Barcode.Barcode Length.Library</heading>
 911         <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>
 912     </report -->
 913
 914     <report>
 915         <name>usr_barcode_patterns</name>
 916         <report_title>Common Barcode Starting Patterns</report_title>
 917         <tag>actors</tag>
 918         <iteration>0</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_legacy acard WHERE acard.x_migrate = TRUE GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2</query>
 921     </report>
 922
 923     <report>
 924         <name>usr_barcode_patterns</name>
 925         <report_title>Common Barcode Starting Patterns</report_title>
 926         <tag>actors</tag>
 927         <iteration>1</iteration>
 928         <heading>Count of Barcodes (greater than 10).Left 60% of Characters</heading>
 929         <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>
 930     </report>
 931
 932    <report>
 933         <name>usr_barcode_incoming_collisions</name>
 934         <report_title>Incoming Patron Barcode Collisions</report_title>
 935         <tag>actors</tag>
 936         <iteration>0</iteration>
 937         <heading>Collision Count</heading>
 938         <query>SELECT COUNT(acard.id) FROM actor_card_legacy acard WHERE barcode ~* 'collision' AND x_migrate = TRUE</query>
 939     </report>
 940
 941     <report>
 942         <name>usr_barcode_incoming_collisions</name>
 943         <report_title>Incoming Patron Barcode Collisions</report_title>
 944         <tag>actors</tag>
 945         <iteration>1</iteration>
 946         <heading>Collision Count</heading>
 947         <query>SELECT COUNT(acard.id) FROM actor_card acard WHERE barcode ~* 'collision'</query>
 948     </report>
 949
 950         <report>
 951         <name>usr_barcode_incumbent_collisions</name>
 952         <report_title>Incumbent Patron Barcode Collisions</report_title>
 953         <tag>actors</tag>
 954         <iteration>0</iteration>
 955         <heading>Collision Count</heading>
 956         <query>SELECT COUNT(acard.id) FROM actor_card_legacy acard WHERE barcode ~* '^x_' AND x_migrate = TRUE</query>
 957         </report>
 958
 959         <report>
 960         <name>usr_barcode_incumbent_collisions</name>
 961         <report_title>Incumbent Patron Barcode Collisions</report_title>
 962         <tag>actors</tag>
 963         <iteration>1</iteration>
 964         <heading>Collision Count</heading>
 965         <query>SELECT COUNT(acard.id) FROM actor_card acard WHERE barcode ~* '^x_'</query>
 966         </report>
 967
 968     <report>
 969         <name>usr_barcode_collision_shortlist</name>
 970         <report_title>Patron Barcode Collisions (first 20)</report_title>
 971         <tag>actors</tag>
 972         <iteration>0</iteration>
 973         <heading>Collision List</heading>
 974         <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>
 975         <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>
 976     </report>
 977
 978     <report>
 979         <name>usr_barcode_collision_shortlist</name>
 980         <report_title>Patron Barcode Collisions (first 20)</report_title>
 981         <tag>actors</tag>
 982         <iteration>1</iteration>
 983         <heading>Collision List</heading>
 984         <query>SELECT acard.barcode FROM actor_card acard WHERE (acard.barcode ~* 'collision' OR acard.barcode ~* '^x_') ORDER BY 1 LIMIT 20</query>
 985         <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>
 986     </report>
 987
 988     <report>
 989         <name>usr_barcode_collision_patterns</name>
 990         <report_title>Common Patron Barcode Collision Patterns</report_title> a.x_migrate
 991         <tag>actors</tag>
 992         <iteration>0</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_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>
 995     </report>
 996
 997     <report>
 998         <name>usr_barcode_collision_patterns</name>
 999         <report_title>Common Patron Barcode Collision Patterns</report_title> a.x_migrate
1000         <tag>actors</tag>
1001         <iteration>1</iteration>
1002         <heading>Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters</heading>
1003         <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>
1004     </report>
1005
1006     <report>
1007         <name>usr_addressses_status</name>
1008         <report_title>Patron Addresses by Valid Status</report_title>
1009         <tag>actors</tag>
1010         <iteration>0</iteration>
1011         <heading>Count.Valid.Migrated</heading>
1012         <query>SELECT COUNT(aua.id), valid::TEXT, x_migrate::TEXT FROM actor_usr_address_legacy aua GROUP BY 2, 3</query>
1013     </report>
1014
1015     <report>
1016         <name>usr_addressses_status</name>
1017         <report_title>Patron Addresses by Valid Status</report_title>
1018         <tag>actors</tag>
1019         <iteration>1</iteration>
1020         <heading>Count.Valid</heading>
1021         <query>SELECT COUNT(aua.id), valid::TEXT FROM actor_usr_address aua GROUP BY 2</query>
1022     </report>
1023
1024     <report>
1025         <name>usr_addresses_pending</name>
1026         <report_title>Patron Addresses by Pending Status</report_title>
1027         <tag>actors</tag>
1028         <iteration>0</iteration>
1029         <heading>Count of Addresses.Pending.Migrated</heading>
1030         <query>SELECT COUNT(aua.id), pending::TEXT, x_migrate::TEXT FROM actor_usr_address_legacy aua GROUP BY 2, 3</query>
1031     </report>
1032
1033     <report>
1034         <name>usr_addresses_pending</name>
1035         <report_title>Patron Addresses by Pending Status</report_title>
1036         <tag>actors</tag>
1037         <iteration>1</iteration>
1038         <heading>Count of Addresses.Pending</heading>
1039         <query>SELECT COUNT(aua.id), pending::TEXT FROM actor_usr_address aua GROUP BY 2</query>
1040     </report>
1041
1042     <report>
1043         <name>usr_messages</name>
1044         <report_title>Patron Messages</report_title>
1045         <tag>actors</tag>
1046         <iteration>0</iteration>
1047         <heading>Count.Deleted.Migrated</heading>
1048         <query>SELECT COUNT(aum.id), deleted::TEXT, x_migrate::TEXT FROM actor_usr_message_legacy aum GROUP BY 2, 3</query>
1049     </report>
1050
1051     <report>
1052         <name>usr_messages</name>
1053         <report_title>Patron Messages</report_title>
1054         <tag>actors</tag>
1055         <iteration>1</iteration>
1056         <heading>Count.Deleted</heading>
1057         <query>SELECT COUNT(aum.id), deleted::TEXT FROM actor_usr_message_legacy aum GROUP BY 2</query>
1058     </report>
1059
1060     <report>
1061         <name>usr_notes</name>
1062         <report_title>Patron Notes</report_title>
1063         <tag>actors</tag>
1064         <iteration>0</iteration>
1065         <heading>Count.Public.Migrated</heading>
1066         <query>SELECT COUNT(aun.id), pub::TEXT, x_migrate::TEXT FROM actor_usr_note_legacy aun GROUP BY 2, 3</query>
1067     </report>
1068
1069     <report>
1070         <name>usr_notes</name>
1071         <report_title>Patron Notes</report_title>
1072         <tag>actors</tag>
1073         <iteration>1</iteration>
1074         <heading>Count.Public</heading>
1075         <query>SELECT COUNT(aun.id), pub::TEXT FROM actor_usr_note aun GROUP BY 2</query>
1076     </report>
1077
1078     <report>
1079          <name>usr_stat_cats</name>
1080         <report_title>Patron Statistical Categories</report_title>
1081         <tag>actors</tag>
1082         <iteration>0</iteration>
1083         <heading>Stat Cat Count.Library.Statistical Category.Migrated</heading>
1084         <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>
1085     </report>
1086
1087     <report>
1088          <name>usr_stat_cats</name>
1089         <report_title>Patron Statistical Categories</report_title>
1090         <tag>actors</tag>
1091         <iteration>1</iteration>
1092         <heading>Stat Cat Count.Library.Statistical Category</heading>
1093         <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>
1094     </report>
1095
1096     <report>
1097         <name>usr_stat_cat_entries</name>
1098         <report_title>Patron Stat Cat User Entries</report_title>
1099         <tag>actors</tag>
1100         <iteration>0</iteration>
1101         <heading>Patron Stat Count.Library.Statistical Category.Migrated</heading>
1102         <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>
1103     </report>
1104
1105     <report>
1106         <name>usr_stat_cat_entries</name>
1107         <report_title>Patron Stat Cat User Entries</report_title>
1108         <tag>actors</tag>
1109         <iteration>1</iteration>
1110         <heading>Patron Stat Count.Library.Statistical Category</heading>
1111         <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>
1112     </report>
1113
1114     <report>
1115         <name>usr_thresholds</name>
1116         <report_title>Patron Thresholds</report_title>
1117         <tag>actors</tag>
1118         <iteration>0</iteration>
1119         <heading>Pateron Group.Org Unit.Penalty.Threshold</heading>
1120         <query>SELECT pgt.name, aou.shortname, sp.name, p.threshold FROM permission_grp_penalty_threshold p
1121                 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
1122                 ORDER BY 2, 1, 3</query>
1123     </report>
1124
1125     <report>
1126         <name>usr_settings</name>
1127         <report_title>Patron Settings</report_title>
1128         <tag>actors</tag>
1129         <iteration>0</iteration>
1130         <heading>Count.Setting.Value</heading>
1131         <query>SELECT COUNT(*), name, 'User''s Phone' FROM actor_usr_setting WHERE name IN ('opac.default_phone') GROUP BY 2, 3
1132                         UNION ALL SELECT COUNT(*), name, value FROM actor_usr_setting WHERE name IN ('opac.hold_notify') GROUP BY 2, 3
1133                         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
1134                                 WHERE a.name IN ('opac.default_pickup_location','opac.default_search_location') GROUP BY 2, 3 ORDER BY 2, 3;</query>
1135     </report>
1136
1137     <!-- ACQUISITIONS REPORTS -->
1138     <report>
1139         <name>fund_count</name>
1140         <iteration>0</iteration>
1141         <tag>acq</tag>
1142         <report_title>Migrated Funds</report_title>
1143         <heading>Number of Funds.Migrated</heading>
1144         <query>SELECT COUNT(id), x_migrate::TEXT FROM acq_fund_legacy GROUP BY 2;</query>
1145     </report>
1146
1147     <report>
1148         <name>fund_count</name>
1149         <iteration>1</iteration>
1150         <tag>acq</tag>
1151         <report_title>Migrated Funds</report_title>
1152         <heading>Number of Funds</heading>
1153         <query>SELECT COUNT(id) FROM acq_fund;</query>
1154     </report>
1155
1156     <report>
1157         <name>invoice_count</name>
1158         <iteration>0</iteration>
1159         <tag>acq</tag>
1160         <report_title>Migrated Invoices</report_title>
1161         <heading>Number of Invoices.Migrated</heading>
1162         <query>SELECT COUNT(id), x_migrate::TEXT FROM acq_invoice_legacy GROUP BY 2;</query>
1163     </report>
1164
1165     <report>
1166         <name>invoice_count</name>
1167         <iteration>1</iteration>
1168         <tag>acq</tag>
1169         <report_title>Migrated Invoices</report_title>
1170         <heading>Number of Funds</heading>
1171         <query>SELECT COUNT(id) FROM acq_invoice;</query>
1172     </report>
1173
1174     <!-- SERIALS REPORTS -->
1175     <report>
1176         <name>serials_mfhd_count</name>
1177         <tag>serials</tag>
1178         <iteration>0</iteration>
1179         <report_title>Migrated Serial MFHDs</report_title>
1180         <heading>Number of MFHDs</heading>
1181         <query>SELECT COUNT(id) FROM serial_record_entry</query>
1182     </report>
1183
1184     <!-- DEDUPE REPORTS -->
1185
1186     <asset>
1187         <name>dedupe_explain</name>
1188         <tag>dedupe</tag>
1189         <file>dedupe_process.asciidoc</file>
1190     </asset>
1191
1192     <report>
1193         <name>dedupe_bib_groups</name>
1194         <tag>dedupe</tag>
1195         <iteration>0</iteration>
1196         <report_title>Scoring and Bib Record Groups</report_title>
1197         <heading>Count.Bib Record Groups</heading>
1198         <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)
1199           UNION ALL SELECT (COUNT(DISTINCT incoming_bib)), 'Incoming Bibs With Matches Found' FROM bib_matches
1200           UNION ALL SELECT (COUNT(bre.id) - (SELECT COUNT(DISTINCT incoming_bib) FROM bib_matches)), 'Incoming Bibs With No Match'
1201                 FROM biblio.record_entry bre WHERE bre.deleted IS FALSE AND bre.id IN (SELECT eg::BIGINT FROM bib_id_map)
1202           UNION ALL SELECT COUNT(DISTINCT incoming_bib), 'Incoming Bibs Being Merged into Incumbent' FROM bib_matches WHERE incumbent_bib_score &gt;= incoming_bib_score
1203           UNION ALL SELECT COUNT(id), 'Incumbent Bibs With Higher Scores to Incoming' FROM bib_matches WHERE incumbent_bib_score &gt; incoming_bib_score
1204           UNION ALL SELECT COUNT(id), 'Incumbent Bibs With Equal Scores to Incoming' FROM bib_matches WHERE incumbent_bib_score = incoming_bib_score
1205           UNION ALL SELECT COUNT(id), 'Incumbent Bibs  With Lower Scores to Incoming' FROM bib_matches WHERE incumbent_bib_score &lt; incoming_bib_score
1206           ;</query>
1207     </report>
1208
1209     <report>
1210         <name>dedupe_format_count</name>
1211         <tag>dedupe</tag>
1212         <iteration>0</iteration>
1213         <report_title>Count of Items Matching By Format</report_title>
1214         <heading>Count.Format(s)</heading>
1215         <query>SELECT COUNT(id), search_formats FROM bib_matches GROUP BY 2 ORDER BY 2;</query>
1216     </report>
1217
1218     <report>
1219         <name>dedupe_score_ranges</name>
1220         <tag>dedupe</tag>
1221         <iteration>0</iteration>
1222         <report_title>Count of Items Matching By Format</report_title>
1223         <heading>Lowest Record Score.Largest Record Score.Record Set</heading>
1224         <query>SELECT MIN(incumbent_bib_score), MAX(incumbent_bib_score), 'Incumbent Records' FROM bib_matches
1225             UNION ALL SELECT MIN(incoming_bib_score), MAX(incoming_bib_score), 'Incoming Records' FROM bib_matches ;
1226         </query>
1227     </report>
1228
1229
1230     <report>
1231         <name>dedupe_sample_set</name>
1232         <tag>dedupe</tag>
1233         <iteration>0</iteration>
1234         <report_title>Sample of 20 Matching Dedupe Record Sets</report_title>
1235         <heading>Bib Being Merged Into.Bib Being Merged</heading>
1236         <query>SELECT incumbent_bib, incoming_bib FROM bib_matches WHERE incumbent_bib_score &gt;= incoming_bib_score LIMIT 20 ;
1237         </query>
1238     </report>
1239
1240     <!-- NOTICES REPORTS -->
1241
1242     <report>
1243         <name>notices_overview</name>
1244         <tag>notices</tag>
1245         <iteration>0</iteration>
1246         <report_title>Action Triggers Setup for Notices</report_title>
1247         <heading>ID.Active.Owner.Name</heading>
1248         <query>SELECT ed.id, ed.active, aou.shortname, ed.name
1249             FROM action_trigger.event_definition ed
1250             JOIN actor.org_unit aou ON aou.id = ed.owner
1251             WHERE ed.owner IN (SELECT DISTINCT home_ou FROM actor_usr)
1252             OR ed.owner IN (SELECT DISTINCT parent_ou FROM actor.org_unit WHERE id in (SELECT DISTINCT home_ou FROM actor_usr));
1253         </query>
1254     </report>
1255
1256     <report>
1257         <name>notices_count</name>
1258         <tag>notices</tag>
1259         <iteration>0</iteration>
1260         <report_title>Count of Notices Run with State</report_title>
1261         <heading>Count of Notices.State.ID.Owner.Name</heading>
1262         <query>SELECT COUNT(ate.id), ate.state, ed.id, aou.shortname, ed.name
1263             FROM action_trigger.event_definition ed
1264             JOIN actor.org_unit aou ON aou.id = ed.owner
1265             JOIN action_trigger.event ate ON ate.event_def = ed.id
1266             WHERE ed.owner IN (SELECT DISTINCT home_ou FROM actor_usr)
1267             OR ed.owner IN (SELECT DISTINCT parent_ou FROM actor.org_unit WHERE id in (SELECT DISTINCT home_ou FROM actor_usr))
1268             GROUP BY 2,3,4;
1269         </query>
1270     </report>
1271
1272
1273 </reports_file>
1274