
##
## Primer test
##

use strict;
use lib ("/usr/local/badger/bin");
use lib ("/nfs/humdata3/prima/bin/mod/");
use trackdb;
use snpdb;

package sts_test;

$ENV{'TWO_TASK'} = 'snp';
$ENV{'ORACLE_HOME'} = '/usr/local/oracle';	# Oracle home


sub check_ord{


    my $dbh;
    my $dbi_user = "snpreport";
    my %rec;
    my $ord;
    my $plate;

    
    eval {
	
	$dbh = WrapDBI->connect($dbi_user, {RaiseError => 1});
	$dbh->{autocommit} = 0;
	my $statement = "select sn.sts_name, oo.id_costcode, oo.id_order
                         from  sts_name  sn,
                         stsorder_status st,
                         oligo_order oo,
                         oligo_primer op
                         where oo.id_order = st.id_order(+)
                         and st.status is NULL
                         and op.id_oligoprimer = oo.id_oligoprimer
                         and  op.id_sts = sn.id_sts
                         and sn.sts_name_type = '1'";
	
	my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
	$sth->execute || die "ERROR:$DBI::errstr";
	my ($a, $b, $c);
	
	while ( ($a, $b, $c) = $sth->fetchrow() ){
	    if($c){
		print "$a\t$b\t$c\n";
	    }
	}
    };
    if ($@) {  
	
	if ($DBI::err) {
	    print "DB ERROR: $DBI::errstr\n";
	} 
	else {
	    print "$@";
	}
    }
    if (defined $dbh) {
	$dbh->disconnect;
    }
}


sub get_id_order{
    
    
    my ($dbh, $input) = @_;
    
    my $plate;
    
    
    eval {
	
	warn "get .$input. \n";
	$plate = $dbh->fetch_scalar(qq[select oo.id_order
				       from oligo_order oo,
				       stsorder_status st
				       where oo.oligoname = '$input'
				       and oo.id_order = st.id_order(+)
				       and st.status is NULL
				       ]);
	
	
    };

    warn "here : $plate,\n";
    if ($@) {  
	
	warn "ERROR :$@";
	
    }
    
    return( $plate->[0]);
    
}

sub get_id_order_fromprimer{
    
    
    my ($input, $type) = @_;
    my $dbi_user = "snp2";
    my $dbh;
    
    my %rec;
    my $ord;
    my $plate;
    
    eval {
	
	$dbh = WrapDBI->connect($dbi_user, {RaiseError => 1});
	$dbh->{autocommit} = 0;
	
	$plate = $dbh->fetch_scalar(qq[select oo.id_order
				       from
				       oligo_order oo,
				       stsorder_status st
				       where oo.oligoname ='$input'
				       and oo.id_order = st.id_order(+)
				       and st.status is NULL
				       ]);
	
    };
    if ($@) {  
	if (defined $dbh) {
	    $dbh->disconnect};
	
	
	if ($DBI::err) {
	    print "DB ERROR: $DBI::errstr\n";
	} 
	else {
	    print "$@";
	}
    }
    else{
	$dbh->disconnect;
    }
    return( $plate);
    
}


sub get_oligo_plate{

    
    my ($input) = @_;
    my $dbi_user = "snpreport";
    my $dbh;
    
    my %rec;
    my $ord;
    
    open  MESSAGE,"| Mail  seh " ; 
    print MESSAGE  "Old version of sts_test::get_oligo_plate in use"; ;
    close MESSAGE ;
   
    
    eval {
	
	$dbh = WrapDBI->connect($dbi_user, {RaiseError => 1});
	$dbh->{autocommit} = 0;
	my $statement;
	my $plate= $dbh->fetch_scalar(qq[select opw.id_oligoplate
					 from oligo_primer op,
					 oligo_order oo,
					 order_well ow,
					 oligoplate_well opw
					 where op.oligoname='$input'
					 and oo.id_oligoprimer = op.id_oligoprimer  
					 and ow.id_oo = oo.id_oo 
					 and opw.id_well =ow.id_well  
					 ]);
	
	if($plate){
	    $statement = "select opw.id_map, op.oligoname, oo.id_order
from oligo_primer op,
oligo_order oo,
order_well ow,
oligoplate_well opw
where opw.id_oligoplate = $plate
and ow.id_well =  opw.id_well
and oo.id_oo = ow.id_oo 
and op.id_oligoprimer = oo.id_oligoprimer";
	    
	    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
	    $sth->execute || die "ERROR:$DBI::errstr";
	    my $res = $sth->fetchall_arrayref();

	    foreach my $l(@$res){
		$l->[1] =~ s/^stSG|^stC//g;
		$rec{$l->[0]}{"name"} = $l->[1];
		$rec{"ord"} = $l->[2];   #### check for repeated orders???
	    }
	    
	    if($rec{"ord"}){
		my $statement2 = qq[select s.oligo_service, u.session_date
				    from sts_order s,
				    user_session u,
				    stsorder_status so
				    where s.id_order = ?
				    and so.id_order = s.id_order
				    and so.id_session = u.id_session];
		
		
		my $sth = $dbh->prepare($statement2) || die "ERROR:$DBI::errstr";
		$sth->execute($rec{"ord"}) || die "ERROR:$DBI::errstr";
		my @a = $sth->fetchrow();
		$rec{"service"} = $a[0];
		$rec{"date"}    = $a[1];
		$rec{"plate"}   = $plate;
				
	    }
	}
	else{ warn "No plate found for $input\n";}
    };
    if ($@) {  
	if (defined $dbh) {
	    $dbh->disconnect};
	
	if ($DBI::err) {
	    print "DB ERROR: $DBI::errstr\n";
	} 
	else {
	    print "$@";
	}
    }
    if (defined $dbh) {
	$dbh->disconnect;
    }
    return(\%rec);
}

sub get_oligo_plate_new{

    
    my ($input,$type) = @_;
    my $dbi_user = "snpreport";
    my $dbh;
    
    my %rec;
    my $ord;

    $dbh->{autocommit} = 0;
    
    eval {
	
	$dbh = WrapDBI->connect($dbi_user, {RaiseError => 1});
	my $statement;
	my $plate;
	if($type eq "sts"){
	    
	    $plate= $dbh->fetch_scalar(qq[select opw.id_oligoplate
					  from 
					  oligo_order oo,
					  order_well ow,
					  oligoplate_well opw
					  where oo.oligoname='$input'
					  and ow.id_oo = oo.id_oo 
					  and opw.id_well =ow.id_well  
					  ]);
	}
	else{
	    $plate = $input;
	}
	if($plate){
	    $statement = (qq[select opw.id_map, oo.oligoname, oo.id_order
			     from 
			     oligo_order oo,
			     order_well ow,
			     oligoplate_well opw
			     where opw.id_oligoplate = ?
			     and ow.id_well =  opw.id_well
			     and oo.id_oo = ow.id_oo 
			     ]);
	    
	    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
	    $sth->execute($plate) || die "ERROR:$DBI::errstr";
	    my $res = $sth->fetchall_arrayref();
	    
	    foreach my $l(@$res){
		$l->[1] =~ s/^stSG|^stC//g;
		$rec{$l->[0]}{"name"} = $l->[1];
		$rec{"ord"} = $l->[2];   #### check for repeated orders???
	    }
	    
	    if($rec{"ord"}){
		my $statement2 = qq[select s.oligo_service, u.session_date
				    from sts_order s,
				    user_session u,
				    stsorder_status so
				    where s.id_order = ?
				    and so.id_order = s.id_order
				    and so.id_session = u.id_session];
		
		
		my $sth = $dbh->prepare($statement2) || die "ERROR:$DBI::errstr";
		$sth->execute($rec{"ord"}) || die "ERROR:$DBI::errstr";
		my @a = $sth->fetchrow();
		$rec{"service"} = $a[0];
		$rec{"date"} = $a[1];
	    
	    }
	}
	else{ warn "No plate found for $input\n";}
    };
    if ($@) {  
	if (defined $dbh) {
	    $dbh->disconnect};
	
	if ($DBI::err) {
	    print "DB ERROR: $DBI::errstr\n";
	} 
	else {
	    print "$@";
	}
    }
    if (defined $dbh) {
	$dbh->disconnect;
    }
    return(\%rec);
}
sub get_oligo_plate_new2{

    
    my ($input,$type) = @_;
    my $dbi_user = "snpreport";
    my $dbh;
    
  
    my $ord;

    my @plates;
    my @out;

    $dbh->{autocommit} = 0;
    
    eval {
	
	$dbh = WrapDBI->connect($dbi_user, {RaiseError => 1});
	my $statement;
	my $plate;
	if($type eq "sts"){
	    
	    my  $plate_ext_sth = $dbh->prepare(qq[select distinct opw.id_oligoplate
						  from 
						  oligo_order oo,
						  order_well ow,
						  oligoplate_well opw
						  where oo.oligoname = ?
						  and ow.id_oo = oo.id_oo 
						  and opw.id_well =ow.id_well  
						  ]);
	    $plate_ext_sth->execute($input) || die "ERROR:$DBI::errstr";
	    my $res = $plate_ext_sth->fetchall_arrayref();
	    foreach my $l(@{$res}){

		push(@plates, $l->[0]);
	    }
	}
	else{
	    @plates = ($input);
	}
	if($plates[0]){
	    
	    foreach my $plate(@plates){

		my %rec;
		$rec{"id"} = $plate;
		my $sts_ext_sth = $dbh->prepare(qq[select opw.id_map, oo.oligoname, oo.id_order, opd.description
						   from 
						   oligo_order oo,
						   order_well ow,
						   oligoplate_well opw,
						   optypedict opd,
						   oligoplate op
						   where opw.id_oligoplate = ?
						   and ow.id_well =  opw.id_well
						   and oo.id_oo = ow.id_oo
						   and op.id_oligoplate=  opw.id_oligoplate
						   and opd.id_dict = op.plate_type
						   ] ) || die "ERROR:$DBI::errstr";
		
		$sts_ext_sth->execute($plate) || die "ERROR:$DBI::errstr";
		my $res = $sts_ext_sth->fetchall_arrayref();
		
		foreach my $l(@$res){
		    $l->[1] =~ s/^stSG|^stC//g;
		    $rec{$l->[0]}{"name"} = $l->[1];
		    $rec{"ord"} = $l->[2];   #### check for repeated orders???
		    $rec{"typ"} = $l->[3];
		}
		my $status_ext_sth = $dbh->prepare(qq[select oligoplate_status.STATUS_DATE,
						      opstatusdict.DESCRIPTION
						      from oligoplate_status,
						      opstatusdict
						      where oligoplate_status.ID_OLIGOPLATE =?
						      and opstatusdict.id_dict = oligoplate_status.STATUS
						      ] ) || die "ERROR:$DBI::errstr";
		
		$status_ext_sth->execute($plate) || die "ERROR:$DBI::errstr";
		$rec{"status"} = $status_ext_sth->fetchall_arrayref();
		
		


		#if($rec{"ord"}){
		 #   my $statement2 = qq[select s.oligo_service, u.session_date
		#			from sts_order s,
		#			user_session u,
		#			stsorder_status so
		#			where s.id_order = ?
		#			and so.id_order = s.id_order
		#			and so.id_session = u.id_session];
		#    
		#    
		#    my $sth = $dbh->prepare($statement2) || die "ERROR:$DBI::errstr";
		#    $sth->execute($rec{"ord"}) || die "ERROR:$DBI::errstr";
		#    my @a = $sth->fetchrow();
		#    $rec{"service"} = $a[0];
		#    $rec{"date"} = $a[1];
		    
		#}
	    
		push (@out, \%rec);
	    }
	}
	else{ warn "No plate found for $input\n";}
    };
    if ($@) {  
	if (defined $dbh) {
	    $dbh->disconnect};
	
	if ($DBI::err) {
	    print "DB ERROR: $DBI::errstr\n";
	} 
	else {
	    print "$@";
	}
    }
    if (defined $dbh) {
	$dbh->disconnect;
    }
    return(\@out);
}
sub get_oligo_plate_new2_dev{

    
    my ($input,$type) = @_;
    my $dbi_user = "snptest";
    my $dbh;
    
  
    my $ord;

    my @plates;
    my @out;

    $dbh->{autocommit} = 0;
    
    eval {
	
	$dbh = WrapDBI->connect($dbi_user, {RaiseError => 1});
	my $statement;
	my $plate;
	if($type eq "sts"){
	    
	    my  $plate_ext_sth = $dbh->prepare(qq[select opw.id_oligoplate
						  from 
						  oligo_order oo,
						  order_well ow,
						  oligoplate_well opw
						  where oo.oligoname = ?
						  and ow.id_oo = oo.id_oo 
						  and opw.id_well =ow.id_well  
						  ]);
	    $plate_ext_sth->execute($input) || die "ERROR:$DBI::errstr";
	    my $res = $plate_ext_sth->fetchall_arrayref();
	    foreach my $l(@{$res}){

		push(@plates, $l->[0]);
	    }
	}
	else{
	    @plates = ($input);
	}
	if($plates[0]){
	    
	    foreach my $plate(@plates){

		my %rec;
		$rec{"id"} = $plate;
		my $sts_ext_sth = $dbh->prepare(qq[select opw.id_map, oo.oligoname, oo.id_order, opd.description
						   from 
						   oligo_order oo,
						   order_well ow,
						   oligoplate_well opw,
						   optypedict opd,
						   oligoplate op
						   where opw.id_oligoplate = ?
						   and ow.id_well =  opw.id_well
						   and oo.id_oo = ow.id_oo
						   and op.id_oligoplate=  opw.id_oligoplate
						   and opd.id_dict = op.plate_type
						   ] ) || die "ERROR:$DBI::errstr";
		
		$sts_ext_sth->execute($plate) || die "ERROR:$DBI::errstr";
		my $res = $sts_ext_sth->fetchall_arrayref();
		
		foreach my $l(@$res){
		    $l->[1] =~ s/^stSG|^stC//g;
		    $rec{$l->[0]}{"name"} = $l->[1];
		    $rec{"ord"} = $l->[2];   #### check for repeated orders???
		    $rec{"typ"} = $l->[3];
		}
		
		if($rec{"ord"}){
		    my $statement2 = qq[select s.oligo_service, u.session_date
					from sts_order s,
					user_session u,
					stsorder_status so
					where s.id_order = ?
					and so.id_order = s.id_order
					and so.id_session = u.id_session];
		    
		    
		    my $sth = $dbh->prepare($statement2) || die "ERROR:$DBI::errstr";
		    $sth->execute($rec{"ord"}) || die "ERROR:$DBI::errstr";
		    my @a = $sth->fetchrow();
		    $rec{"service"} = $a[0];
		    $rec{"date"} = $a[1];
		    
		}
	    
		push (@out, \%rec);
	    }
	}
	else{ warn "No plate found for $input\n";}
    };
    if ($@) {  
	if (defined $dbh) {
	    $dbh->disconnect};
	
	if ($DBI::err) {
	    print "DB ERROR: $DBI::errstr\n";
	} 
	else {
	    print "$@";
	}
    }
    if (defined $dbh) {
	$dbh->disconnect;
    }
    return(\@out);
}

sub get_plate_to_ass{
    
    #### input ol name to get plate mapping to produce seqn assay plate 
    my ($input) = @_;
    
    $ENV{'TWO_TASK'} = 'snp';
    $ENV{'ORACLE_HOME'} = '/usr/local/oracle';	# Oracle home
    
    
    my $dbi_user = "snpreport";
    my $dbh;
    
    my %rec;
    my $ord;
    my %temp;
    
    
    eval {
	
	$dbh = WrapDBI->connect($dbi_user, {RaiseError => 1});
	$dbh->{autocommit} = 0;

	my $statement;
	my $plate= $dbh->fetch_scalar(qq[select opw.id_oligoplate
					 from 
					 oligo_order oo,
					 order_well ow,
					 oligoplate_well opw
					 where oo.oligoname='$input'
					 and ow.id_oo = oo.id_oo 
					 and opw.id_well =ow.id_well  
					 ]);
	
	if($plate){
	    warn "plate found in SNP = $plate\n";
	    $statement = qq[select distinct
			    opw.id_map,  w.id_well, snp_name.snp_name, name.sts_name
			    from oligo_primer op,
			    oligo_order oo,
			    order_well ow,
			    oligoplate_well opw,
			    assay_sequenom as1,
			    assay_sequenom as2,
			    well_assay w,
			    well_assay w2,
			    sts sts,
			    sts_name name,
			    snp_name snp_name
			    where opw.id_oligoplate = ?
			    and ow.id_well =  opw.id_well
			    and oo.id_oo = ow.id_oo 
			    and op.id_oligoprimer = oo.id_oligoprimer
			    and sts.antisense_oligoprimer =  op.id_oligoprimer
			    and as1.id_sts =  sts.id_sts
			    and w.id_assay = as1.id_assay
			    and w2.id_well = w.id_well
			    and as2.id_assay = w2.id_assay
			    and snp_name.id_snp = as2.id_snp 
			    and snp_name.snp_name_type = 6
			    and name.id_sts =  as2.id_sts
			    and name.sts_name_type = ?
			    ];
#select distinct
#			    opw.id_map,  w.id_well, snp.dbsnp_id, name.sts_name
#			    from oligo_primer op,
#			    oligo_order oo,
#			    order_well ow,
#			    oligoplate_well opw,
#			    assay_sequenom as1,
#			    assay_sequenom as2,
#			    well_assay w,
#			    well_assay w2,
#			    sts_name name,
#			    snp snp
#			    where opw.id_oligoplate = ?
#			    and ow.id_well =  opw.id_well
#			    and oo.id_oo = ow.id_oo 
#			    and op.id_oligoprimer = oo.id_oligoprimer
#			    and as1.id_sts =  op.id_sts
#			    and w.id_assay = as1.id_assay
#			    and w2.id_well = w.id_well
#			    and as2.id_assay = w2.id_assay
#			    and snp.id_snp = as2.id_snp 
#			    and name.id_sts =  as2.id_sts
#			    and name.sts_name_type = ?
#			    ];
	    
	    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
	    $sth->execute($plate, 1) || die "ERROR:$DBI::errstr";
	    my $res = $sth->fetchall_arrayref();

	    foreach my $l(@$res){
		my $ass = "$l->[1]\-$l->[2]";
		push(@{$rec{$l->[0]}}, $ass);
		$temp{$l->[0]} = $l->[3];
		
	    }
	    
	    
	}
	else{ warn "No plate found for $input\n";}
    };
    if ($@) {  
	if (defined $dbh) {
	    $dbh->disconnect};
	
	if ($DBI::err) {
	    print "DB ERROR: $DBI::errstr\n";
	} 
	else {
	    print "$@";
	}
    }
    if (defined $dbh) {
	$dbh->disconnect;
    }
    return(\%rec, \%temp);
}

sub get_plate_to_ass_gs{
    
    #### input ol name to get plate mapping to produce seqn assay plate 
    my ($input) = @_;
    
    $ENV{'TWO_TASK'} = 'snp';
    $ENV{'ORACLE_HOME'} = '/usr/local/oracle';	# Oracle home
    
    
    my $dbi_user = "snpreport";
    my $dbh;
    
    my %rec;
    my $ord;
    my %temp;
    
    
    eval {
	
	$dbh = WrapDBI->connect($dbi_user, {RaiseError => 1});
	$dbh->{autocommit} = 0;

	my $statement;
	my $plate= $dbh->fetch_scalar(qq[select opw.id_oligoplate
					 from 
					 oligo_order oo,
					 order_well ow,
					 oligoplate_well opw
					 where oo.oligoname='$input'
					 and ow.id_oo = oo.id_oo 
					 and opw.id_well =ow.id_well  
					 ]);
	
	if($plate){
	    warn "plate found in SNP = $plate\n";
	    my $sth = $dbh->prepare( qq[select distinct
					opw.id_map,  w.id_well, snp.dbsnp_id
					from 
					oligo_order oo,
					order_well ow,
					oligoplate_well opw,
					assay_sequenom as1,
					assay_sequenom as2,
					well_assay w,
					well_assay w2,
					sts sts,
					snp snp
					where opw.id_oligoplate = ?
					and ow.id_well =  opw.id_well
					and oo.id_oo = ow.id_oo 
					and sts.ANTISENSE_OLIGOPRIMER = oo.id_oligoprimer
					and as1.id_sts =  sts.id_sts
					and w.id_assay = as1.id_assay
					and w.id_well like 'W2%'
					and w2.id_well = w.id_well
					and as2.id_assay = w2.id_assay
					and snp.id_snp = as2.id_snp 
					]);
	    
	    $sth->execute($plate, ) || die "ERROR:$DBI::errstr";
	    my $res = $sth->fetchall_arrayref();
	    
	    foreach my $l(@$res){
		my $ass = "$l->[1]\-$l->[2]";
		push(@{$rec{$l->[0]}}, $ass);
		$temp{$l->[0]} = $l->[3];
		
	    }
	    
	    
	}
	else{ die "No plate found for $input\n";}
    };
    if ($@) {  
	if (defined $dbh) {
	    $dbh->disconnect};
	
	if ($DBI::err) {
	    die "DB ERROR: $DBI::errstr\n";
	} 
	else {
	    die "$@";
	}
    }
    if (defined $dbh) {
	$dbh->disconnect;
    }
    return(\%rec, \%temp);
}

sub prep_query{
    
    my ($dbh, $q) = @_;
    my $query ;
    
    if($q eq "get_quad"){
	$query = qq[select wa.id_well,as2.id_assay,  sn2.sts_name, oo.oligoname, sw.term_mix
		    from sts_name  sn1,  
		    assay_sequenom as1,
		    oligo_order oo,
		    well_assay  wa,
		    well_assay  wa2,
		    sts_name sn2, 
		    sequenom_well  sw,
		    assay_sequenom  as2
		    where sn1.sts_name = ?
		    and as1.id_sts = sn1.id_sts
		    and wa.id_assay = as1.id_assay 
		    and sw.id_well = wa.id_well
		    and wa2.id_well  = sw.id_well
		    and as2.id_assay = wa2.id_assay 
		    and oo.id_oligoprimer = as2.id_oligoprimer
		    and sn2.id_sts = as2.id_sts 
		    and sn2.sts_name_type='1'
		    ];
    }
    my $sth = $dbh->prepare($query) || die "ERROR:$DBI::errstr";
    return($sth);
    
}

sub get_quad2{

    my ($sth, $sts) = @_;
        
    $sth->execute($sts) || die "ERROR:$DBI::errstr";
    my $rec = $sth->fetchall_arrayref();
    
    return($rec);
    
}
sub get_quad{

    my ($sts) = @_;

    $ENV{'TWO_TASK'} = 'snp';    
    my $dbi_user = "snpreport";
    my $rec;
    my $dbh;
       
    eval {
	
	$dbh = WrapDBI->connect($dbi_user, {RaiseError => 1});
	$dbh->{autocommit} = 0;
	
	
	
	my $query = qq[select wa.id_well,as2.id_assay,  sn2.sts_name, oo.oligoname, sw.term_mix
		       from sts_name  sn1,  
		       assay_sequenom as1,
		       oligo_order oo, 
		       well_assay  wa,
		       well_assay  wa2,
		       sts_name sn2, 
		       sequenom_well  sw,
		       assay_sequenom  as2
		       where sn1.sts_name = ?
		       and as1.id_sts = sn1.id_sts
		       and wa.id_assay = as1.id_assay 
		       and sw.id_well = wa.id_well
		       and wa2.id_well  = sw.id_well
		       and as2.id_assay = wa2.id_assay 
		       and oo.id_oligoprimer = as2.id_oligoprimer
		       and sn2.id_sts = as2.id_sts 
		       and sn2.sts_name_type= ?
		       ];

	my $sth = $dbh->prepare($query) || die "ERROR:$DBI::errstr";
	$sth->execute($sts, 1) || die "ERROR:$DBI::errstr";
	$rec = $sth->fetchall_arrayref();
	
    };
    if ($@) {  
	if ($DBI::err) {
	    print "DB ERROR: $DBI::errstr\n";
	} 
	else {
	    print "$@";
	}
    }
    if (defined $dbh) {
	$dbh->disconnect;
    }
    return($rec);

}
sub get_plate{

    
    my ($input, $type) = @_;
    my $dbi_user = "snpreport";
    my $dbh;
    
    my %rec;
    my $ord;

    $dbh->{autocommit} = 0;
    
    eval {
	
	$dbh = WrapDBI->connect($dbi_user, {RaiseError => 1});
	my $statement;
	my $plate;
	if($type eq "plate"){
	    $plate = $input;
	}
	else{
	 
	    $plate = $dbh->fetch_scalar(qq[select opw.id_oligoplate
					   from oligo_primer op,
					   oligo_order oo,
					   order_well ow,
					   oligoplate_well opw,
					   sts_name  sn,
					   stsorder_status st
					   where sn.sts_name ='$input'
					   and sn.id_sts = op.id_sts
					   and op.id_oligoprimer = oo.id_oligoprimer 
					   and oo.id_oo = ow.id_oo
					   and ow.id_well = opw.id_well
					   
					   ]);

	}
	if($plate){  ##123
	    $statement = "select opw.id_map, n.sts_name,  n.id_sts, oo.id_order, s.product_len_bp, opw.id_oligoplate
from sts_name n,
sts s,
oligo_primer op,
oligo_order oo,
order_well ow,
oligoplate_well opw
where opw.id_oligoplate = $plate
and ow.id_well =  opw.id_well
and ow.id_oo = oo.id_oo
and op.id_oligoprimer = oo.id_oligoprimer 
and op.id_sts = n.id_sts
and s.id_sts  = n.id_sts
and n.sts_name_type=1
";
	    
	    
	    
	    
	    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
	    $sth->execute || die "ERROR:$DBI::errstr";
	    my ($pos, $sts, $id,$len,$plate);
	    
	    while ( ($pos, $sts, $id, $ord,$len,$plate) = $sth->fetchrow() ){
		$rec{$pos}{name} = $sts;
		$rec{$pos}{id} = $id;
		$rec{"ord"} = $ord;
		$rec{$pos}{"len"} = $len;
		$rec{"plate"} = $plate;
	    }
	}
	if(!$rec{"ord"}){
	    
	    $rec{"ord"}  = $dbh->fetch_scalar(qq[select  oo.id_order
					       from sts_name n,
					       oligo_primer op,
					       oligo_order oo
					       where n.sts_name='$input'
					       and n.id_sts = op.id_sts
					       and op.id_oligoprimer = oo.id_oligoprimer]);
	    
	}
	if($rec{"ord"}){
	    my $statement2 = "select s.oligo_service, u.session_date
from sts_order s,
user_session u,
stsorder_status so
where s.id_order = ?
and so.id_order = ?
and so.id_session = u.id_session";

	    my ($ser, $dt);
	    my $sth = $dbh->prepare($statement2) || die "ERROR:$DBI::errstr";
	    $sth->execute($rec{"ord"},$rec{"ord"})  || die "ERROR:$DBI::errstr";
	    while ( ($ser, $dt) = $sth->fetchrow() ){
		$rec{"service"} = $ser;
		$rec{"date"} = $dt;
	    }
	}
    
    };
    if ($@) {  
	if (defined $dbh) {
	    $dbh->disconnect};
	
	
	if ($DBI::err) {
	    print "DB ERROR: $DBI::errstr\n";
	} 
	else {
	    print "$@";
	    
	}
    }
    
    else{
	$dbh->disconnect;
    }
    return(\%rec);
    
    
    
}

sub get_prescreen{
    
    my ($dbh,  $sts) = @_;
    my %rec;
    my $statement = "select s.OPTIMAL_TEMP, s.IS_PASS, q.NUM_BANDS,  s.PRODUCT_LEN_BP, q.TEMP_C
from STS s,
STS_NAME n,
STS_QUALITY q
where n.STS_NAME = $sts
and s.ID_STS = n.ID_STS
and q.ID_STS = n.ID_STS";
    
    
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($tem, $tp, $ba,$len, $an);
    
    while ( ($tem, $tp, $ba,$len, $an) = $sth->fetchrow() ){
	$rec{$tem}{pass} = $tp;
	$rec{$tem}{ba} = $ba;
    }
    
}



sub update_temp{

    my ($dbh, $id, $temp)= @_;

    $temp =~ s/C$//;
    #warn "updating temp $temp\n";
    $dbh->do(qq[update STS
		set OPTIMAL_TEMP = '$temp'
		where ID_STS ='$id']);
    
}


sub update_pass{

    my ($dbh, $id, $pass)= @_;
    warn "updating $id as $pass\n";
    my %outcome = ( "Passed"   => "1",
		    "Failed"   => "0",
		    );
    $dbh->do(qq[update STS 
		set IS_PASS = '$outcome{$pass}'
		where ID_STS ='$id']);
    
}



sub update_qual{

    my ($dbh, $id, $res, $tem)= @_;
    
### To be improved
    my $score ;
    warn "updating $id $tem as $res\n";
    if($res =~ /Multi/i){$score ="4";}
    elsif($res =~ /No/i){$score ="0";}
    elsif($res =~ /Failed/i){$score ="0";}
    elsif($res =~ /Passed/i){$score ="1";}
    else{$score ="";}

    my $stsq_ins_sth = $dbh->prepare
	("insert into STS_QUALITY (ID_STS, TEMP_C, NUM_BANDS ) values (?,?,?)");
    
    $stsq_ins_sth->execute($id,  $tem, $score);

    
    
}

sub add_read{

### THIS SHOULD BE GET_READ FROM TRACKDB
    my ($dbh, $read, $id_oligo) = @_;

    my $id_read = snpdb::next_num($dbh, "id_read");
    my $read_ins_sth = $dbh->prepare
	("insert into SEQREAD (ID_READ, READ_NAME, ID_OLIGO ) values (?,?,?)");
    
    $read_ins_sth->execute($id_read,  $read, $id_oligo);

    return($id_read);

}




sub get_person_plate{

    my ($dbh, $log)= @_;

    my $id = trackdb::get_person($log);
 
    my $statement = "select opw.id_oligoplate 
from oligoplate_well opw,
order_well ow,
oligo_order oo,
oligoplate op
where  oo.order_person ='$id'
and op.PLATE_TYPE = '3' 
and oo.id_oo = ow.id_oo
and ow.id_well = opw.id_well
 ";
}

sub order_stat{

    my $dbi_user = "snpreport";
    my $dbh;
    my %ord;
    eval {
	
	$dbh = WrapDBI->connect($dbi_user, {RaiseError => 1});
	$dbh->{autocommit} = 0;

	my $statement = qq[select s.ID_ORDER, oo.ID_COSTCODE, us.SESSION_DATE, s.OLIGO_SERVICE
			   from STS_ORDER s,
			   OLIGO_ORDER oo,
			   STSORDER_STATUS st,
			   USER_SESSION us
			   where  oo.ID_ORDER = s.ID_ORDER
			   and st.ID_ORDER = s.ID_ORDER
			   and st.ID_SESSION = us.ID_SESSION
			   ];
	
	my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
	$sth->execute || die "ERROR:$DBI::errstr";
	my ($ord, $cc, $date, $ser) ;
	
	while ( ($ord, $cc, $date, $ser) = $sth->fetchrow() ){
	    $ord{$ord}{cost} = $cc;
	    $ord{$ord}{serv} = $ser;
	    $ord{$ord}{date} = $date;
	    
	}
    };
    if ($@) {  
	
	if ($DBI::err) {
	    print "DB ERROR: $DBI::errstr\n";
	} 
	else {
	    print "$@";
	    
	}
    }
    if (defined $dbh) {
   	$dbh->disconnect;
    }
    
    return(\%ord);
}


sub get_ordnum{
    
    my ($dbh, $sts) = @_;
    
    my $ordnum = $dbh->fetch_scalar(qq[select oo.ID_ORDER
				       from OLIGO_PRIMER op,
				       OLIGO_ORDER oo,
				       STS_NAME  sn
				       where sn.STS_NAME ='$sts'
				       and sn.ID_STS = op.ID_STS
				       and op.ID_OLIGOPRIMER = oo.ID_OLIGOPRIMER ]);
    return( $ordnum);  
}


sub get_sts{


    my ($sts) = @_;

    my $dbi_user = "snpreport";
    my $dbh;
    my $dat;
   
    
    eval {
	
	$dbh = WrapDBI->connect($dbi_user, {RaiseError => 1});
	$dbh->{autocommit} = 0;
	
	my  $sts_ext_sth = $dbh->prepare(qq[select sn.sts_name,
					    o1.oligo_sequence,
					    o2.oligo_sequence,
					    sts.product_len_bp,
					    sts.optimal_temp
					    from 
					    sts sts,
					    sts_name	sn,
					    oligo_primer op1,
					    oligo o1,
					    oligo_primer op2,
					    oligo o2
					    where sn.sts_name =  ?
					    and sts.id_sts = sn.id_sts
					    and sts.SENSE_OLIGOPRIMER = op1.id_oligoprimer
					    and op1.id_oligo = o1.id_oligo 
					    and sts.ANTISENSE_OLIGOPRIMER  = op2.id_oligoprimer
					    and op2.id_oligo = o2.id_oligo 
					    ]);
	
       
	$sts_ext_sth->execute($sts) || die "ERROR:$DBI::errstr";
	$dat = $sts_ext_sth->fetchall_arrayref();  
    };
    return($dat);
}

sub get_accNum{

my ($accNum) = @_;   ## Input required rm later


$accNum =~ s/P//i;
    
    my $plate;           ## Input required rm later


my %seen = ();
my @geneData = ();
my @geneName = ();

my $dbh;
my $dbi_user = "snp";
my %rec;

## acc_number39382
#and oligo_order.oligoname like '%S'

eval {
    
        $dbh = WrapDBI->connect($dbi_user, {RaiseError => 1});
        $dbh->{autocommit} = 0;

my $statement = "select  distinct order_number.ID_ORDER,oligo_order.oligoname, forename,surname,
purchase_number.id_costcode,  order_number.PURCHASE_NUMBER, 
order_number.NUM_BASES, oligoplate_well.id_map, sts_order.id_price, oligo_price.base_price,purchase_number.assign_date,sts_order.oligo_service
from purchase_number, order_number, oligo_order, person, order_well, oligoplate_well, sts_order, oligo_price
where purchase_number.purchase_number = '$accNum' 
and  purchase_number.purchase_number = order_number.purchase_number
and order_number.ID_ORDER = oligo_order.ID_ORDER
and oligo_order.order_person = person.ID_PERSON
and oligo_order.id_oo = order_well.id_oo
and order_well.id_well = oligoplate_well.id_well 
and oligoplate_well.id_map IN ('1','96')
and oligo_order.ID_ORDER = sts_order.ID_ORDER  
and sts_order.ID_PRICE = oligo_price.ID_PRICE
order by order_number.ID_ORDER,oligoplate_well.id_map";


##and oligoplate_well.id_map IN ('1','96')  ##above three lines

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


my @array = ();
	
#	my @array;

	while ( (@array) = $sth->fetchrow_array() ){

#	    print "@array --------------------------------- \n";
#print "$array[1]\n";


$seen{$array[0]} = 1; ## creating individual gene names

my $data = join (' ',@array);          ## joining each snp result into line of data


#print "$data   Account number kkkkkkkkkkkkkkkkk\n\n ---haha";

push (@geneData, $data);                       ## line of Gene result data is put into the array @geneData


}

foreach (keys %seen){                          

        	    push (@geneName, $_);
	    
	}

	my $length_geneData = @geneData;
	my $length_geneName = @geneName;
	
#	print "length_geneData is $length_geneData\n";
#	print "length_geneName is $length_geneName\n";




      };


    if ($@) {  

        if ($DBI::err) {
            print "DB ERROR: $DBI::errstr\n";
        } 
        else {
            print "$@";
        }
    }
    if (defined $dbh) {
        $dbh->disconnect;
    }

return(@geneData);


}

sub get_Who{

my ($who) = @_;

my $userid;

my $month;

($userid, $month) = split(/\s+/,$who);

my @geneData = ();
my $dbh;
my $dbi_user = "snp";
my %rec;

eval {
    
        $dbh = WrapDBI->connect($dbi_user, {RaiseError => 1});
        $dbh->{autocommit} = 0;

my $statement = "select  distinct order_number.ID_ORDER,oligo_order.oligoname, forename,surname,
purchase_number.id_costcode,  order_number.PURCHASE_NUMBER, 
order_number.NUM_BASES, oligoplate_well.id_map, sts_order.id_price, oligo_price.base_price,
user_session.session_date 
from person, oligo_order, sts_order, order_number, purchase_number, order_well, oligoplate_well,oligo_price,
stsorder_status,user_session
where email = '$userid'
and person.ID_PERSON = oligo_order.order_person
and oligo_order.id_order = sts_order.id_order
and oligo_order.id_order = order_number.id_order
and order_number.purchase_number = purchase_number.purchase_number
and purchase_number.assign_date like '%$month'
and oligo_order.id_oo = order_well.id_oo
and order_well.id_well = oligoplate_well.id_well
and oligoplate_well.id_map IN ('1','96')
and sts_order.ID_PRICE = oligo_price.ID_PRICE
and sts_order.id_order = stsorder_status.id_order
and stsorder_status.id_session = user_session.id_session
order by order_number.ID_ORDER,oligoplate_well.id_map";


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


my @array = ();

while ( (@array) = $sth->fetchrow_array() ){

    my $data = join (' ',@array);          ## joining each snp result into line of data

    push (@geneData, $data);                       ## line of Gene result data is put into the array @geneData

}

    };

  if ($@) {  

        if ($DBI::err) {
            print "DB ERROR: $DBI::errstr\n";
        } 
        else {
            print "$@";
        }
    }
    if (defined $dbh) {
        $dbh->disconnect;
    }

return(@geneData);

}


sub get_sts_Order{

my ($sts) = @_;

my @geneData = ();
my $dbh;
my $dbi_user = "snp";
my %rec;

eval {
    
        $dbh = WrapDBI->connect($dbi_user, {RaiseError => 1});
        $dbh->{autocommit} = 0;

my $statement = "select distinct oligo_order.id_order 
from oligo_order 
where oligo_order.oligoname like '%$sts%'";

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


my @array = ();

while ( (@array) = $sth->fetchrow_array() ){

    my $data = join (' ',@array);          ## joining each snp result into line of data

    push (@geneData, $data);                       ## line of Gene result data is put into the array @geneData

}

    };

  if ($@) {  

        if ($DBI::err) {
            print "DB ERROR: $DBI::errstr\n";
        } 
        else {
            print "$@";
        }
    }
    if (defined $dbh) {
        $dbh->disconnect;
    }


return(@geneData);

}


sub get_orderD{

my ($orderD) = @_;

my @geneData = ();
my $dbh;
my $dbi_user = "snp";
my %rec;

eval {
    
        $dbh = WrapDBI->connect($dbi_user, {RaiseError => 1});
        $dbh->{autocommit} = 0;

#my $statement = "select distinct oligo_order.id_order 
#from oligo_order 
#where oligo_order.oligoname like '%140849%'";

my $statement = "select  distinct order_number.ID_ORDER,oligo_order.oligoname, forename,surname,
purchase_number.id_costcode,  order_number.PURCHASE_NUMBER, 
order_number.NUM_BASES, oligoplate_well.id_map, sts_order.id_price, 
oligo_price.base_price,purchase_number.assign_date,sts_order.oligo_service
from oligo_order, person, sts_order, order_number, purchase_number, order_well, oligoplate_well,oligo_price
where oligo_order.id_order= '$orderD'
and oligo_order.order_person = person.ID_PERSON
and oligo_order.id_order = sts_order.id_order
and sts_order.oligo_service = 'Genset'
and oligo_order.id_order = order_number.id_order
and oligo_order.id_order = order_number.id_order
and order_number.purchase_number = purchase_number.purchase_number
and oligo_order.id_oo = order_well.id_oo
and order_well.id_well = oligoplate_well.id_well
and oligoplate_well.id_map IN ('1','96')
and sts_order.ID_PRICE = oligo_price.ID_PRICE
order by order_number.ID_ORDER,oligoplate_well.id_map";

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


my @array = ();

while ( (@array) = $sth->fetchrow_array() ){

    my $data = join (' ',@array);          ## joining each snp result into line of data

    push (@geneData, $data);                       ## line of Gene result data is put into the array @geneData

}

    };

  if ($@) {  

        if ($DBI::err) {
            print "DB ERROR: $DBI::errstr\n";
        } 
        else {
            print "$@";
        }
    }
    if (defined $dbh) {
        $dbh->disconnect;
    }


return(@geneData);

}



sub get_Month_Rep{

my ($month) = @_;

my $date;
my $supplyer;

($date, $supplyer) = split(/\s+/,$month);

my @geneData = ();
my $dbh;
my $dbi_user = "snp";
my %rec;

eval {
    
        $dbh = WrapDBI->connect($dbi_user, {RaiseError => 1});
        $dbh->{autocommit} = 0;



my $statement = "select  distinct stsorder_status.id_order,oligo_order.oligoname, forename,surname,
oligo_order.id_costcode,  order_number.PURCHASE_NUMBER, 
order_number.NUM_BASES, oligoplate_well.id_map, sts_order.id_price, 
oligo_price.base_price,user_session.session_date,sts_order.oligo_service
from user_session, stsorder_status,sts_order,oligo_order,person,order_well,oligoplate_well,order_number,oligo_price
where user_session.session_date like '%$date'
and user_session.ID_SESSION = stsorder_status.ID_SESSION
and stsorder_status.id_order = sts_order.id_order
and sts_order.oligo_service  = '$supplyer'
and sts_order.id_order = oligo_order.id_order
and oligo_order.order_person = person.ID_PERSON
and oligo_order.id_oo = order_well.id_oo
and order_well.id_well = oligoplate_well.id_well
and oligoplate_well.id_map IN ('1')
and sts_order.id_order = order_number.id_order
and sts_order.id_price = oligo_price.id_price
order by oligo_order.id_costcode,stsorder_status.id_order";





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


my @array = ();

while ( (@array) = $sth->fetchrow_array() ){

    my $data = join (' ',@array);          ## joining each snp result into line of data

    push (@geneData, $data);                       ## line of Gene result data is put into the array @geneData

}

    };

  if ($@) {  

        if ($DBI::err) {
            print "DB ERROR: $DBI::errstr\n";
        } 
        else {
            print "$@";
        }
    }
    if (defined $dbh) {
        $dbh->disconnect;
    }


return(@geneData);

}


sub get_ABI_month{

my ($month) = @_;

my $date;
my $supplyer;

($date, $supplyer) = split(/\s+/,$month);

my @geneData = ();
my $dbh;
my $dbi_user = "snp";
my %rec;

eval {
    
        $dbh = WrapDBI->connect($dbi_user, {RaiseError => 1});
        $dbh->{autocommit} = 0;


my $statement = "select  distinct stsorder_status.id_order,oligo_order.oligoname, forename,surname,
oligo_order.id_costcode,  order_number.PURCHASE_NUMBER, 
order_number.num_bases,oligo_price.oligo_price,user_session.session_date,sts_order.oligo_service
from user_session, stsorder_status,sts_order,oligo_order,person,order_well,oligoplate_well,order_number,oligo_price
where user_session.session_date like '%$date'
and user_session.ID_SESSION = stsorder_status.ID_SESSION
and stsorder_status.id_order = sts_order.id_order
and sts_order.oligo_service  = '$supplyer'
and sts_order.id_order = oligo_order.id_order
and oligo_order.order_person = person.ID_PERSON
and oligo_order.id_oo = order_well.id_oo
and order_well.id_well = oligoplate_well.id_well
and oligoplate_well.id_map IN ('1')
and sts_order.id_order = order_number.id_order
and sts_order.id_price = oligo_price.id_price
order by oligo_order.id_costcode,stsorder_status.id_order";





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


my @array = ();

while ( (@array) = $sth->fetchrow_array() ){

    my $data = join (' ',@array);          ## joining each snp result into line of data

    push (@geneData, $data);                       ## line of Gene result data is put into the array @geneData

}

    };

  if ($@) {  

        if ($DBI::err) {
            print "DB ERROR: $DBI::errstr\n";
        } 
        else {
            print "$@";
        }
    }
    if (defined $dbh) {
        $dbh->disconnect;
    }


return(@geneData);

}


sub get_Internal_month{

my ($month) = @_;

my $date;
my $supplyer;

($date, $supplyer) = split(/\s+/,$month);


my @geneData = ();
my $dbh;
my $dbi_user = "snp";
my %rec;

eval {
    
        $dbh = WrapDBI->connect($dbi_user, {RaiseError => 1});
        $dbh->{autocommit} = 0;



my $statement = "select   distinct stsorder_status.id_order,oligo_order.oligoname,forename,surname,oligo_order.id_costcode,sts_order.oligo_service,user_session.session_date,oligo.OLIGO_SEQUENCE
from user_session, stsorder_status,sts_order,oligo_order,person,oligo_primer,oligo
where user_session.session_date like '%$date'
and user_session.ID_SESSION = stsorder_status.ID_SESSION
and stsorder_status.id_order = sts_order.id_order
and sts_order.oligo_service  = '$supplyer'
and sts_order.id_order = oligo_order.id_order
and oligo_order.order_person = person.ID_PERSON
and oligo_order.ID_OLIGOPRIMER = oligo_primer.ID_OLIGOPRIMER
and oligo_primer.ID_OLIGO = oligo.ID_OLIGO
order by oligo_order.id_costcode,stsorder_status.id_order";

#my $statement = "select   distinct stsorder_status.id_order,oligo_order.oligoname,forename,surname,oligo_order.id_costcode,
#sts_order.oligo_service,user_session.session_date,oligoplate_well.id_map,order_number.NUM_BASES,oligo_price.base_price
#from user_session, stsorder_status,sts_order,oligo_order,person,order_well,oligoplate_well,order_number,oligo_price
#where user_session.session_date like '%$date'
#and user_session.ID_SESSION = stsorder_status.ID_SESSION
#and stsorder_status.id_order = sts_order.id_order
#and sts_order.oligo_service  = '$supplyer'
#and sts_order.id_order = oligo_order.id_order
#and oligo_order.order_person = person.ID_PERSON
#and oligo_order.id_oo = order_well.id_oo
#and order_well.id_well = oligoplate_well.id_well
#and oligoplate_well.id_map IN ('1')
#and sts_order.id_order = order_number.id_order
#and sts_order.id_price = oligo_price.id_price
#order by oligo_order.id_costcode,stsorder_status.id_order";




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


my @array = ();

while ( (@array) = $sth->fetchrow_array() ){

    my $data = join (' ',@array);          ## joining each snp result into line of data

    push (@geneData, $data);                       ## line of Gene result data is put into the array @geneData

}

    };

  if ($@) {  

        if ($DBI::err) {
            print "DB ERROR: $DBI::errstr\n";
        } 
        else {
            print "$@";
        }
    }
    if (defined $dbh) {
        $dbh->disconnect;
    }

return(@geneData);

}



sub get_costCode_date{


    my $date = $_[0];
    my $costCode = $_[1];
    
    $date =~ s/\d?\d?\-/\-/;
        

my @geneData = ();
my $dbh;
my $dbi_user = "snp";
my %rec;

eval {
    
        $dbh = WrapDBI->connect($dbi_user, {RaiseError => 1});
        $dbh->{autocommit} = 0;

my $statement = "select  distinct order_number.ID_ORDER,oligo_order.oligoname, forename,surname,
purchase_number.id_costcode,  order_number.PURCHASE_NUMBER, 
order_number.NUM_BASES, oligoplate_well.id_map, sts_order.id_price, 
oligo_price.base_price,purchase_number.assign_date,sts_order.oligo_service
from oligo_order, person, sts_order, order_number, purchase_number, order_well, oligoplate_well,oligo_price
where oligo_order.id_costcode = '$costCode'
and oligo_order.order_person = person.ID_PERSON
and oligo_order.id_order = sts_order.id_order
and sts_order.oligo_service = 'Genset'
and oligo_order.id_order = order_number.id_order
and oligo_order.id_order = order_number.id_order
and order_number.purchase_number = purchase_number.purchase_number
and purchase_number.assign_date like '%$date' 
and oligo_order.id_oo = order_well.id_oo
and order_well.id_well = oligoplate_well.id_well
and sts_order.ID_PRICE = oligo_price.ID_PRICE
order by order_number.ID_ORDER,oligoplate_well.id_map";




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


my @array = ();

while ( (@array) = $sth->fetchrow_array() ){

    my $data = join (' ',@array);          ## joining each snp result into line of data

    push (@geneData, $data);                       ## line of Gene result data is put into the array @geneData

}

    };

  if ($@) {  

        if ($DBI::err) {
            print "DB ERROR: $DBI::errstr\n";
        } 
        else {
            print "$@";
        }
    }
    if (defined $dbh) {
        $dbh->disconnect;
    }


    my $length = @geneData;

return($length);

}



1;


















