package reorder_primer;
#
#
#######################################################################
# 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: reorder_primer.pm,v 1.13 2006/06/20 15:44:28 kva Exp $
#
#####################################################
#                                                   #
# Generates copies of the existing entries for a    #
# map that has already been processed. It will be   #
# used to reorder primers from an existing stock    #
# plate of orders kept at Sigma.                    #
# A file with entries that have been duplicated,    #
# named after the mapplate that is being duplicated #
# with the extension '.txt', to the directory from  #
# which you are running the script.                 #
#                                                   # 
# Author : Karen Ambrose                            #
#                                                   #
#####################################################

#Things we need to use
use strict;
use WrapDBI;

use vars qw(@ISA @EXPORT);
use Exporter;
@ISA = ('Exporter');
@EXPORT = qw(&primer_reorder);
require Exporter;


sub primer_reorder {
    
    my ($dbh, $db, $who, $prog, $session_id, $script_name, $mapid)=@_;
    
#check put in place to check wether the $dbh is present 
    if (!$dbh){
        my $dbh = WrapDBI->connect("$db", {RaiseError => 1});    
        $dbh->{autocommit} = 0;
    }
    
    my $iteration;
  
#check if the map id is valid.  
    my $stat_chkvalid = "select id_mapplate 
from mapplate 
where id_mapplate = ?";
    my $sth_chkvalid = $dbh ->prepare($stat_chkvalid);
    $sth_chkvalid->bind_param(1,$mapid);
    $sth_chkvalid->execute();
    my $validmpid = $sth_chkvalid->fetchrow();
    
    if (!$validmpid) {
        print "<BR>";
        print "The map id $mapid isn't valid in the database.\n";
        print "<BR>";
        print "Please check and resubmit the correct ID.\n";
        print "<BR>";
        exit;
        
    } else {
        
#check if the sample entries match an others in 
#the database using the map id, and if so assess
#the iterations that have been used
        my $stat_chckit = "select distinct a.iteration
from finish_request a, finish_request b , request_map c, request_map d
where a.id_frequest = c.id_frequest
and b.id_frequest = d.id_frequest
and a.PLATENAME   =  b.PLATENAME                   
and a.ROWLETTER   =  b.ROWLETTER                    
and a.COLNUM      =  b.COLNUM                  
and a.ID_PRIMER   =  b.ID_PRIMER                    
and a.ID_SCSP     =  b.ID_SCSP 
and d.id_mapplate = ?";
        my $sth_chckit = $dbh ->prepare($stat_chckit);
        $sth_chckit->bind_param(1,$mapid);
        $sth_chckit->execute();
        my $itn;
        my %all_itns;
        while($itn = $sth_chckit->fetchrow()){
            if($itn ne ""){
                $all_itns{$itn} = 1;
            }
        }
        
        my $count = 0;
        my $hash_count = scalar(keys %all_itns);
#        print "hash count : - $hash_count\n";
#        print "<BR>";
        my @array = keys %all_itns;
#        print "array - >>@array<<\n"; 
#        print "<BR>";   
        
        if ($hash_count > 0){
#            print "<BR>";
#            print "hash count is greater than 0\n";
#            print "<BR>";
            foreach my $akeys (sort {$b cmp $a} keys %all_itns){
                $count++;
#                print "Iteration $count =  $akeys\n";
                if ($akeys eq 'z'){
                    print "<BR>";
                    print "Either some or all of the samples have been iterated using z.\n";
                    print "<BR>";
                    $iteration = 'y';
                }
                elsif ($akeys eq 'y'){
                    print "<BR>";
                    print "Either some or all of the samples have been iterated using y.\n";
                    print "<BR>";
                    print "No more reorders can be placed for this map through this route.\n";
                    print "<BR>";
                    print "This map has already been through this process 3 times (once natural & another 2 times with iteration z & y. Contact dn1g as to what to do next.\n\n";
                    print "<BR>";
                    exit;
                } else {
                    unless ($iteration){
                        $iteration = 'z';
                    }
                    
                }
            }
            
        } else {
            
            $iteration = 'z';
        }
        print "<BR>";
        print "iteration is:- $iteration\n";
        print "<BR>";
        
#Workout if the primerplate the
#map is linked to is expired or not.
        
        my $stat_distpp = "select distinct c.id_primerplate,d.statusdate
from request_map a, loadingsample b, loadingplate c, primerplate_status d
where a.id_loadingsample = b.id_loadingsample
and b.id_loadingplate = c.id_loadingplate
and c.id_primerplate = d.id_primerplate
and d.status = 3
and d.iscurrent = 1
and id_mapplate = ? ";
        
        my $sth_distpp = $dbh ->prepare($stat_distpp);
        $sth_distpp->execute($mapid);
        my ($ppid,$ppdate);
        ($ppid,$ppdate) = $sth_distpp->fetchrow();
        $sth_distpp->finish();
        
        if ($ppid) {
            print "<BR>";
            print "The primerplate $ppid was expired on $ppdate in the database.\n";
            print "<BR>";
            print "Therefore, no reorders can be made from this plate.\n";
            print "<BR>";
            exit;
            
        } else {
            
            
#file location of the output file for the information
            my $file = "/nfs/WWWdev/INTWEB_docs/cgi-bin/prodsoft/Crusade/REORDERS/$mapid$iteration.txt";
            #my $file = "/nfs/WWWdev/INTWEB_docs/cgi-bin/oracle_reports/AUTOFIN/$mapid$iteration.txt";
            my $filename = "Reorder Output File : $file";

            print "<BR>";
            print "The output file made can be viewed here or via the link at the top of the page : - <A HREF=file:$file  TARGET=remote_window>$mapid$iteration.txt</A>\n";
            print "<BR>";
            
            open (FH, "> $file") or die "Cannot open filehandle to $file : $!";
            
            print FH "\nPROGRAM : $prog - SESSION : $session_id - WHO : $who -  MAP ORDER : $mapid\n\n";
            
#get the id_role for finishing for the user, in this case it should be auto.
            my $stat_role = "select id_role
from team_person_role 
where roletype = 'Finishing'
and iscurrent = 1
and id_person in (select id_person from person where email = ?)
";
            my $sth_role = $dbh->prepare($stat_role);
            $sth_role->execute($who) || die "ERROR:$DBI::errstr";
            my $role = $sth_role->fetchrow();
            
            print FH "USER: $who , ROLE : $role\n\n";
            
#create the new mapid id       
            my $stat1 = "select SEQ_MAPPLATE.nextval from dual";
            my $sth1 = $dbh -> prepare($stat1);
            $sth1->execute();
            my($newmapid) = $sth1->fetchrow();
            $sth1->finish();
            
            print FH "NEW MAPID $newmapid\n\n";
            
#insert a new mapplate entry into mapplate with existing mapplate details
            my $stat3 = "insert into mapplate (ID_MAPPLATE,PLATE_FORMAT,ID_TEAM_FINSERV,PROTOCOL,ID_RUNTYPE,ID_PROTOCOL)
select $newmapid,PLATE_FORMAT,ID_TEAM_FINSERV,PROTOCOL,ID_RUNTYPE,ID_PROTOCOL
from mapplate 
where id_mapplate=?";
            my $sth3 = $dbh->prepare($stat3) || die "ERROR:$DBI::errstr";
            $sth3->execute($mapid) || die "ERROR:$DBI::errstr";
            $sth3->finish();
            
#insert a new mapplate status for this entry with 
#status 13 JIT Precreated
            my $status_id='13';
            upd->mapplate_status($dbh, $prog, $who, $session_id, $newmapid, $status_id);
            
#create the new finish batch id (for each finish_batch involved.
#Should work with plates that have multiple projects.      
            my $stat_nfb = "select SEQ_FBATCH.nextval from dual";
            my $sth_nfb = $dbh -> prepare($stat_nfb);   
            
#get the original finish_batch id(s)
            my $stat2 = " select distinct a.id_fbatch 
from finish_batch a, finish_request b, request_map c 
where a.id_fbatch = b.id_fbatch
and b.id_frequest = c.id_frequest
and c.id_mapplate = ?
order by a.id_fbatch
";
            my $sth_2 = $dbh ->prepare($stat2);
            $sth_2->execute($mapid);
            my @orig_fbs;
            my $fb;
            while ($fb = $sth_2->fetchrow()) {
                push @orig_fbs, $fb;
            }
            $sth_2->finish();
            
            my $sth6;
            my $sth7; 
            
            foreach my $orig_idfbatch (@orig_fbs){
                
#get the new id_fbatch for this finish batch
                $sth_nfb->execute();
                my($newfbatchid) = $sth_nfb->fetchrow();
        
                print FH "\nORIGINAL ID_FBATCH - $orig_idfbatch\n";
                print FH "NEW FBATCHID $newfbatchid\n\n";
                
#Make a new copy from exisiting fbatch entry and make a new fbatch status entry;
                my $stat6 = "insert into finish_batch (ID_FBATCH,BATCHDATE,PROJECTNAME,FINISHER,ID_TEAM_FINSERV,ID_AUTO,PRIMERS_PICKED,RESEQS_PICKED)
select $newfbatchid, sysdate, PROJECTNAME, $role, ID_TEAM_FINSERV, ID_AUTO, PRIMERS_PICKED, RESEQS_PICKED 
from finish_batch 
where id_fbatch = ?
";
                $sth6 = $dbh->prepare($stat6) || die "ERROR:$DBI::errstr";  
                $sth6->execute($orig_idfbatch) || die "ERROR:$DBI::errstr"; 
                
                my $stat7 = "insert into fbatch_status values(?,4,1,sysdate,?,?,?)";
                $sth7 = $dbh->prepare($stat7) || die "ERROR:$DBI::errstr";                  
                $sth7->execute($newfbatchid,$session_id,$prog,$who) || die "ERROR:$DBI::errstr";
                
#get the existing finish_request entries for a batch
#and make new ones and add an iteration to the generated
#finish_request entries, also generate the corresponding frequest_status
#entries. Generate entries in the request map
#table for these new entries (not null fields only).
                
                my $stat8 = "select b.id_frequest from finish_batch a, finish_request b where a.id_fbatch = b.id_fbatch and a.id_fbatch = ? and b.id_frequest in (select id_frequest from request_map where id_mapplate = ? ) order by b.id_frequest";
                my ($idfrequests) = $dbh->selectall_arrayref($stat8,{},$orig_idfbatch,$mapid) || die "ERROR:$DBI::errstr";
                
                my $sth_frval;
                my $sth_frins;
                my $sth_f7sins;
                my $sth_f3sins;
                my $sth_rmins;
                
                my $frcount = 0;
                
                foreach my $idfs (@{$idfrequests}) {
                    
                    $frcount++;
                    
#obtain a new frequest id.
                    my $stat_frval = "select seq_frequest.nextval from dual";
                    $sth_frval = $dbh->prepare($stat_frval);
                    $sth_frval->execute() || die "ERROR:$DBI::errstr";
                    my $nvfrequest = $sth_frval -> fetchrow();
                    
                    print FH "ENTRY $frcount : EXISTING id_frequest - @{$idfs} NEW id_frequest -  $nvfrequest\n";
                    
#enter copies of existing frequest entries
#with new frequest id's and a new fbatch id.
                    
                    my $stat_frins = "insert into finish_request (ID_FREQUEST,ID_FBATCH,PLATENAME,ROWLETTER,COLNUM,ID_SEQCHEM,ID_RUNTYPE,ITERATION,ID_PRIMER,ID_SCSP,ID_ADDITIVE)
select $nvfrequest,$newfbatchid,PLATENAME,ROWLETTER,COLNUM,ID_SEQCHEM,ID_RUNTYPE,'$iteration',ID_PRIMER,ID_SCSP,ID_ADDITIVE from finish_request where id_frequest= ?
";
                    $sth_frins = $dbh->prepare($stat_frins);
                    $sth_frins->execute(@{$idfs}) || die "ERROR:$DBI::errstr";
                    
#enter 2 new frequest status's for each one 7 (created for pp reorder) and 3 (assigned)
                    my $stat_f7sins  = "insert into frequest_status values(?,7,0,sysdate,?,?,?)";
                    $sth_f7sins = $dbh->prepare($stat_f7sins);
                    $sth_f7sins->execute($nvfrequest,$session_id,$prog,$who) || die "ERROR:$DBI::errstr";
                    
                    my $stat_f3sins  = "insert into frequest_status values(?,3,1,sysdate,?,?,?)";
                    $sth_f3sins = $dbh->prepare($stat_f3sins);
                    $sth_f3sins->execute($nvfrequest,$session_id,$prog,$who) || die "ERROR:$DBI::errstr";
                    
#populate the request_map table with the 
#id_frequest,id_mapplate & id_map (not null columns)
                    my $stat_rmins = "insert into request_map (ID_FREQUEST,ID_MAPPLATE,ID_MAP)select $nvfrequest,$newmapid,ID_MAP from request_map where id_frequest= ?
";
                    $sth_rmins = $dbh->prepare($stat_rmins);
                    $sth_rmins->execute(@{$idfs}) || die "ERROR:$DBI::errstr";
                    
                }
                
#end of each finish_request        
                $sth_frval->finish();
                $sth_frins->finish();
                $sth_f7sins->finish();
                $sth_f3sins->finish();
                $sth_rmins->finish();
                
            }
#end of each finish_batch
            $sth_nfb->finish();
            $sth6->finish();
            $sth7->finish();
            
            
#generate the new value for the id_loadingplate and
#generate a new loadingplate entry and lp_status entry 
#with a status of 12
            
            my $stat_lp = " select distinct a.id_loadingplate from 
loadingsample a, request_map b 
where a.id_loadingsample = b.id_loadingsample
and b.id_mapplate = ?
";
            my $sth_lp = $dbh ->prepare($stat_lp);  
            $sth_lp ->execute($mapid) || die "ERROR:$DBI::errstr";
            my $ovloadingplate = $sth_lp -> fetchrow();
            $sth_lp->finish();
            
            my $stat_lpval = "select seq_loadingplate.nextval from dual";
            my $sth_lpval = $dbh ->prepare($stat_lpval);
            $sth_lpval->execute() || die "ERROR:$DBI::errstr";
            my $nvloadingplate = $sth_lpval -> fetchrow();
            $sth_lpval->finish();
            
            print FH "\nOLD LOADINGPLATE ID - $ovloadingplate\n\n";
            print FH "NEW LOADINGPLATE ID - $nvloadingplate\n\n";
            
            my $newplate = "pfin$nvloadingplate";
            
            my $stat_lpins = "insert into loadingplate (ID_LOADINGPLATE,PLATENAME,TEAM,CATEGORY,WESTPAV,FORMAT,ID_RUNTYPE,SUFFIX,ITERATION,FASTTRACK,ID_PRIMERPLATE)
select $nvloadingplate,'$newplate',TEAM,CATEGORY,WESTPAV,FORMAT,ID_RUNTYPE,SUFFIX,ITERATION,FASTTRACK,ID_PRIMERPLATE from loadingplate where id_loadingplate in (
select distinct a.id_loadingplate from 
loadingsample a, request_map b 
where a.id_loadingsample = b.id_loadingsample
and b.id_mapplate = ?
)
";
            my $sth_lpins = $dbh ->prepare($stat_lpins);
            $sth_lpins->execute($mapid) || die "ERROR:$DBI::errstr";
            $sth_lpins->finish();
            
            
            my $stat_lpsins = "insert into loadingplate_status values (?,12,1,sysdate,?,?,?,?)";
            my $sth_lpsins = $dbh ->prepare($stat_lpsins);
            $sth_lpsins->execute($nvloadingplate,$session_id,$prog,$who,$filename) || die "ERROR:$DBI::errstr";
            $sth_lpsins->finish();
            
            my $stat_ls = "select a.id_loadingsample from 
loadingsample a, request_map b 
where a.id_loadingsample = b.id_loadingsample
and b.id_mapplate = ?
order by a.id_loadingsample
";
            my ($idlsamples) = $dbh->selectall_arrayref($stat_ls,{},$mapid) || die "ERROR:$DBI::errstr";
            
            my $sth_lsval;
            my $sth_lsins;
            my $sth_rmupd;
            
#foreach loadingsample entry make a new entry 
#with a new id_loadingsample and the new loadingplate id.
            my $lscount = 0;
            
            foreach my $idls (@{$idlsamples}) {
                
                $lscount++;
                
                my $stat_lsval = "select seq_loadingsample.nextval from dual";
                $sth_lsval = $dbh ->prepare($stat_lsval);
                $sth_lsval->execute() || die "ERROR:$DBI::errstr";
                my $nvloadingsample = $sth_lsval -> fetchrow();
                
                print FH "ENTRY $lscount : EXISTING id_loadingsample - @{$idls} NEW id_loadingsample - $nvloadingsample\n";     
                
                my $stat_lsins = "insert into loadingsample (ID_LOADINGSAMPLE,ID_LOADINGPLATE,ID_MAP,ID_SEQCHEM,ITERATION,ID_DNAPLATEMAP,ID_SCSP)
select $nvloadingsample,$nvloadingplate,ID_MAP,ID_SEQCHEM,ITERATION,ID_DNAPLATEMAP,ID_SCSP 
from loadingsample 
where id_loadingsample=?
";
                $sth_lsins = $dbh ->prepare($stat_lsins);
                $sth_lsins->execute(@{$idls}) || die "ERROR:$DBI::errstr";
                
                
                my $stat_rmupd = "update request_map set ID_LOADINGSAMPLE = $nvloadingsample where id_mapplate = ? and id_map in (select id_map from loadingsample where id_loadingsample = ?
)
";
                $sth_rmupd = $dbh ->prepare($stat_rmupd);
                $sth_rmupd->bind_param(1,$newmapid); 
                $sth_rmupd->bind_param(2,@{$idls});
                $sth_rmupd->execute() || die "ERROR:$DBI::errstr";
                
            }
            
            $sth_lsval->finish();
            $sth_lsins->finish();
            $sth_rmupd->finish();
            
            print FH "\n\n";
            print FH "END OF OUTPUT.\n\n";
            close(FH);
            
            return ($nvloadingplate);
        }
        
    }
    
}

1;

