From 2e7112dcbed51e644b0d930fcddf99c1cc65ddde Mon Sep 17 00:00:00 2001 From: Rogan Hamby Date: Wed, 15 Apr 2020 14:09:17 -0400 Subject: [PATCH] adding kmig reporter --- kmig.d/bin/mig-reporter | 243 ++++++++++++++++++++++++++++++++++++++++++ kmig.d/xml/stock_reports.xml | 127 ++++++++++++++++++++++ 2 files changed, 370 insertions(+), 0 deletions(-) create mode 100755 kmig.d/bin/mig-reporter create mode 100644 kmig.d/xml/stock_reports.xml diff --git a/kmig.d/bin/mig-reporter b/kmig.d/bin/mig-reporter new file mode 100755 index 0000000..eea3c1d --- /dev/null +++ b/kmig.d/bin/mig-reporter @@ -0,0 +1,243 @@ +#!/usr/bin/perl + +use strict; +use warnings; + +use DBI; +use Data::Dumper; +use Getopt::Long; +use XML::LibXML; +use Env qw( + HOME MYSQL_HOST MYSQL_TCP_PORT MYSQL_USER MYSQL_DATABASE MYSQL_PW + MIGSCHEMA MIGBASEWORKDIR MIGBASEGITDIR MIGGITDIR MIGWORKDIR +); +use open ':encoding(utf8)'; +use Cwd 'abs_path'; +use Cwd qw(getcwd); +use FindBin; +my $mig_bin = "$FindBin::Bin/"; +use lib "$FindBin::Bin/"; +use KMig; + +my $analyst = 'Equinox Open Library Initiative'; +my $report_title; +my $reports_xml = 'stock_reports.xml'; +my $tags = 'bibs.items.borrowers.circs.reserves.accounts.courses'; +my $added_page_title; +my $added_page_file; +my $i = 0; +my $parser = XML::LibXML->new(); +my $lines_per_page = 42; +my $koha_instance; +my $koha_conf_xml; + +my $dbh = KMig::db_connect(); + +my $ret = GetOptions( + 'instance:s' => \$koha_instance, + 'tags:s' => \$tags, + 'reports_xml:s' => \$reports_xml, + 'analyst:s' => \$analyst, + 'added_page_file:s' => \$added_page_file, + 'added_page_title:s' => \$added_page_title, + 'report_title:s' => \$report_title, + 'title:s' => \$report_title +); + +my $mig_path = abs_path($0); +$mig_path =~ s|[^/]+$||; +$reports_xml = find_xml($reports_xml,$mig_path); +if (!defined $reports_xml) { abort("Can not find xml reports file."); } +my $dom = $parser->parse_file($reports_xml); + +abort('must supply a --title parameter') unless defined $report_title; +if (defined $added_page_title) { abort ('must specify --added_page_file') unless defined $added_page_file; } +if (defined $added_page_file) { abort ('must specify --added_page_title') unless defined $added_page_title; } + +my $report_file = create_report_name($report_title); +$report_file = $MIGGITDIR . $report_file; +open(my $fh, '>', $report_file) or die "Could not open report 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; +} + +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) { + 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) { + print "\nchecking for $rname ... "; + my %report = find_report($dom,$t,$rname); + $r = print_query($fh,%report); + } +} +# end of main logic + +print "\n"; +close $fh; + +sub find_xml { + my $reports_xml = shift; + my $mig_path = shift; + + if ($reports_xml =~ m/\//) { return $reports_xml; } + + my $mig_test_file = $mig_path . '/../xml/' . $reports_xml; + my $working_test_dir = getcwd(); + my $working_test_file = $working_test_dir . '/' . $reports_xml; + + if (-e $mig_test_file) { return $mig_test_file; } + if (-e $working_test_file) { return $working_test_file; } + + return undef; +} + +sub find_report { + my $dom = shift; + my $tag = shift; + my $name = shift; + my %report; + + foreach my $node ($dom->findnodes('//report')) { + if ($node->findvalue('./tag') =~ $tag 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'), + 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 ":toc:\n"; + print $fh "\n"; +} + +sub print_query { + my $fh = shift; + my %report = @_; + my $query = $report{query}; + if (!defined $query) { print "No query defined, returning... \n"; return; } + print "$query\n"; + 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"; + print_usage(); + exit 1; +} + +sub print_usage { + print <<_USAGE_; + + --tags - period delimited these are the tags that it will + use to identify reports to run with (optional) + --report_title + +_USAGE_ +} + diff --git a/kmig.d/xml/stock_reports.xml b/kmig.d/xml/stock_reports.xml new file mode 100644 index 0000000..d7edb71 --- /dev/null +++ b/kmig.d/xml/stock_reports.xml @@ -0,0 +1,127 @@ + + + + + borrower_counts + borrowers + 0 + Borrowers Imported + Count.Branch.Borrower Category + SELECT COUNT(*), branchcode, categorycode FROM borrowers GROUP BY 2, 3 + + + + borrower_attributes + borrowers + 0 + Borrowers Imported + Borrower Count.Attrribute.Value + SELECT COUNT(*), code, attribute FROM borrower_attributes GROUP BY 2, 3 ORDER BY 2, 3 + + + + borrower_letters + borrowers + 0 + Letters Created + Letter Count.Module.Code + SELECT COUNT(*), module, code from letter GROUP BY 2, 3 + + + + bibs_loaded + bibs + Bibliographic Records Loaded + Count + SELECT COUNT(*) AS 'Number of Bib Records Loaded' FROM biblio; + + + + auths_loaded + bibs + Authority Records Loaded + Count + SELECT COUNT(*) AS 'Number of Authority Records Loaded' FROM auth_header; + + + + items_by_type_ccode_branch + items + Items by Item Type and Collection Code By Branch + Branch.Count of Items Loaded.Item Type.Collection Code + SELECT homebranch, COUNT(*), itype, ccode FROM items GROUP BY 1, 3, 4 ORDER BY 1, 3, 4; + + + + items_by_location + items + Items Loaded by Location + Count.Location + SELECT COUNT(*), location FROM items GROUP BY 2; + + + + items_by_lost + items + Items by Lost Value + Count.Lost Status + SELECT COUNT(*), CASE WHEN itemlost = 0 THEN 'Not Lost' WHEN itemlost = 1 THEN 'Lost' WHEN itemlost = 2 THEN 'Long Overdue' WHEN itemlost = 3 THEN 'Lost and Paid For' WHEN itemlost = 4 THEN 'Missing' END FROM items GROUP BY 2; + + + + circs_by_due_date + circs + Migrated Circulations with Due Dates + Count.Due Date + SELECT COUNT(*), YEAR(date_due) FROM issues GROUP BY 2 + + + + accounts_loaded + accounts + Accounts Loaded + Count of Migrated Fines.Sum of Migrated Fines + SELECT COUNT(*), SUM(amountoutstanding) FROM accountlines; + + + + reserves_loaded + reserves + Count of Reserves Loaded + Count + SELECT COUNT(*) FROM reserves; + + + + courses_loaded + courses + Count of Courses Loaded + Count + SELECT COUNT(*) FROM courses; + + + + course_reserves_loaded + courses + Count of Course Reserves Loaded + Count + SELECT COUNT(*) FROM course_reserves; + + + + course_items_linked + courses + Count of Course Items + Count + SELECT COUNT(*) FROM course_items; + + + + course_instructors_linked + courses + Count of Course Instructors + Count + SELECT COUNT(*) FROM course_instructors; + + + -- 1.7.2.5