#!/usr/local/bin/perl -wT

###########################################################################
#                                                                         #
#   The script details the reports that have been written by the          #
#   Oracle team and are available for use, giving information on their    #
#   file location, description of their function and the SQL tables       #
#   involved.  The reports with extension '.sql' can be executed through  #
#   this tool.                                                            #
#                                                                         #             
#   Author : Karen Ambrose                                                #
#                                                                         #
###########################################################################

#BEGIN {
#  use CGI::Carp qw(carpout);
#  my $root = SangerWeb->server_root();
#  open(LOG, ">>$root/cgi-bin/oracle_reports/logs/reportindex-log") or die "Unable to append to reportindex-log:$!\n";
#  carpout(*LOG);
#}

$|=1;

use strict;
use SangerPaths qw(core badger);
use SangerWeb;
use File::Find;
use CGI qw(:standard :html3);
$ENV{'PATH'}     = "/bin:/usr/bin";
my $root         = SangerWeb->server_root();
my $DIR          = "$root/lib/badger/Reports/Programs/";
my $sqlDir       = "$root/lib/badger/Reports/Programs/";           #parent directory of the report files.
my $table_colour = '#B0E0E6';                                   #table colour.#E2E2FF
my $cgititle     = 'Reports Index';                                 #page title.
my $cgiauthor    = 'kva';                                          #page author.
my $reportlink   = '/cgi-bin/oracle_reports/report_parser.pl';      #address of the script that displays the report.
my $script       = $ENV{'SCRIPT_NAME'};

my $filepath;
my @filepaths;
my @fps;
my $fps;
my $count=0;
my $f;
my @types;
my %preferences;

#@types = `(cd $sqlDir ; ls -l | awk '\$1 \~ /^d.+/ {print \$9}') 2>&1`;
@types = `(cd $sqlDir ; ls -l | awk '\$1 \~ /^d.+/ {print \$9}') 2>/dev/null`;
my $stats_types=\@types;

push @types, 'ALL_REPORTS';

&outputheader($cgititle);


if ((! param) || (param('rep_type'))) {
    
    unless (param('rep_type')){
	&NAVIGATION_BAR;    
	&STATS($sqlDir,$stats_types);
	
#add option here to allow the reduction of the amount of
#reports to view at any one time.
#make the list from the list of subdirectories off the main trunk
    
    print "<BR>";
    
    print
        start_form(-method =>"post", -action=>"$script"),
        
        table({-width=>'50%', -align=>'center'},      
              TR({-align=>'center'},
                 td(''),
                 td("Report Types:"),
                 td(popup_menu(-name=>'rep_type',
                               -value=>\@types,
                               -default=>$preferences{'rep_type'})
                    ),
                 td (submit(-label=>'Submit Query')),
                 ),
              ),
        end_form;

    }

    if (param('rep_type')){
	
	my $choice = param('rep_type');
	$choice=~ s/\s+//g;
	
	if ($choice eq 'ALL_REPORTS'){
	    $sqlDir = $sqlDir;
	} else {
	    $sqlDir = $sqlDir.$choice.'/';
	}
	
	&NAVIGATION_BAR;    

#add option here to allow the reduction of the amount of
#reports to view at any one time.
#make the list from the list of subdirectories off the main trunk
	
	print "<BR>";
	
	print
	    start_form(-method =>"post", -action=>"$script"),
	    
	    table({-width=>'50%', -align=>'center'},      
		  TR({-align=>'center'},
		     td(''),
		     td("Report Types:"),
		     td(popup_menu(-name=>'rep_type',
				   -value=>\@types,
				   -default=>$preferences{'rep_type'})
			),
		     td (submit(-label=>'Submit Query')),
		     ),
		  ),
	    end_form;
	
	
	print "<BR>";
	if ($choice eq 'ALL_REPORTS'){
	    print "<CENTER><H4><U>$choice</U></H4></CENTER>";
	} else {
	    print "<CENTER><H4><U>Reports for $choice</U></H4></CENTER>";
	}
	print "<BR>";
	print "<TABLE WIDTH =\"90%\" ALIGN=\"CENTER\" BORDER=\"0\" CELLPADDING=\"10\" BGCOLOR=$table_colour>";
	print Tr([th({-align=>'left', -height=>'40'},["&nbsp;","Description","Truncated Filepath","Oracle Tables in use","Report Usage"])]),"\n";
	
#find file module, which traverses a directory tree and calls to a subroutine.
	find ({
	       'wanted'   => \&WANTED,
	       'no_chdir' => 1,
	      }, "$sqlDir");    
	
	foreach $f (@{$fps}){
	    &EXTRACT_INFORMATION($f);
	}
	print "</TABLE>";
	
	my $time=localtime();
	
	print "<br><br>";
	print "<font size=\"5\" color=\"blue\"><center>The number of reports written to date : $time = $count</center></font>"; 
	
    }
    
}


print "<br><br><center><address>Any feedback should be directed to <a href=\"mailto:kva\@sanger.ac.uk\">Karen Ambrose</a></address></center><br>";
&outputfooter($cgiauthor);


#exiting via mod perl environment or normally.
if ($ENV{'MOD_PERL'}){
    Apache->exit;
}else{
    exit;
}


########## SUBROUTINES ##############################################


sub outputheader {
    
    my ($cgititle)=@_;
    
    print &SangerWeb::header({
        'title'=> $cgititle
        });
}

sub outputfooter {
    
    my ($cgiauthor)=@_;
    
    print &SangerWeb::footer({
        'author' => $cgiauthor
        });
}


#navigation bar
sub NAVIGATION_BAR {
    
    print "<TABLE BORDER=\"0\" HEIGHT=\"10\"  WIDTH=\"80%\"  ALIGN=\"center\" BGCOLOR=$table_colour>";
    print Tr([td({-align=>'center'},[a({href=>"http://intweb.sanger.ac.uk/HGP/oracle_reports/web.shtml"},"Home-KIT"),a({href=>"http://intweb.sanger.ac.uk/cgi-bin/oracle_reports/status.pl?Projectname=Projectname"},"Project List Totals"),a({href=>"http://intweb.sanger.ac.uk/cgi-bin/oracle_reports/status.pl?Clonename=Clonename"},"Clone List Totals"),a({href=>"http://intweb.sanger.ac.uk/cgi-bin/oracle_reports/Reports_work/unix_finishing_reports_log.pl"},"Report Usage Tables"),a({href=>"http://intweb.sanger.ac.uk/HGP/oracle_reports/help.shtml#Reports Index"},"Help")])]);
    print "</TABLE>";
    print "<BR>";
    
#blurb information
    print "<br><CENTER><STRONG><FONT SIZE=\"6\">Report Index</FONT></STRONG></CENTER>";
    print "<BR>";
    print "<TABLE BORDER=\"0\" WIDTH=\"90%\"  ALIGN=\"center\">";
    print "<TR><TD>";
    print "<H5>\nBelow are a list of SQL & PL files presently deposited in the <font color=red>$sqlDir</font> directory.\n\n</H5>";
    print "</TD></TR>";
    print "<TR><TD>";
    print "\nThe reports below can be accessed by selecting the report type, followed by the button on the left of the required report after the list has been shown. Another window will be opened to display the report. The total number of reports written to date for a specified report type is detailed at the bottom of the table.";
    print "</TD></TR>";
    print "<TR><TD>";
    print "\nWhere a report incorporates project information, a link to the ETS system will automatically appear for a list of projects below 500. The projects will be ordered alphabetically and the links will be split into bunches of a 100.";
    print "</TD></TR>";
    print "<TR><TD>";
    print "\n<B>NOTE</B>: Some of these files take more than one minute to return the results, please be patient. No '.pl' extension files can be accessed using this page.\n\n";
    print "</TD></TR>";
    print "</TABLE>";
    print "<BR>";
    print "<BR>";
    
}

sub STATS {
    my($sqlDir,$tps)=@_;
    
    my %REP_STATS;
    print "<CENTER><U>STATS SECTION</U></CENTER>";
    foreach my $t (@$tps){
        $t=~ s/\s+//g;
        my $newsqlDir;
        if ($t eq 'ALL_REPORTS'){
            $newsqlDir=$sqlDir;
        } else {
            $newsqlDir=$sqlDir.$t.'/';
        }
        @filepaths="";
        find ({
	       'wanted'   => \&WANTED,
	       'no_chdir' => 1,
	      }, "$newsqlDir");
        my $rcount=0;
        $rcount=scalar(@{$fps});
        $rcount=$rcount-1;
        $REP_STATS{$t}=$rcount;
    }
    
    print "<BR>";
    print "<TABLE ALIGN=CENTER WIDTH=40% BORDER=1>";
    print "<TR BGCOLOR=$table_colour><TH>REPORT TYPE</TH><TH>Number of reports found</TH></TR>";
    foreach my $keys (sort keys %REP_STATS){
        print "<TR align=center>";
        print "<TD BGCOLOR=$table_colour>$keys</TD><TD>$REP_STATS{$keys}</TD>";
        print "</TR>";
    }
    print "</TABLE>";
    print "<BR>";
    
}

#extract the relevant files from the directory tree.
sub WANTED {
    
    if ($File::Find::name=~ /^.+\.sql(?!_.+|\~)|\.pl$/){
        push @filepaths,$File::Find::name;    
    }
    $fps=\@filepaths;
    return $fps;
    
}


#information describing the different reports, as well as information on the SQL tables used is also extracted.
sub EXTRACT_INFORMATION {
    
    my($f)=@_;
    
    my $line;
    my $dline;
    my @line;
    my $sqltables;
    my $tables;    
    my $report;
    my $path;
    my $l;
    my @fpss;
    my @fpaths;
    my $fpss;
    
    push @fpaths,$f;
    
    my %seen_table;
    foreach my $path(@fpaths){
#	print "PATH INFORMATION - $path <BR>";
        my @tables;
        if(open (FH, "< $path")){
            while(<FH>){
#		print ">>DATA IS $_ << <BR>";
                if ($_=~ /^rem(.+)/gi){
                    $line.="<BR>".$_;
                    
                } 
		elsif ($_=~ /^\#rem(.+)/gi){
                    $line.="<BR>".$_;
                }
		
		if ($line){
		    if ($line=~ /$DIR/){
			$line=~ s/$DIR//g;
		    }
		}

                if ($path=~ /.sql$/){
                    $filepath=$path;
                    if ($_=~ /^select/i../quit/i){
                        next unless /^from/gi../where/gi;
                        $sqltables=$_;
                        $sqltables=~ s/where(.*)//gi;
                        $sqltables=~ s/.+from//gi;
                        $sqltables=~ s/from//gi;
                        $sqltables=~ s/quit//gi;
                        $sqltables=~ s/\"|\;//gi;
                        @tables = split/\,/,$sqltables;
                    }

                }else{

                    $filepath=$path;
                    if ($_=~ /^select/i../quit/i){
                        next unless /^from(.+)|\t*from(.+)/gsi;
                        $sqltables=$_;
                        $sqltables=~ s/where(.*)//gi;
                        $sqltables=~ s/\#.+//g;
                        $sqltables=~ s/.+from//gi;
                        $sqltables=~ s/from//gi;
                        $sqltables=~ s/\"|\;//gi;
                        @tables = split/\,/,$sqltables;
                    }
                }   
                
                foreach (@tables){
                    unless ($seen_table{$_}){
                        $seen_table{$_}=1;
                    }
                }
                
            }    
        } 
        $count++;        
        close FH; 
    } 
    
    foreach my $keys (keys %seen_table){
        $tables.="<BR>".$keys;
    }
  
    if ($line){
	$line=~ s/rem|\#rem//g; 
	$line =~ s/:entry title=|:list title=/<br>\:/gi;
	$line =~ s/\-|~|#|=//g;
    }
    
    if (!defined $tables){
        $tables='&nbsp';
    }
    
    if ((!defined $line) || ($line!~ /\S+/)){
        $line=strong("No Description");
    }
    
    &DISPLAY_REPORT($line,$filepath,$tables);
    return $count;
    
}


#report is displayed.
sub DISPLAY_REPORT{
    
    my ($line,$filepath,$tables)=@_;
    
    my $fp;
    
    $fp=$filepath;  
    $fp=~ s/$DIR//g;
    $fp=~ s/\//\/ /g;
    
    my $reportcount=0;
    my $tfile;
    $tfile=$fp;
    $tfile=~ s/\s+//g;
    my $ruh="$root/cgi-bin/oracle_reports/logs/kit_log";
    if (open(RUH,"< $ruh")){
        while(<RUH>){
            my @pathcount=split(" ",$_);
            my $tpath=pop @pathcount;
            if ($tpath eq $tfile){
                $reportcount++;
            }
        }   
    }
    close(RUH);
    
    my @line;
    push @line,$line;
    
    if ($filepath=~ /\S+\.sql/gi){  
        print Tr([td({-align=>'left'},["<FORM method=POST action=$reportlink target=resource window><INPUT TYPE=HIDDEN NAME=Filepath VALUE=$filepath><INPUT TYPE=HIDDEN NAME=TF VALUE=$tfile><INPUT TYPE=submit VALUE=Go class=blue1></form>",@line,$fp,$tables,$reportcount])]),"\n";
    }else{
        print Tr([td({-align=>'left'},["&nbsp;",@line,$fp,$tables,"&nbsp;"])]),"\n";
    }
    
    
}


__END__












