Changeset 19473


Ignore:
Timestamp:
03/29/19 14:56:01 (6 years ago)
Author:
Daniela Dorner
Message:
fixed query of CU
File:
1 edited

Legend:

Unmodified
Added
Removed

  • trunk/DataCheck/QuickLook/FlareAlerts.sh

    r18906 r19473  
    55# finalize DB-entries for being called
    66# prepare directly template for email
     7#  maybe prepare website with all necessary links and information
    78# add x-ray trigger as trigger type or
    89#    add note for values > 70 evts/h for X-ray triggers
    … …  
    6869threshold="IF (ISNULL(fThresholdMinSet), fThresholdMedian, fThresholdMinSet)"
    6970#cu="20.0"
    70 cu="CUQLA(fNight)"
     71#cu="CUQLA(fNight)"
     72cu="fCU"
    7173corr="1"
    7274# missing: corrected excessrates
    … …  
    8082   query=$query"fNight AS night, MIN(fRunID) AS runmin, MAX(fRunID) AS runmax, "
    8183   query=$query"MIN(fRunStart) AS start, MAX(fRunStop) AS stop, "
    82    query=$query"ROUND(SUM("$ontime")/3600.,1) AS ontime, "
     84   query=$query"ROUND(SUM("$ontime")/3600.,2) AS ontime, "
    8385   query=$query"SUM(fNumSigEvts) AS sig, SUM(fNumBgEvts) AS bg, "
    84    query=$query"ROUND(SUM(fNumBgEvts)/SUM("$ontime")*3600,1) AS bgrate, "
     86   query=$query"ROUND(SUM(fNumBgEvts)/SUM("$ontime")*3600,2) AS bgrate, "
    8587   query=$query"SUM(fNumExcEvts) AS exc, "
    86    query=$query"ROUND(ExcErr(SUM(fNumSigEvts), SUM(fNumBgEvts)), 1) AS excerr, "
    87    query=$query"ROUND(SUM(fNumExcEvts)/SUM("$ontime")*3600,1) AS excrate, "
    88    query=$query"ROUND(ExcErr(SUM(fNumSigEvts), SUM(fNumBgEvts))/SUM("$ontime")*3600, 1) AS excrateerr, "
    89    query=$query"ROUND(SUM(fNumExcEvts*"$corr")/SUM("$ontime")*3600,1) as corexcrate, " # put here correction factor
    90    query=$query"ROUND(ExcErr(SUM(fNumSigEvts), SUM(fNumBgEvts))/SUM("$ontime")*3600*SUM(fNumExcEvts)/SUM(fNumExcEvts*"$corr"), 1) AS corexcrateerr, " # correctionfactor = exc / exc_cor, put here correction factor
    91    query=$query"ROUND(LiMa(SUM(fNumSigEvts), SUM(fNumBgEvts)),1) AS signif, "
    92    query=$query"ROUND(SUM(fNumExcEvts)/SUM("$ontime")*3600/"$cu",1) AS cu, " # make value time dependent
    93    query=$query"ROUND(ExcErr(SUM(fNumSigEvts), SUM(fNumBgEvts))/SUM("$ontime")*3600/"$cu", 1) AS cuerr, " # make value time dependent
    94    query=$query"ROUND(SUM(fNumExcEvts*"$corr")/SUM("$ontime")*3600/"$cu",1) as corcu, " # make value time dependent # put here correction factor
    95    query=$query"ROUND(ExcErr(SUM(fNumSigEvts), SUM(fNumBgEvts))/SUM("$ontime")*3600*SUM(fNumExcEvts)/SUM(fNumExcEvts*"$corr")/"$cu", 1) AS corcuerr, " # correctionfactor = exc / exc_cor # make value time dependent # put here correction factor
     88   query=$query"ROUND(ExcErr(SUM(fNumSigEvts), SUM(fNumBgEvts)), 2) AS excerr, "
     89   query=$query"ROUND(SUM(fNumExcEvts)/SUM("$ontime")*3600,2) AS excrate, "
     90   query=$query"ROUND(ExcErr(SUM(fNumSigEvts), SUM(fNumBgEvts))/SUM("$ontime")*3600, 2) AS excrateerr, "
     91   query=$query"ROUND(SUM(fNumExcEvts*"$corr")/SUM("$ontime")*3600,2) as corexcrate, " # put here correction factor
     92   query=$query"ROUND(ExcErr(SUM(fNumSigEvts), SUM(fNumBgEvts))/SUM("$ontime")*3600*SUM(fNumExcEvts)/SUM(fNumExcEvts*"$corr"), 2) AS corexcrateerr, " # correctionfactor = exc / exc_cor, put here correction factor
     93   query=$query"ROUND(LiMa(SUM(fNumSigEvts), SUM(fNumBgEvts)),2) AS signif, "
     94   query=$query"ROUND(SUM(fNumExcEvts)/SUM("$ontime")*3600/"$cu",2) AS cu, " # make value time dependent
     95   query=$query"ROUND(ExcErr(SUM(fNumSigEvts), SUM(fNumBgEvts))/SUM("$ontime")*3600/"$cu", 2) AS cuerr, " # make value time dependent
     96   query=$query"ROUND(SUM(fNumExcEvts*"$corr")/SUM("$ontime")*3600/"$cu",2) as corcu, " # make value time dependent # put here correction factor
     97   query=$query"ROUND(ExcErr(SUM(fNumSigEvts), SUM(fNumBgEvts))/SUM("$ontime")*3600*SUM(fNumExcEvts)/SUM(fNumExcEvts*"$corr")/"$cu", 2) AS corcuerr, " # correctionfactor = exc / exc_cor # make value time dependent # put here correction factor
    9698   query=$query"MIN(fZenithDistanceMin) as zdmin, MAX(fZenithDistanceMax) as zdmax, "
    9799   query=$query"MIN("$threshold") as thmin, MAX("$threshold") as thmax "
    … …  
    117119   query=$query"MIN(o.n) AS night, MIN(o.run) AS runmin, MAX(o.run) AS runmax, "
    118120   query=$query"MIN(o.start) AS start, MAX(o.stop) AS stop, "
    119    query=$query"ROUND(SUM(o.ot)/60.,1) AS ontime, "
     121   query=$query"ROUND(SUM(o.ot)/60.,2) AS ontime, "
    120122   query=$query"SUM(o.sig) AS sig, SUM(o.bg) AS bg, "
    121    query=$query"ROUND(SUM(o.bg)/SUM(o.ot)*3600,1) AS bgrate, "
     123   query=$query"ROUND(SUM(o.bg)/SUM(o.ot)*3600,2) AS bgrate, "
    122124   query=$query"SUM(o.exc) AS exc, "
    123    query=$query"ROUND(ExcErr(SUM(o.sig), SUM(o.bg)), 1) AS excerr, "
    124    query=$query"ROUND(SUM(o.exc)/SUM(o.ot)*3600,1) AS excrate, "
    125    query=$query"ROUND(ExcErr(SUM(o.sig), SUM(o.bg))/SUM(o.ot)*3600, 1) AS excrateerr, "
    126    query=$query"ROUND(SUM(o.exccor)/SUM(o.ot)*3600,1) as corexcrate, "
    127    query=$query"ROUND(ExcErr(SUM(o.sig), SUM(o.bg))/SUM(o.ot)*3600*SUM(o.exc)/SUM(o.exccor), 1) AS corexcrateerr, " # correctionfactor = exc / exc_cor
    128    query=$query"ROUND(LiMa(SUM(o.sig), SUM(o.bg)),1) AS signif, "
    129    query=$query"ROUND(SUM(o.exc)/SUM(o.ot)*3600/o.cu,1) AS cu, "
    130    query=$query"ROUND(ExcErr(SUM(o.sig), SUM(o.bg))/SUM(o.ot)*3600/o.cu, 1) AS cuerr, "
    131    query=$query"ROUND(SUM(o.exccor)/SUM(o.ot)*3600/o.cu,1) as corcu, "
    132    query=$query"ROUND(ExcErr(SUM(o.sig), SUM(o.bg))/SUM(o.ot)*3600*SUM(o.exc)/SUM(o.exccor)/o.cu, 1) AS corcuerr, " # correctionfactor = exc / exc_cor
     125   query=$query"ROUND(ExcErr(SUM(o.sig), SUM(o.bg)), 2) AS excerr, "
     126   query=$query"ROUND(SUM(o.exc)/SUM(o.ot)*3600,2) AS excrate, "
     127   query=$query"ROUND(ExcErr(SUM(o.sig), SUM(o.bg))/SUM(o.ot)*3600, 2) AS excrateerr, "
     128   query=$query"ROUND(SUM(o.exccor)/SUM(o.ot)*3600,2) as corexcrate, "
     129   query=$query"ROUND(ExcErr(SUM(o.sig), SUM(o.bg))/SUM(o.ot)*3600*SUM(o.exc)/SUM(o.exccor), 2) AS corexcrateerr, " # correctionfactor = exc / exc_cor
     130   query=$query"ROUND(LiMa(SUM(o.sig), SUM(o.bg)),2) AS signif, "
     131   query=$query"ROUND(SUM(o.exc)/SUM(o.ot)*3600/AVG(o.cu),2) AS cu, "
     132   query=$query"ROUND(ExcErr(SUM(o.sig), SUM(o.bg))/SUM(o.ot)*3600/Avg(o.cu), 2) AS cuerr, "
     133   query=$query"ROUND(SUM(o.exccor)/SUM(o.ot)*3600/Avg(o.cu),2) as corcu, "
     134   query=$query"ROUND(ExcErr(SUM(o.sig), SUM(o.bg))/SUM(o.ot)*3600*SUM(o.exc)/SUM(o.exccor)/Avg(o.cu), 2) AS corcuerr, " # correctionfactor = exc / exc_cor
    133135   query=$query"MIN(o.zdmin) as zdmin, MAX(o.zdmax) as zdmax, "
    134136   query=$query"MIN(o.th) as thmin, MAX(o.th) as thmax "
    … …  
    283285{
    284286   oldexc=0
     287   oldnightlyexc=0
    285288   exc=0
    286289   excold=0
    … …  
    320323
    321324      # fast rise/decay trigger
     325      #  this trigger type currently doesn't handle nightly binning
    322326      if [ $triggertype -eq 3 ]
    323327      then
    … …  
    409413            then
    410414               oldnightlyexc=`grep "corr. cu:      [0-9].[0-9] " ${changedfiles[@]} | grep -o -E ' [0-9].[0-9] ' | sort | tail -1`
     415               if [ "$oldnightlyexc" = "" ]
     416               then
     417                  oldnightlyexc=-1000
     418               fi
    411419               trigger2=`echo " $exc > $oldnightlyexc " | bc -l`
    412                echo "trigger2: "$trigger2" (exc: "$exc", oldexc: "$oldnightlyexc")"
     420               #echo "trigger2: "$trigger2" (exc: "$exc", oldexc: "$oldnightlyexc")"
    413421               echo "trigger2: "$trigger2" (exc: "$exc", oldexc: "$oldnightlyexc")" >> $logfile
    414422            fi
    … …  
    504512         if ! [ -e $donetriggerfile ]
    505513         then
    506             query="INSERT FlareAlerts.FlareTriggers SET fTriggerInserted=Now(), fNight="$night", fRunID="$runid", fTriggerType="$triggertype
     514            query="INSERT FlareAlerts.FlareTriggers SET fTriggerInserted=Now(), fNight="$night", fRunID="$runid", fTriggerType="$triggertype", fSourceKey="$sourcekey
    507515            if [ "$bin" = "" ]
    508516            then
    … …  
    532540               echo "sending["$triggertype"] "$triggerfile
    533541               echo "sending["$triggertype"] "$triggerfile >> $logfile
    534                cat $triggerfile | mail -s 'test flare alert ' -b $emailfrom -r $emailfrom $emailto
     542               cat $triggerfile | mail -s 'flare alert' -b $emailfrom -r $emailfrom $emailto
    535543               #cat $triggerfile | mail -s "test flare alert for $sourcename " $emailto
    536544               # that's also the cases for making a call
    … …  
    551559{
    552560   # query average flux from DB
    553    query="SELECT ROUND(SUM(fNumExcEvts)/SUM("$ontime")*3600,1) as excrate,  "
    554    query=$query"ROUND(ExcErr(SUM(fNumSigEvts), SUM(fNumBgEvts))/SUM("$ontime")*3600, 1) AS excerr, "
    555    query=$query"ROUND(SUM("$ontime")/3600.,1) AS ontime "
     561   query="SELECT ROUND(SUM(fNumExcEvts)/SUM("$ontime")*3600,2) as excrate,  "
     562   query=$query"ROUND(ExcErr(SUM(fNumSigEvts), SUM(fNumBgEvts))/SUM("$ontime")*3600, 2) AS excerr, "
     563   query=$query"ROUND(SUM("$ontime")/3600.,2) AS ontime "
    556564   query=$query"FROM AnalysisResultsRunLP "
    557565   query=$query"LEFT JOIN RunInfo USING (fNight, fRunID) "
    … …  
    645653   then
    646654      siglimit=3.0
    647       exclimit=2.0 # cu
     655      exclimit=1.5 # cu
    648656      printprocesslog "INFO checking for [Trigger to MAGIC 501 proposal]" >> $logfile
    649657      echo "[Trigger to MAGIC 501 proposal]" >> $logfile
    … …  
    655663      evaluate_result
    656664      # 20 min binning
    657       bin=20
     665      bin=30
    658666      echo " "$bin" min binning..." >> $logfile
    659667      get_query_minute_binning $bin
Note: See TracChangeset for help on using the changeset viewer.