diff options
| -rw-r--r-- | bower.json | 3 | ||||
| -rw-r--r-- | database.json | 2 | ||||
| -rw-r--r-- | package.json | 1 | ||||
| -rw-r--r-- | src/DillerDao.js | 46 | ||||
| -rw-r--r-- | src/DillerTx.js | 2 | ||||
| -rw-r--r-- | src/types.js | 1 | ||||
| -rw-r--r-- | src/web/DillerWeb.js | 38 | ||||
| -rw-r--r-- | web/static/app/DillerRpc.js | 40 | ||||
| -rw-r--r-- | web/static/app/diller/client.js | 72 | ||||
| -rw-r--r-- | web/static/app/diller/global.js | 60 | ||||
| -rw-r--r-- | web/static/app/diller/line-chart.js | 62 | ||||
| -rw-r--r-- | web/static/app/diller/web.js (renamed from web/static/app/app.js) | 63 | ||||
| -rw-r--r-- | web/static/app/templates/property.html | 30 | ||||
| -rw-r--r-- | web/templates/index.jade | 9 | ||||
| -rw-r--r-- | web/templates/wat.html | 2 |
15 files changed, 397 insertions, 34 deletions
@@ -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}} - - </dd> + <div class="row"> + <dl> + <dt class="col-sm-3">Key</dt> + <dd class="col-sm-9"> + {{ctrl.property.key}} + + </dd> - <dt class="col-sm-3">Created</dt> - <dd class="col-sm-9"> - {{ctrl.property.created_timestamp | date:'medium'}} - - </dd> - </dl> + <dt class="col-sm-3">Created</dt> + <dd class="col-sm-9"> + {{ctrl.property.created_timestamp | date:'medium'}} + + </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 ... |