#! /bin/tcsh -f
	    
# no comments here please.  comment when updating cvs

#ps -fN -p $$ | grep $USER | grep $0 | grep -v grep && exit 5

source ~cdfsoft/cdf2.cshrc
setup cdfsoft2 6.1.1

set aaaaa_oracle_dir = `echo $ORACLE_CLIENT_DIR` 


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


cd /cdf/scratch/cdfopr/cdfcalib/cdfopr/cdfcalib/cron_scripts/fill_passtable

#jobsets to be merged
@ align_jobset = 1454241
@ mpa_jobset = 1387648


#look for any new runs
@ last_merged_date  = `grep . merged_run_and_date.txt | tail -1 | awk '{print int($1)}'` # Get the last date.
if ( $last_merged_date == 0 ) then # If run number is invalid.
    echo "file merged_run_and_date.txt:"; echo "============"; cat merged_run_and_date.txt; exit 2    # Print file, and exit.
endif                     # if ( $last_merged_date == 0 ) then

#find any new runs at v3 of prod_physics_cdf
$ORACLE_CLIENT_DIR/bin/sqlplus -s cdf_reader/reader@cdfonprd @svxalign.sql $last_merged_date || exit 3 

if (! -z merged_run_and_date.txt) then    #there are new runs

    #====================================
    #add in 5.1.x svxalign valid set
    #====================================

    #mark the start of the output so we can find it in the cron output file
    echo "=============================================new run===================================="
    set dat = `date`
    echo "Script ran at $dat"

    #get the list of runs from the sql ouput
    cut -d" " -f3- merged_run_and_date.txt >! runs.lst

    # Check that all runs in list are numeric.
    foreach r ( `grep . runs.lst` )        # Loop over runs.
	@ n = 0                            # Default.
	@ n = `echo $r | awk '{print int($1)}'` # Numeric run.
	if ( $n == 0 ) then                # If non-numeric run translated to zero.
	    echo "Error: Exit because of non-numeric run $r"
	    echo "Will not process any runs."
	    echo "file merged_run_and_date.txt:"; echo "============"; cat merged_run_and_date.txt # Print file.
	    echo $last_merged_date >! merged_run_and_date.txt  # Put the last processed run in there.
	    exit 4                         # Exit.
	endif                              # if ( $n == 0 )
    end                                    # foreach r ( `grep . runs.lst` )

    #loop over runs
    foreach r (`grep . runs.lst`)
	echo "in loop"
	echo "run = $r"
	#now make version4 of PROD_PHYSICS_CDF merging in the new svxalign valid set for 5.1.x
	echo "DBSetMerge TESTMODE OTL /@cdfonprd used $r PROD_PHYSICS_CDF PROD_PHYSICS_CDF  $align_jobset $mpa_jobset"
	DBSetMerge TESTMODE OTL /@cdfonprd used $r PROD_PHYSICS_CDF PROD_PHYSICS_CDF $align_jobset $mpa_jobset  >& db_testmode_out
	cat db_testmode_out
        rm db_testmode_out
	DBSetMerge OTL /@cdfonprd used $r PROD_PHYSICS_CDF PROD_PHYSICS_CDF $align_jobset $mpa_jobset  >& db_out
	cat db_out
	rm db_out

    end
    echo "finished making new versions"
    #===============================================================================================


    #------------------------------------------------------------------------------------------------
    #Automated filling of passcalibs table
    #
    #take this list of runs and fill v4 into passtable
    #-----------------------------------------------------------------------------------------------
    
    
    rm passcalibs.dat >& /dev/null
    rm passcalibs_10.dat >& /dev/null

    echo "removed versions file and passcalib files"
    #==============================Get pass index for each passtable=====================


    #pass10
    #======
    @ highest_pass_index_of_pass_10A = `$CDFSOFT2_DIR/CalibDB/bin/PassTool.pl -c /@cdfonprd -n 10A -l | grep -w "10A" | grep "PROD_PHYSICS_CDF" | grep -v "YES" | grep "A" | awk '{print $1}' `

    echo "got highest pass index for pass 10A: $highest_pass_index_of_pass_10A"

    #now begin making pass10 input file
    
    #echo "P_INDEX LORUN   HIRUN   PROC_CALIB_VERSION" > passcalibs_10.dat
    #echo "--------------------------------------------------------------------------" >> passcalibs_10.dat


    echo " made passcalib file headers"
    
    #loop over the runs in the file and make sure they are now at v4
    foreach r (`grep . runs.lst`)

	rm verOfThisRun.txt >& /dev/null  #clear file


        #query the database and pipe the output to a file
        $ORACLE_CLIENT_DIR/bin/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:]"`

        #is it at version 4?
        if ( $version_of_this_run == 4 ) then #fill the passcalibs.dat file 
               if ($r > 168890) then
		#and add the run number and version number to the passcalibs.dat file
		echo "$highest_pass_index_of_pass_10A        $r          $r          $version_of_this_run" >> passcalibs_10.dat
	    endif  #end if run>shutdown run
	endif  #end if ver = 4
    end     #end loop over runs in these_runs.out file

    echo "filled passcalibs file"


    #==================================================================================================
    #
    #Before filling the passtable check the cids of the versions we've just made to see if everything is ok.
    #
    #if yes then fill pass table; if no then email Dustin and Rob to say something has changed.
    #
    #
    #==================================================================================================

    #use robs script which loops over a list if runs and returns the cid's that are different for each run.

    #seems to need this:
    #setup cdfsoft2 development

    #the calib tables we shall compare to are:
    #for the 5.3.1 calibs(ver4 of PROD_PHYSICS_CDF) compare to those in version 4 of PROD_PHYSCIS_CDF

    #version 4 of run  219487
    @ good_jobset_5_1  = 1549365   
    echo "reference jobset for ver4 5.1.x  = $good_jobset_5_1"

    #first clean all the temp files we will use:
 #   if(-f cid_changes.out) rm cid_changes.out 
 #   if(-f table_changes) rm table_changes
 #   if(-f changes.out) rm changes.out

    setenv ORACLE_CLIENT_DIR $aaaaa_oracle_dir

    #run robs script to get the cid differences, first for ver3 and then ver4
    ./compare_tableids_jobset_runlist.tcsh $good_jobset_5_1 runs.lst PROD_PHYSICS_CDF 4 >& cid_changes_ver4.out
    echo "running compare tableids script" 
    cat cid_changes_ver4.out

    #==========================check cids of ver4=======================
    #now look through the output file for changes; but first remove the lines refering to tables that we expect to change.
    grep . cid_changes_ver4.out | grep -v COTCHANT0 | grep -v COTDEADCH | grep -v COTSTAGE0 | grep -v SICHIPPED | grep -v SICHIPTHRESH | grep -v SIDPSPARMS | grep -v SISTRIPDH | grep -v SISTRIPMASK | grep -v SICHIPON | grep -v COTPROBCH | grep -v SVXBEAMPOSITION | grep -v COTBEAMPOSITION | grep -v dcap >& table_changes_ver4

    #now remove the header infomation and look for any changes
    grep . table_changes_ver4 | grep -v "comm -3" | grep -v Number | grep -v compare_tableids_jobset_runlist.tcsh | grep -v dcap >& changes_ver4.out
 
    #now if table changes then this file will not be of zero length; so email it to us and NOT exit the script.
    if (! -z changes_ver4.out) then
	mail -s "In fill_passtables_10A.tcsh: Changes in Calibration tables for ver4" -c tvine@fnal.gov smalik.hep.ucl.ac.uk  < table_changes_ver4; # exit 21
    endif

    #in addition check that the svx/cot tables in PROD_PHYSICS_CDF version 4 agree with the PROD_PHYSICS_SVX/COT jobset for that run
    #to avoid errors caused by a run reaching a high enough version to progress to this fill_passtable script without having 
    #had svx merged and/or cot merged.
    foreach run(`grep . runs.lst`)

	rm svx_cid_changes.out >& /dev/null
	rm cot_cid_changes.out >& /dev/null
	#first find PROD_PHYSICS_SVX jobset for that run
	
	@ svx_jobset = 0
	@ svx_jobset = `$ORACLE_CLIENT_DIR/bin/sqlplus -s cdf_reader/reader@cdfonprd @get_max_jobset.sql $run PROD_PHYSICS_SVX`

	if($svx_jobset) then

	    ./compare_tableids_jobset_onerun.tcsh $svx_jobset $run PROD_PHYSICS_CDF 4 >& svx_cid_changes.out

	    #counting the number of SI tables in the compare_tableids_jobset_onerun.tcsh output. If there are tables present it
	    #means differences have been found - script exits
	    if ( `grep -c SICHIPON svx_cid_changes.out` ||  `grep -c SICHIPPED svx_cid_changes.out` || `grep -c SIFIBOFFSETS svx_cid_changes.out` || `grep -c SISTRIPDH svx_cid_changes.out` || `grep -c SISTRIPMASK svx_cid_changes.out` ) then
		echo "Problem with SVX tables for ver4 - script exiting"
		cat svx_cid_changes.out;
		mail -s "In fill_passtables_10A.tcsh: Problem with SVX tables for ver4" -c tvine@fnal.gov smalik.hep.ucl.ac.uk   < svx_cid_changes.out; exit 22
	    endif
	    
	else #no PROD_PHYSICS_SVX used_set defined
	echo "SEVERE ERROR - fill_passtable script ran before PROD_PHYSICS_SVX used_set had been defined" 
	    mail -s "SEVERE ERROR $run - fill_passtable_10A.tcsh ran without SVX used_set"   smalik.hep.ucl.ac.uk tvine@fnal.gov
	    exit 7 #exit
	endif

	#check that $run has a properly defined (i.e. not BAD) COT table otherwise the check below will not work.
	@ cot_defined = `$ORACLE_CLIENT_DIR/bin/sqlplus -s cdf_reader/reader@cdfonprd @cot_defined.sql $run PROD_PHYSICS_COT`
	#if the COT table is defined $cot_defined will equal the run number, otherwise will be zero

	#only case where a run should have no COT is if it was UNDEFINED
	@ cot_not_bad = `$ORACLE_CLIENT_DIR/bin/sqlplus -s cdf_reader/reader@cdfonprd @undef_mod.sql $run`

	if(!($cot_defined) && ($cot_not_bad)) then
	    echo "SEVERE ERROR - fill_passtable script ran before PROD_PHYSICS_COT used_set had been defined" 
	    mail -s "SEVERE ERROR $run - fill_passtable_10A.tcsh ran without COT used_set"   smalik.hep.ucl.ac.uk tvine@fnal.gov
	    exit 7 #exit
	endif

	#occasionally more than one cot jobset is created by stage_0 - causes problems with the compare jobset test when we try and compare
	#PROD_PHYSICS_CDF with the max COT jobset which wasn't actually that merged in. Quick fix - if the number of COT jobsets isn't equal
	#to zero don't bother with the test.
	@ num_cot_jobsets = `$ORACLE_CLIENT_DIR/bin/sqlplus -s cdf_reader/reader@cdfonprd @get_all_jobsets.sql $run PROD_PHYSICS_COT | grep -c .`
	@ testable = 1;

	if(($cot_defined) && (($num_cot_jobsets)==($testable))) then
	    echo "COT table is well defined - checking $run PROD_PHYSICS_CDF COT tables against COT used_set."
	    #counting the number of COT tables in the compare_tableids_jobset_onerun.tcsh output. If there are tables present it
	    #means differences have been found - script exits

	    @ cot_jobset = `$ORACLE_CLIENT_DIR/bin/sqlplus -s cdf_reader/reader@cdfonprd @get_max_jobset.sql $run PROD_PHYSICS_COT`
	    ./compare_tableids_jobset_onerun.tcsh $cot_jobset $run PROD_PHYSICS_CDF 4 >& cot_cid_changes.out

	    if ( `grep -c COTCHANT0 cot_cid_changes.out` || `grep -c COTCTI cot_cid_changes.out` ||  `grep -c COTPROBCH cot_cid_changes.out` || `grep -c COTSTAGE0 cot_cid_changes.out`) then 
		echo "Problem with COT tables for ver4 - script exiting"
		cat cot_cid_changes.out;
		mail -s "In fill_passtable_10A.tcsh: Problem with COT tables for ver4" -c  smalik.hep.ucl.ac.uk tvine@fnal.gov < cot_cid_changes.out; exit 23
	    endif
	endif

    end   #foreach run(`grep . runs.lst`)
	
    #otherwise remove the temp files and then go on and fill the passtable
    echo "no cid changes found, so carry on"    
    rm cid_changes_ver4.out
    rm table_changes_ver4
    rm changes_ver4.out
    
    #now fill pass10 
    cp passcalibs_10.dat passcalibs.dat
    #and run the pasascalib tool
    $CDFSOFT2_DIR/CalibDB/bin/PassCalibTool.pl  -c /@cdfonprd -s -r passcalibs.dat #to screen for cronjob
    echo "made insert statements; now filling table"
    $CDFSOFT2_DIR/CalibDB/bin/PassCalibTool.pl  -c /@cdfonprd   -r passcalibs.dat #to screen for cronjob
    echo "filled passcalib table"

    #if successful then put this passcalibs.dat into a log file for safe keeping and errorchecking.
    cat passcalibs_10.dat                                                     #for cronjob just cat file

    #remove the tempoary files to save space
    rm passcalibs_10.dat >& /dev/null
    rm passcalibs.dat >& /dev/null
    rm verOfThisRun.txt >& /dev/null

    #and mark the end of the run in the cron output file
    echo "==============================end run===================================="

else                            # No new runs detected.
    echo $last_merged_date >> merged_run_and_date.txt  # Put the last processed run in there.

endif  #end if new runs