X-Git-Url: https://git.toastfreeware.priv.at/chrisu/seepark.git/blobdiff_plain/2e9bbe4e27630f6f6bdfd714adcae35b7734d582..7c05a3e46921f5dfb74a337d66f469d13b6ad3a2:/web/seepark_web.py diff --git a/web/seepark_web.py b/web/seepark_web.py index cbe55f8..b899a17 100644 --- a/web/seepark_web.py +++ b/web/seepark_web.py @@ -4,11 +4,41 @@ 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 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): @@ -59,24 +89,12 @@ 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 + # except day: 300 -> 600 resolutions = dict( - day = 300, + day = 600, week = 1800, month = 7200, year = 86400, @@ -91,11 +109,30 @@ 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).label('timestamp'), + 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: @@ -108,34 +145,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() @@ -188,7 +220,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') @@ -205,6 +244,10 @@ def currentwatertemperature(sensorid): return result.value, result.timestamp +def add_month(date): + return (date + datetime.timedelta(days=42)).replace(day=1) + + @app.route('/api//sensors/') def sensors(version): """List all sensors found in the database""" @@ -254,6 +297,89 @@ def openweathermap_city(version, 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): + + 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("/") def index(): airvalue, airtime = currentairtemperature(cityid)