protected function convertRawTuple(Interpreter\Interpreter $interpreter, $tuple)
{
try {
$this->openController();
$result = false;
// convert raw reading to converted value
if (!isset($interpreter->push_ts)) {
// skip first conversion result
$interpreter->convertRawTuple($tuple);
} elseif ($tuple[0] > $interpreter->push_ts) {
// AccumulatorInterpreter special handling- suppress duplicate counter values
if ($interpreter instanceof Interpreter\AccumulatorInterpreter) {
if (isset($interpreter->push_raw_value) && $interpreter->push_raw_value == $tuple[1]) {
return false;
}
}
$result = $interpreter->convertRawTuple($tuple);
}
// indicate that tuple conversion has already happened once
$interpreter->push_ts = $tuple[0];
$interpreter->push_raw_value = $tuple[1];
} catch (\Exception $e) {
// make sure EntityManager is re-initialized on error
$this->openController(true);
return false;
}
return $result;
}
protected function getPayload(Interpreter $interpreter, $tuple)
{
try {
$this->openController();
$result = false;
// prevent div by zero
if (!isset($interpreter->calculated_ts) || $tuple[0] > $interpreter->calculated_ts) {
$result = $interpreter->convertRawTuple($tuple);
}
// 1st calculated value is invalid due to interpreter logic
if (!isset($interpreter->calculated_ts)) {
$result = false;
}
$interpreter->calculated_ts = $tuple[0];
} catch (\Exception $e) {
// make sure EntityManager is re-initialized on error
$this->openController(true);
return false;
}
return $result;
}
public function processData($callback)
{
$this->rows = parent::getData();
$tuples = array();
$last = $this->getFrom();
foreach ($this->rows as $row) {
$delta = $row[0] - $last;
$tuple = $callback(array((double) $row[0], (double) $row[1] / $row[2], (int) $row[2]));
if (is_null($this->max) || $tuple[1] > $this->max[1]) {
$this->max = $tuple;
}
if (is_null($this->min) || $tuple[1] < $this->min[1]) {
$this->min = $tuple;
}
$this->consumption += $tuple[1] * $delta;
$tuples[] = $tuple;
$last = $row[0];
}
return $tuples;
}
/**
* Delete tuples from single or multiple channels
*
* @todo deduplicate Model\Channel code
* @param string|array uuid
*/
public function delete($uuids)
{
$from = null;
$to = null;
// parse interval
if (null !== ($from = $this->getParameters()->get('from'))) {
$from = Interpreter::parseDateTimeString($from);
if (null !== ($to = $this->getParameters()->get('to'))) {
$to = Interpreter::parseDateTimeString($to);
if ($from > $to) {
throw new \Exception('From is larger than to');
}
}
} elseif ($from = $this->getParameters()->get('ts')) {
$to = $from;
} else {
throw new \Exception('Missing timestamp (ts, from, to)');
}
$rows = 0;
foreach (self::makeArray($uuids) as $uuid) {
$channel = EntityController::factory($this->em, $uuid, true);
$rows += $channel->clearData($this->em->getConnection(), $from, $to);
}
return array('rows' => $rows);
}
/**
* Add single or multiple tuples
*
* @todo replace by pluggable api parser
* @param Model\Channel $channel
*/
public function add($channel)
{
try {
/* to parse new submission protocol */
$rawPost = $this->request->getContent();
// file_get_contents('php://input')
$json = Util\JSON::decode($rawPost);
if (isset($json['data'])) {
throw new \Exception('Can only add data for a single channel at a time');
/* backed out b111cfa2 */
}
// convert nested ArrayObject to plain array with flattened tuples
$data = array_reduce($json->getArrayCopy(), function ($carry, $tuple) {
return array_merge($carry, $tuple);
}, array());
} catch (Util\JSONException $e) {
/* fallback to old method */
$timestamp = $this->request->parameters->get('ts');
$value = $this->request->parameters->get('value');
if (is_null($timestamp)) {
$timestamp = (double) round(microtime(TRUE) * 1000);
} else {
$timestamp = Interpreter::parseDateTimeString($timestamp);
}
if (is_null($value)) {
$value = 1;
}
// same structure as JSON request result
$data = array($timestamp, $value);
}
$sql = 'INSERT ' . (in_array(self::OPT_SKIP_DUPLICATES, $this->options) ? 'IGNORE ' : '') . 'INTO data (channel_id, timestamp, value) ' . 'VALUES ' . implode(', ', array_fill(0, count($data) >> 1, '(' . $channel->getId() . ',?,?)'));
$rows = $this->em->getConnection()->executeUpdate($sql, $data);
return array('rows' => $rows);
}
/**
* Render Interpreter output
*
* See comments regarding StreamedResponse at renderDeferred()
*/
protected function renderInterpreter(Interpreter\Interpreter $interpreter)
{
$this->content .= '{"tuples":[';
// start with iterating through PDO result set to populate interpreter header data
foreach ($interpreter as $key => $tuple) {
// render buffered content- likely no exception after loop iteration has started
$this->renderContent();
if ($key) {
echo ',';
}
echo '[' . $tuple[0] . ',' . View::formatNumber($tuple[1]) . ',' . $tuple[2] . ']';
}
// render buffered content if not rendered inside foreach loop due to Interpreter empty
$this->renderContent();
$from = 0 + $interpreter->getFrom();
$to = 0 + $interpreter->getTo();
$min = $interpreter->getMin();
$max = $interpreter->getMax();
$average = $interpreter->getAverage();
$consumption = $interpreter->getConsumption();
$header = array();
$header['uuid'] = $interpreter->getEntity()->getUuid();
if (isset($from)) {
$header['from'] = $from;
}
if (isset($to)) {
$header['to'] = $to;
}
if (isset($min)) {
$header['min'] = $min;
}
if (isset($max)) {
$header['max'] = $max;
}
if (isset($average)) {
$header['average'] = View::formatNumber($average);
}
if (isset($consumption)) {
$header['consumption'] = View::formatNumber($consumption);
}
$header['rows'] = $interpreter->getRowCount();
echo '],' . substr(json_encode($header), 1, -1) . '}';
}
/**
* Add data to output queue
*
* @param Interpreter\InterpreterInterface $interpreter
* @param boolean $children
* @todo Aggregate first is assumed- this deviates from json view behaviour
*/
protected function addData(Interpreter\Interpreter $interpreter)
{
$this->response->headers->set('Content-Disposition', 'attachment; ' . 'filename="' . strtolower($interpreter->getEntity()->getProperty('title')) . '.csv" ');
echo PHP_EOL;
// UUID delimiter
echo '# uuid:' . CSV::DELIMITER . $interpreter->getEntity()->getUuid() . PHP_EOL;
echo '# title:' . CSV::DELIMITER . $interpreter->getEntity()->getProperty('title') . PHP_EOL;
if ($interpreter instanceof Interpreter\AggregatorInterpreter) {
// min/ max etc are not populated if $children->processData hasn't been called
return;
}
$data = array();
// iterate through PDO resultset
foreach ($interpreter as $tuple) {
$data[] = $tuple;
}
$min = $interpreter->getMin();
$max = $interpreter->getMax();
$average = $interpreter->getAverage();
$consumption = $interpreter->getConsumption();
$from = $this->formatTimestamp($interpreter->getFrom());
$to = $this->formatTimestamp($interpreter->getTo());
if (isset($from)) {
echo '# from:' . CSV::DELIMITER . $from . PHP_EOL;
}
if (isset($to)) {
echo '# to:' . CSV::DELIMITER . $to . PHP_EOL;
}
if (isset($min)) {
echo '# min:' . CSV::DELIMITER . $this->formatTimestamp($min[0]) . CSV::DELIMITER . ' => ' . CSV::DELIMITER . View::formatNumber($min[1]) . PHP_EOL;
}
if (isset($max)) {
echo '# max:' . CSV::DELIMITER . $this->formatTimestamp($max[0]) . CSV::DELIMITER . ' => ' . CSV::DELIMITER . View::formatNumber($max[1]) . PHP_EOL;
}
if (isset($average)) {
echo '# average:' . CSV::DELIMITER . View::formatNumber($average) . PHP_EOL;
}
if (isset($consumption)) {
echo '# consumption:' . CSV::DELIMITER . View::formatNumber($consumption) . PHP_EOL;
}
echo '# rows:' . CSV::DELIMITER . $interpreter->getRowCount() . PHP_EOL;
if (isset($data)) {
// Aggregators don't return data
foreach ($data as $tuple) {
echo $this->formatTimestamp($tuple[0]) . CSV::DELIMITER . View::formatNumber($tuple[1]) . CSV::DELIMITER . $tuple[2] . PHP_EOL;
}
}
}
/**
* Add data to output queue
*
* @param Interpreter\InterpreterInterface $interpreter
* @param boolean $children
* @todo Aggregate first is assumed- this deviates from json view behaviour
*/
protected function addData(Interpreter\Interpreter $interpreter)
{
// echo "uuid:' . $interpreter->getEntity()->getUuid() . PHP_EOL;
// echo "title:' . $interpreter->getEntity()->getProperty('title') . PHP_EOL;
if ($interpreter instanceof Interpreter\AggregatorInterpreter) {
// min/ max etc are not populated if $children->processData hasn't been called
return;
}
$data = array();
// iterate through PDO resultset
foreach ($interpreter as $tuple) {
$data[] = $tuple;
}
// get unit
$unit = $interpreter->getEntity()->getDefinition();
$unit = isset($unit->unit) ? $unit->unit : '';
if (sizeof($data) == 0 || $this->request->query->has('tuples') && $this->request->query->get('tuples') == 1) {
$val = $interpreter->getConsumption();
$unit .= 'h';
} else {
$val = $data[sizeof($data) - 1];
}
$val = is_array($val) ? $val[1] : $val;
echo $val . ' ' . $unit;
}
/**
* 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;
}
/**
* Raw pulse to power conversion
*
* @param $callback a callback called each iteration for output
* @return array with timestamp, values, and pulse count
*/
public function processData($callback)
{
$tuples = array();
$this->rows = parent::getData();
$this->resolution = $this->channel->getProperty('resolution');
$this->pulseCount = 0;
$last = $this->getFrom();
foreach ($this->rows as $row) {
$delta = $row[0] - $last;
$tuple = $callback(array((double) $last, (double) ($row[1] * 3600000000.0) / ($this->resolution * $delta), (int) $row[2]));
if (is_null($this->max) || $tuple[1] > $this->max[1]) {
$this->max = $tuple;
}
if (is_null($this->min) || $tuple[1] < $this->min[1]) {
$this->min = $tuple;
}
$this->pulseCount += $row[1];
$tuples[] = $tuple;
$last = $row[0];
}
return $tuples;
}
/**
* Add data to output queue
*
* @param Interpreter\InterpreterInterface $interpreter
*/
protected function addData(Interpreter\Interpreter $interpreter)
{
$this->response->setHeader('Content-Disposition', 'attachment; ' . 'filename="' . strtolower($interpreter->getEntity()->getProperty('title')) . '.csv" ' . 'creation-date="' . date(DATE_RFC2822, $interpreter->getTo() / 1000) . '"');
$tuples = $interpreter->processData(function ($tuple) {
return array($tuple[0], View::formatNumber($tuple[1]), $tuple[2]);
});
$min = $interpreter->getMin();
$max = $interpreter->getMax();
$average = $interpreter->getAverage();
$consumption = $interpreter->getConsumption();
$from = $interpreter->getFrom();
$to = $interpreter->getTo();
echo '# uuid: ' . $interpreter->getEntity()->getUuid() . PHP_EOL;
if (isset($from)) {
echo '# from: ' . $from . PHP_EOL;
}
if (isset($to)) {
echo '# to: ' . $to . PHP_EOL;
}
if (isset($min)) {
echo '# min: ' . $min[0] . ' => ' . $min[1] . PHP_EOL;
}
if (isset($max)) {
echo '# max: ' . $max[0] . ' => ' . $max[1] . PHP_EOL;
}
if (isset($average)) {
echo '# average: ' . View::formatNumber($average) . PHP_EOL;
}
if (isset($consumption)) {
echo '# consumption: ' . View::formatNumber($consumption) . PHP_EOL;
}
echo '# rows: ' . $interpreter->getRowCount() . PHP_EOL;
foreach ($tuples as $tuple) {
echo implode(CSV::DELIMITER, $tuple) . PHP_EOL;
}
}
