#! perl
# written by pinghan sometime in september 2005
# This script can read PROD_PHYSICS_COT or PROD_PHYSICS_SVX ,etc. 
# It can print out its cid table and the same table in PROD_PHYSICS_CDF

# checks the latest PROC_CALIB_VERSIONS of PROCESS_NAME $process 
# that are not PASSNAME $pass starting with run $start_run

# command line:
# $perl cid_plot.pl > datafile.dat

#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";
$date = `date`;

$start_run = 206990;
$end_run   = 212133;
$pass      = '17'; 
#version 1 
#$process = 'L1_PHYSICS_FWD';  
#$process = 'L3_PHYSICS_CALTDC';  
#$process = 'L3_PHYSICS_CDF';  
#$process = 'PROD_PHYSICS_TOF';  
#version 2
#$process = 'PROD_PHYSICS_COT';  
#version 3
# $process = 'PROD_PHYSICS_SVX';  
$process = 'PROD_PHYSICS_CDF';  
#version 4
#$process = 'PROD_PHYSICS_MPA';  
#$process = 'PROD_PHYSICS_SVXALIGN';  
#$process = 'CRATES_PHYSICS_CAL';  
#$process = 'FIB_PHYSICS_SVX';  
#$process = 'L1_PHYSICS_CAL';  
#$process = 'L3_PHYSICS_SMX';  
#$process = 'L3_TEST_CDF';  
#$process = 'PAD_PHYSICS_COTBEAM';  
#$process = 'PAD_PHYSICS_SVXBEAM';  
#$process = 'PROD_PATCH_CAL';  


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

$pass_index = &get_pass_index($dbh, $pass);

# print "$pass_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($pass_index,$start_run,$end_run);

while (my $result = $sth->fetchrow_hashref)
{
  my $run= $result->{'PROCESS_RUN'};

#if you want to apply "good silicon selection"
#  if(&get_good_silicon($dbh,$run))
#  {

#   get information of 'PROD_PHYSICS_SVX' or 'PROD_PHYSICS_COT' etc.
    my $latest_version = &get_latest_version($dbh,$run, $process);
    my $latest_jobset  = &get_jobset($dbh,$run, $process,$latest_version);

#   get information of 'PROD_PHYSICS_CDF'
    my $pass_version   = &get_pass_version($dbh,$run, $pass_index);
    my $pass_jobset    = &get_jobset($dbh,$run,'PROD_PHYSICS_CDF',$pass_version);
   
    my %pass_hash;
    my %latest_hash;
     
    &get_tableids($dbh,$latest_jobset,\%latest_hash);
    &get_tableids($dbh,$pass_jobset,\%pass_hash);
    while (my ($latest_table,$latest_cid) = each (%latest_hash))
    {
      my $pass_cid = $pass_hash{$latest_table};

#      if ($pass_cid != $latest_cid)
#      {
        print "$run\t$latest_table\t$latest_cid\t$pass_cid\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;
}
