source: trunk/Mars/hawc/processing/extract-aux-data.sh@ 20030

#!/bin/bash

# The base directory of the aux-files
AUX=/home/tbretz/data/aux
# The Mars build directory
MARS=~/Mars/build
# The path (relative to your Mars/build directory where the processing macros are stored
MACROS=../hawc/processing

# Open the query-file with the create table statement
echo \
"\
CREATE TABLE AuxData
(
   NIGHT           INT       UNSIGNED  NOT NULL,
   RUNID           MEDIUMINT UNSIGNED  NOT NULL,
   TavgDRS         FLOAT,
   TrmsDRS         FLOAT,
   Umed            FLOAT,
   Uavg            FLOAT,
   Udev            FLOAT,
   Urms            FLOAT,
   Imed            FLOAT,
   Iavg            FLOAT,
   Idev            FLOAT,
   Irms            FLOAT,
   Tmed            FLOAT,
   Tavg            FLOAT,
   Tdev            FLOAT,
   Trms            FLOAT,
   TavgPSU         FLOAT,
   TotalOnTime     FLOAT,
   TotalDeadTime   FLOAT,
   TavgFTM         FLOAT,
   ThresholdChMin  MEDIUMINT UNSIGNED,
   ThresholdChMed  FLOAT,
   ThresholdChMax  MEDIUMINT UNSIGNED,
   ThresholdSumMin MEDIUMINT UNSIGNED,
   ThresholdSumMax MEDIUMINT UNSIGNED,
   RateCh0         FLOAT,
   RateCh1         FLOAT,
   RateCh2         FLOAT,
   RateCh3         FLOAT,
   RateCh4         FLOAT,
   RateCh5         FLOAT,
   RateCh6         FLOAT,
   RateCh7         FLOAT,
   RateSum0        FLOAT,
   RateSum1        FLOAT,
   PRIMARY KEY(NIGHT, RUNID)
) 
SELECT * FROM 
( VALUES
"\ > insert-data.sql

# Get all available runs and their start and stop time
# note that no check is done on the stop time!
echo \
"\
SELECT 
    NIGHT, RUNID, TSTARTI+TSTARTF, TSTOPI+TSTOPF
FROM 
    RawData\
"\
 |  mysql \
       --defaults-file=~/.password.cnf \
       --user=hawcwrite \
       --host=ihp-pc45.ethz.ch \
       --database=hawcdata \
       --skip-column-names \
       --batch --raw \
       --compress \
 | \
while IFS= read -r LINE
do

  # Extract night, runid, begin and end
  NIGHT=`echo "$LINE" | awk -F"\t" '{print $1}'`
  RUNID=`echo "$LINE" | awk -F"\t" '{print $2}'`
  BEG=`echo   "$LINE" | awk -F"\t" '{print $3}'`
  END=`echo   "$LINE" | awk -F"\t" '{print $4}'`

  # Split night into year, month, day
  YEAR=`echo ${NIGHT}  | cut -c1-4`
  MONTH=`echo ${NIGHT} | cut -c5-6`
  DAY=`echo ${NIGHT}   | cut -c7-8`

  # Base path for the night
  FNAME=${AUX}/${YEAR}/${MONTH}/${DAY}/${NIGHT}

  echo ""
  echo ${FNAME}

  cd ${MARS}

  # Return corresponding data for DRS temperatures from FAD_CONTROL_TEMPEREATURE
  DRSTEMP=`root -b -q -l '${MACROS}/drstemp.C("'${FNAME}'.FAD_CONTROL_TEMPERATURE.fits",'${BEG}','${END}')' | grep result | cut -f2- -d" "`
  if [ ! -n "${DRSTEMP}" ]
  then
        DRSTEMP="NULL, NULL"
  fi
  echo ${DRSTEMP}

  # Return corresponding data for currents from BIAS_CONTROL_DYNAMIC_DATA
  BIAS_DATA=`root -b -q -l '${MACROS}/currents.C("'${FNAME}'.BIAS_CONTROL_DYNAMIC_DATA.fits",'${BEG}','${END}')' | grep result | cut -f2- -d" "`
  if [ ! -n "${BIAS_DATA}" ]
  then  
        BIAS_DATA="NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL"
  fi
  echo ${BIAS_DATA}

  # Return corresponding data for trigger information from FTM_CONTROL_DATA
  FTM_DATA=`root -b -q -l '${MACROS}/trigger.C("'${FNAME}'.FTM_CONTROL_DATA.fits",'${BEG}','${END}')' | grep result | cut -f2- -d" "`
  if [ ! -n "${RESULT}" ]
  then
        FTM_DATA="NULL, NULL, NULL, NULL"
  fi
  echo ${FTM_DATA}

  # Return corresponding data for threshold seeting from FTU_CONTROL_DATA
  FTU_DATA=`root -b -q -l '${MACROS}/threshold.C("'${FNAME}'.FTU_CONTROL_DATA.fits",'${BEG}','${END}')' | grep result | cut -f2- -d" "`
  if [ ! -n "${FTU_DATA}" ]
  then
        FTU_DATA="NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL"
  fi
  echo ${FTU_DATA}

  cd -

  # Write the result as one row to the query file
  echo "/* "${FNAME}" */" >> insert-data.sql
  echo "ROW(" >> insert-data.sql
  echo " "${NIGHT}, ${RUNID}, >> insert-data.sql
  echo " "${DRS_TEMP},        >> insert-data.sql
  echo " "${BIAS_DATA},       >> insert-data.sql
  echo " "${FTU_DATA},        >> insert-data.sql
  echo " "${FTM_DATA}         >> insert-data.sql
  echo ")," >> insert-data.sql

done

# Finish the query file with defining the column names
echo "\
AS
(
   NIGHT, RUNID,
   TavgDRS, TrmsDRS,
   Umed, Uavg, Udev, Urms,
   Imed, Iavg, Idev, Irms,
   Tmed, Tavg, Tdev, Trms,
   TavgPSU,
   TotalOnTime, TotalDeadTime, Efficiency, TavgFTM,
   ThresholdChMin, ThresholdChMed, ThresholdChMax, 
   ThresholdSumMin, ThresholdSumMax, 
   RateCh0, RateCh1, RateCh2, RateCh3, RateCh4, RateCh5, RateCh6, RateCh7,
   RateSum0, RateSum1
)\ 
" >> insert-data.sql