X-Git-Url: https://git.toastfreeware.priv.at/chrisu/seepark.git/blobdiff_plain/5bc491bbd95fc6dd6cf6b544d0c0f8ee43862605..35b005a713dfc2160b9b887ab1fa37668a0bb504:/web/seepark_web.py

diff --git a/web/seepark_web.py b/web/seepark_web.py
index 368f208..59e0c8b 100644
--- a/web/seepark_web.py
+++ b/web/seepark_web.py
@@ -59,21 +59,8 @@ class OpenWeatherMap(db.Model):
     __tablename__ = 'openweathermap'
 
 
-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)
-    if sensor_type is not None:
-        query = query.filter(Sensors.value_type == sensor_type)
-    if begin is not None:
-        query = query.filter(Sensors.timestamp >= begin)
-    if end is not None:
-        query = query.filter(Sensors.timestamp <= end)
-    return query.all()
-
-
-def select_sensordata_grouped(sensor_id, sensor_type, begin, end):
-    # determine resolution (interval in seconds for data points)
+def calc_grouping_resolution(begin, end):
+    """How many data points should be between the timestamps begin and end?"""
     # copied from munin/master/_bin/munin-cgi-graph.in
     resolutions = dict(
         day   =   300,
@@ -91,10 +78,31 @@ def select_sensordata_grouped(sensor_id, sensor_type, begin, end):
         resolution = resolutions['month']
     else:
         resolution = resolutions['year']
+    return resolution
+
+
+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)
+    if sensor_type is not None:
+        query = query.filter(Sensors.value_type == sensor_type)
+    if begin is not None:
+        query = query.filter(Sensors.timestamp >= begin)
+    if end is not None:
+        query = query.filter(Sensors.timestamp <= end)
+    return query.all()
+
+
+def select_sensordata_grouped(sensor_id, sensor_type, begin, end):
+    # determine resolution (interval in seconds for data points)
+    resolution = calc_grouping_resolution(begin, end)
 
     # 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),
+    query = db.session.query(func.to_seconds(Sensors.timestamp).op('div')(resolution).label('g'),
+            func.from_unixtime(func.avg(func.unix_timestamp(Sensors.timestamp))).label('timestamp'),
+            func.avg(Sensors.value).label('value'),
             Sensors.sensor_id, Sensors.value_type, Sensors.sensor_name)
     if sensor_id is not None:
         query = query.filter(Sensors.sensor_id == sensor_id)
@@ -106,34 +114,29 @@ def select_sensordata_grouped(sensor_id, sensor_type, begin, end):
     return query.all()
 
 
-def select_openweatherdata(cityid, begin, end, mode):
+def select_openweatherdata(cityid, begin, end):
     query = OpenWeatherMap.query.filter(OpenWeatherMap.cityid == cityid)
     if begin is not None:
         query = query.filter(OpenWeatherMap.datetime >= begin)
     if end is not None:
         query = query.filter(OpenWeatherMap.datetime <= 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;
+    return query.all()
+
+
+def select_openweatherdata_grouped(cityid, begin, end):
+    # determine resolution (interval in seconds for data points)
+    resolution = calc_grouping_resolution(begin, end)
+
+    # 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(OpenWeatherMap.datetime).op('div')(resolution).label('g'),
+            func.from_unixtime(func.avg(func.unix_timestamp(OpenWeatherMap.datetime))).label('datetime'),
+            func.avg(OpenWeatherMap.temp).label('temp'),
+            OpenWeatherMap.cityid)
+    OpenWeatherMap.query.filter(OpenWeatherMap.cityid == cityid)
+    query = query.filter(OpenWeatherMap.datetime >= begin)
+    query = query.filter(OpenWeatherMap.datetime <= end)
+    query = query.group_by('g', OpenWeatherMap.cityid)
     return query.all()
 
 
@@ -186,7 +189,14 @@ def openweathermapdata(cityid):
     mode = request.args.get('mode', 'full')
     format = request.args.get('format', 'default')
 
-    result = select_openweatherdata(cityid, begin, end, mode)
+    if mode == 'full':
+        result = select_openweatherdata(cityid, 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_openweatherdata_grouped(cityid, begin, end)
+    else:
+        abort(Response('unknown value for mode', 400))
 
     if format == 'c3':
         return convert_to_c3(result, 'cityid', 'datetime', 'temp')