From f94aa32f91e167e8aade712eafbe5fe552459f07 Mon Sep 17 00:00:00 2001 From: Rogan Hamby Date: Wed, 28 Aug 2019 13:52:02 -0400 Subject: [PATCH] removed excel option from mig-reporter, not needed now that spreadsheets copy and paste html tables well --- mig-bin/mig-reporter | 120 +----- mig-xml/excel_mapping_reports.xml | 836 ------------------------------------- 2 files changed, 23 insertions(+), 933 deletions(-) delete mode 100644 mig-xml/excel_mapping_reports.xml diff --git a/mig-bin/mig-reporter b/mig-bin/mig-reporter index e281580..9bea406 100755 --- a/mig-bin/mig-reporter +++ b/mig-bin/mig-reporter @@ -28,10 +28,6 @@ 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. - --captions or --captions_off Adds the captions tag to asciidoc header to turn off captions in generated output. @@ -52,7 +48,6 @@ 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'; @@ -78,7 +73,6 @@ 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 $captions_off = 0; my $i = 0; my $parser = XML::LibXML->new(); @@ -142,10 +136,6 @@ 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 '--captions_off' or $arg eq '--captions') { $captions_off = 1; next; @@ -158,11 +148,11 @@ foreach my $arg (@ARGV) { if (!defined $tags) {$tags = 'circs.holds.actors.bibs.assets.money.notices'}; if (!defined $report_title) { abort('--report_title must be supplied'); } -if ($excel_output == 0 and !defined $analyst) { abort('--analyst must be supplied'); } +if (!defined $analyst) { abort('--analyst must be supplied'); } my $mig_path = abs_path($0); $mig_path =~ s|[^/]+$||; -$reports_xml = find_xml($reports_xml,$mig_path,$excel_output); +$reports_xml = find_xml($reports_xml,$mig_path); if (!defined $reports_xml) { abort("Can not find xml reports file."); } my $dom = $parser->parse_file($reports_xml); @@ -172,15 +162,11 @@ 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,$excel_output); +my $report_file = create_report_name($report_title); $report_file = $MIGGITDIR . $report_file; -if ($excel_output == 1) { - $workbook = Excel::Writer::XLSX->new( $report_file ); -} else { - open($fh, '>', $report_file) or abort("Could not open output file $report_file!"); - write_title_page($report_title,$fh,$analyst,$captions_off); -}; +open($fh, '>', $report_file) or abort("Could not open output file $report_file!"); +write_title_page($report_title,$fh,$analyst,$captions_off); if (defined $added_page_file and defined $added_page_title) { print $fh "<<<\n"; @@ -228,7 +214,7 @@ foreach my $t (@report_tags) { print $fh "<<<\n"; } - if ($excel_output == 0) { print_section_header(ucfirst($t),$fh); } + print_section_header(ucfirst($t),$fh); my $linecount = $lines_per_page; my $r; @@ -243,7 +229,6 @@ 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); } } } @@ -251,43 +236,35 @@ foreach my $t (@report_tags) { #but this keeps it simple and in practice I haven't needed more than two - 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); } - } + 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"; -if ($excel_output eq 1) { $workbook->close(); } - else { close $fh; } +close $fh; ############ end of main logic sub find_xml { my $reports_xml = shift; my $mig_path = shift; - my $excel_output = shift; - - if (!defined $reports_xml) { - if ($excel_output == 0) { $reports_xml = $mig_path . '../mig-xml/evergreen_staged_report.xml'; return $reports_xml; } - else { $reports_xml = $mig_path . '../mig-xml/excel_mapping_reports.xml'; return $reports_xml; } - }; if ($reports_xml =~ m/\//) { return $reports_xml; } @@ -344,15 +321,13 @@ sub print_section_header { 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; - if ($excel_output == 0) { $report_file = $rt . ' ' . $date . '.asciidoc'; } - else { $report_file = $rt . ' ' . $date . '.xlsx'; } + $report_file = $rt . ' ' . $date . '.asciidoc'; $report_file =~ s/ /_/g; return $report_file; } @@ -484,55 +459,6 @@ 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 = ""; diff --git a/mig-xml/excel_mapping_reports.xml b/mig-xml/excel_mapping_reports.xml deleted file mode 100644 index c9636e5..0000000 --- a/mig-xml/excel_mapping_reports.xml +++ /dev/null @@ -1,836 +0,0 @@ - - - - - - - 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)::TEXT, 'Item Comments' FROM ititeminformation_tsv_clean WHERE l_itemcomment IS NOT NULL GROUP BY 2 - UNION ALL SELECT COUNT(l_physicalcondition)::TEXT, 'Condition Notes' FROM ctlocitem_tsv_clean WHERE l_physicalcondition IS NOT NULL GROUP BY 2 - UNION ALL SELECT COUNT(l_checkinoutnote)::TEXT, 'Circ Notes' FROM ctlocitem_tsv_clean WHERE l_checkinoutnote IS NOT NULL GROUP BY 2 - UNION ALL (SELECT DISTINCT 'Sample Item Comment', l_itemcomment FROM ititeminformation_tsv_clean WHERE l_itemcomment IS NOT NULL LIMIT 20) - UNION ALL (SELECT DISTINCT 'Sample Physical Condition', l_physicalcondition FROM ctlocitem_tsv_clean WHERE l_physicalcondition IS NOT NULL LIMIT 20) - UNION ALL (SELECT DISTINCT 'Sample Circ Note', l_checkinoutnote FROM ctlocitem_tsv_clean WHERE l_checkinoutnote IS NOT NULL LIMIT 20) - - - - - 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_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_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_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_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_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_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_borrowersbybtypes - Borrowers by Borrower Types - horizon2 - 0 - Count.Borrower Type.Description - SELECT COUNT(*), a.btype, b.descr FROM borrower_csv_clean a JOIN btype_csv_clean b ON b.btype = a.btype GROUP BY 2, 3 ORDER BY 2; - - - - hz_borrowerpincount - Borrower PINs Count - horizon - 0 - Count of Migratable Passwords / PINs - SELECT COUNT(l_borrower_pin) FROM actor_usr_legacy WHERE LENGTH(l_borrower_pin) > 1; - - - - hz_borrowerpincount - Borrower PINs Count - horizon2 - 0 - Count of Migratable Passwords / PINs - SELECT COUNT(pin) FROM borrower_csv_clean WHERE LENGTH(pin) > 1; - - - - hz_borrowernotesample - Borrower Note Field Samples - horizon - 0 - Sample of Migratable Notes - SELECT l_borrower_borrower_note FROM actor_usr_legacy WHERE LENGTH(l_borrower_borrower_note) > 1 LIMIT 20; - - - - hz_borrowernotesample - Borrower Note Field Samples - horizon2 - 0 - Sample of Migratable Notes - SELECT borrower_note FROM borrower_csv_clean WHERE LENGTH(borrower_note) > 1 LIMIT 20; - - - - hz_borrowernotescount - Count of Migratable Borrower Notes - horizon - 0 - Count - SELECT COUNT(l_borrower_borrower_note) FROM actor_usr_legacy WHERE LENGTH(l_borrower_borrower_note) > 1; - - - - hz_borrowernotescount - Counnt of Migratable Borrower Notes - horizon2 - 0 - Count - SELECT COUNT(borrower_note) FROM borrower_csv_clean WHERE LENGTH(borrower_note) > 1; - - - - 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; - - - - 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; - - - - 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 - - - - - hz_phones - Borrower Phones - horizon2 - 0 - Count.Borrower Phone Position - - SELECT COUNT(*), '0' FROM borrower_phone_csv_clean WHERE ord = '0' - UNION ALL SELECT COUNT(*), '1' FROM borrower_phone_csv_clean WHERE ord = '1' - UNION ALL SELECT COUNT(*), '2' FROM borrower_phone_csv_clean WHERE ord = '2' - UNION ALL SELECT COUNT(*), '3' FROM borrower_phone_csv_clean WHERE ord = '3' - UNION ALL SELECT COUNT(*), '4' FROM borrower_phone_csv_clean WHERE ord = '4' - - - - - 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_bstats - Borrower B-Stats - horizon2 - 0 - Count.B-Stat.Description - 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; - - - - - 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_copybycollection - Copies by Collection - horizon2 - 0 - Count.Collection.Description.PAC Description - SELECT COUNT(*), a.collection, c.descr, c.pac_descr FROM item_csv_clean a JOIN collection_csv_clean c ON c.collection = a.collection GROUP BY 2, 3, 4 ORDER BY 2, 3, 4; - - - - 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_itemsbyitype - Items by IType - horizon2 - 0 - Count.Item Type (itype).Description - SELECT COUNT(*), 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; - - - - hz_internalnotescount - Internal/Check In Item Notes - horizon - 0 - Count - SELECT COUNT(l_internal_note) FROM asset_copy_legacy WHERE LENGTH(l_internal_note) > 1; - - - - hz_internalnotescount - Internal/Check In Item Notes - horizon2 - 0 - Count - SELECT COUNT(cki_notes) FROM item_csv_clean WHERE LENGTH(cki_notes) > 1; - - - - hz_internalnotesample - Internal/Check In Item Notes Sample - horizon - 0 - Count - SELECT l_internal_note FROM asset_copy_legacy WHERE LENGTH(l_internal_note) > 1 LIMIT 20; - - - - hz_internalnotesample - Internal/Check In Item Notes Sample - horizon2 - 0 - Count - SELECT cki_notes FROM item_csv_clean WHERE LENGTH(cki_notes) > 1 LIMIT 20; - - - - - - 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