#!/usr/local/bin/perl -w

# get_oracle_dbms.pl

# Routine opens a remote connection to the specified Oracle
# data server and issues a select command based on the parameters
# specified via the indirect file read in at run-time.

# Passed parameters
#	indirect file path and filename
#	data level being requested
#	selection specification as
#		MCD_ID=32,MSD_ID=12,...

# Required parameters obtained form the indirect file include
#	datafile=data file path and filename
#	login=Oracle SQLplus login required information as
#		Oracle remote node username and password as
#		groman/jgofs@ORC1
#	tables_for_level_n=necessary database tables as
#		cruises, stations for level 1

# Assumptions
# 	The datafile contains fieldnames separated by tabs
# not commas.
#	The last fieldname within each level specifies the SQL
#key fieldname for the next level.
#	We will use the varlist option of defgb in order to create
#extra variables to accommodate date translations.

$version = "Nov. 25, 1998/V1.03";

# Initial version: November 9, 1998
# November 23, 1998 Add test for "nd_" string in data and replace with "nd".
# November 25, 1998 Change temp file cleanup from 12 hours to 5 days. Remove
#	trailing ">" when outputting fieldname line.

$| = 1;
$error="%x";
$warning="#";

$do_web_sql="/data/pgarrahan/dbaccess/scripts/do_web_sql.csh";
$version = $version;

($indirect_file, $data_level, $key_and_value) = @ARGV;

#print STDOUT ("#Reading data level $data_level by $0 Version: $version\n");

@required = ("login", "tables_for_level_$data_level", "sqlplus",
	"last_level", "sql_script");

#print STDOUT ("#**debug, indirect_file=$indirect_file\n",
#	"\tdata_level=$data_level\n#\tkey_and_value=$key_and_value\n\n");
	
&read_indirect_file($indirect_file);

if (exists $indirect_param{"datafile"} ) {
#	print STDOUT ("#**debug, indirect_param{datafile}=",
#		$indirect_param{"datafile"},"\n");
	&get_field_names($indirect_param{"datafile"}, $data_level)
}
else {
	&sendmessage ($error,
		"No data file specified in indirect file=$indirect_file",
		"Cannot continue.");
	exit;
}

# Output field names

for ($i=0; $i <= $#fieldname; $i++) { print STDOUT ("$fieldname[$i]\t"); }
#unless ($indirect_param{"last_level"} == $data_level ) { print STDOUT (">"); }
print STDOUT ("\n");

$okay="yes";
for ($i=0; $i<=$#required; $i++) {
	unless (exists $indirect_param{"$required[$i]"} ) { 
	   $okay="no"; 
	   &sendmessage ($error,
	     "$required[$i] is missing from configuration file=$indirect_file",
	     " ");
	}
}
if ( $okay eq "no") { 
	&sendmessage ($error,
		"One or more parameters are missing from the configuration file",
		"Cannot continue.");	
	exit;
}

# Test for data at the next level and define $where_name, $where_value
($where_name, $where_value) = split /=/, $key_and_value;
unless ( defined ($where_value) )  { exit; }	#No data at next level.
if ($where_value eq "" or $where_value eq "nd" ) { exit; }
&read_oracle_dbms;

undef $error;
undef $warning;
exit;


#---------------------------------------------
sub read_indirect_file {

# Open and read indirect file specified as first passed parameter $_[0].
# Return the connects of the file as the hash array %indirect_param.
# Lines beginning with "#" are treated as comments.  It is assumed
# that the indirect file contains lines as

#	parameter = value

# and this information is stored as 

#	$indirect_param{"parameter"} = value

my $filename = $_[0];
#print STDOUT ("#**debug, indirect filename=$filename\n");

unless (open INDIRECT, $filename) {
	&sendmessage ($error,
		"Could not open indirect file=$filename",
		"Error code=$!.  Cannot continue.");
	exit;
	}
while (<INDIRECT>) {
	if (m/^#/) { next;}
	($parameter, $value) = split /=/;
	chomp $value;
	$indirect_param{$parameter} = $value;
#	print STDOUT ("#**debug, indirect_param{$parameter}=",
#		$indirect_param{$parameter}, "\n");
}
close INDIRECT;
}
#---------------------------------------------
sub get_field_names {

# Given the full path and file name for the data file as $_[0], open the 
# file and read in and save the field names in @fieldname for the level
# specified as $_[1].  The field name sizes are placed in @fieldname_size.

# Assumes fieldnames are separated by whitespace, not commas.

my ( $i, $length, $level);
#print STDOUT ("#**debug, datafile=$_[0] \n#\tlevel=$_[1]\n");

unless (open DATAFILE, $_[0] ) {
	&sendmessage ($error,
		"Could not open data file=$_[0]",
		"Error code=$!.  Cannot continue.");
	exit;
	}
$level=-1;
while (<DATAFILE>) {
	if (m/^#/ ) { next; }
	$level++;
	if ($level eq $_[1]) {
		chomp;
		@fieldname = split /\s+/;
		$fieldname[0] =~ s/^\s+//;
		unless ($fieldname[0] =~ m/\w*/ ) { shift (@fieldname); }
		last;
	}
}
close DATAFILE;
#print STDOUT ("#**debug, fieldnames=@fieldname\n");

if ($#fieldname < 0 ) {
	&sendmessage ($error,
		"Could not get field names from $_[0]",
		"Please contact the DMO.");
	exit;
}

# Remove width and other parameters set in square brackets
for ($i=0; $i<= $#fieldname; $i++) {
	$fieldname[$i] =~ s/\s+//g;
	$fieldname_size[$i] = $fieldname[$i];
	$fieldname[$i] =~ s/(^.*)\[.*/$1/;
	if ($fieldname_size[$i] =~ m/width=/ ) {
		$fieldname_size[$i] =~ s/^.*\[width=(\d*)\]/$1/;
	}
	else { $fieldname_size[$i] = 0; }
	$length = length ($fieldname[$i]);
	if ($length > $fieldname_size[$i] ) { $fieldname_size[$i]=$length; }
}
if ($fieldname[$#fieldname] =~ m/>/ ) {
	pop @fieldname;
	pop @fieldname_size;
}

return $#fieldname;
}
#---------------------------------------------
sub read_oracle_dbms {

# Routine reads Oracle database specified in $indirect_param{"login"}
# and uses table information from 
#	$indirect_param{"tables_for_level_$data_level"}. 
# Uses @fieldname for the field names (i.e. column names) to retieve.
# Uses $where_name and $where_value defined by calling routine as
# well as several parameters defined in the configuration file.
# See the @required array in the calling program.

# Watch out for:
#   Possible problem has to do with specifying all table names when only
#   one or more are necessary.

my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst);
my ($array_length, $columns, $filename, $fileroot, $file, @files, 
	$id, $id_value, 
	$maxlinesize, $maxpagesize, $maxunderscores, $numb_tables,
	$numb_underscores, $orderby, $record, $sql_script, 
	$sql_input, $tables, $table1, $table2, $temp, $where_clause );

$fileroot = "/tmp/sqlplus";
$sql_script = $indirect_param{"sql_script"};

($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtime(time);
if ($year > 97 and $year < 100 ) {$year = $year + 1900; }
if ($mon < 10) { $mon = "0" . $mon; }
if ($hour < 10) { $hour = "0" . $hour; }
if ($min < 10) { $min = "0" . $min; }
if ($sec < 10) { $sec = "0" . $sec; }

$maxlinesize = 999;
$maxpagesize = 50000;
$maxunderscores = 120;
$filename = $fileroot . "_level" . $data_level . "_" . $year . $mon . $mday . 
	$hour . $min . $sec . ".sql";
#print STDOUT ("#**debug, filename=$filename\n");

chmod 0666, $filename;
# Open sql command file

unless (open SQL, "> $filename" ) {
	&sendmessage ($error,
		"Could not open sql file=$filename",
		"Error code=$!.  Cannot continue.");
	exit;
}	

#Cleanup old files if more than 5 days old
@files = glob ($fileroot . "*.sql");
foreach $file (@files) {
#	print STDOUT ("#**debug, trying to delete file $file\n");
	if ( -M $file  > 5) { unlink ($file) };
}

# Create beginning of sql command file

print SQL <<SQL_BEGIN;
-- JGOFS SQL file $filename
-- Created $year/$mon/$mday $hour:$min by $0
set feedback off
set heading off
set pagesize $maxpagesize
set linesize $maxlinesize
set wrap off
SQL_BEGIN

# Determine columns clause
$columns = "";
for ($i=0; $i <= $#fieldname; $i++) {
	$numb_underscores = abs (length ($fieldname[$i]) - $fieldname_size[$i]);
	if ( $numb_underscores > $maxunderscores) { 
		$numb_underscores = $maxunderscores;
	}
#	print STDOUT ("\n**debug, fieldname[$i]=$fieldname[$i]\n");
#	print STDOUT ("**debug, fieldname_size[$i]=$fieldname_size[$i]\n");
#	print STDOUT ("**debug, numb_underscores=$numb_underscores\n");
	print SQL ("column $fieldname[$i] HEADING \'", $fieldname[$i],
		"_" x $numb_underscores, "\'",
		" NULL \'nd\' \n");
	$columns = $columns . $fieldname[$i] . ", ";
}
$columns =~ s/, $/ /;  #cannot have trailing comma
#print STDOUT ("\n#**debug, columns=$columns\n");

# Determine "order by" clause
if ( exists $indirect_param{"order_by_level_$data_level"} ) {
	$orderby = "order by " . $indirect_param{"order_by_level_$data_level"};}
else { $orderby = "--No order_by_level_" . $data_level . "specified"; }

# Determine "where" clause
$tables = $indirect_param{"tables_for_level_$data_level"};
($table1, $table2, $temp) = split /,/, $tables;
if ($table1 eq $tables) {
	$where_clause =  $indirect_param{"tables_for_level_$data_level"} .
			"." . $key_and_value;
}
elsif ( defined $temp) {
	&sendmessage ($error,
	"Could not define where clause where key_and_value=$key_and_value",
	"and tables=$tables  Cannot continue.");
	exit;
}
else { 
	($table1, $table2) = split /,/, $tables;
	$table1 =~ s/\s//g;
	$table2 =~ s/\s//g;
	$where_clause = $table1 . "." . $where_name . 
			"=" . $table2 . "." . $where_name . " AND " .
			"\n\t" . 
			$table1 . "." . $where_name . "=" . $where_value;
}
	
# Create contents of sql command file
print SQL <<SQL_BODY;
select
$columns
from $indirect_param{"tables_for_level_$data_level"}
where $where_clause
$orderby
;
quit
SQL_BODY
close SQL;

$sql_input = $do_web_sql . " " . $indirect_param{"sqlplus"} . " " .
	$indirect_param{"login"} .
	" @" . $filename . "|";
#print STDOUT ("\n#**debug, sql_input=$sql_input\n");

unless ( open SQL_INPUT, $sql_input ) {
	&sendmessage ($error,
		"Could not open sql input pipe=$sql_input",
		"Error code=$!.  Cannot continue.");
	exit;
}
while (<SQL_INPUT>) {
	$record = $_;
	chomp $record;
	if (length ($record) < 3 ) { next;}
#	print STDOUT ("\n**debug, record=$record\n");
	if ($record =~ m/rows will be truncated/ ) { next;}
	if ($record =~ m/Disconnected from/ ) { next;}
	if ($record =~ m!PL/SQL Release ! ) {print STDOUT ("#$record\n"); next;}
	if ($record =~ m/ERROR/ ) {
		$record = <SQL_INPUT>;
		chomp $record;
		&sendmessage ($error,
		"ERROR returned from remote SQL site",
		"Message: $record\n$error\tSee file $filename\n$error\tCannot continue.");
		close SQL_INPUT;
		exit;
	}
# Handle text fields with embedded blanks such as taxon name.  Replace with _
	if ($indirect_param{"last_level"} == $data_level) {
		$record =~ s/([a-zA-Z_]+) /$1_/g;
	}
	$record =~ s/\s+/\t/g;
	$record =~ s/nd_/nd/g;
	$record =~ s/_nd/nd/g;
	$id = uc $fieldname[$#fieldname];
	@id_value = split /\s+/, $record;
	$array_length=$#id_value;
#	print STDOUT ("\n#**debug, array_length=$array_length\n");
	if ( $array_length < 0 ) { next; }
	print STDOUT ("$record"); 
#	print STDOUT ("\n#**debug, indirect_param{last_level}=",
#		$indirect_param{"last_level"},  " data_level=$data_level\n");	
	if ( $data_level < $indirect_param{"last_level"}) {
#		print STDOUT ("\n#**debug, id=$id and ",
#			"id_value[$array_length]=$id_value[$array_length]\n");
		print STDOUT ("\t(", $sql_script, " ", $indirect_file, 
		" ", $data_level+1, " ", $id, "=", 
		$id_value[$array_length], ")\n");
	}
	else {
		print STDOUT ("\n");
	}
}
close SQL_INPUT;	
return;
}
#---------------------------------------------
sub sendmessage {

#Send a message to the user.
#The message sent will be in the strings $_[0] and $_[1]

my ( $prefix, $message0, $message1);
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst);

$prefix=$_[0];
$message0=$_[1];
$message1=$_[2];
($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtime(time);
print STDOUT ($prefix,"Message from $0\n");
print STDOUT ($prefix," Date of message: $year/$mon/$mday $hour:$min\n");
print STDOUT ($prefix," $message0\n");
print STDOUT ($prefix," $message1\n");
return 0;
}

#---------------------------------------------