# KVA
######################################
# General PCR library (db info)
######################################

use strict;
#use Data::Dumper;

package lookup;

sub set_barcode_printer {
    my($pkg,$dbh,$teamname,$type)=@_;
    
    my $printer_var;
    my $set_printer = "SELECT  BP.DESCRIPTION
FROM    BARCODE_PRINTER BP, DEFAULT_PRINTERS DP, BARCODE_PREFIX BF
WHERE   BF.ID_PREFIX = ?
AND     BF.ID_LABEL  = BP.ACTIVE_LABEL
AND     BP.ISCURRENT = 1
AND     BP.ID_PRINTER = DP.ID_PRINTER
AND     DP.TEAMNAME = ?
AND     ROWNUM = 1";
    my $sth_set_printer=$dbh->prepare($set_printer);
    $sth_set_printer->execute($type,$teamname);
    $printer_var = $sth_set_printer->fetchrow();
    $sth_set_printer->finish();
    
    return ($printer_var);
    
}


sub get_orientation {
    my($pkg,$dbh)=@_;
    
    my ($id_map,$well_nm);
    my %rcmp;
    my $stat_rcmp = "select id_map,maprow||mapcol from rowcol_map order by id_map";
    my $sth_rcmp = $dbh->prepare($stat_rcmp) || die "ERROR:$DBI::errstr";
    $sth_rcmp->execute() || die "ERROR:$DBI::errstr";
    while (($id_map,$well_nm)= $sth_rcmp->fetchrow()){
        $rcmp{$id_map}=$well_nm;
    }
    $sth_rcmp->finish();
    return (\%rcmp);
    
}

sub pcr_type {
    my($pkg,$dbh)=@_; 
    
    my @pcr_type;
    my $ptpe;
    my $stat_pctype = "select description from PCR_LOOKUPTYPEDICT order by id_type";
    my $sth_pcr = $dbh->prepare($stat_pctype) || die "ERROR:$DBI::errstr";
    $sth_pcr->execute || die "ERROR:$DBI::errstr";
    while ($ptpe = $sth_pcr->fetchrow()){
        push @pcr_type,$ptpe;
    }
    $sth_pcr->finish;
    
    return (\@pcr_type);
}

sub project_sql {
    my($pkg,$dbh,$role,$teamname)=@_;
    
    my @projects;
    my $p;
    
    #Get projects for this finisher that have the current status
    #of being assigned to this finisher, for roletype 'Finishing'.
    #and return the associated clonenames that are linked with the 
    #project.
    my $stat1;

    if ($teamname=~ /21|25/){
	$stat1 = "select distinct a.projectname
from project_owner a
where a.teamname in (21,25)
and a.owner_type = 4
and a.iscurrent =1
order by a.projectname";
	
    }
    elsif ($teamname=~ /23|24|44|45|46|47|48|60/){
        $stat1 = "select distinct a.projectname
from project_owner a 
where a.teamname = ?
and a.owner_type = 4
order by a.projectname";
        
    } else {
        
#        $stat1 = "select a.projectname
#from project_role a, project_status b, team_person_role c
#where a.id_role = ?
#and a.projectname = b.projectname
#and b.iscurrent = 1
#and b.status = 18
#and a.id_role = c.id_role
#and c.roletype='Finishing'
#and c.iscurrent = 1
#order by a.projectname";


        $stat1 = "select distinct a.projectname
from project_role a, team_person_role c
where a.id_role = ?
and a.projectname in (select distinct projectname from project_status where status = 18)
and a.id_role = c.id_role
and c.roletype='Finishing'
and c.iscurrent = 1
order by a.projectname";


    }
    
    my $sth_1 = $dbh->prepare($stat1) || die "ERROR:$DBI::errstr";
    if ($teamname=~ /21|25/){
	##nothing needs to be done here
    }
    elsif ($teamname=~ /23|24|44|45|46|47|48|60/){
	$sth_1->bind_param(1, $teamname);
    } else {
	$sth_1->bind_param(1, $role);
    }
    $sth_1->execute || die "ERROR:$DBI::errstr";
    while ($p = $sth_1->fetchrow()){
        push @projects,$p;
    }
    $sth_1->finish;
    
    return (\@projects);
    
}


sub clone_sql {
    my($pkg,$dbh,$role,$teamname)=@_;
    
    my @clones;
    my $c;
    
    #Get projects for this finisher that have the current status
    #of being assigned to this finisher, for roletype 'Finishing'.
    #and return the associated clonenames that are linked with the 
    #project.
    my $stat1;
    
    if ($teamname=~ /21|25/){
	$stat1 = "select distinct b.clonename
from project_owner a, clone_project b 
where a.projectname = b.projectname
and a.teamname in (21,25)
and a.owner_type = 4
and a.iscurrent =1
order by b.clonename";

    }
    elsif ($teamname=~ /23|24|44|45|46|47|48|60/){
        $stat1 = "select distinct b.clonename
from project_owner a, clone_project b 
where a.projectname = b.projectname
and a.teamname = ?
and a.owner_type = 4
order by b.clonename";
        
    } else {
        
#        $stat1 = "select d.clonename
#from project_role a, project_status b, team_person_role c, clone_project d
#where a.id_role = ?
#and a.projectname = b.projectname
#and a.projectname = d.projectname
#and b.iscurrent = 1
#and b.status = 18
#and a.id_role = c.id_role
#and c.roletype='Finishing'
#and c.iscurrent = 1
#order by d.clonename";

        $stat1 = "select distinct d.clonename
from project_role a, team_person_role c, clone_project d
where a.id_role = ?
and a.projectname = d.projectname
and a.projectname in (select distinct projectname from project_status where status = 18)
and a.id_role = c.id_role
and c.roletype='Finishing'
and c.iscurrent = 1
order by d.clonename";
        
    }
    
    my $sth_1 = $dbh->prepare($stat1) || die "ERROR:$DBI::errstr";
    if ($teamname=~ /21|25/){
	##nothing needs to be done here
    }
    elsif ($teamname=~ /23|24|44|45|46|47|48|60/){
	$sth_1->bind_param(1, $teamname);
    } else {
	$sth_1->bind_param(1, $role);
    }
    $sth_1->execute || die "ERROR:$DBI::errstr";
    while ($c = $sth_1->fetchrow()){
        push @clones,$c;
    }
    $sth_1->finish;
    
    return (\@clones);
    
}


sub get_primers { 
    my($pkg,$dbh,$entity,$TYPE)=@_;
    
    my @primers;
    my $pri;
    my $count=0;
    my $stat2;
    my $sth_2;

    #Gets relevant primernames for 
    #a selected project resolved
    #from a entity be it project 
    # or clone.
    
    if ($TYPE=~ /Genomic/){
        
        $stat2 = "select primername
from primer
where projectname = ?
and primername not like 'A%'
order by primername";
        $sth_2 = $dbh->prepare($stat2) || die "ERROR:$DBI::errstr";
        $sth_2->bind_param(1, $entity);
        $sth_2->execute || die "ERROR:$DBI::errstr";
        while ($pri = $sth_2->fetchrow()){
            $count++;
 #           print "PRIMER $count - $pri\n";
            push @primers,$pri;
        }
        
    } else {
        
        $stat2 = "select primername
from primer
where projectname in (select projectname
from clone_project 
where clonename = ?
)
and primername not like 'A%'
order by primername";
        
        $sth_2 = $dbh->prepare($stat2) || die "ERROR:$DBI::errstr";
        $sth_2->bind_param(1, $entity);
        $sth_2->execute || die "ERROR:$DBI::errstr";
        while ($pri = $sth_2->fetchrow()){
            $count++;
 #           print "PRIMER $count - $pri\n";
            push @primers,$pri;
        }
        
    }
    $sth_2->finish;
    my $primernames = \@primers;
    return ($primernames);
    
}


sub get_primerseq {
    my($pkg,$dbh,$entity,$primername,$TYPE)=@_;

    #Retrieve the relevant primer sequence 
    #for the name selected.
    my $priseq;
    my $stat3;
    my $sth_3;
    
    if ($TYPE=~ /Genomic/){
        
        $stat3 = "select primerseq
from primer 
where projectname = ?
and primername = ?";
        $sth_3 = $dbh->prepare($stat3) || die "ERROR:$DBI::errstr";
        $sth_3->bind_param(1, $entity);
        $sth_3->bind_param(2, $primername);
        $sth_3->execute || die "ERROR:$DBI::errstr";
        $priseq = $sth_3->fetchrow();
        
    } else {
        
        $stat3 = "select primerseq
from primer 
where projectname in (select projectname
from clone_project
where clonename = ?
)
and primername = ?
";
        $sth_3 = $dbh->prepare($stat3) || die "ERROR:$DBI::errstr";
        $sth_3->bind_param(1, $entity);
        $sth_3->bind_param(2, $primername);
        $sth_3->execute || die "ERROR:$DBI::errstr";
        $priseq = $sth_3->fetchrow();
    }    
    $sth_3->finish();
    
    return ($priseq);
}

sub check_cloneown {
    my($pkg,$dbh,$clone,$teamname)=@_;
    
    my $stat_cloneown = "select projectname 
from project_owner 
where projectname in (select projectname
from clone_project
where clonename = ?)
and teamname = ? 
and iscurrent = 1
";
    my $sth_cloneown = $dbh->prepare($stat_cloneown) || die "ERROR:$DBI::errstr";
    $sth_cloneown->bind_param(1,$clone);
    $sth_cloneown->bind_param(2,$teamname);
    $sth_cloneown->execute || die "ERROR:$DBI::errstr";
    my $projname = $sth_cloneown->fetchrow();

    $sth_cloneown->finish();

    return ($projname);
    
}


package printinfo;

sub check_pcrmap1 {
    my($pkg,$dbh,$map,$teamname)=@_;
    
    my $stat_chckmap1 = "
select a.id_pcrvessel
from pcr_vessel a
where a.id_pcrvessel = ?
and a.id_pcrvessel in (select a.id_pcrvessel 
from pcr_product a, pcr_batch b
where a.id_pcrbatch = b.id_pcrbatch 
and b.id_team_finserv in (select id_team_finserv
from fin_team_finservice
where teamname = ?)
)
";   
    my $sth_chckmap1 = $dbh->prepare($stat_chckmap1) || die "ERROR:$DBI::errstr";
    $sth_chckmap1->bind_param(1,$map);
    $sth_chckmap1->bind_param(2,$teamname);
    $sth_chckmap1->execute || die "ERROR:$DBI::errstr";
    my $pcr_id1 = $sth_chckmap1->fetchrow();

    $sth_chckmap1->finish();

    return ($pcr_id1);
    
}

sub check_pcrmap2 {
    my($pkg,$dbh,$map)=@_;
    
    my $stat_chckmap2 = "select id_pcrvessel 
from pcr_vessel_status
where status = 2
and iscurrent = 1
and id_pcrvessel = ?
";   
    my $sth_chckmap2 = $dbh->prepare($stat_chckmap2) || die "ERROR:$DBI::errstr";
    $sth_chckmap2->bind_param(1,$map);
    $sth_chckmap2->execute || die "ERROR:$DBI::errstr";
    my $pcr_id2 = $sth_chckmap2->fetchrow();

    $sth_chckmap2->finish();

    return ($pcr_id2);
    
}

sub get_created_pcr {
    my($pkg,$dbh,$teamname)=@_;

    my @pcr_created;
    my $pcr_id;
    my $NA;

###OLD SQL TO BE REMOVED -at some point.
#my $stat_getpcr = "SELECT DISTINCT a.id_pcrvessel,a.id_pcrvessel|| ' created '|| TO_CHAR (e.statusdate, 'DD-MON-YY')|| '  ('|| DECODE (format, 1, 'TUBE', 96, 'PLATE', 384, 'PLATE')|| ')'
#FROM pcr_product a,primer_status b,primer_status c,pcr_vessel d,pcr_vessel_status e
#WHERE (a.primer_1 = b.id_primer OR a.primer_1 IS NULL)
#AND (a.primer_2 = c.id_primer OR a.primer_2 IS NULL)
#AND b.status = 4
#AND b.iscurrent = 1
#AND c.status = 4
#AND c.iscurrent = 1
#AND a.id_pcrvessel = d.id_pcrvessel
#AND d.finisher IN (SELECT id_role
#                   FROM team_person_role
#                   WHERE teamname = ?)
#AND d.id_pcrvessel = e.id_pcrvessel
#AND e.status = 1
#AND e.iscurrent = 1
#ORDER BY a.id_pcrvessel";


    my $stat_getpcr = "SELECT /*+ INDEX(team_person_role) */ distinct a.id_pcrvessel,a.id_pcrvessel||' created '|| TO_CHAR (e.statusdate, 'DD-MON-YY')||' '||g.email||' ('|| DECODE (d.format, 1, 'TUBE', 96, 'PLATE', 384, 'PLATE')|| ')'
FROM pcr_product a,primer_status b,primer_status c,pcr_vessel d,pcr_vessel_status e, team_person_role f, person g
WHERE a.primer_1 = b.id_primer(+) 
AND a.primer_2 = c.id_primer(+)
AND b.status (+) = 4
AND b.iscurrent (+) = 1
AND c.status (+) = 4
AND c.iscurrent (+) = 1
AND a.id_pcrvessel = d.id_pcrvessel
AND d.finisher = f.id_role
AND f.teamname = ?
AND f.roletype = 'Finishing'
AND d.id_pcrvessel = e.id_pcrvessel
AND e.status = 1
AND e.iscurrent = 1
AND f.id_person = g.id_person
ORDER BY a.id_pcrvessel";

    my $sth_getpcr = $dbh->prepare($stat_getpcr) || die "ERROR:$DBI::errstr";
    $sth_getpcr->bind_param(1,$teamname);
    $sth_getpcr->execute || die "ERROR:$DBI::errstr";
    while (($NA,$pcr_id) = $sth_getpcr->fetchrow()){
        push @pcr_created,$pcr_id;
    }

    $sth_getpcr->finish();
    
    return (\@pcr_created);
    
}


sub update_status {
    my($pkg,$dbh,$session_id,$who,$prog,$ids)=@_;
    
    my @pcr_ids = @$ids;
    
    #generate 2 prices of sql one 
    #for update & an insert
    my $stat_up = "update pcr_vessel_status set iscurrent = 0 where id_pcrvessel = ?";
    my $sth_up = $dbh->prepare($stat_up) || die "ERROR:$DBI::errstr";
    
    my $stat_ins = "insert into pcr_vessel_status (ID_PCRVESSEL,STATUS,STATUSDATE,ISCURRENT,SESSIONID,OPERATOR,PROGRAM) values (?,2,sysdate,1,?,?,?)";
    my $sth_ins = $dbh->prepare($stat_ins) || die "ERROR:$DBI::errstr";
    
    foreach (@pcr_ids){
        $sth_up->bind_param(1,$_);
        $sth_up->execute || die "ERROR:$DBI::errstr";
        
        $sth_ins->bind_param(1,$_);
        $sth_ins->bind_param(2,$session_id);
        $sth_ins->bind_param(3,$who);
        $sth_ins->bind_param(4,$prog);
        $sth_ins->execute || die "ERROR:$DBI::errstr";
    }
    
    $sth_up->finish();
    $sth_ins->finish();   
}

 
package mergeinfo;

sub get_pcr_batches {
    my($pkg,$dbh,$id_team_finserv)=@_;
    
    my $entries;
    my($pcr_name,$pcr_type,$pcr_batch,$pcr_sub,$pcr_combi);
    my @all_entries;
    
    my $stat_merge = "select a.pcr_template_name,b.description,a.id_pcrbatch,e.email, a.is_combinatorial 
from pcr_product a, 
pcr_lookuptypedict b, 
pcr_batch c, 
team_person_role d, 
person e
where a.lookup_type = b.id_type 
and a.id_pcrbatch = c.id_pcrbatch 
and c.finisher = d.id_role 
and d.id_person = e.id_person 
and (a.primer_1 in (select id_primer from primer_status where status = 4 and iscurrent = 1) OR a.primer_2 in (select id_primer from primer_status where status = 4 and iscurrent = 1) OR (a.primer_1 is null AND a.primer_2 is null))
and c.id_team_finserv = ?
and a.id_pcrvessel is null
order by b.description, a.pcr_template_name";

    my $sth_merge = $dbh->prepare($stat_merge) || die "ERROR:$DBI::errstr";
    $sth_merge->bind_param(1,$id_team_finserv);
    $sth_merge->execute || die "ERROR:$DBI::errstr";
    while(($pcr_name,$pcr_type,$pcr_batch,$pcr_sub,$pcr_combi)=$sth_merge->fetchrow()){
	my $combi_id;
	if ($pcr_combi){
	    $combi_id='*';
	}
	    
	$entries = sprintf "%-40s %-20s %-6s %-10s %-2s","$pcr_name","$pcr_type","$pcr_batch","$pcr_sub","$combi_id";                                           
        push @all_entries,$entries;
    }
    $sth_merge->finish();
    
    return (\@all_entries);

}


sub group {
    my($pkg,$dbh,$grouplst,$id_role,$plate_size,$session_id,$who,$prog)=@_;
    
    my %HASH1;
    my %HASH2;
    my @group_list = @$grouplst;
    
    my $stat_pcrvessel = "select seq_pcrvessel.nextval from dual";
    my $sth_pcrvessel = $dbh->prepare($stat_pcrvessel) || die "ERROR:$DBI::errstr";
    $sth_pcrvessel->execute || die "ERROR:$DBI::errstr";
    my $id_pcrvessel = $sth_pcrvessel->fetchrow();
    
    my $stat_ins1 = "insert into pcr_vessel (ID_PCRVESSEL,FINISHER,FORMAT) values (?,?,?)";
    my $sth_ins1 = $dbh->prepare($stat_ins1) || die "ERROR:$DBI::errstr";
    $sth_ins1->bind_param(1,$id_pcrvessel);
    $sth_ins1->bind_param(2,$id_role);
    $sth_ins1->bind_param(3,$plate_size);
    $sth_ins1->execute || die "ERROR:$DBI::errstr";
    
    my $stat_ins2 = "insert into pcr_vessel_status (ID_PCRVESSEL,STATUS,STATUSDATE,ISCURRENT,SESSIONID,OPERATOR,PROGRAM) values (?,1,sysdate,1,?,?,?)";
    my $sth_ins2 = $dbh->prepare($stat_ins2) || die "ERROR:$DBI::errstr";
    $sth_ins2->bind_param(1,$id_pcrvessel);
    $sth_ins2->bind_param(2,$session_id);
    $sth_ins2->bind_param(3,$who);
    $sth_ins2->bind_param(4,$prog);
    $sth_ins2->execute || die "ERROR:$DBI::errstr";
    
    my $stat_rcmp = "select id_map 
from rowcol_map 
where maprow = ? 
and mapcol = ?";
    my $sth_rcmp = $dbh->prepare($stat_rcmp) || die "ERROR:$DBI::errstr";
    
    my $stat1 = "select id_pcrproduct 
from pcr_product 
where pcr_template_name = ? 
and id_pcrvessel is null";
    my $sth1 = $dbh->prepare($stat1) || die "ERROR:$DBI::errstr";
    
    my $stat_upd = "update pcr_product 
set id_pcrvessel = ?, id_map = ? 
where id_pcrproduct = ?";
    my $sth_upd = $dbh->prepare($stat_upd) || die "ERROR:$DBI::errstr";
    
    my $count = 0;
    foreach my $gl (@group_list){

        if ($gl ne ''){
            $count++;
            $gl=~ /(\D+\d+)\s+\-\s+(\S+)\s+.+/;
            my $well = $1;
            my $template = $2;
            $well=~ /(\D)(\d+)/;
            my $let = $1;
            my $num = $2;
            $sth_rcmp->bind_param(1,$let);
            $sth_rcmp->bind_param(2,$num);
            $sth_rcmp->execute || die "ERROR:$DBI::errstr";
            my $map_id = $sth_rcmp->fetchrow();
            $HASH1{$template}=$map_id;

            $sth1->bind_param(1,$template);
            $sth1->execute || die "ERROR:$DBI::errstr";
            my $pcrprod = $sth1->fetchrow();
            $HASH2{$template}=$pcrprod;
        }
    }
    
    
    foreach my $keys (keys %HASH2){
        my $id_map = $HASH1{$keys};
        my $pcrprod = $HASH2{$keys};
        $sth_upd->bind_param(1,$id_pcrvessel);
        $sth_upd->bind_param(2,$id_map);
        $sth_upd->bind_param(3,$pcrprod);
        $sth_upd->execute || die "ERROR:$DBI::errstr";
    }    
    
    $sth_pcrvessel->finish();
    $sth_ins1->finish();
    $sth_ins2->finish();
    $sth1->finish();
    $sth_rcmp->finish();
    $sth_upd->finish();
    
    return($id_pcrvessel);
    
}

package newrecords;

sub extra_reply {
    my ($pkg,$dbh,$PLATE_OR_POOL,$plate_size,$FSNAME,$ID_TEAM_FINSERV,$who,$id_role,$teamname,$session_id,$prog,$CONTENTS,$ENTITY,$REMARKS,$TYPE,$SEQ1,$SEQ2,$COSTCODE,$COMBI,$WSOURCE,$WSIZE,$PROBLMS)=@_;
    
    my %WELLCONTENTS=%$CONTENTS;
    my %WELLENTITY=%$ENTITY;
    my %WELLREMARKS=%$REMARKS;
    my %WELLTYPE=%$TYPE;
    my %WELLSEQ1=%$SEQ1;
    my %WELLSEQ2=%$SEQ2;
    my %WELLCOSTCODE=%$COSTCODE;
    my %WELLCOMBI=%$COMBI;
    my %WELLSOURCE=%$WSOURCE;
    my %WELLSIZE=%$WSIZE;
    my %PROBLEMS=%$PROBLMS;
    %PROBLEMS=();

    $prog = $prog.'->EXT';

#    print "my state is :- $PLATE_OR_POOL\n";
#   
#    print "CHECKING COMMIT BUTTON \n";
#    print "Here is the output from data entered\n";
#    print "Vessel type :- $plate_size\n";
#    print "finservice :- $FSNAME & id :-$ID_TEAM_FINSERV\n";
#    print "Finisher in question : - $who & role is $id_role\n";
#    print "Teamname :- $teamname\n"; 
#    
#    print "In the extra reply subroutine ----------->\n";
#    print Data::Dumper::Dumper(\%WELLCONTENTS);
#    print "----------------------------------------->\n";
#    
#    print "WELL ENTITY INFORMATION ----------->\n";
#    print Data::Dumper::Dumper(\%WELLENTITY);
#    print "----------------------------------------->\n";
    

    my $count = scalar (keys %WELLCONTENTS);
    print "count entries :- $count\n";
    print "\n";
    
    my $stat1 = "select seq_pcrvessel.nextval from dual";
    my $sth_1 = $dbh->prepare($stat1) || die "ERROR:$DBI::errstr";

    my $stat1a = "select seq_pcrbatch.nextval from dual";
    my $sth_1a = $dbh->prepare($stat1a) || die "ERROR:$DBI::errstr";

    my $stat2 = "select id_map from rowcol_map where maprow = ? and mapcol = ? and format = ?";
    my $sth_2 = $dbh->prepare($stat2) || die "ERROR:$DBI::errstr";
    
    my $stat3 = "select seq_pcrproduct.nextval from dual";
    my $sth_3 = $dbh->prepare($stat3) || die "ERROR:$DBI::errstr";
    
    my $stat4 = "select id_type from pcr_lookuptypedict where description = ?";
    my $sth_4 = $dbh->prepare($stat4) || die "ERROR:$DBI::errstr";
    
    my $stat5 = "select id_primer from primer where projectname in (select projectname from clone_project where clonename = ? ) and primername = ?";
    my $sth_5 = $dbh->prepare($stat5) || die "ERROR:$DBI::errstr";

    my $stat_5_1 = "select id_primer from primer where projectname = ? and primername = ?";
    my $sth_5_1 = $dbh->prepare($stat_5_1) || die "ERROR:$DBI::errstr";

    my $stat6 = "select seq_id_primer.nextval from dual";
    my $sth_6 = $dbh->prepare($stat6) || die "ERROR:$DBI::errstr";
    
    my $get_proj = "select projectname from clone_project where clonename = ?";
    my $sth_gproj = $dbh->prepare($get_proj) || die "ERROR:$DBI::errstr";

    my $stat7 = "insert into primer (id_primer,primername,projectname,primerseq,id_costcode) values (?,?,?,?,?)";
    my $sth_7 = $dbh->prepare($stat7) || die "ERROR:$DBI::errstr";
        
    my $stat_ps = "insert into primer_status (id_primer,status,statusdate,iscurrent,sessionid,program,operator) values (?,1,sysdate,1,?,?,?)";
    my $sth_ps = $dbh->prepare($stat_ps) || die "ERROR:$DBI::errstr";
    
    my $temp_check = "select count(pcr_template_name) from pcr_product where pcr_template_name like ?";
    my $sth_tmpchk = $dbh->prepare($temp_check) || die "ERROR:$DBI::errstr";

    my $stat_pcrbtch = "insert into pcr_batch (id_pcrbatch,batchdate,finisher,id_team_finserv) values (?,sysdate,?,?)";
    my $sth_pcrbtch = $dbh->prepare($stat_pcrbtch) || die "ERROR:$DBI::errstr";
 
    my $stat_getbbmap = "select bm.id_bbmap from beckman_box bb, bb_map bm, rowcol_map rm where bb.id_beckmanbox = bm.id_beckmanbox and bm.id_map = rm.id_map and bb.name = ? and rm.maprow = ? and rm.mapcol = ?";
    my $sth_getbbmap = $dbh->prepare($stat_getbbmap) || die "ERROR:$DBI::errstr";
 
    my $stat8 = "insert into pcr_product (id_pcrproduct,id_pcrbatch,primer_1,primer_2,lookup_type,pcr_template_name,is_combinatorial,dna_source,product_size_bp) values (?,?,?,?,?,?,?,?,?)";
    my $sth_8 = $dbh->prepare($stat8) || die "ERROR:$DBI::errstr";

    my $stat_8_1 = "insert into pcr_product (id_pcrproduct,id_pcrbatch,primer_1,primer_2,lookup_type,pcr_template_name,id_pcrvessel,id_map,is_combinatorial,dna_source,product_size_bp) values (?,?,?,?,?,?,?,?,?,?,?)";
    my $sth_8_1 = $dbh->prepare($stat_8_1) || die "ERROR:$DBI::errstr";

    my $stat_pvwins = "insert into pcrproduct_status (id_pcrproduct,status,statusdate,iscurrent,sessionid,operator,program,remark) values (?,1,sysdate,1,?,?,?,?)";
    my $sth_pvwins = $dbh->prepare($stat_pvwins) || die "ERROR:$DBI::errstr";

    my $stat9 = "insert into pcr_vessel (id_pcrvessel,finisher,format) values (?,?,?)";
    my $sth_9 = $dbh->prepare($stat9) || die "ERROR:$DBI::errstr";
    
    my $stat10 = "insert into pcr_vessel_status (id_pcrvessel,status,statusdate,iscurrent,sessionid,operator,program) values (?,1,sysdate,1,?,?,?)";
    my $sth_10 = $dbh->prepare($stat10) || die "ERROR:$DBI::errstr";

    my $stat11 = "select distinct speciesname from clone where clonename = ?";
    my $sth_11 = $dbh->prepare($stat11) || die "ERROR:$DBI::errstr";
    
    my $stat_11_1 = "select distinct speciesname from clone where clonename in (select clonename from clone_project where projectname = ?)";
    my $sth_11_1 = $dbh->prepare($stat_11_1) || die "ERROR:$DBI::errstr";

    my $probs;
    
    foreach my $key (keys %WELLCONTENTS){
  
        #get the project or clone name.      
	my $check_ent = $WELLENTITY{$key};
        if ($check_ent eq ''){   
	    $probs = "Problem no project or clonename found with entry $key;";
	    push (@{$PROBLEMS{$key}}, $probs);
	}        
        
        #check the remarks field for illegal characters.
	if ($WELLREMARKS{$key}=~ /\"|\'|\\/g){
	    $probs = "Remark field contains unauthorized characters;";
	    push (@{$PROBLEMS{$key}}, $probs);
	}

        #check that the pcrtype was entered.
	if (($WELLTYPE{$key}=~ /Select the pcr type/) || (! defined $WELLTYPE{$key})){
	    $probs = "PCR type not entered;";
	    push (@{$PROBLEMS{$key}}, $probs);
	}

        #check that the sequences have no illegal characters.
	if ($WELLSEQ1{$key}=~ /(\S+)\_(\S+)/){
	    my $checkname1 = $1;
	    my $checkseq1 = $2;
	    $checkseq1=~ s/\s+//g;
	    $checkseq1 = uc($checkseq1);

	    if ($checkseq1=~ /[^ATGCU]/g){
		$probs = "Problem identified in primer 1 sequence info;";
		push (@{$PROBLEMS{$key}}, $probs);
	    }
	    
	    $sth_5->bind_param(1,$check_ent);
	    $sth_5->bind_param(2,$checkname1);
	    $sth_5->execute || die "ERROR:$DBI::errstr";
	    my $check_idp1 = $sth_5->fetchrow();
	    
            if (! defined $check_idp1){
                $sth_5_1->bind_param(1,$check_ent);
                $sth_5_1->bind_param(2,$checkname1);
                $sth_5_1->execute || die "ERROR:$DBI::errstr";
                $check_idp1 = $sth_5_1->fetchrow();

                if (! defined $check_idp1){
                    if (($WELLCOSTCODE{$key} eq "") || ($WELLCOSTCODE{$key}=~ /Select cost-code/)){
                        $probs = "Costcode not defined for these new primers;";
                        push (@{$PROBLEMS{$key}}, $probs);
                    }   
                }
                
            }
	}
        
	if ($WELLSEQ2{$key}=~ /(\S+)\_(\S+)/){
	    my $checkname2 = $1;
	    my $checkseq2 = $2;
	    $checkseq2=~ s/\s+//g;
	    $checkseq2 = uc($checkseq2);

	    if ($checkseq2=~ /[^ATGCU]/g){
		$probs = "Problem identified in primer 2 sequence info;";
		push (@{$PROBLEMS{$key}}, $probs);
	    }
            
	    $sth_5->bind_param(1,$check_ent);
	    $sth_5->bind_param(2,$checkname2);
	    $sth_5->execute || die "ERROR:$DBI::errstr";
	    my $check_idp2 = $sth_5->fetchrow();

	    if (! defined $check_idp2){
                $sth_5_1->bind_param(1,$check_ent);
                $sth_5_1->bind_param(2,$checkname2);
                $sth_5_1->execute || die "ERROR:$DBI::errstr";
                $check_idp2 = $sth_5_1->fetchrow();

                if (! defined $check_idp2){
                    if (($WELLCOSTCODE{$key} eq "") || ($WELLCOSTCODE{$key}=~ /Select cost-code/)){
                        $probs = "Costcode not defined for these new primers;";
                        push (@{$PROBLEMS{$key}}, $probs);
                    }   
                }
                
            }
	}
        
        #ensure that the dna source for each pcr product has been filled in.
	if (! defined $WELLSOURCE{$key}){
            $probs = "DNA source was not defined for this pcr product;";
            push (@{$PROBLEMS{$key}}, $probs);
        }

        #check that the source info is valid.
        if ($WELLTYPE{$key}=~ /Subclone.*/){
            if ($WELLSOURCE{$key}){
                #split up the dna source into 2 bits and 
                #run it against the following query.
                #3 sections to check with bind variables.
                my $src = $WELLSOURCE{$key};
                $src =~ /^(\S+\-\d+)(\D\d+)$/;
                my $name = $1;
                my $well = $2;
                $well=~ /^(\D)(\d+)$/;
                my $let=$1;
                my $num=$2;
                $sth_getbbmap->bind_param(1,$name);
                $sth_getbbmap->bind_param(2,$let);
                $sth_getbbmap->bind_param(3,$num);
                $sth_getbbmap->execute || die "ERROR:$DBI::errstr";
                my $bb_src =$sth_getbbmap->fetchrow();
                if (!$bb_src){
                    $probs = "DNA source provided was not found;";
                    push (@{$PROBLEMS{$key}}, $probs); 
                }
            }
        }
        
    }   
    
    my $prob_count = scalar (keys %PROBLEMS);

    #Any problems found return to be displayed.
    if ($prob_count > 0){
#        print "PROBLEMS FOUND\n";
	return('',\%PROBLEMS,'','','','');
	
        
    } else {
	
	my %UNIQUE_PRIMERS;
	my %UNORDERED_PRIMERS;
	my %NEW_ENTRIES;
	my %PROJ_SPEC;
        my %COSTCODE_PROJ;
        my %NEW_IDS;
        my %PRIMER_IDS;

	my $pcrplate;
        my $pcrbatch;
        my $count = 0;
        
        unless ($PLATE_OR_POOL=~ /pool/) {
            
            #Get a pcrplate (vessel) ID.
            if ($plate_size=~ /96|384/){   
                $sth_1->execute || die "ERROR:$DBI::errstr";
                $pcrplate = $sth_1->fetchrow();
                $count++;
                my $ordr = "PLATE : PCR VESSEL ID :- $pcrplate";
                $NEW_IDS{$count} =$ordr;
            }
            
            #If it is a 96/384 well plate only need to enter this
            #information once into the db for each plate.
            if ($plate_size=~ /96|384/){
                $sth_9->bind_param(1,$pcrplate);
                $sth_9->bind_param(2,$id_role);
                $sth_9->bind_param(3,$plate_size);
                $sth_9->execute || die "ERROR:$DBI::errstr";
            }
            
        }

        #Get the pcrbatch ID for this submission.
        if ($plate_size=~ /96|384/){
            $sth_1a->execute || die "ERROR:$DBI::errstr";
            $pcrbatch= $sth_1a->fetchrow();
            
            #Make a pcrbatch entry for the plate of samples if 
            #the samples are submitted on a 96 or 384 well plate.
            $sth_pcrbtch->bind_param(1,$pcrbatch);
            $sth_pcrbtch->bind_param(2,$id_role);
            $sth_pcrbtch->bind_param(3,$ID_TEAM_FINSERV);
            $sth_pcrbtch->execute || die "ERROR:$DBI::errstr";
            
        }
        

        #Do the do for each well of information to be submitted.
        foreach my $keys (sort {sensibly($a, $b)} keys %WELLCONTENTS) {

            unless ($PLATE_OR_POOL=~ /pool/) {     
                
                #If it is a tube need to enter it for each tube.
                if ($plate_size == 12){
                    $sth_1->execute || die "ERROR:$DBI::errstr";
                    $pcrplate = $sth_1->fetchrow();
                    $count++;
                    my $ordr = "TUBE $count : PCR VESSEL ID :- $pcrplate";
                    $NEW_IDS{$count} =$ordr;
                }  
                
                if ($plate_size == 12){
                    my $format = 1;
                    $sth_9->bind_param(1,$pcrplate);
                    $sth_9->bind_param(2,$id_role);
                    $sth_9->bind_param(3,$format);
                    $sth_9->execute || die "ERROR:$DBI::errstr";
                }
            }

            #Get the pcrbatch ID for this submission.
            if ($plate_size == 12){
                $sth_1a->execute || die "ERROR:$DBI::errstr";
                $pcrbatch= $sth_1a->fetchrow();
                
                #Make a pcrbatch entry each sample if the
                #samples are submitted as single samples.
                $sth_pcrbtch->bind_param(1,$pcrbatch);
                $sth_pcrbtch->bind_param(2,$id_role);
                $sth_pcrbtch->bind_param(3,$ID_TEAM_FINSERV);
                $sth_pcrbtch->execute || die "ERROR:$DBI::errstr";    
            }
            
            my $contents;
            my $entity;
            my $projectname;
            my $map_id;
            
            $keys =~ /(\D+)(\d+)/;
            my $let = $1;
            my $num = $2;
            
	    my $species;
            
            #If it is a plate, get the relevant
            #mapid number.
            unless ($PLATE_OR_POOL=~ /pool/) {
                if ($plate_size=~ /96|384/){
                    $sth_2->bind_param(1,$let);
                    $sth_2->bind_param(2,$num);
                    $sth_2->bind_param(3,$plate_size);
                    $sth_2->execute || die "ERROR:$DBI::errstr";
                    $map_id = $sth_2->fetchrow();
                }
                
                #else tubes should have the map id
                #field left as null.
                if ($plate_size == 12){
                    $map_id = "";
                }
            }
            
            #get the pcr_product id.
            $sth_3->execute || die "ERROR:$DBI::errstr";
            my $pcrproduct = $sth_3->fetchrow();
            
            if ($WELLENTITY{$keys}){
                $entity = $WELLENTITY{$keys};
                $contents = $WELLCONTENTS{$keys};
#                print ">>NAME: - $entity<<\n";
                
                $sth_11->bind_param(1,$entity);
                $sth_11->execute || die "ERROR:$DBI::errstr";
                $species = $sth_11->fetchrow();

                if (! defined $species){
		    $sth_11_1->bind_param(1,$entity);
		    $sth_11_1->execute || die "ERROR:$DBI::errstr";
		    $species = $sth_11_1->fetchrow(); 
		    #print "In the species section - species is $species\n";
		}
		
                #Then get the relevant projectname associated
		#with the clonename used.
		#In the case of the genomic pcr, projectname
		#is already in use, so no database query is needed.
                if ($WELLTYPE{$keys} eq 'Genomic'){
                    $projectname=$entity;
                } else {   
                    $sth_gproj->bind_param(1,$entity);
                    $sth_gproj->execute || die "ERROR:$DBI::errstr";
                    $projectname=$sth_gproj->fetchrow();
                }

	    } 
            
            #get any remarks documented if any and check
            #for unauthorized characters.
            my $remarks = $WELLREMARKS{$keys};
            if (! $remarks){
                $remarks = "";
            }
            
            #get the id of the pcrproduct type.
            my $type;
            my $id_type;
            if ($WELLTYPE{$keys} ne ""){
                $type = $WELLTYPE{$keys};
                $sth_4->bind_param(1,$type);
                $sth_4->execute || die "ERROR:$DBI::errstr";
                $id_type = $sth_4->fetchrow();    
            } 
            
            my $pname1;
            my $pseq1;
            my $pname2;
            my $pseq2;
            my $id_primer1;
            my $id_primer2;
            my $new_prid1;
            my $new_prid2;
            
            #get the well sequence information.
            if ($WELLSEQ1{$keys}=~ /(\S+)\_(\S+)/){
                $pname1 = $1;
                $pseq1 = $2;
                $pseq1=~ s/\s+//g;
                $pseq1 = uc($pseq1);
                
                $sth_5->bind_param(1,$entity);
                $sth_5->bind_param(2,$pname1);
                $sth_5->execute || die "ERROR:$DBI::errstr";
                $id_primer1 = $sth_5->fetchrow();
                
                if (! defined $id_primer1){
                    $sth_5_1->bind_param(1,$entity);
                    $sth_5_1->bind_param(2,$pname1);
                    $sth_5_1->execute || die "ERROR:$DBI::errstr";
                    $id_primer1 = $sth_5_1->fetchrow();
		    
		    if (!$id_primer1){
			$id_primer1 = $NEW_ENTRIES{$pname1};
			unless ($UNIQUE_PRIMERS{$pname1}){
			    $sth_6->execute || die "ERROR:$DBI::errstr";
			    $new_prid1= $sth_6->fetchrow();
			    
			    #If it is a new primer, get the relevant
			    #costcode assigned to it.
			    my $indiv_costcode = $WELLCOSTCODE{$keys};
#			    print "The individual costcode for well $keys : - $indiv_costcode\n";
			    
			    ##insert into primer table
			    $sth_7->bind_param(1,$new_prid1);
			    $sth_7->bind_param(2,$pname1);
			    $sth_7->bind_param(3,$projectname);
			    $sth_7->bind_param(4,$pseq1);
			    $sth_7->bind_param(5,$indiv_costcode);
			    $sth_7->execute || die "ERROR:$DBI::errstr";
			    
			    ##insert into primer status table
			    $sth_ps->bind_param(1,$new_prid1);
			    $sth_ps->bind_param(2,$session_id);
			    $sth_ps->bind_param(3,$prog);
			    $sth_ps->bind_param(4,$who);
			    $sth_ps->execute || die "ERROR:$DBI::errstr";
			    
			    $NEW_ENTRIES{$pname1} = $new_prid1;
			    $id_primer1 = $new_prid1;
			    my $uniq_pn = $projectname.".".$pname1;
			    $UNORDERED_PRIMERS{$uniq_pn} = $pseq1;
			    $PRIMER_IDS{$uniq_pn} = $new_prid1;

			    #Hold project information where
			    #primers are yet to be ordered.
			    unless ($PROJ_SPEC{$projectname}){
			        $PROJ_SPEC{$projectname} = $species;
			    }

			    my $spec_cc = $WELLCOSTCODE{$keys};
			    unless ($COSTCODE_PROJ{$uniq_pn}){
				$COSTCODE_PROJ{$uniq_pn} = $spec_cc;
			    }
			    
			}
			$UNIQUE_PRIMERS{$pname1} = $pseq1;
		    } 
                }
                
            } 
            
            if ($WELLSEQ2{$keys}=~ /(\S+)\_(\S+)/){
                $pname2 = $1;
                $pseq2 = $2;
                $pseq2=~ s/\s+//g;
                $pseq2 = uc($pseq2);
                
                $sth_5->bind_param(1,$entity);
                $sth_5->bind_param(2,$pname2);
                $sth_5->execute || die "ERROR:$DBI::errstr";
                $id_primer2 = $sth_5->fetchrow();
                
                if (! defined $id_primer2){
                    $sth_5_1->bind_param(1,$entity);
                    $sth_5_1->bind_param(2,$pname2);
                    $sth_5_1->execute || die "ERROR:$DBI::errstr";
                    $id_primer2 = $sth_5_1->fetchrow();
		    
		    if (!$id_primer2){
			$id_primer2 = $NEW_ENTRIES{$pname2};
			unless($UNIQUE_PRIMERS{$pname2}){
			    $sth_6->execute || die "ERROR:$DBI::errstr";
			    $new_prid2= $sth_6->fetchrow(); 
			    
			    #If it is a new primer, get the relevant
			    #costcode assigned to it.
			    my $indiv_costcode = $WELLCOSTCODE{$keys};
#			    print "The individual costcode for well $keys : - $indiv_costcode\n";
			    
			    ##insert into primer table
			    $sth_7->bind_param(1,$new_prid2);
			    $sth_7->bind_param(2,$pname2);
			    $sth_7->bind_param(3,$projectname);
			    $sth_7->bind_param(4,$pseq2);
			    $sth_7->bind_param(5,$indiv_costcode);
			    $sth_7->execute || die "ERROR:$DBI::errstr";
			    
			    ##insert into primer status table
			    $sth_ps->bind_param(1,$new_prid2);
			    $sth_ps->bind_param(2,$session_id);
			    $sth_ps->bind_param(3,$prog);
			    $sth_ps->bind_param(4,$who);
			    $sth_ps->execute || die "ERROR:$DBI::errstr";
			    
			    $NEW_ENTRIES{$pname2} = $new_prid2;
			    $id_primer2 = $new_prid2;
			    my $uniq_pn = $projectname.".".$pname2;
			    $UNORDERED_PRIMERS{$uniq_pn} = $pseq2;
                            $PRIMER_IDS{$uniq_pn} = $new_prid2; #obtain the primer ID for primer2.

			    #Hold project information where
			    #primers are yet to be ordered.
			    unless ($PROJ_SPEC{$projectname}){
			        $PROJ_SPEC{$projectname} = $species;
			    }

			    my $spec_cc = $WELLCOSTCODE{$keys};
			    unless ($COSTCODE_PROJ{$uniq_pn}){
				$COSTCODE_PROJ{$uniq_pn} = $spec_cc;
			    }
			}
			$UNIQUE_PRIMERS{$pname2} = $pseq2;
		    }  
                }
            } 
            
            #Get the dna source information for this product.
            my $dna_src;
            $dna_src = $WELLSOURCE{$keys};
	    
            #Get the pcr size information for this product if any.
            my $pcr_size;
            $pcr_size = $WELLSIZE{$keys};
            $pcr_size =~ s/\s+//g;
            unless (defined $pcr_size){
                $pcr_size = "";
            }

            #insert the the entry for the individual
            #pcr products.
            my $which_combi = $WELLCOMBI{$keys};
            if ($which_combi=~ /Yes/){
                $which_combi='1';
            } else {
                $which_combi='';
            } 
	    
            #check that the pcr_template isn't already in
            #the database.
            my $newcontents = $contents.'-'.'%';
            $sth_tmpchk->bind_param(1,$newcontents);
            $sth_tmpchk->execute || die "ERROR:$DBI::errstr";
            my $tmp_count =$sth_tmpchk->fetchrow();
            my $inc_contents;
            if ($tmp_count){
#                print "The number of template contents so far is :- $tmp_count\n";
                my $inc_count = $tmp_count+1;
#                print "The next entry needs to be incremented to $inc_count\n";
                $inc_contents = $contents.'-'.$inc_count;
#                print "The new inc_contents = $inc_contents\n";
            } else {
                $inc_contents = $contents.'-'.'1';
#                print "No inc_contents it is the first= $inc_contents\n";
            }
	    
            if ($WELLTYPE{$keys}=~ /Subclone.*/){
                #split up the dna source into 2 bits and 
                #run it against the following query.
                #3 sections to check with bind variables.
                $dna_src;
                $sth_getbbmap;
                $dna_src =~ /^(\S+\-\d+)(\D\d+)$/;
                my $name = $1;
                my $well = $2;
#                print "1st piece :- $name, 2nd piece :- $well\n";
                $well=~ /^(\D)(\d+)$/;
                my $let=$1;
                my $num=$2;
                $sth_getbbmap->bind_param(1,$name);
                $sth_getbbmap->bind_param(2,$let);
                $sth_getbbmap->bind_param(3,$num);
                $sth_getbbmap->execute || die "ERROR:$DBI::errstr";
                $dna_src =$sth_getbbmap->fetchrow();
#                print "DNA SOURCE FOR SUBCLONE :- BBMAP ID = $dna_src.\n";
		
            }
            
            if ($PLATE_OR_POOL=~ /pool/) {
                if ($WELLTYPE{$keys}=~ /Subclone\_.+/){
                    $id_primer1='';
                    $id_primer2='';
                }
                $sth_8->bind_param(1,$pcrproduct);
                $sth_8->bind_param(2,$pcrbatch);
                $sth_8->bind_param(3,$id_primer1);
                $sth_8->bind_param(4,$id_primer2);
                $sth_8->bind_param(5,$id_type);
                $sth_8->bind_param(6,$inc_contents);
                $sth_8->bind_param(7,$which_combi);
                $sth_8->bind_param(8,$dna_src);
                $sth_8->bind_param(9,$pcr_size);
                $sth_8->execute || die "ERROR:$DBI::errstr";
                
            } else { 
                if ($WELLTYPE{$keys}=~ /Subclone\_.+/){
                    $id_primer1='';
                    $id_primer2='';
                }
                $sth_8_1->bind_param(1,$pcrproduct);
                $sth_8_1->bind_param(2,$pcrbatch);
                $sth_8_1->bind_param(3,$id_primer1);
                $sth_8_1->bind_param(4,$id_primer2);
                $sth_8_1->bind_param(5,$id_type);
                $sth_8_1->bind_param(6,$inc_contents);
                $sth_8_1->bind_param(7,$pcrplate);
                $sth_8_1->bind_param(8,$map_id);
                $sth_8_1->bind_param(9,$which_combi);
                $sth_8_1->bind_param(10,$dna_src);
                $sth_8_1->bind_param(11,$pcr_size);
                $sth_8_1->execute || die "ERROR:$DBI::errstr";
            }        
            
            $sth_pvwins->bind_param(1,$pcrproduct);
            $sth_pvwins->bind_param(2,$session_id);
            $sth_pvwins->bind_param(3,$who);
            $sth_pvwins->bind_param(4,$prog);
            $sth_pvwins->bind_param(5,$remarks);
            $sth_pvwins->execute || die "ERROR:$DBI::errstr";
            
            unless ($PLATE_OR_POOL=~ /pool/) {
                #insert the information into the pcr_vessel
                #status table.
                if ($plate_size == 12){
                    $sth_10->bind_param(1,$pcrplate);
                    $sth_10->bind_param(2,$session_id);
                    $sth_10->bind_param(3,$who);
                    $sth_10->bind_param(4,$prog);
                    $sth_10->execute || die "ERROR:$DBI::errstr";
                }
            }
            
        }
        
        unless ($PLATE_OR_POOL=~ /pool/) {       
            if ($plate_size=~ /96|384/){
                
                $sth_10->bind_param(1,$pcrplate);
                $sth_10->bind_param(2,$session_id);
                $sth_10->bind_param(3,$who);
                $sth_10->bind_param(4,$prog);
                $sth_10->execute || die "ERROR:$DBI::errstr";
            } 
        }
	
#finish with statement handles.
        $sth_1->finish();
        $sth_2->finish();
        $sth_3->finish();
        $sth_4->finish();
        $sth_5->finish();
        $sth_5_1->finish();
        $sth_6->finish();
	$sth_gproj->finish();
        $sth_ps->finish();
        $sth_7->finish();
        $sth_tmpchk->finish();
        $sth_8->finish();
        $sth_8_1->finish();
        $sth_pvwins->finish();
        $sth_9->finish();
        $sth_10->finish();
        $sth_11->finish();	    
	$sth_11_1->finish();


        return($pcrbatch,\%PROBLEMS,\%UNORDERED_PRIMERS,\%PROJ_SPEC,\%COSTCODE_PROJ,\%NEW_IDS,\%PRIMER_IDS);
    }
    
    
}

sub sensibly {
    my ($aa, $bb) = @_;
    my ($a_lhs, $b_lhs);
    
    return $aa cmp $bb if $aa eq "" or $bb eq "";
    $aa =~ s/^(\D+|\d+)//;
    $a_lhs = lc($1);
    
    $bb =~ s/^(\D+|\d+)//;
    $b_lhs = lc($1);
    
    if ($a_lhs =~ /^[0-9]/ and $b_lhs =~ /^[0-9]/) {
        if ($a_lhs == $b_lhs) {
            return sensibly($aa, $bb);
        } else {
            return $a_lhs <=> $b_lhs;
        }
    } else {
        if ($a_lhs eq $b_lhs) {
            return sensibly($aa, $bb);
        } else {
            return $a_lhs cmp $b_lhs;
        }
    }
}



1;

__END__
