package finch2_sj_routine;

use strict;
use Checkfinentries;
use Submitfinentries;
use vars qw(@ISA @EXPORT);
use Exporter;
@ISA = ('Exporter');
@EXPORT = qw(&insert_samples);

# KVA
#
######################################
# Processing transposon plate routine
######################################
#
#warn "Using local version of transposon plate routine\n";

sub insert_samples {
    my ($dbh,$who,$id_role,$currproject,$ID_TEAM_FINSERV,$format_var,$plate_var,$chem_var,$it_var,$suffix_var,$additive_var,$start_no,$end_no,$id2rowcol_96,$ADDS,$backwards,$valid_chems,$valid_custom,$valid_additives, $auto, $chem2suff, $old_suff, $TEAM, $currprojectspecies, $currprojectchr, $FSNAME, $session_id) = @_;
    
    my $prog = 'finch2_sj_routine';
#    print "$plate_var,$currproject\n";
    #check that the box related projectname matches the submission project.
    my $stat_check = "select cp.projectname
from beckman_box bb, bb_map bbm, subclone sc, ligation l,clone_project cp
where bb.id_beckmanbox = bbm.id_beckmanbox
and bbm.id_subclone = sc.id_subclone
and sc.id_ligation = l.id_ligation
and l.clonename = cp.clonename
and bbm.id_map = 1
and bb.name = ?";
    
    my $sth_chk = $dbh->prepare($stat_check) || die "ERROR:$DBI::errstr";
    $sth_chk->execute($plate_var) || die "ERROR:$DBI::errstr";
    my ($check_project) = $sth_chk->fetchrow();
    $sth_chk->finish();
    
    if ($check_project ne $currproject){
#        eval {
#          main::popup ("Warn", ["The project $check_project linked to this dnaplate ID isn't the same as the project you have opened finch2 with. You will need to reopen finch2 with the project in question."]);
#        };
        return('','','',$check_project);
    }
    
    my $lastSECT = 1;
    my $SECT = 1;
    my $DATA = [ [ [] ] ];
    my $DATA_TAG = [ [ [] ] ];
    my $GRID_ROWS = 16;
    my $DATA_INFO = [ [ [] ] ];
    
    my $hitcount;
    my @requests;
    foreach my $next ($start_no .. $end_no){
	$hitcount++;
	my %info;
	my $rowcol = $id2rowcol_96->{$next};
	my $read1 = "$plate_var" . "$rowcol" . "." . "$suffix_var" . "$it_var";
	print "$read1\n";
	$info{te} = $plate_var.$rowcol;
	$info{su} = $suffix_var;
	if ($it_var){
	    $info{it} = $it_var;
	}
	$info{id} = $chem_var;
	if ($additive_var){
	    $info{ad} =$additive_var;
	}
	push @requests, \%info;
    }
    
    my $hitS = 1;
    my $hitr = 0;
    
    # No point having empty sections for no good reason
    $lastSECT = $hitS;
    $SECT = $hitS;
    my $sam_r = $hitr;
    
    # Report number of reads to import to update-screen
    warn "Importing $hitcount requests.\n";
    
    foreach my $newsample (@requests) {
	
	# te:Template su:Suffix it:Iteration pr:Primername se:Primersequence ru.Runtype.description[old]||No Preference id.id_scsp ad.id_additive
	
	$DATA->[$SECT]->[$sam_r]->[0] = $newsample->{te};
	my $suffix_data = "$newsample->{su}"."$newsample->{it}";
	$DATA->[$SECT]->[$sam_r]->[1] = $suffix_data;
	$DATA->[$SECT]->[$sam_r]->[2] = $newsample->{pr}='';
	$DATA->[$SECT]->[$sam_r]->[3] = $newsample->{se}='';
	$DATA->[$SECT]->[$sam_r]->[4] = $newsample->{ru}='';
	$DATA->[$SECT]->[$sam_r]->[6] = $newsample->{id};
	$DATA->[$SECT]->[$sam_r]->[7] = $newsample->{ad};
	
	######################################################
	# Also tag all new entries so that they can be easily
	# updated
	######################################################
	$DATA_TAG->[$SECT]->[$sam_r]->[8] = 'SlateGray1';
	
	$sam_r++;
	if ($sam_r == $GRID_ROWS) {  # Switch to a new $SECT if we get to the end of the rows
	    $SECT++;
	    $lastSECT++;
	    $sam_r = 0;
	}
    }
    
    # If the SECT we have ended up in is empty may as well go back one
    if ($sam_r == 0){
	$SECT--;
	$lastSECT = $SECT;
    }
    
    #do the checking of the entries through
    #the standard checkfinentries module
    #needed to define the information into the
    #relevant data structure.
    my $DP_avail_check = 0;
    my $COSTCODE='';
    my ($update_report, $errors, $sub_count, $missing_DP) = &Checkfinentries::start_check($dbh, $currproject, $valid_custom, $valid_chems, $valid_additives, $backwards, \$DATA, $COSTCODE, $SECT, $lastSECT, $GRID_ROWS, \$DATA_TAG, \$DATA_INFO, $auto, $chem2suff, $old_suff, '', '',  $TEAM, $DP_avail_check);
    
    if ($errors){
#        eval {
#          main::popup ("Warn", ["Duplicate entries have been found to exist, either add or increase the iteration."]);
#        };
        return('','',$errors,'');
	
    } else {

	#Submit the samples into the database and return a 
	#finish batch ID to enable me to generate the
	#mapplate information off this.
	my ($submit_return, $send_mail, $id_fbatch) = &Submitfinentries::start_submit($dbh, $currproject, \$DATA, \$DATA_INFO, $COSTCODE, $SECT, $lastSECT, $GRID_ROWS, $who, $TEAM, $currprojectspecies, $currprojectchr, $FSNAME, $ID_TEAM_FINSERV, $session_id, $id_role, $valid_additives, $backwards, 0);
	
        ##not needed anymore as I collect the finish request id's
        ##when they are made into an array.
        my $stat1 = "select id_frequest from finish_request where id_fbatch = ? order by id_frequest";
	my $sth1  = $dbh->prepare($stat1) || die "ERROR:$DBI::errstr";
	$sth1->execute($id_fbatch);
	my @frequest_lst;
	while(my $freq = $sth1->fetchrow()){
	    push @frequest_lst,$freq;
	}
	
	my $stat2_1 = "update frequest_status set iscurrent = 0 where id_frequest = ?";
	my $sth2_1  = $dbh->prepare($stat2_1) || die "ERROR:$DBI::errstr";
	my $stat2_2 = "insert into frequest_status values (?,3,1,sysdate,?,?,?)";
	my $sth2_2  = $dbh->prepare($stat2_2) || die "ERROR:$DBI::errstr";
	
	foreach my $fl (sort {$a <=> $b} @frequest_lst){
	    
	    $sth2_1->bind_param(1,$fl);
	    $sth2_1->execute();

	    $sth2_2->bind_param(1,$fl);
	    $sth2_2->bind_param(2,$session_id);
	    $sth2_2->bind_param(3,$prog);
	    $sth2_2->bind_param(4,$who);
	    $sth2_2->execute();
	    
	}
        
        #need to get the protocol information 
	#to enter it into the mapplate table.
        my $stat3 = "select id_protocol,OLD_NAME,NAME from seqprotocol where id_protocol in (select id_protocol from seqchem_seqprotocol where id_scsp = ?)";
	my $sth3 = $dbh->prepare($stat3) || die "ERROR:$DBI::errstr";
        $sth3->execute($chem_var);
	my($id_prot,$o_name,$name)=$sth3->fetchrow();
	
        my $stat4_1 = "select seq_mapplate.nextval from dual";
	my $sth4_1 = $dbh->prepare($stat4_1) || die "ERROR:$DBI::errstr";
	$sth4_1->execute();
	my($id_mapplate)=$sth4_1->fetchrow();
	
	#Insert a new entry for the mapplate ID.
	my $stat4_2 = "insert into mapplate (ID_MAPPLATE,PLATE_FORMAT,ID_TEAM_FINSERV,PROTOCOL) values (?,?,?,?)";
	my $sth4_2 =$dbh->prepare($stat4_2) || die "ERROR:$DBI::errstr";
	
        my $stat4_3 = "insert into mapplate (ID_MAPPLATE,PLATE_FORMAT,ID_TEAM_FINSERV,ID_PROTOCOL) values (?,?,?,?)";
	my $sth4_3 =$dbh->prepare($stat4_3) || die "ERROR:$DBI::errstr";
	
        #do different mapplate status updates if the chemistry is an
        #ABI or 3700 CHEMISTRY (USE THE PROTOCOL NAME FOR CHECK)
        #ABI insert status's 1,2,9
        my $stat4_4 = "insert into mapplate_status values (?,1,sysdate,0,?,?,?)";
	my $sth4_4  = $dbh->prepare($stat4_4) || die "ERROR:$DBI::errstr";
        my $stat4_5 = "insert into mapplate_status values (?,2,sysdate,0,?,?,?)";
	my $sth4_5  = $dbh->prepare($stat4_5) || die "ERROR:$DBI::errstr";
        my $stat4_6 = "insert into mapplate_status values (?,9,sysdate,1,?,?,?)";
	my $sth4_6  = $dbh->prepare($stat4_6) || die "ERROR:$DBI::errstr";
        
        #3700 insert status's 10,11
        my $stat4_7 = "insert into mapplate_status values (?,10,sysdate,0,?,?,?)";
	my $sth4_7  = $dbh->prepare($stat4_7) || die "ERROR:$DBI::errstr";
        my $stat4_8 = "insert into mapplate_status values (?,11,sysdate,1,?,?,?)";
	my $sth4_8  = $dbh->prepare($stat4_8) || die "ERROR:$DBI::errstr";
        
	if ($name=~ /ABI.+/){	
	    
	    $sth4_2->bind_param(1,$id_mapplate);
	    $sth4_2->bind_param(2,'96');
	    $sth4_2->bind_param(3,$ID_TEAM_FINSERV);
	    $sth4_2->bind_param(4,$o_name);
	    $sth4_2->execute();
	    
	    $sth4_4->bind_param(1,$id_mapplate);
	    $sth4_4->bind_param(2,$session_id);
	    $sth4_4->bind_param(3,$prog);
	    $sth4_4->bind_param(4,$who);
	    $sth4_4->execute() || die "ERROR:$DBI::errstr";

	    $sth4_5->bind_param(1,$id_mapplate);
	    $sth4_5->bind_param(2,$session_id);
	    $sth4_5->bind_param(3,$prog);
	    $sth4_5->bind_param(4,$who);
	    $sth4_5->execute() || die "ERROR:$DBI::errstr";

	    $sth4_6->bind_param(1,$id_mapplate);
	    $sth4_6->bind_param(2,$session_id);
	    $sth4_6->bind_param(3,$prog);
	    $sth4_6->bind_param(4,$who);
	    $sth4_6->execute() || die "ERROR:$DBI::errstr";

	} else {	
	    
	    $sth4_3->bind_param(1,$id_mapplate);
	    $sth4_3->bind_param(2,'96');
	    $sth4_3->bind_param(3,$ID_TEAM_FINSERV);
	    $sth4_3->bind_param(4,$id_prot);
	    $sth4_3->execute();

	    $sth4_7->bind_param(1,$id_mapplate);
	    $sth4_7->bind_param(2,$session_id);
	    $sth4_7->bind_param(3,$prog);
	    $sth4_7->bind_param(4,$who);
	    $sth4_7->execute() || die "ERROR:$DBI::errstr";

	    $sth4_8->bind_param(1,$id_mapplate);
	    $sth4_8->bind_param(2,$session_id);
	    $sth4_8->bind_param(3,$prog);
	    $sth4_8->bind_param(4,$who);
	    $sth4_8->execute() || die "ERROR:$DBI::errstr";

	}
	
	my $stat5_1 = "select id_map from rowcol_map where format = 96 order by id_map";
	my $sth5_1 = $dbh->prepare($stat5_1) || die "ERROR:$DBI::errstr";
	$sth5_1->execute();
	my $id_map;
	my %HASH;
	my $count=0;
	while($id_map = $sth5_1->fetchrow()){
	    $count++;
	    $HASH{$count} = $id_map;
	}
	
        my $stat5_2 = "insert into request_map (ID_FREQUEST,ID_MAPPLATE,ID_MAP) values (?,?,?)";
	my $sth5_2 = $dbh->prepare($stat5_2) || die "ERROR:$DBI::errstr";
	
	my $count=0;
	foreach my $fl (sort {$a <=> $b} @frequest_lst){
	    $count++;
	    $sth5_2->execute($fl,$id_mapplate,$HASH{$count});
	}
	
	
	$sth1->finish();
	$sth2_1->finish();
	$sth2_2->finish();
	$sth3->finish();
	$sth4_1->finish();
	$sth4_2->finish();
	$sth4_3->finish();
	$sth4_4->finish();
	$sth4_5->finish();
	$sth4_6->finish();
	$sth4_7->finish();
	$sth4_8->finish();
        $sth5_1->finish();
	$sth5_2->finish();
	
        #printout the finish batch ID, then the mapplate ID in the update window.
	print ">>FINISH BATCH ID - $id_fbatch - MAPPLATE ID - $id_mapplate<<\n";
        return($id_fbatch,$id_mapplate);
	
    }
    
}

1;

