source: trunk/Mars/hawc/processing/DiskToDB/extract-aux-data2.sh@ 20112

#!/bin/bash
#
# Calculate aux data from auxfiles in directory DIR and write SQL query 
# containing the aux information to OUTPUT
#
# Example:
#       bash extract-aux-data2.sh cred-file 1 /data/HE01/aux 20201111

set -o nounset

function ErrExit()
{
        echo "ERROR: Line `caller`: ${BASH_COMMAND}" >&2
        rm -f ${TMPOUT}
        exit 1
}

function StrgCExit()
{
        echo " "
        echo "$0 was forcefully terminated" >&2
        rm -f ${TMPOUT}
        exit 1
}

trap ErrExit ERR
trap StrgCExit INT


# File to which the queries are written
OUTPUT="insert-aux.sql"
# The Mars build directory
readonly MARS="/home/hawc/Desktop/Mars/"
# The path to your Mars/build directory where the processing macros are stored
readonly MACROS="/home/hawc/Desktop/Mars-src/hawc/processing/"
readonly PROGRAM=$0
INSERT="INSERT INTO"

usage()
{
        echo "usage: $PROGRAM [-hir] [-o outfile] [Credentials Telescope Dir Night]" 
        echo "  -h              display help"
        echo "  -i              query ignores already existing rows"
        echo "  -r              query replaces already existing rows(overwrites -i)"
        echo "  -d              delete all entries for a given Night and Telescope"
        echo "  -o outfile      name of the SQL query file"
        exit 1;
}

# Check for flags
while getopts 'hirdo:' flag
do
        case "${flag}" in
                h) usage ;;
                i) INSERT="INSERT IGNORE" ;;
                r) INSERT="REPLACE INTO" ;;
                d) DELETE="true" ;;
                o) OUTPUT="${OPTARG}" ;;
                *) usage ;;
        esac
done
shift $(($OPTIND-1))

# Check if Mars exists
if [ ! -d "${MARS}" ]
then
        echo "Mars does not exists at ${MARS}. Please change in \
                script ${PROGRAM}."
        exit 1
fi

# Check if MACROS exists
if [ ! -d "${MACROS}" ]
then
        echo "Macros directorey does not exists at ${MACROS}. Please change in \
                script ${PROGRAM}."
        exit 1
fi

# Check if at least two arguments are provided
if [ -z "${1}" ] || [ -z "${2}" ] || [ -z "${3}" ] || [ -z "${4}" ]
then
        echo "Not enough arguments. Check -h for help!"
        exit 1
fi

# File containing the access credentials for the database
CREDENTIALS="${1}"
# Specify the telescope for which to run the script.
TEL="${2}"
# The base directory of the aux-files
DIR="${3}"
# NIGHT
NIGHT="${4}"

TMPOUT="./tmpAuxOut-${TEL}.sql"
ERRCODE=

# Open the query-file with the create table statement
echo \
"\
CREATE TABLE IF NOT EXISTS AuxData
(
   Telescope       TINYINT   UNSIGNED  NOT NULL,
   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(Telescope, NIGHT, RUNID)
);
"\ > ${OUTPUT}

# Set previous fail stats to also be fail here
echo "UPDATE DataOnDisk"                        >> ${OUTPUT}
echo "SET DataOnDisk.auxiliary = 6"             >> ${OUTPUT}
echo "WHERE (DataOnDisk.calibration != 0"       >> ${OUTPUT}
echo "OR DataOnDisk.header != 0)"               >> ${OUTPUT}
echo "AND DataOnDisk.Telescope = ${TEL}"        >> ${OUTPUT}
echo "AND DataOnDisk.NIGHT = ${NIGHT};"         >> ${OUTPUT}

if [ ${DELETE} = "true" ]
then
        echo "DELETE FROM AuxData" >> ${OUTPUT}
        echo "WHERE Telescope = ${TEL}" >> ${OUTPUT}
        echo "AND NIGHT = ${NIGHT};" >> ${OUTPUT}
        echo "" >> ${OUTPUT}
fi

echo \
"\
${INSERT} AuxData
SELECT * FROM
( VALUES
"\ >> ${OUTPUT}

# Get all available runs and their start and stop time
# note that no check is done on the stop time!
echo \
"\
SELECT RawData.NIGHT,
        RawData.RUNID,
        RawData.TSTARTI + RawData.TSTARTF,
        RawData.TSTOPI + RawData.TSTOPF
FROM RawData
INNER JOIN DataOnDisk
ON DataOnDisk.Telescope = RawData.Telescope
AND DataOnDisk.NIGHT = RawData.NIGHT
AND DataOnDisk.RUNID = RawData.RUNID
WHERE RawData.Telescope=${TEL}
AND RawData.NIGHT=${NIGHT}
AND DataOnDisk.ISDRSFILE = 0
AND DataOnDisk.header = 0
AND DataOnDisk.calibration = 0\
"\
 |  mysql \
       --defaults-file=${CREDENTIALS} \
       --skip-column-names \
       --batch --raw \
       --compress \
 | \
while read -r -a LINE
do
        # Extract night, runid, begin and end
        NIGHT=${LINE[0]}
        RUNID=${LINE[1]}
        BEG=${LINE[2]}
        END=${LINE[3]}

        # Split night into year, month, day
        # Base path for the night
        FNAME="${DIR}"/${NIGHT:0:4}/${NIGHT:4:2}/${NIGHT:6:2}/${NIGHT}

        echo ${FNAME}

        cd ${MARS}

        TNAME="${FNAME}".FAD_CONTROL_TEMPERATURE.fits
        if [ ! -f "${TNAME}" ]
        then
                DRS_TEMP="NULL, NULL"
                DRS_TEMP_ERR=2
        else
                # Return corresponding data for DRS temperatures from 
                # FAD_CONTROL_TEMPEREATURE
                DRS_TEMP=`root -b -q -l \
                        "${MACROS}"/drstemp.C\(\""${TNAME}"\","${BEG}","${END}"\) \
                        | grep result \
                        | cut -f2- -d" "`

                if [ ! -n "${DRS_TEMP}" ]
                then
                        DRS_TEMP="NULL, NULL"
                        DRS_TEMP_ERR=1
                else
                        DRS_TEMP_ERR=0
                fi
                echo ${DRS_TEMP}
        fi

        # Return corresponding data for currents from BIAS_CONTROL_DYNAMIC_DATA
        BNAME="${FNAME}".BIAS_CONTROL_DYNAMIC_DATA.fits
        if [ ! -f "${BNAME}" ]
        then
                BIAS_DATA="NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL"
                BIAS_DATA_ERR=20
        else
                BIAS_DATA=`root -b -q -l \
                        "${MACROS}"/currents.C\(\""${BNAME}"\","${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"
                        BIAS_DATA_ERR=10
                else
                        BIAS_DATA_ERR=0
                fi
                echo ${BIAS_DATA}
        fi

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

                if [ ! -n "${FTM_DATA}" ]
                then
                        FTM_DATA="NULL, NULL, NULL, NULL"
                        FTM_DATA_ERR=100
                else
                        FTM_DATA_ERR=0
                fi
                echo ${FTM_DATA}
        fi

        # Return corresponding data for threshold seeting from FTU_CONTROL_DATA
        UNAME="${FNAME}".FTU_CONTROL_DATA.fits
        if [ ! -f "${UNAME}" ]
        then
                        FTU_DATA="NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL"
                        FTU_DATA_ERR=2000
        else
                FTU_DATA=`root -b -q -l \
                        "${MACROS}"/threshold.C\(\""${UNAME}"\","${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"
                        FTU_DATA_ERR=1000
                else
                        FTU_DATA_ERR=0
                fi
                echo ${FTU_DATA}
        fi

        cd -

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

        ERRCODE=$((DRS_TEMP_ERR + BIAS_DATA_ERR + FTM_DATA_ERR + FTU_DATA_ERR))

        echo "UPDATE DataOnDisk SET auxiliary=${ERRCODE}"       >> ${TMPOUT}
        echo "WHERE NIGHT=${NIGHT}"                             >> ${TMPOUT}
        echo "AND Telescope=${TEL}"                             >> ${TMPOUT}
        echo "AND RUNID=${RUNID};"                              >> ${TMPOUT}

done

# Delete last comma and replace it with closing parintheses
if [ -f "${TMPOUT}" ]
then
        sed -i '$s/,/)/' "${OUTPUT}"

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

else
        head -n -4 "${OUTPUT}" > out.sql.new && mv out.sql.new "${OUTPUT}"
fi

if [ -f "${TMPOUT}" ]
then
        cat ${TMPOUT} >> ${OUTPUT}
fi

rm -f ${TMPOUT}