package Prepfinished;
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 	CLONE, CHROMOSOMEDICT, GLYSTOCK, CLONE_CULTURE, DNAPREP_STATUS, DNAPREP, DNAPREP_REQUEST
			WHERE	(CHROMOSOMEDICT.ID_DICT = CLONE.CHROMOSOME)				AND
				(CLONE.CLONENAME	= GLYSTOCK.CLONENAME)				AND
				(GLYSTOCK.ID_GLYSTOCK	= DNAPREP_REQUEST.ID_GLYSTOCK)		AND
				(DNAPREP_REQUEST.ID_REQUEST	= CLONE_CULTURE.ID_REQUEST)	AND
				(CLONE_CULTURE.ID_CULTURE	= DNAPREP.ID_CULTURE)		AND
				(DNAPREP.ID_PREP		= DNAPREP_STATUS.ID_PREP)	AND
				(DNAPREP_STATUS.ISCURRENT	= 1)				AND
				(DNAPREP_STATUS.STATUS 		= 4 OR
				DNAPREP_STATUS.STATUS 		= 5)
			GROUP BY CHROMOSOMEDICT.SPECIESNAME,CHROMOSOMEDICT.CHROMOSOME ";



$pkg->{ResultSQL} ="	SELECT	CLONE.CLONENAME, 
				DNAPREP_REQUEST.ID_REQUEST, 
				DNAPREP.ID_PREP,  
				DNAPREP.PREP_NUMBER, 
				DNAPREP_REQUEST.REQUEST_DATE, 
				DNAPREPSTATUSDICT.DESCRIPTION,
				TO_CHAR(ACTIONED.STATUSDATE,'DD-mm-YY') AC,
				DNAPREP_REQUEST.IS_FASTTRACK,
				CHROMOSOMEDICT.CHROMOSOME, 
				TO_CHAR(DNAPREP_STATUS.STATUSDATE,'DD-MON-YY') SD
			FROM	CLONE, CHROMOSOMEDICT, GLYSTOCK, CLONE_CULTURE,
				DNAPREP_STATUS,DNAPREP_STATUS ACTIONED, DNAPREP, DNAPREP_REQUEST, DNAPREPSTATUSDICT
			WHERE	(CHROMOSOMEDICT.CHROMOSOME 	LIKE 'VAR1') 		AND 
				(CHROMOSOMEDICT.SPECIESNAME 	LIKE 'VAR2')		AND 
				(CLONE.CHROMOSOME 		= CHROMOSOMEDICT.ID_DICT)	AND
				(CLONE.CLONENAME 		= GLYSTOCK.CLONENAME)		AND
				(GLYSTOCK.ID_GLYSTOCK 		= DNAPREP_REQUEST.ID_GLYSTOCK)	AND
				(DNAPREP_REQUEST.ID_REQUEST	= CLONE_CULTURE.ID_REQUEST)	AND
				(CLONE_CULTURE.ID_CULTURE	= DNAPREP.ID_CULTURE)		AND
				(DNAPREP.ID_PREP		= DNAPREP_STATUS.ID_PREP)	AND
				(DNAPREPSTATUSDICT.ID_DICT	= DNAPREP_STATUS.STATUS)	AND
				ACTIONED.STATUS			= 1				AND
				ACTIONED.ID_PREP		= DNAPREP_STATUS.ID_PREP	AND
				(DNAPREP_STATUS.ISCURRENT	= 1)				AND
				(DNAPREP_STATUS.STATUS 		= 4 OR
				DNAPREP_STATUS.STATUS 		= 5)
			ORDER BY DNAPREPSTATUSDICT.DESCRIPTION desc, CHROMOSOMEDICT.CHROMOSOME ,SD, CLONE.CLONENAME, DNAPREP.PREP_NUMBER";


$pkg->{Button1Label}	= 'Passed QC Checks';
$pkg->{Duplicatecheck}	= 2;

$pkg->{Columnheaders} = "Clone";
while(length($pkg->{Columnheaders}) < 17)
	{$pkg->{Columnheaders} = "$pkg->{Columnheaders} ";}
	
$pkg->{Columnheaders} = "$pkg->{Columnheaders} | Prep";
while(length($pkg->{Columnheaders}) < 25)
	{$pkg->{Columnheaders} = "$pkg->{Columnheaders} ";}


$pkg->{Columnheaders} = "$pkg->{Columnheaders} | Request Date";
while(length($pkg->{Columnheaders}) < 42)
	{$pkg->{Columnheaders} = "$pkg->{Columnheaders} ";}
	
$pkg->{Columnheaders} = "$pkg->{Columnheaders} | Status Date";
while(length($pkg->{Columnheaders}) < 59)
	{$pkg->{Columnheaders} = "$pkg->{Columnheaders} ";}

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

$pkg->{Columnheaders} = "$pkg->{Columnheaders} | Current Status";
while(length($pkg->{Columnheaders}) < 84)
	{$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'],[3,6,'Prep'],[4,15,'Request Date'],[9,15,'Status Date'],[8,5,'Chromosome'],[5,15,'Current Status']];					
undef $pkg->{Selection};
		
$pkg->Addtobrowse();
undef @{$pkg->{Contents}};
$pkg->Refresh();
$pkg->{Title}->configure(-text => "Operator : $pkg->{Who}\tRole Type : $pkg->{Roletype}\t\tPASS or FAIL DNApreps");

}


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


$sql = "	SELECT 	DNAPREP.ID_PREP FROM DNAPREP, DNAPREP_REQUEST, 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_REQUEST	= DNAPREP_REQUEST.ID_REQUEST)	AND
			(DNAREQUEST_STATUS.ISCURRENT	= 1)				AND
			(DNAREQUEST_STATUS.ID_REQUEST	= DNAPREP_REQUEST.ID_REQUEST)	AND
			(";
			
$sql = "	SELECT	DNAPREP.ID_PREP
		FROM 	DNAPREP, DNAPREP_STATUS
		WHERE	(DNAPREP_STATUS.ISCURRENT	= 1)				AND
			(DNAPREP_STATUS.ID_PREP		= DNAPREP.ID_PREP)		AND
			(";
			
			
foreach(@{$pkg->{Data}})
{
	$sql = "$sql\nDNAPREP.ID_PREP 		= $_->[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 $ref;
my $chk;

# To kill prep. Set prep status to 9 QC-Failed
# Check to see if any more preps are live on that request
# If not then reissue
my $radio = 'Failed FP or FISH';
	# Need To ask For Failure Reason;
	$pkg->radiocheck(\$radio,'Failed Either FP or FISH','Selected By Mistake');
	$pkg->{Mainwin}->waitVariable(\$radio);
	if($radio !~ /\D/){return;}

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

eval{
	
	if($radio =~ /^Selected/)
	{		
	$sql = "DELETE 	FROM DNAPREP_STATUS
		WHERE	ISCURRENT	= 1	AND
			ID_PREP 	= $_->[2]";
			
	push @multisql,$sql;
		
	# ROLLING BACK TO PREVIOUS STATUS USING MAX STATUSDATE TO FIND IT ONA PREP	
	$sql = "UPDATE 	DNAPREP_STATUS 
		SET 	ISCURRENT = 1
		WHERE	STATUSDATE IN (	
			SELECT 	MAX(STATUSDATE) 
			FROM 	DNAPREP_STATUS 
			WHERE 	ID_PREP = $_->[2] and ISCURRENT = 0) AND
			ID_PREP = $_->[2]";
			
	push @multisql,$sql;
	
	}# selected
	else
	{
	if($_->[5] eq 'Sent For FISHing')
	{
	# Ask for FISH Comments
	$pkg->InputBox("Please Enter FISH Comment For :\nClone $_->[0] / $_->[3]",\$chk);
	$pkg->{Mainwin}->waitVariable(\$chk);
	if($chk !~ /\D/){
		undef @{$pkg->{Contents}};
		$pkg->Refresh();
		$pkg->Addtobrowse();
		$pkg->Addtoresults();
	return;}
	
	$chk = substr($chk,0,100);
	
	
	# insert FISH comment into  DNAprep 
	$sql = "	UPDATE	DNAPREP 
			SET	FISH_COMMENTS = '$chk' 
			WHERE 	DNAPREP.ID_PREP = $_->[2]";
			 
	
	push @multisql,$sql;


	# insert FISH comment into  DNAprep 
	$sql = "	UPDATE	DNAPREP 
			SET	FISH_COMMENTS = '$chk' 
			WHERE 	DNAPREP.ID_PREP = $_->[2]";
			 
	
	push @multisql,$sql;
	
	# Kill individual prep
	$sql = "UPDATE 	DNAPREP_STATUS 
		SET 	ISCURRENT = 0 
		WHERE 	ISCURRENT = 1 AND ID_PREP = $_->[2]";
	
	push @multisql,$sql;
	
	$sql = "INSERT INTO 	DNAPREP_STATUS	(ID_PREP, STATUS, STATUSDATE, ISCURRENT, SESSIONID, PROGRAM, OPERATOR)
		VALUES				($_->[2] ,6,SYSDATE,1, $$, 'DODO','$pkg->{Who}')";
					
	push @multisql,$sql;
	
	}
	
	
	
	# Kill individual prep
	$sql = "UPDATE 	DNAPREP_STATUS 
		SET 	ISCURRENT = 0 
		WHERE 	ISCURRENT = 1 AND ID_PREP = $_->[2]";
	
	push @multisql,$sql;
	
	$sql = "INSERT INTO 	DNAPREP_STATUS	(ID_PREP, STATUS, STATUSDATE, ISCURRENT, SESSIONID, PROGRAM, OPERATOR)
		VALUES				($_->[2] ,9,SYSDATE,1, $$, 'DODO','$pkg->{Who}')";
					
	push @multisql,$sql;
	
	$sql = "SELECT 	COUNT(DNAPREP.ID_PREP)
		FROM 	DNAPREP , DNAPREP_STATUS, CLONE_CULTURE
		WHERE 	(DNAPREP_STATUS.STATUS		= 7)				AND
			(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	= $_->[1])";

	my $completed = $pkg->Querydb($sql);




	# Check to see how many preps live on request
	$sql = "SELECT 	COUNT(DNAPREP.ID_PREP)
		FROM 	DNAPREP , DNAPREP_STATUS, CLONE_CULTURE
		WHERE 	(DNAPREP_STATUS.STATUS		IN (1,2,3,4,5,6,30,31))		AND
			(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	= $_->[1])";

	$ref = $pkg->Querydb($sql);
	
	
	# No other preps live
	if($ref->[0][0] == 1)
	{
	$sql = "UPDATE 	DNAREQUEST_STATUS 
		SET 	ISCURRENT = 0 
		WHERE 	ISCURRENT = 1 AND ID_REQUEST = $_->[1]";
	
	push @multisql,$sql;
	
	if($completed->[0][0] == 0)
	{
		# RE REQUEST !!!!!! not RE ISSUE as we are further down the pipeline
		# Leave it Up to the Operator to either cancel the request and resend
		$sql = "INSERT INTO 	DNAREQUEST_STATUS	(ID_REQUEST, STATUS, STATUSDATE, ISCURRENT, SESSIONID, PROGRAM, OPERATOR)
			VALUES					($_->[1] , 6,SYSDATE,1, $$, 'DODO','$pkg->{Who}')";
	}
	else
	{
		# complete, all preps through
		$sql = "INSERT INTO 	DNAREQUEST_STATUS	(ID_REQUEST, STATUS, STATUSDATE, ISCURRENT, SESSIONID, PROGRAM, OPERATOR)
			VALUES					($_->[1] , 4,SYSDATE,1, $$, 'DODO','$pkg->{Who}')";
	}
	push @multisql,$sql;
	}#end no live preps
	}# else failed prep


$pkg->MultiWritedb(@multisql);
undef @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($_->[5] eq 'Sent For FISHing')
	{
	###################
	#TO STORE FISH COMMENT FOR PROJECT UPDATE
	$_->[1] = $chk;
	}
	undef $chk;

}

# Tell project Status it was FISHed
$pkg->Projectstatus(9);

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

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


foreach (@{$pkg->{Contents}})
{
	$mail = "$mail\nPrep Start Date = $_->[6]\t$_->[0]-$_->[3]\n";
	
	$sql = "UPDATE 	DNAPREP_STATUS 
		SET 	ISCURRENT = 0 
		WHERE 	ISCURRENT = 1 AND ID_PREP = $_->[2]";
	
	push @multisql,$sql;
	
	# if the prep has been FISHED ask for a fish comment [6]
	if($_->[5] eq 'Sent For FISHing')
	{
	
	# Ask for FISH Comments
	$pkg->InputBox("Please Enter FISH Comment For :\nClone $_->[0] / $_->[3]",\$chk);
	$pkg->{Mainwin}->waitVariable(\$chk);
	if($chk !~ /\D/){return;}
	
	$chk = substr($chk,0,100);
	
	# insert FISH comment into  DNAprep 
	$sql = "	UPDATE	DNAPREP 
			SET	FISH_COMMENTS = '$chk' 
			WHERE 	DNAPREP.ID_PREP = $_->[2]";
			 
	
	push @multisql,$sql;
	
	# Adding FISH Status to the DNAprep
	$sql = "INSERT INTO 	DNAPREP_STATUS	(ID_PREP, STATUS, STATUSDATE, ISCURRENT, SESSIONID, PROGRAM, OPERATOR)
		VALUES				($_->[2] ,6,SYSDATE,0, $$, 'DODO','$pkg->{Who}')";
		
	push @multisql,$sql;
	}# If fish
	
	
	# Complete prep 
	$sql = "INSERT INTO 	DNAPREP_STATUS	(ID_PREP, STATUS, STATUSDATE, ISCURRENT, SESSIONID, PROGRAM, OPERATOR)
		VALUES				($_->[2] ,7,SYSDATE,1, $$, 'DODO','$pkg->{Who}')";
		
	push @multisql,$sql;


	$sql = "SELECT 	COUNT(DNAPREP.ID_PREP)
		FROM 	DNAPREP, DNAPREP_STATUS, CLONE_CULTURE, DNAREQUEST_STATUS
		WHERE 	(DNAPREP_STATUS.STATUS		IN (1,2,3,4,5,6,30,31))		AND
			(DNAPREP_STATUS.ISCURRENT	= 1)				AND
			(DNAPREP.ID_PREP 		= DNAPREP_STATUS.ID_PREP)	AND
			(CLONE_CULTURE.ID_CULTURE	= DNAPREP.ID_CULTURE)		AND
			(DNAREQUEST_STATUS.ID_REQUEST	= CLONE_CULTURE.ID_REQUEST)	AND
			(DNAREQUEST_STATUS.STATUS	= 2)				AND
			(DNAREQUEST_STATUS.ISCURRENT	= 1)				AND
			(DNAREQUEST_STATUS.ID_REQUEST	= $_->[1])";

	$ref = $pkg->Querydb($sql);
	
	# a value of one refers to this prep hence
	if($ref->[0][0] == 1)
	{# This is last prep, complete request				
	$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] ,4,SYSDATE,1, $$, 'DODO','$pkg->{Who}')";
	


	push @multisql,$sql;
	}# last prep
	
	if($_->[5] eq 'Sent For FISHing')
	{
	###################
	#TO STORE FISH COMMENT FOR PROJECT UPDATE
	$_->[1] = $chk;
	}
	undef $chk;
}#foreach

eval
{$pkg->MultiWritedb(@multisql);};if($@){print STDERR "Function	: Complete::action\nRequest ID	:$_->[1]\n$@ \n";}

undef @multisql;

# Issue restriction digests
$pkg->RequestdigestFP();

# FISH Project
$pkg->Projectstatus(9);

# Tell project status its cleared for library making.
$pkg->Projectstatus(31);




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



if($mail)
{
eval{

open (MAIL,"|/usr/bin/mail rs2");
print MAIL "From: DODO                <>\n";
print MAIL "To: rs2\n";
print MAIL "Subject: Preps Arriving from F-Printers\n\n";
print MAIL "This is an Automated Mail Message from Oracle.\n";
print MAIL "The following preps will shortly be arriving from the Fingerprinter : $pkg->{Who}\n";
print MAIL $mail;
close(MAIL);

#remind the user to send preps up to Subcloning
open (MAIL,"|/usr/bin/mail $pkg->{Who}");
print MAIL "From: DODO                <>\n";
print MAIL "To: $pkg->{Who}\n";
print MAIL "Subject: Send Preps To Subcloning\n\n";
print MAIL "******** Reminder *********\nPlease Ensure The Following Preps Have Been Placed In the Subcloning Freezers :\n";
print MAIL $mail;
close(MAIL);

open (MAIL,"|/usr/bin/mail bsw");
print MAIL "From: DODO                <>\n";
print MAIL "To: bsw\n";
print MAIL "Subject: Send Preps To Subcloning\n\n";
print MAIL "******** Reminder *********\nPlease Ensure The Following Preps Have Been Placed In the Subcloning Freezers :\n";
print MAIL $mail;
close(MAIL);

};if($@){print STDERR "Function	: Complete::action\n$@\n";}
}#$mail end if


#}#not needed  ?
}


sub RequestdigestFP {
my $pkg = shift;
my $sql;
my $ref;
my $person;
my $confirm;
my $mail;

$sql = "SELECT ID_PERSON FROM PERSON WHERE EMAIL = '$pkg->{Who}'";
$ref = $pkg->Querydb($sql);
$person = $ref->[0][0];

$confirm = Database->new();
$confirm->Initialisedb();
$confirm->Userdb('image');
$confirm->Connect_To_DB();

my @multisql;
foreach (@{$pkg->{Contents}})
{		
my $priority = 0;
my $sample;


	$sample = substr($_->[6],3);
	$sample =~ s/\-/\./g;
	$sample = "$sample/$_->[3]";

	if($_->[7] > 0){$priority = 1}
	
foreach my $enzyme ('EcoRI','HindIII','BamHI')
{
	# Trigger on rdrequest for clonename on id_dnaprep
	$sql	= "	INSERT INTO RDREQUEST 	(ID_RDREQUEST, CLONENAME, REQUESTED_BY, REQUEST_DATE, IS_FASTTRACK, ID_DNAPREP, SAMPLE_REF)
			VALUES			(SEQ_RDREQUEST.NEXTVAL, '$_->[0]', $person, SYSDATE, $priority, $_->[2], '$sample')";
	
	push @multisql,$sql;
	
	
	$sql	= "	INSERT INTO RDREQUEST_STATUS 	(ID_RDREQUEST, STATUS, STATUSDATE, ISCURRENT, PROGRAM, OPERATOR)
			VALUES				(SEQ_RDREQUEST.CURRVAL, 1, SYSDATE, 1, 'DODO','$pkg->{Who}')";
	
	push @multisql,$sql;
		
	$sql	= "	INSERT INTO RDREQUEST_ENZYME 	(ID_RDREQUEST, ENZYMENAME)
			VALUES				(SEQ_RDREQUEST.CURRVAL,'$enzyme')";
	
	push @multisql,$sql;
}
	$confirm->MultiWritedb(@multisql);
	undef @multisql;
	
}

$confirm->{dbh}->disconnect;

}









1;

