Toutes les sorties et tous les événements du Québec, un seul endroit — 7 connecteurs, fiches SSR, design Groupe KA.
HTML 82.9%
Python 15.2%
TypeScript 0.9%
JavaScript 0.7%
1# -----------------------------------------------------------------------------2# Sorti-Ka — Agrégateur de sorties & événements (province de Québec)3# Auteur : Simon-Pierre Boucher — contact@spboucher.ai4# stats.py : tableau de bord analytique /api/stats/dashboard (contrat ka-stats5# SPEC.md v2) — agrégations SQL réelles sur events/sync_log, AUCUNE6# statistique inventée, cache mémoire 5 min par période.7# v2 : sparklines KPI, jauges, multi-courbes (top catégories),8# barres empilées (sources, gratuits vs payants), distributions9# (prix, durée), heatmap horaire 7×24, deltas de répartitions,10# 5 tableaux, ~10 records.11# -----------------------------------------------------------------------------12from __future__ import annotations1314import json15import time16from datetime import date, datetime, timedelta17from zoneinfo import ZoneInfo1819from . import db2021TZ = ZoneInfo("America/Toronto")22CACHE_TTL = 300 # ≥ 5 min (SPEC)23_cache: dict[tuple, tuple[float, dict]] = {}2425HEATMAP_DAYS = 182 # 26 semaines de calendrier des dates d'événements26ONGOING_DAYS = 30 # série « se déroulant par jour » (30 prochains jours)27BIGDAYS_HORIZON = 90 # tableau « prochains gros jours »28WEEKLY_OVER = 62 # au-delà de N jours, empilées/multi-courbes par semaine2930# Libellés humains (mêmes que le frontend — taxonomie sortika/normalize.py)31CAT_LABELS = {32 "festival": "Festivals", "musique": "Musique", "arts-scene": "Arts de la scène",33 "exposition-musee": "Expos & musées", "cinema": "Cinéma", "sport": "Sport",34 "plein-air": "Plein air", "famille": "Famille", "gastronomie": "Gastronomie",35 "marche-foire": "Marchés & foires", "conference": "Conférences & ateliers",36 "communautaire": "Communautaire", "patrimoine": "Patrimoine", "autre": "Autre",37}38SRC_LABELS = {39 "sitq": "SIT Québec (MTO)", "montreal": "Ville de Montréal",40 "laval": "Ville de Laval", "lepointdevente": "Le point de vente",41 "evenko": "evenko", "atuvu": "Atuvu.ca", "lavitrine": "La Vitrine",42 "sherbrooke": "Ville de Sherbrooke", "eventbrite": "Eventbrite",43 "ticketmaster": "Ticketmaster", "bandsintown": "Bandsintown",44 "brossard": "Ville de Brossard", "longueuil": "Ville de Longueuil",45 "cantonsdelest": "Cantons-de-l'Est (ATR)",46}4748PERIOD_DAYS = {"7j": 7, "30j": 30, "3m": 91, "6m": 182, "12m": 365}49PERIOD_LABELS = {50 "auj": "Aujourd'hui", "7j": "7 jours", "30j": "30 jours", "3m": "3 mois",51 "6m": "6 mois", "12m": "12 mois", "annee": "Année en cours",52 "tout": "Toute la période",53}5455PRICE_BINS = [(0, 10), (10, 20), (20, 30), (30, 50), (50, 75), (75, 100),56 (100, 150), (150, None)]57DURATION_BINS = [("1 jour", 1, 1), ("2-3 jours", 2, 3), ("4-7 jours", 4, 7),58 ("1-2 semaines", 8, 14), ("2-4 semaines", 15, 28),59 ("1-3 mois", 29, 92), ("3 mois +", 93, None)]606162def _parse_iso(s: str) -> date | None:63 try:64 return date.fromisoformat((s or "").strip())65 except ValueError:66 return None676869def resolve_period(con, period: str, dfrom: str = "", dto: str = "") -> tuple[date, date, str]:70 """(from, to, label) — plage personnalisée > période nommée > 30 j."""71 today = datetime.now(TZ).date()72 f, t = _parse_iso(dfrom), _parse_iso(dto)73 if f and t:74 if f > t:75 f, t = t, f76 return f, t, f"{f.isoformat()} → {t.isoformat()}"77 if period == "auj":78 return today, today, PERIOD_LABELS["auj"]79 if period == "annee":80 return date(today.year, 1, 1), today, PERIOD_LABELS["annee"]81 if period == "tout":82 row = con.execute(83 "SELECT date(MIN(first_seen),'unixepoch','localtime') FROM events"84 ).fetchone()85 start = _parse_iso(row[0] or "") or today86 return start, today, PERIOD_LABELS["tout"]87 days = PERIOD_DAYS.get(period, 30)88 return today - timedelta(days=days - 1), today, PERIOD_LABELS.get(period, "30 jours")899091def _pct(cur: float, prev: float) -> float | None:92 if prev <= 0:93 return None94 return round(100.0 * (cur - prev) / prev, 1)959697def _kpi(id, label, value, unit="", delta_pct=None, spark=None) -> dict:98 d = {"id": id, "label": label, "value": value, "unit": unit,99 "delta_pct": delta_pct}100 if delta_pct is not None:101 d["direction"] = "up" if delta_pct >= 0 else "down"102 if spark:103 d["spark"] = spark104 return d105106107def _added_by_day(con, d0: date, d1: date) -> dict[str, int]:108 """Événements découverts (first_seen) par jour local, entre d0 et d1."""109 return {r[0]: r[1] for r in con.execute(110 "SELECT date(first_seen,'unixepoch','localtime') AS d, COUNT(*) "111 "FROM events WHERE d >= ? AND d <= ? GROUP BY d",112 (d0.isoformat(), d1.isoformat()))}113114115def _ongoing_counts(con, start: date, days: int) -> list[int]:116 """Nb d'événements actifs SE DÉROULANT chaque jour (tableau de différences :117 +1 au début effectif, -1 au lendemain de la fin — O(événements + jours))."""118 diff = [0] * (days + 1)119 horizon = start + timedelta(days=days - 1)120 for r in con.execute(121 "SELECT start_date, COALESCE(end_date, start_date) AS e FROM events "122 "WHERE active=1 AND quarantine IS NULL AND start_date IS NOT NULL "123 "AND start_date <= ? AND COALESCE(end_date, start_date) >= ?",124 (horizon.isoformat(), start.isoformat())):125 s = max(_parse_iso(r["start_date"]) or start, start)126 e = min(_parse_iso(r["e"]) or s, horizon)127 if e < s:128 continue129 diff[(s - start).days] += 1130 diff[(e - start).days + 1] -= 1131 out, acc = [], 0132 for i in range(days):133 acc += diff[i]134 out.append(acc)135 return out136137138def _upcoming_where() -> str:139 return ("active=1 AND quarantine IS NULL AND (end_date >= :today OR "140 "(end_date IS NULL AND start_date >= :today))")141142143def _buckets(p_from: date, p_to: date) -> tuple[list[date], bool]:144 """Bornes des seaux temporels (jour, ou lundi de semaine si la période145 dépasse WEEKLY_OVER jours) couvrant [p_from, p_to]."""146 length = (p_to - p_from).days + 1147 weekly = length > WEEKLY_OVER148 if not weekly:149 return [p_from + timedelta(days=i) for i in range(length)], False150 start = p_from - timedelta(days=p_from.weekday()) # lundi151 out, d = [], start152 while d <= p_to:153 out.append(d)154 d += timedelta(days=7)155 return out, True156157158def _bucket_of(d: date, weekly: bool) -> date:159 return d - timedelta(days=d.weekday()) if weekly else d160161162def dashboard(period: str = "30j", dfrom: str = "", dto: str = "") -> dict:163 key = (period, dfrom, dto)164 hit = _cache.get(key)165 if hit and time.time() - hit[0] < CACHE_TTL:166 return hit[1]167168 con = db.connect()169 now = datetime.now(TZ)170 today = now.date()171 p_from, p_to, p_label = resolve_period(con, period, dfrom, dto)172 length = (p_to - p_from).days + 1173 prev_to = p_from - timedelta(days=1)174 prev_from = prev_to - timedelta(days=length - 1)175 tp = {"today": today.isoformat()}176177 # ---------- KPI ----------178 active = con.execute("SELECT COUNT(*) FROM events WHERE active=1 AND quarantine IS NULL").fetchone()[0]179 upcoming = con.execute(180 f"SELECT COUNT(*) FROM events WHERE {_upcoming_where()}", tp).fetchone()[0]181 free_up = con.execute(182 f"SELECT COUNT(*) FROM events WHERE {_upcoming_where()} AND is_free=1",183 tp).fetchone()[0]184 paid_up = con.execute(185 f"SELECT COUNT(*) FROM events WHERE {_upcoming_where()} AND is_free=0",186 tp).fetchone()[0]187 unknown_price_up = upcoming - free_up - paid_up188 past = con.execute(189 "SELECT COUNT(*) FROM events WHERE start_date IS NOT NULL "190 "AND COALESCE(end_date, start_date) < :today", tp).fetchone()[0]191 cities = con.execute(192 "SELECT COUNT(DISTINCT city) FROM events WHERE active=1 AND quarantine IS NULL AND city != ''"193 ).fetchone()[0]194 venues_up = con.execute(195 f"SELECT COUNT(DISTINCT venue) FROM events WHERE {_upcoming_where()} "196 "AND venue != ''", tp).fetchone()[0]197 n_sources = con.execute(198 "SELECT COUNT(DISTINCT source) FROM events WHERE active=1 AND quarantine IS NULL").fetchone()[0]199200 added_map = _added_by_day(con, prev_from, p_to)201 added_cur = sum(v for d, v in added_map.items()202 if p_from.isoformat() <= d <= p_to.isoformat())203 added_prev = sum(v for d, v in added_map.items()204 if prev_from.isoformat() <= d <= prev_to.isoformat())205206 # « actifs » au début de la période, reconstruit depuis les journaux réels :207 # actifs_avant = actifs_maintenant − ajoutés depuis (toujours actifs)208 # + désactivés depuis (updated_at du passage active=0),209 # seulement s'ils existaient déjà avant la période210 added_active = con.execute(211 "SELECT COUNT(*) FROM events WHERE active=1 AND quarantine IS NULL AND "212 "date(first_seen,'unixepoch','localtime') >= ?",213 (p_from.isoformat(),)).fetchone()[0]214 deactivated = con.execute(215 "SELECT COUNT(*) FROM events WHERE active=0 AND "216 "date(updated_at,'unixepoch','localtime') >= ? AND "217 "date(first_seen,'unixepoch','localtime') < ?",218 (p_from.isoformat(), p_from.isoformat())).fetchone()[0]219 prev_active = active - added_active + deactivated220221 # sparklines : ajouts/jour (période) et « se déroulant » (30 prochains j.)222 horizon_counts = _ongoing_counts(con, today, HEATMAP_DAYS)223 spark_added = [{"t": (p_from + timedelta(days=i)).isoformat(),224 "v": added_map.get((p_from + timedelta(days=i)).isoformat(), 0)}225 for i in range(length)]226 spark_up = [{"t": (today + timedelta(days=i)).isoformat(),227 "v": horizon_counts[i]} for i in range(ONGOING_DAYS)]228229 kpis = [230 _kpi("actifs", "Événements actifs", active, "",231 _pct(active, prev_active)),232 _kpi("avenir", "Événements à venir", upcoming, "", None, spark_up),233 _kpi("ajouts", f"Ajoutés sur la période ({p_label})", added_cur, "",234 _pct(added_cur, added_prev), spark_added),235 _kpi("passes", "Événements passés (archives)", past),236 _kpi("gratuits", "Gratuits à venir", free_up),237 ]238 if free_up + paid_up > 0:239 kpis.append(_kpi(240 "part_gratuits",241 f"Part de gratuits (sur {free_up + paid_up} avec prix connu)",242 round(100.0 * free_up / (free_up + paid_up), 1), "%"))243 kpis += [244 _kpi("villes", "Villes couvertes", cities),245 _kpi("lieux", "Lieux référencés (à venir)", venues_up),246 _kpi("sources_n", "Sources branchées", n_sources),247 ]248 prices = con.execute(249 f"SELECT COUNT(*), AVG(price_min) FROM events WHERE {_upcoming_where()} "250 "AND is_free=0 AND price_min IS NOT NULL AND price_min > 0",251 tp).fetchone()252 if prices[0] >= 10: # champ prix exploitable seulement si assez de relevés253 kpis.append(_kpi("prix", f"Prix moyen des billets ({prices[0]} relevés)",254 round(prices[1], 2), "$"))255256 # ---------- jauges (complétude des fiches à venir) ----------257 gauges = []258 if upcoming:259 def _cov(id_, label, where_extra):260 n = con.execute(261 f"SELECT COUNT(*) FROM events WHERE {_upcoming_where()} "262 f"AND {where_extra}", tp).fetchone()[0]263 gauges.append({"id": id_, "label": label,264 "value": round(100.0 * n / upcoming, 1),265 "max": 100, "unit": "%"})266 _cov("geo", "Événements géolocalisés", "lat IS NOT NULL AND lng IS NOT NULL")267 _cov("prixinfo", "Info de prix connue", "is_free IS NOT NULL")268 _cov("image", "Fiches avec image", "image != ''")269 _cov("heure", "Heure de début connue",270 "start_time IS NOT NULL AND start_time != ''")271272 # ---------- séries ----------273 series = []274 s_added = {"id": "ajouts", "title": "Événements ajoutés par jour",275 "unit": "événements", "kind": "line", "points": spark_added}276 if period not in ("tout", "annee") and not (dfrom and dto):277 s_added["compare"] = [278 {"t": (prev_from + timedelta(days=i)).isoformat(),279 "v": added_map.get((prev_from + timedelta(days=i)).isoformat(), 0)}280 for i in range(length)]281 series.append(s_added)282283 series.append({284 "id": "en_cours", "title":285 f"Événements se déroulant par jour ({ONGOING_DAYS} prochains jours)",286 "unit": "événements", "kind": "line", "points": spark_up})287288 starts_by_day = {r[0]: r[1] for r in con.execute(289 "SELECT start_date, COUNT(*) FROM events WHERE active=1 AND quarantine IS NULL "290 "AND start_date >= ? GROUP BY start_date", (today.isoformat(),))}291 series.append({292 "id": "debuts", "title":293 f"Événements débutant par jour ({ONGOING_DAYS} prochains jours)",294 "unit": "événements", "kind": "bar",295 "points": [{"t": (today + timedelta(days=i)).isoformat(),296 "v": starts_by_day.get((today + timedelta(days=i)).isoformat(), 0)}297 for i in range(ONGOING_DAYS)]})298299 # ---------- multi-courbes : ajouts par top catégories (≤ 4) ----------300 buckets, weekly = _buckets(p_from, p_to)301 b_index = {b.isoformat(): i for i, b in enumerate(buckets)}302 cat_added: dict[str, list[int]] = {}303 cat_tot: dict[str, int] = {}304 src_added: dict[str, list[int]] = {}305 for r in con.execute(306 "SELECT date(first_seen,'unixepoch','localtime') AS d, categories, "307 "source FROM events WHERE d >= ? AND d <= ?",308 (p_from.isoformat(), p_to.isoformat())):309 d = _parse_iso(r["d"])310 if not d:311 continue312 bi = b_index.get(_bucket_of(d, weekly).isoformat())313 if bi is None:314 continue315 for c in json.loads(r["categories"] or "[]"):316 cat_added.setdefault(c, [0] * len(buckets))[bi] += 1317 cat_tot[c] = cat_tot.get(c, 0) + 1318 src_added.setdefault(r["source"], [0] * len(buckets))[bi] += 1319320 gran = "semaine" if weekly else "jour"321 multiseries = []322 top4 = [c for c, _ in sorted(cat_tot.items(), key=lambda kv: -kv[1])[:4]]323 if top4 and added_cur:324 multiseries.append({325 "id": "cat_ajouts",326 "title": f"Ajouts par {gran} — top {len(top4)} catégories",327 "unit": "événements",328 "series": [{"label": CAT_LABELS.get(c, c),329 "points": [{"t": buckets[i].isoformat(),330 "v": cat_added[c][i]}331 for i in range(len(buckets))]}332 for c in top4]})333334 # ---------- barres empilées ----------335 stacked = []336 if src_added and added_cur:337 top_src = [s for s, _ in sorted(338 ((s, sum(v)) for s, v in src_added.items()),339 key=lambda kv: -kv[1])[:5]]340 others = [s for s in src_added if s not in top_src]341 keys = [SRC_LABELS.get(s, s) for s in top_src] + (342 ["Autres"] if others else [])343 pts = []344 for i in range(len(buckets)):345 vals = [src_added[s][i] for s in top_src]346 if others:347 vals.append(sum(src_added[s][i] for s in others))348 pts.append({"t": buckets[i].isoformat(), "values": vals})349 stacked.append({"id": "src_ajouts",350 "title": f"Ajouts par {gran} et par source",351 "unit": "ajouts", "keys": keys, "points": pts})352353 # gratuits vs payants par semaine de tenue (12 prochaines semaines)354 monday = today - timedelta(days=today.weekday())355 wk_free, wk_paid, wk_unk = [0] * 12, [0] * 12, [0] * 12356 for r in con.execute(357 f"SELECT start_date, is_free FROM events WHERE {_upcoming_where()} "358 "AND start_date >= :today", tp):359 d = _parse_iso(r["start_date"])360 if not d:361 continue362 wi = ((d - timedelta(days=d.weekday())) - monday).days // 7363 if 0 <= wi < 12:364 if r["is_free"] == 1:365 wk_free[wi] += 1366 elif r["is_free"] == 0:367 wk_paid[wi] += 1368 else:369 wk_unk[wi] += 1370 if sum(wk_free) + sum(wk_paid) + sum(wk_unk) > 0:371 stacked.append({372 "id": "gratuite_temps",373 "title": "Gratuits vs payants — débuts par semaine (12 prochaines)",374 "unit": "événements",375 "keys": ["Gratuits", "Payants", "Prix non précisé"],376 "points": [{"t": (monday + timedelta(days=7 * i)).isoformat(),377 "values": [wk_free[i], wk_paid[i], wk_unk[i]]}378 for i in range(12)]})379380 # ---------- répartitions ----------381 cat_counts: dict[str, int] = {}382 for r in con.execute(383 f"SELECT categories FROM events WHERE {_upcoming_where()}", tp):384 for c in json.loads(r["categories"] or "[]"):385 cat_counts[c] = cat_counts.get(c, 0) + 1386 top_cats = sorted(cat_counts.items(), key=lambda kv: -kv[1])[:8]387388 # ajouts par région (période vs précédente) — deltas honnêtes389 reg_cur: dict[str, int] = {}390 reg_prev: dict[str, int] = {}391 for r in con.execute(392 "SELECT region, date(first_seen,'unixepoch','localtime') AS d, "393 "COUNT(*) AS n FROM events WHERE d >= ? AND d <= ? "394 "GROUP BY region, d",395 (prev_from.isoformat(), p_to.isoformat())):396 lbl = r["region"] or "Non rattachée"397 if r["d"] >= p_from.isoformat():398 reg_cur[lbl] = reg_cur.get(lbl, 0) + r["n"]399 else:400 reg_prev[lbl] = reg_prev.get(lbl, 0) + r["n"]401402 breakdowns = [403 {"id": "categories", "title": "Événements à venir par catégorie (top 8)",404 "kind": "donut",405 "items": [{"label": CAT_LABELS.get(c, c), "value": n}406 for c, n in top_cats]},407 {"id": "gratuite", "title": "Gratuits vs payants (à venir)", "kind": "bar",408 "items": [409 {"label": "Gratuits", "value": free_up},410 {"label": "Payants", "value": paid_up},411 {"label": "Prix non précisé", "value": unknown_price_up},412 ]},413 {"id": "sources", "title": "Événements actifs par source", "kind": "bar",414 "items": [{"label": SRC_LABELS.get(r["source"], r["source"]),415 "value": r["n"]}416 for r in con.execute(417 "SELECT source, COUNT(*) AS n FROM events WHERE active=1 AND quarantine IS NULL "418 "GROUP BY source ORDER BY n DESC")]},419 ]420 if reg_cur:421 breakdowns.append({422 "id": "reg_ajouts",423 "title": f"Ajouts par région ({p_label}) — Δ vs période précédente",424 "kind": "bar",425 "items": [{"label": lbl, "value": n,426 "delta_pct": _pct(n, reg_prev.get(lbl, 0))}427 for lbl, n in sorted(reg_cur.items(),428 key=lambda kv: -kv[1])[:12]]})429430 # ---------- distributions ----------431 distributions = []432 price_vals = [r[0] for r in con.execute(433 f"SELECT price_min FROM events WHERE {_upcoming_where()} "434 "AND is_free=0 AND price_min IS NOT NULL AND price_min > 0", tp)]435 if len(price_vals) >= 10:436 bins = []437 for lo, hi in PRICE_BINS:438 n = sum(1 for v in price_vals439 if v >= lo and (hi is None or v < hi))440 bins.append({"label": f"{lo}-{hi} $" if hi else f"{lo} $ +",441 "value": n})442 distributions.append({443 "id": "prix",444 "title": f"Distribution des prix d'entrée ({len(price_vals)} relevés, à venir)",445 "unit": "événements", "bins": bins})446 dur_bins = [0] * len(DURATION_BINS)447 n_dur = 0448 for r in con.execute(449 f"SELECT julianday(COALESCE(end_date,start_date)) - "450 f"julianday(start_date) + 1 AS j FROM events "451 f"WHERE {_upcoming_where()} AND start_date IS NOT NULL", tp):452 j = int(r["j"] or 1)453 n_dur += 1454 for i, (_, lo, hi) in enumerate(DURATION_BINS):455 if j >= lo and (hi is None or j <= hi):456 dur_bins[i] += 1457 break458 if n_dur:459 distributions.append({460 "id": "duree", "title": "Durée des événements à venir",461 "unit": "événements",462 "bins": [{"label": DURATION_BINS[i][0], "value": dur_bins[i]}463 for i in range(len(DURATION_BINS))]})464465 # ---------- géographie ----------466 geo = {"title": "Événements à venir par région", "items": [467 {"label": r["region"] or "Non rattachée", "value": r["n"]}468 for r in con.execute(469 f"SELECT region, COUNT(*) AS n FROM events WHERE {_upcoming_where()} "470 "GROUP BY region ORDER BY n DESC LIMIT 17", tp)]}471472 # ---------- heatmap : calendrier des dates d'événements (26 semaines) ----473 heatmap = {"title": "Calendrier des événements — jours les plus chargés",474 "cells": [{"date": (today + timedelta(days=i)).isoformat(),475 "value": horizon_counts[i]}476 for i in range(HEATMAP_DAYS)]}477478 # ---------- heatmap horaire 7×24 : heure de début des événements à venir --479 hourly = None480 hh: dict[tuple[int, int], int] = {}481 n_hourly = 0482 for r in con.execute(483 f"SELECT start_date, start_time FROM events WHERE {_upcoming_where()} "484 "AND start_time IS NOT NULL AND start_time != '' "485 "AND start_date IS NOT NULL", tp):486 d = _parse_iso(r["start_date"])487 try:488 h = int(str(r["start_time"])[:2])489 except ValueError:490 continue491 if d is None or not 0 <= h <= 23:492 continue493 hh[(d.weekday(), h)] = hh.get((d.weekday(), h), 0) + 1494 n_hourly += 1495 if n_hourly >= 20:496 hourly = {"title": f"Débuts d'événements par jour et heure "497 f"({n_hourly} événements à venir avec heure)",498 "cells": [{"dow": k[0], "hour": k[1], "value": v}499 for k, v in sorted(hh.items())]}500501 # ---------- tableaux ----------502 city_rows = [[r["city"], r["n"], r["g"],503 f"{100.0 * r['n'] / upcoming:.1f} %".replace(".", ",")]504 for r in con.execute(505 f"SELECT city, COUNT(*) AS n, "506 f"SUM(CASE WHEN is_free=1 THEN 1 ELSE 0 END) AS g "507 f"FROM events WHERE {_upcoming_where()} AND city != '' "508 "GROUP BY city ORDER BY n DESC LIMIT 40", tp)]509 venue_rows = [[r["venue"], r["city"] or "—", r["n"]]510 for r in con.execute(511 f"SELECT venue, city, COUNT(*) AS n FROM events "512 f"WHERE {_upcoming_where()} AND venue != '' "513 "GROUP BY venue, city ORDER BY n DESC LIMIT 40", tp)]514 big_days = sorted(515 ((today + timedelta(days=i), horizon_counts[i])516 for i in range(min(BIGDAYS_HORIZON, HEATMAP_DAYS))),517 key=lambda dv: -dv[1])[:15]518 bigday_rows = [[d.isoformat(), n, starts_by_day.get(d.isoformat(), 0)]519 for d, n in big_days]520 org_rows = [[r["organizer"], r["n"], r["g"], r["c"]]521 for r in con.execute(522 f"SELECT organizer, COUNT(*) AS n, "523 f"SUM(CASE WHEN is_free=1 THEN 1 ELSE 0 END) AS g, "524 f"COUNT(DISTINCT city) AS c FROM events "525 f"WHERE {_upcoming_where()} AND organizer != '' "526 "GROUP BY organizer ORDER BY n DESC LIMIT 40", tp)]527 # sources & connecteurs : actifs, ajoutés (période), dernière synchro OK528 last_sync = {r["source"]: r["ts"] for r in con.execute(529 "SELECT source, MAX(ts) AS ts FROM sync_log WHERE error IS NULL "530 "GROUP BY source")}531 src_rows = []532 for r in con.execute(533 "SELECT source, COUNT(*) AS n FROM events WHERE active=1 AND quarantine IS NULL "534 "GROUP BY source ORDER BY n DESC"):535 s = r["source"]536 ts = last_sync.get(s)537 sync_txt = (datetime.fromtimestamp(ts, TZ).strftime("%Y-%m-%d %H:%M")538 if ts else "—")539 src_rows.append([SRC_LABELS.get(s, s), r["n"],540 sum(src_added.get(s, [])), sync_txt])541 tables = [542 {"id": "villes", "title": "Top villes (événements à venir)",543 "columns": ["Ville", "À venir", "Gratuits", "Part"], "rows": city_rows},544 {"id": "lieux", "title": "Top lieux d'événements (à venir)",545 "columns": ["Lieu", "Ville", "À venir"], "rows": venue_rows},546 {"id": "gros_jours",547 "title": f"Prochains gros jours ({BIGDAYS_HORIZON} jours)",548 "columns": ["Date", "Événements ce jour-là", "Débutent ce jour-là"],549 "rows": bigday_rows},550 {"id": "organisateurs", "title": "Top organisateurs (à venir)",551 "columns": ["Organisateur", "À venir", "Gratuits", "Villes"],552 "rows": org_rows},553 {"id": "sources_sync", "title": "Sources & connecteurs",554 "columns": ["Source", "Événements actifs", f"Ajoutés ({p_label})",555 "Dernière synchro"],556 "rows": src_rows},557 ]558559 # ---------- records ----------560 records = []561 if horizon_counts:562 i_max = max(range(HEATMAP_DAYS), key=lambda i: horizon_counts[i])563 records.append({"label": "Jour le plus chargé (26 prochaines semaines)",564 "value": f"{horizon_counts[i_max]:,} événements".replace(",", " "),565 "date": (today + timedelta(days=i_max)).isoformat()})566 best_w, best_wi = -1, 0567 for i in range(HEATMAP_DAYS - 6):568 s = sum(horizon_counts[i:i + 7])569 if s > best_w:570 best_w, best_wi = s, i571 records.append({"label": "Semaine la plus chargée (à venir)",572 "value": f"≈ {round(best_w / 7):,} événements/jour".replace(",", " "),573 "date": (today + timedelta(days=best_wi)).isoformat()})574 add_rec = con.execute(575 "SELECT date(first_seen,'unixepoch','localtime') AS d, COUNT(*) AS n "576 "FROM events GROUP BY d ORDER BY n DESC LIMIT 1").fetchone()577 if add_rec:578 records.append({"label": "Plus grosse journée d'ajouts",579 "value": f"{add_rec['n']:,} événements".replace(",", " "),580 "date": add_rec["d"]})581 if city_rows:582 records.append({"label": "Ville la plus active (à venir)",583 "value": f"{city_rows[0][0]} — "584 f"{city_rows[0][1]:,} événements".replace(",", " ")})585 if venue_rows:586 records.append({"label": "Lieu le plus actif (à venir)",587 "value": f"{venue_rows[0][0]} — "588 f"{venue_rows[0][2]:,} événements".replace(",", " ")})589 if geo["items"]:590 g0 = geo["items"][0]591 records.append({"label": "Région la plus animée (à venir)",592 "value": f"{g0['label']} — "593 f"{g0['value']:,} événements".replace(",", " ")})594 if top_cats:595 records.append({"label": "Catégorie dominante (à venir)",596 "value": f"{CAT_LABELS.get(top_cats[0][0], top_cats[0][0])}"597 f" — {top_cats[0][1]:,} événements".replace(",", " ")})598 if hh:599 (dow, hr), n_pk = max(hh.items(), key=lambda kv: kv[1])600 days_fr = ["lundi", "mardi", "mercredi", "jeudi",601 "vendredi", "samedi", "dimanche"]602 records.append({"label": "Créneau de début le plus fréquent (à venir)",603 "value": f"{days_fr[dow]} {hr} h — "604 f"{n_pk:,} événements".replace(",", " ")})605 if src_added and added_cur:606 s_top = max(src_added.items(), key=lambda kv: sum(kv[1]))607 records.append({"label": f"Source la plus productive ({p_label})",608 "value": f"{SRC_LABELS.get(s_top[0], s_top[0])} — "609 f"{sum(s_top[1]):,} ajouts".replace(",", " ")})610 longest = con.execute(611 f"SELECT title, start_date, end_date, "612 f"julianday(end_date) - julianday(start_date) + 1 AS j "613 f"FROM events WHERE {_upcoming_where()} AND end_date > start_date "614 "ORDER BY j DESC LIMIT 1", tp).fetchone()615 if longest:616 records.append({"label": "Plus longue affiche à venir — "617 f"{longest['title'][:36]}",618 "value": f"{int(longest['j']):,} jours".replace(",", " "),619 "date": longest["start_date"]})620 con.close()621622 payload = {623 "updated": now.isoformat(timespec="seconds"),624 "period": {"from": p_from.isoformat(), "to": p_to.isoformat(),625 "label": p_label},626 "kpis": kpis, "gauges": gauges, "series": series,627 "multiseries": multiseries, "stacked": stacked,628 "breakdowns": breakdowns, "distributions": distributions,629 "geo": geo, "heatmap": heatmap, "tables": tables, "records": records,630 }631 if hourly:632 payload["hourly"] = hourly633 _cache[key] = (time.time(), payload)634 return payload635