#!/bin/bash

# setup
sqlpw=/home/$USER/.mysql.pw.op
dbname=factdata
#doupdate=92

query="SELECT Max(fPeriod) FROM RunInfo WHERE NOT ISNULL(fR750Cor)"
pmaxcor=`mysql --defaults-file=$sqlpw -s -e "$query" `
echo "fR750Cor is already filled up to P"$pmaxcor

query="SELECT Max(fPeriod) FROM RunInfo WHERE NOT ISNULL(fR750Ref)"
pmaxref=`mysql --defaults-file=$sqlpw -s -e "$query" `
echo "fR750Ref is already filled up to P"$pmaxref

if [ "$doupdate" == "" ]
then 
   numstart=$pmaxref
else
   numstart=$doupdate
fi



# setup for zd- and th-correction: 
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
echo ""
echo "Filling the corrected R750 to the DB..."
query="UPDATE RunInfo SET "
query=$query" fR750Cor=round(fNumThreshold750/("$min")/("$cor"),2) "
query=$query" WHERE fRunTypeKey=1 AND NOT (("$min")/("$cor"))=0 "
query=$query" AND fPeriod>="$numstart
echo $query
mysql --defaults-file=$sqlpw -s -e "$query" 

# 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.3 #82
       245.2 #83
       236.0 #84
       233.4 #85
       248.1 #86
       254.6 #87
       251.5 #88
       249.4 #89
       251.9 #90
       249.4 #91
       241.8 #92 
       244.3 #93
       240.9 #94
       240.1 #95
       240.6 #96
       228.7 #97
       )
if [ ${#rates[@]} -lt $pmaxref ]
then 
   ${#rates[@]}" "$pmaxref 
   "No new reference values available ("$pmaxref")"
   exit
fi 

# fill reference values
echo ""
echo "Filling the reference values for the corrected R750 for "${#rates[@]}" periods to the DB..."
echo "  Starting from period "$numstart 
for (( i=$numstart; i < ${#rates[@]} ; i++)) 
do
   echo "Filling P"$i
   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 -s -e "$query" 
done

if [ $numstart -gt 0 ]
then
   exit
fi

echo "Filling split periods..."
# several periods are split: 
#   update the values for those
# 71b
query="UPDATE RunInfo SET fR750Ref=250.0 WHERE fPeriod=71 AND fNight>20170726"
   echo "  "$query
mysql --defaults-file=$sqlpw -s -e "$query" 
# 69b
query="UPDATE RunInfo SET fR750Ref=253.0 WHERE fPeriod=69 AND fNight>20170524"
   echo "  "$query
mysql --defaults-file=$sqlpw -s -e "$query" 
# 60a
query="UPDATE RunInfo SET fR750Ref=239.3 WHERE fPeriod=60 AND fNight<20170901"
   echo "  "$query
mysql --defaults-file=$sqlpw -s -e "$query" 
# 46a
query="UPDATE RunInfo SET fR750Ref=271 WHERE fPeriod=46 AND fNight<20150716"
   echo "  "$query
mysql --defaults-file=$sqlpw -s -e "$query" 
# 24b
query="UPDATE RunInfo SET fR750Ref=286.2 WHERE fPeriod=24 AND fNight>20131003"
   echo "  "$query
mysql --defaults-file=$sqlpw -s -e "$query" 


echo ""
echo "Done"