# DN1
#######################################
# General platetrack library (db info)
#######################################



package convert;

sub email2teamname { 
    my ($pkg, $dbh, $email) = @_;
    my $statement = "
select team_person_role.teamname 
from person, team_person_role 
where person.id_person = team_person_role.id_person 
and person.email = \'$email\' 
and person.iscurrent = 1
and team_person_role.roletype = 'Shotgun'
and team_person_role.iscurrent = 1
";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my $team = $sth1->fetchrow();
    $sth1->finish();
    
    if ($team eq ''){
	$team = '... Not registered ...';
    }
    return ($team);
}

sub id_person2email {
    my ($pkg, $dbh, $id_person) = @_;
    my $statement = "select email from person where id_person = $id_person and iscurrent = 1";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my $email = $sth1->fetchrow();
    $sth1->finish();

    if ($email eq ''){
	$email = 'Not a person';
    }
    return ($email);
}

#sub id_person2id_role {
#    my ($pkg, $dbh, $id_person) = @_;
#    my $statement = "select id_role, teamname from team_person_role where id_person = $id_person and iscurrent = 1 and roletype = 'Shotgun'";
#    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
#    $sth1->execute || die "ERROR:$DBI::errstr";
#    my ($id_role, $teamname) = $sth1->fetchrow();
#    $sth1->finish();
#
#    if ($id_role eq ''){
#	$id_role = 'Not a shotgun person';
#    }
#    return ($id_role, $teamname);
#}




sub id_person2id_role {
    my ($pkg, $dbh, $id_person, $mw) = @_;
    my $statement = "select id_role, teamname from team_person_role where id_person = $id_person and iscurrent = 1 and roletype = 'Shotgun'";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my ($id_role, $teamname);
    my $c = 0;
    my @possibles;
    my %hash;
    while ( ($id_role, $teamname)  = $sth1->fetchrow() ){

#	warn "-----------------------------------------\n";
#	warn "id_role -> $id_role teamname -> $teamname\n";
#	warn "-----------------------------------------\n";
	
	$c++;
	$hash{$teamname} = $id_role; 
    }
    $sth1->finish();
    
    my @possibles = (keys %hash);
    
    if ($c == 0){
	$id_role = 'Not a shotgun person';
	
    } elsif ($c > 1){
	##################################################################
	# The user must decide for whom they are working in this session
	##################################################################
	my $choice;
	my $win = $mw->Toplevel;
	$win->focusForce;
	$win->grab;
	$win->configure (-title => "Shotgun team selector");
	my $frame = $win->Frame();
	my $label = $frame->Label(-text => "Who are you currently working for ?");
	$label->pack(-side=> 'top');
	my $scrllist = $frame->ScrlListbox (
					    -bg => 'lemonchiffon1',
					    -scrollbars => 'e',
					    -label => "You can choose from the following.",
					    -height => 5,
					    -width => 30
					    );
	$scrllist->bind ('<Double-Button-1>' => sub { 
	    $choice = $scrllist->get('active');
	    if ($choice eq ''){
		return;
	    }
	    $id_role = $hash{$choice};
	    $teamname = $choice;
	    $win->destroy();
	});

	$scrllist->pack(-side => 'left', -expand => 1, -fill => 'both');
	$scrllist->insert('end', @possibles);
	$frame->pack;
	$frame->waitWindow;
    } else {
	my @array = %hash;
	$id_role = $array[1];
	$teamname = $array[0];
    }

#    warn "Returning $id_role, $teamname\n";
    return ($id_role, $teamname);
}



############################################################################
# Some of this code becomes redundant with a NOT NULL on ligation.clonename
############################################################################
sub id_ligation2clonename {
    my ($pkg, $dbh, $id_ligation) = @_;

    my $statement = "
select ligation.clonename
from ligation
where ligation.id_ligation = \'$id_ligation\'
";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my $clonename = $sth1->fetchrow();
    $sth1->finish();

    if ($clonename eq ''){
	$statement = "
select clone.clonename
from ligation, fragmentation_band, dnaprep_frag, dnaprep, clone_culture, glystock, clone
where ligation.id_fragband = fragmentation_band.id_fragband
and fragmentation_band.id_frag = dnaprep_frag.id_frag
and dnaprep_frag.id_prep = dnaprep.id_prep 
and dnaprep.id_culture = clone_culture.id_culture
and clone_culture.id_glystock = glystock.id_glystock
and glystock.clonename = clone.clonename
and ligation.id_ligation = \'$id_ligation\'
";
	$sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
	$sth1->execute || die "ERROR:$DBI::errstr";
	$clonename = $sth1->fetchrow();
	$sth1->finish();
	
	if ($clonename eq ''){
	    $clonename = 'No clone';
	}
    }
    return ($clonename); 

}

sub clonename2teamname {
    my ($pkg, $dbh, $clone) = @_;
    my $statement = "
select distinct project_owner.teamname
from project_owner, clone_project, clone
where project_owner.projectname = clone_project.projectname
and clone_project.clonename = clone.clonename
and clone.clonename = \'$clone\'
and (project_owner.projectname, project_owner.owned_from) in (
        select po.projectname, max(po.owned_from)
        from project_owner po
        where po.owner_type = 2
        group by po.projectname
)
";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my $t;
    my @teams;
    while ($t = $sth1->fetchrow()){
	push @teams, $t
    }
    $sth1->finish();
    return (\@teams); 

}

package check;

sub date_submitted {
    my ($pkg, $dbh, $id) = @_;
    my $statement = "
select statusdate 
from loadingplate_status
where id_loadingplate = \'$id\'
and (status = 2 or status = 7)"; 
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my $date = $sth1->fetchrow();
    $sth1->finish();
    if ($date eq ''){ $date = 'Never submitted' };
    return $date;
}



sub ok_2_cancel_BB {
    # You should not be able to cancel a BB if a 'live' DP has been created
    # from it.
    my ($pkg, $dbh, $id) = @_;
    my $statement = "
select distinct dna_plate.id_dnaplate, dna_plate.copy_type
from bb_map, dnaplate_map, dna_plate, dnaplate_status, dnaplatestatusdict
where bb_map.id_bbmap = dnaplate_map.id_bbmap
and dnaplate_map.id_dnaplate = dna_plate.id_dnaplate
and dna_plate.id_dnaplate = dnaplate_status.id_dnaplate
and dnaplate_status.status = dnaplatestatusdict.id_dict
and bb_map.id_beckmanbox = \'$id\'
and dnaplate_status.iscurrent = 1
and dnaplatestatusdict.description = 'Created'
";


    my %hash;
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my ($dnaplate, $copy_type);
    while ( ($dnaplate, $copy_type) = $sth1->fetchrow){
	$hash{$dnaplate} = $copy_type;
    }
    $sth1->finish();

    return (\%hash);
}

sub ok_2_cancel_DP {
    # You should not be able to cancel a DP if a 'live' LP has been created
    # from it.

    my ($pkg, $dbh, $id, $type) = @_;
    my @plates;

    if ($type eq 'GP'){ 
	#push @plates, 'NONE';
	# For now, NO RULES WHEN CANCELLING A GP
    } else {

	my $statement = "
select distinct loadingplate.id_loadingplate
from dnaplate_map, loadingsample, loadingplate, loadingplate_status, lpstatusdict
where dnaplate_map.id_dnaplatemap = loadingsample.id_dnaplatemap
and loadingsample.id_loadingplate = loadingplate.id_loadingplate
and loadingplate.id_loadingplate = loadingplate_status.id_loadingplate
and loadingplate_status.status = lpstatusdict.id_dict
and loadingplate_status.iscurrent = 1
and lpstatusdict.description != 'Cancelled'
and dnaplate_map.id_dnaplate = \'$id\'
";
	
	my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
	$sth1->execute || die "ERROR:$DBI::errstr";
	my $p;
	while ($p = $sth1->fetchrow()){
	    push @plates, $p
	    }
	$sth1->finish();
	
    }
    return (\@plates);
}



sub ok_2_cancel_LP {
    # You cannot cancel a LP once it has been submitted to the West Pavilion.
    my ($pkg, $dbh, $id) = @_;
    my $statement = "
select loadingplate.id_loadingplate 
from loadingplate, loadingplate_status, lpstatusdict
where loadingplate.id_loadingplate = loadingplate_status.id_loadingplate
and loadingplate_status.status = lpstatusdict.id_dict
and loadingplate_status.iscurrent = 1
and lpstatusdict.description != 'Created'
and loadingplate.id_loadingplate = \'$id\'
";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my ($lp) = $sth1->fetchrow();
    $sth1->finish();

    if ($lp eq ''){
	$lp = 'good';
    } else { 
	$lp = 'bad' ;
    }

    return($lp);
}

#########################################


sub autonaming_status {
    my ($pkg, $dbh, $clone) = @_;
    my $statement = "select CLONENAME, ROOT, NEXT_VAL from autoboxname where clonename = \'$clone\'";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my %hash;
    ($hash{CLONENAME}, $hash{ROOT}, $hash{NEXT_VAL}) = $sth1->fetchrow();
    $sth1->finish();
    #warn "$hash{CLONENAME} $hash{ROOT} $hash{NEXT_VAL}\n";
    return (\%hash);
}


sub previous_BB_submission {
     my ($pkg, $dbh, $clone) = @_;

my $statement = "
select 'Yes' from dual
where exists (select c.id_beckmanbox
        from ligation a, subclone b, bb_map c
        where a.id_ligation = b.id_ligation
        and b.id_subclone = c.id_subclone
        and a.clonename = \'$clone\'
        and exists (select 1
                from bb_status d
                where d.iscurrent = 1
                and d.status != 2
                and c.id_beckmanbox = d.id_beckmanbox)
)
";

     my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
     $sth1->execute || die "ERROR:$DBI::errstr";
     my $l = $sth1->fetchrow();
     $sth1->finish();
     if ($l ne 'Yes'){
	 $l = 'No';
     }
     return($l);
}


sub ligation_exists {
    my ($pkg, $dbh, $lig) = @_;
    my $statement = "select clonename from ligation where id_ligation = \'$lig\'";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my $l = $sth1->fetchrow();
    $sth1->finish();
    return($l);
} 

#########################################

sub platename_exists  {
    my ($pkg, $dbh, $name) = @_;
    my $statement = "select name from beckman_box where name = \'$name\'";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my $n = $sth1->fetchrow();
    $sth1->finish();
    if ($n eq ''){ $n = 0;} else { $n = 1;}
    return ($n);
}

sub lp_name_used {
    my ($pkg, $dbh, $name, $suffix) = @_;
    my $statement = 
"select platename 
from loadingplate, loadingplate_status
where loadingplate.id_loadingplate = loadingplate_status.id_loadingplate
and platename = \'$name\'
and suffix = \'$suffix\'
and loadingplate_status.iscurrent = 1
and loadingplate_status.status != 6
";

#    warn "\n$statement\n";

    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my $n = $sth1->fetchrow();
    $sth1->finish();
    if ($n eq ''){ $n = 0;} else { $n = 1;}
    return ($n);
}


sub p_s_i_loadingplate_combo {

    #warn "Running new version of p_s_i_loadingplate_combo !!!\n";

    my ($pkg, $dbh, $id_dnaplate, $suffix, $iteration) = @_;
    my $statement;

#    if ($iteration ne ''){
#	 $statement = "
#select platename 
#from loadingplate, loadingplate_status
#where loadingplate.id_loadingplate = loadingplate_status.id_loadingplate
#and platename = \'$name\'
#and suffix = \'$suffix\'
#and iteration = \'$iteration\'
#and loadingplate_status.iscurrent = 1
#and loadingplate_status.status != 6
#";
#    } else {
#	 $statement = "
#select platename 
#from loadingplate, loadingplate_status
#where loadingplate.id_loadingplate = loadingplate_status.id_loadingplate
#and platename = \'$name\'
#and suffix = \'$suffix\'
#and iteration is NULL
#and loadingplate_status.iscurrent = 1
#and loadingplate_status.status != 6
#";
#    }

    
    if ($iteration ne ''){
	$statement = "
select distinct loadingsample.id_loadingplate 
from dnaplate_map, loadingsample, loadingplate, loadingplate_status
where dnaplate_map.id_dnaplatemap = loadingsample.id_dnaplatemap
and loadingsample.id_loadingplate = loadingplate_status.id_loadingplate
and loadingsample.id_loadingplate = loadingplate.id_loadingplate
and dnaplate_map.id_dnaplate = \'$id_dnaplate\'
and loadingplate.suffix = \'$suffix\'
and loadingsample.iteration = \'$iteration\'
and loadingplate_status.iscurrent = 1
and loadingplate_status.status != 6
";
    } else {
	$statement = "
select distinct loadingsample.id_loadingplate 
from dnaplate_map, loadingsample, loadingplate, loadingplate_status
where dnaplate_map.id_dnaplatemap = loadingsample.id_dnaplatemap
and loadingsample.id_loadingplate = loadingplate_status.id_loadingplate
and loadingsample.id_loadingplate = loadingplate.id_loadingplate
and dnaplate_map.id_dnaplate = \'$id_dnaplate\'
and loadingplate.suffix = \'$suffix\'
and loadingsample.iteration is NULL
and loadingplate_status.iscurrent = 1
and loadingplate_status.status != 6
";
    }

    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my $n = $sth1->fetchrow();
    $sth1->finish();
    if ($n eq ''){ $n = 0;} else { $n = 1;}
    return ($n);
}

sub p_s_i_loadingplate_combo_by_platename {

    my ($pkg, $dbh, $name, $suffix, $iteration) = @_;
    my $statement;

    if ($iteration ne ''){
	 $statement = "
select platename 
from loadingplate, loadingplate_status
where loadingplate.id_loadingplate = loadingplate_status.id_loadingplate
and platename = \'$name\'
and suffix = \'$suffix\'
and iteration = \'$iteration\'
and loadingplate_status.iscurrent = 1
and loadingplate_status.status != 6
";
    } else {
	 $statement = "
select platename 
from loadingplate, loadingplate_status
where loadingplate.id_loadingplate = loadingplate_status.id_loadingplate
and platename = \'$name\'
and suffix = \'$suffix\'
and iteration is NULL
and loadingplate_status.iscurrent = 1
and loadingplate_status.status != 6
";
    }

    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my $n = $sth1->fetchrow();
    $sth1->finish();
    if ($n eq ''){ $n = 0;} else { $n = 1;}
    return ($n);
}

######################################################
######################################################




sub teamname_exists {
    my ($pkg, $dbh, $team) = @_;
    my $statement = "select teamname from team where teamname = \'$team\' and istracking = 1";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my $t = $sth1->fetchrow();
    $sth1->finish();
    if ($t eq ''){ $t = 0;} else { $t = 1;}
    return ($t);
}

sub beckman_ok2sequence {
    my ($pkg, $dbh, $bb) = @_;
    my $statement = "
select beckman_box.id_beckmanbox
from beckman_box, bb_status
where beckman_box.id_beckmanbox = bb_status.id_beckmanbox
and beckman_box.id_beckmanbox = \'$bb\'
and bb_status.iscurrent = 1
and bb_status.status = 1
";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my $b = $sth1->fetchrow();
    $sth1->finish();
    if ($b eq ''){ $b = 0;} else { $b = 1;}
    return ($b);
}

package get;

sub clone_info {
   my ($pkg, $dbh, $name) = @_;
###
### These changes were made to ensure that function returns 
### correct chromosome for none human species.

### Also changed to using bind variables
### bsw 11 April 2001
###   
###my $statement = "
###select a.chromosome, a.speciesname
###from clone a
###where a.clonename = \'$name\'
###";

my $statement = "
SELECT 	CD.CHROMOSOME, A.SPECIESNAME
FROM 	CLONE A, CHROMOSOMEDICT CD
WHERE 	CD.ID_DICT (+) = A.CHROMOSOME
AND	A.CLONENAME = ? ";


   my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
   $sth1->execute($name) || die "ERROR:$DBI::errstr";
   my ($c, $s) = $sth1->fetchrow();
   $sth1->finish();
   return ($c, $s);
}

sub bb_info_from_name {
    my ($pkg, $dbh, $name) = @_;
    my $statement = "
select beckman_box.id_beckmanbox
from beckman_box
where name = \'$name\'
";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my $id = $sth1->fetchrow();
    $sth1->finish();
    return ($id);
}

sub wp_submit_data_from_lps {

    my ($pkg, $dbh, $list, $JIT) = @_;
    my $statement;
    my $desc;

    if( $JIT ){
        $desc = "Received - JIT";
    }
    else {
        $desc = "Submitted";
    }

#warn "JIT = $JIT, desc = $desc\n";

    $statement = "
select distinct loadingplate.id_loadingplate, loadingplate.platename, loadingplate.suffix, loadingplate.iteration,
lpstatusdict.description, to_char(loadingplate_status.statusdate,'dd-Mon-YYYY HH24-MI-SS'), seqprotocol.name
from loadingplate, loadingplate_status, lpstatusdict, loadingsample, seqchem_seqprotocol, seqprotocol
where loadingplate.id_loadingplate = loadingsample.id_loadingplate
and loadingsample.id_scsp = seqchem_seqprotocol.id_scsp
and seqchem_seqprotocol.id_protocol = seqprotocol.id_protocol
and loadingplate.id_loadingplate = loadingplate_status.id_loadingplate(+)
and loadingplate_status.status = lpstatusdict.id_dict(+)
and loadingplate.id_loadingplate = ?
and lpstatusdict.description = '$desc'
";


    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    my ($id_loadingplate, $platename, $suffix, $iteration, $description, $date, $type);
    my %data;
    foreach my $next (@$list){
	$sth1->execute($next) || die "ERROR:$DBI::errstr";
	while ( ($id_loadingplate, $platename, $suffix, $iteration, $description, $date, $type) = $sth1->fetchrow() ){
	    $type =~ /^(\w+)-/;
	    $data{$id_loadingplate} = "$platename$suffix$iteration   $date $1";
	}
    }
    $sth1->finish();
    return(\%data);
}


sub today {
    my ($pkg, $dbh) = @_;
    my $statement = "select to_char(sysdate,'dd-Mon-YYYY') from dual";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my $t = $sth1->fetchrow();
    $sth1->finish();
    return ($t);
}


sub id_map_vs_id_bb_map {
    my ($pkg, $dbh, $box) = @_;
    my $statement = "
select id_map, id_bbmap
from bb_map
where id_beckmanbox = \'$box\'
";

    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my ($id_map, $id_bbmap);
    my %hash;
    while ( ($id_map, $id_bbmap) = $sth1->fetchrow() ){
	$hash{$id_map} = $id_bbmap;
    }
    $sth1->finish();
    return (\%hash);
}

sub id_map_vs_id_dnaplatemap {
    my ($pkg, $dbh, $id_dnaplate) = @_;
    my $statement = "
select id_map, id_dnaplatemap
from dnaplate_map
where id_dnaplate = \'$id_dnaplate\'
";

    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my ($id_map, $id_dnaplatemap);
    my %hash;
    while ( ($id_map, $id_dnaplatemap) = $sth1->fetchrow() ){
	$hash{$id_map} = $id_dnaplatemap;
    }
    $sth1->finish();
    return (\%hash);
}

sub list_of_preptypes {    
    my ($pkg, $dbh) = @_;
    my $statement = "select id_dict, description from preptypedict";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my ($id_dict, $description);
    my %hash;
    while ( ($id_dict, $description) = $sth1->fetchrow() ){
	$hash{$description} = $id_dict;
    }
    $sth1->finish();
    return (\%hash);
}

sub list_of_protocols {
    my ($pkg, $dbh) = @_;
#    my $statement = "select id_protocol, name from seqprotocol";
    my $statement = "
select id_protocol, name 
from seqprotocol
where is_shotgun = 1
";

    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my ($id_dict, $description);
    my %hash;
    while ( ($id_dict, $description) = $sth1->fetchrow() ){
	$hash{$description} = $id_dict;
    }
    $sth1->finish();
    return (\%hash);
}

sub list_of_dyesets {
    my ($pkg, $dbh, $id_seqchems) = @_;
    my $statement = "
select dyeset.id_dyeset, dyeset.name
from seqchemistry, dyeset
where seqchemistry.id_dyeset = dyeset.id_dyeset
and seqchemistry.id_seqchem in ( @$id_seqchems )
and seqchemistry.is_westpav = 1
";

    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my ($id_dict, $description);
    my %hash;
    while ( ($id_dict, $description) = $sth1->fetchrow() ){
	$hash{$description} = $id_dict;
    }
    $sth1->finish();
    return (\%hash);
}

sub list_of_directions {
    my ($pkg, $dbh, $id_seqchems) = @_;
#    my $statement = "
#select distinct seqchemistry.is_fwd
#from seqchemistry
#where seqchemistry.id_seqchem in ( @$id_seqchems )
#and seqchemistry.is_westpav = 1
#";

my $statement = "
select distinct pt_vector.is_fwd
from pt_vector, seqchemistry
where seqchemistry.id_ptv = pt_vector.id_ptv
and seqchemistry.id_seqchem in ( @$id_seqchems )
and seqchemistry.is_westpav = 1
";

    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my $id;
    my %hash;
    while ( ($id) = $sth1->fetchrow() ){
	my $description;
	if ($id == 1){
	    $description = 'Forward';
	} else {
	    $description = 'Reverse';
	}
	$hash{$description} = $id;
    }
    $sth1->finish();
    return (\%hash);
}

sub list_of_pcrs {
    my ($pkg, $dbh, $id_seqchems) = @_;
    my $statement = "
select distinct seqchemistry.is_pcr
from seqchemistry
where seqchemistry.id_seqchem in ( @$id_seqchems )
and seqchemistry.is_westpav = 1
";

    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my $id;
    my %hash;
    while ( ($id) = $sth1->fetchrow() ){
	my $description;
	if ($id == 1){
	    $description = 'Yes';
	} else {
	    $description = 'No';
	}
	$hash{$description} = $id;
    }
    $sth1->finish();
    return (\%hash);
}

sub list_of_terms {
    my ($pkg, $dbh, $id_seqchems) = @_;
    my $statement = "
select distinct seqchemistry.is_primer
from seqchemistry
where seqchemistry.id_seqchem in ( @$id_seqchems )
and seqchemistry.is_westpav = 1
";

    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my $id;
    my %hash;
    while ( ($id) = $sth1->fetchrow() ){
	my $description;
	if ($id == 1){
	    $description = 'Primer';
	} else {
	    $description = 'Terminator';
	}
	$hash{$description} = $id;
    }
    $sth1->finish();
    return (\%hash);
}

sub list_of_vectors {
    my ($pkg, $dbh, $id_seqchems) = @_;

    my $statement = "
select distinct pt_vector.vectorname
from pt_vector, seqchemistry
where seqchemistry.id_ptv = pt_vector.id_ptv
and seqchemistry.id_seqchem in ( @$id_seqchems )
and seqchemistry.is_westpav = 1
";

    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my $description;
    my $id = 0;
    my %hash;
    while ( ($description) = $sth1->fetchrow() ){
	$id++;
	$hash{$description} = $id;
    }
    $sth1->finish();
    return (\%hash);
}

sub list_of_primers {
    my ($pkg, $dbh, $id_seqchems) = @_;
#    my $statement = "
#select distinct seqchemistry.primername
#from seqchemistry
#where seqchemistry.id_seqchem in ( @$id_seqchems )
#and seqchemistry.is_westpav = 1
#";

    my $statement = "
select distinct pt_vector.primername
from pt_vector, seqchemistry
where seqchemistry.id_ptv = pt_vector.id_ptv
and seqchemistry.id_seqchem in ( @$id_seqchems )
and seqchemistry.is_westpav = 1
";


    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my $description;
    my $id = 0;
    my %hash;
    while ( ($description) = $sth1->fetchrow() ){
	$id++;
	$hash{$description} = $id;
    }
    $sth1->finish();
    return (\%hash);
}


sub seqchems_wrt_protocol {
    my ($pkg, $dbh, $id_protocol) = @_;
    my $statement = "
select distinct seqchemistry.id_seqchem 
from seqchem_seqprotocol, seqchemistry
where seqchem_seqprotocol.id_seqchem = seqchemistry.id_seqchem
and id_protocol = \'$id_protocol\'
and seqchemistry.is_westpav = 1
";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my @a;
    my $s;
    while ( $s = $sth1->fetchrow() ){
	push @a, "$s";
	push @a, ",";
    }
    pop @a;
    $sth1->finish();
    return (\@a);
}

sub seqchems_wrt_dyeset {
    my ($pkg, $dbh, $id_dyeset, $id_seqchems) = @_;
    my $statement = "
select distinct id_seqchem
from seqchemistry 
where id_dyeset = \'$id_dyeset\'
and seqchemistry.id_seqchem in ( @$id_seqchems )
and seqchemistry.is_westpav = 1
";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my @a;
    my $s;
    while ( $s = $sth1->fetchrow() ){
	push @a, "$s";
	push @a, ",";
    }
    pop @a;
    $sth1->finish();
    return (\@a);
}

sub seqchems_wrt_direction {
    my ($pkg, $dbh, $direction, $id_seqchems) = @_;

    my $statement = "
select distinct id_seqchem
from seqchemistry, pt_vector
where seqchemistry.id_ptv = pt_vector.id_ptv
and pt_vector.is_fwd = \'$direction\'
and seqchemistry.id_seqchem in ( @$id_seqchems )
and seqchemistry.is_westpav = 1
";

    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my @a;
    my $s;
    while ( $s = $sth1->fetchrow() ){
	push @a, "$s";
	push @a, ",";
    }
    pop @a;
    $sth1->finish();
    return (\@a);
}

sub seqchems_wrt_pcr {
    my ($pkg, $dbh, $pcr, $id_seqchems) = @_;
    my $statement = "
select distinct id_seqchem
from seqchemistry 
where seqchemistry.is_pcr = \'$pcr\'
and seqchemistry.id_seqchem in ( @$id_seqchems )
and seqchemistry.is_westpav = 1
";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my @a;
    my $s;
    while ( $s = $sth1->fetchrow() ){
	push @a, "$s";
	push @a, ",";
    }
    pop @a;
    $sth1->finish();
    return (\@a);
}

sub seqchems_wrt_term {
    my ($pkg, $dbh, $term, $id_seqchems) = @_;
    my $statement = "
select distinct id_seqchem
from seqchemistry 
where seqchemistry.is_primer = \'$term\'
and seqchemistry.id_seqchem in ( @$id_seqchems )
and seqchemistry.is_westpav = 1
";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my @a;
    my $s;
    while ( $s = $sth1->fetchrow() ){
	push @a, "$s";
	push @a, ",";
    }
    pop @a;
    $sth1->finish();
    return (\@a);
}

sub seqchems_wrt_vector {
    my ($pkg, $dbh, $vectorname, $id_seqchems) = @_;

    my $statement = "
select distinct id_seqchem
from seqchemistry, pt_vector
where seqchemistry.id_ptv = pt_vector.id_ptv
and pt_vector.vectorname = \'$vectorname\' 
and seqchemistry.id_seqchem in ( @$id_seqchems )
and seqchemistry.is_westpav = 1
";

    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my @a;
    my $s;
    while ( $s = $sth1->fetchrow() ){
	push @a, "$s";
	push @a, ",";
    }
    pop @a;
    $sth1->finish();
    return (\@a);
}


sub seqchems_wrt_primer {
    my ($pkg, $dbh, $primername, $id_seqchems) = @_;

        my $statement = "
select distinct id_seqchem
from seqchemistry, pt_vector
where seqchemistry.id_ptv = pt_vector.id_ptv
and pt_vector.primername = \'$primername\' 
and seqchemistry.id_seqchem in ( @$id_seqchems )
and seqchemistry.is_westpav = 1
";

    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my @a;
    my $s;
    while ( $s = $sth1->fetchrow() ){
	push @a, "$s";
	push @a, ",";
    }
    pop @a;
    $sth1->finish();
    return (\@a);
}

sub suffix_from_seqchem {
    my ($pkg, $dbh, $id_seqchems) = @_;
    my $statement = "
select distinct suffix
from seqchemistry 
where seqchemistry.id_seqchem =  \'$id_seqchems->[0]\'
and seqchemistry.is_westpav = 1
";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my $s = $sth1->fetchrow();
    return ($s);
}

sub id_scsp_wrt_id_seqchem_and_id_protocol {
    my ($pkg, $dbh, $id_seqchems, $id_protocol) = @_;
    my $statement = "
select id_scsp
from seqchem_seqprotocol 
where id_seqchem =  \'$id_seqchems->[0]\'
and id_protocol = \'$id_protocol\'
";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my $s = $sth1->fetchrow();
    return ($s);   
}



sub TEST_new_id_dnaplatemap {
    my ($pkg, $dbh, $count) = @_;
    my $statement = "select seq_dnaplatemap.nextval from dual";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    my @ret;
    foreach my $next (1 .. $count){
	$sth1->execute || die "ERROR:$DBI::errstr";
	my $s = $sth1->fetchrow();
	push @ret, $s;
    }
    $sth1->finish();
    return (\@ret);
}


sub TEST_new_id_subclone {
    my ($pkg, $dbh, $count) = @_;
    my $statement = "select seq_subclone.nextval from dual";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    my @ret;
    foreach my $next (1 .. $count){
	$sth1->execute || die "ERROR:$DBI::errstr";
	my $s = $sth1->fetchrow();
	push @ret, $s;
    }
    $sth1->finish();
    return (\@ret);

}

sub TEST_new_id_loadingsample {
    my ($pkg, $dbh, $count) = @_;
    my $statement = "select SEQ_LOADINGSAMPLE.nextval from dual";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    my @ret;
    foreach my $next (1 .. $count){
	$sth1->execute || die "ERROR:$DBI::errstr";
	my $s = $sth1->fetchrow();
	push @ret, $s;
    }
    $sth1->finish();
    return (\@ret);
}

sub rowcol_map_id {
    my ($pkg, $dbh) = @_;
    my $statement = "select id_map from rowcol_map where format = 384 order by id_map";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my @ret;
    my $got;
    while ($got = $sth1->fetchrow()){
	push @ret, $got;
    }
    $sth1->finish();
    return (\@ret);
}


sub new_id_subclone {
    my ($pkg, $dbh) = @_;
    my $statement = "select seq_subclone.nextval from dual";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my $s = $sth1->fetchrow();
    $sth1->finish();
    return ($s);
}

sub new_id_dnaplatemap {
    my ($pkg, $dbh) = @_;
    my $statement = "select seq_dnaplate.nextval from dual";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my $s = $sth1->fetchrow();
    $sth1->finish();
    return ($s);
}


sub TEST_new_id_bbmap {
    my ($pkg, $dbh, $count) = @_;
    my $statement = "select seq_bbmap.nextval from dual";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    my @ret;
    foreach my $next (1 .. $count){
	$sth1->execute || die "ERROR:$DBI::errstr";
	my $s = $sth1->fetchrow();
	push @ret, $s;
    }
    $sth1->finish();
    return (\@ret);
}

sub new_id_bbmap {
    my ($pkg, $dbh) = @_;
    my $statement = "select seq_bbmap.nextval from dual";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my $s = $sth1->fetchrow();
    $sth1->finish();
    return ($s);
}

sub new_id_beckmanbox {
    my ($pkg, $dbh) = @_;
    my $statement = "select seq_beckmanbox.nextval from dual";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my $s = $sth1->fetchrow();
    $sth1->finish();
    return ($s);
}

sub new_id_dnaplate {
    my ($pkg, $dbh) = @_;
    my $statement = "select seq_dnaplate.nextval from dual";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my $s = $sth1->fetchrow();
    $sth1->finish();
    return ($s);
}

sub new_id_loadingplate {
    my ($pkg, $dbh) = @_;
    my $statement = "select SEQ_LOADINGPLATE.nextval from dual";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my $s = $sth1->fetchrow();
    $sth1->finish();
    return ($s);
}

sub new_id_loadingsample {
    my ($pkg, $dbh) = @_;
    my $statement = "select SEQ_LOADINGSAMPLE.nextval from dual";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my $s = $sth1->fetchrow();
    $sth1->finish();
    return ($s);
}

sub new_session {
    my ($pkg, $dbh) = @_;
    my $statement = "select seq_session.nextval from dual";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $s = $sth->fetchrow();
    $sth->finish;
    return ($s);
}

sub loading_plate_info_from_id {
    my ($pkg, $dbh, $lp) = @_;
    my $statement = "
select loadingplate.id_loadingplate, loadingplate.platename, loadingplate.format, loadingplate_status.status, lpstatusdict.description
from loadingplate, loadingplate_status, lpstatusdict
where loadingplate.id_loadingplate = loadingplate_status.id_loadingplate
and loadingplate_status.status = lpstatusdict.id_dict
and loadingplate_status.iscurrent = 1
and loadingplate.id_loadingplate = \'$lp\'
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($b, $n, $f, $s, $d) = $sth->fetchrow();
    $sth->finish;
    return($b, $n, $f, $s, $d);
}


sub box_info_from_id {
    my ($pkg, $dbh, $box) = @_;
    my $statement = "
select beckman_box.id_beckmanbox, beckman_box.name, beckman_box.format, bb_status.status, bbstatusdict.description 
from beckman_box, bb_status, bbstatusdict
where beckman_box.id_beckmanbox = bb_status.id_beckmanbox
and bb_status.status = bbstatusdict.id_dict
and bb_status.iscurrent = 1
and beckman_box.ID_BECKMANBOX = \'$box\'
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($b, $n, $f, $s, $d) = $sth->fetchrow();
    $sth->finish;
    return($b, $n, $f, $s, $d);
}



sub box_name_from_dna_id {
    my ($pkg, $dbh, $dna) = @_;
    my $statement = "
select distinct beckman_box.name
from dnaplate_map, bb_map, beckman_box
where dnaplate_map.id_bbmap = bb_map.id_bbmap
and bb_map.id_beckmanbox = beckman_box.id_beckmanbox
and dnaplate_map.id_dnaplate = \'$dna\'
";

    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($n) = $sth->fetchrow();
    $sth->finish;
    return($n);
    
}


sub box_info_from_dna_id {
    my ($pkg, $dbh, $dna, $copy_type) = @_;
    my $statement = "
select distinct dna_plate.id_dnaplate, beckman_box.name, beckman_box.format, dnaplate_status.status, dnaplatestatusdict.description
from dna_plate, dnaplate_map, bb_map, beckman_box, dnaplate_status, dnaplatestatusdict
where dna_plate.id_dnaplate = dnaplate_map.id_dnaplate
and dna_plate.id_dnaplate = dnaplate_status.id_dnaplate
and dnaplate_map.id_bbmap = bb_map.id_bbmap
and bb_map.id_beckmanbox = beckman_box.id_beckmanbox
and dnaplate_status.status = dnaplatestatusdict.id_dict
and dna_plate.id_dnaplate = \'$dna\'
and dna_plate.copy_type = \'$copy_type\'
and dnaplate_status.iscurrent = 1
";


    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($d, $n, $f, $s, $desc) = $sth->fetchrow();
    $sth->finish;
    return($d, $n, $f, $s, $desc);
    
}


sub current_dnaplate_status {
    my ($pkg, $dbh, $dna) = @_;
    my $statement = "
select dnaplate_status.status
from dnaplate_status
where id_dnaplate = \'$dna\'
and dnaplate_status.iscurrent = 1 
";
#    warn "\n$statement\n";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($s) = $sth->fetchrow();
    return($s);
}



sub clonename_from_box_id {
    my ($pkg, $dbh, $box) = @_;

    my $statement = "
select distinct ligation.clonename
from bb_map, subclone, ligation
where bb_map.id_subclone = subclone.id_subclone
and subclone.id_ligation = ligation.id_ligation
and bb_map.id_beckmanbox = \'$box\'
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($name) = $sth->fetchrow();
    $sth->finish;

    if ($name eq ''){
	$statement = "
select distinct clone.clonename
from bb_map, subclone, ligation, fragmentation_band, dnaprep_frag, dnaprep, clone_culture, glystock, clone
where bb_map.id_subclone = subclone.id_subclone
and subclone.id_ligation = ligation.id_ligation
and ligation.id_fragband = fragmentation_band.id_fragband
and fragmentation_band.id_frag = dnaprep_frag.id_frag
and dnaprep_frag.id_prep = dnaprep.id_prep
and dnaprep.id_culture = clone_culture.id_culture
and clone_culture.id_glystock = glystock.id_glystock
and glystock.clonename = clone.clonename
and bb_map.id_beckmanbox = \'$box\'
";
	$sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
	$sth->execute || die "ERROR:$DBI::errstr";
	($name) = $sth->fetchrow();
	$sth->finish;
    }
    return($name);
}

sub seq_info_from_ic_scsp {
    my ($pkg, $dbh, $id) = @_;

    my $statement = "
select seqprotocol.name, seqchem_lookup.old_id, pt_vector.is_fwd, seqchemistry.is_primer, seqchemistry.suffix, pt_vector.primername, pt_vector.vectorname, dyeset.name, seqchemistry.is_westpav, seqchemistry.is_pcr
from seqchem_seqprotocol, seqprotocol, seqchemistry, dyeset, pt_vector, seqchem_lookup
where seqchemistry.id_ptv = pt_vector.id_ptv
and seqchem_seqprotocol.id_scsp = seqchem_lookup.id_scsp(+)
and seqchem_seqprotocol.id_protocol = seqprotocol.id_protocol
and seqchem_seqprotocol.id_seqchem = seqchemistry.id_seqchem
and seqchemistry.id_dyeset = dyeset.id_dyeset
and seqchem_seqprotocol.id_scsp = \'$id\'
and seqchemistry.is_westpav = 1
and seqprotocol.is_shotgun = 1
";

    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($protocol, $seqchem, $dir, $primer, $suffix, $primername, $vectorname, $dyeset, $wp, $pcr) = $sth->fetchrow();
    $sth->finish;

    if ($dir == 1){
	$dir = "Forward";
    } else {
	$dir = "Reverse";
    }

    if ($primer == 1){
	$primer = "Primer";
    } else {
	$primer = "Terminator";
    }

    if ($pcr == 1){
	$pcr = "Yes";
    } else {
	$pcr = "No";
    }

    return($protocol, $seqchem, $dir, $primer, $suffix, $primername, $vectorname, $dyeset, $wp, $pcr);

}

sub last_iteration_of_loadingplate {

    my ($pkg, $dbh, $name, $suffix) = @_;

    my $statement = "
select platename, iteration 
from loadingplate
where platename = \'$name\'
and iteration is not NULL
and suffix = \'$suffix\'
and loadingplate.id_loadingplate not in (
    select a.id_loadingplate
    from loadingplate a, loadingplate_status b
    where a.id_loadingplate = b.id_loadingplate
    and b.status = 6
    and b.iscurrent = 1)
order by iteration desc
";

    #warn "----->\n$statement\n";

    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($plate, $iter) = $sth->fetchrow();
    $sth->finish;

#    warn "GOT -> $iter\n";

    if ( ($plate eq '') && ($iter eq '') ){

	# We need to check if the lp is in the db with NO iteration
	$statement = "
select platename 
from loadingplate 
where platename = \'$name\' 
and suffix = \'$suffix\' 
and iteration is NULL
and loadingplate.id_loadingplate not in (
    select a.id_loadingplate
    from loadingplate a, loadingplate_status b
    where a.id_loadingplate = b.id_loadingplate
    and b.status = 6
    and b.iscurrent = 1)
";

	#warn "----->\n$statement\n";
	
	my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
	$sth->execute || die "ERROR:$DBI::errstr";
	($plate) = $sth->fetchrow();
	$sth->finish;
	if ($plate eq ''){
	    $iter = 'lp not used before';
	} else {
	    $iter = 'lp with no iteration';
	}
    }

    return ($iter);
}



sub NEWlast_iteration_of_loadingplate {

    my ($pkg, $dbh, $dna_plate, $suffix) = @_;

    my $statement = "
select /*+ USE_NL(loadingplate) */ 
distinct loadingplate.id_loadingplate, loadingsample.iteration
from dnaplate_map, loadingsample, loadingplate, loadingplate_status
where dnaplate_map.id_dnaplatemap = loadingsample.id_dnaplatemap
and loadingsample.id_loadingplate = loadingplate.id_loadingplate
and loadingplate_status.id_loadingplate = loadingplate.id_loadingplate
and dnaplate_map.id_dnaplate = \'$dna_plate\'
and loadingplate.suffix = \'$suffix\'
and loadingplate_status.status != 6
and loadingplate_status.iscurrent = 1 
order by loadingsample.iteration asc
";

    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($plate, $iter) = $sth->fetchrow();
    $sth->finish;

    if ( ($plate eq '') && ($iter eq '') ){
	$iter = 'lp not used before';
    } elsif ( ($plate ne '') && ($iter eq '') ){
	$iter = 'lp with no iteration';
    }

    return ($iter);

}







sub beckman_id_from_dnaplate_id {
    my ($pkg, $dbh, $id) = @_;
    my $statement = "
select distinct bb_map.id_beckmanbox
from bb_map, dnaplate_map
where bb_map.id_bbmap = dnaplate_map.id_bbmap
and dnaplate_map.id_dnaplate = \'$id\'
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($b) = $sth->fetchrow();
    $sth->finish;
    return($b);
}







package make;

sub new_subclone_entry {
    my ($pkg, $dbh, $subclone, $ligation) = @_;
    my $statement = "insert into subclone (ID_SUBCLONE, ID_LIGATION) values (\'$subclone\', \'$ligation\')";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    $sth1->finish();
}


sub new_subclone_entry_batch {
    my ($pkg, $dbh, $subclones, $ligation) = @_;
    my $statement = "insert into subclone (ID_SUBCLONE, ID_LIGATION) values ( ? , \'$ligation\')";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    foreach my $next (@$subclones){
	$sth1->execute($next) || die "ERROR:$DBI::errstr";
    }
    $sth1->finish();
}


sub new_plate_entry {
    my ($pkg, $dbh, $id_dnaplate, $format, $prep_type, $copy_type, $team) = @_;
    my $statement = "insert into dna_plate (ID_DNAPLATE, FORMAT, PREP_TYPE, COPY_TYPE,TEAM) values(\'$id_dnaplate\', \'$format\', \'$prep_type\', \'$copy_type\', \'$team\')";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    $sth1->finish();
}

sub new_lpplate_entry {
    my ($pkg, $dbh, $id_loadingplate, $name, $team, $format, $suffix, $iteration, $cat) = @_;

    my $statement = "
insert into loadingplate
(ID_LOADINGPLATE, PLATENAME, TEAM, CATEGORY, WESTPAV, FORMAT, ID_RUNTYPE, SUFFIX, ITERATION, FASTTRACK) 
values(\'$id_loadingplate\', \'$name\', \'$team\', \'$cat\', 1, \'$format\', NULL, \'$suffix\', \'$iteration\', 0)
";

#warn "$statement\n";

    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    $sth1->finish();
}

sub new_beckman_box_entry {
    my ($pkg, $dbh, $id_beckmanbox, $format, $name, $is_original, $team) = @_;

    #Check the name given
    if ($name ne ''){
	my $name_test = test->naming($name);
	unless ($name_test) { 
	    die "Name $name cannot be used.\n";
	}
    }

    my $statement;

    if ($name eq '-' || $name eq ''){
	$statement = "
insert into beckman_box (ID_BECKMANBOX, FORMAT, NAME, IS_ORIGINAL, TEAM) values (\'$id_beckmanbox\', \'$format\', NULL, \'$is_original\', \'$team\')";
    } else {
	$statement = "
insert into beckman_box (ID_BECKMANBOX, FORMAT, NAME, IS_ORIGINAL, TEAM) values (\'$id_beckmanbox\', \'$format\', \'$name\', \'$is_original\', \'$team\')";
    }
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    $sth1->finish();
}

sub new_dna_plate_status_entry {
    my ($pkg, $dbh, $id_plate, $session, $prog, $who) = @_;
    my $statement = "
insert into dnaplate_status
(ID_DNAPLATE, STATUS, STATUSDATE, ISCURRENT, SESSIONID, PROGRAM, OPERATOR)
values (\'$id_plate\', 1, sysdate, 1, \'$session\', \'$prog\', \'$who\')
";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    $sth1->finish();
}

sub new_lp_plate_status_entry {
    my ($pkg, $dbh, $id_plate, $session, $prog, $who) = @_; 
    my $statement = "
insert into loadingplate_status
(ID_LOADINGPLATE, STATUS, STATUSDATE, ISCURRENT, SESSIONID, PROGRAM, OPERATOR)
values (\'$id_plate\', 1, sysdate, 1, \'$session\', \'$prog\', \'$who\')
";

#warn "$statement\n";

    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    $sth1->finish();
}

sub new_beckman_box_status_entry {
    my ($pkg, $dbh, $id_beckmanbox, $session, $prog, $who) = @_;
    my $statement = "
insert into bb_status
(ID_BECKMANBOX, STATUS, STATUSDATE, ISCURRENT, SESSIONID, PROGRAM, OPERATOR)
values (\'$id_beckmanbox\', 1, sysdate, 1, \'$session\', \'$prog\', \'$who\')
";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    $sth1->finish();
}

sub new_bbmap_entry {
    my ($pkg, $dbh, $id_bbmap, $id_subclone, $id_beckmanbox, $id_map) = @_;
    my $statement = "
insert into bb_map (ID_BBMAP, ID_SUBCLONE, ID_BECKMANBOX, ID_MAP) values (\'$id_bbmap\', \'$id_subclone\', \'$id_beckmanbox\', \'$id_map\')";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    $sth1->finish();
}

sub new_bbmap_entry_batch {
    my ($pkg, $dbh, $id_beckmanbox, $info) = @_;
    my $statement = "
insert into bb_map (ID_BBMAP, ID_SUBCLONE, ID_BECKMANBOX, ID_MAP) values (?, ?, \'$id_beckmanbox\', ?)";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";

    my ($a, $b, $c);
    foreach my $next (@$info){
	$a = $next->{subclone};
	$b = $next->{bb_map};
	$c = $next->{id_map};
	$sth1->bind_param(1, $b);
	$sth1->bind_param(2, $a);
	$sth1->bind_param(3, $c);
	$sth1->execute || die "ERROR:$DBI::errstr";
    }
    $sth1->finish();
}

sub new_dnaplate_map_entry {
    my ($pkg, $dbh, $id_dnaplatemap, $id_bbmap, $id_dnaplate, $id_map) = @_;
    my $statement = "
insert into  dnaplate_map(ID_DNAPLATEMAP, ID_BBMAP, ID_DNAPLATE, ID_MAP) values (\'$id_dnaplatemap\', \'$id_bbmap\', \'$id_dnaplate\', \'$id_map\')";

    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    $sth1->finish();
}

sub new_dnaplate_map_entry_batch {
    my ($pkg, $dbh, $id_dnaplatemaps, $mapping, $id_dnaplate, $count) = @_;
    my $statement = "insert into dnaplate_map(ID_DNAPLATEMAP, ID_BBMAP, ID_DNAPLATE, ID_MAP) values (?, ?, $id_dnaplate, ?)";

    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    my ($a, $b, $c);
    foreach my $next (0 .. ($count-1) ){
	my $number = ($next+1);
	$a = $id_dnaplatemaps->[$next];
	$b = $mapping->{$number};
	$c = $number;

	$sth1->bind_param(1, $a);
	$sth1->bind_param(2, $b);
	$sth1->bind_param(3, $c);
	$sth1->execute || die "ERROR:$DBI::errstr";
    }
    $sth1->finish();
}


sub new_loadingsample_entry {
    my ($pkg, $dbh, $ID_LOADINGSAMPLE, $ID_LOADINGPLATE, $ID_MAP, $ID_SEQCHEM, $ITERATION, $ID_DNAPLATEMAP, $ID_SCSP) = @_;
    my $statement = "
insert into loadingsample
(ID_LOADINGSAMPLE, ID_LOADINGPLATE, ID_MAP, ID_SEQCHEM, ITERATION, ID_DNAPLATEMAP, ID_SCSP) 
values (\'$ID_LOADINGSAMPLE\', \'$ID_LOADINGPLATE\', \'$ID_MAP\', \'$ID_SEQCHEM\', \'$ITERATION\', \'$ID_DNAPLATEMAP\', \'$ID_SCSP\')
";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    $sth1->finish();
}


sub new_loadingsample_entry_batch {
    my ($pkg, $dbh, $ID_LOADINGSAMPLE, $ID_LOADINGPLATE, $ID_SEQCHEM, $ITERATION, $mapping, $ID_SCSP, $count) = @_;

    my $statement;

    if ($ID_SEQCHEM ne 'NULL') {
$statement = "
insert into loadingsample
(ID_LOADINGSAMPLE, ID_LOADINGPLATE, ID_MAP, ID_SEQCHEM, ITERATION, ID_DNAPLATEMAP, ID_SCSP) 
values (?, \'$ID_LOADINGPLATE\', ? , \'$ID_SEQCHEM\', \'$ITERATION\', ? , \'$ID_SCSP\')
";
} else {
$statement = "
insert into loadingsample
(ID_LOADINGSAMPLE, ID_LOADINGPLATE, ID_MAP, ID_SEQCHEM, ITERATION, ID_DNAPLATEMAP, ID_SCSP) 
values (?, \'$ID_LOADINGPLATE\', ? , NULL, \'$ITERATION\', ? , \'$ID_SCSP\')
";
}

    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    my ($a, $b, $c);
    foreach my $next (0 .. ($count-1) ){
	my $number = ($next+1);
	$a = $ID_LOADINGSAMPLE->[$next];
	$c = $mapping->{$number};
	$b = $number;
	
#	warn "ID_LOADINGSAMPLE $a ID_LOADINGPLATE $ID_LOADINGPLATE ID_MAP $b ID_SEQCHEM $ID_SEQCHEM ITERATION $ITERATION ID_DNAPLATEMAP $c ID_SCSP $ID_SCSP\n";

	$sth1->bind_param(1, $a);
	$sth1->bind_param(2, $b);
	$sth1->bind_param(3, $c);
	$sth1->execute || die "ERROR:$DBI::errstr";
    }
    $sth1->finish();
}

#################################################################################################
#################################################################################################
#################################################################################################

sub NEWnew_loadingsample_entry_batch {
    #############################
    # This gets called PER PLATE
    #############################

    my ($pkg, $dbh, $data) = @_;
    
    ##########################################################################
    # Check the first loadingsample entry to get the id_seqchem for the plate
    # Need 1 for a NULL (old_style) ID_SEQCHEM
    ##########################################################################
    if ($data->[0]->{ID_SEQCHEM} eq 'NULL'){

	my $statement = "insert into loadingsample (ID_LOADINGSAMPLE, ID_LOADINGPLATE, ID_MAP, ID_SEQCHEM, ITERATION, ID_DNAPLATEMAP, ID_SCSP) values (?, ?, ?, NULL, ?, ?, ?)";
	my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
	my ($a, $b, $c, $d, $e, $f, $g);
	foreach my $next (@$data){
	    $a = $next->{ID_LOADINGSAMPLE};
	    $b = $next->{ID_LOADINGPLATE};
	    $c = $next->{ID_MAP};
	    $d = $next->{ID_SEQCHEM};
	    $e = $next->{ITERATION};
	    $f = $next->{ID_DNAPLATEMAP};
	    $g = $next->{ID_SCSP};
	    $sth1->bind_param(1, $a);
	    $sth1->bind_param(2, $b);
	    $sth1->bind_param(3, $c);
	    ############################
	    # $sth1->bind_param(4, $d);
	    ############################
	    $sth1->bind_param(4, $e);
	    $sth1->bind_param(5, $f);
	    $sth1->bind_param(6, $g);
	    $sth1->execute || die "ERROR:$DBI::errstr";
	}
	$sth1->finish();

    } else {

	##############################################
	# Need 1 for a NOT NULL (old_style ID_SEQCHEM
	##############################################
	my $statement = "insert into loadingsample (ID_LOADINGSAMPLE, ID_LOADINGPLATE, ID_MAP, ID_SEQCHEM, ITERATION, ID_DNAPLATEMAP, ID_SCSP) values (?, ?, ?, ?, ?, ?, ?)";
	my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
	my ($a, $b, $c, $d, $e, $f, $g);
	foreach my $next (@$data){
	    $a = $next->{ID_LOADINGSAMPLE};
	    $b = $next->{ID_LOADINGPLATE};
	    $c = $next->{ID_MAP};
	    $d = $next->{ID_SEQCHEM};
	    $e = $next->{ITERATION};
	    $f = $next->{ID_DNAPLATEMAP};
	    $g = $next->{ID_SCSP};
	    $sth1->bind_param(1, $a);
	    $sth1->bind_param(2, $b);
	    $sth1->bind_param(3, $c);
	    $sth1->bind_param(4, $d);
	    $sth1->bind_param(5, $e);
	    $sth1->bind_param(6, $f);
	    $sth1->bind_param(7, $g);
	    $sth1->execute || die "ERROR:$DBI::errstr";
	}
	$sth1->finish();

    }
}

package upd;

sub name_of_loadingplate {
    my ($pkg, $dbh, $plate, $name) = @_;
    my $statement = "
update loadingplate set platename = \'$name\'
where ID_LOADINGPLATE = \'$plate\'
";

    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    $sth1->finish();
}

sub was_called_entry_of_beckmanbox {
    my ($pkg, $dbh, $box, $name) = @_;
    my $statement = "
update beckman_box set WAS_CALLED = \'$name\'
where ID_BECKMANBOX = \'$box\'
";

    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    $sth1->finish();
}

sub name_of_beckmanbox {
    my ($pkg, $dbh, $box, $name) = @_;
    my $statement = "
update beckman_box set NAME = \'$name\'
where ID_BECKMANBOX = \'$box\'
";

    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    $sth1->finish();
}

sub beckmanbox_status {
    my ($pkg, $dbh, $box, $new_status, $session, $program, $operator, $remark) = @_;
    my $statement = "
update bb_status set iscurrent = 0
where ID_BECKMANBOX = \'$box\'
and iscurrent = 1
";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    $sth1->finish();
    
    $statement = "insert into bb_status(ID_BECKMANBOX, STATUS, STATUSDATE, ISCURRENT, SESSIONID, PROGRAM, OPERATOR, REMARK) values(\'$box\', \'$new_status\', sysdate, 1, \'$session\', \'$program\', \'$operator\', \'$remark\')";

    $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    $sth1->finish();
}

sub dnaplate_status {
    my ($pkg, $dbh, $plate, $new_status, $session, $program, $operator, $remark) = @_;
    my $statement = "
update dnaplate_status set iscurrent = 0
where ID_DNAPLATE = \'$plate\'
and iscurrent = 1
";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    $sth1->finish();
    
    $statement = "insert into dnaplate_status(ID_DNAPLATE, STATUS, STATUSDATE, ISCURRENT, SESSIONID, PROGRAM, OPERATOR, REMARK) values(\'$plate\', \'$new_status\', sysdate, 1, \'$session\', \'$program\', \'$operator\', \'$remark\')";
    $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    $sth1->finish();
}

sub loadingplate_status {
    my ($pkg, $dbh, $lp, $new_status, $session, $program, $operator, $remark) = @_;
    my $statement = "
update loadingplate_status set iscurrent = 0
where ID_LOADINGPLATE = \'$lp\'
and iscurrent = 1
";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    $sth1->finish();
    
    $statement = "insert into loadingplate_status(ID_LOADINGPLATE, STATUS, STATUSDATE, ISCURRENT, SESSIONID, PROGRAM, OPERATOR, REMARK) values(\'$lp\', \'$new_status\', sysdate, 1, \'$session\', \'$program\', \'$operator\', \'$remark\')";
    $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    $sth1->finish();
}


sub autoboxname_initially {
    my ($pkg, $dbh, $CLONENAME, $ROOT, $NEXT_VAL) = @_;

    #Check the name given
    my $name_test = test->naming($ROOT);
    unless ($name_test) { 
	die "Name $ROOT cannot be used.\n";
    }


    my $statement = "
insert into autoboxname
(CLONENAME, ROOT, NEXT_VAL)
values (\'$CLONENAME\', \'$ROOT\', \'$NEXT_VAL\')
";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    $sth1->finish();
}


sub autoboxname_entry {
    my ($pkg, $dbh, $NEXT_VAL, $CLONENAME) = @_;
    my $statement = "
update autoboxname
set NEXT_VAL = \'$NEXT_VAL\'
where CLONENAME = \'$CLONENAME\'
    ";

    #warn "$statement\n";

    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    $sth1->finish();
}


package lock;

sub autoboxname_entry {
    my ($pkg, $dbh, $clone) = @_;
    my $statement = "
select CLONENAME, ROOT, NEXT_VAL
from autoboxname
where clonename = \'$clone\'
for update nowait 
";

    #warn "$statement\n";

    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    $sth1->finish;
}

package test;

sub naming {
    my ($pkg, $name) = @_;
#####################################
# RULES;
# Only allow alphanumeric plus 1 '-' 
#####################################
    if ( ($name =~ /^\w*-{0,1}\w*$/) && ($name !~ /^-/) && ($name !~ /^_+/) && ($name ne '') ){
	return 1;
    } else {
	return 0;
    }
}

1;





