65 lines
2.9 KiB
PHP
65 lines
2.9 KiB
PHP
<?php
|
|
|
|
$app = require(__DIR__ . '/../bootstrap.php');
|
|
$day_seconds = 86400;
|
|
|
|
$local_db = $app['local_db'];
|
|
$local_table = $app['config']['local']['table_aggregation'];
|
|
$remote_db = $app['remote_db'];
|
|
$remote_table = $app['config']['remote']['table'];
|
|
|
|
// Get latest date from local database;
|
|
$latest_date_query = 'SELECT MAX(date) FROM ' . $local_table . ';';
|
|
$latest_date_result = $local_db->query($latest_date_query);
|
|
$latest_date = new DateTime($latest_date_result->fetch_column(0));
|
|
|
|
$date_yesterday = new DateTime();
|
|
$date_yesterday->modify('-1 day')->modify('0:00');
|
|
$interval = $date_yesterday->diff($latest_date);
|
|
|
|
for ($date_diff = $interval->days; $date_diff > 0; $date_diff--) {
|
|
$date = new DateTime();
|
|
$date->modify('-' . $date_diff . ' day');
|
|
$date->modify('0:00');
|
|
|
|
$day_start = $date->getTimestamp() * 1000;
|
|
$day_end = ($date->getTimestamp() + $day_seconds) * 1000;
|
|
|
|
// electric_meter is the meter reading in Wh.
|
|
// The daily consumption ist the last minus the first value of a day
|
|
$query_consumption_end = 'SELECT value FROM ' . $remote_table . ' WHERE channel_id = ' . $app['config']['remote']['electric_meter'] . ' AND timestamp < ' . $day_end . ' ORDER BY timestamp DESC LIMIT 1;';
|
|
$query_consumption_start = 'SELECT value FROM ' . $remote_table . ' WHERE channel_id = ' . $app['config']['remote']['electric_meter'] . ' AND timestamp > ' . $day_start . ' ORDER BY timestamp ASC LIMIT 1;';
|
|
|
|
$result = $remote_db->query($query_consumption_end);
|
|
$tmp = $result->fetch_assoc();
|
|
$consumption_end = (float)$tmp['value'];
|
|
$result = $remote_db->query($query_consumption_start);
|
|
$tmp = $result->fetch_assoc();
|
|
$consumption_start = (float)$tmp['value'];
|
|
|
|
// power_sensor is the actual consumption in W.
|
|
$query_grid_feed = 'SELECT timestamp, value FROM ' . $remote_table . ' WHERE channel_id = ' . $app['config']['remote']['power_sensor'] . ' AND timestamp < ' . $day_end . ' AND timestamp > ' . $day_start . ' ORDER BY timestamp ASC;';
|
|
$result = $remote_db->query($query_grid_feed);
|
|
$last = null;
|
|
$power_sensor = 0;
|
|
while ($query_grid_data = $result->fetch_assoc()) {
|
|
if (!is_null($last)) {
|
|
$time_diff = $query_grid_data['timestamp'] - $last['timestamp'];
|
|
$power_sensor += $last['value'] * $time_diff;
|
|
}
|
|
$last = $query_grid_data;
|
|
}
|
|
|
|
$power_sensor /= 3600000; // 3600000 milliseconds per hour
|
|
$meter_consumption = $consumption_end - $consumption_start;
|
|
$grid_feed = $meter_consumption - $power_sensor;
|
|
|
|
if ($grid_feed < 0) {
|
|
$grid_feed = 0;
|
|
}
|
|
|
|
$aggregation_query = 'INSERT INTO ' . $local_table . ' (`date`, `meter_consumption`, `power_sensor`, `grid_feed`, `last_value`, `production_eg`, `production_og`) VALUES ("' . $date->format('Y-m-d') . '", ' . $meter_consumption . ', ' . $power_sensor . ', ' . $grid_feed . ', ' . $consumption_end . ', 0, 0);';
|
|
|
|
$local_db->query($aggregation_query);
|
|
}
|