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.
29 Allows you to override the default evergreen_staged_report.xml in the mig-xml folder.
31 --excel_output or --excel
33 Pushes output to an Excel file instead of asciidoc file.
35 --captions or --captions_off
37 Adds the captions tag to asciidoc header to turn off captions in generated output.
43 ###############################################################################
52 HOME PGHOST PGPORT PGUSER PGDATABASE MIGSCHEMA
53 MIGBASEWORKDIR MIGBASEGITDIR MIGGITDIR MIGWORKDIR
55 use Excel::Writer::XLSX;
61 my $mig_bin = "$FindBin::Bin/";
62 use lib "$FindBin::Bin/";
64 use open ':encoding(utf8)';
66 pod2usage(-verbose => 2) if defined $ARGV[0] && $ARGV[0] eq '--help';
67 pod2usage(-verbose => 1) if ! $ARGV[1];
70 my $next_arg_is_analyst;
72 my $next_arg_is_report_title;
74 my $next_arg_is_reports_xml;
78 my $next_arg_is_added_page_title;
80 my $next_arg_is_added_page_file;
84 my $parser = XML::LibXML->new();
85 my $lines_per_page = 42;
90 foreach my $arg (@ARGV) {
91 if ($arg eq '--report_title') {
92 $next_arg_is_report_title = 1;
95 if ($next_arg_is_report_title) {
97 $next_arg_is_report_title = 0;
100 if ($arg eq '--analyst') {
101 $next_arg_is_analyst = 1;
104 if ($next_arg_is_analyst) {
106 $next_arg_is_analyst = 0;
109 if ($arg eq '--reports_xml') {
110 $next_arg_is_reports_xml = 1;
113 if ($next_arg_is_reports_xml) {
115 $next_arg_is_reports_xml = 0;
118 if ($arg eq '--tags') {
119 $next_arg_is_tags = 1;
122 if ($next_arg_is_tags) {
124 $next_arg_is_tags = 0;
127 if ($arg eq '--added_page_title') {
128 $next_arg_is_added_page_title = 1;
131 if ($next_arg_is_added_page_title) {
132 $added_page_title = $arg;
133 $next_arg_is_added_page_title = 0;
136 if ($arg eq '--added_page_file') {
137 $next_arg_is_added_page_file = 1;
140 if ($next_arg_is_added_page_file) {
141 $added_page_file = $arg;
142 $next_arg_is_added_page_file = 0;
145 if ($arg eq '--excel_output' or $arg eq '--excel') {
149 if ($arg eq '--captions_off' or $arg eq '--captions') {
153 if ($arg eq '--debug') {
159 if (!defined $tags) {$tags = 'circs.holds.actors.bibs.assets.money.notices'};
160 if (!defined $report_title) { abort('--report_title must be supplied'); }
161 if ($excel_output == 0 and !defined $analyst) { abort('--analyst must be supplied'); }
163 my $mig_path = abs_path($0);
164 $mig_path =~ s|[^/]+$||;
165 $reports_xml = find_xml($reports_xml,$mig_path,$excel_output);
166 if (!defined $reports_xml) { abort("Can not find xml reports file."); }
167 my $dom = $parser->parse_file($reports_xml);
169 if (defined $added_page_file or defined $added_page_title) {
170 abort('must specify --added_page_file and --added_page_title') unless defined $added_page_file and defined $added_page_title;
172 if (defined $added_page_file) { $added_page_file = $MIGGITDIR . $added_page_file; }
174 my $dbh = Mig::db_connect();
175 my $report_file = create_report_name($report_title,$excel_output);
176 $report_file = $MIGGITDIR . $report_file;
178 if ($excel_output == 1) {
179 $workbook = Excel::Writer::XLSX->new( $report_file );
181 open($fh, '>', $report_file) or abort("Could not open output file!");
182 write_title_page($report_title,$fh,$analyst,$captions_off);
185 if (defined $added_page_file and defined $added_page_title) {
187 print $fh "== $added_page_title\n";
188 print "$added_page_file\t$added_page_title\n";
189 open(my $an,'<:encoding(UTF-8)', $added_page_file) or abort("Could not open $added_page_file!");
190 while ( my $line = <$an> ) {
197 foreach my $func ($dom->findnodes('//function')) {
198 my $fdrop = $func->findvalue('./drop');
199 my $fcreate = $func->findvalue('./create');
200 my $fname = $func->findvalue('./name');
201 my $sdrop = $dbh->prepare($fdrop);
202 my $screate = $dbh->prepare($fcreate);
203 print "dropping function $fname ... ";
205 print "creating function $fname\n\n";
210 my @report_tags = split(/\./,$tags);
211 foreach my $t (@report_tags) {
212 print "\n\n=========== Starting to process tag $t\n";
213 print "==========================================\n\n";
216 foreach my $asset ($dom->findnodes('//asset')) {
217 if (index($asset->findvalue('./tag'),$t) != -1) {
218 push @asset_files, $asset->findvalue('./file');
222 foreach my $fname (@asset_files) {
223 my $asset_path = $mig_path . '../mig-asc/' . $fname;
224 open my $a, $asset_path or abort("Could not open $fname.");
225 while ( my $l = <$a> ) {
231 if ($excel_output == 0) { print_section_header(ucfirst($t),$fh); }
232 my $linecount = $lines_per_page;
236 foreach my $asset ($dom->findnodes('//asset')) {
237 if (index($asset->findvalue('./tag'),$t) != -1) {
238 push @asset_files, $asset->findvalue('./file');
243 foreach my $report ($dom->findnodes('//report')) {
244 if (index($report->findvalue('./tag'),$t) != -1 and $report->findvalue('./iteration') eq '0') {
245 push @report_names, $report->findvalue('./name');
246 if ($excel_output == 1) { print_query_to_excel($workbook,$report); }
250 #only has one level of failover now but could change to array of hashes and loops
251 #but this keeps it simple and in practice I haven't needed more than two
254 if ($excel_output == 0) {
255 foreach my $rname (@report_names) {
261 if ($debug_flag == 1) {print "\nchecking for $rname ... ";}
262 %report0 = find_report($dom,$t,$rname,'0',$debug_flag);
263 $check_tables0 = check_table($report0{query},$MIGSCHEMA,$debug_flag,$rname);
264 if ($check_tables0 == 1) { $r = print_query($fh,%report0); } else {
265 %report1 = find_report($dom,$t,$rname,'1',$debug_flag);
266 if (defined $report1{query}) {
267 $check_tables1 = check_table($report1{query},$MIGSCHEMA,$debug_flag,$rname);
268 if ($check_tables1 == 1) { $r = print_query($fh,%report1); }
277 if ($excel_output eq 1) { $workbook->close(); }
280 ############ end of main logic
283 my $reports_xml = shift;
284 my $mig_path = shift;
285 my $excel_output = shift;
287 if (!defined $reports_xml) {
288 if ($excel_output == 0) { $reports_xml = $mig_path . '../mig-xml/evergreen_staged_report.xml'; return $reports_xml; }
289 else { $reports_xml = $mig_path . '../mig-xml/excel_mapping_reports.xml'; return $reports_xml; }
292 if ($reports_xml =~ m/\//) { return $reports_xml; }
294 my $mig_test_file = $mig_path . '/../mig-xml/' . $reports_xml;
295 my $working_test_dir = getcwd();
296 my $working_test_file = $working_test_dir . '/' . $reports_xml;
298 if (-e $mig_test_file) { return $mig_test_file; }
299 if (-e $working_test_file) { return $working_test_file; }
308 my $iteration = shift;
309 my $debug_flag = shift;
312 if ($debug_flag == 1) {print "iteration $iteration ";}
313 foreach my $node ($dom->findnodes('//report')) {
314 if ($node->findvalue('./tag') =~ $tag and $node->findvalue('./iteration') eq $iteration and $node->findvalue('./name') eq $name) {
315 if ($debug_flag == 1) {print "succeeded ... \n";}
317 name => $node->findvalue('./name'),
318 report_title => $node->findvalue('./report_title'),
319 query => $node->findvalue('./query'),
320 heading => $node->findvalue('./heading'),
321 tag => $node->findvalue('./tag'),
322 iteration => $node->findvalue('./iteration'),
323 note => $node->findvalue('./note'),
328 if ($debug_flag == 1) {print "failed ... \n";}
330 name => "eaten by grue"
334 sub print_section_header {
339 #$t =~ s/(\w+)/\u$1/g;;
341 print $fh "== $t Reports\n";
345 sub create_report_name {
347 my $excel_output = shift;
349 my @abbr = qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec);
350 my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
352 my $date = $year . '_' . $abbr[$mon] . '_' . $mday;
354 if ($excel_output == 0) { $report_file = $rt . ' ' . $date . '.asciidoc'; }
355 else { $report_file = $rt . ' ' . $date . '.xlsx'; }
356 $report_file =~ s/ /_/g;
360 sub write_title_page {
364 my $captions_off = shift;
366 my @abbr = qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec);
367 my $l = length($report_title);
368 my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
371 print $fh "$mday $abbr[$mon] $year\n";
373 #print $fh ":title-logo-image: image::eolilogosmall.png[pdfwidth=3in]\n";
375 if ($captions_off == 1) { print $fh ":caption:\n"; }
381 my $MIGSCHEMA = shift;
382 my $debug_flag = shift;
383 my $report_name = shift;
385 if ($debug_flag == 1) {print "$query\n";}
389 my @qe = split(/ /,$query);
394 if ($qe[$i] eq 'FROM' or $qe[$i] eq 'JOIN') {
396 if ($qe[$q] ne '(SELECT') {
397 push @tables, $qe[$q];
402 if ($debug_flag == 1) {print "checking tables ... ";}
405 foreach my $table (@tables) {
408 if (index($table,'.') != -1) {
409 $schema = (split /\./,$table)[0];
410 $table = (split /\./,$table)[1];
412 $table = clean_query_string($table);
413 if (defined $schema) {
414 $schema = clean_query_string($schema);
415 $sql = 'SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = \'' . $schema . '\' AND table_name = \'' . $table . '\');';
417 $sql = 'SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = \'' . $MIGSCHEMA . '\' AND table_name = \'' . $table . '\');';
419 my $sth = $dbh->prepare($sql);
421 while (my @row = $sth->fetchrow_array) {
422 if ($row[0] eq '1') {
426 if ($debug_flag == 1) {print "detecting $table failed...\n";}
428 if ($row[0] eq '0') {$return_flag = 0;}
431 if ($return_flag == 1 and $debug_flag == 1) {print "succeeded ...\n";}
432 if ($return_flag == 0) {print "! a table failed the find test for report $report_name\n\n";}
436 sub clean_query_string {
439 $str =~ s/(?!_)[[:punct:]]//g; #remove punct except underscores
448 my $query = $report{query};
449 my $sth = $dbh->prepare($query);
454 while (my @row = $sth->fetchrow_array) {
455 if ($header_flag == 0) {
456 print $fh "\n.*$report{report_title}*\n";
458 my @h = split(/\./,$report{heading});
461 while ($h_count <= $h_length) {
462 print $fh "|$h[$h_count-1] ";
468 my $row_length = @row;
470 while ($r <= $row_length) {
471 if (! defined $row[$r-1] ) {
474 print $fh "|$row[$r-1] ";
479 if ($header_flag == 1) {
480 print $fh "|===\n\n";
481 print $fh $report{note};
484 print "successfully wrote output for $report{name}.\n\n";
487 sub print_query_to_excel {
488 my $workbook = shift;
491 my $header_format = $workbook->add_format( bold => 1, color => 'green', size => 16);
492 my $note_format = $workbook->add_format( bold => 1, color => 'red', size => 14);
494 my $query = $report->findvalue('./query');
495 my $title = $report->findvalue('./report_title');
496 my $headings = $report->findnodes('./heading');
498 my $sth = $dbh->prepare($query);
501 my $worksheet = $workbook->add_worksheet( $title );
505 my @h = split(/\./,$headings);
508 while ($h_count <= $h_length) {
509 $col = give_column($h_count-1);
511 $worksheet->write($cell,$h[$h_count-1],$header_format);
515 while (my @row = $sth->fetchrow_array) {
517 my $row_length = @row;
520 while ($r <= $row_length) {
521 if (! defined $row[$r-1] ) {
524 $col = give_column($r-1);
525 $cell = $col . $cur_row;
526 $worksheet->write($cell,$row[$r-1]);
530 $cur_row = $cur_row + 2;
531 $cell = "A" . "$cur_row";
532 $worksheet->write($cell,$report->findvalue('./note'),$note_format);
533 print "Printed Query for $title.\n";
541 $col .= chr( ( $i % 26 ) + ord('A') );
542 $i = int( $i / 26 ) - 1;
545 return scalar reverse $col;
550 print STDERR "$0: $msg", "\n";