#! /bin/tcsh -f

#=========================================================================
#Script to check on runs not yet in pass table.
#if older than 2days it emails the experts and complaint.
#
#author: Dustin McGivern
#
#notes: to be implemented as a cron job on b0dap30 once every hour? day?
#
#
#Date: 8th April 2002
#
#
#Updated 10 06 2003  :  updated to check which used sets are missing and email the experts complaining
#
#Updated 18/01/04 (Ben Cooper) : Include checks on PROD_PHYSICS_CDF pass 11A and if this is incomplete check for missing beamlines
#=========================================================================

#look for any other processes with the same name and exit if you find them
#(we don't want multiple copies of the same script)
ps -fN -p $$ | grep $USER | grep $0 | grep -v grep && exit 5

#setup the software
source ~cdfsoft/cdf2.cshrc
setup cdfsoft2 5.3.3
setup yasql
set aaaaa_oracle_dir = `echo $ORACLE_CLIENT_DIR` 

setup cdfdb


#is the database working? if not exit with no output
dbPing -dcdfonprd -s > /dev/null || exit 6

#move to the right place
cd /data1/cdfcalib/cron_scripts/monitoring/checkCalibs

#get a list of runs the farms want to process that are older than 2 days and younger than one week
#and do not yet have a pass entry.
#spools a file called farmsRuns.lst

sqlplus cdf_reader/reader@cdfonprd @get_runs_farms_are_to_process_new.sql 45

#loop over these runs and ask what version of each used it has

#first touch some files:
touch runs_no_cdf_10.lst
touch runs_no_svx.lst
touch runs_no_cot.lst
touch runs_no_cdf_11.lst
touch runs_no_svxbeam.lst
touch runs_no_cotbeam.lst
touch runs_no_pass10.lst
touch runs_no_pass11.lst

#now loop
foreach run (`grep . farmsRuns.lst`)

    @ bad_run_d = 181101
    @ run_limit = 180000
   
    if(($run >= $run_limit) && ($run != $bad_run_d)) then #these runs have been declared bad but are still on farms list, so want to ignore them
    
	echo "$run"
	sqlplus cdf_reader/reader@cdfonprd @testpasstable.sql 10A $run  #spools a file called runsMissingPasstable
	sqlplus cdf_reader/reader@cdfonprd @testcdf.sql $run  #spools a file called runsMissingCDF
	sqlplus cdf_reader/reader@cdfonprd @testsvx.sql $run  #spools a file called runsMissingSVX
	sqlplus cdf_reader/reader@cdfonprd @testcot.sql $run  #spools a file called runsMissingCOT

	#now if a calib is missing for this run then the spooled file will be of zero length(cos there's no entry in the db) 
	cat runsMissingPasstable.lst
	if(-z runsMissingPasstable.lst) then
	#it doesn't yet have an entry in pass10

	    echo "$run" >> runs_no_pass10.lst

	    #test the length and if its not what it should be  then put this run in a list and send to expert.
	    if(`grep -c . runsMissingCDF.lst` != 4) then #it is not complete, pass 10 should be at version 4
		echo "$run" >> runs_no_cdf_10.lst
	    endif
	    
	    if(-z runsMissingSVX.lst) then #it is zero length
		echo "$run" >> runs_no_svx.lst
	    endif
	    
	    if(-z runsMissingCOT.lst) then #it is zero length
		echo "$run" >> runs_no_cot.lst
	    endif
	endif	

	sqlplus cdf_reader/reader@cdfonprd @testpasstable.sql 11A $run  #spools a file called runsMissingPasstable
	sqlplus cdf_reader/reader@cdfonprd @testcdf.sql $run  #spools a file called runsMissingCDF
	sqlplus cdf_reader/reader@cdfonprd @testsvxbeam.sql $run  #spools a file called runsMissingSVXBEAM
	sqlplus cdf_reader/reader@cdfonprd @testcotbeam.sql $run  #spools a file called runsMissingCOTBEAM
	    
	#now if a calib is missing for this run then the spooled file will be of zero length(cos there's no entry in the db) 
	cat runsMissingPasstable.lst
	if(-z runsMissingPasstable.lst) then
	#it doesn't yet have an entry in pass11A
	
	    echo "$run" >> runs_no_pass11.lst

	    #test the length and if its not what it should be  then put this run in a list and send to expert.
	    if(`grep -c . runsMissingCDF.lst` != 5) then #it is not complete, pass 11A should be at version 5 
		echo "$run" >> runs_no_cdf_11.lst
	    endif
	    
	    if(-z runsMissingSVXBEAM.lst) then #it is zero length
		echo "$run" >> runs_no_svxbeam.lst
	    endif
    
	    if(-z runsMissingCOTBEAM.lst) then #it is zero length
		echo "$run" >> runs_no_cotbeam.lst
	    endif
	endif	
    endif # if($run >= $run_limit)
end


scp runs_no_cdf_10.lst cdfopr@fcdflnx3.fnal.gov:/cdf/home/www/usr/cdfopr/calibrations/opstatus
scp runs_no_svx.lst cdfopr@fcdflnx3.fnal.gov:/cdf/home/www/usr/cdfopr/calibrations/opstatus
scp runs_no_cot.lst cdfopr@fcdflnx3.fnal.gov:/cdf/home/www/usr/cdfopr/calibrations/opstatus
scp runs_no_cdf_11.lst cdfopr@fcdflnx3.fnal.gov:/cdf/home/www/usr/cdfopr/calibrations/opstatus
scp runs_no_svxbeam.lst cdfopr@fcdflnx3.fnal.gov:/cdf/home/www/usr/cdfopr/calibrations/opstatus
scp runs_no_cotbeam.lst cdfopr@fcdflnx3.fnal.gov:/cdf/home/www/usr/cdfopr/calibrations/opstatus
scp runs_no_pass10.lst cdfopr@fcdflnx3.fnal.gov:/cdf/home/www/usr/cdfopr/calibrations/opstatus
scp runs_no_pass11.lst cdfopr@fcdflnx3.fnal.gov:/cdf/home/www/usr/cdfopr/calibrations/opstatus


rm runs_no_cdf_10.lst >& /dev/null
rm runs_no_svx.lst >& /dev/null
rm runs_no_cot.lst >& /dev/null
rm runs_no_cdf_11.lst >& /dev/null
rm runs_no_svxbeam.lst >& /dev/null
rm runs_no_cotbeam.lst >& /dev/null
rm runs_no_pass10.lst >& /dev/null
rm runs_no_pass11.lst >& /dev/null
#end of the script