#!/usr/local/bin/perl5.004 
#
# Cron script to query the oracle database for all the capminder finishing plates
# that have come thropugh the system over the last 100 days.
#
# This information is saved in a comma delimited file (in my home dir - tmp) from
# where it can be queried by a webpage
#
# bt1 20/12/00
#


BEGIN { 
    unshift @INC, "/usr/local/badger/bin", '/usr/local/badger/gelminder/src/perl';
    #$ENV{TWO_TASK} = "cas2";
}

use strict;
use WrapDBI;
use GIFgraph::bars;

my ($w, $h) = (500, 300); # for graph

my $team_string   = "'team44', 'team45', 'team46', 'team47', 'team48', 'team56', 'team57', 'team58', 'team21', 'team23', 'team24', 'team25'";
my $teamno_string = "44, 45, 46, 47, 48, 56, 57, 58, 21, 23, 24, 25";

#
# for the reports
#
my $keep   = 7;  # number of old versions (including this one) of report to keep
my $prefix = 'projects_needing_evaluation';
my $path = '/nfs/disk54/badger/Admin/Finishing_throughput/';
#my $path   = $ENV{HOME} . '/Admin/Finishing_throughput/';
my $suffix = "html";

#
# Delete old reports...
#
opendir DIR, $path or die "Can't open $path: $!\n";
my @reps = sort grep /^$prefix/, readdir DIR;
closedir DIR;
while (scalar @reps > $keep) { 
    my $old = shift @reps;
    #print "$old<br>";
    unlink "$path/$old" or die "Unable to unlink $path/$old\n";
}

#
# Open filehandle for logfile to produce...
#
my ($ss, $mi, $hh, $d, $m, $y) = localtime();
my $report =  sprintf("$path/${prefix}_%4d_%02d_%02d.$suffix",
                      $y + 1900,
                      $m + 1,
                      $d
                      );
open (FILE, ">$report") || die "Failed to open $report for WRITING...$!\n";
my $dbh  = &OracleConnect("reports");
my $days = 100;
my $ref  = &sql($days);
unless ($ref){
    die  "NOTHING WAS RETURNED FROM THE DATABASE!!!<hr>";
}
foreach my $line (@{$ref}){
    my ($gelfile, $status, $status_date, $operator, $seqread, $seqteam) = @{$line};
    print FILE  "$gelfile, $status, $status_date, $operator, $seqread, $seqteam\n";
}
close FILE;


#
# Get counts per team and plot a graph...
#
my @teams = split /,/, $teamno_string;
my @LoL;

foreach (@teams){
    s/^\s//;
    
    my $count = `grep \" $_,\" $report | /bin/wc -l`+0;
    if ($count){
    #  print "Count for >$_< = >$count<\n";
        push @{$LoL[0]}, $_; # Onto teams array
        push @{$LoL[1]}, $count; # Onto counts array
    }
}

#
# get maximum value in datastructure...
#
my $highest_value = 0;
foreach (@{$LoL[1]}){
    $highest_value = $_ if ($_ >= $highest_value);
    # print ">>>$_<\n";
}
$highest_value = $highest_value + 20;

my $date = localtime();
my $graph = new GIFgraph::bars($w, $h);
$graph->set(x_label        => 'Team',
            transparent    => 1,
            y_label        => 'No. Runs',
            title          => "Throughput over last 100 days ($date)...",
            y_plot_values  => 1,
            y_max_value    => $highest_value,
            y_tick_number  => 8,
            y_min_value    => 0,
            x_ticks        => 1,
            long_ticks     => 0,
           x_label_skip   => 0,
            zero_axis      => 0,
            zero_axis_only => 0,
            overwrite      => 1,
            line_width     => 2,
            dclrs => [ qw(red) ],
            );

#$graph->plot_to_gif("/nfs/intweb/doctree/htdocs/Software/Crusade/crusadegraph.gif", \@LoL);

$graph->plot_to_gif("/nfs/WWWdev/INTWEB_docs/htdocs/Software/Crusade/crusadegraph.gif", \@LoL);
system("/usr/local/bin/webpublish -r -s /nfs/WWWdev/INTWEB_docs/htdocs/Software/Crusade/crusadegraph.gif") ;

####################################################################################

sub OracleConnect {
    my ($oracle_user) = @_;
   
    my $dbh = WrapDBI->connect($oracle_user, {RaiseError => 1, AutoCommit=> 0} );
    die "Could not connect to database" if (!defined $dbh);
    return $dbh;
}

###################################################################################

sub sql{
    my ($days) = @_;
    my $see_all = 1;
   # print "looking in oracle\n";
    my $ref;
    eval{
        #
        # If we want to see all the runs we need to look at the seqread teamname,
        # as when a run is expired, the gelfile status operator will be 'gels'
        #

##
## OLD INEFFICIENT SQL BELOW REPLACED WITH NEW SQL.
##
##        select /*+ INDEX(lp) INDEX(sr) INDEX(gf)*/ distinct gf.gelfilename, gfs.status, 
##                                           gfs.statusdate, gfs.operator, sr.teamname, sr.name
##                                           from gelfile_status gfs, gelfile gf, seqread sr,
##                                           gel_sample gs, loadingsample ls, loadingplate lp     
##                                           where sr.teamname         in ($teamno_string)
##                                           and     gfs.iscurrent     = 1
##                                           and   gfs.gelfilename     = gf.gelfilename
##                                           and   gf.id_partition     is not null
##                                           and   gfs.statusdate      >= trunc(sysdate) - $days
##                                           and   gf.id_gel           = sr.id_gel
##                                           and   sr.lane             = 1
##                                           and   gf.id_gel           = gs.id_gel
##                                           and   gs.id_loadingsample = ls.id_loadingsample
##                                           and   ls.id_loadingplate  = lp.id_loadingplate
##                                           and   lp.category         in (4,2)
##                                           order by sr.teamname, gfs.statusdate
 ##

        $ref = $dbh->selectall_arrayref(qq[
	select gfs.gelfilename, gfs.status, gfs.statusdate, gfs.operator, sr.teamname,gf.label
from gelfile_status gfs, gelfile gf, seqread sr
where gfs.statusdate >=trunc(sysdate)-100
and gfs.status = 'Processing complete'
and operator in ('team56','team57','team58','team44','team45','team46','team47','team48','team21','team23','team24','team25')
and gfs.gelfilename = gf.gelfilename
and gf.label = sr.name
and exists (select 'correct category'
                                                       from gel_sample     gs
                                                       ,loadingsample  ls
                                                       ,loadingplate   lp
                                                       where gf.id_gel           = gs.id_gel
                                                       and   gs.id_loadingsample = ls.id_loadingsample
                                                       and   ls.id_loadingplate  = lp.id_loadingplate
                                                       and   lp.category         in (2,4)
                                                       )
order by 5,3,1,2,4,6
                                         
                                           ]);
    };    
    if ($@){
        die "ORACLE ERROR: $@\n";
    }
    return $ref;
} 



END {
    $dbh->disconnect if ($dbh);
}

__END__

# $Id
#
# $Log


