package Database;
use WrapDBI;

sub new {
my ($pkg, $U, $I)  = @_ ;
my $setup = {};


bless $setup,$pkg;

$setup->Initialise($U, $I);
$setup->Connect();
return $setup;
}

sub Program {
my $pkg = shift;

if(@_)
{
	$pkg->{Program} = shift;
	$pkg->{Program} =~ s/.*\///g;
	$pkg->{Program} = substr($pkg->{Program},0,29);
}
else
{
	return $pkg->{Program};
}


}
sub Rollback {
my $pkg = shift;

if($pkg->{dbh})
{
	$pkg->{dbh}->rollback();
}
}

sub Commit {
my $pkg = shift;

if($pkg->{dbh})
{
	$pkg->{dbh}->commit();
}
}






sub Initialise {
my ($pkg, $check, $who)  = @_ ;


if(!$who)
{
	$who = `whoami`;
	chomp $who;
}


$pkg->Whoami($who);
$pkg->Program($0);

$pkg->{User}		= $check;

$ENV{TWO_TASK} 		= 'sids';
if($check =~ /dev/)
{
	$ENV{TWO_TASK} 	= 'dev';
}





warn "========================================================\n";
warn "Application connecting to $pkg->{User} On $ENV{TWO_TASK}\n";
warn "========================================================\n";




}

sub AccessLevel {
#############################################
# Available Levels
# SuperUser
# User
#############################################
my ($pkg,$who) 	= @_;
my $level;
my $id;
my $nom;
my $idperson;

$pkg->Whoami($who);



	$who 	= $pkg->Whoami();
	
	eval{
	$ArrayRef = $pkg->Retrieve("
	SELECT 	T.ID_ROLE, T.ROLETYPE, P.FORENAME||' '||P.SURNAME, P.ID_PERSON
	FROM 	TEAM_PERSON_ROLE T, PERSON P
	WHERE 	T.ROLETYPE	IN ('F-Printer', 'Subcloning')
	AND	T.ID_PERSON	= P.ID_PERSON
	AND	P.ISCURRENT 	= 1 
	AND 	P.EMAIL 	= ?
	ORDER BY T.ROLETYPE",
	[$who]);
	
	
	$id 	= $ArrayRef->[0][0];
	$nom	= $ArrayRef->[0][2];
	$idperson = $ArrayRef->[0][3];
	};if($@)
	{
	warn "\n\nSorry There Is A Problem Accessing Person Information From Oracle\nSwitching To readonly mode\n\n";
	$level = 'disabled';
	}



if(!$id)
{
	$level = 'disabled';
}
else
{
	$level = 'normal';
}


$pkg->IDrole($id);

return ($who, $id, $level, $nom, $idperson);

}

sub DatabaseHandle {
my $pkg = shift;
return $pkg->{"dbh"};

}



sub Connect {

my $pkg = shift;

################################################
# Make A connection or use the one passed to it
################################################
if(@_)
{
	$pkg->{"dbh"} 		= shift;
}
else
{
	warn "\nStandby.....\nConnecting to oracle.....\n";
	eval {
	if($pkg->{"dbh"}){$pkg->Disconnect();}
	
	$pkg->{"dbh"} 			= WrapDBI->connect($pkg->{"User"}, {RaiseError => 1, AutoCommit => 0});
	#$pkg->{"dbh"}->{AutoCommit}	=	0;
	};if($@){die "Error Connecting\n\n$@\n";}


$pkg->{Session} = $pkg->Lookup("
SELECT	SEQ_SESSION.NEXTVAL
FROM	DUAL");

$pkg->{Session}	= $pkg->{Session}->[0][0];

$pkg->{Date} = $pkg->Lookup("
SELECT	SYSDATE
FROM	DUAL");

$pkg->{Date}	= $pkg->{Date}->[0][0];



}
}


sub Disconnect {
my $pkg = shift;

if($pkg->{Prepare})
{$pkg->{Prepare}->finish();}



if($pkg->{"dbh"})
{
	$pkg->{"dbh"}->disconnect();
	undef $pkg->{"dbh"};
}


}



############################################
sub Retrieve {
my ($pkg, $sql, $data) = @_;
my $prepare;
my $ref = [];
my @all_results;

eval {

$prepare = $pkg->{"dbh"}->prepare($sql);


foreach(@{$data})
{
	if(ref($_) eq "ARRAY")
	{
		$prepare->execute(@{$_});
	}
	else
	{
		$prepare->execute($_);
	}
	my $ref = $prepare->fetchall_arrayref();
	
	foreach my $r (@$ref)
	{
		my $out;
		
		push @all_results, [@$r];	
	}
}
	
$prepare->finish();
};if($@){die "Retrieving : \n\n$sql\n\n$@\n";}
return \@all_results;
}

############################################
sub Write {
my ($pkg, $sql, $data) = @_;
my $prepare;

#warn "\n$sql\n";
eval {


$prepare = $pkg->{dbh}->prepare($sql);

foreach(@{$data})
{
	if(ref($_) eq "ARRAY")
	{
	#	warn "\n >> @{$_} <<\n";
		$prepare->execute(@{$_});
	}
	else
	{
	#	warn "\n ARR>> $_ <<\n";
		$prepare->execute($_);
	}
		
}
	
$prepare->finish();
};if($@){$pkg->{dbh}->rollback(); die "write :\n\n$sql\n\n$@\n";}

}

############################################
sub WriteLiteral {
my ($pkg, $sql) = @_;
my $prepare;

#warn "\n$sql\n";
eval {


$prepare = $pkg->{dbh}->prepare($sql);
$prepare->execute();
$prepare->finish();

};if($@){$pkg->{dbh}->rollback(); die "write :\n\n$sql\n\n$@\n";}

}





###########################################
sub PrepareLookup {
my ($pkg, $sql) = @_;
eval {

	$pkg->{Prepare} = $pkg->{"dbh"}->prepare($sql);

};if($@){die "PrepareLookup :\n\n$sql\n\n$@\n";}

}

sub GetLookup {
my ($pkg, $value) = @_;
eval {

if(ref($value) eq "ARRAY")
{
	#	warn "\n >> @{$_} <<\n";
	$pkg->{Prepare}->execute(@{$value});
}
else
{
	#	warn "\n ARR>> $_ <<\n";
	$pkg->{Prepare}->execute($value);
}

};if($@){die "GetLookup :\n\n$@\n";}
return $pkg->{Prepare}->fetchall_arrayref();
}

sub FinishLookup {
my ($pkg) = @_;

$pkg->{Prepare}->finish();
undef $pkg->{Prepare};

}

sub Lookup {
my ($pkg, $sql) = @_;
my $prepare;
my $ref;
eval {


$prepare = $pkg->{"dbh"}->prepare($sql);
$prepare->execute();
$ref = $prepare->fetchall_arrayref();
$prepare->finish();
};if($@){die "Lookup :\n\n$sql\n\n$@\n";}
return $ref;

}
############################################
sub IDrole {
my $pkg = shift;

if(@_)
{
	$pkg->{IDrole} = shift;
}
else
{
	return $pkg->{IDrole};
}


}

############################################
############################################
sub Whoami {
my $pkg = shift;

if(!$pkg->{Who}){$pkg->{Who} = $ENV{USER};}

if(@_)
{
	$pkg->{Who} = shift;
	$pkg->{Who} = substr($pkg->{Who},0,29);
}
else
{
	return $pkg->{Who};
}


}

############################################
sub Lock {
my $pkg = shift;
if(@_)
{
	undef $pkg->{Lock};
}
else
{
	return $pkg->{Lock};
}

}

############################################
sub GetLock {
my ($pkg,$data) = @_;
my $prepare;
my $sql;


$pkg->{"dbh"}->commit();
my $l = $pkg->Lock();

$pkg->Lock(1);


$prepare = $pkg->{"dbh"}->prepare($sql);

eval {
foreach(@{$data})
{
		
}
};if($@ =~ /ORA-00054:/)
{	
	
	return 999;
}	
if($@){die "Error Getting Data Lock";}


}
############################################




sub Projectstatus {
my ($pkg,$RefProject, $status,$by, $set) = @_;

if($set)
{
$sql = "	SELECT 	PS.PROJECTNAME	
		FROM 	PROJECT_STATUS PS
		WHERE	PS.ISCURRENT	= 1		
		AND	PS.PROJECTNAME	= ?";
}
elsif($status == 31)# Cleared For Library
{
$sql = "	SELECT 	PS.PROJECTNAME	
		FROM 	PROJECT_STATUS PS
		WHERE	PS.STATUS 	IN (5,7,9)	
		AND	PS.ISCURRENT	= 1
		AND	PS.PROJECTNAME	= ?";
}
else
{
$sql = "	SELECT 	PS.PROJECTNAME	
		FROM 	PROJECT_STATUS PS
		WHERE	PS.STATUS 	= $status - 1	
		AND	PS.ISCURRENT	= 1
		AND	PS.PROJECTNAME	= ?";	
}

my $RefProjectForUpdate = $pkg->Retrieve($sql,$RefProject);

$pkg->Write("
UPDATE 	PROJECT_STATUS
SET	ISCURRENT	= 0
WHERE	ISCURRENT	= 1
AND	PROJECTNAME	= ?",
$RefProject);


$pkg->Write("
INSERT INTO PROJECT_STATUS ( PROJECTNAME, STATUS, STATUSDATE, ISCURRENT, SESSIONID, PROGRAM, OPERATOR)
VALUES ( ?, $status, SYSDATE,1,SEQ_SESSION.CURRVAL,\'$pkg->{\"Program\"}\',\'$by\')",
$RefProject);
}
1;
