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;
60 my $mig_bin = "$FindBin::Bin/";
61 use lib "$FindBin::Bin/";
63 use open ':encoding(utf8)';
65 pod2usage(-verbose => 2) if defined $ARGV[0] && $ARGV[0] eq '--help';
66 pod2usage(-verbose => 1) if ! $ARGV[1];
69 my $next_arg_is_analyst;
71 my $next_arg_is_report_title;
73 my $next_arg_is_reports_xml;
77 my $next_arg_is_added_page_title;
79 my $next_arg_is_added_page_file;
83 my $parser = XML::LibXML->new();
84 my $lines_per_page = 42;
89 foreach my $arg (@ARGV) {
90 if ($arg eq '--report_title') {
91 $next_arg_is_report_title = 1;
94 if ($next_arg_is_report_title) {
96 $next_arg_is_report_title = 0;
99 if ($arg eq '--analyst') {
100 $next_arg_is_analyst = 1;
103 if ($next_arg_is_analyst) {
105 $next_arg_is_analyst = 0;
108 if ($arg eq '--reports_xml') {
109 $next_arg_is_reports_xml = 1;
112 if ($next_arg_is_reports_xml) {
114 $next_arg_is_reports_xml = 0;
117 if ($arg eq '--tags') {
118 $next_arg_is_tags = 1;
121 if ($next_arg_is_tags) {
123 $next_arg_is_tags = 0;
126 if ($arg eq '--added_page_title') {
127 $next_arg_is_added_page_title = 1;
130 if ($next_arg_is_added_page_title) {
131 $added_page_title = $arg;
132 $next_arg_is_added_page_title = 0;
135 if ($arg eq '--added_page_file') {
136 $next_arg_is_added_page_file = 1;
139 if ($next_arg_is_added_page_file) {
140 $added_page_file = $arg;
141 $next_arg_is_added_page_file = 0;
144 if ($arg eq '--excel_output' or $arg eq '--excel') {
148 if ($arg eq '--captions_off' or $arg eq '--captions') {
152 if ($arg eq '--debug') {
158 if (!defined $tags) {$tags = 'circs.holds.actors.bibs.assets.money'};
159 if (!defined $report_title) { abort('--report_title must be supplied'); }
160 if ($excel_output == 0 and !defined $analyst) { abort('--analyst must be supplied'); }
162 my $mig_path = abs_path($0);
163 $mig_path =~ s|[^/]+$||;
164 if (!defined $reports_xml) {
165 if ($excel_output == 0) { $reports_xml = $mig_path . '../mig-xml/evergreen_staged_report.xml'; }
166 else { $reports_xml = $mig_path . '../mig-xml/excel_mapping_reports.xml'; }
167 } else { $reports_xml = $mig_path . '/../mig-xml/' . $reports_xml; }
168 my $dom = $parser->parse_file($reports_xml);
170 if (defined $added_page_file or defined $added_page_title) {
171 abort('must specify --added_page_file and --added_page_title') unless defined $added_page_file and defined $added_page_title;
173 if (defined $added_page_file) { $added_page_file = $MIGGITDIR . $added_page_file; }
175 my $dbh = Mig::db_connect();
176 my $report_file = create_report_name($report_title,$excel_output);
177 $report_file = $MIGGITDIR . $report_file;
179 if ($excel_output == 1) {
180 $workbook = Excel::Writer::XLSX->new( $report_file );
182 open($fh, '>', $report_file) or abort("Could not open output file!");
183 write_title_page($report_title,$fh,$analyst,$captions_off);
186 if (defined $added_page_file and defined $added_page_title) {
188 print $fh "== $added_page_title\n";
189 print "$added_page_file\t$added_page_title\n";
190 open(my $an,'<:encoding(UTF-8)', $added_page_file) or abort("Could not open $added_page_file!");
191 while ( my $line = <$an> ) {
198 foreach my $func ($dom->findnodes('//function')) {
199 my $fdrop = $func->findvalue('./drop');
200 my $fcreate = $func->findvalue('./create');
201 my $fname = $func->findvalue('./name');
202 my $sdrop = $dbh->prepare($fdrop);
203 my $screate = $dbh->prepare($fcreate);
204 print "dropping function $fname ... ";
206 print "creating function $fname\n\n";
211 my @report_tags = split(/\./,$tags);
212 foreach my $t (@report_tags) {
213 print "\n\n=========== Starting to process tag $t\n";
214 print "==========================================\n\n";
217 foreach my $asset ($dom->findnodes('//asset')) {
218 if (index($asset->findvalue('./tag'),$t) != -1) {
219 push @asset_files, $asset->findvalue('./file');
223 foreach my $fname (@asset_files) {
224 my $asset_path = $mig_path . '../mig-asc/' . $fname;
225 open my $a, $asset_path or abort("Could not open $fname.");
226 while ( my $l = <$a> ) {
232 if ($excel_output == 0) { print_section_header(ucfirst($t),$fh); }
233 my $linecount = $lines_per_page;
237 foreach my $asset ($dom->findnodes('//asset')) {
238 if (index($asset->findvalue('./tag'),$t) != -1) {
239 push @asset_files, $asset->findvalue('./file');
244 foreach my $report ($dom->findnodes('//report')) {
245 if (index($report->findvalue('./tag'),$t) != -1 and $report->findvalue('./iteration') eq '0') {
246 push @report_names, $report->findvalue('./name');
247 if ($excel_output == 1) { print_query_to_excel($workbook,$report); }
251 #only has one level of failover now but could change to array of hashes and loops
252 #but this keeps it simple and in practice I haven't needed more than two
255 if ($excel_output == 0) {
256 foreach my $rname (@report_names) {
262 if ($debug_flag == 1) {print "\nchecking for $rname ... ";}
263 %report0 = find_report($dom,$t,$rname,'0',$debug_flag);
264 $check_tables0 = check_table($report0{query},$MIGSCHEMA,$debug_flag,$rname);
265 if ($check_tables0 == 1) { $r = print_query($fh,%report0); } else {
266 %report1 = find_report($dom,$t,$rname,'1',$debug_flag);
267 if (defined $report1{query}) {
268 $check_tables1 = check_table($report1{query},$MIGSCHEMA,$debug_flag,$rname);
269 if ($check_tables1 == 1) { $r = print_query($fh,%report1); }
278 if ($excel_output eq 1) { $workbook->close(); }
281 ############ end of main logic
287 my $iteration = shift;
288 my $debug_flag = shift;
291 if ($debug_flag == 1) {print "iteration $iteration ";}
292 foreach my $node ($dom->findnodes('//report')) {
293 if ($node->findvalue('./tag') =~ $tag and $node->findvalue('./iteration') eq $iteration and $node->findvalue('./name') eq $name) {
294 if ($debug_flag == 1) {print "succeeded ... \n";}
296 name => $node->findvalue('./name'),
297 report_title => $node->findvalue('./report_title'),
298 query => $node->findvalue('./query'),
299 heading => $node->findvalue('./heading'),
300 tag => $node->findvalue('./tag'),
301 iteration => $node->findvalue('./iteration'),
302 note => $node->findvalue('./note'),
307 if ($debug_flag == 1) {print "failed ... \n";}
309 name => "eaten by grue"
313 sub print_section_header {
318 #$t =~ s/(\w+)/\u$1/g;;
320 print $fh "== $t Reports\n";
324 sub create_report_name {
326 my $excel_output = shift;
328 my @abbr = qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec);
329 my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
331 my $date = $year . '_' . $abbr[$mon] . '_' . $mday;
333 if ($excel_output == 0) { $report_file = $rt . ' ' . $date . '.asciidoc'; }
334 else { $report_file = $rt . ' ' . $date . '.xlsx'; }
335 $report_file =~ s/ /_/g;
339 sub write_title_page {
343 my $captions_off = shift;
345 my @abbr = qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec);
346 my $l = length($report_title);
347 my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
350 print $fh "$mday $abbr[$mon] $year\n";
352 #print $fh ":title-logo-image: image::eolilogosmall.png[pdfwidth=3in]\n";
354 if ($captions_off == 1) { print $fh ":caption:\n"; }
360 my $MIGSCHEMA = shift;
361 my $debug_flag = shift;
362 my $report_name = shift;
364 if ($debug_flag == 1) {print "$query\n";}
368 my @qe = split(/ /,$query);
373 if ($qe[$i] eq 'FROM' or $qe[$i] eq 'JOIN') {
375 if ($qe[$q] ne '(SELECT') {
376 push @tables, $qe[$q];
381 if ($debug_flag == 1) {print "checking tables ... ";}
384 foreach my $table (@tables) {
387 if (index($table,'.') != -1) {
388 $schema = (split /\./,$table)[0];
389 $table = (split /\./,$table)[1];
391 $table = clean_query_string($table);
392 if (defined $schema) {
393 $schema = clean_query_string($schema);
394 $sql = 'SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = \'' . $schema . '\' AND table_name = \'' . $table . '\');';
396 $sql = 'SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = \'' . $MIGSCHEMA . '\' AND table_name = \'' . $table . '\');';
398 my $sth = $dbh->prepare($sql);
400 while (my @row = $sth->fetchrow_array) {
401 if ($row[0] eq '1') {
405 if ($debug_flag == 1) {print "detecting $table failed...\n";}
407 if ($row[0] eq '0') {$return_flag = 0;}
410 if ($return_flag == 1 and $debug_flag == 1) {print "succeeded ...\n";}
411 if ($return_flag == 0) {print "! a table failed the find test for report $report_name\n\n";}
415 sub clean_query_string {
418 $str =~ s/(?!_)[[:punct:]]//g; #remove punct except underscores
427 my $query = $report{query};
428 my $sth = $dbh->prepare($query);
433 while (my @row = $sth->fetchrow_array) {
434 if ($header_flag == 0) {
435 print $fh "\n.*$report{report_title}*\n";
437 my @h = split(/\./,$report{heading});
440 while ($h_count <= $h_length) {
441 print $fh "|$h[$h_count-1] ";
447 my $row_length = @row;
449 while ($r <= $row_length) {
450 if (! defined $row[$r-1] ) {
453 print $fh "|$row[$r-1] ";
458 if ($header_flag == 1) {
459 print $fh "|===\n\n";
460 print $fh $report{note};
463 print "successfully wrote output for $report{name}.\n\n";
466 sub print_query_to_excel {
467 my $workbook = shift;
470 my $header_format = $workbook->add_format( bold => 1, color => 'green', size => 16);
471 my $note_format = $workbook->add_format( bold => 1, color => 'red', size => 14);
473 my $query = $report->findvalue('./query');
474 my $title = $report->findvalue('./report_title');
475 my $headings = $report->findnodes('./heading');
477 my $sth = $dbh->prepare($query);
480 my $worksheet = $workbook->add_worksheet( $title );
484 my @h = split(/\./,$headings);
487 while ($h_count <= $h_length) {
488 $col = give_column($h_count-1);
490 $worksheet->write($cell,$h[$h_count-1],$header_format);
494 while (my @row = $sth->fetchrow_array) {
496 my $row_length = @row;
499 while ($r <= $row_length) {
500 if (! defined $row[$r-1] ) {
503 $col = give_column($r-1);
504 $cell = $col . $cur_row;
505 $worksheet->write($cell,$row[$r-1]);
509 $cur_row = $cur_row + 2;
510 $cell = "A" . "$cur_row";
511 $worksheet->write($cell,$report->findvalue('./note'),$note_format);
512 print "Printed Query for $title.\n";
520 $col .= chr( ( $i % 26 ) + ord('A') );
521 $i = int( $i / 26 ) - 1;
524 return scalar reverse $col;
529 print STDERR "$0: $msg", "\n";