日時指定ダッシュボードへの trips_map 画面・trips.csv 追加

from __future__ import annotations

import os
import io
import csv
import datetime as dt
from dataclasses import dataclass

from flask import (
    Flask,
    request,
    make_response,
    send_from_directory,
    redirect,
    Response,
)
import psycopg2

APP_HOST = "0.0.0.0"
APP_PORT = int(os.getenv("SAMPLE2_PORT", "18080"))
BASE_DIR = os.path.dirname(os.path.abspath(__file__))

# ========= DB 接続 =========
PGHOST = os.getenv("PGHOST", "localhost")
PGPORT = int(os.getenv("PGPORT", "15432"))
PGDATABASE = os.getenv("PGDATABASE", "tsubame_db")
PGUSER = os.getenv("PGUSER", "postgres")
PGPASSWORD = os.getenv("PGPASSWORD", "password")

# ========= cookie =========
COOKIE_START = "date_start"
COOKIE_END = "date_end"

STOP_TABLE = "stop_tsubame"
LOG_TABLE = "log_pass_tsubame"

app = Flask(__name__, static_folder=None)


@dataclass(frozen=True)
class DateRange:
    start: dt.date
    end: dt.date

    @property
    def end_exclusive(self):
        return self.end + dt.timedelta(days=1)


def parse_date(s: str) -> dt.date:
    return dt.datetime.strptime(s, "%Y-%m-%d").date()


def get_range(req) -> DateRange:
    s = req.cookies.get(COOKIE_START)
    e = req.cookies.get(COOKIE_END)

    if not s or not e:
        today = dt.date.today()
        return DateRange(today.replace(day=1), today)

    d1 = parse_date(s)
    d2 = parse_date(e)
    return DateRange(min(d1, d2), max(d1, d2))


def db_connect():
    return psycopg2.connect(
        host=PGHOST,
        port=PGPORT,
        dbname=PGDATABASE,
        user=PGUSER,
        password=PGPASSWORD,
        sslmode="disable",
    )


def csv_response(name, text, download):
    r = make_response(text)
    r.headers["Cache-Control"] = "no-store"
    if download:
        r.headers["Content-Disposition"] = f'attachment; filename="{name}"'
        r.headers["Content-Type"] = "application/octet-stream"
    else:
        r.headers["Content-Type"] = "text/csv; charset=utf-8"
    return r


@app.get("/")
def root():
    return send_from_directory(BASE_DIR, "index.html")


@app.post("/set_range")
def set_range():
    s = request.form["start"]
    e = request.form["end"]
    r = redirect("/")
    r.set_cookie(COOKIE_START, s, max_age=86400 * 365)
    r.set_cookie(COOKIE_END, e, max_age=86400 * 365)
    return r


# ---------- trips.csv ----------
@app.get("/trips.csv")
def trips_csv():
    dr = get_range(request)

    sql = """
WITH with_stop AS (
  SELECT
    l.psg_id,
    l.ride_time,
    l.on_off,
    (s.stop_num::text || '. ' ||
     regexp_replace(s.name, '^[0-9]+[．\\.]\\s*', '')
    ) AS stop_name
  FROM log_pass_tsubame l
  JOIN LATERAL (
    SELECT stop_num, name, geom
    FROM stop_tsubame
    ORDER BY l.geom <-> geom
    LIMIT 1
  ) s ON true
  WHERE l.ride_time >= %(s)s
    AND l.ride_time <  %(e)s
),
seq AS (
  SELECT
    psg_id,
    ride_time,
    on_off,
    stop_name,
    SUM(CASE WHEN on_off = 1 THEN 1 ELSE 0 END)
      OVER (PARTITION BY psg_id ORDER BY ride_time) AS trip_no
  FROM with_stop
),
od AS (
  SELECT
    psg_id,
    trip_no,
    (array_agg(stop_name ORDER BY ride_time)
      FILTER (WHERE on_off = 1))[1] AS "from",
    (
      array_agg(stop_name ORDER BY ride_time)
        FILTER (WHERE on_off = 0)
    )[
      array_length(
        array_agg(stop_name ORDER BY ride_time)
          FILTER (WHERE on_off = 0)
      , 1)
    ] AS "to"
  FROM seq
  WHERE trip_no > 0
  GROUP BY psg_id, trip_no
)
SELECT "from","to",COUNT(*) AS trips
FROM od
WHERE "from" IS NOT NULL
  AND "to" IS NOT NULL
  AND "from" <> "to"
GROUP BY "from","to"
ORDER BY "from","to";
"""

    out = io.StringIO()
    w = csv.writer(out)
    w.writerow(["from", "to", "trips"])

    with db_connect() as c:
        with c.cursor() as cur:
            cur.execute(sql, {"s": dr.start, "e": dr.end_exclusive})
            for f, t, n in cur.fetchall():
                w.writerow([f, t, n])

    return csv_response("trips.csv", out.getvalue(),
                        request.args.get("download") == "1")


@app.get("/trips_map.html")
def trips_map_html():
    return send_from_directory(BASE_DIR, "trips_map.html")


@app.get("/<path:filename>")
def any_file(filename):
    p = os.path.join(BASE_DIR, filename)
    if os.path.exists(p):
        return send_from_directory(BASE_DIR, filename)
    return Response("not found", 404)


if __name__ == "__main__":
    app.run(host=APP_HOST, port=APP_PORT)

<!DOCTYPE html>
<html lang="ja">
<head>
<meta charset="UTF-8">
<title>日時指定ダッシュボード（4画面）</title>
<style>
body{font-family:sans-serif;margin:16px}
.row{display:flex;gap:12px;align-items:center;flex-wrap:wrap}
.panel{margin-top:16px;border:1px solid #ccc;border-radius:8px;overflow:hidden}
.panel h2{margin:0;padding:8px;background:#f5f5f5}
iframe{width:100%;border:0}
.h1,.h2{height:640px}
.h3,.h4{height:720px}
a.btn{padding:6px 10px;border:1px solid #888;text-decoration:none}
</style>
</head>
<body>

<h1>日時指定（4表示）</h1>

<form method="POST" action="/set_range" class="row">
  <label>開始日 <input type="date" name="start" required></label>
  <label>終了日 <input type="date" name="end" required></label>
  <button type="submit">OK（表示）</button>

  <a class="btn" href="/sample2.csv?download=1">CSV(sample2)</a>
  <a class="btn" href="/ride_summary.csv?download=1">CSV(rides)</a>
  <a class="btn" href="/bus_stops.csv?download=1">CSV(bus_stops)</a>
  <a class="btn" href="/trips.csv?download=1">CSV(trips)</a>
</form>

<div class="panel">
<h2>sample2</h2>
<iframe class="h1" src="/sample2.html"></iframe>
</div>

<div class="panel">
<h2>rides</h2>
<iframe class="h2" src="/rides.html"></iframe>
</div>

<div class="panel">
<h2>bus_map</h2>
<iframe class="h3" src="/bus_map.html"></iframe>
</div>

<div class="panel">
<h2>trips_map</h2>
<iframe class="h4" src="/trips_map.html"></iframe>
</div>

</body>
</html>