From 9e1dd47deb11013ec5dbf77ba9757075d5b74aac Mon Sep 17 00:00:00 2001 From: Rogan Hamby Date: Thu, 8 Feb 2018 14:39:22 -0500 Subject: [PATCH] initial working branch for mig reporter --- mig | 4 + mig-bin/mig-reporter | 339 ++++++++++++ mig-xml/evergreen_staged_report.xml | 1019 +++++++++++++++++++++++++++++++++++ 3 files changed, 1362 insertions(+), 0 deletions(-) create mode 100755 mig-bin/mig-reporter create mode 100644 mig-xml/evergreen_staged_report.xml diff --git a/mig b/mig index 3f6557b..914b247 100755 --- a/mig +++ b/mig @@ -277,6 +277,10 @@ switch($ARGV[0]) { Mig::die_if_no_env_migschema(); standard_invocation(@ARGV); } + case "reporter" { + Mig::die_if_no_env_migschema(); + standard_invocation(@ARGV); + } case "remove" { Mig::die_if_no_env_migschema(); standard_invocation(@ARGV); diff --git a/mig-bin/mig-reporter b/mig-bin/mig-reporter new file mode 100755 index 0000000..2dc23b9 --- /dev/null +++ b/mig-bin/mig-reporter @@ -0,0 +1,339 @@ +#!/usr/bin/perl + +use strict; +use warnings; + +use DBI; +use Data::Dumper; +use XML::LibXML; +use Env qw( + HOME PGHOST PGPORT PGUSER PGDATABASE MIGSCHEMA + MIGBASEWORKDIR MIGBASEGITDIR MIGGITDIR MIGWORKDIR +); +use Pod::Usage; +use Switch; +use Cwd 'abs_path'; +use FindBin; +my $mig_bin = "$FindBin::Bin/"; +use lib "$FindBin::Bin/"; +use Mig; +use open ':encoding(utf8)'; + +pod2usage(-verbose => 2) if defined $ARGV[0] && $ARGV[0] eq '--help'; + +my $analyst; +my $next_arg_is_analyst; +my $report_title; +my $next_arg_is_report_title; +my $reports_xml; +my $next_arg_is_reports_xml; +my $tags; +my $next_arg_is_tags; +my $added_page_title; +my $next_arg_is_added_page_title; +my $added_page_file; +my $next_arg_is_added_page_file; +my $i = 0; +my $parser = XML::LibXML->new(); +my $lines_per_page = 42; + +foreach my $arg (@ARGV) { + if ($arg eq '--report_title') { + $next_arg_is_report_title = 1; + next; + } + if ($next_arg_is_report_title) { + $report_title = $arg; + $next_arg_is_report_title = 0; + next; + } + if ($arg eq '--analyst') { + $next_arg_is_analyst = 1; + next; + } + if ($next_arg_is_analyst) { + $analyst = $arg; + $next_arg_is_analyst = 0; + next; + } + if ($arg eq '--reports_xml') { + $next_arg_is_reports_xml = 1; + next; + } + if ($next_arg_is_reports_xml) { + $reports_xml = $arg; + $next_arg_is_reports_xml = 0; + next; + } + if ($arg eq '--tags') { + $next_arg_is_tags = 1; + next; + } + if ($next_arg_is_tags) { + $tags = $arg; + $next_arg_is_tags = 0; + next; + } + if ($arg eq '--added_page_title') { + $next_arg_is_added_page_title = 1; + next; + } + if ($next_arg_is_added_page_title) { + $added_page_title = $arg; + $next_arg_is_added_page_title = 0; + next; + } + if ($arg eq '--added_page_file') { + $next_arg_is_added_page_file = 1; + next; + } + if ($next_arg_is_added_page_file) { + $added_page_file = $arg; + $next_arg_is_added_page_file = 0; + next; + } +} + +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'); } + +my $mig_path = abs_path($0); +$mig_path =~ s|[^/]+$||; +if (!defined $reports_xml) { $reports_xml = $mig_path . '../mig-xml/evergreen_staged_report.xml'; } +print "$reports_xml \n"; +my $dom = $parser->parse_file($reports_xml); + +if (defined $added_page_file or defined $added_page_title) { + abort('must specify --added_page_file and --added_page_title') unless defined $added_page_file and defined $added_page_title; + } +if ($MIGSCHEMA eq 'full') { $MIGSCHEMA = ''; } + +my $dbh = Mig::db_connect(); +my $report_file = create_report_name($report_title); +$report_file = $MIGGITDIR . $report_file; +my $mig_func_schema = $MIGSCHEMA; + +open(my $fh, '>', $report_file) or die "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"; + print $fh "== $added_page_title\n"; + print "$added_page_file\t$added_page_title\n"; + open(my $an,'<:encoding(UTF-8)', $added_page_file) or die "Could not open $added_page_file !"; + while ( my $line = <$an> ) { + print $fh $line; + } + print $fh "\n"; + close $an; +} + +foreach my $func ($dom->findnodes('//function')) { + my $fdrop = $func->findvalue('./drop'); + my $fcreate = $func->findvalue('./create'); + my $fname = $func->findvalue('./name'); + $fdrop =~ s/mig_func_schema/$mig_func_schema/g; + $fcreate =~ s/mig_func_schema/$mig_func_schema/g; + my $sdrop = $dbh->prepare($fdrop); + my $screate = $dbh->prepare($fcreate); + print "dropping function $fname ... "; + $sdrop->execute(); + print "creating function $fname\n\n"; + $screate->execute(); +} + + + +my @report_tags = split(/\./,$tags); +foreach my $t (@report_tags) { + print "\n\n=========== Starting to process tag $t\n"; + print "==========================================\n"; + print_section_header(ucfirst($t),$fh); + my $linecount = $lines_per_page; + my $r; + + my @report_names; + + 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'); + } + } + + print Dumper(@report_names); + + #only has one level of failover now but could change to array of hashes and loops + foreach my $rname (@report_names) { + my %report0; + my %report1; + my $check_tables0; + my $check_tables1; + + print "\nchecking for $rname ... "; + %report0 = find_report($dom,$t,$rname,'0'); + $check_tables0 = check_table($report0{query},$MIGSCHEMA); + if ($check_tables0 == 1) { + $r = print_query($fh,%report0); + } else { + %report1 = find_report($dom,$t,$rname,'1'); + if (defined $report1{query}) { + $check_tables1 = check_table($report1{query},$MIGSCHEMA); + if ($check_tables1 == 1) {$r = print_query($fh,%report1);} + } + } + } +} +# end of main logic + +print "\n"; +close $fh; + +sub find_report { + my $dom = shift; + my $tag = shift; + my $name = shift; + my $iteration = shift; + my %report; + + print "iteration $iteration "; + foreach my $node ($dom->findnodes('//report')) { + if ($node->findvalue('./tag') =~ $tag and $node->findvalue('./iteration') eq $iteration and $node->findvalue('./name') eq $name) { + print "succeeded ... "; + %report = ( + name => $node->findvalue('./name'), + report_title => $node->findvalue('./report_title'), + query => $node->findvalue('./query'), + heading => $node->findvalue('./heading'), + tag => $node->findvalue('./tag'), + iteration => $node->findvalue('./iteration'), + note => $node->findvalue('./note'), + ); + return %report; + } + } + print "failed ... "; + return %report = ( + name => "eaten by grue" + ); +} + +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"; +} + +sub create_report_name { + my $rt = 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'; + $report_file =~ s/ /_/g; + return $report_file; +} + +sub write_title_page { + my $rt = shift; + my $fh = shift; + my $a = shift; + + my @abbr = qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec); + my $l = length($report_title); + my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time); + $year += 1900; + print $fh "= $rt\n"; + print $fh "$mday $abbr[$mon] $year\n"; + print $fh "$a\n"; + print $fh ":title-logo-image: image::eolilogosmall.png[pdfwidth=3in]\n"; + print $fh ":toc:\n"; + print $fh "\n"; +} + +sub check_table { + my $query = shift; + my $MIGSCHEMA = shift; + + my $i = 0; + my $return_flag = 1; + my @qe = split(/ /,$query); + my @tables = grep /mig_schema/, @qe; + + print "checking tables ... "; + foreach my $table (@tables) { + $table =~ s/mig_schema.//g; + $table =~ s/\)//g; + $table =~ s/\prepare($sql); + $sth->execute(); + while (my @row = $sth->fetchrow_array) { + if ($row[0] eq '1') { + next; + } else { + $return_flag = 0; + } + if ($row[0] eq '0') {$return_flag = 0;} + } + } + if ($return_flag == 1) {print "succeeded ... ";} else {print "failed ... ";} + return $return_flag; +} + +sub print_query { + my $fh = shift; + my %report = @_; + my $query = $report{query}; + $query =~ s/mig_schema/$MIGSCHEMA/g; + $query =~ s/mig_func_schema/$mig_func_schema/g; + my $sth = $dbh->prepare($query); + $sth->execute(); + + my $header_flag = 0; + + while (my @row = $sth->fetchrow_array) { + if ($header_flag == 0) { + print $fh "\n.*$report{report_title}*\n"; + print $fh "|===\n"; + my @h = split(/\./,$report{heading}); + my $h_length = @h; + my $h_count = 1; + while ($h_count <= $h_length) { + print $fh "|$h[$h_count-1] "; + $h_count++; + } + print $fh "\n"; + $header_flag = 1; + } + my $row_length = @row; + my $r = 1; + while ($r <= $row_length) { + if (! defined $row[$r-1] ) { + $row[$r-1] = 'none'; + } + print $fh "|$row[$r-1] "; + $r++; + } + print $fh "\n"; + } + if ($header_flag == 1) { + print $fh "|===\n\n"; + print $fh $report{note}; + print $fh "\n\n"; + } + print "successfully wrote output for $report{name}.\n\n"; +} + +sub abort { + my $msg = shift; + print STDERR "$0: $msg", "\n"; + exit 1; +} + + diff --git a/mig-xml/evergreen_staged_report.xml b/mig-xml/evergreen_staged_report.xml new file mode 100644 index 0000000..5f2f9eb --- /dev/null +++ b/mig-xml/evergreen_staged_report.xml @@ -0,0 +1,1019 @@ + + + find_cmm + DROP FUNCTION IF EXISTS mig_func_schema.find_cmm(BIGINT) + + CREATE OR REPLACE FUNCTION mig_func_schema.find_cmm(circ_id BIGINT) + RETURNS SETOF INTEGER[] + LANGUAGE plpgsql + AS $function$ + DECLARE + aou INTEGER; + ac INTEGER; + au INTEGER; + r INTEGER[]; + BEGIN + SELECT circ_lib FROM action.circulation WHERE id = circ_id INTO aou; + SELECT target_copy FROM action.circulation WHERE id = circ_id INTO ac; + SELECT usr FROM action.circulation WHERE id = circ_id INTO au; + + FOR r IN SELECT buildrows FROM action.find_circ_matrix_matchpoint(aou,ac,au,FALSE) + LOOP + RETURN NEXT r; + END LOOP; + RETURN; + END + $function$ + + + + + + + + circ_count + Circs + 0 + Open Circulations + Circulation Status.Migrated.Count of Circs + SELECT 'Closed Circulations', x_migrate::TEXT, COUNT(id) FROM mig_schema.action_circulation_legacy WHERE xact_finish IS NOT NULL GROUP BY 2 UNION ALL SELECT 'Open Circulations', x_migrate::TEXT, COUNT(id) FROM mig_schema.action_circulation_legacy WHERE xact_finish IS NULL GROUP BY 2 + + + + circ_count + Circs + 1 + Open Circulations + Circulation Status.Count of Circs + SELECT 'Closed Circulations', COUNT(id) FROM mig_schema.action_circulation WHERE xact_finish IS NOT NULL UNION ALL SELECT 'Open Circulations', COUNT(id) FROM mig_schema.action_circulation WHERE xact_finish IS NULL + + + + circ_by_orgunit + Circs + 0 + Circulations by Org Unit + Circulations Count.Migrated.Org Unit + SELECT COUNT(acirc.id), acirc.x_migrate::TEXT, aou.name FROM mig_schema.action_circulation_legacy acirc JOIN actor.org_unit aou ON aou.id = acirc.circ_lib WHERE acirc.xact_finish IS NULL GROUP BY 2, 3 + + + + circ_by_orgunit + Circs + 1 + Circulations by Org Unit + Circulations Count.Org Unit + SELECT COUNT(acirc.id), aou.name FROM mig_schema.action_circulation acirc JOIN actor.org_unit aou ON aou.id = acirc.circ_lib WHERE acirc.xact_finish IS NULL GROUP BY 2 + + + + circs_by_duration + Circs + 0 + Migrated Circulations by Duration, Fine and Max Fine + Count of Circs.Duration.Fine.Max Fine.Migrated + SELECT COUNT(id), duration, recurring_fine, max_fine, x_migrate::TEXT FROM mig_schema.action_circulation_legacy WHERE x_migrate = TRUE GROUP BY 2, 3, 4, 5 + + + + circs_by_duration + Circs + 1 + Circulations by Duration, Fine and Max Fine + Count of Circs.Duration.Fine.Max Fine + SELECT COUNT(id), duration, recurring_fine, max_fine FROM mig_schema.action_circulation GROUP BY 2, 3, 4 ORDER BY 2, 3, 4 + + + + circs_by_usrgroup + Circs + 0 + Circulations by Rules and Patron Group + Count of Circs.Duration.Fine.Max Fine.User Group.Matchpoints + SELECT COUNT(acirc.id), acirc.duration, acirc.recurring_fine, acirc.max_fine, pgt.name, x.buildrows FROM mig_schema.action_circulation_legacy acirc JOIN actor.usr au ON au.id = acirc.usr JOIN permission.grp_tree pgt ON pgt.id = au.profile JOIN (SELECT acirc.id, ARRAY_TO_STRING(mig_func_schema.find_cmm(acirc.id),',') AS buildrows FROM mig_schema.action_circulation_legacy acirc WHERE acirc.x_migrate = TRUE) x ON x.id = acirc.id WHERE acirc.x_migrate = TRUE GROUP BY 2, 3, 4, 5, 6 ORDER BY 2, 3, 4, 5, 6 + + + + circs_by_usrgroup + Circs + 1 + Circulations by Rules and Patron Group + Count of Circs.Duration.Fine.Max Fine.User Group.Matchpoints + SELECT COUNT(acirc.id), acirc.duration, acirc.recurring_fine, acirc.max_fine, pgt.name, x.buildrows FROM mig_schema.action_circulation acirc JOIN actor.usr au ON au.id = acirc.usr JOIN permission.grp_tree pgt ON pgt.id = au.profile JOIN (SELECT acirc.id, ARRAY_TO_STRING(mig_func_schema.find_cmm(acirc.id),',') AS buildrows FROM mig_schema.action_circulation acirc) x ON x.id = acirc.id GROUP BY 2, 3, 4, 5, 6 ORDER BY 2, 3, 4, 5, 6 + + + + circs_by_circmod + Circs + 0 + Circulations by Rules and Circulation Modifier + Count of Circs.Duration.Fine.Max Fine.Circulation Modifier.Matchpoints + SELECT COUNT(acirc.id), acirc.duration, acirc.recurring_fine, acirc.max_fine, ac.circ_modifier, x.buildrows FROM mig_schema.action_circulation_legacy acirc JOIN asset.copy ac ON ac.id = acirc.target_copy JOIN (SELECT acirc.id, ARRAY_TO_STRING(mig_func_schema.find_cmm(acirc.id),',') AS buildrows FROM mig_schema.action_circulation_legacy acirc WHERE acirc.x_migrate = TRUE) x ON x.id = acirc.id WHERE acirc.x_migrate = TRUE GROUP BY 2, 3, 4, 5, 6 ORDER BY 2, 3, 4, 5, 6 + + + + circs_by_circmod + Circs + 1 + Circulations by Rules and Circulation Modifier + Count of Circs.Duration.Fine.Max Fine.Circulation Modifier.Matchpoints + SELECT COUNT(acirc.id), acirc.duration, acirc.recurring_fine, acirc.max_fine, ac.circ_modifier, x.buildrows FROM mig_schema.action_circulation acirc JOIN asset.copy ac ON ac.id = acirc.target_copy JOIN (SELECT acirc.id, ARRAY_TO_STRING(mig_func_schema.find_cmm(acirc.id),',') AS buildrows FROM mig_schema.action_circulation acirc) x ON x.id = acirc.id + GROUP BY 2, 3, 4, 5, 6 ORDER BY 2, 3, 4, 5, 6 + + + + circs_by_orgunit + Circs + 0 + Circulations by Rules and Org Unit + Count of Circs.Duration.Fine.Max Fine.Library Branch + SELECT COUNT(acirc.id), acirc.duration, acirc.recurring_fine, acirc.max_fine, aou.name FROM mig_schema.action_circulation_legacy acirc JOIN actor.org_unit aou ON aou.id = acirc.circ_lib WHERE acirc.x_migrate = TRUE GROUP BY 2, 3, 4, 5 ORDER BY 2, 3, 4, 5 + + + + circs_by_orgunit + Circs + 1 + Circulations by Rules and Org Unit + Count of Circs.Duration.Fine.Max Fine.Library Branch + SELECT COUNT(acirc.id), acirc.duration, acirc.recurring_fine, acirc.max_fine, aou.name FROM mig_schema.action_circulation acirc JOIN actor.org_unit aou ON aou.id = acirc.circ_lib GROUP BY 2, 3, 4, 5 ORDER BY 2, 3, 4, 5 + + + + non_cat_circs + Circs + 0 + Non-Cataloged Circulation + Circulations Count.Migrated + SELECT COUNT(id), x_migrate::TEXT FROM mig_schema.action_non_cataloged_circulation_legacy GROUP BY 2 + + + + non_cat_circs + Circs + 1 + Non-Cataloged Circulation + Circulations Count + SELECT COUNT(id) FROM mig_schema.action_non_cataloged_circulation + + + + in_house + Circs + 0 + In House Use + In House Use Records.Migrated + SELECT COUNT(id), x_migrate::TEXT FROM mig_schema.action_in_house_use_legacy GROUP BY 2 + + + + in_house + Circs + 1 + In House Use + In House Use Records + SELECT COUNT(id) FROM mig_schema.action_in_house_use + + + + circs_missing_rules + Circs + 1 + Circs Missing Rules + Count.Field Missing + SELECT COUNT(id), 'Duration Rule Value' FROM mig_schema.action_circulation WHERE duration IS NULL + UNION ALL SELECT COUNT(id), 'Recurring Fine Rule Value' FROM mig_schema.action_circulation WHERE recurring_fine IS NULL + UNION ALL SELECT COUNT(id), 'Max Fine Rule Value' FROM mig_schema.action_circulation WHERE max_fine IS NULL + UNION ALL SELECT COUNT(id), 'Duration Rule' FROM mig_schema.action_circulation WHERE duration_rule IS NULL + UNION ALL SELECT COUNT(id), 'Recurring Fine Rule' FROM mig_schema.action_circulation WHERE recurring_fine_rule IS NULL + UNION ALL SELECT COUNT(id), 'Max Fine Rule' FROM mig_schema.action_circulation WHERE max_fine_rule IS NULL + + + + + circ_open_by_item_status + Circs + 0 + Open Circulation and Status of Linked Items + Count.Status + SELECT COUNT(acirc.id), ccs.name FROM action.circulation acirc JOIN asset.copy ac ON ac.id = acirc.target_copy JOIN config.copy_status ccs ON ccs.id = ac.status WHERE acirc.xact_finish IS NULL AND acirc.checkin_time IS NULL AND acirc.id IN (SELECT id FROM mig_schema.action_circulation) GROUP BY 2 ORDER BY 2 + + + + + + holds + Holds + 0 + Migrated Holds + Hold Type.Hold Count.Migrated + SELECT 'Closed Holds', COUNT(id), x_migrate::TEXT FROM mig_schema.action_hold_request_legacy WHERE (expire_time::TIMESTAMP < now()) OR cancel_time IS NOT NULL OR fulfillment_time IS NOT NULL GROUP BY 3 UNION ALL SELECT 'Open Holds', COUNT(id), x_migrate::TEXT FROM mig_schema.action_hold_request_legacy WHERE (expire_time IS NULL OR expire_time::TIMESTAMP > now()) AND cancel_time IS NULL AND fulfillment_time IS NULL GROUP BY 3 + + + + holds_bytype + Holds + 0 + Migrated Holds By Type + Hold Type.Hold Count.Migrated + SELECT hold_type as "Hold Type", COUNT(id), x_migrate::TEXT FROM mig_schema.action_hold_request_legacy GROUP BY 1, 3 + + + + transit_open_by_item_status + Holds + 0 + Transit Copy Records and Status of Linked Items + Count.Status + SELECT COUNT(atc.id), ccs.name FROM action.transit_copy atc JOIN asset.copy ac ON ac.id = atc.target_copy JOIN config.copy_status ccs ON ccs.id = ac.status WHERE atc.id IN (SELECT id FROM mig_schema.action_transit_copy) AND atc.dest_recv_time IS NULL GROUP BY 2 ORDER BY 2 + + + + transit_copies_by_status + Holds + 0 + Status of Items with Count of Open In Transits + Count.Status.Count of Open Transits + SELECT COUNT(ac.id), ccs.name, SUM(CASE WHEN atc.id IS NULL THEN 0 ELSE 1 END) FROM asset.copy ac JOIN config.copy_status ccs ON ccs.id = ac.status LEFT JOIN (SELECT * FROM action.transit_copy WHERE id IN (SELECT id FROM mig_schema.action_transit_copy) AND dest_recv_time IS NULL) atc ON atc.target_copy = ac.id WHERE ac.id IN (SELECT id from mig_schema.asset_copy) GROUP BY 2 ORDER BY 2 + + + + hold_copies_by_status + Holds + 0 + Captured Holds with Status of Items + Count of Captured Hold.Status of Item + SELECT COUNT(ahr.id), ccs.name FROM action.hold_request ahr JOIN asset.copy ac ON ac.id = ahr.current_copy JOIN config.copy_status ccs ON ccs.id = ac.status WHERE ahr.capture_time IS NOT NULL AND ahr.fulfillment_time IS NULL and ahr.cancel_time IS NULL AND ahr.id IN (SELECT id FROM mig_schema.action_hold_request) GROUP BY 2 ORDER By 2 + + + + hold_depth + Holds + 0 + Depth of Unfilled Holds + Count.Depth + SELECT COUNT(ahr.id), ahr.selection_depth FROM action.hold_request ahr WHERE ahr.id IN (SELECT id FROM mig_schema.action_hold_request) AND ahr.cancel_time IS NULL AND ahr.capture_time IS NULL AND ahr.fulfillment_time IS NULL GROUP BY 2 ORDER BY 2 + + + + + + asset_copy_count + Count of Copies by Library + Assets + 0 + Copy Count.Library.Migrated + SELECT COUNT(ac.id), aou.name, ac.x_migrate::TEXT FROM mig_schema.asset_copy_legacy ac JOIN actor.org_unit aou ON aou.id = ac.circ_lib GROUP BY 2, 3 ORDER BY 2, 3 + + + + asset_deleted_copies + Deleted Copies + Assets + 0 + Copy Count.Deleted.Migrated + SELECT COUNT(ac.id), ac.deleted::TEXT, ac.x_migrate::TEXT FROM mig_schema.asset_copy_legacy ac GROUP BY 2, 3 + + + + asset_copies_by_status + Copies by Status + Assets + 0 + Copy Count.Status.Migrated + SELECT COUNT(ac.id), cs.name, ac.x_migrate::TEXT FROM mig_schema.asset_copy_legacy ac JOIN config.copy_status cs ON cs.id = ac.status GROUP BY 2, 3 ORDER BY 2, 3 + + + + asset_circ_mod_copies_count + Copies by Circulation Modifier + Assets + 0 + Copy Count.Circulation Modifier.Migrated + SELECT COUNT(ac.id), ac.circ_modifier, ac.x_migrate::TEXT FROM mig_schema.asset_copy_legacy ac GROUP BY 2, 3 ORDER BY 2, 3 + + + + asset_copy_notes + Copy Notes + Assets + 0 + Note Count.Public.Migrated + SELECT COUNT(acnote.id), acnote.pub::TEXT, acnote.x_migrate::TEXT FROM mig_schema.asset_copy_note_legacy acnote GROUP BY 2, 3 ORDER BY 2, 3 + + + + asset_copy_notes + Copy Notes + Assets + 1 + Note Count.Public + SELECT COUNT(acnote.id), acnote.pub::TEXT FROM mig_schema.asset_copy_note acnote GROUP BY 2 ORDER BY 2 + + + + asset_vols_by_lib + Volumes by Library + Assets + 0 + Volume Count.Library.Migrated + SELECT COUNT(acn.id), aou.name, acn.x_migrate::TEXT FROM mig_schema.asset_call_number_legacy acn JOIN mig_schema.actor_org_unit_legacy aou ON aou.id = acn.owning_lib GROUP BY 2, 3 ORDER BY 2, 3 + + + + asset_vols_by_lib + Volumes by Library + Assets + 1 + Volume Count.Library + SELECT COUNT(acn.id), aou.name FROM mig_schema.asset_call_number acn JOIN actor.org_unit aou ON aou.id = acn.owning_lib GROUP BY 2 ORDER BY 2 + + + + asset_cops_by_loc_and_org + Copies by Location + Assets + 0 + Copy Count.Library.Circ Library.Migrated + SELECT COUNT(ac.id), acl.name, aou.name, ac.x_migrate::TEXT FROM mig_schema.asset_copy_legacy ac JOIN asset.copy_location acl ON acl.id = ac.location JOIN actor.org_unit aou ON aou.id = ac.circ_lib GROUP BY 2, 3, 4 ORDER BY 2, 3, 4 + + + + asset_barcode_lengths + Barcode Lengths by Library + Assets + 0 + Count of Barcode.Barcode Length.Library + SELECT COUNT(ac.id), LENGTH(ac.barcode), aou.name FROM mig_schema.asset_copy_legacy ac JOIN actor.org_unit aou ON aou.id = ac.circ_lib WHERE ac.x_migrate = TRUE GROUP BY 2, 3 ORDER BY 3, 2 + + + + asset_barcode_patterns + Common Barcode Starting Patterns + Assets + 0 + Count of Barcodes (greater than 10).Left 60% of Characters + SELECT COUNT(ac.id), LEFT(ac.barcode,(ROUND(LENGTH(ac.barcode)*.6))::INT) FROM mig_schema.asset_copy_legacy ac WHERE ac.x_migrate = TRUE GROUP BY 2 HAVING COUNT(ac.id) > 10 ORDER BY 2 + + + + asset_barcode_incumbent_collisions + Copy Barcode Incumbent Collisions + Assets + 0 + Collision Count + SELECT COUNT(id) FROM mig_schema.asset_copy_legacy WHERE x_migrate = TRUE AND barcode ~* 'collision' and barcode ~* 'incumbent' + Incumbent collisions are those where the migrated barcodes collide with existing barcodes in the database. + + + + asset_barcode_incumbent_collisions + Copy Barcode Incumbent Collisions + Assets + 1 + Collision Count + SELECT COUNT(id) FROM mig_schema.asset_copy WHERE barcode ~* 'collision' and barcode ~* 'incumbent' + Incumbent collisions are those where the migrated barcodes collide with existing barcodes in the database. + + + + asset_barcode_internal_collisions + Copy Barcode Internal Collisions + Assets + 0 + Collision Count + SELECT COUNT(id) FROM mig_schema.asset_copy_legacy WHERE x_migrate = TRUE AND barcode ~* 'collision' and barcode ~* 'internal' + Internal collisions are those where the migrated barcodes have conflicts within their list of barcodes. + + + + asset_barcode_internal_collisions + Copy Barcode Internal Collisions + Assets + 1 + Collision Count + SELECT COUNT(id) FROM mig_schema.asset_copy WHERE barcode ~* 'collision' and barcode ~* 'internal' + Internal collisions are those where the migrated barcodes have conflicts within their list of barcodes. + + + + asset_barcode_collisions_shortlist + Copy Barcode Collisions (first 20) + Assets + 0 + Collision List + SELECT ac.barcode FROM mig_schema.asset_copy_legacy ac WHERE ac.barcode ~* 'collision' ORDER BY 1 LIMIT 20 + This is a shortlist of patron barcode collisions that maxes out at 20. If there are more collisions we will need to run a custom report. + + + + asset_barcode_collisions_shortlist + Copy Barcode Collisions (first 20) + Assets + 1 + Collision List + SELECT ac.barcode FROM mig_schema.asset_copy ac WHERE ac.barcode ~* 'collision' ORDER BY 1 LIMIT 20 + This is a shortlist of patron barcode collisions that maxes out at 20. If there are more collisions we will need to run a custom report. + + + + asset_barcode_collision_patterns + Common Copy Barcode Collision Patterns + Assets + 0 + Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters + SELECT COUNT(ac.id), LEFT(ac.barcode,(ROUND(LENGTH(ac.barcode)*.6))::INT) FROM mig_schema.asset_copy_legacy ac WHERE barcode ~* 'collision' GROUP BY 2 HAVING COUNT(ac.id) > 10 ORDER BY 2 + + + + asset_barcode_collision_patterns + Common Copy Barcode Collision Patterns + Assets + 1 + Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters + SELECT COUNT(ac.id), LEFT(ac.barcode,(ROUND(LENGTH(ac.barcode)*.6))::INT) FROM mig_schema.asset_copy ac WHERE barcode ~* 'collision' GROUP BY 2 HAVING COUNT(ac.id) > 10 ORDER BY 2 + + + + asset_stat_cats + Copy Statistical Categories + Assets + 0 + Stat Cat Count.Library.Statistical Category + SELECT COUNT(ac_sc.id), aou.name, ac_sc.name FROM mig_schema.asset_stat_cat_legacy ac_sc JOIN actor.org_unit aou ON aou.id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 + + + + asset_stat_cats + Copy Statistical Categories + Assets + 1 + Stat Cat Count.Library.Statistical Category + SELECT COUNT(ac_sc.id), aou.name, ac_sc.name FROM mig_schema.asset_stat_cat ac_sc JOIN actor.org_unit aou ON aou.id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 + + + + asset_stat_cat_entries + Copy Stat Cat User Entries + Assets + 0 + Copy Stat Count.Library.Statistical Category + SELECT COUNT(map.id), aou.name, ac_sc.name FROM mig_schema.asset_stat_cat_entry_copy_map_legacy map JOIN mig_schema.asset_stat_cat_legacy ac_sc ON ac_sc.id = map.stat_cat JOIN actor.org_unit aou ON aou.id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 + + + + asset_stat_cat_entries + Copy Stat Cat User Entries + Assets + 1 + Copy Stat Count.Library.Statistical Category + SELECT COUNT(map.id), aou.name, ac_sc.name FROM mig_schema.asset_stat_cat_entry_copy_map map JOIN mig_schema.asset_stat_cat ac_sc ON ac_sc.id = map.stat_cat JOIN actor.org_unit aou ON aou. +id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 + + + + asset_copy_tags + Copy Tags + Assets + 0 + Tag Count.Copy Tag Type.Copy Tag Label.Staff Note.Public + SELECT COUNT(map.id), tag.tag_type, tag.label, tag.staff_note, tag.pub FROM mig_schema.asset_copy_tag tag JOIN mig_schema.asset_copy_tag_copy_map map ON map.tag = tag.id GROUP BY 2,3,4,5 ORDER BY 2,3 + + + + + + money_billing_voided + Bills Voided And Not + Money + 0 + Count.Voided.Sum.Migrated + SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM mig_schema.money_billing_legacy a GROUP BY 2, 4 ORDER BY 2, 4 + + + + money_billing_voided + Bills Voided And Not + Money + 1 + Count.Voided.Sum + SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM mig_schema.money_billing a GROUP BY 2 ORDER BY 2, 3 + + + + money_billing_by_type + Bills by Type + Money + 0 + Count.Billing Type.Migrated + SELECT COUNT(a.id), a.billing_type, a.x_migrate::TEXT FROM mig_schema.money_billing_legacy a GROUP BY 2, 3 ORDER BY 2, 3 + + + + money_billing_by_type + Bills by Type + Money + 1 + Count.Billing Type + SELECT COUNT(a.id), a.billing_type FROM mig_schema.money_billing a GROUP BY 2 ORDER BY 2 + + + + money_cash_payment + Cash Payments + Money + 0 + Count.Voided.Sum.Migrated + SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM mig_schema.money_cash_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 + + + + money_cash_payment + Cash Payments + Money + 1 + Count.Voided.Sum + SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM mig_schema.money_cash_payment a GROUP BY 2 ORDER BY 2 + + + + money_check_payment + Check Payments + Money + 0 + Count.Voided.Sum.Migrated + SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM mig_schema.money_check_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 + + + + money_forgive_payment + Forgive Payments + Money + 0 + Count.Voided.Sum.Migrated + SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM mig_schema.money_forgive_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 + + + + money_forgive_payment + Forgive Payments + Money + 1 + Count.Voided.Sum + SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM mig_schema.money_forgive_paymen a GROUP BY 2 ORDER BY 2 + + + + money_goods_payment + Goods Payments + Money + 0 + Count.Voided.Sum.Migrated + SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM mig_schema.money_goods_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 + + + + money_work_payment + Work Payments + Money + 0 + Count.Voided.Sum.Migrated + SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM mig_schema.money_work_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 + + + + money_credit_card_payment + Credit Card Payments + Money + 0 + Count.Voided.Sum.Migrated + SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM mig_schema.money_credit_card_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 + + + + money_credit_payment + Credit Payments + Money + 0 + Count.Voided.Sum.Migrated + SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM mig_schema.money_credit_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 + + + + + + + bibs + Extracted Bibliographic Records + Bibs + 0 + Count.Deleted.Migrated + SELECT COUNT(bre.id), bre.deleted::TEXT, bre.x_migrate::TEXT FROM mig_schema.biblio_record_entry_legacy bre GROUP BY 2, 3 ORDER BY 2, 3 + False means the records are not deleted. + + + + bibs + Extracted Bibliographic Records + Bibs + 1 + Count + SELECT COUNT(eg) FROM mig_schema.bib_id_map + + + + bibswovolumes + Bibliographic Records Without Volumes + Bibs + 0 + Count + SELECT COUNT(eg) FROM mig_schema.bib_id_map where eg::INTEGER NOT IN (SELECT DISTINCT record FROM mig_schema.asset_call_number) + + + + bibs_notes + Bib Record Notes + Bibs + 0 + Count.Migrated + SELECT COUNT(b.id), b.x_migrate::TEXT FROM mig_schema.biblio_record_note_legacy b GROUP BY 2 + + + + bibs_notes + Bib Record Notes + Bibs + 1 + Count + SELECT COUNT(b.id) FROM mig_schema.biblio_record_note b + + + + bibs_peers + Peer Bib Copies + Bibs + 0 + Count.Migrated + SELECT COUNT(b.id), b.x_migrate::TEXT FROM mig_schema.biblio_peer_bib_copy_map_legacy b GROUP BY 2 + + + + bibs_peers + Peer Bib Copies + Bibs + 1 + Count + SELECT COUNT(b.id) FROM mig_schema.biblio_peer_bib_copy_map b + + + + bibs_parts + Monograph Parts + Bibs + 0 + Count.Migrated + SELECT COUNT(b.id), b.x_migrate::TEXT FROM mig_schema.biblio_monograph_part_legacy b GROUP BY 2 + + + + bibs_parts + Monograph Parts + Bibs + 1 + Count + SELECT COUNT(b.id) FROM mig_schema.biblio_monograph_part b + + + + + + usrsbyorg + Patrons by Home Org + Actors + 0 + Count.Library.Deleted.Migrated + SELECT COUNT(au.id), aou.name, au.deleted::TEXT, au.x_migrate::TEXT FROM mig_schema.actor_usr_legacy au JOIN actor.org_unit aou ON aou.id = au.home_ou GROUP BY 2, 3, 4 ORDER BY 2, 3, 4 + + + + usrsbypgt + Patrons by Permission Group + Actors + 0 + Count.Permission Group.Migrated + SELECT COUNT(au.id), pgt.name, au.x_migrate::TEXT FROM mig_schema.actor_usr_legacy au JOIN permission.grp_tree pgt ON pgt.id = au.profile GROUP BY 2, 3 ORDER BY 2, 3 + + + + active_usrs + Patron by Active Status + Actors + 0 + Count of Users.Active.Migrated + SELECT COUNT(id), active::TEXT, x_migrate::TEXT FROM mig_schema.actor_usr_legacy GROUP BY 2, 3 + + + + active_usrs + Patrons by Active Status + Actors + 1 + Count of Users.Active + SELECT COUNT(id), active::TEXT FROM mig_schema.actor_usr GROUP BY 2 + + + + active_usr_barcodes + Patron Barcodes by Active Status + Actors + 0 + Count of Barcodes.Active.Migrated + SELECT COUNT(id), active::TEXT, x_migrate::TEXT FROM mig_schema.actor_card_legacy GROUP BY 2, 3 + + + + active_usr_barcodes + Patron Barcodes by Active Status + Actors + 1 + Count of Barcodes.Active + SELECT COUNT(id), active::TEXT FROM mig_schema.actor_card GROUP BY 2 + + + + usr_barcode_lengths + Barcode Lengths by Library + Actors + 0 + Count of Barcode.Barcode Length.Library + SELECT COUNT(acard.id), LENGTH(acard.barcode), aou.name FROM mig_schema.actor_card_legacy acard JOIN mig_schema.actor_usr_legacy au ON au.id = acard.usr JOIN actor.org_unit aou ON aou.id = au.home_ou WHERE acard.x_migrate = TRUE GROUP BY 2, 3 ORDER BY 3, 2 + + + + usr_barcode_lengths + Barcode Lengths by Library + Actors + 1 + Count of Barcode.Barcode Length.Library + SELECT COUNT(acard.id), LENGTH(acard.barcode), aou.name FROM mig_schema.actor_card acard JOIN mig_schema.actor_usr au ON au.id = acard.usr JOIN actor.org_unit aou ON aou.id = au.home_ou GROUP BY 2, 3 ORDER BY 3, 2 + + + + usr_barcode_patterns + Common Barcode Starting Patterns + Actors + 0 + Count of Barcodes (greater than 10).Left 60% of Characters + SELECT COUNT(acard.id), LEFT(acard.barcode,(ROUND(LENGTH(acard.barcode)*.6))::INT) FROM mig_schema.actor_card_legacy acard WHERE acard.x_migrate = TRUE GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2 + + + + usr_barcode_patterns + Common Barcode Starting Patterns + Actors + 1 + Count of Barcodes (greater than 10).Left 60% of Characters + SELECT COUNT(acard.id), LEFT(acard.barcode,(ROUND(LENGTH(acard.barcode)*.6))::INT) FROM mig_schema.actor_card acard GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2 + + + + usr_barcode_collisions + Patron Barcode Collisions + Actors + 0 + Collision Count + SELECT COUNT(acard.id) FROM mig_schema.actor_card_legacy acard WHERE barcode ~* 'collision' AND x_migrate = TRUE + + + + usr_barcode_collisions + Patron Barcode Collisions + Actors + 1 + Collision Count + SELECT COUNT(acard.id) FROM mig_schema.actor_card acard WHERE barcode ~* 'collision' + + + + usr_barcode_collision_shortlist + Patron Barcode Collisions (first 20) + Actors + 0 + Collision List + SELECT acard.barcode FROM mig_schema.actor_card_legacy acard WHERE acard.barcode ~* 'collision' AND acard.x_migrate = TRUE ORDER BY 1 LIMIT 20 + This is a shortlist of patron barcode collisions that maxes out at 20. If there are more collisions we will need to run a custom report. In some cases we may flag individual accounts to not migrate. + + + + usr_barcode_collision_shortlist + Patron Barcode Collisions (first 20) + Actors + 1 + Collision List + SELECT acard.barcode FROM mig_schema.actor_card acard WHERE acard.barcode ~* 'collision' ORDER BY 1 LIMIT 20 + This is a shortlist of patron barcode collisions that maxes out at 20. If there are more collisions we will need to run a custom report. In some cases we may flag individual accounts to not migrate. + + + + usr_barcode_collision_patterns + Common Patron Barcode Collision Patterns a.x_migrate + Actors + 0 + Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters + SELECT COUNT(acard.id), LEFT(acard.barcode,(ROUND(LENGTH(acard.barcode)*.6))::INT) FROM mig_schema.actor_card_legacy acard WHERE acard.barcode ~* 'collision' AND acard.x_migrate = TRUE GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2 + + + + usr_barcode_collision_patterns + Common Patron Barcode Collision Patterns a.x_migrate + Actors + 1 + Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters + SELECT COUNT(acard.id), LEFT(acard.barcode,(ROUND(LENGTH(acard.barcode)*.6))::INT) FROM mig_schema.actor_card acard WHERE acard.barcode ~* 'collision' GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2 + + + + usr_addressses_status + Patron Addresses by Valid Status + Actors + 0 + Count.Valid.Migrated + SELECT COUNT(aua.id), valid::TEXT, x_migrate::TEXT FROM mig_schema.actor_usr_address_legacy aua GROUP BY 2, 3 + + + + usr_addressses_status + Patron Addresses by Valid Status + Actors + 1 + Count.Valid + SELECT COUNT(aua.id), valid::TEXT FROM mig_schema.actor_usr_address aua GROUP BY 2 + + + + usr_addresses_pending + Patron Addresses by Pending Status + Actors + 0 + Count of Addresses.Pending.Migrated + SELECT COUNT(aua.id), pending::TEXT, x_migrate::TEXT FROM mig_schema.actor_usr_address_legacy aua GROUP BY 2, 3 + + + + usr_addresses_pending + Patron Addresses by Pending Status + Actors + 1 + Count of Addresses.Pending + SELECT COUNT(aua.id), pending::TEXT FROM mig_schema.actor_usr_address aua GROUP BY 2 + + + + usr_messages + Patron Messages + Actors + 0 + Count.Deleted.Migrated + SELECT COUNT(aum.id), deleted::TEXT, x_migrate::TEXT FROM mig_schema.actor_usr_message_legacy aum GROUP BY 2, 3 + + + + usr_messages + Patron Messages + Actors + 1 + Count.Deleted + SELECT COUNT(aum.id), deleted::TEXT FROM mig_schema.actor_usr_message_legacy aum GROUP BY 2 + + + + usr_notes + Patron Notes + Actors + 0 + Count.Public.Migrated + SELECT COUNT(aun.id), pub::TEXT, x_migrate::TEXT FROM mig_schema.actor_usr_note_legacy aun GROUP BY 2, 3 + + + + usr_notes + Patron Notes + Actors + 1 + Count.Public + SELECT COUNT(aun.id), pub::TEXT FROM mig_schema.actor_usr_note aun GROUP BY 2 + + + + usr_stat_cats + Patron Statistical Categories + Actors + 0 + Stat Cat Count.Library.Statistical Category.Migrated + SELECT COUNT(au_sc.id), aou.name, au_sc.name, au_sc.x_migrate::TEXT FROM mig_schema.actor_stat_cat_legacy au_sc JOIN actor.org_unit aou ON aou.id = au_sc.owner GROUP BY 2, 3, 4 ORDER BY 2, 3, 4 + + + + usr_stat_cats + Patron Statistical Categories + Actors + 1 + Stat Cat Count.Library.Statistical Category + SELECT COUNT(au_sc.id), aou.name, au_sc.name FROM mig_schema.actor_stat_cat au_sc JOIN actor.org_unit aou ON aou.id = au_sc.owner GROUP BY 2, 3 ORDER BY 2, 3 + + + + usr_stat_cat_entries + Patron Stat Cat User Entries + Actors + 0 + Patron Stat Count.Library.Statistical Category.Migrated + SELECT COUNT(map.id), aou.name, au_sc.name, map.x_migrate::TEXT FROM mig_schema.actor_stat_cat_entry_usr_map_legacy map JOIN mig_schema.actor_stat_cat_legacy au_sc ON au_sc.id = map.stat_cat JOIN actor.org_unit aou ON aou.id = au_sc.owner GROUP BY 2, 3, 4 ORDER BY 2,3, 4 + + + + usr_stat_cat_entries + Patron Stat Cat User Entries + Actors + 1 + Patron Stat Count.Library.Statistical Category + SELECT COUNT(map.id), aou.name, au_sc.name FROM mig_schema.actor_stat_cat_entry_usr_map map JOIN mig_schema.actor_stat_cat au_sc ON au_sc.id = map.stat_cat JOIN actor.org_unit aou ON aou.id = au_sc.owner GROUP BY 2, 3 ORDER BY 2,3 + + + + + fund_count + 0 + Acq + Migrated Funds + Number of Funds.Migrated + SELECT COUNT(id), x_migrate::TEXT FROM mig_schema.acq_fund_legacy GROUP BY 2; + + + + fund_count + 1 + Acq + Migrated Funds + Number of Funds + SELECT COUNT(id) FROM mig_schema.acq_fund; + + + + invoice_count + 0 + Acq + Migrated Invoices + Number of Invoices.Migrated + SELECT COUNT(id), x_migrate::TEXT FROM mig_schema.acq_invoice_legacy GROUP BY 2; + + + + invoice_count + 1 + Acq + Migrated Invoices + Number of Funds + SELECT COUNT(id) FROM mig_schema.acq_invoice; + + + + + serials_mfhd_count + serials + 0 + Migrated Serial MFHDs + Number of MFHDs + SELECT COUNT(id) FROM mig_schema.seriarecord_entry + + + + -- 1.7.2.5