#!/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.                      #
#######################################################################


# $Id: owl_get_blocks.pm,v 1.27 1999/08/18 11:43:44 dn1 Exp $
# $Log: owl_get_blocks.pm,v $
# Revision 1.27  1999/08/18 11:43:44  dn1
# *** empty log message ***
#
# Revision 1.26  1998/09/23 08:39:04  dn1
# *** empty log message ***
#
# Revision 1.25  1998/08/14  17:01:31  dn1
# *** empty log message ***
#
# Revision 1.24  1998/08/14  14:32:21  dn1
# added go faster stripes
#
# Revision 1.23  1998/07/13  17:07:18  dn1
# *** empty log message ***
#
# Revision 1.22  1998/07/13  16:54:16  dn1
# major pruning...
#

require Exporter;
use DBI;
use finch_view;
use finch_suffix_runtype;
use strict;


@owl_get_blocks::ISA = qw(Exporter);
@owl_get_blocks::EXPORT = qw(get_blocks);
#use Data::Dumper;

package owl_get_blocks;

# DN1
################################################################################
#Interrogate the database for blocks of 'created' samples for the correct view #
################################################################################
# Supply the module with $db_database, $db_user, $db_pass -> for db connection
# Supply                 $request_view                    -> for query type
# Supply                 $id_role                         -> who is asking ?
# Supply                 $teamname                        -> the team in question
# Supply                 $ID_TEAM_FINSERV                 -> batch submission id (cf. fsname)

sub get_blocks {


    # Get the incoming
    my($db_database, $db_user, $db_pass, $request_view, $id_role, $teamname, $ID_TEAM_FINSERV, $primersort) = @_;
    my $fsname =  $request_view;

    # Init some vars
    my ($ID_FREQUEST, $PLATENAME, $ROWLETTER, $COLNUM, $SUFFIX, $PROJECTNAME, $FINISHER, $BATCHNUM, $BATCHDATE, $FINSERVICE, $ITERATION, $OLIGO, @finisherList, $private, @datacollector, $statement_batch2, $finisher, $forename, $surname, $email, $sth1A, $projectname, $this_proj, $statementR, $sth1R, $valid_runtype, @valid_runtypes, $next, $sth1PR, $valid_protocol, @valid_protocols, $statementB, $sth1B, $statement_olname, $sth_olname, $statement_olseq, $sth_olseq, $info, $requests, $statementper, $sthper, $updates, @info, @requests, @updates, $dbh, $ID_FBATCH, $ID_SEQCHEM, $ID_RUNTYPE, $ID_PRIMER, $SEQ, $statement_batch, $sthbatch, $batchnum_a, $batchnum_b, $batchnum, $statement_fin, $num, $statement_fs, $sthfs, $num1, @projectnames, $statementA, $protocol, $id_finisher, $num2, $statementPR, $num3, $num, $validnumber, $title, $iter, $sthfin);

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

    #########################################################
    # Are there actually any samples awaiting attention >?< #
    #########################################################

    $statement_batch;
    if ($request_view eq 'private finisher'){
	$fsname = 'private finisher';
	$private = 1;

	######################################## 
	# Count statements for Private samples #
	########################################
	# Firstly for requests without primers 

	$statement_batch = "
select count(finish_request.id_frequest) 
from finish_request, frequest_status, finish_batch 
where finish_request.id_fbatch = finish_batch.id_fbatch 
and finish_request.id_frequest = frequest_status.id_frequest 
and finish_request.id_primer is NULL
and frequest_status.status = 1 
and frequest_status.iscurrent = 1 
and finish_batch.ID_TEAM_FINSERV = \'$ID_TEAM_FINSERV\'
and finisher = \'$id_role\'
";
	# Secondly for requests with primers

	$statement_batch2 = "
select count(finish_request.id_frequest) 
from finish_request, frequest_status, finish_batch, primer_status
where finish_request.id_fbatch = finish_batch.id_fbatch 
and finish_request.id_frequest = frequest_status.id_frequest 
and finish_request.id_primer is not NULL
and finish_request.id_primer = primer_status.id_primer
and primer_status.status = 4
and primer_status.iscurrent = 1
and frequest_status.status = 1 
and frequest_status.iscurrent = 1 
and finish_batch.ID_TEAM_FINSERV = \'$ID_TEAM_FINSERV\'
and finisher = \'$id_role\'
";
    } else {

	#########################################
	# Count statements for finserve samples #
	#########################################
	$fsname =  $request_view;
	$private = 0;
        # Firstly for requests without primers 

	$statement_batch = "
select count(finish_request.id_frequest) 
from finish_request, frequest_status, finish_batch
where finish_request.id_fbatch = finish_batch.id_fbatch 
and finish_request.id_frequest = frequest_status.id_frequest 
and finish_request.id_primer is NULL
and frequest_status.status = 1 
and frequest_status.iscurrent = 1 
and finish_batch.id_team_finserv = \'$ID_TEAM_FINSERV\'
";
        # Secondly for requests with primers

	$statement_batch2 = "
select count(finish_request.id_frequest) 
from finish_request, frequest_status, finish_batch, primer_status
where finish_request.id_fbatch = finish_batch.id_fbatch 
and finish_request.id_frequest = frequest_status.id_frequest 
and finish_request.id_primer is not NULL
and finish_request.id_primer = primer_status.id_primer
and primer_status.status = 4
and primer_status.iscurrent = 1
and frequest_status.status = 1 
and frequest_status.iscurrent = 1 
and finish_batch.id_team_finserv = \'$ID_TEAM_FINSERV\'
";

    }

    $sthbatch = $dbh->prepare($statement_batch) || die "ERROR:$DBI::errstr";
    #warn "$statement_batch\n";

    $sthbatch->execute || die "ERROR:$DBI::errstr";
    $batchnum_a = $sthbatch->fetchrow();
    $sthbatch->finish;

    $sthbatch = $dbh->prepare($statement_batch2) || die "ERROR:$DBI::errstr";
    #warn "$statement_batch2\n";

    $sthbatch->execute || die "ERROR:$DBI::errstr";
    $batchnum_b = $sthbatch->fetchrow();
    $sthbatch->finish;

    $batchnum = $batchnum_a + $batchnum_b;

############## Return if there are no samples awaiting attention ##############

    if ($batchnum == 0){
	$dbh->disconnect;
	return ($batchnum, \@datacollector);
    }

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


############## Do stuff if there are samples waiting ##########################

    if ($batchnum != 0){
    # We have some samples waiting in oracle to map.
	if ($private == 0){
	# We need a list of the finishers who have submitted samples with the status 'created'
	    # for $fsname's attention

	    $statement_fin = "
select unique finish_batch.finisher
from finish_batch, finish_request, frequest_status 
where finish_batch.id_fbatch = finish_request.id_fbatch 
and finish_request.id_frequest = frequest_status.id_frequest 
and finish_batch.ID_TEAM_FINSERV = \'$ID_TEAM_FINSERV\' 
and frequest_status.status = 1 
and frequest_status.iscurrent = 1
";
	    $num = 0;
	    $sthfin = $dbh->prepare($statement_fin) || die "ERROR:$DBI::errstr";
	    #warn "$statement_fin\n";

	    $sthfin->execute || die "ERROR:$DBI::errstr";
	    while (($finisher) = $sthfin->fetchrow()) {
		$finisherList[$num] = $finisher;
		$num++;
	    }
	    $sthfin->finish;
	    # Let's turn these ID_ROLES into names for display

	    foreach $id_role (@finisherList) {

		$statement_fs = "
select a.forename, a.surname, a.email
from person a, team_person_role b 
where a.id_person = b.id_person
and b.id_role = '$id_role'
";
		$sthfs = $dbh->prepare($statement_fs) || die "ERROR:$DBI::errstr";
		#warn "$statement_fs\n";

		$sthfs->execute || die "ERROR:$DBI::errstr";
		($forename, $surname, $email) = $sthfs->fetchrow();
		$sthfs->finish;
	    }
	} else {		# NOT a private check
	    # We want to look at samples from just $id_role
	    $finisherList[0] = $id_role;
	}
    
	# We need a list of the projectnames for which samples have been submitted 'created'
	# for $fsname's attention
     
	########################################
	# Start looping through to find samples
	########################################
	
	##############################
	# Needs to be FOREACH FINISHER
	##############################

	###################
	#warn "FINSIHERS\n";
	#warn @finisherList;
	###################

	foreach $id_finisher (@finisherList) {


	    #########################################################
	    # We should work out the projects for this finisher here
	    #########################################################
	    $statementA = "
select unique finish_batch.projectname
from finish_batch, finish_request, frequest_status
where finish_batch.id_fbatch = finish_request.id_fbatch
and finish_request.id_frequest = frequest_status.id_frequest
and finish_batch.ID_TEAM_FINSERV = \'$ID_TEAM_FINSERV\'
and finish_batch.finisher = \'$id_finisher\'
and frequest_status.status = 1 
and frequest_status.iscurrent = 1
";

	    $sth1A = $dbh->prepare($statementA) || die "ERROR:$DBI::errstr";
	    $sth1A->execute || die "ERROR:$DBI::errstr";
	    $num1 = 0;
	    @projectnames = ();
	    while (($projectname) = $sth1A->fetchrow()) {
		$projectnames[$num1] = $projectname;
		$num1++;
	    }
	    $sth1A->finish;

	    #############################
	    # Needs to be FOREACH PROJECT
	    #############################

	    ######################
	    #warn "projectnames\n";
	    #warn @projectnames;
	    ######################

	    foreach $this_proj (@projectnames) {

		if ($this_proj ne '') {

		    #############################
		    # Needs to be FOREACH RUNTYPE
		    #############################

		    ############################################################
		    # We should work out the runtypes for this finisher/project
		    ############################################################
		    
		    $statementR = "
select unique finish_request.id_runtype
from finish_batch, finish_request, frequest_status		    
where finish_batch.id_fbatch = finish_request.id_fbatch
and finish_request.id_frequest = frequest_status.id_frequest
and finish_batch.ID_TEAM_FINSERV = \'$ID_TEAM_FINSERV\'
and finish_batch.finisher = \'$id_finisher\'
and finish_batch.projectname = \'$this_proj\'
and frequest_status.status = 1 
and frequest_status.iscurrent = 1
";

		    #warn "$statementR\n";

		    $sth1R = $dbh->prepare($statementR) || die "ERROR:$DBI::errstr";
		    $sth1R->execute || die "ERROR:$DBI::errstr";
		    $num2 = 0;
		    @valid_runtypes = ();
		    while (($valid_runtype) = $sth1R->fetchrow()) {
			if ($valid_runtype eq ''){
			    $valid_runtype = 'NULL';
			}
			$valid_runtypes[$num2] = $valid_runtype;
			$num2++;
		    }
		    $sth1R->finish;

		    #####################
		    #warn "RUNTYPES \n";
		    #warn @valid_runtypes;
		    #####################

		    foreach $next (@valid_runtypes){
			####################################################################
			# We should work out the protocols for this finisher/project/runtype
			####################################################################

			if ($next ne 'NULL'){
			    $statementPR = "
select unique seqchem.protocol
from finish_batch, finish_request, frequest_status, seqchem		    
where finish_batch.id_fbatch = finish_request.id_fbatch
and finish_request.id_frequest = frequest_status.id_frequest
and finish_request.id_seqchem = seqchem.id_seqchem
and finish_batch.ID_TEAM_FINSERV = \'$ID_TEAM_FINSERV\'
and finish_batch.finisher = \'$id_finisher\'
and finish_batch.projectname = \'$this_proj\'
and finish_request.id_runtype = \'$next\'
and frequest_status.status = 1 
and frequest_status.iscurrent = 1
";
			} else {
			$statementPR = "
select unique seqchem.protocol
from finish_batch, finish_request, frequest_status, seqchem		    
where finish_batch.id_fbatch = finish_request.id_fbatch
and finish_request.id_frequest = frequest_status.id_frequest
and finish_request.id_seqchem = seqchem.id_seqchem
and finish_batch.ID_TEAM_FINSERV = \'$ID_TEAM_FINSERV\'
and finish_batch.finisher = \'$id_finisher\'
and finish_batch.projectname = \'$this_proj\'
and finish_request.id_runtype is NULL
and frequest_status.status = 1 
and frequest_status.iscurrent = 1
";    
		    }
			

			$sth1PR = $dbh->prepare($statementPR) || die "ERROR:$DBI::errstr";
			$sth1PR->execute || die "ERROR:$DBI::errstr";
			$num3 = 0;
			@valid_protocols  = ();
			while (($valid_protocol) = $sth1PR->fetchrow()) {
			$valid_protocols[$num3] = $valid_protocol;
			$num3++;
		    }
			$sth1PR->finish;

			##############################
			# Needs to be foreach protocol
			##############################

			#warn "valid_protocols\n";
			#warn @valid_protocols;

			foreach $protocol (@valid_protocols){

			    $num = 0;


				if ($protocol =~ /1/){
###############################################################################################################
###################### Need statements for NON PRIMER requests ################################################

				    if ($next eq 'NULL') {
					$statementB = "
select finish_request.* 
from finish_request, finish_batch, frequest_status, seqchem 
where finish_request.id_fbatch = finish_batch.id_fbatch 
and finish_request.id_frequest = frequest_status.id_frequest
and seqchem.id_seqchem = finish_request.id_seqchem 
and seqchem.protocol = \'$protocol\'
and finish_request.id_runtype is NULL 
and finish_request.id_primer is NULL
and frequest_status.status = 1 
and frequest_status.iscurrent = 1 
and finish_batch.projectname = \'$this_proj\' 
and finish_batch.finisher = \'$id_finisher\' 
and finish_batch.ID_TEAM_FINSERV = \'$ID_TEAM_FINSERV\'
order by finish_request.platename, finish_request.rowletter, finish_request.colnum, finish_request.iteration
";

				    } else {
					$statementB = "
select finish_request.* 
from finish_request, finish_batch, frequest_status, seqchem
where finish_request.id_fbatch = finish_batch.id_fbatch 
and finish_request.id_frequest = frequest_status.id_frequest 
and seqchem.id_seqchem = finish_request.id_seqchem
and seqchem.protocol = \'$protocol\'
and finish_request.id_runtype = \'$next'\ 
and finish_request.id_primer is NULL
and frequest_status.status = 1 
and frequest_status.iscurrent = 1 
and finish_batch.projectname = \'$this_proj\' 
and finish_batch.finisher = \'$id_finisher\' 
and finish_batch.ID_TEAM_FINSERV = \'$ID_TEAM_FINSERV\'
order by finish_request.platename, finish_request.rowletter, finish_request.colnum, finish_request.iteration
";
				    }
###############################################################################################################
###################### ...and statements for PRIMER requests ##################################################
				} elsif ($protocol =~ /2/){

#-------------------------------------------------------------------------------------------------------------
# if $primersort is "yes" then we should order by ID.PRIMER before sorting by platename 
#-------------------------------------------------------------------------------------------------------------

				    if ($primersort eq "yes"){

					if ($next eq 'NULL') {
					    $statementB = "
select finish_request.* 
from finish_request, finish_batch, frequest_status, primer_status, seqchem
where finish_request.id_fbatch = finish_batch.id_fbatch 
and finish_request.id_frequest = frequest_status.id_frequest
and finish_request.id_primer = primer_status.id_primer
and seqchem.id_seqchem = finish_request.id_seqchem
and seqchem.protocol = \'$protocol\'
and finish_request.id_runtype is NULL 
and finish_request.id_primer is not NULL
and primer_status.status = 4
and primer_status.iscurrent = 1
and frequest_status.status = 1 
and frequest_status.iscurrent = 1 
and finish_batch.projectname = \'$this_proj\' 
and finish_batch.finisher = \'$id_finisher\' 
and finish_batch.ID_TEAM_FINSERV = \'$ID_TEAM_FINSERV\'
order by finish_request.id_primer, finish_request.platename, finish_request.rowletter, finish_request.colnum, finish_request.iteration
";

					} else {
					    $statementB = "
select finish_request.* 
from finish_request, finish_batch, frequest_status, primer_status, seqchem
where finish_request.id_fbatch = finish_batch.id_fbatch 
and finish_request.id_frequest = frequest_status.id_frequest
and finish_request.id_primer = primer_status.id_primer
and seqchem.id_seqchem = finish_request.id_seqchem 
and seqchem.protocol = \'$protocol\'
and finish_request.id_runtype = \'$next\' 
and finish_request.id_primer is not NULL
and primer_status.status = 4
and primer_status.iscurrent = 1
and frequest_status.status = 1 
and frequest_status.iscurrent = 1 
and finish_batch.projectname = \'$this_proj\' 
and finish_batch.finisher = \'$id_finisher\' 
and finish_batch.ID_TEAM_FINSERV = \'$ID_TEAM_FINSERV\'
order by finish_request.id_primer, finish_request.platename, finish_request.rowletter, finish_request.colnum, finish_request.iteration
";
					}
				    }
				    elsif ($primersort eq "no"){
					if ($next eq 'NULL') {
					    $statementB = "
select finish_request.* 
from finish_request, finish_batch, frequest_status, primer_status, seqchem

where finish_request.id_fbatch = finish_batch.id_fbatch 
and finish_request.id_frequest = frequest_status.id_frequest
and finish_request.id_primer = primer_status.id_primer
and seqchem.id_seqchem = finish_request.id_seqchem

and seqchem.protocol = \'$protocol\'
and finish_request.id_runtype is NULL 
and finish_request.id_primer is not NULL
and primer_status.status = 4
and primer_status.iscurrent = 1
and frequest_status.status = 1 
and frequest_status.iscurrent = 1 
and finish_batch.projectname = \'$this_proj\' 
and finish_batch.finisher = \'$id_finisher\' 
and finish_batch.ID_TEAM_FINSERV = \'$ID_TEAM_FINSERV\'
order by finish_request.platename, finish_request.rowletter, finish_request.colnum, finish_request.iteration
";

					} else {
					    $statementB = "
select finish_request.* 
from finish_request, finish_batch, frequest_status, primer_status, seqchem
where finish_request.id_fbatch = finish_batch.id_fbatch 
and finish_request.id_frequest = frequest_status.id_frequest
and finish_request.id_primer = primer_status.id_primer
and seqchem.id_seqchem = finish_request.id_seqchem
and seqchem.protocol = \'$protocol\'
and finish_request.id_runtype = \'$next\' 
and finish_request.id_primer is not NULL
and primer_status.status = 4
and primer_status.iscurrent = 1
and frequest_status.status = 1 
and frequest_status.iscurrent = 1 
and finish_batch.projectname = \'$this_proj\' 
and finish_batch.finisher = \'$id_finisher\' 
and finish_batch.ID_TEAM_FINSERV = \'$ID_TEAM_FINSERV\'
order by finish_request.platename, finish_request.rowletter, finish_request.colnum, finish_request.iteration
";
					}
				    }
				}
###############################################################################################################
###############################################################################################################

			    $sth1B = $dbh->prepare($statementB) || die "ERROR:$DBI::errstr";

			    #warn "$statementB\n";

			    #warn "Executing grab statement !!!\n";
			    #warn "$statementB\n\n";
			    $sth1B->execute || die "ERROR:$DBI::errstr";
			    #warn "Done grab !\n";

				while (($ID_FREQUEST,$ID_FBATCH,$PLATENAME,$ROWLETTER,$COLNUM,$ID_SEQCHEM,$ID_RUNTYPE,$ITERATION,$ID_PRIMER) = $sth1B->fetchrow()) {
				    				    
                                # If we have an ID_PRIMER value then we need PRIMERNAME and PRIMERSEQ from PRIMER
				    if (defined $ID_PRIMER){
					$statement_olname = "select PRIMERNAME from primer where ID_PRIMER = \'$ID_PRIMER\'";
					$sth_olname = $dbh->prepare($statement_olname) || die "ERROR:$DBI::errstr";
					#warn "$statement_olname\n";

					$sth_olname->execute || die "ERROR:$DBI::errstr";
					$OLIGO = $sth_olname->fetchrow();
					$sth_olname->finish;
					$statement_olseq = "select PRIMERSEQ from primer where ID_PRIMER = \'$ID_PRIMER\'";
					$sth_olseq = $dbh->prepare($statement_olseq) || die "ERROR:$DBI::errstr";
					#warn "$statement_olseq\n";

					$sth_olseq->execute || die "ERROR:$DBI::errstr";
					$SEQ = $sth_olseq->fetchrow();
					$sth_olseq->finish;
				    }


				    ####################################
				    @info = ("$ID_FREQUEST");
				    ####################################
				    $requests[$num] = [@info];
				    $num++;
				}
			    
				$sth1B->finish;

                                # Here samples are changed from created to assigned via $ID_FREQUEST in @updates
				# At this point we need to make a title for the block in question
				$validnumber = (@requests);
				if ($validnumber > 0) {
				    # FIND OUT projectname >?<
				    # present in $this_proj
				    #warn "debug -> $this_proj\n";
				    # FIND OUT who the block is for >?<
				    $statementper = "

select person.email
from team_person_role, person 
where team_person_role.id_person = person.id_person
and team_person_role.id_role = \'$id_finisher\'

";
				    $sthper = $dbh->prepare($statementper) || die "ERROR:$DBI::errstr";
				    #warn "$statementper\n";

				    $sthper->execute || die "ERROR:$DBI::errstr";
				    $email = $sthper->fetchrow();
				    $sthper->finish;
				    # HOW MANY VALID SAMPLES ARE THERE IN THIS SET ?
				    # get this from $validnumber
				    # PROTOCOL >?<
				    # RUNTYPE >?<
				    $title = "$this_proj"."__"."$email"."__"."$validnumber"."__"."$protocol"."$next";
				    # We need to make a data structure to send back to owl.

				    $iter = {
					"block" => "$title",
					"contents" => [@requests]
					};
				    push @datacollector, $iter;
				    
				}
			    @requests = ();
			    @updates = ();
			}
		    }
		}
	    }
	}
    }

    $dbh->disconnect;
    return ($batchnum, \@datacollector);

}				# end of sub get_blocks

1;

