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