From e092916cfb7e0dcabe3de25c965c4b23f8409130 Mon Sep 17 00:00:00 2001 From: Rogan Hamby Date: Tue, 20 Mar 2018 15:19:05 -0400 Subject: [PATCH] mig-reporter can now output excel and has some mapping sheets defined --- mig-bin/mig-reporter | 142 ++++++-- mig-xml/excel_mapping_reports.xml | 756 +++++++++++++++++++++++++++++++++++++ 2 files changed, 871 insertions(+), 27 deletions(-) create mode 100644 mig-xml/excel_mapping_reports.xml diff --git a/mig-bin/mig-reporter b/mig-bin/mig-reporter index 8e9e81d..7f223f9 100755 --- a/mig-bin/mig-reporter +++ b/mig-bin/mig-reporter @@ -28,6 +28,9 @@ Gives more information about what is happening. Allows you to override the default evergreen_staged_report.xml in the mig-xml folder. +--excel_output or --excel + +Pushes output to an Excel file instead of asciidoc file. =back @@ -45,6 +48,7 @@ use Env qw( HOME PGHOST PGPORT PGUSER PGDATABASE MIGSCHEMA MIGBASEWORKDIR MIGBASEGITDIR MIGGITDIR MIGWORKDIR ); +use Excel::Writer::XLSX; use Pod::Usage; use Switch; use Cwd 'abs_path'; @@ -69,10 +73,13 @@ my $added_page_title; my $next_arg_is_added_page_title; my $added_page_file; my $next_arg_is_added_page_file; +my $excel_output = 0; my $i = 0; my $parser = XML::LibXML->new(); my $lines_per_page = 42; my $debug_flag = 0; +my $workbook; +my $fh; foreach my $arg (@ARGV) { if ($arg eq '--report_title') { @@ -129,6 +136,10 @@ foreach my $arg (@ARGV) { $next_arg_is_added_page_file = 0; next; } + if ($arg eq '--excel_output' or $arg eq '--excel') { + $excel_output = 1; + next; + } if ($arg eq '--debug') { $debug_flag = 1; next; @@ -136,13 +147,15 @@ foreach my $arg (@ARGV) { } if (!defined $tags) {$tags = 'circs.holds.actors.bibs.assets.money'}; -if (!defined $analyst) { abort('--analyst must be supplied'); } if (!defined $report_title) { abort('--report_title must be supplied'); } +if ($excel_output == 0 and !defined $analyst) { abort('--analyst must be supplied'); } my $mig_path = abs_path($0); $mig_path =~ s|[^/]+$||; -if (!defined $reports_xml) { $reports_xml = $mig_path . '../mig-xml/evergreen_staged_report.xml'; } - else { $reports_xml = $mig_path . '/../mig-xml/' . $reports_xml; } +if (!defined $reports_xml) { + if ($excel_output == 0) { $reports_xml = $mig_path . '../mig-xml/evergreen_staged_report.xml'; } + else { $reports_xml = $mig_path . '../mig-xml/excel_mapping_reports.xml'; } + } else { $reports_xml = $mig_path . '/../mig-xml/' . $reports_xml; } my $dom = $parser->parse_file($reports_xml); if (defined $added_page_file or defined $added_page_title) { @@ -151,12 +164,15 @@ if (defined $added_page_file or defined $added_page_title) { if (defined $added_page_file) { $added_page_file = $MIGGITDIR . $added_page_file; } my $dbh = Mig::db_connect(); -my $report_file = create_report_name($report_title); +my $report_file = create_report_name($report_title,$excel_output); $report_file = $MIGGITDIR . $report_file; -open(my $fh, '>', $report_file) or abort("Could not open output file!"); - -write_title_page($report_title,$fh,$analyst); +if ($excel_output == 1) { + $workbook = Excel::Writer::XLSX->new( $report_file ); +} else { + open($fh, '>', $report_file) or abort("Could not open output file!"); + write_title_page($report_title,$fh,$analyst); +}; if (defined $added_page_file and defined $added_page_title) { print $fh "<<<\n"; @@ -204,7 +220,7 @@ foreach my $t (@report_tags) { print $fh "<<<\n"; } - print_section_header(ucfirst($t),$fh); + if ($excel_output == 0) { print_section_header(ucfirst($t),$fh); } my $linecount = $lines_per_page; my $r; @@ -219,34 +235,39 @@ foreach my $t (@report_tags) { foreach my $report ($dom->findnodes('//report')) { if (index($report->findvalue('./tag'),$t) != -1 and $report->findvalue('./iteration') eq '0') { push @report_names, $report->findvalue('./name'); + if ($excel_output == 1) { print_query_to_excel($workbook,$report); } } } #only has one level of failover now but could change to array of hashes and loops #but this keeps it simple and in practice I haven't needed more than two - foreach my $rname (@report_names) { - my %report0; - my %report1; - my $check_tables0; - my $check_tables1; - - if ($debug_flag == 1) {print "\nchecking for $rname ... ";} - %report0 = find_report($dom,$t,$rname,'0',$debug_flag); - $check_tables0 = check_table($report0{query},$MIGSCHEMA,$debug_flag,$rname); - if ($check_tables0 == 1) { - $r = print_query($fh,%report0); - } else { - %report1 = find_report($dom,$t,$rname,'1',$debug_flag); - if (defined $report1{query}) { - $check_tables1 = check_table($report1{query},$MIGSCHEMA,$debug_flag,$rname); - if ($check_tables1 == 1) {$r = print_query($fh,%report1);} + + + if ($excel_output == 0) { + foreach my $rname (@report_names) { + my %report0; + my %report1; + my $check_tables0; + my $check_tables1; + + if ($debug_flag == 1) {print "\nchecking for $rname ... ";} + %report0 = find_report($dom,$t,$rname,'0',$debug_flag); + $check_tables0 = check_table($report0{query},$MIGSCHEMA,$debug_flag,$rname); + if ($check_tables0 == 1) { $r = print_query($fh,%report0); } else { + %report1 = find_report($dom,$t,$rname,'1',$debug_flag); + if (defined $report1{query}) { + $check_tables1 = check_table($report1{query},$MIGSCHEMA,$debug_flag,$rname); + if ($check_tables1 == 1) { $r = print_query($fh,%report1); } + } } } } } print "\n"; -close $fh; + +if ($excel_output eq 1) { $workbook->close(); } + else { close $fh; } ############ end of main logic @@ -283,19 +304,25 @@ sub find_report { sub print_section_header { my $t = shift; my $fh = shift; + $t =~ s/_/ /g; #$t =~ s/(\w+)/\u$1/g;; print $fh "<<<\n"; print $fh "== $t Reports\n"; + return; } sub create_report_name { my $rt = shift; + my $excel_output = shift; + my @abbr = qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec); my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time); $year += 1900; my $date = $year . '_' . $abbr[$mon] . '_' . $mday; - my $report_file = $rt . ' ' . $date . '.asciidoc'; + my $report_file; + if ($excel_output == 0) { $report_file = $rt . ' ' . $date . '.asciidoc'; } + else { $report_file = $rt . ' ' . $date . '.xlsx'; } $report_file =~ s/ /_/g; return $report_file; } @@ -370,7 +397,7 @@ sub check_table { } } if ($return_flag == 1 and $debug_flag == 1) {print "succeeded ...\n";} - if ($return_flag == 0) {print "!!!!! a table failed the find test for report $report_name\n\n";} + if ($return_flag == 0) {print "! a table failed the find test for report $report_name\n\n";} return $return_flag; } @@ -425,6 +452,67 @@ sub print_query { print "successfully wrote output for $report{name}.\n\n"; } +sub print_query_to_excel { + my $workbook = shift; + my $report = shift; + + my $header_format = $workbook->add_format( bold => 1, color => 'green', size => 16); + my $note_format = $workbook->add_format( bold => 1, color => 'red', size => 14); + + my $query = $report->findvalue('./query'); + my $title = $report->findvalue('./report_title'); + my $headings = $report->findnodes('./heading'); + + my $sth = $dbh->prepare($query); + $sth->execute(); + + my $worksheet = $workbook->add_worksheet( $title ); + my $cell = ""; + my $col = ""; + + my @h = split(/\./,$headings); + my $h_length = @h; + my $h_count = 1; + while ($h_count <= $h_length) { + $col = give_column($h_count-1); + $cell = $col . '1'; + $worksheet->write($cell,$h[$h_count-1],$header_format); + $h_count++; + } + my $cur_row = 1; + while (my @row = $sth->fetchrow_array) { + $cur_row++; + my $row_length = @row; + my $r = 1; + print Dumper(@row); + while ($r <= $row_length) { + if (! defined $row[$r-1] ) { + $row[$r-1] = 'none'; + } + $col = give_column($r-1); + $cell = $col . $cur_row; + $worksheet->write($cell,$row[$r-1]); + $r++; + } + } + $cur_row = $cur_row + 2; + $cell = "A" . "$cur_row"; + $worksheet->write($cell,$report->findvalue('./note'),$note_format); + print "Printed Query for $title.\n"; +} + +sub give_column { + my $i = shift; + my $col = ""; + + do { + $col .= chr( ( $i % 26 ) + ord('A') ); + $i = int( $i / 26 ) - 1; + } while ( $i >= 0 ); + + return scalar reverse $col; +} + sub abort { my $msg = shift; print STDERR "$0: $msg", "\n"; diff --git a/mig-xml/excel_mapping_reports.xml b/mig-xml/excel_mapping_reports.xml new file mode 100644 index 0000000..397f214 --- /dev/null +++ b/mig-xml/excel_mapping_reports.xml @@ -0,0 +1,756 @@ + + + + + + + evg_asset_copy_statuses + Statuses + evergreen + 0 + Copy Count.Migrating Status.New Evergreen Status.Notes + 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 + You only need to fill this sheet out if you use custom statuses that need to be migrated. + + + + evg_asset_circ_mods + Circulation Modifiers + evergreen + 0 + Copy Count.Migrating Circ Mod.New Circ Mod.Notes + SELECT COUNT(ac.l_id), ac.l_circ_modifier FROM asset_copy_legacy ac GROUP BY 2 ORDER BY 2 + + + + evg_asset_copy_locs + Copy Locations + evergreen + 0 + Count.Library.Migrating Copy Location.New Copy Location.Notes + 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 + Any locations not mapped can be moved over as their existing locations. + + + + evg_permission_grps + Permission Groups + evergreen + 0 + Count.Migrating Permission Group.New Permission Group.Notes + 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 + + + + + tlc_load_branches_list + tlc + 0 + Branches Present in Extract + Name.Evergreen Org Unit.Notes + SELECT l_name FROM ctbranches_tsv_clean ORDER BY 1 + + + + tlc_load_asset_notes + Item Notes + tlc + 0 + Count.Note Type.Notes + SELECT COUNT(l_itemcomment), 'Item Comments' FROM ititeminformation_tsv_clean WHERE l_itemcomment IS NOT NULL GROUP BY 2 + UNION ALL SELECT COUNT(l_physicalcondition), 'Condition Notes' FROM ctlocitem_tsv_clean WHERE l_physicalcondition IS NOT NULL GROUP BY 2 + UNION ALL SELECT COUNT(l_checkinoutnote), 'Circ Notes' FROM ctlocitem_tsv_clean WHERE l_checkinoutnote IS NOT NULL GROUP BY 2 + + + + + tlc_load_asset_in_house_use + Copies With In House Uses + tlc + 0 + Copy Count.Sum of In House Uses.Do Not Migrate + SELECT COUNT(l_totalinhouseuses), SUM(l_totalinhouseuses::INTEGER) FROM ititeminformation_tsv_clean WHERE l_totalinhouseuses != '0' + 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. + + + + tlc_load_asset_holdings_codes + Holdings Codes + tlc + 0 + Count.Holdings Codes.Evergreen Circulation Modifier.Evergreen Shelving Locatione + SELECT COUNT(l_barcode), l_activeholdingscode FROM ctlocitem_tsv_clean GROUP BY 2 ORDER BY 2 + + + + + tlc_load_asset_barcodes_with_spaces + Count of Barcodes with Spaces + tlc + 0 + Count of Barcodes With Spaces.Transformation + SELECT COUNT(l_itembarcode) FROM ititeminformation_tsv_clean WHERE l_itembarcode ~* ' ' + + + + tlc_load_money_migrating_bills + Migrating Bills By Bill Type + tlc + 0 + Count.Billing Type.Evergreen Bill Type + 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 + + + + tlc_load_usrs_bygroup + Patrons by Agency Type + tlc + 0 + Count.Permission Group.Evergreen Permission Group + SELECT COUNT(l_agencynumber), l_agencytype FROM itagency_tsv_clean GROUP BY 2 ORDER BY 2 + + + + tlc_load_usrs_byexpiration + Patrons by Expiration Date + tlc + 0 + Count.Year of Expiration.Do Not Migrate? + SELECT COUNT(l_agencynumber), LEFT(l_expirationdate,4) FROM itagency_tsv_clean GROUP BY 2 ORDER BY 2 + + + + tlc_load_usrs_bynever + Patrons With Claims + tlc + 0 + Count.Claim Type.Do Not Migrate + 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 + + + + tlc_load_usrs_byactive + Patrons by Last Active Date + tlc + 0 + Count.Year Last Active.Migrate as Active Flag? + SELECT COUNT(l_agencynumber), LEFT(l_lastactivedate,4) FROM itagency_tsv_clean GROUP BY 2 ORDER BY 2 + We can set the active flag based on this if desired. + + + + tlc_load_usrs_blocks + Patrons by Block Status + tlc + 0 + Count.Block Status.Migration Note + SELECT COUNT(l_agencynumber), l_blockstatus FROM itagency_tsv_clean GROUP BY 2 ORDER BY 2 + + + + tlc_load_usrs_gender + Patrons by Gender + tlc + 0 + Count.Gender.Migrate as Stat Cat + SELECT COUNT(l_agencynumber), l_gender FROM itagency_tsv_clean GROUP BY 2 ORDER BY 2 + Evergreen can load these as statistical categories + + + + tlc_load_usrs_alternate_ids + Count of Patrons w Alt Id + tlc + 0 + Count.Migration Need + SELECT COUNT(l_agencynumber) FROM itagency_tsv_clean WHERE l_alternateid IS NOT NULL + + + + tlc_load_active_usr_passwords + Count of Patrons w Passwords + tlc + 0 + Count of NULL PINs.Default Password + SELECT COUNT(l_agencynumber) FROM itagency_tsv_clean WHERE l_pin IS NULL + If any PINS are blank we will have to choose a method to supply them as Evergreen requires passwords. + + + + tlc_load_usr_barcodes_wspacess + Barcodes with Spaces + tlc + 0 + Count + SELECT COUNT(l_agencynumber) FROM itagency_tsv_clean WHERE l_barcode ~* ' ' + + + + tlc_load_usr_barcode_lengths + Barcode Lengths by Library + tlc + 0 + Count of Barcode.Barcode Length.Branch.Transformation Note + SELECT COUNT(l_agencynumber), LENGTH(l_barcode), l_issuingbranch FROM itagency_tsv_clean GROUP BY 2, 3 ORDER BY 3, 2 + + + + tlc_load_usr_notes + Count of Patron Notes + tlc + 0 + Count.Note Type.Action + 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 + + + + tlc_load_misc + Miscellaneous Options + tlc + 0 + Count.Option.Value.Note + 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 + + + + tlc_load_usr_balance + Count and Sum of Balances + tlc + 0 + Count.SUM in Pennies.Migrate? + SELECT COUNT(l_agencynumber), SUM(l_accountbalance::INTEGER) FROM itagency_tsv_clean WHERE l_accountbalance != '0' + If this is being migrated there are a few options of how to do it and each will have different workflows. + + + + tlc_load_usr_addresses_phones + Patron Phones + tlc + 0 + Count.Phone Type.Phone Type + 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 + These need to be mounted to Evergreen phone fields. + + + + tlc_load_usr_stat_cats + Patron Stat Cats and Counts + tlc + 0 + Patron Count.Stat Cat.Migrate? + 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 + + + + + + destiny_load_usr_by_gradelevel + Patrons by Destiny Grade Level + destiny + 0 + Count.Graduation Year + SELECT COUNT(*), grade_level FROM patrons_csv GROUP BY 2 ORDER BY 2 + Transfer to note or stat cat? + + + + destiny_load_usr_by_gender + Patrons by Destiny Gender + destiny + 0 + Count.Gender + SELECT COUNT(*), gender FROM patrons_csv GROUP BY 2 ORDER BY 2 + Transfer to stat cat? + + + + destiny_load_usr_by_patrontype + Patrons by Destiny Patron Type + destiny + 0 + Count.Patron Type.Permission Group + SELECT COUNT(*), patron_type FROM patrons_csv GROUP BY 2 ORDER BY 2 + + + + destiny_load_usr_by_status + Patrons by Destiny Status + destiny + 0 + Count.Status + SELECT COUNT(*), status FROM patrons_csv GROUP BY 2 ORDER BY 2 + + + + destiny_load_usr_by_municipality + Patrons by Municipality + destiny + 0 + Count.Municipality + SELECT COUNT(*), municipality FROM patrons_csv GROUP BY 2 ORDER BY 2 + State cat? + + + + destiny_load_usr_notes + Types of and Counts of Notes + destiny + 0 + Type of Note.Count + 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 != '' + Messsage / alert / note? + + + + destiny_load_usr_userdefined5 + User Defined Field 5 + destiny + 0 + Count.Values + SELECT COUNT(*), user_defined_5 FROM patrons_csv GROUP BY 2 ORDER BY 2 + Retain somewhere? + + + + destiny_load_usrs_pswdconfigured + Patrons by Password Configured + destiny + 0 + Count.Password Configured + SELECT COUNT(*), password_configured FROM patrons_csv GROUP BY 2 ORDER BY 2 + 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? + + + + destiny_load_usrs_phonefields + Phone Fields + destiny + 0 + Phone Field.Count.Evergreen Phone Field + 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 != '' + + + + destiny_load_asset_categories + Count of Categories + destiny + 0 + Count.Category.Circ Mod? + SELECT COUNT(*), category FROM copies_csv GROUP BY 2 ORDER BY 2 + + + + destiny_load_asset_notes + Copies by Note Types + destiny + 0 + Note Type.Count + 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 != '' + Retain? + + + + destiny_load_asset_sublocation + Copies by Sub Location + destiny + 0 + Count.Sub Location.Shelving Location? + SELECT COUNT(*), sublocation FROM copies_csv GROUP BY 2 ORDER BY 2 + + + + destiny_load_asset_vendor + Copies by Vendor + destiny + 0 + Count.Vendor + SELECT COUNT(*), vendor FROM copies_csv GROUP BY 2 ORDER BY 2 + Retain? + + + + destiny_load_asset_descriptions + Copies with Description Fields + destiny + 0 + Description Field.Count + 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 != '' + Need report? Retain? + + + + destiny_load_fines_byreason + destiny + 0 + Fines by Reason + Count.Reason + SELECT COUNT(*), reason FROM fines_csv GROUP BY 2 ORDER BY 2 + + + + + + + circ_bystatus + Circulations by Status + apollo + 0 + Count.Status.Type + SELECT COUNT(id), l_status, l_type FROM action_circulation_legacy GROUP BY 2, 3 + Circulations will only not be migrated if they can't be attached to a migrated patron and holding. + + + + hold_bystatus + Holds by Status + apollo + 0 + Count.Status + SELECT COUNT(id), l_status FROM action_hold_request_legacy GROUP BY 2 + Only unfilled holds are being migrated. + + + + asset_pending_bibs + Pending Records + apollo + 0 + Count.Year of Last Edit.Count of Copies Attached + 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 + + + + + asset_copies_by_status + Copies by Status + apollo + 0 + Count.Status + SELECT COUNT(id), l_status FROM asset_copy_legacy GROUP BY 2 ORDER BY 2 + Deleted copies with a delete date were not exported additionally those with the deleted status won't get loaded. + + + + asset_pending_copies + Pending Copies by Last Edit + apollo + 0 + Count.Last Edited + 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 + + + + assets_by_memberships + Copies by Memberships + apollo + 0 + Count.Membership Number.Membership Name + 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 + + + + + money_bills + Bills + apollo + 0 + Count.Status + SELECT COUNT(id), l_status FROM money_billing_legacy GROUP BY 2 ORDER BY 2 + Unless there is a good reason to do so forgiven and paid bills will not be migrated. + + + + actor_groups + Patron Membership Groups + apollo + 0 + Membership List Name.Membership List ID.Membership Number.Membership Name.Membership Length in Monthst + 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 + Age looks like a good target for a stat cat and / or juvenile setting while the patron type can map to profiles. + + + + actor_by_groups + Patrons by Membership Groups + apollo + 0 + Count.Membership List Name.Membership Number + 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 + + + + actor_addresses_nulls + Patron Addresses + apollo + 0 + Address Field.Nulls + 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 + If any of these fields are null then we need defaults to fill in, note the extract had no city data. + + + + actor_phones + Patron Phones + apollo + 0 + Count.Type + SELECT COUNT(*), l_type FROM actor_usr_phones_tsv GROUP BY 2 ORDER BY 2 + These need to be mapped to Evergreen phone types. + + + + hz_borrowersbybtypes + Borrowers by Borrower Types + horizon + 0 + Count.Borrower Type + SELECT COUNT(id), l_borrower_btype FROM actor_usr_legacy GROUP BY 2 ORDER BY 2; + + + + hz_borrowerpinsamples + Borrower PINs Samples + horizon + 0 + Sample PINs + SELECT l_borrower_pin FROM actor_usr_legacy WHERE LENGTH(l_borrower_pin) > 1 LIMIT 20; + Only 16 PINS defined. + + + + hz_borrowernotesample + Borrower Note Field Samples + horizon + 0 + Count + SELECT l_borrower_borrower_note FROM actor_usr_legacy WHERE LENGTH(l_borrower_borrower_note) > 1 LIMIT 20; + 19,473 + + + + hz_borrowernotesample2 + Borrower Note Field 2 Samples + horizon + 0 + Count + SELECT l_borrower_note2 FROM actor_usr_legacy WHERE LENGTH(l_borrower_note2) > 1 LIMIT 20; + 78 + + + + hz_borrowernotesample3 + Borrower Note Field 3 Samples + horizon + 0 + Count + SELECT l_borrower_note3 FROM actor_usr_legacy WHERE LENGTH(l_borrower_note3) > 1 LIMIT 20; + Only 6. + + + + hz_phones + Borrower Phones + horizon + 0 + Count.Borrower Phone Type + + SELECT COUNT(*), b + FROM (SELECT l_borrower_phone_1_phone_type AS b FROM actor_usr_legacy + UNION ALL SELECT l_borrower_phone_2_phone_type AS b FROM actor_usr_legacy + UNION ALL SELECT l_borrower_phone_3_phone_type AS b FROM actor_usr_legacy + UNION ALL SELECT l_borrower_phone_4_phone_type AS b FROM actor_usr_legacy) x + GROUP BY 2 ORDER BY 2 + + Ordinal phones will have to be combined. + + + + hz_bstats + Borrower B-Stats + horizon + 0 + Count.BStat + SELECT COUNT(*), b + FROM (SELECT l_borrower_bstat_1_bstat AS b FROM actor_usr_legacy + UNION ALL + SELECT l_borrower_bstat_2_bstat AS b FROM actor_usr_legacy + UNION ALL + SELECT l_borrower_bstat_3_bstat AS b FROM actor_usr_legacy) x + GROUP BY 2 ORDER BY 1; + + + + + hz_copybycollection + Copies by Collection + horizon + 0 + Count.Collection + SELECT COUNT(id), l_collection FROM asset_copy_legacy GROUP BY 2 ORDER BY 2; + + + + hz_copyfieldexamples + Sample Copy Field Values + horizon + 0 + Field + SELECT l_copy FROM asset_copy_legacy WHERE LENGTH(l_copy) > 1 LIMIT 30; + + + + hz_itemsbyitype + Items by IType + horizon + 0 + Count.Item Type (itype) + SELECT COUNT(id), l_itype FROM asset_copy_legacy GROUP BY 2 ORDER BY 2; + + + + hz_volfieldexamples + Sample Volume Field Values + horizon + 0 + Field + SELECT l_volume FROM asset_copy_legacy WHERE LENGTH(l_volume) > 1 LIMIT 30; + + + + + hz_iteminhouseuses + Item In House Uses + horizon + 0 + Count.Number of Uses + SELECT COUNT(id), l_n_inhouse_uses FROM asset_copy_legacy GROUP BY 2 ORDER BY 2; + + + + hz_internalnotesample + Internal Item Notes + horizon + 0 + Sample Note + SELECT l_internal_note FROM asset_copy_legacy WHERE LENGTH(l_internal_note) > 1 LIMIT 30; + + + + + + rm_load_circ_count + rm + 0 + Circs by Status + Count of Circs.Status + SELECT COUNT(id), l_is_checked_out FROM asset_copy_legacy GROUP BY 2 + + + + rm_load_asset_by_resource_type + Resource Type + rm + 0 + Count.Resource Type + SELECT COUNT(*), l_resource_type FROM asset_copy_legacy GROUP BY 2 ORDER BY 2 + + + + rm_load_asset_by_location + Copies by Location + rm + 0 + Count.Location + SELECT COUNT(*), l_location FROM asset_copy_legacy GROUP BY 2 ORDER BY 2 + + + + rm_load_asset_by_category + Copies by Category + rm + 0 + Count.Category + SELECT COUNT(*), l_category FROM asset_copy_legacy GROUP BY 2 ORDER BY 2 + + + + rm_load_asset_by_status + Copies by Status + rm + 0 + Count.Status + SELECT COUNT(*), l_status FROM asset_copy_legacy GROUP BY 2 ORDER BY 2 + + + + rm_actor_groups + Patrons by User Groups + rm + 0 + Count.Group + SELECT COUNT(id), l_user_group FROM actor_usr_legacy GROUP BY 2 ORDER BY 2; + + + + + rm_actor_access + Patrons by Access Field + rm + 0 + Count.Access + SELECT COUNT(id), l_access_if_applicable FROM actor_usr_legacy GROUP BY 2 ORDER BY 2; + + + + rm_actor_comments + Patron Comments + rm + 0 + Count.Sample + SELECT COUNT(id), 'All Comments' FROM actor_usr_legacy WHERE LENGTH(l_comments) > 1 + UNION ALL SELECT NULL, l_comments FROM actor_usr_legacy WHERE LENGTH(l_comments) > 1 LIMIT 10 + + + + + rm_actor_circulation_note + Patron Circ Notes + rm + 0 + Count.Sample + SELECT COUNT(id), 'All Notes' FROM actor_usr_legacy WHERE LENGTH(l_circulation_note) > 1 + UNION ALL SELECT NULL, l_circulation_note FROM actor_usr_legacy WHERE LENGTH(l_circulation_note) > 1 LIMIT 10 + + + + + -- 1.7.2.5