package Database;
use WrapDBI;


sub new {

my ($pkg)  = @_ ;
my $setup = {};

bless $setup,$pkg;

$setup->Initialise();

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

if($check eq 'dev1' )
{
	$pkg->{User}		= 'dev1';
	$ENV{TWO_TASK} 		= 'old_sids';

}
else
{
	$pkg->{User}		= 'platetrack';
	$ENV{TWO_TASK} 		= 'sids';
}

}

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



if((!$who)||($who =~ /^ND/))
{
	my $ArrayRef ;
	$who 	= $pkg->Whoami();
	
	eval{
	$ArrayRef = $pkg->Lookup("SELECT ID_PERSON FROM PERSON WHERE ISCURRENT = 1 AND EMAIL = '$who'");
	$id = $ArrayRef->[0][0];

	};if($@)
	{
	warn "\n\nSorry There Is A Problem Accessing Person Information From Oracle\nSwitching To readonly mode\n\n";
	$level = 'disabled';
	}

}


my $turnips = `ypcat -k aliases`;
my @aliases = split(/\n/,$turnips);

my @t = grep /^bug/, @aliases;


my $superuser = "vt1 cfl cle @t";

$level = 'normal';

$pkg->Whoami($who);
$pkg->IDperson($id);

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

}

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.....\npt++ connecting 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";}


###############################################
# Determine Unique Session ID For connection
# ------ DISABLED, SESION DETERMINED AT write time
###############################################
#$pkg->Session();
#warn "Session ID = $pkg->{Session} \n";
}
}

sub Session {
my $pkg = shift;

my $session = $pkg->Lookup("SELECT SEQ_SESSION.NEXTVAL FROM DUAL");
	
# dn1 disabled
#$pkg->Write("	INSERT INTO USER_SESSION(ID_SESSION, SESSION_DATE, OPERATOR, PROGRAM, ID_PERSON)
#		VALUES(SEQ_SESSION.CURRVAL, SYSDATE, ?, ?, ?)",
#		[[$pkg->{Who}, $pkg->{Program}, $pkg->{IDPERSON}]], 'NO SESSION'); 	


$pkg->{Session} 	= $session->[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, $choose_hash) = @_;


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();


#	my $cap = Data::Dumper::Dumper($ref);
#	warn $cap;
#	warn "----------------------------------------------\n";
#	die if (defined $choose_hash);

	
	# dn1; a switch here ?
	#######################
	if (defined $choose_hash){
	    my %hash;
	    foreach my $r (@$ref) {
		my $id_map = $r->[0];
		my $id_dnaplatemap = $r->[1];
		$hash{$id_map} = $id_dnaplatemap;
	    }
	    push @all_results, \%hash;
	    ##########################################


	} else {
	    foreach my $r (@$ref)
	    {
		# dn1 -> var not used 
		#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, $nosession) = @_;
my $prepare;

eval {

# dn1 disabled
#if(!$nosession){$pkg->Session();}

$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 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 {

$pkg->{Prepare}->execute($value);

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



# dn1
sub GetLookup_nobind {
my ($pkg) = @_;
eval {

$pkg->{Prepare}->execute();

};if($@){die "GetLookup_nobind :\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 IDperson {
my $pkg = shift;

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


}

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

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


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




1;
