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

# written by troy sometime in september 2005

my $documentation = 
"
-w                              write to database
-d  name\/password\@database    default is /\@cdfonprd
-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:h');


die $documentation if $Getopt::Std::opt_h;
    

my $file = "passcalibs.dat";    

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

my $db_string = "/\@cdfonprd";
if ($Getopt::Std::opt_d) {$db_string = $Getopt::Std::opt_d}

my ($user,$password,$dbname) = split(/[\/\@]/,$db_string);

#$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;

    if (!$version)
    {
      die "failure: error in $file. Each line must have 4 columns (is there a blank line?)";
    }

    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]/ )
    {
	die "failure: error in $file all values must be numeric for $insert_values";
    }
    if ($lorun != $hirun) 
    {
	die "failure: error in $file lorun and hirun must be equal for $insert_values";
    } 

    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;