web:

o Adding an API method to get per-hour aggregate values. Doesn't use the by_hour table yet.
o Adding a simple line graph component that can graph a single property's value.

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';