#######################################################################
# 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.                #
# THESE FUNCTIONS allow Finger printers and finishers to edit their   #
# preparation requests                                                #
#                                                                     #
#######################################################################




package Review;
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 that have been actioned.

$pkg->{ListSQL} = "	SELECT 	CHROMOSOMEDICT.SPECIESNAME,CHROMOSOMEDICT.CHROMOSOME
			FROM 	GLYSTOCK, DNAPREP_REQUEST, DNAREQUEST_STATUS, CHROMOSOMEDICT, CLONE
			WHERE 	(CHROMOSOMEDICT.ID_DICT 	= CLONE.CHROMOSOME)		AND
				(CLONE.CLONENAME		= GLYSTOCK.CLONENAME)		AND
				(GLYSTOCK.ID_GLYSTOCK		= DNAPREP_REQUEST.ID_GLYSTOCK)	AND
				(	DNAREQUEST_STATUS.STATUS 		< 4 OR				
					DNAREQUEST_STATUS.STATUS 		> 5)		AND
				(DNAREQUEST_STATUS.ISCURRENT	= 1)				AND
				(DNAREQUEST_STATUS.ID_REQUEST	= DNAPREP_REQUEST.ID_REQUEST)";

# Only show finishers requests.				
if($pkg->{Roletype} =~ /^Finishing/)
{			
$pkg->{ListSQL} = "	$pkg->{ListSQL} AND
			(DNAPREP_REQUEST.REQUESTED_BY	= $pkg->{Role})";
}
# Caution people with multiple roles can see extra preps due to their other role.

		
$pkg->{ListSQL} = "	$pkg->{ListSQL} GROUP BY CHROMOSOMEDICT.SPECIESNAME,CHROMOSOMEDICT.CHROMOSOME";


$pkg->{ResultSQL} = "	SELECT	CLONE.CLONENAME, 
				DNAPREP_REQUEST.ID_REQUEST, 
				DNAREQUEST_STATUS.STATUS,
				GLYSTOCK.ID_GLYSTOCK, 
				DNAPREPREQSTATUSDICT.DESCRIPTION,
				DNAPREP_REQUEST.REQUEST_DATE, 
				DNAPREP_REQUEST.IS_FASTTRACK,
				PERSON.EMAIL, 
				SEQREASONDICT.DESCRIPTION
			FROM	DNAPREP_REQUEST, DNAREQUEST_STATUS, CLONE, CHROMOSOMEDICT, GLYSTOCK,
				DNAPREPREQSTATUSDICT, TEAM_PERSON_ROLE, PERSON, SEQREASONDICT
			WHERE	(CHROMOSOMEDICT.CHROMOSOME 		LIKE 'VAR1') 			AND 
				(CHROMOSOMEDICT.SPECIESNAME 		LIKE 'VAR2')			AND
				(SEQREASONDICT.ID_DICT 			= CLONE.SEQ_REASON)		AND 
				(CLONE.CHROMOSOME 			= CHROMOSOMEDICT.ID_DICT)	AND
				(CLONE.CLONENAME 			= GLYSTOCK.CLONENAME)		AND
				(PERSON.ID_PERSON			= TEAM_PERSON_ROLE.ID_PERSON)	AND
				(TEAM_PERSON_ROLE.ID_ROLE		= DNAPREP_REQUEST.REQUESTED_BY)	AND
				(GLYSTOCK.ID_GLYSTOCK 			= DNAPREP_REQUEST.ID_GLYSTOCK)	AND
				(DNAPREPREQSTATUSDICT.ID_DICT		= DNAREQUEST_STATUS.STATUS)	AND
				(DNAREQUEST_STATUS.ID_REQUEST 		= DNAPREP_REQUEST.ID_REQUEST)	AND
				(	DNAREQUEST_STATUS.STATUS 		< 4 OR				
					DNAREQUEST_STATUS.STATUS 		> 5)			AND
				(DNAREQUEST_STATUS.ISCURRENT 		= 1)				AND
				(DNAREQUEST_STATUS.ID_REQUEST		= DNAPREP_REQUEST.ID_REQUEST)";

# Only show finishers requests.				
if($pkg->{Roletype} =~ /^Finishing/)
{			
$pkg->{ResultSQL} = "	$pkg->{ResultSQL} AND
			(DNAPREP_REQUEST.REQUESTED_BY	= $pkg->{Role})";
}

$pkg->{ResultSQL} = "	$pkg->{ResultSQL} ORDER BY DNAPREP_REQUEST.REQUEST_DATE, DNAREQUEST_STATUS.STATUS DESC, CLONE.CLONENAME"; 



$pkg->{Button1Label}	= 'Change Priority';
$pkg->{Duplicatecheck}	= 1;

$pkg->{Columnheaders} = "Clone";
while(length($pkg->{Columnheaders}) < 31)
	{$pkg->{Columnheaders} = "$pkg->{Columnheaders} ";}
	
$pkg->{Columnheaders} = "$pkg->{Columnheaders} | Request Date";
while(length($pkg->{Columnheaders}) < 47)
	{$pkg->{Columnheaders} = "$pkg->{Columnheaders} ";}


$pkg->{Columnheaders} = "$pkg->{Columnheaders} | Request Status";
while(length($pkg->{Columnheaders}) < 65)
	{$pkg->{Columnheaders} = "$pkg->{Columnheaders} ";}
	
$pkg->{Columnheaders} = "$pkg->{Columnheaders} | Seq Reason";
while(length($pkg->{Columnheaders}) < 81)
	{$pkg->{Columnheaders} = "$pkg->{Columnheaders} ";}
	
$pkg->{Columnheaders} = "$pkg->{Columnheaders} | Request By";
while(length($pkg->{Columnheaders}) < 97)
	{$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,29,'Clonename'],[5,14,'Request Date'],[4,16,'Request Status'],[8,14,'Sequence Reason'],[7,13,'Requested By']];
$pkg->{Sort_by} = 'Default';
			
undef $pkg->{Selection};
		
$pkg->Addtobrowse();
undef @{$pkg->{Contents}};
$pkg->Refresh();
$pkg->{Title}->configure(-text => "Operator : $pkg->{Who}\tRole Type : $pkg->{Roletype}\t\tREVIEW YOUR DNAprep Requests");
			

}



sub Lock
{
my $pkg = shift;
my $sql;

$sql = "	SELECT 	DNAPREP_REQUEST.ID_REQUEST FROM DNAPREP, DNAPREP_REQUEST, GLYSTOCK, DNAPREP_STATUS, DNAREQUEST_STATUS, CLONE_CULTURE
		WHERE	(DNAPREP_STATUS.ISCURRENT	= 1)				AND
			(DNAPREP_STATUS.ID_PREP		= DNAPREP.ID_PREP)		AND
			(DNAPREP.ID_CULTURE		= CLONE_CULTURE.ID_CULTURE)	AND
			(CLONE_CULTURE.ID_GLYSTOCK	= GLYSTOCK.ID_GLYSTOCK)		AND
			(GLYSTOCK.ID_GLYSTOCK		= DNAPREP_REQUEST.ID_GLYSTOCK)	AND
			(DNAREQUEST_STATUS.ISCURRENT	= 1)				AND
			(DNAREQUEST_STATUS.ID_REQUEST	= DNAPREP_REQUEST.ID_REQUEST)	AND
			(";

foreach(@{$pkg->{Data}})
{
	$sql = "$sql\nDNAPREP_REQUEST.ID_REQUEST 		= $_->[1] OR";
}
$sql = substr($sql,0,length($sql)-2);
$sql = "$sql)\nUNION";


$sql = "	SELECT 	DNAPREP_REQUEST.ID_REQUEST FROM DNAPREP_REQUEST, DNAREQUEST_STATUS,GLYSTOCK, CLONE_CULTURE
		WHERE	(GLYSTOCK.ID_GLYSTOCK		= DNAPREP_REQUEST.ID_GLYSTOCK)	AND
			(DNAREQUEST_STATUS.ISCURRENT	= 1)				AND
			(DNAREQUEST_STATUS.ID_REQUEST	= DNAPREP_REQUEST.ID_REQUEST)	AND
			(";

			
foreach(@{$pkg->{Data}})
{
	$sql = "$sql\nDNAPREP_REQUEST.ID_REQUEST 		= $_->[1] OR";
}
$sql = substr($sql,0,length($sql)-2);
$sql = "$sql)\nFOR UPDATE NOWAIT";



$sql = "	SELECT 	DNAPREP_REQUEST.ID_REQUEST
		FROM 	DNAPREP_REQUEST, DNAREQUEST_STATUS
		WHERE	DNAPREP_REQUEST.ID_REQUEST 	= DNAREQUEST_STATUS.ID_REQUEST AND 
		(";
	
foreach(@{$pkg->{Data}})
{
	$sql = "$sql\nDNAPREP_REQUEST.ID_REQUEST 		= $_->[1] 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 $resend; 

foreach (@{$pkg->{Contents}})
{
	if($_->[2] == 2)
	{
	my $chk;
	$pkg->Message("You Cannot Kill an ACTIONED Request !\n\nClone : $_->[0] Request ID: $_->[1]",\$chk);
	$pkg->{Mainwin}->waitVariable(\$chk);
	
	
	
	}
	else
	{
		if($_->[2] == 7)
		{
			if(!$resend){$resend = "REMINDER \n\nDNA Reception\nRequest That You Re-Send Glystock For the Following Clone(s)\n\n"; }
			$resend = "$resend\n$_->[0]";
		}
	
	
	$sql = "UPDATE 	DNAREQUEST_STATUS 
		SET 	ISCURRENT = 0 
		WHERE 	ISCURRENT = 1 AND ID_REQUEST = $_->[1]";
	
	push @multisql,$sql;
	$sql = "INSERT INTO 	DNAREQUEST_STATUS	(ID_REQUEST, STATUS, STATUSDATE, ISCURRENT, SESSIONID, PROGRAM, OPERATOR)
		VALUES					($_->[1] ,5,SYSDATE,1, $$, 'DODO','$pkg->{Who}')";
					
	push @multisql,$sql;
	$sql = "UPDATE GLYSTOCK
		SET	IS_AVAILABLE 	= 0
		WHERE	ID_GLYSTOCK 	= $_->[3]";
		
	if($_->[2] == 7){push @multisql,$sql;}#resend so kill off old glystock
	
	$pkg->MultiWritedb(@multisql);
	undef @multisql;
	}
}

if($resend)
{
	open (MAIL,"|/usr/bin/mail $pkg->{Who}");
	print MAIL "From: DODO                <>\n";
	print MAIL "To: $pkg->{Who}\n";
	print MAIL "Subject: Resend Glystock\n\n";
	print MAIL $resend;
	close(MAIL);
}

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







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

eval{
# Go through selection
foreach (@{$pkg->{Contents}})
{		
	if($_->[2] == 7)
	{
	my $chk;
	$pkg->Message("You Cannot Change the Priority of a RESEND Request !\n\nClone : $_->[0] Request ID: $_->[1]",\$chk);
	$pkg->{Mainwin}->waitVariable(\$chk);
	next;
	}
	
	
	
	if($_->[6] == 0)
		{$_->[6] = 1;}
	else
		{$_->[6] = 0;}
	
	$sql = "UPDATE 	DNAPREP_REQUEST
		SET	IS_FASTTRACK = $_->[6] 
		WHERE 	ID_REQUEST = $_->[1]";

	push @multisql,$sql;
	
	if($_->[2] == 2)
	{# update related prep related priorities
	$sql = "UPDATE 	DNAPREP
		SET 	DNAPREP.IS_PRIORITY 		= $_->[6]
		WHERE	DNAPREP.ID_PREP IN 
			(SELECT DNAPREP.ID_PREP
			FROM	DNAPREP, CLONE_CULTURE, DNAPREP_STATUS
			WHERE	DNAPREP_STATUS.ISCURRENT	= 1				AND
				DNAPREP_STATUS.ID_PREP		= DNAPREP.ID_PREP		AND
				DNAPREP.ID_CULTURE	 	= CLONE_CULTURE.ID_CULTURE 	AND
				CLONE_CULTURE.ID_REQUEST 	= 12)";
	push @multisql,$sql
	}


}
};if($@){print STDERR "Function	: Review::Action\nRequest ID	: $_->[1]\n$@ \n";}

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







1;

