#!/usr/local/bin/perl
#######################################################################
# This software has been created by Genome Research Limited (GRL).    # 
# GRL hereby grants permission to use, copy, modify and distribute    # 
# this software and its documentation for non-commercial purposes     # 
# without fee at the user's own risk on the basis set out below.      #
# GRL neither undertakes nor accepts any duty whether contractual or  # 
# otherwise in connection with the software, its use or the use of    # 
# any derivative, and makes no representations or warranties, express #
# or implied, concerning the software, its suitability, fitness for   #
# a particular purpose or non-infringement.                           #
# In no event shall the authors of the software or GRL be responsible # 
# or liable for any loss or damage whatsoever arising in any way      # 
# directly or indirectly out of the use of this software or its       # 
# derivatives, even if advised of the possibility of such damage.     #
# Our software can be freely distributed under the conditions set out # 
# above, and must contain this copyright notice.                      #
#######################################################################

package finch_readname;

# DN1
#####################################################
#Allow a finisher to query the database by readname #
#####################################################
# Supply the module with $db_database, $db_user, $db_pass -> for db connection
# Supply                 $id_role                         -> who is asking ?
# Supply                 $qplate, $qrow, $qcol            -> readname-request
# Returns results in @dbcontents
# If only $plate contains a string -> returns ALL orders for that plate
# Returns 'Warn' in first index of @dbcontents if query is fruitless or an error is detected
# $Id: finch_readname.pm,v 1.2 1998/09/23 08:32:06 dn1 Exp $
# $Log: finch_readname.pm,v $
# Revision 1.2  1998/09/23  08:32:06  dn1
# *** empty log message ***
#
# Revision 1.1  1998/02/07  13:06:17  badger
# Initial revision
#
# Revision 1.2  1998/01/13  12:12:08  dn1
# removed some junk comments
#
# Revision 1.1  1998/01/13  12:09:02  dn1
# Initial revision
#

require Exporter;

use DBI;

@finch_readname::ISA = qw(Exporter);
@finch_readname::EXPORT = qw(readname);

sub readname {
    
    my($db_database, $db_user, $db_pass, $id_role, $qplate, $qrow, $qcol) = @_;

    # Need to check that $qcol is a number
    if ($qcol =~ /\D/){ #if we see a non-digit
	    $next_in = "Warn";
	    push (@dbcontents,$next_in);
	    return @dbcontents;
	}

# Connect to Oracle
    my $dbh = DBI->connect( $db_database, $db_user, $db_pass, 'Oracle') || die "ERROR: Could not connect to database: $DBI::errstr\n";

# Intitiate some stuff
    my @dbcontents ;
    my $statement = '';
    my $sth1 = '';
    my $sth2 = '';
    my $sth3 = '';
    my $sth4 = '';
    my $sth5 = '';
    my $sth6 = '';
    my $sth7 = '';
    my $sth8 = '';
    my $sth9 = '';
    my $statement2 = '';
    my $statement3 = '';
    my $statement4 = '';
    my $statement5 = '';
    my $statement6 = '';
    my $statement7 = '';
    my $statement8 = '';
    my $statement9 = '';
    my $num = 0;
    my $next_in = " ";
    my ($ID_FREQUEST, $ID_FBATCH, $PLATENAME, $ROWLETTER , $COLNUM, $SUFFIX, $ITERATION, $ID_PRIMER, $ID_LOADINGSAMPLE);
    my $STATUS = '';
    my $OLIGONAME = '';
    my $OLIGOSEQ = '';
    my $PROJECTNAME = '';
    my $BATCHDATE = '';
    my $ID_RUNTYPE;
    my $ID_SEQCHEM;
    my $RUN;
    
    
    # We need one statement for specific plate_row_col
    if ($qplate ne '' && $qrow ne '' && $qcol ne ''){
	$statement = "select * from finish_request where platename = \'$qplate\' and rowletter = \'$qrow\' and colnum = \'$qcol'\ ";
    } elsif ($qplate ne ''){

    # And another for just plate
	$statement = "select * from finish_request where platename = \'$qplate\'";
    } else {
	$next_in = "Warn";
	push (@dbcontents,$next_in);
	$dbh->disconnect;
	return @dbcontents;
    }

    $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    while (($ID_FREQUEST,$ID_FBATCH,$PLATENAME,$ROWLETTER,$COLNUM,$ID_SEQCHEM,$ID_RUNTYPE,$ITERATION,$ID_PRIMER,$ID_LOADINGSAMPLE) = $sth1->fetchrow()) {
    
        # What project is this batch for ?
	$statement6 = "select projectname from finish_batch where id_fbatch = \'$ID_FBATCH\'";
	$sth6 = $dbh->prepare($statement6) || die "ERROR:$DBI::errstr";  
	$sth6->execute || die "ERROR:$DBI::errstr";
	$PROJECTNAME = $sth6->fetchrow();
	$sth6->finish;

	# What date was this batch submitted ?
	$statement7 = "select batchdate from finish_batch where id_fbatch = \'$ID_FBATCH\'";
	$sth7 = $dbh->prepare($statement7) || die "ERROR:$DBI::errstr";  
	$sth7->execute || die "ERROR:$DBI::errstr";
	$BATCHDATE = $sth7->fetchrow();
	$sth7->finish;

        # Get the individual-sample's status
	$statement2 = "select status from frequest_status where id_frequest = \'$ID_FREQUEST\' and iscurrent =1";
	$sth2 = $dbh->prepare($statement2) || die "ERROR:$DBI::errstr";  
	$sth2->execute || die "ERROR:$DBI::errstr";
	$STATUS = $sth2->fetchrow();
	$sth2->finish;
	$statement3 = "select description from frequeststatusdict where id_dict = \'$STATUS\'";
	$sth3 = $dbh->prepare($statement3) || die "ERROR:$DBI::errstr";  
	$sth3->execute || die "ERROR:$DBI::errstr";
	$STATUS = $sth3->fetchrow();
	$sth3->finish;
	if ($STATUS eq '') {
	    $STATUS = 'Unknown';
		    }
        # Need $SUFFIX from TABLE_SEQCHEM
	$statement8 = "select suffix from SEQCHEM where id_seqchem = \'$ID_SEQCHEM\'";
	$sth8 = $dbh->prepare($statement8) || die "ERROR:$DBI::errstr";  
	$sth8->execute || die "ERROR:$DBI::errstr";
	$SUFFIX = $sth8->fetchrow();
	$sth8->finish;

	# Need runtype_description from TABLE_RUNTYPE
	if ($ID_RUNTYPE ne ''){
	    $statement9 = "select description from RUNTYPE where id_runtype = \'$ID_RUNTYPE\'";
	    $sth9 = $dbh->prepare($statement9) || die "ERROR:$DBI::errstr";  
	    $sth9->execute || die "ERROR:$DBI::errstr";
	    $RUN = $sth9->fetchrow();
	    $sth9->finish;
	} else {
	    $RUN = 'No Preference';
	}
	

	if ($ID_PRIMER ne ''){
	    # get oligoname from id_primer
	    $statement4 = "select primername from primer where id_primer = \'$ID_PRIMER\'";
	    $sth4 = $dbh->prepare($statement4) || die "ERROR:$DBI::errstr";  
	    $sth4->execute || die "ERROR:$DBI::errstr";	
	    $OLIGONAME = $sth4->fetchrow();
	    $sth4->finish;
	    # get oligoseq from id_primer
	    $statement5 = "select primerseq from primer where id_primer  = \'$ID_PRIMER\'";
	    $sth5 = $dbh->prepare($statement5) || die "ERROR:$DBI::errstr";  
	    $sth5->execute || die "ERROR:$DBI::errstr";	
	    $OLIGOSEQ = $sth5->fetchrow();
	    $sth5->finish;
	    $next_in = "Batch-no. $ID_FBATCH $BATCHDATE $PROJECTNAME Status $STATUS $PLATENAME$ROWLETTER$COLNUM$SUFFIX$ITERATION$OLIGONAME $OLIGOSEQ $RUN";
	    #warn "$next_in \n";
	    push (@dbcontents,$next_in);
	} else {
	    $next_in = "Batch-no. $ID_FBATCH $BATCHDATE $PROJECTNAME Run $RUN Status $STATUS $PLATENAME$ROWLETTER$COLNUM$SUFFIX$ITERATION $RUN";
	    #warn "$next_in \n";
	    push (@dbcontents,$next_in);
	}
	$num++;
    }

    $sth1->finish;
    
    # Nothing found :( 
    if ($num == 0){
	    $next_in = "Warn";
	    push (@dbcontents,$next_in);
	    $dbh->disconnect;
	    return @dbcontents;
	}


#################################################
# We must return something here ie. @dbcontents;#
#################################################
    $dbh->disconnect;
    return @dbcontents;
}

1;


