Index: trunk/DataCheck/QuickLook/SendData.sh
===================================================================
--- trunk/DataCheck/QuickLook/SendData.sh	(revision 18672)
+++ trunk/DataCheck/QuickLook/SendData.sh	(revision 18672)
@@ -0,0 +1,212 @@
+#!/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
+
+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
+   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)] "
+   echo "# start("$time") stop("$time") excess-rate(evts/h) error-excess-rate(evts/h) "
+   getdbsetup
+   printprocesslog "DEBUG sendquery QUERY: "$query
+   mysql -s -u $us --password=$pw --host=$ho $db -e " $query "
+}
+
+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' "
+
+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, "
+         break;;
+   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, "
+         break;;
+   *)    timeselect="MIN(fRunStart) AS start, MAX(fRunStop) AS stop, "
+         timeselect2=" MIN(o.start) AS start, MAX(o.stop) as stop, "
+         break;;
+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" 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" Avg(fThresholdMedian) as th "
+   query=$query$from$where" 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)/20./60.)<1, @bl, @bl := @bl + 1) AS block, "
+   query=$query" IF (@night=fNight AND FLOOR((@os+@ot)/20./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" ORDER BY fRunStart) o GROUP BY block ORDER BY start"
+fi
+#echo $query
+
+#printinfo | mail -a "From: dorner@astro.uni-wuerzburg.de" -s 'test downloading data ' -b dorner@astro.uni-wuerzburg.de $email
+printinfo | mail -s 'FACT data (test download)' -c dorner@astro.uni-wuerzburg.de -r dorner@astro.uni-wuerzburg.de $email
+#printinfo
+
+finish
+
+# new query:
+# select  o.night, Min(o.start), Max(o.stop), Sum(o.sigevts), Sum(o.bgevts), Round(Sum(o.ot)/60.,1)  FROM (Select fNight, fRunID, fOnTimeAfterCuts as ot, fRunStart as start, fRunStop as stop, fNumSigEvts as sigevts, fNumBgEvts as bgevts, if (@night=fNight AND floor((@os+fOnTimeAfterCuts)/20./60.)<1, @bl, @bl := @bl + 1) as block, if (@night=fNight AND floor((@os+fOnTimeAfterCuts)/20./60.)<1, @os:=@os + fOnTimeAfterCuts, @os := fOnTimeAfterCuts) as os,  @os/20./60. as a, floor(@os/20./60.) as b, @night :=fNight as night from RunInfo left join AnalysisResultsRunLP using(fNight,fRunID) CROSS JOIN (SELECT @night :=0, @os :=0, @bl:=0) PARAMS where fNight between 20130414 and 20130415 and fRunTypeKey=1 and fSourceKey=1) o group by block;
+
+# input
+#  - binning
+#  - source
+#  - email address
+#  - night range 
+
+# binning
+bin=20
+
+# get date
+# add funtion
+night=20160102
+
+# get sources for current night from DB (RunInfo)
+sourcekey=1
+
+# get limits from DB
+siglimit=3.0
+exclimit=20
+
+# query to get excess rate and significance from DB
+query="SELECT o.* FROM"
+query=$query" (SELECT fRunID, IF (@os+fOnTimeAfterCuts/60.>"$bin", @sig := fNumSigEvts, @sig := @sig + fNumSigEvts) AS sigevts, "
+query=$query" IF (@os+fOnTimeAfterCuts/60.>"$bin", @bg := fNumBgEvts, @bg := @bg + fNumBgEvts) AS bgevts,  "
+query=$query" IF (@os+fOnTimeAfterCuts/60.>"$bin", @exc := fNumExcEvts, @exc := @exc + fNumExcEvts) AS excevts, "
+query=$query" IF (@os+fOnTimeAfterCuts/60.>"$bin", @os := fOnTimeAfterCuts/60., @os := @os + fOnTimeAfterCuts/60.) AS ontime, "
+query=$query" @exc/@os*60 AS excrate, "
+query=$query" IF (@sig>0 AND @bg*5>0 AND (@sig*Log(@sig/(@sig+@bg*5)*1.2/0.2)+@bg*5*Log(@bg*5/(@sig+@bg*5)*1.2))> 0, Sqrt((@sig*Log(@sig/(@sig+@bg*5)*1.2/0.2)+@bg*5*Log(@bg*5/(@sig+@bg*5)*1.2))*2), 0) AS sig "
+query=$query" FROM AnalysisResultsRunLP O LEFT JOIN RunInfo USING (fNight, fRunID) "
+query=$query" CROSS JOIN (SELECT @os :=0, @sig :=0, @bg := 0, @exc :=0, @rate :=0) PARAMS "
+query=$query" WHERE fNight="$night" AND fSourceKey="$sourcekey") o HAVING ontime > "$bin"*0.9"
+results=( `sendquery` )
+#echo ${results[@]}
+
+i=0
+while [ 0 -lt 1 ]
+do 
+   num=`echo "$i * 7" | bc -l`
+   if [ "${results[$num]}" = "" ]
+   then 
+      break
+   fi
+   runid=${results[$num]}
+   sig=${results[$num+6]}
+   exc=${results[$num+5]}
+   
+   trigger=`echo " $exc > $exclimit && $sig > $siglimit " | bc -l`
+   
+#   echo $i" "$num" "$exc" "$sig
+   if [ $trigger -eq 1 ]
+   then
+      # do whatever to be done to trigger
+      #  - send email/sms / call
+      #  - create amon file
+      #  - prepare email for alert
+      echo $runid" "$exc" "$sig" -> "$trigger
+      triggerfile="/home/fact/flare.alerts/"$night"_"$runid"_"$sourcekey".trigger"
+      touch $triggerfile
+   fi
+   # counter
+   i=`echo $i +1 | bc -l`
+done
+
+finish 
+
+
+# old query:
+#   #query="SELECT "$timeselect2", Round(o.excrate,2), Round(ExcErr(o.sigevts, o.bgevts),2), IF (o.sigevts>10 AND o.bgevts>10, LiMa(o.sigevts, o.bgevts), '-'), Round(o.ontime,1) as ontime FROM"
+#   query="SELECT "$timeselect2" AS time, ROUND((o.sigevts-o.bgevts)/o.ontime*60,2) AS excrate, ROUND(ExcErr(o.sigevts, o.bgevts)/o.ontime*60,2) AS excerr, ROUND(o.ontime,1) AS ontime, IF (o.sigevts>10 AND o.bgevts>10, LiMa(o.sigevts, o.bgevts), IF (o.sigevts-o.bgevts>10, CONCAT('(', LiMa(o.sigevts, o.bgevts), ')'), '-')) AS significance FROM"
+#   query=$query" (SELECT IF (@os+fOnTimeAfterCuts/60.>20, @start:=fRunStart, IF (@os=0, @start:=fRunStart, @start)) as start, fRunStop as stop, IF (@os+fOnTimeAfterCuts/60.>"$bin", @sig := fNumSigEvts, @sig := @sig + fNumSigEvts) AS sigevts, "
+#   #query=$query" (SELECT fRunID, IF (@os+fOnTimeAfterCuts/60.>"$bin", @sig := fNumSigEvts, @sig := @sig + fNumSigEvts) AS sigevts, "
+#   query=$query" IF (@os+fOnTimeAfterCuts/60.>"$bin", @bg := fNumBgEvts, @bg := @bg + fNumBgEvts) AS bgevts,  "
+#   query=$query" IF (@os+fOnTimeAfterCuts/60.>"$bin", @exc := fNumExcEvts, @exc := @exc + fNumExcEvts) AS excevts, "
+#   query=$query" IF (@os+fOnTimeAfterCuts/60.>"$bin", @os := fOnTimeAfterCuts/60., @os := @os + fOnTimeAfterCuts/60.) AS ontime "
+#   #query=$query" @exc/@os*60 AS excrate, "
+#   #query=$query" ExcErr(@sig, @bg) AS excerr, "
+#   #query=$query" IF (@sig>0 AND @bg*5>0 AND (@sig*Log(@sig/(@sig+@bg*5)*1.2/0.2)+@bg*5*Log(@bg*5/(@sig+@bg*5)*1.2))> 0, Sqrt((@sig*Log(@sig/(@sig+@bg*5)*1.2/0.2)+@bg*5*Log(@bg*5/(@sig+@bg*5)*1.2))*2), 0) AS sig "
+#   #query=$query" LiMa(@sig,@bg) AS signif "
+#   query=$query" FROM AnalysisResultsRunLP O LEFT JOIN RunInfo USING (fNight, fRunID) "
+#   query=$query" CROSS JOIN (SELECT @os :=0, @sig :=0, @bg := 0, @exc :=0, @rate :=0, @start :=0) PARAMS "
+#   query=$query" WHERE fNight between "$start" AND "$stop" AND fSourceKey="$source" ORDER BY fNight, fRunID) o HAVING ontime > "$bin"*0.75"
+#
+
Index: trunk/DataCheck/QuickLook/SendData_Internal.sh
===================================================================
--- trunk/DataCheck/QuickLook/SendData_Internal.sh	(revision 18672)
+++ trunk/DataCheck/QuickLook/SendData_Internal.sh	(revision 18672)
@@ -0,0 +1,185 @@
+#!/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 -s -u $us --password=$pw --host=$ho $db -e " $query "
+   if [ "$table" == "AnalysisResultsRunLP" ]
+   then 
+      query2=`echo $query | sed -e 's/AnalysisResultsRunLP/AnalysisResultsRunISDC/g'`
+      echo "# QUERY: "$query2
+      mysql -s -u $us --password=$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, "
+         break;;
+   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, "
+         break;;
+   *)    timeselect="MIN(fRunStart) AS start, MAX(fRunStop) AS stop, "
+         timeselect2=" MIN(o.start) AS start, MAX(o.stop) as stop, "
+         break;;
+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" 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 ' -c dorner@astro.uni-wuerzburg.de -r dorner@astro.uni-wuerzburg.de $email
+#printinfo
+
+finish
+