#######################################################################
# 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.                      #
#######################################################################
#                                                                     #
# THIS MODULE CONTAINS VARIOUS FUNCTIONS USED BY DODO.                #
# TThese functions allow fingerprinters or finishers to re request    #
# DNApreparations on glycerol stocks held in DNA reception            #
#                                                                     #
#######################################################################
package Rerequest;
use strict;


sub new
{
my $pkg = shift;
my $setup = {};
$setup->{Contents} = [];
bless $setup,$pkg;
return $setup;
}



sub Initialiseobject
{

my $pkg = shift;

# pull out speciesname and chromosome name of all clones glystocks that are idle.

$pkg->{ListSQL} = "	SELECT 	CHROMOSOMEDICT.SPECIESNAME,CHROMOSOMEDICT.CHROMOSOME
			FROM	DNAREQUEST_STATUS, GLYSTOCK, DNAPREP_REQUEST, CLONE, CLONEPLATE, CHROMOSOMEDICT,
				CLONEPLATE_STATUS, CLONEWELL
			WHERE	(CHROMOSOMEDICT.ID_DICT 		= CLONE.CHROMOSOME)			AND
				(CLONE.CLONENAME			= GLYSTOCK.CLONENAME)			AND
				(CLONEPLATE_STATUS.STATUS		= 3)					AND
				(CLONEPLATE.ID_CLONEPLATE		= CLONEPLATE_STATUS.ID_CLONEPLATE)	AND
				(CLONEPLATE.ID_CLONEPLATE		= CLONEWELL.ID_CLONEPLATE)		AND
				(CLONEWELL.ID_GLYSTOCK			= GLYSTOCK.ID_GLYSTOCK)			AND
				DNAPREP_REQUEST.ID_REQUEST		= DNAREQUEST_STATUS.ID_REQUEST		AND
				DNAREQUEST_STATUS.STATUS 		IN (4,5)				AND
				DNAREQUEST_STATUS.ISCURRENT		= 1					AND
				DNAPREP_REQUEST.ID_GLYSTOCK		=	GLYSTOCK.ID_GLYSTOCK		AND
				GLYSTOCK.CLONENAME    NOT IN	      (SELECT GLYSTOCK.CLONENAME
				FROM	DNAREQUEST_STATUS, GLYSTOCK, DNAPREP_REQUEST
				WHERE	GLYSTOCK.ID_GLYSTOCK		= DNAPREP_REQUEST.ID_GLYSTOCK		AND
				DNAPREP_REQUEST.ID_REQUEST		= DNAREQUEST_STATUS.ID_REQUEST		AND
				DNAREQUEST_STATUS.STATUS 		IN (1,2,3,6,7)				AND
				DNAREQUEST_STATUS.ISCURRENT		= 1
				)
			GROUP BY CHROMOSOMEDICT.SPECIESNAME,CHROMOSOMEDICT.CHROMOSOME";
	
$pkg->{ResultSQL} ="	SELECT	CLONE.CLONENAME, 
				CLONEPLATE.ID_CLONEPLATE, 
				CLONEPLATE.DESCRIPTION,
				CHROMOSOMEDICT.SPECIESNAME, 
				CHROMOSOMEDICT.CHROMOSOME,
				0 AS PRIORITY,
				CLONEPLATE_STATUS.STATUSDATE,
				GLYSTOCK.ID_GLYSTOCK
			FROM	DNAREQUEST_STATUS, GLYSTOCK, DNAPREP_REQUEST, CLONE, CLONEPLATE, CHROMOSOMEDICT,
				CLONEPLATE_STATUS, CLONEWELL
			WHERE	(CHROMOSOMEDICT.CHROMOSOME 	LIKE 'VAR1') 					AND 
				(CHROMOSOMEDICT.SPECIESNAME 	LIKE 'VAR2')					AND
				(CHROMOSOMEDICT.ID_DICT 		= CLONE.CHROMOSOME)			AND
				(CLONE.CLONENAME			= GLYSTOCK.CLONENAME)			AND
				(CLONEPLATE_STATUS.STATUS		= 3)					AND
				(CLONEPLATE.ID_CLONEPLATE		= CLONEPLATE_STATUS.ID_CLONEPLATE)	AND
				(CLONEPLATE.ID_CLONEPLATE		= CLONEWELL.ID_CLONEPLATE)		AND
				(CLONEWELL.ID_GLYSTOCK			= GLYSTOCK.ID_GLYSTOCK)			AND
				DNAPREP_REQUEST.ID_REQUEST		= DNAREQUEST_STATUS.ID_REQUEST		AND
				DNAREQUEST_STATUS.STATUS 		IN (4,5)				AND
				DNAREQUEST_STATUS.ISCURRENT		= 1					AND
				DNAPREP_REQUEST.ID_GLYSTOCK		=	GLYSTOCK.ID_GLYSTOCK		AND
				GLYSTOCK.CLONENAME    NOT IN	      (SELECT GLYSTOCK.CLONENAME
				FROM	DNAREQUEST_STATUS, GLYSTOCK, DNAPREP_REQUEST	
				WHERE	GLYSTOCK.ID_GLYSTOCK			= DNAPREP_REQUEST.ID_GLYSTOCK		AND
					DNAPREP_REQUEST.ID_REQUEST		= DNAREQUEST_STATUS.ID_REQUEST		AND
					DNAREQUEST_STATUS.STATUS 		IN (1,2,3,6,7)				AND
					DNAREQUEST_STATUS.ISCURRENT		= 1
			)";


$pkg->{Button1Label}	= 'Re Request';
$pkg->{Duplicatecheck}	= 7;
				
$pkg->{Columnheaders} = "Clone";
while(length($pkg->{Columnheaders}) < 17)
	{$pkg->{Columnheaders} = "$pkg->{Columnheaders} ";}
	
$pkg->{Columnheaders} = "$pkg->{Columnheaders} | Plate";
while(length($pkg->{Columnheaders}) < 27)
	{$pkg->{Columnheaders} = "$pkg->{Columnheaders} ";}


$pkg->{Columnheaders} = "$pkg->{Columnheaders} | Description";
while(length($pkg->{Columnheaders}) < 52)
	{$pkg->{Columnheaders} = "$pkg->{Columnheaders} ";}
	
$pkg->{Columnheaders} = "$pkg->{Columnheaders} | Species";
while(length($pkg->{Columnheaders}) < 74)
	{$pkg->{Columnheaders} = "$pkg->{Columnheaders} ";}

$pkg->{Columnheaders} = "$pkg->{Columnheaders} | Chr";
while(length($pkg->{Columnheaders}) < 82)
	{$pkg->{Columnheaders} = "$pkg->{Columnheaders} ";}
	
$pkg->{Columnheaders} = "$pkg->{Columnheaders} | Date Recieved";
while(length($pkg->{Columnheaders}) < 100)
	{$pkg->{Columnheaders} = "$pkg->{Columnheaders} ";}

	
	
$pkg->{Columnheaders} = "$pkg->{Columnheaders}|";
# [a,b] a = content array index, b = width of field to display -1
# Internal column headings = sum b + 2 end one + 3
$pkg->{Column} =
[[0,15,'Clone'],[1,8,'Plate'],[2,23,'Description'],[3,20,'Species'],[4,6,'Chromosome'],[6,15,'Date Recieved']];		
undef $pkg->{Selection};
		
$pkg->Addtobrowse();
undef @{$pkg->{Contents}};
$pkg->Refresh();
$pkg->{Title}->configure(-text => "Operator : $pkg->{Who}\tRole Type : $pkg->{Roletype}\t\tRe Request DNAprep Using Existing Glystocks");

}


sub Lock
{
my $pkg = shift;
my $sql;
$sql = "	SELECT 	ID_GLYSTOCK FROM GLYSTOCK
		WHERE	(";
			
foreach(@{$pkg->{Data}})
{
	$sql = "$sql\nGLYSTOCK.ID_GLYSTOCK 		= $_->[7] OR";
}
$sql = substr($sql,0,length($sql)-2);
$sql = "$sql)\nFOR UPDATE NOWAIT";


return $sql;


}	

sub Killprep
{
my $pkg = shift;
my $sql;
my @multisql;
my $chk;

$pkg->Message("FINAL WARNING\n\nAre You Really Sure You Want To Reject These Clone Glystocks ?",\$chk,'reject');
$pkg->{Mainwin}->waitVariable(\$chk);
if($chk == 0)
	{return;}


eval{
foreach (@{$pkg->{Contents}})
{
$sql = "UPDATE 	GLYSTOCK
	SET 	IS_AVAILABLE = 0
	WHERE	ID_GLYSTOCK = $_->[3]";
	
push @multisql,$sql;

}


$pkg->MultiWritedb(@multisql);
};
if($@ =~ /ORA-00054:/)
{	my $chk;
	$pkg->Message("LOCK OUT\n\nData Currently Being Accessed By another user\n\nContact Bug If Problem persists",\$chk);
	$pkg->{Mainwin}->waitVariable(\$chk);
	$pkg->{Query1} = "Another User Is Accessing $pkg->{Query1} Try Again Later";
	undef @{$pkg->{Data}};
	return;
}
if($@)
{
	print STDERR "$@ \n"; return;
}
undef @{$pkg->{Contents}};
$pkg->Refresh();
$pkg->Addtobrowse();
$pkg->Addtoresults();
}







sub Action
{
my $pkg = shift;
my $sql;
my @multisql;
my $ref;
my $chk;
my $req;

eval{

foreach (@{$pkg->{Contents}})
{		
	$sql = "	INSERT INTO DNAPREP_REQUEST	(ID_REQUEST, ID_GLYSTOCK, REQUESTED_BY,REQUEST_DATE,IS_FASTTRACK)
			VALUES				(SEQ_DPREQUEST.NEXTVAL,'$_->[7]','$pkg->{Role}',SYSDATE,'1')";
	
	push @multisql, $sql;

	$sql = "	INSERT INTO DNAREQUEST_STATUS	(ID_REQUEST, STATUS, STATUSDATE, ISCURRENT,SESSIONID, PROGRAM, OPERATOR)
			VALUES				(SEQ_DPREQUEST.CURRVAL,'6',SYSDATE,'1',$$, 'DODO','$pkg->{Who}')";
	push @multisql, $sql;
$req++;
}


$pkg->MultiWritedb(@multisql);
};
if($@ =~ /ORA-00054:/)
{	my $chk;
	$pkg->Message("LOCK OUT\n\nData Currently Being Accessed By another user\n\nContact Bug If Problem persists",\$chk);
	$pkg->{Mainwin}->waitVariable(\$chk);
	$pkg->{Query1} = "Another User Is Accessing $pkg->{Query1} Try Again Later";
	undef @{$pkg->{Data}};
	return;
}
if($@)
{
	print STDERR "$@ \n"; return;
}
if($req)
{
$pkg->Message("Do You Want To Print A Copy Of Your Requests ?",\$chk,"Prep Requests \n");	

$pkg->{Mainwin}->waitVariable(\$chk);
if($chk == 1)
{
	undef $chk;
	$pkg->Setprinter("Print request",\$chk);
	$pkg->{Mainwin}->waitVariable(\$chk);
	if($chk == 0)
		{return;}
	$ref = $pkg->Querydb("SELECT SEQ_DPREQUEST.CURRVAL FROM DUAL");
	print "$ref->[0][0] x $req\n";
	$req--;
	$req = $ref->[0][0] - $req;
	
	$pkg->Printrequest("$req-$ref->[0][0]");
	#$pkg->Printrequest("29-31");
}
}

undef @{$pkg->{Contents}};
$pkg->Refresh();
$pkg->Addtobrowse();
$pkg->Addtoresults();
}








1;

