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>Open Circulations</report_title>
  48         <heading>Circulation Status.Migrated.Count of Circs</heading>
  49         <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>
  50     </report>
  51
  52     <report>
  53         <name>circ_count</name>
  54         <tag>circs</tag>
  55         <iteration>1</iteration>
  56         <report_title>Open Circulations</report_title>
  57         <heading>Circulation Status.Count of Circs</heading>
  58         <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>
  59     </report>
  60 <!-- intent is to add a report by circulated and item status
  61     <report>
  62         <name>circ_count_by_item_status</name>
  63         <tag>circs</tag>
  64         <iteration>0</iteration>
  65         <report_title>Open Circulations</report_title>
  66         <heading>Circulation Status.Migrated.Count of Circs</heading>
  67         <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>
  68     </report>
  69 -->
  70      <report>
  71         <name>circ_by_orgunit</name>
  72         <tag>circs</tag>
  73         <iteration>0</iteration>
  74         <report_title>Circulations by Org Unit</report_title>
  75         <heading>Circulations Count.Migrated.Org Unit</heading>
  76         <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>
  77     </report>
  78
  79      <report>
  80         <name>circ_by_orgunit</name>
  81         <tag>circs</tag>
  82         <iteration>1</iteration>
  83         <report_title>Circulations by Org Unit</report_title>
  84         <heading>Circulations Count.Org Unit</heading>
  85         <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>
  86     </report>
  87
  88      <report>
  89         <name>circs_by_duration</name>
  90         <tag>circs</tag>
  91         <iteration>0</iteration>
  92         <report_title>Migrated Circulations by Duration, Fine and Max Fine</report_title>
  93         <heading>Count of Circs.Duration.Fine.Max Fine.Migrated</heading>
  94         <query>SELECT COUNT(id), duration, recurring_fine, max_fine, x_migrate::TEXT FROM action_circulation_legacy WHERE x_migrate = TRUE GROUP BY 2, 3, 4, 5</query>
  95     </report>
  96
  97      <report>
  98         <name>circs_by_duration</name>
  99         <tag>circs</tag>
 100         <iteration>1</iteration>
 101         <report_title>Circulations by Duration, Fine and Max Fine</report_title>
 102         <heading>Count of Circs.Duration.Fine.Max Fine</heading>
 103         <query>SELECT COUNT(id), duration, recurring_fine, max_fine FROM action_circulation GROUP BY 2, 3, 4 ORDER BY 2, 3, 4</query>
 104     </report>
 105
 106      <report>
 107         <name>circs_by_usrgroup</name>
 108         <tag>circs</tag>
 109         <iteration>0</iteration>
 110         <report_title>Circulations by Rules and Patron Group</report_title>
 111         <heading>Count of Circs.Duration.Fine.Max Fine.User Group.Matchpoints</heading>
 112         <query>SELECT COUNT(acirc.id), acirc.duration, acirc.recurring_fine, acirc.max_fine, 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>
 113     </report>
 114
 115      <report>
 116         <name>circs_by_usrgroup</name>
 117         <tag>circs</tag>
 118         <iteration>1</iteration>
 119         <report_title>Circulations by Rules and Patron Group</report_title>
 120         <heading>Count of Circs.Duration.Fine.Max Fine.User Group.Matchpoints</heading>
 121         <query>SELECT COUNT(acirc.id), acirc.duration, acirc.recurring_fine, acirc.max_fine, 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>
 122     </report>
 123
 124      <report>
 125         <name>circs_by_circmod</name>
 126         <tag>circs</tag>
 127         <iteration>0</iteration>
 128         <report_title>Circulations by Rules and Circulation Modifier</report_title>
 129         <heading>Count of Circs.Duration.Fine.Max Fine.Circulation Modifier.Matchpoints</heading>
 130         <query>SELECT COUNT(acirc.id), acirc.duration, acirc.recurring_fine, acirc.max_fine, 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>
 131     </report>
 132
 133      <report>
 134         <name>circs_by_circmod</name>
 135         <tag>circs</tag>
 136         <iteration>1</iteration>
 137         <report_title>Circulations by Rules and Circulation Modifier</report_title>
 138         <heading>Count of Circs.Duration.Fine.Max Fine.Circulation Modifier.Matchpoints</heading>
 139         <query>SELECT COUNT(acirc.id), acirc.duration, acirc.recurring_fine, acirc.max_fine, 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
 140              GROUP BY 2, 3, 4, 5, 6 ORDER BY 2, 3, 4, 5, 6</query>
 141     </report>
 142
 143      <report>
 144         <name>circs_by_orgunit</name>
 145         <tag>circs</tag>
 146         <iteration>0</iteration>
 147         <report_title>Circulations by Rules and Org Unit</report_title>
 148         <heading>Count of Circs.Duration.Fine.Max Fine.Library Branch</heading>
 149         <query>SELECT COUNT(acirc.id), acirc.duration, acirc.recurring_fine, acirc.max_fine, 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>
 150     </report>
 151
 152      <report>
 153         <name>circs_by_orgunit</name>
 154         <tag>circs</tag>
 155         <iteration>1</iteration>
 156         <report_title>Circulations by Rules and Org Unit</report_title>
 157         <heading>Count of Circs.Duration.Fine.Max Fine.Library Branch</heading>
 158         <query>SELECT COUNT(acirc.id), acirc.duration, acirc.recurring_fine, acirc.max_fine, 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>
 159     </report>
 160
 161      <report>
 162         <name>non_cat_circs</name>
 163         <tag>circs</tag>
 164         <iteration>0</iteration>
 165         <report_title>Non-Cataloged Circulation</report_title>
 166         <heading>Circulations Count.Migrated</heading>
 167         <query>SELECT COUNT(id), x_migrate::TEXT FROM action_non_cataloged_circulation_legacy GROUP BY 2</query>
 168     </report>
 169
 170      <report>
 171         <name>non_cat_circs</name>
 172         <tag>circs</tag>
 173         <iteration>1</iteration>
 174         <report_title>Non-Cataloged Circulation</report_title>
 175         <heading>Circulations Count</heading>
 176         <query>SELECT COUNT(id) FROM action_non_cataloged_circulation</query>
 177     </report>
 178
 179      <report>
 180         <name>in_house</name>
 181         <tag>circs</tag>
 182         <iteration>0</iteration>
 183         <report_title>In House Use</report_title>
 184         <heading>In House Use Records.Migrated</heading>
 185         <query>SELECT COUNT(id), x_migrate::TEXT FROM action_in_house_use_legacy GROUP BY 2</query>
 186     </report>
 187
 188      <report>
 189         <name>in_house</name>
 190         <tag>circs</tag>
 191         <iteration>1</iteration>
 192         <report_title>In House Use</report_title>
 193         <heading>In House Use Records</heading>
 194         <query>SELECT COUNT(id) FROM action_in_house_use</query>
 195     </report>
 196
 197      <report>
 198         <name>circs_missing_rules</name>
 199         <tag>circs</tag>
 200         <iteration>1</iteration>
 201         <report_title>Circs Missing Rules</report_title>
 202         <heading>Count.Field Missing</heading>
 203         <query>SELECT COUNT(id), 'Duration Rule Value' FROM action_circulation WHERE duration IS NULL
 204             UNION ALL SELECT COUNT(id), 'Recurring Fine Rule Value' FROM action_circulation WHERE recurring_fine IS NULL
 205             UNION ALL SELECT COUNT(id), 'Max Fine Rule Value' FROM action_circulation WHERE max_fine IS NULL
 206             UNION ALL SELECT COUNT(id), 'Duration Rule' FROM action_circulation WHERE duration_rule IS NULL
 207             UNION ALL SELECT COUNT(id), 'Recurring Fine Rule' FROM action_circulation WHERE recurring_fine_rule IS NULL
 208             UNION ALL SELECT COUNT(id), 'Max Fine Rule' FROM action_circulation WHERE max_fine_rule IS NULL
 209         </query>
 210     </report>
 211
 212      <report>
 213         <name>circ_open_by_item_status</name>
 214         <tag>circs</tag>
 215         <iteration>0</iteration>
 216         <report_title>Open Circulation and Status of Linked Items</report_title>
 217         <heading>Count.Status</heading>
 218         <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>
 219     </report>
 220
 221     <!-- HOLDS REPORTS -->
 222
 223      <report>
 224         <name>holds</name>
 225         <tag>holds</tag>
 226         <iteration>0</iteration>
 227         <report_title>Migrated Holds</report_title>
 228         <heading>Hold Type.Hold Count.Migrated</heading>
 229         <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>
 230     </report>
 231
 232      <report>
 233         <name>holds_bytype</name>
 234         <tag>holds</tag>
 235         <iteration>0</iteration>
 236         <report_title>Migrated Holds By Type</report_title>
 237         <heading>Hold Type.Hold Count.Migrated</heading>
 238         <query>SELECT hold_type as "Hold Type", COUNT(id), x_migrate::TEXT FROM action_hold_request_legacy GROUP BY 1, 3</query>
 239     </report>
 240
 241      <report>
 242         <name>transit_open_by_item_status</name>
 243         <tag>holds</tag>
 244         <iteration>0</iteration>
 245         <report_title>Transit Copy Records and Status of Linked Items</report_title>
 246         <heading>Count.Status</heading>
 247         <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>
 248     </report>
 249
 250      <report>
 251         <name>transit_copies_by_status</name>
 252         <tag>holds</tag>
 253         <iteration>0</iteration>
 254         <report_title>Status of Items with Count of Open In Transits</report_title>
 255         <heading>Count.Status.Count of Open Transits</heading>
 256         <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>
 257     </report>
 258
 259      <report>
 260         <name>hold_copies_by_status</name>
 261         <tag>holds</tag>
 262         <iteration>0</iteration>
 263         <report_title>Captured Holds with Status of Items</report_title>
 264         <heading>Count of Captured Hold.Status of Item</heading>
 265         <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>
 266     </report>
 267
 268       <report>
 269         <name>hold_depth</name>
 270         <tag>holds</tag>
 271         <iteration>0</iteration>
 272         <report_title>Depth of Unfilled Holds</report_title>
 273         <heading>Count.Depth</heading>
 274         <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>
 275     </report>
 276
 277     <!-- ASSET REPORTS -->
 278
 279     <report>
 280         <name>asset_copy_count</name>
 281         <report_title>Count of Copies by Library</report_title>
 282         <tag>assets</tag>
 283         <iteration>0</iteration>
 284         <heading>Copy Count.Library.Migrated</heading>
 285         <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>
 286     </report>
 287
 288     <report>
 289         <name>asset_deleted_copies</name>
 290         <report_title>Deleted Copies</report_title>
 291         <tag>assets</tag>
 292         <iteration>0</iteration>
 293         <heading>Copy Count.Deleted.Migrated</heading>
 294         <query>SELECT COUNT(ac.id), ac.deleted::TEXT, ac.x_migrate::TEXT FROM asset_copy_legacy ac GROUP BY 2, 3</query>
 295     </report>
 296
 297     <report>
 298         <name>asset_copies_by_status</name>
 299         <report_title>Copies by Status</report_title>
 300         <tag>assets</tag>
 301         <iteration>0</iteration>
 302         <heading>Copy Count.Status.Migrated</heading>
 303         <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>
 304     </report>
 305
 306     <report>
 307         <name>asset_circ_mod_copies_count</name>
 308         <report_title>Copies by Circulation Modifier</report_title>
 309         <tag>assets</tag>
 310         <iteration>0</iteration>
 311         <heading>Copy Count.Circulation Modifier.Migrated</heading>
 312         <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>
 313     </report>
 314
 315     <report>
 316         <name>asset_copy_notes</name>
 317         <report_title>Copy Notes</report_title>
 318         <tag>assets</tag>
 319         <iteration>0</iteration>
 320         <heading>Note Count.Public.Migrated</heading>
 321         <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>
 322     </report>
 323
 324     <report>
 325         <name>asset_copy_notes</name>
 326         <report_title>Copy Notes</report_title>
 327         <tag>assets</tag>
 328         <iteration>1</iteration>
 329         <heading>Note Count.Public</heading>
 330         <query>SELECT COUNT(acnote.id), acnote.pub::TEXT FROM asset_copy_note acnote GROUP BY 2 ORDER BY 2</query>
 331     </report>
 332
 333    <report>
 334         <name>asset_vols_by_lib</name>
 335         <report_title>Volumes by Library</report_title>
 336         <tag>assets</tag>
 337         <iteration>0</iteration>
 338         <heading>Volume Count.Library.Migrated</heading>
 339         <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>
 340     </report>
 341
 342    <report>
 343         <name>asset_vols_by_lib</name>
 344         <report_title>Volumes by Library</report_title>
 345         <tag>assets</tag>
 346         <iteration>1</iteration>
 347         <heading>Volume Count.Library</heading>
 348         <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>
 349     </report>
 350     <!--
 351    <report>
 352         <name>asset_cops_by_loc</name>
 353         <report_title>Copies by Location</report_title>
 354         <tag>assets</tag>
 355         <iteration>0</iteration>
 356         <heading>Copy Count.Library.Migrated</heading>
 357         <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>
 358     </report>
 359    -->
 360    <report>
 361         <name>asset_cops_by_loc_and_org</name>
 362         <report_title>Copies by Location</report_title>
 363         <tag>assets</tag>
 364         <iteration>0</iteration>
 365         <heading>Copy Count.Library.Circ Library.Migrated</heading>
 366         <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>
 367     </report>
 368
 369     <report>
 370         <name>asset_barcode_lengths</name>
 371         <report_title>Barcode Lengths by Library</report_title>
 372         <tag>assets</tag>
 373         <iteration>0</iteration>
 374         <heading>Count of Barcode.Barcode Length.Library</heading>
 375         <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>
 376     </report>
 377
 378     <report>
 379         <name>asset_barcode_patterns</name>
 380         <report_title>Common Barcode Starting Patterns</report_title>
 381         <tag>assets</tag>
 382         <iteration>0</iteration>
 383         <heading>Count of Barcodes (greater than 10).Left 60% of Characters</heading>
 384         <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>
 385     </report>
 386
 387     <report>
 388         <name>asset_barcode_incumbent_collisions</name>
 389         <report_title>Copy Barcode Incumbent Collisions</report_title>
 390         <tag>assets</tag>
 391         <iteration>0</iteration>
 392         <heading>Collision Count</heading>
 393         <query>SELECT COUNT(id) FROM asset_copy_legacy WHERE x_migrate = TRUE AND barcode ~* 'collision' and barcode ~* 'incumbent'</query>
 394         <note>Incumbent collisions are those where the migrated barcodes collide with existing barcodes in the database.</note>
 395     </report>
 396
 397     <report>
 398         <name>asset_barcode_incumbent_collisions</name>
 399         <report_title>Copy Barcode Incumbent Collisions</report_title>
 400         <tag>assets</tag>
 401         <iteration>1</iteration>
 402         <heading>Collision Count</heading>
 403         <query>SELECT COUNT(id) FROM asset_copy WHERE barcode ~* 'collision' and barcode ~* 'incumbent'</query>
 404         <note>Incumbent collisions are those where the migrated barcodes collide with existing barcodes in the database.</note>
 405     </report>
 406
 407     <report>
 408         <name>asset_barcode_internal_collisions</name>
 409         <report_title>Copy Barcode Internal Collisions</report_title>
 410         <tag>assets</tag>
 411         <iteration>0</iteration>
 412         <heading>Collision Count</heading>
 413         <query>SELECT COUNT(id) FROM asset_copy_legacy WHERE x_migrate = TRUE AND barcode ~* 'collision' and barcode ~* 'internal'</query>
 414         <note>Internal collisions are those where the migrated barcodes have conflicts within their list of barcodes.</note>
 415     </report>
 416
 417     <report>
 418         <name>asset_barcode_internal_collisions</name>
 419         <report_title>Copy Barcode Internal Collisions</report_title>
 420         <tag>assets</tag>
 421         <iteration>1</iteration>
 422         <heading>Collision Count</heading>
 423         <query>SELECT COUNT(id) FROM asset_copy WHERE barcode ~* 'collision' and barcode ~* 'internal'</query>
 424         <note>Internal collisions are those where the migrated barcodes have conflicts within their list of barcodes.</note>
 425     </report>
 426
 427     <report>
 428         <name>asset_barcode_collisions_shortlist</name>
 429         <report_title>Copy Barcode Collisions (first 20)</report_title>
 430         <tag>assets</tag>
 431         <iteration>0</iteration>
 432         <heading>Collision List</heading>
 433         <query>SELECT ac.barcode FROM asset_copy_legacy ac WHERE ac.barcode ~* 'collision' ORDER BY 1 LIMIT 20</query>
 434         <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>
 435     </report>
 436
 437     <report>
 438         <name>asset_barcode_collisions_shortlist</name>
 439         <report_title>Copy Barcode Collisions (first 20)</report_title>
 440         <tag>assets</tag>
 441         <iteration>1</iteration>
 442         <heading>Collision List</heading>
 443         <query>SELECT ac.barcode FROM asset_copy ac WHERE ac.barcode ~* 'collision' ORDER BY 1 LIMIT 20</query>
 444         <note>This is a shortlist of patron barcode collisions that maxes out at 20.  If there are more collisions we will need to run a custom report.</note>
 445     </report>
 446
 447     <report>
 448         <name>asset_barcode_collision_patterns</name>
 449         <report_title>Common Copy Barcode Collision Patterns</report_title>
 450         <tag>assets</tag>
 451         <iteration>0</iteration>
 452         <heading>Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters</heading>
 453         <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>
 454     </report>
 455
 456     <report>
 457         <name>asset_barcode_collision_patterns</name>
 458         <report_title>Common Copy Barcode Collision Patterns</report_title>
 459         <tag>assets</tag>
 460         <iteration>1</iteration>
 461         <heading>Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters</heading>
 462         <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>
 463     </report>
 464
 465     <report>
 466          <name>asset_stat_cats</name>
 467         <report_title>Copy Statistical Categories</report_title>
 468         <tag>assets</tag>
 469         <iteration>0</iteration>
 470         <heading>Stat Cat Count.Library.Statistical Category</heading>
 471         <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>
 472     </report>
 473
 474     <report>
 475          <name>asset_stat_cats</name>
 476         <report_title>Copy Statistical Categories</report_title>
 477         <tag>assets</tag>
 478         <iteration>1</iteration>
 479         <heading>Stat Cat Count.Library.Statistical Category</heading>
 480         <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>
 481     </report>
 482
 483     <report>
 484         <name>asset_stat_cat_entries</name>
 485         <report_title>Copy Stat Cat User Entries</report_title>
 486         <tag>assets</tag>
 487         <iteration>0</iteration>
 488         <heading>Copy Stat Count.Library.Statistical Category</heading>
 489         <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>
 490     </report>
 491
 492     <report>
 493         <name>asset_stat_cat_entries</name>
 494         <report_title>Copy Stat Cat User Entries</report_title>
 495         <tag>assets</tag>
 496         <iteration>1</iteration>
 497         <heading>Copy Stat Count.Library.Statistical Category</heading>
 498         <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.
 499 id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3</query>
 500     </report>
 501
 502     <report>
 503          <name>asset_copy_tags</name>
 504         <report_title>Copy Tags</report_title>
 505         <tag>assets</tag>
 506         <iteration>0</iteration>
 507         <heading>Tag Count.Copy Tag Type.Copy Tag Label.Staff Note.Public</heading>
 508         <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>
 509     </report>
 510
 511     <!-- MONEY REPORTS -->
 512
 513     <report>
 514         <name>money_billing_voided</name>
 515         <report_title>Bills Voided And Not</report_title>
 516         <tag>money</tag>
 517         <iteration>0</iteration>
 518         <heading>Count.Voided.Sum.Migrated</heading>
 519         <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>
 520     </report>
 521
 522     <report>
 523         <name>money_billing_voided</name>
 524         <report_title>Bills Voided And Not</report_title>
 525         <tag>money</tag>
 526         <iteration>1</iteration>
 527         <heading>Count.Voided.Sum</heading>
 528         <query>SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM money_billing a GROUP BY 2 ORDER BY 2, 3</query>
 529     </report>
 530
 531     <report>
 532         <name>money_billing_by_type</name>
 533         <report_title>Bills by Type</report_title>
 534         <tag>money</tag>
 535         <iteration>0</iteration>
 536         <heading>Count.Billing Type.Migrated</heading>
 537         <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>
 538     </report>
 539
 540     <report>
 541         <name>money_billing_by_type</name>
 542         <report_title>Bills by Type</report_title>
 543         <tag>money</tag>
 544         <iteration>1</iteration>
 545         <heading>Count.Billing Type</heading>
 546         <query>SELECT COUNT(a.id), a.billing_type FROM money_billing a GROUP BY 2 ORDER BY 2</query>
 547     </report>
 548
 549     <report>
 550         <name>money_cash_payment</name>
 551         <report_title>Cash Payments</report_title>
 552         <tag>money</tag>
 553         <iteration>0</iteration>
 554         <heading>Count.Voided.Sum.Migrated</heading>
 555         <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>
 556     </report>
 557
 558     <report>
 559         <name>money_cash_payment</name>
 560         <report_title>Cash Payments</report_title>
 561         <tag>money</tag>
 562         <iteration>1</iteration>
 563         <heading>Count.Voided.Sum</heading>
 564         <query>SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM money_cash_payment a GROUP BY 2 ORDER BY 2</query>
 565     </report>
 566
 567     <report>
 568         <name>money_check_payment</name>
 569         <report_title>Check Payments</report_title>
 570         <tag>money</tag>
 571         <iteration>0</iteration>
 572         <heading>Count.Voided.Sum.Migrated</heading>
 573         <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>
 574     </report>
 575
 576     <report>
 577         <name>money_forgive_payment</name>
 578         <report_title>Forgive Payments</report_title>
 579         <tag>money</tag>
 580         <iteration>0</iteration>
 581         <heading>Count.Voided.Sum.Migrated</heading>
 582         <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>
 583     </report>
 584
 585     <report>
 586         <name>money_forgive_payment</name>
 587         <report_title>Forgive Payments</report_title>
 588         <tag>money</tag>
 589         <iteration>1</iteration>
 590         <heading>Count.Voided.Sum</heading>
 591         <query>SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM money_forgive_paymen a GROUP BY 2 ORDER BY 2</query>
 592     </report>
 593
 594     <report>
 595         <name>money_goods_payment</name>
 596         <report_title>Goods Payments</report_title>
 597         <tag>money</tag>
 598         <iteration>0</iteration>
 599         <heading>Count.Voided.Sum.Migrated</heading>
 600         <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>
 601     </report>
 602
 603     <report>
 604         <name>money_work_payment</name>
 605         <report_title>Work Payments</report_title>
 606         <tag>money</tag>
 607         <iteration>0</iteration>
 608         <heading>Count.Voided.Sum.Migrated</heading>
 609         <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>
 610     </report>
 611
 612     <report>
 613         <name>money_credit_card_payment</name>
 614         <report_title>Credit Card Payments</report_title>
 615         <tag>money</tag>
 616         <iteration>0</iteration>
 617         <heading>Count.Voided.Sum.Migrated</heading>
 618         <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>
 619     </report>
 620
 621     <report>
 622         <name>money_credit_payment</name>
 623         <report_title>Credit Payments</report_title>
 624         <tag>money</tag>
 625         <iteration>0</iteration>
 626         <heading>Count.Voided.Sum.Migrated</heading>
 627         <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>
 628     </report>
 629
 630
 631     <!-- BIBS REPORTS -->
 632
 633     <report>
 634         <name>bibs</name>
 635         <report_title>Extracted Bibliographic Records</report_title>
 636         <tag>bibs</tag>
 637         <iteration>0</iteration>
 638         <heading>Count.Deleted.Migrated</heading>
 639         <query>SELECT COUNT(bre.id), bre.deleted::TEXT, bre.x_migrate::TEXT FROM biblio_record_entry_legacy bre GROUP BY 2, 3 ORDER BY 2, 3</query>
 640         <note>False means the records are not deleted.</note>
 641     </report>
 642
 643     <report>
 644         <name>bibs</name>
 645         <report_title>Extracted Bibliographic Records</report_title>
 646         <tag>bibs</tag>
 647         <iteration>1</iteration>
 648         <heading>Count</heading>
 649         <query>SELECT COUNT(id) FROM biblio_record_entry</query>
 650     </report>
 651
 652     <report>
 653         <name>bibswovolumes</name>
 654         <report_title>Bibliographic Records Without Volumes</report_title>
 655         <tag>bibs</tag>
 656         <iteration>0</iteration>
 657         <heading>Count</heading>
 658         <query>SELECT COUNT(id) FROM biblio_record_entry where id NOT IN (SELECT DISTINCT record FROM asset_call_number)</query>
 659     </report>
 660
 661     <report>
 662         <name>bibs_notes</name>
 663         <report_title>Bib Record Notes</report_title>
 664         <tag>bibs</tag>
 665         <iteration>0</iteration>
 666         <heading>Count.Migrated</heading>
 667         <query>SELECT COUNT(b.id), b.x_migrate::TEXT FROM biblio_record_note_legacy b GROUP BY 2</query>
 668     </report>
 669
 670     <report>
 671         <name>bibs_notes</name>
 672         <report_title>Bib Record Notes</report_title>
 673         <tag>bibs</tag>
 674         <iteration>1</iteration>
 675         <heading>Count</heading>
 676         <query>SELECT COUNT(b.id) FROM biblio_record_note b</query>
 677     </report>
 678
 679     <report>
 680         <name>bibs_peers</name>
 681         <report_title>Peer Bib Copies</report_title>
 682         <tag>bibs</tag>
 683         <iteration>0</iteration>
 684         <heading>Count.Migrated</heading>
 685         <query>SELECT COUNT(b.id), b.x_migrate::TEXT FROM biblio_peer_bib_copy_map_legacy b GROUP BY 2</query>
 686     </report>
 687
 688     <report>
 689         <name>bibs_peers</name>
 690         <report_title>Peer Bib Copies</report_title>
 691         <tag>bibs</tag>
 692         <iteration>1</iteration>
 693         <heading>Count</heading>
 694         <query>SELECT COUNT(b.id) FROM biblio_peer_bib_copy_map b</query>
 695     </report>
 696
 697     <report>
 698         <name>bibs_parts</name>
 699         <report_title>Monograph Parts</report_title>
 700         <tag>bibs</tag>
 701         <iteration>0</iteration>
 702         <heading>Count.Migrated</heading>
 703         <query>SELECT COUNT(b.id), b.x_migrate::TEXT FROM biblio_monograph_part_legacy b GROUP BY 2</query>
 704     </report>
 705
 706     <report>
 707         <name>bibs_parts</name>
 708         <report_title>Monograph Parts</report_title>
 709         <tag>bibs</tag>
 710         <iteration>1</iteration>
 711         <heading>Count</heading>
 712         <query>SELECT COUNT(b.id) FROM biblio_monograph_part b</query>
 713     </report>
 714
 715     <!-- ACTORS REPORTS -->
 716
 717     <report>
 718         <name>usrsbyorg</name>
 719         <report_title>Patrons by Home Org</report_title>
 720         <tag>actors</tag>
 721         <iteration>0</iteration>
 722         <heading>Count.Library.Deleted.Migrated</heading>
 723         <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>
 724     </report>
 725
 726     <report>
 727         <name>usrsbypgt</name>
 728         <report_title>Patrons by Permission Group</report_title>
 729         <tag>actors</tag>
 730         <iteration>0</iteration>
 731         <heading>Count.Permission Group.Migrated</heading>
 732         <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>
 733     </report>
 734
 735     <report>
 736         <name>active_usrs</name>
 737         <report_title>Patron by Active Status</report_title>
 738         <tag>actors</tag>
 739         <iteration>0</iteration>
 740         <heading>Count of Users.Active.Migrated</heading>
 741         <query>SELECT COUNT(id), active::TEXT, x_migrate::TEXT FROM actor_usr_legacy GROUP BY 2, 3</query>
 742     </report>
 743
 744     <report>
 745         <name>active_usrs</name>
 746         <report_title>Patrons by Active Status</report_title>
 747         <tag>actors</tag>
 748         <iteration>1</iteration>
 749         <heading>Count of Users.Active</heading>
 750         <query>SELECT COUNT(id), active::TEXT FROM actor_usr GROUP BY 2</query>
 751     </report>
 752
 753     <report>
 754         <name>active_usr_barcodes</name>
 755         <report_title>Patron Barcodes by Active Status</report_title>
 756         <tag>actors</tag>
 757         <iteration>0</iteration>
 758         <heading>Count of Barcodes.Active.Migrated</heading>
 759         <query>SELECT COUNT(id), active::TEXT, x_migrate::TEXT FROM actor_card_legacy GROUP BY 2, 3</query>
 760     </report>
 761
 762     <report>
 763         <name>active_usr_barcodes</name>
 764         <report_title>Patron Barcodes by Active Status</report_title>
 765         <tag>actors</tag>
 766         <iteration>1</iteration>
 767         <heading>Count of Barcodes.Active</heading>
 768         <query>SELECT COUNT(id), active::TEXT FROM actor_card GROUP BY 2</query>
 769     </report>
 770
 771     <report>
 772         <name>usr_barcode_lengths</name>
 773         <report_title>Barcode Lengths by Library</report_title>
 774         <tag>actors</tag>
 775         <iteration>0</iteration>
 776         <heading>Count of Barcode.Barcode Length.Library</heading>
 777         <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>
 778     </report>
 779
 780     <report>
 781         <name>usr_barcode_lengths</name>
 782         <report_title>Barcode Lengths by Library</report_title>
 783         <tag>actors</tag>
 784         <iteration>1</iteration>
 785         <heading>Count of Barcode.Barcode Length.Library</heading>
 786         <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>
 787     </report>
 788
 789     <report>
 790         <name>usr_barcode_patterns</name>
 791         <report_title>Common Barcode Starting Patterns</report_title>
 792         <tag>actors</tag>
 793         <iteration>0</iteration>
 794         <heading>Count of Barcodes (greater than 10).Left 60% of Characters</heading>
 795         <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>
 796     </report>
 797
 798     <report>
 799         <name>usr_barcode_patterns</name>
 800         <report_title>Common Barcode Starting Patterns</report_title>
 801         <tag>actors</tag>
 802         <iteration>1</iteration>
 803         <heading>Count of Barcodes (greater than 10).Left 60% of Characters</heading>
 804         <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>
 805     </report>
 806
 807     <report>
 808         <name>usr_barcode_collisions</name>
 809         <report_title>Patron Barcode Collisions</report_title>
 810         <tag>actors</tag>
 811         <iteration>0</iteration>
 812         <heading>Collision Count</heading>
 813         <query>SELECT COUNT(acard.id) FROM actor_card_legacy acard WHERE barcode ~* 'collision' AND x_migrate = TRUE</query>
 814     </report>
 815
 816     <report>
 817         <name>usr_barcode_collisions</name>
 818         <report_title>Patron Barcode Collisions</report_title>
 819         <tag>actors</tag>
 820         <iteration>1</iteration>
 821         <heading>Collision Count</heading>
 822         <query>SELECT COUNT(acard.id) FROM actor_card acard WHERE barcode ~* 'collision'</query>
 823     </report>
 824
 825     <report>
 826         <name>usr_barcode_collision_shortlist</name>
 827         <report_title>Patron Barcode Collisions (first 20)</report_title>
 828         <tag>actors</tag>
 829         <iteration>0</iteration>
 830         <heading>Collision List</heading>
 831         <query>SELECT acard.barcode FROM actor_card_legacy acard WHERE acard.barcode ~* 'collision' AND acard.x_migrate = TRUE ORDER BY 1 LIMIT 20</query>
 832         <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>
 833     </report>
 834
 835     <report>
 836         <name>usr_barcode_collision_shortlist</name>
 837         <report_title>Patron Barcode Collisions (first 20)</report_title>
 838         <tag>actors</tag>
 839         <iteration>1</iteration>
 840         <heading>Collision List</heading>
 841         <query>SELECT acard.barcode FROM actor_card acard WHERE acard.barcode ~* 'collision' ORDER BY 1 LIMIT 20</query>
 842         <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>
 843     </report>
 844
 845     <report>
 846         <name>usr_barcode_collision_patterns</name>
 847         <report_title>Common Patron Barcode Collision Patterns</report_title> a.x_migrate
 848         <tag>actors</tag>
 849         <iteration>0</iteration>
 850         <heading>Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters</heading>
 851         <query>SELECT COUNT(acard.id), LEFT(acard.barcode,(ROUND(LENGTH(acard.barcode)*.6))::INT) FROM actor_card_legacy acard WHERE acard.barcode ~* 'collision' AND acard.x_migrate = TRUE GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2</query>
 852     </report>
 853
 854     <report>
 855         <name>usr_barcode_collision_patterns</name>
 856         <report_title>Common Patron Barcode Collision Patterns</report_title> a.x_migrate
 857         <tag>actors</tag>
 858         <iteration>1</iteration>
 859         <heading>Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters</heading>
 860         <query>SELECT COUNT(acard.id), LEFT(acard.barcode,(ROUND(LENGTH(acard.barcode)*.6))::INT) FROM actor_card acard WHERE acard.barcode ~* 'collision' GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2</query>
 861     </report>
 862
 863     <report>
 864         <name>usr_addressses_status</name>
 865         <report_title>Patron Addresses by Valid Status</report_title>
 866         <tag>actors</tag>
 867         <iteration>0</iteration>
 868         <heading>Count.Valid.Migrated</heading>
 869         <query>SELECT COUNT(aua.id), valid::TEXT, x_migrate::TEXT FROM actor_usr_address_legacy aua GROUP BY 2, 3</query>
 870     </report>
 871
 872     <report>
 873         <name>usr_addressses_status</name>
 874         <report_title>Patron Addresses by Valid Status</report_title>
 875         <tag>actors</tag>
 876         <iteration>1</iteration>
 877         <heading>Count.Valid</heading>
 878         <query>SELECT COUNT(aua.id), valid::TEXT FROM actor_usr_address aua GROUP BY 2</query>
 879     </report>
 880
 881     <report>
 882         <name>usr_addresses_pending</name>
 883         <report_title>Patron Addresses by Pending Status</report_title>
 884         <tag>actors</tag>
 885         <iteration>0</iteration>
 886         <heading>Count of Addresses.Pending.Migrated</heading>
 887         <query>SELECT COUNT(aua.id), pending::TEXT, x_migrate::TEXT FROM actor_usr_address_legacy aua GROUP BY 2, 3</query>
 888     </report>
 889
 890     <report>
 891         <name>usr_addresses_pending</name>
 892         <report_title>Patron Addresses by Pending Status</report_title>
 893         <tag>actors</tag>
 894         <iteration>1</iteration>
 895         <heading>Count of Addresses.Pending</heading>
 896         <query>SELECT COUNT(aua.id), pending::TEXT FROM actor_usr_address aua GROUP BY 2</query>
 897     </report>
 898
 899     <report>
 900         <name>usr_messages</name>
 901         <report_title>Patron Messages</report_title>
 902         <tag>actors</tag>
 903         <iteration>0</iteration>
 904         <heading>Count.Deleted.Migrated</heading>
 905         <query>SELECT COUNT(aum.id), deleted::TEXT, x_migrate::TEXT FROM actor_usr_message_legacy aum GROUP BY 2, 3</query>
 906     </report>
 907
 908     <report>
 909         <name>usr_messages</name>
 910         <report_title>Patron Messages</report_title>
 911         <tag>actors</tag>
 912         <iteration>1</iteration>
 913         <heading>Count.Deleted</heading>
 914         <query>SELECT COUNT(aum.id), deleted::TEXT FROM actor_usr_message_legacy aum GROUP BY 2</query>
 915     </report>
 916
 917     <report>
 918         <name>usr_notes</name>
 919         <report_title>Patron Notes</report_title>
 920         <tag>actors</tag>
 921         <iteration>0</iteration>
 922         <heading>Count.Public.Migrated</heading>
 923         <query>SELECT COUNT(aun.id), pub::TEXT, x_migrate::TEXT FROM actor_usr_note_legacy aun GROUP BY 2, 3</query>
 924     </report>
 925
 926     <report>
 927         <name>usr_notes</name>
 928         <report_title>Patron Notes</report_title>
 929         <tag>actors</tag>
 930         <iteration>1</iteration>
 931         <heading>Count.Public</heading>
 932         <query>SELECT COUNT(aun.id), pub::TEXT FROM actor_usr_note aun GROUP BY 2</query>
 933     </report>
 934
 935     <report>
 936          <name>usr_stat_cats</name>
 937         <report_title>Patron Statistical Categories</report_title>
 938         <tag>actors</tag>
 939         <iteration>0</iteration>
 940         <heading>Stat Cat Count.Library.Statistical Category.Migrated</heading>
 941         <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>
 942     </report>
 943
 944     <report>
 945          <name>usr_stat_cats</name>
 946         <report_title>Patron Statistical Categories</report_title>
 947         <tag>actors</tag>
 948         <iteration>1</iteration>
 949         <heading>Stat Cat Count.Library.Statistical Category</heading>
 950         <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>
 951     </report>
 952
 953     <report>
 954         <name>usr_stat_cat_entries</name>
 955         <report_title>Patron Stat Cat User Entries</report_title>
 956         <tag>actors</tag>
 957         <iteration>0</iteration>
 958         <heading>Patron Stat Count.Library.Statistical Category.Migrated</heading>
 959         <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>
 960     </report>
 961
 962     <report>
 963         <name>usr_stat_cat_entries</name>
 964         <report_title>Patron Stat Cat User Entries</report_title>
 965         <tag>actors</tag>
 966         <iteration>1</iteration>
 967         <heading>Patron Stat Count.Library.Statistical Category</heading>
 968         <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>
 969     </report>
 970
 971     <report>
 972         <name>usr_thresholds</name>
 973         <report_title>Patron Thresholds</report_title>
 974         <tag>actors</tag>
 975         <iteration>0</iteration>
 976         <heading>Pateron Group.Org Unit.Penalty.Threshold</heading>
 977         <query>SELECT pgt.name, aou.shortname, sp.name, p.threshold FROM permission_grp_penalty_threshold p
 978                 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
 979                 ORDER BY 2, 1, 3</query>
 980     </report>
 981
 982
 983     <!-- ACQUISITIONS REPORTS -->
 984     <report>
 985         <name>fund_count</name>
 986         <iteration>0</iteration>
 987         <tag>acq</tag>
 988         <report_title>Migrated Funds</report_title>
 989         <heading>Number of Funds.Migrated</heading>
 990         <query>SELECT COUNT(id), x_migrate::TEXT FROM acq_fund_legacy GROUP BY 2;</query>
 991     </report>
 992
 993     <report>
 994         <name>fund_count</name>
 995         <iteration>1</iteration>
 996         <tag>acq</tag>
 997         <report_title>Migrated Funds</report_title>
 998         <heading>Number of Funds</heading>
 999         <query>SELECT COUNT(id) FROM acq_fund;</query>
1000     </report>
1001
1002     <report>
1003         <name>invoice_count</name>
1004         <iteration>0</iteration>
1005         <tag>acq</tag>
1006         <report_title>Migrated Invoices</report_title>
1007         <heading>Number of Invoices.Migrated</heading>
1008         <query>SELECT COUNT(id), x_migrate::TEXT FROM acq_invoice_legacy GROUP BY 2;</query>
1009     </report>
1010
1011     <report>
1012         <name>invoice_count</name>
1013         <iteration>1</iteration>
1014         <tag>acq</tag>
1015         <report_title>Migrated Invoices</report_title>
1016         <heading>Number of Funds</heading>
1017         <query>SELECT COUNT(id) FROM acq_invoice;</query>
1018     </report>
1019
1020     <!-- SERIALS REPORTS -->
1021     <report>
1022         <name>serials_mfhd_count</name>
1023         <tag>serials</tag>
1024         <iteration>0</iteration>
1025         <report_title>Migrated Serial MFHDs</report_title>
1026         <heading>Number of MFHDs</heading>
1027         <query>SELECT COUNT(id) FROM serial_record_entry</query>
1028     </report>
1029
1030     <!-- DEDUPE REPORTS -->
1031
1032     <asset>
1033         <name>dedupe_explain</name>
1034         <tag>dedupe</tag>
1035         <file>dedupe_process.asciidoc</file>
1036     </asset>
1037
1038     <report>
1039         <name>dedupe_bib_groups</name>
1040         <tag>dedupe</tag>
1041         <iteration>0</iteration>
1042         <report_title>Scoring and Bib Record Groups</report_title>
1043         <heading>Count.Bib Record Groups</heading>
1044         <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)
1045           UNION ALL SELECT (COUNT(DISTINCT incoming_bib)), 'Incoming Bibs With Matches Found' FROM bib_matches
1046           UNION ALL SELECT (COUNT(bre.id) - (SELECT COUNT(DISTINCT incoming_bib) FROM bib_matches)), 'Incoming Bibs With No Match'
1047                 FROM biblio.record_entry bre WHERE bre.deleted IS FALSE AND bre.id IN (SELECT eg::BIGINT FROM bib_id_map)
1048           UNION ALL SELECT COUNT(DISTINCT incoming_bib), 'Incoming Bibs Being Merged into Incumbent' FROM bib_matches WHERE incumbent_bib_score &gt;= incoming_bib_score
1049           UNION ALL SELECT COUNT(id), 'Incumbent Bibs With Higher Scores to Incoming' FROM bib_matches WHERE incumbent_bib_score &gt; incoming_bib_score
1050           UNION ALL SELECT COUNT(id), 'Incumbent Bibs With Equal Scores to Incoming' FROM bib_matches WHERE incumbent_bib_score = incoming_bib_score
1051           UNION ALL SELECT COUNT(id), 'Incumbent Bibs  With Lower Scores to Incoming' FROM bib_matches WHERE incumbent_bib_score &lt; incoming_bib_score
1052           ;</query>
1053     </report>
1054
1055     <report>
1056         <name>dedupe_format_count</name>
1057         <tag>dedupe</tag>
1058         <iteration>0</iteration>
1059         <report_title>Count of Items Matching By Format</report_title>
1060         <heading>Count.Format(s)</heading>
1061         <query>SELECT COUNT(id), search_formats FROM bib_matches GROUP BY 2 ORDER BY 2;</query>
1062     </report>
1063
1064     <report>
1065         <name>dedupe_score_ranges</name>
1066         <tag>dedupe</tag>
1067         <iteration>0</iteration>
1068         <report_title>Count of Items Matching By Format</report_title>
1069         <heading>Lowest Record Score.Largest Record Score.Record Set</heading>
1070         <query>SELECT MIN(incumbent_bib_score), MAX(incumbent_bib_score), 'Incumbent Records' FROM bib_matches
1071             UNION ALL SELECT MIN(incoming_bib_score), MAX(incoming_bib_score), 'Incoming Records' FROM bib_matches ;
1072         </query>
1073     </report>
1074
1075
1076     <report>
1077         <name>dedupe_sample_set</name>
1078         <tag>dedupe</tag>
1079         <iteration>0</iteration>
1080         <report_title>Sample of 20 Matching Dedupe Record Sets</report_title>
1081         <heading>Bib Being Merged Into.Bib Being Merged</heading>
1082         <query>SELECT incumbent_bib, incoming_bib FROM bib_matches WHERE incumbent_bib_score &gt;= incoming_bib_score LIMIT 20 ;
1083         </query>
1084     </report>
1085
1086 </reports_file>
1087