# Author: KVA
###################################
# General printmap library (db info)
###################################

use strict;

package validate;

sub validate_finserv {

    my ($pkg, $dbh, $number) = @_;
    my $finserv;

    my $stat_init = "select id_team_finserv from mapplate where
id_mapplate in (select id_mapplate 
from request_map 
where id_loadingsample in (select id_loadingsample 
from loadingsample 
where id_loadingplate in (select id_loadingplate 
from loadingplate 
where id_loadingplate=?)))";
    
    my $sth_init = $dbh->prepare($stat_init) || die "ERROR:$DBI::errstr";
    $sth_init->bind_param(1, $number);
    $sth_init->execute || die "ERROR:$DBI::errstr";
    $finserv = $sth_init->fetchrow();
    
    $sth_init->finish;

    return ($finserv);
}


sub validate_mapplate {

    my ($pkg, $dbh, $number) = @_;
    my $map_id;

    my $statement = "select distinct id_mapplate from request_map where id_loadingsample in (select id_loadingsample from loadingsample where id_loadingplate= ?)";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->bind_param(1, $number);
    $sth->execute || die "ERROR:$DBI::errstr";
    $map_id = $sth->fetchrow();
    
    $sth->finish;

    return ($map_id);
}


sub validate_status {

    my ($pkg, $dbh, $map_id) = @_;

    my $mode;

    #Check that the status is at 13 (precreated) to allow direct
    #printing through this route.
    my $stat1 = "select status from mapplate_status where id_mapplate=? and iscurrent=1";
    my $sth1 = $dbh->prepare($stat1) || die "ERROR:$DBI::errstr";
    $sth1->bind_param(1, $map_id);
    $sth1->execute || die "ERROR:$DBI::errstr";
    my $status = $sth1->fetchrow();

    
    #OR check wether a map related to a loadingplate ever had a status of precreated
    #to be printed through this route. These are for maps that have had status 
    #updated to 12 from 13, and may have been sent to the wrong printer
    my $stat2 = "select status from mapplate_status where id_mapplate=? and status =13";
    my $sth2 = $dbh->prepare($stat2) || die "ERROR:$DBI::errstr";
    $sth2->bind_param(1, $map_id);
    $sth2->execute || die "ERROR:$DBI::errstr";
    my $status2 = $sth2->fetchrow();
    
    if ($status2 == '13') {
        $mode=$status2;
    }else{  
        $mode=$status;
    }

    $sth1->finish;
    $sth2->finish;
    
    return($mode, $status, $status2);
}


sub validate_users {
    
    my ($pkg, $dbh) = @_;
    
    my $names;
    my @users;
    
    #Check that users that are connected to team 58
    #and thus valid to use it.
    my $stat1 = "select distinct email from person where id_person in (select id_person from team_person_role where teamname = 58 and roletype in ('Finishing','Prefinishing'))";
    my $sth1 = $dbh->prepare($stat1) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    while($names = $sth1->fetchrow()){
        push @users, $names;
    }
    
    $sth1->finish;
    
    return(\@users);
    
}


package pmselect;


sub precreated_maps {
    
    my ($pkg, $dbh, $ID_TEAM_FINSERV, $mode, $map_id) = @_;
    
    my @RETURNMAPS = ();
    
    my $statement;
    
    # Grab Created-maps
    if (($mode == 5) || ($mode == 7) || ($mode == 10) || ($mode == 11) || ($mode == 12) || ($mode == 13)){
        
        $statement = "
select mapplate.ID_MAPPLATE, mapplate_status.STATUSDATE, mapplate.PLATE_FORMAT, mapplate.ID_RUNTYPE, seqprotocol.NAME
from mapplate, mapplate_status, seqprotocol
where mapplate.id_mapplate = mapplate_status.id_mapplate
and mapplate.id_protocol = seqprotocol.id_protocol
and mapplate_status.status in ($mode)
and mapplate_status.iscurrent = 1
and mapplate.ID_TEAM_FINSERV = \'$ID_TEAM_FINSERV\'
and mapplate.id_mapplate = $map_id
"; 
    } else {
	die "Cannot handle any mode other than 5,7,10,11,12,13 at present !\n";
    }
     
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($ID_MAPPLATE, $STATUSDATE, $PLATE_FORMAT, $ID_RUNTYPE, $PROTOCOL);
    while (($ID_MAPPLATE, $STATUSDATE, $PLATE_FORMAT, $ID_RUNTYPE, $PROTOCOL) = $sth->fetchrow()){
	# eg. protocol -> ABI-F1C (.f1c) : get rid of whitespace onwards
	$PROTOCOL =~ s/\s+.+//g;
        
	if ($ID_RUNTYPE eq ''){
	    $ID_RUNTYPE = 'NULL';
	}
        
	my $map = "$ID_MAPPLATE" . "_" . "$PROTOCOL" . "-" . "$ID_RUNTYPE" . " " . "  $STATUSDATE". " " . "$PLATE_FORMAT well";
	
	push @RETURNMAPS, $map;
    }
    $sth->finish;
    
    return(\@RETURNMAPS);

}



package updprimer;


sub update_primer_related_info {

#obtain a lp ID and a database handle, and some other stuff.
    my ($pkg, $dbh, $number, $session_id, $prog, $who) = @_;

    my %HASHPP;
    my %HASHPR;
    
#update lp status
    my $stat1 = "update loadingplate_status set iscurrent = 0 where id_loadingplate = ? and iscurrent=1 and status = 12";
    my $sth1 =  $dbh->prepare($stat1) || die "ERROR:$DBI::errstr";
    $sth1->bind_param(1, $number);
    $sth1->execute || die "ERROR:$DBI::errstr\n";
    
#insert new lp record of created
    my $stat2 = "insert into loadingplate_status (ID_LOADINGPLATE,STATUS,ISCURRENT,STATUSDATE,SESSIONID,PROGRAM,OPERATOR) values (?, 1, 1, sysdate, ?, ?, ?)";
    my $sth2 =  $dbh->prepare($stat2) || die "ERROR:$DBI::errstr";
    $sth2->bind_param(1, $number);
    $sth2->bind_param(2, $session_id);
    $sth2->bind_param(3, $prog);
    $sth2->bind_param(4, $who);
    $sth2->execute || die "ERROR:$DBI::errstr\n";
    
#obtain primerplate id & primer id's.
    my $stat3 = "select distinct e.id_primerplate, e.id_primer
from loadingplate a, loadingsample b, request_map c, finish_request d, primer_map e
where a.id_loadingplate = ?
and a.id_loadingplate = b.id_loadingplate
and b.id_loadingsample = c.id_loadingsample
and c.id_map = e.id_map
and c.id_frequest = d.id_frequest
and d.id_primer = e.id_primer
";
    my $sth3 =  $dbh->prepare($stat3) || die "ERROR:$DBI::errstr";
    $sth3->bind_param(1, $number);
    $sth3->execute || die "ERROR:$DBI::errstr\n";
    my ($primerplate, $primer);
    while (($primerplate, $primer) = $sth3->fetchrow()){
#Place the primerplate in a hash, to get unique entities.
        $HASHPP{$primerplate}=1;
        
#Place the primers in a hash, to get unique entities.
        $HASHPR{$primer}=1;
    }
  
#checks what the current status of the primerplate is,
#if it is 2, it skips the update for the primerplate.
#This will protect against the fact that a primer
#may span 2 plates, and beeping in the loadingplates,
#will cause the status entry for some primerplates to be updated
#twice.
#The same is done for the status of 5 when a plate has been reordered,
#also retrieve the program information to check wether it was a reorder
#or not.
    my $check_pp = "select status,program from primerplate_status where id_primerplate = ? and iscurrent=1";
    my $sth_cpp = $dbh->prepare($check_pp) || die "ERROR:$DBI::errstr";

#update the primerplate status & insert a new one.
#also update latest_link url to null when the orders 
#are physically present at the Sanger.
    my $stat4 = "update primerplate_status set iscurrent=0 where id_primerplate = ? and iscurrent=1";
    my $sth4 = $dbh->prepare($stat4) || die "ERROR:$DBI::errstr";
    
    my $stat5 = "insert into primerplate_status values (?, ?, sysdate, 1, ?, ?, ?)";
    my $sth5 = $dbh->prepare($stat5) || die "ERROR:$DBI::errstr";
    
#check if there is a url entry for the primer_plate.
    my $check_ll = "select latest_link from primer_plate where id_primerplate = ?";
    my $sth_ll =  $dbh->prepare($check_ll) || die "ERROR:$DBI::errstr";
    
    my $stat6 = "update primer_plate set latest_link=NULL where id_primerplate = ?";
    my $sth6 = $dbh->prepare($stat6) || die "ERROR:$DBI::errstr";
    
    foreach my $pp (keys %HASHPP){
        $sth_cpp->bind_param(1, $pp);
        $sth_cpp->execute || die "ERROR:$DBI::errstr\n";
        my ($primerplatestatus,$pp_program) = $sth_cpp->fetchrow();
        
        if (($primerplatestatus != 2) ||($primerplatestatus != 5)) {
            $sth4->bind_param(1, $pp);
            $sth4->execute || die "ERROR:$DBI::errstr\n";
            
            $sth5->bind_param(1, $pp);
	    if ($pp_program eq 'REORDER'){
		$sth5->bind_param(2, '5');
	    } else {
		$sth5->bind_param(2, '2');
	    }
            $sth5->bind_param(3, $session_id);
            $sth5->bind_param(4, $prog);
            $sth5->bind_param(5, $who);
            $sth5->execute || die "ERROR:$DBI::errstr\n"; 
            
            $sth_ll->bind_param(1, $pp);
            $sth_ll->execute || die "ERROR:$DBI::errstr\n";
            my $latest_link= $sth_ll->fetchrow();
            
            if ($latest_link){
                $sth6->bind_param(1, $pp);
                $sth6->execute || die "ERROR:$DBI::errstr\n";
            }
        } 
    }
    
#checks what the current status of the primer is,
#if it is 4, it skips the update for the primer.
#This will protect against the fact that a primer
#may span 2 plates, and beeping in the loadingplates,
#will cause the status entry for some primers to be updated
#twice.
    my $check_ps = "select status from primer_status where id_primer = ? and iscurrent=1";
    my $sth_cps = $dbh->prepare($check_ps) || die "ERROR:$DBI::errstr";

#update the primer status's & insert a new one.
    my $stat7 = "update primer_status set iscurrent=0 where id_primer = ? and iscurrent=1 and status=6";
    my $sth7 = $dbh->prepare($stat7) || die "ERROR:$DBI::errstr";
    
    my $stat8 = "insert into primer_status values (?, 4, sysdate, 1, ?, ?, ?)";
    my $sth8 = $dbh->prepare($stat8) || die "ERROR:$DBI::errstr";
    
    foreach my $pr (keys %HASHPR){
        $sth_cps->bind_param(1, $pr);
        $sth_cps->execute || die "ERROR:$DBI::errstr\n";
        my $primerstatus = $sth_cps->fetchrow();
        
        if ($primerstatus != 4) {
            $sth7->bind_param(1, $pr);
            $sth7->execute || die "ERROR:$DBI::errstr\n";
            
            $sth8->bind_param(1, $pr);
            $sth8->bind_param(2, $session_id);
            $sth8->bind_param(3, $prog);
            $sth8->bind_param(4, $who);
            $sth8->execute || die "ERROR:$DBI::errstr\n";  
        }
    }
    
#finish all statement handles.
$sth1->finish;
$sth2->finish;
$sth3->finish;
$sth_cpp->finish;
$sth4->finish;
$sth5->finish;
$sth_ll->finish;
$sth6->finish;
$sth_cps->finish;
$sth7->finish;
$sth8->finish;

}

package chkEST;

sub check_EST {
    my ($pkg, $dbh, $lpid) = @_;
    
    my $type_id;
    my $statement = "select distinct p.project_type
from project p, finish_batch fb, finish_request fr, request_map rm, loadingsample ls
where p.projectname = fb.projectname
and fb.id_fbatch = fr.id_fbatch
and fr.id_frequest = rm.id_frequest
and rm.id_loadingsample = ls.id_loadingsample
and ls.id_loadingplate = ?
and ls.id_map = 1";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->bind_param(1, $lpid);
    $sth->execute || die "ERROR:$DBI::errstr";
    ($type_id) = $sth->fetchrow();
    
    $sth->finish;
    
    return ($type_id);
    
}


package reprint;

sub check_map_and_status {

    my ($pkg, $dbh, $bmap) = @_;
    my ($map_id,$status);

    my $statement = "select id_mapplate, status 
from mapplate_status  
where id_mapplate = ?  
and iscurrent = 1";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->bind_param(1, $bmap);
    $sth->execute || die "ERROR:$DBI::errstr";
    ($map_id,$status) = $sth->fetchrow();
    
    $sth->finish;

    return ($map_id,$status);

}

sub map_info {

    my ($pkg, $dbh, $bmap) = @_;
    
    my @RETURNMAPS;
    
    my $statement = "select mapplate.ID_MAPPLATE, mapplate.PLATE_FORMAT, mapplate.ID_RUNTYPE, seqprotocol.NAME, mapplate_status.STATUSDATE, mapplate.PLATE_FORMAT
from mapplate, mapplate_status, seqprotocol
where mapplate.id_mapplate = mapplate_status.id_mapplate
and mapplate.id_protocol = seqprotocol.id_protocol
and mapplate_status.status =12
and mapplate_status.iscurrent = 1
and mapplate.id_mapplate = ?";
    
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->bind_param(1, $bmap);
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($ID_MAPPLATE, $PLATE_FORMAT, $ID_RUNTYPE, $PROTOCOL,$STATUSDATE,$PLATE_FORMAT) = $sth->fetchrow();
    $PROTOCOL =~ s/\s+.+//g;
    
    if ($ID_RUNTYPE eq ''){
        $ID_RUNTYPE = 'NULL';
    }
    
    my $map = "$ID_MAPPLATE" . "_" . "$PROTOCOL" . "-" . "$ID_RUNTYPE" . " " . "  $STATUSDATE". " " . "$PLATE_FORMAT well";
    
    push @RETURNMAPS, $map;
    $sth->finish;
    
    return(\@RETURNMAPS);
    
}


1;
