# snag # Matt Kohls # (c) 2023 from flask import ( Blueprint, flash, g, redirect, render_template, request, url_for, json ) from werkzeug.exceptions import abort from datetime import datetime, timedelta from snag.db import get_db from snag.graphs import get_table import time bp = Blueprint('data', __name__, url_prefix='/data') # Check if device is aware of daylight savings, we may need to adjust their reported time def is_dst_aware(deviceId): db = get_db() sql = "SELECT dstAware FROM devices WHERE deviceId = ?" data = db.execute(sql, [deviceId]) if data is None: return False else: return data.fetchone()['dstAware'] != 0 # Process json data # # Expects something like this: # { # "scheme": "1", # "deviceId": 1, # "readings": [ # { # "env": "outdoor", # "temp": 123 # }, # { # "env": "enclosed", # "temp": 456 # } # ], # "status": [ # { # "heater": "off", # "battery": 10 # } # ] # } def unpack_json_scheme_1(req): try: deviceId = req['deviceId'] db = get_db() dstAware = is_dst_aware(deviceId) # Status only has one object in it, and is optional if 'status' in req: if 'timeStamp' in req['status'][0]: now = req['status'][0]['timeStamp'] now = datetime.strptime(now, "%Y-%m-%dT%H:%M:%SZ") if not dstAware and time.daylight: now = now + timedelta(hours=1) now = now.strftime("%Y-%m-%d %H:%M:%S") else: now = datetime.now() battery = req['status'][0]['battery'] heater = req['status'][0]['heater'] db.execute('INSERT INTO device_status (date, deviceId, batteryCharge, heaterRunning) VALUES (?, ?, ?, ?)', [now, deviceId, battery, heater]) for environment in req['readings']: env = environment['environment'] temp = environment['temperature'] humidity = environment['humidity'] pressure = environment['pressure'] now = environment['timeStamp'] now = datetime.strptime(now, "%Y-%m-%dT%H:%M:%SZ") if not dstAware and time.daylight: now = now + timedelta(hours=1) now = now.strftime("%Y-%m-%d %H:%M:%S") if env == 'outdoor': lux = environment['lux'] uv = environment['uv'] db.execute('INSERT INTO env_outdoor (date, deviceId, temperature, humidity, pressure, lux, uv_intensity) VALUES (?, ?, ?, ?, ?, ?, ?)', [now, deviceId, temp, humidity, pressure, lux, uv]) elif env == 'indoor': db.execute('INSERT INTO env_indoor (date, deviceId, temperature, humidity, pressure) VALUES (?, ?, ?, ?, ?)', [now, deviceId, temp, humidity, pressure]) elif env == 'enclosed': light = environment['light'] db.execute('INSERT INTO env_enclosed (date, deviceId, temperature, humidity, pressure, light) VALUES (?, ?, ?, ?, ?, ?)', [now, deviceId, temp, humidity, pressure, light]) else: raise 'Bad reading environment' db.commit() except Exception as err: print(err) return "Bad JSON", 400 return "Data received", 200 ## For submitting data to server @bp.route('/json', methods=['POST']) def add_json_data(): if request.is_json: req = request.get_json() if 'scheme' in req: if req['scheme'] == '1': return unpack_json_scheme_1(req) else: abort(400, "Unknown scheme") else: abort(400, "Unknown JSON") else: abort(400, "Unknown payload type") ### Information Routes ### ## Gives information about all the devices @bp.route('/info/devices', methods=['GET', 'POST']) def get_info_devices(): db = get_db() out = [] devices = db.execute('SELECT * FROM devices ORDER BY deviceId').fetchall() for device in devices: envList = [] envs = db.execute('SELECT * FROM device_env WHERE deviceId = ?', [device['deviceId']]).fetchall() for env in envs: envDict = { "environment" : env["environment"], #"environmentDesc" : env["environmentDescription"] } envList.append(envDict) deviceLine = { "deviceId" : device["deviceId"], "deviceName" : device["deviceName"], "deviceDescription" : device["deviceLocation"] } deviceLine['environments'] = envList out.append(deviceLine) return out ## Grabs data from db, or None if not def get_data_selection(device_ids, start_time, end_time, columns, environment): if end_time is None: end_time = datetime.now() if start_time is None: start_time = end_time - timedelta(days=1) if end_time < start_time: start_time, end_time = end_time, start_time db = get_db() if environment is not None: devqs = "{0}".format(', '.join(['?'] * len(device_ids))) argument_list = [start_time.isoformat(" ", "seconds"), end_time.isoformat(" ", "seconds")] + device_ids sql = (f"SELECT {columns} " f"FROM {environment} " f"WHERE (date BETWEEN ? AND ?) AND deviceId IN ( {devqs} )") subset = db.execute(sql, argument_list).fetchall() else: subset = None return subset ### Device Routes ### ## Get data by devices # # Expected json payload # { # "devices" : [ "", "", .. ] ; Required # "environment" : "", ; Required # "startTime" : "", ; Optional # "endTime" : "", ; Optional # "measurements" : [ "", "", .. ] ; Required # } # # Returns a json payload like this # [ # {"timeStamp":"2112-09-21T09:21:00", "":"", ..}, .. # ] # @bp.route('/device', methods=['GET', 'POST']) def get_n_device_data(): if request.is_json: payload = request.get_json() return process_device_data_req(payload) else: abort(400, "Missing request data") ## Get data by deviceId # # Expected json payload # { # "environment" : "", ; Required # "startTime" : "", ; Optional # "endTime" : "", ; Optional # "measurements" : [ "", "", ..] ; Required # } # # Returns a json payload like this # [ # {"timeStamp":"2112-09-21T09:21:00", "":"", ..}, .. # ] # @bp.route('/device/', methods=['GET', 'POST']) def get_device_data(device_id): if request.is_json: payload = request.get_json() payload['devices'] = [device_id] return process_device_data_req(payload) else: abort(400, "Missing request data") ## Processes a device data request # # Expected json payload # { # "devices" : [ "", "", .. ] ; Required # "environment" : "", ; Required # "startTime" : "", ; Optional # "endTime" : "", ; Optional # "measurements" : [ "", "", .. ] ; Required # } # # Returns a json payload like this # [ # {"timeStamp":"2112-09-21T09:21:00", "":"", ..}, .. # ] # def process_device_data_req(payload): if 'environment' in payload and 'measurements' in payload and 'devices' in payload: env = get_table(payload['environment']) if env is None: abort(400, "Unknown environment: " + payload['environment']) end_time = datetime.now() start_time = end_time - timedelta(days=1) if 'startTime' in payload: start_time = payload['startTime'] start_time = datetime.strptime(start_time, "%Y-%m-%dT%H:%M:%SZ") if 'endTime' in payload: end_time = payload['endTime'] end_time = datetime.strptime(end_time, "%Y-%m-%dT%H:%M:%SZ") selected_columns = "date, deviceId" for measurement in payload['measurements']: selected_columns = selected_columns + ", " + measurement if selected_columns == "date, deviceId": abort(400, "Bad json payload - missing measurements") else: device_ids = [] for device in payload['devices']: device_ids.append(device) if device_ids == []: abort(400, "Bad json payload - missing devices") data = get_data_selection(device_ids, start_time, end_time, selected_columns, env) if data is None: abort(404, "No data found") outlist = [] for row in data: reading = { "timeStamp":row["date"].strftime('%Y-%m-%dT%H:%M:%SZ'), "deviceId":row["deviceId"]} for measurement in payload['measurements']: reading[measurement] = row[measurement] outlist.append(reading) return outlist else: abort(400, "Bad json payload") # Get data by environment type @bp.route('/environment', methods=['GET', 'POST']) def get_env_data(): if request.args['type'] is not None: return "todo" else: abort(400, "Unknown environment type")