#!/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_maps_for_WP.pm,v 1.4 1998/09/23 08:49:33 dn1 Exp $
# $Log: owl_get_maps_for_WP.pm,v $
# Revision 1.4  1998/09/23  08:49:33  dn1
# *** empty log message ***
#
# Revision 1.3  1998/06/25  16:53:43  dn1
# *** empty log message ***
#
# Revision 1.2  1998/06/24  14:27:30  dn1
# *** empty log message ***
#

require Exporter;

use DBI;

@owl_get_maps_for_WP::ISA = qw(Exporter);
@owl_get_maps_for_WP::EXPORT = qw(get_maps_for_WP);

#use Data::Dumper;

package owl_get_maps_for_WP;

# DN1
###################################################################################
#Interrogate the database for maps in the database which have not been            #
#assigned to loading plates yet (for a paticular team) !> AT ALL <!               # 
###################################################################################
# Supply the module with $db_database, $db_user, $db_pass -> for db connection
# Supply                 $teamname                        -> the team in question

sub get_maps_for_WP {
    my($db_database, $db_user, $db_pass, $teamname) = @_;
    my @possible_maps = ();
    #warn "Inside get_maps_for_WP -> $db_database, $db_user, $db_pass, $teamname\n";
    # CONNECT TO THE DATABASE
    my $dbh = DBI->connect( $db_database, $db_user, $db_pass, 'Oracle') || die "ERROR: Could not connect to database: $DBI::errstr\n"; 
    ########################################################## 
    # What are the possible sources of map for this team >?< #
    ##########################################################
    my $statement = "
select ID_TEAM_FINSERV
from team_finservice
where TEAMNAME = \'$teamname\'
"; 
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr"; # 
    $sth1->execute || die "ERROR:$DBI::errstr";
    
    while (($ID_TEAM_FINSERV) = $sth1->fetchrow()){
	#warn "ID_TEAM_FINSERV ---> $ID_TEAM_FINSERV\n";
    ############################################################ 
    # We now want all of the maps for each $ID_TEAM_FINSERV    #
    # where none of the attached request_map entries have an   #
    # id_loadingsample entry and status is 'Printed'           #
    # ...or 'Robot Assigned'                                   #
    # >!< No partially assigned plates should be displayed >!< #
    ############################################################

	my $statementmap = "
select unique mapplate.id_mapplate, mapplate.protocol, mapplate.id_runtype, mapplate.plate_format 
from mapplate, request_map, mapplate_status
where mapplate.id_mapplate = mapplate_status.id_mapplate
and mapplate.id_mapplate = request_map.id_mapplate
and (mapplate_status.status = 2 or mapplate_status.status = 6)
and mapplate_status.iscurrent = 1
and mapplate.id_team_finserv = \'$ID_TEAM_FINSERV\'
and request_map.id_loadingsample is NULL
";


	my $sthmap = $dbh->prepare($statementmap) || die "ERROR:$DBI::errstr";
	$sthmap->execute || die "ERROR:$DBI::errstr";
	while (($ID_MAPPLATE, $PROTOCOL, $ID_RUNTYPE, $PLATE_FORMAT) = $sthmap->fetchrow()){
	    if ($ID_RUNTYPE eq ''){
		$ID_RUNTYPE = 'NULL';
	    }
	    my $return = "$ID_MAPPLATE"."_"."$PROTOCOL"."$ID_RUNTYPE"."_$PLATE_FORMAT";
	    push @possible_maps, $return;

	}
	$sthmap->finish;
    }

    $sth1->finish;
    $dbh->disconnect();
    return (@possible_maps);
}

1;

