#! /bin/tcsh -f

#======================================================================================================================================================================

# Name: add_beamlines_make_pass_17A.tcsh

# Created: 08/12/04 Ben Cooper (from add_beamlines_make_pass_11A.tcsh)

# Purpose: Merge PAD_PHYSICS_SVXBEAM and PAD_PHYSICS_COTBEAM pass 10 into PROD_PHYSICS_CDF pass 10A as they become available and fill pass 17A with these new versions. 
#          After this is done PROD_PHYSICS_CDF should be at version 5 (unless there have been problems upstream). This merging is only done if both PAD_PHYSICS_SVXBEAM 
#          and PAD_PHYSICS_COTBEAM are available,and then both are merged simultaneously. Runs as a cronjob on b0dap30.fnal.gov.

# Arguements : None

# Options : None

#======================================================================================================================================================================

ps -fN -p $$ | grep $USER | grep $0 | grep -v grep && exit 5
source ~cdfsoft/cdf2.cshrc
setup cdfsoft2 5.3.1
setup cdfdb
dbPing -dcdfonprd -s > /dev/null || exit 6
cd /data1/cdfcalib/cron_scripts/add_beamlines

#remove temporary files 
rm runs_to_merge.lst >& /dev/null
rm matched_runs.lst >& /dev/null
rm last_svx_date.txt >& /dev/null
rm last_cot_date.txt >& /dev/null
rm mail_report1.txt >& /dev/null
rm mail_report2.txt >& /dev/null


@ one = 1 #needed below because tcsh (or me) is shit

#Both cot_date_not_processed.lst and svx_date_not_processed.lst must exist with at least zero size for script to run

#if svx_date_not_processed.lst is of zero size it means that on last running there were no runs which had svx defined but not cot. Therefore take the svx_date from the list of runs that were last merged merged_runs.lst 
if (-z svx_date_not_processed.lst) then
    @ last_run = `grep . merged_runs.lst | tail -1`
    sqlplus -s cdf_reader/reader@cdfonprd @last_svx_processed.sql $last_run > last_svx_date.txt
    @ last_svx_date  = `grep . last_svx_date.txt | tail -1`
else #on last running of job there were runs with svx defined but not cot
    @ last_svx_date  = `grep . svx_date_not_processed.lst | head -1` # Get earliest date for a run that has SVX defined but has not been merged (because no COT)
    @ last_svx_date = $last_svx_date - $one; #necessary to ensure that the earliest run which wasn't processed gets processed (see svx_newruns.sql)
    echo "WARNING On last running of add_beamlines_make_pass_11.tcsh found run(s) which did not have both COT and SVX beamlines" > mail_report1.txt
    @ last_run = `grep . merged_runs.lst | tail -1`
    echo "Last run that was processed: $last_run" >> mail_report1.txt
    echo "Date of last run that was processed:" >> mail_report1.txt
    sqlplus -s cdf_reader/reader@cdfonprd @last_svx_processed.sql $last_run >> mail_report1.txt
    echo "last_svx_date for this job: $last_svx_date. Check that these dates make sense" >> mail_report1.txt
    mail -s "WARNING Runs without both COT and SVX beamlines" bdc@hep.ucl.ac.uk < mail_report1.txt ; 
endif    #if (-z svx_date_not_processed.lst)

if  ( $last_svx_date == 0 ) then # If cannot find a last_svx_date;.
    echo "In /cron_scripts/add_beamlines/add_beamlines_make_pass_11.tcsh: Cannot find a last_svx_date. svx_date_not_processed.lst ===========" > mail_svx.txt;  
    echo "In /cron_scripts/add_beamlines/add_beamlines_make_pass_11.tcsh: SCRIPT ABORTED" >> mail_svx.txt;  
    cat svx_date_not_processed.lst >> mail_svx.txt  
    mail -s "WARNING Cannot find a last_svx_date" -c bdc@hep.ucl.ac.uk < mail_svx.txt ; 
    exit 19; #we always want a last_svx_date - script should exit if one cannot be defined
endif

#if cot_date_not_processed.lst is of zero size it means that on last running there were no runs which had cot defined but not svx. Therefore take the cot date from the last run that was merged: date_last_cot_merged_run.txt
if (-z cot_date_not_processed.lst) then
    #NOTE - cannot use the last run in merged_runs.lst to find this last cot date as merged_runs.lst is ordered by svx_date - date_last_cot_merged_run.txt is filled at the end of the job if any runs have been merged.
    @ last_cot_date = `grep . date_last_cot_merged_run.txt`
else #on last running of job there were runs with cot defined but not svx
    @ last_cot_date  = `grep . cot_date_not_processed.lst | head -1` # Get earliest date for a run that has COT defined but has not been merged
    @ last_cot_date  = $last_cot_date - $one; #necessary to ensure that the earliest run which wasn't processed gets processed (see cot_newruns.sql)
    echo "WARNING On last running of add_beamlines_make_pass_11.tcsh found run(s) which did not have both COT and SVX beamlines" > mail_report2.txt
    @ last_run = `grep . merged_runs.lst | tail -1`
    echo "Last run that was processed: $last_run" >> mail_report2.txt
    echo "Date of last run that was processed:" >> mail_report2.txt
    sqlplus -s cdf_reader/reader@cdfonprd @last_cot_processed.sql $last_run >> mail_report2.txt
    echo "last_svx_date for this job: $last_cot_date. Check that these dates make sense" >> mail_report2.txt
    mail -s "WARNING Runs without both COT and SVX beamlines" bdc@hep.ucl.ac.uk  < mail_report2.txt ; 
endif    #if (-z cot_date_not_processed.lst)

if  ( $last_cot_date == 0 ) then # If cannot find a last_cot_date.
    echo "In /cron_scripts/add_baemlines/add_beamlines_make_pass_17A.tcsh: Cannot find a last_cot_date. cot_date_not_processed.lst ===========" > mail_cot.txt;
    echo "In /cron_scripts/add_beamlines/add_beamlines_make_pass_17A.tcsh: SCRIPT ABORTED" >> mail_cot.txt;  
    cat cot_date_not_processed.lst >> mail_cot.txt  
    mail -s "WARNING Cannot find a last_cot_date" -c bdc@hep.ucl.ac.uk < mail_cot.txt ; 
    exit 20; #we always want a last_cot_date - script should exit if one cannot be defined
endif

#needed for an SQL query later on.
if($last_cot_date >= $last_svx_date) then
    @ earliest_date = $last_svx_date
else
    @ earliest_date = $last_cot_date
endif

rm cot_run_date.lst >& /dev/null
rm svx_run_date.lst >& /dev/null
touch cot_run_date.lst
touch svx_run_date.lst

#Find runs with new PAD_PHYSICS_COTBEAM pass 10 in passcalibs table where passcalibs entry created after last_cot_date
#ues passcalibs table and not used_set because we want to merge in specifically pass 10 PAD_PHYSICS_COTBEAM used_set.
sqlplus -s cdf_reader/reader@cdfonprd @cot_newruns.sql $last_cot_date >> cot_run_date.lst

#Find runs with new PAD_PHYSICS_SVXBEAM pass 10 in passcalibs table where passcalibs entry created after last_svx_date
sqlplus -s cdf_reader/reader@cdfonprd @svx_newruns.sql $last_svx_date >> svx_run_date.lst


if  ( (! -z svx_run_date.lst) || (! -z cot_run_date.lst)) then #if there are new svx or cot runs   

    rm svx_date_not_processed.lst >& /dev/null
    rm cot_date_not_processed.lst >& /dev/null
    rm  mail_report3.txt >& /dev/null

    echo "=============================================new run===================================="
   
    set dat = `date`
    echo "Script ran at $dat"

    echo "Found last_cot_date = $last_cot_date, Found last_svx_date = $last_svx_date"
    echo "svx_run_date.lst ============ "; cat svx_run_date.lst
    echo "cot_run_date.lst ============ "; cat cot_run_date.lst

    echo "Running add_beamlines_make_pass_17A.tcsh over the following svx runs:" > mail_report3.txt
    cat svx_run_date.lst >> mail_report3.txt
    echo "Running add_beamlines_make_pass_17A.tcsh over the following cot runs:" >> mail_report3.txt
    cat cot_run_date.lst >> mail_report3.txt
    mail -s "Running add_beamlines_make_pass_17A.tcsh" -c bdc@hep.ucl.ac.uk < mail_report3.txt; 

    touch matched_runs.lst
    touch runs_to_merge.lst

    echo "Looking for matched runs between svx/cot_run_date.lst"
    #we will only do the merging into PROD_PHYSICS_CDF if both SVXBEAM and COTBEAM are available for a particular run
    foreach svx_run(`grep . svx_run_date.lst | awk '{print int($1)}'`)
	foreach cot_run(`grep . cot_run_date.lst | awk '{print int($1)}'`)
    
	    if ($svx_run == $cot_run) then
		echo "$svx_run" >> matched_runs.lst #put runs with both SVXBEAM and COTBEAM defined into a list
		rm  already_processed.lst >& /dev/null
		touch already_processed.lst
		#we do an additional check via sql query to ensure that the matched runs are not already in pass 17A.
		#if they are in pass 17A they will not be merged
		sqlplus -s cdf_reader/reader@cdfonprd @already_processed_17A.sql $svx_run >> already_processed.lst
		    #if already_processed.lst is of zero size then the test is passed - merging can go ahead for this run. 
		    if (-z already_processed.lst) then 
			echo "$svx_run" >> runs_to_merge.lst #put runs to be merged into a list
		    endif  #if (-z already_processed.lst) 
	    endif  #if ($svx_run == $cot_run)

	end  #foreach svx_run(`grep . svx_run_date.lst | awk '{print int($1)}'`)
    end  #foreach cot_run(`grep . cot_run_date.lst | awk '{print int($1)}'`)

    touch svx_date_not_processed.lst
    touch cot_date_not_processed.lst

    if(-z matched_runs.lst) then 
	echo "matched_runs.lst has zero size - no runs found with both COT and SVX tables defined"
    endif

    #this loop identifies runs which had a new PAD_PHYSICS_SVXBEAM defined but which did not have a corresponding PAD_PHYSICS_COTBEAM. 
    #these runs are put into svx_date_not_processed.lst - they will be run over again next time the script executes
    foreach svx_run_again(`grep . svx_run_date.lst | awk '{print int($1)}'`)
	@ n = 0 #counter
	    if(! -z matched_runs.lst) then
		#testing if  svx_run_again did not match with cot
		foreach matched_run(`grep . matched_runs.lst`)
		    if ($svx_run_again == $matched_run) then
			@ n = $n + 1;
		    endif   # if ($svx_run_again != $matched_run)
		end      #foreach matched_run(`grep . matched_runs.lst`) 
		if($n == 0) then #svx_run_again did not match with any cot i.e. only had a new PAD_PHYSICS_SVXBEAM defined
		    sqlplus -s cdf_reader/reader@cdfonprd @get_svx_date_from_run.sql $svx_run_again >> svx_date_not_processed.lst
		endif    #if($n == 0)
	    else
		#if there are no matched runs then all svx runs will not be processed and thus are fed into svx_date_not_processed.lst
		sqlplus -s cdf_reader/reader@cdfonprd @get_svx_date_from_run.sql $svx_run_again >> svx_date_not_processed.lst
	    endif   #if(! -z matched_runs.lst) then
    end      #foreach svx_run_again(`grep . svx_run_date.lst | awk '{print int($1)}'`)

    #exactly the same as the loop above but identifying runs which had a new PAD_PHYSICS_COTBEAM defined but which did not have a 
    #corresponding PAD_PHYSICS_SVXBEAM. 
    foreach cot_run_again(`grep . cot_run_date.lst | awk '{print int($1)}'`)
	@ n = 0
	    if(! -z matched_runs.lst) then
		foreach matched_run(`grep . matched_runs.lst`)
		    if ($cot_run_again == $matched_run) then
			@ n = $n + 1;
		    endif   # if ($svx_run_again != $matched_run)
		end      #foreach matched_run(`grep . matched_runs.lst`) 
	    if($n == 0) then
		sqlplus -s cdf_reader/reader@cdfonprd @get_cot_date_from_run.sql $cot_run_again >> cot_date_not_processed.lst
	    endif    #if($n == 0)
	    else
		#if there are no matched runs then all cot runs will not be processed and thus are fed into cot_date_not_processed.lst
		sqlplus -s cdf_reader/reader@cdfonprd @get_cot_date_from_run.sql $cot_run_again >> cot_date_not_processed.lst
	    endif   #if(! -z matched_runs.lst) then
    end      #foreach svx_run_again(`grep . svx_run_date.lst | awk '{print int($1)}'`)

    echo "Found runs to be merged. runs_to_merge.lst ==================="; cat runs_to_merge.lst;
    echo "   "
    echo "Dates of Runs which did not match and will not be merged:"
    echo "cot_date_not_processed.lst ================ "; cat cot_date_not_processed.lst;
    echo "   "
    echo "svx_date_not_processed.lst ================ "; cat svx_date_not_processed.lst;


    #merge PAD_PHYSICS_COTBEAM and PAD_PHYSICS_SVXBEAM into PROD_PHYSICS_CDF for those runs which had both of these beamlines used_sets defined.
    #if this was not the case for any runs runs_to_merge.lst will be of zero size.
    if (! -z runs_to_merge.lst) then 
    
	rm failed_test.txt >& /dev/null  
	rm merged_runs.lst >& /dev/null
	touch merged_runs.lst
	touch failed_test.txt

	foreach run(`grep . runs_to_merge.lst`)
	    rm test.lst >& /dev/null
	    rm prod_10_jobset >& /dev/null
	    rm cot_10_jobset >& /dev/null
	    rm svx_10_jobset >& /dev/null
	    #this query is an 'intersect' query - double checks that the run has both COTBEAM and SVXBEAM defined - if not then test.lst 
	    #will be of zero size.
	    sqlplus -s cdf_reader/reader@cdfonprd @merge_test.sql $run $earliest_date > test.lst
		if (! -z test.lst) then
		    #finding the appropriate jobsets to be merged, using PROD_PHYSICS_CDF pass 10 as the base.
		    sqlplus -s cdf_reader/reader@cdfonprd @get_jobsets.sql $run PROD_PHYSICS_CDF 10A > prod_10_jobset.lst
		    sqlplus -s cdf_reader/reader@cdfonprd @get_jobsets.sql $run PAD_PHYSICS_COTBEAM 10 > cot_10_jobset.lst
		    sqlplus -s cdf_reader/reader@cdfonprd @get_jobsets.sql $run PAD_PHYSICS_SVXBEAM 10 > svx_10_jobset.lst

		    @ prod_10_jobset = `grep . prod_10_jobset.lst`
		    @ cot_10_jobset = `grep . cot_10_jobset.lst`
		    @ svx_10_jobset = `grep . svx_10_jobset.lst`

		    #09/12/04 temporary measure - merging in latest COTHV table (should be in PROD_PHYSICS_COT but currently missing)
		    @ cothv_jobset = 1355501;

		    #performing the merger using Rob's DBSetMerge tool
		    echo "Merging runs after passing intersect merge_test.sql"
		    echo "DBSetMerge TESTMODE OTL /@cdfonprd used $run PROD_PHYSICS_CDF $prod_10_jobset $svx_10_jobset $cot_10_jobset $cothv_jobset" 
		    ./DBSetMerge TESTMODE OTL /@cdfonprd used $run PROD_PHYSICS_CDF $prod_10_jobset $svx_10_jobset $cot_10_jobset $cothv_jobset  
		    echo "DBSetMerge OTL /@cdfonprd used $run PROD_PHYSICS_CDF  $prod_10_jobset $svx_10_jobset $cot_10_jobset $cothv_jobset " 
		    ./DBSetMerge OTL /@cdfonprd used $run PROD_PHYSICS_CDF  $prod_10_jobset $svx_10_jobset $cot_10_jobset $cothv_jobset
		    #form a list of runs that have been merged - this is crucial for when the script runs next time, to avoid trying to merge same 
		    #same runs (there are a number of safety checks which will prevent this from happening)
		    echo "$run" >> merged_runs.lst

		else #if run does not have both PAD_PHYSICS_COTBEAM and PAD_PHYSICS_SVXBEAM it will fail intersect test above and will not be merged
		    echo "In /cron_scripts/add_beamlines/add_beamlines_make_pass_17A.tcsh Run: $run failed sql intersect test, not processed" >> failed_test.txt ;    
		    mail -s "WARNING in adding beamlines to pass 17A" -c bdc@hep.ucl.ac.uk < failed_test.txt ; 	
		endif        #if (! -z test.lst)
	end          #foreach run(`grep . runs_to_merge.lst`)


	echo "Runs which have been merged. merged_runs.lst ================== " ; cat merged_runs.lst;

	#now work out what the passidex is: highest pass index for passname 17A and processName PROD_PHYSICS_CDF (ie the one that isn't retired)
	@ highest_pass_index =  `$CDFSOFT2_DIR/CalibDB/bin/PassTool.pl -c /@cdfonprd -n 17A -l | grep "17A" | grep "PROD_PHYSICS_CDF" | grep -v "YES" | grep "A" | awk '{print $1}' `
	
	rm passcalibs.dat >& /dev/null #crucial otherwise may try and fill passcalibs table with runs that went in last time script was run
	
	#make headers for passcalibs.dat file
	echo "P_INDEX LORUN   HIRUN   PROC_CALIB_VERSION" > passcalibs.dat
	echo "--------------------------------------------------------------------------" >> passcalibs.dat

 
	foreach r (`grep . merged_runs.lst`) #fill passcalibs.dat for those runs which have just been merged
	    
	    rm verOfThisRun.txt >& /dev/null  #clear file
	    
	    #query the database - finds the highest version number for this run (i.e. the used set just made)
	    sqlplus -s cdf_reader/reader@cdfonprd @version_from_run_and_name.sql $r PROD_PHYSICS_CDF > verOfThisRun.txt  
	    
	    #assign the parsed file to a variable
	    @ version_of_this_run  = `tail -1 verOfThisRun.txt | tr -d "[:blank:]"`

	    echo "$highest_pass_index        $r          $r          $version_of_this_run" >> passcalibs.dat
	end        #foreach r (`grep . merged_runs.lst`)

	# now fill the passtable 
	echo "Now filling the passtable"
	echo " $CDFSOFT2_DIR/CalibDB/bin/PassCalibTool.pl -c /@cdfonprd -r  passcalibs.dat"
	$CDFSOFT2_DIR/CalibDB/bin/PassCalibTool.pl -c /@cdfonprd -r  passcalibs.dat
    
	#if successful then print out passcalibs.dat
	echo "passcalibs.dat: ============== "
	cat passcalibs.dat

    endif        #if (! -z runs_to_merge.lst)

    #merged_runs.lst is ordered by svx date, cot runs not necessarily created in the same order - need to find last (date wise) cot run that was merged such that on the next running the script uses the correct cot_date. 
    if (! -z merged_runs.lst) then 
	rm date_last_cot_merged_run.txt >& /dev/null
	@ date_last_cot_merged_run = 0;
	foreach merged_run(`grep . merged_runs.lst`)
	
	    rm merged_cot_date.txt >& /dev/null
	    sqlplus -s cdf_reader/reader@cdfonprd @last_cot_processed.sql $merged_run > merged_cot_date.txt
	    @ merged_cot_date =  `grep . merged_cot_date.txt | tail -1`
	    if ($merged_cot_date > $date_last_cot_merged_run) then
	    @ date_last_cot_merged_run = $merged_cot_date
	    endif     # if (merged_cot_date > date_last_cot_merged_run)
   
	end       #foreach merged_run(`grep . merged_runs.lst`)

    echo "$date_last_cot_merged_run" > date_last_cot_merged_run.txt

    endif    #if(! -z merged_runs.lst)
	
    echo "==============================end run===================================="

endif        #if (! ((-z svx_run_date.lst) || (-z cot_run_date.lst)))



#end of script



 










