#!/bin/sh

# Declare Program Variables
sDBNAME='VellemanK8055'		# Enter the mysql database name
sDBUSER='root'				# Enter the mysql user name
sDBPASS='logitech'			# Enter the password of the user above


dbasequery=`mysql -u$sDBUSER -p$sDBPASS -e'use '$sDBNAME'; SELECT (
									MAX( MeterValue_1 ) - MIN( MeterValue_1 ) 
									) AS LOusage, (
									MAX( MeterValue_2 ) - MIN( MeterValue_2 ) 
									) AS HIusage, DATE( datetime ) AS Day, (
									(
									MAX( MeterValue_1 ) - MIN( MeterValue_1 ) 
									) * ( 
									SELECT TarifEMeter_1
									FROM tblEnergyTarifs
									ORDER BY ValidUntil
									LIMIT 0 , 1 ) 
									) AS COSTS_LO, (
									(
									MAX( MeterValue_2 ) - MIN( MeterValue_2 ) 
									) * ( 
									SELECT TarifEMeter_2
									FROM tblEnergyTarifs
									ORDER BY ValidUntil
									LIMIT 0 , 1 ) 
									) AS COSTS_HI
									FROM tblEnergyMeter 
									WHERE DATE( datetime ) = DATE( CURDATE( ) ) 
									GROUP BY DATE( datetime ) 
									ORDER BY DATE( datetime ) ;'`
Total_MeterValue_1=`echo $dbasequery | cut -d \  -f 6`
Total_MeterValue_2=`echo $dbasequery | cut -d \  -f 7`
Cost_Total_MeterValue_1=`echo $dbasequery | cut -d \  -f 9`
Cost_Total_MeterValue_2=`echo $dbasequery | cut -d \  -f 10`


dbasequery=`mysql -u$sDBUSER -p$sDBPASS -e'use '$sDBNAME'; SELECT dbrowid FROM tblEnergyMeterDailyTotals WHERE DATE( date ) = DATE( CURDATE( ) ) ;'`
dbemptyset=`mysql -u$sDBUSER -p$sDBPASS -e'use '$sDBNAME'; SELECT dbrowid FROM tblEnergyMeterDailyTotals WHERE DATE( date ) = DATE( CURDATE( ) +1 ) ;'`


if [ $dbasequery -eq $dbemptyset ]; then 
	dbasequery=`mysql -u$sDBUSER -p$sDBPASS -e'use '$sDBNAME'; INSERT INTO tblEnergyMeterDailyTotals  (date, Total_MeterValue_1, Total_MeterValue_2, Cost_Total_MeterValue_1, Cost_Total_MeterValue_2) VALUES (CURDATE( ), '$Total_MeterValue_1', '$Total_MeterValue_2', '$Cost_Total_MeterValue_1', '$Cost_Total_MeterValue_2');'`
else
	dbasequery=`mysql -u$sDBUSER -p$sDBPASS -e'use '$sDBNAME'; UPDATE tblEnergyMeterDailyTotals SET Total_MeterValue_1 = '$Total_MeterValue_1' , Total_MeterValue_2 ='$Total_MeterValue_2', Cost_Total_MeterValue_1 = '$Cost_Total_MeterValue_1', Cost_Total_MeterValue_2 = '$Cost_Total_MeterValue_2' WHERE DATE( date ) = DATE( CURDATE( ) );'`
fi


cd /mnt/protected/domotica/libchart/demo
php /mnt/protected/domotica/libchart/demo/electricitydaily-8.php