source: trunk/Mars/hawc/processing/DiskToDB/write-calibration-query.sh@ 20103

#!/bin/bash
#
# Write SQL query for calibration for a given telescope TEL and night NIGHT to
# OUTPUT
#
# Example:
#       bash write-calibration-query.sh 1 20201111

set -o errexit
set -o errtrace
set -o nounset
set -o pipefail

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

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

trap ErrExit ERR
trap StrgCExit INT

# define variables
OUTPUT=find-calibration-file2.sql
readonly PROGRAM=$0
INSERT="INSERT INTO"

usage()
{
        echo "usage: $PROGRAM [-hir] [-o outfile] [Telescope 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 at least two arguments are provided
if [ -z "${1}" ] || [ -z "${2}" ]
then
        echo "Not enough arguments. Check -h for help!"
        exit 1
fi

TEL="${1}"
NIGHT="${2}"


echo \
"\
-- You might want to change the table name
CREATE TABLE IF NOT EXISTS Calibration
(
        Telescope tinyint UNSIGNED NOT NULL,
        NIGHT int UNSIGNED NOT NULL,
        RUNID mediumint UNSIGNED NOT NULL,
        DrsNight double NOT NULL,
        DrsRunId double NOT NULL,
        DeltaT double UNSIGNED NOT NULL,
        PRIMARY KEY (Telescope, NIGHT, RUNID)
);
"\ > ${OUTPUT}

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

echo \
"\
-- Set Status to 6 if there was an error is the extraction of RawData
UPDATE DataOnDisk 
SET DataOnDisk.calibration = 6
WHERE DataOnDisk.header != 0;

CREATE TEMPORARY TABLE Calibration_tmp
(
        WITH Table1 AS
        (
                SELECT
                -- NIGHT and RUNID of the data file
                R1.Telescope AS Telescope,
                R1.NIGHT     AS NIGHT,
                R1.RUNID     AS RUNID,
                -- NIGHT and RUNID of the DRS file
                R2.NIGHT     AS DrsNight,
                R2.RUNID     AS DrsRunID,
                -- Time difference between the two
                LEAST(
                        ABS(UNIX_TIMESTAMP(R2.\`DATE-OBS\`)
                                - UNIX_TIMESTAMP(R1.\`DATE-OBS\`)),
                        ABS(UNIX_TIMESTAMP(R2.\`DATE-OBS\`)
                                - UNIX_TIMESTAMP(R1.\`DATE-END\`)),
                        ABS(UNIX_TIMESTAMP(R2.\`DATE-END\`)
                                - UNIX_TIMESTAMP(R1.\`DATE-OBS\`)),
                        ABS(UNIX_TIMESTAMP(R2.\`DATE-END\`)
                                - UNIX_TIMESTAMP(R1.\`DATE-END\`))
                ) AS DeltaT,
                -- Assign a row number to each combination of data-file and 
                -- drs-file after sorting them by time difference
                ROW_NUMBER() OVER(PARTITION BY R1.NIGHT, R1.RUNID, R1.Telescope 
                        ORDER BY
                        LEAST(
                                ABS(UNIX_TIMESTAMP(R2.\`DATE-OBS\`)
                                        - UNIX_TIMESTAMP(R1.\`DATE-OBS\`)),
                                ABS(UNIX_TIMESTAMP(R2.\`DATE-OBS\`)
                                        - UNIX_TIMESTAMP(R1.\`DATE-END\`)),
                                ABS(UNIX_TIMESTAMP(R2.\`DATE-END\`)
                                        - UNIX_TIMESTAMP(R1.\`DATE-OBS\`)),
                                ABS(UNIX_TIMESTAMP(R2.\`DATE-END\`)
                                        - UNIX_TIMESTAMP(R1.\`DATE-END\`))
                        )
                ) AS RowNumber
                FROM (
                        SELECT RawData.* FROM RawData 
                        INNER JOIN DataOnDisk 
                        ON DataOnDisk.Telescope = RawData.Telescope
                        AND DataOnDisk.NIGHT = RawData.NIGHT
                        AND DataOnDisk.RUNID = RawData.RUNID
                        AND DataOnDisk.ISDRSFILE = 0
                        WHERE DataOnDisk.header = 0
                ) AS R1
                -- Join the table with itself to get all possible combinations 
                -- of runs
                CROSS JOIN (
                        SELECT RawData.* FROM RawData 
                        INNER JOIN DataOnDisk 
                        ON DataOnDisk.Telescope = RawData.Telescope
                        AND DataOnDisk.NIGHT = RawData.NIGHT
                        AND DataOnDisk.RUNID = RawData.RUNID
                        AND DataOnDisk.ISDRSFILE = 0
                        WHERE DataOnDisk.header = 0
                ) AS R2
                -- Join the DRS files with the table that tells you whether
                -- they can be used if the table does not (yet) exists, this
                -- can be omitted
                WHERE
                -- Of course claibration must be from the same telescope
                R1.Telescope=R2.Telescope
                AND
                -- Select all combinations that have a data file in R1 and a
                -- drs file (at step 1) in R2
                ISNULL(R1.DRSSTEP) AND R2.DRSSTEP=1
                AND 
                -- Only DRS files with 100 events and the correct ROI are
                -- considered
                R2.ZNAXIS2=1000 AND R1.NROI=R2.NROI 
                AND R1.NIGHT = ${NIGHT}
                AND R2.NIGHT = ${NIGHT}
                AND R1.Telescope = ${TEL}
                -- AND
                -- Check if they are valid
                -- IFNULL(DoNotUse.WrongVoltage,false)!=true
                -- This can be used to restrict the number of combinations     
                -- AND ABS(UNIX_TIMESTAMP(R2.\`DATE-OBS\`)-UNIX_TIMESTAMP(R1.\`DATE-OBS\`))<78*3600
                ORDER BY
                R1.Telescope,
                R1.NIGHT,
                R1.RUNID,
                DeltaT
        )
        SELECT
        Telescope,
        NIGHT,
        RUNID,
        DrsNight,
        DrsRunId,
        DeltaT/3600e0 AS DeltaT
        FROM
        Table1
        WHERE
        -- Select only the rows with the smallest DeltaT
        RowNumber = 1
);

-- Insert Calibration data into Database
INSERT INTO Calibration SELECT * FROM Calibration_tmp;

-- Set Status to 0 for all valid headers
UPDATE DataOnDisk 
SET DataOnDisk.calibration = 0
WHERE DataOnDisk.header = 0;

-- Set Status to 2 if there was no drs file with step = 1 in the NIGHT
UPDATE DataOnDisk
SET DataOnDisk.calibration = 2
WHERE EXISTS(SELECT * FROM Calibration WHERE NIGHT=${NIGHT}) = 0;
"\ >> ${OUTPUT}