source: trunk/DataCheck/QuickLook/SendData_Internal.sh@ 19007

#!/bin/bash
#

source `dirname $0`/../Sourcefile.sh
printprocesslog "INFO starting $0"

echo "Sent data"

start=$1
stop=$2
source=$3
bin=$4
email=$5
table=$6
time=$7
expert=$8
dch=$9
zdcut=${10}
thcut=${11}
dark=${12}
dust=${13}


query="SELECT fSourceName FROM Source WHERE fSourceKey="$source
sourcename=`sendquery`

function printinfo()
{
   echo "# "
   echo "# Please cite the FACT design paper and the QLA webpage when using these data."
   echo "#   FACT design paper: H. Anderhub et al. JINST 8 (2013) P6008 "
   echo "#                http://iopscience.iop.org/1748-0221/8/06/P06008 "
   echo "#   QLA webpage: http://www.fact-project.org/monitoring"
   echo "# If you intent to use the data, please let us know for reference. "
   echo "# "
   echo "# Remarks:"
   echo "#  * These are the results of a fast quick look analyis "
   echo "#    on site, i.e. they are preliminary. "
   echo "#  * The quick look analysis includes all data, "
   echo "#    i.e. no data selection done."
   echo "#  * The given values are not fluxes but excess rates "
   echo "#    (number of excess events per effective ontime), "
   echo "#    i.e. there is a dependence on trigger threshold and "
   echo "#    zenith distance of the observation (with the current "
   echo "#    analysis for zenith distance > 40 degree and trigger "
   echo "#    threshold > 500 DAC counts)."
   echo "#  * Nights with less than 20 minutes of data are neglected "
   echo "#    for nightly binning. "
   echo "#  * The QLA results are not reprocessed when a new software "
   echo "#    version is introduced. "
   echo "#  * In case, you need further details about the data or a"
   echo "#    different binning, please do not hesitate to contact us."
   echo "#  * The QLA contains all data since 12.12.2012. "
   echo "#    For older data, please contact us. "
   echo "# "
   echo "# Contact: Daniela Dorner dorner@astro.uni-wuerzburg.de "
   echo "# "
   echo "# This file was created at "`date`
   echo "# Source: "$sourcename
   echo "# Timerange: "$start" - "$stop
   echo "# Table: "$table
   echo "# Zdcut: "$zdcut
   echo "# thcut: "$thcut
   echo "# moon zd: "$dark
   echo "# dust: "$dust
   if [ $dch -eq 0 ]
   then 
      echo "# no datacheck"
   else
      echo "# some rough automatic datacheck applied"
   fi
   if [ $bin -lt 0 ]
   then
      echo "# Binning: nightly "
   else
      echo "# Binning: "$bin" min "
   fi
   echo "# "
   echo "# start("$time") stop("$time") excess-rate(evts/h) error-excess-rate(evts/h) ontime(min) significance[if Nsig>10 and Nbg>10 (or Nexc>10)] significance zdmean thmean sigrate[evts/h] bgrate[evts/h] corr-factor bg-corr-factor"
   #echo "# start("$time") stop("$time") excess-rate(evts/h) error-excess-rate(evts/h) "
   echo " # QUERY: "$query
   getdbsetup
   printprocesslog "DEBUG sendquery QUERY: "$query
   echo "DEBUG sendquery QUERY: "$query
   mysql $sqlpwfile -s -u $us $pw --host=$ho $db -e " $query "
   if [ "$table" == "AnalysisResultsRunLP" ]
   then 
      query2=`echo $query | sed -e 's/AnalysisResultsRunLP/AnalysisResultsRunISDC/g'`
      echo "# QUERY: "$query2
      mysql $sqlpwfile -s -u $us $pw --host=$ho factdata_cp20150811 -e " $query2 "
      fi
}

ontime1=" TIME_TO_SEC(TIMEDIFF(fRunStop,fRunStart))*fEffectiveOn "
ontime2=" fOnTimeAfterCuts "
ontimeif=" IF(ISNULL(fEffectiveOn), fOnTimeAfterCuts, TIME_TO_SEC(TIMEDIFF(fRunStop,fRunStart))*fEffectiveOn) "
from=" FROM RunInfo LEFT JOIN "$table" USING (fNight, fRunID) "
# time range and source
where=" WHERE fSourceKey="$source" AND fNight BETWEEN "$start" AND "$stop
# some sanity checks
where=$where" AND fRunTypeKey=1 AND NOT ISNULL(fNumSigEvts) AND NOT ISNULL(fNumBgEvts) AND NOT fRunStart='0000-00-00 00:00:00' AND NOT fRunStop='0000-00-00 00:00:00' "
where=$where" AND fZenithDistanceMax<"$zdcut" AND fThresholdMedian<"$thcut" AND fMoonZenithDistance>"$dark
where=$where" AND (fTNGDust < "$dust" OR isnull(fTNGDust)) "

# DataCheck (old)
##data with old feedback and/or different bias voltage
#query=$query" AND fNight>20120420 AND NOT fNight IN (20120406,20120410,20120503) AND"
## broken bias channel
#query=$query" NOT fNight BETWEEN 20121206 AND 20130110"
# bg-rate cut
zdparam=" pow(0.753833*cos(Radians(fZenithDistanceMean)), 7.647435)*exp(-5.753686*pow(Radians(fZenithDistanceMean),2.089609))"
thparam=" pow((if(isnull(fThresholdMinSet),fThresholdMedian,fThresholdMinSet)-329.4203),2)*(-0.0000002044803) "
param=" (fNumEvtsAfterBgCuts/5-fNumSigEvts)/fOnTimeAfterCuts - "$zdparam" - "$thparam" "
dchold=" -0.085 < ("$param") "
dchold=$dchold" AND 0.25 > ("$param") "
# Datacheck (new) -> combine
dchval=" fNumEvtsAfterBgCuts/(1.41*POW(fZenithDistanceMean*PI()/180,2)+0.975)/(-7.53e-12*POW(10, LOG10(fThresholdMinSet)*3.69)+1.035)/TIME_TO_SEC(TIMEDIFF(fRunStop,fRunStart))/fEffectiveOn "
# some semi-automatic datacheck
datacheck="  AND (("$dchval" BETWEEN 0.8 AND 1.7 AND fNight BETWEEN 20140520 AND 20150131) " #A
datacheck=$datacheck" OR  ("$dchval" BETWEEN 0.4 AND 1.6 AND fNight BETWEEN 20150201 AND 20150715) " #B
datacheck=$datacheck" OR  ("$dchval" BETWEEN 0.7 AND 1.4 AND fNight BETWEEN 20150716 AND 20160218) " #C
datacheck=$datacheck" OR  ("$dchval" BETWEEN 0.5 AND 1.0 AND fNight > 20160220) " #D
datacheck=$datacheck" OR  ("$dchold" AND fNight<20140520)) " #old


if [ $dch -eq 0 ]
then 
   datacheck=""
fi

case $time in
   mjd)  timeselect="Mjd(Min(fRunStart)) AS start, Mjd(MAX(fRunStop)) AS stop, "
         timeselect2=" Mjd(MIN(o.start)) AS start, Mjd(MAX(o.stop)) AS stop, "
         ;;
   unix) timeselect="Unix_timestamp(CONVERT_TZ(Min(fRunStart), '+00:00', 'SYSTEM')) AS start, Unix_timestamp(CONVERT_TZ(Max(fRunStop), '+00:00', 'SYSTEM')) AS stop, "
         timeselect2=" Unix_timestamp(CONVERT_TZ(MIN(o.start), '+00:00', 'SYSTEM')) AS start, Unix_timestamp(CONVERT_TZ(MAX(o.stop), '+00:00', 'SYSTEM')) AS stop, "
         ;;
   *)    timeselect="MIN(fRunStart) AS start, MAX(fRunStop) AS stop, "
         timeselect2=" MIN(o.start) AS start, MAX(o.stop) as stop, "
         ;;
esac

# todo: 
# implement login
# implement for internal use
#   table
#   more binnings (insert box for minutes? period, 10d?, weekly?)
#   zd limit
#   threshold limit
#   datacheck

if [ $bin -eq -1 ]
then 
   #query="SELECT Min(fRunStart), Max(fRunStop), Sum(fNumExcEvts)/Sum(fOnTimeAfterCuts)*3600, if ((Sum(fNumSigEvts) + 0.2*0.2*Sum(fNumBgEvts))<0, 0, Sqrt(Sum(fNumSigEvts) + 0.2*0.2*Sum(fNumBgEvts))) FROM RunInfo LEFT JOIN "$table" USING (fNight, fRunID) WHERE fSourceKey="$source" AND fNight BETWEEN "$start" AND "$stop" GROUP BY fNight HAVING SUM(fOnTimeAfterCuts)*60>20"
   query="SELECT "$timeselect" SUM(fNumExcEvts)/SUM("$ontimeif")*3600 AS excrate, "
   query=$query" ExcErr(Sum(fNumSigEvts), SUM(fNumBgEvts))/SUM("$ontimeif")*3600 AS excerr, "
   query=$query" SUM("$ontimeif"/60.) AS ontime, "
   query=$query" IF (SUM(fNumSigEvts)>10 AND SUM(fNumBgEvts)>10, LiMa(SUM(fNumSigEvts), SUM(fNumBgEvts)), IF (SUM(fNumSigEvts)-SUM(fNumBgEvts)>10, concat('(', LiMa(SUM(fNumSigEvts), SUM(fNumBgEvts)), ')'), '-')) AS significance, "
   query=$query" LiMa(SUM(fNumSigEvts), SUM(fNumBgEvts)) AS significance2, "
   query=$query" Avg(fZenithDistanceMean) as zd, "
   query=$query" Avg(fThresholdMedian) as th, "
   query=$query" SUM(fNumSigEvts)/SUM("$ontimeif")*3600 AS sigrate, "
   query=$query" SUM(fNumBgEvts)/SUM("$ontimeif")*3600 AS bgrate, "
   #query=$query" ((1.41*POW(fZenithDistanceMean*PI()/180,2)+0.975)/(-7.53e-12*POW(10, LOG10(fThresholdMinSet)*3.69)+1.035) ) AS factor "
   query=$query" (SUM(fNumExcEvts)/SUM(fNumExcEvts/((1.41*POW(fZenithDistanceMean*PI()/180,2)+0.975)/(-7.53e-12*POW(10, LOG10(fThresholdMinSet)*3.69)+1.035) ))) AS factor, "
   query=$query" SUM(fNumEvtsAfterBgCuts/(1.41*POW(fZenithDistanceMean*PI()/180,2)+0.975)/(-7.53e-12*POW(10, LOG10(fThresholdMinSet)*3.69)+1.035) )/SUM(TIME_TO_SEC(TIMEDIFF(fRunStop,fRunStart))*fEffectiveOn) AS bgfactor "
   query=$query$from$where$datacheck" GROUP BY fNight HAVING SUM("$ontimeif")/60>20 ORDER BY fNight "
else
   # query to get excess rate and significance from DB
   query="SELECT "$timeselect2" ROUND((SUM(o.sigevts)-SUM(o.bgevts))/SUM(o.ot)*3600, 2) AS excrate, "
   query=$query" ROUND(ExcErr(SUM(o.sigevts),SUM(o.bgevts))/SUM(o.ot)*3600,2) AS err "
   #query=$query" ROUND(SUM(o.ot)/60.,1) AS 'ontime [min]' "
   #query=$query" IF (SUM(o.sigevts)>10 AND SUM(o.bgevts)>10, LiMa(SUM(o.sigevts), SUM(o.bgevts)), IF (SUM(o.sigevts)-SUM(o.bgevts)>10, CONCAT('(', LiMa(SUM(o.sigevts), SUM(o.bgevts)), ')'), '-')) AS significance  "
   query=$query" FROM (SELECT fNight, @ot:="$ontimeif" AS ot, fRunStart AS start, fRunStop AS stop, fNumSigEvts AS sigevts, fNumBgEvts AS bgevts, "
   query=$query" IF (@night=fNight AND FLOOR((@os+@ot)/"$bin"./60.)<1, @bl, @bl := @bl + 1) AS block, "
   query=$query" IF (@night=fNight AND FLOOR((@os+@ot)/"$bin"./60.)<1, @os:=@os + @ot, @os := @ot) AS os, @night :=fNight AS night "
   query=$query$from" CROSS JOIN (SELECT @night :=0, @ot :=0, @os :=0, @bl:=0) PARAMS "
   query=$query$where$datacheck" ORDER BY fRunStart) o GROUP BY block ORDER BY start"
fi
#echo $query

printinfo | mail -s 'test internal downloading data ' -b dorner@astro.uni-wuerzburg.de -r dorner@astro.uni-wuerzburg.de $email
#printinfo

finish