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.
39 ###############################################################################
48 HOME PGHOST PGPORT PGUSER PGDATABASE MIGSCHEMA
49 MIGBASEWORKDIR MIGBASEGITDIR MIGGITDIR MIGWORKDIR
51 use Excel::Writer::XLSX;
56 my $mig_bin = "$FindBin::Bin/";
57 use lib "$FindBin::Bin/";
59 use open ':encoding(utf8)';
61 pod2usage(-verbose => 2) if defined $ARGV[0] && $ARGV[0] eq '--help';
62 pod2usage(-verbose => 1) if ! $ARGV[1];
65 my $next_arg_is_analyst;
67 my $next_arg_is_report_title;
69 my $next_arg_is_reports_xml;
73 my $next_arg_is_added_page_title;
75 my $next_arg_is_added_page_file;
79 my $parser = XML::LibXML->new();
80 my $lines_per_page = 42;
85 foreach my $arg (@ARGV) {
86 if ($arg eq '--report_title') {
87 $next_arg_is_report_title = 1;
90 if ($next_arg_is_report_title) {
92 $next_arg_is_report_title = 0;
95 if ($arg eq '--analyst') {
96 $next_arg_is_analyst = 1;
99 if ($next_arg_is_analyst) {
101 $next_arg_is_analyst = 0;
104 if ($arg eq '--reports_xml') {
105 $next_arg_is_reports_xml = 1;
108 if ($next_arg_is_reports_xml) {
110 $next_arg_is_reports_xml = 0;
113 if ($arg eq '--tags') {
114 $next_arg_is_tags = 1;
117 if ($next_arg_is_tags) {
119 $next_arg_is_tags = 0;
122 if ($arg eq '--added_page_title') {
123 $next_arg_is_added_page_title = 1;
126 if ($next_arg_is_added_page_title) {
127 $added_page_title = $arg;
128 $next_arg_is_added_page_title = 0;
131 if ($arg eq '--added_page_file') {
132 $next_arg_is_added_page_file = 1;
135 if ($next_arg_is_added_page_file) {
136 $added_page_file = $arg;
137 $next_arg_is_added_page_file = 0;
140 if ($arg eq '--excel_output' or $arg eq '--excel') {
144 if ($arg eq '--captions_off' or $arg eq '--captions') {
148 if ($arg eq '--debug') {
154 if (!defined $tags) {$tags = 'circs.holds.actors.bibs.assets.money'};
155 if (!defined $report_title) { abort('--report_title must be supplied'); }
156 if ($excel_output == 0 and !defined $analyst) { abort('--analyst must be supplied'); }
158 my $mig_path = abs_path($0);
159 $mig_path =~ s|[^/]+$||;
160 if (!defined $reports_xml) {
161 if ($excel_output == 0) { $reports_xml = $mig_path . '../mig-xml/evergreen_staged_report.xml'; }
162 else { $reports_xml = $mig_path . '../mig-xml/excel_mapping_reports.xml'; }
163 } else { $reports_xml = $mig_path . '/../mig-xml/' . $reports_xml; }
164 my $dom = $parser->parse_file($reports_xml);
166 if (defined $added_page_file or defined $added_page_title) {
167 abort('must specify --added_page_file and --added_page_title') unless defined $added_page_file and defined $added_page_title;
169 if (defined $added_page_file) { $added_page_file = $MIGGITDIR . $added_page_file; }
171 my $dbh = Mig::db_connect();
172 my $report_file = create_report_name($report_title,$excel_output);
173 $report_file = $MIGGITDIR . $report_file;
175 if ($excel_output == 1) {
176 $workbook = Excel::Writer::XLSX->new( $report_file );
178 open($fh, '>', $report_file) or abort("Could not open output file!");
179 write_title_page($report_title,$fh,$analyst,$captions_off);
182 if (defined $added_page_file and defined $added_page_title) {
184 print $fh "== $added_page_title\n";
185 print "$added_page_file\t$added_page_title\n";
186 open(my $an,'<:encoding(UTF-8)', $added_page_file) or abort("Could not open $added_page_file!");
187 while ( my $line = <$an> ) {
194 foreach my $func ($dom->findnodes('//function')) {
195 my $fdrop = $func->findvalue('./drop');
196 my $fcreate = $func->findvalue('./create');
197 my $fname = $func->findvalue('./name');
198 my $sdrop = $dbh->prepare($fdrop);
199 my $screate = $dbh->prepare($fcreate);
200 print "dropping function $fname ... ";
202 print "creating function $fname\n\n";
207 my @report_tags = split(/\./,$tags);
208 foreach my $t (@report_tags) {
209 print "\n\n=========== Starting to process tag $t\n";
210 print "==========================================\n\n";
213 foreach my $asset ($dom->findnodes('//asset')) {
214 if (index($asset->findvalue('./tag'),$t) != -1) {
215 push @asset_files, $asset->findvalue('./file');
219 foreach my $fname (@asset_files) {
220 my $asset_path = $mig_path . '../mig-asc/' . $fname;
221 open my $a, $asset_path or abort("Could not open $fname.");
222 while ( my $l = <$a> ) {
228 if ($excel_output == 0) { print_section_header(ucfirst($t),$fh); }
229 my $linecount = $lines_per_page;
233 foreach my $asset ($dom->findnodes('//asset')) {
234 if (index($asset->findvalue('./tag'),$t) != -1) {
235 push @asset_files, $asset->findvalue('./file');
240 foreach my $report ($dom->findnodes('//report')) {
241 if (index($report->findvalue('./tag'),$t) != -1 and $report->findvalue('./iteration') eq '0') {
242 push @report_names, $report->findvalue('./name');
243 if ($excel_output == 1) { print_query_to_excel($workbook,$report); }
247 #only has one level of failover now but could change to array of hashes and loops
248 #but this keeps it simple and in practice I haven't needed more than two
251 if ($excel_output == 0) {
252 foreach my $rname (@report_names) {
258 if ($debug_flag == 1) {print "\nchecking for $rname ... ";}
259 %report0 = find_report($dom,$t,$rname,'0',$debug_flag);
260 $check_tables0 = check_table($report0{query},$MIGSCHEMA,$debug_flag,$rname);
261 if ($check_tables0 == 1) { $r = print_query($fh,%report0); } else {
262 %report1 = find_report($dom,$t,$rname,'1',$debug_flag);
263 if (defined $report1{query}) {
264 $check_tables1 = check_table($report1{query},$MIGSCHEMA,$debug_flag,$rname);
265 if ($check_tables1 == 1) { $r = print_query($fh,%report1); }
274 if ($excel_output eq 1) { $workbook->close(); }
277 ############ end of main logic
283 my $iteration = shift;
284 my $debug_flag = shift;
287 if ($debug_flag == 1) {print "iteration $iteration ";}
288 foreach my $node ($dom->findnodes('//report')) {
289 if ($node->findvalue('./tag') =~ $tag and $node->findvalue('./iteration') eq $iteration and $node->findvalue('./name') eq $name) {
290 if ($debug_flag == 1) {print "succeeded ... \n";}
292 name => $node->findvalue('./name'),
293 report_title => $node->findvalue('./report_title'),
294 query => $node->findvalue('./query'),
295 heading => $node->findvalue('./heading'),
296 tag => $node->findvalue('./tag'),
297 iteration => $node->findvalue('./iteration'),
298 note => $node->findvalue('./note'),
303 if ($debug_flag == 1) {print "failed ... \n";}
305 name => "eaten by grue"
309 sub print_section_header {
314 #$t =~ s/(\w+)/\u$1/g;;
316 print $fh "== $t Reports\n";
320 sub create_report_name {
322 my $excel_output = shift;
324 my @abbr = qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec);
325 my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
327 my $date = $year . '_' . $abbr[$mon] . '_' . $mday;
329 if ($excel_output == 0) { $report_file = $rt . ' ' . $date . '.asciidoc'; }
330 else { $report_file = $rt . ' ' . $date . '.xlsx'; }
331 $report_file =~ s/ /_/g;
335 sub write_title_page {
339 my $captions_off = shift;
341 my @abbr = qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec);
342 my $l = length($report_title);
343 my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
346 print $fh "$mday $abbr[$mon] $year\n";
348 #print $fh ":title-logo-image: image::eolilogosmall.png[pdfwidth=3in]\n";
350 if ($captions_off == 1) { print $fh ":caption:\n"; }
356 my $MIGSCHEMA = shift;
357 my $debug_flag = shift;
358 my $report_name = shift;
360 if ($debug_flag == 1) {print "$query\n";}
364 my @qe = split(/ /,$query);
369 if ($qe[$i] eq 'FROM' or $qe[$i] eq 'JOIN') {
371 if ($qe[$q] ne '(SELECT') {
372 push @tables, $qe[$q];
377 if ($debug_flag == 1) {print "checking tables ... ";}
380 foreach my $table (@tables) {
383 if (index($table,'.') != -1) {
384 $schema = (split /\./,$table)[0];
385 $table = (split /\./,$table)[1];
387 $table = clean_query_string($table);
388 if (defined $schema) {
389 $schema = clean_query_string($schema);
390 $sql = 'SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = \'' . $schema . '\' AND table_name = \'' . $table . '\');';
392 $sql = 'SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = \'' . $MIGSCHEMA . '\' AND table_name = \'' . $table . '\');';
394 my $sth = $dbh->prepare($sql);
396 while (my @row = $sth->fetchrow_array) {
397 if ($row[0] eq '1') {
401 if ($debug_flag == 1) {print "detecting $table failed...\n";}
403 if ($row[0] eq '0') {$return_flag = 0;}
406 if ($return_flag == 1 and $debug_flag == 1) {print "succeeded ...\n";}
407 if ($return_flag == 0) {print "! a table failed the find test for report $report_name\n\n";}
411 sub clean_query_string {
414 $str =~ s/(?!_)[[:punct:]]//g; #remove punct except underscores
423 my $query = $report{query};
424 my $sth = $dbh->prepare($query);
429 while (my @row = $sth->fetchrow_array) {
430 if ($header_flag == 0) {
431 print $fh "\n.*$report{report_title}*\n";
433 my @h = split(/\./,$report{heading});
436 while ($h_count <= $h_length) {
437 print $fh "|$h[$h_count-1] ";
443 my $row_length = @row;
445 while ($r <= $row_length) {
446 if (! defined $row[$r-1] ) {
449 print $fh "|$row[$r-1] ";
454 if ($header_flag == 1) {
455 print $fh "|===\n\n";
456 print $fh $report{note};
459 print "successfully wrote output for $report{name}.\n\n";
462 sub print_query_to_excel {
463 my $workbook = shift;
466 my $header_format = $workbook->add_format( bold => 1, color => 'green', size => 16);
467 my $note_format = $workbook->add_format( bold => 1, color => 'red', size => 14);
469 my $query = $report->findvalue('./query');
470 my $title = $report->findvalue('./report_title');
471 my $headings = $report->findnodes('./heading');
473 my $sth = $dbh->prepare($query);
476 my $worksheet = $workbook->add_worksheet( $title );
480 my @h = split(/\./,$headings);
483 while ($h_count <= $h_length) {
484 $col = give_column($h_count-1);
486 $worksheet->write($cell,$h[$h_count-1],$header_format);
490 while (my @row = $sth->fetchrow_array) {
492 my $row_length = @row;
495 while ($r <= $row_length) {
496 if (! defined $row[$r-1] ) {
499 $col = give_column($r-1);
500 $cell = $col . $cur_row;
501 $worksheet->write($cell,$row[$r-1]);
505 $cur_row = $cur_row + 2;
506 $cell = "A" . "$cur_row";
507 $worksheet->write($cell,$report->findvalue('./note'),$note_format);
508 print "Printed Query for $title.\n";
516 $col .= chr( ( $i % 26 ) + ord('A') );
517 $i = int( $i / 26 ) - 1;
520 return scalar reverse $col;
525 print STDERR "$0: $msg", "\n";