#!/home/cdfsoft/products/perl/v5_8/Linux+2.4/bin/perl 
# written by pinghan sometime in september 2005
# This script can read table name from table.dat and print its cid value 
# in two different tables (but have the same cid table).

#use lib '/home/cdfsoft/products/perl_dbd_oracle/v5_8_9201/Linux+2.4/lib/site_perl/5.8.0/i686-linux-thread-multi/';
# load the perl subroutines for qb queries

use lib '/cdf/scratch/cdfopr/cdfcalib/perl_libs';
require "db_queries.pl";
require "Personal.pm";

$date = `date`;
print "TALBES IN PASS 17 AND 17A ran on $date";

$start_run = 190000;
$end_run   = 205000;
#$start_run = 190921;
#$end_run   = 190921;
$pass1 = '17'; 
$pass2 = '17A'; 
$process ='PROD_PHYSICS_CDF';

# set the relavent email addresses
$recipients = 'pchu@fnal.gov'; 

use DBI;

$dbh = DBI->connect('dbi:Oracle:cdfonprd','cdf_reader','reader') 
	or die "Error connecting $DBI::errstr\n";


# we are only interested in runs with version 2 and in pass specified pass


$pass1_index = &get_pass_index($dbh, $pass1);
$pass2_index = &get_pass_index($dbh, $pass2);

print "$pass1_index\n";
print "$pass2_index\n";

$sql = "SELECT CUS.PROCESS_RUN
        FROM USED_SETS CUS, PASSCALIBS CPSC
        WHERE CUS.PROCESS_RUN >= CPSC.LORUN AND CUS.PROCESS_RUN <= CPSC.HIRUN
        AND CUS.PROCESS_NAME = 'PROD_PHYSICS_CDF'
        AND CPSC.PASS_INDEX  = ?
        AND CUS.PROCESS_RUN >= ?
        AND CUS.PROCESS_RUN <= ?
        AND CUS.PROC_CALIB_VERSION = CPSC.PROC_CALIB_VERSION
        ORDER BY CUS.PROCESS_RUN";

$sth = $dbh->prepare($sql) or die "Error reading DBI::errstr\n";
$sth->execute($pass1_index,$start_run,$end_run);
#$sth->execute($pass1_index,$start_run);

while (my $run = $sth->fetchrow)
{
    my $latest_version   = &get_pass_version($dbh,$run, $pass1_index);
    my $latest_jobset    = &get_jobset($dbh,$run,$process,$latest_version);

    my $pass_version   = &get_pass_version($dbh,$run, $pass2_index);
    my $pass_jobset    = &get_jobset($dbh,$run,$process,$pass_version);

    open INPUT, "<table.dat" or die $!;
    while (<INPUT>)
    {
      my @latest_tables = split /\n/, $_;
      while(<@latest_tables>)
      {    
        $table_name = $_;
        my $latest_cid = Personal::get_cid($dbh,$latest_jobset,$table_name);
        my $pass_cid = Personal::get_cid($dbh,$pass_jobset,$table_name);
        my $latest_cid_date = Personal::get_cid_date($dbh,$latest_cid);
        my $latest_cid_update_date = Personal::get_cid_update_date($dbh,$latest_cid);
        if ($latest_cid)
        {
          print "$run\t$table_name\t$latest_cid\t$latest_cid_date\t$latest_cid_update_date\n";
        }
      }
    }
}


$dbh->disconnect();

exit;

#-----------------------------------------------------------------
sub send_mail
{
#Arguments 1) email addresses separated my commas 2) content 3) subject
    my $mail = '/bin/mail';
    open MAIL, "| $mail -s \"CdfCalib Monitoring: $_[2]\" $_[0]";
    print MAIL $_[1];
    close MAIL;
}
