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

###########################################################################
#                                                                         #
#   The script details the individual project or clone report for         #
#   a selected project or clone name from the previous list.              #           
#                                                                         #                                      
#   Author : Karen Ambrose                                                #
#                                                                         #
###########################################################################

$|=1;
#set up the components needed.
use strict;
use SangerPaths qw(core badger);	
use CGI qw(:all);
use SangerWeb;
use WrapDBI;
use HTTP::Cookies;

#Declare variables.
my $db;
my $DBI;
my $dbh;
my $sqlstatement;
my $sth;
my $table_colour    = '#E2E2FF'; #table colour
my $cgititle        = 'Project Reports';
my $cgititle2       = 'Clone Reports';
my $cgititle3       = 'SQL Table Information';
my $cgiauthor       = 'kva';
my $chromosomepage  = 'http://www.sanger.ac.uk/HGP/Chr';              # displays info on the chromosome.
my $listprojaddress = '/cgi-bin/oracle_reports/projstatusclones.pl';  # displays the projects or clones at a given status.
my $summaryaddress  = '/cgi-bin/oracle_reports/projsummary.pl';       # displays the summary information.
my $reportaddress   = '/cgi-bin/oracle_reports/report.pl';            # displays either clone or project report information.
my $phonebook       = '/cgi-bin/phonebook.pl';                        # connects to employee info + picture.
my $embladdress     = 'http://srs.sanger.ac.uk/srsbin/cgi-bin/wgetz'; # extracts embl records.
my $teampages       = 'http://www.sanger.ac.uk/Teams/Team';           # displays info on the teams.

my $h;
my $value;
my $VAR1;
my $result_arrayref;

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

my $sw = SangerWeb->new({
			 'author' => $cgiauthor,
			});
my $query = $sw->cgi();
if($query->param("Projectname")) {
  $sw->title($cgititle);
} elsif($query->param("Clonename")) {
  $sw->title($cgititle2);
} elsif($query->param("information")) {
  $sw->title($cgititle3);
}

print $sw->header();

eval {
  $dbh = WrapDBI->connect("$db", {RaiseError => 1}) or die "Connection with database failed: $DBI::errstr";
};
if($@) {
  print $@;
  warn $@;
  print $sw->footer();
  exit;
}

my $projectname  = $query->param("Projectname");
my $clonename    = $query->param("Clonename");
my $information  = $query->param("information");
my $internalname = $query->param("Internal_Name");

#########
# Chromoview appears to pass this in sometimes:
#
if($internalname && !$projectname) {
  $projectname = $internalname;
}

if ($projectname) {
    my $param = "Projectname";

#    &COOKIE_INFO($projectname);
    &HEADER_INFORMATION($projectname, $param);
    &VAR_PROJECTSTATEMENTS($projectname);    
}

if ($query->param("Clonename")) {
    my $param = "Clonename";

#    &COOKIE_INFO($clonename);
    &HEADER_INFORMATION($clonename, $param);
    &VAR_CLONESTATEMENTS($clonename);
}


if ($query->param("information")) {
    &TABLE_INFORMATION($information);
}


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

print $sw->footer();

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

1;


#SUBROUTINES---------------------------------------------------------


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

#sub COOKIE_INFO {
#    
#    my ($value)=@_;
#    
##    my $firstcookie;
##    $firstcookie=cookie(-name    => "StatusID",
##                        -value   => $value,
##                        -path    => '/cgi-bin/oracle_reports/ ',
##                        -domain  => '.sanger.ac.uk',
##                        -expires => "+2h");
#    
##print $query->header(-cookie=>a({href=>$firstcookie},"Project List Totals"));
##print header(-cookie=>$firstcookie);
#    
#}

#formats the navigational bar,and blurb information for the top of the page.
sub HEADER_INFORMATION {
    
    my ($value,$param)=@_;
    
    my ($name,$columnname,$tablename,$header,$title);
    if ($param=~ /Projectname/){
        $name       = "projectname";
        $columnname = "clonename";
        $tablename  = "clone_project";
        $header     = "Clone(s) associated with this project: ";
        $title      = "Project Report";
    } else {
        $name       = "clonename";
        $columnname = "projectname";
        $tablename  = "clone_project";
        $header     = "Project associated with this clone: ";
        $title      = "Clone Report";
    }

#Toolbars

    print "<TABLE BORDER=\"0\" HEIGHT=\"10\"  WIDTH=\"90%\"  ALIGN=\"center\" BGCOLOR=$table_colour>";
    print Tr([td({-align=>'center'},[a({href=>"/HGP/oracle_reports/web.shtml"},"Home-KIT"),a({href=>"/cgi-bin/oracle_reports/status.pl?Projectname=Projectname"},"Project List Totals"),a({href=>"/cgi-bin/oracle_reports/status.pl?Clonename=Clonename"},"Clone List Totals"),a({href=>"/cgi-bin/oracle_reports/reportindex.pl"},"Reports Index"),a({href=>"/cgi-bin/oracle_reports/Reports_work/unix_finishing_reports_log.pl"},"Report Usage Tables"),a({href=>"/HGP/oracle_reports/help.shtml#Project/Clone Reports"},"Help")])]);
    print "</TABLE>";
    print "<BR>";
    print "<BR>";
    
    print "<TABLE BORDER=\"3\" WIDTH=\"90%\" ALIGN=\"center\" >";
      print Tr([th({-align=>'center', -height=>'60'},[font({-size=>'6'}, $title,"for",ucfirst $name, ':', $value)])]), "\n";
    
#Extraction of clones names that are associated with the project
    
    $sqlstatement = "select $columnname from $tablename where $name = '$value'"; 
    my $clone_arrayref = $dbh->selectall_arrayref($sqlstatement) || die print "cannot execute statement: $DBI::errstr";
    
    my @all;
    my $arrayref;
    
    foreach my $arrayref(@{$clone_arrayref}){
        
        if ($param=~ /Projectname/){
            @{$arrayref}=a({href=>"$reportaddress?Clonename=@{$arrayref}"}, "@{$arrayref}");
            push (@all,@{$arrayref});

        }else{
            @{$arrayref}=a({href=>"$reportaddress?Projectname=@{$arrayref}"}, "@{$arrayref}");
            push (@all,@{$arrayref});
        }
    }
    
    #checks to see if there are any corresponding reports
    my $scount=scalar @all;    
    if ($scount == 0){
        @all="<font color=\"red\">No report available</font>";
    }  
    
    print  Tr([td({-align=>'left'},[font({-size=>'3'},["<BR>","<B>","$header","</B>","@all"])])]),"\n";
    print "</TABLE>";
}

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

#sql extracts the relevant data for the selected project or clone name, constructing a report format of information.

##############
#            #
#Project     #
#Report      #
#Information #
#            #
##############
sub VAR_PROJECTSTATEMENTS {
    
    my ($value)=@_;
    
    
#STATUS HISTORY
    $VAR1 = {'sql'=>{'statement'=> "select b.description,a.statusdate,a.iscurrent,a.status from project_status a,projectstatusdict b where a.status = b.id_dict and a.projectname ='$value'  order by a.statusdate" ,
                     'fields'=>['Project Status', 'StatusDate', 'Current Status'] },
             'text'=>{'title'=>['Status History']}};
 
    eval {
        $result_arrayref = $dbh->selectall_arrayref($VAR1->{'sql'}{'statement'});
        foreach my $h(@{$result_arrayref}){
            @{$h}[0]=a({href=>"$listprojaddress?Projectstatus=@{$h}[3]"},"@{$h}[0]");
            if (@{$h}[2]=='1'){
                @{$h}[2]=(strong("Present Status"));
            }else{ 
                @{$h}[2]="--";
            }
            @{$h}=@{$h}[0..2] ;  
        }
    };
    
    &DISPLAY_REPORT($VAR1,$result_arrayref,$h);
    
    
#TEAMS INVOLVED WITH THE PROJECT
    $VAR1 = { 'sql' => { 'statement' => "select a.teamname,b.description,a.owned_from,a.iscurrent from project_owner a, projectownerdict b where a.owner_type = b.id_dict and a.projectname = '$value' order by owned_from",
                         'fields' =>['Teamname','Description','Owned_from','Current Owner'] },
              'text'=>{'title'=>['Teams involved with the project']}};
    
    eval {
        $result_arrayref = $dbh->selectall_arrayref($VAR1->{'sql'}{'statement'});
        foreach my $h(@{$result_arrayref}){
            @{$h}[0]=a({href=>"$teampages@{$h}[0]"},"@{$h}[0]"); 
            if (@{$h}[3]=='1'){
                @{$h}[3]=(strong("present owner"));
            }else{ 
                @{$h}[3]="--";
            }
        }
    };
    
    &DISPLAY_REPORT($VAR1,$result_arrayref,$h);
    
    
#PEOPLE WORKING ON THIS PROJECT	
    $VAR1 = { 'sql' => { 'statement' => "select a.id_person, a.forename, a.surname , a.email,  b.roletype,c.assigned_from from person a, team_person_role b, project_role c where a.id_person = b.id_person and b.id_role = c.id_role and c.projectname = '$value' order by c.assigned_from"},
              'text'=>{'title'=>['People working on this project']}};
    
    eval {
        $result_arrayref = $dbh->selectall_arrayref($VAR1->{'sql'}{'statement'});
        foreach my $h(@{$result_arrayref}){
            @{$h}[1]=@{$h}[1]." ".@{$h}[2];
            @{$h}[3]=a({href=>"$phonebook?name=@{$h}[3]&action=search"}, "@{$h}[3]");
            @{$h}=@{$h}[1,3..5] ;  
        }
    };
    
    &DISPLAY_REPORT($VAR1,$result_arrayref,$h);
    
    
#PEOPLE LINKED TO THIS PROJECT	
    $VAR1 = { 'sql' => { 'statement' => "select accountname, date_created, date_expired from project_link a where projectname = '$value' order by date_created",
                         'fields' =>['Accountname','Created','Expired'] },
              'text'=>{'title'=>['People linked to this project']}};
    
    eval {
        $result_arrayref = $dbh->selectall_arrayref($VAR1->{'sql'}{'statement'});
    };
    &DISPLAY_REPORT($VAR1,$result_arrayref);
    
    
#EMBL INFORMATION - unfinished submission
    $VAR1 = { 'sql' => { 'statement' => "select accession from unfinished_submission a where projectname = '$value'",
                         'fields' =>['Unfinished EMBL Submission'] },
              'text'=>{'title'=>['EMBL information']}};
    
    eval {
        $result_arrayref = $dbh->selectall_arrayref($VAR1->{'sql'}{'statement'});
        foreach my $h(@{$result_arrayref}){
            @{$h}[0]=a({href=>"$embladdress?-id+4Flds1Dap2a+-e+[EMBL-AccNumber:@{$h}[0]]"},"@{$h}[0]");
        }
    };
    &DISPLAY_REPORT3($VAR1,$result_arrayref,$h);
    
    
#EMBL INFORMATION - finished submission
    $VAR1 = { 'sql' => { 'statement' => "select accession from finished_submission a where projectname = '$value'",
                         'fields' =>['Finished EMBL Submission'] }};
    
    eval {
        $result_arrayref = $dbh->selectall_arrayref($VAR1->{'sql'}{'statement'});
        foreach my $h(@{$result_arrayref}){
            @{$h}[0]=a({href=>"$embladdress?-id+4Flds1Dap2a+-e+[EMBL-AccNumber:@{$h}[0]]"},"@{$h}[0]");
        }
    };
    &DISPLAY_REPORT3($VAR1,$result_arrayref,$h);
    
    
#EXTRA INFORMATION - summaries generated
    $VAR1 = { 'sql' => { 'statement' => "select count(*) from project_summary a where projectname = '$value'",
                         'fields' =>['Summaries Generated'] },
              'text'=>{'title'=>['Extra information']}};
    
    eval {
        $result_arrayref = $dbh->selectall_arrayref($VAR1->{'sql'}{'statement'});
        foreach my $h(@{$result_arrayref}){
            if (@{$h}[0]=="0"){
               @{$h}[0]="0";
            }else {
                @{$h}[0]=a({href=>"$summaryaddress?Projectname=$value",-target=>"infowin", -onClick=>"open_win()"},"@{$h}[0]");
            }
        }
    };
    &DISPLAY_REPORT3($VAR1,$result_arrayref,$h);
    
    
#EXTRA INFORMATION - project online path
    $VAR1 = { 'sql' => { 'statement' => "select b.online_path from project a, online_data b where a.id_online = b.id_online and a.projectname = '$value'",
                         'fields' =>['Project is Online at'] }};
    
    eval {
        $result_arrayref = $dbh->selectall_arrayref($VAR1->{'sql'}{'statement'});
    };
    &DISPLAY_REPORT3($VAR1,$result_arrayref);
    
    
#EXTRA INFORMATION - finishing direction
    $VAR1 = { 'sql' => { 'statement' => "select finish_towards from finishing_direction where projectname = '$value'",
                         'fields' =>['Finishing Direction'] }};
    eval {
        $result_arrayref = $dbh->selectall_arrayref($VAR1->{'sql'}{'statement'});
    };
    &DISPLAY_REPORT3($VAR1,$result_arrayref);
    
    
#EXTRA INFORMATION - robot information
    $VAR1 = { 'sql' => { 'statement' => "select b.robotname from robot_project a, robot b where a.id_robot = b.id_robot and a.projectname = '$value'",
                         'fields' =>['Robot Used'] }};
    
    eval {
        $result_arrayref = $dbh->selectall_arrayref($VAR1->{'sql'}{'statement'});
    };
    &DISPLAY_REPORT3($VAR1,$result_arrayref,$h);
    
    
#EXTRA INFORMATION - numbers of primers generated 
    $VAR1 = { 'sql' => { 'statement' => "select count(*) from primer a where projectname =  '$value'",
                         'fields' =>['Number of Primers Generated'] }};
    
    eval {
        $result_arrayref = $dbh->selectall_arrayref($VAR1->{'sql'}{'statement'}); 
    };
    &DISPLAY_REPORT3($VAR1,$result_arrayref);
    
    
#EXTRA INFORMATION - batches of finishing reads  
    $VAR1 = { 'sql' => { 'statement' => "select count(*) from finish_batch a where projectname = '$value'",
                         'fields' =>['Batches of Finishing Reads'] }};
    
    eval {
        $result_arrayref = $dbh->selectall_arrayref($VAR1->{'sql'}{'statement'});
    };
    &DISPLAY_REPORT3($VAR1,$result_arrayref);
    
    
##EXTRA EXTRA INFORMATION - blurb  
#    $VAR1 = { 'sql' => { 'statement' => "select c.description, b.media_name, a.archive_text from archived_data a,archive_storage b, mediatypedict c where a.id_archive = b.id_archive and b.media_type = c.id_dict and a.projectname = '$value'" }};
#'fields' =>['Description','Name','Archive'] }};
    
#    eval {
#        $result_arrayref = $dbh->selectall_arrayref($VAR1->{'sql'}{'statement'});
#foreach my $h(@{$result_arrayref}){
#    @{$h}[0] = "This is stored offline on ".@{$h}[0]." ".@{$h}[1]." with the label of ".@{$h}[2];
#    @{$h}=@{$h}[0];
#        }
#    };
#    &DISPLAY_REPORT($VAR1,$result_arrayref);
    
}

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

##############
#            #
#Clone       #
#Report      #
#Information #
#            #
##############

sub VAR_CLONESTATEMENTS{
    
    my ($value)=@_;
    
#CLONE INFORMATION 
    $VAR1 = { 'sql' => { 'statement' => qq(SELECT a.speciesname,h.chromosome, a.sourcename, a.is_hsm, d.description,
					          c.orgtitle, g.orgtitle, e.description, a.libraryname, a.remark, c.www, g.www
					   FROM   clone a, sequencing_org b, organisation c, clonetypedict d,
					          seqreasondict e, funding_org f, organisation g, chromosomedict h
					   WHERE  a.seq_reason   = e.id_dict
					   AND    a.sequenced_by = b.id_org
					   AND    b.id_org       = c.id_org
					   AND    a.funded_by    = f.id_org
					   AND    f.id_org       = g.id_org
					   AND    a.clone_type   = d.id_dict
					   AND    a.chromosome   = h.id_dict (+)
					   AND    a.clonename    = '$value'),
                         'fields' =>['Speciesname','Chromosome','Sourcename','Is tilepath?','Clone type','Sequenced by','Funded by','Sequencing Reason','Library name','Remark'] },
              'text'=>{'title'=>['Clone Information'] }};
    
    eval {
        $result_arrayref = $dbh->selectall_arrayref($VAR1->{'sql'}{'statement'});
        foreach my $h(@{$result_arrayref}){
            if (defined @{$h}[1] && @{$h}[0]=~ /Human/){
                @{$h}[1]=a({href=>"$chromosomepage@{$h}[1]"},"@{$h}[1]");
            }else{
                @{$h}[1]=@{$h}[1];
            }
            if (@{$h}[3]=='1'){
                @{$h}[3]="Yes";
            }elsif (@{$h}[3]=='0'){
                @{$h}[3]="No";}
            else{ undef;}
            
            if (defined @{$h}[10]){
                @{$h}[5]=a({href=>"@{$h}[10]"},"@{$h}[5]");
            }else{
                @{$h}[5]=@{$h}[5];
            }
            
            if (defined @{$h}[11]){
                @{$h}[6]=a({href=>"@{$h}[11]"},"@{$h}[6]");
            }else{
                @{$h}[6]=@{$h}[6];
            }
            @{$h}=@{$h}[0..9] ;  
        }
    };
    
    &DISPLAY_REPORT($VAR1,$result_arrayref,$h);
    
    
#FINGERPRINT PRIORITY
    $VAR1 = { 'sql' => { 'statement' => " select a.assign_date, b.description, a.remark from clone_priority a, cloneprioritydict b where a.priority=b.id_dict and clonename='$value'",
                         'fields' =>['Assign date','Priority description','Remark'] },
              'text'=>{'title'=>['Fingerprint Priority'] }};
    
    eval {
        $result_arrayref = $dbh->selectall_arrayref($VAR1->{'sql'}{'statement'});
    };
    
    &DISPLAY_REPORT($VAR1,$result_arrayref,$h);
    
    
#CLONE STATUS
    $VAR1 = { 'sql' => { 'statement' => " select b.description, a.statusdate, a.iscurrent, a.status from clone_status a, clonestatusdict b where a.status=b.id_dict and a.clonename='$value' order by a.statusdate",
                         'fields' =>['Clone Status','Date','Current Status'] },
              'text'=>{'title'=>['Clone Status'] }};
    
    eval {
        $result_arrayref = $dbh->selectall_arrayref($VAR1->{'sql'}{'statement'});
        foreach my $h(@{$result_arrayref}){
            @{$h}[0]=a({href=>"$listprojaddress?Clonestatus=@{$h}[3]"},"@{$h}[0]"); 
            if (@{$h}[2]=='1'){
                @{$h}[2]=(strong("Present Status"));
            }else{ 
                @{$h}[2]="--";
            }
            @{$h}=@{$h}[0..2] ;  
        }
    };
    
    &DISPLAY_REPORT($VAR1,$result_arrayref,$h);
    
    
#RESTRICTION DIGEST REQUEST INFORMATION
    $VAR1 = { 'sql' => { 'statement' => "select a.id_rdrequest, b.email, a.request_date, nvl(d.description,''), b.id_person, nvl(e.enzymename,'') 
from rdrequest a, person b, rdrequest_status c, rdrequeststatusdict d, rdrequest_enzyme e
where a.requested_by = b.id_person
and a.id_rdrequest = c.id_rdrequest (+)
and c.iscurrent (+) = 1
and c.status  = d.id_dict(+) 
and a.id_rdrequest = e.id_rdrequest (+)
and a.clonename = '$value'",
                         'fields' =>['RD request ID','Requested by','Date requested','Request Status','Enzyme'] },
              'text'=>{'title'=>['Restriction Digest Request Information'] }};
    
    
    eval {
        $result_arrayref = $dbh->selectall_arrayref($VAR1->{'sql'}{'statement'});
        foreach my $h(@{$result_arrayref}){   
            @{$h}[1]=a({href=>"$phonebook?name=@{$h}[1]&action=search"},"@{$h}[1]");
            @{$h}=@{$h}[0..3,5] ;  
        }
    };
    
    &DISPLAY_REPORT($VAR1,$result_arrayref,$h);
    
    
#CLONE OVERLAP INFORMATION
    $VAR1 = { 'sql' => { 'statement' => "select a.clonename, a.overlaps_clone, a.confirmation, a.size_bp, a.variation_check, a.remark from clone_overlaps a where a.clonename='$value' OR a.overlaps_clone='$value' ",
                         'fields' =>['Clonename','Overlaps Clone','Confirmation','Size (bp)','Variation Check','Remark'] },
              'text'=>{'title'=>['Clone Overlap Information'] }};
    
    eval {
        $result_arrayref = $dbh->selectall_arrayref($VAR1->{'sql'}{'statement'});
        foreach my $h(@{$result_arrayref}){
            if (defined @{$h}[2]=="0"){
                @{$h}[2]="Overlap refuted";}
            elsif (@{$h}[2]=="1"){
                @{$h}[2]="Overlap confirmed";}
            else { @{$h}[2]="Not Checked";}
            
            #if (defined @{$h}[4]==" "){
            if (!defined @{$h}[4]){
                @{$h}[4]="Not Checked";}
            elsif (@{$h}[4]=="1"){
                @{$h}[4]="Variation found";}
            else { @{$h}[4]="No variation found";}
            
            @{$h}[0]=a({href=>"$reportaddress?Clonename=@{$h}[0]"},"@{$h}[0]");
            @{$h}[1]=a({href=>"$reportaddress?Clonename=@{$h}[1]"},"@{$h}[1]");
        }
    };
    
    &DISPLAY_REPORT($VAR1,$result_arrayref,$h);
    

#MORE INFORMATION - Contig Location
    $VAR1 = { 'sql' => { 'statement' => " select contigname from clone_contig a where clonename='$value'",
                         'fields' =>['Located on contig'] },
              'text'=>{'title'=>['More Information'] }};
    
    eval {
        $result_arrayref = $dbh->selectall_arrayref($VAR1->{'sql'}{'statement'});
    };
    
    &DISPLAY_REPORT3($VAR1,$result_arrayref,$h); 

   
#MORE INFORMATION - External Clonename
    $VAR1 = { 'sql' => { 'statement' => " select externalname from external_name a where clonename='$value'",
                         'fields' =>['External Clonename'] }};
    
    eval {
        $result_arrayref = $dbh->selectall_arrayref($VAR1->{'sql'}{'statement'});
    };
    
    &DISPLAY_REPORT3($VAR1,$result_arrayref,$h);
        
    
#MORE INFORMATION - Markers
    $VAR1 = { 'sql' => { 'statement' => " select markeralias from clone_marker a where clonename='$value'",
                         'fields' =>['Markers on this clone'] }}; 
    
    eval {
        $result_arrayref = $dbh->selectall_arrayref($VAR1->{'sql'}{'statement'});
    };
    
    &DISPLAY_REPORT3($VAR1,$result_arrayref,$h);
    
    
}


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

#there are 2 subroutines for displaying the report information. The first "DISPLAY_REPORT", displays the data in a tabular format with relevant headings for each column, the second "DISPLAY_REPORT3", diplays the data in row form, with each header forming the left side of each row, with the relevant results on the right.

sub DISPLAY_REPORT {
    
    my ($reports,$result_arrayref,$h)=@_;
    
    my $statement=$reports->{'sql'}{'statement'};
    $statement=~ s/\s+/\t/g;
    my @statement=$statement;
    
    print "<TABLE align=\"center\" width=\"90%\" border=\"1\">";
    if (defined @{$reports->{'text'}{'title'}}&& @{$result_arrayref}){
        print Tr([th({-align=>'left'},[(font{-size=>'3'},@{$reports->{'text'}{'title'}}),a({href=>"$reportaddress?information=@statement" ,target=>"resource window"}, img{border=>'0',src=>'/icons/new.gif',align=>'right'})])]), "\n";
    }else{
        undef;}
    print "</TABLE>";
    
    print "<TABLE align=\"center\" width=\"90%\" border=\"1\">";
    if (defined @{$reports->{'sql'}{'fields'}}&& @{$result_arrayref}){
        print Tr([th({-align=>'center', -bgcolor=>$table_colour},[@{$reports->{'sql'}{'fields'}}])]), "\n";
    }else{
        undef;}
    
    
    foreach my $arrayref(@{$result_arrayref}){
        my @total;
        my $total;    
        push (@total, @{$arrayref});
        $total=@total;
        #print ">>$total";
        my $i;
        for($i=0; $i<$total; $i++){
            @{$arrayref}[$i]= '&nbsp;' unless  @{$arrayref}[$i];
        }
        if (defined $h){
            print $h;
        }
        print Tr([td({-align=>'center', -bgcolor=>$table_colour},[@{$arrayref}])]), "\n";
    }
    print "</TABLE>";
    
}


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


sub DISPLAY_REPORT3 {
    
    my ($reports,$result_arrayref,$h)=@_;
    
    my $statement=$reports->{'sql'}{'statement'};
    $statement=~ s/\s+/\t/g;
    my @statement=$statement;

=pod
 print <<EOS;
<script language="JavaScript1.1">


var myWin
var myWinExist = false
var windowOptions = "toolbar,resizable,scrollbars,width=900,height=450"

function open_win (str) {
  if (!myWinExist || myWin.closed) {
      myWinExist = true;
      myWin = window.open ("", "infowin", windowOptions);
  } else {
      myWin.document.close();
  }
}

self.onerror = null;


</script>
<body onLoad="self.name='browser'; self.focus()">
EOS
=cut 
   
    print "<TABLE align=\"center\" width=\"90%\" border=\"1\">";
    
    if (defined @{$reports->{'text'}{'title'}}&& @{$result_arrayref}){
        print Tr([th({-align=>'left'},[(font{-size=>'3'},@{$reports->{'text'}{'title'}})])]), "\n";
    }else{  
        undef;
    }
    print "</TABLE>";
    
    print "<TABLE align=\"center\" width=\"90%\" border=\"1\">";
    if (defined @{$result_arrayref}){
        foreach my $arrayref(@{$result_arrayref}){
            my @total;
            my $total;    
            push (@total, @{$arrayref});
            $total=@total;
            #print ">>$total";
            my $i;
            for($i=0; $i<$total; $i++){
                @{$arrayref}[$i]= '&nbsp;' unless  @{$arrayref}[$i];
                if (defined $h){
                    print $h;
                }
                print Tr([td({-align=>'center', -width=>'30%,*%',-bgcolor=>$table_colour},[a({href=>"$reportaddress?information=@statement", target=>"resource window", STYLE=>"text-decoration:none"}, img{border=>'0',src=>'/icons/new.gif', align=>'left',hspace=>'0'},font({color=>"#000000"},strong(@{$reports->{'sql'}{'fields'}}[$i]))),@{$arrayref}[$i]])],), "\n"; 
            }
        }
    }else{
        undef;
    }
    print "</TABLE>";

}

#this diplays the SQL tables which are involved in constructing the various sections of the table.Each table is obtained by dissecting each SQL statement and extracting the relevant from statement, and using that in an SQL statement to extract everything about a table.
sub TABLE_INFORMATION {
    
    my ($value)=@_;
    
    my $statement;
    my @statement;
    $statement=$value;
    if ($statement=~ /from/i../where/i){
        $statement=~ s/\S*from//gi;
        $statement=~ s/where\S*//gi;
        @statement=split(/,/,$statement);
        
        print <<EOS;
        <IMG SRC="/icons/new.gif" align="LEFT"><STRONG><FONT SIZE="6">SQL Table Information</FONT></STRONG>
EOS
    
    
    print "<H5>The Tables below are those involved in the SQL for this section of the reports table. Each table involved is described with it's table description and the columns which make up the table, information of their column name and description is also provided.</H5>";
        
        print "<BR>";
        
        foreach my $table(@statement){
            $table=~ s/\S$//gi;
            $table=uc($table);
            
            $sqlstatement = "select comments from all_tab_comments where table_name='$table'";
            my $a_arrayref = $dbh->selectall_arrayref($sqlstatement) || die print "cannot execute statement: $DBI::errstr";   
            foreach my $a(@{$a_arrayref}){
                print strong("$table-@{$a}\n");
                print "<BR>";
            }
            
            $sqlstatement = "select column_name,comments from all_col_comments where table_name='$table'";
            my $h_arrayref = $dbh->selectall_arrayref($sqlstatement) || die print "cannot execute statement: $DBI::errstr";   
            
            print "<TABLE BORDER=\"3\" WIDTH=\"90%\" ALIGN=\"center\" >";
            
            foreach my $arrayref(@{$h_arrayref}){
                
                my @total;
                my $total;    
                push (@total, @{$arrayref});
                $total=@total;
                my $i;
                for($i=0; $i<$total; $i++){
                    @{$arrayref}[$i]= '&nbsp;' unless  @{$arrayref}[$i];
                }
                print  Tr([td({-align=>'left'},[@{$arrayref}])]),"\n";
                
            }
            print "</TABLE>"; 
            print "<BR>";
        }
    }
    
}


__END__



#print <<EOS;
 #       <FORM>
  #                             <INPUT TYPE="button" VALUE="Close Window"
   #                            onClick="EXIT(0)">
    #                           </FORM>
#EOS


#sub DISPLAY_REPORT2 {
    
 #   my ($reports,$result_arrayref,$h)=@_;
    
  #  print "<TABLE align=\"center\" width=\"90%\" border=\"1\">";
   # if (defined @{$reports->{'text'}{'title'}}&& @{$result_arrayref}){
    #    print Tr([th({-align=>'left'},[(font{-size=>'3'},@{$reports->{'text'}{'title'}})])]), "\n";
   # }else{
    #    undef;}
    #print "</TABLE>";
    
    #print "<TABLE align=\"center\" width=\"90%\" border=\"1\">";
    #if (defined @{$result_arrayref}){
     #   foreach my $arrayref(@{$result_arrayref}){
      #      my @total;
       #     my $total;    
        #    push (@total, @{$arrayref});
         #  $total=@total;
            #print ">>$total";
          #  my $i;
          #  for($i=0; $i<$total; $i++){
          #      @{$arrayref}[$i]= '&nbsp;' unless  @{$arrayref}[$i];
          #  }
            
          #  print $h;
          #  print Tr([td({-align=>'center', -width=>'45%',-bgcolor=>$table_colour},[@{$reports->{'sql'}{'fields'}},@{$arrayref}])]), "\n";
        #}
   # }else{
    #    undef;
    #}
    #print "</TABLE>";
    
#}


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