X-Git-Url: https://git.toastfreeware.priv.at/chrisu/seepark.git/blobdiff_plain/15e7a24e38086359c94122538a0779fcc2c22727..d782d33862c95ae0fd1c2b0f8cd75a9cbc2af95b:/web/seepark_web.py diff --git a/web/seepark_web.py b/web/seepark_web.py index 912b09a..c6ceffb 100644 --- a/web/seepark_web.py +++ b/web/seepark_web.py @@ -1,18 +1,482 @@ -from flask import Flask, render_template, jsonify +import collections +import datetime +import itertools +import time +import configparser +import os +import sys +from collections import defaultdict +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 +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 +def sqlalchemy_model_to_dict(model): + return {c.key: getattr(model, c.key) + for c in inspect(model).mapper.column_attrs} + + +class JSONEncoder(flask.json.JSONEncoder): + def default(self, object): + if isinstance(object, datetime.datetime): + return object.isoformat() + elif isinstance(object, db.Model): + return sqlalchemy_model_to_dict(object) + return super().default(object) + + +def parse_datetime(date_str): + return datetime.datetime.strptime(date_str, '%Y-%m-%dT%H:%M:%S') + + +def ntimes(it, n): + for v in it: + yield from itertools.repeat(v, n) + + +def get_sqlalchemy_database_uri(config): + user = config.get('database', 'user') + pwd = config.get('database', 'password') + host = config.get('database', 'hostname') + db = config.get('database', 'database') + return 'mysql+mysqldb://{}:{}@{}/{}'.format(user, pwd, host, db) + + +config = configparser.ConfigParser() +config.read(os.environ['SEEPARKINI']) +apikey = config.get('openweathermap', 'apikey') +cityid = config.get('openweathermap', 'cityid') +mainsensor = config.get('webapp', 'mainsensor') + app = Flask(__name__) +app.json_encoder = JSONEncoder +app.config['SQLALCHEMY_DATABASE_URI'] = get_sqlalchemy_database_uri(config) +app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False +db = SQLAlchemy(app) +db.reflect(app=app) + + +class Sensors(db.Model): + __tablename__ = 'sensors' + + +class OpenWeatherMap(db.Model): + __tablename__ = 'openweathermap' + + +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) + 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 sensordata_to_xy(sensordata): + sensordata = list(sensordata) + x = np.array([d.timestamp for d in sensordata]) + y = np.array([d.value for d in sensordata]) + return x, y + + +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) + return query.all() + + +def openweatherdata_to_xy(openweatherdata): + openweatherdata = list(openweatherdata) + x = np.array([d.datetime for d in openweatherdata]) + y = np.array([d.temp for d in openweatherdata]) + return x, y + + +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 estimate_swimmer_count(date): + return date.day + + +def select_swimmerdata(begin, end): + def report_times(begin, end): + d = begin + while d < end: + for t in [10, 15]: + a = datetime.datetime.combine(d.date(), datetime.time(t)) + if a >= d: + yield a + d += datetime.timedelta(days=1) + SwimmerData = collections.namedtuple('SwimmerData', ['datetime', 'count']) + for d in report_times(begin, end): + count = estimate_swimmer_count(d) + yield SwimmerData(d, count) + + +def swimmerdata_to_xy(swimmerdata): + swimmerdata = list(swimmerdata) + x = np.array([d.datetime for d in swimmerdata]) + y = np.array([d.count for d in swimmerdata]) + return x, y + + +def convert_to_c3(result, id, field_x, field_y): + c3result = defaultdict(list) + for row in result: + 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 + + +def request_arg(key, type, default=None): + """Returns the key from the request if available, otherwise the default value. + In case type is provided and the key is present, the value is converted by calling type. + In other words: Reimplement request.args.get but don't return default value if + type raises a ValueError.""" + if key in request.args: + try: + return type(request.args[key]) + except ValueError as e: + abort(Response(str(e), 400)) + else: + return default + + +def sensordata(sensor_id=None, sensor_type=None): + begin = request_arg('begin', parse_datetime) + end = request_arg('end', parse_datetime) + mode = request.args.get('mode', 'full') + format = request.args.get('format', 'default') + + 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') + return result + + +def openweathermapdata(cityid): + begin = request_arg('begin', parse_datetime) + end = request_arg('end', parse_datetime) + mode = request.args.get('mode', 'full') + format = request.args.get('format', 'default') + + 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') + return result + + +def currentairtemperature(cityid): + result = OpenWeatherMap.query.filter_by(cityid=cityid).order_by(OpenWeatherMap.datetime.desc()).first() + return result.temp, result.datetime + + +def currentwatertemperature(sensorid): + result = Sensors.query.filter_by(sensor_id=sensorid).order_by(Sensors.timestamp.desc()).first() + return result.value, result.timestamp + + +def first_of_month(date, month): + date = date.replace(day=1) + if month == 0: + return date + if month == 1: + return (date + datetime.timedelta(days=42)).replace(day=1) + if month == -1: + return (date - datetime.timedelta(days=1)).replace(day=1) + assert False + + +@app.route('/api//sensors/') +def sensors(version): + """List all sensors found in the database""" + result = db.session.query(Sensors.sensor_id, Sensors.sensor_name, Sensors.value_type).distinct().all() + return jsonify(result) + + +@app.route('/api//sensor/id/') +def sensorid(version, sensor_id): + """Return all data for a specific sensor + + URL parameters: + + * ``begin=``, optional, format like ``2018-05-19T21:07:53`` + * ``end=``, optional, format like ``2018-05-19T21:07:53`` + * ``mode=``, optional. return all rows (default) or with lower resolution (for charts) + * ``format=``, optional. return result as returned by sqlalchemy (default) or formatted for c3.js + """ + result = sensordata(sensor_id=sensor_id) + return jsonify(result) + + +@app.route('/api//sensor/type/') +def sensortype(version, sensor_type): + """Return all data for a specific sensor type + + URL parameters: + + * ``begin=``, optional, format like ``2018-05-19T21:07:53`` + * ``end=``, optional, format like ``2018-05-19T21:07:53`` + * ``mode=``, optional. return all rows (default) or with lower resolution (for charts) + * ``format=``, optional. return result as returned by sqlalchemy (default) or formatted for c3.js + """ + result = sensordata(sensor_type=sensor_type) + return jsonify(result) + + +@app.route('/api//openweathermap/cities') +def openweathermap_cities(version): + """List all city IDs found in the database""" + result = db.session.query(OpenWeatherMap.cityid).distinct().all() + return jsonify(result) + + +@app.route('/api//openweathermap/city/') +def openweathermap_city(version, cityid): + """List all data found for a city""" + result = openweathermapdata(cityid=cityid) + return jsonify(result) + + +@app.route('/api//currentairtemperature') +def currentair(version): + value, timestamp = currentairtemperature(cityid) + return jsonify({"value": value, "timestamp": timestamp}) + + +@app.route('/api//currentwatertemperature') +def currentwater(version): + value, timestamp = currentwatertemperature(mainsensor) + return jsonify({"value": value, "timestamp": timestamp}) + + +@app.route('/report//') +def report(year, month): + """Report for given year (4 digits) and month (2 digits) + """ + paper_size = (29.7 / 2.54, 21. / 2.54) # A4 + + begin = datetime.datetime(year, month, 1) + end = first_of_month(begin, 1) + + water_data = sensordata_to_xy(select_sensordata(mainsensor, 'Wassertemperatur', begin, end)) + air_data = openweatherdata_to_xy(select_openweatherdata(cityid, begin, end)) + swimmer_data = swimmerdata_to_xy(select_swimmerdata(begin, end)) + + report_times = [datetime.time(10), datetime.time(15)] + report_data = {'Wasser': water_data, 'Luft': air_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: + title = 'Seepark Obsteig {} {}'.format(MONTH_DE[begin.month-1], begin.year) + + # graphic + plt.figure(figsize=paper_size) + report_colors = [] + for label, data in sorted(report_data.items(), reverse=True): + x, y = data + lines = plt.plot(x, y, label=label) + report_colors.append(lines[0].get_color()) + plt.xticks(days_datetime, [''] * len(days_datetime)) + plt.ylabel('Temperatur in °C') + plt.axis(xmin=begin, xmax=end) + plt.legend() + plt.grid() + plt.title(title) + + # table + columns = [] + for d in days_datetime: + columns.append('{}.'.format(d.day)) + rows = [] + for label in sorted(report_data.keys(), reverse=True): + for t in report_times: + rows.append('{:02d}:{:02d} {} °C'.format(t.hour, t.minute, label)) + for t in report_times: + rows.append('{:02d}:{:02d} Badende'.format(t.hour, t.minute)) + cells = [] + for label, data in sorted(report_data.items(), reverse=True): + for t in report_times: + row_cells = [] + x, y = data + 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) + for t in report_times: + row_cells = [] + x, y = swimmer_data + 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: + cell = y[closest_index] + row_cells.append(cell) + cells.append(row_cells) + row_colors = list(ntimes(report_colors + ['w'], len(report_times))) + table = plt.table(cellText=cells, colLabels=columns, rowLabels=rows, rowColours=row_colors, 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'] = 'Temperaturen' + pdf_info['Keywords'] = 'Seepark Obsteig' + pdf_info['CreationDate'] = datetime.datetime.now() + pdf_info['ModDate'] = datetime.datetime.today() -@app.route("/data") -def data(): - data = { - '0316a2193bff': [20, 19, 20, 21, 20, 21], - '0316a2193bff_x': ['2018-05-20 12:00:01', '2018-05-21 13:01:02', '2018-05-22 14:05:00', '2018-05-23 14:05:01', '2018-05-24 14:05:00', '2018-05-27 14:05:02'], - '0316a21383ff': [27, 32, 26, 29, 30, 31], - '0316a21383ff_x': [ '2018-05-21 18:00:00', '2018-05-22 14:05:00', '2018-05-23 14:05:00', '2018-05-24 14:05:02', '2018-05-25 14:05:01', '2018-05-27 14:05:01'], - } - return jsonify(data) + 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("/") -def hello(): - return render_template('seepark_web.html') +def index(): + airvalue, airtime = currentairtemperature(cityid) + watervalue, watertime = currentwatertemperature(mainsensor) + this_month = first_of_month(datetime.date.today(), 0) + last_month = first_of_month(this_month, -1) + return render_template( + 'seepark_web.html', + apikey=apikey, + watervalue=watervalue, + watertime=watertime, + airvalue=airvalue, + airtime=airtime, + this_month=this_month, + last_month=last_month + )