aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/DillerDao.js46
-rw-r--r--src/DillerTx.js2
-rw-r--r--src/types.js1
-rw-r--r--src/web/DillerWeb.js38
4 files changed, 78 insertions, 9 deletions
diff --git a/src/DillerDao.js b/src/DillerDao.js
index cf4c75b..7620541 100644
--- a/src/DillerDao.js
+++ b/src/DillerDao.js
@@ -4,7 +4,7 @@ var _ = require('lodash');
* @param tx {PgTx}
* @class
*/
-function DillerDao(tx) {
+function DillerDao(tx, as) {
var deviceColumns = ['id', 'created_timestamp', 'key', 'name', 'description'];
var deviceStatusColumns = ['device', 'online', 'timestamp', 'host'];
@@ -176,8 +176,47 @@ function DillerDao(tx) {
// -------------------------------------------------------------------------------------------------------------------
function valuesByPropertyId(propertyId, limit) {
- limit = limit || 10;
- return tx.manyOrNone('SELECT timestamp, coalesce(value_numeric::text, value_text) AS value FROM value WHERE property=$1 ORDER BY timestamp DESC LIMIT $2', [propertyId, limit]);
+ var sql = 'SELECT timestamp, coalesce(value_numeric::text, value_text) AS value FROM value WHERE property=$1';
+ var args = [propertyId];
+
+ sql += ' ORDER BY timestamp DESC';
+
+ if (limit) {
+ args.push(limit);
+ sql += ' LIMIT $' + args.length;
+ }
+
+ return tx.manyOrNone(sql, args);
+ }
+
+ function aggregateValuesByPropertyId(propertyId, level, from, to) {
+ var sql, args = [level, as.date(from), as.date(to), propertyId];
+
+ if (level == 'hour') {
+ // TODO: use correct table instead of querying raw table
+ } else {
+ throw 'Unsupported level: ' + level;
+ }
+
+ sql = 'with g as (select * from generate_series($2::timestamp, $3::timestamp, (\'1 \' || $1)::interval) as times(ts)),\n' +
+ 'v as (select\n' +
+ ' date_trunc($1, timestamp) as ts,\n' +
+ ' count(timestamp)::real as count,\n' +
+ ' min(value_numeric)::real as min,\n' +
+ ' max(value_numeric)::real as max,\n' +
+ ' avg(value_numeric)::real as avg\n' +
+ 'FROM value\n' +
+ 'WHERE timestamp >= $2::timestamp\n' +
+ ' AND timestamp < $3::timestamp\n' +
+ ' AND property=$4\n' +
+ ' AND value_numeric is not null\n' +
+ ' GROUP BY date_trunc($1, timestamp)\n' +
+ ')\n' +
+ 'select g.ts as timestamp, v.count, v.min, v.max, v.avg\n' +
+ 'from g left outer join v on g.ts = v.ts\n' +
+ 'order by 1';
+
+ return tx.manyOrNone(sql, args);
}
function insertValue(propertyId, timestamp, value) {
@@ -234,6 +273,7 @@ function DillerDao(tx) {
updateProperty: updateProperty,
valuesByPropertyId: valuesByPropertyId,
+ aggregateValuesByPropertyId: aggregateValuesByPropertyId,
insertValue: insertValue,
updateHourAggregatesForProperty: updateHourAggregatesForProperty,
updateMinuteAggregatesForProperty: updateMinuteAggregatesForProperty
diff --git a/src/DillerTx.js b/src/DillerTx.js
index 0f6b2ea..f1caa09 100644
--- a/src/DillerTx.js
+++ b/src/DillerTx.js
@@ -46,7 +46,7 @@ function _DillerTx(config) {
var con = pgp(config.postgresqlConfig);
return con.tx(function (pg) {
- var dao = new _DillerDao(con);
+ var dao = new _DillerDao(con, pgp.as);
var diller = new _Diller(config, con, dao);
return action(pg, dao, diller)
});
diff --git a/src/types.js b/src/types.js
index 8c6300c..9856036 100644
--- a/src/types.js
+++ b/src/types.js
@@ -74,6 +74,7 @@ var HttpReq = {};
HttpReq.prototype.body = {};
HttpReq.prototype.headers = {};
HttpReq.prototype.params = {};
+HttpReq.prototype.query = {};
var HttpRes = {};
/**
diff --git a/src/web/DillerWeb.js b/src/web/DillerWeb.js
index 396c2f8..b42e47e 100644
--- a/src/web/DillerWeb.js
+++ b/src/web/DillerWeb.js
@@ -1,8 +1,11 @@
var express = require('express');
+var moment = require('moment');
var bodyParser = require('body-parser');
var _ = require('lodash');
var di = require('di');
+var isoFormat = 'YYYY-MM-DDTHH:mm:ss';
+
/**
* @param {DillerConfig} config
* @param {DillerMqttClient} mqttClient
@@ -105,14 +108,33 @@ function DillerWeb(config, mqttClient, tx) {
* @param {HttpRes} res
*/
function getValues(req, res) {
+ var from = req.query.from && moment(req.query.from, isoFormat);
+ var to = req.query.to && moment(req.query.to, isoFormat);
+ var limit = req.query.limit || 10;
+
tx(function (tx, dao) {
- var propertyId = req.params.propertyId;
- return dao.valuesByPropertyId(propertyId, 10);
+ var propertyId = Number(req.params.propertyId);
+
+ if (from || to) {
+ from = (from || moment()).startOf('hour');
+ to = (to || moment()).startOf('hour');
+
+ if (typeof propertyId !== 'number' || !from.isValid() || !to.isValid()) {
+ log.info('getValues: Invalid parameters: propertyId', propertyId, 'from', from.toISOString(), 'to', to.toISOString());
+ return Promise.reject('Invalid parameters: ' + typeof propertyId);
+ } else {
+ log.info('getValues: propertyId', propertyId, 'from', from.toISOString(), 'to', to.toISOString());
+
+ return dao.aggregateValuesByPropertyId(propertyId, 'hour', from.toDate(), to.toDate());
+ }
+ } else {
+ return dao.valuesByPropertyId(propertyId, limit);
+ }
}).then(function (values) {
res.json({values: values});
}, function (err) {
log.warn('fail', err);
- res.status(500).json({message: 'fail'});
+ res.status(500).json({message: typeof err === 'string' ? err : 'fail'});
});
}
@@ -254,7 +276,7 @@ function DillerWeb(config, mqttClient, tx) {
s += _.map(calls, function (call) {
- var s = ' function ' + call.name + '(' + call.keys.join(', ') + ') {\n' +
+ var s = ' function ' + call.name + 'Req(' + call.keys.join(', ') + ') {\n' +
' var req = {};\n' +
' req.method = \'' + call.method + '\';\n' +
' req.url = baseUrl + \'/api' + call.path + '\';\n';
@@ -268,6 +290,12 @@ function DillerWeb(config, mqttClient, tx) {
}
s +=
+ ' return req;\n' +
+ ' }\n' +
+ '\n';
+
+ s += ' function ' + call.name + '(' + call.keys.join(', ') + ') {\n' +
+ ' var req = ' + call.name + 'Req(' + call.keys.join(', ') + ');\n' +
' return $http(req);\n' +
' }\n';
@@ -276,7 +304,7 @@ function DillerWeb(config, mqttClient, tx) {
s += '\n';
s += ' return {\n';
s += _.map(calls, function (call) {
- return ' ' + call.name + ': ' + call.name
+ return ' ' + call.name + 'Req: ' + call.name + 'Req,\n ' + call.name + ': ' + call.name
}).join(',\n');
s += '\n';
s += ' };\n';