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

require Exporter;

# $Id: owl_get_maps_for_internal.pm,v 1.11 1998/12/14 10:17:10 dn1 Exp $
# $Log: owl_get_maps_for_internal.pm,v $
# Revision 1.11  1998/12/14  10:17:10  dn1
# *** empty log message ***
#
# Revision 1.10  1998/12/14  10:01:16  dn1
# *** empty log message ***
#
# Revision 1.9  1998/12/08  10:45:56  dn1
# added desc to an order
#
# Revision 1.8  1998/12/07  14:40:36  dn1
# plate_count gets returned instead of plate_format
#
# Revision 1.7  1998/10/21  12:14:08  dn1
# changes to allow viewing of maps from different sources
#

use DBI;

@owl_get_maps_for_internal::ISA = qw(Exporter);
@owl_get_maps_for_internal::EXPORT = qw(get_maps_for_internal);

package owl_get_maps_for_internal;

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

sub get_maps_for_internal {
    #warn "Searching ---> for status-2 6 \n";
    my($db_database, $db_user, $db_pass, $teamname) = @_;
    my @possible_maps = ();
    #warn "Inside get_maps_for_internal -> $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()){
    ############################################################ 
    # We now want all of the maps for each $ID_TEAM_FINSERV    #
    # where any  of the attached request_map entries have an   #
    # id_loadingsample entry of NULL (ie. not yet complately   #
    # assigned to a gel.)                                      #    
    # and status is 'Printed'                                  #
    # ...or status is 'Robot Assigned'                         #
    # 
    ############################################################	

	my $statementmap = "
select unique mapplate.id_mapplate, mapplate.protocol, mapplate.id_runtype, mapplate.plate_format,  mapplate_status.STATUSDATE
from mapplate, request_map, mapplate_status
where request_map.id_loadingsample is NULL
and 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\'
order by mapplate.id_mapplate desc
";
	my $sthmap = $dbh->prepare($statementmap) || die "ERROR:$DBI::errstr";
	$sthmap->execute || die "ERROR:$DBI::errstr";
	while (($ID_MAPPLATE, $PROTOCOL, $ID_RUNTYPE, $PLATE_FORMAT, $STATUSDATE) = $sthmap->fetchrow()){
	    if ($ID_RUNTYPE eq ''){
		$ID_RUNTYPE = 'NULL';
	    }

	    ############################################
	    # Work out the number of samples on the map
	    ############################################
	    my $cstatement = "
select count(id_frequest)
from request_map
where id_mapplate = \'$ID_MAPPLATE\'
and id_loadingsample is NULL
";
	    my $csth1 = $dbh->prepare($cstatement) || die "ERROR:$DBI::errstr";
	    $csth1->execute || die "ERROR:$DBI::errstr";
	    my $plate_count = $csth1->fetchrow();
	    $csth1->finish;

	    my $return = "$ID_MAPPLATE"."_"."$PROTOCOL"."$ID_RUNTYPE"."_"."$plate_count";
   	    #my $return = "$ID_MAPPLATE"."_"."$PROTOCOL"."$ID_RUNTYPE"."_"."$PLATE_FORMAT";
	    
	    push @possible_maps, $return;

	}
	$sthmap->finish;
    }

    $sth1->finish;
    $dbh->disconnect();

    return (@possible_maps);

}


#############################################################
# This second sub uses a status 9 instead; ie, Ready to load
#############################################################
sub get_maps_for_internal2 {
    #warn "Searching ---> for status-9 \n";

    my($db_database, $db_user, $db_pass, $teamname) = @_;
    my @possible_maps = ();
    #warn "Inside get_maps_for_internal -> $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()){
    ############################################################ 
    # We now want all of the maps for each $ID_TEAM_FINSERV    #
    # where any  of the attached request_map entries have an   #
    # id_loadingsample entry of NULL (ie. not yet complately   #
    # assigned to a gel.)                                      #    
    # and status is 'Printed'                                  #
    # ...or status is 'Robot Assigned'                         #
    # 
    ############################################################	

	my $statementmap = "
select unique mapplate.id_mapplate, mapplate.protocol, mapplate.id_runtype, mapplate.plate_format,  mapplate_status.STATUSDATE
from mapplate, request_map, mapplate_status
where request_map.id_loadingsample is NULL
and mapplate.id_mapplate = mapplate_status.id_mapplate
and mapplate.id_mapplate = request_map.id_mapplate
and (mapplate_status.status = 9 or mapplate_status.status = 3)
and mapplate_status.iscurrent = 1
and mapplate.id_team_finserv = \'$ID_TEAM_FINSERV\'
order by mapplate.id_mapplate desc
";
	my $sthmap = $dbh->prepare($statementmap) || die "ERROR:$DBI::errstr";
	$sthmap->execute || die "ERROR:$DBI::errstr";
	while (($ID_MAPPLATE, $PROTOCOL, $ID_RUNTYPE, $PLATE_FORMAT, $STATUSDATE) = $sthmap->fetchrow()){
	    if ($ID_RUNTYPE eq ''){
		$ID_RUNTYPE = 'NULL';
	    }

	    ############################################
	    # Work out the number of samples on the map
	    ############################################
	    my $cstatement = "
select count(id_frequest)
from request_map
where id_mapplate = \'$ID_MAPPLATE\'
and id_loadingsample is NULL
";
	    my $csth1 = $dbh->prepare($cstatement) || die "ERROR:$DBI::errstr";
	    $csth1->execute || die "ERROR:$DBI::errstr";
	    my $plate_count = $csth1->fetchrow();
	    $csth1->finish;

	    my $return = "$ID_MAPPLATE"."_"."$PROTOCOL"."$ID_RUNTYPE"."_"."$plate_count";
   	    #my $return = "$ID_MAPPLATE"."_"."$PROTOCOL"."$ID_RUNTYPE"."_"."$PLATE_FORMAT";
	    
	    push @possible_maps, $return;

	}
	$sthmap->finish;
    }

    $sth1->finish;
    $dbh->disconnect();

    return (@possible_maps);

}

1;
