2 # -*- coding: iso-8859-15 -*-
3 ###############################################################################
6 =item B<reporter> --title "Report Title"
8 Generates an asciidoc file in the git working directory that can be converted to
9 any appropriate format. The analyst and report parameters are required.
11 Optional parameters are :
15 Default to "Equinox Open Library Initiative"
17 --added_page_title and --added_page_file
19 If one is used both must be. The added page file can be plain text or asciidoc. This
20 adds an extra arbitrary page of notes to the report. Mig assumes the page file is in the mig git directory.
24 This will define a set of tags to use, if not set it will default to Circs,
25 Holds, Actors, Bibs, Assets & Money.
29 Gives more information about what is happening. Defaults to off.
33 Allows you to override the default evergreen_staged_report.xml in the mig-xml folder.
35 --captions on OR --captions off
37 Adds the captions tag to asciidoc header to turn off captions in generated output.
44 ###############################################################################
53 HOME PGHOST PGPORT PGUSER PGDATABASE MIGSCHEMA
54 MIGBASEWORKDIR MIGBASEGITDIR MIGGITDIR MIGWORKDIR
62 my $mig_bin = "$FindBin::Bin/";
63 use lib "$FindBin::Bin/";
65 use open ':encoding(utf8)';
67 pod2usage(-verbose => 2) if defined $ARGV[0] && $ARGV[0] eq '--help';
68 pod2usage(-verbose => 1) if ! $ARGV[1];
70 my $analyst = 'Equinox Open Library Initiative';;
72 my $reports_xml = 'evergreen_staged_report.xml';
78 my $parser = XML::LibXML->new();
79 my $lines_per_page = 42;
85 'analyst:s' => \$analyst,
86 'report_title:s' => \$report_title,
87 'title:s' => \$report_title,
88 'reports_xml:s' => \$reports_xml,
90 'added_page_title:s' => \$added_page_title,
91 'added_page_file:s' => \$added_page_file,
92 'captions:s' => \$captions,
96 if (!defined $tags) {$tags = 'circs.holds.actors.bibs.assets.money.notices'};
97 if (!defined $report_title) { abort('--report_title or --title must be supplied'); }
98 if (!defined $analyst) { abort('--analyst must be supplied'); }
100 my $mig_path = abs_path($0);
101 $mig_path =~ s|[^/]+$||;
102 $reports_xml = find_xml($reports_xml,$mig_path);
103 if (!defined $reports_xml) { abort("Can not find xml reports file."); }
104 my $dom = $parser->parse_file($reports_xml);
106 if (defined $added_page_file or defined $added_page_title) {
107 abort('must specify --added_page_file and --added_page_title') unless defined $added_page_file and defined $added_page_title;
109 if (defined $added_page_file) { $added_page_file = $MIGGITDIR . $added_page_file; }
111 my $dbh = Mig::db_connect();
112 my $report_file = create_report_name($report_title);
113 $report_file = $MIGGITDIR . $report_file;
115 open($fh, '>', $report_file) or abort("Could not open output file $report_file!");
116 write_title_page($report_title,$fh,$analyst,$captions);
117 load_javascript($fh);
119 if (defined $added_page_file and defined $added_page_title) {
121 print $fh "== $added_page_title\n";
122 print "$added_page_file\t$added_page_title\n";
123 open(my $an,'<:encoding(UTF-8)', $added_page_file) or abort("Could not open $added_page_file!");
124 while ( my $line = <$an> ) {
131 foreach my $func ($dom->findnodes('//function')) {
132 my $fdrop = $func->findvalue('./drop');
133 my $fcreate = $func->findvalue('./create');
134 my $fname = $func->findvalue('./name');
135 my $sdrop = $dbh->prepare($fdrop);
136 my $screate = $dbh->prepare($fcreate);
137 print "dropping function $fname ... ";
139 print "creating function $fname\n\n";
143 foreach my $table ($dom->findnodes('//table')) {
144 my $tdrop = $table->findvalue('./drop');
145 my $tcreate = $table->findvalue('./create');
146 my $tname = $table->findvalue('./name');
147 my $sdrop = $dbh->prepare($tdrop);
148 my $screate = $dbh->prepare($tcreate);
149 print "dropping table $tname ... ";
151 print "creating table $tname\n\n";
156 my @report_tags = split(/\./,$tags);
157 foreach my $t (@report_tags) {
158 print "\n\n=========== Starting to process tag $t\n";
159 print "==========================================\n\n";
162 foreach my $asset ($dom->findnodes('//asset')) {
163 if (index($asset->findvalue('./tag'),$t) != -1) {
164 push @asset_files, $asset->findvalue('./file');
168 foreach my $fname (@asset_files) {
169 my $asset_path = $mig_path . '../mig-asc/' . $fname;
170 open my $a, $asset_path or abort("Could not open $fname.");
171 while ( my $l = <$a> ) {
177 print_section_header(ucfirst($t),$fh);
178 my $linecount = $lines_per_page;
182 foreach my $asset ($dom->findnodes('//asset')) {
183 if (index($asset->findvalue('./tag'),$t) != -1) {
184 push @asset_files, $asset->findvalue('./file');
189 foreach my $report ($dom->findnodes('//report')) {
190 if (index($report->findvalue('./tag'),$t) != -1 and $report->findvalue('./iteration') eq '0') {
191 push @report_names, $report->findvalue('./name');
195 #only has one level of failover now but could change to array of hashes and loops
196 #but this keeps it simple and in practice I haven't needed more than two
199 foreach my $rname (@report_names) {
205 if ($debug eq 'on') {print "\nchecking for $rname ... ";}
206 %report0 = find_report($dom,$t,$rname,'0',$debug);
207 $check_tables0 = check_table($report0{query},$MIGSCHEMA,$debug,$rname);
208 if ($check_tables0 == 1) { $r = print_query($fh,%report0); } else {
209 %report1 = find_report($dom,$t,$rname,'1',$debug);
210 if (defined $report1{query}) {
211 $check_tables1 = check_table($report1{query},$MIGSCHEMA,$debug,$rname);
212 if ($check_tables1 == 1) { $r = print_query($fh,%report1); }
221 foreach my $table ($dom->findnodes('//table')) {
222 my $tdrop = $table->findvalue('./drop');
223 my $tname = $table->findvalue('./name');
224 my $sdrop = $dbh->prepare($tdrop);
225 print "cleaning up table $tname ... \n";
231 ############ end of main logic
234 my $reports_xml = shift;
235 my $mig_path = shift;
237 if ($reports_xml =~ m/\//) { return $reports_xml; }
239 my $mig_test_file = $mig_path . '/../mig-xml/' . $reports_xml;
240 my $working_test_dir = getcwd();
241 my $working_test_file = $working_test_dir . '/' . $reports_xml;
243 if (-e $mig_test_file) { return $mig_test_file; }
244 if (-e $working_test_file) { return $working_test_file; }
253 my $iteration = shift;
257 if ($debug eq 'on') {print "iteration $iteration ";}
258 foreach my $node ($dom->findnodes('//report')) {
259 if ($node->findvalue('./tag') =~ $tag and $node->findvalue('./iteration') eq $iteration and $node->findvalue('./name') eq $name) {
260 if ($debug eq 'on') {print "succeeded ... \n";}
262 name => $node->findvalue('./name'),
263 report_title => $node->findvalue('./report_title'),
264 query => $node->findvalue('./query'),
265 heading => $node->findvalue('./heading'),
266 tag => $node->findvalue('./tag'),
267 iteration => $node->findvalue('./iteration'),
268 note => $node->findvalue('./note'),
269 display => $node->findvalue('./display'),
270 chart_labels => $node->findvalue('./chart_labels'),
271 divwidth => $node->findvalue('./divwidth'),
272 divheight => $node->findvalue('./divheight'),
277 if ($debug eq 'on') {print "failed ... \n";}
279 name => "eaten by grue"
283 sub print_section_header {
288 #$t =~ s/(\w+)/\u$1/g;;
290 print $fh "== $t Reports\n";
294 sub create_report_name {
297 my @abbr = qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec);
298 my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
300 my $date = $year . '_' . $abbr[$mon] . '_' . $mday;
302 $report_file = $rt . ' ' . $date . '.asciidoc';
303 $report_file =~ s/ /_/g;
307 sub write_title_page {
311 my $captions = shift;
313 my @abbr = qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec);
314 my $l = length($report_title);
315 my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
318 print $fh "$mday $abbr[$mon] $year\n";
320 #print $fh ":title-logo-image: image::eolilogosmall.png[pdfwidth=3in]\n";
322 if ($captions eq 'on') { print $fh ":caption:\n"; }
326 sub load_javascript {
330 print $fh "<script type=\"text/javascript\" src=\"https://www.gstatic.com/charts/loader.js\"></script>\n";
336 my $MIGSCHEMA = shift;
338 my $report_name = shift;
340 if ($debug eq 'on') {print "$query\n";}
344 my @qe = split(/ /,$query);
349 if ($qe[$i] eq 'FROM' or $qe[$i] eq 'JOIN') {
351 if ($qe[$q] ne '(SELECT') {
352 push @tables, $qe[$q];
357 if ($debug eq 'on') {print "checking tables ... ";}
360 foreach my $table (@tables) {
363 if (index($table,'.') != -1) {
364 $schema = (split /\./,$table)[0];
365 $table = (split /\./,$table)[1];
367 $table = clean_query_string($table);
368 if (defined $schema) {
369 $schema = clean_query_string($schema);
370 $sql = 'SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = \'' . $schema . '\' AND table_name = \'' . $table . '\');';
372 $sql = 'SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = \'' . $MIGSCHEMA . '\' AND table_name = \'' . $table . '\');';
374 my $sth = $dbh->prepare($sql);
376 while (my @row = $sth->fetchrow_array) {
377 if ($row[0] eq '1') {
381 if ($debug eq 'on') {print "detecting $table failed...\n";}
383 if ($row[0] eq '0') {$return_flag = 0;}
386 if ($return_flag == 1 and $debug eq 'on') {print "succeeded ...\n";}
387 if ($return_flag == 0) {print "! a table failed the find test for report $report_name\n\n";}
391 sub clean_query_string {
394 $str =~ s/(?!_)[[:punct:]]//g; #remove punct except underscores
404 my $display = $report{display};
405 my $height = $report{divheight};
406 my $width = $report{divwidth};
407 if (!defined $display or length $display == 0) { $display = 'table'; }
408 my $rname = $report{name};
409 my $query = $report{query};
410 my $title = $report{report_title};
411 my $sth = $dbh->prepare($query);
414 if ($height) { $height = $height . 'px'; }
415 if ($width) { $width = $width . 'px'; }
419 if ($display eq 'table') {
420 while (my @row = $sth->fetchrow_array) {
421 if ($header_flag == 0) {
422 print $fh "\n.*$report{report_title}*\n";
424 my @h = split(/\./,$report{heading});
427 while ($h_count <= $h_length) {
428 print $fh "|*$h[$h_count-1]* ";
434 my $row_length = @row;
436 while ($r <= $row_length) {
437 if (! defined $row[$r-1] ) {
440 print $fh "|$row[$r-1] ";
445 if ($header_flag == 1) {
446 print $fh "|===\n\n";
447 print $fh $report{note};
453 if ($display eq 'pie_chart' or $display eq 'donut_chart') {
454 my @h = split(/\./,$report{heading});
455 my @l = split(/\./,$report{chart_labels});
458 if (defined $height and defined $width) { print $fh "<div id=\"$rname\" style=\"width: $width; height: $height;\"></div>\n"; }
459 else { print $fh "<div id=\"$rname\"></div>\n"; }
460 print $fh "<script type=\"text/javascript\">\n";
461 print $fh "google.charts.load('current', {'packages':['corechart']});\n";
462 print $fh "google.charts.setOnLoadCallback(drawChart);\n";
463 print $fh "function drawChart() {\n";
464 print $fh " var data = google.visualization.arrayToDataTable([\n";
465 #loop through data here
466 print $fh "['$l[0]', '$l[1]' ],\n";
467 while (my @row = $sth->fetchrow_array) {
468 my $row_length = @row;
470 while ($r < $row_length) {
471 print $fh "['$h[$r-1]', $row[$r-1] ],\n";
474 if ($r = $row_length) { print $fh "['$h[$r-1]', $row[$r-1] ]\n"; }
477 if ($display eq 'pie_chart') { print $fh "var options = {'title':'$title'};\n"; }
478 if ($display eq 'donut_chart') { print $fh "var options = {'title':'$title', pieHole: 0.4};\n"; }
479 print $fh "var chart = new google.visualization.PieChart(document.getElementById('$rname'));\n";
480 print $fh "chart.draw(data, options);\n";
482 print $fh "</script>\n";
486 print "successfully wrote output for $report{name}.\n\n";
494 $col .= chr( ( $i % 26 ) + ord('A') );
495 $i = int( $i / 26 ) - 1;
498 return scalar reverse $col;
503 print STDERR "$0: $msg", "\n";