#! /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
#=========================================================================

#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 development
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

#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.sql 45

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

#first touch some files:
touch runs_no_cdf.lst
touch runs_no_svx.lst
touch runs_no_cot.lst

#now loop
foreach run (`grep . farmsRuns.lst`)
    echo "$run"
    sqlplus cdf_reader/reader@cdfonprd @testpasstable.sql 10 $run  #spools a file called runsMissingPasstable10
    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 runsMissingPasstable10.lst
    if(-z runsMissingPasstable10.lst) then
    #it doesn't yet have an entry in pass10

	#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,  
	    echo "$run" >> runs_no_cdf.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	

end

#now if the run list has size (ie there are runs that have no db entry) then send it to the expert
if(! -z runs_no_cdf.lst ) then
    cat mail_header runs_no_cdf.lst > mail_message_cdf
    mail -s "runs with incomplete PROD_PHYSICS_CDF" djm@fnal.gov bdc@hep.ucl.ac.uk ershov@fnal.gov <mail_message_cdf
endif
if(! -z runs_no_cot.lst ) then
    cat mail_header runs_no_cot.lst > mail_message_cot
    mail -s "runs with no version of PROD_PHYSICS_COT" djm@fnal.gov bdc@hep.ucl.ac.uk <mail_message_cot
endif
if(! -z runs_no_svx.lst ) then
    cat mail_header runs_no_svx.lst > mail_message_svx
    mail -s "runs with no version of PROD_PHYSICS_SVX" djm@fnal.gov bdc@hep.ucl.ac.uk <mail_message_svx
endif

rm runs_no_cdf.lst >& /dev/null
rm runs_no_svx.lst >& /dev/null
rm runs_no_cot.lst >& /dev/null

#end of the script