#!/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_commit_blocks.pm,v 1.3 1998/09/23 08:40:38 dn1 Exp $
# $Log: owl_commit_blocks.pm,v $
# Revision 1.3  1998/09/23  08:40:38  dn1
# *** empty log message ***
#
# Revision 1.2  1998/07/08  15:39:38  dn1
# #if ($next =~ /^Number\s(\d+)/){
# changed to...
# if ($next =~ /^\w+-(\d+)/){
#
# Revision 1.1  1998/06/24  09:36:17  dn1
# Initial revision
#

require Exporter;

use DBI;

@owl_commit_blocks::ISA = qw(Exporter);
@owl_commit_blocks::EXPORT = qw(commit);

#use Data::Dumper;

package owl_commit_blocks;

# DN1
################################################################################
# Submit map requests to the database                                          #
################################################################################
# Supply the module with $db_database, $db_user, $db_pass -> for db connection
#                        $map_structure -> the mapped sample datastructure
#                        $\@sel_maps    -> the maps you want to commit from the above datastructure
#                        $format        -> the plate-format
#                        $ID_TEAM_FINSERV      -> the finishing service
#                        $session_id    -> for status-updates
#                        $who           -> operator

sub commit {
    my($db_database, $db_user, $db_pass, $map_structure, $sel_maps, $format, $ID_TEAM_FINSERV, $session_id, $who, $my_status) = @_;

    # CONNECT TO THE DATABASE
    my $dbh = DBI->connect( $db_database, $db_user, $db_pass, 'Oracle') || die "ERROR: Could not connect to database: $DBI::errstr\n";

    #warn "inside owl_commit_blocks -> $db_database, $db_user, $db_pass, $map_structure, $sel_maps, $format, $fsname, $session_id, $who \n";
    #my $capture = Data::Dumper::Dumper($map_structure);
    #warn $capture;
    #my $capture2 = Data::Dumper::Dumper($sel_maps);
    #warn "selected_maps ... \n";
    #warn $capture2;
    # hmmmm  we want to take the selected maps ($sel_maps) from $map_structure and put them in the database
    # Relevant database tables here are;
    # Request_Map
    # Mapplate
    
    my $next;
    my $id_mapplate;
    my $lastmap = 'None';
    my $thismap;

    ############################################
    # ..store the new mapID's used in an array #
    ############################################
    my @plates_used = ();

    foreach $next (@$sel_maps){
	# go through each selected map in turn
	#warn "----> $next \n";
	my $map;
	#if ($next =~ /^Number\s(\d+)/){
	if ($next =~ /^\w+-(\d+)/){
	    #warn "debug -> $1 \n";
	    $map = $1;
	} else {
	    #warn "Something is not cool ! \n";
	    return;
	}
	# Need to grab a new plate
	my $id_mapplate = &newplate($dbh);

	push @plates_used, $id_mapplate;

	#warn "new-plate -> $id_mapplate \n";
	# find the data for this map
	my $n;
	foreach $n (0 .. $#{$map_structure}){

	    if ($map_structure->[$n]->{contents}->[0] == $map){
		#warn "$map_structure->[$n]->{contents}->[2] \n";
		#warn "$map_structure->[$n]->{plate_type} \n";
		my $PROTOCOL = substr $map_structure->[$n]->{plate_type}, 0, 3;	
		my $ID_RUNTYPE = substr $map_structure->[$n]->{plate_type}, 3;
		#warn "PROTOCOL -> $PROTOCOL \n";
		if ($ID_RUNTYPE eq 'NULL'){
		    $ID_RUNTYPE = '';
		}
		#warn "ID_RUNTYPE -> $ID_RUNTYPE \n";
		#ID_MAPPLATE   -> $id_mapplate 
                # PLATE_FORMAT -> $format 
		# FSNAME       -> $fsname  
                # ID_RUNTYPE   -> $PROTOCOL
                # PROTOCOL     -> $ID_RUNTYPE               
		
		$thismap = $map;

		#warn "THISMAP -> $thismap LASTMAP -> $lastmap \n";

		if ($thismap ne $lastmap){

		    #warn "CODE EXECUTED !!!!\n";

		    #######################################################
		    # Make a new plate in MAPPLATE if this IS a new plate #
		    #######################################################
#		    my $statement = "
#insert into MAPPLATE (ID_MAPPLATE, PLATE_FORMAT, FSNAME, ID_RUNTYPE, PROTOCOL)
#values (\'$id_mapplate\',\'$format\',\'$fsname\',\'$ID_RUNTYPE\',\'$PROTOCOL\')
#";

my $statement = "
insert into MAPPLATE (ID_MAPPLATE, PLATE_FORMAT, ID_TEAM_FINSERV, ID_RUNTYPE, PROTOCOL)
values (\'$id_mapplate\',\'$format\',\'$ID_TEAM_FINSERV\',\'$ID_RUNTYPE\',\'$PROTOCOL\')
";

		    #warn "statement -> $statement \n";
		    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
		    unless ($sth->execute) {
			#&rollback;
			die "Rollback NOT activated. ERROR:$DBI::errstr";
		    }
		    $sth->finish;

		    #######################################################
		    # Create a status for this new plate of 'Created'     #
		    #######################################################
		    my $statement2 = "
insert into MAPPLATE_STATUS (ID_MAPPLATE, STATUS , STATUSDATE, ISCURRENT, SESSIONID, PROGRAM, OPERATOR)
values (\'$id_mapplate\', \'$my_status\', sysdate, 1, \'$session_id\', 'owl', \'$who\')
";
		    #warn "status -> $statement2 \n";
		    my $sth2 = $dbh->prepare($statement2) || die "ERROR:$DBI::errstr";
		    unless ($sth2->execute) {
			#&rollback;
			die "Rollback NOT activated. ERROR:$DBI::errstr";
		    }
		    $sth2->finish;

		}
		$lastmap = $thismap;

		#######################################################
		# Fill up REQUEST_MAP                                 #
		#######################################################
		my $statement3 = "
insert into REQUEST_MAP (ID_FREQUEST, ID_MAPPLATE, ID_MAP)
values (\'$map_structure->[$n]->{contents}->[2]\',\'$id_mapplate\',\'$map_structure->[$n]->{contents}->[1]\')
";
		#warn "statement -> $statement3 \n";
		my $sth3 = $dbh->prepare($statement3) || die "ERROR:$DBI::errstr";
		unless ($sth3->execute) {
		    #&rollback;
		    die "Rollback NOT activated. ERROR:$DBI::errstr";
		}
		$sth3->finish;

		############################################################################
		# update FREQUEST_STATUS for this sample ie. set is current to zero first  #
		############################################################################
		my $statement4 = "
update frequest_status
set iscurrent = 0 
where ID_FREQUEST = \'$map_structure->[$n]->{contents}->[2]\'
";
		$sth4 = $dbh->prepare($statement4) || die "ERROR:$DBI::errstr";
		unless ($sth4->execute) {
		    &rollback;
		    die "Rollback activated. ERROR:$DBI::errstr";
		}
		$sth4->finish;
		
		##############################################################
		# update FREQUEST_STATUS for this id_frequest to 'Assigned'; #
		##############################################################
		my $statement5 = "
insert into FREQUEST_STATUS (ID_FREQUEST,STATUS,ISCURRENT,STATUSDATE,PROGRAM,OPERATOR)
values (\'$map_structure->[$n]->{contents}->[2]\',3 ,1,sysdate,\'owl\',\'$who\')
";
		$sth5 = $dbh->prepare($statement5) || die "ERROR:$DBI::errstr";
		unless ($sth5->execute) {
		    &rollback;
		    die "Rollback activated. ERROR:$DBI::errstr";
		}
		$sth5->finish;
	    
	    
	    } else {
		next;
	    }
	}
    } # matches foreach $next (@$sel_maps){

    # DISCONNECT FROM THE DATABASE
    $dbh->disconnect();

    ###############################
    # Return the new mapID's made #
    ###############################
    return (@plates_used);
    

}


sub newplate {
    # grab a new mapplate -> ID_MAPPLATE from the database
    my ($dbh) = @_;
    my $statement_id = "select seq_mapplate.nextval from dual";
    my $sth_id = $dbh->prepare($statement_id) || die "ERROR:$DBI::errstr";
    $sth_id->execute || die "ERROR:$DBI::errstr";
    my $map_id = $sth_id->fetchrow();
    $sth_id->finish;
    #warn "grabbed map_id ---> $map_id \n";
    return $map_id;
}	

1;
