#!/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.                      #
#######################################################################
# dn1g; code to enter new source_plates into the system

use strict;
use WrapDBI;


my $db = 'platetrack';

warn "Connecting to database...\n";
my $dbh = WrapDBI->connect("$db", {RaiseError => 1});

my @to_do;


my %info1 = (
	     base      => "TGas",
	     from      => "1",
	     to        => "144",
	     add_zero  => "1", 
	     format    => "384",
	     id_type   => "1",
	     id_source => "67",
	     status    => 1,
	     comment   => 'Gastrula-pCS107-sp6',
);
push @to_do, (\%info1);


#my %info2 = (
#	     base        => "TNeu",
#	     from        => "1",
#	     to          => "5",
#	     add_zero    => "1",
#	     format      => "384",
#	     id_type     => "1",
#	     id_source   => "67",
#	     status      => 1,
#	     ligation    => 38175,
#	     libraryname => 'Neurola',
#	     comment     => 'Neurola-pCS107-sp6',
#);
#push @to_do, (\%info2);


#my %info3 = (
#	     base      => "L1E",
#	     from      => "1",
#	     to        => "12",
#	     add_zero  => "0",
#	     format    => "96",
#	     id_type   => "1",
#	     id_source => "67",
#	     status    => 1,
#	     comment   => 'Egg-pCS107-sp6',
#);
#push @to_do, (\%info3);


#my %info4 = (
#	     base      => "L1G",
#	     from      => "1",
#	     to        => "12",
#	     add_zero  => "0",
#	     format    => "96",
#	     id_type   => "1",
#	     id_source => "67",
#	     status    => 1,
#	     comment   => 'Gastrula-pCS107-sp6',
#);
#push @to_do, (\%info4);


# Prepare the statements we will use
my $st_id_sourceplate = "select seq_sourceplate.nextval from dual";
my $sth_id_sourceplate = $dbh->prepare($st_id_sourceplate) || die "ERROR:$DBI::errstr";

# Get a sessionid for entries
my $st_sessionid = "select seq_session.nextval from dual";
my $sth_session = $dbh->prepare($st_sessionid) || die "ERROR:$DBI::errstr";
$sth_session->execute || die "ERROR:$DBI::errstr";
my $sessionid = $sth_session->fetchrow;
warn "Session = $sessionid\n";

my $st1 = "insert into source_plate (id_sourceplate, format, id_type, platename, id_source, id_ligation, libraryname)
values (?, ?, ?, ?, ?, ?, ?)";
my $sth1 = $dbh->prepare($st1) || die "ERROR:$DBI::errstr";

my $st2 = "insert into sourceplate_map
(id_sourceplatemap, id_sourceplate, id_map, samplename)
    values (seq_sourceplatemap.nextval, ?, ?, ?)";
my $sth2 = $dbh->prepare($st2) || die "ERROR:$DBI::errstr";

my $st3 = "select maprow, mapcol from rowcol_map where id_map = ?";
my $sth3 = $dbh->prepare($st3) || die "ERROR:$DBI::errstr";

my $st4 = "insert into sourceplate_status 
(id_sourceplate, status, statusdate, iscurrent, sessionid, program, operator, remark)
    values (?, 1, sysdate, 1, ?, 'pt2_spentry', 'dn1', ?)
";
my $sth4 = $dbh->prepare($st4) || die "ERROR:$DBI::errstr";


foreach my $do (@to_do){
    &start_processing($do);
}


sub start_processing {
    
    my ($do) = @_;

    my %info = %$do;

    foreach my $it ($info{from} .. $info{to}){

	if ($info{add_zero} == 1){
	    if ($it < 10){
		$it = '0'.'0'.$it;
	    }
	    if ( ($it > 9) && ($it < 100) ){
		$it = '0'.$it;
	    }
	}

	my $this_platename = "$info{base}" . "$it";
	warn "---> Entering source_plate entry for $this_platename\n";
	&enter_sourceplate(\%info, $this_platename);
    }
}


sub enter_sourceplate {

    my ($info, $platename) = @_;

    # Get an id_sourceplate
    $sth_id_sourceplate->execute || die "ERROR:$DBI::errstr";
    my $id_sourceplate = $sth_id_sourceplate->fetchrow;
    
    $sth1->bind_param(1, $id_sourceplate);
    $sth1->bind_param(2, $info->{format});
    $sth1->bind_param(3, $info->{id_type});
    $sth1->bind_param(4, $platename);
    $sth1->bind_param(5, $info->{id_source});
    $sth1->bind_param(6, $info->{ligation});
    $sth1->bind_param(7, $info->{libraryname});
    $sth1->execute || die "ERROR:$DBI::errstr";
    warn "---> Entering sourceplate_map entries for $platename\n";
    &enter_sourceplate_status($id_sourceplate, $info->{comment});

    warn "---> Entering sourceplate_status entries for $platename\n";
    &enter_sourceplate_map($info->{format}, $id_sourceplate, $platename);
}

sub enter_sourceplate_status {
    my ($id_sourceplate, $comment) = @_;

    $sth4->bind_param(1, $id_sourceplate);
    $sth4->bind_param(2, $sessionid);
    $sth4->bind_param(3, $comment);
    $sth4->execute || die "ERROR:$DBI::errstr";

}

sub enter_sourceplate_map {
    my ($format, $id_sourceplate, $platename) = @_;

    my ($from, $to);
    if ($format == 96){
	$from = 1;
	$to = 96;
    } elsif ($format == 384){
	$from = 97;
	$to = 480;
    } else {
	die "Unkown plate format !\n";
    }


    foreach my $n ($from .. $to){

	$sth3->bind_param(1, $n);
	$sth3->execute || die "ERROR:$DBI::errstr";
	my ($row, $col) = $sth3->fetchrow;
	if ($col < 10) {
	    $col = "0". "$col";
	}
	
	my $samplename = "$platename" . "$row" . "$col";
	
	# id_sourceplate, id_map, samplename
	$sth2->bind_param(1, $id_sourceplate);
	$sth2->bind_param(2, $n);
	$sth2->bind_param(3, $samplename);
	$sth2->execute || die "ERROR:$DBI::errstr";
    }	
    
}


# If we get to here without a die commit
warn "Commiting entries made...\n";
$dbh->commit;



END {

    if (defined $sth1){
	$sth1->finish;
    }

    if (defined $sth2){
	$sth2->finish;
    }

    if (defined $sth_id_sourceplate){
	$sth_id_sourceplate->finish;
    }

    if (defined $sth3){
	$sth3->finish;
    }

    if (defined $sth4){
	$sth4->finish;
    }

    if (defined $sth_session){
	$sth_session->finish;
    }
   
    if (defined $dbh){
	warn "Rolling back...\n";
	$dbh->rollback;
	warn "Disconnecting from database...\n";
	$dbh->disconnect;
    }
    
};
