/**
* Add debugging information include queries and messages to output queue
*
* @param Util\Debug $debug
*/
protected function convertDebug(Util\Debug $debug)
{
$jsonDebug['level'] = $debug->getLevel();
if ($dbDriver = Util\Configuration::read('db.driver')) {
$jsonDebug['database'] = $dbDriver;
}
$jsonDebug['time'] = $debug->getExecutionTime();
if ($uptime = Util\Debug::getUptime()) {
$jsonDebug['uptime'] = $uptime * 1000;
}
if ($load = Util\Debug::getLoadAvg()) {
$jsonDebug['load'] = $load;
}
if ($commit = Util\Debug::getCurrentCommit()) {
$jsonDebug['commit-hash'] = $commit;
}
if ($version = phpversion()) {
$jsonDebug['php-version'] = $version;
}
$jsonDebug['messages'] = $debug->getMessages();
// SQL statements
if (count($statements = $debug->getQueries())) {
$this->getSQLTimes($statements);
$jsonDebug['sql'] = array('totalTime' => $this->sqlTotalTime, 'worstTime' => $this->sqlWorstTime, 'queries' => array_map(function ($q) {
return array('sql' => Util\Debug::getParametrizedQuery($q['sql'], $q['params']), 'execTime' => $q['executionMS']);
}, array_values($debug->getQueries())));
}
return $jsonDebug;
}
/**
* Core data aggregation
*
* @param int $channel_id id of channel to perform aggregation on
* @param string $interpreter interpreter class name
* @param string $mode aggregation mode (full, delta)
* @param string $level aggregation level (day...)
* @param int $period delta days to aggregate
* @return int number of rows
*/
protected function aggregateChannel($channel_id, $interpreter, $mode, $level, $period)
{
$format = self::getAggregationDateFormat($level);
$type = self::getAggregationLevelTypeValue($level);
$weighed_avg = $interpreter == 'Volkszaehler\\Interpreter\\SensorInterpreter';
$sqlParameters = array($type);
$sql = 'REPLACE INTO aggregate (channel_id, type, timestamp, value, count) ';
if ($weighed_avg) {
// get interpreter's aggregation function
$aggregationFunction = $interpreter::groupExprSQL('agg.value');
// SQL query similar to MySQLOptimizer group mode
$sql .= 'SELECT channel_id, ? AS type, ' . 'MAX(agg.timestamp) AS timestamp, ' . 'COALESCE( ' . 'SUM(agg.val_by_time) / (MAX(agg.timestamp) - MIN(agg.prev_timestamp)), ' . $aggregationFunction . ') AS value, ' . 'COUNT(agg.value) AS count ' . 'FROM ( ' . 'SELECT channel_id, timestamp, value, ' . 'value * (timestamp - @prev_timestamp) AS val_by_time, ' . 'GREATEST(0, IF(@prev_timestamp = NULL, NULL, @prev_timestamp)) AS prev_timestamp, ' . '@prev_timestamp := timestamp ' . 'FROM data ' . 'CROSS JOIN (SELECT @prev_timestamp := NULL) AS vars ' . 'WHERE ';
} else {
// get interpreter's aggregation function
$aggregationFunction = $interpreter::groupExprSQL('value');
$sql .= 'SELECT channel_id, ? AS type, MAX(timestamp) AS timestamp, ' . $aggregationFunction . ' AS value, COUNT(timestamp) AS count ' . 'FROM data WHERE ';
}
// selected channel only
if ($channel_id) {
$sqlParameters[] = $channel_id;
$sql .= 'channel_id = ? ';
}
// since last aggregation only
if ($mode == 'delta') {
if ($channel_id) {
// selected channel
$sqlTimestamp = 'SELECT UNIX_TIMESTAMP(DATE_ADD(' . 'FROM_UNIXTIME(MAX(timestamp) / 1000, ' . $format . '), ' . 'INTERVAL 1 ' . $level . ')) * 1000 ' . 'FROM aggregate ' . 'WHERE type = ? AND channel_id = ?';
if ($ts = $this->conn->fetchColumn($sqlTimestamp, array($type, $channel_id), 0)) {
$sqlParameters[] = $ts;
$sql .= 'AND timestamp >= ? ';
}
} else {
// all channels
$sqlParameters[] = $type;
$sql .= 'AND timestamp >= IFNULL((' . 'SELECT UNIX_TIMESTAMP(DATE_ADD(' . 'FROM_UNIXTIME(MAX(timestamp) / 1000, ' . $format . '), ' . 'INTERVAL 1 ' . $level . ')) * 1000 ' . 'FROM aggregate ' . 'WHERE type = ? AND aggregate.channel_id = data.channel_id ' . '), 0) ';
}
}
// selected number of periods only
if ($period) {
$sql .= 'AND timestamp >= (SELECT UNIX_TIMESTAMP(DATE_SUB(DATE_FORMAT(NOW(), ' . $format . '), INTERVAL ? ' . $level . ')) * 1000) ';
$sqlParameters[] = $period;
}
// up to before current period
$sql .= 'AND timestamp < UNIX_TIMESTAMP(DATE_FORMAT(NOW(), ' . $format . ')) * 1000 ';
if ($weighed_avg) {
// close inner table
$sql .= 'ORDER BY timestamp ' . ') AS agg ';
}
$sql .= 'GROUP BY channel_id, ' . Interpreter\Interpreter::buildGroupBySQL($level);
if (Util\Debug::isActivated()) {
echo Util\Debug::getParametrizedQuery($sql, $sqlParameters) . "\n";
}
$rows = $this->conn->executeUpdate($sql, $sqlParameters);
return $rows;
}