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'),
271 if ($debug eq 'on') {print "failed ... \n";}
273 name => "eaten by grue"
277 sub print_section_header {
282 #$t =~ s/(\w+)/\u$1/g;;
284 print $fh "== $t Reports\n";
288 sub create_report_name {
291 my @abbr = qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec);
292 my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
294 my $date = $year . '_' . $abbr[$mon] . '_' . $mday;
296 $report_file = $rt . ' ' . $date . '.asciidoc';
297 $report_file =~ s/ /_/g;
301 sub write_title_page {
305 my $captions = shift;
307 my @abbr = qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec);
308 my $l = length($report_title);
309 my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
312 print $fh "$mday $abbr[$mon] $year\n";
314 #print $fh ":title-logo-image: image::eolilogosmall.png[pdfwidth=3in]\n";
316 if ($captions eq 'on') { print $fh ":caption:\n"; }
320 sub load_javascript {
324 print $fh "<script type=\"text/javascript\" src=\"https://www.gstatic.com/charts/loader.js\"></script>\n";
330 my $MIGSCHEMA = shift;
332 my $report_name = shift;
334 if ($debug eq 'on') {print "$query\n";}
338 my @qe = split(/ /,$query);
343 if ($qe[$i] eq 'FROM' or $qe[$i] eq 'JOIN') {
345 if ($qe[$q] ne '(SELECT') {
346 push @tables, $qe[$q];
351 if ($debug eq 'on') {print "checking tables ... ";}
354 foreach my $table (@tables) {
357 if (index($table,'.') != -1) {
358 $schema = (split /\./,$table)[0];
359 $table = (split /\./,$table)[1];
361 $table = clean_query_string($table);
362 if (defined $schema) {
363 $schema = clean_query_string($schema);
364 $sql = 'SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = \'' . $schema . '\' AND table_name = \'' . $table . '\');';
366 $sql = 'SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = \'' . $MIGSCHEMA . '\' AND table_name = \'' . $table . '\');';
368 my $sth = $dbh->prepare($sql);
370 while (my @row = $sth->fetchrow_array) {
371 if ($row[0] eq '1') {
375 if ($debug eq 'on') {print "detecting $table failed...\n";}
377 if ($row[0] eq '0') {$return_flag = 0;}
380 if ($return_flag == 1 and $debug eq 'on') {print "succeeded ...\n";}
381 if ($return_flag == 0) {print "! a table failed the find test for report $report_name\n\n";}
385 sub clean_query_string {
388 $str =~ s/(?!_)[[:punct:]]//g; #remove punct except underscores
398 my $display = $report{display};
399 if (!defined $display) { $display = 'table'; }
400 my $rname = $report{name};
401 my $query = $report{query};
402 my $title = $report{report_title};
403 my $sth = $dbh->prepare($query);
409 if ($display eq 'table') {
410 while (my @row = $sth->fetchrow_array) {
411 if ($header_flag == 0) {
412 print $fh "\n.*$report{report_title}*\n";
414 my @h = split(/\./,$report{heading});
417 while ($h_count <= $h_length) {
418 print $fh "|*$h[$h_count-1]* ";
424 my $row_length = @row;
426 while ($r <= $row_length) {
427 if (! defined $row[$r-1] ) {
430 print $fh "|$row[$r-1] ";
435 if ($header_flag == 1) {
436 print $fh "|===\n\n";
437 print $fh $report{note};
443 if ($display eq 'pie_chart') {
444 my @h = split(/\./,$report{heading});
445 my @l = split(/\./,$report{chart_labels});
448 print $fh "<div id=\"$rname\"></div>\n";
449 print $fh "<script type=\"text/javascript\">\n";
450 print $fh "google.charts.load('current', {'packages':['corechart']});\n";
451 print $fh "google.charts.setOnLoadCallback(drawChart);\n";
452 print $fh "function drawChart() {\n";
453 print $fh " var data = google.visualization.arrayToDataTable([\n";
454 #loop through data here
455 print $fh "['$l[0]', '$l[1]' ],\n";
456 while (my @row = $sth->fetchrow_array) {
457 my $row_length = @row;
459 while ($r < $row_length) {
460 print $fh "['$h[$r-1]', $row[$r-1] ],\n";
463 if ($r = $row_length) { print $fh "['$h[$r-1]', $row[$r-1] ]\n"; }
466 print $fh "var options = {'title':'$title'};\n";
467 print $fh "var chart = new google.visualization.PieChart(document.getElementById('$rname'));\n";
468 print $fh "chart.draw(data, options);\n";
470 print $fh "</script>\n";
474 print "successfully wrote output for $report{name}.\n\n";
482 $col .= chr( ( $i % 26 ) + ord('A') );
483 $i = int( $i / 26 ) - 1;
486 return scalar reverse $col;
491 print STDERR "$0: $msg", "\n";