#!/bin/bash
echo '
delete from `data` where `timestamp` = (select * from (select max(timestamp) from data where channel_id = 7) x) and channel_id = 7
' | mysql --user=vz --password=fdfdfdfdf volkszaehler -T
echo '
INSERT INTO `data`( `channel_id`, `timestamp`, `value`) select 7,  timestamp_2,   data_4.value - data_2.value /1000    from (
SELECT 
max(case when `channel_id`  = 2 then timestamp else 0 end) timestamp_2,
max(case when `channel_id`  = 4 then timestamp else 0 end) timestamp_4
  FROM `data` where channel_id in (2,4) 
and timestamp > (select max(timestamp) from data where `channel_id` = 7)
group by floor(`timestamp`/60/1000/15) ) a inner join data data_2 on  timestamp_2  = data_2.timestamp and data_2.channel_id = 2
inner join data data_4 on  timestamp_4  = data_4.timestamp and data_4.channel_id = 4
' | mysql --user=vz --password=fdfdfdfdf volkszaehler

echo '
delete from `data` where channel_id = 8
' | mysql --user=vz --password=fdfdfdfdf volkszaehler -T
#and timestamp > (select max(timestamp) from data where `channel_id` = 8)

echo '
INSERT INTO `data`( `channel_id`, `timestamp`, `value`) select 8,  timestamp_1,   (select data.value from data where channel_id = 7 and  data.timestamp <= data_1.timestamp order by timestamp desc limit 1) + data_1.value/1000     from 
(
SELECT 
max(case when `channel_id`  = 1 then timestamp else 0 end) timestamp_1,
max(case when `channel_id`  = 7 then timestamp else 0 end) timestamp_7
  FROM `data` where channel_id in (1,7) 
group by floor(`timestamp`/60/1000/15) 
) a inner join data data_1 on  timestamp_1  = data_1.timestamp and data_1.channel_id = 1
' | mysql --user=vz --password=dsdsdsdsds volkszaehler