From b56acfca2dfc336eec6591fab809292e226ed09e Mon Sep 17 00:00:00 2001
From: Philipp Spitzer <philipp@spitzer.priv.at>
Date: Wed, 22 Aug 2018 22:17:17 +0200
Subject: [PATCH] Split select_sensordata and implement grouping.

---
 web/seepark_web.py | 69 +++++++++++++++++++++++++++++-----------------
 1 file changed, 44 insertions(+), 25 deletions(-)

diff --git a/web/seepark_web.py b/web/seepark_web.py
index 9871b04..238be54 100644
--- a/web/seepark_web.py
+++ b/web/seepark_web.py
@@ -7,6 +7,7 @@ from collections import defaultdict
 from flask import Flask, render_template, jsonify, request, abort, Response
 import flask.json
 from flask_sqlalchemy import SQLAlchemy, inspect
+from sqlalchemy import func
 
 
 # https://stackoverflow.com/a/37350445
@@ -58,7 +59,7 @@ class OpenWeatherMap(db.Model):
     __tablename__ = 'openweathermap'
 
 
-def select_sensordata(sensor_id, sensor_type, begin, end, mode):
+def select_sensordata(sensor_id, sensor_type, begin, end):
     query = Sensors.query
     if sensor_id is not None:
         query = query.filter(Sensors.sensor_id == sensor_id)
@@ -68,29 +69,40 @@ def select_sensordata(sensor_id, sensor_type, begin, end, mode):
         query = query.filter(Sensors.timestamp >= begin)
     if end is not None:
         query = query.filter(Sensors.timestamp <= end)
-    if mode == 'consolidated' and begin is not None and end is not None:
-        # copied from munin/master/_bin/munin-cgi-graph.in
-        # interval in seconds for data points
-        resolutions = dict(
-            day   =   300,
-            week  =  1800,
-            month =  7200,
-            year  = 86400,
-        )
-        duration = (end - begin).total_seconds()
-        day = 60 * 60 * 24
-        if duration <= day:
-            resolution = resolutions['day']
-        elif duration <= 7 * day:
-            resolution = resolutions['week']
-        elif duration <= 31 * day:
-            resolution = resolutions['month']
-        else:
-            resolution = resolutions['year']
-        # TODO: filter out samples from 'result'
-        # something like
-        # select to_seconds(datetime) DIV (60*60*24) as interval_id, min(datetime), max(datetime), min(temp), avg(temp), max(temp), count(temp) from openweathermap group by interval_id order by interval_id;
-        # seepark_web.db.session.query(func.to_seconds(Sensors.timestamp).op('div')(60*60*24).label('g'), func.min(Sensors.timestamp), func.min(Sensors.value)).group_by('g').all()
+    return query.all()
+
+
+def select_sensordata_grouped(sensor_id, sensor_type, begin, end):
+    # determine resolution (interval in seconds for data points)
+    # copied from munin/master/_bin/munin-cgi-graph.in
+    resolutions = dict(
+        day   =   300,
+        week  =  1800,
+        month =  7200,
+        year  = 86400,
+    )
+    duration = (end - begin).total_seconds()
+    day = 60 * 60 * 24
+    if duration <= day:
+        resolution = resolutions['day']
+    elif duration <= 7 * day:
+        resolution = resolutions['week']
+    elif duration <= 31 * day:
+        resolution = resolutions['month']
+    else:
+        resolution = resolutions['year']
+
+    # Let the database do the grouping. Example in SQL (MySQL):
+    # select to_seconds(datetime) DIV (60*60*24) as interval_id, min(datetime), max(datetime), min(temp), avg(temp), max(temp), count(temp) from openweathermap group by interval_id order by interval_id;
+    query = db.session.query(func.to_seconds(Sensors.timestamp).op('div')(resolution).label('g'), func.min(Sensors.timestamp), func.mean(Sensors.value),
+            Sensors.sensor_id, Sensors.value_type, Sensors.sensor_name)
+    if sensor_id is not None:
+        query = query.filter(Sensors.sensor_id == sensor_id)
+    if sensor_type is not None:
+        query = query.filter(Sensors.value_type == sensor_type)
+    query = query.filter(Sensors.timestamp >= begin)
+    query = query.filter(Sensors.timestamp <= end)
+    query = query.group_by('g', Sensors.sensor_id, Sensors.value_type, Sensors.sensor_name)
     return query.all()
 
 
@@ -154,7 +166,14 @@ def sensordata(sensor_id=None, sensor_type=None):
     mode = request.args.get('mode', 'full')
     format = request.args.get('format', 'default')
 
-    result = select_sensordata(sensor_id, sensor_type, begin, end, mode)
+    if mode == 'full':
+        result = select_sensordata(sensor_id, sensor_type, begin, end)
+    elif mode == 'consolidated':
+        if begin is None or end is None:
+            abort(Response('begin and end have to be set for mode==consolidated', 400))
+        result = select_sensordata_grouped(sensor_id, sensor_type, begin, end)
+    else:
+        abort(Response('unknown value for mode', 400))
 
     if format == 'c3':
         return convert_to_c3(result, 'sensor_id', 'timestamp', 'value')
-- 
2.47.3