#!/usr/local/bin/perl5.6.1
#
#######################################################################
# This software has been created by Genome Research Limited (GRL).    # 
# GRL hereby grants permission to use, copy, modify and distribute    # 
# this software and its documentation for non-commercial purposes     # 
# without fee at the user's own risk on the basis set out below.      #
# GRL neither undertakes nor accepts any duty whether contractual or  # 
# otherwise in connection with the software, its use or the use of    # 
# any derivative, and makes no representations or warranties, express #
# or implied, concerning the software, its suitability, fitness for   #
# a particular purpose or non-infringement.                           #
# In no event shall the authors of the software or GRL be responsible # 
# or liable for any loss or damage whatsoever arising in any way      # 
# directly or indirectly out of the use of this software or its       # 
# derivatives, even if advised of the possibility of such damage.     #
# Our software can be freely distributed under the conditions set out # 
# above, and must contain this copyright notice.                      #
#######################################################################
#
#$Id: pporders_permonth.pl,v 1.13 2005/11/07 10:19:17 kva Exp $
#
#Author : Karen Ambrose (kva).

use strict;
use WrapDBI;
use SDTable;

#designate the destination filepath of the stats reports.
my $filepath = $ENV{'HOME'}.'/custom_primer/PP_STATS/';

# Which Database
my $db = 'finch';
my $dbh = WrapDBI->connect("$db", {RaiseError => 1});    
$dbh->{autocommit} = 0;

print "Starting the primer orders per month script\n\n";

my $stat_date = "select to_char(add_months(sysdate,-1), 'MON YYYY') from dual";
my $sth_date = $dbh->prepare($stat_date) || die "ERROR:$DBI::errstr";
$sth_date->execute || die "ERROR:$DBI::errstr";
my $date = $sth_date->fetchrow();
$sth_date->finish();

my $alter_date = $date;
$alter_date =~ s/\s+//g;
$alter_date = lc($alter_date);
my $file = $filepath."ppstats_".$alter_date;

open(FH, "> $file") or die "Cannot open filehandle to $file : $!";

print FH "\nEXTERNAL PRIMERPLATE ORDER STATISTICS\n";
print FH "-------------------------------------\n\n";

#my $stat_purchase = "select ID_PURCHASEORDER from purchaseorderdict where to_char(assign_date,'MON-YYYY') = to_char(add_months(sysdate,-1),'MON-YYYY')";

my $stat_purchase = "select PTPD.PREFIX||POD.ID_PURCHASEORDER from purchaseorderdict POD, PURCHASE_TEAM_PREFIX_DICT PTPD where to_char(pod.assign_date,'MON-YYYY') = to_char(add_months(sysdate,-1),'MON-YYYY') and PTPD.TEAM = 58";
my $sth_purchase = $dbh->prepare($stat_purchase) || die "ERROR:$DBI::errstr";
$sth_purchase->execute || die "ERROR:$DBI::errstr";
my $purchaseorder = $sth_purchase->fetchrow();
$sth_purchase->finish();
print FH "Purchase Order # $purchaseorder\n\n";

#statement1 - extract the loadingplate and primerplate information
my $stat1 = "select /*+ INDEX(a) INDEX(b) */ a.id_loadingplate, a.id_primerplate, c.supplier_order_number
from loadingplate a, loadingplate_status b, primer_plate c  
where a.id_loadingplate = b.id_loadingplate
and a.id_primerplate = c.id_primerplate
and b.status = 12 
and b.program != 'REORDER'
and to_char(b.statusdate,'MON YYYY') = to_char(add_months(sysdate,-1),'MON YYYY')
order by a.id_loadingplate, a.id_primerplate";
my $sth1 = $dbh->prepare($stat1) || die "ERROR:$DBI::errstr";
$sth1->execute || die "ERROR:$DBI::errstr";

my %HASH;
my %HASH1;
my %HASH2;
my ($lp, $pp, $son);
while (($lp, $pp, $son) = $sth1->fetchrow()){
    $HASH{$lp} = $pp;
    $HASH1{$lp} = $son;
}
$sth1->finish();

my $overall_platecount;
my $overall_primercount;
my $overall_basecount;

my $count;
my $primer;



my $stat2 = "select id_primer, primerseq from primer where id_primer in (select distinct id_primer from primer_map where id_primerplate = ? )";
my $sth2 = $dbh->prepare($stat2);

print FH "\n\n\n";
my $sequences;
my @r_cs;
my $table1 = SDTable->new('format' => 'gaudy');
$table1->set('title'=> "All Plates Submitted for $date", 'titles' => ["#","Sigma Order#", "Loadingplate ID", "Primerplate ID","Unique Oligo (primer) total","Base total"]);

foreach my $key (sort keys %HASH){
    $count++;
    my $base_total;  
    my $primerplate = $HASH{$key};
    $sth2->bind_param(1,$primerplate);
    $sth2->execute || die "ERROR:$DBI::errstr";
    
    while (($primer,$sequences) = $sth2->fetchrow()){
        push ( @{$HASH2{$primerplate}}, $primer);
        $base_total+=length($sequences);
    }
    my $up_total = scalar(@{$HASH2{$primerplate}});
    
    $overall_primercount+=$up_total;
    
    my @cs=("$count","$HASH1{$key}","$key","$HASH{$key}","$up_total","$base_total");
    push (@r_cs, \@cs);
    
    $overall_basecount+= $base_total;   
}
$sth2->finish();

foreach my $rc (@r_cs){
    $table1->add(@$rc);
}
print FH $table1->format();

$overall_platecount = $count;

print FH "\n\n\n";
my $table = SDTable->new('format' => 'gaudy');
$table->set('title'=> "Total results for $date", 'titles' => ["Loadingplate total", "Unique Oligo (primer) total","Base total"]);
my @contents=("$overall_platecount","$overall_primercount","$overall_basecount");
my @r_contents;
push (@r_contents, \@contents);
foreach my $rc (@r_contents){
    $table->add(@$rc);
}
print FH $table->format();
print FH "\n";


#,a.s_gen genus
#,a.s_spec species

my $stateall = " select a.lp_pp_id primerplate
,a.p_proj project
,a.s_gen ||' '|| a.s_spec organism
,a.cc_cproj
,a.po_team team
--,C
--,BASES
,sum(a.C) primer
,sum(a.BASES) bases
from
(
select /*+ INDEX(lp) INDEX(p) */
lp.id_loadingplate lp_lp_id
, lp.id_primerplate lp_pp_id
, p.primername      p_pname
, p.projectname     p_proj
, s.genus           s_gen
, s.species         s_spec
, cc.cost_project   cc_cproj
, po.teamname       po_team
, count(distinct p.id_primer) C
, length(p.primerseq) BASES
from loadingplate   lp,
loadingplate_status lps,
project_owner       po,
primer_map          pm,
primer              p,
clone_project       cp,
clone               c,
species             s,
costcode            cc
where lp.id_loadingplate = lps.id_loadingplate
and lp.id_primerplate    = pm.id_primerplate
and pm.id_primer         = p.id_primer
and p.projectname        = po.projectname (+)
and p.projectname        = cp.projectname
and cp.clonename         = c.clonename
and c.speciesname        = s.speciesname
and p.id_costcode        = cc.id_costcode
and lps.status           = 12
and lps.program          != 'REORDER'
and po.iscurrent         (+)= 1
and to_char(lps.statusdate,'MON YY') = to_char(add_months(sysdate,-1),'MON YY')
group by
  lp.id_loadingplate
, lp.id_primerplate
, p.primername
, p.projectname
, s.genus
, s.species
, cc.cost_project
, po.teamname
, p.primerseq
) a
group by
 a.lp_pp_id
,a.p_proj
,a.s_gen
,a.s_spec
,a.cc_cproj
,a.po_team
order by a.lp_pp_id, a.po_team
";

my $alldata;
$alldata = $dbh->selectall_arrayref($stateall) || die print "Cannot execute statement to extract external primer data: $DBI::errstr"; 

my @ad_cs;
my $table2 = SDTable->new('format' => 'gaudy');
$table2->set('title'=> "Full SP on the plates for $date", 'titles' => ["Primerplate","Project","Organism","Project No","Team","Unique Primers Total","Base Total"]);

my $primer_total;
my $b_total;

foreach my $ad (@{$alldata}) {
my @cs=(@$ad[0],@$ad[1],@$ad[2],@$ad[3],@$ad[4],@$ad[5],@$ad[6]);
$primer_total+=@$ad[5];
$b_total+=@$ad[6];
push (@ad_cs, \@cs);
}

foreach my $adc (@ad_cs){
    $table2->add(@$adc);
}
print FH $table2->format();


print FH "\n\n";
print FH "Unique primer total : $primer_total\n";
print FH "Base total : $b_total\n\n";


my $stat_summ = "select a.s_gen ||' '|| a.s_spec organism
,a.cc_cproj
--,C
--,BASES
,sum(a.C) primer
,sum(a.BASES) bases
from
(
select /*+ INDEX(lp) INDEX(p) */
  lp.id_loadingplate lp_lp_id
, lp.id_primerplate lp_pp_id
, p.primername      p_pname
, s.genus           s_gen
, s.species         s_spec
, cc.cost_project   cc_cproj
, count(distinct p.id_primer) C
, length(p.primerseq) BASES
from loadingplate   lp,
loadingplate_status lps,
project_owner       po,
primer_map          pm,
primer              p,
clone_project       cp,
clone               c,
species             s,
costcode            cc
where lp.id_loadingplate = lps.id_loadingplate
and lp.id_primerplate    = pm.id_primerplate
and pm.id_primer         = p.id_primer
and p.projectname        = po.projectname (+)
and p.projectname        = cp.projectname
and cp.clonename         = c.clonename
and c.speciesname        = s.speciesname
and p.id_costcode        = cc.id_costcode
and lps.status           = 12
and lps.program          != 'REORDER'
and po.iscurrent         (+)= 1
and to_char(lps.statusdate,'MON YY') = to_char(add_months(sysdate,-1),'MON YY')
group by
  lp.id_loadingplate
, lp.id_primerplate 
, p.primername
, s.genus
, s.species
, cc.cost_project
, p.primerseq
) a
group by
a.s_gen
,a.s_spec
,a.cc_cproj
order by a.s_gen,a.s_spec
";

my $sumdata;
$sumdata = $dbh->selectall_arrayref($stat_summ) || die print "Cannot execute statement to summarise external primer data: $DBI::errstr";

my @sum_cs;
my $table3 = SDTable->new('format' => 'gaudy');
$table3->set('title'=> "Summarised Data on the external primer order statistics.", 'titles' => ["Organism","Project No","Unique Primers Total","Base Total"]);

my $op_total;
my $ob_total;

foreach my $sd (@{$sumdata}) {
my @cs=(@$sd[0],@$sd[1],@$sd[2],@$sd[3],@$sd[4]);
push (@sum_cs, \@cs);
}

foreach my $sdc (@sum_cs){
    $table3->add(@$sdc);
}
print FH $table3->format();
print FH "\n\n";

#stat_plates - details the number of plates per organism and 
#provides the result to 4 decimal places to account for the fact
#that more than one organism may be found on a plate.
my $stat_plates = "select a.s_gen ||' '|| a.s_spec, a.cc_proj, ROUND(sum(a.C)/96,4)
from (
select distinct  s.genus s_gen, s.species s_spec, pm.id_primerplate p_plate, cc.cost_project cc_proj, count(distinct pm.id_map) C
from loadingplate_status lps, loadingplate lp, primer_map pm, primer p, clone_project cp, clone c,species s, costcode cc
where lps.id_loadingplate = lp.id_loadingplate
and lp.id_primerplate = pm.id_primerplate
and pm.id_primer = p.id_primer
and p.projectname = cp.projectname
and cp.clonename = c.clonename
and c.speciesname = s.speciesname
and p.id_costcode = cc.id_costcode
and lps.status = 12 
and lps.program != 'REORDER'
and to_char(lps.statusdate,'MON YYYY') = to_char(add_months(sysdate,-1),'MON YYYY')
group by s.genus, s.species, pm.id_primerplate, cc.cost_project
) a
group by a.s_gen,a.s_spec, a.cc_proj
order by a.s_gen,a.s_spec, a.cc_proj
";

my $platedata;
$platedata = $dbh->selectall_arrayref($stat_plates) || die print "Cannot execute statement to summarise external primer data: $DBI::errstr";

my @sum_plates;
my $table4 = SDTable->new('format' => 'gaudy');
$table4->set('title'=> "Number of plates per organism.", 'titles' => ["Organism","Project No","Number of plates (4dp)"]);

foreach my $pd (@{$platedata}) {
my @cs=(@$pd[0],@$pd[1],@$pd[2]);
push (@sum_plates, \@cs);
}

foreach my $sp (@sum_plates){
    $table4->add(@$sp);
}
print FH $table4->format();
print FH "\n\n";

#statement_reor - extract the loadingplate and primerplate information
#for reorders if there are any.
my $stat_reor = "select /*+ INDEX(a) INDEX(b) */ a.id_loadingplate, a.id_primerplate
from loadingplate a, loadingplate_status b  
where a.id_loadingplate = b.id_loadingplate
and b.status = 12 
and b.program = 'REORDER'
and to_char(b.statusdate,'MON YY') = to_char(add_months(sysdate,-1),'MON YY')
order by a.id_loadingplate, a.id_primerplate";
my $sth_reor = $dbh->prepare($stat_reor) || die "ERROR:$DBI::errstr";
$sth_reor->execute || die "ERROR:$DBI::errstr";

my %HASHREOR;
my %HASH2REOR;
my ($lp_reor, $pp_reor);
while (($lp_reor, $pp_reor) = $sth_reor->fetchrow()){
    $HASHREOR{$lp_reor} = $pp_reor;
}
$sth_reor->finish();

my $count_keys = scalar(keys %HASHREOR);

if ($count_keys > 0){
my $reorder_platecount;
my $reorder_primercount;
my $reorder_basecount;

my $count_reor;
my $primer_reor;

my $stat2_reor = "select id_primer, primerseq from primer where id_primer in (select distinct id_primer from primer_map where id_primerplate = ? )";
my $sth2_reor = $dbh->prepare($stat2_reor);

print FH "\n";
print FH "########################################################################\n";
print FH "\n";
print FH "REORDERS\n";
print FH "--------\n";
print FH "\n";
print FH "The information below details reorder information from existing primerplates\n";
print FH "\n\n";
my $sequences_reor;
my @r_cs;
my $table_reor = SDTable->new('format' => 'gaudy');
$table_reor->set('title'=> "Reorder Plates Submitted for $date", 'titles' => ["#","Loadingplate ID", "Primerplate ID","Unique Oligo (primer) total","Base total"]);

##NOTE:- $key was used instead of $primerplate to get around the problem of
##having 2 reorders from the same plate in the same month, which were causing
##the reorder calculations to be grouped together.
##Loadingplate ID has been included in the list within the statement - $stat_reoplates
foreach my $key (sort keys %HASHREOR){
    $count_reor++;
    my $base_total;  
    my $primerplate = $HASHREOR{$key};
    $sth2_reor->bind_param(1,$primerplate);
    $sth2_reor->execute || die "ERROR:$DBI::errstr";
    
    while (($primer_reor,$sequences_reor) = $sth2_reor->fetchrow()){
        push ( @{$HASH2REOR{$key}}, $primer_reor);
        $base_total+=length($sequences_reor);
    }
    my $up_total = scalar(@{$HASH2REOR{$key}});
    
    $reorder_primercount+=$up_total;
    
    my @cs=("$count_reor","$key","$HASHREOR{$key}","$up_total","$base_total");
    push (@r_cs, \@cs);
    
    $reorder_basecount+= $base_total;   
}
$sth2_reor->finish();

foreach my $rc (@r_cs){
    $table_reor->add(@$rc);
}
print FH $table_reor->format();

$reorder_platecount = $count_reor;

print FH "\n\n\n";
my $table2_reor = SDTable->new('format' => 'gaudy');
$table2_reor->set('title'=> "Total reorder results for $date", 'titles' => ["Loadingplate total", "Unique Oligo (primer) total","Base total"]);
my @contents=("$reorder_platecount","$reorder_primercount","$reorder_basecount");
my @r_contents;
push (@r_contents, \@contents);
foreach my $rc (@r_contents){
    $table2_reor->add(@$rc);
}
print FH $table2_reor->format();
print FH "\n\n";

#stat_reoplates - details the number of reordered plates per organism and 
#provides the result to 4 decimal places to account for the fact
#that more than one organism may be found on a plate.
my $stat_reoplates = "select a.s_gen ||' '|| a.s_spec, a.cc_proj, ROUND(sum(a.C)/96,4)
from (
select distinct  s.genus s_gen, s.species s_spec, lp.id_loadingplate l_plate, pm.id_primerplate p_plate, cc.cost_project cc_proj, count(distinct pm.id_map) C
from loadingplate_status lps, loadingplate lp, primer_map pm, primer p, clone_project cp, clone c,species s, costcode cc
where lps.id_loadingplate = lp.id_loadingplate
and lp.id_primerplate = pm.id_primerplate
and pm.id_primer = p.id_primer
and p.projectname = cp.projectname
and cp.clonename = c.clonename
and c.speciesname = s.speciesname
and p.id_costcode = cc.id_costcode
and lps.status = 12 
and lps.program = 'REORDER'
and to_char(lps.statusdate,'MON YYYY') = to_char(add_months(sysdate,-1),'MON YYYY')
group by s.genus, s.species, lp.id_loadingplate, pm.id_primerplate, cc.cost_project
) a
group by a.s_gen,a.s_spec,a.cc_proj
order by a.s_gen,a.s_spec,a.cc_proj
";

my $reor_platedata;
$reor_platedata = $dbh->selectall_arrayref($stat_reoplates) || die print "Cannot execute statement to summarise external primer data: $DBI::errstr";

my @sum_reoplates;
my $table5 = SDTable->new('format' => 'gaudy');
$table5->set('title'=> "Number of reordered plates per organism.", 'titles' => ["Organism","Project No","Number of plates (4dp)"]);

foreach my $rpd (@{$reor_platedata}) {
my @cs=(@$rpd[0],@$rpd[1],@$rpd[2]);
push (@sum_reoplates, \@cs);
}

foreach my $srp (@sum_reoplates){
    $table5->add(@$srp);
}
print FH $table5->format();
print FH "\n\n";
print FH "########################################################################\n";
print FH "\n\n";

}



close(FH);

print "END OF STATS\n";

END {
    if ($dbh){
        eval {
            $dbh->rollback();
            $dbh->disconnect();
        }
    }
};




