package load_waitingpool;
#######################################################################
# 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: load_waitingpool.pm,v 1.6 2004/10/07 12:52:02 kva Exp $

use vars qw(@ISA @EXPORT);
use Exporter;
@ISA = ('Exporter');
@EXPORT = qw(&load_wp);
require Exporter;
#use DBI;

sub load_wp {
    
    my ($dbh, $dbh2, $loadingplate, $debug) = @_;
    
    my @lps = @$loadingplate;
    
#    my $dbi=;
#    my $db=; #live db
#    my $db_user=; #live db
#    my $dbh2;
#    
###log into generitrack
###pull the data via db link from cas.
###cross check the output with what is in WAITING_POOL
###Then insert the mapping data into the WAITING_POOL table.
#    
#    eval {
#        $dbh2 = DBI->connect("$dbi", "$db_user", "$db", {RaiseError => 1}) || die $DBI::errstr;
#        $dbh2->{AutoCommit} = 0;  
#    };
#    if ($@){
#        print "Error occurred - $@\n";
#    }
    
    if ((@lps) > 0) {
        
        my $lpcount = scalar(@lps);
        print ">>number of loadingplates = $lpcount<<\n" if $debug==1;

        my $statement = "select distinct ls.id_loadingplate 
from loadingsample ls, request_map rm, mapplate mp, loadingplate_status lps
where mp.id_mapplate = rm.id_mapplate
and rm.id_map in (1,97)
and rm.id_loadingsample = ls.id_loadingsample
and ls.id_loadingplate = lps.id_loadingplate
and mp.id_team_finserv in (select id_team_finserv from fin_finservice where finmethod = 'Robot')
and lps.status = 1
and lps.iscurrent = 1
and ls.id_loadingplate = ?";
        
        my $sth=$dbh->prepare($statement);
        

##sql to ensure that the most recent dnaplate is placed into the list.

        my $statement2 = "SELECT a.LP, a.RMIDMAP, dpm.id_dnaplate, dpm.id_map FROM (
SELECT distinct ls.id_loadingplate LP, rm.id_map RMIDMAP, max(dpm.id_dnaplatemap) IDDPMAP
FROM loadingsample ls, request_map rm, finish_request fr, beckman_box bb, bb_map bbm, dnaplate_map dpm, rowcol_map rcm
WHERE ls.id_loadingsample = rm.id_loadingsample 
AND rm.id_frequest = fr.id_frequest 
AND fr.platename = bb.name
AND bb.id_beckmanbox = bbm.id_beckmanbox 
AND fr.rowletter = rcm.maprow 
AND fr.colnum = rcm.mapcol 
AND rcm.id_map = bbm.id_map
AND bbm.id_bbmap = dpm.id_bbmap 
AND ls.id_loadingplate = ?
group by ls.id_loadingplate, rm.id_map
ORDER by rm.id_map)a, dnaplate_map dpm
WHERE a.IDDPMAP = dpm.id_dnaplatemap";

#select a.LP, a.RMIDMAP, MAX(a.DP), a.RCMIDMAP FROM (SELECT distinct ls.id_loadingplate LP, rm.id_map RMIDMAP, dpm.id_dnaplate DP, rcm.id_map RCMIDMAP FROM loadingsample ls, request_map rm, finish_request fr, beckman_box bb, bb_map bbm, dnaplate_map dpm, rowcol_map rcm, dna_plate dp WHERE ls.id_loadingsample = rm.id_loadingsample AND rm.id_frequest = fr.id_frequest AND fr.platename = bb.name AND bb.id_beckmanbox = bbm.id_beckmanbox AND bbm.id_bbmap = dpm.id_bbmap AND dpm.id_dnaplate = dp.id_dnaplate AND fr.rowletter = rcm.maprow AND fr.colnum = rcm.mapcol AND rcm.id_map = bbm.id_map AND ls.id_loadingplate = ? ORDER by rm.id_map)a GROUP BY a.LP, a.RMIDMAP, a.RCMIDMAP

        my $sth2=$dbh->prepare($statement2);
            
            my $statement1 = "select distinct targetplate_id
from waiting_pool
where targetplate_type = 'LP'
and targetplate_id = ?";
        my $sth1=$dbh2->prepare($statement1);
        
        my @avail_lps;
        my $lp;
        
        foreach (@lps){
            $sth->execute($_);
            while($lp=$sth->fetchrow()){
                    $sth1->execute($lp);
                    my $free_lp=$sth1->fetchrow();
                    unless ($free_lp){
                        print ">>LP $lp<<\n" if $debug==1;
                        push @avail_lps, $lp;
                    }
                }
        }
            
        my $insert_using_procedure_sth = $dbh2->prepare(q{
            BEGIN 
                waitingpool_package.WaitingPool(:tpref,:tplate,:twell,:spref,:splate,:swell); 
            END;
        });
        
        my ($tpl, $twell, $spl,$swell);
        my $comma;
        
        if ((@avail_lps) > 0) {
            
            eval {
                
                foreach (@avail_lps){
                    my $count=0;
                    $sth2->bind_param(1,$_);
                    $sth2->execute();
                    
                    my($ftpl,$ftwell,$fspl,$fswell);
                    
                    while(($tpl, $twell, $spl,$swell) = $sth2->fetchrow()){
                        $count++;
                        print "Entry $count --> $tpl, $twell, $spl,$swell\n" if $debug==1;
                        if ($count == 1){
                            $comma = '';
                        } else {
                            $comma = ',';
                        }
                        $ftpl.=$comma.$tpl;
                        $ftwell.=$comma.$twell;
                        $fspl.=$comma.$spl;
                        $fswell.=$comma.$swell;
                        
                    }
                    print ">>$ftpl<<\n" if $debug==1;
                    print ">>$ftwell<<\n" if $debug==1;
                    print ">>$fspl<<\n" if $debug==1;
                    print ">>$fswell<<\n" if $debug==1;
                    
                    ##need to execute the api procedure.
                    $insert_using_procedure_sth->bind_param(":tpref",'LP'); 
                    $insert_using_procedure_sth->bind_param(":tplate",$ftpl); 
                    $insert_using_procedure_sth->bind_param(":twell",$ftwell); 
                    $insert_using_procedure_sth->bind_param(":spref",'DP'); 
                    $insert_using_procedure_sth->bind_param(":splate",$fspl); 
                    $insert_using_procedure_sth->bind_param(":swell",$fswell);
                    $insert_using_procedure_sth->execute or die "ERROR:$DBI::errstr\n";
                } 
                
            };
            
            if ($@){
                $dbh2->rollback();
                print "Errors occurred :- $@.\n";
            } else {
                print "Information has been added without any errors.\n";
                $dbh2->commit();
            }
            
            
        } else {
            print "No information to be added into the WAITING_POOL table.\n";
        }
        
        $sth->finish();
        $sth1->finish();
        $sth2->finish();
        $insert_using_procedure_sth->finish;
        
        
        
    } 
    
#    END {
#        if ($dbh2){
#            eval {
#                $dbh2->rollback();
#                $dbh2->disconnect();
#            }
#        }
#    };
    
    
}


1;
