X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=blobdiff_plain;f=mig-bin%2Fmig-reporter;h=79410f702052aafa9223a63decc237268eb6edb8;hp=cb7e4e6153a79f26dd3011ea6c2c8791c3d6043a;hb=f16a4f89a26412af791edf4b0fd504d49553f155;hpb=91f3fc3c89d088788f7064bc06764ff83a40e607

diff --git a/mig-bin/mig-reporter b/mig-bin/mig-reporter
index cb7e4e6..79410f7 100755
--- a/mig-bin/mig-reporter
+++ b/mig-bin/mig-reporter
@@ -1,5 +1,47 @@
 #!/usr/bin/perl
 
+###############################################################################
+=pod
+
+=item B<reporter> --analyst "Analyst Name" --report_title "Report Title"
+
+Generates an asciidoc file in the git working directory that can be converted to 
+any appropriate format.  The analyst and report parameters are required.
+
+Optional parameters are : 
+
+--added_page_title and --added_page_file 
+
+If one is used both must be.  The added page file can be plain text or asciidoc.  This
+adds an extra arbitrary page of notes to the report.  Mig assumes the page file is in the mig git directory.
+
+--tags
+
+This will define a set of tags to use, if not set it will default to Circs, 
+Holds, Actors, Bibs, Assets & Money. 
+
+--debug
+
+Gives more information about what is happening.
+
+--reports_xml 
+
+Allows you to override the default evergreen_staged_report.xml in the mig-xml folder.
+
+--excel_output or --excel
+
+Pushes output to an Excel file instead of asciidoc file. 
+
+--captions or --captions_off
+
+Adds the captions tag to asciidoc header to turn off captions in generated output.
+
+=back
+
+=cut
+
+###############################################################################
+
 use strict;
 use warnings;
 
@@ -10,6 +52,7 @@ use Env qw(
     HOME PGHOST PGPORT PGUSER PGDATABASE MIGSCHEMA
     MIGBASEWORKDIR MIGBASEGITDIR MIGGITDIR MIGWORKDIR
 );
+use Excel::Writer::XLSX;
 use Pod::Usage;
 use Switch;
 use Cwd 'abs_path';
@@ -20,6 +63,7 @@ use Mig;
 use open ':encoding(utf8)';
 
 pod2usage(-verbose => 2) if defined $ARGV[0] && $ARGV[0] eq '--help';
+pod2usage(-verbose => 1) if ! $ARGV[1];
 
 my $analyst;
 my $next_arg_is_analyst;
@@ -33,9 +77,14 @@ my $added_page_title;
 my $next_arg_is_added_page_title;
 my $added_page_file;
 my $next_arg_is_added_page_file;
+my $excel_output = 0;
+my $captions_off = 0;
 my $i = 0;
 my $parser = XML::LibXML->new();
 my $lines_per_page = 42;
+my $debug_flag = 0;
+my $workbook;
+my $fh;
 
 foreach my $arg (@ARGV) {
     if ($arg eq '--report_title') {
@@ -92,38 +141,53 @@ foreach my $arg (@ARGV) {
         $next_arg_is_added_page_file = 0;
         next;
     }
+    if ($arg eq '--excel_output' or $arg eq '--excel') {
+        $excel_output = 1;
+        next;
+    }
+    if ($arg eq '--captions_off' or $arg eq '--captions') {
+        $captions_off = 1;
+        next;
+    }
+    if ($arg eq '--debug') {
+        $debug_flag = 1;
+        next;
+    }
 }
 
 if (!defined $tags) {$tags = 'circs.holds.actors.bibs.assets.money'};
-if (!defined $analyst) { abort('--analyst must be supplied'); }
 if (!defined $report_title) { abort('--report_title must be supplied'); }
+if ($excel_output == 0 and !defined $analyst) { abort('--analyst must be supplied'); }
 
 my $mig_path = abs_path($0);
 $mig_path =~ s|[^/]+$||;
-if (!defined $reports_xml) { $reports_xml = $mig_path . '../mig-xml/evergreen_staged_report.xml'; } 
-    else { $reports_xml = $mig_path . '/../mig-xml/' . $reports_xml; }
-print "$reports_xml \n";
+if (!defined $reports_xml) { 
+    if ($excel_output == 0) { $reports_xml = $mig_path . '../mig-xml/evergreen_staged_report.xml'; } 
+        else { $reports_xml = $mig_path . '../mig-xml/excel_mapping_reports.xml'; } 
+    } else { $reports_xml = $mig_path . '/../mig-xml/' . $reports_xml; }
 my $dom = $parser->parse_file($reports_xml);
 
 if (defined $added_page_file or defined $added_page_title) {
     abort('must specify --added_page_file and --added_page_title') unless defined $added_page_file and defined $added_page_title;
     }
 if (defined $added_page_file) { $added_page_file = $MIGGITDIR . $added_page_file; }
-if ($MIGSCHEMA eq 'full') { $MIGSCHEMA = ''; }
 
 my $dbh = Mig::db_connect();
-my $report_file = create_report_name($report_title);
+my $report_file = create_report_name($report_title,$excel_output);
 $report_file = $MIGGITDIR . $report_file;
 
-open(my $fh, '>', $report_file) or die "Could not open output file!";
-
-write_title_page($report_title,$fh,$analyst);
+if ($excel_output == 1) {
+    $workbook = Excel::Writer::XLSX->new( $report_file );
+} else {
+    open($fh, '>', $report_file) or abort("Could not open output file!");
+    write_title_page($report_title,$fh,$analyst,$captions_off);
+};
 
 if (defined $added_page_file and defined $added_page_title) { 
     print $fh "<<<\n";
     print $fh "== $added_page_title\n";
     print "$added_page_file\t$added_page_title\n";
-    open(my $an,'<:encoding(UTF-8)', $added_page_file) or die "Could not open $added_page_file !";
+    open(my $an,'<:encoding(UTF-8)', $added_page_file) or abort("Could not open $added_page_file!");
     while ( my $line = <$an> ) {
         print $fh $line;
     }
@@ -147,45 +211,72 @@ $tags = lc($tags);
 my @report_tags = split(/\./,$tags);
 foreach my $t (@report_tags) {
     print "\n\n=========== Starting to process tag $t\n";
-    print   "==========================================\n";
-    print_section_header(ucfirst($t),$fh);
+    print   "==========================================\n\n";
+
+    my @asset_files;
+    foreach my $asset ($dom->findnodes('//asset')) {
+        if (index($asset->findvalue('./tag'),$t) != -1) {
+            push @asset_files, $asset->findvalue('./file');
+        }
+    }
+
+    foreach my $fname (@asset_files) {
+        my $asset_path = $mig_path . '../mig-asc/' . $fname;
+        open my $a, $asset_path or abort("Could not open $fname.");
+        while ( my $l = <$a> ) {
+            print $fh $l;
+        }
+    print $fh "<<<\n";
+    }
+
+    if ($excel_output == 0) { print_section_header(ucfirst($t),$fh); }
     my $linecount = $lines_per_page;
     my $r;
 
-    my @report_names;
+    undef @asset_files;
+    foreach my $asset ($dom->findnodes('//asset')) {
+        if (index($asset->findvalue('./tag'),$t) != -1) {
+            push @asset_files, $asset->findvalue('./file');
+        }
+    }
 
+    my @report_names;
     foreach my $report ($dom->findnodes('//report')) {
         if (index($report->findvalue('./tag'),$t) != -1 and $report->findvalue('./iteration') eq '0') {
             push @report_names, $report->findvalue('./name');
+            if ($excel_output == 1) { print_query_to_excel($workbook,$report); }
         }
     }
-    
-    print Dumper(@report_names);
 
     #only has one level of failover now but could change to array of hashes and loops
-    foreach my $rname (@report_names) {
-        my %report0;
-        my %report1;
-        my $check_tables0;
-        my $check_tables1;
-
-        print "\nchecking for $rname ... ";
-        %report0 = find_report($dom,$t,$rname,'0');
-        $check_tables0 = check_table($report0{query},$MIGSCHEMA);
-        if ($check_tables0 == 1) {
-           $r =  print_query($fh,%report0); 
-        } else {
-        %report1 = find_report($dom,$t,$rname,'1');
-        if (defined $report1{query}) {
-            $check_tables1 = check_table($report1{query},$MIGSCHEMA);
-            if ($check_tables1 == 1) {$r = print_query($fh,%report1);}    
+    #but this keeps it simple and in practice I haven't needed more than two
+    
+
+    if ($excel_output == 0) {
+        foreach my $rname (@report_names) {
+            my %report0;
+            my %report1;
+            my $check_tables0;
+            my $check_tables1;
+
+            if ($debug_flag == 1) {print "\nchecking for $rname ... ";}
+            %report0 = find_report($dom,$t,$rname,'0',$debug_flag);
+            $check_tables0 = check_table($report0{query},$MIGSCHEMA,$debug_flag,$rname);
+            if ($check_tables0 == 1) { $r =  print_query($fh,%report0); } else {
+                %report1 = find_report($dom,$t,$rname,'1',$debug_flag);
+                if (defined $report1{query}) {
+                    $check_tables1 = check_table($report1{query},$MIGSCHEMA,$debug_flag,$rname);
+                    if ($check_tables1 == 1) { $r = print_query($fh,%report1); }
+                }
             }
         }
     }
 }
 
 print "\n";
-close $fh;
+
+if ($excel_output eq 1) { $workbook->close(); } 
+    else { close $fh; }
 
 ############ end of main logic
 
@@ -194,12 +285,13 @@ sub find_report {
     my $tag = shift;
     my $name = shift;
     my $iteration = shift;
+    my $debug_flag = shift;
     my %report;
 
-    print "iteration $iteration ";
+    if ($debug_flag == 1) {print "iteration $iteration ";}
     foreach my $node ($dom->findnodes('//report')) {
         if ($node->findvalue('./tag') =~ $tag and $node->findvalue('./iteration') eq $iteration and $node->findvalue('./name') eq $name) {
-            print "succeeded ... ";
+            if ($debug_flag == 1) {print "succeeded ... \n";}
             %report = (
                 name => $node->findvalue('./name'),
                 report_title => $node->findvalue('./report_title'),
@@ -212,7 +304,7 @@ sub find_report {
             return %report;
         }
     }
-    print "failed ... ";
+    if ($debug_flag == 1) {print "failed ... \n";}
     return %report = (
         name => "eaten by grue"
     );
@@ -221,19 +313,25 @@ sub find_report {
 sub print_section_header {
     my $t = shift;
     my $fh = shift;
+
     $t =~ s/_/ /g;
     #$t =~ s/(\w+)/\u$1/g;;
     print $fh "<<<\n";
     print $fh "== $t Reports\n";
+    return;
 }
 
 sub create_report_name {
     my $rt = shift;
+    my $excel_output = shift;
+
     my @abbr = qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec);
     my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
     $year += 1900;
     my $date = $year . '_' . $abbr[$mon] . '_' . $mday;
-    my $report_file = $rt . ' ' . $date . '.asciidoc';
+    my $report_file;
+    if ($excel_output == 0) { $report_file = $rt . ' ' . $date . '.asciidoc'; }
+        else { $report_file = $rt . ' ' . $date . '.xlsx'; }
     $report_file =~ s/ /_/g;
     return $report_file;
 }
@@ -242,6 +340,7 @@ sub write_title_page {
     my $rt = shift;
     my $fh = shift;
     my $a = shift;
+    my $captions_off = shift;
 
     my @abbr = qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec);
     my $l = length($report_title);
@@ -250,26 +349,52 @@ sub write_title_page {
     print $fh "= $rt\n"; 
     print $fh "$mday $abbr[$mon] $year\n";
     print $fh "$a\n";
-    print $fh ":title-logo-image: image::eolilogosmall.png[pdfwidth=3in]\n";
+    #print $fh ":title-logo-image: image::eolilogosmall.png[pdfwidth=3in]\n";
     print $fh ":toc:\n";
+    if ($captions_off == 1) { print $fh ":caption:\n"; }
     print $fh "\n";
 }
 
 sub check_table {
     my $query = shift;
     my $MIGSCHEMA = shift;
+    my $debug_flag = shift;
+    my $report_name = shift;
+
+    if ($debug_flag == 1) {print "$query\n";}
 
-    my $i = 0;
+    my $i;
     my $return_flag = 1;   
     my @qe = split(/ /,$query);
-    my @tables = grep /MIGSCHEMA/, @qe;
+    $i = @qe;
+    $i--;
+    my @tables;
+    while ($i > -1) {
+        if ($qe[$i] eq 'FROM' or $qe[$i] eq 'JOIN') {
+            my $q = $i + 1;
+            if ($qe[$q] ne '(SELECT') {
+                push @tables, $qe[$q];            
+            }
+        }
+        $i--;
+    }
+    if ($debug_flag == 1) {print "checking tables ... ";}
 
-    print "checking tables ... ";
+    $i = 0;
     foreach my $table (@tables) {
-        $table =~ s/MIGSCHEMA.//g;
-        $table =~ s/\)//g;
-        $table =~ s/\<//g;
-        my $sql = 'SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = \'' . $MIGSCHEMA . '\' AND table_name = \'' . $table . '\');';
+        my $sql;
+        my $schema;
+        if (index($table,'.') != -1) {
+            $schema = (split /\./,$table)[0];
+            $table = (split /\./,$table)[1];
+        }
+        $table = clean_query_string($table); 
+        if (defined $schema) {
+            $schema = clean_query_string($schema);
+            $sql = 'SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = \'' . $schema . '\' AND table_name = \'' . $table . '\');';
+        } else {
+            $sql = 'SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = \'' . $MIGSCHEMA . '\' AND table_name = \'' . $table . '\');';
+        }
         my $sth = $dbh->prepare($sql);
         $sth->execute();
         while (my @row = $sth->fetchrow_array) {
@@ -277,19 +402,29 @@ sub check_table {
                     next;
                 } else {
                     $return_flag = 0;
+                    if ($debug_flag == 1) {print "detecting $table failed...\n";}
                 }
             if ($row[0] eq '0') {$return_flag = 0;}
         }
     }
-    if ($return_flag == 1) {print "succeeded ... ";} else {print "failed ... ";}
+    if ($return_flag == 1 and $debug_flag == 1) {print "succeeded ...\n";}
+    if ($return_flag == 0) {print "! a table failed the find test for report $report_name\n\n";}
     return $return_flag;
 }
 
+sub clean_query_string {
+    my $str = shift;
+    
+    $str =~ s/(?!_)[[:punct:]]//g; #remove punct except underscores
+    $str =~ s/\n//g;
+    $str =~ s/\r//g;
+    return $str;
+}
+
 sub print_query {
     my $fh = shift;
     my %report = @_;
     my $query = $report{query};
-    print $query;
     my $sth = $dbh->prepare($query);
     $sth->execute();
 
@@ -328,6 +463,67 @@ sub print_query {
     print "successfully wrote output for $report{name}.\n\n";
 }
 
+sub print_query_to_excel {
+    my $workbook = shift;
+    my $report = shift;
+
+    my $header_format = $workbook->add_format( bold => 1, color => 'green', size => 16);
+    my $note_format = $workbook->add_format( bold => 1, color => 'red', size => 14);
+
+    my $query = $report->findvalue('./query');
+    my $title = $report->findvalue('./report_title');
+    my $headings = $report->findnodes('./heading');
+
+    my $sth = $dbh->prepare($query);
+    $sth->execute();
+
+    my $worksheet = $workbook->add_worksheet( $title );
+    my $cell = "";
+    my $col = "";
+
+    my @h = split(/\./,$headings);
+    my $h_length = @h;
+    my $h_count = 1;
+    while ($h_count <= $h_length) {
+        $col = give_column($h_count-1);
+        $cell = $col . '1';
+        $worksheet->write($cell,$h[$h_count-1],$header_format);
+        $h_count++;
+    }
+    my $cur_row = 1;
+    while (my @row = $sth->fetchrow_array) {
+            $cur_row++;
+            my $row_length = @row;
+            my $r = 1;
+            print Dumper(@row);
+            while ($r <= $row_length) {
+                if (! defined $row[$r-1] ) {
+                    $row[$r-1] = 'none';
+                }
+                $col = give_column($r-1);
+                $cell = $col . $cur_row;
+                $worksheet->write($cell,$row[$r-1]);
+                $r++;
+            }
+        }
+    $cur_row = $cur_row + 2;
+    $cell = "A" . "$cur_row"; 
+    $worksheet->write($cell,$report->findvalue('./note'),$note_format);
+    print "Printed Query for $title.\n";
+}
+
+sub give_column {
+    my $i = shift;
+    my $col = "";
+
+    do {
+        $col .= chr( ( $i % 26 ) + ord('A') );
+        $i = int( $i / 26 ) - 1;
+    } while ( $i >= 0 );
+
+    return scalar reverse $col;
+}
+
 sub abort {
     my $msg = shift;
     print STDERR "$0: $msg", "\n";