From 7ca173de3de046501d79164da0c8c8871a03089b Mon Sep 17 00:00:00 2001
From: Trygve Laugstøl <trygvis@inamo.no>
Date: Sat, 5 Mar 2016 16:47:32 +0100
Subject: 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.

---
 src/DillerDao.js | 46 +++++++++++++++++++++++++++++++++++++++++++---
 1 file changed, 43 insertions(+), 3 deletions(-)

(limited to 'src/DillerDao.js')

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
-- 
cgit v1.2.3