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 Defaults to "Project Data Analyst"
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 = 'Project Data Analyst';
72 my $reports_xml = 'evergreen_staged_report.xml';
73 my $tags = 'circs.holds.actors.bibs.assets.money.notices';
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 $report_title) { abort('--report_title or --title must be supplied'); }
98 my $mig_path = abs_path($0);
99 $mig_path =~ s|[^/]+$||;
100 $reports_xml = find_xml($reports_xml,$mig_path);
101 if (!defined $reports_xml) { abort("Can not find xml reports file."); }
102 my $dom = $parser->parse_file($reports_xml);
104 if (defined $added_page_file or defined $added_page_title) {
105 abort('must specify --added_page_file and --added_page_title') unless defined $added_page_file and defined $added_page_title;
107 if (defined $added_page_file) { $added_page_file = $MIGGITDIR . $added_page_file; }
109 my $dbh = EMig::db_connect();
110 my $report_file = create_report_name($report_title);
111 $report_file = $MIGGITDIR . $report_file;
113 open($fh, '>', $report_file) or abort("Could not open output file $report_file!");
114 write_title_page($report_title,$fh,$analyst,$captions);
115 load_javascript($fh);
117 if (defined $added_page_file and defined $added_page_title) {
119 print $fh "== $added_page_title\n";
120 print "$added_page_file\t$added_page_title\n";
121 open(my $an,'<:encoding(UTF-8)', $added_page_file) or abort("Could not open $added_page_file!");
122 while ( my $line = <$an> ) {
129 foreach my $func ($dom->findnodes('//function')) {
130 my $fdrop = $func->findvalue('./drop');
131 my $fcreate = $func->findvalue('./create');
132 my $fname = $func->findvalue('./name');
133 my $sdrop = $dbh->prepare($fdrop);
134 my $screate = $dbh->prepare($fcreate);
135 print "dropping function $fname ... ";
137 print "creating function $fname\n\n";
141 foreach my $table ($dom->findnodes('//table')) {
142 my $tdrop = $table->findvalue('./drop');
143 my $tcreate = $table->findvalue('./create');
144 my $tname = $table->findvalue('./name');
145 my $sdrop = $dbh->prepare($tdrop);
146 my $screate = $dbh->prepare($tcreate);
147 print "dropping table $tname ... ";
149 print "creating table $tname\n\n";
154 my @report_tags = split(/\./,$tags);
155 foreach my $t (@report_tags) {
156 print "\n\n=========== Starting to process tag $t\n";
157 print "==========================================\n\n";
160 foreach my $asset ($dom->findnodes('//asset')) {
161 if (index($asset->findvalue('./tag'),$t) != -1) {
162 push @asset_files, $asset->findvalue('./file');
166 foreach my $fname (@asset_files) {
167 my $asset_path = $mig_path . '../asc/' . $fname;
168 open my $a, $asset_path or abort("Could not open $fname.");
169 while ( my $l = <$a> ) {
175 print_section_header(ucfirst($t),$fh);
176 my $linecount = $lines_per_page;
180 foreach my $asset ($dom->findnodes('//asset')) {
181 if (index($asset->findvalue('./tag'),$t) != -1) {
182 push @asset_files, $asset->findvalue('./file');
187 foreach my $report ($dom->findnodes('//report')) {
188 if (index($report->findvalue('./tag'),$t) != -1 and $report->findvalue('./iteration') eq '0') {
189 push @report_names, $report->findvalue('./name');
193 #only has one level of failover now but could change to array of hashes and loops
194 #but this keeps it simple and in practice I haven't needed more than two
197 foreach my $rname (@report_names) {
203 if ($debug eq 'on') {print "\nchecking for $rname ... ";}
204 %report0 = find_report($dom,$t,$rname,'0',$debug);
205 $check_tables0 = check_table($report0{query},$MIGSCHEMA,$debug,$rname);
206 if ($check_tables0 == 1) { $r = print_query($fh,%report0); } else {
207 %report1 = find_report($dom,$t,$rname,'1',$debug);
208 if (defined $report1{query}) {
209 $check_tables1 = check_table($report1{query},$MIGSCHEMA,$debug,$rname);
210 if ($check_tables1 == 1) { $r = print_query($fh,%report1); }
219 foreach my $table ($dom->findnodes('//table')) {
220 my $tdrop = $table->findvalue('./drop');
221 my $tname = $table->findvalue('./name');
222 my $sdrop = $dbh->prepare($tdrop);
223 print "cleaning up table $tname ... \n";
229 ############ end of main logic
232 my $reports_xml = shift;
233 my $mig_path = shift;
235 if ($reports_xml =~ m/\//) { return $reports_xml; }
237 my $mig_test_file = $mig_path . '/../xml/' . $reports_xml;
238 my $working_test_dir = getcwd();
239 my $working_test_file = $working_test_dir . '/' . $reports_xml;
241 if (-e $mig_test_file) { return $mig_test_file; }
242 if (-e $working_test_file) { return $working_test_file; }
251 my $iteration = shift;
255 if ($debug eq 'on') {print "iteration $iteration ";}
256 foreach my $node ($dom->findnodes('//report')) {
257 if ($node->findvalue('./tag') =~ $tag and $node->findvalue('./iteration') eq $iteration and $node->findvalue('./name') eq $name) {
258 if ($debug eq 'on') {print "succeeded ... \n";}
260 name => $node->findvalue('./name'),
261 report_title => $node->findvalue('./report_title'),
262 query => $node->findvalue('./query'),
263 heading => $node->findvalue('./heading'),
264 tag => $node->findvalue('./tag'),
265 iteration => $node->findvalue('./iteration'),
266 note => $node->findvalue('./note'),
267 display => $node->findvalue('./display'),
268 chart_labels => $node->findvalue('./chart_labels'),
269 divwidth => $node->findvalue('./divwidth'),
270 divheight => $node->findvalue('./divheight'),
275 if ($debug eq 'on') {print "failed ... \n";}
277 name => "eaten by grue"
281 sub print_section_header {
286 #$t =~ s/(\w+)/\u$1/g;;
288 print $fh "== $t Reports\n";
292 sub create_report_name {
295 my @abbr = qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec);
296 my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
298 my $date = $year . '_' . $abbr[$mon] . '_' . $mday;
300 $report_file = $rt . ' ' . $date . '.asciidoc';
301 $report_file =~ s/ /_/g;
305 sub write_title_page {
309 my $captions = shift;
311 my @abbr = qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec);
312 my $l = length($report_title);
313 my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
316 print $fh "$mday $abbr[$mon] $year\n";
318 #print $fh ":title-logo-image: image::eolilogosmall.png[pdfwidth=3in]\n";
320 if ($captions eq 'on') { print $fh ":caption:\n"; }
324 sub load_javascript {
328 print $fh "<script type=\"text/javascript\" src=\"https://www.gstatic.com/charts/loader.js\"></script>\n";
334 my $MIGSCHEMA = shift;
336 my $report_name = shift;
338 if ($debug eq 'on') {print "$query\n";}
342 my @qe = split(/ /,$query);
347 if ($qe[$i] eq 'FROM' or $qe[$i] eq 'JOIN') {
349 if ($qe[$q] ne '(SELECT') {
350 push @tables, $qe[$q];
355 if ($debug eq 'on') {print "checking tables ... ";}
358 foreach my $table (@tables) {
361 if (index($table,'.') != -1) {
362 $schema = (split /\./,$table)[0];
363 $table = (split /\./,$table)[1];
365 $table = clean_query_string($table);
366 if (defined $schema) {
367 $schema = clean_query_string($schema);
368 $sql = 'SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = \'' . $schema . '\' AND table_name = \'' . $table . '\');';
370 $sql = 'SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = \'' . $MIGSCHEMA . '\' AND table_name = \'' . $table . '\');';
372 my $sth = $dbh->prepare($sql);
374 while (my @row = $sth->fetchrow_array) {
375 if ($row[0] eq '1') {
379 if ($debug eq 'on') {print "detecting $table failed...\n";}
381 if ($row[0] eq '0') {$return_flag = 0;}
384 if ($return_flag == 1 and $debug eq 'on') {print "succeeded ...\n";}
385 if ($return_flag == 0) {print "! a table failed the find test for report $report_name\n\n";}
389 sub clean_query_string {
392 $str =~ s/(?!_)[[:punct:]]//g; #remove punct except underscores
402 my $display = $report{display};
403 my $height = $report{divheight};
404 my $width = $report{divwidth};
405 if (!defined $display or length $display == 0) { $display = 'table'; }
406 my $rname = $report{name};
407 my $query = $report{query};
408 my $title = $report{report_title};
409 my $sth = $dbh->prepare($query);
412 if ($height) { $height = $height . 'px'; }
413 if ($width) { $width = $width . 'px'; }
417 if ($display eq 'table') {
418 while (my @row = $sth->fetchrow_array) {
419 if ($header_flag == 0) {
420 print $fh "\n.*$report{report_title}*\n";
422 my @h = split(/\./,$report{heading});
425 while ($h_count <= $h_length) {
426 print $fh "|*$h[$h_count-1]* ";
432 my $row_length = @row;
434 while ($r <= $row_length) {
435 if (! defined $row[$r-1] ) {
438 print $fh "|$row[$r-1] ";
443 if ($header_flag == 1) {
444 print $fh "|===\n\n";
445 print $fh $report{note};
451 if ($display eq 'pie_chart' or $display eq 'donut_chart') {
452 my @h = split(/\./,$report{heading});
453 my @l = split(/\./,$report{chart_labels});
456 if (defined $height and defined $width) { print $fh "<div id=\"$rname\" style=\"width: $width; height: $height;\"></div>\n"; }
457 else { print $fh "<div id=\"$rname\"></div>\n"; }
458 print $fh "<script type=\"text/javascript\">\n";
459 print $fh "google.charts.load('current', {'packages':['corechart']});\n";
460 print $fh "google.charts.setOnLoadCallback(drawChart);\n";
461 print $fh "function drawChart() {\n";
462 print $fh " var data = google.visualization.arrayToDataTable([\n";
463 #loop through data here
464 print $fh "['$l[0]', '$l[1]' ],\n";
465 while (my @row = $sth->fetchrow_array) {
466 my $row_length = @row;
468 while ($r < $row_length) {
469 print $fh "['$h[$r-1]', $row[$r-1] ],\n";
472 if ($r = $row_length) { print $fh "['$h[$r-1]', $row[$r-1] ]\n"; }
475 if ($display eq 'pie_chart') { print $fh "var options = {'title':'$title'};\n"; }
476 if ($display eq 'donut_chart') { print $fh "var options = {'title':'$title', pieHole: 0.4};\n"; }
477 print $fh "var chart = new google.visualization.PieChart(document.getElementById('$rname'));\n";
478 print $fh "chart.draw(data, options);\n";
480 print $fh "</script>\n";
484 print "successfully wrote output for $report{name}.\n\n";
492 $col .= chr( ( $i % 26 ) + ord('A') );
493 $i = int( $i / 26 ) - 1;
496 return scalar reverse $col;
501 print STDERR "$0: $msg", "\n";