#!/usr/local/bin/perl

# $Id: owl_quick_check.pm,v 1.3 1998/12/07 13:20:15 dn1 Exp $
# $Log: owl_quick_check.pm,v $
# Revision 1.3  1998/12/07  13:20:15  dn1
# Now gives the names of the projects which are waiting for primer arrivals (for the different views).
#
# Revision 1.2  1998/07/07  07:56:32  dn1
# if ($next =~ /^private/){$next = "$next". "     $who";}
#
# Revision 1.1  1998/06/29  15:33:22  dn1
# Initial revision
#

require Exporter;
use DBI;


@owl_quick_check::ISA = qw(Exporter);
@owl_quick_check::EXPORT = qw(quick_check);

package owl_quick_check;

# DN1
#################################################################################
# How many samples are waiting to be mapped for each of the available-views >?< 
# How many of these samples are awaiting the arrival of primers >?<             
# *NEW* Which projects are awaiting primers for each of the views >?<
#################################################################################

sub quick_check {
    my ($db_database, $db_user, $db_pass, $id_role, $who, $teamname, $possibles) = @_;
    my @possibles = @$possibles;
    my $dbh = DBI->connect( $db_database, $db_user, $db_pass, 'Oracle') || die "ERROR: Could not connect to database: $DBI::errstr\n";
    my $next;
    my $id;
    my @return;

    foreach $next (@possibles){

	if ($next =~ /_id_(\d+)/){
	    $id = $1;
	} else {
	    die "Cannot parse $next ! \n";
	}

	############################################################
	# If this one is for private orders, we should add who for #
	############################################################
	if ($next =~ /^private/){
	    $next = "$next". "     $who";
	}

	push @return, "*************************************************************";
	push @return, $next;
	push @return, "*************************************************************";
	####################### 
	# We want to count... #
	#######################
	# (a) The total number of requests waiting
	# ...need a different statement if this is a 'private count'
	my $statement;

	if ($next =~ /private/){
	    $statement = "
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 frequest_status.iscurrent = 1
and frequest_status.status = 1

and finish_batch.id_team_finserv = \'$id\'
and finish_batch.finisher = \'$id_role\'
";
	} else {
	    $statement = "
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 frequest_status.iscurrent = 1 
and frequest_status.status = 1

and finish_batch.id_team_finserv = \'$id\'
";
	}


	my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
	$sth->execute || die "ERROR:$DBI::errstr";
	my $count1 = $sth->fetchrow();
	$sth->finish;
	push @return, "Total number of requests awaiting mapping = $count1 \n";

        # (b) The total number which are awaiting the arrival of custom primers
	if ($next =~ /private/){
	    $statement = "
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.iscurrent = 1
and primer_status.status = 1

and frequest_status.iscurrent = 1 
and frequest_status.status = 1

and finish_batch.id_team_finserv = \'$id\'
and finish_batch.finisher = \'$id_role\'
";
    } else {
	$statement = "
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.iscurrent = 1
and primer_status.status = 1

and frequest_status.status = 1
and frequest_status.iscurrent = 1 
and finish_batch.id_team_finserv = \'$id\'
";
    }


	$sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
	$sth->execute || die "ERROR:$DBI::errstr";
	my $count2 = $sth->fetchrow();
	$sth->finish;
	push @return, "Of which $count2 require custom-primer delivery\n";

	# (c) The total number waiting which are ready to map
	# ...the remainder
	my $count3 = $count1 - $count2;
    	push @return, "and $count3 can be arrayed now.\n";
	push @return, "\n";

	####################
	# start of new code
	####################
	if ($count2 > 0){
	    push @return, "Projects requiring custom-primer delivery are\n";
	    if ($next =~ /private/){
		$statement = "
select unique primer.projectname
from finish_request, frequest_status, finish_batch, primer_status, primer
where finish_request.id_fbatch = finish_batch.id_fbatch 
and finish_request.id_frequest = frequest_status.id_frequest 
and primer.id_primer = primer_status.id_primer
and finish_request.id_primer is not NULL
and finish_request.id_primer = primer_status.id_primer

and primer_status.iscurrent = 1
and primer_status.status = 1

and frequest_status.iscurrent = 1 
and frequest_status.status = 1

and finish_batch.id_team_finserv = \'$id\'
and finish_batch.finisher = \'$id_role\'
";
	    } else {

	$statement = "
select unique primer.projectname 
from finish_request, frequest_status, finish_batch, primer_status, primer
where finish_request.id_fbatch = finish_batch.id_fbatch 
and finish_request.id_frequest = frequest_status.id_frequest
and primer.id_primer = primer_status.id_primer
and finish_request.id_primer is not NULL
and finish_request.id_primer = primer_status.id_primer

and primer_status.iscurrent = 1
and primer_status.status = 1

and frequest_status.status = 1
and frequest_status.iscurrent = 1 
and finish_batch.id_team_finserv = \'$id\'
";
    }
	    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
	    $sth->execute || die "ERROR:$DBI::errstr";
	    my $primer_project;
	    while ($primer_project = $sth->fetchrow()){
		push @return, "$primer_project \n";
	    }
	    push @return, "\n";

	    $sth->finish;
	    
	    ##################
	    # end of new code
	    ##################
	    

	}


    }
    




    $dbh->disconnect();
    return @return;

}

1;
