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

###########################################################################
#                                                                         #
#   The script lists and displays the information of the relative clones  #
#   or projects at a particular status.                                   #
#   The data is paginated in rows of 30.                                  #
#                                                                         #
#   Author : Karen Ambrose                                                #
#                                                                         #
###########################################################################
#
# $Id: projstatusclones.pl,v 1.17 2006/11/03 15:07:41 js10 Exp $
#
# $Log: projstatusclones.pl,v $
% Revision 1.17  2006/11/03  15:07:41  js10
% Added EMBL status column
%
#

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

#Declare variables.
my $db;
my $DBI;
my $dbh;
my $sqlstatement;
my $sth;
my $cgititle1= 'Projects';
my $cgititle2= 'Clones';
my $cgititle3= 'Projects - By Organism';
my $cgititle4= 'Clones - By Organism';
my $cgititle5= 'Projects - By Chromosome';
my $cgititle6= 'Clones - By Chromosome';
my $cgititle7= 'Projects - By Teams';
my $cgititle8= 'Clones - By Teams';
my $cgiauthor='kva';
my $reportaddress='/cgi-bin/oracle_reports/report.pl'; #displays the clone or project report information.
my $webaceaddress='http://webace.sanger.ac.uk/cgi-bin/webace';
my $table_colour = '#E2E2FF'; #table colour.

my $status;
my $list=0;
my $listinfo=0;
my ($value, $chromo, $state_num, $total, $name, $value2, $value3, $label, $sort);
my $title;
my $team;
my $nametitle;
my $nametitle2;

my $script_name=$ENV{'SCRIPT_NAME'}; #holds the name of the present script.

my $se = new SqlEngine2;
my $seDbh = SqlEngine2::getDbh('reports');
my $getEmblStatusQuery;

#Names and connects to the database 'reports'.
$db = 'reports';
$dbh = WrapDBI->connect("$db", {RaiseError => 1}) || die print "Connection with database failed: $DBI::errstr";


# the different sections handle the different parameters
# that are sent to this script, and also hold the information 
# regarding the number of rows of data , present for pagination 
# into groups of 30 rows.

if (param("Projectstatus")){
    
    &outputheader($cgititle1);
    
    my $query=CGI::new();
    $value = $query ->param("Projectstatus");
    $value2 = $query ->param('rowcount');
    $value3 = $query ->param('rw');
    $sort=$query->param('Sort');

    &HEADER;
    &SQL_PROJECTSTATS($value,$value2,$value3,$sort);
}

if (param("Clonestatus")) {
    
    &outputheader($cgititle2);
    
    my $query=CGI::new();
    $value = $query ->param("Clonestatus");
    $value2 = $query ->param('rowcount');
    $value3 = $query ->param('rw');
    $sort=$query->param('Sort');
  
    &HEADER;
    &SQL_CLONESTATS($value,$value2,$value3,$sort);   
}
    
if (param("Organism")) {
    
    my $query=CGI::new();
    $value = $query ->param("Organism");
    if ($value=~ /\+/){
        $value=~ s/\+/ /;
    }
    $chromo = $query ->param("chromosome");
    $state_num = $query ->param("status");
    $total = $query ->param("total");
    $name = $query ->param("name");
    $value2 = $query ->param('rowcount');
    $value3 = $query ->param('rw');
    $label="Organism";
    $sort=$query->param('Sort');   

    if ($name=~ /Projectname/){    
        &outputheader($cgititle3);
    }else{
        &outputheader($cgititle4);
    }
    
    if (!$chromo){
        $chromo = 'N/A';
    }

    &HEADER;
    &ORG_STATS($value,$chromo,$state_num,$total,$name,$value2,$value3,$label,$sort);   
}

if (param("Chromosome")) {
    
    my $query=CGI::new();
    $value = $query ->param("organism");
    $chromo = $query ->param("Chromosome");
    $state_num = $query ->param("status");
    $total = $query ->param("total");
    $name = $query ->param("name");
    $team = $query ->param("team");
    $value2 = $query ->param('rowcount');
    $value3 = $query ->param('rw');
    $label="Chromosome";
    $sort=$query->param('Sort'); 

    if ($name=~ /Projectname/){    
        &outputheader($cgititle5);
    }else{
        &outputheader($cgititle6);
    }

    if ($team !~ /\d+/g){
        $team="NO_TEAM";
    }

    &HEADER;
    &CHROMO_STATS($value,$chromo,$state_num,$total,$name,$value2,$value3,$label,$team,$sort);   
}



if (param("Teamname")) {
    
    my $query=CGI::new();
    
    $team = $query ->param("Teamname");
    $state_num = $query ->param("status");
    $total = $query ->param("total");
    $name = $query ->param("name");
    $value2 = $query ->param('rowcount');
    $value3 = $query ->param('rw');
    $label="Teamname";
    $sort=$query->param('Sort');

    if ($name=~ /Projectname/){    
        &outputheader($cgititle7);
    }else{
        &outputheader($cgititle8);
    }

    &HEADER;
    &TEAM_STATS($team,$state_num,$total,$name,$value2,$value3,$label,$sort);   
}


print "<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
        });
}


#formats the navigational bar,and blurb information for the top of the page.
sub HEADER{
    
    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 Status Lists"},"Help")])]);
    print "</TABLE>";
    print "<BR>";
    
}

#sql extracts the relevant data for the selected status.
sub SQL_PROJECTSTATS{
    
    my ($value,$value2,$value3,$sort)=@_;

    $sqlstatement= "select description from projectstatusdict where id_dict=?";
    $sth = $dbh->prepare($sqlstatement)|| die "cannot prepare statement:my $DBI::errstr\n";
    $sth->bind_param(1,$value);  
    $sth->execute || die "cannot execute:$DBI::errstr\n";
    
    $status = $sth->fetchall_arrayref;

    $sqlstatement= "
select /*+ INDEX(a) INDEX(b) INDEX(c) INDEX(e) INDEX(f)*/ a.projectname,a.clonename,f.chromosome,c.statusdate,d.description,e.speciesname,b.remark,c.remark 
from clone_project a, project b, project_status c, repositorytypedict d, clone e, chromosomedict f
where a.projectname=b.projectname
and a.clonename=e.clonename (+) 
and b.projectname=c.projectname 
and b.project_type=d.id_dict (+) 
and c.status= ?
and c.iscurrent='1' 
and e.chromosome=f.id_dict (+)
";

    my $sqladd1 = "order by lower(a.projectname),upper(a.projectname)";
    my $sqladd2 = "order by f.chromosome,lower(a.projectname),upper(a.projectname),c.statusdate DESC";
    my $sqladd3 = "order by c.statusdate DESC";
    my $sqladd4 = "order by d.id_dict,f.chromosome, c.statusdate DESC";
    my $sqladd5 = "order by e.speciesname,f.chromosome,c.statusdate DESC";
    my $sqltest;
    
    if ($sort){
        if ($sort=~ /.*\_chromo/){
            $sqltest="$sqlstatement"."$sqladd2";
        }
        elsif ($sort=~ /.*\_date/){
            $sqltest="$sqlstatement"."$sqladd3";   
        }
        elsif ($sort=~ /.*\_type/){
            $sqltest="$sqlstatement"."$sqladd4";   
        }
        elsif ($sort=~ /.*\_speciesname/){
            $sqltest="$sqlstatement"."$sqladd5";   
        }
    }else{
        $sqltest="$sqlstatement"."$sqladd1";
    }
    
    $sth = $dbh->prepare($sqltest)|| die "cannot prepare statement:my $DBI::errstr\n";
    $sth ->bind_param(1, $value);
    $sth->execute || die "cannot execute:$DBI::errstr\n";
    
    $listinfo = $sth->fetchall_arrayref; 
    
    $title="Projects";

    $sth->finish();
    &DISPLAY_LIST($sort,$status,$listinfo,$title,$value,$value2,$value3);
   
}


sub SQL_CLONESTATS{
    
    my ($value,$value2,$value3,$sort)=@_;
   
    $sqlstatement= "select description from clonestatusdict where id_dict=?";
    $sth = $dbh->prepare($sqlstatement)|| die "cannot prepare statement:my $DBI::errstr\n";
    $sth->bind_param(1,$value);
    $sth->execute || die "cannot execute:$DBI::errstr\n";
    
    $status = $sth->fetchall_arrayref;
    
    $sqlstatement= "select /*+ INDEX(a) INDEX(b) INDEX(c) INDEX(e) */ b.clonename, b.projectname, e.chromosome, c.statusdate, d.description, a.speciesname, a.remark 
from clone a, clone_project b, clone_status c, clonetypedict d, chromosomedict e 
where a.clonename=b.clonename
and a.clone_type=d.id_dict(+) 
and a.chromosome=e.id_dict (+)  
and b.clonename=c.clonename 
and c.iscurrent='1'
and c.status = ?  
";

    my $sqladd1 = "order by lower(b.clonename),upper(b.clonename)";   
    my $sqladd2 = "order by e.chromosome,lower(b.clonename),upper(b.clonename),c.statusdate DESC";
    my $sqladd3 = "order by c.statusdate DESC";
    my $sqladd4 = "order by d.id_dict,e.chromosome,c.statusdate DESC";
    my $sqladd5 = "order by a.speciesname,e.chromosome,c.statusdate DESC";
    my $sqltest;

    if ($sort){
        if ($sort=~ /.*\_chromo/){
            $sqltest="$sqlstatement"."$sqladd2";
        }
        elsif ($sort=~ /.*\_date/){
            $sqltest="$sqlstatement"."$sqladd3";   
        }
        elsif ($sort=~ /.*\_type/){
            $sqltest="$sqlstatement"."$sqladd4";   
        }
        elsif ($sort=~ /.*\_speciesname/){
            $sqltest="$sqlstatement"."$sqladd5";   
        }
    }else{
        $sqltest="$sqlstatement"."$sqladd1";
    }

    $sth = $dbh->prepare($sqltest)|| die "cannot prepare statement:my $DBI::errstr\n";
    $sth->bind_param(1, $value);  
    $sth->execute || die "cannot execute:$DBI::errstr\n";
    
    $listinfo = $sth->fetchall_arrayref; 
    
    $title="Clones";

    $sth->finish(); 
    &DISPLAY_LIST($sort,$status,$listinfo,$title,$value,$value2,$value3);  
    
}


sub ORG_STATS {

    my ($value,$chromo,$state_num,$total,$name,$value2,$value3,$label,$sort)=@_;

    my $sqltest;   
    my $sqlstat;
    
    if ($name=~ /Projectname/){
        
        $sqlstat= "select description from projectstatusdict where id_dict=?";
        
        
        if ($chromo=~ /NO_CHROMO/){
            
            $sqlstatement="select /*+  INDEX(a) INDEX(b) INDEX(c) INDEX(d) INDEX(f) */ b.projectname,b.clonename,f.chromosome,d.statusdate,e.description,a.speciesname,c.remark, d.remark 
from clone a, clone_project b, project c, project_status d, repositorytypedict e, chromosomedict f 
where a.speciesname=? 
and d.status=?
and a.clonename=b.clonename 
and b.projectname=c.projectname 
and c.projectname=d.projectname 
and c.project_type=e.id_dict(+) 
and a.chromosome=f.id_dict(+) 
and d.iscurrent='1'
group by b.projectname,b.clonename,f.chromosome,d.statusdate,e.description,a.speciesname,c.remark,d.remark  
having f.chromosome is null 
";
    
         }

elsif ($chromo=~ /N\/A/){

  $sqlstatement="select /*+  INDEX(a) INDEX(b) INDEX(c) INDEX(d) INDEX(f) */ b.projectname,b.clonename,f.chromosome,d.statusdate,e.description,a.speciesname,c.remark,d.remark 
from clone a, clone_project b, project c, project_status d, repositorytypedict e, chromosomedict f 
where a.speciesname=? 
and d.status=?
and a.clonename=b.clonename 
and b.projectname=c.projectname 
and c.projectname=d.projectname 
and c.project_type=e.id_dict(+) 
and a.chromosome=f.id_dict(+) 
and d.iscurrent='1' 
";
       
        }else{
            
$sqlstatement="select /*+  INDEX(a) INDEX(b) INDEX(c) INDEX(d) INDEX(f) */ b.projectname,b.clonename,f.chromosome,d.statusdate,e.description,a.speciesname,c.remark,d.remark 
from clone a, clone_project b, project c, project_status d, repositorytypedict e, chromosomedict f 
where a.speciesname=? 
and d.status=? 
and a.chromosome=?
and a.clonename=b.clonename 
and b.projectname=c.projectname 
and c.projectname=d.projectname 
and c.project_type=e.id_dict(+) 
and a.chromosome=f.id_dict(+) 
and d.iscurrent='1' 
";
        }        
        
        my $sqladd1 = "order by lower(b.projectname),upper(b.projectname)";
        my $sqladd2 = "order by f.chromosome,lower(b.projectname),upper(b.projectname),d.statusdate DESC";
        my $sqladd3 = "order by d.statusdate DESC";
        my $sqladd4 = "order by e.id_dict,f.chromosome, d.statusdate DESC";
        my $sqladd5 = "order by a.speciesname,f.chromosome,d.statusdate DESC";
        
        
        if ($sort){
            if ($sort=~ /.*\_chromo/){
                $sqltest="$sqlstatement"."$sqladd2";
            }
            elsif ($sort=~ /.*\_date/){
                $sqltest="$sqlstatement"."$sqladd3";   
            }
            elsif ($sort=~ /.*\_type/){
                $sqltest="$sqlstatement"."$sqladd4";   
            }
            elsif ($sort=~ /.*\_speciesname/){
                $sqltest="$sqlstatement"."$sqladd5";   
            }
        }else{
            $sqltest="$sqlstatement"."$sqladd1";
        }
              
        
    }else{

        $sqlstat= "select description from clonestatusdict where id_dict=?";

        if ($chromo=~ /NO_CHROMO/){
            
$sqlstatement= "select /*+  INDEX(a) INDEX(b) INDEX(c) INDEX(e) */ b.clonename,b.projectname,e.chromosome,c.statusdate,d.description,a.speciesname,a.remark 
from clone a, clone_project b, clone_status c, clonetypedict d, chromosomedict e 
where a.speciesname=?
and c.status=? 
and a.clonename=b.clonename 
and b.clonename=c.clonename 
and a.clone_type=d.id_dict(+) 
and a.chromosome=e.id_dict (+) 
and c.iscurrent='1' 
group by b.clonename,b.projectname,e.chromosome,c.statusdate,d.description,a.speciesname,a.remark 
having e.chromosome is null 
";
           }
elsif ($chromo=~ /N\/A/){

$sqlstatement= "select /*+  INDEX(a) INDEX(b) INDEX(c) INDEX(e) */ b.clonename,b.projectname,e.chromosome,c.statusdate,d.description,a.speciesname,a.remark 
from clone a, clone_project b, clone_status c, clonetypedict d, chromosomedict e 
where a.speciesname=?
and c.status=? 
and a.clonename=b.clonename 
and b.clonename=c.clonename 
and a.clone_type=d.id_dict(+) 
and a.chromosome=e.id_dict (+) 
and c.iscurrent='1' 
";
          
        }else{      

$sqlstatement= "select /*+  INDEX(a) INDEX(b) INDEX(c) INDEX(e) */ b.clonename,b.projectname,e.chromosome,c.statusdate,d.description,a.speciesname,a.remark 
from clone a, clone_project b, clone_status c, clonetypedict d, chromosomedict e 
where a.speciesname=?
and c.status=? 
and a.chromosome=?
and a.clonename=b.clonename 
and b.clonename=c.clonename 
and a.clone_type=d.id_dict(+) 
and a.chromosome=e.id_dict (+) 
and c.iscurrent='1' 
";
        }
        
        my $sqladd1 = "order by lower(b.clonename),upper(b.clonename)";   
        my $sqladd2 = "order by e.chromosome,lower(b.clonename),upper(b.clonename),c.statusdate DESC";
        my $sqladd3 = "order by c.statusdate DESC";
        my $sqladd4 = "order by d.id_dict,e.chromosome,c.statusdate DESC";
        my $sqladd5 = "order by a.speciesname,e.chromosome,c.statusdate DESC";
        
        if ($sort){
            if ($sort=~ /.*\_chromo/){
                $sqltest="$sqlstatement"."$sqladd2";
            }
            elsif ($sort=~ /.*\_date/){
                $sqltest="$sqlstatement"."$sqladd3";   
            }
            elsif ($sort=~ /.*\_type/){
                $sqltest="$sqlstatement"."$sqladd4";   
            }
            elsif ($sort=~ /.*\_speciesname/){
                $sqltest="$sqlstatement"."$sqladd5";   
            }
        }else{
            $sqltest="$sqlstatement"."$sqladd1";
        }
        
    }

    my $sth_stat;
    #to display the title information
    $sth_stat = $dbh->prepare($sqlstat)|| die "cannot prepare statement:my $DBI::errstr\n";
    $sth_stat->bind_param(1, $state_num);
    $sth_stat->execute || die "cannot execute:$DBI::errstr\n";
    $status = $sth_stat->fetchall_arrayref;


    #to display the list information
    $sth = $dbh->prepare($sqltest)|| die "cannot prepare statement:my $DBI::errstr\n";
    if ($value){
        $sth->bind_param(1, $value);
    }
    if ($state_num){
        $sth->bind_param(2, $state_num);
    }
    if ($chromo !~ /NO_CHROMO|N\/A/){
        $sth->bind_param(3, $chromo);
    }
    $sth->execute || die "cannot execute:$DBI::errstr\n";
    $listinfo = $sth->fetchall_arrayref; 
    
    
    if ($name=~ /Projectname/){
        $title="Projects_By_Organism";
    }else{
        $title="Clones_By_Organism";
    }
 
    $sth_stat->finish();
    $sth->finish();

    &DISPLAY_LIST($sort,$status,$listinfo,$title,$value,$chromo,$state_num,$total,$name,$value2,$value3,$label);
    
    
}

sub CHROMO_STATS {

    my ($value,$chromo,$state_num,$total,$name,$value2,$value3,$label,$team,$sort)=@_;

    my $sqlstat;
    my $sqltest;
    
    if ($name=~ /Projectname/){
        
        $sqlstat= "select description from projectstatusdict where id_dict= ?";
        
        if ($team=~ /NO_TEAM/){
            
$sqlstatement="select /*+ INDEX(a) INDEX(b) INDEX(f) INDEX(g) */ b.projectname,b.clonename,f.chromosome,d.statusdate,e.description,a.speciesname,c.remark,d.remark 
from clone a, clone_project b, project c, project_status d, repositorytypedict e, chromosomedict f, project_owner g 
where a.speciesname=?
and d.status=?
and a.chromosome=? 
and a.clonename=b.clonename 
and b.projectname=c.projectname 
and c.projectname=d.projectname 
and c.project_type=e.id_dict(+) 
and a.chromosome=f.id_dict(+) 
and d.iscurrent='1' 
and b.projectname=g.projectname (+) 
and g.iscurrent (+)='1' 
and g.teamname is null 
group by b.projectname,b.clonename,f.chromosome,d.statusdate,e.description,a.speciesname,c.remark,d.remark 
";

        }elsif ($team=~ /222/){
#This statement lists the projects from the 
#status table which dosen't include the 
#team breakdown.

$sqlstatement="select b.projectname,b.clonename,f.chromosome,d.statusdate,e.description,a.speciesname,c.remark,d.remark 
from clone a, clone_project b, project c, project_status d, repositorytypedict e, chromosomedict f 
where a.speciesname=?
and d.status=?  
and a.chromosome=? 
and a.clonename=b.clonename 
and b.projectname=c.projectname 
and c.projectname=d.projectname 
and c.project_type=e.id_dict(+) 
and a.chromosome=f.id_dict(+) 
and d.iscurrent='1' 
";
            
        }else{
        
$sqlstatement="select b.projectname,b.clonename,f.chromosome,d.statusdate,e.description,a.speciesname,c.remark,d.remark 
from clone a, clone_project b, project c, project_status d, repositorytypedict e, chromosomedict f, project_owner g 
where a.speciesname=? 
and d.status=? 
and a.chromosome=? 
and a.clonename=b.clonename 
and b.projectname=c.projectname 
and c.projectname=d.projectname 
and c.project_type=e.id_dict(+) 
and a.chromosome=f.id_dict(+) 
and d.iscurrent='1' 
and c.projectname=g.projectname (+) 
and g.iscurrent (+)='1' 
and g.teamname=? 
";
    }
       
        my $sqladd1 = "order by lower(b.projectname),upper(b.projectname)";
        my $sqladd2 = "order by f.chromosome,lower(b.projectname),upper(b.projectname),d.statusdate DESC";
        my $sqladd3 = "order by d.statusdate DESC";
        my $sqladd4 = "order by e.id_dict,f.chromosome, d.statusdate DESC";
        my $sqladd5 = "order by a.speciesname,f.chromosome,d.statusdate DESC";
        
        
        if ($sort){
            if ($sort=~ /.*\_chromo/){
                $sqltest="$sqlstatement"."$sqladd2";
            }
            elsif ($sort=~ /.*\_date/){
                $sqltest="$sqlstatement"."$sqladd3";   
            }
            elsif ($sort=~ /.*\_type/){
                $sqltest="$sqlstatement"."$sqladd4";   
            }
            elsif ($sort=~ /.*\_speciesname/){
                $sqltest="$sqlstatement"."$sqladd5";   
            }
        }else{
            $sqltest="$sqlstatement"."$sqladd1";
        }
      
        
    }else{


        $sqlstat= "select description from clonestatusdict where id_dict=?";

        if ($team=~ /NO_TEAM/){
            
$sqlstatement= "select b.clonename,b.projectname,e.chromosome,c.statusdate,d.description,a.speciesname,a.remark 
from clone a, clone_project b, clone_status c, clonetypedict d,chromosomedict e, project_owner f 
where a.speciesname=?
and c.status=? 
and a.chromosome=?
and a.clonename=b.clonename 
and b.clonename=c.clonename 
and a.clone_type=d.id_dict(+) 
and a.chromosome=e.id_dict (+) 
and c.iscurrent='1' 
and b.projectname=f.projectname (+) 
and f.iscurrent (+)='1' 
and f.teamname is null 
group by b.clonename,b.projectname,e.chromosome,c.statusdate,d.description,a.speciesname,a.remark 
";
            
        }elsif ($team=~ /222/){
            
$sqlstatement= "select b.clonename,b.projectname,e.chromosome,c.statusdate,d.description,a.speciesname,a.remark 
from clone a, clone_project b, clone_status c, clonetypedict d,chromosomedict e 
where a.speciesname=?
and c.status=? 
and a.chromosome=?
and a.clonename=b.clonename 
and b.clonename=c.clonename 
and a.clone_type=d.id_dict(+) 
and a.chromosome=e.id_dict (+) 
and c.iscurrent='1' 
";      
            
        }else{      
            
$sqlstatement= "select b.clonename,b.projectname,e.chromosome,c.statusdate,d.description,a.speciesname,a.remark 
from clone a, clone_project b, clone_status c, clonetypedict d,chromosomedict e, project_owner f 
where a.speciesname=?
and c.status=?
and a.chromosome=? 
and a.clonename=b.clonename 
and b.clonename=c.clonename 
and a.clone_type=d.id_dict(+) 
and a.chromosome=e.id_dict (+) 
and c.iscurrent='1' 
and b.projectname=f.projectname (+) 
and f.iscurrent(+)='1' 
and f.teamname=? 
";
        }

        my $sqladd1 = "order by lower(b.clonename),upper(b.clonename)";   
        my $sqladd2 = "order by e.chromosome,lower(b.clonename),upper(b.clonename),c.statusdate DESC";
        my $sqladd3 = "order by c.statusdate DESC";
        my $sqladd4 = "order by d.id_dict,e.chromosome,c.statusdate DESC";
        my $sqladd5 = "order by a.speciesname,e.chromosome,c.statusdate DESC";
        
        if ($sort){
            if ($sort=~ /.*\_chromo/){
                $sqltest="$sqlstatement"."$sqladd2";
            }
            elsif ($sort=~ /.*\_date/){
                $sqltest="$sqlstatement"."$sqladd3";   
            }
            elsif ($sort=~ /.*\_type/){
                $sqltest="$sqlstatement"."$sqladd4";   
            }
            elsif ($sort=~ /.*\_speciesname/){
                $sqltest="$sqlstatement"."$sqladd5";   
            }
        }else{
            $sqltest="$sqlstatement"."$sqladd1";
        }
       
    }
    

    my $sth_stat;
    #to display the title information
    $sth_stat = $dbh->prepare($sqlstat)|| die "cannot prepare statement:my $DBI::errstr\n";
    $sth_stat->bind_param(1, $state_num);
    $sth_stat->execute || die "cannot execute:$DBI::errstr\n";
    $status = $sth_stat->fetchall_arrayref;


    #to display the list information
    $sth = $dbh->prepare($sqltest)|| die "cannot prepare statement:my $DBI::errstr\n";
    if ($value){
        $sth->bind_param(1, $value);
    }
    if ($state_num){
        $sth->bind_param(2, $state_num);
    }
    if ($chromo !~ /NO_CHROMO/){
        $sth->bind_param(3, $chromo);
    }
    if ($team !~ /NO_TEAM|222/){
        $sth->bind_param(4, $team);
    }
    $sth->execute || die "cannot execute:$DBI::errstr\n";
    $listinfo = $sth->fetchall_arrayref; 



    if ($name=~ /Projectname/){
        if ($team=~ /NO_TEAM|222/){
            $title="Projects_By_Chromosome";
        }else{
            $title="Projects_By_Chromosome(tm$team)";
        }
        }else{
            if ($team=~ /NO_TEAM|222/){
            $title="Clones_By_Chromosome";
        }else{
          $title="Clones_By_Chromosome(tm$team)";
        }  
        }    
    
    $sth->finish();
    $sth_stat->finish();
    &DISPLAY_LIST($sort,$status,$listinfo,$title,$value,$chromo,$state_num,$total,$name,$value2,$value3,$label,$team);
    

}


sub TEAM_STATS {

    my ($team,$state_num,$total,$name,$value2,$value3,$label,$sort)=@_;

    my $sqlstat;
    my $sqltest;

    if ($name=~ /Projectname/){
        
        $sqlstat= "select description from projectstatusdict where id_dict=?";
        
        $sqlstatement="select b.projectname,b.clonename,f.chromosome,d.statusdate,e.description,a.speciesname,c.remark,d.remark 
from clone a, clone_project b, project c, project_status d, repositorytypedict e, chromosomedict f, project_owner g
where a.clonename=b.clonename 
and b.projectname=c.projectname 
and b.projectname=g.projectname 
and c.projectname=d.projectname 
and c.project_type=e.id_dict(+) 
and a.chromosome=f.id_dict(+) 
and d.iscurrent='1' 
and g.iscurrent='1' 
and g.teamname=? 
and d.status=? 
";
        
        my $sqladd1 = "order by lower(b.projectname),upper(b.projectname)";
        my $sqladd2 = "order by f.chromosome,lower(b.projectname),upper(b.projectname),d.statusdate DESC";
        my $sqladd3 = "order by d.statusdate DESC";
        my $sqladd4 = "order by e.id_dict,f.chromosome, d.statusdate DESC";
        my $sqladd5 = "order by a.speciesname,f.chromosome,d.statusdate DESC";
        
        
        if ($sort){
            if ($sort=~ /.*\_chromo/){
                $sqltest="$sqlstatement"."$sqladd2";
            }
            elsif ($sort=~ /.*\_date/){
                $sqltest="$sqlstatement"."$sqladd3";   
            }
            elsif ($sort=~ /.*\_type/){
                $sqltest="$sqlstatement"."$sqladd4";   
            }
            elsif ($sort=~ /.*\_speciesname/){
                $sqltest="$sqlstatement"."$sqladd5";   
            }
        }else{
            $sqltest="$sqlstatement"."$sqladd1";
        }
        
        
    }else{
        
        $sqlstat= "select description from clonestatusdict where id_dict='$state_num'";
        
        $sqlstatement= "select b.clonename,b.projectname,e.chromosome,c.statusdate,d.description,a.speciesname,a.remark 
from clone a, clone_project b, clone_status c, clonetypedict d,chromosomedict e,project_owner f 
where a.clonename=b.clonename 
and b.projectname=f.projectname 
and b.clonename=c.clonename 
and a.clone_type=d.id_dict (+) 
and a.chromosome=e.id_dict (+) 
and c.iscurrent='1' 
and f.iscurrent='1' 
and f.teamname=?
and c.status=? 
";

        my $sqladd1 = "order by lower(b.clonename),upper(b.clonename)";   
        my $sqladd2 = "order by e.chromosome,lower(b.clonename),upper(b.clonename),c.statusdate DESC";
        my $sqladd3 = "order by c.statusdate DESC";
        my $sqladd4 = "order by d.id_dict,e.chromosome,c.statusdate DESC";
        my $sqladd5 = "order by a.speciesname,e.chromosome,c.statusdate DESC";
        
        if ($sort){
            if ($sort=~ /.*\_chromo/){
                $sqltest="$sqlstatement"."$sqladd2";
            }
            elsif ($sort=~ /.*\_date/){
                $sqltest="$sqlstatement"."$sqladd3";   
            }
            elsif ($sort=~ /.*\_type/){
                $sqltest="$sqlstatement"."$sqladd4";   
            }
            elsif ($sort=~ /.*\_speciesname/){
                $sqltest="$sqlstatement"."$sqladd5";   
            }
        }else{
            $sqltest="$sqlstatement"."$sqladd1";
        }
        
    }
    

    my $sth_stat;
    #to display the title information
    $sth_stat = $dbh->prepare($sqlstat)|| die "cannot prepare statement:my $DBI::errstr\n";
    $sth_stat->bind_param(1, $state_num);
    $sth_stat->execute || die "cannot execute:$DBI::errstr\n";
    $status = $sth_stat->fetchall_arrayref;


    #to display the list information
    $sth = $dbh->prepare($sqltest)|| die "cannot prepare statement:my $DBI::errstr\n";
    if ($team){
        $sth->bind_param(1, $team);
    }
    if ($state_num){
        $sth->bind_param(2, $state_num);
    }
    $sth->execute || die "cannot execute:$DBI::errstr\n";
    $listinfo = $sth->fetchall_arrayref;    
    
    
    if ($name=~ /Projectname/){
        $title="Projects_By_Team_$team";
    }else{
        $title="Clones_By_Team_$team";
    }
    
    my($chromo,$param);
    my $value;
    $value=$team;
    $label=$title;

    $sth->finish();
    $sth_stat->finish();

    &DISPLAY_LIST($sort,$status,$listinfo,$title,$value,$chromo,$state_num,$total,$name,$value2,$value3,$label);
   
    
}



#formats the data into tables that are paginated and have the corresponding buttons for easy navigation through the pages.
sub DISPLAY_LIST{
    
    my($sort,$status,$listinfo,$title,$value,$chromo,$state_num,$total,$name,$value2,$value3,$label,$team)=@_;

#page parameters are set up, which include the number of rows on a page, and the actual number of rows the data has returned.
    my $num_per_page=30;
    my $row;
    $row= CGI::param('rw');          # number of first row on the page.
    #$row = 0 if $row < 0;
    $row = 0 unless $row; 
    my $rowcount;
    my $c;
    
#either the number of rows is known, or they have to be counted.
    if (defined(CGI::param('rowcount'))){
	$rowcount=CGI::param('rowcount');
    }else{
        $c=0;
        $c+=@{$listinfo};
        $rowcount=$c;
    }
    #print $rowcount;
    
#page length is sorted,as well as  previous page, number of pages in total and the current page parameters.
    if (defined(CGI::param('single'))){$num_per_page=$rowcount;}
    my $length = $row+$num_per_page-1;
    my $prev = $row-$num_per_page;
    $prev = 0 if $prev < 0;
    
    my $num_pages=int($rowcount/$num_per_page);
    $num_pages++ if $rowcount % $num_per_page;
    
    my $current_page=int(($row+1)/$num_per_page);
    $current_page++ if ($row+1) % $num_per_page;
 
    
#Status title for each table formed.
    foreach my $stage(@{$status}){
        &sorts($title,$value,$chromo,$state_num,$total,$name,$team);
        print "<CENTER><STRONG><FONT SIZE=\"6\">$title - @{$stage}</FONT></STRONG><CENTER>";
    }
    print "<H5><CENTER>$current_page of $num_pages page(s)</CENTER></H5>";
    
   
#Table which houses the project or clone information
    
    if($sort){
        print "<TABLE BORDER=\"0\" WIDTH=\"90%\" ALIGN=\"center\">";
        print Tr([th({-align=>'left'},[$sort])]), "\n"; 
        print "</TABLE>";
    }
    
    print"<TABLE BORDER=\"1\" WIDTH=\"90%\" ALIGN=\"center\" CELLPADDING=\"2\" BGCOLOR=$table_colour>";
    if ($title=~ /Projects|Projects_\w+/){
        print Tr([th({-align=>'center'},['Projectname','Corresponding Clonename','Chromosome','Project Statusdate','Project Type','Speciesname','Project Remark','Project Status Remark','EMBL Status'])]), "\n";
    }else{
        print Tr([th({-align=>'center'},['Clonename','Corresponding Projectname','Chromosome','Clone Statusdate','Clone Type','Speciesname','Clone Remark','EMBL Status'])]), "\n";
    }
   
#loops through the records and sorts the records in 30 row pages.   
    $c=0;
    foreach my $list(@{$listinfo}){
        
        my $count=@{$list};    
        my $i;
        for ($i=0; $i<$count; $i++){
            @{$list}[$i]='&nbsp;' unless @{$list}[$i];       
        }
  
        if ($c < $row){
            $c++;
            next;
        }else{
            if ($title=~ /Projects|Projects_\w+/){
				my $clonename = @{$list}[1];
                @{$list}[0]=a({href=>"$reportaddress?Projectname=@{$list}[0]"},"@{$list}[0]");
                @{$list}[1]=a({href=>"$reportaddress?Clonename=@{$list}[1]"},"@{$list}[1]");
                print Tr([td({-align=>'center'},[@{$list}, getEmblStatus($clonename)])]);
            }else{
				my $clonename = @{$list}[0];
                @{$list}[0]=a({href=>"$reportaddress?Clonename=@{$list}[0]"},"@{$list}[0]");
                @{$list}[1]=a({href=>"$reportaddress?Projectname=@{$list}[1]"},"@{$list}[1]");
                print Tr([td({-align=>'center'},[@{$list}, getEmblStatus($clonename)])]);
            }
        }
        last if $c==$length;
        $c++;   
    }
    print "</TABLE>"; 
    
    $length++;	#so that the next page starts in the right place
    
#the buttons used to navigate though the pages of tables are prepared with data and formatted.
    
    if ($title=~ /^Projects$|Clones$/){    
        if ($title=~ /Projects/){
            $nametitle="Projectstatus";
        }else{
            $nametitle="Clonestatus";
        }
    
print<<EOS;

    <table align="center" border="0" width="50%" cellspacing="0" cellpadding="0">
	<tr valign="center">
            <td align="right">
                <form method="POST" action="$script_name">
                    <input type="hidden" name="$nametitle" value="$value">
EOS
    if ($sort){
        print "<input type=\"hidden\" name=\"Sort\" value=\"$sort\">";
                        }
print<<EOS;
                        <input type="hidden" name="rw" value="0">
                            <input type="hidden" name="rowcount" value="$rowcount">
                                <INPUT TYPE="submit" VALUE="First Page" class="red1">
                                    </form>&nbsp;
    </td>
EOS
            unless ($current_page==1){
                print<<EOS;
  		<td align="right">
                    <form method="POST" action="$script_name">
                        <input type="hidden" name="$nametitle" value="$value">
EOS
    if ($sort){
print "<input type=\"hidden\" name=\"Sort\" value=\"$sort\">";
                        }
print<<EOS;
                                <input type="hidden" name="rw" value="$prev">
                                    <input type="hidden" name="rowcount" value="$rowcount">
                                        <INPUT TYPE="submit" VALUE="<< Previous Page" class="red1">
                                            </form>&nbsp;
  		</td>
EOS
}

        unless ($current_page==$num_pages) {
            print<<EOS;
            <td align="left">
                <form method="POST" action="$script_name">
                    <input type="hidden" name="$nametitle" value="$value">
EOS
    if ($sort){
print "<input type=\"hidden\" name=\"Sort\" value=\"$sort\">";
                        }
print<<EOS;
                            <input type="hidden" name="rw" value="$length">
                                <input type="hidden" name="rowcount" value="$rowcount">
                                    <INPUT TYPE="submit" VALUE="Next Page >>" class="red1">
                                        </form>&nbsp;
            </td>
EOS
}

        unless ($num_pages==1){
            print<<EOS;
            <td align="right">
                <form method="POST" action="$script_name">
                    <input type="hidden" name="$nametitle" value="$value">
EOS
    if ($sort){
print "<input type=\"hidden\" name=\"Sort\" value=\"$sort\">";
                        }
print<<EOS;
                            <input type="hidden" name="single" value="1">
                                <input type="hidden" name="rowcount" value="$rowcount">	
                                    <INPUT TYPE="submit" VALUE="All $rowcount hits" class="blue1">
                                        </form>&nbsp;
            </td>
EOS
}
    print<<EOS;
    </tr>
        </table>
EOS


}

elsif ($label=~ /Organism|Chromosome/){    
    
    
    if ($title=~ /Projects_By_Organism|Clones_By_Organism/){
        $nametitle="Organism";
        $nametitle2="chromosome";
    }else{
        $nametitle="organism";
        $nametitle2="Chromosome";
    }
  
print<<EOS;
<table align="center" border="0" width="50%" cellspacing="0" cellpadding="0">
    <tr valign="center">
        <td align="right">
            <form method="POST" action="$script_name">
                <input type="hidden" name="$nametitle" value="$value">
                    <input type="hidden" name="$nametitle2" value="$chromo">
                        <input type="hidden" name="status" value="$state_num">
                            <input type="hidden" name="total" value="$total">
                                <input type="hidden" name="name" value="$name">
EOS
    if ($title=~ /\S+\_Chromosome\S*/){
        print "<input type=\"hidden\" name=\"team\" value=\"$team\">";
        }

    if ($sort){
        print "<input type=\"hidden\" name=\"Sort\" value=\"$sort\">";
    }

print<<EOS;
                                    <input type="hidden" name="rw" value="0">
                                        <input type="hidden" name="rowcount" value="$rowcount">
                                            <INPUT TYPE="submit" VALUE="First Page" class="red1">
                                                </form>&nbsp;
</td>
EOS
    unless ($current_page==1){
        print<<EOS;
        <td align="right">
            <form method="POST" action="$script_name">
                <input type="hidden" name="$nametitle" value="$value">
                    <input type="hidden" name="$nametitle2" value="$chromo">
                        <input type="hidden" name="status" value="$state_num">
                            <input type="hidden" name="total" value="$total">
                                <input type="hidden" name="name" value="$name">
EOS
    if ($title=~ /\S+\_Chromosome\S*/){
        print "<input type=\"hidden\" name=\"team\" value=\"$team\">";
        }

        if ($sort){
            print "<input type=\"hidden\" name=\"Sort\" value=\"$sort\">";
        }

print<<EOS;
                                    <input type="hidden" name="rw" value="$prev">
                                        <input type="hidden" name="rowcount" value="$rowcount">
                                            <INPUT TYPE="submit" VALUE="<< Previous Page" class="red1">
                                                </form>&nbsp;
        </td>
EOS
                    }

unless ($current_page==$num_pages) {
    print<<EOS;
    <td align="left">
        <form method="POST" action="$script_name">
            <input type="hidden" name="$nametitle" value="$value">
                <input type="hidden" name="$nametitle2" value="$chromo">
                    <input type="hidden" name="status" value="$state_num">
                        <input type="hidden" name="total" value="$total">
                            <input type="hidden" name="name" value="$name">
EOS
    if ($title=~ /\S+\_Chromosome\S*/){
        print "<input type=\"hidden\" name=\"team\" value=\"$team\">";
        }

    if ($sort){
        print "<input type=\"hidden\" name=\"Sort\" value=\"$sort\">";
    }

print<<EOS;
                                <input type="hidden" name="rw" value="$length">
                                    <input type="hidden" name="rowcount" value="$rowcount">
                                        <INPUT TYPE="submit" VALUE="Next Page >>" class="red1">
                                            </form>&nbsp;
    </td>
EOS
}

unless ($num_pages==1){
    print<<EOS;
    <td align="right">
        <form method="POST" action="$script_name">	
            <input type="hidden" name="$nametitle" value="$value">
                <input type="hidden" name="$nametitle2" value="$chromo">
                    <input type="hidden" name="status" value="$state_num">
                        <input type="hidden" name="total" value="$total">
                            <input type="hidden" name="name" value="$name">
EOS
    if ($title=~ /\S+\_Chromosome\S*/){
        print "<input type=\"hidden\" name=\"team\" value=\"$team\">";
        }

    if ($sort){
        print "<input type=\"hidden\" name=\"Sort\" value=\"$sort\">";
    }
    
print<<EOS;
                                <input type="hidden" name="single" value="1">
                                    <input type="hidden" name="rowcount" value="$rowcount">
                                        <INPUT TYPE="submit" VALUE="All $rowcount hits" class="blue1">
                                            </form>&nbsp;
    </td>
EOS
            }
print<<EOS;
</tr>
    </table>
EOS

}

elsif ($label=~ /^\w+_By_Team_\w+/){
    
print<<EOS;
<table align="center" border="0" width="50%" cellspacing="0" cellpadding="0">
    <tr valign="center">
        <td align="right">
            <form method="POST" action="$script_name">
                <input type="hidden" name="Teamname" value="$value">
                        <input type="hidden" name="status" value="$state_num">
                            <input type="hidden" name="total" value="$total">
                                <input type="hidden" name="name" value="$name">

EOS
    if ($sort){
print "<input type=\"hidden\" name=\"Sort\" value=\"$sort\">";
                        }
print<<EOS;

                                    <input type="hidden" name="rw" value="0">
                                        <input type="hidden" name="rowcount" value="$rowcount">
                                            <INPUT TYPE="submit" VALUE="First Page" class="red1">
                                                </form>&nbsp;
</td>
EOS
    unless ($current_page==1){
        print<<EOS;
        <td align="right">
            <form method="POST" action="$script_name">
                <input type="hidden" name="Teamname" value="$value">
                        <input type="hidden" name="status" value="$state_num">
                            <input type="hidden" name="total" value="$total">
                                <input type="hidden" name="name" value="$name">

EOS
    if ($sort){
print "<input type=\"hidden\" name=\"Sort\" value=\"$sort\">";
                        }
print<<EOS;

                                    <input type="hidden" name="rw" value="$prev">
                                        <input type="hidden" name="rowcount" value="$rowcount">
                                            <INPUT TYPE="submit" VALUE="<< Previous Page" class="red1">
                                                </form>&nbsp;
        </td>
EOS
                    }

unless ($current_page==$num_pages) {
    print<<EOS;
    <td align="left">
        <form method="POST" action="$script_name">
            <input type="hidden" name="Teamname" value="$value">
                    <input type="hidden" name="status" value="$state_num">
                        <input type="hidden" name="total" value="$total">
                            <input type="hidden" name="name" value="$name">

EOS
    if ($sort){
print "<input type=\"hidden\" name=\"Sort\" value=\"$sort\">";
                        }
print<<EOS;

                                <input type="hidden" name="rw" value="$length">
                                    <input type="hidden" name="rowcount" value="$rowcount">
                                        <INPUT TYPE="submit" VALUE="Next Page >>" class="red1">
                                            </form>&nbsp;
    </td>
EOS
            }

unless ($num_pages==1){
    print<<EOS;
    <td align="right">
        <form method="POST" action="$script_name">	
            <input type="hidden" name="Teamname" value="$value">
                    <input type="hidden" name="status" value="$state_num">
                        <input type="hidden" name="total" value="$total">
                            <input type="hidden" name="name" value="$name">

EOS
    if ($sort){
print "<input type=\"hidden\" name=\"Sort\" value=\"$sort\">";
                        }
print<<EOS;

                                <input type="hidden" name="single" value="1">
                                    <input type="hidden" name="rowcount" value="$rowcount">
                                        <INPUT TYPE="submit" VALUE="All $rowcount hits" class="blue1">
                                            </form>&nbsp;
    </td>
EOS
            }
print<<EOS;
</tr>
    </table>
EOS
    
}   
    else{
        print "Serious Problems!!";
    }                
    
    
}


sub sorts {

    my($title,$value,$chromo,$state_num,$total,$name,$team)=@_;

    $title=~ s/\s+//g;

    my $nametitle;

    if ($title=~ /^Projects$|Clones$/){    
        if ($title=~ /Projects/){
            $nametitle="Projectstatus";
        }else{
            $nametitle="Clonestatus";
        }
    }
    elsif ($title=~ /.*Organism|Chromosome/){    
#    elsif ($title=~ /.*\Organism|Chromosome/){    
        if ($title=~ /Projects_By_Organism|Clones_By_Organism/){
            $nametitle="Organism";
            $nametitle2="chromosome";
        }else{
            $nametitle="organism";
            $nametitle2="Chromosome";
        }
    }
    elsif ($title=~ /^\w+_By_Team_\w+/){
        $nametitle="Teamname";
    }  
    
    print<<EOS;
    
    <table align="center" border="0" width="50%">
        <tr valign="center">
            <td align="center">
                <form method="POST" action="$script_name">
                    <input type="hidden" name="$nametitle" value="$value">
EOS

    unless ($nametitle=~ /Projectstatus|Clonestatus/){
        
        
        if ($title=~ /\S+\_Chromosome\S*/){
            print "<input type=\"hidden\" name=\"team\" value=\"$team\">";
        }
        
        if ($chromo){
            print "<input type=\"hidden\" name=\"$nametitle2\" value=\"$chromo\">";
        }
        
        if ($state_num){
            print "<input type=\"hidden\" name=\"status\" value=\"$state_num\">";
        }
        
        if ($total){
            print "<input type=\"hidden\" name=\"total\" value=\"$total\">";
        }
        
        if ($name){
            print "<input type=\"hidden\" name=\"name\" value=\"$name\">";
        }
        
    }

print<<EOS;
                        <input type="hidden" name="Sort" value="sort_by_chromo">
                            <INPUT TYPE="submit" VALUE="Sort by Chromosome" class="red1">
                                </form>&nbsp;
    </td>
        <td align="center">
            <form method="POST" action="$script_name">
                <input type="hidden" name="$nametitle" value="$value">
EOS

    unless ($nametitle=~ /Projectstatus|Clonestatus/){
        
        if ($title=~ /\S+\_Chromosome\S*/){
            print "<input type=\"hidden\" name=\"team\" value=\"$team\">";
        }
        
        if ($chromo){
            print "<input type=\"hidden\" name=\"$nametitle2\" value=\"$chromo\">";
        }
        
        if ($state_num){
            print "<input type=\"hidden\" name=\"status\" value=\"$state_num\">";
        }
        
        if ($total){
            print "<input type=\"hidden\" name=\"total\" value=\"$total\">";
        }
        
        if ($name){
            print "<input type=\"hidden\" name=\"name\" value=\"$name\">";
        }
        
    }

print<<EOS;
                    <input type="hidden" name="Sort" value="sort_by_date">
                    <INPUT TYPE="submit" VALUE="Sort by Statusdate" class="red1">
                        </form>&nbsp;
    </td>
        <td align="center">
            <form method="POST" action="$script_name">
                <input type="hidden" name="$nametitle" value="$value">
EOS

    unless ($nametitle=~ /Projectstatus|Clonestatus/){
        
        if ($title=~ /\S+\_Chromosome\S*/){
            print "<input type=\"hidden\" name=\"team\" value=\"$team\">";
        }
        
        if ($chromo){
            print "<input type=\"hidden\" name=\"$nametitle2\" value=\"$chromo\">";
        }
        
        if ($state_num){
            print "<input type=\"hidden\" name=\"status\" value=\"$state_num\">";
        }
        
        if ($total){
            print "<input type=\"hidden\" name=\"total\" value=\"$total\">";
        }
        
        if ($name){
            print "<input type=\"hidden\" name=\"name\" value=\"$name\">";
        }
        
    }

print<<EOS;
                    <input type="hidden" name="Sort" value="sort_by_type">
                        <INPUT TYPE="submit" VALUE="Sort by Type" class="red1">
                            </form>&nbsp;
    </td>
        <td align="center">
            <form method="POST" action="$script_name">
                <input type="hidden" name="$nametitle" value="$value">
EOS

    unless ($nametitle=~ /Projectstatus|Clonestatus/){
        
        if ($title=~ /\S+\_Chromosome\S*/){
            print "<input type=\"hidden\" name=\"team\" value=\"$team\">";
        }
        
        if ($chromo){
            print "<input type=\"hidden\" name=\"$nametitle2\" value=\"$chromo\">";
        }
        
        if ($state_num){
            print "<input type=\"hidden\" name=\"status\" value=\"$state_num\">";
        }
        
        if ($total){
            print "<input type=\"hidden\" name=\"total\" value=\"$total\">";
        }
        
        if ($name){
            print "<input type=\"hidden\" name=\"name\" value=\"$name\">";
        }
        
    }
    
print<<EOS;
                    <input type="hidden" name="Sort" value="sort_by_speciesname">
                        <INPUT TYPE="submit" VALUE="Sort by Speciesname" class="red1">
                            </form>&nbsp;
    </td>
        </tr>
            </table>
EOS
    
}


#
# Look up the EMBL status for a given clone
#
sub getEmblStatus
{
	my $clonename = shift;

	if (!$getEmblStatusQuery) {
		$getEmblStatusQuery = $se->virtualSqlLib('projstatusclones', 'getEmblStatus',
		                                         "select hd.name
		                                          from htgsphasedict hd, clone_sequence cs, sequence s
		                                          where cs.clonename = ?
		                                          and cs.id_sequence = s.id_sequence
		                                          and cs.is_current = 1
		                                          and s.id_htgsphase = hd.id_htgsphase");
	}
	my $emblStatus = $se->getRow($getEmblStatusQuery, [$clonename], $seDbh);
	return $emblStatus || '-';
}



__END__
    


