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

###########################################################################
#                                                                         #
#   The script count up the current projects or clones for each status.   #
#                                                                         #
#   Author : Karen Ambrose                                                #
#                                                                         #
###########################################################################
#
# $Id: status.pl,v 1.18 2006/11/03 15:34:01 js10 Exp $
#

$|=1;

#set up the components needed.
use SangerPaths qw(core badger);
use SangerWeb;
use WrapDBI;
use strict;
use CGI qw(:all :html3);
use HTTP::Cookies;
use CGI::Cookie;
use SqlEngine2;

use Data::Dumper;

#Declare variables.
my $db;
my $DBI;
my $dbh;
my $sqlstatement;
my $sth;
my $table_colour = '#E2E2FF'; #table colour
my $cgititle1= 'Project List Totals';
my $cgititle2= 'Clone List Totals';
my $cgititle3= 'Breakdown By Chromosome';
my $cgititle4= 'Breakdown By Organism';
my $cgititle5= 'Breakdown By Teams';
my $cgiauthor='kva';
my $listprojaddress='/cgi-bin/oracle_reports/projstatusclones.pl'; #displays the projects or clones at a given status.

my $VAR1;
my $script_name=$ENV{'SCRIPT_NAME'}; #holds the name of the present script.
my $name;
my $chromo;
my $org;
my $team;
my $value;
my $h_arrayref;

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

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

# The different sections handle the different 
# parameters that are sent to this script.
if (param("Projectname")){
   
    &outputheader($cgititle1); 
    
    my $query=CGI::new();
    $value = $query ->param("Projectname");
    
    &COOKIE_INFO($value);

    #passes the name 'projectname' to be printed out 
    #in the title.
    $name=param("Projectname");

    &NAVIGATOR($name);
    &HEADER($name);
    &STATUSES($value);
    &CHOICES($value);
}

if (param("Clonename")){
    
    &outputheader($cgititle2);

    my $query=CGI::new();
    $value = $query ->param("Clonename");
    &COOKIE_INFO($value);

    #passes the name 'clonename' to be printed out 
    #in the title.
    $name=param("Clonename");

    &NAVIGATOR($name);
    &HEADER($name);       
    &STATUSES($value);
    &CHOICES($value);    
}

if (param("Chromosome")){
    
    &outputheader($cgititle3);

    my $query=CGI::new();
    $chromo = $query ->param("Chromosome");
    $name = $query ->param("ID");
    &NAVIGATOR($name);
    &Chromo_list($chromo,$name);
}

if (param("Organism")){

    &outputheader($cgititle4);

    my $query=CGI::new();
    $org = $query ->param("Organism");
    $name = $query ->param("ID");
    &NAVIGATOR($name);
    &Organism_list($org,$name);    
}

if (param("Teamname")){
 
    &outputheader($cgititle5);  
    
    my $query=CGI::new();
    $team = $query ->param("Teamname");
    $name = $query ->param("ID");
    &NAVIGATOR($name);
    &Team_list($team,$name);
}


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

#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;
}


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


sub outputheader {

    my ($cgititle)=@_;

    print &SangerWeb::header({
        'title'=> $cgititle
        });
}

sub outputfooter {

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

#houses the cookie information.
sub COOKIE_INFO{

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

#formats the navigational bar,
sub NAVIGATOR{

    my ($name)=@_;
    my $pairs;
    my $title;
    my $headertitle;
    
    if ($name=~ /Projectname/){
        $name="projects";
        $pairs="Clonename=Clonename";
        $title="Clone List Totals";
        $headertitle="Project List Totals";
        
    }else{
        $name="clones";
        $pairs="Projectname=Projectname";
        $title="Project List Totals"; 
        $headertitle="Clone List Totals";
        
    }
    
    print "<TABLE BORDER=\"0\" HEIGHT=\"10\"  WIDTH=\"90%\"  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?$pairs"},"$title"),a({href=>"http://intweb.sanger.ac.uk/cgi-bin/oracle_reports/reportindex.pl"},"Reports Index"),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#Project/Clone List Totals"},"Help")])]);
    print "</TABLE>";
}



#formats blurb information for the top of the page and the table headings.
sub HEADER{
    
    my ($name)=@_;

    if ($name=~ /Projectname/){ 
        print "<BR><BR><CENTER><STRONG><FONT SIZE=\"6\">Project List Totals</FONT></STRONG></CENTER><BR>";
        print "<P><FONT SIZE=\"4\"><CENTER>The information details the number of projects currently present at each status.\n </CENTER></FONT></P>";
        print "<BLOCKQUOTE><FONT SIZE=\"2\"><CENTER>Any status's not shown are due to the fact there are no projects listed at that status, at this present time. More options are available at the bottom of this table. The informaton presented can be brokendown by teamname, by chromosome or by organisms. Select the teamname,chromosome or organism you would like information on and then press the related button to return the information\n </CENTER></FONT></BLOCKQUOTE>";
        print"<TABLE BORDER=\"1\" WIDTH=\"80%\" ALIGN=\"center\">";
        print Tr([th({-align=>'center', -bgcolor=>'#E2E2FF', -height=>'30'},['Project Status','Number of Projects'])]), "\n";

    }else{

        print "<BR><BR><CENTER><STRONG><FONT SIZE=\"6\">Clone List Totals</FONT></STRONG></CENTER><BR>";
        print "<P><FONT SIZE=\"4\"><CENTER>The information details the number of clones currently present at each status.\n </CENTER></FONT></P>";
        print "<BLOCKQUOTE><FONT SIZE=\"2\"><CENTER>Any status's not shown are due to the fact there are no projects listed at that status, at this present time. More options are available at the bottom of this table. The informaton presented can be brokendown by teamname, by chromosome or by organisms. Select the teamname,chromosome or organism you would like information on and then press the related button to return the information\n </CENTER></FONT></BLOCKQUOTE>";
        print"<TABLE BORDER=\"1\" WIDTH=\"80%\" ALIGN=\"center\">";
        print Tr([th({-align=>'center', -bgcolor=>'#E2E2FF', -height=>'30'},['Clone Status','Number of Clones'])]), "\n";
    }
      
}


#provides the count for all the current projeccts or clones at their respective status's
sub STATUSES{
    
    my ($value)=@_;
    
    my $statusdict;
    my $statustype;
    my $paramstatus;
    
    if ($value =~ /Projectname/){          
        $paramstatus="Projectstatus";
        $sqlstatement="select c.description,count(a.projectname),b.status,c.assign_order from clone_project a, project_status b, projectstatusdict c where a.projectname=b.projectname and b.status=c.id_dict and b.iscurrent='1' group by c.description,b.status,c.assign_order order by c.assign_order";
        
    }
    else{ 
        $paramstatus="Clonestatus";
        $sqlstatement="select c.description,count(a.clonename),b.status,c.assign_order from clone_project a, clone_status b, clonestatusdict c where a.clonename=b.clonename and b.status=c.id_dict and b.iscurrent='1' group by c.description,b.status,c.assign_order order by c.assign_order";
        
    }
    
    $h_arrayref = $dbh->selectall_arrayref($sqlstatement) || die "cannot execute statement:$DBI::errstr\n";
    
    foreach my $arrayref(@{$h_arrayref}){
        @{$arrayref}[0]=a({href=>"$listprojaddress?$paramstatus=@{$arrayref}[2]"},"@{$arrayref}[0]");
        @{$arrayref}=@{$arrayref}[0,1];
        print Tr([td({-align=>'center'},[@{$arrayref}])]);  
    } 

    print"</TABLE>";
}


sub CHOICES{
    my ($value)=@_;

    if ($value=~ /Clonename|Projectname/){
       
        print "<BR><BR>";
        print "<H4><CENTER>Further breakdown by teamname, by chromosome or by organism</CENTER></H4>";
        
        print<<EOS;
        <table ALIGN="CENTER" width="50%"><tr>
            <td align="center">
                <FORM NAME="Teamname DropDown" method="POST" action="$script_name">
                    <SELECT NAME="Teamname" size="1">
                        <OPTION SELECTED>Choose a Teamname

EOS

 my @teamnames;
        $sqlstatement="select a.teamname, b.description from team a, teamtypedict b where a.teamtype=b.id_dict group by a.teamname, b.description order by a.teamname";
        
        $h_arrayref = $dbh->selectall_arrayref($sqlstatement) || die "cannot execute statement:$DBI::errstr\n";
        foreach my $teams(@{$h_arrayref}){
            @teamnames=@{$teams};
            print "<OPTION VALUE='@teamnames'>@teamnames";
        }
        
        print<<EOS;

        </SELECT>
            <INPUT TYPE="submit" VALUE="By Teamname" class="blue1">
                <input type="hidden" name="ID" value="$value">
                </form>                
                    </td>
                        </tr>

<tr>
            <td align="center">
                <FORM NAME="Chromosome DropDown" method="POST" action="$script_name">
                    <SELECT NAME="Chromosome" size="1">
                        <OPTION SELECTED>Choose a Chromosome
EOS
                                
    my @chromosomes;
        $sqlstatement="select a.description from chromosomedict a, clone b where a.id_dict=b.chromosome group by a.description order by a.description";
        
        $h_arrayref = $dbh->selectall_arrayref($sqlstatement) || die "cannot execute statement:$DBI::errstr\n";
        foreach my $chromosomes(@{$h_arrayref}){
            @chromosomes=@{$chromosomes};
            print "<OPTION VALUE='@chromosomes'>@chromosomes";
        }
        
        print<<EOS;
        </SELECT>
            <INPUT TYPE="submit" VALUE="By Chromosome" class="blue1">
                <input type="hidden" name="ID" value="$value">
                </form>                
                    </td>
                        </tr>

                            <tr>
                                <td align="center">        
            <FORM NAME="Organism DropDown" method="POST" action="$script_name">
                <SELECT NAME="Organism" size="1">
                    <OPTION SELECTED>Choose an Organism
EOS
                            
    my @organisms;
        $sqlstatement="select speciesname from clone group by speciesname order by speciesname"; 
        
        $h_arrayref = $dbh->selectall_arrayref($sqlstatement) || die "cannot execute statement:$DBI::errstr\n";
        foreach my $organisms(@{$h_arrayref}){
            @organisms=@{$organisms};
            print "<OPTION VALUE='@organisms'>@organisms";
    }
        
        print<<EOS;
        <OPTION VALUE="All">All
            </SELECT>
                <INPUT TYPE="submit" VALUE="By Organism" class="blue1">
                    <input type="hidden" name="ID" value="$value">
                    </form>
                        </td>
                            </tr>
                                </table>
                
EOS
}else{
    print "<BR>";
    print "<center>No Extra options for now</center>";
}
    
  
}



sub Chromo_list{
    
    my ($chromo,$name)=@_;
    
    if ($name=~ /Projectname/){
        print "<H4><CENTER>Project list Totals by Chromosome - $chromo</CENTER></H4>";
 #       print "<H4><CENTER><FONT COLOR=\"red\">This page is under alteration, please do not use for any statistical information at the moment. Thankyou!!</FONT></CENTER></H4>";
    }else{
        print "<H4><CENTER>Clone list Totals by Chromosome - $chromo</CENTER></H4>";
#print "<H4><CENTER><FONT COLOR=\"red\">This page is under alteration, please do not use for any statistical information at the moment. Thankyou!!</FONT></CENTER></H4>";
    }
    

my $sql1;
    
    if ($name=~ /Projectname/){

        $sql1="select a.speciesname,a.description chrom,e.description status, b.chromosome, d.status, e.assign_order,count(d.projectname) num_clones from chromosomedict a,clone b, clone_project c, project_status d, projectstatusdict e where a.description='$chromo' and a.id_dict = b.chromosome and b.clonename = c.clonename and c.projectname = d.projectname and d.status = e.id_dict and d.iscurrent ='1' group by a.speciesname,a.description,e.description,b.chromosome,d.status,e.assign_order order by a.speciesname,a.description,e.assign_order";
        
        $sqlstatement="select f.teamname, a.speciesname,a.description chrom,e.description status, b.chromosome, d.status, e.assign_order, count(d.projectname) num_clones from chromosomedict a, clone b, clone_project c, project_status d, projectstatusdict e, project_owner f where a.description='$chromo' and a.id_dict = b.chromosome and b.clonename = c.clonename and c.projectname = d.projectname and d.status = e.id_dict and d.iscurrent ='1' and d.projectname=f.projectname(+) and f.iscurrent (+)= '1' group by a.speciesname,a.description,e.description,b.chromosome,d.status,e.assign_order,f.teamname order by a.speciesname,a.description,e.assign_order";
        
        
    }else{

        $sql1="select a.speciesname,a.description,d.description status, b.chromosome, c.status, d.assign_order,count(e.clonename) num_clones from chromosomedict a,clone b, clone_status c, clonestatusdict d, clone_project e where a.description='$chromo' and a.id_dict = b.chromosome and b.clonename = c.clonename and c.status = d.id_dict and c.iscurrent ='1' and e.clonename=b.clonename group by a.speciesname,a.description,d.description,b.chromosome,c.status,d.assign_order order by a.speciesname,a.description,d.assign_order";
        
        $sqlstatement="select f.teamname, a.speciesname,a.description,d.description status, b.chromosome, c.status, d.assign_order,count(e.clonename) num_clones from chromosomedict a,clone b, clone_status c, clonestatusdict d, clone_project e, project_owner f where a.description='$chromo' and a.id_dict = b.chromosome and b.clonename = c.clonename and c.status = d.id_dict and c.iscurrent ='1' and e.clonename=b.clonename and e.projectname=f.projectname (+) and f.iscurrent(+)='1' group by f.teamname,a.speciesname,a.description,d.description,b.chromosome,c.status,d.assign_order order by a.speciesname,a.description,d.assign_order";

    }

    my $h_ref1 = $dbh->selectall_arrayref($sql1) || die "cannot execute statement:$DBI::errstr\n";

    $h_arrayref = $dbh->selectall_arrayref($sqlstatement) || die "cannot execute statement:$DBI::errstr\n";
    

    print "<br>";
    print "<TABLE border=\"1\" width=\"80%\" align=\"CENTER\">";
    
    if (defined @{$h_ref1}){    
        if ($name=~ /Projectname/){
            print Tr([th({-align=>'center', -bgcolor=>'#E2E2FF', -height=>'30'},['Organism','Project Status','Number of Projects'])]);
        }else{
            print Tr([th({-align=>'center', -bgcolor=>'#E2E2FF', -height=>'30'},['Organism','Clone Status','Number of Clones'])]);
        }
    }
    
    foreach my $ref(@{$h_ref1}){
        
        my $count=@{$ref};    
        my $i;
        for ($i=0; $i<$count; $i++){
            @{$ref}[$i]='&nbsp;' unless @{$ref}[$i];       
        }

        #this is the ensure that the listed data, is for the
        #table which dosen't have the team breakdown.
        my $obs='222';

        @{$ref}[6]=a({href=>"$listprojaddress?Chromosome=@{$ref}[3]&organism=@{$ref}[0]&status=@{$ref}[4]&total=@{$ref}[6]&name=$name&team=$obs"},"@{$ref}[6]");
        @{$ref}=@{$ref}[0,2,6];
        print Tr([td({-align=>'center'},[@{$ref}])]),"\n"; 
    } 
    if (!defined @{$h_ref1}){
        my $noentry= "No information found for $chromo";
        print Tr([td({-align=>'center'},[$noentry])]), "\n";  
    }     
    print"</TABLE>";
    print "<BR>";



    print "<br>";
    print "<br>";
    print "<FONT SIZE=\"4\"><CENTER>The table below provides the statistics for this chromosome with the team breakdown</CENTER></FONT>"; 
    print "<br>"; 
    print "<TABLE border=\"1\" width=\"80%\" align=\"CENTER\">";
    
    if (defined @{$h_arrayref}){    
        if ($name=~ /Projectname/){
            print Tr([th({-align=>'center', -bgcolor=>'#E2E2FF', -height=>'30'},['Teamname','Organism','Project Status','Number of Projects'])]);
        }else{
            print Tr([th({-align=>'center', -bgcolor=>'#E2E2FF', -height=>'30'},['Teamname','Organism','Clone Status','Number of Clones'])]);
        }
    }
    
    foreach my $arrayref(@{$h_arrayref}){

        my $count=@{$arrayref};    
        my $i;
        for ($i=0; $i<$count; $i++){
            @{$arrayref}[$i]='&nbsp;' unless @{$arrayref}[$i];       
        }
        @{$arrayref}[7]=a({href=>"$listprojaddress?Chromosome=@{$arrayref}[4]&organism=@{$arrayref}[1]&status=@{$arrayref}[5]&total=@{$arrayref}[7]&name=$name&team=@{$arrayref}[0]"},"@{$arrayref}[7]");
        @{$arrayref}=@{$arrayref}[0,1,3,7];
print Tr([td({-align=>'center'},[@{$arrayref}])]),"\n"; 
    } 
    if (!defined @{$h_arrayref}){
        my $noentry= "No information found for $chromo";
        print Tr([td({-align=>'center'},[$noentry])]), "\n";  
    }     
    print"</TABLE>";
    print "<BR>";
    






 
}


sub Organism_list{

    my ($org,$name)=@_;
	my $clonesQ;
	my $cloneList;

    if ($name=~ /Projectname/){
        print "<H4><CENTER>Project list Totals by Organism - $org</CENTER></H4>";
    }else{
        print "<H4><CENTER>Clone list Totals by Organism - $org</CENTER></H4>";
    }
    
    if ($name=~ /Projectname/){
        
        if ($org=~ /All/){
$VAR1={'sql'=>{ 'statement' => "select a.speciesname,b.description,e.description,a.chromosome,d.status,e.assign_order,count (d.projectname) from clone a, chromosomedict b, clone_project c,project_status d,projectstatusdict e where a.chromosome=b.id_dict (+) and a.clonename=c.clonename and c.projectname=d.projectname and d.status=e.id_dict and d.iscurrent='1' group by a.speciesname,b.description,e.description,a.chromosome,d.status,e.assign_order order by a.speciesname,b.description,e.assign_order",
                'fields'=>['Organism','Chromosome','Project Status','Number of Projects']}};
			$clonesQ = $se->virtualSqlLib('status', 'getClones1',
			                              "select hd.name, count(*)
			                               from clone a, clone_project c, project_status d,
			                                    htgsphasedict hd, sequence s, clone_sequence cs
			                               where a.clonename=c.clonename 
			                               and c.projectname=d.projectname 
			                               and cs.clonename = a.clonename
			                               and cs.is_current = 1
			                               and s.id_sequence = cs.id_sequence
			                               and s.id_htgsphase = hd.id_htgsphase
			                               and a.speciesname = ?
			                               and d.status =  ?
			                               and a.chromosome = ?
			                               and d.iscurrent=1
			                               group by hd.name");
            
        }else{
$VAR1={'sql'=>{ 'statement' => "select /*+ INDEX(d) */ a.speciesname,b.description,e.description,a.chromosome,d.status,e.assign_order,count (d.projectname) from clone a, chromosomedict b, clone_project c,project_status d,projectstatusdict e where a.chromosome=b.id_dict (+) and a.clonename=c.clonename and c.projectname=d.projectname and d.status=e.id_dict and a.speciesname='$org' and d.iscurrent='1' group by a.speciesname,b.description,e.description,a.chromosome,d.status,e.assign_order order by a.speciesname,b.description,e.assign_order",
         'fields'=>['Chromosome','Project Status','Number of Projects']}};       
			$clonesQ = $se->virtualSqlLib('status', 'getClones2',
			                              "select hd.name, count(*)
			                               from clone a, 
			                                    chromosomedict b, 
			                                    clone_project c,
			                                    project_status d,
			                                    projectstatusdict e,
			                                    htgsphasedict hd, sequence s, clone_sequence cs
			                               where a.chromosome=b.id_dict (+) 
			                               and a.clonename=c.clonename 
			                               and c.projectname = d.projectname
			                               and d.status=e.id_dict 
			                               and cs.clonename = a.clonename
			                               and cs.is_current = 1
			                               and s.id_sequence = cs.id_sequence
			                               and s.id_htgsphase = hd.id_htgsphase
			                               and a.speciesname=? 
			                               and d.iscurrent='1' 
			                               and d.status = ?
			                               and a.chromosome = ?
			                               group by hd.name");
		}
    }else{        
        
        if ($org=~ /All/){
$VAR1={'sql'=>{ 'statement' => "select a.speciesname,b.description,d.description,a.chromosome,c.status,d.assign_order,count (e.clonename) from clone a, chromosomedict b, clone_status c,clonestatusdict d,clone_project e where a.chromosome=b.id_dict (+) and a.clonename=c.clonename and a.clonename=e.clonename and c.clonename=e.clonename and c.status=d.id_dict and c.iscurrent='1' group by a.speciesname,b.description,d.description,a.chromosome,c.status,d.assign_order order by a.speciesname,b.description,d.assign_order",
           'fields'=>['Organism','Chromosome','Clone Status','Number of Clones']}};     
			$clonesQ = $se->virtualSqlLib('status', 'getClones3',
			                              "select hd.name, count(*)
			                               from clone a, clone_status c, 
			                                    htgsphasedict hd, sequence s, clone_sequence cs
			                               where a.clonename=c.clonename 
			                               and c.iscurrent='1' 
			                               and cs.clonename = a.clonename
			                               and cs.is_current = 1
			                               and s.id_sequence = cs.id_sequence
			                               and s.id_htgsphase = hd.id_htgsphase
			                               and a.speciesname = ?
			                               and c.status=? 
			                               and a.chromosome = ?
			                               group by hd.name");
            
        }else{
$VAR1={'sql'=>{ 'statement' => "select a.speciesname,b.description,d.description,a.chromosome,c.status,d.assign_order,count (e.clonename) from clone a, chromosomedict b, clone_status c,clonestatusdict d,clone_project e where a.chromosome=b.id_dict (+) and a.clonename=c.clonename and a.clonename=e.clonename and c.clonename=e.clonename and c.status=d.id_dict and a.speciesname='$org' and c.iscurrent='1' group by a.speciesname,b.description,d.description,a.chromosome,c.status,d.assign_order order by a.speciesname,b.description,d.assign_order",
       'fields'=>['Chromosome','Clone Status','Number of Clones']}};         
			$clonesQ = $se->virtualSqlLib('status', 'getClones4',
			                              "select hd.name, count(*)
			                               from clone a, 
			                                    clone_status c,
			                                    clone_project e,
			                                    htgsphasedict hd, sequence s, clone_sequence cs
			                               where a.clonename=c.clonename 
			                               and a.clonename=e.clonename 
			                               and c.clonename=e.clonename 
			                               and cs.clonename = a.clonename
			                               and cs.is_current = 1
			                               and s.id_sequence = cs.id_sequence
			                               and s.id_htgsphase = hd.id_htgsphase
			                               and a.speciesname=? 
			                               and c.status = ?
			                               and a.chromosome = ?
			                               and c.iscurrent='1'
			                               group by hd.name");

        }
    }
    $h_arrayref = $dbh->selectall_arrayref($VAR1->{'sql'}{'statement'}) || die "cannot execute statement:$DBI::errstr\n"; 
    my %ORD_HASH;
    my %STAT_HASH;
    foreach my $each (@{$h_arrayref}){
        unless ($ORD_HASH{@$each[2]}){
            $ORD_HASH{@$each[2]} = @$each[-2];
	    $STAT_HASH{@$each[2]} = @$each[4];
        }  
    }

    my %SUM_HASH;
    foreach my $each (@{$h_arrayref}){
        push (@{$SUM_HASH{@$each[2]}}, @$each[-1]);  
    }
    my %NEW_HASH;
    foreach my $keys (keys %SUM_HASH){
        my $total;
        foreach my $values (@{$SUM_HASH{$keys}}){
            $total +=$values;
        }
        $NEW_HASH{$keys}=$total;
    }
    
#print STDOUT "----------------------------------------->\n";
#print STDOUT Data::Dumper::Dumper(\%NEW_HASH);
#print STDOUT "----------------------------------------->\n";

print "<TABLE border=\"1\" width=\"70%\" align=\"CENTER\">";

    if (defined @{$h_arrayref}){    
		print Tr([th({-align=>'center', -bgcolor=>'#E2E2FF', -height=>'30'},[@{$VAR1->{'sql'}{'fields'}}, 'Unfinished', 'Contiguous', 'Finished'])]);
    } 

    foreach my $arrayref(@{$h_arrayref}){
		my ($organism, $status, $chromosome);
		my @count = ('-','-','-');
		if ($clonesQ) {
			$status = @{$arrayref}[4];
			$chromosome = @{$arrayref}[3];
			if ($org =~ 'All') { $organism = @{$arrayref}[0]; }
			else               { $organism = $org; }
			my $res = $se->getAll($clonesQ, [$organism, $status, $chromosome], $seDbh);
#			$cloneList = $se->getAll($clonesQ, [$organism, $status, $chromosome], $seDbh);
#			my @cloneArray;
#			foreach my $c (@$cloneList) { push @cloneArray, $c->[0]; }
#			my $res = $se->getAllDynamic('reports::getPhaseCount', [], [@cloneArray], [], $mysqlDbh);
			my $n=0;
			foreach my $r (@$res) {
				$count[0] = $r->[1] if ($r->[0] eq 'Unfinished');
				$count[1] = $r->[1] if ($r->[0] eq 'Contiguous');
				$count[2] = $r->[1] if ($r->[0] eq 'Finished');
#$count[0]="$r->[0] $r->[1]";
			}
		}

        if (! defined @{$arrayref}[3]){
            @{$arrayref}[3]="NO_CHROMO";
        }
        
        my $count=@{$arrayref};    
        my $i;
        for ($i=0; $i<$count; $i++){
            @{$arrayref}[$i]='&nbsp;' unless @{$arrayref}[$i];       
        } 
        if ($org=~ /All/){

            if (@{$arrayref}[0]=~ /\s+/){
                @{$arrayref}[0]=~ s/\s+/\+/;
            }

            @{$arrayref}[6]=a({href=>"$listprojaddress?Organism=@{$arrayref}[0]&&chromosome=@{$arrayref}[3]&status=@{$arrayref}[4]&total=@{$arrayref}[6]&name=$name"},"@{$arrayref}[6]");
            @{$arrayref}=@{$arrayref}[0..2,6];

        }else{
            
            if ($org=~ /\s+/){
                $org=~ s/\s+/\+/;
            }

            @{$arrayref}[6]=a({href=>"$listprojaddress?Organism=$org&chromosome=@{$arrayref}[3]&status=@{$arrayref}[4]&total=@{$arrayref}[6]&name=$name"},"@{$arrayref}[6]");
            @{$arrayref}=@{$arrayref}[1,2,6];
        }

@{$arrayref}[0]=~ s/\+/ /;
print Tr([td({-align=>'center'},[@{$arrayref}, @count])]),"\n";   
}
 
    if (!defined @{$h_arrayref}){
        my $noentry= "No information found for Organism $org";
        print Tr([td({-align=>'center'},[$noentry])]), "\n";  
    }     
    print"</TABLE>";
    print "<BR>";
    print "<BR>";
    print "<BR>";
    
    print "<H4><CENTER>SUMMARY INFORMATION</CENTER></H4>";
    print "<BR>";
    print "<TABLE border=\"1\" width=\"70%\" align=\"CENTER\">";
if ($name=~ /Projectname/){
    print "<TR><TH bgcolor=\"#E2E2FF\", height=\"30\">STATUS</TH><TH bgcolor=\"#E2E2FF\", height=\"30\">Number of projects</TH></TR>";
} else {
    print "<TR><TH bgcolor=\"#E2E2FF\", height=\"30\">STATUS</TH><TH bgcolor=\"#E2E2FF\", height=\"30\">Number of clones</TH></TR>";
}
    foreach my $okeys (sort {$ORD_HASH{$a} cmp $ORD_HASH{$b}} keys %ORD_HASH) {
        my $val = $NEW_HASH{$okeys};
	 my $ostatus = $STAT_HASH{$okeys};
	$val=a({href=>"$listprojaddress?Organism=$org&status=$ostatus&total=$val&name=$name"},"$val");
        print Tr([td({-align=>'center'},[$okeys,$val])]);
    }
    print"</TABLE>";
    print "<BR>";
    
}


sub Team_list{

    my ($team,$name)=@_;
    
    if ($name=~ /Projectname/){
        print "<H4><CENTER>Project list Totals by Team - $team</CENTER></H4>";
    }else{
        print "<H4><CENTER>Clone list Totals by Team - $team</CENTER></H4>";
    }
    
$team=~ /(\d+)\s+\w+/;
my $t=$1;

    if ($name=~ /Projectname/){
        
$VAR1={'sql'=>{ 'statement' => "select c.description,b.status,c.assign_order,count(b.projectname) 
from clone_project a,project_status b,projectstatusdict c,project_owner d 
where a.projectname=b.projectname and 
b.status=c.id_dict and 
a.projectname=d.projectname and 
b.iscurrent='1' and 
d.iscurrent='1' and 
d.teamname='$t' 
group by c.description,b.status,c.assign_order 
order by c.assign_order",
         'fields'=>['Project Status','Number of Projects']}};       
       
    }else{        
    
$VAR1={'sql'=>{ 'statement' => "select c.description,b.status,c.assign_order,count (a.clonename) 
from clone_project a, clone_status b, clonestatusdict c, project_owner d
where a.clonename=b.clonename and 
b.status=c.id_dict and 
a.projectname=d.projectname and 
b.iscurrent='1' and 
d.iscurrent='1' and 
d.teamname='$t'
group by c.description,b.status,c.assign_order
order by c.assign_order",
       'fields'=>['Clone Status','Number of Clones']}};         
       
    }
    $h_arrayref = $dbh->selectall_arrayref($VAR1->{'sql'}{'statement'}) || die "cannot execute statement:$DBI::errstr\n"; 
    print "<TABLE border=\"1\" width=\"70%\" align=\"CENTER\">";
    
    if (defined @{$h_arrayref}){    
        if ($name=~ /Projectname/){
            
             print Tr([th({-align=>'center', -bgcolor=>'#E2E2FF', -height=>'30'},[@{$VAR1->{'sql'}{'fields'}}])]);

        }else{

            print Tr([th({-align=>'center', -bgcolor=>'#E2E2FF', -height=>'30'},[@{$VAR1->{'sql'}{'fields'}}])]);
       
        }
    } 

    foreach my $arrayref(@{$h_arrayref}){
        
        my $count=@{$arrayref};    
        my $i;
        for ($i=0; $i<$count; $i++){
            @{$arrayref}[$i]='&nbsp;' unless @{$arrayref}[$i];       
        } 

           @{$arrayref}[3]=a({href=>"$listprojaddress?Teamname=$t&&status=@{$arrayref}[1]&total=@{$arrayref}[3]&name=$name"},"@{$arrayref}[3]");

            @{$arrayref}=@{$arrayref}[0,3];
        
        print Tr([td({-align=>'center'},[@{$arrayref}])]),"\n";   
    } 
    if (!defined @{$h_arrayref}){
        my $noentry= "No information found for Teamname $team";
        print Tr([td({-align=>'center'},[$noentry])]), "\n";  
    }     
    print"</TABLE>";
    print "<BR>";
    
    
}



__END__
    



