#######################################################################
# 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.                      #
#######################################################################

###################################################
# dn1; A new approach to platetrack db interaction
# ie. Prepare everything once and then re-use
# from an object.
###################################################

package statements;

sub new {
    my ($pkg, $dbh) = @_;
    
    ############################
    # What are the statements ?
    ############################

    # Ponder... how can we load in only the statements that we need ?

    my $st = {

##################
# platetrack_pick
##################
	      
	"today"             => 
"select to_char(sysdate,'dd-Mon-YYYY') from dual",

	    
	"source_plate_info" => 

"select sp.platename, sps.status,sp.format, sp.id_ligation, l.clonename 
from source_plate sp, sourceplate_status sps, ligation l
where sp.id_sourceplate = sps.id_sourceplate 
and sp.id_ligation = l.id_ligation
and sps.iscurrent = 1 
and sp.id_sourceplate = ?",

        "clonename2teamname" =>

# Note that if a clone has multiple-projects, there may be multiple owners 

"select cp.projectname, po.teamname
from clone c, clone_project cp, project_owner po
where c.clonename = cp.clonename
and cp.projectname = po.projectname
and c.clonename = ?
and po.iscurrent = 1
and po.owner_type = 2",

        "96_to_384position" =>

"select ap.source_idmap
from array_pattern ap
where ap.id_arraypattern = 2 
and ap.source_format = 384
and ap.source_quadrant = 1
and ap.target_idmap = ?
and ap.target_format = 96
and ap.target_quadrant = ?
",

        "idsubcloneplatemap_for_bbmap" =>

"select sm.id_sourceplatemap
from sourceplate_map sm
where sm.id_sourceplate = ?
and sm.id_map = ?
",
    
        "make_bbmap_entry"      =>
	
"insert into bb_map (ID_BBMAP, ID_SUBCLONE, ID_BECKMANBOX, ID_MAP, ID_SOURCEPLATEMAP) 
values (?, ?, ?, ?, ?)",

##################
# platetrack_seq2
##################

         "all_available_categories" =>

"select scd.description, scd.id_dict
from seqcategorydict scd",


         "available_categories" =>

"select distinct ps.category, scd.description, scd.id_dict
from procstrategy ps,
seqcategorydict scd
where ps.category = scd.id_dict
and teamname = ?
and user_selectable = 1",     


         "can_team_process_EST"  =>

"select 'X'
from procstrategy ps,
seqcategorydict scd
where ps.category = scd.id_dict
and teamname = ?
and scd.description = 'EST plate'",  
    

         "boxinfo_from_dpid" =>

"select distinct dp.id_dnaplate, bb.name, bb.format, dps.status, dpsd.description, bbm.id_sourceplatemap
from dna_plate dp, 
dnaplate_status dps, 
dnaplatestatusdict dpsd, 
dnaplate_map dpm, 
bb_map bbm, 
beckman_box bb
where dp.id_dnaplate = dps.id_dnaplate
and dps.status = dpsd.id_dict
and dp.id_dnaplate = dpm.id_dnaplate
and dpm.id_bbmap = bbm.id_bbmap
and bbm.id_beckmanbox = bb.id_beckmanbox
and dp.id_dnaplate = ?
and dp.copy_type = ?
and dps.iscurrent = 1
and dpm.id_map = 1",


    "last_loadingsample_iteration_of_dnaplate" =>

"select /*+ USE_NL(loadingplate) */ 
distinct lp.id_loadingplate, decode(ls.iteration, NULL, 'Z', ls.iteration)
from dnaplate_map dpm, loadingsample ls, loadingplate lp, loadingplate_status lps
where dpm.id_dnaplatemap = ls.id_dnaplatemap
and ls.id_loadingplate = lp.id_loadingplate
and lps.id_loadingplate = lp.id_loadingplate
and dpm.id_dnaplate = ?
and lp.suffix = ?
and lps.status != 6
and lps.iscurrent = 1 
order by 2 desc",


# cf NEWlast_iteration_of_loadingplate
    "last_loadingsample_iteration_of_sourceplate" =>

"select distinct lp.id_loadingplate, decode(ls.iteration, NULL, 'Z', ls.iteration)
from source_plate sp,
sourceplate_map spm,
bb_map bbm, 
dnaplate_map dpm,
loadingsample ls,
loadingplate lp,
loadingplate_status lps
where spm.id_sourceplate = sp.id_sourceplate
and spm.id_sourceplatemap = bbm.id_sourceplatemap
and bbm.id_bbmap = dpm.id_bbmap
and dpm.id_dnaplatemap = ls.id_dnaplatemap
and ls.id_loadingplate = lp.id_loadingplate
and ls.id_loadingplate = lps.id_loadingplate
and lps.status != 6
and lps.iscurrent = 1
and lp.suffix = ? 
and spm.id_sourceplatemap = ?
order by 2 desc
"
};


    foreach my $sts (keys %$st){
	#warn "Now preparing * $sts *\n";
	#warn "$st->{$sts}\n";
	$st->{$sts} = $dbh->prepare("$st->{$sts}") || die "ERROR:$DBI::errstr";
    }
    
    bless $st, $pkg;
    return $st;
}


sub insert {
    my ($self, $statement, $binds) = @_;

    eval {                          
	$self->{$statement}->execute(@$binds);
    };
    if($@){
	die "ERROR $self sub insert :\n\n$@\n";
    }
    $self->{$statement}->finish;
}


sub get {
    my ($self, $statement, $binds) = @_;
    eval {                          
	$self->{$statement}->execute(@$binds);
    };
    if($@){
	die "ERROR $self sub get :\n\n$@\n";
    }
    my $t = $self->{$statement}->fetchall_arrayref();
    $self->{$statement}->finish;
    return ($t);
}


sub DESTROY {
    my ($self) = @_;
    foreach my $sts (keys %$self){
	#warn "Now finishing handle for * $sts *\n";
	$self->{$sts}->finish;
    }
}



1;



