#!/bin/bash
# calculate own PV usage 
# Author: Markus 2014-05-20
# latest version:
# $Header: /home/wf/smaspot/RCS/eigenverbrauch.bash,v 1.5 2014/05/29 10:17:11 wf Exp wf $

#
# get the delete query for the given channel
#
deletequery() {
  local l_channel=$1
cat << EOF
  DELETE 
   FROM `data` 
   WHERE `timestamp` = 
     (SELECT MAX(timestamp) FROM data WHERE channel_id = $l_channel)  
   AND channel_id = $l_channel;
EOF
}

#
# get the insert query for the given channels
#  l_chanel the channel to insert into
#    l_c2 - the channel to substract 
#    l_c3 - the channel to substract the value from
#
insertquery() {
  local l_channel=$1
  local l_c2=$2
  local l_c3=$3
cat << EOF
  INSERT INTO `data`( `channel_id`, `timestamp`, `value`) 
    SELECT $l_chanel,  timestamp_2,   data_3.value - data_2.value /1000    
    FROM (
      SELECT 
        MAX(CASE WHEN `channel_id` = $l_c2 then timestamp else 0 end) timestamp_2,
        MAX(CASE WHEN `channel_id` = $l_c3 then timestamp else 0 end) timestamp_3
        FROM `data` 
        WHERE channel_id in ($l_c2,$l_c3) 
          AND timestamp > (
            SELECT MAX(timestamp) FROM data WHERE `channel_id` = $l_channel 
          )
        GROUP by floor(`timestamp`/60/1000/15) ) a 
        INNER JOIN data data_2 
          ON  timestamp_2  = data_2.timestamp 
          AND data_2.channel_id = $l_c2 
        INNER JOIN data data_3 
          ON  timestamp_3  = data_3.timestamp 
          AND data_3.channel_id = $l_c3 
EOF
}

# modify according to your volkszaehler mysql database settings
user=vz
password=fdfdfdfdf 
db=volkszaehler 

# debug setting
debug=
# uncomment to debug sql
#debug=-T

# modify to suite your channel settings

# channel 7 is calculated from as channel 4 - channel 2 / 1000 
deletequery 7     | egrep -v "^#" | mysql --user=$user --password=$password $db $debug
insertquery 7 2 4 | egrep -v "^#" | mysql --user=$user --password=$password $db $debug

# channel 8 is calculated from as channel 7 - channel 1 / 1000 
echo "DELETE FROM `data` WHERE channel_id = 8" |  mysql --user=$user --password=$password $db $debug
insertquery 8 1 7 | egrep -v "^#" | mysql --user=$user --password=$password $db $debug