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

###########################################################################
#                                                                         #
#   The script details the summary of the projects for the                #
#   project report.                                                       #
#                                                                         #                                      
#   Author : Karen Ambrose                                                #
#                                                                         #
###########################################################################

#BEGIN {	unshift @INC, "/usr/local/badger/bin","/nfs/WWW/cgi-bin";
#	$ENV{'ORACLE_HOME'}='/usr/local/oracle';		   
#	$ENV{TWO_TASK} = 'sids';
#    }

#set up the components needed.
use SangerPaths qw(badger);
use WrapDBI;
use strict;
use CGI qw(:standard :html3);

#Declare variables.
my $db;
my $DBI;
my $dbh;
my $sqlstatement;
my $sth; 
my $table_colour = '#E2E2FF'; # Colour of table cells in report
my $cgititle= 'Summary Reports';
my $cgiauthor='kva';
my $address=' http://www.sanger.ac.uk';



#Names and connects to the database 'reports'.
$db = 'reports';

$dbh = WrapDBI->connect("$db", {RaiseError => 1})
    || die print "Connection with database failed: $DBI::errstr";

#setting the environmental variable for the information sent from the web
#interface, and setting up another test statement.

my $query=CGI::new();
print $query->header;
my $value = $query ->param("Projectname");

print $query->start_html(-title=>'Summary Reports', -bgcolor=>'#FFFFFF');

print"<FONT SIZE=\"5\"><CENTER> Summary Report Data For Projectname : <B>$value</B></CENTER></FONT><BR>\n";

#Devision of the SQL statement and it's execution.


#counts up the number of summary reports and displays it.
$sqlstatement = " select count(*) from project_summary where projectname = '$value'"; 
$sth = $dbh->prepare($sqlstatement) || die "cannot prepare statement:$DBI::errstr\n";
$sth-> execute || die "cannot execute statement:$DBI::errstr\n";

my $count;

#printing out and structuring of the results.

while ($count = $sth->fetchrow)
{
    print "<TABLE BORDER=\"1\" WIDTH=\"30%\" ALIGN=\"CENTER\"> 
         <TR><TD WIDTH=\"25%\"><CENTER>Summaries Generated</CENTER></TD>
         <TD WIDTH=\"5%\"><CENTER><B>&nbsp;$count</B></CENTER></TD>
         </TR></TABLE> ";
}

print "<BR>";

#gets all the information for each summary report and formats and displays the data.
$sqlstatement = "select summarydate,num_contigs,total_ctg_len,num_lge_contigs,lge_ctg_len,num_reads,coverage from project_summary where projectname = '$value'";    
$sth = $dbh->prepare($sqlstatement) || die "cannot prepare statement:$DBI::errstr\n";
$sth-> execute || die "cannot execute statement:$DBI::errstr\n";

#printing out and structuring of the results.

my $summarydate;
my $num_contigs;
my $total_ctg_len;
my $num_lge_contigs;
my $lge_ctg_len;
my $num_reads;
my $coverage;


print "<TABLE BORDER=\"1\" WIDTH=\"90%\" ALIGN=\"center\" BGCOLOR=$table_colour>";
print Tr([th({-align=>'center'},['Summary Date','Number of Contigs','Total Contig Length (bp)','Number of Large Contigs (>1000bp)','Large Contig Length Total (bp)','Number of Reads','Coverage'])]), "\n";

while (($summarydate,$num_contigs,$total_ctg_len,$num_lge_contigs,$lge_ctg_len,$num_reads,$coverage )=$sth->fetchrow_array)
{  
    
#each  variable is checked, and if it contains no data, the value of a space is added, to fill out the table boxes, purely a cosmetic measure.  
    $summarydate = '&nbsp;' unless $summarydate;
    $num_contigs = '&nbsp;' unless $num_contigs;
    $total_ctg_len = '&nbsp;' unless $total_ctg_len;
    $num_lge_contigs = '&nbsp;' unless $num_lge_contigs;
    $lge_ctg_len = '&nbsp;' unless $lge_ctg_len;
    $num_reads = '&nbsp;' unless $num_reads;
    $coverage = '&nbsp;' unless $coverage;
    
    print Tr([td({-align=>'center'},[$summarydate,$num_contigs,$total_ctg_len,$num_lge_contigs,$lge_ctg_len,$num_reads,$coverage])]), "\n";  
}

print "</TABLE>";

print <<EOF;
<center>
<form><input type=button value="   Close   " onClick="self.close()"></form>
<hr noshade size=1>
</center>
EOF

$sth->finish();

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

print $query->end_html;

#Disconnecting from the database.
$dbh->disconnect
    or warn "Disconnection failed: $DBI::errstr\n";


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


__END__


