#######################################################################
# 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.                #
# THIS IS THE MODULE CONTROLS THE STREAKING OF NEW REQUESTS           #
#                                       #
#                                                                     #
#######################################################################
package Streak;
use strict;

sub new
{
my $pkg = shift;
my $setup = {};


$setup->{Contents} = [];
bless $setup,$pkg;
}



sub Initialiseobject
{

my $pkg = shift;
$pkg->{ListSQL} =  "	SELECT 	UNIQUE CD.SPECIESNAME,CD.CHROMOSOME 
			FROM 	CHROMOSOMEDICT CD, CLONE C, GLYSTOCK G, DNAPREP_REQUEST DR, 
				DNAREQUEST_STATUS DS, PROJECT_STATUS PS, CLONE_PROJECT CPP
			WHERE	CD.ID_DICT 		= C.CHROMOSOME
			AND	(PS.STATUS		< 22
			OR	PS.STATUS		= 31
			OR	PS.STATUS		= 30)
			AND	PS.ISCURRENT		= 1
			AND	PS.PROJECTNAME		= CPP.PROJECTNAME
			AND	CPP.CLONENAME		= C.CLONENAME
			AND	C.CLONENAME		= G.CLONENAME
			AND	G.ID_GLYSTOCK 		= DR.ID_GLYSTOCK 
			AND	DR.REQUEST_DATE		< SYSDATE
			AND 	DR.ID_REQUEST 		= DS.ID_REQUEST
			AND	DS.STATUS 		IN (1,3,6) 
			AND	DS.ISCURRENT		= 1";
			
			
$pkg->{ResultSQL} = "	SELECT	C.CLONENAME, 
				G.ID_GLYSTOCK, 
				DR.ID_REQUEST, 
				DR.REQUEST_DATE, 
				P.EMAIL, 
				DS.STATUS, 
				SD.DESCRIPTION,
				CP.PRIORITY-1, 
				CL.DESCRIPTION,
				DD.DESCRIPTION
			FROM	DNAPREP_REQUEST DR, GLYSTOCK G, CLONE C, 
				CHROMOSOMEDICT CD,CLONE_PRIORITY CP, DNAREQUEST_STATUS DS, 
				DNAPREPREQSTATUSDICT DD, TEAM_PERSON_ROLE T, PERSON P, CLONE_PROJECT CPP,
				SEQREASONDICT SD, PROJECT_STATUS PS, CLONEWELL CW, CLONEPLATE CL
			WHERE	P.ID_PERSON		= T.ID_PERSON
			AND	T.ID_ROLE		= DR.REQUESTED_BY
			AND	SD.ID_DICT		= C.SEQ_REASON
			AND	CL.ID_CLONEPLATE	= CW.ID_CLONEPLATE
			AND	CW.ID_GLYSTOCK		= G.ID_GLYSTOCK
			AND	DD.ID_DICT		= DS.STATUS
			AND	CP.IS_CURRENT		= 1
			AND	CP.CLONENAME		= C.CLONENAME 
			AND	(PS.STATUS		< 22
			OR	PS.STATUS		= 31
			OR	PS.STATUS		= 30)
			AND	PS.ISCURRENT		= 1
			AND	PS.PROJECTNAME		= CPP.PROJECTNAME
			AND	CPP.CLONENAME		= C.CLONENAME
			AND	CD.CHROMOSOME 		LIKE 'VAR1' 
			AND	CD.SPECIESNAME 		LIKE 'VAR2' 
			AND	CD.ID_DICT		= C.CHROMOSOME
			AND	C.CLONENAME 		= G.CLONENAME
			AND	G.ID_GLYSTOCK 		= DR.ID_GLYSTOCK
			AND	DR.REQUEST_DATE		< SYSDATE	
			AND	DR.ID_REQUEST 		= DS.ID_REQUEST
			AND	DS.ISCURRENT 		= 1
			AND	DS.STATUS 		IN(1,3,6)
			AND	DS.ISCURRENT 		= 1
			ORDER BY CP.PRIORITY DESC, SD.DESCRIPTION, DS.STATUS DESC, DR.REQUEST_DATE";
			
	
$pkg->{Button1Label}	= 'Streak Clones';
$pkg->{Duplicatecheck} 	= 2;

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


$pkg->{Columnheaders} = "$pkg->{Columnheaders} | Status";
while(length($pkg->{Columnheaders}) < 41)
	{$pkg->{Columnheaders} = "$pkg->{Columnheaders} ";}
	

$pkg->{Columnheaders} = "$pkg->{Columnheaders} | By";
while(length($pkg->{Columnheaders}) <49)
	{$pkg->{Columnheaders} = "$pkg->{Columnheaders} ";}
	
$pkg->{Columnheaders} = "$pkg->{Columnheaders} | Seq Reason";
while(length($pkg->{Columnheaders}) < 66)
	{$pkg->{Columnheaders} = "$pkg->{Columnheaders} ";}
	
$pkg->{Columnheaders} = "$pkg->{Columnheaders} | Plate";
while(length($pkg->{Columnheaders}) < 101)
	{$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,10,'Clone'],[3,13,'Request Date'],[9,12,'Status'],[4,6,'By'],[6,15,'Seq Reason'],[8,32,'Plate Name']];
				
undef $pkg->{Selection};

$pkg->Addtobrowse();
undef @{$pkg->{Contents}};
$pkg->Refresh();
$pkg->{Title}->configure(-text => "Operator : $pkg->{Who}\tRole Type : $pkg->{Roletype}\t\tFETCH AND STREAK DNAprep REQUESTS");
}


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

$sql = "	SELECT 	DNAPREP_REQUEST.ID_REQUEST FROM DNAPREP_REQUEST, DNAREQUEST_STATUS, GLYSTOCK
		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 		= $_->[2] 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("Sorry you cannot reject a request at this stage",\$chk);
$pkg->{Mainwin}->waitVariable(\$chk);
if($chk == 0)
	{return;}

}


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


eval{

foreach (@{$pkg->{Contents}})
{

$sql = "UPDATE 	DNAREQUEST_STATUS
	SET 	ISCURRENT = 0
	WHERE 	ISCURRENT = 1	AND
		ID_REQUEST = $_->[2]";
		
push @multisql,$sql;

$sql = "INSERT INTO DNAREQUEST_STATUS 	(ID_REQUEST, STATUS, STATUSDATE, ISCURRENT, SESSIONID, PROGRAM, OPERATOR)
	VALUES				($_->[2],2,SYSDATE,1, $$, 'DODO','$pkg->{Who}')";
push @multisql,$sql;


# operator is role not person
$sql = "INSERT INTO CLONE_CULTURE	(ID_CULTURE, ID_GLYSTOCK,OPERATOR, DATE_STREAKED, IS_AVAILABLE, ID_REQUEST)
	VALUES				(SEQ_CULTURE.NEXTVAL,$_->[1], $pkg->{Role},SYSDATE,1,$_->[2])";

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;
}

##############################
#Add project update, Streaked
##############################
$pkg->Projectstatus(4);

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


}
1;

