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.

15 files changed, 397 insertions, 34 deletions

diff --git a/bower.json b/bower.json
index 60b30ff..7a91f98 100644
--- a/bower.json
+++ b/bower.json
@@ -19,6 +19,7 @@
     "angular-route": "~1.4.7",
     "bootstrap": "4.0.0-alpha",
     "angular-bootstrap": "~0.14.3",
-    "font-awesome": "fontawesome#~4.4.0"
+    "font-awesome": "fontawesome#~4.4.0",
+    "chartist": "^0.9.7"
   }
 }
diff --git a/database.json b/database.json
index b37dd20..0bdd83c 100644
--- a/database.json
+++ b/database.json
@@ -7,4 +7,4 @@
     "database": "diller"
   },
   "sql-file": true
-}
\ No newline at end of file
+}
diff --git a/package.json b/package.json
index 2c082af..95e23ce 100644
--- a/package.json
+++ b/package.json
@@ -16,6 +16,7 @@
     "express": "^4.13.3",
     "jade": "^1.11.0",
     "lodash": "^3.10.1",
+    "moment": "^2.11.2",
     "mqtt": "^1.4.3",
     "pg": "^4.4.2",
     "pg-promise": "^2.0.12"
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';
diff --git a/web/static/app/DillerRpc.js b/web/static/app/DillerRpc.js
index f865dc5..eddbbf9 100644
--- a/web/static/app/DillerRpc.js
+++ b/web/static/app/DillerRpc.js
@@ -1,52 +1,82 @@
 function DillerRpc($http, DillerConfig) {
   var baseUrl = DillerConfig.baseUrl;
 
-  function getDevices() {
+  function getDevicesReq() {
     var req = {};
     req.method = 'get';
     req.url = baseUrl + '/api/device';
+    return req;
+  }
+
+  function getDevices() {
+    var req = getDevicesReq();
     return $http(req);
   }
 
-  function getDevice(deviceId) {
+  function getDeviceReq(deviceId) {
     var req = {};
     req.method = 'get';
     req.url = baseUrl + '/api/device/:deviceId';
     req.url = req.url.replace(/:deviceId/, deviceId);
+    return req;
+  }
+
+  function getDevice(deviceId) {
+    var req = getDeviceReq(deviceId);
     return $http(req);
   }
 
-  function patchDevice(deviceId, payload) {
+  function patchDeviceReq(deviceId, payload) {
     var req = {};
     req.method = 'patch';
     req.url = baseUrl + '/api/device/:deviceId';
     req.url = req.url.replace(/:deviceId/, deviceId);
     req.data = payload;
+    return req;
+  }
+
+  function patchDevice(deviceId, payload) {
+    var req = patchDeviceReq(deviceId, payload);
     return $http(req);
   }
 
-  function getValues(propertyId) {
+  function getValuesReq(propertyId) {
     var req = {};
     req.method = 'get';
     req.url = baseUrl + '/api/property/:propertyId/values';
     req.url = req.url.replace(/:propertyId/, propertyId);
+    return req;
+  }
+
+  function getValues(propertyId) {
+    var req = getValuesReq(propertyId);
     return $http(req);
   }
 
-  function patchProperty(propertyId, payload) {
+  function patchPropertyReq(propertyId, payload) {
     var req = {};
     req.method = 'patch';
     req.url = baseUrl + '/api/property/:propertyId/values';
     req.url = req.url.replace(/:propertyId/, propertyId);
     req.data = payload;
+    return req;
+  }
+
+  function patchProperty(propertyId, payload) {
+    var req = patchPropertyReq(propertyId, payload);
     return $http(req);
   }
 
   return {
+    getDevicesReq: getDevicesReq,
     getDevices: getDevices,
+    getDeviceReq: getDeviceReq,
     getDevice: getDevice,
+    patchDeviceReq: patchDeviceReq,
     patchDevice: patchDevice,
+    getValuesReq: getValuesReq,
     getValues: getValues,
+    patchPropertyReq: patchPropertyReq,
     patchProperty: patchProperty
   };
 }
diff --git a/web/static/app/diller/client.js b/web/static/app/diller/client.js
new file mode 100644
index 0000000..6641ffb
--- /dev/null
+++ b/web/static/app/diller/client.js
@@ -0,0 +1,72 @@
+(function () {
+  function extractData(res) {
+    return res && res.data;
+  }
+
+  function Property($http, DillerRpc, propertyId) {
+    function getInterval(interval) {
+
+      // moment().subtract(24, 'hour')
+      var req = DillerRpc.getValuesReq(propertyId);
+      req.params = {
+        from: interval.getFrom().toISOString()
+      };
+      return $http(req).then(extractData);
+    }
+
+    /** @lends Property.prototype */
+    return {
+      getInterval: getInterval
+    };
+  }
+
+  function Device($http, DillerRpc, deviceId) {
+    var properties = {};
+
+    /**
+     * @param propertyId
+     * @returns {Property}
+     */
+    function getProperty(propertyId) {
+      var p = properties[propertyId];
+
+      if (!p) {
+        p = new Property($http, DillerRpc, propertyId);
+        properties[propertyId] = p;
+      }
+
+      return p;
+    }
+
+    /** @lends Device.prototype */
+    return {
+      getProperty: getProperty
+    };
+  }
+
+  function DillerClient($timeout, $http, DillerRpc) {
+
+    var devices = {};
+
+    function getDevice(deviceId) {
+      var d = devices[deviceId];
+
+      if (!d) {
+        d = new Device($http, DillerRpc, deviceId);
+        devices[deviceId] = d;
+      }
+
+      return d;
+    }
+
+    /** @lends DillerClient.prototype */
+    return {
+      getDevice: getDevice
+    };
+  }
+
+  angular
+    .module('diller.client', [])
+    .service('DillerClient', DillerClient)
+    .service('DillerRpc', window.DillerRpc);
+})();
diff --git a/web/static/app/diller/global.js b/web/static/app/diller/global.js
new file mode 100644
index 0000000..0da8da7
--- /dev/null
+++ b/web/static/app/diller/global.js
@@ -0,0 +1,60 @@
+(function () {
+  var Diller = window.Diller = window.Diller || {};
+
+  function toDate(unknown) {
+    if (!unknown) {
+      return undefined;
+    } else if (moment.isMoment(unknown)) {
+      return unknown;
+    } else if (typeof unknown === 'string') {
+      unknown = unknown.trim();
+
+      if (unknown == '') {
+        return moment();
+      }
+
+      return moment(unknown, 'YYYY-MM-DDTHH:mm.sssZ');
+    } else if (typeof unknown === 'string' || moment.isDate(unknown)) {
+      return moment(unknown);
+    } else {
+      return undefined;
+    }
+  }
+
+  Diller.Interval = function (from, to) {
+    var f = toDate(from),
+      t = toDate(to);
+
+    if (f.isAfter(t)) {
+      var tmp = f;
+      f = t;
+      t = tmp;
+    }
+
+    return {
+      getFrom: function () {
+        return f || moment();
+      },
+      getTo: function () {
+        return t || moment();
+      },
+      toString: function () {
+        return 'yo'
+      }
+    };
+  };
+
+  Diller.Interval.create = function (value) {
+    if (value instanceof Diller.Interval) {
+      return value;
+    }
+
+    return new Diller.Interval.hours(24);
+  };
+
+  Diller.Interval.hours = function (hours) {
+    var to = moment();
+    var from = to.subtract(hours, 'hours');
+    return new Diller.Interval(from, to);
+  };
+})();
diff --git a/web/static/app/diller/line-chart.js b/web/static/app/diller/line-chart.js
new file mode 100644
index 0000000..69b6f77
--- /dev/null
+++ b/web/static/app/diller/line-chart.js
@@ -0,0 +1,62 @@
+(function () {
+
+  var isoFormat = 'YYYY-MM-DDTHH:mm:ss';
+
+  function DlLineChartDirective($timeout, DillerClient) {
+    var id_seq = 0;
+
+    return {
+      restrict: 'E',
+      scope: {
+        device: '=',
+        property: '=',
+        value: '=',
+        interval: '='
+      },
+      replace: true,
+      template: '<div/>',
+      link: function (scope, element, attrs) {
+        var elementId = element.attr('id');
+        if (!elementId) {
+          elementId = 'dl-line-chart-' + id_seq++;
+          element.attr('id', elementId);
+        }
+
+        var deviceId = scope.device;
+        var propertyId = scope.property;
+        var interval = Diller.Interval.create(scope.interval);
+        var property = DillerClient.getDevice(deviceId).getProperty(propertyId);
+
+        var options = {
+          axisX: {
+            showLabel: true,
+            showGrid: true,
+            labelInterpolationFnc: function (value, index) {
+              return index % 4 === 0 ? value.format('HH:mm') : null;
+            }
+          }
+        };
+
+        property.getInterval(interval).then(function (data) {
+          var avgs = _.pluck(data.values, 'avg');
+          var timestamps = _.map(data.values, function (row) {
+            return moment(row.timestamp, isoFormat);
+          });
+
+          var chartData = {
+            labels: timestamps,
+            series: [
+              avgs
+            ]
+          };
+
+          var chart = new Chartist.Line('#' + elementId, chartData, options);
+        });
+      }
+    };
+  }
+
+  angular
+    .module('diller.line-chart', ['diller.client'])
+    .directive('dlLineChart', DlLineChartDirective);
+})();
diff --git a/web/static/app/app.js b/web/static/app/diller/web.js
index 22c7f83..4b173ad 100644
--- a/web/static/app/app.js
+++ b/web/static/app/diller/web.js
@@ -1,4 +1,6 @@
 (function () {
+  var isoFormat = 'YYYY-MM-DDTHH:mm:ss';
+
   function FrontPageController(devices) {
     var ctrl = this;
 
@@ -87,7 +89,63 @@
         bindToController: true,
         templateUrl: 'app/templates/edit-attribute.modal.html'
       });
+    };
+
+    ctrl.interval = Diller.Interval.hours(5);
+
+    // ctrl.values24h = values24h.data.values;
+
+    function chartist() {
+      var avgs = _.pluck(ctrl.values24h, 'avg');
+      var timestamps = _.map(ctrl.values24h, function(row) {
+        var m = moment(row.timestamp, isoFormat);
+        return m.format('HH:mm');
+      });
+
+      var data = {
+        labels: timestamps,
+        series: [
+          avgs
+        ]
+      };
+
+      var options = {
+        axisX: {
+          showLabel: false,
+          showGrid: false
+        }
+      };
+
+//      options.lineSmooth = Chartist.Interpolation.cardinal({
+//        fillHoles: true,
+//      })
+
+      options.axisX = {
+        showLabel: true,
+        showGrid: true,
+        labelInterpolationFnc: function(value, index) {
+          return index % 4 === 0 ? value : null;
+        }
+      };
+
+      var chartData = {
+        series: [[]],
+        labels: []
+      };
+
+      // var chart = new Chartist.Line('#values-chart', chartData, options);
+      // console.log('chart', chart);
+/*
+      $timeout(function() {
+        chartData = data;
+        // console.log(data);
+        var chart = new Chartist.Line('#values-chart', chartData, options);
+        console.log('chart', chart);
+      });
+*/
     }
+
+    // chartist();
   }
 
   function TimestampFilter() {
@@ -168,12 +226,11 @@
   }
 
   angular
-    .module('Diller', ['ngRoute', 'ui.bootstrap'])
+    .module('diller.web', ['ngRoute', 'ui.bootstrap', 'diller.line-chart'])
     .config(config)
     .run(run)
     .filter('timestamp', TimestampFilter)
     .directive('dlTimestamp', DlTimestampDirective)
     .directive('dlDots', DlDotsDirective)
-    .service('DillerConfig', DillerConfig)
-    .service('DillerRpc', DillerRpc);
+    .service('DillerConfig', DillerConfig);
 })();
diff --git a/web/static/app/templates/property.html b/web/static/app/templates/property.html
index 969734b..3f3424f 100644
--- a/web/static/app/templates/property.html
+++ b/web/static/app/templates/property.html
@@ -20,20 +20,25 @@
     </a>
   </p>
 
-  <dl>
-    <dt class="col-sm-3">Key</dt>
-    <dd class="col-sm-9">
-      {{ctrl.property.key}}
-      &nbsp;
-    </dd>
+  <div class="row">
+    <dl>
+      <dt class="col-sm-3">Key</dt>
+      <dd class="col-sm-9">
+        {{ctrl.property.key}}
+        &nbsp;
+      </dd>
 
-    <dt class="col-sm-3">Created</dt>
-    <dd class="col-sm-9">
-      {{ctrl.property.created_timestamp | date:'medium'}}
-      &nbsp;
-    </dd>
-  </dl>
+      <dt class="col-sm-3">Created</dt>
+      <dd class="col-sm-9">
+        {{ctrl.property.created_timestamp | date:'medium'}}
+        &nbsp;
+      </dd>
+    </dl>
+  </div>
 
+  <dl-line-chart class="ct-golden" device="ctrl.device.id" property="ctrl.property.id" interval="ctrl.interval"></dl-line-chart>
+
+  <!--
   <h3>
     Latest Values
     <small ng-hide="ctrl.loading">
@@ -68,5 +73,6 @@
     </tr>
     </tbody>
   </table>
+  -->
 
 </div>
diff --git a/web/templates/index.jade b/web/templates/index.jade
index 1b2adbc..84c31dc 100644
--- a/web/templates/index.jade
+++ b/web/templates/index.jade
@@ -17,14 +17,19 @@ html(lang='en')
     script(src="bower_components/angular-bootstrap/ui-bootstrap-tpls.js", type="application/javascript")
     script(src="bower_components/lodash/lodash.js", type="application/javascript")
     script(src="bower_components/moment/moment.js", type="application/javascript")
+    script(src="bower_components/chartist/dist/chartist.js", type="application/javascript")
 
     script(src="app/DillerRpc.js", type="application/javascript")
-    script(src="app/app.js", type="application/javascript")
+    script(src="app/diller/global.js", type="application/javascript")
+    script(src="app/diller/client.js", type="application/javascript")
+    script(src="app/diller/web.js", type="application/javascript")
+    script(src="app/diller/line-chart.js", type="application/javascript")
     link(href="app/app.css", rel="stylesheet")
 
     link(rel="stylesheet", href="bower_components/font-awesome/css/font-awesome.min.css")
+    link(rel="stylesheet", href="bower_components/chartist/dist/chartist.css")
 
-  body(ng-app="Diller")
+  body(ng-app="diller.web")
     .container
       nav.navbar.navbar-dark.bg-inverse
         a.navbar-brand(href='#/') Diller
diff --git a/web/templates/wat.html b/web/templates/wat.html
index fd504a7..0e77077 100644
--- a/web/templates/wat.html
+++ b/web/templates/wat.html
@@ -17,7 +17,7 @@
   <script src="./bower_components/lodash/lodash.js" type="application/javascript"></script>
 
   <script src="../static/app/DillerRpc.js" type="application/javascript"></script>
-  <script src="../static/app/app.js" type="application/javascript"></script>
+  <script src="../static/app/diller/app.js" type="application/javascript"></script>
 </head>
 <body ng-app="Diller" ng-view>
 Loading Diller ...