mig-xml/excel_mapping_reports.xml

   1 <reports_file>
   2 <!-- sample reports entry
   3     <report>
   4         <name>name of report</name>
   5         <iteration value='0' /> which version, the first version to run is 0, first backup is 1, then so on
   6         <tag>money</tag>  arbitrary tags, each should go in separate tag
   7         <report_title>Migrated Billings</report_title>  title used in the asciidoc output
   8         <heading column='1'>Number of Billings.Migrated.Total</heading> period delimited
   9         <query>SELECT COUNT(id),x_migrate,SUM(amount) FROM money_billing_legacy GROUP BY 2;</query>  query itself, will be replaced
  10         <note>Arbitrary note that can be included in the entries.</note>
  11     </report>
  12 -->
  13
  14    <!-- evergreen -->
  15
  16     <report>
  17         <name>evg_asset_copy_statuses</name>
  18         <report_title>Statuses</report_title>
  19         <tag>evergreen</tag>
  20         <iteration>0</iteration>
  21         <heading>Copy Count.Migrating Status.New Evergreen Status.Notes</heading>
  22         <query>SELECT COUNT(ac.l_id), cs.l_name FROM asset_copy_legacy ac JOIN config_copy_status_legacy cs ON cs.l_id = ac.l_status GROUP BY 2 ORDER BY 2</query>
  23         <note>You only need to fill this sheet out if you use custom statuses that need to be migrated.</note>
  24     </report>
  25
  26     <report>
  27         <name>evg_asset_circ_mods</name>
  28         <report_title>Circulation Modifiers</report_title>
  29         <tag>evergreen</tag>
  30         <iteration>0</iteration>
  31         <heading>Copy Count.Migrating Circ Mod.New Circ Mod.Notes</heading>
  32         <query>SELECT COUNT(ac.l_id), ac.l_circ_modifier FROM asset_copy_legacy ac GROUP BY 2 ORDER BY 2</query>
  33     </report>
  34
  35     <report>
  36         <name>evg_asset_copy_locs</name>
  37         <report_title>Copy Locations</report_title>
  38         <tag>evergreen</tag>
  39         <iteration>0</iteration>
  40         <heading>Count.Library.Migrating Copy Location.New Copy Location.Notes</heading>
  41         <query>SELECT COUNT(ac.l_id), aou.l_name, acl.l_name FROM asset_copy_location_legacy acl JOIN actor_org_unit_legacy aou ON aou.l_id = acl.l_owning_lib JOIN asset_copy_legacy ac ON ac.l_location = acl.l_id GROUP BY 2, 3 ORDER BY 2, 3</query>
  42         <note>Any locations not mapped can be moved over as their existing locations.</note>
  43     </report>
  44
  45    <report>
  46         <name>evg_permission_grps</name>
  47         <report_title>Permission Groups</report_title>
  48         <tag>evergreen</tag>
  49         <iteration>0</iteration>
  50         <heading>Count.Migrating Permission Group.New Permission Group.Notes</heading>
  51         <query>SELECT COUNT(au.l_id), pgt.l_name FROM actor_usr_legacy au JOIN permission_grp_tree_legacy pgt ON pgt.l_id = au.l_profile GROUP BY 2 ORDER BY 2</query>
  52     </report>
  53
  54     <!-- tlc -->
  55     <report>
  56         <name>tlc_load_branches_list</name>
  57         <tag>tlc</tag>
  58         <iteration>0</iteration>
  59         <report_title>Branches Present in Extract</report_title>
  60         <heading>Name.Evergreen Org Unit.Notes</heading>
  61         <query>SELECT l_name FROM ctbranches_tsv_clean ORDER BY 1</query>
  62     </report>
  63
  64     <report>
  65         <name>tlc_load_asset_notes</name>
  66         <report_title>Item Notes</report_title>
  67         <tag>tlc</tag>
  68         <iteration>0</iteration>
  69         <heading>Count.Note Type.Notes</heading>
  70         <query>SELECT COUNT(l_itemcomment), 'Item Comments' FROM ititeminformation_tsv_clean WHERE l_itemcomment IS NOT NULL GROUP BY 2
  71                UNION ALL SELECT COUNT(l_physicalcondition), 'Condition Notes' FROM ctlocitem_tsv_clean WHERE l_physicalcondition IS NOT NULL GROUP BY 2
  72                UNION ALL SELECT COUNT(l_checkinoutnote), 'Circ Notes' FROM ctlocitem_tsv_clean WHERE l_checkinoutnote IS NOT NULL GROUP BY 2
  73          </query>
  74     </report>
  75
  76     <report>
  77         <name>tlc_load_asset_in_house_use</name>
  78         <report_title>Copies With In House Uses</report_title>
  79         <tag>tlc</tag>
  80         <iteration>0</iteration>
  81         <heading>Copy Count.Sum of In House Uses.Do Not Migrate</heading>
  82         <query>SELECT COUNT(l_totalinhouseuses), SUM(l_totalinhouseuses::INTEGER) FROM ititeminformation_tsv_clean WHERE l_totalinhouseuses != '0'</query>
  83         <note>TLC doesn't have dates recorded for in house use so these can only be migrated as date of migration or we can not migrate them.</note>
  84     </report>
  85
  86     <report>
  87         <name>tlc_load_asset_holdings_codes</name>
  88         <report_title>Holdings Codes</report_title>
  89         <tag>tlc</tag>
  90         <iteration>0</iteration>
  91         <heading>Count.Holdings Codes.Evergreen Circulation Modifier.Evergreen Shelving Locatione</heading>
  92         <query>SELECT COUNT(l_barcode), l_activeholdingscode FROM ctlocitem_tsv_clean GROUP BY 2 ORDER BY 2</query>
  93     </report>
  94 <!--
  95     <report>
  96         <name>tlc_load_asset_callnumbertype</name>
  97         <report_title>Call Number Types</report_title>
  98         <tag>tlc</tag>
  99         <iteration>0</iteration>
 100         <heading>Copy Count.Call Number Type.Evergreen Call Number Type.Notes</heading>
 101         <query>SELECT COUNT(l_barcode), l_callnumbertype FROM ctlocitem_tsv_clean GROUP BY 2 ORDER BY 2</query>
 102         <note>Mappable to generic, LoC and Dewey types in Evergreen.</note>
 103     </report>
 104 -->
 105 <!--
 106     <report>
 107         <name>tlc_load_asset_callnumberparts</name>
 108         <report_title>Call Number Parts</report_title>
 109         <tag>tlc</tag>
 110         <iteration>0</iteration>
 111         <heading>Copy Count.Call Number Part.Evergreen Sequence.Evergreen Call Number Part.Delimiter</heading>
 112         <query>SELECT COUNT(l_barcode) FROM ctlocitem_tsv_clean, 'Prefixes' WHERE l_callnumberprefix IS NOT NULL GROUP BY 1 UNION ALL SELECT 'Class Label', COUNT(l_barcode) FROM ctlocitem_tsv_clean WHERE l_callnumberclass IS NOT NULL GROUP BY 1 UNION ALL SELECT 'Cutter', COUNT(l_barcode) FROM ctlocitem_tsv_clean WHERE l_callnumbercutter IS NOT NULL GROUP BY 1 UNION ALL SELECT 'Year', COUNT(l_barcode) FROM ctlocitem_tsv_clean WHERE l_year IS NOT NULL GROUP BY 1 UNION ALL SELECT 'Volume', COUNT(l_barcode) FROM ctlocitem_tsv_clean WHERE l_volume IS NOT NULL GROUP BY 1</query>
 113     </report>
 114 -->
 115     <report>
 116         <name>tlc_load_asset_barcodes_with_spaces</name>
 117         <report_title>Count of Barcodes with Spaces</report_title>
 118         <tag>tlc</tag>
 119         <iteration>0</iteration>
 120         <heading>Count of Barcodes With Spaces.Transformation</heading>
 121         <query>SELECT COUNT(l_itembarcode) FROM ititeminformation_tsv_clean WHERE l_itembarcode ~* ' '</query>
 122     </report>
 123
 124     <report>
 125         <name>tlc_load_money_migrating_bills</name>
 126         <report_title>Migrating Bills By Bill Type</report_title>
 127         <tag>tlc</tag>
 128         <iteration>0</iteration>
 129         <heading>Count.Billing Type.Evergreen Bill Type</heading>
 130         <query>SELECT COUNT(a.l_chargenumber), b.l_description FROM itpayment_tsv_clean a JOIN itpaymenttype_tsv_clean b ON b.l_paymenttype = a.l_paymenttype WHERE a.l_dueamount::INTEGER - (a.l_waiveamount::INTEGER + a.l_tenderamount::INTEGER) > 0 GROUP BY 2 ORDER BY 2 </query>
 131     </report>
 132
 133     <report>
 134         <name>tlc_load_usrs_bygroup</name>
 135         <report_title>Patrons by Agency Type</report_title>
 136         <tag>tlc</tag>
 137         <iteration>0</iteration>
 138         <heading>Count.Permission Group.Evergreen Permission Group</heading>
 139         <query>SELECT COUNT(l_agencynumber), l_agencytype FROM itagency_tsv_clean GROUP BY 2 ORDER BY 2</query>
 140     </report>
 141
 142     <report>
 143         <name>tlc_load_usrs_byexpiration</name>
 144         <report_title>Patrons by Expiration Date</report_title>
 145         <tag>tlc</tag>
 146         <iteration>0</iteration>
 147         <heading>Count.Year of Expiration.Do Not Migrate?</heading>
 148         <query>SELECT COUNT(l_agencynumber), LEFT(l_expirationdate,4) FROM itagency_tsv_clean GROUP BY 2 ORDER BY 2</query>
 149     </report>
 150
 151     <report>
 152         <name>tlc_load_usrs_bynever</name>
 153         <report_title>Patrons With Claims</report_title>
 154         <tag>tlc</tag>
 155         <iteration>0</iteration>
 156         <heading>Count.Claim Type.Do Not Migrate</heading>
 157         <query>SELECT COUNT(l_agencynumber) FROM itagency_tsv_clean, 'Never Checked Out' WHERE l_itemsclaimednevercheckedout != '0' GROUP BY 1 UNION ALL SELECT COUNT(l_agencynumber), 'Claims Returned' FROM itagency_tsv_clean WHERE l_itemsclaimedreturned != '0' GROUP BY 1</query>
 158     </report>
 159
 160     <report>
 161         <name>tlc_load_usrs_byactive</name>
 162         <report_title>Patrons by Last Active Date</report_title>
 163         <tag>tlc</tag>
 164         <iteration>0</iteration>
 165         <heading>Count.Year Last Active.Migrate as Active Flag?</heading>
 166         <query>SELECT COUNT(l_agencynumber), LEFT(l_lastactivedate,4) FROM itagency_tsv_clean GROUP BY 2 ORDER BY 2</query>
 167         <note>We can set the active flag based on this if desired.</note>
 168     </report>
 169
 170     <report>
 171         <name>tlc_load_usrs_blocks</name>
 172         <report_title>Patrons by Block Status</report_title>
 173         <tag>tlc</tag>
 174         <iteration>0</iteration>
 175         <heading>Count.Block Status.Migration Note</heading>
 176         <query>SELECT COUNT(l_agencynumber), l_blockstatus FROM itagency_tsv_clean GROUP BY 2 ORDER BY 2</query>
 177     </report>
 178
 179     <report>
 180         <name>tlc_load_usrs_gender</name>
 181         <report_title>Patrons by Gender</report_title>
 182         <tag>tlc</tag>
 183         <iteration>0</iteration>
 184         <heading>Count.Gender.Migrate as Stat Cat</heading>
 185         <query>SELECT COUNT(l_agencynumber), l_gender FROM itagency_tsv_clean GROUP BY 2 ORDER BY 2</query>
 186         <note>Evergreen can load these as statistical categories</note>
 187     </report>
 188
 189     <report>
 190         <name>tlc_load_usrs_alternate_ids</name>
 191         <report_title>Count of Patrons w Alt Id</report_title>
 192         <tag>tlc</tag>
 193         <iteration>0</iteration>
 194         <heading>Count.Migration Need</heading>
 195         <query>SELECT COUNT(l_agencynumber) FROM itagency_tsv_clean WHERE l_alternateid IS NOT NULL</query>
 196     </report>
 197
 198     <report>
 199         <name>tlc_load_active_usr_passwords</name>
 200         <report_title>Count of Patrons w Passwords</report_title>
 201         <tag>tlc</tag>
 202         <iteration>0</iteration>
 203         <heading>Count of NULL PINs.Default Password</heading>
 204         <query>SELECT COUNT(l_agencynumber) FROM itagency_tsv_clean WHERE l_pin IS NULL</query>
 205         <note>If any PINS are blank we will have to choose a method to supply them as Evergreen requires passwords.</note>
 206     </report>
 207
 208     <report>
 209         <name>tlc_load_usr_barcodes_wspacess</name>
 210         <report_title>Barcodes with Spaces</report_title>
 211         <tag>tlc</tag>
 212         <iteration>0</iteration>
 213         <heading>Count</heading>
 214         <query>SELECT COUNT(l_agencynumber) FROM itagency_tsv_clean WHERE l_barcode ~* ' '</query>
 215     </report>
 216
 217     <report>
 218         <name>tlc_load_usr_barcode_lengths</name>
 219         <report_title>Barcode Lengths by Library</report_title>
 220         <tag>tlc</tag>
 221         <iteration>0</iteration>
 222         <heading>Count of Barcode.Barcode Length.Branch.Transformation Note</heading>
 223         <query>SELECT COUNT(l_agencynumber), LENGTH(l_barcode), l_issuingbranch FROM itagency_tsv_clean GROUP BY 2, 3 ORDER BY 3, 2</query>
 224     </report>
 225
 226    <report>
 227         <name>tlc_load_usr_notes</name>
 228         <report_title>Count of Patron Notes</report_title>
 229         <tag>tlc</tag>
 230         <iteration>0</iteration>
 231         <heading>Count.Note Type.Action</heading>
 232         <query>SELECT COUNT(l_agencynumber), 'Patron Note' FROM itagency_tsv_clean WHERE l_agencycomment IS  NOT NULL UNION ALL SELECT COUNT(l_agencynumber), 'Address Comments' FROM itaddress_tsv_clean WHERE l_addresscomment IS NOT NULL</query>
 233     </report>
 234
 235     <report>
 236         <name>tlc_load_misc</name>
 237         <report_title>Miscellaneous Options</report_title>
 238         <tag>tlc</tag>
 239         <iteration>0</iteration>
 240         <heading>Count.Option.Value.Note</heading>
 241         <query>SELECT COUNT(l_agencynumber), 'Create Evergreen Grouped/Family Accounts?' FROM itagency_tsv_clean WHERE l_responsibleagencynumber IS NOT NULL GROUP BY 2 UNION ALL SELECT COUNT(l_itembarcode), 'Lost Items' FROM ititemlocation_tsv_clean WHERE l_outdate IS NOT NULL AND l_status = 'L' GROUP BY 2</query>
 242     </report>
 243
 244     <report>
 245         <name>tlc_load_usr_balance</name>
 246         <report_title>Count and Sum of Balances</report_title>
 247         <tag>tlc</tag>
 248         <iteration>0</iteration>
 249         <heading>Count.SUM in Pennies.Migrate?</heading>
 250         <query>SELECT COUNT(l_agencynumber), SUM(l_accountbalance::INTEGER) FROM itagency_tsv_clean WHERE l_accountbalance != '0'</query>
 251         <note>If this is being migrated there are a few options of how to do it and each will have different workflows.</note>
 252     </report>
 253
 254     <report>
 255         <name>tlc_load_usr_addresses_phones</name>
 256         <report_title>Patron Phones</report_title>
 257         <tag>tlc</tag>
 258         <iteration>0</iteration>
 259         <heading>Count.Phone Type.Phone Type</heading>
 260         <query>SELECT COUNT(l_phone1), 'Phone 1' FROM itaddress_tsv_clean WHERE l_phone1 IS NOT NULL GROUP BY 2 UNION ALL SELECT COUNT(l_phone1), 'Phone 2' FROM itaddress_tsv_clean WHERE l_phone2 IS NOT NULL GROUP BY 2</query>
 261         <note>These need to be mounted to Evergreen phone fields.</note>
 262     </report>
 263
 264     <report>
 265          <name>tlc_load_usr_stat_cats</name>
 266         <report_title>Patron Stat Cats and Counts</report_title>
 267         <tag>tlc</tag>
 268         <iteration>0</iteration>
 269         <heading>Patron Count.Stat Cat.Migrate?</heading>
 270         <query>SELECT COUNT(b.l_agencynumber), a.l_description FROM itagencyfields_tsv_clean a JOIN itagencydata_tsv_clean b ON b.l_agencyfieldnumber = a.l_agencyfieldnumber GROUP BY 2 ORDER BY 1</query>
 271     </report>
 272
 273 <!-- destiny -->
 274
 275     <report>
 276         <name>destiny_load_usr_by_gradelevel</name>
 277         <report_title>Patrons by Destiny Grade Level</report_title>
 278         <tag>destiny</tag>
 279         <iteration>0</iteration>
 280         <heading>Count.Graduation Year</heading>
 281         <query>SELECT COUNT(*), grade_level FROM patrons_csv GROUP BY 2 ORDER BY 2</query>
 282         <note>Transfer to note or stat cat?</note>
 283     </report>
 284
 285         <report>
 286         <name>destiny_load_usr_by_gender</name>
 287         <report_title>Patrons by Destiny Gender</report_title>
 288         <tag>destiny</tag>
 289         <iteration>0</iteration>
 290         <heading>Count.Gender</heading>
 291         <query>SELECT COUNT(*), gender FROM patrons_csv GROUP BY 2 ORDER BY 2</query>
 292         <note>Transfer to stat cat?</note>
 293     </report>
 294
 295         <report>
 296         <name>destiny_load_usr_by_patrontype</name>
 297         <report_title>Patrons by Destiny Patron Type</report_title>
 298         <tag>destiny</tag>
 299         <iteration>0</iteration>
 300         <heading>Count.Patron Type.Permission Group</heading>
 301         <query>SELECT COUNT(*), patron_type FROM patrons_csv GROUP BY 2 ORDER BY 2</query>
 302     </report>
 303
 304     <report>
 305         <name>destiny_load_usr_by_status</name>
 306         <report_title>Patrons by Destiny Status</report_title>
 307         <tag>destiny</tag>
 308         <iteration>0</iteration>
 309         <heading>Count.Status</heading>
 310         <query>SELECT COUNT(*), status FROM patrons_csv GROUP BY 2 ORDER BY 2</query>
 311     </report>
 312
 313     <report>
 314         <name>destiny_load_usr_by_municipality</name>
 315         <report_title>Patrons by Municipality</report_title>
 316         <tag>destiny</tag>
 317         <iteration>0</iteration>
 318         <heading>Count.Municipality</heading>
 319         <query>SELECT COUNT(*), municipality FROM patrons_csv GROUP BY 2 ORDER BY 2</query>
 320         <note>State cat?</note>
 321     </report>
 322
 323     <report>
 324         <name>destiny_load_usr_notes</name>
 325         <report_title>Types of and Counts of Notes</report_title>
 326         <tag>destiny</tag>
 327         <iteration>0</iteration>
 328         <heading>Type of Note.Count</heading>
 329         <query>SELECT 'General Note', COUNT(*) FROM patrons_csv WHERE note_general IS NOT NULL and note_general != '' UNION ALL SELECT 'Important Note', COUNT(*) FROM patrons_csv WHERE note_important IS NOT NULL and note_important != '' </query>
 330         <note>Messsage / alert / note?</note>
 331     </report>
 332
 333     <report>
 334         <name>destiny_load_usr_userdefined5</name>
 335         <report_title>User Defined Field 5</report_title>
 336         <tag>destiny</tag>
 337         <iteration>0</iteration>
 338         <heading>Count.Values</heading>
 339         <query>SELECT COUNT(*), user_defined_5 FROM patrons_csv GROUP BY 2 ORDER BY 2</query>
 340         <note>Retain somewhere?</note>
 341     </report>
 342
 343     <report>
 344         <name>destiny_load_usrs_pswdconfigured</name>
 345         <report_title>Patrons by Password Configured</report_title>
 346         <tag>destiny</tag>
 347         <iteration>0</iteration>
 348         <heading>Count.Password Configured</heading>
 349         <query>SELECT COUNT(*), password_configured FROM patrons_csv GROUP BY 2 ORDER BY 2</query>
 350         <note>Need how they want passwords set since we don't have them to migrate and do they want these that are configured with some special note?</note>
 351     </report>
 352
 353     <report>
 354         <name>destiny_load_usrs_phonefields</name>
 355         <report_title>Phone Fields</report_title>
 356         <tag>destiny</tag>
 357         <iteration>0</iteration>
 358         <heading>Phone Field.Count.Evergreen Phone Field</heading>
 359         <query>SELECT 'Primary Phone 1', COUNT(*) FROM patrons_csv WHERE primary_phone_1 IS NOT NULL AND primary_phone_1 != '' UNION ALL SELECT 'Primary Phone 2', COUNT(*) FROM patrons_csv WHERE primary_phone_2 IS NOT NULL AND primary_phone_2 != '' UNION ALL SELECT 'Secondary Phone 1', COUNT(*) FROM patrons_csv WHERE secondary_phone_1 IS NOT NULL AND secondary_phone_1 != '' UNION ALL SELECT 'Secondary Phone 2', COUNT(*) FROM patrons_csv WHERE secondary_phone_2 IS NOT NULL AND secondary_phone_2 != '' </query>
 360     </report>
 361
 362     <report>
 363         <name>destiny_load_asset_categories</name>
 364         <report_title>Count of Categories</report_title>
 365         <tag>destiny</tag>
 366         <iteration>0</iteration>
 367         <heading>Count.Category.Circ Mod?</heading>
 368         <query>SELECT COUNT(*), category FROM copies_csv GROUP BY 2 ORDER BY 2</query>
 369     </report>
 370
 371     <report>
 372         <name>destiny_load_asset_notes</name>
 373         <report_title>Copies by Note Types</report_title>
 374         <tag>destiny</tag>
 375         <iteration>0</iteration>
 376         <heading>Note Type.Count</heading>
 377         <query>SELECT 'General Note', COUNT(*) FROM copies_csv WHERE note_general IS NOT NULL and note_general != '' UNION ALL SELECT 'Important Note', COUNT(*) FROM copies_csv WHERE note_important IS NOT NULL and note_important != ''</query>
 378         <note>Retain?</note>
 379     </report>
 380
 381         <report>
 382         <name>destiny_load_asset_sublocation</name>
 383         <report_title>Copies by Sub Location</report_title>
 384         <tag>destiny</tag>
 385         <iteration>0</iteration>
 386         <heading>Count.Sub Location.Shelving Location?</heading>
 387         <query>SELECT COUNT(*), sublocation FROM copies_csv GROUP BY 2 ORDER BY 2</query>
 388     </report>
 389
 390     <report>
 391         <name>destiny_load_asset_vendor</name>
 392         <report_title>Copies by Vendor</report_title>
 393         <tag>destiny</tag>
 394         <iteration>0</iteration>
 395         <heading>Count.Vendor</heading>
 396         <query>SELECT COUNT(*), vendor FROM copies_csv GROUP BY 2 ORDER BY 2</query>
 397         <note>Retain?</note>
 398     </report>
 399
 400     <report>
 401         <name>destiny_load_asset_descriptions</name>
 402         <report_title>Copies with Description Fields</report_title>
 403         <tag>destiny</tag>
 404         <iteration>0</iteration>
 405         <heading>Description Field.Count</heading>
 406         <query>SELECT 'Description Field 1', COUNT(*) FROM copies_csv WHERE description_1 IS NOT NULL and description_1 != '' UNION ALL SELECT 'Description Field 2', COUNT(*) FROM copies_csv WHERE description_2 IS NOT NULL and description_2 != '' UNION ALL SELECT 'Description Field 3', COUNT(*) FROM copies_csv WHERE description_3 IS NOT NULL and description_3 != ''</query>
 407         <note>Need report?  Retain?</note>
 408     </report>
 409
 410     <report>
 411         <name>destiny_load_fines_byreason</name>
 412         <tag>destiny</tag>
 413         <iteration>0</iteration>
 414         <report_title>Fines by Reason</report_title>
 415         <heading>Count.Reason</heading>
 416         <query>SELECT COUNT(*), reason FROM fines_csv GROUP BY 2 ORDER BY 2</query>
 417     </report>
 418
 419 <!-- apollo -->
 420
 421
 422     <report>
 423         <name>circ_bystatus</name>
 424         <report_title>Circulations by Status</report_title>
 425         <tag>apollo</tag>
 426         <iteration>0</iteration>
 427         <heading>Count.Status.Type</heading>
 428         <query>SELECT COUNT(id), l_status, l_type FROM action_circulation_legacy GROUP BY 2, 3</query>
 429         <note>Circulations will only not be migrated if they can't be attached to a migrated patron and holding.</note>
 430     </report>
 431
 432     <report>
 433         <name>hold_bystatus</name>
 434         <report_title>Holds by Status</report_title>
 435         <tag>apollo</tag>
 436         <iteration>0</iteration>
 437         <heading>Count.Status</heading>
 438         <query> SELECT COUNT(id), l_status FROM action_hold_request_legacy GROUP BY 2</query>
 439         <note>Only unfilled holds are being migrated.</note>
 440     </report>
 441
 442     <report>
 443         <name>asset_pending_bibs</name>
 444         <report_title>Pending Records</report_title>
 445         <tag>apollo</tag>
 446         <iteration>0</iteration>
 447         <heading>Count.Year of Last Edit.Count of Copies Attached</heading>
 448         <query>SELECT COUNT(bre.id), CASE WHEN LENGTH(bre.l_edited) > 1 THEN EXTRACT(YEAR FROM bre.l_edited::TIMESTAMP) ELSE '1900' END, COUNT(ac.id) FROM biblio_record_entry_legacy bre LEFT JOIN asset_copy_legacy ac ON ac.l_biblio = bre.l_id WHERE bre.l_status = 'pending' GROUP BY 2 ORDER BY 2
 449 </query>
 450     </report>
 451
 452     <report>
 453         <name>asset_copies_by_status</name>
 454         <report_title>Copies by Status</report_title>
 455         <tag>apollo</tag>
 456         <iteration>0</iteration>
 457         <heading>Count.Status</heading>
 458         <query>SELECT COUNT(id), l_status FROM asset_copy_legacy GROUP BY 2 ORDER BY 2</query>
 459         <note>Deleted copies with a delete date were not exported additionally those with the deleted status won't get loaded.</note>
 460     </report>
 461
 462     <report>
 463         <name>asset_pending_copies</name>
 464         <report_title>Pending Copies by Last Edit</report_title>
 465         <tag>apollo</tag>
 466         <iteration>0</iteration>
 467         <heading>Count.Last Edited</heading>
 468         <query>SELECT COUNT(id), CASE WHEN LENGTH(l_edited) > 1 THEN EXTRACT(YEAR FROM l_edited::TIMESTAMP) ELSE '1900' END FROM asset_copy_legacy WHERE l_status = 'pending' GROUP BY 2 ORDER BY 2</query>
 469     </report>
 470
 471     <report>
 472         <name>assets_by_memberships</name>
 473         <report_title>Copies by Memberships</report_title>
 474         <tag>apollo</tag>
 475         <iteration>0</iteration>
 476         <heading>Count.Membership Number.Membership Name</heading>
 477         <query>SELECT COUNT(ac.id), acl.l_membership_number, acl.l_membership_name FROM asset_copy_legacy ac JOIN asset_copy_location_legacy acl ON acl.l_membership_name = ac.l_memberships GROUP BY 2,3 ORDER BY 2
 478 </query>
 479     </report>
 480
 481    <report>
 482         <name>money_bills</name>
 483         <report_title>Bills</report_title>
 484         <tag>apollo</tag>
 485         <iteration>0</iteration>
 486         <heading>Count.Status</heading>
 487         <query>SELECT COUNT(id), l_status FROM money_billing_legacy GROUP BY 2 ORDER BY 2</query>
 488         <note>Unless there is a good reason to do so forgiven and paid bills will not be migrated.</note>
 489     </report>
 490
 491    <report>
 492         <name>actor_groups</name>
 493         <report_title>Patron Membership Groups</report_title>
 494         <tag>apollo</tag>
 495         <iteration>0</iteration>
 496         <heading>Membership List Name.Membership List ID.Membership Number.Membership Name.Membership Length in Monthst</heading>
 497         <query>SELECT l_membership_list_name, l_membership_list_id, l_membership_number, l_membership_name, l_membership_length_months FROM actor_usr_legacy_groups_tsv ORDER BY 1, 3</query>
 498         <note>Age looks like a good target for a stat cat and / or juvenile setting while the patron type can map to profiles.</note>
 499     </report>
 500
 501     <report>
 502         <name>actor_by_groups</name>
 503         <report_title>Patrons by Membership Groups</report_title>
 504         <tag>apollo</tag>
 505         <iteration>0</iteration>
 506         <heading>Count.Membership List Name.Membership Number</heading>
 507         <query>SELECT COUNT(*), l_membership_list_name, l_membership_number FROM (SELECT id, UNNEST(STRING_TO_ARRAY(l_memberships,'|')) AS m FROM actor_usr_legacy ) x JOIN actor_usr_legacy_groups_tsv t ON t.l_membership_name = x.m GROUP BY 2, 3 ORDER BY 2, 3</query>
 508     </report>
 509
 510     <report>
 511         <name>actor_addresses_nulls</name>
 512         <report_title>Patron Addresses</report_title>
 513         <tag>apollo</tag>
 514         <iteration>0</iteration>
 515         <heading>Address Field.Nulls</heading>
 516         <query>SELECT 'Street Address', COUNT(id) FROM actor_usr_address_legacy WHERE l_lines IS NULL UNION ALL SELECT 'City', COUNT(id) FROM actor_usr_address_legacy UNION ALL SELECT 'State', COUNT(id) FROM actor_usr_address_legacy WHERE l_country_division IS NULL UNION ALL SELECT 'Postal Code', COUNT(id) FROM actor_usr_address_legacy WHERE l_postal_code IS NULL </query>
 517         <note>If any of these fields are null then we need defaults to fill in, note the extract had no city data.</note>
 518     </report>
 519
 520     <report>
 521         <name>actor_phones</name>
 522         <report_title>Patron Phones</report_title>
 523         <tag>apollo</tag>
 524         <iteration>0</iteration>
 525         <heading>Count.Type</heading>
 526         <query>SELECT COUNT(*), l_type FROM actor_usr_phones_tsv GROUP BY 2 ORDER BY 2</query>
 527         <note>These need to be mapped to Evergreen phone types.</note>
 528     </report>
 529
 530     <report>
 531         <name>hz_borrowersbybtypes</name>
 532         <report_title>Borrowers by Borrower Types</report_title>
 533         <tag>horizon</tag>
 534         <iteration>0</iteration>
 535         <heading>Count.Borrower Type</heading>
 536         <query>SELECT COUNT(id), l_borrower_btype FROM actor_usr_legacy GROUP BY 2 ORDER BY 2;</query>
 537     </report>
 538
 539     <report>
 540         <name>hz_borrowerpinsamples</name>
 541         <report_title>Borrower PINs Samples</report_title>
 542         <tag>horizon</tag>
 543         <iteration>0</iteration>
 544         <heading>Sample PINs</heading>
 545         <query>SELECT l_borrower_pin FROM actor_usr_legacy WHERE LENGTH(l_borrower_pin) > 1 LIMIT 20;</query>
 546         <note>Only 16 PINS defined.</note>
 547     </report>
 548
 549     <report>
 550         <name>hz_borrowernotesample</name>
 551         <report_title>Borrower Note Field Samples</report_title>
 552         <tag>horizon</tag>
 553         <iteration>0</iteration>
 554         <heading>Count</heading>
 555         <query>SELECT l_borrower_borrower_note FROM actor_usr_legacy WHERE LENGTH(l_borrower_borrower_note) > 1 LIMIT 20;</query>
 556         <note>19,473</note>
 557     </report>
 558
 559     <report>
 560         <name>hz_borrowernotesample2</name>
 561         <report_title>Borrower Note Field 2 Samples</report_title>
 562         <tag>horizon</tag>
 563         <iteration>0</iteration>
 564         <heading>Count</heading>
 565         <query>SELECT l_borrower_note2 FROM actor_usr_legacy WHERE LENGTH(l_borrower_note2) > 1 LIMIT 20;</query>
 566         <note>78</note>
 567     </report>
 568
 569     <report>
 570         <name>hz_borrowernotesample3</name>
 571         <report_title>Borrower Note Field 3 Samples</report_title>
 572         <tag>horizon</tag>
 573         <iteration>0</iteration>
 574         <heading>Count</heading>
 575         <query>SELECT l_borrower_note3 FROM actor_usr_legacy WHERE LENGTH(l_borrower_note3) > 1 LIMIT 20;</query>
 576         <note>Only 6.</note>
 577     </report>
 578
 579     <report>
 580         <name>hz_phones</name>
 581         <report_title>Borrower Phones</report_title>
 582         <tag>horizon</tag>
 583         <iteration>0</iteration>
 584         <heading>Count.Borrower Phone Type</heading>
 585         <query>
 586         SELECT COUNT(*), b
 587         FROM (SELECT l_borrower_phone_1_phone_type AS b FROM actor_usr_legacy
 588         UNION ALL SELECT l_borrower_phone_2_phone_type AS b FROM actor_usr_legacy
 589         UNION ALL SELECT l_borrower_phone_3_phone_type AS b FROM actor_usr_legacy
 590         UNION ALL SELECT l_borrower_phone_4_phone_type AS b FROM actor_usr_legacy) x
 591         GROUP BY 2 ORDER BY 2
 592         </query>
 593         <note>Ordinal phones will have to be combined.</note>
 594     </report>
 595
 596     <report>
 597         <name>hz_bstats</name>
 598         <report_title>Borrower B-Stats</report_title>
 599         <tag>horizon</tag>
 600         <iteration>0</iteration>
 601         <heading>Count.BStat</heading>
 602         <query>SELECT COUNT(*), b
 603         FROM (SELECT l_borrower_bstat_1_bstat AS b FROM actor_usr_legacy
 604         UNION ALL
 605         SELECT l_borrower_bstat_2_bstat AS b FROM actor_usr_legacy
 606         UNION ALL
 607         SELECT l_borrower_bstat_3_bstat AS b FROM actor_usr_legacy) x
 608         GROUP BY 2 ORDER BY 1;
 609         </query>
 610     </report>
 611
 612     <report>
 613         <name>hz_copybycollection</name>
 614         <report_title>Copies by Collection</report_title>
 615         <tag>horizon</tag>
 616         <iteration>0</iteration>
 617         <heading>Count.Collection</heading>
 618         <query>SELECT COUNT(id), l_collection FROM asset_copy_legacy GROUP BY 2 ORDER BY 2;</query>
 619     </report>
 620
 621     <report>
 622         <name>hz_copyfieldexamples</name>
 623         <report_title>Sample Copy Field Values</report_title>
 624         <tag>horizon</tag>
 625         <iteration>0</iteration>
 626         <heading>Field</heading>
 627         <query>SELECT l_copy FROM asset_copy_legacy WHERE LENGTH(l_copy) > 1 LIMIT 30;</query>
 628     </report>
 629
 630     <report>
 631         <name>hz_itemsbyitype</name>
 632         <report_title>Items by IType</report_title>
 633         <tag>horizon</tag>
 634         <iteration>0</iteration>
 635         <heading>Count.Item Type (itype)</heading>
 636         <query>SELECT COUNT(id), l_itype FROM asset_copy_legacy GROUP BY 2 ORDER BY 2;</query>
 637     </report>
 638
 639     <report>
 640         <name>hz_volfieldexamples</name>
 641         <report_title>Sample Volume Field Values</report_title>
 642         <tag>horizon</tag>
 643         <iteration>0</iteration>
 644         <heading>Field</heading>
 645         <query>SELECT l_volume FROM asset_copy_legacy WHERE LENGTH(l_volume) > 1 LIMIT 30;</query>
 646         <note></note>
 647     </report>
 648
 649      <report>
 650         <name>hz_iteminhouseuses</name>
 651         <report_title>Item In House Uses</report_title>
 652         <tag>horizon</tag>
 653         <iteration>0</iteration>
 654         <heading>Count.Number of Uses</heading>
 655         <query>SELECT COUNT(id), l_n_inhouse_uses FROM asset_copy_legacy GROUP BY 2 ORDER BY 2;</query>
 656     </report>
 657
 658     <report>
 659         <name>hz_internalnotesample</name>
 660         <report_title>Internal Item Notes</report_title>
 661         <tag>horizon</tag>
 662         <iteration>0</iteration>
 663         <heading>Sample Note</heading>
 664         <query>SELECT l_internal_note FROM asset_copy_legacy WHERE LENGTH(l_internal_note) > 1 LIMIT 30;</query>
 665     </report>
 666
 667 <!-- resource mate -->
 668
 669     <report>
 670         <name>rm_load_circ_count</name>
 671         <tag>rm</tag>
 672         <iteration>0</iteration>
 673         <report_title>Circs by Status</report_title>
 674         <heading>Count of Circs.Status</heading>
 675         <query>SELECT COUNT(id), l_is_checked_out FROM asset_copy_legacy GROUP BY 2</query>
 676     </report>
 677
 678    <report>
 679         <name>rm_load_asset_by_resource_type</name>
 680         <report_title>Resource Type</report_title>
 681         <tag>rm</tag>
 682         <iteration>0</iteration>
 683         <heading>Count.Resource Type</heading>
 684         <query>SELECT COUNT(*), l_resource_type FROM asset_copy_legacy GROUP BY 2 ORDER BY 2</query>
 685     </report>
 686
 687    <report>
 688         <name>rm_load_asset_by_location</name>
 689         <report_title>Copies by Location</report_title>
 690         <tag>rm</tag>
 691         <iteration>0</iteration>
 692         <heading>Count.Location</heading>
 693         <query>SELECT COUNT(*), l_location FROM asset_copy_legacy GROUP BY 2 ORDER BY 2</query>
 694     </report>
 695
 696     <report>
 697         <name>rm_load_asset_by_category</name>
 698         <report_title>Copies by Category</report_title>
 699         <tag>rm</tag>
 700         <iteration>0</iteration>
 701         <heading>Count.Category</heading>
 702         <query>SELECT COUNT(*), l_category FROM asset_copy_legacy GROUP BY 2 ORDER BY 2</query>
 703     </report>
 704
 705     <report>
 706         <name>rm_load_asset_by_status</name>
 707         <report_title>Copies by Status</report_title>
 708         <tag>rm</tag>
 709         <iteration>0</iteration>
 710         <heading>Count.Status</heading>
 711         <query>SELECT COUNT(*), l_status FROM asset_copy_legacy GROUP BY 2 ORDER BY 2</query>
 712     </report>
 713
 714     <report>
 715         <name>rm_actor_groups</name>
 716         <report_title>Patrons by User Groups</report_title>
 717         <tag>rm</tag>
 718         <iteration>0</iteration>
 719         <heading>Count.Group</heading>
 720         <query>SELECT COUNT(id), l_user_group FROM actor_usr_legacy GROUP BY 2 ORDER BY 2;
 721         </query>
 722     </report>
 723
 724     <report>
 725         <name>rm_actor_access</name>
 726         <report_title>Patrons by Access Field</report_title>
 727         <tag>rm</tag>
 728         <iteration>0</iteration>
 729         <heading>Count.Access</heading>
 730         <query>SELECT COUNT(id), l_access_if_applicable FROM actor_usr_legacy GROUP BY 2 ORDER BY 2;
 731         </query>
 732     </report>
 733     <report>
 734         <name>rm_actor_comments</name>
 735         <report_title>Patron Comments</report_title>
 736         <tag>rm</tag>
 737         <iteration>0</iteration>
 738         <heading>Count.Sample</heading>
 739         <query>SELECT COUNT(id), 'All Comments' FROM actor_usr_legacy WHERE LENGTH(l_comments) > 1
 740             UNION ALL SELECT NULL, l_comments FROM  actor_usr_legacy WHERE LENGTH(l_comments) > 1 LIMIT 10
 741         </query>
 742     </report>
 743
 744     <report>
 745         <name>rm_actor_circulation_note</name>
 746         <report_title>Patron Circ Notes</report_title>
 747         <tag>rm</tag>
 748         <iteration>0</iteration>
 749         <heading>Count.Sample</heading>
 750         <query>SELECT COUNT(id), 'All Notes' FROM actor_usr_legacy WHERE LENGTH(l_circulation_note) > 1
 751             UNION ALL SELECT NULL, l_circulation_note FROM  actor_usr_legacy WHERE LENGTH(l_circulation_note) > 1 LIMIT 10
 752         </query>
 753     </report>
 754
 755
 756 </reports_file>