#! /cdf/scratch/cdfopr/cdfcalib/products/bin/perl -w

# written by troy sometime in september 2005

my $documentation = 
"
-d (database)  name\/password\@database
-w (writemode) write to database
-f (filename)  default is passcalibs.dat 

input file must be of format:

PASS_INDEX   LORUN   HIRUN   PROC_CALIB_VERSION

";

use DBI;
use Getopt::Std;

getopts('d:wf:');


if(!$Getopt::Std::opt_d)
{
    print $documentation;
    exit 0;
}


print "Getopt::Std::opt_d\n";
my $file = "passcalibs.dat";    

if ($Getopt::Std::opt_f) {$file = $Getopt::Std::opt_f}


my ($user,$password,$dbname) = split(/[\/\@]/,$Getopt::Std::opt_d);

#$dbh = DBI->connect('dbi:Oracle:cdfonprd','/','') 

if ($Getopt::Std::opt_w)
{
    print "connecting to $dbname and inserting values from $file\n";
}
else
{
    print "writing sql to screen\n";
}

$dbh = DBI->connect("dbi:Oracle:$dbname",$user,$password) 
    or  die "Error connecting $DBI::errstr\n";

my $sql ="insert into passcalibs(pass_index,lorun,hirun,proc_calib_version)";    
$sth = $dbh->prepare("$sql\n values(?,?,?,?)") 
    or die "failure: \n$DBI::err\n";

open FLH,"< $file" or die "cant open $file";


while (<FLH>)
{
    my ($pass_index,$lorun,$hirun,$version) = split;

    my $insert_values = "($pass_index,$lorun,$hirun,$version)\n";
    
# check the input file
    
    if ($pass_index =~ /[^0-9]/ 
	| $lorun    =~ /[^0-9]/ 
	| $hirun    =~ /[^0-9]/ 
	| $version  =~ /[^0-9]/ )
    {
	print "failure: error in $file all values must be numeric for $insert_values";
	next;
    }
    if ($lorun != $hirun) 
    {
	print "failure: error in $file lorun and hirun must be equal for $insert_values";
	next;
    } 

    if($Getopt::Std::opt_w)
    {
	$sth->execute($pass_index,$lorun,$hirun,$version);
    
	if ($DBI::err) {print "\n"} # the database prints the error
	else {print "write successfull: $sql\nvalues$insert_values"}
    }
    else {print "$sql\nvalues$insert_values"}
}


$dbh->disconnect();

exit;
