# subroutines for common queries of cdfonprd database

# first written by troy sometime in september 2005

# don't list revisions here, make sure current version is checked into cvs
# and then check in new version with commments

sub get_tableids
{
#Arguments: 1) database handle 2) jobset 3) hash to return tables

# fills hash with key = table_name and value = table_cid 
    
    my ($dbh, $jobset, $table_ref) = @_;

    my $sql = "select calibrunlists.calib_table,calibrunlists.cid
               from calibrunlists,set_run_maps 
               where calibrunlists.cid = set_run_maps.cid 
               and set_run_maps.jobset = ?";
    
    
    my $sth = $dbh->prepare($sql);
    
    $sth->execute($jobset);
    
  # There is probably a faster way to do this like binding columns or something

    while (my @row = $sth->fetchrow_array)
    {
	${$table_ref}{$row[0]} = $row[1];
    }
    
    return 1;
}

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

sub get_jobset
{
#Arguments: 1) database handle 2) process_run 3) process_name 4) proc_calib_version

  my ($dbh, $run, $name, $pcv) = @_;

  my $sql = "select jobset from used_sets 
  where process_run = ?
    and process_name like ?
    and proc_calib_version = ?";

  my $sth = $dbh->prepare($sql);

  $sth->execute($run, $name, $pcv);

  my $jobset =  $sth->fetchrow;

  return $jobset if ($jobset);

  print "run $run has no jobset with name $name and version $pcv\n";
  return;

}

#-------------------------------------------------------------
sub get_pass_version
{
#Arguments: 1) database handle 2) process_run 3) pass_index 

    my ($dbh, $run, $pass_index) = @_;

    my $sql = "select proc_calib_version from passcalibs 
               where lorun = ? and pass_index = ?";  
    
    my $sth = $dbh->prepare($sql);
    
    $sth->execute($run, $pass_index);
    return $sth->fetchrow;
}

#-------------------------------------------------------------
sub get_latest_version
{
#Arguments: 1) database handle 2) process_run 3) process_name 

# for some reason the max function doesnt work for the proc_calib_version field

  my ($dbh, $run, $name) = @_;

  my $sql = "select proc_calib_version from used_sets 
  where process_run = ?
    and process_name like ?";

  my $sth = $dbh->prepare($sql);
  $sth->execute($run, $name);
  $latest = 0;
  while($row = $sth->fetchrow)
  {
    if ($row > $latest)
    {
      $latest = $row;    
    }
  }

  #unless ($latest) {print "run $run has no process_name $process_name";}
  
  return $latest;
}

#-------------------------------------------------------------
sub get_pass_index
{
#Arguments: 1) database handle 2) passname 

    my ($dbh, $name) = @_;

    my $sql = "select pass_index from passes 
               where passname = ? and retired is null
               and process_name = 'PROD_PHYSICS_CDF'";
    
    my $sth = $dbh->prepare($sql);
    
    $sth->execute($name);
    my $index = return $sth->fetchrow;
    return $index if($index);

    die "cannot find pass $passname\n";
}
# ___________________________________________________________
sub list_tables
{
# Arguments: i)dbh ii) jobset

# returns a string of all the tables and their create date 

  my ($dbh, $jobset) = @_;

  my %table;

  &get_tableids($dbh,$jobset,\%table);

  my $list;
  my $count = 0;

  while (my ($table,$cid) = each (%table)) 
  {
    my $cid = $table{$table};
    my $date = &get_cid_date($dbh,$cid);
    $list .= "$table\t$cid\t$date\n";
    ++$count;	    
  }

  $list .= "jobset $jobset contains $count tables\n";
  return $list;
}
# ---------------------------------------------------
sub diff_tables
{
# Arguments: i)dbh ii) jobsetA iii) jobsetB

# checks the table names and cid's in jobsetA are in jobsetB
# returns a string of differences found

  my ($dbh, $jobsetA, $jobsetB) = @_;

  my (%tableA, %tableB);

  &get_tableids($dbh,$jobsetA,\%tableA);
  &get_tableids($dbh,$jobsetB,\%tableB);

  my $diff;


  while (my ($table,$cidA) = each (%tableA)) 
  {
    my $cidB = $tableB{$table};
    if ($cidB)
    {
      if($cidB != $cidA) 
      {
	my $dateA = &get_cid_date($dbh,$cidA);
	my $dateB = &get_cid_date($dbh,$cidB);

	$diff .= "$table\t$cidA ($dateA)\t$cidB ($dateB)\n"	    
      }
    }
    else
    {
      my $dateA = &get_cid_date($dbh,$cidA);
      $diff .= "$table\t$cidA ($dateA)\tMISSING\n";
    }
  }

  return $diff;
}

#-----------------------------------------------------------------
sub diff_all_tables
{
# Arguments: i)dbh ii) jobsetA iii) jobsetB

# compares two jobsets and returns the tables and cids that are not
# common to both


  my ($dbh, $jobsetA, $jobsetB) = @_;

  my (%tableA, %tableB);

  &get_tableids($dbh,$jobsetA,\%tableA);
  &get_tableids($dbh,$jobsetB,\%tableB);

  my $diff;


  while (my ($table,$cidA) = each (%tableA)) 
  {
    my $cidB = $tableB{$table};
    if ($cidB)
    {
      if($cidB != $cidA) 
      {
	my $dateA = &get_cid_date($dbh,$cidA);
	my $dateB = &get_cid_date($dbh,$cidB);

	$diff .= "$table\t$cidA ($dateA)\t$cidB ($dateB)\n"	    
      }
      delete $tableB{$table};
    }
    else
    {
      my $dateA = &get_cid_date($dbh,$cidA);
      $diff .= "$table\t$cidA ($dateA)\tMISSING\n";
    }
  }

  while (my ($table,$cidB) = each (%tableB))
  { 
    my $dateB = &get_cid_date($dbh,$cidB);
    $diff .= "$table\tMISSING\t$cidB ($dateB)\n";
  }

  return $diff;

}

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

sub get_good_silicon
{
#Arguments: 1) database handle 2) process run

  my ($dbh, $run) = @_;

  my $sql = "SELECT RUNCONFIGURATIONS.RUNNUMBER FROM RUNSETS, RUNCONFIGURATIONS
  WHERE RUNCONFIGURATIONS.RUNNUMBER = ?
  AND   RUNCONFIGURATIONS.RS_NAME = RUNSETS.NAME
  AND   RUNSETS.USESRC = 1
  AND   RUNCONFIGURATIONS.ACTIVATETIME IS NOT NULL
  AND   (RUNSETS.RUNTYPE = 1 OR RUNSETS.RUNTYPE = 2)";

  my $sth = $dbh->prepare($sql);

  $sth->execute($run);
  return $sth->fetchrow;
}

#-----------------------------------------------------------
sub get_cid_date
{
#Arguments: 1) database handle 2) cid 3) optional date format

# pass 'monkeys' as the third argument to return unix time otherwise
# human date is returned

# returns the create date from a table cid

  my ($dbh, $cid, $date_format) = @_;

  my $sql;
  if ($date_format)
  {
    $sql = "select create_date from calibrunlists where cid = ? ";	
  }
  else
  {
    $sql = "select hdwdb.unix_to_date(create_date)
    from calibrunlists where cid = ? ";
  }

  my $sth = $dbh->prepare($sql);
  $sth->execute($cid);
  return $sth->fetchrow;
}    
# -------------------------------------------------------
sub get_max_run # USE get_run_range for consistentcy
{
# arguments: database handle

  my $dbh = $_[0];    
  my $sth = $dbh->prepare("select max(distinct process_run) from used_sets"); 

  $sth->execute();
  return $sth->fetchrow;
}
# -----------------------------------------------------------------
sub db_set_merge
{
# arguments:
# i) run 
# ii) jobsets/process_names (separated by a space)  
# iii) mode ($mode = 'write' to write to db) 

# this merges the specified jobsets into a new version of PROD_PHYSICS_CDF

  # set the environment variables if necessary
  if ( $ENV{ 'PATH' } !~ /cdfcalib\/products/ )
  {
    #$ENV{'ORACLE_HOME'} = '/home/cdfsoft/products/oracle_client/v9_2_0_1a/Linux+2';
    $ENV{'ORACLE_HOME'} = $ENV{'CDFSOFT2_DIR'}.'/../../../products/oracle_client/v9_2_0_1a/Linux+2';
    #$ENV{'TNS_ADMIN'} = '/home/cdfsoft/dist/oracle_tnsnames';
    $ENV{'TNS_ADMIN'} = $ENV{'CDFSOFT2_DIR'}.'/../../oracle_tnsnames';
    $ENV{ 'PATH' }            .= ':/cdf/scratch/cdfopr/cdfcalib/products/bin/';
    $ENV{ 'LD_LIBRARY_PATH' } .= ':/cdf/scratch/cdfopr/cdfcalib/products/lib/';
  }



  my ($run, $cdf_jobset, $jobsets, $mode) = @_;

  if ( !$mode ) { $mode = 'test' }

  use Shell 'DBSetMerge';
  $Shell::capture_stderr = 1; # capture the error as well


  my $txt =  DBSetMerge('TESTMODE','OTL', '/@cdfonprd','used',$run,
    'PROD_PHYSICS_CDF', $cdf_jobset,$jobsets);    

  if ( $mode =~ /write/i )
  {
    $txt .=  DBSetMerge('OTL', '/@cdfonprd','used',$run,
      'PROD_PHYSICS_CDF',$cdf_jobset,$jobsets);

  }

  return $txt;

}
# ---------------------------------------------------------------------
sub write_passcalib
{
# write a line to FILE in correct format for pass_calib_tool.pl

# eg  &write_passcalib( *FILE{IO}, $pass_index, $run, $version)

  my ( $fh, $pass_index, $run, $version ) = @_;

  print $fh "$pass_index     $run     $run     $version", "\n";
}
# ----------------------------------------------------------------
sub get_version_create_date
{
    my ( $dbh, $run, $version, $name ) = @_;

    my $sth = $dbh->prepare("select hdwdb.unix_to_date(create_date) from used_sets
                          where process_name like ?
                          and proc_calib_version = ?
                          and process_run = ?");


    $sth->execute( $name, $version, $run );
    return $sth->fetchrow;
}

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

sub get_all_pass_index
{
# arguments 1) database handle 2) pass_name
# prints all the pass_index's associated with a specific pass

    my ($dbh,$pass_name) = @_;
    
    my $txt = "listing all pass index's for pass $pass_name\n";
    my $sth = $dbh->prepare("select pass_index,retired from passes where passname like ? order by pass_index desc");
    $sth->execute($pass_name);	    
    while(@row = $sth->fetchrow_array)  {$txt .= "$row[0]    $row[1]\n"}

    $txt =~ s/YES/RETIRED/g;    

    return $txt;
}

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

sub get_missing_good_runs
{
# returns an array of good runs (farms definiton minus csl_summary) that are not in 17A 

# Arguments: 1) database handle 2) start run 3 array to return runs 
# fills hash with key = run and value = run terminate time 

  my ( $dbh, $start_run, $run) = @_;

  my $sql = 
  "select rc.runnumber
  from runconfigurations rc, run_status rs, physicstables pt
  where rs.runcontrol_status != 0 
    and rs.shiftcrew_status != 0
    and rc.b0integratedlivelumi > 10.0
    and rc.csl_neventsoutput > 1000
    and not pt.physicstablename like '%TEST%'
    and (pt.physicstablename like '%Phyiscs' or pt.physicstablename like '%PHYSICS%')
    and rc.runnumber not in (select lorun from passcalibs where pass_index = 
  ( select pass_index from passes where passname like '17A' and retired is null) )
    and pt.runnumber = rc.runnumber
    and rs.runnumber = rc.runnumber
    and rc.runnumber > ?
  order by rc.runnumber"; 

  my $sth = $dbh->prepare($sql);
  $sth->execute($start_run);

  while (my $row = $sth->fetchrow) { push @$run, $row }

  return 1;
}
#------------------------------------------------------------

sub get_run_age
{
# arguments: 1) database handle 2) run number
# returns the age of a run in hours rounded to the nearest hour

  my ( $dbh, $run) = @_;
  my $sql = 
  "select (select hdwdb.date_to_unix(sysdate) from dual) 
  - hdwdb.date_to_unix(terminatetime) 
  from runconfigurations where runnumber = ?";

  my $sth = $dbh->prepare($sql);
  $sth->execute($run);

  return int( $sth->fetchrow / (60*60) + .5);

}
#------------------------------------------------------------------
#
sub get_run_range
{

  # the start and end run passed by reference are set and a strin gis returned
  # with the text version of the run range

  my $doc = 
  '
  invalid run range option -r usage.  Examples:

  -r 9999
  -r 9999-last
  -r 9999-10000

  ';

  my ( $dbh, $start_run_ref, $end_run_ref, $option_r) = @_;

  $$start_run_ref = 1;
  $$end_run_ref   = 'latest';
  $run_txt = "entire run range";

  if ( $option_r )
  {
    ( $$start_run_ref, $$end_run_ref ) = split(/-/, $option_r);


    if ( !$$end_run_ref ) 
    { 
      $$end_run_ref = $$start_run_ref;
      $run_txt = "for run $$start_run_ref";
    }
    else { $run_txt = "for run range $$start_run_ref to $$end_run_ref"; }

    if ( $$start_run_ref =~ /[^0-9]/ ) { die $doc; }

    if ( $$end_run_ref !~ /[^0-9]/ && $$start_run_ref > $$end_run_ref) 
    {
      die "start run must be smaller than end run\n";
    }

    if ( $$end_run_ref !~ /[^0-9]/ ) 
    { 
      return $run_txt; 
    }

  }

  if ( $$end_run_ref =~ /L/i )
  {
    my $sth = $dbh->prepare("select max(distinct process_run) from used_sets"); 
    $sth->execute();
    $$end_run_ref = $sth->fetchrow;
    $run_txt = "for run range $$start_run_ref to latest ($$end_run_ref)";
    return $run_txt;
  }

  die $doc;
}
1;
