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;
78 my $parser = XML::LibXML->new();
79 my $lines_per_page = 42;
84 foreach my $arg (@ARGV) {
85 if ($arg eq '--report_title') {
86 $next_arg_is_report_title = 1;
89 if ($next_arg_is_report_title) {
91 $next_arg_is_report_title = 0;
94 if ($arg eq '--analyst') {
95 $next_arg_is_analyst = 1;
98 if ($next_arg_is_analyst) {
100 $next_arg_is_analyst = 0;
103 if ($arg eq '--reports_xml') {
104 $next_arg_is_reports_xml = 1;
107 if ($next_arg_is_reports_xml) {
109 $next_arg_is_reports_xml = 0;
112 if ($arg eq '--tags') {
113 $next_arg_is_tags = 1;
116 if ($next_arg_is_tags) {
118 $next_arg_is_tags = 0;
121 if ($arg eq '--added_page_title') {
122 $next_arg_is_added_page_title = 1;
125 if ($next_arg_is_added_page_title) {
126 $added_page_title = $arg;
127 $next_arg_is_added_page_title = 0;
130 if ($arg eq '--added_page_file') {
131 $next_arg_is_added_page_file = 1;
134 if ($next_arg_is_added_page_file) {
135 $added_page_file = $arg;
136 $next_arg_is_added_page_file = 0;
139 if ($arg eq '--excel_output' or $arg eq '--excel') {
143 if ($arg eq '--debug') {
149 if (!defined $tags) {$tags = 'circs.holds.actors.bibs.assets.money'};
150 if (!defined $report_title) { abort('--report_title must be supplied'); }
151 if ($excel_output == 0 and !defined $analyst) { abort('--analyst must be supplied'); }
153 my $mig_path = abs_path($0);
154 $mig_path =~ s|[^/]+$||;
155 if (!defined $reports_xml) {
156 if ($excel_output == 0) { $reports_xml = $mig_path . '../mig-xml/evergreen_staged_report.xml'; }
157 else { $reports_xml = $mig_path . '../mig-xml/excel_mapping_reports.xml'; }
158 } else { $reports_xml = $mig_path . '/../mig-xml/' . $reports_xml; }
159 my $dom = $parser->parse_file($reports_xml);
161 if (defined $added_page_file or defined $added_page_title) {
162 abort('must specify --added_page_file and --added_page_title') unless defined $added_page_file and defined $added_page_title;
164 if (defined $added_page_file) { $added_page_file = $MIGGITDIR . $added_page_file; }
166 my $dbh = Mig::db_connect();
167 my $report_file = create_report_name($report_title,$excel_output);
168 $report_file = $MIGGITDIR . $report_file;
170 if ($excel_output == 1) {
171 $workbook = Excel::Writer::XLSX->new( $report_file );
173 open($fh, '>', $report_file) or abort("Could not open output file!");
174 write_title_page($report_title,$fh,$analyst);
177 if (defined $added_page_file and defined $added_page_title) {
179 print $fh "== $added_page_title\n";
180 print "$added_page_file\t$added_page_title\n";
181 open(my $an,'<:encoding(UTF-8)', $added_page_file) or abort("Could not open $added_page_file!");
182 while ( my $line = <$an> ) {
189 foreach my $func ($dom->findnodes('//function')) {
190 my $fdrop = $func->findvalue('./drop');
191 my $fcreate = $func->findvalue('./create');
192 my $fname = $func->findvalue('./name');
193 my $sdrop = $dbh->prepare($fdrop);
194 my $screate = $dbh->prepare($fcreate);
195 print "dropping function $fname ... ";
197 print "creating function $fname\n\n";
202 my @report_tags = split(/\./,$tags);
203 foreach my $t (@report_tags) {
204 print "\n\n=========== Starting to process tag $t\n";
205 print "==========================================\n\n";
208 foreach my $asset ($dom->findnodes('//asset')) {
209 if (index($asset->findvalue('./tag'),$t) != -1) {
210 push @asset_files, $asset->findvalue('./file');
214 foreach my $fname (@asset_files) {
215 my $asset_path = $mig_path . '../mig-asc/' . $fname;
216 open my $a, $asset_path or abort("Could not open $fname.");
217 while ( my $l = <$a> ) {
223 if ($excel_output == 0) { print_section_header(ucfirst($t),$fh); }
224 my $linecount = $lines_per_page;
228 foreach my $asset ($dom->findnodes('//asset')) {
229 if (index($asset->findvalue('./tag'),$t) != -1) {
230 push @asset_files, $asset->findvalue('./file');
235 foreach my $report ($dom->findnodes('//report')) {
236 if (index($report->findvalue('./tag'),$t) != -1 and $report->findvalue('./iteration') eq '0') {
237 push @report_names, $report->findvalue('./name');
238 if ($excel_output == 1) { print_query_to_excel($workbook,$report); }
242 #only has one level of failover now but could change to array of hashes and loops
243 #but this keeps it simple and in practice I haven't needed more than two
246 if ($excel_output == 0) {
247 foreach my $rname (@report_names) {
253 if ($debug_flag == 1) {print "\nchecking for $rname ... ";}
254 %report0 = find_report($dom,$t,$rname,'0',$debug_flag);
255 $check_tables0 = check_table($report0{query},$MIGSCHEMA,$debug_flag,$rname);
256 if ($check_tables0 == 1) { $r = print_query($fh,%report0); } else {
257 %report1 = find_report($dom,$t,$rname,'1',$debug_flag);
258 if (defined $report1{query}) {
259 $check_tables1 = check_table($report1{query},$MIGSCHEMA,$debug_flag,$rname);
260 if ($check_tables1 == 1) { $r = print_query($fh,%report1); }
269 if ($excel_output eq 1) { $workbook->close(); }
272 ############ end of main logic
278 my $iteration = shift;
279 my $debug_flag = shift;
282 if ($debug_flag == 1) {print "iteration $iteration ";}
283 foreach my $node ($dom->findnodes('//report')) {
284 if ($node->findvalue('./tag') =~ $tag and $node->findvalue('./iteration') eq $iteration and $node->findvalue('./name') eq $name) {
285 if ($debug_flag == 1) {print "succeeded ... \n";}
287 name => $node->findvalue('./name'),
288 report_title => $node->findvalue('./report_title'),
289 query => $node->findvalue('./query'),
290 heading => $node->findvalue('./heading'),
291 tag => $node->findvalue('./tag'),
292 iteration => $node->findvalue('./iteration'),
293 note => $node->findvalue('./note'),
298 if ($debug_flag == 1) {print "failed ... \n";}
300 name => "eaten by grue"
304 sub print_section_header {
309 #$t =~ s/(\w+)/\u$1/g;;
311 print $fh "== $t Reports\n";
315 sub create_report_name {
317 my $excel_output = shift;
319 my @abbr = qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec);
320 my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
322 my $date = $year . '_' . $abbr[$mon] . '_' . $mday;
324 if ($excel_output == 0) { $report_file = $rt . ' ' . $date . '.asciidoc'; }
325 else { $report_file = $rt . ' ' . $date . '.xlsx'; }
326 $report_file =~ s/ /_/g;
330 sub write_title_page {
335 my @abbr = qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec);
336 my $l = length($report_title);
337 my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
340 print $fh "$mday $abbr[$mon] $year\n";
342 #print $fh ":title-logo-image: image::eolilogosmall.png[pdfwidth=3in]\n";
349 my $MIGSCHEMA = shift;
350 my $debug_flag = shift;
351 my $report_name = shift;
353 if ($debug_flag == 1) {print "$query\n";}
357 my @qe = split(/ /,$query);
362 if ($qe[$i] eq 'FROM' or $qe[$i] eq 'JOIN') {
364 if ($qe[$q] ne '(SELECT') {
365 push @tables, $qe[$q];
370 if ($debug_flag == 1) {print "checking tables ... ";}
373 foreach my $table (@tables) {
376 if (index($table,'.') != -1) {
377 $schema = (split /\./,$table)[0];
378 $table = (split /\./,$table)[1];
380 $table = clean_query_string($table);
381 if (defined $schema) {
382 $schema = clean_query_string($schema);
383 $sql = 'SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = \'' . $schema . '\' AND table_name = \'' . $table . '\');';
385 $sql = 'SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = \'' . $MIGSCHEMA . '\' AND table_name = \'' . $table . '\');';
387 my $sth = $dbh->prepare($sql);
389 while (my @row = $sth->fetchrow_array) {
390 if ($row[0] eq '1') {
394 if ($debug_flag == 1) {print "detecting $table failed...\n";}
396 if ($row[0] eq '0') {$return_flag = 0;}
399 if ($return_flag == 1 and $debug_flag == 1) {print "succeeded ...\n";}
400 if ($return_flag == 0) {print "! a table failed the find test for report $report_name\n\n";}
404 sub clean_query_string {
407 $str =~ s/(?!_)[[:punct:]]//g; #remove punct except underscores
416 my $query = $report{query};
417 my $sth = $dbh->prepare($query);
422 while (my @row = $sth->fetchrow_array) {
423 if ($header_flag == 0) {
424 print $fh "\n.*$report{report_title}*\n";
426 my @h = split(/\./,$report{heading});
429 while ($h_count <= $h_length) {
430 print $fh "|$h[$h_count-1] ";
436 my $row_length = @row;
438 while ($r <= $row_length) {
439 if (! defined $row[$r-1] ) {
442 print $fh "|$row[$r-1] ";
447 if ($header_flag == 1) {
448 print $fh "|===\n\n";
449 print $fh $report{note};
452 print "successfully wrote output for $report{name}.\n\n";
455 sub print_query_to_excel {
456 my $workbook = shift;
459 my $header_format = $workbook->add_format( bold => 1, color => 'green', size => 16);
460 my $note_format = $workbook->add_format( bold => 1, color => 'red', size => 14);
462 my $query = $report->findvalue('./query');
463 my $title = $report->findvalue('./report_title');
464 my $headings = $report->findnodes('./heading');
466 my $sth = $dbh->prepare($query);
469 my $worksheet = $workbook->add_worksheet( $title );
473 my @h = split(/\./,$headings);
476 while ($h_count <= $h_length) {
477 $col = give_column($h_count-1);
479 $worksheet->write($cell,$h[$h_count-1],$header_format);
483 while (my @row = $sth->fetchrow_array) {
485 my $row_length = @row;
488 while ($r <= $row_length) {
489 if (! defined $row[$r-1] ) {
492 $col = give_column($r-1);
493 $cell = $col . $cur_row;
494 $worksheet->write($cell,$row[$r-1]);
498 $cur_row = $cur_row + 2;
499 $cell = "A" . "$cur_row";
500 $worksheet->write($cell,$report->findvalue('./note'),$note_format);
501 print "Printed Query for $title.\n";
509 $col .= chr( ( $i % 26 ) + ord('A') );
510 $i = int( $i / 26 ) - 1;
513 return scalar reverse $col;
518 print STDERR "$0: $msg", "\n";