#!/bin/bash

# ---------------------------------------------------------------- #
#  README README README README README README README README README  #
# ---------------------------------------------------------------- #
#                                                                  #
# To use this script, you need                                     #
#   - a computer with access to the FACT database in La Palma      #
#   - a file with the password of a valid mysql-user               #
#   - to define the setup below for                                #
#     a) the DB access                                             #
#     b) the data you want to have                                 #
#                                                                  #
# To define the setup, search for SETUP in this script and         #
#   read the details there                                         #
#                                                                  #
# Per data request, you get up to 3 files:                         #
#   *_internal.dat                                                 #
#   *_collaborators.dat                                            #
#   *_external.dat (only if binning is 20min or nightly)           #
#                                                                  #
# Please have in mind that this started as a tool for myself, then #
# others started using it. Also the script is not yet finalized.   #
# In case you find problems and/or have a feature request, please  #
# send and email to dorner@astro.uni-wuerzburg.de                  #
#                                                                  #
# ---------------------------------------------------------------- #
#  README README README README README README README README README  #
# ---------------------------------------------------------------- #



# ToDo (notes DD): 
# ----------------
# - do conversion to fluxes using FACT Crab spectrum
# - add < 20121212 data for QLA
# - new version of zd/th-correction and CU from Aachen when ready
# nice-to-have
# - add E2dNdE
# - functionality to determine start time for seaon-binning
# - file as attachment to email for download.php

#
# content of files (wish list):
# -----------------------------
# REMARK: keep order of columns to allow for reading with TGraph directly from file: X Y EX EY
# 
# internal 
# --------
# time: time, delta time, start, stop, ontime
# flux: excrate, excerr, CU, CUerr, flux, fluxerr corrate, corerr, CUcor CUcorerr, fluxcor, fluxcorerr, 
# other info on flux: signif, cu-factor, num exc, num sig, num bg
# other info: zd th R750cor R750ref
# 
# external (allow only 20min and nightly binning)
# --------
# time: time, delta time, start, stop
# flux: excrate, excerr
#
# collaborators
# -------------
# time: time, delta time, start, stop, ontime
# flux: excrate, excerr, corrate, corerr, CUcor CUcorerr, fluxcor, fluxcorrerr, significance
# 
# additional information to put:
# ------------------------------
# timestamp of creation
# query (for debugging / answering questions) 
# policy (adapted for internal/collaborators/external) [define in files to be used also by Send_Data*.sh
# 


function print_policy()
{
   echo "#" 
   echo "# Data Usage Policy: "
   if [ "$expert" == "no" ]
   then 
      echo "#   Using data from the FACT Quick Look Analysis, you agree to cite the FACT design"
      echo "#   paper (H. Anderhub et al. JINST 8 P6008) and the quick look analysis website "
      echo "#   (https://fact-project.org/monitoring)."
      echo "#" 
      echo "# References: "
      echo "#   FACT design paper: http://adsabs.harvard.edu/abs/2013JInst...8P6008A"
      echo "#                      http://iopscience.iop.org/1748-0221/8/06/P06008 "
      echo "#   FACT Performance Paper: http://adsabs.harvard.edu/abs/2014JInst...9P0012B"
      echo "#                           http://iopscience.iop.org/1748-0221/9/10/P10012"
      echo "#   FACT quick look analysis: https://fact-project.org/monitoring"
      echo "#                             http://adsabs.harvard.edu/abs/2015arXiv150202582D"
      echo "# If you intend to use data or information from this website, please let us know for reference."
   else
      echo "#   As a member, associated member or collaborator of the FACT Collaboration, you have access to internal information."
      echo "#   Any publication using FACT internal information has to have the full FACT author list."
   fi
   echo "#" 
}

print_selection()
{
   echo "#" 
   echo "# Your Selection: " 
   sourcename=`mysql --defaults-file=$sqlpw -s -e "SELECT fSourceName FROM Source WHERE fSourceKey="$source`
   echo "#   Source: "$sourcename 
   echo "#   Time range: "$nightmin"-"$nightmax 
   echo "#   Time format: "$timeunit 
   if [ $bin -lt 0 ]
   then 
      unit="night(s)"
   else
      if [ $bin -eq 0 ]
      then 
         unit="periods"
      else
         unit="minutes"
      fi
   fi
   binning=`echo $bin | sed -e 's/-//'`" "$unit
   echo "#   Binning: "$binning
   if [ "$expert" != "no" ]
   then
      echo "# Additional Internal Selection: "
      echo "#   "`echo $table | sed -e 's/AnalysisResultsRun//'`"-Analysis was used."
      if [ "$zdmax" != "" ]
      then
         echo "#   Maximum Zenith Distance: "$zdmax" degree"
      fi
      if [ "$thmax" != "" ]
      then
         echo "#   Maximum Trigger Threshold: "$thmax" DAC counts"
      fi
      if [ "$light" != "" ]
      then
         echo "#   Light Condition Cut: "$lightcut
      fi
      if [ "$dust" != "" ]
      then
         echo "#   Calima Cut: dust < "$dust" ug/cm3"
      fi
      if [ "$factorcut" != "" ]
      then
         echo "#   Factor Cut: zdfactor*thfactor > "$factorcut
      fi
      if [ "$usedch" == "yes" ]
      then
         echo "#   Data quality selection was applied. "
         if [ "$dch" == "" ]
         then 
            echo "#      Standard data check based on cosmic-ray rate: "$dchstd
         else
            echo "#      You selected a custom datacheck: "$dch
         fi
      fi
   fi
   echo "#" 
}

function get_results()
{
   # some query parts
   
   # these values use the Crab spectrum from HESS 2006 
   # hess 2006 http://www.aanda.org/articles/aa/pdf/2006/39/aa5351-06.pdf
   # PL.SetParameter(1,-2.63);
   # PL.SetParameter(0, 3.45e-11);
   case "$table" in 
   "AnalysisResultsRunISDC")
      zdfactor="pow(cos(fZenithDistanceMean*PI()/180)*exp(1-cos(fZenithDistanceMean*PI()/180)),4.2)" 
      thfactor="(1.18-IF(ISNULL(fThresholdMinSet),fThresholdMedian,fThresholdMinSet)*0.00062)" 
      # ETh: 1070 GeV 
      crabflux="1.89"
      ;;
   "AnalysisResultsRunCutsLC")
      zdfactor="pow(cos(fZenithDistanceMean*PI()/180)*exp(1-cos(fZenithDistanceMean*PI()/180)),4.5)" 
      thfactor="(1.37-IF(ISNULL(fThresholdMinSet),fThresholdMedian,fThresholdMinSet)*0.00118)" 
      # ETh: 785 GeV
      crabflux="3.14"
      ;;
   "AnalysisResultsRunLP")
      zdfactor="(pow(cos(fZenithDistanceMean*PI()/180),3)+14.8/21.9*pow(sin(2*fZenithDistanceMean*PI()/180),5))" 
      #zdfactor="(1/(pow(cos(fZenithDistanceMean*PI()/180),3)+14.8/21.9*pow(sin(2*fZenithDistanceMean*PI()/180),5)))" 
      thfactor="(1-0.00124/1.21*(fThresholdMinSet-500)*(fThresholdMinSet>=500))"
      # ETh: 1100 GeV 
      crabflux="1.81"
      ;;
   "*")
      zdfactor="0"
      thfactor="0"
      crabflux="0"
      ;;
   esac

   # conversion from crab units to fluxes (if a different value is given in setup
   if [ "$crabfluxconv" != "" ]
   then
      crabflux=$crabfluxconv #e-11
   fi
   #echo "crabflux: "$crabflux
   fluxprec=2
   
   # some names and definitions needed several times below
   # ontime 
   ontime1=" TIME_TO_SEC(TIMEDIFF(fRunStop,fRunStart))*fEffectiveOn "
   ontime2=" fOnTimeAfterCuts "
   ontimeif=" IF(ISNULL(fEffectiveOn), "$ontime2", "$ontime1") "
   # zd and threshold
   zenith="fZenithDistance"
   thresh="IF(ISNULL(fThresholdMinSet),fThresholdMedian,fThresholdMinSet)"
   # correction for zd- and th-dependence
   correvts=" fNumExcEvts/"$zdfactor"/"$thfactor
   # conversion to CU - stored in each result-table (determined by DD Feb 2019)
   cufactor="fCU" 
   # some calculations
   #excerr="ExcErr(SUM(fNumSigEvts), SUM(fNumBgEvts))"
   excerr="ExcErr(fNumSigEvts, fNumBgEvts)"
   CU="SUM(fNumExcEvts/"$cufactor")/SUM("$ontimeif")*3600"
   #CUerr=$excerr"/SUM("$ontimeif")*3600*SUM(fNumExcEvts/"$cufactor")/SUM(fNumExcEvts)"
   CUerr="SQRT(SUM(POW("$excerr"/"$cufactor",2)))/SUM("$ontimeif")*3600"
   CUcor="SUM("$correvts"/"$cufactor")/SUM("$ontimeif")*3600"
   #CUcorerr=$excerr"/SUM("$ontimeif")*3600*SUM("$correvts"/"$cufactor")/SUM(fNumExcEvts)"
   CUcorerr="SQRT(SUM(POW("$excerr"/"$cufactor"/"$zdfactor"/"$thfactor",2)))/SUM("$ontimeif")*3600"
   #excerr2="ExcErr(SUM(o.sigevts),SUM(o.bgevts))"
   excerr2="ExcErr(o.sigevts,o.bgevts)"
   CU2="SUM((o.sigevts-o.bgevts)/o.cufactor)/SUM(o.ot)*3600"
   #CUerr2=$excerr2"/SUM(o.ot)*3600*SUM((o.sigevts-o.bgevts)/o.cufactor)/(SUM(o.sigevts)-SUM(o.bgevts))"
   CUerr2="SQRT(SUM(POW("$excerr2"/o.cufactor,2)))/SUM(o.ot)*3600"
   #CUcor2="SUM(o.corevts/o.cufactor)/SUM(o.ot)*3600"
   CUcor2="SUM(o.excevts/o.zdfactor/o.thfactor/o.cufactor)/SUM(o.ot)*3600"
   #CUcorerr2=$excerr2"/SUM(o.ot)*3600*SUM(o.corevts/o.cufactor)/(SUM(o.sigevts)-SUM(o.bgevts))"
   CUcorerr2="SQRT(SUM(POW("$excerr2"/o.cufactor/o.zdfactor/o.thfactor,2)))/SUM(o.ot)*3600"
   
   #"ROUND(SQRT(SUM(POW(ExcErr(fNumSigEvts, fNumBgEvts)/pow(cos(fZenithDistanceMean*PI()/180)*exp(1-cos(fZenithDistanceMean*PI()/180)),4.5)/(1.37-IF(ISNULL(fThresholdMinSet),fThresholdMedian,fThresholdMinSet)*0.00118), 2)))/SUM( IF(ISNULL(fEffectiveOn),  fOnTimeAfterCuts ,  TIME_TO_SEC(TIMEDIFF(fRunStop,fRunStart))*fEffectiveOn ) )*3600, 1)"
   
   # columns to be selected
   # for night-binning
   ontime=" ROUND(SUM("$ontimeif")/60., 1) AS ontime"
   excrate=" ROUND(SUM(fNumExcEvts)/SUM("$ontimeif")*3600, 1) AS excrate"
   significance="ROUND(LiMa(Sum(fNumSigEvts), SUM(fNumBgEvts)), 1) AS significance"
   numexc="SUM(fNumExcEvts) AS numexc"
   numsig="SUM(fNumSigEvts) AS numsig"
   numbg="SUM(fNumBgEvts) AS numbg"
   #excrateerr=" ROUND("$excerr"/SUM("$ontimeif")*3600, 1) AS excrateerr"
   excrateerr=" ROUND(ExcErr(SUM(fNumSigEvts), SUM(fNumBgEvts))/SUM("$ontimeif")*3600, 1) AS excrateerr"
   correxcrate=" ROUND(SUM("$correvts")/SUM("$ontimeif")*3600, 1) AS correxcrate"
   #correxcrateerr=" ROUND("$excerr"/SUM("$ontimeif")*3600*SUM("$correvts")/SUM(fNumExcEvts), 1) AS correxcrateerr"
   correxcrateerr=" ROUND(SQRT(SUM(POW("$excerr"/"$zdfactor"/"$thfactor", 2)))/SUM("$ontimeif")*3600, 1) AS correxcrateerr"
   cu=" ROUND("$CU", 2) AS cu"
   cuerr=" ROUND("$CUerr", 2) AS cuerr"
   cucor=" ROUND("$CUcor", 2) AS cucor"
   cucorerr=" ROUND("$CUcorerr", 2) AS 'cucorerr'"
   flux="ROUND("$CU" * "$crabflux", 2) AS flux"
   fluxerr="ROUND("$CUerr" * "$crabflux", 2) AS fluxerr"
   fluxcor="ROUND("$CUcor" * "$crabflux", 2) AS fluxcor"
   fluxcorerr="ROUND("$CUcorerr" * "$crabflux", 2) AS fluxcorerr"
   # for minute binning
   ontime2=" ROUND(SUM(o.ot)/60., 1) AS ontime"
   #excrate2=" ROUND((SUM(o.sigevts)-SUM(o.bgevts))/SUM(o.ot)*3600, 1) AS excrate"
   excrate2=" ROUND((SUM(o.excevts))/SUM(o.ot)*3600, 1) AS excrate"
   significance2=" ROUND(LiMa(SUM(o.sigevts),SUM(o.bgevts)), 1) AS significance"
   #numexc2="Sum(o.sigevts-o.bgevts) AS numexc"
   numexc2="SUM(o.excevts) AS numexc"
   numsig2="SUM(o.sigevts) AS numsig"
   numbg2="SUM(o.bgevts) AS numbg"
   excrateerr2=" ROUND(ExcErr(SUM(o.sigevts),SUM(o.bgevts))/SUM(o.ot)*3600, 1) AS excrateerr"
   correxcrate2=" ROUND(SUM(o.excevts/o.zdfactor/o.thfactor)/SUM(o.ot)*3600, 1) AS correxcrate"
   #correxcrateerr2=" ROUND("$excerr2"/SUM(o.ot)*3600*SUM(o.corevts)/(SUM(o.sigevts)-SUM(o.bgevts)), 1) AS correxcrateerr"
   correxcrateerr2=" ROUND(SQRT(SUM(POW("$excerr2"/o.zdfactor/o.thfactor,2)))/SUM(o.ot)*3600,1) AS correxcrateerr"
   cu2=" ROUND("$CU2", 2) AS cu"
   cuerr2=" ROUND("$CUerr2", 2) AS cuerr"
   cucor2=" ROUND("$CUcor2", 2) AS cucor"
   cucorerr2=" ROUND("$CUcorerr2", 2) AS 'cucorerr'"
   flux2="ROUND("$CU2" * "$crabflux", "$fluxprec") AS flux"
   fluxerr2="ROUND("$CUerr2" *"$crabflux", "$fluxprec") AS fluxerr"
   fluxcor2="ROUND("$CUcor2" * "$crabflux", "$fluxprec") AS fluxcor"
   fluxcorerr2="ROUND("$CUcorerr2" *"$crabflux", "$fluxprec") AS fluxcorerr"

   case $timeunit in
      mjd)  delta="(Mjd(MAX(fRunStop))-Mjd(MIN(fRunStart)))/2"
            start=" Mjd(MIN(fRunStart)) AS start"
            stop=" Mjd(MAX(fRunStop)) AS stop"
            deltat=$delta" AS deltat"
            time=" Mjd(MIN(fRunStart))+"$delta" AS time"
            delta2="(Mjd(MAX(o.stop))-Mjd(MIN(o.start)))/2"
            start2=" Mjd(MIN(o.start)) AS start"
            stop2=" Mjd(MAX(o.stop)) AS stop"
            deltat2=$delta2" AS deltat"
            time2=" Mjd(MIN(o.start))+"$delta2" AS time"
            ;;
      unix) delta="(Unix_timestamp(CONVERT_TZ(MAX(fRunStop), '+00:00', 'SYSTEM')) - Unix_timestamp(CONVERT_TZ(MIN(fRunStart), '+00:00', 'SYSTEM')))/2"
            start="Unix_timestamp(CONVERT_TZ(MIN(fRunStart), '+00:00', 'SYSTEM')) AS start"
            stop="Unix_timestamp(CONVERT_TZ(MAX(fRunStop), '+00:00', 'SYSTEM')) AS stop"
            deltat=$delta" AS deltat"
            time=" Unix_timestamp(CONVERT_TZ(MIN(fRunStart), '+00:00', 'SYSTEM'))+"$delta" AS time"
            delta2="(Unix_timestamp(CONVERT_TZ(MAX(o.stop), '+00:00', 'SYSTEM')) - Unix_timestamp(CONVERT_TZ(MIN(o.start), '+00:00', 'SYSTEM')))/2"
            start2=" Unix_timestamp(CONVERT_TZ(MIN(o.start), '+00:00', 'SYSTEM')) AS start"
            stop2=" Unix_timestamp(CONVERT_TZ(MAX(o.stop), '+00:00', 'SYSTEM')) AS stop"
            deltat2=$delta2" AS deltat"
            time2=" Unix_timestamp(CONVERT_TZ(MIN(o.start), '+00:00', 'SYSTEM'))+"$delta2" AS time"
            ;;
      *)    delta="sec_to_time(time_to_sec(timediff(MAX(fRunStop), MIN(fRunStart)))/2)"
            start=" MIN(fRunStart) AS start"
            stop=" MAX(fRunStop) AS stop"
            deltat=$delta" AS deltat"
            time=" addtime(MIN(fRunStart), "$delta") AS time"
            delta2="sec_to_time(time_to_sec(timediff(MAX(o.stop), MIN(o.start)))/2)"
            start2=" MIN(o.start) AS start"
            stop2=" MAX(o.stop) AS stop"
            deltat2=$delta2" AS deltat"
            time2=" addtime(MIN(o.start), "$delta2") AS time"
            ;;
   esac
   
   # from and join of query 
   from=" FROM RunInfo LEFT JOIN "$table" USING (fNight, fRunID) "
   
   # data check based on artificial trigger rate
   # details see https://www.fact-project.org/logbook/showthread.php?tid=5790
   #dch=" AND fR750Cor/fR750Ref >0.93 "
   dchstd=" AND fR750Cor/fR750Ref BETWEEN 0.93 AND 1.3 "
   
   # put together where-clause of query
   # time range and source
   where=" WHERE fSourceKey="$source" AND fNight BETWEEN "$nightmin" AND "$nightmax
   where=$where" AND NOT ISNULL(fNumExcEvts) "
   # some sanity checks
   where=$where" AND fRunTypeKey=1 "
   # remove empty rows
   if [ "$rmemptyrows" == "yes" ]
   then
      where=$where" AND NOT ISNULL(fZenithDistanceMean) AND (NOT ISNULL(fThresholdMinSet) OR NOT ISNULL(fThresholdMedian)) AND NOT ISNULL(fCU) " 
   fi
   # zd cut
   if [ "$zdmax" != "" ]
   then 
      where=$where" AND fZenithDistanceMax < "$zdmax
   fi
   # th cut
   if [ "$thmax" != "" ]
   then 
      where=$where" AND "$thresh" < "$thmax
   fi
   # dust cut
   if [ "$dust" != "" ] 
   then
      where=$where" AND fTNGDust<"$dust
   fi
   # light condition cut
   if [ "$light" == "nomoon" ]
   then
      lightcut=" fZenithDistanceMoon>90"
   fi
   if [ "$light" == "dark" ]
   then
      lightcut=" fMoonZenithDistance>90 AND fSunZenithDistance>108 "
   fi
   if [ "$light" != "" ]
   then 
      where=$where" AND "$lightcut
   fi
#   if [ "$factorcut" != "" ]
#   then
#      where=$where" AND ( ("$zdfactor") * ("$thfactor")) > "$factorcut" "
#   fi
   querybase=$from$where

   if [ "$usedch" == "yes" ]
   then
      if [ "$dch" == "" ]
      then 
         querydch=$dchstd
      else
         echo "you are using for datacheck: "$dch
         querydch=$dch
      fi
   else
      querydch=
   fi
   
   
   if [ $bin -le 0 ]
   then 
      # first part of the query
      querystart="SELECT "
      querystart=$querystart" "$time", "$start", "$stop", "
      # final part of the query
      if [ $bin -eq 0 ]
      then 
         orderby=" ORDER BY fPeriod "
         #querystart=$querystart" fPeriod AS num, "
         queryend=" GROUP BY fPeriod "
      else
         num=" FLOOR((Mjd(fRunStart)-Mjd("$nightmin")-0.5)/"`echo $bin | sed -e 's/-//'`".) "
         orderby=" ORDER BY "$num
         #querystart=$querystart" FLOOR((Mjd(fRunStart)-Mjd("$nightmin")-0.5)/"`echo $bin | sed -e 's/-//'`".) AS num, "
         queryend=" GROUP BY "$num
      fi
      #queryend=" GROUP BY num "
      if [ "$ontimelimit" = "" ]
      then 
         queryend=$queryend" HAVING SUM("$ontimeif")>1200 "
      else
         queryend=$queryend" HAVING SUM("$ontimeif")>"$ontimelimit
      fi
#      if [ "$ontimelimit" = "" ]
#      then 
#         queryend=$queryend" HAVING abs(cucorerr) < 1 AND SUM("$ontimeif")>1200 ORDER BY "$orderby
#      else
#         queryend=$queryend" HAVING abs(cucorerr) < 1 AND SUM("$ontimeif")>"$ontimelimit" ORDER BY "$orderby
#      fi
      
      # internal
      queryint=$querystart
      queryint=$queryint" "$excrate", "$cu", "$flux", "$correxcrate", "$cucor", "$fluxcor", "
      queryint=$queryint" "$deltat", "$ontime", "
      queryint=$queryint" "$excrateerr", "$cuerr", "$fluxerr", "$correxcrateerr", "$cucorerr", "$fluxcorerr", "
      queryint=$queryint" "$significance", "
      queryint=$queryint" MIN(fNight) AS nightmin, MAX(fNight) AS nightmax, "
      queryint=$queryint" "$numexc", "$numsig", "$numbg", "
      queryint=$queryint" MIN("$zenith"Min) AS zdmin, MAX("$zenith"Max) AS zdmax, "
      queryint=$queryint" MIN("$thresh") AS thmin, MAX("$thresh") AS thmax, "
      queryint=$queryint" ROUND(AVG("$cufactor"), 1) AS cufactor, ROUND(AVG("$zdfactor"), 1) AS zdfactor, ROUND(AVG("$thfactor"), 1) AS thfactor, "
      queryint=$queryint" ROUND(AVG(fR750Cor), 2) AS R750cor,  ROUND(AVG(fR750Ref), 2) AS R750ref "
      if [ "$factorcut" == "" ]
      then
         queryint=$queryint" "$querybase" "$querydch" "$queryend" "$orderby
      else
#         queryint=$queryint" "$querybase" "$querydch" "$queryend" "$orderby
         queryint=$queryint" "$querybase" "$querydch" "$queryend" AND ( zdfactor * thfactor > "$factorcut") "$orderby
      fi
      
      # for collaborators
      querycol=$querystart
      querycol=$querycol" "$excrate", "$correxcrate", "$cucor", "$fluxcor", "
      querycol=$querycol" "$deltat", "$ontime", "
      querycol=$querycol" "$excrateerr", "$correxcrateerr", "$cucorerr", "$fluxcorerr", "
      querycol=$querycol" "$significance
      if [ "$factorcut" == "" ]
      then
         querycol=$querycol" "$querybase" "$querydch" "$queryend" "$orderby
      else
#         querycol=$querycol" "$querybase" "$querydch" "$queryend" "$orderby
         querycol=$querycol" "$querybase" "$querydch" "$queryend" AND (AVG("$zdfactor") * AVG("$thfactor") > "$factorcut") "$orderby
      fi
      
      # external
      # no datacheck applied for external files
      # no cut on factors 
      queryext=$querystart" "$excrate", "$deltat", "$excrateerr" "$querybase" "$queryend" "$orderby
      
   else
      # first part of the query
      querystart="SELECT "
      querystart=$querystart" "$time2", "$start2", "$stop2", "
      
      # final part of the query
      querybase=" FROM (SELECT fNight, fZenithDistanceMin AS zdmin, fZenithDistanceMax AS zdmax, "$thresh" AS th, "
      querybase=$querybase" fR750Cor AS R750cor, fR750Ref AS R750ref, "$cufactor" AS cufactor, "
      querybase=$querybase" "$zdfactor" AS zdfactor, "$thfactor" AS thfactor, "
      querybase=$querybase" @ot:="$ontimeif" AS ot, fRunStart AS start, fRunStop AS stop, "
      #querybase=$querybase" fNumSigEvts AS sigevts, fNumBgEvts AS bgevts, "$correvts" AS corevts, "
      querybase=$querybase" fNumSigEvts AS sigevts, fNumBgEvts AS bgevts, fNumExcEvts AS excevts, "
      querybase=$querybase" IF (@night=fNight AND FLOOR((@os+@ot)/"$bin"./60.)<1, @bl, @bl := @bl + 1) AS block, "
      querybase=$querybase" IF (@night=fNight AND FLOOR((@os+@ot)/"$bin"./60.)<1, @os:=@os + @ot, @os := @ot) AS os, @night :=fNight AS night "
      querybase=$querybase$from" CROSS JOIN (SELECT @night :=0, @ot :=0, @os :=0, @bl:=0) PARAMS "
      querybase=$querybase$where" ORDER BY fRunStart) o GROUP BY block HAVING ontime>0.75*"$bin" ORDER BY 'time'"
      
      # internal
      queryint=$querystart
      queryint=$queryint" "$excrate2", "$cu2", "$flux2", "$correxcrate2", "$cucor2", "$fluxcor2", "
      queryint=$queryint" "$deltat2", "$ontime2", "
      queryint=$queryint" "$excrateerr2", "$cuerr2", "$fluxerr2", "$correxcrateerr2", "$cucorerr2", "$fluxcorerr2", "
      queryint=$queryint" "$significance2", "
      #queryint=$queryint" avg(o.night) AS night, "
      queryint=$queryint" min(o.night) AS nightmin, max(o.night) AS nightmax, "
      queryint=$queryint" "$numexc2", "$numsig2", "$numbg2", "
      queryint=$queryint" MIN(o.zdmin) AS zdmin, MAX(o.zdmax) AS zdmax, MIN(o.th) AS thmin, MAX(o.th) AS thmax, "
      queryint=$queryint" ROUND(AVG(o.cufactor), 1) AS cufactor, ROUND(AVG(o.R750cor), 2) AS R750cor,  ROUND(AVG(o.R750ref), 2) AS R750ref "
      queryint=$queryint" "$querybase
      
      # for collaborators
      querycol=$querystart
      querycol=$querycol" "$excrate2", "$correxcrate2", "$cu2", "$fluxcor2", "
      querycol=$querycol" "$deltat2", "$ontime2", "
      querycol=$querycol" "$excrateerr2", "$correxcrateerr2", "$cuerr2", "$fluxcorerr2", "
      querycol=$querycol" "$significance2
      querycol=$querycol" "$querybase
      
      # external
      queryext=$querystart" "$excrate2", "$deltat2", "$ontime2", "$excrateerr2" "$querybase
      
   fi
   
   # write file for externals only for allowed binnings
   if [ $bin -eq 20 ] || [ $bin -eq -1 ]
   then 
      fileext=$datapath"/FACT_preliminary_"$name"_external.dat"
      if [ "$overwrite" = "yes" ]
      then 
         if [ "$mode" != "auto" ]
         then
            echo "creating "$fileext" ..."
         fi
         echo "# This file was created at "`date` > $fileext
         print_policy >> $fileext
      fi
      print_selection >> $fileext
      headerext="# time["$timeunit"] start["$timeunit"] stop["$timeunit"] excess-rate[evts/h] (stop-start)/2["$timeunit"] excess-rate_error[evts/h] "
      echo $headerext >> $fileext
      #echo "$queryext"
      mysql --defaults-file=$sqlpw -s -e "$queryext" >> $fileext
      #mysql --defaults-file=$sqlpw -e "$queryext"
   fi
   if [ "$mode" == "auto" ] && [ "$expert" == "no" ]
   then
      return
   fi
   
   fileint=$datapath"/FACT_preliminary_"$name"_internal.dat"
   if [ "$overwrite" = "yes" ]
   then 
      if [ "$mode" != "auto" ]
      then
         echo "creating "$fileint" ..."
      fi
      echo "# This file was created at "`date` > $fileint
      print_policy >> $fileint
   fi
   print_selection >> $fileint
   echo "# The following query was used: " >> $fileint
   echo "# $queryint" >> $fileint
   echo "#" >> $fileint
   headerint="# time["$timeunit"] start["$timeunit"] stop["$timeunit"] excess-rate[evts/h] flux[CU] flux[e-11ph/cm2/s] corrected_excess-rate[evts/h] corrected_flux[CU] corrected_flux[e-11ph/cm2/s] (stop-start)/2["$timeunit"] ontime[min]"
   headerint=$headerint" excess-rate_error[evts/h] flux_error[CU] flux_error[e-11ph/cm2/s] corrected_excess-rate_error[evts/h] corrected_flux_error[CU] corrected_flux_error[e-11/cm2/s] significance nightmin nightmax num_exc num_sig num_bg "
   headerint=$headerint" zdmin zdmax thmin thmax avg(cufactor) avg(zdfactor) avg(thfactor) avg(R750cor) avg(R750ref) "
   echo $headerint >> $fileint
   #echo "$queryint"
   mysql --defaults-file=$sqlpw -s -e "$queryint" >> $fileint
   #mysql --defaults-file=$sqlpw -e "$queryint" 
   if [ "$mode" == "auto" ]
   then
      return
   fi
   
   filecol=$datapath"/FACT_preliminary_"$name"_collaborators.dat"
   if [ "$overwrite" = "yes" ]
   then 
      echo "creating "$filecol" ..."
      echo "# This file was created at "`date` > $filecol
      print_policy >> $filecol
   fi
   print_selection >> $filecol
   #echo "# The following query was used: " >> $filecol
   #echo "# $querycol" >> $filecol
   #echo "#" >> $filecol
   headercol="# time["$timeunit"] start["$timeunit"] stop["$timeunit"] excess-rate[evts/h] corrected_excess-rate[evts/h] corrected_flux[CU] corrected_flux[e-11ph/cm2/s] (stop-start)/2["$timeunit"] ontime[min]"
   headercol=$headercol" excess-rate_error[evts/h] corrected_excess-rate_error[evts/h] corrected_flux_error[CU] corrected_flux_error[e-11ph/cm2/s] significance "
   echo $headercol >> $filecol
   #echo "$querycol"
   mysql --defaults-file=$sqlpw -s -e "$querycol" >> $filecol
   #mysql --defaults-file=$sqlpw -e "$querycol
}

# evaluation of command line options (for usage with download.php)

if [ ${#@} -eq 15 ]
then
   #get_data.sh $start $stop $source $timebin $email $table $time $expert $dch $zd $th $light $dust $rmemptyrows $factorcut
   mode="auto"
   overwrite="yes"
   # setup for usage with download.php
   datapath="/home/factwww/dch/data"
   #datapath="./data"
   sqlpw=/home/fact/.mysql.pw 
   #sqlpw=/home/fact/.mysql.pw2
   #host=10.0.100.21 
   #dbname=factdata 
   nightmin=$1
   nightmax=$2
   source=$3
   bin=$4
   if [ "$bin" == "00" ]
   then
      bin=0
   fi
   email=$5
   table=$6
   timeunit=$7
   expert=$8
   usedch=$9 # novalue gives same result as no 
   if [ "${10}" != "novalue" ] && [ "${10}" != "all" ]
   then 
      zdmax=${10}
   fi
   if [ "${11}" != "novalue" ] && [ "${11}" != "all" ]
   then 
      thmax=${11}
   fi
   if [ "${12}" != "novalue" ] && [ "${12}" != "all" ]
   then 
      light=${12}
   fi
   if [ "${13}" != "novalue" ] && [ "${13}" != "all" ]
   then 
      dust=${13}
   fi
   rmemptyrows=${14}
   if [ "${15}" != "novalue" ] && [ "${15}" != "all" ]
   then 
      factorcut=${15}
   fi
   name=`echo $email | sed -e 's/@/-at-/'`
   get_results
   
   # sending email
   if [ "$expert" == "yes" ]
   then 
      cat $fileint | mail -s 'FACT internal data download' -b qla@fact-project.org -r qla@fact-project.org $email
   else
      cat $fileext | mail -s 'FACT data download' -b qla@fact-project.org -r qla@fact-project.org $email
   fi
      
   exit
fi



# -------------------------------------------------------------------------------------- #
#  SETUP SETUP SETUP SETUP SETUP SETUP SETUP SETUP SETUP SETUP SETUP SETUP SETUP SETUP   #
# -------------------------------------------------------------------------------------- #
#                                                                                        #
# The lines below define the basic setup for the database and give examples and          #
# explanations for the various options available.                                        #
# The request of the data itself is done with a smaller setup further down.              # 
#                                                                                        #
# -------------------------------------------------------------------------------------- #
#
# ----------
#  DB SETUP
# ----------
# path to file with mysql setup (user, password, host, db-name)
sqlpw=/home/$USER/.mysql.pw.local
sqlpw=/home/$USER/.mysql.pw.ethz.fact
#
# -------------
#  BASIC SETUP
# -------------
# output path
path=`dirname $0`
datapath=$path"/data"
# create directory for data files 
if ! [ -e $datapath ]
then
   mkdir $datapath
fi
# time unit 
#timeunit=timestamp # default
#timeunit=unix
timeunit=mjd
# time binning 
#  positive values: minutes
#  negative values: days
#  special case 0: period
#  for season binning choose -365 and according start date
#bin=20 # minutes
#bin=0 # period
bin=-1 # nightly
#bin=-365 # yearly
# choose analysis 
#table="AnalysisResultsAllQLA" # N/A
#table="AnalysisResultsRunLP" # QLA 
#table="AnalysisResultsRunISDC"  # ISDC
table="AnalysisResultsRunCutsLC" # CutsLC
# time range
nightmin=20111115
nightmax=20201231
# overwrite dataset file? 
# (useful to combine different binnings in one file -> set to "no")
overwrite="yes"
# optional: require minimal ontime per bin (default 20 min)
#ontimelimit=30 # 30 min
ontimelimit= # default 20 min
# data quality selection
# if you explicitely don't want a datacheck, you can comment the following line
usedch="yes" 
# use your own datacheck instead
#   use a line like the following defining your own data quality selection cut
#dch=" AND fR750Cor/fR750Ref BETWEEN 0.93 AND 1.3 "
# apply additional predefined cuts
# light conditions
#light="nomoon" # only data with no moon (but twilight allowed)
#light="dark" # only dark night data
# TNG dust - cut away data with calima
#dust=1
#dust=10
# use different conversion from CU to fluxes
#crabfluxconv="2.5"
# remove lines which contain NULL (e.g. zd-corrected flux when zd=NULL)
rmemptyrows="yes"
# remove lines with zdfactor * thfactor > value
factorcut=0.3


# -------------------------------------------------------------------------------------- #
#  SETUP - GET YOUR DATA HERE - SETUP - GET YOUR DATA HERE - SETUP - GET YOUR DATA HERE  #
# -------------------------------------------------------------------------------------- #
#                                                                                        #
# Adapt the lines below to your needs.                                                   #
# Overwrite default settings above.                                                      #
# The  data-request is sent with the line 'get_results'.                                 #
# Minumum setup: Define source key and name for file.                                    #
# The list of source keys can be found at                                                #
#   https://fact-project.org/run_db/db/printtable.php?fTable=Source&fSortBy=fSourceKEY+  #
# More examples can be found further down.                                               #
#                                                                                        #
# -------------------------------------------------------------------------------------- #


datapath=/home/dorner/FACT.analysis/corrected.lcs.for.collaborators/lcs

# put your data request here, examples below

# LCs for 421 campaign with Astrosat Jan 2019
table="AnalysisResultsRunLP" 
# Mrk 421
source=1
# nightly 
bin=-1
nightmin=20190109
nightmax=20190113
name="QLA_Mrk421_nightly_Astrosat-Jan-2019"
get_results
# 20min
bin=20
name="QLA_Mrk421_20min_Astrosat-Jan-2019"
get_results
# 30min
bin=30
name="QLA_Mrk421_30min_Astrosat-Jan-2019"
get_results
# 40min
bin=40
name="QLA_Mrk421_40min_Astrosat-Jan-2019"
get_results
# 60min
bin=60
name="QLA_Mrk421_60min_Astrosat-Jan-2019"
get_results
# 80min
bin=80
name="QLA_Mrk421_80min_Astrosat-Jan-2019"
get_results
# 90min
bin=90
name="QLA_Mrk421_90min_Astrosat-Jan-2019"
get_results




exit

# LCs for SED project 2013 
table="AnalysisResultsRunCutsLC" # CutsLC
# Mrk 421
source=1
# nightly 
bin=-1
nightmin=20120901
nightmax=20130516
name="Mrk421_nightly_SED-project-2013"
get_results



# LCs for XMM proposal
table="AnalysisResultsRunCutsLC" # CutsLC
# Mrk 421
source=1
# nightly 
bin=-1
nightmin=20111115
nightmax=20201231
name="Mrk421_nightly_XMM-proposal-2019"
get_results
# Mrk 501
source=2
name="Mrk501_nightly_XMM-proposal-2019"
get_results
# 1959
source=7
name="1959_nightly_XMM-proposal-2019"
get_results



# LCs for 421 campaign with Astrosat Jan 2019
table="AnalysisResultsRunCutsLC" # CutsLC
# Mrk 421
source=1
# nightly 
bin=-1
nightmin=20190109
nightmax=20190113
name="Mrk421_nightly_Astrosat-Jan-2019"
get_results
# 20min
bin=20
name="Mrk421_20min_Astrosat-Jan-2019"
get_results
# 30min
bin=30
name="Mrk421_30min_Astrosat-Jan-2019"
get_results
# 40min
bin=40
name="Mrk421_40min_Astrosat-Jan-2019"
get_results
# 60min
bin=60
name="Mrk421_60min_Astrosat-Jan-2019"
get_results
# 80min
bin=80
name="Mrk421_80min_Astrosat-Jan-2019"
get_results
# 90min
bin=90
name="Mrk421_90min_Astrosat-Jan-2019"
get_results



# LCs for flaring pattern project 
table="AnalysisResultsRunCutsLC" # CutsLC
# Mrk 421
source=1
# nightly 
bin=-1
nightmin=20111115
nightmax=20201231
name="Mrk421_nightly_Flaring-pattern-project"
get_results
# 2d
bin=-2
nightmin=20111115
nightmax=20201231
name="Mrk421_2day_Flaring-pattern-project"
get_results



# LCs for ToO-Trigger project 
table="AnalysisResultsRunCutsLC" # CutsLC
# Mrk 421
source=1
# nightly for 2019
bin=-1
nightmin=20190101
nightmax=20190630
name="Mrk421_nightly_20190101-20190630_ToO-project-2019"
get_results
# 20 min around flare
bin=20
nightmin=20190609
nightmax=20190612
name="Mrk421_20min_20190609-20190612_ToO-project-2019"
get_results

exit

table="AnalysisResultsRunLP" # QLA
# Mrk 421
source=1
# nightly for 2019
bin=-1
nightmin=20190101
nightmax=20190630
name="QLA_Mrk421_nightly_20190101-20190630_ToO-project-2019"
get_results
# 20 min around flare
bin=20
nightmin=20190609
nightmax=20190612
name="QLA_Mrk421_20min_20190609-20190612_ToO-project-2019"
get_results


table="AnalysisResultsRunISDC" # ISDC
# Mrk 421
source=1
# nightly for 2019
bin=-1
nightmin=20190101
nightmax=20190630
name="ISDC_Mrk421_nightly_20190101-20190630_ToO-project-2019"
get_results
# 20 min around flare
bin=20
nightmin=20190609
nightmax=20190612
name="ISDC_Mrk421_20min_20190609-20190612_ToO-project-2019"
get_results


exit

# LC for ICRC
table="AnalysisResultsRunCutsLC" # CutsLC
bin=-1
# Mrk 421
source=1
name="Mrk421_all_nightly"
get_results
# Mrk 501
source=2
name="Mrk501_all_nightly"
get_results
# 1959
source=7
name="1959_all_nightly"
get_results

# 2344
source=3
name="2344_all_nightly"
get_results

# Crab
source=5
name="Crab_all_nightly"
get_results


exit

# Mrk 501
source=2
zdmax=90
thmax=1500
# new analysis
table="AnalysisResultsRunCutsLC" # CutsLC
nightmin=20140520
nightmax=20140930
name="Mrk501_nightly_newAnalysis_forHESS"
bin=-1
get_results
name="Mrk501_7d_newAnalysis_forHESS"
bin=-7
get_results
nightmin=20140623
nightmax=20140623
name="Mrk501_5min_FlareNight_newAnalysis_forHESS"
bin=5
get_results
nightmin=20140622
nightmax=20140624
name="Mrk501_5min_Flare_newAnalysis_forHESS"
bin=5
get_results

exit

# LC for INTEGRAL Proposal
table="AnalysisResultsRunCutsLC" # CutsLC
bin=-1
# Mrk 421
source=1
name="Mrk421_all_nightly"
get_results
# Mrk 501
source=2
name="Mrk501_all_nightly"
get_results
# 1959
source=7
name="1959_all_nightly"
get_results
# for other INTEGRAL proposal
# Mrk 421
bin=0
source=1
name="Mrk421_all_period"
get_results

exit


# Crab
source=5
nightmin=20121214
nightmax=20180418
table="AnalysisResultsRunCutsLC" # CutsLC
bin=-1
zdmax=30
thmax=330
name="testCrabnightlycheck"
get_results

exit

# Crab
source=5
nightmin=20121214
nightmax=20180418
table="AnalysisResultsRunCutsLC" # CutsLC
bin=20
name="testCrab20min"
get_results

exit

# Crab
source=5
nightmin=20121214
nightmax=20180418
table="AnalysisResultsRunCutsLC" # CutsLC
bin=-1
name="testCrabnightly"
get_results

exit

# Crab
source=5
#nightmin=20121214
#nightmax=20180418
table="AnalysisResultsRunCutsLC" # CutsLC
bin=-1
name="testCraball"
get_results

exit


# Crab
source=5
nightmin=20121214
nightmax=20180418
table="AnalysisResultsRunCutsLC" # CutsLC
bin=60
name="testCrab"
get_results

exit


# for 2344 paper with MAGIC

source=3
zdmax=90
thmax=1500

# new analysis
table="AnalysisResultsRunCutsLC" # CutsLC
nightmin=20160618
nightmax=20160815
name="2344_2016flare_newAnalysis"
bin=-7
get_results
overwrite="no"
bin=-28
nightmin=20160816
nightmax=20161031
get_results
overwrite="yes"

# ISDC analysis
table="AnalysisResultsRunISDC"
nightmin=20160618
nightmax=20160815
name="2344_2016flare_stdAnalysis"
bin=-7
get_results
overwrite="no"
bin=-28
nightmin=20160816
nightmax=20161031
get_results
overwrite="yes"


usedch="no" 
# new analysis
table="AnalysisResultsRunCutsLC" # CutsLC
nightmin=20160618
nightmax=20160815
name="2344_2016flare_newAnalysis_noDCh"
bin=-7
get_results
overwrite="no"
bin=-28
nightmin=20160816
nightmax=20161031
get_results
overwrite="yes"

# ISDC analysis
table="AnalysisResultsRunISDC"
nightmin=20160618
nightmax=20160815
name="2344_2016flare_stdAnalysis_noDCh"
bin=-7
get_results
overwrite="no"
bin=-28
nightmin=20160816
nightmax=20161031
get_results
overwrite="yes"

usedch="yes" 


exit

# Mrk 501
source=2
zdmax=90
thmax=1500
# new analysis
table="AnalysisResultsRunCutsLC" # CutsLC
nightmin=20140520
nightmax=20140930
name="Mrk501_nightly_newAnalysis_forHESS"
bin=-1
get_results
name="Mrk501_7d_newAnalysis_forHESS"
bin=-7
get_results
nightmin=20140623
nightmax=20140623
name="Mrk501_5min_FlareNight_newAnalysis_forHESS"
bin=5
get_results
# isdc analysis
thmax=850
table="AnalysisResultsRunISDC"
nightmin=20140520
nightmax=20140930
name="Mrk501_nightly_stdAnalysis_forHESS"
bin=-1
get_results
name="Mrk501_7d_stdAnalysis_forHESS"
bin=-7
get_results
nightmin=20140623
nightmax=20140623
name="Mrk501_5min_FlareNight_stdAnalysis_forHESS"
bin=5
get_results
# qla
thmax=500
table="AnalysisResultsRunLP"
nightmin=20140520
nightmax=20140930
name="Mrk501_nightly_QLA_forHESS"
bin=-1
get_results

exit

# 501 MAGIC 
source=2
name="Mrk501_2014_forMAGIC"
bin=-1
crabfluxconv="2.87" # using threshold of 830GeV
crabfluxconv="3.01" # using threshold of 830GeV and MAGIC Crab spectrum
nightmin=20140714
nightmax=20140805
get_results

name="Mrk501_2014_forMAGIC_20min"
bin=20
get_results


exit

bin=30
name="Mrk501_2014_forMAGIC30"
get_results

bin=0
name="P"
nightmin=20140501
nightmax=20140930
get_results

bin=20
nightmin=20140623
nightmax=20140623
name="Mrk501_test"
get_results


# end script here
exit



#
# more examples
#

# Mrk 421
source=1
name="Mrk421_nightly"
bin=-1
get_results
name="Mrk421_20min"
bin=20
get_results
name="Mrk421_3d"
bin=-3
get_results
name="Mrk421_10d"
bin=-10
get_results
name="Mrk421_period"
bin=0
get_results



# Mrk 501
source=2
name="Mrk501_nightly"
bin=-1
get_results
name="Mrk501_20min"
bin=20
get_results
name="Mrk501_3d"
bin=-3
get_results
name="Mrk501_10d"
bin=-10
get_results
name="Mrk501_period"
bin=0
get_results



# 2344
source=3
name="2344_nightly"
bin=-1
get_results
name="2344_20min"
bin=20
get_results
name="2344_period"
bin=0
get_results



# 1959
source=7
name="1959_nightly"
bin=-1
get_results
name="1959_20min"
bin=20
get_results
name="1959_period"
bin=0
get_results



# 0323
source=12
name="0323_nightly"
bin=-1
get_results
name="0323_20min"
bin=20
get_results
name="0323_period"
bin=0
get_results



# crab
source=5
name="Crab_nightly"
bin=-1
get_results
name="Crab_20min"
bin=20
get_results
name="Crab_period"
bin=0
get_results
name="Crab_season"
bin=-365
nightmin=20110716
nightmax=20180716
get_results



name="1959_2016"
source=7
bin=-1
nightmin=20160201
nightmax=20161105
get_results

name="1959_all_variable"
overwrite="no"
source=7
bin=-365
nightmin=20120201
nightmax=20130131
get_results
nightmin=20130201
nightmax=20140131
get_results
nightmin=20140201
nightmax=20150131
get_results
bin=0
nightmin=20150201
nightmax=20160131
get_results
bin=-1
nightmin=20160201
nightmax=20170131
get_results
bin=0
nightmin=20170201
nightmax=20180131
get_results



overwrite="yes"
name="1959_all_variable2"
overwrite="no"
source=7
bin=-365
nightmin=20120201
nightmax=20130131
get_results
nightmin=20130201
nightmax=20140131
get_results
nightmin=20140201
nightmax=20150131
get_results
bin=0
nightmin=20150201
nightmax=20160131
get_results
bin=-1
nightmin=20160201
nightmax=20160817
get_results
bin=0	
nightmin=20160818
nightmax=20180131
get_results



overwrite="yes"
bin=0
source=3
name="2344period"
get_results



# flare night (HESS)
name="Mrk501_10min_flarenight"
source=2
bin=10
nightmin=20140623
nightmax=20140623
get_results



# flare night (HESS)
name="Mrk501_5min_flarenight"
source=2
bin=5
nightmin=20140623
nightmax=20140623
get_results




# full sample 
name="Mrk421_all_nightly"
source=1
get_results

name="Mrk501_all_nightly"
source=2
get_results

name="1959_all_nightly"
source=7
get_results

name="2344_all_nightly"
source=3
get_results



name="HESE20160427"
source=19
nightmin=20160425
bin=-10
get_results

name="AMON20160731"
source=21
nightmin=20160730
bin=-10
get_results