--- /dev/null
+#!/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/\<//g;
+ my $sql = 'SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = \'' . $MIGSCHEMA . '\' AND table_name = \'' . $table . '\');';
+ my $sth = $dbh->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;
+}
+
+
--- /dev/null
+<reports_file>
+ <function>
+ <name>find_cmm</name>
+ <drop>DROP FUNCTION IF EXISTS mig_func_schema.find_cmm(BIGINT)</drop>
+ <create>
+ 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$</create>
+ </function>
+
+
+<!-- sample reports entry
+ <report>
+ <name>name of report</name>
+ <iteration value='0' /> which version, the first version to run is 0, first backup is 1, then so on
+ <tag>Money</tag> arbitrary tags, each should go in separate tag
+ <report_title>Migrated Billings</report_title> title used in the asciidoc output
+ <heading column='1'>Number of Billings.Migrated.Total</heading> period delimited
+ <query>SELECT COUNT(id),x_migrate::TEXT,SUM(amount) FROM mig_schema.money_billing_legacy GROUP BY 2;</query> query itself, mig_schema will be replaced
+ <note>Arbitrary note that can be included in the entries.</note>
+ </report>
+-->
+
+ <!-- CIRC REPORTS -->
+ <report>
+ <name>circ_count</name>
+ <tag>Circs</tag>
+ <iteration>0</iteration>
+ <report_title>Open Circulations</report_title>
+ <heading>Circulation Status.Migrated.Count of Circs</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>circ_count</name>
+ <tag>Circs</tag>
+ <iteration>1</iteration>
+ <report_title>Open Circulations</report_title>
+ <heading>Circulation Status.Count of Circs</heading>
+ <query>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</query>
+ </report>
+<!-- intent is to add a report by circulated and item status
+ <report>
+ <name>circ_count_by_item_status</name>
+ <tag>Circs</tag>
+ <iteration>0</iteration>
+ <report_title>Open Circulations</report_title>
+ <heading>Circulation Status.Migrated.Count of Circs</heading>
+ <query>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</query>
+ </report>
+-->
+ <report>
+ <name>circ_by_orgunit</name>
+ <tag>Circs</tag>
+ <iteration>0</iteration>
+ <report_title>Circulations by Org Unit</report_title>
+ <heading>Circulations Count.Migrated.Org Unit</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>circ_by_orgunit</name>
+ <tag>Circs</tag>
+ <iteration>1</iteration>
+ <report_title>Circulations by Org Unit</report_title>
+ <heading>Circulations Count.Org Unit</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>circs_by_duration</name>
+ <tag>Circs</tag>
+ <iteration>0</iteration>
+ <report_title>Migrated Circulations by Duration, Fine and Max Fine</report_title>
+ <heading>Count of Circs.Duration.Fine.Max Fine.Migrated</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>circs_by_duration</name>
+ <tag>Circs</tag>
+ <iteration>1</iteration>
+ <report_title>Circulations by Duration, Fine and Max Fine</report_title>
+ <heading>Count of Circs.Duration.Fine.Max Fine</heading>
+ <query>SELECT COUNT(id), duration, recurring_fine, max_fine FROM mig_schema.action_circulation GROUP BY 2, 3, 4 ORDER BY 2, 3, 4</query>
+ </report>
+
+ <report>
+ <name>circs_by_usrgroup</name>
+ <tag>Circs</tag>
+ <iteration>0</iteration>
+ <report_title>Circulations by Rules and Patron Group</report_title>
+ <heading>Count of Circs.Duration.Fine.Max Fine.User Group.Matchpoints</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>circs_by_usrgroup</name>
+ <tag>Circs</tag>
+ <iteration>1</iteration>
+ <report_title>Circulations by Rules and Patron Group</report_title>
+ <heading>Count of Circs.Duration.Fine.Max Fine.User Group.Matchpoints</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>circs_by_circmod</name>
+ <tag>Circs</tag>
+ <iteration>0</iteration>
+ <report_title>Circulations by Rules and Circulation Modifier</report_title>
+ <heading>Count of Circs.Duration.Fine.Max Fine.Circulation Modifier.Matchpoints</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>circs_by_circmod</name>
+ <tag>Circs</tag>
+ <iteration>1</iteration>
+ <report_title>Circulations by Rules and Circulation Modifier</report_title>
+ <heading>Count of Circs.Duration.Fine.Max Fine.Circulation Modifier.Matchpoints</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>circs_by_orgunit</name>
+ <tag>Circs</tag>
+ <iteration>0</iteration>
+ <report_title>Circulations by Rules and Org Unit</report_title>
+ <heading>Count of Circs.Duration.Fine.Max Fine.Library Branch</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>circs_by_orgunit</name>
+ <tag>Circs</tag>
+ <iteration>1</iteration>
+ <report_title>Circulations by Rules and Org Unit</report_title>
+ <heading>Count of Circs.Duration.Fine.Max Fine.Library Branch</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>non_cat_circs</name>
+ <tag>Circs</tag>
+ <iteration>0</iteration>
+ <report_title>Non-Cataloged Circulation</report_title>
+ <heading>Circulations Count.Migrated</heading>
+ <query>SELECT COUNT(id), x_migrate::TEXT FROM mig_schema.action_non_cataloged_circulation_legacy GROUP BY 2</query>
+ </report>
+
+ <report>
+ <name>non_cat_circs</name>
+ <tag>Circs</tag>
+ <iteration>1</iteration>
+ <report_title>Non-Cataloged Circulation</report_title>
+ <heading>Circulations Count</heading>
+ <query>SELECT COUNT(id) FROM mig_schema.action_non_cataloged_circulation</query>
+ </report>
+
+ <report>
+ <name>in_house</name>
+ <tag>Circs</tag>
+ <iteration>0</iteration>
+ <report_title>In House Use</report_title>
+ <heading>In House Use Records.Migrated</heading>
+ <query>SELECT COUNT(id), x_migrate::TEXT FROM mig_schema.action_in_house_use_legacy GROUP BY 2</query>
+ </report>
+
+ <report>
+ <name>in_house</name>
+ <tag>Circs</tag>
+ <iteration>1</iteration>
+ <report_title>In House Use</report_title>
+ <heading>In House Use Records</heading>
+ <query>SELECT COUNT(id) FROM mig_schema.action_in_house_use</query>
+ </report>
+
+ <report>
+ <name>circs_missing_rules</name>
+ <tag>Circs</tag>
+ <iteration>1</iteration>
+ <report_title>Circs Missing Rules</report_title>
+ <heading>Count.Field Missing</heading>
+ <query>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
+ </query>
+ </report>
+
+ <report>
+ <name>circ_open_by_item_status</name>
+ <tag>Circs</tag>
+ <iteration>0</iteration>
+ <report_title>Open Circulation and Status of Linked Items</report_title>
+ <heading>Count.Status</heading>
+ <query>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</query>
+ </report>
+
+ <!-- HOLDS REPORTS -->
+
+ <report>
+ <name>holds</name>
+ <tag>Holds</tag>
+ <iteration>0</iteration>
+ <report_title>Migrated Holds</report_title>
+ <heading>Hold Type.Hold Count.Migrated</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>holds_bytype</name>
+ <tag>Holds</tag>
+ <iteration>0</iteration>
+ <report_title>Migrated Holds By Type</report_title>
+ <heading>Hold Type.Hold Count.Migrated</heading>
+ <query>SELECT hold_type as "Hold Type", COUNT(id), x_migrate::TEXT FROM mig_schema.action_hold_request_legacy GROUP BY 1, 3</query>
+ </report>
+
+ <report>
+ <name>transit_open_by_item_status</name>
+ <tag>Holds</tag>
+ <iteration>0</iteration>
+ <report_title>Transit Copy Records and Status of Linked Items</report_title>
+ <heading>Count.Status</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>transit_copies_by_status</name>
+ <tag>Holds</tag>
+ <iteration>0</iteration>
+ <report_title>Status of Items with Count of Open In Transits</report_title>
+ <heading>Count.Status.Count of Open Transits</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>hold_copies_by_status</name>
+ <tag>Holds</tag>
+ <iteration>0</iteration>
+ <report_title>Captured Holds with Status of Items</report_title>
+ <heading>Count of Captured Hold.Status of Item</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>hold_depth</name>
+ <tag>Holds</tag>
+ <iteration>0</iteration>
+ <report_title>Depth of Unfilled Holds</report_title>
+ <heading>Count.Depth</heading>
+ <query>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</query>
+ </report>
+
+ <!-- ASSET REPORTS -->
+
+ <report>
+ <name>asset_copy_count</name>
+ <report_title>Count of Copies by Library</report_title>
+ <tag>Assets</tag>
+ <iteration>0</iteration>
+ <heading>Copy Count.Library.Migrated</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>asset_deleted_copies</name>
+ <report_title>Deleted Copies</report_title>
+ <tag>Assets</tag>
+ <iteration>0</iteration>
+ <heading>Copy Count.Deleted.Migrated</heading>
+ <query>SELECT COUNT(ac.id), ac.deleted::TEXT, ac.x_migrate::TEXT FROM mig_schema.asset_copy_legacy ac GROUP BY 2, 3</query>
+ </report>
+
+ <report>
+ <name>asset_copies_by_status</name>
+ <report_title>Copies by Status</report_title>
+ <tag>Assets</tag>
+ <iteration>0</iteration>
+ <heading>Copy Count.Status.Migrated</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>asset_circ_mod_copies_count</name>
+ <report_title>Copies by Circulation Modifier</report_title>
+ <tag>Assets</tag>
+ <iteration>0</iteration>
+ <heading>Copy Count.Circulation Modifier.Migrated</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>asset_copy_notes</name>
+ <report_title>Copy Notes</report_title>
+ <tag>Assets</tag>
+ <iteration>0</iteration>
+ <heading>Note Count.Public.Migrated</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>asset_copy_notes</name>
+ <report_title>Copy Notes</report_title>
+ <tag>Assets</tag>
+ <iteration>1</iteration>
+ <heading>Note Count.Public</heading>
+ <query>SELECT COUNT(acnote.id), acnote.pub::TEXT FROM mig_schema.asset_copy_note acnote GROUP BY 2 ORDER BY 2</query>
+ </report>
+
+ <report>
+ <name>asset_vols_by_lib</name>
+ <report_title>Volumes by Library</report_title>
+ <tag>Assets</tag>
+ <iteration>0</iteration>
+ <heading>Volume Count.Library.Migrated</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>asset_vols_by_lib</name>
+ <report_title>Volumes by Library</report_title>
+ <tag>Assets</tag>
+ <iteration>1</iteration>
+ <heading>Volume Count.Library</heading>
+ <query>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</query>
+ </report>
+ <!--
+ <report>
+ <name>asset_cops_by_loc</name>
+ <report_title>Copies by Location</report_title>
+ <tag>Assets</tag>
+ <iteration>0</iteration>
+ <heading>Copy Count.Library.Migrated</heading>
+ <query>SELECT COUNT(ac.id), acl.name, ac.x_migrate::TEXT FROM mig_schema.asset_copy_legacy ac JOIN asset.copy_location acl ON acl.id = ac.location GROUP BY 2, 3 ORDER BY 2, 3</query>
+ </report>
+ -->
+ <report>
+ <name>asset_cops_by_loc_and_org</name>
+ <report_title>Copies by Location</report_title>
+ <tag>Assets</tag>
+ <iteration>0</iteration>
+ <heading>Copy Count.Library.Circ Library.Migrated</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>asset_barcode_lengths</name>
+ <report_title>Barcode Lengths by Library</report_title>
+ <tag>Assets</tag>
+ <iteration>0</iteration>
+ <heading>Count of Barcode.Barcode Length.Library</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>asset_barcode_patterns</name>
+ <report_title>Common Barcode Starting Patterns</report_title>
+ <tag>Assets</tag>
+ <iteration>0</iteration>
+ <heading>Count of Barcodes (greater than 10).Left 60% of Characters</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>asset_barcode_incumbent_collisions</name>
+ <report_title>Copy Barcode Incumbent Collisions</report_title>
+ <tag>Assets</tag>
+ <iteration>0</iteration>
+ <heading>Collision Count</heading>
+ <query>SELECT COUNT(id) FROM mig_schema.asset_copy_legacy WHERE x_migrate = TRUE AND barcode ~* 'collision' and barcode ~* 'incumbent'</query>
+ <note>Incumbent collisions are those where the migrated barcodes collide with existing barcodes in the database.</note>
+ </report>
+
+ <report>
+ <name>asset_barcode_incumbent_collisions</name>
+ <report_title>Copy Barcode Incumbent Collisions</report_title>
+ <tag>Assets</tag>
+ <iteration>1</iteration>
+ <heading>Collision Count</heading>
+ <query>SELECT COUNT(id) FROM mig_schema.asset_copy WHERE barcode ~* 'collision' and barcode ~* 'incumbent'</query>
+ <note>Incumbent collisions are those where the migrated barcodes collide with existing barcodes in the database.</note>
+ </report>
+
+ <report>
+ <name>asset_barcode_internal_collisions</name>
+ <report_title>Copy Barcode Internal Collisions</report_title>
+ <tag>Assets</tag>
+ <iteration>0</iteration>
+ <heading>Collision Count</heading>
+ <query>SELECT COUNT(id) FROM mig_schema.asset_copy_legacy WHERE x_migrate = TRUE AND barcode ~* 'collision' and barcode ~* 'internal'</query>
+ <note>Internal collisions are those where the migrated barcodes have conflicts within their list of barcodes.</note>
+ </report>
+
+ <report>
+ <name>asset_barcode_internal_collisions</name>
+ <report_title>Copy Barcode Internal Collisions</report_title>
+ <tag>Assets</tag>
+ <iteration>1</iteration>
+ <heading>Collision Count</heading>
+ <query>SELECT COUNT(id) FROM mig_schema.asset_copy WHERE barcode ~* 'collision' and barcode ~* 'internal'</query>
+ <note>Internal collisions are those where the migrated barcodes have conflicts within their list of barcodes.</note>
+ </report>
+
+ <report>
+ <name>asset_barcode_collisions_shortlist</name>
+ <report_title>Copy Barcode Collisions (first 20)</report_title>
+ <tag>Assets</tag>
+ <iteration>0</iteration>
+ <heading>Collision List</heading>
+ <query>SELECT ac.barcode FROM mig_schema.asset_copy_legacy ac WHERE ac.barcode ~* 'collision' ORDER BY 1 LIMIT 20</query>
+ <note>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.</note>
+ </report>
+
+ <report>
+ <name>asset_barcode_collisions_shortlist</name>
+ <report_title>Copy Barcode Collisions (first 20)</report_title>
+ <tag>Assets</tag>
+ <iteration>1</iteration>
+ <heading>Collision List</heading>
+ <query>SELECT ac.barcode FROM mig_schema.asset_copy ac WHERE ac.barcode ~* 'collision' ORDER BY 1 LIMIT 20</query>
+ <note>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.</note>
+ </report>
+
+ <report>
+ <name>asset_barcode_collision_patterns</name>
+ <report_title>Common Copy Barcode Collision Patterns</report_title>
+ <tag>Assets</tag>
+ <iteration>0</iteration>
+ <heading>Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>asset_barcode_collision_patterns</name>
+ <report_title>Common Copy Barcode Collision Patterns</report_title>
+ <tag>Assets</tag>
+ <iteration>1</iteration>
+ <heading>Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>asset_stat_cats</name>
+ <report_title>Copy Statistical Categories</report_title>
+ <tag>Assets</tag>
+ <iteration>0</iteration>
+ <heading>Stat Cat Count.Library.Statistical Category</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>asset_stat_cats</name>
+ <report_title>Copy Statistical Categories</report_title>
+ <tag>Assets</tag>
+ <iteration>1</iteration>
+ <heading>Stat Cat Count.Library.Statistical Category</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>asset_stat_cat_entries</name>
+ <report_title>Copy Stat Cat User Entries</report_title>
+ <tag>Assets</tag>
+ <iteration>0</iteration>
+ <heading>Copy Stat Count.Library.Statistical Category</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>asset_stat_cat_entries</name>
+ <report_title>Copy Stat Cat User Entries</report_title>
+ <tag>Assets</tag>
+ <iteration>1</iteration>
+ <heading>Copy Stat Count.Library.Statistical Category</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>asset_copy_tags</name>
+ <report_title>Copy Tags</report_title>
+ <tag>Assets</tag>
+ <iteration>0</iteration>
+ <heading>Tag Count.Copy Tag Type.Copy Tag Label.Staff Note.Public</heading>
+ <query>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</query>
+ </report>
+
+ <!-- MONEY REPORTS -->
+
+ <report>
+ <name>money_billing_voided</name>
+ <report_title>Bills Voided And Not</report_title>
+ <tag>Money</tag>
+ <iteration>0</iteration>
+ <heading>Count.Voided.Sum.Migrated</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>money_billing_voided</name>
+ <report_title>Bills Voided And Not</report_title>
+ <tag>Money</tag>
+ <iteration>1</iteration>
+ <heading>Count.Voided.Sum</heading>
+ <query>SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM mig_schema.money_billing a GROUP BY 2 ORDER BY 2, 3</query>
+ </report>
+
+ <report>
+ <name>money_billing_by_type</name>
+ <report_title>Bills by Type</report_title>
+ <tag>Money</tag>
+ <iteration>0</iteration>
+ <heading>Count.Billing Type.Migrated</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>money_billing_by_type</name>
+ <report_title>Bills by Type</report_title>
+ <tag>Money</tag>
+ <iteration>1</iteration>
+ <heading>Count.Billing Type</heading>
+ <query>SELECT COUNT(a.id), a.billing_type FROM mig_schema.money_billing a GROUP BY 2 ORDER BY 2</query>
+ </report>
+
+ <report>
+ <name>money_cash_payment</name>
+ <report_title>Cash Payments</report_title>
+ <tag>Money</tag>
+ <iteration>0</iteration>
+ <heading>Count.Voided.Sum.Migrated</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>money_cash_payment</name>
+ <report_title>Cash Payments</report_title>
+ <tag>Money</tag>
+ <iteration>1</iteration>
+ <heading>Count.Voided.Sum</heading>
+ <query>SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM mig_schema.money_cash_payment a GROUP BY 2 ORDER BY 2</query>
+ </report>
+
+ <report>
+ <name>money_check_payment</name>
+ <report_title>Check Payments</report_title>
+ <tag>Money</tag>
+ <iteration>0</iteration>
+ <heading>Count.Voided.Sum.Migrated</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>money_forgive_payment</name>
+ <report_title>Forgive Payments</report_title>
+ <tag>Money</tag>
+ <iteration>0</iteration>
+ <heading>Count.Voided.Sum.Migrated</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>money_forgive_payment</name>
+ <report_title>Forgive Payments</report_title>
+ <tag>Money</tag>
+ <iteration>1</iteration>
+ <heading>Count.Voided.Sum</heading>
+ <query>SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM mig_schema.money_forgive_paymen a GROUP BY 2 ORDER BY 2</query>
+ </report>
+
+ <report>
+ <name>money_goods_payment</name>
+ <report_title>Goods Payments</report_title>
+ <tag>Money</tag>
+ <iteration>0</iteration>
+ <heading>Count.Voided.Sum.Migrated</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>money_work_payment</name>
+ <report_title>Work Payments</report_title>
+ <tag>Money</tag>
+ <iteration>0</iteration>
+ <heading>Count.Voided.Sum.Migrated</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>money_credit_card_payment</name>
+ <report_title>Credit Card Payments</report_title>
+ <tag>Money</tag>
+ <iteration>0</iteration>
+ <heading>Count.Voided.Sum.Migrated</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>money_credit_payment</name>
+ <report_title>Credit Payments</report_title>
+ <tag>Money</tag>
+ <iteration>0</iteration>
+ <heading>Count.Voided.Sum.Migrated</heading>
+ <query>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</query>
+ </report>
+
+
+ <!-- BIBS REPORTS -->
+
+ <report>
+ <name>bibs</name>
+ <report_title>Extracted Bibliographic Records</report_title>
+ <tag>Bibs</tag>
+ <iteration>0</iteration>
+ <heading>Count.Deleted.Migrated</heading>
+ <query>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</query>
+ <note>False means the records are not deleted.</note>
+ </report>
+
+ <report>
+ <name>bibs</name>
+ <report_title>Extracted Bibliographic Records</report_title>
+ <tag>Bibs</tag>
+ <iteration>1</iteration>
+ <heading>Count</heading>
+ <query>SELECT COUNT(eg) FROM mig_schema.bib_id_map</query>
+ </report>
+
+ <report>
+ <name>bibswovolumes</name>
+ <report_title>Bibliographic Records Without Volumes</report_title>
+ <tag>Bibs</tag>
+ <iteration>0</iteration>
+ <heading>Count</heading>
+ <query>SELECT COUNT(eg) FROM mig_schema.bib_id_map where eg::INTEGER NOT IN (SELECT DISTINCT record FROM mig_schema.asset_call_number)</query>
+ </report>
+
+ <report>
+ <name>bibs_notes</name>
+ <report_title>Bib Record Notes</report_title>
+ <tag>Bibs</tag>
+ <iteration>0</iteration>
+ <heading>Count.Migrated</heading>
+ <query>SELECT COUNT(b.id), b.x_migrate::TEXT FROM mig_schema.biblio_record_note_legacy b GROUP BY 2</query>
+ </report>
+
+ <report>
+ <name>bibs_notes</name>
+ <report_title>Bib Record Notes</report_title>
+ <tag>Bibs</tag>
+ <iteration>1</iteration>
+ <heading>Count</heading>
+ <query>SELECT COUNT(b.id) FROM mig_schema.biblio_record_note b</query>
+ </report>
+
+ <report>
+ <name>bibs_peers</name>
+ <report_title>Peer Bib Copies</report_title>
+ <tag>Bibs</tag>
+ <iteration>0</iteration>
+ <heading>Count.Migrated</heading>
+ <query>SELECT COUNT(b.id), b.x_migrate::TEXT FROM mig_schema.biblio_peer_bib_copy_map_legacy b GROUP BY 2</query>
+ </report>
+
+ <report>
+ <name>bibs_peers</name>
+ <report_title>Peer Bib Copies</report_title>
+ <tag>Bibs</tag>
+ <iteration>1</iteration>
+ <heading>Count</heading>
+ <query>SELECT COUNT(b.id) FROM mig_schema.biblio_peer_bib_copy_map b</query>
+ </report>
+
+ <report>
+ <name>bibs_parts</name>
+ <report_title>Monograph Parts</report_title>
+ <tag>Bibs</tag>
+ <iteration>0</iteration>
+ <heading>Count.Migrated</heading>
+ <query>SELECT COUNT(b.id), b.x_migrate::TEXT FROM mig_schema.biblio_monograph_part_legacy b GROUP BY 2</query>
+ </report>
+
+ <report>
+ <name>bibs_parts</name>
+ <report_title>Monograph Parts</report_title>
+ <tag>Bibs</tag>
+ <iteration>1</iteration>
+ <heading>Count</heading>
+ <query>SELECT COUNT(b.id) FROM mig_schema.biblio_monograph_part b</query>
+ </report>
+
+ <!-- ACTORS REPORTS -->
+
+ <report>
+ <name>usrsbyorg</name>
+ <report_title>Patrons by Home Org</report_title>
+ <tag>Actors</tag>
+ <iteration>0</iteration>
+ <heading>Count.Library.Deleted.Migrated</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>usrsbypgt</name>
+ <report_title>Patrons by Permission Group</report_title>
+ <tag>Actors</tag>
+ <iteration>0</iteration>
+ <heading>Count.Permission Group.Migrated</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>active_usrs</name>
+ <report_title>Patron by Active Status</report_title>
+ <tag>Actors</tag>
+ <iteration>0</iteration>
+ <heading>Count of Users.Active.Migrated</heading>
+ <query>SELECT COUNT(id), active::TEXT, x_migrate::TEXT FROM mig_schema.actor_usr_legacy GROUP BY 2, 3</query>
+ </report>
+
+ <report>
+ <name>active_usrs</name>
+ <report_title>Patrons by Active Status</report_title>
+ <tag>Actors</tag>
+ <iteration>1</iteration>
+ <heading>Count of Users.Active</heading>
+ <query>SELECT COUNT(id), active::TEXT FROM mig_schema.actor_usr GROUP BY 2</query>
+ </report>
+
+ <report>
+ <name>active_usr_barcodes</name>
+ <report_title>Patron Barcodes by Active Status</report_title>
+ <tag>Actors</tag>
+ <iteration>0</iteration>
+ <heading>Count of Barcodes.Active.Migrated</heading>
+ <query>SELECT COUNT(id), active::TEXT, x_migrate::TEXT FROM mig_schema.actor_card_legacy GROUP BY 2, 3</query>
+ </report>
+
+ <report>
+ <name>active_usr_barcodes</name>
+ <report_title>Patron Barcodes by Active Status</report_title>
+ <tag>Actors</tag>
+ <iteration>1</iteration>
+ <heading>Count of Barcodes.Active</heading>
+ <query>SELECT COUNT(id), active::TEXT FROM mig_schema.actor_card GROUP BY 2</query>
+ </report>
+
+ <report>
+ <name>usr_barcode_lengths</name>
+ <report_title>Barcode Lengths by Library</report_title>
+ <tag>Actors</tag>
+ <iteration>0</iteration>
+ <heading>Count of Barcode.Barcode Length.Library</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>usr_barcode_lengths</name>
+ <report_title>Barcode Lengths by Library</report_title>
+ <tag>Actors</tag>
+ <iteration>1</iteration>
+ <heading>Count of Barcode.Barcode Length.Library</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>usr_barcode_patterns</name>
+ <report_title>Common Barcode Starting Patterns</report_title>
+ <tag>Actors</tag>
+ <iteration>0</iteration>
+ <heading>Count of Barcodes (greater than 10).Left 60% of Characters</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>usr_barcode_patterns</name>
+ <report_title>Common Barcode Starting Patterns</report_title>
+ <tag>Actors</tag>
+ <iteration>1</iteration>
+ <heading>Count of Barcodes (greater than 10).Left 60% of Characters</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>usr_barcode_collisions</name>
+ <report_title>Patron Barcode Collisions</report_title>
+ <tag>Actors</tag>
+ <iteration>0</iteration>
+ <heading>Collision Count</heading>
+ <query>SELECT COUNT(acard.id) FROM mig_schema.actor_card_legacy acard WHERE barcode ~* 'collision' AND x_migrate = TRUE</query>
+ </report>
+
+ <report>
+ <name>usr_barcode_collisions</name>
+ <report_title>Patron Barcode Collisions</report_title>
+ <tag>Actors</tag>
+ <iteration>1</iteration>
+ <heading>Collision Count</heading>
+ <query>SELECT COUNT(acard.id) FROM mig_schema.actor_card acard WHERE barcode ~* 'collision'</query>
+ </report>
+
+ <report>
+ <name>usr_barcode_collision_shortlist</name>
+ <report_title>Patron Barcode Collisions (first 20)</report_title>
+ <tag>Actors</tag>
+ <iteration>0</iteration>
+ <heading>Collision List</heading>
+ <query>SELECT acard.barcode FROM mig_schema.actor_card_legacy acard WHERE acard.barcode ~* 'collision' AND acard.x_migrate = TRUE ORDER BY 1 LIMIT 20</query>
+ <note>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.</note>
+ </report>
+
+ <report>
+ <name>usr_barcode_collision_shortlist</name>
+ <report_title>Patron Barcode Collisions (first 20)</report_title>
+ <tag>Actors</tag>
+ <iteration>1</iteration>
+ <heading>Collision List</heading>
+ <query>SELECT acard.barcode FROM mig_schema.actor_card acard WHERE acard.barcode ~* 'collision' ORDER BY 1 LIMIT 20</query>
+ <note>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.</note>
+ </report>
+
+ <report>
+ <name>usr_barcode_collision_patterns</name>
+ <report_title>Common Patron Barcode Collision Patterns</report_title> a.x_migrate
+ <tag>Actors</tag>
+ <iteration>0</iteration>
+ <heading>Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>usr_barcode_collision_patterns</name>
+ <report_title>Common Patron Barcode Collision Patterns</report_title> a.x_migrate
+ <tag>Actors</tag>
+ <iteration>1</iteration>
+ <heading>Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>usr_addressses_status</name>
+ <report_title>Patron Addresses by Valid Status</report_title>
+ <tag>Actors</tag>
+ <iteration>0</iteration>
+ <heading>Count.Valid.Migrated</heading>
+ <query>SELECT COUNT(aua.id), valid::TEXT, x_migrate::TEXT FROM mig_schema.actor_usr_address_legacy aua GROUP BY 2, 3</query>
+ </report>
+
+ <report>
+ <name>usr_addressses_status</name>
+ <report_title>Patron Addresses by Valid Status</report_title>
+ <tag>Actors</tag>
+ <iteration>1</iteration>
+ <heading>Count.Valid</heading>
+ <query>SELECT COUNT(aua.id), valid::TEXT FROM mig_schema.actor_usr_address aua GROUP BY 2</query>
+ </report>
+
+ <report>
+ <name>usr_addresses_pending</name>
+ <report_title>Patron Addresses by Pending Status</report_title>
+ <tag>Actors</tag>
+ <iteration>0</iteration>
+ <heading>Count of Addresses.Pending.Migrated</heading>
+ <query>SELECT COUNT(aua.id), pending::TEXT, x_migrate::TEXT FROM mig_schema.actor_usr_address_legacy aua GROUP BY 2, 3</query>
+ </report>
+
+ <report>
+ <name>usr_addresses_pending</name>
+ <report_title>Patron Addresses by Pending Status</report_title>
+ <tag>Actors</tag>
+ <iteration>1</iteration>
+ <heading>Count of Addresses.Pending</heading>
+ <query>SELECT COUNT(aua.id), pending::TEXT FROM mig_schema.actor_usr_address aua GROUP BY 2</query>
+ </report>
+
+ <report>
+ <name>usr_messages</name>
+ <report_title>Patron Messages</report_title>
+ <tag>Actors</tag>
+ <iteration>0</iteration>
+ <heading>Count.Deleted.Migrated</heading>
+ <query>SELECT COUNT(aum.id), deleted::TEXT, x_migrate::TEXT FROM mig_schema.actor_usr_message_legacy aum GROUP BY 2, 3</query>
+ </report>
+
+ <report>
+ <name>usr_messages</name>
+ <report_title>Patron Messages</report_title>
+ <tag>Actors</tag>
+ <iteration>1</iteration>
+ <heading>Count.Deleted</heading>
+ <query>SELECT COUNT(aum.id), deleted::TEXT FROM mig_schema.actor_usr_message_legacy aum GROUP BY 2</query>
+ </report>
+
+ <report>
+ <name>usr_notes</name>
+ <report_title>Patron Notes</report_title>
+ <tag>Actors</tag>
+ <iteration>0</iteration>
+ <heading>Count.Public.Migrated</heading>
+ <query>SELECT COUNT(aun.id), pub::TEXT, x_migrate::TEXT FROM mig_schema.actor_usr_note_legacy aun GROUP BY 2, 3</query>
+ </report>
+
+ <report>
+ <name>usr_notes</name>
+ <report_title>Patron Notes</report_title>
+ <tag>Actors</tag>
+ <iteration>1</iteration>
+ <heading>Count.Public</heading>
+ <query>SELECT COUNT(aun.id), pub::TEXT FROM mig_schema.actor_usr_note aun GROUP BY 2</query>
+ </report>
+
+ <report>
+ <name>usr_stat_cats</name>
+ <report_title>Patron Statistical Categories</report_title>
+ <tag>Actors</tag>
+ <iteration>0</iteration>
+ <heading>Stat Cat Count.Library.Statistical Category.Migrated</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>usr_stat_cats</name>
+ <report_title>Patron Statistical Categories</report_title>
+ <tag>Actors</tag>
+ <iteration>1</iteration>
+ <heading>Stat Cat Count.Library.Statistical Category</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>usr_stat_cat_entries</name>
+ <report_title>Patron Stat Cat User Entries</report_title>
+ <tag>Actors</tag>
+ <iteration>0</iteration>
+ <heading>Patron Stat Count.Library.Statistical Category.Migrated</heading>
+ <query>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</query>
+ </report>
+
+ <report>
+ <name>usr_stat_cat_entries</name>
+ <report_title>Patron Stat Cat User Entries</report_title>
+ <tag>Actors</tag>
+ <iteration>1</iteration>
+ <heading>Patron Stat Count.Library.Statistical Category</heading>
+ <query>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</query>
+ </report>
+
+ <!-- ACQUISITIONS REPORTS -->
+ <report>
+ <name>fund_count</name>
+ <iteration>0</iteration>
+ <tag>Acq</tag>
+ <report_title>Migrated Funds</report_title>
+ <heading>Number of Funds.Migrated</heading>
+ <query>SELECT COUNT(id), x_migrate::TEXT FROM mig_schema.acq_fund_legacy GROUP BY 2;</query>
+ </report>
+
+ <report>
+ <name>fund_count</name>
+ <iteration>1</iteration>
+ <tag>Acq</tag>
+ <report_title>Migrated Funds</report_title>
+ <heading>Number of Funds</heading>
+ <query>SELECT COUNT(id) FROM mig_schema.acq_fund;</query>
+ </report>
+
+ <report>
+ <name>invoice_count</name>
+ <iteration>0</iteration>
+ <tag>Acq</tag>
+ <report_title>Migrated Invoices</report_title>
+ <heading>Number of Invoices.Migrated</heading>
+ <query>SELECT COUNT(id), x_migrate::TEXT FROM mig_schema.acq_invoice_legacy GROUP BY 2;</query>
+ </report>
+
+ <report>
+ <name>invoice_count</name>
+ <iteration>1</iteration>
+ <tag>Acq</tag>
+ <report_title>Migrated Invoices</report_title>
+ <heading>Number of Funds</heading>
+ <query>SELECT COUNT(id) FROM mig_schema.acq_invoice;</query>
+ </report>
+
+ <!-- SERIALS REPORTS -->
+ <report>
+ <name>serials_mfhd_count</name>
+ <tag>serials</tag>
+ <iteration>0</iteration>
+ <report_title>Migrated Serial MFHDs</report_title>
+ <heading>Number of MFHDs</heading>
+ <query>SELECT COUNT(id) FROM mig_schema.seriarecord_entry</query>
+ </report>
+
+</reports_file>
+
projects / migration-tools.git / commitdiff