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, 
				DNAPREP_REQUEST.IS_FASTTRACK,
				CHROMOSOMEDICT.CHROMOSOME, 
				TO_CHAR(DNAPREP_STATUS.STATUSDATE,'DD-MON-YY') SD
			FROM	CLONE, CHROMOSOMEDICT, GLYSTOCK, CLONE_CULTURE, DNAPREP_STATUS,
				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
				(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'],[8,15,'Status Date'],[7,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;

# 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
	{
	# 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		< 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])";

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




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

sub Action
{
my $pkg = shift;
my $sql;
my @multisql;
my $chk;
my $dates;
my @projects;
my $ref;
my @error1;
my @error2;
my @error3;
my @error4;
my $mail = "******** Reminder *********\nPlease Ensure The Following Preps Have Been Placed In the Subcloning Freezers :\n";;
my $i = 0;


# complete preps that already have one sucessfull prep in the series
for($i = 0; $i < @{$pkg->{Contents}}; $i++)
{

#count the number of successfull preps # should we be using clone names ?
$sql = "SELECT 	COUNT(DNAPREP.ID_PREP)
	FROM 	DNAPREP, DNAPREP_STATUS, CLONE_CULTURE
	WHERE 	(DNAPREP_STATUS.STATUS		= 7	OR
		DNAPREP_STATUS.STATUS		> 10)				AND
		DNAPREP_STATUS.ISCURRENT	= 1				AND
		DNAPREP_STATUS.ID_PREP		= DNAPREP.ID_PREP		AND
		DNAPREP.CLONENAME		= '$pkg->{Contents}->[$i][0]'";

$ref = $pkg->Querydb($sql);

if($ref->[0][0] > 0)
{
	#No need to tell the project a prep dates thats already been done by a previous prep
	my $x = $pkg->Complete(\$i);
	if(!$x){return;}# Failure writing prep stats
	$mail = "$mail\n$x";
	splice @{$pkg->{Contents}},$i,1;
	$i--;
}
else
{
# Update projectstatus because this is a new clone ???

# Find out How Many Projects
$sql = "SELECT 	CLONE.CLONENAME, PROJECT.PROJECTNAME, PROJECT_STATUS.STATUS
	FROM	CLONE_PROJECT PROJECT, CLONE_PROJECT CLONE, PROJECT_STATUS
	WHERE	PROJECT_STATUS.ISCURRENT	= 1			AND
		PROJECT_STATUS.PROJECTNAME	= PROJECT.PROJECTNAME	AND
		PROJECT.PROJECTNAME 		= CLONE.PROJECTNAME 	AND
		CLONE.CLONENAME 		= '$pkg->{Contents}->[$i][0]'";

$ref = $pkg->Querydb($sql);


if(!$ref->[0][0])#No project found
	{
	push @error1,"$pkg->{Contents}->[$i][0]\t\tID_PREP $pkg->{Contents}->[$i][2]";
	splice @{$pkg->{Contents}},$i,1;
	$i--;
	}
elsif(@{$ref} > 1)#More than one project found
	{
	push @error2,"$pkg->{Contents}->[$i][0]\t\tID_PREP $pkg->{Contents}->[$i][2]";
	splice @{$pkg->{Contents}},$i,1;
	$i--;
	}
elsif($ref->[0][2] > 2)# Has the Project status progressed too far ??
	{
	push @error3,"$pkg->{Contents}->[$i][0]\t\tProject $ref->[0][1]";
	splice @{$pkg->{Contents}},$i,1;
	$i--;
	}
else
	{
	my $x = $pkg->Complete(\$i);
	if(!$x){return;}# Failure writing prep stats
	$mail = "$mail\n$x";
	push @projects,[$ref->[0][0],$ref->[0][1],$ref->[0][2],$pkg->{Contents}->[$i][5]];
	}	
undef $ref;
}#end of else

}#Going through all preps


if($pkg->{Contents}->[0])
{
	@error4 = $pkg->Projectstatus(\@projects);
}

# Formatted Error 
my $msg;
if((@error1)||(@error2)||(@error3)||(@error4))
{
$msg = "DODO Project Status Update\n";

$msg = "$msg\n******************************* WARNING ******************************************\n";
$msg = "$msg\nThe Project And DNA prep Statuses For the Following Clone(s) have not been Updated\n";
$msg = "$msg\n\nDO NOT ATTEMPT TO UPDATE THE PROJECT STATUSES YOURSELF !\n\n";
$msg = "$msg\nContact bug to have the Project Statuses Updated\n\n";

if(@error1)
{
	$msg = "\n$msg\nCould not find a project for Clone(s)\n";
	foreach (@error1)
		{$msg = "$msg\t$_\n";}
}


if(@error2)
{
	$msg = "\n$msg\nA multiple PROJECT >-< CLONE relationship exists for Clone(s)\n";
	foreach (@error2)
		{$msg = "$msg\t$_\n";}
}

if(@error3)
{
	$msg = "\n$msg\nProject status already beyond DNA Preparation for Clone(s)\n";
	foreach (@error3)
		{$msg = "$msg\t$_\n";}
}

if(@error4)
{
	$msg = "\n$msg\nCould not find DNAprep status dates for clone(s)\n";
	foreach (@error4)
		{$msg = "$msg\t$_\n";}
}


}#End of error checking
undef @{$pkg->{Contents}};
$pkg->Refresh();
$pkg->Addtobrowse();
$pkg->Addtoresults();

if($msg)
{$pkg->email($msg);}

if(length($mail) > 108)
{

eval{
#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 $mail;
close(MAIL);



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


}


sub Projectstatus
{
my ($pkg,$projects) = @_;
my $dates;
my $FISHdates;
my $sql;
my @e;
my $i;
my @multisql;
my $gull;

# problems converting between formats going for simple approach
my $d = "ddmmyyyyhhmiss"; 
# Retrieving Status dates for DNApreps on Each request specified
$sql = "	SELECT 	DNAPREP.CLONENAME, 
			DNAPREP_REQUEST.REQUEST_DATE, 
			CLONE_CULTURE.DATE_STREAKED, 
			DNA_MADE.STATUSDATE, 
			SENT_TO_FP.STATUSDATE, 
			FINGERPRINTED.STATUSDATE
		FROM 	DNAPREP_STATUS DNA_MADE, DNAPREP_STATUS SENT_TO_FP, 
			DNAPREP_STATUS FINGERPRINTED, CLONE_CULTURE, DNAPREP_REQUEST, DNAPREP
		WHERE	DNAPREP_REQUEST.ID_REQUEST	= CLONE_CULTURE.ID_REQUEST	AND
			CLONE_CULTURE.ID_CULTURE	= DNAPREP.ID_CULTURE		AND
			DNAPREP.ID_PREP			= DNA_MADE.ID_PREP		AND
			DNA_MADE.STATUS			= 2				AND
			DNA_MADE.ID_PREP 		= SENT_TO_FP.ID_PREP		AND
			SENT_TO_FP.STATUS 		= 3				AND
			SENT_TO_FP.ID_PREP 		= FINGERPRINTED.ID_PREP		AND
			FINGERPRINTED.STATUS		= 4				AND
			FINGERPRINTED.ID_PREP 		= SENT_TO_FP.ID_PREP		AND
			SENT_TO_FP.STATUS 		= 3				AND
			(";
eval{	
foreach (@{$pkg->{Contents}})
{
	$sql = "$sql\nSENT_TO_FP.ID_PREP 		= $_->[2] OR";
}
$sql = substr($sql,0,length($sql)-2);
$sql = "$sql)";  
			

$dates = $pkg->Querydb($sql);

# GET FISH DATES IF THEY EXIST
$sql = "	SELECT 	DNAPREP.CLONENAME, 
			SENT_TO_FISH.STATUSDATE,
			FISHED.STATUSDATE,
			DNAPREP.FISH_COMMENTS
		FROM	DNAPREP_STATUS SENT_TO_FISH, DNAPREP_STATUS FISHED, DNAPREP
		WHERE	DNAPREP.ID_PREP			= SENT_TO_FISH.ID_PREP		AND
			SENT_TO_FISH.STATUS		= 5				AND
			SENT_TO_FISH.ID_PREP		= FISHED.ID_PREP		AND
			FISHED.STATUS			= 6				AND
			(";
foreach (@{$pkg->{Contents}})
{
	$sql = "$sql\nFISHED.ID_PREP 		= $_->[2] OR";
}
$sql = substr($sql,0,length($sql)-2);
$sql = "$sql)";


$FISHdates = $pkg->Querydb($sql);
};if($@){print STDERR "Function	: Complete::Projectstatus\n$@\n";}


if(!$dates)
	{push @e, "\nCANNOT FIND DNAPREP STATUS DATES FOR ANY CLONE SELECTED !\n";}
else
{
# hack cause simon removed dodos project writing rights;

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

$sql = "	SELECT 	PROJECT_STATUS.PROJECTNAME 	
		FROM 	CLONE_PROJECT, PROJECT, PROJECT_STATUS
		WHERE	PROJECT_STATUS.PROJECTNAME	= PROJECT.PROJECTNAME		AND
			PROJECT.PROJECTNAME		= CLONE_PROJECT.PROJECTNAME	AND
			(";
foreach (@{$projects})
{
	$sql = "$sql\nCLONE_PROJECT.CLONENAME 	=  '$_->[0]' OR";
}
$sql = substr($sql,0,length($sql)-2);
$sql = "$sql)\nFOR UPDATE NOWAIT";

eval{$gull->Querydb($sql);};

if($@ =~ /ORA-00054:/)
{	my $chk;
	my $m = "LOCK OUT\n\nUnable To Update Project status\n";
	$m = "$m\nAnother User Is Currently Accessing The Projects Associated With These Clone(s)";
	$m = "$m\n\nUse Gull to update project statuses.\n\nContact Bug If Problem Persists";
	$pkg->Message($m,\$chk);
	$pkg->{Mainwin}->waitVariable(\$chk);
	#Re-establish Lock On Main data
	undef @{$pkg->{Data}};
	$pkg->Addtoresults();
	return;}
if($@)
{
	print STDERR "$@ \n";
	return;
}




foreach (@{$projects})
{my $hit = 0;
	for($i = 0; $i < @{$dates}; $i++)
	{
	if($_->[0] eq $dates->[$i][0])#Find each projects status dates
		{
		
		
		$sql = "UPDATE 	PROJECT_STATUS
			SET	ISCURRENT 	= 0
			WHERE	ISCURRENT	= 1	AND
				PROJECTNAME	= '$_->[1]'";
		
		push @multisql, $sql;
		#$dates->[$i][1] =~ s/[-]|[:]|[ ]//g;
		
		# 	recieved = request date
		$sql = "INSERT INTO 	PROJECT_STATUS	(PROJECTNAME, STATUS, STATUSDATE, ISCURRENT, SESSIONID, PROGRAM, OPERATOR)
			VALUES				('$_->[1]' , 3,'$dates->[$i][1]',0, $$, 'DODO','$pkg->{Who}')";
		
		push @multisql, $sql;
		#$dates->[$i][2] =~ s/[-]|[:]|[ ]//g;
		# 	Steaked = date streaked
		$sql = "INSERT INTO 	PROJECT_STATUS	(PROJECTNAME, STATUS, STATUSDATE, ISCURRENT, SESSIONID, PROGRAM, OPERATOR)
			VALUES				('$_->[1]' , 4,'$dates->[$i][2]',0, $$, 'DODO','$pkg->{Who}')";
		
		push @multisql, $sql;
		#$dates->[$i][3] =~ s/[-]|[:]|[ ]//g;
		# 	Steaked = date streaked
		# 	DNA made = made
		$sql = "INSERT INTO 	PROJECT_STATUS	(PROJECTNAME, STATUS, STATUSDATE, ISCURRENT, SESSIONID, PROGRAM, OPERATOR)
			VALUES				('$_->[1]' , 5,'$dates->[$i][3]',0, $$, 'DODO','$pkg->{Who}')";
		
		push @multisql, $sql;
		#$dates->[$i][4] =~ s/[-]|[:]|[ ]//g;
		# 	sent to fp
		$sql = "INSERT INTO 	PROJECT_STATUS	(PROJECTNAME, STATUS, STATUSDATE, ISCURRENT, SESSIONID, PROGRAM, OPERATOR)
			VALUES				('$_->[1]' ,6,'$dates->[$i][4]',0, $$, 'DODO','$pkg->{Who}')";
		
		push @multisql, $sql;
		
		
		# 	FINGER PRINTED
		#$dates->[$i][5] =~ s/[-]|[:]|[ ]//g;
		$sql = "INSERT INTO 	PROJECT_STATUS	(PROJECTNAME, STATUS, STATUSDATE, ISCURRENT, SESSIONID, PROGRAM, OPERATOR)
			VALUES				('$_->[1]' ,7,'$dates->[$i][5]',1, $$, 'DODO','$pkg->{Who}')";

		push @multisql, $sql;
		
		#	FISH ??
		if(($_->[3] eq 'Sent For FISHing')&&($FISHdates))
		{
		
		my $FISHhit = 0;
		my $j;
		for($j = 0; $j < @{$FISHdates}; $j++)
		{
		
		if($_->[0] eq $FISHdates->[$j][0])#Find each projects FISH status dates
		{
		$sql = "UPDATE 	PROJECT_STATUS
			SET	ISCURRENT 	= 0
			WHERE	ISCURRENT	= 1	AND
				PROJECTNAME	= '$_->[1]'";
		
		push @multisql, $sql;
		
		
		#$FISHdates->[$j][1] =~ s/[-]|[:]|[ ]//g;
		$sql = "INSERT INTO 	PROJECT_STATUS	(PROJECTNAME, STATUS, STATUSDATE, ISCURRENT, SESSIONID, PROGRAM, OPERATOR)
			VALUES				('$_->[1]',8,'$FISHdates->[$j][1]',0, $$, 'DODO','$pkg->{Who}')";
		
		
		push @multisql, $sql;
		
		#$FISHdates->[$j][2] =~ s/[-]|[:]|[ ]//g;
		#$FISHdates->[$j][2] = $FISHdates->[$j][2]/1;
		
		
		$sql = "INSERT INTO 	PROJECT_STATUS	(PROJECTNAME, STATUS, STATUSDATE, ISCURRENT,SESSIONID, PROGRAM, OPERATOR, REMARK)
			VALUES				('$_->[1]' ,9,'$FISHdates->[$j][2]',1, $$,'DODO','$pkg->{Who}', '$FISHdates->[$j][3]')";
		
		
		push @multisql, $sql;
		
		
		
		$j = scalar @{$FISHdates};
		$j += 10;# This causes the loop to end
		$FISHhit = 1;# might use this later
		
		}# end of search for FISH dates
		}#end of for FISH loop
		}#end of FISH 
		
		
		
		
		
		eval{
		# connect as gull
		
		$gull->MultiWritedb(@multisql);
		undef @multisql;
			
		};if($@){print STDERR "Function	: Complete::Projectstatus\n$@\n";}	
		
		splice @{$dates}, $i,1;
		$i = scalar @{$dates};
		$i += 10;# This causes the loop to end and also informs
		$hit = 1;
		}# match dates - clone
	}#dates
	# changed 11 June bsw
	if($hit == 0)
	{push @e,"Clone $_->[0]\tProject $_->[1]\t\n";}


}#projects
}#dates else
$gull->{dbh}->disconnect();

return @e;

}




sub Complete
{
my ($pkg,$i) = @_;
my $sql;
my @multisql;
my $ref;
my $chk;
my $pp;
my $mail;

	
	$mail = "$pkg->{Contents}->[$$i][0]-$pkg->{Contents}->[$$i][3]";
	$sql = "UPDATE 	DNAPREP_STATUS 
		SET 	ISCURRENT = 0 
		WHERE 	ISCURRENT = 1 AND ID_PREP = $pkg->{Contents}->[$$i][2]";
	
	push @multisql,$sql;
	
	# if the prep has been FISHED ask for a fish comment [5]
	if($pkg->{Contents}->[$$i][5] eq 'Sent For FISHing')
	{
	# Ask for FISH Comments
	
	$pkg->InputBox("Enter FISH Comment For :\nClone $pkg->{Contents}->[$$i][0] / $pkg->{Contents}->[$$i][3]",\$chk);
	
	$pkg->{Mainwin}->waitVariable(\$chk);
	if($chk !~ /\D/){return;}
	
	
	$sql = "	UPDATE	DNAPREP 
			SET	FISH_COMMENTS = '$chk' 
			WHERE 	DNAPREP.ID_PREP = $pkg->{Contents}->[$$i][2]";
			
	# insert FISH comment into Comments field in DNAprep over writing previous eppendorf
	# location
	undef $chk;
	push @multisql,$sql;
	# Adding FISH Status to the DNAprep
	$sql = "INSERT INTO 	DNAPREP_STATUS	(ID_PREP, STATUS, STATUSDATE, ISCURRENT, SESSIONID, PROGRAM, OPERATOR)
		VALUES				($pkg->{Contents}->[$$i][2] ,6,SYSDATE,0, $$, 'DODO','$pkg->{Who}')";
		
	push @multisql,$sql;
	
	
	}
	
	
	# QC-Success 
	$sql = "INSERT INTO 	DNAPREP_STATUS	(ID_PREP, STATUS, STATUSDATE, ISCURRENT, SESSIONID, PROGRAM, OPERATOR)
		VALUES				($pkg->{Contents}->[$$i][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		< 7)				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	= $pkg->{Contents}->[$$i][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 = $pkg->{Contents}->[$$i][1]";
	
	push @multisql,$sql;
	
	$sql = "INSERT INTO 	DNAREQUEST_STATUS	(ID_REQUEST, STATUS, STATUSDATE, ISCURRENT, SESSIONID, PROGRAM, OPERATOR)
		VALUES					($pkg->{Contents}->[$$i][1] ,4,SYSDATE,1, $$, 'DODO','$pkg->{Who}')";
	
	push @multisql,$sql;
	}
eval
{
$pkg->MultiWritedb(@multisql);
};if($@){print STDERR "Function	: Complete::complete\nRequest ID	:$pkg->{Contents}->[$$i][1]\n$@ \n";}

undef @multisql;


return $mail;

}
1;

