Now the generated PDF contains only 1 page and in A4 landscape mode.

[chrisu/seepark.git] / web / seepark_web.py

diff --git a/web/seepark_web.py b/web/seepark_web.py
index a6d39302553bcd17f052b6cbde606473a81b9fd0..b899a1730ebdfaea506fd652c7993f0d3b2d2779 100644 (file)
--- a/web/seepark_web.py
+++ b/web/seepark_web.py
@@ -1,19 +1,43 @@
-from random import uniform

 import datetime
 import time
 import configparser
 import os
 import sys
 from collections import defaultdict
 import datetime
 import time
 import configparser
 import os
 import sys
 from collections import defaultdict

-from flask import Flask, render_template, jsonify, request, abort, Response
+import io
+import numpy as np
+import matplotlib
+matplotlib.use('pdf')
+import matplotlib.pyplot as plt
+from matplotlib.backends.backend_pdf import PdfPages
+
+from flask import Flask, render_template, jsonify, request, abort, Response, make_response

 import flask.json
 from flask_sqlalchemy import SQLAlchemy, inspect
 import flask.json
 from flask_sqlalchemy import SQLAlchemy, inspect

-
-
-app_path = os.path.dirname(os.path.realpath(__file__))
-lib_path = os.path.join(app_path, '..')
-sys.path.append(lib_path)
-from seeparklib.openweathermap import openweathermap_json, OpenWeatherMapError
+from sqlalchemy import func
+
+MONTH_DE = [
+    'Jänner',
+    'Februar',
+    'März',
+    'April',
+    'Mai',
+    'Juni',
+    'Juli',
+    'August',
+    'September',
+    'Oktober',
+    'November',
+    'Dezember']
+
+DAY_OF_WEEK_DE = [
+    'Montag',
+    'Dienstag',
+    'Mittwoch',
+    'Donnerstag',
+    'Freitag',
+    'Samstag',
+    'Sonntag']

 
 
 # https://stackoverflow.com/a/37350445
 
 
 # https://stackoverflow.com/a/37350445


@@ -47,7 +71,7 @@ config = configparser.ConfigParser()
 config.read(os.environ['SEEPARKINI'])
 apikey = config.get('openweathermap', 'apikey')
 cityid = config.get('openweathermap', 'cityid')
 config.read(os.environ['SEEPARKINI'])
 apikey = config.get('openweathermap', 'apikey')
 cityid = config.get('openweathermap', 'cityid')

-mainsensor = config.get('temperature', 'mainsensor')
+mainsensor = config.get('webapp', 'mainsensor')

 
 app = Flask(__name__)
 app.json_encoder = JSONEncoder
 
 app = Flask(__name__)
 app.json_encoder = JSONEncoder


@@ -65,7 +89,30 @@ class OpenWeatherMap(db.Model):
     __tablename__ = 'openweathermap'
 
 
     __tablename__ = 'openweathermap'
 
 

-def select_sensordata(sensor_id, sensor_type, begin, end, mode):
+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
+    # except day: 300 -> 600
+    resolutions = dict(
+        day   =   600,
+        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']
+    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)
     query = Sensors.query
     if sensor_id is not None:
         query = query.filter(Sensors.sensor_id == sensor_id)


@@ -75,68 +122,61 @@ 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)
         query = query.filter(Sensors.timestamp >= begin)
     if end is not None:
         query = query.filter(Sensors.timestamp <= end)

-    if mode == 'consolidated' and begin is None and end is 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()
 
 
     return query.all()
 
 

-def select_openweatherdata(cityid, begin, end, mode):
+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.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)
+    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()
+
+
+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)
     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 None and end is 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()
 
 
     return query.all()
 
 

-def convert_to_c3(result):
+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()
+
+
+def convert_to_c3(result, id, field_x, field_y):

     c3result = defaultdict(list)
     for row in result:
     c3result = defaultdict(list)
     for row in result:

-        c3result[row.sensor_id].append(row.value)
-        dt = row.timestamp.strftime('%Y-%m-%d %H:%M:%S')
-        c3result[row.sensor_id + '_x'].append(dt)
+        c3result[str(getattr(row, id))].append(getattr(row, field_y))
+        dt = getattr(row, field_x).strftime('%Y-%m-%d %H:%M:%S')
+        c3result[str(getattr(row, id)) + '_x'].append(dt)

     return c3result
 
 
     return c3result
 
 


@@ -160,10 +200,17 @@ def sensordata(sensor_id=None, sensor_type=None):
     mode = request.args.get('mode', 'full')
     format = request.args.get('format', 'default')
 
     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':
 
     if format == 'c3':

-        return convert_to_c3(result)
+        return convert_to_c3(result, 'sensor_id', 'timestamp', 'value')

     return result
 
 
     return result
 
 


@@ -173,10 +220,17 @@ def openweathermapdata(cityid):
     mode = request.args.get('mode', 'full')
     format = request.args.get('format', 'default')
 
     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':
 
     if format == 'c3':

-        return convert_to_c3(result)
+        return convert_to_c3(result, 'cityid', 'datetime', 'temp')

     return result
 
 
     return result
 
 


@@ -190,6 +244,10 @@ def currentwatertemperature(sensorid):
     return result.value, result.timestamp
 
 
     return result.value, result.timestamp
 
 

+def add_month(date):
+    return (date + datetime.timedelta(days=42)).replace(day=1)
+
+

 @app.route('/api/<version>/sensors/')
 def sensors(version):
     """List all sensors found in the database"""
 @app.route('/api/<version>/sensors/')
 def sensors(version):
     """List all sensors found in the database"""


@@ -239,34 +297,87 @@ def openweathermap_city(version, cityid):
     return jsonify(result)
 
 
     return jsonify(result)
 
 

-@app.route('/data/', defaults={'timespan': 1})
-@app.route("/data/<int:timespan>", methods=['GET'])
-def data(timespan):
-    granularity = 5 * timespan               # (every) minute(s) per day
-    samples = 60/granularity * 24 * timespan # per hour over whole timespan
-    s4m   = []
-    s4m_x = []
-    s5m   = []
-    s5m_x = []
-    end   = time.time()
-    start = end - samples * granularity * 60
-
-    for i in range(int(samples)):
-        s4m.append(uniform(-10,30))
-        s5m.append(uniform(-10,30))
-        s4mt = uniform(start, end)
-        s5mt = uniform(start, end)
-        s4m_x.append(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(s4mt)))
-        s5m_x.append(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(s5mt)))
-
-    data = {
-        '0316a2193bff':   s4m,
-        '0316a2193bff_x': s4m_x,
-        '0316a21383ff':   s5m,
-        '0316a21383ff_x': s5m_x,
-        }
-
-    return jsonify(data)
+@app.route('/api/<version>/currentairtemperature')
+def currentair(version):
+    value, timestamp = currentairtemperature(cityid)
+    return jsonify({"value": value, "timestamp": timestamp})
+
+
+@app.route('/api/<version>/currentwatertemperature')
+def currentwater(version):
+    value, timestamp = currentwatertemperature(mainsensor)
+    return jsonify({"value": value, "timestamp": timestamp})
+
+
+@app.route('/report/<int:year>-<int:month>')
+def report(year, month):
+
+    begin = datetime.datetime(year, month, 1)
+    end = add_month(begin)
+    data = list(select_sensordata(mainsensor, 'Wassertemperatur', begin, end))
+    x = np.array([d.timestamp for d in data])
+    y = np.array([d.value for d in data])
+
+    days_datetime = []
+    d = begin
+    while d < end:
+        days_datetime.append(d)
+        d = d + datetime.timedelta(1)
+
+    binary_pdf = io.BytesIO()
+    with PdfPages(binary_pdf) as pdf:
+        a4 = (29.7/2.54, 21./2.54)
+        title = 'Seepark Wassertemperatur {} {}'.format(MONTH_DE[begin.month-1], begin.year)
+        report_times = [datetime.time(10), datetime.time(15)]
+
+        # graphic
+        plt.figure(figsize=a4)
+        plt.plot(x, y)
+        plt.xticks(days_datetime, [''] * len(days_datetime))
+        plt.ylabel('Temperatur in °C')
+        plt.axis(xmin=begin, xmax=end)
+        plt.grid()
+        plt.title(title)
+
+        # table
+        columns = []
+        for d in days_datetime:
+            columns.append('{}.'.format(d.day))
+        rows = []
+        for t in report_times:
+            rows.append('Wasser {:02d}:{:02d} °C'.format(t.hour, t.minute))
+        cells = []
+        for t in report_times:
+            columns.append('{}.'.format(d.day))
+            row_cells = []
+            for d in days_datetime:
+                report_datetime = datetime.datetime.combine(d.date(), t)
+                closest_index = np.argmin(np.abs(x - report_datetime))
+                if abs(x[closest_index] - report_datetime) > datetime.timedelta(hours=1):
+                    cell = 'N/A'
+                else:
+                    value = y[closest_index]
+                    cell = '{:.1f}'.format(value)
+                row_cells.append(cell)
+            cells.append(row_cells)
+        table = plt.table(cellText=cells, colLabels=columns, rowLabels=rows, loc='bottom')
+        table.scale(xscale=1, yscale=2)
+        plt.title(title)
+        plt.subplots_adjust(left=0.15, right=0.97, bottom=0.3)  # do not cut row labels
+        pdf.savefig()
+
+        pdf_info = pdf.infodict()
+        pdf_info['Title'] = title
+        pdf_info['Author'] = 'Chrisu Jähnl'
+        pdf_info['Subject'] = 'Wassertemperatur'
+        pdf_info['Keywords'] = 'Seepark Wassertemperatur'
+        pdf_info['CreationDate'] = datetime.datetime.now()
+        pdf_info['ModDate'] = datetime.datetime.today()
+
+    response = make_response(binary_pdf.getvalue())
+    response.headers['Content-Type'] = 'application/pdf'
+    response.headers['Content-Disposition'] = 'attachment; filename=seepark_{:04d}-{:02d}.pdf'.format(year, month)
+    return response

 
 
 @app.route("/")
 
 
 @app.route("/")