package Owl_commit_blocks2;

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

#                        NOTES ON INCOMING PARAMETERS
#                        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#
#                        $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
#                        $my_status     -> it is possible to 'force' the mapplate_status
#                        $protocols     -> provides a mapping between the id_protocol
#                                          and the protocol 'name'
#                        $ext_primer    -> 1 indicates the primers for the mapplate are to
#                                          be ordered externally. 2 indicated that no primers were
#                                          ordered but the samples are for resequencing
#                        $team          -> ... is needed to make a loadingplate entry wrt
#                                          external primer orders
#                        $id_robot      -> Indicates which robot the orders are for


sub commit {

    # dn1 $ext_primer, $id_robot
    my ($dbh, $map_structure, $sel_maps, $format, $ID_TEAM_FINSERV, $session_id, $who, $my_status, $protocols, $ext_primer, $team, $id_robot) = @_;

    # We need a list of the loadingplates made if this is
    # an external primer-order
    my @loadingplates;


    # dn1
    unless ($ext_primer){
	$ext_primer = 0;
    }

    unless ($team){
	$team = "Inappropriate";
    }

    unless ($id_robot){
	$id_robot = 0;
    }


    my $lastmap = 'None';
    my $thismap;
    my $errors = 0;
    my @update_report;
    my @plates_used = (); # ..store the new mapID's used 

    my $statement_id = "select seq_mapplate.nextval from dual";
    my $sth1 = $dbh->prepare($statement_id) || die "ERROR:$DBI::errstr";



    # newdn1
#    my $statement_map = "insert into MAPPLATE (ID_MAPPLATE, PLATE_FORMAT, ID_TEAM_FINSERV, ID_PROTOCOL) values (?, \'$format\',\'$ID_TEAM_FINSERV\',?)";
    my $statement_map = "insert into MAPPLATE (ID_MAPPLATE, PLATE_FORMAT, ID_TEAM_FINSERV, ID_PROTOCOL) values (?, ?, ?, ?)";




    my $sth2 = $dbh->prepare($statement_map) || die "ERROR:$DBI::errstr";


    # dn1new
    #my $statement_mapstatus = "insert into MAPPLATE_STATUS (ID_MAPPLATE, STATUS , STATUSDATE, ISCURRENT, SESSIONID, PROGRAM, OPERATOR)values (?, ?, sysdate, 1, \'$session_id\', 'Owl_commit_blocks2.pm', \'$who\')";
    my $statement_mapstatus = "insert into MAPPLATE_STATUS (ID_MAPPLATE, STATUS , STATUSDATE, ISCURRENT, SESSIONID, PROGRAM, OPERATOR)values (?, ?, sysdate, ?, ?, ?, ?)";


    my $sth3 = $dbh->prepare($statement_mapstatus) || die "ERROR:$DBI::errstr";
    



    #############################################################
    # dn1 2x versions; 1 with a bind to loadingsample/ 1 without
    #############################################################
    
    my $statement_rm1 = "insert into REQUEST_MAP (ID_FREQUEST, ID_MAPPLATE, ID_MAP, ID_LOADINGSAMPLE)values (?, ?, ?, ?)";
    my $sth44 = $dbh->prepare($statement_rm1) || die "ERROR:$DBI::errstr";


    my $statement_rm = "insert into REQUEST_MAP (ID_FREQUEST, ID_MAPPLATE, ID_MAP)values (?, ?, ?)";
    my $sth4 = $dbh->prepare($statement_rm) || die "ERROR:$DBI::errstr";



    my $statement_upf = "update FREQUEST_STATUS set iscurrent = 0 where ID_FREQUEST = ?";
    my $sth5 = $dbh->prepare($statement_upf) || die "ERROR:$DBI::errstr";

    #my $statement_newf = "insert into FREQUEST_STATUS (ID_FREQUEST, STATUS, ISCURRENT, SESSIONID, STATUSDATE, PROGRAM, OPERATOR) values (?, 3 , 1, \'$session_id\', sysdate, 'Owl_commit_blocks2.pm', \'$who\')";
    my $statement_newf = "insert into FREQUEST_STATUS (ID_FREQUEST, STATUS, ISCURRENT, SESSIONID, STATUSDATE, PROGRAM, OPERATOR) values (?, ? , ?, ?, sysdate, ?, ?)";

    my $sth6 = $dbh->prepare($statement_newf) || die "ERROR:$DBI::errstr";

 


    # dn1 new statements to make loadingplate and loadingplate status entries
    my ($st_lp, $sth7, $st_lps, $sth8, $st_lpi, $sth9, $st_lsi, $sth10, $st_fr, $sth11, $st_lsample, $sth12);
    if (($ext_primer == 1) || ($ext_primer == 2)){
	$st_lp = "insert into LOADINGPLATE (ID_LOADINGPLATE, PLATENAME, TEAM, CATEGORY, WESTPAV, FORMAT, FASTTRACK)
values (?, ?, ?, ?, ?, ?, ?)";
	$sth7 = $dbh->prepare($st_lp) || die "ERROR:$DBI::errstr";
    
	$st_lps = "insert into LOADINGPLATE_STATUS (ID_LOADINGPLATE, STATUS, ISCURRENT, STATUSDATE, SESSIONID, PROGRAM, OPERATOR)
values (?, ?, ?, sysdate, ?, ?, ?)";
	$sth8 = $dbh->prepare($st_lps) || die "ERROR:$DBI::errstr";

	$st_lpi = "select seq_loadingplate.nextval from dual";
	$sth9 = $dbh->prepare($st_lpi) || die "ERROR:$DBI::errstr";

	$st_lsi = "select seq_loadingsample.nextval from dual";
	$sth10 = $dbh->prepare($st_lsi) || die "ERROR:$DBI::errstr";

	$st_fr = "select iteration, id_scsp from FINISH_REQUEST where id_frequest = ?";
	$sth11 = $dbh->prepare($st_fr) || die "ERROR:$DBI::errstr";

	$st_lsample = "insert into LOADINGSAMPLE (ID_LOADINGSAMPLE, ID_LOADINGPLATE, ID_MAP, ITERATION, ID_SCSP) values (?, ?, ?, ?, ?)";
	$sth12 = $dbh->prepare($st_lsample) || die "ERROR:$DBI::errstr";
	

    }

    
    # dn1; need some info to fill in mapplate_lookup
    #################################################
    my ($s_map, $sth_s_map, $dp_info, $sth_dp_info, $mapplate_lookup, $sth_mapplate_lookup);
    if ($id_robot > 0){
	$s_map = "
select rm.id_map
from finish_request fr, rowcol_map rm
where fr.rowletter = rm.maprow
and fr.colnum = rm.mapcol
and rm.format = 96
and fr.id_frequest = ?
";
	$sth_s_map = $dbh->prepare($s_map) || die "ERROR:$DBI::errstr";

	$dp_info = "
select distinct dp.id_dnaplate, dm.id_map, dm.id_dnaplatemap
from finish_request fr, beckman_box bb, bb_map bm, dnaplate_map dm, dna_plate dp
where fr.platename = bb.name
and bb.id_beckmanbox = bm.id_beckmanbox
and bm.id_bbmap = dm.id_bbmap
and dm.id_dnaplate = dp.id_dnaplate
and fr.id_frequest = ?
and dp.copy_type = 3
and bm.id_map = ?
";
	$sth_dp_info = $dbh->prepare($dp_info) || die "ERROR:$DBI::errstr";


	$mapplate_lookup = "
insert into mapplate_lookup
(ID_MAPPLATE, ID_MPMAP, ID_DNAPLATE, ID_DNAPLATEMAP, ID_DPMAP, VOLUME_NEEDED, ID_ROBOT)
values (?, ?, ?, ?, ?, ?, ?)
";
	$sth_mapplate_lookup = $dbh->prepare($mapplate_lookup) || die "ERROR:$DBI::errstr";
    }
    

    # Collect all our statement handles together
    my @prepared = (\$sth1, \$sth2, \$sth3, \$sth4, \$sth44, \$sth5, \$sth6);
    if (($ext_primer == 1) || ($ext_primer == 2)){
        push @prepared, \$sth7;
        push @prepared, \$sth8;
        push @prepared, \$sth9;
        push @prepared, \$sth10;
        push @prepared, \$sth11;
        push @prepared, \$sth12;
    }
    if ($id_robot > 1){
        push @prepared, \$sth_s_map;
        push @prepared, \$sth_dp_info;
        push @prepared, \$sth_mapplate_lookup;
    }



    foreach my $next (@$sel_maps){ # go through each selected map in turn
	my $map;
	if ($next =~ /\w+-(\d+)$/){
	    $map = $1;
	} else {
	    die "Cannot parse $next ! Contact bug.\n";
	}

	warn "\tLooking at map... $map\n";
	
	my $count = 0;

	# Get a new id_mapplate value
	$sth1->execute || die "ERROR:$DBI::errstr";
	my $map_id = $sth1->fetchrow();
	push @plates_used, $map_id;
	


	# dn1
	my $loadingplate_id;
	my $custom_primer_map = 0;

	# Find the data for this map
	foreach my $n (0 .. $#{$map_structure}){

	    if ($map_structure->[$n]->{contents}->[0] == $map){ # This one !

		$map_structure->[$n]->{plate_type} =~ /^(\w+-\w+)/;
		my $PROTOCOL = $1;

		$thismap = $map;
		
		if ($thismap ne $lastmap){

		    # dn1
		    # Is this a custom primer map ?
		    warn "\tPROTOCOL $PROTOCOL\n";
		    if ($PROTOCOL =~ /^\w+-\w2\w/){
			warn "\t\tPlate contains custom primers\n";
			$custom_primer_map = 1;
		    } else {
			warn "\t\t*NOT* a custom primer plate\n";
			$custom_primer_map = 0;
		    }
		    


		    # Make an entry for mapplate
		    $sth2->bind_param(1, $map_id);

                    # newdn1
                    $sth2->bind_param(2, $format);
                    $sth2->bind_param(3, $ID_TEAM_FINSERV);

		    # Need a mapping between id_protocol and $PROTOCOL
		    my $P = $protocols->{$PROTOCOL};
		    $sth2->bind_param(4, $P);

		    eval {
			$sth2->execute || die "ERROR:$DBI::errstr";
		    };


		    if ($@){
			$errors++;

                        &handle_cleanup(\@prepared);

			push @update_report, "Problems making mapplate entry $@";
			push @update_report, "Contact bug.";
			return (\@update_report, $errors, \@plates_used, \@loadingplates);
		    }



		    # Make an entry for mapplate_status
		    $sth3->bind_param(1, $map_id);
		    
		    # dn1; different status depending on case
		    # What status are we adding ?
		    if ($my_status ne ''){
			$sth3->bind_param(2, $my_status);
		    } elsif ( ($ext_primer == 1)  && ($custom_primer_map == 1) && ($id_robot == 0) ){
			$sth3->bind_param(2, 13); # JIT Precreated
		    } elsif ($id_robot > 0){
			$sth3->bind_param(2, 5);  # Robot Created 
		    } else {
			$sth3->bind_param(2, 10); # JIT Created
		    }

		    $sth3->bind_param(3, 1);
                    $sth3->bind_param(4, $session_id);
                    $sth3->bind_param(5, 'Owl_commit_blocks2.pm');
                    $sth3->bind_param(6, $who);

		    eval {
			$sth3->execute || die "ERROR:$DBI::errstr";
		    };
		    if ($@){
			$errors++;

                        &handle_cleanup(\@prepared);

			push @update_report, "Problems making mapplate_status entry $@";
			push @update_report, "Contact bug.";
			return (\@update_report, $errors, \@plates_used, \@loadingplates);
		    }

		    

		    # dn1
                    # If this is an external primer plate order, we also need
		    # to make an entry for loadingplate
		    
		    if ( ($ext_primer == 1) && ($custom_primer_map == 1) ){
			
			# Get a new id_loadingplate value
			# dn1
			$sth9->execute || die "ERROR:$DBI::errstr";
			$loadingplate_id = $sth9->fetchrow();

			
			# Make an entry for loadingplate
			my $lp_platename = "pfin" . "$loadingplate_id";
			$sth7->bind_param(1, $loadingplate_id);
			$sth7->bind_param(2, $lp_platename);
			$sth7->bind_param(3, $team);
			$sth7->bind_param(4, 4);
			$sth7->bind_param(5, 0);
			$sth7->bind_param(6, $format);
			$sth7->bind_param(7, 0);
			eval {
			    $sth7->execute || die "ERROR:$DBI::errstr";
			};
			
			
			if ($@){
			    $errors++;

                            &handle_cleanup(\@prepared);

			    push @update_report, "Problems making loadingplate entry $@";
			    push @update_report, "Contact bug.";
			    return (\@update_report, $errors, \@plates_used, \@loadingplates);
			}

			# A text record of what should be linked to what
			warn "\t\tMapplate $map_id-> Loadingplate $loadingplate_id\n";

                        # Keep a running score of the precreated loadingplate
                        # entries that we have made
                        push @loadingplates, $loadingplate_id;

			# Make a loadingplate_status entry
			$sth8->bind_param(1, $loadingplate_id);
			$sth8->bind_param(2, 12);
			$sth8->bind_param(3, 1);
			#$sth8->bind_param(4, 'sysdate');
			$sth8->bind_param(4, $session_id);
			$sth8->bind_param(5, "Owl_commit_blocks2.pm");
			$sth8->bind_param(6, $who);

			eval {
			    $sth8->execute || die "ERROR:$DBI::errstr";
			};
			
			
			if ($@){
			    $errors++;

                            &handle_cleanup(\@prepared);

			    push @update_report, "Problems making loadingplate_status entry $@";
			    push @update_report, "Contact bug.";
			    return (\@update_report, $errors, \@plates_used, \@loadingplates);
			}
			
		    }


		    $lastmap = $thismap;
		}

		
		# Note that if this is an external primer plate order;
		# before we can fill in loadingsample in request_map
		# we must fill in the actual entry for loadingsample

		
		################################
		# Get; finish_request.iteration
		#      finish_request.id_scsp
		################################
		my ($fr_iteration, $fr_id_scsp, $id_loadingsample);
		if ( ($ext_primer == 1) && ($custom_primer_map == 1) ){
		    $sth11->bind_param(1, $map_structure->[$n]->{contents}->[2]);
		    $sth11->execute || die "ERROR:$DBI::errstr";
		    ($fr_iteration, $fr_id_scsp) = $sth11->fetchrow();
		    
		    
		    #############################################################
		    # Get a new id_loadingplate and make the loadingsample entry
		    #############################################################
		    $sth10->execute || die "ERROR:$DBI::errstr";
		    $id_loadingsample = $sth10->fetchrow();
		    
		    $sth12->bind_param(1, $id_loadingsample);
		    $sth12->bind_param(2, $loadingplate_id);
		    $sth12->bind_param(3, $map_structure->[$n]->{contents}->[1]);
		    $sth12->bind_param(4, $fr_iteration);
		    $sth12->bind_param(5, $fr_id_scsp);
		    
		    
		    eval {
			$sth12->execute || die "ERROR:$DBI::errstr";
		    };
		    
		    if ($@){
			$errors++;
			
                        &handle_cleanup(\@prepared);
			
			push @update_report, "Problems making loadingsample entry $@";
			push @update_report, "Contact bug.";
			return (\@update_report, $errors, \@plates_used, \@loadingplates);
		    }
		    
		}




		if ( ($ext_primer == 1) && ($custom_primer_map == 1) ){
		    
		    # Make request_map entries with a loadingsample
		    $sth44->bind_param(1, $map_structure->[$n]->{contents}->[2]);
		    $sth44->bind_param(2, $map_id);
		    $sth44->bind_param(3, $map_structure->[$n]->{contents}->[1]);
		    $sth44->bind_param(4, $id_loadingsample);

		    eval {
			$sth44->execute || die "ERROR:$DBI::errstr";
		    };
		    
		    $count++;
		    
		    if ($@){
			$errors++;

                        &handle_cleanup(\@prepared);

			push @update_report, "Problems making request_map entry (with loadingsample entry) $@";
			push @update_report, "Contact bug.";
			return (\@update_report, $errors, \@plates_used, \@loadingplates);
		    }


		} else {

		    # Make request_map entries *without* a loadingsample
		    $sth4->bind_param(1, $map_structure->[$n]->{contents}->[2]);
		    $sth4->bind_param(2, $map_id);
		    $sth4->bind_param(3, $map_structure->[$n]->{contents}->[1]);

		    eval {
			$sth4->execute || die "ERROR:$DBI::errstr";
		    };
		    
		    $count++;
		    
		    if ($@){
			$errors++;

                        &handle_cleanup(\@prepared);

			push @update_report, "Problems making request_map entry (no loadingsample entry) $@";
			push @update_report, "Contact bug.";
			return (\@update_report, $errors, \@plates_used, \@loadingplates);
		    }
		}
		

		##########################################################
		# dn1
		# If this is a robot mapplate; derive information and fill
		# in mapplate_lookup

		if ($id_robot > 0){

		    # Get an id_map for this finish_request
		    $sth_s_map->bind_param(1, $map_structure->[$n]->{contents}->[2]);
		    $sth_s_map->execute || die "ERROR:$DBI::errstr";
		    my ($frequest_map) = $sth_s_map->fetchrow();
		    unless ($frequest_map) {

                        push @update_report, "Could not determine an id_map for finish_request.id_frequest $map_structure->[$n]->{contents}->[2] !";
                        push @update_report, "Contact bug.";
                        return (\@update_report, $errors, \@plates_used, \@loadingplates);

		    }

		    #warn "\tid_frequest $map_structure->[$n]->{contents}->[2] ... $frequest_map\n";

		    # Use this plus the id_frequst to get the mapplate_lookup data
		    $sth_dp_info->bind_param(1, $map_structure->[$n]->{contents}->[2]);
		    $sth_dp_info->bind_param(2, $frequest_map);
		    $sth_dp_info->execute || die "ERROR:$DBI::errstr";
		    my ($dp_id_dnaplate, $dm_id_map, $dm_id_dnaplatemap);
		    my ($xdp_id_dnaplate, $xdm_id_map, $xdm_id_dnaplatemap);

		    # A check here to ensure multiple robot DPs have not been made
		    my $t_count = 0;
		    while ( ($xdp_id_dnaplate, $xdm_id_map, $xdm_id_dnaplatemap) = $sth_dp_info->fetchrow() ){
			$dp_id_dnaplate = $xdp_id_dnaplate;
			$dm_id_map = $xdm_id_map;
			$dm_id_dnaplatemap = $xdm_id_dnaplatemap;
			$t_count++;
		    }
		    if ($t_count > 1){

                        push @update_report, "Cannot locate a unique well in a robot dnaplate for id_frequest $map_structure->[$n]->{contents}->[2] !";
                        push @update_report, "Contact bug.";
                        return (\@update_report, $errors, \@plates_used, \@loadingplates);

		    }
		    
		    #warn "ID_FREQUEST $map_structure->[$n]->{contents}->[2]\n\tID_MAPPLATE $map_id ID_MPMAP $map_structure->[$n]->{contents}->[1] ID_DNAPLATE $dp_id_dnaplate ID_DNAPLATEMAP $dm_id_dnaplatemap ID_DPMAP $dm_id_map\n";


		    # Make an entry for this request in mapplate_lookup

		    $sth_mapplate_lookup->bind_param(1, $map_id);
                    $sth_mapplate_lookup->bind_param(2, $map_structure->[$n]->{contents}->[1]);
                    $sth_mapplate_lookup->bind_param(3, $dp_id_dnaplate);
                    $sth_mapplate_lookup->bind_param(4, $dm_id_dnaplatemap);
                    $sth_mapplate_lookup->bind_param(5, $dm_id_map);
                    $sth_mapplate_lookup->bind_param(6, 10);
                    $sth_mapplate_lookup->bind_param(7, $id_robot);
     
                    eval {
		        $sth_mapplate_lookup->execute || die "ERROR:$DBI::errstr";
		    };
		    if ($@){
		        $errors++;

                        &handle_cleanup(\@prepared);

		        push @update_report, "Problems making mapplate_lookup entry $@";
		        push @update_report, "Contact bug.";
		        return (\@update_report, $errors, \@plates_used, \@loadingplates);
		    }

		}


		
		# Update the frequest_status entry
		$sth5->bind_param(1, $map_structure->[$n]->{contents}->[2]);
		eval {
		    $sth5->execute || die "ERROR:$DBI::errstr";
		};
		if ($@){
		    $errors++;
		    
                    &handle_cleanup(\@prepared);

		    push @update_report, "Problems making frequest_status entry $@";
		    push @update_report, "Contact bug.";
		    return (\@update_report, $errors, \@plates_used, \@loadingplates);
		}
		$sth6->bind_param(1, $map_structure->[$n]->{contents}->[2]);

                # newdn1
                $sth6->bind_param(2, 3);
                $sth6->bind_param(3, 1);
                $sth6->bind_param(4, $session_id);
                $sth6->bind_param(5, 'Owl_commit_blocks2.pm');
                $sth6->bind_param(6, $who);

		eval {
		    $sth6->execute || die "ERROR:$DBI::errstr";
		};
		if ($@){
		    $errors++;

                    &handle_cleanup(\@prepared);

		    return (\@update_report, $errors, \@plates_used, \@loadingplates);
		}
	    }
	}

	if ($count < 96){
	    $errors++;

            &handle_cleanup(\@prepared);
	    
	    push @update_report, "Map $map contains less than 96 samples. Cannot submit.";
	    return (\@update_report, $errors, \@plates_used, \@loadingplates);
	}
	
	
    }

    &handle_cleanup(\@prepared);

    push @update_report, "Mapplate entries entered.";
 
    return (\@update_report, $errors, \@plates_used, \@loadingplates);
}


sub handle_cleanup {
    my ($handles) = @_; 
    foreach my $n (@$handles){
	$$n->finish();
    }
}


1;





