#!/bin/bash

# new version of the script to run on newdaq

# option whether to fill all row or only those where information is missing
# $doupdate might be given as environment variable
if [ "$doupdate" = "" ]
then
#   doupdate="yes" # update all entries (needed when new fields have been added)
#   doupdate="force" # needed when something with insert in La Palma did not work (adds more information)
   doupdate="no" # fill only entries which are not yet existing (default)
fi

source `dirname $0`/../Sourcefile.sh
printprocesslog "INFO starting $0 with option doupdate="$doupdate

logfile=$runlogpath"/FillAuxData-"$datetime".log"
date >> $logfile

# check if software is available
if ! ls $factpath/fitsdump >/dev/null 2>&1
then 
   printprocesslog "ERROR "$factpath"/fitsdump is not available."
   finish
fi

# get dates
if [ "$certaindate" != "" ]
then
   checkstring=`echo $certaindate | grep -E -o '^20[0-9][0-9]\/[01][0-9]\/[0-3][0-9]$'`
   if [ "$checkstring" = "" ]
   then
      echo "Please give the variable certaindate in the correct format (YYYY/MM/DD)"
      finish
   fi
   getdates $certaindate
else
   # get all night
   #getdates "all"
   # get last 3 nights if hour between 7 and 19h, else only current night
   getdates 3 7 19
fi


printprocesslog "INFO processing the following night(s): "${dates[@]}
echo  `date`": processing the following night(s): "${dates[@]} >> $logfile 2>&1

# mjd of 1970-01-01
#   needed in this script as for 1 day the mjd in the aux files are inconsistent
# mjdref=40587

# known: 
# 2011/11/22 MJDREF in DRIVE empty, Time > 55000
# 2011/11/23 MJDREF in DRIVE not empty, Time > 55000
# 2011/11/24 MJDREF in DRIVE not empty, Time > 15000
# raw files
# 2011/11/21 no MJDREF
# 2011/11/22 MJDREF
# further things: https://www.fact-project.org/logbook/showthread.php?tid=67

# trigger rate has as first value -1, but with using the median it should be fine


function evaluatestatistics()
{
   # $1 variable name
   # $@ statistics
   if [ "$2" = "" ]
   then
      printprocesslog "WARN couldn't get statistics from file $1 for run "$date" "$file
      continue
   fi
   min=
   mean=
   med=
   max=
   rms=
   evaluation=`echo $@ | grep -E -o '\['${1}':0[:]?[0-9]*\]\ Min:\ [-]?[0-9]+[.]?[0-9]*\ Max:\ [-]?[0-9]+[.]?[0-9]*\ Med:\ [-]?[0-9]+[.]?[0-9]*\ Avg:\ [-]?[0-9]+[.]?[0-9]*\ Rms:\ [-]?[0-9]+[.]?[0-9]*[e]?[-]?[0-9]*'`
   if [ "$evaluation" = "" ]
   then
      printprocesslog "WARN empty evaluation of statistic ("$@") for run "$date" "$file
      #echo "   ---> "$@
   fi
   #echo "ev: "$evaluation
   min=`echo $evaluation | grep -E -o 'Min:\ [-]?[0-9]+[.]?[0-9]*' | sed -e 's/Min:\ //'`
   max=`echo $evaluation | grep -E -o 'Max:\ [-]?[0-9]+[.]?[0-9]*' | sed -e 's/Max:\ //'`
   med=`echo $evaluation | grep -E -o 'Med:\ [-]?[0-9]+[.]?[0-9]*' | sed -e 's/Med:\ //'`
   mean=`echo $evaluation | grep -E -o 'Avg:\ [-]?[0-9]+[.]?[0-9]*' | sed -e 's/Avg:\ //'`
   rms=`echo $evaluation | grep -E -o 'Rms:\ [-]?[0-9]+[.]?[0-9]*[e]?[-]?[0-9]+' | sed -e 's/Rms:\ //'`
   #echo "eval: "$min" "$max" "$med" "$mean" "$rms
}

# do filling of aux data 
for date in ${dates[@]}
do 
   auxdir=$auxdata/$date
   rawdir=$rawdata/$date
   runnumber=`echo $date | sed -e 's/\///g'`

   # check if aux files are available from that night
   if ! [ -d $auxdir ]
   then
      printprocesslog "INFO no data available in "$auxdir
      continue
   else
      printprocesslog "INFO processing files in "$auxdir
      #echo "INFO processing files in "$auxdir
   fi

   # check if raw files are available from that night
   #   only needed to get start/stop time
   #   might be removed once the start/stop time comes from the DB
   if ! [ -d $rawdir ]
   then
      printprocesslog "INFO no data available in "$rawdir" -> continue"
      continue
   fi

   # get file numbers from DB
   #   but only for not-corrupted files
   # as aux files are written only once a minute, select only files which are older than 1.5 minutes
   query="SELECT fRunID from RunInfo WHERE fNight="$runnumber" AND fFitsFileErrors=0 AND NOT ISNULL(fRunStop) AND fRunStop < SUBTIME(UTC_TIMESTAMP(), \"00:01:30\")"
   # only runs which are not yet filled 
   if [ "$doupdate" = "no" ]
   then
      query=$query" AND ISNULL(fRightAscension) "
   fi
   printprocesslog "DEBUG get filenumbers from DB: QUERY: "$query
   filenumbers=( `sendquery $query` )
   if [ ${#filenumbers} -eq 0 ]
   then
      printprocesslog "INFO No files found in the DB for night "$date
      continue
   fi

   # get daily fits files 
   trackingfile=$auxdir/$runnumber.DRIVE_CONTROL_TRACKING_POSITION.fits
   if ! [ -e $trackingfile ]
   then 
      printprocesslog "WARN "$trackingfile" not found."
   fi
   
   sourceposfile=$auxdir/$runnumber.DRIVE_CONTROL_SOURCE_POSITION.fits
   if ! [ -e $sourceposfile ]
   then 
      printprocesslog "WARN "$sourceposfile" not found."
   else
      sourceposfiletstarti=`$factpath/fitsdump -h $sourceposfile  2>/dev/null | grep 'TSTARTI' | grep -E -o '[0-9]{5}'`
      sourceposfiletstartf=`$factpath/fitsdump -h $sourceposfile  2>/dev/null | grep 'TSTARTF' | grep -E -o '0[.][0-9]+'`
      if [ $sourceposfiletstarti -gt 30000 ]
      then 
         sourceposfiletstart=`echo " $sourceposfiletstarti + $sourceposfiletstartf - 40587 " | bc -l`
      else
         sourceposfiletstart=`echo " $sourceposfiletstarti + $sourceposfiletstartf " | bc -l`
      fi
   fi
   
   triggerratefile=$auxdir/$runnumber.FTM_CONTROL_TRIGGER_RATES.fits
   if ! [ -e $triggerratefile ]
   then 
      printprocesslog "WARN "$triggerratefile" not found."
   fi
   
   thresholdfile=$auxdir/$runnumber.FTM_CONTROL_STATIC_DATA.fits
   if ! [ -e $thresholdfile ]
   then 
      printprocesslog "WARN "$thresholdfile" not found."
   fi
   
   biasvoltagefile=$auxdir/$runnumber.BIAS_CONTROL_VOLTAGE.fits
   if ! [ -e $biasvoltagefile ]
   then 
      printprocesslog "WARN "$biasvoltagefile" not found."
   fi

   # fill auxiliary information for files
   for filenum in ${filenumbers[@]}
   do
      printprocesslog "INFO processing file number "$runnumber"_"`printf %03d $filenum`
      echo  `date`": processing file number "$runnumber"_"`printf %03d $filenum` >> $logfile 2>&1
      echo  `date`": processing file number "$runnumber"_"`printf %03d $filenum`
 
      # get information from rawfile
      rawfile=`ls $rawdir/$runnumber"_"\`printf %03d $filenum\`.fits*`
      if ! [ -e $rawfile ]
      then 
         printprocesslog "ERROR: "$rawfile" not found."
         continue
      fi

      #runtype=`$factpath/fitsdump -h $rawfile  2>/dev/null | grep RUNTYPE | grep -E -o "['][a-z-]+[']" | sed -e "s/'//g"`
      #mjdrefraw=`$factpath/fitsdump -h $rawfile  2>/dev/null | grep 'MJDREF' | grep -E -o '[0-9]{5}'`
      tstarti=`$factpath/fitsdump -h $rawfile  2>/dev/null | grep 'TSTARTI' | grep -E -o '[0-9]{5}'`
      tstartf=`$factpath/fitsdump -h $rawfile  2>/dev/null | grep 'TSTARTF' | grep -E -o '0[.][0-9]+'`
      tstopi=`$factpath/fitsdump -h $rawfile  2>/dev/null | grep 'TSTOPI' | grep -E -o '[0-9]{5}'`
      tstopf=`$factpath/fitsdump -h $rawfile  2>/dev/null | grep 'TSTOPF' | grep -E -o '0[.][0-9]+'`
      if [ "$tstarti" == "" ] || [ "$tstopi" == "" ] || [ "$tstartf" == "" ] || [ "$tstopf" == "" ]
      then 
         printprocesslog "WARN: "$rawfile": one of the following keywords is empty or 0: TSTARTI TSTARTF TSTOPI TSTOPF "
         continue
      fi
      echo $runtype
      # assuming that at least TSTARTI and TSTOPI are consistent
      #echo $rawfile
      #echo $tstarti
      #echo $tstopi
      #echo $tstartf
      #echo $tstopf
      if [ $tstarti -gt 30000 ]
      then 
         tstart=`echo " $tstarti + $tstartf - 40587 " | bc -l`
         tstart2=`echo " $tstarti + $tstartf - 40587 - 0.00011574 " | bc -l`  # 10 sec
         #tstart2=`echo " $tstarti + $tstartf - 40587 - 0.000023148 " | bc -l` # 2 sec
         tstop=`echo " $tstopi + $tstopf - 40587 " | bc -l`
      else
         tstart=`echo " $tstarti + $tstartf " | bc -l`
         tstart2=`echo " $tstarti + $tstartf - 0.00011574 " | bc -l`  # 10 sec
         #tstart2=`echo " $tstarti + $tstartf - 0.000023148 " | bc -l` # 2 sec
         tstop=`echo " $tstopi + $tstopf " | bc -l`
      fi
      #echo $tstart
      #echo $tstop
      #if [ $runnumber -eq 20111123 ] 
      #then 
      #   # add mjdref for days were aux files were inconsistent
      #   tstart=`echo " $tstart + $mjdref " | bc -l`
      #   tstart2=`echo " $tstart2 + $mjdref " | bc -l`
      #   tstop=`echo " $tstop + $mjdref " | bc -l`
      #fi

      # get information from source_pos file
      if [ -e $sourceposfile ] 
      then 
         sourcename=`$factpath/fitsdump ${sourceposfile} -c Time -c Name --filter='[1]<'${tstop}' && [1]>'${sourceposfiletstart}  2>/dev/null | tail -1 2>&1 | grep -o -E "['][a-zA-Z0-9\ \.\+\-]+[']" | sed -e "s/'//g"`
         if [ "$sourcename" == "" ]
         then
            printprocesslog "INFO couldn't get sourcename ("$sourcename") from "$sourceposfile" for "$runnumber"_"$filenum
         else 
            query="SELECT fSourceKey FROM Source WHERE fSourceName='"$sourcename"'"
            sourcekey=`sendquery`
            if [ "$sourcename" == "" ]
            then
               printprocesslog "WARN couldn't get sourcekey for source "$sourcename" from DB for "$runnumber"_"$filenum
            fi 
         fi
      fi
      if [ "$doupdate" == "force" ]
      then
         # set runtype to 'unknown', if no runtype could be retrieved from file
         if [ "$runtype" == "" ]
         then
            runtype="n/a"
         fi
         # on 15.11.2011 the runtypes had different names
         if [ "$date" == "2011/11/15" ]
         then
            if [ "$runtype" == "drs-calib" ]
            then
               runtype="drs-gain"
            fi
            if [ "$runtype" == "drs-time-calib" ]
            then
               runtype="drs-time"
            fi
            if [ "$runtype" == "pedestal" ]
            then
               runtype="drs-pedestal"
            fi
            if [ "$runtype" == "light-pulser" ]
            then
               runtype="light-pulser-ext"
            fi
            if [ "$runtype" == "pedestal-on" ]
            then
               runtype="pedestal"
            fi
         fi
         # get runtype
         query="SELECT fRunTypeKEY FROM RunType WHERE fRunTypeName='"$runtype"'"
         result2=( `sendquery` )
         if [ ${#result2} -eq 0 ]
         then 
            printprocesslog "ERROR "$numberfromname": Could not query fRunTypeKey for runtype "$runtype" ."
            continue
         fi
         # in newest data start time is in DATE-OBS
         # in older data start time is in TSTART
         # in the beginning TSTART was empty
         #runstart=`$factpath/fitsdump -h -t Events $rawfile  2>/dev/null | grep DATE-OBS | grep -E -o '20[1-9][0-9][\-][01][0-9][\-][0-3][0-9]T[0-2][0-9]:[0-6][0-9]:[0-6][0-9][.][0-9]{6}'`
         runstart=`$factpath/fitsdump -h $rawfile  2>/dev/null | grep DATE-OBS | grep -E -o '20[1-9][0-9][\-][01][0-9][\-][0-3][0-9]T[0-2][0-9]:[0-6][0-9]:[0-6][0-9][.][0-9]{6}'`
         #runstart2=`$factpath/fitsdump -h -t Events $rawfile  2>/dev/null | grep TSTART | grep -E -o '20[1-9][0-9][\-][01][0-9][\-][0-3][0-9]T[0-2][0-9]:[0-6][0-9]:[0-6][0-9][.][0-9]{6}'`
         runstart2=`$factpath/fitsdump -h $rawfile  2>/dev/null | grep TSTART | grep -E -o '20[1-9][0-9][\-][01][0-9][\-][0-3][0-9]T[0-2][0-9]:[0-6][0-9]:[0-6][0-9][.][0-9]{6}'`
         if [ "$runstart" == ""  ]
         then
            if [ "$runstart2" == ""  ]
            then
               #runstart=`$factpath/fitsdump -h -t Events $rawfile  2>/dev/null | grep DATE | grep -v 'DATE-' | grep -E -o '20[1-9][0-9][\-][01][0-9][\-][0-3][0-9]T[0-2][0-9]:[0-6][0-9]:[0-6][0-9][.][0-9]{6}'`
               runstart=`$factpath/fitsdump -h $rawfile  2>/dev/null | grep DATE | grep -v 'DATE-' | grep -E -o '20[1-9][0-9][\-][01][0-9][\-][0-3][0-9]T[0-2][0-9]:[0-6][0-9]:[0-6][0-9][.][0-9]{6}'`
            else
               runstart=$runstart2
            fi
         fi
         # in newest data start time is in DATE-END
         # in older data start time is in TSTOP
         # in the beginning TSTOP was empty
         #runstop=`$factpath/fitsdump -h -t Events $rawfile  2>/dev/null | grep DATE-END | grep -E -o '20[1-9][0-9][\-][01][0-9][\-][0-3][0-9]T[0-2][0-9]:[0-6][0-9]:[0-6][0-9][.][0-9]{6}'`
         runstop=`$factpath/fitsdump -h $rawfile  2>/dev/null | grep DATE-END | grep -E -o '20[1-9][0-9][\-][01][0-9][\-][0-3][0-9]T[0-2][0-9]:[0-6][0-9]:[0-6][0-9][.][0-9]{6}'`
         #runstop2=`$factpath/fitsdump -h -t Events $rawfile  2>/dev/null | grep TSTOP | grep -E -o '20[1-9][0-9][\-][01][0-9][\-][0-3][0-9]T[0-2][0-9]:[0-6][0-9]:[0-6][0-9][.][0-9]{6}'`
         runstop2=`$factpath/fitsdump -h $rawfile  2>/dev/null | grep TSTOP | grep -E -o '20[1-9][0-9][\-][01][0-9][\-][0-3][0-9]T[0-2][0-9]:[0-6][0-9]:[0-6][0-9][.][0-9]{6}'`
         if [ "$runstop" == ""  ]
         then
            if [ "$runstop2" == ""  ]
            then
               runstop=`stat $rawfile  2>/dev/null | grep Modify | grep -E -o '20[1-9][0-9][\-][01][0-9][\-][0-3][0-9][ ][0-2][0-9]:[0-6][0-9]:[0-6][0-9][.][0-9]{9}'`
            else
               runstop=$runstop2
            fi
         fi
         echo $runstart $runstop
         #numevents=`$factpath/fitsdump -h -t Events $rawfile  2>/dev/null | grep Events | grep -E -o '[0-9]+'`
         numevents=`$factpath/fitsdump -h $rawfile  2>/dev/null | grep Events | grep -E -o '[0-9]+'`
         #roi=`$factpath/fitsdump -h -t Events $rawfile  2>/dev/null | grep NROI | grep -v NROITM | grep -E -o '[0-9]{1,4}'`
         roi=`$factpath/fitsdump -h $rawfile  2>/dev/null | grep NROI | grep -v NROITM | grep -E -o '[0-9]{1,4}'`
         #roitm=`$factpath/fitsdump -h -t Events $rawfile  2>/dev/null | grep NROITM | grep -E -o '[0-9]{1,4}'`
         roitm=`$factpath/fitsdump -h $rawfile  2>/dev/null | grep NROITM | grep -E -o '[0-9]{1,4}'`
         #numphys=`$factpath/fitsdump -h -t Events $rawfile  2>/dev/null | grep 'NTRG ' | grep -E -o '[0-9]+'`
         numphys=`$factpath/fitsdump -h $rawfile  2>/dev/null | grep 'NTRG ' | grep -E -o '[0-9]+'`
         #numext1=`$factpath/fitsdump -h -t Events $rawfile  2>/dev/null | grep 'NTRGEXT1' | grep -E -o '[ ][0-9]+[ ]' | sed -e 's/\ //g'`
         numext1=`$factpath/fitsdump -h $rawfile  2>/dev/null | grep 'NTRGEXT1' | grep -E -o '[ ][0-9]+[ ]' | sed -e 's/\ //g'`
         #numext2=`$factpath/fitsdump -h -t Events $rawfile  2>/dev/null | grep 'NTRGEXT2' | grep -E -o '[ ][0-9]+[ ]' | sed -e 's/\ //g'`
         numext2=`$factpath/fitsdump -h $rawfile  2>/dev/null | grep 'NTRGEXT2' | grep -E -o '[ ][0-9]+[ ]' | sed -e 's/\ //g'`
         #numelp=`$factpath/fitsdump -h -t Events $rawfile  2>/dev/null | grep 'NTRGLPE' | grep -E -o '[0-9]+'`
         numelp=`$factpath/fitsdump -h $rawfile  2>/dev/null | grep 'NTRGLPE' | grep -E -o '[0-9]+'`
         #numilp=`$factpath/fitsdump -h -t Events $rawfile  2>/dev/null | grep 'NTRGLPI' | grep -E -o '[0-9]+'`
         numilp=`$factpath/fitsdump -h $rawfile  2>/dev/null | grep 'NTRGLPI' | grep -E -o '[0-9]+'`
         #numoth=`$factpath/fitsdump -h -t Events $rawfile  2>/dev/null | grep 'NTRGMISC' | grep -E -o '[0-9]+'`
         numoth=`$factpath/fitsdump -h $rawfile  2>/dev/null | grep 'NTRGMISC' | grep -E -o '[0-9]+'`
         #numped=`$factpath/fitsdump -h -t Events $rawfile  2>/dev/null | grep 'NTRGPED' | grep -E -o '[0-9]+'`
         numped=`$factpath/fitsdump -h $rawfile  2>/dev/null | grep 'NTRGPED' | grep -E -o '[0-9]+'`
         #numtime=`$factpath/fitsdump -h -t Events $rawfile  2>/dev/null | grep 'NTRGTIM' | grep -E -o '[0-9]+'`
         numtime=`$factpath/fitsdump -h $rawfile  2>/dev/null | grep 'NTRGTIM' | grep -E -o '[0-9]+'`
         #compiled=`$factpath/fitsdump -h -t Events $rawfile  2>/dev/null | grep 'COMPILED' | grep -E -o "['][a-zA-Z]+[ ][ 12][0-9][ ]20[0-9][0-9][ ][0-2][0-9]:[0-5][0-9]:[0-5][0-9][']" | sed -e "s/'//g"`
         compiled=`$factpath/fitsdump -h $rawfile  2>/dev/null | grep 'COMPILED' | grep -E -o "['][a-zA-Z]+[ ][ 12][0-9][ ]20[0-9][0-9][ ][0-2][0-9]:[0-5][0-9]:[0-5][0-9][']" | sed -e "s/'//g"`
         if ! [ "$compiled" == "" ]
         then 
            compiletime=`date +'%F %H:%M:%S' --date="${compiled}" `
         else
            compiletime=
         fi
         #revnum=`$factpath/fitsdump -h -t Events $rawfile  2>/dev/null | grep 'REVISION' | grep -E -o "['][0-9]+[:]?[0-9]*[MSP]*[']" | sed -e "s/'//g"`
         revnum=`$factpath/fitsdump -h $rawfile  2>/dev/null | grep 'REVISION' | grep -E -o "['][0-9]+[:]?[0-9]*[MSP]*[']" | sed -e "s/'//g"`
         # get checksums from header
         #checksum=`$factpath/fitsdump -h -t Events $rawfile  2>/dev/null | grep CHECKSUM | grep -E -o '[a-zA-Z0-9]{16}'`
         checksum=`$factpath/fitsdump -h $rawfile  2>/dev/null | grep CHECKSUM | grep -E -o '[a-zA-Z0-9]{16}'`
         if [ "$checksum" == "" ]
         then 
            printprocesslog "WARN checksum for file "$rawfile" is empty."
         fi
         #datasum=`$factpath/fitsdump -h -t Events $rawfile  2>/dev/null | grep DATASUM | grep -E -o '[0-9]{1,10}'`
         datasum=`$factpath/fitsdump -h $rawfile  2>/dev/null | grep DATASUM | grep -E -o '[0-9]{1,10}'`
         if [ "$datasum" == "" ]
         then 
            printprocesslog "WARN datasum for file "$rawfile" is empty."
         fi
         # check if this run has drs file
         #   in case file is available, get STEP from header
         # in the very beginning only drs-files were existing
         # in the beginning the keywords DRSCALIB and STEP were not existing
         drsfile=`echo $rawfile | sed -e 's/fits/drs.fits/'`
         numdrsfiles=`ls $drsfile 2>/dev/null | wc -l`
         #drscalib=`$factpath/fitsdump -h -t Events $rawfile  2>/dev/null | grep DRSCALIB | grep -E -o "['][TF][']" | sed -e "s/'//g"`
         drscalib=`$factpath/fitsdump -h $rawfile  2>/dev/null | grep DRSCALIB | grep -E -o "[\ ][TF][\ ]" | sed -e "s/\ //g"`
         if [ "$drscalib" == "T" ]
         then 
            #step=`$factpath/fitsdump -h -t Events $rawfile  2>/dev/null | grep DRSSTEP | grep -E -o "['][012][']" | sed -e "s/'//g"`
            step=`$factpath/fitsdump -h $rawfile  2>/dev/null | grep DRSSTEP | grep -E -o "[\ ][012][\ ]" | sed -e "s/\ //g"`
            #stepfromdrs=`$factpath/fitsdump -h -t Events $drsfile  2>/dev/null | grep STEP | grep -E -o "['][012][']" | sed -e "s/'//g"`
            stepfromdrs=`$factpath/fitsdump -h $drsfile  2>/dev/null | grep STEP | grep -E -o "[\ ][012][\ ]?" | sed -e "s/\ //g"`
            if [ "$stepfromdrs" != "$step" ]
            then
               printprocesslog "ERROR for file "$rawfile" step from drsfile ("$stepfromdrs") and from file ("$step") do not agree."
               if [ "$stepfromdrsfile" != "" ] && [ "$step" == "" ]
               then 
                  step=$stepfromdrsfile
                  printprocesslog "WARN setting drsstep from drsfile ("$stepfromdrs") although value differs from the one in file "$rawfile"."
               fi
            fi
            if ! [ $numdrsfiles -eq 1 ]
            then 
               printprocesslog "ERROR for file "$rawfile" number of drsfiles ("$numdrsfiles") and information from header ("$drscalib") don't agree."
            fi
            if [ "$step" = "" ]
            then 
               printprocesslog "ERROR file "$rawfile" has drsfiles ("$numdrsfiles"), but step ("$step") is empty."
            fi
         else
            if ! [ "$drscalib" == "F" ]
            then
               printprocesslog "WARN for file "$rawfile" DRSCALIB is neither T nor F."
            fi
         fi
      fi
      
      # build query to update runinfo in DB
      query="UPDATE RunInfo SET "
      
      # fill source key only if available
      if ! [ "$sourcekey" = "" ]
      then
         query=$query" fSourceKey="$sourcekey", "
      else
         query=$query" fSourceKey=NULL, "
      fi
      
      # get information from tracking
      if [ -e $trackingfile ] 
      then 
         # get statistics
         trackingstats=`$factpath/fitsdump $trackingfile -s -c Time -c Ra -c Dec -c Zd -c Az --filter='[1]<'${tstop}' && [1]>'${tstart}  2>/dev/null`
         # RA
         evaluatestatistics "Ra" $trackingstats
         #echo $min"_"$max"_"$
         if [ "$evaluation" != "" ]
         then
            if [ "$min" == "$max" ] 
            then
               query=$query" fRightAscension="$mean
            else
               query=$query" fRightAscension=NULL"
               printprocesslog "WARN for $rawfile RA changes within run (min: "$min", max: "$max")."
            fi
            # Declination
            evaluatestatistics "Dec" $trackingstats
            if [ "$decmin" == "$decmax" ]
            then
               query=$query", fDeclination="$mean
            else
               query=$query", fDeclination=NULL"
               printprocesslog "WARN for $rawfile declination changes within run (min: "$min", max: "$max")."
            fi
         else
            query=$query" fRightAscension=NULL"
            query=$query", fDeclination=NULL"
         fi
         # Zd
         evaluatestatistics "Zd" $trackingstats
         if [ "$evaluation" != "" ]
         then
            query=$query", fZenithDistanceMin="$min
            query=$query", fZenithDistanceMean="$mean
            query=$query", fZenithDistanceMax="$max
         else
            query=$query", fZenithDistanceMin=NULL"
            query=$query", fZenithDistanceMean=NULL"
            query=$query", fZenithDistanceMax=NULL"
         fi
         # Az
         evaluatestatistics "Az" $trackingstats
         if [ "$evaluation" != "" ]
         then
            query=$query", fAzimuthMin="$min
            query=$query", fAzimuthMean="$mean
            query=$query", fAzimuthMax="$max
         else
            query=$query", fAzimuthMin=NULL"
            query=$query", fAzimuthMean=NULL"
            query=$query", fAzimuthMax=NULL"
         fi
      else
         query=$query" fRightAscension=NULL"
         query=$query", fDeclination=NULL"
         query=$query", fZenithDistanceMin=NULL"
         query=$query", fZenithDistanceMean=NULL"
         query=$query", fZenithDistanceMax=NULL"
         query=$query", fAzimuthMin=NULL"
         query=$query", fAzimuthMean=NULL"
         query=$query", fAzimuthMax=NULL"
      fi
   
      # get information from trigger
      if [ -e $triggerratefile ] 
      then 
         # get statistics
         triggerstats=`$factpath/fitsdump $triggerratefile -s -c Time -c TriggerRate --filter='[1]<'${tstop}' && [1]>'${tstart}  2>/dev/null`
         evaluatestatistics "TriggerRate" $triggerstats
         if [ "$evaluation" != "" ]
         then
            query=$query", fTriggerRateMedian="$med
         else
            query=$query", fTriggerRateMedian=NULL"
         fi
      else
         query=$query", fTriggerRateMedian=NULL"
      fi
      
      # get information from thresholds
      if [ -e $thresholdfile ] 
      then 
         # get statistics
         thresholdstats=`$factpath/fitsdump $thresholdfile -s -c Time -c PatchThresh --filter='[1]<'${tstop}' && [1]>'${tstart}  2>/dev/null`
         evaluatestatistics "PatchThresh" $thresholdstats
         if [ "$evaluation" = "" ]
         then
            thresholdstats=`$factpath/fitsdump $thresholdfile -s -c Time -c PatchThresh --filter='[1]<'${tstop}' && [1]>'${tstart2}  2>/dev/null`
            #echo "$factpath/fitsdump $thresholdfile -s -c Time -c PatchThresh --filter='[1]<'${tstop}' && [1]>'${tstart2}  2>/dev/null"
            evaluatestatistics "PatchThresh" $thresholdstats
         fi
         if [ "$evaluation" != "" ]
         then
            query=$query", fThresholdMedian="$med
         else
            query=$query", fThresholdMedian=NULL"
         fi
      else
         query=$query", fThresholdMedian=NULL"
      fi

      # get information from bias: U
      if [ -e $biasvoltagefile ] 
      then 
         if [ $runnumber -gt 20120324 ]
         then
            biasstats=`$factpath/fitsdump $biasvoltagefile -s -c Time -c Uout --filter='[1]<'${tstop}' && [1]>'${tstart}  2>/dev/null`
            evaluatestatistics "Uout" $biasstats
            if [ "$evaluation" = "" ]
            then
               biasstats=`$factpath/fitsdump $biasvoltagefile -s -c Time -c Uout --filter='[1]<'${tstop}' && [1]>'${tstart2}  2>/dev/null`
               evaluatestatistics "Uout" $biasstats
            fi
         else
            biasstats=`$factpath/fitsdump $biasvoltagefile -s -c Time -c U --filter='[1]<'${tstop}' && [1]>'${tstart}  2>/dev/null`
            evaluatestatistics "U" $biasstats
            if [ "$evaluation" = "" ]
            then
               biasstats=`$factpath/fitsdump $biasvoltagefile -s -c Time -c U --filter='[1]<'${tstop}' && [1]>'${tstart2}  2>/dev/null`
               evaluatestatistics "U" $biasstats
            fi
         fi
         if [ "$evaluation" != "" ]
         then
            query=$query", fBiasVoltageMedian="$med
         else
            query=$query", fBiasVoltageMedian=NULL"
         fi
      else
         query=$query", fBiasVoltageMedian=NULL"
      fi
      if [ "$doupdate" == "force" ]
      then
         query=$query", fRunStart='"$runstart"', fRunStop='"$runstop"'"
         query=$query", fRunTypeKey="${result2[0]}
         if [ "$numevents" != "" ]
         then
            query=$query", fNumEvents="$numevents
         fi
         if [ "$roi" != "" ]
         then
            query=$query", fROI="$roi
         fi
         if [ "$roitm" != "" ]
         then
            query=$query", fROITimeMarker="$roitm
         fi
         if [ "$numphys" != "" ]
         then
            query=$query", fNumPhysicsTrigger="$numphys
         fi
         if [ "$numext1" != "" ]
         then
            query=$query", fNumExt1Trigger="$numext1
         fi
         if [ "$numext2" != "" ]
         then
            query=$query", fNumExt2Trigger="$numext2
         fi
         if [ "$numelp" != "" ]
         then
            query=$query", fNumELPTrigger="$numelp
         fi
         if [ "$numilp" != "" ]
         then
            query=$query", fNumILPTrigger="$numilp
         fi
         if [ "$numped" != "" ]
         then
            query=$query", fNumPedestalTrigger="$numped
         fi
         if [ "$numtime" != "" ]
         then
            query=$query", fNumTimeTrigger="$numtime
         fi
         if [ "$numoth" != "" ]
         then
            query=$query", fNumOtherTrigger="$numoth
         fi
         if [ "$checksum" != "" ]
         then
            query=$query", fCheckSum='"$checksum"'"
         fi
         if [ "$datasum" != "" ]
         then
            query=$query", fDataSum='"$datasum"'"
         fi
         if [ "$numdrsfiles" != "" ]
         then
            query=$query", fHasDrsFile="$numdrsfiles
         fi
         if [ "$step" != "" ]
         then
            query=$query", fDrsStep="$step
         fi
         if [ "$compiletime" != "" ]
         then
            query=$query", fCompileTime='"$compiletime"'"
         fi
         if [ "$revnum" != "" ]
         then
            query=$query", fRevisionNumber='"$revnum"'"
         fi
      fi

      
      # add where condition
      query=$query" WHERE fNight="$runnumber" AND fRunID="$filenum
      echo $query

      # send query to DB
      sendquery >/dev/null
   done
done

finish