2 <!-- sample reports entry
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>
17 <name>evg_asset_copy_statuses</name>
18 <report_title>Statuses</report_title>
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>
27 <name>evg_asset_circ_mods</name>
28 <report_title>Circulation Modifiers</report_title>
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>
36 <name>evg_asset_copy_locs</name>
37 <report_title>Copy Locations</report_title>
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>
46 <name>evg_permission_grps</name>
47 <report_title>Permission Groups</report_title>
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>
56 <name>tlc_load_branches_list</name>
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>
65 <name>tlc_load_asset_notes</name>
66 <report_title>Item Notes</report_title>
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
77 <name>tlc_load_asset_in_house_use</name>
78 <report_title>Copies With In House Uses</report_title>
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>
87 <name>tlc_load_asset_holdings_codes</name>
88 <report_title>Holdings Codes</report_title>
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>
96 <name>tlc_load_asset_callnumbertype</name>
97 <report_title>Call Number Types</report_title>
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>
107 <name>tlc_load_asset_callnumberparts</name>
108 <report_title>Call Number Parts</report_title>
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>
116 <name>tlc_load_asset_barcodes_with_spaces</name>
117 <report_title>Count of Barcodes with Spaces</report_title>
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>
125 <name>tlc_load_money_migrating_bills</name>
126 <report_title>Migrating Bills By Bill Type</report_title>
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>
134 <name>tlc_load_usrs_bygroup</name>
135 <report_title>Patrons by Agency Type</report_title>
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>
143 <name>tlc_load_usrs_byexpiration</name>
144 <report_title>Patrons by Expiration Date</report_title>
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>
152 <name>tlc_load_usrs_bynever</name>
153 <report_title>Patrons With Claims</report_title>
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>
161 <name>tlc_load_usrs_byactive</name>
162 <report_title>Patrons by Last Active Date</report_title>
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>
171 <name>tlc_load_usrs_blocks</name>
172 <report_title>Patrons by Block Status</report_title>
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>
180 <name>tlc_load_usrs_gender</name>
181 <report_title>Patrons by Gender</report_title>
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>
190 <name>tlc_load_usrs_alternate_ids</name>
191 <report_title>Count of Patrons w Alt Id</report_title>
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>
199 <name>tlc_load_active_usr_passwords</name>
200 <report_title>Count of Patrons w Passwords</report_title>
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>
209 <name>tlc_load_usr_barcodes_wspacess</name>
210 <report_title>Barcodes with Spaces</report_title>
212 <iteration>0</iteration>
213 <heading>Count</heading>
214 <query>SELECT COUNT(l_agencynumber) FROM itagency_tsv_clean WHERE l_barcode ~* ' '</query>
218 <name>tlc_load_usr_barcode_lengths</name>
219 <report_title>Barcode Lengths by Library</report_title>
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>
227 <name>tlc_load_usr_notes</name>
228 <report_title>Count of Patron Notes</report_title>
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>
236 <name>tlc_load_misc</name>
237 <report_title>Miscellaneous Options</report_title>
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>
245 <name>tlc_load_usr_balance</name>
246 <report_title>Count and Sum of Balances</report_title>
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>
255 <name>tlc_load_usr_addresses_phones</name>
256 <report_title>Patron Phones</report_title>
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>
265 <name>tlc_load_usr_stat_cats</name>
266 <report_title>Patron Stat Cats and Counts</report_title>
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>
276 <name>destiny_load_usr_by_gradelevel</name>
277 <report_title>Patrons by Destiny Grade Level</report_title>
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>
286 <name>destiny_load_usr_by_gender</name>
287 <report_title>Patrons by Destiny Gender</report_title>
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>
296 <name>destiny_load_usr_by_patrontype</name>
297 <report_title>Patrons by Destiny Patron Type</report_title>
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>
305 <name>destiny_load_usr_by_status</name>
306 <report_title>Patrons by Destiny Status</report_title>
308 <iteration>0</iteration>
309 <heading>Count.Status</heading>
310 <query>SELECT COUNT(*), status FROM patrons_csv GROUP BY 2 ORDER BY 2</query>
314 <name>destiny_load_usr_by_municipality</name>
315 <report_title>Patrons by Municipality</report_title>
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>
324 <name>destiny_load_usr_notes</name>
325 <report_title>Types of and Counts of Notes</report_title>
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>
334 <name>destiny_load_usr_userdefined5</name>
335 <report_title>User Defined Field 5</report_title>
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>
344 <name>destiny_load_usrs_pswdconfigured</name>
345 <report_title>Patrons by Password Configured</report_title>
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>
354 <name>destiny_load_usrs_phonefields</name>
355 <report_title>Phone Fields</report_title>
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>
363 <name>destiny_load_asset_categories</name>
364 <report_title>Count of Categories</report_title>
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>
372 <name>destiny_load_asset_notes</name>
373 <report_title>Copies by Note Types</report_title>
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>
382 <name>destiny_load_asset_sublocation</name>
383 <report_title>Copies by Sub Location</report_title>
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>
391 <name>destiny_load_asset_vendor</name>
392 <report_title>Copies by Vendor</report_title>
394 <iteration>0</iteration>
395 <heading>Count.Vendor</heading>
396 <query>SELECT COUNT(*), vendor FROM copies_csv GROUP BY 2 ORDER BY 2</query>
401 <name>destiny_load_asset_descriptions</name>
402 <report_title>Copies with Description Fields</report_title>
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>
411 <name>destiny_load_fines_byreason</name>
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>
423 <name>circ_bystatus</name>
424 <report_title>Circulations by Status</report_title>
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>
433 <name>hold_bystatus</name>
434 <report_title>Holds by Status</report_title>
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>
443 <name>asset_pending_bibs</name>
444 <report_title>Pending Records</report_title>
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
453 <name>asset_copies_by_status</name>
454 <report_title>Copies by Status</report_title>
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>
463 <name>asset_pending_copies</name>
464 <report_title>Pending Copies by Last Edit</report_title>
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>
472 <name>assets_by_memberships</name>
473 <report_title>Copies by Memberships</report_title>
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
482 <name>money_bills</name>
483 <report_title>Bills</report_title>
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>
492 <name>actor_groups</name>
493 <report_title>Patron Membership Groups</report_title>
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>
502 <name>actor_by_groups</name>
503 <report_title>Patrons by Membership Groups</report_title>
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>
511 <name>actor_addresses_nulls</name>
512 <report_title>Patron Addresses</report_title>
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>
521 <name>actor_phones</name>
522 <report_title>Patron Phones</report_title>
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>
531 <name>hz_borrowersbybtypes</name>
532 <report_title>Borrowers by Borrower Types</report_title>
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>
540 <name>hz_borrowerpinsamples</name>
541 <report_title>Borrower PINs Samples</report_title>
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>
550 <name>hz_borrowernotesample</name>
551 <report_title>Borrower Note Field Samples</report_title>
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>
560 <name>hz_borrowernotesample2</name>
561 <report_title>Borrower Note Field 2 Samples</report_title>
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>
570 <name>hz_borrowernotesample3</name>
571 <report_title>Borrower Note Field 3 Samples</report_title>
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>
580 <name>hz_phones</name>
581 <report_title>Borrower Phones</report_title>
583 <iteration>0</iteration>
584 <heading>Count.Borrower Phone Type</heading>
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
593 <note>Ordinal phones will have to be combined.</note>
597 <name>hz_bstats</name>
598 <report_title>Borrower B-Stats</report_title>
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
605 SELECT l_borrower_bstat_2_bstat AS b FROM actor_usr_legacy
607 SELECT l_borrower_bstat_3_bstat AS b FROM actor_usr_legacy) x
608 GROUP BY 2 ORDER BY 1;
613 <name>hz_copybycollection</name>
614 <report_title>Copies by Collection</report_title>
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>
622 <name>hz_copyfieldexamples</name>
623 <report_title>Sample Copy Field Values</report_title>
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>
631 <name>hz_itemsbyitype</name>
632 <report_title>Items by IType</report_title>
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>
640 <name>hz_volfieldexamples</name>
641 <report_title>Sample Volume Field Values</report_title>
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>
650 <name>hz_iteminhouseuses</name>
651 <report_title>Item In House Uses</report_title>
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>
659 <name>hz_internalnotesample</name>
660 <report_title>Internal Item Notes</report_title>
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>
667 <!-- resource mate -->
670 <name>rm_load_circ_count</name>
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>
679 <name>rm_load_asset_by_resource_type</name>
680 <report_title>Resource Type</report_title>
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>
688 <name>rm_load_asset_by_location</name>
689 <report_title>Copies by Location</report_title>
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>
697 <name>rm_load_asset_by_category</name>
698 <report_title>Copies by Category</report_title>
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>
706 <name>rm_load_asset_by_status</name>
707 <report_title>Copies by Status</report_title>
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>
715 <name>rm_actor_groups</name>
716 <report_title>Patrons by User Groups</report_title>
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;
725 <name>rm_actor_access</name>
726 <report_title>Patrons by Access Field</report_title>
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;
734 <name>rm_actor_comments</name>
735 <report_title>Patron Comments</report_title>
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
745 <name>rm_actor_circulation_note</name>
746 <report_title>Patron Circ Notes</report_title>
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