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)::TEXT, 'Item Comments' FROM ititeminformation_tsv_clean WHERE l_itemcomment IS NOT NULL GROUP BY 2
  71                UNION ALL SELECT COUNT(l_physicalcondition)::TEXT, 'Condition Notes' FROM ctlocitem_tsv_clean WHERE l_physicalcondition IS NOT NULL GROUP BY 2
  72                UNION ALL SELECT COUNT(l_checkinoutnote)::TEXT, 'Circ Notes' FROM ctlocitem_tsv_clean WHERE l_checkinoutnote IS NOT NULL GROUP BY 2
  73                UNION ALL (SELECT DISTINCT 'Sample Item Comment', l_itemcomment FROM ititeminformation_tsv_clean WHERE l_itemcomment IS NOT NULL LIMIT 20)
  74                UNION ALL (SELECT DISTINCT 'Sample Physical Condition', l_physicalcondition FROM ctlocitem_tsv_clean WHERE l_physicalcondition IS NOT NULL LIMIT 20)
  75                UNION ALL (SELECT DISTINCT 'Sample Circ Note', l_checkinoutnote FROM ctlocitem_tsv_clean WHERE l_checkinoutnote IS NOT NULL LIMIT 20)
  76          </query>
  77     </report>
  78
  79     <report>
  80         <name>tlc_load_asset_holdings_codes</name>
  81         <report_title>Holdings Codes</report_title>
  82         <tag>tlc</tag>
  83         <iteration>0</iteration>
  84         <heading>Count.Holdings Codes.Evergreen Circulation Modifier.Evergreen Shelving Locatione</heading>
  85         <query>SELECT COUNT(l_barcode), l_activeholdingscode FROM ctlocitem_tsv_clean GROUP BY 2 ORDER BY 2</query>
  86     </report>
  87 <!--
  88     <report>
  89         <name>tlc_load_asset_callnumbertype</name>
  90         <report_title>Call Number Types</report_title>
  91         <tag>tlc</tag>
  92         <iteration>0</iteration>
  93         <heading>Copy Count.Call Number Type.Evergreen Call Number Type.Notes</heading>
  94         <query>SELECT COUNT(l_barcode), l_callnumbertype FROM ctlocitem_tsv_clean GROUP BY 2 ORDER BY 2</query>
  95         <note>Mappable to generic, LoC and Dewey types in Evergreen.</note>
  96     </report>
  97 -->
  98 <!--
  99     <report>
 100         <name>tlc_load_asset_callnumberparts</name>
 101         <report_title>Call Number Parts</report_title>
 102         <tag>tlc</tag>
 103         <iteration>0</iteration>
 104         <heading>Copy Count.Call Number Part.Evergreen Sequence.Evergreen Call Number Part.Delimiter</heading>
 105         <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>
 106     </report>
 107 -->
 108     <report>
 109         <name>tlc_load_money_migrating_bills</name>
 110         <report_title>Migrating Bills By Bill Type</report_title>
 111         <tag>tlc</tag>
 112         <iteration>0</iteration>
 113         <heading>Count.Billing Type.Evergreen Bill Type</heading>
 114         <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>
 115     </report>
 116
 117     <report>
 118         <name>tlc_load_usrs_bygroup</name>
 119         <report_title>Patrons by Agency Type</report_title>
 120         <tag>tlc</tag>
 121         <iteration>0</iteration>
 122         <heading>Count.Permission Group.Evergreen Permission Group</heading>
 123         <query>SELECT COUNT(l_agencynumber), l_agencytype FROM itagency_tsv_clean GROUP BY 2 ORDER BY 2</query>
 124     </report>
 125
 126     <report>
 127         <name>tlc_load_usrs_byexpiration</name>
 128         <report_title>Patrons by Expiration Date</report_title>
 129         <tag>tlc</tag>
 130         <iteration>0</iteration>
 131         <heading>Count.Year of Expiration.Do Not Migrate?</heading>
 132         <query>SELECT COUNT(l_agencynumber), LEFT(l_expirationdate,4) FROM itagency_tsv_clean GROUP BY 2 ORDER BY 2</query>
 133     </report>
 134 <!--
 135     <report>
 136         <name>tlc_load_usrs_bynever</name>
 137         <report_title>Patrons With Claims</report_title>
 138         <tag>tlc</tag>
 139         <iteration>0</iteration>
 140         <heading>Count.Claim Type.Do Not Migrate</heading>
 141         <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>
 142     </report>
 143 -->
 144     <report>
 145         <name>tlc_load_usrs_byactive</name>
 146         <report_title>Patrons by Last Active Date</report_title>
 147         <tag>tlc</tag>
 148         <iteration>0</iteration>
 149         <heading>Count.Year Last Active.Migrate as Active Flag?</heading>
 150         <query>SELECT COUNT(l_agencynumber), LEFT(l_lastactivedate,4) FROM itagency_tsv_clean GROUP BY 2 ORDER BY 2</query>
 151         <note>We can set the active flag based on this if desired.</note>
 152     </report>
 153
 154     <report>
 155         <name>tlc_load_usrs_blocks</name>
 156         <report_title>Patrons by Block Status</report_title>
 157         <tag>tlc</tag>
 158         <iteration>0</iteration>
 159         <heading>Count.Block Status.Migration Note</heading>
 160         <query>SELECT COUNT(l_agencynumber), l_blockstatus FROM itagency_tsv_clean GROUP BY 2 ORDER BY 2</query>
 161     </report>
 162
 163     <report>
 164         <name>tlc_load_usrs_gender</name>
 165         <report_title>Patrons by Gender</report_title>
 166         <tag>tlc</tag>
 167         <iteration>0</iteration>
 168         <heading>Count.Gender.Migrate as Stat Cat</heading>
 169         <query>SELECT COUNT(l_agencynumber), l_gender FROM itagency_tsv_clean GROUP BY 2 ORDER BY 2</query>
 170         <note>Evergreen can load these as statistical categories</note>
 171     </report>
 172
 173     <report>
 174         <name>tlc_load_active_usr_passwords</name>
 175         <report_title>Count of Patrons w Passwords</report_title>
 176         <tag>tlc</tag>
 177         <iteration>0</iteration>
 178         <heading>Count of NULL PINs.Default Password</heading>
 179         <query>SELECT COUNT(l_agencynumber) FROM itagency_tsv_clean WHERE l_pin IS NULL</query>
 180         <note>If any PINS are blank we will have to choose a method to supply them as Evergreen requires passwords.</note>
 181     </report>
 182
 183    <report>
 184         <name>tlc_load_usr_notes</name>
 185         <report_title>Count of Patron Notes</report_title>
 186         <tag>tlc</tag>
 187         <iteration>0</iteration>
 188         <heading>Count.Note Type.Action</heading>
 189         <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>
 190     </report>
 191
 192     <report>
 193         <name>tlc_load_usr_balance</name>
 194         <report_title>Count and Sum of Balances</report_title>
 195         <tag>tlc</tag>
 196         <iteration>0</iteration>
 197         <heading>Count.SUM in Pennies.Migrate?</heading>
 198         <query>SELECT COUNT(l_agencynumber), SUM(l_accountbalance::INTEGER) FROM itagency_tsv_clean WHERE l_accountbalance != '0'</query>
 199         <note>If this is being migrated there are a few options of how to do it and each will have different workflows.</note>
 200     </report>
 201 <!--
 202     <report>
 203         <name>tlc_load_usr_addresses_phones</name>
 204         <report_title>Patron Phones</report_title>
 205         <tag>tlc</tag>
 206         <iteration>0</iteration>
 207         <heading>Count.Phone Type.Phone Type</heading>
 208         <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>
 209         <note>These need to be mounted to Evergreen phone fields.</note>
 210     </report>
 211 -->
 212     <report>
 213          <name>tlc_load_usr_stat_cats</name>
 214         <report_title>Patron Stat Cats and Counts</report_title>
 215         <tag>tlc</tag>
 216         <iteration>0</iteration>
 217         <heading>Patron Count.Stat Cat.Migrate?</heading>
 218         <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>
 219     </report>
 220
 221 <!-- destiny -->
 222
 223     <report>
 224         <name>destiny_load_usr_by_gradelevel</name>
 225         <report_title>Patrons by Destiny Grade Level</report_title>
 226         <tag>destiny</tag>
 227         <iteration>0</iteration>
 228         <heading>Count.Graduation Year</heading>
 229         <query>SELECT COUNT(*), grade_level FROM patrons_csv GROUP BY 2 ORDER BY 2</query>
 230         <note>Transfer to note or stat cat?</note>
 231     </report>
 232
 233         <report>
 234         <name>destiny_load_usr_by_gender</name>
 235         <report_title>Patrons by Destiny Gender</report_title>
 236         <tag>destiny</tag>
 237         <iteration>0</iteration>
 238         <heading>Count.Gender</heading>
 239         <query>SELECT COUNT(*), gender FROM patrons_csv GROUP BY 2 ORDER BY 2</query>
 240         <note>Transfer to stat cat?</note>
 241     </report>
 242
 243         <report>
 244         <name>destiny_load_usr_by_patrontype</name>
 245         <report_title>Patrons by Destiny Patron Type</report_title>
 246         <tag>destiny</tag>
 247         <iteration>0</iteration>
 248         <heading>Count.Patron Type.Permission Group</heading>
 249         <query>SELECT COUNT(*), patron_type FROM patrons_csv GROUP BY 2 ORDER BY 2</query>
 250     </report>
 251
 252     <report>
 253         <name>destiny_load_usr_by_status</name>
 254         <report_title>Patrons by Destiny Status</report_title>
 255         <tag>destiny</tag>
 256         <iteration>0</iteration>
 257         <heading>Count.Status</heading>
 258         <query>SELECT COUNT(*), status FROM patrons_csv GROUP BY 2 ORDER BY 2</query>
 259     </report>
 260
 261     <report>
 262         <name>destiny_load_usr_by_municipality</name>
 263         <report_title>Patrons by Municipality</report_title>
 264         <tag>destiny</tag>
 265         <iteration>0</iteration>
 266         <heading>Count.Municipality</heading>
 267         <query>SELECT COUNT(*), municipality FROM patrons_csv GROUP BY 2 ORDER BY 2</query>
 268         <note>State cat?</note>
 269     </report>
 270
 271     <report>
 272         <name>destiny_load_usr_notes</name>
 273         <report_title>Types of and Counts of Notes</report_title>
 274         <tag>destiny</tag>
 275         <iteration>0</iteration>
 276         <heading>Type of Note.Count</heading>
 277         <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>
 278         <note>Messsage / alert / note?</note>
 279     </report>
 280
 281     <report>
 282         <name>destiny_load_usr_userdefined5</name>
 283         <report_title>User Defined Field 5</report_title>
 284         <tag>destiny</tag>
 285         <iteration>0</iteration>
 286         <heading>Count.Values</heading>
 287         <query>SELECT COUNT(*), user_defined_5 FROM patrons_csv GROUP BY 2 ORDER BY 2</query>
 288         <note>Retain somewhere?</note>
 289     </report>
 290
 291     <report>
 292         <name>destiny_load_usrs_pswdconfigured</name>
 293         <report_title>Patrons by Password Configured</report_title>
 294         <tag>destiny</tag>
 295         <iteration>0</iteration>
 296         <heading>Count.Password Configured</heading>
 297         <query>SELECT COUNT(*), password_configured FROM patrons_csv GROUP BY 2 ORDER BY 2</query>
 298         <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>
 299     </report>
 300
 301     <report>
 302         <name>destiny_load_usrs_phonefields</name>
 303         <report_title>Phone Fields</report_title>
 304         <tag>destiny</tag>
 305         <iteration>0</iteration>
 306         <heading>Phone Field.Count.Evergreen Phone Field</heading>
 307         <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>
 308     </report>
 309
 310     <report>
 311         <name>destiny_load_asset_categories</name>
 312         <report_title>Count of Categories</report_title>
 313         <tag>destiny</tag>
 314         <iteration>0</iteration>
 315         <heading>Count.Category.Circ Mod?</heading>
 316         <query>SELECT COUNT(*), category FROM copies_csv GROUP BY 2 ORDER BY 2</query>
 317     </report>
 318
 319     <report>
 320         <name>destiny_load_asset_notes</name>
 321         <report_title>Copies by Note Types</report_title>
 322         <tag>destiny</tag>
 323         <iteration>0</iteration>
 324         <heading>Note Type.Count</heading>
 325         <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>
 326         <note>Retain?</note>
 327     </report>
 328
 329         <report>
 330         <name>destiny_load_asset_sublocation</name>
 331         <report_title>Copies by Sub Location</report_title>
 332         <tag>destiny</tag>
 333         <iteration>0</iteration>
 334         <heading>Count.Sub Location.Shelving Location?</heading>
 335         <query>SELECT COUNT(*), sublocation FROM copies_csv GROUP BY 2 ORDER BY 2</query>
 336     </report>
 337
 338     <report>
 339         <name>destiny_load_asset_vendor</name>
 340         <report_title>Copies by Vendor</report_title>
 341         <tag>destiny</tag>
 342         <iteration>0</iteration>
 343         <heading>Count.Vendor</heading>
 344         <query>SELECT COUNT(*), vendor FROM copies_csv GROUP BY 2 ORDER BY 2</query>
 345         <note>Retain?</note>
 346     </report>
 347
 348     <report>
 349         <name>destiny_load_asset_descriptions</name>
 350         <report_title>Copies with Description Fields</report_title>
 351         <tag>destiny</tag>
 352         <iteration>0</iteration>
 353         <heading>Description Field.Count</heading>
 354         <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>
 355         <note>Need report?  Retain?</note>
 356     </report>
 357
 358     <report>
 359         <name>destiny_load_fines_byreason</name>
 360         <tag>destiny</tag>
 361         <iteration>0</iteration>
 362         <report_title>Fines by Reason</report_title>
 363         <heading>Count.Reason</heading>
 364         <query>SELECT COUNT(*), reason FROM fines_csv GROUP BY 2 ORDER BY 2</query>
 365     </report>
 366
 367 <!-- apollo -->
 368
 369
 370     <report>
 371         <name>circ_bystatus</name>
 372         <report_title>Circulations by Status</report_title>
 373         <tag>apollo</tag>
 374         <iteration>0</iteration>
 375         <heading>Count.Status.Type</heading>
 376         <query>SELECT COUNT(id), l_status, l_type FROM action_circulation_legacy GROUP BY 2, 3</query>
 377         <note>Circulations will only not be migrated if they can't be attached to a migrated patron and holding.</note>
 378     </report>
 379
 380     <report>
 381         <name>hold_bystatus</name>
 382         <report_title>Holds by Status</report_title>
 383         <tag>apollo</tag>
 384         <iteration>0</iteration>
 385         <heading>Count.Status</heading>
 386         <query> SELECT COUNT(id), l_status FROM action_hold_request_legacy GROUP BY 2</query>
 387         <note>Only unfilled holds are being migrated.</note>
 388     </report>
 389
 390     <report>
 391         <name>asset_pending_bibs</name>
 392         <report_title>Pending Records</report_title>
 393         <tag>apollo</tag>
 394         <iteration>0</iteration>
 395         <heading>Count.Year of Last Edit.Count of Copies Attached</heading>
 396         <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
 397 </query>
 398     </report>
 399
 400     <report>
 401         <name>asset_copies_by_status</name>
 402         <report_title>Copies by Status</report_title>
 403         <tag>apollo</tag>
 404         <iteration>0</iteration>
 405         <heading>Count.Status</heading>
 406         <query>SELECT COUNT(id), l_status FROM asset_copy_legacy GROUP BY 2 ORDER BY 2</query>
 407         <note>Deleted copies with a delete date were not exported additionally those with the deleted status won't get loaded.</note>
 408     </report>
 409
 410     <report>
 411         <name>asset_pending_copies</name>
 412         <report_title>Pending Copies by Last Edit</report_title>
 413         <tag>apollo</tag>
 414         <iteration>0</iteration>
 415         <heading>Count.Last Edited</heading>
 416         <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>
 417     </report>
 418
 419     <report>
 420         <name>assets_by_memberships</name>
 421         <report_title>Copies by Memberships</report_title>
 422         <tag>apollo</tag>
 423         <iteration>0</iteration>
 424         <heading>Count.Membership Number.Membership Name</heading>
 425         <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
 426 </query>
 427     </report>
 428
 429    <report>
 430         <name>money_bills</name>
 431         <report_title>Bills</report_title>
 432         <tag>apollo</tag>
 433         <iteration>0</iteration>
 434         <heading>Count.Status</heading>
 435         <query>SELECT COUNT(id), l_status FROM money_billing_legacy GROUP BY 2 ORDER BY 2</query>
 436         <note>Unless there is a good reason to do so forgiven and paid bills will not be migrated.</note>
 437     </report>
 438
 439    <report>
 440         <name>actor_groups</name>
 441         <report_title>Patron Membership Groups</report_title>
 442         <tag>apollo</tag>
 443         <iteration>0</iteration>
 444         <heading>Membership List Name.Membership List ID.Membership Number.Membership Name.Membership Length in Monthst</heading>
 445         <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>
 446         <note>Age looks like a good target for a stat cat and / or juvenile setting while the patron type can map to profiles.</note>
 447     </report>
 448
 449     <report>
 450         <name>actor_by_groups</name>
 451         <report_title>Patrons by Membership Groups</report_title>
 452         <tag>apollo</tag>
 453         <iteration>0</iteration>
 454         <heading>Count.Membership List Name.Membership Number</heading>
 455         <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>
 456     </report>
 457
 458     <report>
 459         <name>actor_addresses_nulls</name>
 460         <report_title>Patron Addresses</report_title>
 461         <tag>apollo</tag>
 462         <iteration>0</iteration>
 463         <heading>Address Field.Nulls</heading>
 464         <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>
 465         <note>If any of these fields are null then we need defaults to fill in, note the extract had no city data.</note>
 466     </report>
 467
 468     <report>
 469         <name>actor_phones</name>
 470         <report_title>Patron Phones</report_title>
 471         <tag>apollo</tag>
 472         <iteration>0</iteration>
 473         <heading>Count.Type</heading>
 474         <query>SELECT COUNT(*), l_type FROM actor_usr_phones_tsv GROUP BY 2 ORDER BY 2</query>
 475         <note>These need to be mapped to Evergreen phone types.</note>
 476     </report>
 477
 478 <!-- horizon -->
 479
 480     <report>
 481         <name>hz_borrowersbybtypes</name>
 482         <report_title>Borrowers by Borrower Types</report_title>
 483         <tag>horizon</tag>
 484         <iteration>0</iteration>
 485         <heading>Count.Borrower Type</heading>
 486         <query>SELECT COUNT(id), l_borrower_btype FROM actor_usr_legacy GROUP BY 2 ORDER BY 2;</query>
 487     </report>
 488
 489     <report>
 490         <name>hz_borrowersbybtypes</name>
 491         <report_title>Borrowers by Borrower Types</report_title>
 492         <tag>horizon</tag>
 493         <iteration>1</iteration>
 494         <heading>Count.Borrower Type</heading>
 495         <query>SELECT COUNT(id), btype FROM borrower_csv_clean GROUP BY 2 ORDER BY 2;</query>
 496     </report>
 497
 498     <report>
 499         <name>hz_borrowerpinsamples</name>
 500         <report_title>Borrower PINs Samples</report_title>
 501         <tag>horizon</tag>
 502         <iteration>0</iteration>
 503         <heading>Count of Migratable Passwords / PINs</heading>
 504         <query>SELECT COUNT(l_borrower_pin) FROM actor_usr_legacy WHERE LENGTH(l_borrower_pin) > 1;</query>
 505     </report>
 506
 507     <report>
 508         <name>hz_borrowerpinsamples</name>
 509         <report_title>Borrower PINs Samples</report_title>
 510         <tag>horizon</tag>
 511         <iteration>1</iteration>
 512         <heading>Count of Migratable Passwords / PINs</heading>
 513         <query>SELECT COUNT(pin) FROM borrower_csv_clean WHERE LENGTH(pin) > 1;</query>
 514     </report>
 515
 516     <report>
 517         <name>hz_borrowernotesample</name>
 518         <report_title>Borrower Note Field Samples</report_title>
 519         <tag>horizon</tag>
 520         <iteration>0</iteration>
 521         <heading>Sample of Migratable Notes</heading>
 522         <query>SELECT l_borrower_borrower_note FROM actor_usr_legacy WHERE LENGTH(l_borrower_borrower_note) > 1 LIMIT 20;</query>
 523     </report>
 524
 525     <report>
 526         <name>hz_borrowernotesample</name>
 527         <report_title>Borrower Note Field Samples</report_title>
 528         <tag>horizon</tag>
 529         <iteration>1</iteration>
 530         <heading>Sampel of Migratable Notes</heading>
 531         <query>SELECT l_borrower_borrower_note FROM actor_usr_legacy WHERE LENGTH(l_borrower_borrower_note) > 1 LIMIT 20;</query>
 532     </report>
 533
 534     <report>
 535         <name>hz_borrowernotescount</name>
 536         <report_title>Count of Migratable Borrower Notes</report_title>
 537         <tag>horizon</tag>
 538         <iteration>0</iteration>
 539         <heading>Count</heading>
 540         <query>SELECT COUNT(l_borrower_borrower_note) FROM actor_usr_legacy WHERE LENGTH(l_borrower_borrower_note) > 1;</query>
 541     </report>
 542
 543     <report>
 544         <name>hz_borrowernotescount</name>
 545         <report_title>Counnt of Migratable Borrower NOtes</report_title>
 546         <tag>horizon</tag>
 547         <iteration>1</iteration>
 548         <heading>Count</heading>
 549         <query>SELECT COUNT(l_borrower_borrower_note) FROM actor_usr_legacy WHERE LENGTH(l_borrower_borrower_note) > 1;</query>
 550     </report>
 551
 552     <report>
 553         <name>hz_borrowernotesample2</name>
 554         <report_title>Borrower Note Field 2 Samples</report_title>
 555         <tag>horizon</tag>
 556         <iteration>0</iteration>
 557         <heading>Count</heading>
 558         <query>SELECT l_borrower_note2 FROM actor_usr_legacy WHERE LENGTH(l_borrower_note2) > 1 LIMIT 20;</query>
 559     </report>
 560
 561     <report>
 562         <name>hz_borrowernotesample3</name>
 563         <report_title>Borrower Note Field 3 Samples</report_title>
 564         <tag>horizon</tag>
 565         <iteration>0</iteration>
 566         <heading>Count</heading>
 567         <query>SELECT l_borrower_note3 FROM actor_usr_legacy WHERE LENGTH(l_borrower_note3) > 1 LIMIT 20;</query>
 568     </report>
 569
 570     <report>
 571         <name>hz_phones</name>
 572         <report_title>Borrower Phones</report_title>
 573         <tag>horizon</tag>
 574         <iteration>0</iteration>
 575         <heading>Count.Borrower Phone Type</heading>
 576         <query>
 577         SELECT COUNT(*), b
 578         FROM (SELECT l_borrower_phone_1_phone_type AS b FROM actor_usr_legacy
 579         UNION ALL SELECT l_borrower_phone_2_phone_type AS b FROM actor_usr_legacy
 580         UNION ALL SELECT l_borrower_phone_3_phone_type AS b FROM actor_usr_legacy
 581         UNION ALL SELECT l_borrower_phone_4_phone_type AS b FROM actor_usr_legacy) x
 582         GROUP BY 2 ORDER BY 2
 583         </query>
 584     </report>
 585
 586     <report>
 587         <name>hz_phones</name>
 588         <report_title>Borrower Phones</report_title>
 589         <tag>horizon</tag>
 590         <iteration>1</iteration>
 591         <heading>Count.Borrower Phone Position</heading>
 592         <query>
 593         SELECT COUNT(*), '0' FROM borrower_phone_csv_clean WHERE ord = '0'
 594         UNION ALL SELECT COUNT(*), '1' FROM borrower_phone_csv_clean WHERE ord = '1'
 595         UNION ALL SELECT COUNT(*), '2' FROM borrower_phone_csv_clean WHERE ord = '2'
 596         UNION ALL SELECT COUNT(*), '3' FROM borrower_phone_csv_clean WHERE ord = '3'
 597                 UNION ALL SELECT COUNT(*), '4' FROM borrower_phone_csv_clean WHERE ord = '4'
 598         </query>
 599     </report>
 600
 601     <report>
 602         <name>hz_bstats</name>
 603         <report_title>Borrower B-Stats</report_title>
 604         <tag>horizon</tag>
 605         <iteration>0</iteration>
 606         <heading>Count.BStat</heading>
 607         <query>SELECT COUNT(*), b
 608         FROM (SELECT l_borrower_bstat_1_bstat AS b FROM actor_usr_legacy
 609         UNION ALL
 610         SELECT l_borrower_bstat_2_bstat AS b FROM actor_usr_legacy
 611         UNION ALL
 612         SELECT l_borrower_bstat_3_bstat AS b FROM actor_usr_legacy) x
 613         GROUP BY 2 ORDER BY 1;
 614         </query>
 615     </report>
 616
 617     <report>
 618         <name>hz_bstats</name>
 619         <report_title>Borrower B-Stats</report_title>
 620         <tag>horizon</tag>
 621         <iteration>1</iteration>
 622         <heading>Count.B-Stat.Description</heading>
 623         <query>SELECT COUNT(*), a.bstat, b.descr FROM borrower_bstat_csv_clean a JOIN bstat_csv_clean b ON b.bstat = a.bstat GROUP BY 2, 3;
 624         </query>
 625     </report>
 626
 627     <report>
 628         <name>hz_copybycollection</name>
 629         <report_title>Copies by Collection</report_title>
 630         <tag>horizon</tag>
 631         <iteration>0</iteration>
 632         <heading>Count.Collection</heading>
 633         <query>SELECT COUNT(id), l_collection FROM asset_copy_legacy GROUP BY 2 ORDER BY 2;</query>
 634     </report>
 635
 636
 637     <name>hz_copybycollection</name>
 638         <report_title>Copies by Collection</report_title>
 639         <tag>horizon</tag>
 640         <iteration>1</iteration>
 641         <heading>Count.Collection.Description.PAC Description</heading>
 642         <query>SELECT COUNT(*), a.collection, b.descr, b.pac_descr FROM item_csv_clean a JOIN from collection_csv_clean c ON c.collection = a.collection GROUP BY 2, 3, 4 ORDER BY 2, 3, 4;</query>
 643     </report>
 644
 645     <report>
 646         <name>hz_itemsbyitype</name>
 647         <report_title>Items by IType</report_title>
 648         <tag>horizon</tag>
 649         <iteration>0</iteration>
 650         <heading>Count.Item Type (itype)</heading>
 651         <query>SELECT COUNT(id), l_itype FROM asset_copy_legacy GROUP BY 2 ORDER BY 2;</query>
 652     </report>
 653
 654     <report>
 655         <name>hz_itemsbyitype</name>
 656         <report_title>Items by IType</report_title>
 657         <tag>horizon</tag>
 658         <iteration>1</iteration>
 659         <heading>Count.Item Type (itype).Description</heading>
 660         <query>SELECT COUNT(id), a.itype, b.descr FROM item_csv_clean a JOIN itype_csv_clean b ON b.itype = a.itype  GROUP BY 2, 3 ORDER BY 2;</query>
 661     </report>
 662
 663     <report>
 664         <name>hz_internalnotescount</name>
 665         <report_title>Internal/Check In Item Notes</report_title>
 666         <tag>horizon</tag>
 667         <iteration>0</iteration>
 668         <heading>Count</heading>
 669         <query>SELECT COUNT(l_internal_note) FROM asset_copy_legacy WHERE LENGTH(l_internal_note) > 1;</query>
 670     </report>
 671
 672     <report>
 673         <name>hz_internalnotescount</name>
 674         <report_title>Internal/Check In Item Notes</report_title>
 675         <tag>horizon</tag>
 676         <iteration>1</iteration>
 677         <heading>Count</heading>
 678         <query>SELECT COUNT(cki_notes) FROM item_csv_clean WHERE LENGTH(cki_notes) > 1;</query>
 679     </report>
 680
 681     <report>
 682         <name>hz_internalnotesample</name>
 683         <report_title>Internal/Check In Item Notes Sample</report_title>
 684         <tag>horizon</tag>
 685         <iteration>0</iteration>
 686         <heading>Count</heading>
 687         <query>SELECT l_internal_note FROM asset_copy_legacy WHERE LENGTH(l_internal_note) > 1 LIMIT 20;</query>
 688     </report>
 689
 690     <report>
 691         <name>hz_internalnotesample</name>
 692         <report_title>Internal/Check In Item Notes Sample</report_title>
 693         <tag>horizon</tag>
 694         <iteration>1</iteration>
 695         <heading>Count</heading>
 696         <query>SELECT cki_notes FROM item_csv_clean WHERE LENGTH(cki_notes) > 1 LIMIT 20;</query>
 697     </report>
 698
 699 <!-- resource mate -->
 700
 701     <report>
 702         <name>rm_load_circ_count</name>
 703         <tag>rm</tag>
 704         <iteration>0</iteration>
 705         <report_title>Circs by Status</report_title>
 706         <heading>Count of Circs.Status</heading>
 707         <query>SELECT COUNT(id), l_is_checked_out FROM asset_copy_legacy GROUP BY 2</query>
 708     </report>
 709
 710    <report>
 711         <name>rm_load_asset_by_resource_type</name>
 712         <report_title>Resource Type</report_title>
 713         <tag>rm</tag>
 714         <iteration>0</iteration>
 715         <heading>Count.Resource Type</heading>
 716         <query>SELECT COUNT(*), l_resource_type FROM asset_copy_legacy GROUP BY 2 ORDER BY 2</query>
 717     </report>
 718
 719    <report>
 720         <name>rm_load_asset_by_location</name>
 721         <report_title>Copies by Location</report_title>
 722         <tag>rm</tag>
 723         <iteration>0</iteration>
 724         <heading>Count.Location</heading>
 725         <query>SELECT COUNT(*), l_location FROM asset_copy_legacy GROUP BY 2 ORDER BY 2</query>
 726     </report>
 727
 728     <report>
 729         <name>rm_load_asset_by_category</name>
 730         <report_title>Copies by Category</report_title>
 731         <tag>rm</tag>
 732         <iteration>0</iteration>
 733         <heading>Count.Category</heading>
 734         <query>SELECT COUNT(*), l_category FROM asset_copy_legacy GROUP BY 2 ORDER BY 2</query>
 735     </report>
 736
 737     <report>
 738         <name>rm_load_asset_by_status</name>
 739         <report_title>Copies by Status</report_title>
 740         <tag>rm</tag>
 741         <iteration>0</iteration>
 742         <heading>Count.Status</heading>
 743         <query>SELECT COUNT(*), l_status FROM asset_copy_legacy GROUP BY 2 ORDER BY 2</query>
 744     </report>
 745
 746     <report>
 747         <name>rm_actor_groups</name>
 748         <report_title>Patrons by User Groups</report_title>
 749         <tag>rm</tag>
 750         <iteration>0</iteration>
 751         <heading>Count.Group</heading>
 752         <query>SELECT COUNT(id), l_user_group FROM actor_usr_legacy GROUP BY 2 ORDER BY 2;
 753         </query>
 754     </report>
 755
 756     <report>
 757         <name>rm_actor_access</name>
 758         <report_title>Patrons by Access Field</report_title>
 759         <tag>rm</tag>
 760         <iteration>0</iteration>
 761         <heading>Count.Access</heading>
 762         <query>SELECT COUNT(id), l_access_if_applicable FROM actor_usr_legacy GROUP BY 2 ORDER BY 2;
 763         </query>
 764     </report>
 765     <report>
 766         <name>rm_actor_comments</name>
 767         <report_title>Patron Comments</report_title>
 768         <tag>rm</tag>
 769         <iteration>0</iteration>
 770         <heading>Count.Sample</heading>
 771         <query>SELECT COUNT(id), 'All Comments' FROM actor_usr_legacy WHERE LENGTH(l_comments) > 1
 772             UNION ALL SELECT NULL, l_comments FROM  actor_usr_legacy WHERE LENGTH(l_comments) > 1 LIMIT 10
 773         </query>
 774     </report>
 775
 776     <report>
 777         <name>rm_actor_circulation_note</name>
 778         <report_title>Patron Circ Notes</report_title>
 779         <tag>rm</tag>
 780         <iteration>0</iteration>
 781         <heading>Count.Sample</heading>
 782         <query>SELECT COUNT(id), 'All Notes' FROM actor_usr_legacy WHERE LENGTH(l_circulation_note) > 1
 783             UNION ALL SELECT NULL, l_circulation_note FROM  actor_usr_legacy WHERE LENGTH(l_circulation_note) > 1 LIMIT 10
 784         </query>
 785     </report>
 786
 787 <!-- tlc reports
 788
 789     <report>
 790         <name>tlc_reports_load_asset_holdings_codes</name>
 791         <report_title>Holdings Codes</report_title>
 792         <tag>tlc_reports</tag>
 793         <iteration>0</iteration>
 794         <heading>Count.Library.Holdings Codes.Evergreen Circulation Modifier.Evergreen Shelving Locatione</heading>
 795         <query>SELECT COUNT(id), x_source, l_holdingscode FROM asset_copy_legacy GROUP BY 2, 3 ORDER BY 2, 3</query>
 796     </report>
 797
 798     <report>
 799         <name>tlc_reports_load_usrs_bygroup</name>
 800         <report_title>Patrons by Agency Type</report_title>
 801         <tag>tlc_reports</tag>
 802         <iteration>0</iteration>
 803         <heading>Count.Library.Permission Group.Evergreen Permission Group</heading>
 804         <query>SELECT COUNT(id), l_location, l_borrower_type FROM actor_usr_legacy GROUP BY 2, 3 ORDER BY 2, 3</query>
 805     </report>
 806
 807     <report>
 808         <name>tlc_reports_load_usr_balance</name>
 809         <report_title>Count and Sum of Balances</report_title>
 810         <tag>tlc_reports</tag>
 811         <iteration>0</iteration>
 812         <heading>Count.SUM.Migrate?</heading>
 813         <query>SELECT COUNT(id), SUM(l_current_fine_balance::NUMERIC(6,2)) FROM actor_usr_address_legacy WHERE l_current_fine_balance != '0' AND l_current_fine_balance IS NOT NULL</query>
 814     </report>
 815
 816     <report>
 817         <name>tlc_reports_users_blocked</name>
 818         <report_title>Count of Blocked Patrons</report_title>
 819         <tag>tlc_reports</tag>
 820         <iteration>0</iteration>
 821         <heading>Count.Blocked.Migrate?</heading>
 822         <query>SELECT COUNT(id), l_blocked FROM actor_usr_address_legacy GROUP BY 2</query>
 823     </report>
 824
 825     <report>
 826         <name>tlc_reports_load_usr_addresses_phones</name>
 827         <report_title>Patron Phones</report_title>
 828         <tag>tlc_reports</tag>
 829         <iteration>0</iteration>
 830         <heading>Count.Phone Type.Phone Type</heading>
 831         <query>SELECT COUNT(id), 'Phone 1' FROM actor_usr_address_legacy WHERE l_phone_1 IS NOT NULL GROUP BY 2 UNION ALL SELECT COUNT(id), 'Phone 2' FROM actor_usr_address_legacy WHERE l_phone_2 IS NOT NULL GROUP BY 2</query>
 832     </report>
 833 -->
 834 </reports_file>