#!/bin/bash
# IP address of AVR-NET-IO board and port number
AVRNETIO_IP="192.168.178.5"
AVRNETIO_PORT=2701
TEMP_ID="283e5dde03000069"
umdr=1008
#variablen datenbank
table_name="energie_nslu"
db_user="root"
db_passwd="test"
db_name="test"
host="192.168.178.28"
while true
  do
  d=`echo "date" | nc -v -n -u -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
minute=`echo | awk '{print substr("'"${d}"'",20,1)}'`
echo "minute=$minute"
if [ $minute == 5 ] || [ $minute == 0 ]  
then
datum_u=date
 `echo 1w convert $TEMP_ID | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1  | grep -v OK || exit 1`
temp_buero=`echo "1w get $TEMP_ID" | nc -v -u -v -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1`






#variablen strom mit test c6 skript
stromz=`echo "c6 get stromz" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
zaehlertag=`echo "c6 get zaehler_tag" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
zaehlertagl=`echo "c6 get zaehler_tagl" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
zaehler_h=`echo "c6 get zaehler_h" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
zaehler_h_log=`echo "c6 get zaehler_h_log" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
zaehler_5=`echo "c6 get zaehler_5" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
zaehler_15_l=`echo "c6 get zaehler_15_l" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
#variablen strom mit test c6 skript ende

#variablen strom zahl
zaehlertag_zahl=`echo | awk '{print substr("'"${zaehlertag}"'",13,6)}'` 
zaehlertag_zahl_l=`echo | awk '{print substr("'"${zaehlertagl}"'",13,6)}'` 
strom_z=`echo | awk '{print substr("'"${stromz}"'",7,5)}'`
strom_z_h=`echo | awk '{print substr("'"${zaehler_h}"'",11,3)}'`
strom_z_h_log_zahl=`echo | awk '{print substr("'"${zaehler_h_log}"'",15,3)}'`
strom_z_5=`echo | awk '{print substr("'"${zaehler_5}"'",11,3)}'`
strom_z_15_l=`echo | awk '{print substr("'"${zaehler_15_l}"'",14,3)}'`
#variablen gaszähler
gasz=`echo "c6 get gasz" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
gas_zaehlertag=`echo "c6 get gas_zaehler_tag" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
gas_zaehlertagl=`echo "c6 get gas_zaehler_tagl" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
gas_zaehler_h=`echo "c6 get gas_zaehler_h" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
gas_zaehler_h_log=`echo "c6 get gas_zaehler_h_log" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
gas_zaehler_5=`echo "c6 get gas_zaehler_5" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
gas_zaehler_15_l=`echo "c6 get gas_zaehler_15_l" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 

#variablen gaszähler zahl
gas_zaehlertag_zahl=`echo | awk '{print substr("'"${gas_zaehlertag}"'",17,6)}'` 
gas_zaehlertag_zahl_l=`echo | awk '{print substr("'"${gas_zaehlertagl}"'",18,6)}'` 
gas_z=`echo | awk '{print substr("'"${gasz}"'",5,5)}'`
gas_z_h=`echo | awk '{print substr("'"${gas_zaehler_h}"'",15,3)}'`
gas_z_h_log_zahl=`echo | awk '{print substr("'"${gas_zaehler_h_log}"'",19,3)}'`
gas_z_5=`echo | awk '{print substr("'"${gas_zaehler_5}"'",15,3)}'`
gas_z_15_l=`echo | awk '{print substr("'"${gas_zaehler_15_l}"'",18,3)}'`
#variablen gaszähler zahl ende
#variablen zeit
wochentag=`echo | awk '{print substr("'"${d}"'",0,3)}'`
datum=`echo | awk '{print substr("'"${d}"'",5,11)}'`
jahr=`echo | awk '{print substr("'"${d}"'",11,4)}'`
monat=`echo | awk '{print substr("'"${d}"'",8,2)}'`
tag_datum=`echo | awk '{print substr("'"${d}"'",5,2)}'`
minute=`echo | awk '{print substr("'"${d}"'",2,1)}'`
minute_2=`echo | awk '{print substr("'"${d}"'",19,2)}'`
DATUM=`date +%Y'-'%m'-'%d' '%k':'%M`
datum_skript=`date +%Y'-'%m'-'%d' '%k':'%M':'%S`
DATUM_avr=`echo "date +%Y'-'%m'-'%d' '%k':'%M':'%S" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
stunde=`echo | awk '{print substr("'"${d}"'",16,2)}'`



#variablen watt cbm

watt5min=`echo "scale=5; $strom_z_5 / $umdr" | bc`
kWhwatt5min=`echo "scale=5; $strom_z_5 / $umdr/1000" | bc`
Whwatt15min=`echo "scale=5; $strom_z_15_l / $umdr/1000" | bc`

wattstunde=`echo "scale=7; $strom_z_h_log_zahl / $umdr" | bc`
kwatth_tag=`echo "scale=7; $zaehlertag_zahl_l / $umdr/1000" | bc`
watt5min=`echo "scale=5; $strom_z_5 / $umdr" | bc`

#cbm gaszähler
liter=10
liter5min=`echo "scale=5; $gas_z_5 * $liter" | bc`
liter15min=`echo "scale=5; $gas_z_15_l * $liter" | bc`
literstunde=`echo "scale=5; $gas_z_h_log_zahl * $liter/1000" | bc`
cbm_tag=`echo "scale=7; $gas_zaehlertag_zahl_l  * $liter/1000" | bc`
cbm5min=`echo "scale=5; $gas_z_5 * $liter/1000" | bc`

#stromzähler berechnung


#gaszählerberechnung
#gaszaehler=`echo 'SELECT zaehlerstand_gas FROM energie_nslu ORDER BY datum_zeit DESC LIMIT 1;'| 
#mysql --skip-column-names -t -u$db_user  $db_name |awk '{print $2}' `
#gaszaehler_neu=`echo "scale=5; $gaszaehler + $cbm5min" | bc`
#gaszaehler_neu=0

#temp_buero=`echo "1w get $TEMP_ID" | socat -t 4 stdio tcp4:$AVRNETIO_IP:2701`
d=`echo "date" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
minute_2=`echo | awk '{print substr("'"${d}"'",19,2)}'`

stunde=`echo | awk '{print substr("'"${d}"'",16,2)}'`
jahr=`echo | awk '{print substr("'"${d}"'",11,4)}'`
monat=`echo | awk '{print substr("'"${d}"'",8,2)}'`
tag_datum=`echo | awk '{print substr("'"${d}"'",5,2)}'`
datum_stunde_minute_avr="$jahr-$monat-$tag_datum $stunde:$minute_2:00"
DATUM=`date +%Y'-'%m'-'%d' '%k':'%M`
stunde=`echo | awk '{print substr("'"${d}"'",16,2)}'`
echo "minute2=$minute_2"
echo "stunde=$stunde"
stunde_01=`echo | awk '{print substr("'"${d}"'",17,1)}'`

n=$(($minute_2%15))
m=$(($minute_2%5))
echo "m=$m"
echo "n=$n"
#***************************************************************5min
if [ $m == 0 ] && [ $minute_2 != 00 ] && [ $n != 0 ]
then
echo " 5min"
minute_2=`echo | awk '{print substr("'"${d}"'",19,2)}'`
 datum_stunde_minute_avr="$jahr-$monat-$tag_datum $stunde:$minute_2:00"
#net_io stromwerte
#net_io strom 5 min
zaehler_5=`echo "c6 get zaehler_5" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
strom_z_5=`echo | awk '{print substr("'"${zaehler_5}"'",11,3)}'`
 while [ -z "$strom_z_5" ]
do
zaehler_5=`echo "c6 get zaehler_5" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
strom_z_5=`echo | awk '{print substr("'"${zaehler_5}"'",11,3)}'`
done


Wh5min=`echo "scale=7; $strom_z_5" | bc`
kWh5min=`echo "scale=7; $strom_z_5 / $umdr" | bc`
#mysql abholen letzter Zählerstand Strom
stromzaehler=`echo 'SELECT zaehlerstand_strom FROM energie_nslu ORDER BY datum_zeit DESC LIMIT 1;'| 
mysql --skip-column-names -t -u$db_user  $db_name |awk '{print $2}' `
#Berechnung Zählerstand Strom neu
zstand_stroms0=`echo 'SELECT  z_stand_strom_s0 FROM energie_nslu
ORDER BY datum_zeit DESC LIMIT 1;'| mysql --skip-column-names -h$host -t -u$db_user  $db_name |awk '{print $2}'` 
#Berechnung Zählerstand Strom neu
stromzaehler_neu=`echo "scale=5; $stromzaehler + $kWh5min" | bc`
zstand_stroms0neu=`echo "scale=5; $zstand_stroms0 + $kWh5min" | bc`
#net_io gas werte###############------------------------------------------------------------
#net_io gas 5min 
gas_zaehler_5=`echo "c6 get gas_zaehler_5" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
gas_z_5=`echo | awk '{print substr("'"${gas_zaehler_5}"'",15,3)}'`
while [  -z "$gas_zaehler_5" ]; do
gas_zaehler_5=`echo "c6 get gas_zaehler_5" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
gas_z_5=`echo | awk '{print substr("'"${gas_zaehler_5}"'",15,3)}'`
done
#berechnung Gas cbm liter
liter=10
liter5min=`echo "scale=5; $gas_z_5 * $liter" | bc`
DATUM=`date +%Y'-'%m'-'%d' '%k':'%M`
#mysql abholen letzter Zählerstand Gas
gaszaehler=`echo 'SELECT zaehlerstand_gas FROM energie_nslu ORDER BY datum_zeit DESC LIMIT 1;'| 
mysql --skip-column-names -t -u$db_user  $db_name |awk '{print $2}' `
#berechnung Gaszählerstand neu
cbm5min=`echo "scale=5; $gas_z_5 * $liter/1000" | bc`
gaszaehler_neu=`echo "scale=5; $gaszaehler + $cbm5min" | bc`
#Temperatur abholen
 `echo 1w convert $TEMP_ID | nc -v -u -n -w 6 $AVRNETIO_IP $NETIOPORT -q 1  | grep -v OK || exit 1`
temp_buero=`echo "1w get $TEMP_ID" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
while [ -z "$temp_buero" ]
do
 `echo 1w convert $TEMP_ID | nc -v -u -n -w 6 $AVRNETIO_IP $NETIOPORT -q 1  | grep -v OK || exit 1`
temp_buero=`echo "1w get $TEMP_ID" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1`  
done

#mysql abholen letzter Zählerstand Gas überprüfen und korrigieren
gaszaehler=`echo 'SELECT zaehlerstand_gas FROM energie_nslu ORDER BY datum_zeit DESC LIMIT 1;'| 
mysql --skip-column-names -t -u$db_user  $db_name |awk '{print $2}' `
while [  -z "$gaszaehler" ]; do
gaszaehler=`echo 'SELECT zaehlerstand_gas FROM energie_nslu ORDER BY datum_zeit DESC LIMIT 1;'| 
mysql --skip-column-names -t -u$db_user  $db_name |awk '{print $2}' `
cbm5min=`echo "scale=5; $gas_z_5 * $liter/1000" | bc`

gaszaehler_neu=`echo "scale=5; $gaszaehler + $cbm5min" | bc` 
echo "gaszaehler_neu=$gaszaehler_neu"
update_zstand_gas="UPDATE  $table_name  SET zaehlerstand_gas=$gaszaehler_neu ORDER BY datum_zeit DESC LIMIT 1;"

#mysql   -u$db_user  $db_name -e"$update_zstand_gas"
done
#mysql abholen letzter Zählerstand Gas überprüfen und korrigieren ende
#mysql werte in datenbank eintragen Minute 05
echo "INSERT INTO  $table_name (datum_zeit,strom_imp,z_stand_strom_s0,Wh,temp_buero,gas_imp,gas_liter,zaehlerstand_strom,zaehlerstand_gas) 
VALUES('$datum_stunde_minute_avr','$strom_z_5','$zstand_stroms0neu','$Wh5min','$temp_buero','$gas_z_5','$liter5min','$stromzaehler_neu','$gaszaehler_neu');" | mysql -t -u$db_user  $db_name
echo "insert Minute $minute_2 $datum_skript"

sleep 60
#*****************************************************************15min
elif [ $n == 0 ] && [ $m == 0 ] && [ $stunde != 00 ] && [ $minute_2 != "00" ] || ([ $stunde == 00 ] && ([ $minute_2 == 15 ]  || [ $minute_2 == 30 ] || [ $minute_2 == 45 ]))
then
minute_2=`echo | awk '{print substr("'"${d}"'",19,2)}'`
 datum_stunde_minute_avr="$jahr-$monat-$tag_datum $stunde:$minute_2:00"
echo " 15min"
#net_io stromwerte
#net_io strom 5 min
zaehler_5=`echo "c6 get zaehler_5" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
strom_z_5=`echo | awk '{print substr("'"${zaehler_5}"'",11,3)}'`
 while [ -z "$strom_z_5" ]
do
zaehler_5=`echo "c6 get zaehler_5" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
strom_z_5=`echo | awk '{print substr("'"${zaehler_5}"'",11,3)}'`
done
#net_io strom 15 min
zaehler_15_l=`echo "c6 get zaehler_15_l" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
strom_z_15_l=`echo | awk '{print substr("'"${zaehler_15_l}"'",14,3)}'`
#Berechnung kWh Strom


Wh5min=`echo "scale=7; $strom_z_5" | bc`
kWh5min=`echo "scale=7; $strom_z_5 / $umdr" | bc`
Wh15min=`echo "scale=7; $strom_z_15_l" | bc`
#mysql abholen letzter Zählerstand Strom
stromzaehler=`echo 'SELECT zaehlerstand_strom FROM energie_nslu ORDER BY datum_zeit DESC LIMIT 1;'| 
mysql --skip-column-names -t -u$db_user  $db_name |awk '{print $2}' `
#Berechnung Zählerstand Strom neu
zstand_stroms0=`echo 'SELECT  z_stand_strom_s0 FROM energie_nslu
ORDER BY datum_zeit DESC LIMIT 1;'| mysql --skip-column-names -h$host -t -u$db_user  $db_name |awk '{print $2}'` 
#Berechnung Zählerstand Strom neu
stromzaehler_neu=`echo "scale=5; $stromzaehler + $kWh5min" | bc`
zstand_stroms0neu=`echo "scale=5; $zstand_stroms0 + $kWh5min" | bc`
#net_io gaswerte
#net_io gas 5min 
gas_zaehler_5=`echo "c6 get gas_zaehler_5" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
gas_z_5=`echo | awk '{print substr("'"${gas_zaehler_5}"'",15,3)}'`
while [  -z "$gas_zaehler_5" ]; do
gas_zaehler_5=`echo "c6 get gas_zaehler_5" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
gas_z_5=`echo | awk '{print substr("'"${gas_zaehler_5}"'",15,3)}'`
done
#net_io gas 15 min
gas_zaehler_15_l=`echo "c6 get gas_zaehler_15_l" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
gas_z_15_l=`echo | awk '{print substr("'"${gas_zaehler_15_l}"'",18,3)}'`
#mysql abholen letzter Zählerstand Gas
gaszaehler=`echo 'SELECT zaehlerstand_gas FROM energie_nslu ORDER BY datum_zeit DESC LIMIT 1;'| 
mysql --skip-column-names -t -u$db_user  $db_name |awk '{print $2}' `
#berechnung Gas cbm liter
liter=10
liter5min=`echo "scale=5; $gas_z_5 * $liter" | bc`
liter15min=`echo "scale=5; $gas_z_15_l * $liter" | bc`
literstunde=`echo "scale=5; $gas_z_h_log_zahl * $liter/1000" | bc`
cbm_tag=`echo "scale=7; $gas_zaehlertag_zahl_l  * $liter/1000" | bc`
cbm5min=`echo "scale=5; $gas_z_5 * $liter/1000" | bc`
#berechnung Gaszählerstand neu
gaszaehler_neu=`echo "scale=5; $gaszaehler + $cbm5min" | bc`

##Temperatur abholen
 `echo 1w convert $TEMP_ID | nc -v -u -n -w 6 $AVRNETIO_IP $NETIOPORT -q 1  | grep -v OK || exit 1`
temp_buero=`echo "1w get $TEMP_ID" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
while [ -z "$temp_buero" ] ; do
 `echo 1w convert $TEMP_ID | nc -v -u -n -w 6 $AVRNETIO_IP $NETIOPORT -q 1  | grep -v OK || exit 1`
temp_buero=`echo "1w get $TEMP_ID" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1`  
done

#mysql abholen letzter Zählerstand Gas überprüfen und korrigieren
gaszaehler=`echo 'SELECT zaehlerstand_gas FROM energie_nslu ORDER BY datum_zeit DESC LIMIT 1;'| 
mysql --skip-column-names -t -u$db_user  $db_name |awk '{print $2}' `
while [  -z "$gaszaehler" ]; do
gaszaehler=`echo 'SELECT zaehlerstand_gas FROM energie_nslu ORDER BY datum_zeit DESC LIMIT 1;'| 
mysql --skip-column-names -t -u$db_user  $db_name |awk '{print $2}' `
cbm5min=`echo "scale=5; $gas_z_5 * $liter/1000" | bc`

gaszaehler_neu=`echo "scale=5; $gaszaehler + $cbm5min" | bc` 
echo "gaszaehler_neu=$gaszaehler_neu"
update_zstand_gas="UPDATE  $table_name  SET zaehlerstand_gas=$gaszaehler_neu ORDER BY datum_zeit DESC LIMIT 1;"

#mysql   -u$db_user  $db_name -e"$update_zstand_gas"
done
#mysql abholen letzter Zählerstand Gas überprüfen und korrigieren ende
#mysql Werte in datenbank schreiben

DATUM=`date +%Y'-'%m'-'%d' '%k':'%M`	
datum_jahr_monat_tag=`date  "+%Y-%m-%d"`
gestern_ende=`date --date='1 day ago' "+%Y-%m-%d 23:59:59"`
mysql_insert_15="INSERT INTO  $table_name (datum_zeit,strom_imp,z_stand_strom_s0,strom_imp15,Wh,Wh_15,temp_buero,gas_imp,gas_imp15,gas_liter,gas_liter15,zaehlerstand_strom,zaehlerstand_gas) 
VALUES('$datum_stunde_minute_avr','$strom_z_5','$zstand_stroms0neu','$strom_z_15_l','$Wh5min','$Wh15min','$temp_buero','$gas_z_5','$gas_z_15_l',
'$liter5min','$liter15min','$stromzaehler_neu','$gaszaehler_neu');"
echo "INSERT INTO  $table_name (datum_zeit,strom_imp,z_stand_strom_s0,strom_imp15,Wh,Wh_15,temp_buero,gas_imp,gas_imp15,gas_liter,gas_liter15,zaehlerstand_strom,zaehlerstand_gas) 
VALUES('$datum_stunde_minute_avr','$strom_z_5','$zstand_stroms0neu','$strom_z_15_l','$Wh5min','$Wh15min','$temp_buero','$gas_z_5','$gas_z_15_l',
'$liter5min','$liter15min','$stromzaehler_neu','$gaszaehler_neu');" | mysql -t -u$db_user  $db_name
echo "insert Minute $minute_2 $datum_skript"



sleep 60
#************************************Volle Stunde
elif [ $m == 0 ] && [ $minute_2 == 00 ] && ([ $stunde_01 != 0 ] || [ $stunde == 10 ] || [ $stunde == 20 ])
then
echo " volle stunde"
datum_stunde_minute_avr="$jahr-$monat-$tag_datum $stunde:$minute_2:00"
#net_io strom 15 min
zaehler_5=`echo "c6 get zaehler_5" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
strom_z_5=`echo | awk '{print substr("'"${zaehler_5}"'",11,3)}'`
while [ -z "$strom_z_5" ];
do
zaehler_5=`echo "c6 get zaehler_5" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
strom_z_5=`echo | awk '{print substr("'"${zaehler_5}"'",11,3)}'`
done
zaehler_15_l=`echo "c6 get zaehler_15_l" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
strom_z_15_l=`echo | awk '{print substr("'"${zaehler_15_l}"'",14,3)}'`
#net_io strom stunde
zaehler_h_log=`echo "c6 get zaehler_h_log" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
strom_z_h_log_zahl=`echo | awk '{print substr("'"${zaehler_h_log}"'",15,4)}'`
#Berechnung kWh Strom


Wh5min=`echo "scale=7; $strom_z_5" | bc`
kWh5min=`echo "scale=7; $strom_z_5 / $umdr" | bc`
Wh15min=`echo "scale=7; $strom_z_15_l " | bc`
kWhstunde=`echo "scale=7; $strom_z_h_log_zahl / $umdr" | bc`
#mysql abholen letzter Zählerstand Strom
stromzaehler=`echo 'SELECT zaehlerstand_strom FROM energie_nslu ORDER BY datum_zeit DESC LIMIT 1;'| 
mysql --skip-column-names -t -u$db_user  $db_name |awk '{print $2}' `
zstand_stroms0=`echo 'SELECT  z_stand_strom_s0 FROM energie_nslu
ORDER BY datum_zeit DESC LIMIT 1;'| mysql --skip-column-names -h$host -t -u$db_user  $db_name |awk '{print $2}'` 
zstand_stroms0_stunde=`echo 'SELECT  z_stand_strom_s0_stunde FROM energie_nslu
WHERE NOT ISNULL(z_stand_strom_s0_stunde)ORDER BY datum_zeit DESC LIMIT 1;'| mysql --skip-column-names -h$host -t -u$db_user  $db_name |awk '{print $2}'` 
#Berechnung Zählerstand Strom neu
stromzaehler_neu=`echo "scale=5; $stromzaehler + $kWh5min" | bc`
zstand_stroms0neu=`echo "scale=5; $zstand_stroms0 + $kWh5min" | bc`
zstand_stroms0_stunde_neu=`echo "scale=5; $zstand_stroms0_stunde + $kWhstunde" | bc`
#net_io gas werte###############------------------------------------------------------------
#net_io gas 5min 
gas_zaehler_5=`echo "c6 get gas_zaehler_5" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
gas_z_5=`echo | awk '{print substr("'"${gas_zaehler_5}"'",15,3)}'`

while [  -z "$gas_zaehler_5" ]; do
gas_zaehler_5=`echo "c6 get gas_zaehler_5" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
gas_z_5=`echo | awk '{print substr("'"${gas_zaehler_5}"'",15,3)}'`
done
#net_io gas 15 min
gas_zaehler_15_l=`echo "c6 get gas_zaehler_15_l" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
gas_z_15_l=`echo | awk '{print substr("'"${gas_zaehler_15_l}"'",18,3)}'`
#net_io gas Stunde
gas_zaehler_h_log=`echo "c6 get gas_zaehler_h_log" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
gas_z_h_log_zahl=`echo | awk '{print substr("'"${gas_zaehler_h_log}"'",19,3)}'`
#berechnung Gas cbm liter
liter=10
liter5min=`echo "scale=5; $gas_z_5 * $liter" | bc`
liter15min=`echo "scale=5; $gas_z_15_l * $liter" | bc`
literstunde=`echo "scale=5; $gas_z_h_log_zahl * $liter/1000" | bc`
cbm_tag=`echo "scale=7; $gas_zaehlertag_zahl_l  * $liter/1000" | bc`
cbm5min=`echo "scale=5; $gas_z_5 * $liter/1000" | bc`
#mysql abholen letzter Zählerstand Gas
gaszaehler=`echo 'SELECT zaehlerstand_gas FROM energie_nslu ORDER BY datum_zeit DESC LIMIT 1;'| 
mysql --skip-column-names -t -u$db_user  $db_name |awk '{print $2}' `
#berechnung Gaszählerstand neu

gaszaehler_neu=`echo "scale=5; $gaszaehler + $cbm5min" | bc`

#Temperatur abholen
 `echo 1w convert $TEMP_ID | nc -v -u -n -w 6 $AVRNETIO_IP $NETIOPORT -q 1  | grep -v OK || exit 1`
temp_buero=`echo "1w get $TEMP_ID" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
while [ -z "$temp_buero" ]; do
 `echo 1w convert $TEMP_ID | nc -v -u -n -w 6 $AVRNETIO_IP $NETIOPORT -q 1  | grep -v OK || exit 1`
temp_buero=`echo "1w get $TEMP_ID" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1`  
done

#mysql abholen letzter Zählerstand Gas überprüfen und korrigieren
gaszaehler=`echo 'SELECT zaehlerstand_gas FROM energie_nslu ORDER BY datum_zeit DESC LIMIT 1;'| 
mysql --skip-column-names -t -u$db_user  $db_name |awk '{print $2}' `
while [  -z "$gaszaehler" ]; do
gaszaehler=`echo 'SELECT zaehlerstand_gas FROM energie_nslu ORDER BY datum_zeit DESC LIMIT 1;'| 
mysql --skip-column-names -t -u$db_user  $db_name |awk '{print $2}' `
cbm5min=`echo "scale=5; $gas_z_5 * $liter/1000" | bc`

gaszaehler_neu=`echo "scale=5; $gaszaehler + $cbm5min" | bc` 
echo "gaszaehler_neu=$gaszaehler_neu"
update_zstand_gas="UPDATE  $table_name  SET zaehlerstand_gas=$gaszaehler_neu ORDER BY datum_zeit DESC LIMIT 1;"

#mysql   -u$db_user  $db_name -e"$update_zstand_gas"
done
#mysql abholen letzter Zählerstand Gas überprüfen und korrigieren ende
#mysql werte in datenbank eintragen Volle Stunde
echo "INSERT INTO  $table_name (datum_zeit,strom_imp,z_stand_strom_s0,strom_imp15,Wh,Wh_15,stromimpulse_stunde,z_stand_strom_s0_stunde,
Wh_Stunde,temp_buero,gas_imp,gas_imp15,gas_liter,gas_liter15,gasimp_stunde,gas_cbm_stunde,zaehlerstand_strom,zaehlerstand_gas) 
VALUES('$datum_stunde_minute_avr','$strom_z_5','$zstand_stroms0neu','$strom_z_15_l','$Wh5min', '$Whwatt15min',
'$strom_z_h_log_zahl','$zstand_stroms0_stunde_neu','$kWhstunde','$temp_buero','$gas_z_5','$gas_z_15_l','$liter5min','$liter15min',
'$gas_z_h_log_zahl','$literstunde','$stromzaehler_neu','$gaszaehler_neu');" | mysql -t -u$db_user  $db_name
echo "insert volle Stunde $datum_skript"

sleep 60
#**************************************Mitternacht
elif [ $m == 0 ] && [ $minute_2 == 00 ] && [ $stunde_01 == 0 ] && [ $stunde != 10 ] && [ $stunde != 20 ]
then
d=$d
#datenbank 00.00 Uhr


datum_stunde_minute_avr="$jahr-$monat-$tag_datum $stunde:$minute_2:00"
#net_io stromwerte
#net_io strom 5 min

zaehler_5=`echo "c6 get zaehler_5" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
strom_z_5=`echo | awk '{print substr("'"${zaehler_5}"'",11,3)}'`
while [ -z "$strom_z_5" ]; do
zaehler_5=`echo "c6 get zaehler_5" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
strom_z_5=`echo | awk '{print substr("'"${zaehler_5}"'",11,3)}'`
done
#net_io strom 15 min
zaehler_15_l=`echo "c6 get zaehler_15_l" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
strom_z_15_l=`echo | awk '{print substr("'"${zaehler_15_l}"'",14,3)}'`
#net_io strom stunde
zaehler_h_log=`echo "c6 get zaehler_h_log" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
strom_z_h_log_zahl=`echo | awk '{print substr("'"${zaehler_h_log}"'",15,3)}'`
#net_io strom Tageswerte
zaehlertagl=`echo "c6 get zaehler_tagl" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
zaehlertag_zahl_l=`echo | awk '{print substr("'"${zaehlertagl}"'",13,6)}'` 
#Berechnung kWh Strom


Wh5min=`echo "scale=7; $strom_z_5"  | bc`
kWh5min=`echo "scale=7; $strom_z_5 / $umdr" | bc`
Wh15min=`echo "scale=7; $strom_z_15_l " | bc`
kWhstunde=`echo "scale=7; $strom_z_h_log_zahl / $umdr" | bc`
kwatth_tag=`echo "scale=7; $zaehlertag_zahl / $umdr" | bc`

#net_io gaswerte
#net_io gas 5min
gas_zaehler_5=`echo "c6 get gas_zaehler_5" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
gas_z_5=`echo | awk '{print substr("'"${gas_zaehler_5}"'",15,3)}'`
#net_io gas 15 min
gas_zaehler_15_l=`echo "c6 get gas_zaehler_15_l" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
gas_z_15_l=`echo | awk '{print substr("'"${gas_zaehler_15_l}"'",18,3)}'`
#net_io gas Stunde
gas_zaehler_h_log=`echo "c6 get gas_zaehler_h_log" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
gas_z_h_log_zahl=`echo | awk '{print substr("'"${gas_zaehler_h_log}"'",19,3)}'`
#net_io gas Tageswert
gas_zaehlertagl=`echo "c6 get gas_zaehler_tagl" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
gas_zaehlertag_zahl_l=`echo | awk '{print substr("'"${gas_zaehlertagl}"'",18,6)}'`


#mysql abholen letzter Zählerstand Strom
stromzaehler=`echo 'SELECT zaehlerstand_strom FROM energie_nslu ORDER BY datum_zeit DESC LIMIT 1;'| 
mysql --skip-column-names -t -u$db_user  $db_name |awk '{print $2}' `

#net_io gas werte###############------------------------------------------------------------
#net_io gas 5min 
gas_zaehler_5=`echo "c6 get gas_zaehler_5" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
gas_z_5=`echo | awk '{print substr("'"${gas_zaehler_5}"'",15,3)}'`
while [  -z "$gas_zaehler_5" ]; do
gas_zaehler_5=`echo "c6 get gas_zaehler_5" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
gas_z_5=`echo | awk '{print substr("'"${gas_zaehler_5}"'",15,3)}'`
done
#berechnung Gas liter cbm
liter=10
liter5min=`echo "scale=5; $gas_z_5 * $liter" | bc`
liter15min=`echo "scale=5; $gas_z_15_l * $liter" | bc`
literstunde=`echo "scale=5; $gas_z_h_log_zahl * $liter/1000" | bc`
cbm_tag=`echo "scale=7; $gas_zaehlertag_zahl_l  * $liter/1000" | bc`
cbm5min=`echo "scale=5; $gas_z_5 * $liter/1000" | bc`
#berechnung Gas liter cbm ende
#berechnung Gaszählerstand neu
gaszaehler=`echo 'SELECT zaehlerstand_gas FROM energie_nslu ORDER BY datum_zeit DESC LIMIT 1;'| 
mysql --skip-column-names -t -u$db_user  $db_name |awk '{print $2}' `
gaszaehler_neu=`echo "scale=5; $gaszaehler + $cbm5min" | bc`
#zeit tagesende
gestern_ende=`date --date='1 day ago' "+%Y-%m-%d 23:59:59"`
#Temperatur abholen
 `echo 1w convert $TEMP_ID | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1` 
temp_buero=`echo "1w get $TEMP_ID" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1`  
while [ -z "$temp_buero" ] ; do
 `echo 1w convert $TEMP_ID | nc -v -u -n -w 6 $AVRNETIO_IP $NETIOPORT -q 1  | grep -v OK || exit 1`
temp_buero=`echo "1w get $TEMP_ID" | nc -v -u -n -w 6 $AVRNETIO_IP $AVRNETIO_PORT -q 1`  
done

#mysql abholen letzter Zählerstand Gas überprüfen und korrigieren
gaszaehler=`echo 'SELECT zaehlerstand_gas FROM energie_nslu ORDER BY datum_zeit DESC LIMIT 1;'| 
mysql --skip-column-names -t -u$db_user  $db_name |awk '{print $2}' `
while [  -z "$gaszaehler" ]; do
gaszaehler=`echo 'SELECT zaehlerstand_gas FROM energie_nslu ORDER BY datum_zeit DESC LIMIT 1;'| 
mysql --skip-column-names -t -u$db_user  $db_name |awk '{print $2}' `
cbm5min=`echo "scale=5; $gas_z_5 * $liter/1000" | bc`

gaszaehler_neu=`echo "scale=5; $gaszaehler + $cbm5min" | bc` 
echo "gaszaehler_neu=$gaszaehler_neu"
update_zstand_gas="UPDATE  $table_name  SET zaehlerstand_gas=$gaszaehler_neu ORDER BY datum_zeit DESC LIMIT 1;"

#mysql   -u$db_user  $db_name -e"$update_zstand_gas"
done
#mysql abholen letzter Zählerstand Gas überprüfen und korrigieren ende

zstand_stroms0=`echo 'SELECT z_stand_strom_s0 FROM energie_nslu ORDER BY datum_zeit DESC LIMIT 1;'| 
mysql --skip-column-names -t -u$db_user  $db_name |awk '{print $2}' `
zstand_stroms0_stunde=`echo 'SELECT  z_stand_strom_s0_stunde FROM energie_nslu
WHERE NOT ISNULL(z_stand_strom_s0_stunde)ORDER BY datum_zeit DESC LIMIT 1;'| mysql --skip-column-names -h$host -t -u$db_user  $db_name |awk '{print $2}'` 

#Berechnung Zählerstand Strom neu
stromzaehler_neu=`echo "scale=5; $stromzaehler + $kWh5min" | bc`
zstand_stroms0neu=`echo "scale=5; $zstand_stroms0 + $kWh5min" | bc`
zstand_stroms0_stunde_neu=`echo "scale=5; $zstand_stroms0_stunde + $kWhstunde" | bc`
echo "INSERT INTO $table_name (datum_zeit,strom_imp,z_stand_strom_s0,strom_imp15,Wh,Wh_15,stromimpulse_stunde,z_stand_strom_s0_stunde,Wh_Stunde,strom_imp_tag,
kWh_tag,temp_buero,gas_imp,gas_imp15,gas_liter,gas_liter15,gasimp_stunde,gas_cbm_stunde,gasimp_tag,gas_cbm_tag,
zaehlerstand_strom,zaehlerstand_gas) 
VALUES('$gestern_ende', '$strom_z_5','$zstand_stroms0neu','$strom_z_15_l','$Wh5min','$Wh15min', 
'$strom_z_h_log_zahl','$zstand_stroms0_stunde_neu','$kWhstunde','$zaehlertag_zahl_l','$kwatth_tag','$temp_buero','$gas_z_5','$gas_z_15_l','$liter5min','$liter15min',
'$gas_z_h_log_zahl',
'$literstunde','$gas_zaehlertag_zahl_l','$cbm_tag','$stromzaehler_neu','$gaszaehler_neu');" | mysql -t -u$db_user  $db_name


zeit_stunde=$d
datum_jahr_monat_tag=`date  "+%Y-%m-%d"`
heute_minus_7tage=`date --date='7 days ago' "+%Y-%m-%d"`
heute_minus_1tag=`date --date='1 day ago' "+%Y-%m-%d"`
gestern_ende=`date --date='1 day ago' "+%Y-%m-%d 23:59:59"`
stromzaehler_email=`echo "SELECT datum_zeit,sum(strom_imp)/1008 as kWh,gasimp_tag,zaehlerstand_strom,z_stand_strom_s0,zaehlerstand_gas FROM energie_nslu where DATE_FORMAT(datum_zeit,'%Y-%m-%d')>='$heute_minus_7tage' AND DATE_FORMAT(datum_zeit,'%H:%i') = '23:59' order by datum_zeit desc;"| mysql  -h$host -t -u$db_user  $db_name`
temp_min=`echo "SELECT MIN(temp_buero) FROM energie_nslu WHERE DATE_FORMAT(datum_zeit,'%Y-%m-%d')='$heute_minus_1tag';"| mysql --skip-column-names -h$host -t -u$db_user  $db_name |awk '{print $2}'` 
temp_max=`echo "SELECT MAX(temp_buero) FROM energie_nslu WHERE DATE_FORMAT(datum_zeit,'%Y-%m-%d') ='$heute_minus_1tag';"| mysql --skip-column-names -h$host -t -u$db_user  $db_name |awk '{print $2}'` 
#echo "temp_min=$temp_min"
temp_min_datum_zeit=`echo "SELECT datum_zeit,temp_buero as Mintemp FROM energie_nslu WHERE DATE_FORMAT(datum_zeit,'%Y-%m-%d')='$heute_minus_1tag' AND temp_buero =$temp_min ;"| mysql  -h$host -t -u$db_user  $db_name`
temp_max_datum_zeit=`echo "SELECT datum_zeit,temp_buero as Maxtemp FROM energie_nslu WHERE DATE_FORMAT(datum_zeit,'%Y-%m-%d')='$heute_minus_1tag' AND temp_buero =$temp_max ;"| mysql  -h$host -t -u$db_user  $db_name`
inhalt="$stromzaehler_email $temp_min_datum_zeit $temp_max_datum_zeit"
#echo $inhalt
betreff="Tageswerte Strom, Gas $datum_jahr_monat_tag bis $heute_minus_7tage -- Temperatur Minimum und Temperaturmaximum $heute_minus_1tag"
from="NSLU"
to="max.mustermann@t-online.de"
echo - "$inhalt"| mail -a "Content-Type: text/plain; charset=UTF-8" -s "$betreff" -a  "From: $from" $to
echo "insert Mitternacht $datum_skript"

sleep 60

echo "mitternacht"
fi
echo "nicht minute 5 oder 0"
fi
done

exit 0