package Fish;
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)
			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, DNAPREP_REQUEST.IS_FASTTRACK, 
				DNAPREP_STATUS.STATUSDATE, CHROMOSOMEDICT.CHROMOSOME

			FROM	CLONE, CHROMOSOMEDICT, GLYSTOCK, CLONE_CULTURE, DNAPREP_STATUS, DNAPREP, DNAPREP_REQUEST
			
			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
				(DNAPREP_STATUS.ISCURRENT	= 1)				AND
				(DNAPREP_STATUS.STATUS 		= 4)
			ORDER BY CHROMOSOMEDICT.CHROMOSOME, DNAPREP_REQUEST.IS_FASTTRACK DESC, DNAPREP.ID_PREP, DNAPREP.PREP_NUMBER, DNAPREP_REQUEST.ID_REQUEST";


$pkg->{Button1Label}	= "Send To FISH";
$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} | FP Date";
while(length($pkg->{Columnheaders}) < 59)
	{$pkg->{Columnheaders} = "$pkg->{Columnheaders} ";}

$pkg->{Columnheaders} = "$pkg->{Columnheaders} | Chr";
while(length($pkg->{Columnheaders}) < 67)
	{$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'],[6,15,'Fingerprint Date'],[7,5,'Chromosome']];					
undef $pkg->{Selection};
		
$pkg->Addtobrowse();
undef @{$pkg->{Contents}};
$pkg->Refresh();
$pkg->{Title}->configure(-text => "Operator : $pkg->{Who}\tRole Type : $pkg->{Roletype}\t\tFISH 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.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;
$pkg->Message("Use the COMPLETE option from the TOOLBAR to reject a Preparation",\$chk);
$pkg->{Mainwin}->waitVariable(\$chk);

}

sub Action
{
my $pkg = shift;
my $sql;
my @multisql;
my $chk;
my $ref;
my $list =[];
my $clonelist = [];
eval{
#my $i = 1;
$pkg->Projectstatus(8);
foreach (@{$pkg->{Contents}})
{

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

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

$sql = "SELECT 	TO_CHAR(STATUSDATE,'dd-MON-yyyy'), TO_CHAR(SYSDATE,'dd-MON-yyyy')
	FROM	DNAPREP_STATUS
	WHERE	STATUS 		= 2	AND
		ID_PREP		= $_->[2]";

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


$ref->[0][0] =~ s/ //g;
$ref->[0][1] =~ s/ //g;
$ref->[0][2] =~ s/ //g;

$_->[0] = "$ref->[0][1]\t$_->[0]-$_->[3]\t \t$pkg->{Who}\t$ref->[0][0]\tSeq";

push @{$list}, $_->[0];
}



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

open (MAIL,"|/usr/bin/mail smc");
print MAIL "From: DODO (Oracle)            <>\n";
print MAIL "To: smc\n";
print MAIL "Subject: Preps For FISH\n\n";
print MAIL "The Following Preps will shortly arrive for FISH from $pkg->{Who}\n";
print MAIL "******* This is REAL. These preps will be arriving shortly\n";
print MAIL "DATE IN    \tCLONE-PREP\t\tSource\tDATE MADE\tMade by\n";
foreach(@{$list})
{
	print MAIL "$_\n";
}
close(MAIL);

open (MAIL,"|/usr/bin/mail $pkg->{Who}");
print MAIL "From: DODO (Oracle)            <>\n";
print MAIL "To: $pkg->{Who}\n";
print MAIL "Subject: Send Preps For FISH\n\n";
print MAIL "DATE IN    \tCLONE-PREP\t\tSource\tDATE MADE\tMade by\n";
foreach(@{$list})
{
	print MAIL "$_\n";
}
close(MAIL);

open (MAIL,"|/usr/bin/mail npc");
print MAIL "From: DODO (Oracle)            <>\n";
print MAIL "To: npc\n";
print MAIL "Subject: Preps For FISH\n\n";
print MAIL "The Following Preps will shortly arrive for FISH from $pkg->{Who}\n";
print MAIL "DATE IN    \tCLONE-PREP\t\tSource\tDATE MADE\tMade by\n";
foreach(@{$list})
{
	print MAIL "$_\n";
}
close(MAIL);


open (MAIL,"|/usr/bin/mail bsw");
print MAIL "From: DODO (Oracle)            <>\n";
print MAIL "To: bsw\n";
print MAIL "Subject: Send Preps For FISH $pkg->{Who}\n\n";
print MAIL "DATE IN    \tCLONE-PREP\t\tSource\tDATE MADE\tMade by\n";
foreach(@{$list})
{
	print MAIL "$_\n";
}
close(MAIL);




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





}
1;

