--- /dev/null
+#!/usr/bin/perl
+# -*- coding: iso-8859-15 -*-
+###############################################################################
+=pod
+
+=item B<reporter> --title "Report Title"
+
+Generates an asciidoc file in the git working directory that can be converted to
+any appropriate format. The analyst and report parameters are required.
+
+Optional parameters are :
+
+-- analyst
+
+Default to "Equinox Open Library Initiative"
+
+--added_page_title and --added_page_file
+
+If one is used both must be. The added page file can be plain text or asciidoc. This
+adds an extra arbitrary page of notes to the report. Mig assumes the page file is in the mig git directory.
+
+--tags
+
+This will define a set of tags to use, if not set it will default to Circs,
+Holds, Actors, Bibs, Assets & Money.
+
+--debug on
+
+Gives more information about what is happening. Defaults to off.
+
+--reports_xml
+
+Allows you to override the default evergreen_staged_report.xml in the mig-xml folder.
+
+--captions on OR --captions off
+
+Adds the captions tag to asciidoc header to turn off captions in generated output.
+Defaults to off.
+
+=back
+
+=cut
+
+###############################################################################
+
+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 Getopt::Long;
+use Cwd 'abs_path';
+use Cwd qw(getcwd);
+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';
+pod2usage(-verbose => 1) if ! $ARGV[1];
+
+my $analyst = 'Equinox Open Library Initiative';;
+my $report_title;
+my $reports_xml = 'evergreen_staged_report.xml';
+my $tags;
+my $added_page_title;
+my $added_page_file;
+my $captions = 'off';
+my $i = 0;
+my $parser = XML::LibXML->new();
+my $lines_per_page = 42;
+my $debug = 'off';
+my $workbook;
+my $fh;
+
+my $ret = GetOptions(
+ 'analyst:s' => \$analyst,
+ 'report_title:s' => \$report_title,
+ 'title:s' => \$report_title,
+ 'reports_xml:s' => \$reports_xml,
+ 'tags:s' => \$tags,
+ 'added_page_title:s' => \$added_page_title,
+ 'added_page_file:s' => \$added_page_file,
+ 'captions:s' => \$captions,
+ 'debug:s' => \$debug
+);
+
+if (!defined $tags) {$tags = 'circs.holds.actors.bibs.assets.money.notices'};
+if (!defined $report_title) { abort('--report_title or --title must be supplied'); }
+if (!defined $analyst) { abort('--analyst must be supplied'); }
+
+my $mig_path = abs_path($0);
+$mig_path =~ s|[^/]+$||;
+$reports_xml = find_xml($reports_xml,$mig_path);
+if (!defined $reports_xml) { abort("Can not find xml reports file."); }
+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 (defined $added_page_file) { $added_page_file = $MIGGITDIR . $added_page_file; }
+
+my $dbh = Mig::db_connect();
+my $report_file = create_report_name($report_title);
+$report_file = $MIGGITDIR . $report_file;
+
+open($fh, '>', $report_file) or abort("Could not open output file $report_file!");
+write_title_page($report_title,$fh,$analyst,$captions);
+load_javascript($fh);
+
+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 abort("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');
+ my $sdrop = $dbh->prepare($fdrop);
+ my $screate = $dbh->prepare($fcreate);
+ print "dropping function $fname ... ";
+ $sdrop->execute();
+ print "creating function $fname\n\n";
+ $screate->execute();
+}
+
+foreach my $table ($dom->findnodes('//table')) {
+ my $tdrop = $table->findvalue('./drop');
+ my $tcreate = $table->findvalue('./create');
+ my $tname = $table->findvalue('./name');
+ my $sdrop = $dbh->prepare($tdrop);
+ my $screate = $dbh->prepare($tcreate);
+ print "dropping table $tname ... ";
+ $sdrop->execute();
+ print "creating table $tname\n\n";
+ $screate->execute();
+}
+
+$tags = lc($tags);
+my @report_tags = split(/\./,$tags);
+foreach my $t (@report_tags) {
+ print "\n\n=========== Starting to process tag $t\n";
+ print "==========================================\n\n";
+
+ my @asset_files;
+ foreach my $asset ($dom->findnodes('//asset')) {
+ if (index($asset->findvalue('./tag'),$t) != -1) {
+ push @asset_files, $asset->findvalue('./file');
+ }
+ }
+
+ foreach my $fname (@asset_files) {
+ my $asset_path = $mig_path . '../mig-asc/' . $fname;
+ open my $a, $asset_path or abort("Could not open $fname.");
+ while ( my $l = <$a> ) {
+ print $fh $l;
+ }
+ print $fh "<<<\n";
+ }
+
+ print_section_header(ucfirst($t),$fh);
+ my $linecount = $lines_per_page;
+ my $r;
+
+ undef @asset_files;
+ foreach my $asset ($dom->findnodes('//asset')) {
+ if (index($asset->findvalue('./tag'),$t) != -1) {
+ push @asset_files, $asset->findvalue('./file');
+ }
+ }
+
+ 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');
+ }
+ }
+
+ #only has one level of failover now but could change to array of hashes and loops
+ #but this keeps it simple and in practice I haven't needed more than two
+
+
+ foreach my $rname (@report_names) {
+ my %report0;
+ my %report1;
+ my $check_tables0;
+ my $check_tables1;
+
+ if ($debug eq 'on') {print "\nchecking for $rname ... ";}
+ %report0 = find_report($dom,$t,$rname,'0',$debug);
+ $check_tables0 = check_table($report0{query},$MIGSCHEMA,$debug,$rname);
+ if ($check_tables0 == 1) { $r = print_query($fh,%report0); } else {
+ %report1 = find_report($dom,$t,$rname,'1',$debug);
+ if (defined $report1{query}) {
+ $check_tables1 = check_table($report1{query},$MIGSCHEMA,$debug,$rname);
+ if ($check_tables1 == 1) { $r = print_query($fh,%report1); }
+ }
+ }
+ }
+
+}
+
+print "\n";
+
+foreach my $table ($dom->findnodes('//table')) {
+ my $tdrop = $table->findvalue('./drop');
+ my $tname = $table->findvalue('./name');
+ my $sdrop = $dbh->prepare($tdrop);
+ print "cleaning up table $tname ... \n";
+ $sdrop->execute();
+}
+
+close $fh;
+
+############ end of main logic
+
+sub find_xml {
+ my $reports_xml = shift;
+ my $mig_path = shift;
+
+ if ($reports_xml =~ m/\//) { return $reports_xml; }
+
+ my $mig_test_file = $mig_path . '/../mig-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 $iteration = shift;
+ my $debug = shift;
+ my %report;
+
+ if ($debug eq 'on') {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) {
+ if ($debug eq 'on') {print "succeeded ... \n";}
+ %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'),
+ display => $node->findvalue('./display'),
+ chart_labels => $node->findvalue('./chart_labels'),
+ divwidth => $node->findvalue('./divwidth'),
+ divheight => $node->findvalue('./divheight'),
+ );
+ return %report;
+ }
+ }
+ if ($debug eq 'on') {print "failed ... \n";}
+ 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";
+ return;
+}
+
+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;
+ $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 $captions = 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";
+ if ($captions eq 'on') { print $fh ":caption:\n"; }
+ print $fh "\n";
+}
+
+sub load_javascript {
+ my $fh = shift;
+
+ print $fh "++++\n";
+ print $fh "<script type=\"text/javascript\" src=\"https://www.gstatic.com/charts/loader.js\"></script>\n";
+ print $fh "++++\n";
+}
+
+sub check_table {
+ my $query = shift;
+ my $MIGSCHEMA = shift;
+ my $debug = shift;
+ my $report_name = shift;
+
+ if ($debug eq 'on') {print "$query\n";}
+
+ my $i;
+ my $return_flag = 1;
+ my @qe = split(/ /,$query);
+ $i = @qe;
+ $i--;
+ my @tables;
+ while ($i > -1) {
+ if ($qe[$i] eq 'FROM' or $qe[$i] eq 'JOIN') {
+ my $q = $i + 1;
+ if ($qe[$q] ne '(SELECT') {
+ push @tables, $qe[$q];
+ }
+ }
+ $i--;
+ }
+ if ($debug eq 'on') {print "checking tables ... ";}
+
+ $i = 0;
+ foreach my $table (@tables) {
+ my $sql;
+ my $schema;
+ if (index($table,'.') != -1) {
+ $schema = (split /\./,$table)[0];
+ $table = (split /\./,$table)[1];
+ }
+ $table = clean_query_string($table);
+ if (defined $schema) {
+ $schema = clean_query_string($schema);
+ $sql = 'SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = \'' . $schema . '\' AND table_name = \'' . $table . '\');';
+ } else {
+ $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 ($debug eq 'on') {print "detecting $table failed...\n";}
+ }
+ if ($row[0] eq '0') {$return_flag = 0;}
+ }
+ }
+ if ($return_flag == 1 and $debug eq 'on') {print "succeeded ...\n";}
+ if ($return_flag == 0) {print "! a table failed the find test for report $report_name\n\n";}
+ return $return_flag;
+}
+
+sub clean_query_string {
+ my $str = shift;
+
+ $str =~ s/(?!_)[[:punct:]]//g; #remove punct except underscores
+ $str =~ s/\n//g;
+ $str =~ s/\r//g;
+ return $str;
+}
+
+sub print_query {
+ my $fh = shift;
+ my %report = @_;
+
+ my $display = $report{display};
+ my $height = $report{divheight};
+ my $width = $report{divwidth};
+ if (!defined $display or length $display == 0) { $display = 'table'; }
+ my $rname = $report{name};
+ my $query = $report{query};
+ my $title = $report{report_title};
+ my $sth = $dbh->prepare($query);
+ $sth->execute();
+
+ if ($height) { $height = $height . 'px'; }
+ if ($width) { $width = $width . 'px'; }
+ my $header_flag = 0;
+
+ #print asciidoc
+ if ($display eq 'table') {
+ 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 chart
+ if ($display eq 'pie_chart' or $display eq 'donut_chart') {
+ my @h = split(/\./,$report{heading});
+ my @l = split(/\./,$report{chart_labels});
+
+ print $fh "++++\n";
+ if (defined $height and defined $width) { print $fh "<div id=\"$rname\" style=\"width: $width; height: $height;\"></div>\n"; }
+ else { print $fh "<div id=\"$rname\"></div>\n"; }
+ print $fh "<script type=\"text/javascript\">\n";
+ print $fh "google.charts.load('current', {'packages':['corechart']});\n";
+ print $fh "google.charts.setOnLoadCallback(drawChart);\n";
+ print $fh "function drawChart() {\n";
+ print $fh " var data = google.visualization.arrayToDataTable([\n";
+ #loop through data here
+ print $fh "['$l[0]', '$l[1]' ],\n";
+ while (my @row = $sth->fetchrow_array) {
+ my $row_length = @row;
+ my $r = 1;
+ while ($r < $row_length) {
+ print $fh "['$h[$r-1]', $row[$r-1] ],\n";
+ $r++;
+ }
+ if ($r = $row_length) { print $fh "['$h[$r-1]', $row[$r-1] ]\n"; }
+ }
+ print $fh "]);\n";
+ if ($display eq 'pie_chart') { print $fh "var options = {'title':'$title'};\n"; }
+ if ($display eq 'donut_chart') { print $fh "var options = {'title':'$title', pieHole: 0.4};\n"; }
+ print $fh "var chart = new google.visualization.PieChart(document.getElementById('$rname'));\n";
+ print $fh "chart.draw(data, options);\n";
+ print $fh "}\n";
+ print $fh "</script>\n";
+ print $fh "++++\n";
+ }
+
+ print "successfully wrote output for $report{name}.\n\n";
+}
+
+sub give_column {
+ my $i = shift;
+ my $col = "";
+
+ do {
+ $col .= chr( ( $i % 26 ) + ord('A') );
+ $i = int( $i / 26 ) - 1;
+ } while ( $i >= 0 );
+
+ return scalar reverse $col;
+}
+
+sub abort {
+ my $msg = shift;
+ print STDERR "$0: $msg", "\n";
+ exit 1;
+}
+
+
projects / migration-tools.git / blobdiff