#!/usr/local/bin/perl 
#######################################################################
# 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: owl_make_WPplates.pm,v 1.2 1998/09/23 08:51:02 dn1 Exp $
# $Log: owl_make_WPplates.pm,v $
# Revision 1.2  1998/09/23  08:51:02  dn1
# *** empty log message ***
#
# Revision 1.1  1998/06/24  14:24:25  dn1
# Initial revision
#

require Exporter;

use DBI;

@owl_make_WPplates::ISA = qw(Exporter);
@owl_make_WPplates::EXPORT = qw(make_WPplates);

#use Data::Dumper;

package owl_make_WPplates;

# DN1
###################################################################################
#Create loadingplates for the WestPavilion from selected mapplates                # 
###################################################################################
# Supply the module with $db_database, $db_user, $db_pass -> for db connection
# Supply                 $teamname
# Supply                 $SESSIONID
# Supply                 $prog        -the program doing the submission
# Supply                 $who         -the user doing the submission      
# Supply                 @list (the list of selected plate id-s) 


sub make_WPplates {
    #warn "Inside make_WPplates\n";
    my($db_database, $db_user, $db_pass, $teamname, $SESSIONID, $prog, $who, @list) = @_;
    #warn @list;
    my $dbh = DBI->connect( $db_database, $db_user, $db_pass, 'Oracle') || die "ERROR: Could not connect to database: $DBI::errstr\n";
    
    my $plate;
    foreach $plate (@list){
	#warn "Inside foreach -> $plate\n";
	#######################################################    
	# 1) Create a new loading-plate and associated status #
	#######################################################
	# Get a new id_loadingplate-value
	my $nextid_statement = "select seq_loadingplate.nextval from dual";
	my $sthid = $dbh->prepare($nextid_statement) || die "ERROR:$DBI::errstr";
	$sthid->execute || die "ERROR:$DBI::errstr"; 
	$next_id = $sthid->fetchrow();
	$sthid->finish;
	#warn "new id is $next_id \n";

	# Create an entry for 'PLATENAME'
	# and get PLATE_FORMAT
	my $plate_statement = "
select PROTOCOL, ID_RUNTYPE, PLATE_FORMAT
from mapplate
where ID_MAPPLATE = \'$plate\'
";
	my $sthplate = $dbh->prepare($plate_statement) || die "ERROR:$DBI::errstr";
	$sthplate->execute || die "ERROR:$DBI::errstr"; 
	my ($PROTOCOL, $ID_RUNTYPE, $PLATE_FORMAT) = $sthplate->fetchrow();
	$sthplate->finish;
	if ($ID_RUNTYPE eq ''){
	    $ID_RUNTYPE = 'NULL';
	}
	my $plate_name = "$plate"."_"."$PROTOCOL"."$ID_RUNTYPE";
	#warn "new plate_name is $plate_name \n";

	# Make the loadingplate entry
	# (From seqcategorydict; 2 is Finishing)
	# FASTTRACK is set to zero for the moment
	if ($ID_RUNTYPE eq 'NULL'){
	    $ID_RUNTYPE = '';
	}
	my $lpstatement = "insert into loadingplate(ID_LOADINGPLATE, PLATENAME, TEAM, CATEGORY, INTERLEAVE, FORMAT, ID_RUNTYPE, FASTTRACK, WESTPAV) values (\'$next_id\', \'$plate_name\', \'$teamname\', 2, 0, \'$PLATE_FORMAT\', \'$ID_RUNTYPE\', 0, 1)";
	

	my $sthlp = $dbh->prepare($lpstatement) || die "ERROR:$DBI::errstr";
	$sthlp->execute || die "ERROR:$DBI::errstr"; 
	$sthlp->finish;

	# Create the associated-status entry
	# (status of 2 = Submitted)
	my $lps_statement = "
insert into loadingplate_status(ID_LOADINGPLATE, STATUS, ISCURRENT, STATUSDATE, SESSIONID, PROGRAM, OPERATOR)
values (\'$next_id\', 2, 1, sysdate, \'$SESSIONID\', \'$prog\', \'$who\')
";
	#warn "\n\n $lps_statement\n";

	my $sthlps = $dbh->prepare($lps_statement) || die "ERROR:$DBI::errstr";
	$sthlps->execute || die "ERROR:$DBI::errstr"; 
	$sthlps->finish;

	#######################################################################################    
	# 2) Change the status of this mapplate as we don't want to make another loadingplate #
	#######################################################################################
        # make the old status-entry (where iscurrent = 1) iscurrent = 0
	my $upis_statement = "
update mapplate_status
set iscurrent = 0
where iscurrent = 1
and id_mapplate = \'$plate\'
";
	my $sthupis = $dbh->prepare($upis_statement) || die "ERROR:$DBI::errstr";
	$sthupis->execute || die "ERROR:$DBI::errstr"; 
	$sthupis->finish;
	
	# make a new status entry
	# (status = 4; Fully assigned (mapplatestatusdict)
	my $upmap_statement = "insert into mapplate_status(ID_MAPPLATE, STATUS, STATUSDATE, ISCURRENT, SESSIONID, PROGRAM, OPERATOR) values (\'$plate\', 4, sysdate, 1, \'$SESSIONID\', \'$prog\', \'$who\')";

	#warn "\n\n $upmap_statement\n";

	my $sthupmap = $dbh->prepare($upmap_statement) || die "ERROR:$DBI::errstr";
	$sthupmap->execute || die "ERROR:$DBI::errstr"; 
	$sthupmap->finish;


	#############################################################################################    
	# 3) Enter an id_loadingsample entry into table LOADINGSAMPLE for each well of the mapplate #
	#############################################################################################
	# get the id_map positions for the mapplate in question
	my $map_statement = "
select id_map, id_frequest
from request_map
where id_mapplate = \'$plate\'
";
	#warn "\n\n $map_statement \n";

	my $sthmap = $dbh->prepare($map_statement) || die "ERROR:$DBI::errstr";
	$sthmap->execute || die "ERROR:$DBI::errstr"; 
	while (($id_map, $ID_FREQUEST) = $sthmap->fetchrow()){
	    #warn "plate -> $plate .... id_map -> $id_map .... id_frequest -> $ID_FREQUEST\n";

	    # What is the seqchem for this particular $ID_FREQUEST
	    my $f_statement = "
select ID_SEQCHEM
from finish_request
where id_frequest = \'$ID_FREQUEST\'
";
	    my $sthf = $dbh->prepare($f_statement) || die "ERROR:$DBI::errstr";
	    $sthf->execute || die "ERROR:$DBI::errstr"; 
	    my $ID_SEQCHEM = $sthf->fetchrow();
	    $sthf->finish;
	    #warn "Got ID_SEQCHEM -> $ID_SEQCHEM \n";

	    # Get a new number in the sequence for id_loadingsample
	    my $samid_statement = "select seq_loadingplate.nextval from dual";
	    my $sthsam = $dbh->prepare($samid_statement) || die "ERROR:$DBI::errstr";
	    $sthsam->execute || die "ERROR:$DBI::errstr"; 
	    $ID_LOADINGSAMPLE = $sthsam->fetchrow();
	    $sthsam->finish;
	    #warn "New loading sample id is -> $ID_LOADINGSAMPLE \n";

	    
	    # Create the loadingsample entry
	    my $ls_statement = "
insert into loadingsample(ID_LOADINGSAMPLE, ID_LOADINGPLATE, ID_MAP, ID_SEQCHEM)
values (\'$ID_LOADINGSAMPLE\', \'$next_id\', \'$id_map\', \'$ID_SEQCHEM\')
";
	    my $sthls = $dbh->prepare($ls_statement) || die "ERROR:$DBI::errstr";
	    $sthls->execute || die "ERROR:$DBI::errstr"; 
	    $sthls->finish;

	    
	    # update ID_LOADINGSAMPLE entry in REQUEST_MAP
	    my $rm_statement = "
update request_map
set ID_LOADINGSAMPLE = \'$ID_LOADINGSAMPLE\'
where ID_MAPPLATE = \'$plate\'
and ID_MAP = \'$id_map\'
";

	    #warn "BUG\n\n$rm_statement\n";

	    my $sthrm = $dbh->prepare($rm_statement) || die "ERROR:$DBI::errstr";
	    $sthrm->execute || die "ERROR:$DBI::errstr"; 
	    $sthrm->finish;
	    
	    ########################################################################
	    # 4) Inform the finisher (via finch) that this sample is WESTPAV bound # 
	    ########################################################################
	    #warn "updating frequest_status... \n";
	    # Set old record to iscurrent = 0 first
	    my $freq_statement = "
update frequest_status
set iscurrent = 0
where iscurrent = 1
and status = 3
and ID_FREQUEST = \'$ID_FREQUEST\'
";
	    my $sthfreq = $dbh->prepare($freq_statement) || die "ERROR:$DBI::errstr";
	    $sthfreq->execute || die "ERROR:$DBI::errstr";
	    $sthfreq->finish;
	    # ...and make a new record
	    my $fr_statement = "insert into frequest_status(ID_FREQUEST, STATUS, ISCURRENT, STATUSDATE, SESSIONID, PROGRAM, OPERATOR) values (\'$ID_FREQUEST\', 4, 1, sysdate, \'$SESSIONID\', \'$prog\', \'$who\')
";
	    my $sthfr = $dbh->prepare($fr_statement) || die "ERROR:$DBI::errstr";
	    $sthfr->execute || die "ERROR:$DBI::errstr";
	    $sthfr->finish;

	}
	$sthmap->finish;
	 
	


    } #end of foreach $plate (@list){
    


    ################################
    # Disconnect from the database #
    ################################
    $dbh->disconnect;

}

1;

