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

###########################################################################
#                                                                         #
#   The script allows the user to submit an ID as a full query or         #
#   as wildcard queries to retrieve information relating to the           #
#   query. The data is paginated in rows of 30.                           #
#                                                                         #
#   Author : Karen Ambrose                                                #
#                                                                         #
###########################################################################

$|=1;
use SangerPaths qw(core badger);
use SangerWeb;
use WrapDBI;
use strict;
use CGI qw(:all :html3);

#variables used
my $db;
my $DBI;
my $dbh;
my $sqlstatement;
my $sth;
my $table_colour = '#E2E2FF'; #table colour
my $cgititle= 'Query Results';
my $cgiauthor='kva';
my $address='http://www.sanger.ac.uk';
my $projaddress='/cgi-bin/oracle_reports/report.pl'; #displays project report information.
my $cloneprojaddress='/cgi-bin/oracle_reports/report.pl'; #displays the clone project report information.
my $phonebook='/cgi-bin/phonebook.pl'; #connects to employee info + picture.
my $list;
my $listinfo;
my $noentry;

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

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

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

&outputheader($cgititle);

my $query=CGI::new();
my $value = $query ->param("Queryname");

#safeguard against submit button being activated without data.
if (!$value){
    print "<H4>No Query was typed into the box, please use the back key on your browser to re-enter data.</H4>"
    } 

#safeguard against the amount of data submitted at any one time.
else{
    my @value= split(/\,/,$value);
    my $total=@value;
#if ($total>5){
    if ($total>1){
        print "<H4><CENTER>Too much data, reduce numbers and try again.<CENTER></H4>";
    }
    #elsif ($total<=5){   
    #elsif ($total=1){
    else{
        
#formating of the data found with navigation bar, header and the information found.
        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#Query"},"Help")])]);
        print "</TABLE>";
        print "<BR>";
        
        print "<H4><CENTER>Query results for $value.<CENTER></H4>";
        print "Any matches below are hightlighted and linked to their respective project or clone report (ordered by clonename).";
        print "<BR>";
        print "<BR>";
        
        foreach $value(@value){ 
            
            $value=~ s/^\s+//g;
            $value=~ s/\s+$//g;
            
            if (defined $value){
                
                if ($value=~ /\*/g){
                    $value=~ s/\*/\%/g; 
                }

                $sqlstatement = "select c.projectname, e.description, c.clonename, b.speciesname,
       a.chromosome, g.orgtitle
from   chromosomedict a, clone b, clone_project c, project d,
       repositorytypedict e, sequencing_org f, organisation g
where  (c.projectname like '$value' OR c.clonename like '$value')
and    a.id_dict  (+)   = b.chromosome
and    b.clonename    = c.clonename
and    b.sequenced_by = f.id_org
and    c.projectname  = d.projectname
and    d.project_type = e.id_dict
and    f.id_org       = g.id_org
group by c.projectname, e.description, c.clonename, b.speciesname,a.chromosome,g.orgtitle
order by lower(c.clonename),upper(c.clonename)"; 
                }
            
            $sth = $dbh->prepare($sqlstatement) || die "cannot prepare statement:$DBI::errstr\n";
            $sth-> execute || die "cannot execute statement:$DBI::errstr\n";
            
            $listinfo = $sth->fetchall_arrayref;
            $sth->finish();
            
            my $num_per_page=30;
            my $row;
            $row= CGI::param('rw'); 
            #$row = 0 if $row < 0;         # number of first row on the page.
            $row = 0 unless $row; 
            my $rowcount;
            my $c;
            
            if (defined(CGI::param('rowcount'))){
                $rowcount=CGI::param('rowcount');
            }else{
                $c=0;
                $c+=@{$listinfo};
                $rowcount=$c;
            }
            
            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;
            
            if (defined @{$listinfo}){
                print "<H5><CENTER>$current_page of $num_pages page(s)</CENTER></H5>";
            }
            else{
                undef;
            }

            print "<TABLE BORDER=\"0\" WIDTH=\"90%\" ALIGN=\"center\" CELLPADDING=\"0\">";
            print Tr([th({-align=>'right'},[font({color=>"blue"},"$rowcount hit(s)")])]),"\n";
            print "</TABLE>";

            print "<TABLE BORDER=\"2\" WIDTH=\"90%\" ALIGN=\"center\" CELLPADDING=\"5\">";
            print Tr([th({-align=>'center', -bgcolor=>'#E2E2FF', -height=>'30'},['PROJECTNAME','Project Type','CLONENAME','Speciesname','Chromosome','Sequenced by'])]), "\n";

            $value=~ s/\%/\*/g;
            $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 (defined @{$list}){                   
                        @{$list}[0]=a({href=>"$projaddress?Projectname=@{$list}[0]"},"@{$list}[0]"); 
                        @{$list}[2]=a({href=>"$cloneprojaddress?Clonename=@{$list}[2]"},"@{$list}[2]");  
                        print Tr([td({-align=>'center'},[@{$list}])]), "\n";
                    }  
                }   
                
                last if $c==$length;
                $c++;   
            }
            if (!defined @{$listinfo}){
                $value=~ s/\%/\*/g;
                $noentry= "No Match Found for $value";
                print Tr([td({-align=>'center'},[$noentry,"--",$noentry,"--","--","--",])]), "\n";
            }
            print "</TABLE>";
            $length++;
            
            if (defined @{$listinfo}){
                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="Queryname" value="$value">
                                    <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="Queryname" value="$value">
                    <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="Queryname" value="$value">
                                <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="Queryname" value="$value">
                                <input type="hidden" name="single" value="1">
                                    <input type="hidden" name="rowcount" value="$rowcount">	
                                        <INPUT TYPE="submit" VALUE="All $rowcount hit(s)" class="blue1">
                                            </form>&nbsp;
        </td>
EOS
            }
    print<<EOS;
                </tr>
                    </table>
EOS
}else{
    undef;   
}
        }
    }
}

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


__END__

