#!/usr/bin/perl
#This scripts takes a user supplied direcory of xml files that passed validation and 
# inserts them into the Oracle database

use Getopt::Long;
use File::Copy;
use DBI;

#------------------------------------getting the date---------------------------------------------------------------------------------------------------------
my $now=`date`;
$mon = substr($now,4,3);
$day=substr($now,8,2);
$year =substr($now,24,4);
$time= substr($now,11,5);

if ($day < 10){
$daynuma = substr($day,1,1);
$daynum = "0$daynuma";
}else{
$daynum=$day;
};

#converting the date to a number
if($mon eq "Jan") {
$monnum="01";
}elsif($mon eq "Feb"){
$monnum= "02";
}elsif($mon eq"Mar"){
$monnum="03";
}elsif($mon eq "Apr"){
$monnum="04";
}elsif($mon eq "May"){
$monnum="05";
}elsif($mon eq "Jun"){
$monnum="06";
}elsif($mon eq "Jul"){
$monnum="07";
}elsif($mon eq "Aug"){
$monnum="08";
}elsif($mon eq "Sep"){
$monnum="09";
}elsif($mon eq "Oct"){
$monnum="10";
}elsif($mon eq "Nov"){
$monnum="11";
}elsif ($mon eq "Dec"){
$monnum="12";
};


$dateString =$year . $monnum . $daynum;
#------------------------------------------------------------------------------------------
$ENV{ORACLE_SID}='devpcrac4';
$ENV{ORACLE_ASK}='NO';
$env{CLASSPATH}='/usr/java/jdk1.5.0_11/lib:/opt/saxon/saxon8sa.jar:/opt/saxon/saxon8-dom.jar:/opt/saxon/';

#setting the mrf directory variable
#$dira ="/nfs/gpms/output/mrf";
$dira ="/mtdata/geo/gpms/output/mrf";

sub usage() {
	print"Usage: $0 [-directory=<directory>][-password=<password>]\n";
	print"Example: $0 -directory=matt -password=abc \n";
	print"where matt is the directory under $dira and abc is the password\n";
}
%gOptions;
&GetOptions(\%gOptions, qw(directory=s password=s));
unless ($gOptions{directory} and $gOptions{password}) {
	usage();
	exit;
};

#setting the other directory variables and the password variable
$dirb = $gOptions{directory};
$pass = $gOptions{password};
chomp($pass);
$dir= $dira . "/" . $dirb;
#print "\$dir=$dir\n";


#checking to make sure that the directory exists
if (-e $dir){
print"Please Wait, now reading $dir.\n";
}else{
print"Now terminating $0!\n";
print"The directory, $dirb, could not be found in $dira! \n";
die "Please make sure the directory, $dirb, was spelled correctly and is located in $dira.\n";
}; 

#changing directory
chdir $dir;

#getting branch to insert into database
$dirblength =length($dirb)-14;
$firstbranch = substr($dirb, 0, $dirblength);


#getting all the xml files in $dir
@mrf =glob "*.mrf";

#creating the Oracle Log

if (-e "/nfs/gpms/log/$dirb"){
}else{
mkdir "/nfs/gpms/log/$dirb" or warn "Cannot make $dirb: $!";
};

if ( -e "/nfs/gpms/log/$dirb/OracleLog.log"){
unlink "/nfs/gpms/log/$dirb/OracleLog.log";
}

open ORACLELOG, ">/nfs/gpms/log/$dirb/OracleLog.log";


my $dbh = DBI->connect( 'dbi:Oracle:devpcrac',

                        'gpms',
                        $pass,
                        {
                          RaiseError => 1,
                          AutoCommit => 1
                        }
                      ) || die "Database connection not made: $DBI::errstr";










$len= length($DBI::errstr);

if ($len > 0){
printf ORACLELOG "$DBI::errstr \n"; 
print "$0 is now terminating\n";
die
};

$counter=1;

printf ORACLELOG "The files from gpms/output/mrf/$dirb were inserted on $monnum/$daynum/$year \n ";
printf ORACLELOG "for the $firstbranch project.\n";
		     
#run a loop here
foreach $mrf (@mrf){
#inserting all the xml files into the Oracle database
print"\n $counter: Now inserting $mrf into the Oracle database\n";
$ins = "'/output/mrf/$dirb/$mrf'";
$branch ="'$firstbranch'";
#orginal method
my $sql =qq{INSERT INTO metadata_vw VALUES( NULL, $branch, xmltype ( bfilename('GPMS_DIR', $ins), nls_charset_id('WE8ISO8859P1') ) )};#experimental3
#print "\$sql =$sql\n";
my $sth = $dbh->prepare( $sql );
#print "OK so far\n";
#always need execute
$sth->execute() or warn "Can't execute SQL statement:",  $sth->errstr(), "\n";
#$sth->execute();
#getting the length of the error string produced by $sth->errstr(). If the length is greater than zero, something is wrong with the xml file being inserted
$len= length($sth->errstr());
#print "\$len =$len\n";

if ($len > 0){
printf ORACLELOG "\n$mrf was not inserted into the Oracle Database. Please see the reasons for this below:\n";
printf ORACLELOG $sth->errstr(), "\n";
printf ORACLELOG "\n";
$fail++;
}else{

#getting the curval
print "Now getting the current id number for $mrf in the Oracle database.\n";
my $sql = qq{SELECT ID_seq.currval FROM metadata_vw};
my $sth = $dbh->prepare($sql);

#2 execute the statement within the database
$sth->execute() or warn "Can't execute SQL statement:",  $sth->errstr(), "\n";
#print "stage 2  curval completed\n";

#3the fetch stage
my($id);

$sth->bind_columns( undef, \$id,);

while ($sth->fetch()) {
	#print "\$id = $id\n";
}

$idleng=length($id);
#print "\$idleng = $idleng\n";

if ($idleng ==1){
$newid ='00' . $id;
}elsif($idleng ==2){
$newid ='0' . $id;
}else{
$newid = $id;
};

#print "\$newid = $newid\n";

#getting the id from the metadata table
my $sql = qq{SELECT VID FROM metadata_vw};
my $sth = $dbh->prepare($sql);

#2 execute the statement within the database
$sth->execute() or warn "Can't execute SQL statement:",  $sth->errstr(), "\n";

#3the fetch stage
my($metaid);

$sth->bind_columns( undef, \$metaid,);



while ($sth->fetch()) {
       $lastthree=substr($metaid,-3,3);
	if ($lastthree eq $newid){
        #print "match found in the Metadata Table\n";
	$finalid = $metaid;
	$match='yes'

};

}



if ( $match eq 'yes'){
print "\n$mrf was inserted into the Oracle Database. It has an id number of $finalid.\n"; 
printf ORACLELOG "\n$counter: $mrf was inserted into the Oracle Database. It has an id number of $finalid.\n";
$match='no';
#print "has id \n";
}else{
print "\n$mrf was inserted into the Oracle Database. Its id number is unavailable. It is probably a duplicate of a
record that is already in the Metadata Table.\n";
printf ORACLELOG "\n$counter: $mrf was inserted into the Oracle Database. Its id number is unavailable. It is probably a ";
printf ORACLELOG "duplicate of a record that is already in the Metadata Table.\n";
#print "no id!\n";
};

$success++;
};
$counter++;
};


$dbh->disconnect();




$total = $fail+ $success;
$persuc = ($success/$total) * 100;
$perfail = ($fail/$total) *100;

if ($fail > 0){
print $sth;
printf ORACLELOG $sth;
};

if ($success >0) {
print "\n$success, or $persuc%, of your files were successfully loaded into the Oracle database \n";
printf ORACLELOG "\n$success, or $persuc%, of your files were successfully loaded into the Oracle database \n";
};

print "Please see the Oracle Log at /nfs/gpms/log/$dirb/OracleLog.log\n "


#remooving files
#print"now removing files from the $dir direcory\n";
#chdir $dir;
#foreach $mrf (@mrf){
#system "rm $mrf";
#};
#chdir $dira;
#system "rm $dir";