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 m_money_billing_legacy GROUP BY 2;</query>  query itself, will be replaced
  38         <note>Arbitrary note that can be included in the entries.</note>
  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 m_action_circulation_legacy WHERE xact_finish IS NOT NULL AND x_migrate
  50             UNION ALL SELECT 'Open Circulations', COUNT(id) FROM m_action_circulation_legacy WHERE xact_finish IS NULL AND x_migrate</query>
  51     </report>
  52
  53     <report>
  54         <name>circ_count</name>
  55         <tag>circs</tag>
  56         <iteration>1</iteration>
  57         <report_title>Open Circulations</report_title>
  58         <heading>Circulation Status.Count of Circs</heading>
  59         <query>SELECT 'Closed Circulations', COUNT(id) FROM m_action_circulation WHERE xact_finish IS NOT NULL UNION ALL SELECT 'Open Circulations', COUNT(id) FROM m_action_circulation WHERE xact_finish IS NULL</query>
  60     </report>
  61
  62      <report>
  63         <name>circ_by_orgunit</name>
  64         <tag>circs</tag>
  65         <iteration>0</iteration>
  66         <report_title>Circulations by Org Unit</report_title>
  67         <heading>Circulations Count.Org Unit</heading>
  68         <query>SELECT COUNT(acirc.id), aou.name FROM m_action_circulation_legacy acirc JOIN actor.org_unit aou ON aou.id = acirc.circ_lib WHERE acirc.xact_finish IS NULL AND x_migrate = TRUE GROUP BY 2</query>
  69     </report>
  70
  71      <report>
  72         <name>circ_by_orgunit</name>
  73         <tag>circs</tag>
  74         <iteration>1</iteration>
  75         <report_title>Circulations by Org Unit</report_title>
  76         <heading>Circulations Count.Org Unit</heading>
  77         <query>SELECT COUNT(acirc.id), aou.name FROM m_action_circulation acirc JOIN actor.org_unit aou ON aou.id = acirc.circ_lib WHERE acirc.xact_finish IS NULL GROUP BY 2</query>
  78     </report>
  79
  80      <report>
  81         <name>circs_by_duration</name>
  82         <tag>circs</tag>
  83         <iteration>0</iteration>
  84         <report_title>Migrated Circulations by Duration, Fine and Max Fine</report_title>
  85         <heading>Count of Circs.Duration.Fine.Max Fine</heading>
  86         <query>SELECT COUNT(id), duration_rule, recurring_fine_rule, max_fine_rule FROM m_action_circulation_legacy WHERE x_migrate = TRUE GROUP BY 2, 3, 4</query>
  87     </report>
  88
  89      <report>
  90         <name>circs_by_duration</name>
  91         <tag>circs</tag>
  92         <iteration>1</iteration>
  93         <report_title>Circulations by Duration, Fine and Max Fine</report_title>
  94         <heading>Count of Circs.Duration.Fine.Max Fine</heading>
  95         <query>SELECT COUNT(id), duration_rule, recurring_fine_rule, max_fine_rule FROM m_action_circulation GROUP BY 2, 3, 4 ORDER BY 2, 3, 4</query>
  96     </report>
  97
  98      <report>
  99         <name>circs_by_usrgroup</name>
 100         <tag>circs</tag>
 101         <iteration>0</iteration>
 102         <report_title>Circulations by Rules and Patron Group</report_title>
 103         <heading>Count of Circs.Duration.Fine.Max Fine.User Group.Matchpoints</heading>
 104         <query>SELECT COUNT(acirc.id), acirc.duration_rule, acirc.recurring_fine_rule, acirc.max_fine_rule, pgt.name, x.buildrows FROM m_action_circulation_legacy acirc JOIN actor.usr au ON au.id = acirc.usr JOIN permission.grp_tree pgt ON pgt.id = au.profile JOIN (SELECT acirc.id, ARRAY_TO_STRING(find_cmm(acirc.id),',') AS buildrows FROM m_action_circulation_legacy acirc WHERE acirc.x_migrate = TRUE) x ON x.id = acirc.id WHERE acirc.x_migrate = TRUE GROUP BY 2, 3, 4, 5, 6 ORDER BY 2, 3, 4, 5, 6</query>
 105     </report>
 106
 107      <report>
 108         <name>circs_by_usrgroup</name>
 109         <tag>circs</tag>
 110         <iteration>1</iteration>
 111         <report_title>Circulations by Rules and Patron Group</report_title>
 112         <heading>Count of Circs.Duration.Fine.Max Fine.User Group.Matchpoints</heading>
 113         <query>SELECT COUNT(acirc.id), acirc.duration_rule, acirc.recurring_fine_rule, acirc.max_fine_rule, pgt.name, x.buildrows FROM m_action_circulation acirc JOIN actor.usr au ON au.id = acirc.usr JOIN permission.grp_tree pgt ON pgt.id = au.profile JOIN (SELECT acirc.id, ARRAY_TO_STRING(find_cmm(acirc.id),',') AS buildrows FROM m_action_circulation acirc) x ON x.id = acirc.id GROUP BY 2, 3, 4, 5, 6 ORDER BY 2, 3, 4, 5, 6</query>
 114     </report>
 115
 116      <report>
 117         <name>circs_by_circmod</name>
 118         <tag>circs</tag>
 119         <iteration>0</iteration>
 120         <report_title>Circulations by Rules and Circulation Modifier</report_title>
 121         <heading>Count of Circs.Duration.Fine.Max Fine.Circulation Modifier.Matchpoints</heading>
 122         <query>SELECT COUNT(acirc.id), acirc.duration_rule, acirc.recurring_fine_rule, acirc.max_fine_rule, ac.circ_modifier, x.buildrows FROM m_action_circulation_legacy acirc JOIN asset.copy ac ON ac.id = acirc.target_copy JOIN (SELECT acirc.id, ARRAY_TO_STRING(find_cmm(acirc.id),',') AS buildrows FROM m_action_circulation_legacy acirc WHERE acirc.x_migrate = TRUE) x ON x.id = acirc.id WHERE acirc.x_migrate = TRUE GROUP BY 2, 3, 4, 5, 6 ORDER BY 2, 3, 4, 5, 6</query>
 123     </report>
 124
 125      <report>
 126         <name>circs_by_circmod</name>
 127         <tag>circs</tag>
 128         <iteration>1</iteration>
 129         <report_title>Circulations by Rules and Circulation Modifier</report_title>
 130         <heading>Count of Circs.Duration.Fine.Max Fine.Circulation Modifier.Matchpoints</heading>
 131         <query>SELECT COUNT(acirc.id), acirc.duration_rule, acirc.recurring_fine_rule, acirc.max_fine_rule, ac.circ_modifier, x.buildrows FROM m_action_circulation acirc JOIN asset.copy ac ON ac.id = acirc.target_copy JOIN (SELECT acirc.id, ARRAY_TO_STRING(find_cmm(acirc.id),',') AS buildrows FROM m_action_circulation acirc) x ON x.id = acirc.id
 132              GROUP BY 2, 3, 4, 5, 6 ORDER BY 2, 3, 4, 5, 6</query>
 133     </report>
 134
 135      <report>
 136         <name>circs_by_orgunit</name>
 137         <tag>circs</tag>
 138         <iteration>0</iteration>
 139         <report_title>Circulations by Rules and Org Unit</report_title>
 140         <heading>Count of Circs.Duration.Fine.Max Fine.Library Branch</heading>
 141         <query>SELECT COUNT(acirc.id), acirc.duration_rule, acirc.recurring_fine_rule, acirc.max_fine_rule, aou.name FROM m_action_circulation_legacy acirc JOIN actor.org_unit aou ON aou.id = acirc.circ_lib WHERE acirc.x_migrate = TRUE GROUP BY 2, 3, 4, 5 ORDER BY 2, 3, 4, 5</query>
 142     </report>
 143
 144      <report>
 145         <name>circs_by_orgunit</name>
 146         <tag>circs</tag>
 147         <iteration>1</iteration>
 148         <report_title>Circulations by Rules and Org Unit</report_title>
 149         <heading>Count of Circs.Duration.Fine.Max Fine.Library Branch</heading>
 150         <query>SELECT COUNT(acirc.id), acirc.duration_rule, acirc.recurring_fine_rule, acirc.max_fine_rule, aou.name FROM m_action_circulation acirc JOIN actor.org_unit aou ON aou.id = acirc.circ_lib GROUP BY 2, 3, 4, 5 ORDER BY 2, 3, 4, 5</query>
 151     </report>
 152
 153      <report>
 154         <name>non_cat_circs</name>
 155         <tag>circs</tag>
 156         <iteration>0</iteration>
 157         <report_title>Non-Cataloged Circulation</report_title>
 158         <heading>Circulations Count.Migrated</heading>
 159         <query>SELECT COUNT(id), x_migrate::TEXT FROM m_action_non_cataloged_circulation_legacy GROUP BY 2</query>
 160     </report>
 161
 162      <report>
 163         <name>non_cat_circs</name>
 164         <tag>circs</tag>
 165         <iteration>1</iteration>
 166         <report_title>Non-Cataloged Circulation</report_title>
 167         <heading>Circulations Count</heading>
 168         <query>SELECT COUNT(id) FROM m_action_non_cataloged_circulation</query>
 169     </report>
 170
 171      <report>
 172         <name>in_house</name>
 173         <tag>circs</tag>
 174         <iteration>0</iteration>
 175         <report_title>In House Use</report_title>
 176         <heading>In House Use Records.Migrated</heading>
 177         <query>SELECT COUNT(id), x_migrate::TEXT FROM m_action_in_house_use_legacy GROUP BY 2</query>
 178     </report>
 179
 180      <report>
 181         <name>in_house</name>
 182         <tag>circs</tag>
 183         <iteration>1</iteration>
 184         <report_title>In House Use</report_title>
 185         <heading>In House Use Records</heading>
 186         <query>SELECT COUNT(id) FROM m_action_in_house_use</query>
 187     </report>
 188
 189      <report>
 190         <name>circs_missing_rules</name>
 191         <tag>circs</tag>
 192         <iteration>1</iteration>
 193         <report_title>Circs Missing Rules</report_title>
 194         <heading>Count.Field Missing</heading>
 195         <query>SELECT COUNT(id), 'Duration Rule Value' FROM m_action_circulation WHERE duration IS NULL
 196             UNION ALL SELECT COUNT(id), 'Recurring Fine Rule Value' FROM m_action_circulation WHERE recurring_fine IS NULL
 197             UNION ALL SELECT COUNT(id), 'Max Fine Rule Value' FROM m_action_circulation WHERE max_fine IS NULL
 198             UNION ALL SELECT COUNT(id), 'Duration Rule' FROM m_action_circulation WHERE duration_rule IS NULL
 199             UNION ALL SELECT COUNT(id), 'Recurring Fine Rule' FROM m_action_circulation WHERE recurring_fine_rule IS NULL
 200             UNION ALL SELECT COUNT(id), 'Max Fine Rule' FROM m_action_circulation WHERE max_fine_rule IS NULL
 201         </query>
 202     </report>
 203
 204      <report>
 205         <name>circ_open_by_item_status</name>
 206         <tag>circs</tag>
 207         <iteration>0</iteration>
 208         <report_title>Status of Currently Circulating Items</report_title>
 209         <heading>Count.Status</heading>
 210         <query>SELECT COUNT(acirc.id), ccs.name FROM action.circulation acirc JOIN asset.copy ac ON ac.id = acirc.target_copy JOIN config.copy_status ccs ON ccs.id = ac.status WHERE acirc.xact_finish IS NULL AND acirc.checkin_time IS NULL AND acirc.id IN (SELECT id FROM m_action_circulation) GROUP BY 2 ORDER BY 2</query>
 211     </report>
 212
 213     <!-- HOLDS REPORTS -->
 214
 215      <report>
 216         <name>holds</name>
 217         <tag>holds</tag>
 218         <iteration>0</iteration>
 219         <report_title>Migrated and Non-Migrated Holds</report_title>
 220         <heading>Hold Type.Hold Count.Migrated</heading>
 221         <query>SELECT 'Closed Holds', COUNT(id), x_migrate::TEXT FROM m_action_hold_request_legacy WHERE (expire_time::TIMESTAMP &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 m_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>
 222     </report>
 223
 224      <report>
 225         <name>holds_bytype</name>
 226         <tag>holds</tag>
 227         <iteration>0</iteration>
 228         <report_title>Migrated Holds By Type</report_title>
 229         <heading>Hold Type.Hold Count</heading>
 230         <query>SELECT hold_type as "Hold Type", COUNT(id) FROM m_action_hold_request_legacy WHERE x_migrate = TRUE GROUP BY 1</query>
 231     </report>
 232
 233      <report>
 234         <name>transit_open_by_item_status</name>
 235         <tag>holds</tag>
 236         <iteration>0</iteration>
 237         <report_title>Transit Copy Records and Status of Linked Items</report_title>
 238         <heading>Count.Status</heading>
 239         <query>SELECT COUNT(atc.id), ccs.name FROM action.transit_copy atc JOIN asset.copy ac ON ac.id = atc.target_copy JOIN config.copy_status ccs ON ccs.id = ac.status WHERE atc.id IN (SELECT id FROM m_action_transit_copy) AND atc.dest_recv_time IS NULL GROUP BY 2 ORDER BY 2</query>
 240     </report>
 241
 242      <report>
 243         <name>transit_copies_by_status</name>
 244         <tag>holds</tag>
 245         <iteration>0</iteration>
 246         <report_title>Status of Items with Count of Open In Transits</report_title>
 247         <heading>Count.Status.Count of Open Transits</heading>
 248         <query>SELECT COUNT(ac.id), ccs.name, SUM(CASE WHEN atc.id IS NULL THEN 0 ELSE 1 END) FROM asset.copy ac JOIN config.copy_status ccs ON ccs.id = ac.status LEFT JOIN (SELECT * FROM action.transit_copy WHERE id IN (SELECT id FROM m_action_transit_copy) AND dest_recv_time IS NULL) atc ON atc.target_copy = ac.id WHERE ac.id IN (SELECT id from m_asset_copy) GROUP BY 2 ORDER BY 2</query>
 249     </report>
 250
 251      <report>
 252         <name>hold_copies_by_status</name>
 253         <tag>holds</tag>
 254         <iteration>0</iteration>
 255         <report_title>Captured Holds with Status of Items</report_title>
 256         <heading>Count of Captured Hold.Status of Item</heading>
 257         <query>SELECT COUNT(ahr.id), ccs.name FROM action.hold_request ahr JOIN asset.copy ac ON ac.id = ahr.current_copy JOIN config.copy_status ccs ON ccs.id = ac.status WHERE ahr.capture_time IS NOT NULL AND ahr.fulfillment_time IS NULL and ahr.cancel_time IS NULL AND ahr.id IN (SELECT id FROM m_action_hold_request) GROUP BY 2 ORDER By 2</query>
 258     </report>
 259
 260       <report>
 261         <name>hold_depth</name>
 262         <tag>holds</tag>
 263         <iteration>0</iteration>
 264         <report_title>Depth of Unfilled Holds</report_title>
 265         <heading>Count.Depth</heading>
 266         <query>SELECT COUNT(ahr.id), ahr.selection_depth FROM action.hold_request ahr WHERE ahr.id IN (SELECT id FROM m_action_hold_request) AND ahr.cancel_time IS NULL AND ahr.capture_time IS NULL AND ahr.fulfillment_time IS NULL GROUP BY 2 ORDER BY 2</query>
 267     </report>
 268
 269     <!-- ASSET REPORTS -->
 270
 271     <report>
 272         <name>m_asset_copy_count</name>
 273         <report_title>Count of Copies by Library</report_title>
 274         <tag>assets</tag>
 275         <iteration>0</iteration>
 276         <heading>Copy Count.Library</heading>
 277         <query>SELECT COUNT(ac.id), aou.name FROM m_asset_copy_legacy ac JOIN actor.org_unit aou ON aou.id = ac.circ_lib WHERE ac.x_migrate = TRUE GROUP BY 2 ORDER BY 2</query>
 278     </report>
 279
 280     <report>
 281         <name>m_asset_copy_count_non_migrated</name>
 282         <report_title>Non-Migrated Count of Copies by Library</report_title>
 283         <tag>assets</tag>
 284         <iteration>0</iteration>
 285         <heading>Copy Count.Library</heading>
 286         <query>SELECT COUNT(ac.id), aou.name FROM m_asset_copy_legacy ac JOIN actor.org_unit aou ON aou.id = ac.circ_lib WHERE ac.x_migrate = FALSE GROUP BY 2 ORDER BY 2</query>
 287     </report>
 288
 289     <report>
 290         <name>m_asset_copies_by_status</name>
 291         <report_title>Copies by Status</report_title>
 292         <tag>assets</tag>
 293         <iteration>0</iteration>
 294         <heading>Copy Count.Status</heading>
 295         <query>SELECT COUNT(ac.id), cs.name FROM m_asset_copy_legacy ac JOIN config.copy_status cs ON cs.id = ac.status WHERE ac.x_migrate = TRUE GROUP BY 2 ORDER BY 2</query>
 296     </report>
 297
 298     <report>
 299         <name>m_asset_circ_mod_copies_count</name>
 300         <report_title>Copies by Circulation Modifier</report_title>
 301         <tag>assets</tag>
 302         <iteration>0</iteration>
 303         <heading>Copy Count.Circulation Modifier</heading>
 304         <query>SELECT COUNT(ac.id), ac.circ_modifier FROM m_asset_copy_legacy ac WHERE ac.x_migrate = TRUE GROUP BY 2 ORDER BY 2</query>
 305     </report>
 306
 307     <report>
 308         <name>m_asset_copy_notes</name>
 309         <report_title>Copy Notes</report_title>
 310         <tag>assets</tag>
 311         <iteration>0</iteration>
 312         <heading>Note Count.Public</heading>
 313         <query>SELECT COUNT(acnote.id), acnote.pub::TEXT FROM m_asset_copy_note_legacy acnote WHERE acnote.x_migrate = TRUE GROUP BY 2 ORDER BY 2</query>
 314     </report>
 315
 316     <report>
 317         <name>m_asset_copy_notes</name>
 318         <report_title>Copy Notes</report_title>
 319         <tag>assets</tag>
 320         <iteration>1</iteration>
 321         <heading>Note Count.Public</heading>
 322         <query>SELECT COUNT(acnote.id), acnote.pub::TEXT FROM m_asset_copy_note acnote GROUP BY 2 ORDER BY 2</query>
 323     </report>
 324
 325    <report>
 326         <name>m_asset_vols_by_lib</name>
 327         <report_title>Volumes by Library</report_title>
 328         <tag>assets</tag>
 329         <iteration>0</iteration>
 330         <heading>Volume Count.Library</heading>
 331         <query>SELECT COUNT(acn.id), aou.name FROM m_asset_call_number_legacy acn JOIN m_actor_org_unit_legacy aou ON aou.id = acn.owning_lib WHERE acn.x_migrate = TRUE GROUP BY 2 ORDER BY 2</query>
 332     </report>
 333
 334    <report>
 335         <name>m_asset_vols_by_lib</name>
 336         <report_title>Volumes by Library</report_title>
 337         <tag>assets</tag>
 338         <iteration>1</iteration>
 339         <heading>Volume Count.Library</heading>
 340         <query>SELECT COUNT(acn.id), aou.name FROM m_asset_call_number acn JOIN actor.org_unit aou ON aou.id = acn.owning_lib GROUP BY 2 ORDER BY 2</query>
 341     </report>
 342
 343    <report>
 344         <name>m_asset_cops_by_loc_and_org</name>
 345         <report_title>Copies by Location</report_title>
 346         <tag>assets</tag>
 347         <iteration>0</iteration>
 348         <heading>Copy Count.Library.Circ Library</heading>
 349         <query>SELECT COUNT(ac.id), acl.name, aou.name FROM m_asset_copy_legacy ac JOIN asset.copy_location acl ON acl.id = ac.location JOIN actor.org_unit aou ON aou.id = ac.circ_lib WHERE ac.x_migrate = TRUE GROUP BY 2, 3 ORDER BY 2, 3</query>
 350     </report>
 351
 352    <report>
 353         <name>m_asset_cops_w_loc_one</name>
 354         <report_title>Copies with a Location of Stacks</report_title>
 355         <tag>assets</tag>
 356         <iteration>0</iteration>
 357         <heading>Barcode</heading>
 358         <query>SELECT barcode FROM m_asset_copy_legacy WHERE location = 1 AND x_migrate</query>
 359     </report>
 360
 361     <report>
 362         <name>m_asset_no_barcode</name>
 363         <report_title>Items Without Barcodes</report_title>
 364         <tag>assets</tag>
 365         <iteration>0</iteration>
 366         <heading>Assigned Barcode</heading>
 367         <query>SELECT barcode FROM m_asset_copy_legacy WHERE barcode ~* 'no_barocde' AND x_migrate</query>
 368     </report>
 369
 370     <report>
 371         <name>m_asset_barcode_patterns</name>
 372         <report_title>Common Barcode Starting Patterns</report_title>
 373         <tag>assets</tag>
 374         <iteration>0</iteration>
 375         <heading>Count of Barcodes (greater than 10).Left 60% of Characters</heading>
 376         <query>SELECT COUNT(ac.id), LEFT(ac.barcode,(ROUND(LENGTH(ac.barcode)*.6))::INT) FROM m_asset_copy_legacy ac WHERE ac.x_migrate = TRUE GROUP BY 2 HAVING COUNT(ac.id) > 10 ORDER BY 2</query>
 377     </report>
 378
 379     <report>
 380         <name>m_asset_barcode_collisions</name>
 381         <report_title>Copy Barcode Collisions</report_title>
 382         <tag>assets</tag>
 383         <iteration>0</iteration>
 384         <heading>Collision Count</heading>
 385         <query>SELECT COUNT(id) FROM m_asset_copy_legacy WHERE x_migrate = TRUE AND barcode LIKE 'x_%'</query>
 386         <note>Incumbent collisions are those where the migrated barcodes collide with existing barcodes in the database.</note>
 387     </report>
 388
 389     <report>
 390         <name>m_asset_barcode_collisions</name>
 391         <report_title>Copy Barcode Collisions</report_title>
 392         <tag>assets</tag>
 393         <iteration>1</iteration>
 394         <heading>Collision Count</heading>
 395         <query>SELECT COUNT(id) FROM m_asset_copy WHERE barcode LIKE 'x_%'</query>
 396         <note>Incumbent collisions are those where the migrated barcodes collide with existing barcodes in the database.</note>
 397     </report>
 398
 399     <report>
 400         <name>m_asset_barcode_collisions_shortlist</name>
 401         <report_title>Copy Barcode Collisions (first 20)</report_title>
 402         <tag>assets</tag>
 403         <iteration>0</iteration>
 404         <heading>Collision List</heading>
 405         <query>SELECT ac.barcode FROM m_asset_copy_legacy ac WHERE ac.barcode LIKE 'x_%' ORDER BY 1 LIMIT 20</query>
 406         <note>This is a shortlist of copy barcode collisions that maxes out at 20.  If there are more collisions we will need to run a custom report.</note>
 407     </report>
 408
 409     <report>
 410         <name>m_asset_barcode_collisions_shortlist</name>
 411         <report_title>Copy Barcode Collisions (first 20)</report_title>
 412         <tag>assets</tag>
 413         <iteration>1</iteration>
 414         <heading>Collision List</heading>
 415         <query>SELECT ac.barcode FROM m_asset_copy ac WHERE ac.barcode LIKE 'x_%' ORDER BY 1 LIMIT 20</query>
 416         <note>This is a shortlist of patron barcode collisions that maxes out at 20.  If there are more collisions we will need to run a custom report.</note>
 417     </report>
 418
 419     <report>
 420         <name>m_asset_barcode_collision_patterns</name>
 421         <report_title>Common Copy Barcode Collision Patterns</report_title>
 422         <tag>assets</tag>
 423         <iteration>0</iteration>
 424         <heading>Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters</heading>
 425         <query>SELECT COUNT(ac.id), LEFT(ac.barcode,(ROUND(LENGTH(ac.barcode)*.6))::INT) FROM m_asset_copy_legacy ac WHERE barcode LIKE 'x_%' GROUP BY 2 HAVING COUNT(ac.id) > 10 ORDER BY 2</query>
 426     </report>
 427
 428     <report>
 429         <name>m_asset_barcode_collision_patterns</name>
 430         <report_title>Common Copy Barcode Collision Patterns</report_title>
 431         <tag>assets</tag>
 432         <iteration>1</iteration>
 433         <heading>Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters</heading>
 434         <query>SELECT COUNT(ac.id), LEFT(ac.barcode,(ROUND(LENGTH(ac.barcode)*.6))::INT) FROM m_asset_copy ac WHERE barcode LIKE 'x_%' GROUP BY 2 HAVING COUNT(ac.id) > 10 ORDER BY 2</query>
 435     </report>
 436
 437     <report>
 438          <name>m_asset_stat_cats</name>
 439         <report_title>Copy Statistical Categories</report_title>
 440         <tag>assets</tag>
 441         <iteration>0</iteration>
 442         <heading>Stat Cat Count.Library.Statistical Category</heading>
 443         <query>SELECT COUNT(ac_sc.id), aou.name, ac_sc.name FROM m_asset_stat_cat_legacy ac_sc JOIN actor.org_unit aou ON aou.id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3</query>
 444     </report>
 445
 446     <report>
 447          <name>m_asset_stat_cats</name>
 448         <report_title>Copy Statistical Categories</report_title>
 449         <tag>assets</tag>
 450         <iteration>1</iteration>
 451         <heading>Stat Cat Count.Library.Statistical Category</heading>
 452         <query>SELECT COUNT(ac_sc.id), aou.name, ac_sc.name FROM m_asset_stat_cat ac_sc JOIN actor.org_unit aou ON aou.id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3</query>
 453     </report>
 454
 455     <report>
 456         <name>m_asset_stat_cat_entries</name>
 457         <report_title>Copy Stat Cat User Entries</report_title>
 458         <tag>assets</tag>
 459         <iteration>0</iteration>
 460         <heading>Copy Stat Count.Library.Statistical Category</heading>
 461         <query>SELECT COUNT(map.id), aou.name, ac_sc.name FROM m_asset_stat_cat_entry_copy_map_legacy map JOIN m_asset_stat_cat_legacy ac_sc ON ac_sc.id = map.stat_cat JOIN actor.org_unit aou ON aou.id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3</query>
 462     </report>
 463
 464     <report>
 465         <name>m_asset_stat_cat_entries</name>
 466         <report_title>Copy Stat Cat User Entries</report_title>
 467         <tag>assets</tag>
 468         <iteration>1</iteration>
 469         <heading>Copy Stat Count.Library.Statistical Category</heading>
 470         <query>SELECT COUNT(map.id), aou.name, ac_sc.name FROM m_asset_stat_cat_entry_copy_map map JOIN m_asset_stat_cat ac_sc ON ac_sc.id = map.stat_cat JOIN actor.org_unit aou ON aou.
 471 id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3</query>
 472     </report>
 473
 474     <report>
 475          <name>m_asset_copy_tags</name>
 476         <report_title>Copy Tags</report_title>
 477         <tag>assets</tag>
 478         <iteration>0</iteration>
 479         <heading>Tag Count.Copy Tag Type.Copy Tag Label.Staff Note.Public</heading>
 480         <query>SELECT COUNT(map.id), tag.tag_type, tag.label, tag.staff_note, tag.pub FROM m_asset_copy_tag tag JOIN m_asset_copy_tag_copy_map map ON map.tag = tag.id GROUP BY 2,3,4,5 ORDER BY 2,3</query>
 481     </report>
 482
 483     <report>
 484          <name>m_asset_copy_alerts</name>
 485         <report_title>Copy Alerts</report_title>
 486         <tag>assets</tag>
 487         <iteration>0</iteration>
 488         <heading>Alert Count.Alert Type</heading>
 489                 <query>SELECT COUNT(*), cat.name FROM m_asset_copy_alert aca JOIN config.copy_alert_type cat ON cat.id = aca.alert_type GROUP BY 2</query>
 490     </report>
 491
 492     <!-- MONEY REPORTS -->
 493
 494     <report>
 495         <name>m_money_billing_voided</name>
 496         <report_title>Bills Voided And Not</report_title>
 497         <tag>money</tag>
 498         <iteration>0</iteration>
 499         <heading>Count.Voided.Sum.Migrated</heading>
 500         <query>SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM m_money_billing_legacy a GROUP BY 2, 4 ORDER BY 2, 4</query>
 501     </report>
 502
 503     <report>
 504         <name>m_money_billing_voided</name>
 505         <report_title>Bills Voided And Not</report_title>
 506         <tag>money</tag>
 507         <iteration>1</iteration>
 508         <heading>Count.Voided.Sum</heading>
 509         <query>SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM m_money_billing a GROUP BY 2 ORDER BY 2, 3</query>
 510     </report>
 511
 512     <report>
 513         <name>m_money_billing_by_type</name>
 514         <report_title>Bills by Type</report_title>
 515         <tag>money</tag>
 516         <iteration>0</iteration>
 517         <heading>Count.Billing Type.Migrated</heading>
 518         <query>SELECT COUNT(a.id), a.billing_type, a.x_migrate::TEXT FROM m_money_billing_legacy a GROUP BY 2, 3 ORDER BY 2, 3</query>
 519     </report>
 520
 521     <report>
 522         <name>m_money_billing_by_type</name>
 523         <report_title>Bills by Type</report_title>
 524         <tag>money</tag>
 525         <iteration>1</iteration>
 526         <heading>Count.Billing Type</heading>
 527         <query>SELECT COUNT(a.id), a.billing_type FROM m_money_billing a GROUP BY 2 ORDER BY 2</query>
 528     </report>
 529
 530     <report>
 531         <name>m_money_cash_payment</name>
 532         <report_title>Cash Payments</report_title>
 533         <tag>money</tag>
 534         <iteration>0</iteration>
 535         <heading>Count.Voided.Sum.Migrated</heading>
 536         <query>SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM m_money_cash_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4</query>
 537     </report>
 538
 539     <report>
 540         <name>m_money_cash_payment</name>
 541         <report_title>Cash Payments</report_title>
 542         <tag>money</tag>
 543         <iteration>1</iteration>
 544         <heading>Count.Voided.Sum</heading>
 545         <query>SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM m_money_cash_payment a GROUP BY 2 ORDER BY 2</query>
 546     </report>
 547
 548     <report>
 549         <name>m_money_check_payment</name>
 550         <report_title>Check Payments</report_title>
 551         <tag>money</tag>
 552         <iteration>0</iteration>
 553         <heading>Count.Voided.Sum.Migrated</heading>
 554         <query>SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM m_money_check_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4</query>
 555     </report>
 556
 557     <report>
 558         <name>m_money_forgive_payment</name>
 559         <report_title>Forgive Payments</report_title>
 560         <tag>money</tag>
 561         <iteration>0</iteration>
 562         <heading>Count.Voided.Sum.Migrated</heading>
 563         <query>SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM m_money_forgive_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4</query>
 564     </report>
 565
 566     <report>
 567         <name>m_money_forgive_payment</name>
 568         <report_title>Forgive Payments</report_title>
 569         <tag>money</tag>
 570         <iteration>1</iteration>
 571         <heading>Count.Voided.Sum</heading>
 572         <query>SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM m_money_forgive_paymen a GROUP BY 2 ORDER BY 2</query>
 573     </report>
 574
 575     <report>
 576         <name>m_money_goods_payment</name>
 577         <report_title>Goods Payments</report_title>
 578         <tag>money</tag>
 579         <iteration>0</iteration>
 580         <heading>Count.Voided.Sum.Migrated</heading>
 581         <query>SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM m_money_goods_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4</query>
 582     </report>
 583
 584     <report>
 585         <name>m_money_work_payment</name>
 586         <report_title>Work Payments</report_title>
 587         <tag>money</tag>
 588         <iteration>0</iteration>
 589         <heading>Count.Voided.Sum.Migrated</heading>
 590         <query>SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM m_money_work_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4</query>
 591     </report>
 592
 593     <report>
 594         <name>m_money_credit_card_payment</name>
 595         <report_title>Credit Card Payments</report_title>
 596         <tag>money</tag>
 597         <iteration>0</iteration>
 598         <heading>Count.Voided.Sum.Migrated</heading>
 599         <query>SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM m_money_credit_card_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4</query>
 600     </report>
 601
 602     <report>
 603         <name>m_money_credit_payment</name>
 604         <report_title>Credit Payments</report_title>
 605         <tag>money</tag>
 606         <iteration>0</iteration>
 607         <heading>Count.Voided.Sum.Migrated</heading>
 608         <query>SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM m_money_credit_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4</query>
 609     </report>
 610
 611
 612     <!-- BIBS REPORTS -->
 613
 614     <report>
 615         <name>bibs_loaded</name>
 616         <report_title>Loaded Bibliographic Records</report_title>
 617         <tag>bibs</tag>
 618         <iteration>0</iteration>
 619         <heading>Count</heading>
 620         <query>SELECT COUNT(bre.id) FROM m_biblio_record_entry_legacy bre WHERE id IN (SELECT id FROM biblio.record_entry) ORDER BY 1</query>
 621     </report>
 622
 623     <report>
 624         <name>bibs_loaded</name>
 625         <report_title>Loaded Bibliographic Records</report_title>
 626         <tag>bibs</tag>
 627         <iteration>1</iteration>
 628         <heading>Count</heading>
 629         <query>SELECT COUNT(bre.id) FROM m_biblio_record_entry bre WHERE id IN (SELECT id FROM biblio.record_entry) ORDER BY 1</query>
 630     </report>
 631
 632     <report>
 633         <name>bibswovolumes</name>
 634         <report_title>Bibliographic Records Without Volumes</report_title>
 635         <tag>bibs</tag>
 636         <iteration>0</iteration>
 637         <heading>Count</heading>
 638         <query>SELECT COUNT(id) FROM m_biblio_record_entry where id NOT IN (SELECT DISTINCT record FROM m_asset_call_number)</query>
 639                 <note>These records would not have been loaded but many may be deduplicated against incumbent records.</note>
 640     </report>
 641
 642     <report>
 643         <name>bibswuri</name>
 644         <report_title>Bibliographic Records With 856$9s</report_title>
 645         <tag>bibs</tag>
 646         <iteration>0</iteration>
 647         <heading>Count</heading>
 648         <query>SELECT COUNT(id) FROM m_biblio_record_entry where id IN (SELECT record FROM asset.call_number WHERE label ~* '##URI##')</query>
 649     </report>
 650
 651         <report>
 652                 <name>bibsff</name>
 653                 <report_title>Bibliographic Records with Adjusted Fixed Fields</report_title>
 654                 <tag>bibs</tag>
 655                 <iteration>0</iteration>
 656                 <heading>Count.Original Search Format.New Search Format</heading>
 657                 <query>SELECT COUNT(*), ARRAY_TO_STRING(x_search_format,','), ARRAY_TO_STRING(x_after_search_format,',') FROM m_biblio_record_entry_legacy WHERE x_migrate AND x_after_search_format IS NOT NULL GROUP BY 2, 3 ORDER BY 3,2</query>
 658         </report>
 659
 660     <report>
 661         <name>bibs_notes</name>
 662         <report_title>Bib Record Notes</report_title>
 663         <tag>bibs</tag>
 664         <iteration>0</iteration>
 665         <heading>Count.Migrated</heading>
 666         <query>SELECT COUNT(b.id), b.x_migrate::TEXT FROM m_biblio_record_note_legacy b GROUP BY 2</query>
 667     </report>
 668
 669     <report>
 670         <name>bibs_notes</name>
 671         <report_title>Bib Record Notes</report_title>
 672         <tag>bibs</tag>
 673         <iteration>1</iteration>
 674         <heading>Count</heading>
 675         <query>SELECT COUNT(b.id) FROM m_biblio_record_note b</query>
 676     </report>
 677
 678     <report>
 679         <name>bibs_peers</name>
 680         <report_title>Peer Bib Copies</report_title>
 681         <tag>bibs</tag>
 682         <iteration>0</iteration>
 683         <heading>Count.Migrated</heading>
 684         <query>SELECT COUNT(b.id), b.x_migrate::TEXT FROM m_biblio_peer_bib_copy_map_legacy b GROUP BY 2</query>
 685     </report>
 686
 687     <report>
 688         <name>bibs_peers</name>
 689         <report_title>Peer Bib Copies</report_title>
 690         <tag>bibs</tag>
 691         <iteration>1</iteration>
 692         <heading>Count</heading>
 693         <query>SELECT COUNT(b.id) FROM m_biblio_peer_bib_copy_map b</query>
 694     </report>
 695
 696     <report>
 697         <name>bibs_parts</name>
 698         <report_title>Monograph Parts</report_title>
 699         <tag>bibs</tag>
 700         <iteration>0</iteration>
 701         <heading>Count.Migrated</heading>
 702         <query>SELECT COUNT(b.id), b.x_migrate::TEXT FROM m_biblio_monograph_part_legacy b GROUP BY 2</query>
 703     </report>
 704
 705     <report>
 706         <name>bibs_parts</name>
 707         <report_title>Monograph Parts</report_title>
 708         <tag>bibs</tag>
 709         <iteration>1</iteration>
 710         <heading>Count</heading>
 711         <query>SELECT COUNT(b.id) FROM m_biblio_monograph_part b</query>
 712     </report>
 713
 714     <report>
 715         <name>bib_merges</name>
 716         <report_title>Bibliographic Merge Count</report_title>
 717         <tag>bibs</tag>
 718         <iteration>0</iteration>
 719         <heading>Records Merged.Incumbent Records Merged Into</heading>
 720         <query>SELECT SUM(array_length(records,1)), COUNT(*) FROM groups</query>
 721     </report>
 722
 723     <!-- ACTORS REPORTS -->
 724
 725     <report>
 726         <name>usrsbyorg</name>
 727         <report_title>Migrated Patrons by Home Org</report_title>
 728         <tag>actors</tag>
 729         <iteration>0</iteration>
 730         <heading>Count.Library</heading>
 731         <query>SELECT COUNT(au.id), aou.name FROM m_actor_usr_legacy au JOIN actor.org_unit aou ON aou.id = au.home_ou WHERE au.x_migrate = TRUE GROUP BY 2 ORDER BY 2</query>
 732     </report>
 733
 734     <report>
 735         <name>nonmigusrsbyorg</name>
 736         <report_title>Non-Migrated Patrons by Home Org</report_title>
 737         <tag>actors</tag>
 738         <iteration>0</iteration>
 739         <heading>Count.Library</heading>
 740         <query>SELECT COUNT(au.id), aou.name FROM m_actor_usr_legacy au JOIN actor.org_unit aou ON aou.id = au.home_ou WHERE au.x_migrate = FALSE GROUP BY 2 ORDER BY 2</query>
 741     </report>
 742
 743     <report>
 744         <name>usrsbypgt</name>
 745         <report_title>Migrated Patrons by Permission Group</report_title>
 746         <tag>actors</tag>
 747         <iteration>0</iteration>
 748         <heading>Count.Permission Group</heading>
 749         <query>SELECT COUNT(au.id), pgt.name FROM m_actor_usr_legacy au JOIN permission.grp_tree pgt ON pgt.id = au.profile WHERE au.x_migrate = TRUE GROUP BY 2 ORDER BY 2</query>
 750     </report>
 751
 752     <report>
 753         <name>active_usrs</name>
 754         <report_title>Patron by Active Status</report_title>
 755         <tag>actors</tag>
 756         <iteration>0</iteration>
 757         <heading>Count of Users.Active</heading>
 758         <query>SELECT COUNT(id), active::TEXT FROM m_actor_usr_legacy GROUP BY 2</query>
 759     </report>
 760
 761     <report>
 762         <name>active_usrs</name>
 763         <report_title>Patron Cards</report_title>
 764         <tag>actors</tag>
 765         <iteration>1</iteration>
 766         <heading>Count of Users</heading>
 767         <query>SELECT COUNT(id) FROM m_actor_usr</query>
 768     </report>
 769
 770     <report>
 771         <name>active_usr_barcodes</name>
 772         <report_title>Patron Barcodes by Active Status</report_title>
 773         <tag>actors</tag>
 774         <iteration>0</iteration>
 775         <heading>Count of Barcodes.Active.Migrated</heading>
 776         <query>SELECT COUNT(id), active::TEXT, x_migrate::TEXT FROM m_actor_card_legacy GROUP BY 2, 3</query>
 777     </report>
 778
 779     <report>
 780         <name>active_usr_barcodes</name>
 781         <report_title>Patron Barcodes by Active Status</report_title>
 782         <tag>actors</tag>
 783         <iteration>1</iteration>
 784         <heading>Count of Barcodes.Active</heading>
 785         <query>SELECT COUNT(id), active::TEXT FROM m_actor_card GROUP BY 2</query>
 786     </report>
 787
 788     <report>
 789         <name>usr_barcode_patterns</name>
 790         <report_title>Common Barcode Starting Patterns</report_title>
 791         <tag>actors</tag>
 792         <iteration>0</iteration>
 793         <heading>Count of Barcodes (greater than 10).Left 60% of Characters</heading>
 794         <query>SELECT COUNT(acard.id), LEFT(acard.barcode,(ROUND(LENGTH(acard.barcode)*.6))::INT) FROM m_actor_card_legacy acard WHERE acard.x_migrate = TRUE GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2</query>
 795     </report>
 796
 797     <report>
 798         <name>usr_barcode_patterns</name>
 799         <report_title>Common Barcode Starting Patterns</report_title>
 800         <tag>actors</tag>
 801         <iteration>1</iteration>
 802         <heading>Count of Barcodes (greater than 10).Left 60% of Characters</heading>
 803         <query>SELECT COUNT(acard.id), LEFT(acard.barcode,(ROUND(LENGTH(acard.barcode)*.6))::INT) FROM m_actor_card acard GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2</query>
 804     </report>
 805
 806    <report>
 807         <name>usr_barcode_collisions</name>
 808         <report_title>Patron Barcode Collisions</report_title>
 809         <tag>actors</tag>
 810         <iteration>0</iteration>
 811         <heading>Collision Count</heading>
 812         <query>SELECT COUNT(acard.id) FROM m_actor_card_legacy acard WHERE barcode LIKE 'x_%' AND x_migrate = TRUE</query>
 813     </report>
 814
 815     <report>
 816         <name>usr_barcode_collisions</name>
 817         <report_title>Patron Barcode Collisions</report_title>
 818         <tag>actors</tag>
 819         <iteration>1</iteration>
 820         <heading>Collision Count</heading>
 821         <query>SELECT COUNT(acard.id) FROM m_actor_card acard WHERE barcode LIKE 'x_%'</query>
 822     </report>
 823
 824     <report>
 825         <name>usr_barcode_collision_shortlist</name>
 826         <report_title>Patron Barcode Collisions (first 20)</report_title>
 827         <tag>actors</tag>
 828         <iteration>0</iteration>
 829         <heading>Collision List</heading>
 830         <query>SELECT acard.barcode FROM m_actor_card_legacy acard WHERE acard.barcode LIKE 'x_' AND acard.x_migrate = TRUE ORDER BY 1 LIMIT 20</query>
 831         <note>This is a shortlist of patron barcode collisions that maxes out at 20.  If there are more collisions we will need to run a custom report.  In some cases we may flag individual accounts to not migrate.</note>
 832     </report>
 833
 834     <report>
 835         <name>usr_barcode_collision_shortlist</name>
 836         <report_title>Patron Barcode Collisions (first 20)</report_title>
 837         <tag>actors</tag>
 838         <iteration>1</iteration>
 839         <heading>Collision List</heading>
 840         <query>SELECT acard.barcode FROM m_actor_card acard WHERE acard.barcode LIKE 'x_%' ORDER BY 1 LIMIT 20</query>
 841         <note>This is a shortlist of patron barcode collisions that maxes out at 20.  If there are more collisions we will need to run a custom report.  In some cases we may flag individual accounts to not migrate.</note>
 842     </report>
 843
 844     <report>
 845         <name>usr_barcode_collision_patterns</name>
 846         <report_title>Common Patron Barcode Collision Patterns</report_title> a.x_migrate
 847         <tag>actors</tag>
 848         <iteration>0</iteration>
 849         <heading>Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters</heading>
 850         <query>SELECT COUNT(acard.id), LEFT(acard.barcode,(ROUND(LENGTH(acard.barcode)*.6))::INT) FROM m_actor_card_legacy acard WHERE (acard.barcode ~* 'collision' OR acard.barcode ~* '^x_') AND acard.x_migrate = TRUE GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2</query>
 851     </report>
 852
 853     <report>
 854         <name>usr_barcode_collision_patterns</name>
 855         <report_title>Common Patron Barcode Collision Patterns</report_title> a.x_migrate
 856         <tag>actors</tag>
 857         <iteration>1</iteration>
 858         <heading>Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters</heading>
 859         <query>SELECT COUNT(acard.id), LEFT(acard.barcode,(ROUND(LENGTH(acard.barcode)*.6))::INT) FROM m_actor_card acard WHERE (acard.barcode ~* 'collision' OR acard.barcode ~* '^x_') GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2</query>
 860     </report>
 861
 862     <report>
 863         <name>usr_addressses_status</name>
 864         <report_title>Patron Addresses</report_title>
 865         <tag>actors</tag>
 866         <iteration>0</iteration>
 867         <heading>Count</heading>
 868         <query>SELECT COUNT(aua.id) FROM m_actor_usr_address_legacy aua WHERE aua.x_migrate = TRUE</query>
 869     </report>
 870
 871     <report>
 872         <name>usr_addressses_status</name>
 873         <report_title>Patron Addresses</report_title>
 874         <tag>actors</tag>
 875         <iteration>1</iteration>
 876         <heading>Count</heading>
 877         <query>SELECT COUNT(aua.id) FROM m_actor_usr_address aua</query>
 878     </report>
 879
 880     <report>
 881         <name>usr_addresses_pending</name>
 882         <report_title>Patron Addresses by Pending Status</report_title>
 883         <tag>actors</tag>
 884         <iteration>0</iteration>
 885         <heading>Count of Addresses.Pending</heading>
 886         <query>SELECT COUNT(aua.id), pending::TEXT FROM m_actor_usr_address_legacy aua WHERE aua.x_migrate = TRUE GROUP BY 2</query>
 887     </report>
 888
 889     <report>
 890         <name>usr_addresses_pending</name>
 891         <report_title>Patron Addresses by Pending Status</report_title>
 892         <tag>actors</tag>
 893         <iteration>1</iteration>
 894         <heading>Count of Addresses.Pending</heading>
 895         <query>SELECT COUNT(aua.id), pending::TEXT FROM m_actor_usr_address aua GROUP BY 2</query>
 896     </report>
 897
 898     <report>
 899         <name>usr_messages</name>
 900         <report_title>Patron Messages</report_title>
 901         <tag>actors</tag>
 902         <iteration>0</iteration>
 903         <heading>Count.Deleted.Migrated</heading>
 904         <query>SELECT COUNT(aum.id), deleted::TEXT, x_migrate::TEXT FROM m_actor_usr_message_legacy aum GROUP BY 2, 3</query>
 905     </report>
 906
 907     <report>
 908         <name>usr_messages</name>
 909         <report_title>Patron Messages</report_title>
 910         <tag>actors</tag>
 911         <iteration>1</iteration>
 912         <heading>Count.Deleted</heading>
 913         <query>SELECT COUNT(aum.id), deleted::TEXT FROM m_actor_usr_message_legacy aum GROUP BY 2</query>
 914     </report>
 915
 916     <report>
 917         <name>usr_notes</name>
 918         <report_title>Patron Notes</report_title>
 919         <tag>actors</tag>
 920         <iteration>0</iteration>
 921         <heading>Count.Public.Migrated</heading>
 922         <query>SELECT COUNT(aun.id), pub::TEXT, x_migrate::TEXT FROM m_actor_usr_note_legacy aun GROUP BY 2, 3</query>
 923     </report>
 924
 925     <report>
 926         <name>usr_notes</name>
 927         <report_title>Patron Notes</report_title>
 928         <tag>actors</tag>
 929         <iteration>1</iteration>
 930         <heading>Count.Public</heading>
 931         <query>SELECT COUNT(aun.id), pub::TEXT FROM m_actor_usr_note aun GROUP BY 2</query>
 932     </report>
 933
 934     <report>
 935          <name>usr_stat_cats</name>
 936         <report_title>Patron Statistical Categories</report_title>
 937         <tag>actors</tag>
 938         <iteration>0</iteration>
 939         <heading>Stat Cat Count.Library.Statistical Category.Migrated</heading>
 940         <query>SELECT COUNT(au_sc.id), aou.name, au_sc.name, au_sc.x_migrate::TEXT FROM m_actor_stat_cat_legacy au_sc JOIN actor.org_unit aou ON aou.id = au_sc.owner GROUP BY 2, 3, 4 ORDER BY 2, 3, 4</query>
 941     </report>
 942
 943     <report>
 944          <name>usr_stat_cats</name>
 945         <report_title>Patron Statistical Categories</report_title>
 946         <tag>actors</tag>
 947         <iteration>1</iteration>
 948         <heading>Stat Cat Count.Library.Statistical Category</heading>
 949         <query>SELECT COUNT(au_sc.id), aou.name, au_sc.name FROM m_actor_stat_cat au_sc JOIN actor.org_unit aou ON aou.id = au_sc.owner GROUP BY 2, 3 ORDER BY 2, 3</query>
 950     </report>
 951
 952     <report>
 953         <name>usr_stat_cat_entries</name>
 954         <report_title>Patron Stat Cat User Entries</report_title>
 955         <tag>actors</tag>
 956         <iteration>0</iteration>
 957         <heading>Patron Stat Count.Library.Statistical Category.Migrated</heading>
 958         <query>SELECT COUNT(map.id), aou.name, au_sc.name, map.x_migrate::TEXT FROM m_actor_stat_cat_entry_usr_map_legacy map JOIN m_actor_stat_cat_legacy au_sc ON au_sc.id = map.stat_cat JOIN actor.org_unit aou ON aou.id = au_sc.owner GROUP BY 2, 3, 4 ORDER BY 2,3, 4</query>
 959     </report>
 960
 961     <report>
 962         <name>usr_stat_cat_entries</name>
 963         <report_title>Patron Stat Cat User Entries</report_title>
 964         <tag>actors</tag>
 965         <iteration>1</iteration>
 966         <heading>Patron Stat Count.Library.Statistical Category</heading>
 967         <query>SELECT COUNT(map.id), aou.name, au_sc.name FROM m_actor_stat_cat_entry_usr_map map JOIN m_actor_stat_cat au_sc ON au_sc.id = map.stat_cat JOIN actor.org_unit aou ON aou.id = au_sc.owner GROUP BY 2, 3 ORDER BY 2,3</query>
 968     </report>
 969
 970     <report>
 971         <name>usr_thresholds</name>
 972         <report_title>Patron Thresholds</report_title>
 973         <tag>actors</tag>
 974         <iteration>0</iteration>
 975         <heading>Pateron Group.Org Unit.Penalty.Threshold</heading>
 976         <query>SELECT pgt.name, aou.shortname, sp.name, p.threshold FROM permission_grp_penalty_threshold p
 977                 JOIN actor.org_unit aou ON aou.id = p.org_unit JOIN permission.grp_tree pgt ON pgt.id = p.grp JOIN config.standing_penalty sp ON sp.id = p.penalty
 978                 ORDER BY 2, 1, 3</query>
 979     </report>
 980
 981     <report>
 982         <name>usr_settings</name>
 983         <report_title>Patron Settings</report_title>
 984         <tag>actors</tag>
 985         <iteration>0</iteration>
 986         <heading>Count.Setting.Value</heading>
 987         <query>SELECT COUNT(*), name, 'User''s Phone' FROM m_actor_usr_setting WHERE name IN ('opac.default_phone') GROUP BY 2, 3
 988                         UNION ALL SELECT COUNT(*), name, value FROM m_actor_usr_setting WHERE name IN ('opac.hold_notify') GROUP BY 2, 3
 989                         UNION ALL SELECT COUNT(*), a.name, aou.shortname FROM m_actor_usr_setting a JOIN actor.org_unit aou ON aou.id = REPLACE(a.value,'"','')::INTEGER
 990                                 WHERE a.name IN ('opac.default_pickup_location','opac.default_search_location') GROUP BY 2, 3 ORDER BY 2, 3;</query>
 991     </report>
 992
 993     <!-- ACQUISITIONS REPORTS -->
 994     <report>
 995         <name>fund_count</name>
 996         <iteration>0</iteration>
 997         <tag>acq</tag>
 998         <report_title>Migrated Funds</report_title>
 999         <heading>Number of Funds.Migrated</heading>
1000         <query>SELECT COUNT(id), x_migrate::TEXT FROM m_acq_fund_legacy GROUP BY 2;</query>
1001     </report>
1002
1003     <report>
1004         <name>fund_count</name>
1005         <iteration>1</iteration>
1006         <tag>acq</tag>
1007         <report_title>Migrated Funds</report_title>
1008         <heading>Number of Funds</heading>
1009         <query>SELECT COUNT(id) FROM m_acq_fund;</query>
1010     </report>
1011
1012     <report>
1013         <name>invoice_count</name>
1014         <iteration>0</iteration>
1015         <tag>acq</tag>
1016         <report_title>Migrated Invoices</report_title>
1017         <heading>Number of Invoices.Migrated</heading>
1018         <query>SELECT COUNT(id), x_migrate::TEXT FROM m_acq_invoice_legacy GROUP BY 2;</query>
1019     </report>
1020
1021     <report>
1022         <name>invoice_count</name>
1023         <iteration>1</iteration>
1024         <tag>acq</tag>
1025         <report_title>Migrated Invoices</report_title>
1026         <heading>Number of Funds</heading>
1027         <query>SELECT COUNT(id) FROM m_acq_invoice;</query>
1028     </report>
1029
1030     <!-- SERIALS REPORTS -->
1031     <report>
1032         <name>serials_mfhd_count</name>
1033         <tag>serials</tag>
1034         <iteration>0</iteration>
1035         <report_title>Migrated Serial MFHDs</report_title>
1036         <heading>Number of MFHDs</heading>
1037         <query>SELECT COUNT(id) FROM m_serial_record_entry</query>
1038     </report>
1039
1040     <!-- DEDUPE REPORTS -->
1041
1042     <asset>
1043         <name>dedupe_explain</name>
1044         <tag>dedupe</tag>
1045         <file>dedupe_process.asciidoc</file>
1046     </asset>
1047
1048     <report>
1049         <name>dedupe_bib_groups</name>
1050         <tag>dedupe</tag>
1051         <iteration>0</iteration>
1052         <report_title>Scoring and Bib Record Groups</report_title>
1053         <heading>Count.Bib Record Groups</heading>
1054         <query>SELECT COUNT(id), 'Total Bibs Being Evaluated' FROM biblio.record_entry WHERE deleted IS FALSE AND id IN (SELECT eg::BIGINT FROM bib_id_map)
1055           UNION ALL SELECT (COUNT(DISTINCT incoming_bib)), 'Incoming Bibs With Matches Found' FROM bib_matches
1056           UNION ALL SELECT (COUNT(bre.id) - (SELECT COUNT(DISTINCT incoming_bib) FROM bib_matches)), 'Incoming Bibs With No Match'
1057                 FROM biblio.record_entry bre WHERE bre.deleted IS FALSE AND bre.id IN (SELECT eg::BIGINT FROM bib_id_map)
1058           UNION ALL SELECT COUNT(DISTINCT incoming_bib), 'Incoming Bibs Being Merged into Incumbent' FROM bib_matches WHERE incumbent_bib_score &gt;= incoming_bib_score
1059           UNION ALL SELECT COUNT(id), 'Incumbent Bibs With Higher Scores to Incoming' FROM bib_matches WHERE incumbent_bib_score &gt; incoming_bib_score
1060           UNION ALL SELECT COUNT(id), 'Incumbent Bibs With Equal Scores to Incoming' FROM bib_matches WHERE incumbent_bib_score = incoming_bib_score
1061           UNION ALL SELECT COUNT(id), 'Incumbent Bibs  With Lower Scores to Incoming' FROM bib_matches WHERE incumbent_bib_score &lt; incoming_bib_score
1062           ;</query>
1063     </report>
1064
1065     <report>
1066         <name>dedupe_format_count</name>
1067         <tag>dedupe</tag>
1068         <iteration>0</iteration>
1069         <report_title>Count of Items Matching By Format</report_title>
1070         <heading>Count.Format(s)</heading>
1071         <query>SELECT COUNT(id), search_formats FROM bib_matches GROUP BY 2 ORDER BY 2;</query>
1072     </report>
1073
1074     <report>
1075         <name>dedupe_score_ranges</name>
1076         <tag>dedupe</tag>
1077         <iteration>0</iteration>
1078         <report_title>Count of Items Matching By Format</report_title>
1079         <heading>Lowest Record Score.Largest Record Score.Record Set</heading>
1080         <query>SELECT MIN(incumbent_bib_score), MAX(incumbent_bib_score), 'Incumbent Records' FROM bib_matches
1081             UNION ALL SELECT MIN(incoming_bib_score), MAX(incoming_bib_score), 'Incoming Records' FROM bib_matches ;
1082         </query>
1083     </report>
1084
1085
1086     <report>
1087         <name>dedupe_sample_set</name>
1088         <tag>dedupe</tag>
1089         <iteration>0</iteration>
1090         <report_title>Sample of 20 Matching Dedupe Record Sets</report_title>
1091         <heading>Bib Being Merged Into.Bib Being Merged</heading>
1092         <query>SELECT incumbent_bib, incoming_bib FROM bib_matches WHERE incumbent_bib_score &gt;= incoming_bib_score LIMIT 20 ;
1093         </query>
1094     </report>
1095
1096     <!-- NOTICES REPORTS -->
1097
1098     <report>
1099         <name>notices_overview</name>
1100         <tag>notices</tag>
1101         <iteration>0</iteration>
1102         <report_title>Action Triggers Setup for Notices</report_title>
1103         <heading>ID.Active.Owner.Name</heading>
1104         <query>SELECT ed.id, ed.active, aou.shortname, ed.name
1105             FROM m_action_trigger.event_definition ed
1106             JOIN actor.org_unit aou ON aou.id = ed.owner
1107             WHERE ed.owner IN (SELECT DISTINCT home_ou FROM m_actor_usr)
1108             OR ed.owner IN (SELECT DISTINCT parent_ou FROM actor.org_unit WHERE id in (SELECT DISTINCT home_ou FROM m_actor_usr));
1109         </query>
1110     </report>
1111
1112     <report>
1113         <name>notices_count</name>
1114         <tag>notices</tag>
1115         <iteration>0</iteration>
1116         <report_title>Count of Notices Run with State</report_title>
1117         <heading>Count of Notices.State.ID.Owner.Name</heading>
1118         <query>SELECT COUNT(ate.id), ate.state, ed.id, aou.shortname, ed.name
1119             FROM m_action_trigger.event_definition ed
1120             JOIN actor.org_unit aou ON aou.id = ed.owner
1121             JOIN m_action_trigger.event ate ON ate.event_def = ed.id
1122             WHERE ed.owner IN (SELECT DISTINCT home_ou FROM m_actor_usr)
1123             OR ed.owner IN (SELECT DISTINCT parent_ou FROM actor.org_unit WHERE id in (SELECT DISTINCT home_ou FROM m_actor_usr))
1124             GROUP BY 2,3,4;
1125         </query>
1126     </report>
1127
1128
1129 </reports_file>
1130