# !/bin/sh
# /usr/local/bin/upd_temp_file
# extrahiert die stündliche, durchschittliche
# Aussentemperatur (Sensor 11)
# und Innentemperatur (Sensor 14)
# aus der Datenbank (ems_data) und schreibt
# diese, nachdem sed die #-Zeile entfernt
# hat in die vorhandenen Dateien
# /data/home/log/temp_in.log und */temp_out.log,
# wird jede Stunde und 59 Minuten ausgeführt
# durch cron
# kmw 20131219 - 20140419 - 20141208
# ------------------------------------------

mysql -uems -p63 ems_data -B -e "SELECT DATE_FORMAT(endtime,'%Y-%m-%dT%H:30:00Z') AS '# Datum', FORMAT(AVG(value),1) AS ' ATemp' FROM numeric_data WHERE endtime >= date_sub(now(), interval 59 minute) AND endtime <= now() AND sensor=11 GROUP BY DATE_FORMAT(endtime, '%Y-%m-%d %H') ORDER BY endtime ASC;" | sed '/^#/d' >> /data/home/log/temp_out.log
# mysql -uems -p63 ems_data -B -e "SELECT DATE_FORMAT(starttime,'%Y-%m-%dT%H:30:00Z') AS '# Datum', FORMAT(AVG(value),1) AS ' ATemp' FROM numeric_data WHERE starttime >= date_sub(now(), interval 1 hour) AND starttime <= now() AND sensor=11 GROUP BY DATE_FORMAT(starttime, '%Y-%m-%d %H') ORDER BY starttime ASC;" | sed '/^#/d' >> /data/home/log/temp_out.log
sleep 1
mysql -uems -p63 ems_data -B -e "SELECT DATE_FORMAT(endtime,'%Y-%m-%dT%H:30:00Z') AS '# Datum', FORMAT(AVG(value),1) AS ' ITemp' FROM numeric_data WHERE endtime >= date_sub(now(), interval 59 minute) AND endtime <= now() AND sensor=14 GROUP BY DATE_FORMAT(endtime, '%Y-%m-%d %H') ORDER BY endtime ASC;" | sed '/^#/d' >> /data/home/log/temp_in.log
# mysql -uems -p63 ems_data -B -e "SELECT DATE_FORMAT(starttime,'%Y-%m-%dT%H:30:00Z') AS '# Datum', FORMAT(AVG(value),1) AS ' ITemp' FROM numeric_data WHERE starttime >= date_sub(now(), interval 1 hour) AND starttime <= now() AND sensor=14 GROUP BY DATE_FORMAT(starttime, '%Y-%m-%d %H') ORDER BY starttime ASC;" | sed '/^#/d' >> /data/home/log/temp_in.log