2 # -*- coding: iso-8859-15 -*-
3 ###############################################################################
6 =item B<reporter> --analyst "Analyst Name" --report_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 :
13 --added_page_title and --added_page_file
15 If one is used both must be. The added page file can be plain text or asciidoc. This
16 adds an extra arbitrary page of notes to the report. Mig assumes the page file is in the mig git directory.
20 This will define a set of tags to use, if not set it will default to Circs,
21 Holds, Actors, Bibs, Assets & Money.
25 Gives more information about what is happening. Defaults to off.
29 Allows you to override the default evergreen_staged_report.xml in the mig-xml folder.
31 --captions on OR --captions off
33 Adds the captions tag to asciidoc header to turn off captions in generated output.
40 ###############################################################################
49 HOME PGHOST PGPORT PGUSER PGDATABASE MIGSCHEMA
50 MIGBASEWORKDIR MIGBASEGITDIR MIGGITDIR MIGWORKDIR
58 my $mig_bin = "$FindBin::Bin/";
59 use lib "$FindBin::Bin/";
61 use open ':encoding(utf8)';
63 pod2usage(-verbose => 2) if defined $ARGV[0] && $ARGV[0] eq '--help';
64 pod2usage(-verbose => 1) if ! $ARGV[1];
66 my $analyst = 'Equinox Open Library Initiative';;
68 my $reports_xml = 'evergreen_staged_report.xml';
74 my $parser = XML::LibXML->new();
75 my $lines_per_page = 42;
81 'analyst:s' => \$analyst,
82 'report_title:s' => \$report_title,
83 'title:s' => \$report_title,
84 'reports_xml:s' => \$reports_xml,
86 'added_page_title:s' => \$added_page_title,
87 'added_page_file:s' => \$added_page_file,
88 'captions:s' => \$captions,
92 if (!defined $tags) {$tags = 'circs.holds.actors.bibs.assets.money.notices'};
93 if (!defined $report_title) { abort('--report_title or --title must be supplied'); }
94 if (!defined $analyst) { abort('--analyst must be supplied'); }
96 my $mig_path = abs_path($0);
97 $mig_path =~ s|[^/]+$||;
98 $reports_xml = find_xml($reports_xml,$mig_path);
99 if (!defined $reports_xml) { abort("Can not find xml reports file."); }
100 my $dom = $parser->parse_file($reports_xml);
102 if (defined $added_page_file or defined $added_page_title) {
103 abort('must specify --added_page_file and --added_page_title') unless defined $added_page_file and defined $added_page_title;
105 if (defined $added_page_file) { $added_page_file = $MIGGITDIR . $added_page_file; }
107 my $dbh = Mig::db_connect();
108 my $report_file = create_report_name($report_title);
109 $report_file = $MIGGITDIR . $report_file;
111 open($fh, '>', $report_file) or abort("Could not open output file $report_file!");
112 write_title_page($report_title,$fh,$analyst,$captions);
113 load_javascript($fh);
115 if (defined $added_page_file and defined $added_page_title) {
117 print $fh "== $added_page_title\n";
118 print "$added_page_file\t$added_page_title\n";
119 open(my $an,'<:encoding(UTF-8)', $added_page_file) or abort("Could not open $added_page_file!");
120 while ( my $line = <$an> ) {
127 foreach my $func ($dom->findnodes('//function')) {
128 my $fdrop = $func->findvalue('./drop');
129 my $fcreate = $func->findvalue('./create');
130 my $fname = $func->findvalue('./name');
131 my $sdrop = $dbh->prepare($fdrop);
132 my $screate = $dbh->prepare($fcreate);
133 print "dropping function $fname ... ";
135 print "creating function $fname\n\n";
139 foreach my $table ($dom->findnodes('//table')) {
140 my $tdrop = $table->findvalue('./drop');
141 my $tcreate = $table->findvalue('./create');
142 my $tname = $table->findvalue('./name');
143 my $sdrop = $dbh->prepare($tdrop);
144 my $screate = $dbh->prepare($tcreate);
145 print "dropping table $tname ... ";
147 print "creating table $tname\n\n";
152 my @report_tags = split(/\./,$tags);
153 foreach my $t (@report_tags) {
154 print "\n\n=========== Starting to process tag $t\n";
155 print "==========================================\n\n";
158 foreach my $asset ($dom->findnodes('//asset')) {
159 if (index($asset->findvalue('./tag'),$t) != -1) {
160 push @asset_files, $asset->findvalue('./file');
164 foreach my $fname (@asset_files) {
165 my $asset_path = $mig_path . '../mig-asc/' . $fname;
166 open my $a, $asset_path or abort("Could not open $fname.");
167 while ( my $l = <$a> ) {
173 print_section_header(ucfirst($t),$fh);
174 my $linecount = $lines_per_page;
178 foreach my $asset ($dom->findnodes('//asset')) {
179 if (index($asset->findvalue('./tag'),$t) != -1) {
180 push @asset_files, $asset->findvalue('./file');
185 foreach my $report ($dom->findnodes('//report')) {
186 if (index($report->findvalue('./tag'),$t) != -1 and $report->findvalue('./iteration') eq '0') {
187 push @report_names, $report->findvalue('./name');
191 #only has one level of failover now but could change to array of hashes and loops
192 #but this keeps it simple and in practice I haven't needed more than two
195 foreach my $rname (@report_names) {
201 if ($debug eq 'on') {print "\nchecking for $rname ... ";}
202 %report0 = find_report($dom,$t,$rname,'0',$debug);
203 $check_tables0 = check_table($report0{query},$MIGSCHEMA,$debug,$rname);
204 if ($check_tables0 == 1) { $r = print_query($fh,%report0); } else {
205 %report1 = find_report($dom,$t,$rname,'1',$debug);
206 if (defined $report1{query}) {
207 $check_tables1 = check_table($report1{query},$MIGSCHEMA,$debug,$rname);
208 if ($check_tables1 == 1) { $r = print_query($fh,%report1); }
217 foreach my $table ($dom->findnodes('//table')) {
218 my $tdrop = $table->findvalue('./drop');
219 my $tname = $table->findvalue('./name');
220 my $sdrop = $dbh->prepare($tdrop);
221 print "cleaning up table $tname ... \n";
227 ############ end of main logic
230 my $reports_xml = shift;
231 my $mig_path = shift;
233 if ($reports_xml =~ m/\//) { return $reports_xml; }
235 my $mig_test_file = $mig_path . '/../mig-xml/' . $reports_xml;
236 my $working_test_dir = getcwd();
237 my $working_test_file = $working_test_dir . '/' . $reports_xml;
239 if (-e $mig_test_file) { return $mig_test_file; }
240 if (-e $working_test_file) { return $working_test_file; }
249 my $iteration = shift;
253 if ($debug eq 'on') {print "iteration $iteration ";}
254 foreach my $node ($dom->findnodes('//report')) {
255 if ($node->findvalue('./tag') =~ $tag and $node->findvalue('./iteration') eq $iteration and $node->findvalue('./name') eq $name) {
256 if ($debug eq 'on') {print "succeeded ... \n";}
258 name => $node->findvalue('./name'),
259 report_title => $node->findvalue('./report_title'),
260 query => $node->findvalue('./query'),
261 heading => $node->findvalue('./heading'),
262 tag => $node->findvalue('./tag'),
263 iteration => $node->findvalue('./iteration'),
264 note => $node->findvalue('./note'),
265 display => $node->findvalue('./display'),
266 chart_labels => $node->findvalue('./chart_labels'),
267 divwidth => $node->findvalue('./divwidth'),
268 divheight => $node->findvalue('./divheight'),
273 if ($debug eq 'on') {print "failed ... \n";}
275 name => "eaten by grue"
279 sub print_section_header {
284 #$t =~ s/(\w+)/\u$1/g;;
286 print $fh "== $t Reports\n";
290 sub create_report_name {
293 my @abbr = qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec);
294 my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
296 my $date = $year . '_' . $abbr[$mon] . '_' . $mday;
298 $report_file = $rt . ' ' . $date . '.asciidoc';
299 $report_file =~ s/ /_/g;
303 sub write_title_page {
307 my $captions = shift;
309 my @abbr = qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec);
310 my $l = length($report_title);
311 my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
314 print $fh "$mday $abbr[$mon] $year\n";
316 #print $fh ":title-logo-image: image::eolilogosmall.png[pdfwidth=3in]\n";
318 if ($captions eq 'on') { print $fh ":caption:\n"; }
322 sub load_javascript {
326 print $fh "<script type=\"text/javascript\" src=\"https://www.gstatic.com/charts/loader.js\"></script>\n";
332 my $MIGSCHEMA = shift;
334 my $report_name = shift;
336 if ($debug eq 'on') {print "$query\n";}
340 my @qe = split(/ /,$query);
345 if ($qe[$i] eq 'FROM' or $qe[$i] eq 'JOIN') {
347 if ($qe[$q] ne '(SELECT') {
348 push @tables, $qe[$q];
353 if ($debug eq 'on') {print "checking tables ... ";}
356 foreach my $table (@tables) {
359 if (index($table,'.') != -1) {
360 $schema = (split /\./,$table)[0];
361 $table = (split /\./,$table)[1];
363 $table = clean_query_string($table);
364 if (defined $schema) {
365 $schema = clean_query_string($schema);
366 $sql = 'SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = \'' . $schema . '\' AND table_name = \'' . $table . '\');';
368 $sql = 'SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = \'' . $MIGSCHEMA . '\' AND table_name = \'' . $table . '\');';
370 my $sth = $dbh->prepare($sql);
372 while (my @row = $sth->fetchrow_array) {
373 if ($row[0] eq '1') {
377 if ($debug eq 'on') {print "detecting $table failed...\n";}
379 if ($row[0] eq '0') {$return_flag = 0;}
382 if ($return_flag == 1 and $debug eq 'on') {print "succeeded ...\n";}
383 if ($return_flag == 0) {print "! a table failed the find test for report $report_name\n\n";}
387 sub clean_query_string {
390 $str =~ s/(?!_)[[:punct:]]//g; #remove punct except underscores
400 my $display = $report{display};
401 my $height = $report{divheight};
402 my $width = $report{divwidth};
403 if (!defined $display) { $display = 'table'; }
404 my $rname = $report{name};
405 my $query = $report{query};
406 my $title = $report{report_title};
407 my $sth = $dbh->prepare($query);
410 if ($height) { $height = $height . 'px'; }
411 if ($width) { $width = $width . 'px'; }
415 if ($display eq 'table') {
416 while (my @row = $sth->fetchrow_array) {
417 if ($header_flag == 0) {
418 print $fh "\n.*$report{report_title}*\n";
420 my @h = split(/\./,$report{heading});
423 while ($h_count <= $h_length) {
424 print $fh "|*$h[$h_count-1]* ";
430 my $row_length = @row;
432 while ($r <= $row_length) {
433 if (! defined $row[$r-1] ) {
436 print $fh "|$row[$r-1] ";
441 if ($header_flag == 1) {
442 print $fh "|===\n\n";
443 print $fh $report{note};
449 if ($display eq 'pie_chart' or $display eq 'donut_chart') {
450 my @h = split(/\./,$report{heading});
451 my @l = split(/\./,$report{chart_labels});
454 if (defined $height and defined $width) { print $fh "<div id=\"$rname\" style=\"width: $width; height: $height;\"></div>\n"; }
455 else { print $fh "<div id=\"$rname\"></div>\n"; }
456 print $fh "<script type=\"text/javascript\">\n";
457 print $fh "google.charts.load('current', {'packages':['corechart']});\n";
458 print $fh "google.charts.setOnLoadCallback(drawChart);\n";
459 print $fh "function drawChart() {\n";
460 print $fh " var data = google.visualization.arrayToDataTable([\n";
461 #loop through data here
462 print $fh "['$l[0]', '$l[1]' ],\n";
463 while (my @row = $sth->fetchrow_array) {
464 my $row_length = @row;
466 while ($r < $row_length) {
467 print $fh "['$h[$r-1]', $row[$r-1] ],\n";
470 if ($r = $row_length) { print $fh "['$h[$r-1]', $row[$r-1] ]\n"; }
473 if ($display eq 'pie_chart') { print $fh "var options = {'title':'$title'};\n"; }
474 if ($display eq 'donut_chart') { print $fh "var options = {'title':'$title', pieHole: 0.4};\n"; }
475 print $fh "var chart = new google.visualization.PieChart(document.getElementById('$rname'));\n";
476 print $fh "chart.draw(data, options);\n";
478 print $fh "</script>\n";
482 print "successfully wrote output for $report{name}.\n\n";
490 $col .= chr( ( $i % 26 ) + ord('A') );
491 $i = int( $i / 26 ) - 1;
494 return scalar reverse $col;
499 print STDERR "$0: $msg", "\n";