#!/bin/bash

sqlpw=/home/$USER/.mysql.pw.op
dbname=factdata

# reference values for corrected R750
rates=(  0.0 # 0
         0.0 # 1
         0.0 # 2
         0.0 # 3
         0.0 # 4
         0.0 # 5
         0.0 # 6
       260.6 # 7
       265.2 # 8
       270.9 # 9
       291.0 #10
       253.1 #11
       254.5 #12
       239.3 #13
       248.7 #14
       245.5 #15
       265.4 #16
       250.1 #17
       257.4 #18
       253.6 #19
       276.4 #20
       268.5 #21
       261.9 #22
       255.7 #23
       268.7 #24a
       284.4 #25
       274.8 #26
       271.1 #27
       270.0 #28
         0.0 #29
       248.2 #30
       248.2 #31
       284.4 #32
       284.1 #33
       273.8 #34
       263.4 #35
       258.3 #36
       277.0 #37
       275.5 #38
       283.8 #39
       284.7 #40
       292.3 #41
       292.2 #42
       276.5 #43
       276.8 #44
       272.9 #45
       249.1 #46b
       249.5 #47
       250.4 #48
       255.1 #49
       266.7 #50
       266.0 #51
       268.0 #52
       258.9 #53
       246.2 #54
       243.9 #55
       240.6 #56
       244.8 #57
       246.8 #58
       239.3 #59
       247.3 #60b
       248.2 #61
       261.5 #62
       259.6 #63
       248.6 #64
       253.1 #65
       248.1 #66
       237.4 #67
       233.6 #68
       288.9 #69a
       253.0 #70
       232.9 #71a
       238.7 #72
       223.7 #73
       215.7 #74
       248.2 #75
       250.6 #76
       254.2 #77
       247.4 #78
       254.6 #79
       248.3 #80
       244.0 #81
       247.2 #82
       )

for (( i=0; i < ${#rates[@]} ; i++)) 
do
   if [ "${rates[$i]}" = "0.0" ]
   then 
      rate="NULL"
   else
      rate=${rates[$i]}
   fi
   query="UPDATE RunInfo SET fR750Ref="$rate" WHERE fPeriod="$i
   echo $query
   mysql --defaults-file=$sqlpw -u operator -h 10.0.100.21 $dbname -s -e "$query" 
done

# several periods are split: 
# 71b
query="UPDATE RunInfo SET fR750Ref=250.0 WHERE fPeriod=69 AND fNight>20170726"
mysql --defaults-file=$sqlpw -u operator -h 10.0.100.21 $dbname -s -e "$query" 
# 69b
query="UPDATE RunInfo SET fR750Ref=253.0 WHERE fPeriod=69 AND fNight>20170524"
mysql --defaults-file=$sqlpw -u operator -h 10.0.100.21 $dbname -s -e "$query" 
# 60a
query="UPDATE RunInfo SET fR750Ref=239.3 WHERE fPeriod=60 AND fNight<20170901"
mysql --defaults-file=$sqlpw -u operator -h 10.0.100.21 $dbname -s -e "$query" 
# 46a
query="UPDATE RunInfo SET fR750Ref=271 WHERE fPeriod=46 AND fNight<20150716"
mysql --defaults-file=$sqlpw -u operator -h 10.0.100.21 $dbname -s -e "$query" 
# 24b
query="UPDATE RunInfo SET fR750Ref=286.2 WHERE fPeriod=24 AND fNight>20131003"
mysql --defaults-file=$sqlpw -u operator -h 10.0.100.21 $dbname -s -e "$query" 

zd="cos(fZenithDistanceMean/180*Pi())"
min="TIME_TO_SEC(TIMEDIFF(fRunStop,fRunStart))*fEffectiveOn/60"
cor="pow("$zd", 1.94)*exp((1-1/"$zd")*0.331)*exp(2.39*(1-"$zd"))"

# fill corrected R750
query="UPDATE RunInfo SET "
query=$query" fR750Cor=round(fNumThreshold750/("$min")/("$cor"),2) "
query=$query" WHERE fRunTypeKey=1 AND NOT (("$min")/("$cor"))=0"
echo $query
mysql --defaults-file=$sqlpw -u operator -h 10.0.100.21 $dbname -s -e "$query"