302 lines
11 KiB
Python
302 lines
11 KiB
Python
"""Web dashboard for dynamic Wi-Fi signal heatmaps."""
|
|
|
|
from __future__ import annotations
|
|
|
|
import csv
|
|
from collections.abc import Sequence
|
|
from datetime import datetime
|
|
from pathlib import Path
|
|
from typing import Any
|
|
|
|
import psycopg
|
|
from flask import Flask, jsonify, render_template, request
|
|
|
|
from settings import SETTINGS, Settings
|
|
|
|
MAX_BSSID_FILTERS = 100
|
|
MAX_AGENT_FILTERS = 100
|
|
|
|
|
|
def create_app(settings: Settings = SETTINGS) -> Flask:
|
|
app = Flask(__name__)
|
|
app.config["WIFI_SETTINGS"] = settings
|
|
|
|
@app.get("/")
|
|
def dashboard() -> str:
|
|
return render_template(
|
|
"dashboard.html",
|
|
default_date_range=settings.web_default_date_range_minutes,
|
|
map_min_x=settings.web_map_min_x,
|
|
map_max_x=settings.web_map_max_x,
|
|
map_min_y=settings.web_map_min_y,
|
|
map_max_y=settings.web_map_max_y,
|
|
)
|
|
|
|
@app.get("/api/map-objects")
|
|
def map_objects():
|
|
"""Return all map primitives stored in doc/object_*.csv."""
|
|
objects: list[dict[str, Any]] = []
|
|
for path in sorted(Path(app.root_path, "doc").glob("object_*.csv")):
|
|
with path.open(encoding="utf-8-sig", newline="") as source:
|
|
for row in csv.DictReader(source):
|
|
try:
|
|
item: dict[str, Any] = {
|
|
"x": float(row["x"]),
|
|
"y": float(row["y"]),
|
|
}
|
|
if row.get("width") and row.get("height"):
|
|
item.update(
|
|
width=float(row["width"]),
|
|
height=float(row["height"]),
|
|
shape="rectangle",
|
|
)
|
|
else:
|
|
item.update(shape="circle", name=row.get("name", ""))
|
|
except (KeyError, TypeError, ValueError):
|
|
continue
|
|
objects.append(item)
|
|
return jsonify(objects)
|
|
|
|
@app.get("/health")
|
|
def health():
|
|
try:
|
|
with connect(settings) as connection:
|
|
connection.execute("SELECT 1").fetchone()
|
|
return jsonify(status="ok")
|
|
except psycopg.Error:
|
|
return jsonify(status="error", database="unavailable"), 503
|
|
|
|
@app.get("/api/bssids")
|
|
def bssids():
|
|
with connect(settings) as connection:
|
|
rows = connection.execute(
|
|
"""SELECT b.id, b.bssid, COUNT(s.id) AS samples
|
|
FROM bssids AS b
|
|
LEFT JOIN scan_results AS s ON s.bssid_id = b.id
|
|
GROUP BY b.id, b.bssid
|
|
ORDER BY b.bssid"""
|
|
).fetchall()
|
|
return jsonify(
|
|
[
|
|
{"id": row[0], "bssid": database_text(row[1]), "samples": row[2]}
|
|
for row in rows
|
|
]
|
|
)
|
|
|
|
@app.get("/api/agents")
|
|
def agents():
|
|
with connect(settings) as connection:
|
|
rows = connection.execute(
|
|
"""SELECT a.id, a.agent_name, COUNT(s.id) AS samples
|
|
FROM agents AS a
|
|
LEFT JOIN scan_results AS s ON s.agent_id = a.id
|
|
GROUP BY a.id, a.agent_name
|
|
ORDER BY a.agent_name"""
|
|
).fetchall()
|
|
return jsonify(
|
|
[
|
|
{"id": row[0], "name": database_text(row[1]), "samples": row[2]}
|
|
for row in rows
|
|
]
|
|
)
|
|
|
|
@app.get("/api/heatmap")
|
|
def heatmap():
|
|
try:
|
|
date_from = datetime_parameter("from")
|
|
date_to = datetime_parameter("to")
|
|
if date_from >= date_to:
|
|
raise ValueError("from must be earlier than to")
|
|
bssid_ids = parse_bssid_ids(request.args.getlist("bssid"))
|
|
agent_ids = parse_positive_ids(
|
|
request.args.getlist("agent"), "agent", MAX_AGENT_FILTERS
|
|
)
|
|
aggregation = request.args.get("aggregation", "max")
|
|
if aggregation not in ("max", "avg", "daily_max_avg"):
|
|
raise ValueError("aggregation must be max, avg or daily_max_avg")
|
|
min_x = integer_parameter("min_x", settings.web_map_min_x)
|
|
max_x = integer_parameter("max_x", settings.web_map_max_x)
|
|
min_y = integer_parameter("min_y", settings.web_map_min_y)
|
|
max_y = integer_parameter("max_y", settings.web_map_max_y)
|
|
if min_x >= max_x or min_y >= max_y:
|
|
raise ValueError("map minimum bounds must be lower than maximum bounds")
|
|
except ValueError as error:
|
|
return jsonify(error=str(error)), 400
|
|
|
|
where_filter = ""
|
|
parameters: list[Any] = [date_from, date_to, min_x, max_x, min_y, max_y]
|
|
if bssid_ids:
|
|
where_filter = "AND scan.bssid_id = ANY(%s)"
|
|
parameters.append(bssid_ids)
|
|
if agent_ids:
|
|
where_filter += " AND scan.agent_id = ANY(%s)"
|
|
parameters.append(agent_ids)
|
|
|
|
base_where = f"""
|
|
scan.collected_at >= %s
|
|
AND scan.collected_at <= %s
|
|
AND scan.location_x BETWEEN %s AND %s
|
|
AND scan.location_y BETWEEN %s AND %s
|
|
{where_filter}
|
|
"""
|
|
if aggregation == "daily_max_avg":
|
|
query = f"""
|
|
SELECT
|
|
daily.location_x,
|
|
daily.location_y,
|
|
AVG(daily.maximum_signal) AS signal,
|
|
SUM(daily.samples) AS samples
|
|
FROM (
|
|
SELECT
|
|
scan.collected_at::date AS collected_date,
|
|
scan.location_x,
|
|
scan.location_y,
|
|
MAX(scan.signal_level) AS maximum_signal,
|
|
COUNT(*) AS samples
|
|
FROM scan_results AS scan
|
|
WHERE {base_where}
|
|
GROUP BY collected_date, scan.location_x, scan.location_y
|
|
) AS daily
|
|
GROUP BY daily.location_x, daily.location_y
|
|
ORDER BY daily.location_x, daily.location_y
|
|
"""
|
|
else:
|
|
signal_aggregate = "AVG" if aggregation == "avg" else "MAX"
|
|
query = f"""
|
|
SELECT
|
|
scan.location_x,
|
|
scan.location_y,
|
|
{signal_aggregate}(scan.signal_level) AS signal,
|
|
COUNT(*) AS samples
|
|
FROM scan_results AS scan
|
|
WHERE {base_where}
|
|
GROUP BY scan.location_x, scan.location_y
|
|
ORDER BY scan.location_x, scan.location_y
|
|
"""
|
|
with connect(settings) as connection:
|
|
rows = connection.execute(query, parameters).fetchall()
|
|
access_point_rows: list[tuple[Any, ...]] = []
|
|
if aggregation in ("max", "avg"):
|
|
coordinate_parameters: list[Any] = [min_x, max_x, min_y, max_y]
|
|
coordinate_filter = ""
|
|
if bssid_ids:
|
|
coordinate_filter = "AND coordinates.bssid_id = ANY(%s)"
|
|
coordinate_parameters.append(bssid_ids)
|
|
access_point_rows = connection.execute(
|
|
f"""SELECT b.id, b.bssid,
|
|
coordinates.location_x, coordinates.location_y
|
|
FROM access_point_coordinates AS coordinates
|
|
JOIN bssids AS b ON b.id = coordinates.bssid_id
|
|
WHERE coordinates.location_x BETWEEN %s AND %s
|
|
AND coordinates.location_y BETWEEN %s AND %s
|
|
{coordinate_filter}
|
|
ORDER BY b.bssid""",
|
|
coordinate_parameters,
|
|
).fetchall()
|
|
|
|
points: list[dict[str, Any]] = []
|
|
bounds = {"min_x": min_x, "max_x": max_x, "min_y": min_y, "max_y": max_y}
|
|
for x, y, signal, samples in rows:
|
|
points.append(
|
|
{"x": x, "y": y, "signal": float(signal), "samples": samples}
|
|
)
|
|
access_points = [
|
|
{
|
|
"id": row[0],
|
|
"bssid": database_text(row[1]),
|
|
"x": float(row[2]),
|
|
"y": float(row[3]),
|
|
}
|
|
for row in access_point_rows
|
|
]
|
|
return jsonify(
|
|
date_from=date_from.isoformat(),
|
|
date_to=date_to.isoformat(),
|
|
bssid_ids=bssid_ids,
|
|
agent_ids=agent_ids,
|
|
aggregation=aggregation,
|
|
bounds=bounds,
|
|
points=points,
|
|
access_points=access_points,
|
|
)
|
|
|
|
@app.errorhandler(psycopg.Error)
|
|
def database_error(_error: psycopg.Error):
|
|
app.logger.exception("PostgreSQL request failed")
|
|
return jsonify(error="database is temporarily unavailable"), 503
|
|
|
|
return app
|
|
|
|
|
|
def connect(settings: Settings):
|
|
return psycopg.connect(
|
|
host=settings.postgres_host,
|
|
port=settings.postgres_port,
|
|
dbname=settings.postgres_database,
|
|
user=settings.postgres_user,
|
|
password=settings.postgres_password,
|
|
connect_timeout=settings.postgres_connect_timeout,
|
|
)
|
|
|
|
|
|
def database_text(value: Any) -> str:
|
|
"""Normalize text returned from both current TEXT and legacy BYTEA columns."""
|
|
if isinstance(value, bytes):
|
|
return value.decode("utf-8", errors="replace")
|
|
return str(value)
|
|
|
|
|
|
def datetime_parameter(name: str) -> datetime:
|
|
raw_value = request.args.get(name)
|
|
if not raw_value:
|
|
raise ValueError(f"{name} is required")
|
|
try:
|
|
value = datetime.fromisoformat(raw_value.replace("Z", "+00:00"))
|
|
except ValueError as error:
|
|
raise ValueError(f"{name} must be an ISO 8601 date and time") from error
|
|
if value.tzinfo is None:
|
|
raise ValueError(f"{name} must include a timezone")
|
|
return value
|
|
|
|
|
|
def integer_parameter(name: str, default: int) -> int:
|
|
raw_value = request.args.get(name)
|
|
if raw_value is None or raw_value == "":
|
|
return default
|
|
try:
|
|
return int(raw_value)
|
|
except ValueError as error:
|
|
raise ValueError(f"{name} must be an integer") from error
|
|
|
|
|
|
def parse_bssid_ids(raw_values: Sequence[str]) -> list[int]:
|
|
return parse_positive_ids(raw_values, "bssid", MAX_BSSID_FILTERS)
|
|
|
|
|
|
def parse_positive_ids(
|
|
raw_values: Sequence[str], parameter_name: str, maximum: int
|
|
) -> list[int]:
|
|
values: list[int] = []
|
|
for raw_value in raw_values:
|
|
for item in raw_value.split(","):
|
|
if not item:
|
|
continue
|
|
try:
|
|
value = int(item)
|
|
except ValueError as error:
|
|
raise ValueError(f"{parameter_name} values must be integer ids") from error
|
|
if value <= 0:
|
|
raise ValueError(f"{parameter_name} values must be positive")
|
|
if value not in values:
|
|
values.append(value)
|
|
if len(values) > maximum:
|
|
raise ValueError(f"no more than {maximum} {parameter_name} filters are allowed")
|
|
return values
|
|
|
|
|
|
app = create_app()
|
|
|
|
|
|
if __name__ == "__main__":
|
|
app.run(host=SETTINGS.web_host, port=SETTINGS.web_port)
|