HTML 82.1%
Python 14.6%
TypeScript 1.9%
CSS 1%
JavaScript 0.5%
1# =============================================================================2# Job·Ka — Groupe KA3# Auteur : Simon-Pierre Boucher4# Contact : contact@spboucher.ai5# Fichier : jobka/statsdash.py6# Rôle : Tableau de bord statistique — contrat commun Groupe KA v27# (voir frontend/src/ka/stats/SPEC.md). Construit le JSON du dashboard à8# partir de requêtes SQL agrégées (jobs, sync_log, sources.json) avec un9# cache mémoire de 5 minutes par clé de période. AUCUNE stat inventée : une10# section sans donnée réelle est simplement absente du JSON (le front11# affiche « Pas encore mesuré »). Les salaires publiés sont bornés12# [SAL_MIN, SAL_MAX] $/an (et [SALH_MIN, SALH_MAX] $/h) pour écarter les13# aberrations de lecture à la source.14# v2 : sparklines KPI, jauges de complétude, série des retraits,15# multi-courbes (top catégories), barres empilées (ajouts par source),16# distributions (salaires annuels/horaires, âge des offres), heatmap17# horaire 7×24 (observations des connecteurs), tableaux catégories +18# sources, records enrichis. Deltas HONNÊTES : calculés seulement quand la19# période précédente est couverte par nos observations.20# Créé : 2026-08-17 Modifié : 2026-08-1921# =============================================================================22from __future__ import annotations2324import json25import threading26import time27from datetime import date, datetime, timedelta28from pathlib import Path29from zoneinfo import ZoneInfo3031from . import db3233TZ = ZoneInfo("America/Toronto")3435CACHE_TTL = 300 # secondes36_cache: dict[str, tuple[float, dict]] = {}37_cache_lock = threading.Lock()3839SOURCES_PATH = Path(__file__).resolve().parent.parent / "data" / "sources.json"4041# Bornes de plausibilité des salaires publiés : en dehors, c'est presque42# toujours une erreur de parsing à la source (taux horaire annualisé deux43# fois, montant par quart, etc.), pas un vrai salaire.44SAL_MIN, SAL_MAX = 25_000, 400_000 # $ CA / an45SALH_MIN, SALH_MAX = 12.0, 150.0 # $ CA / h4647PERIODS = {48 "auj": ("Aujourd'hui", 0),49 "7j": ("7 jours", 6),50 "30j": ("30 jours", 29),51 "3m": ("3 mois", 89),52 "6m": ("6 mois", 179),53 "12m": ("12 mois", 364),54}5556ATS_FR = {57 "workday": "Workday", "smartrecruiters": "SmartRecruiters",58 "workable": "Workable", "lever": "Lever", "ashby": "Ashby",59 "bamboohr": "BambooHR", "breezy": "Breezy", "recruitee": "Recruitee",60 "greenhouse": "Greenhouse", "custom": "Site employeur",61 "": "Autre / sur mesure",62}63MODE_FR = {"presentiel": "Présentiel", "hybride": "Hybride",64 "teletravail": "Télétravail", None: "Non précisé", "": "Non précisé"}65TYPE_FR = {"temps_plein": "Temps plein", "temps_partiel": "Temps partiel",66 "contractuel": "Contractuel", "stage": "Stage",67 "saisonnier": "Saisonnier", None: "Non précisé", "": "Non précisé"}6869# Fourchettes salariales annualisées (bornes inférieures, $ / an)70SAL_BUCKETS = [71 (SAL_MIN, 40_000, "25 k$ – 40 k$"),72 (40_000, 60_000, "40 k$ – 60 k$"),73 (60_000, 80_000, "60 k$ – 80 k$"),74 (80_000, 100_000, "80 k$ – 100 k$"),75 (100_000, 130_000, "100 k$ – 130 k$"),76 (130_000, SAL_MAX + 1, "130 k$ et plus"),77]7879# Fourchettes de taux horaires publiés ($ / h)80SALH_BUCKETS = [81 (SALH_MIN, 18.0, "12 $ – 18 $/h"),82 (18.0, 22.0, "18 $ – 22 $/h"),83 (22.0, 26.0, "22 $ – 26 $/h"),84 (26.0, 30.0, "26 $ – 30 $/h"),85 (30.0, 36.0, "30 $ – 36 $/h"),86 (36.0, 45.0, "36 $ – 45 $/h"),87 (45.0, SALH_MAX + 1, "45 $/h et plus"),88]8990# Âge des offres actives (jours depuis la date de publication AFFICHÉE)91AGE_BUCKETS = [92 (0, 7, "0–7 j"),93 (7, 14, "8–14 j"),94 (14, 30, "15–30 j"),95 (30, 60, "31–60 j"),96 (60, 90, "61–90 j"),97 (90, 100_000, "Plus de 90 j"),98]99100# Milieu de fourchette annualisé / horaire (expressions SQL réutilisées)101_SAL_MID = ("(COALESCE(salary_year_min, salary_year_max)"102 " + COALESCE(salary_year_max, salary_year_min)) / 2.0")103_SALH_MID = ("(COALESCE(salary_hour_min, salary_hour_max)"104 " + COALESCE(salary_hour_max, salary_hour_min)) / 2.0")105106107def _sal_where() -> str:108 """Clause : offre avec salaire annualisé publié ET plausible."""109 return (f" AND (salary_year_min IS NOT NULL OR salary_year_max IS NOT NULL)"110 f" AND {_SAL_MID} BETWEEN {SAL_MIN} AND {SAL_MAX}")111112113def _salh_where() -> str:114 """Clause : offre avec taux horaire publié ET plausible."""115 return (f" AND (salary_hour_min IS NOT NULL OR salary_hour_max IS NOT NULL)"116 f" AND {_SALH_MID} BETWEEN {SALH_MIN} AND {SALH_MAX}")117118119# ---------------------------------------------------------------- utilitaires120def _day_start_ts(d: date) -> float:121 return datetime(d.year, d.month, d.day, tzinfo=TZ).timestamp()122123124def _coverage(con) -> tuple[date | None, date | None]:125 """Première et dernière date OBSERVÉES par Job·Ka (first/last_seen)."""126 row = con.execute(127 "SELECT MIN(first_seen) a, MAX(last_seen) b FROM jobs"128 " WHERE dup_of IS NULL").fetchone()129 if row["a"] is None:130 return None, None131 return (datetime.fromtimestamp(row["a"], TZ).date(),132 datetime.fromtimestamp(row["b"], TZ).date())133134135def resolve_period(period: str, from_: str | None, to_: str | None,136 cov_min: date, today: date) -> tuple[date, date, str]:137 """Bornes [from, to] (dates locales incluses) + libellé humain."""138 if from_ and to_:139 try:140 a = date.fromisoformat(from_)141 b = date.fromisoformat(to_)142 if a > b:143 a, b = b, a144 return max(a, cov_min), min(b, today), f"{a} → {b}"145 except ValueError:146 pass147 if period == "tout":148 return cov_min, today, "Toute la période"149 if period == "annee":150 return max(date(today.year, 1, 1), cov_min), today, "Année en cours"151 label, back = PERIODS.get(period, PERIODS["30j"])152 return max(today - timedelta(days=back), cov_min), today, label153154155def _pct(cur: float, prev: float) -> float | None:156 if not prev:157 return None158 return round(100.0 * (cur - prev) / prev, 1)159160161def _days(a: date, b: date) -> list[date]:162 return [a + timedelta(days=i) for i in range((b - a).days + 1)]163164165def _fr_int(n: float) -> str:166 return f"{int(round(n)):,}".replace(",", " ")167168169def _fr_money(n: float) -> str:170 return _fr_int(n) + " $"171172173def _spark(points: list[dict], cap: int = 40) -> list[dict]:174 """Échantillonne une série pour la sparkline d'un KPI (≤ cap points)."""175 if len(points) <= cap:176 return points177 step = (len(points) - 1) / (cap - 1)178 idx = sorted({round(i * step) for i in range(cap)})179 return [points[i] for i in idx if i < len(points)]180181182def _source_names() -> dict[str, str]:183 """id de connecteur -> nom humain (registre data/sources.json)."""184 try:185 reg = json.loads(SOURCES_PATH.read_text(encoding="utf-8"))["sources"]186 return {s["id"]: s.get("name") or s["id"] for s in reg}187 except (OSError, ValueError, KeyError):188 return {}189190191# ------------------------------------------------------------------- dashboard192def compute(period: str = "30j", from_: str | None = None,193 to_: str | None = None) -> dict:194 key = f"{period}|{from_ or ''}|{to_ or ''}"195 now = time.time()196 with _cache_lock:197 hit = _cache.get(key)198 if hit and hit[0] > now:199 return hit[1]200 data = _compute(period, from_, to_)201 with _cache_lock:202 _cache[key] = (now + CACHE_TTL, data)203 return data204205206def _compute(period: str, from_: str | None, to_: str | None) -> dict:207 con = db.connect()208 try:209 return _build(con, period, from_, to_)210 finally:211 con.close()212213214def _build(con, period: str, from_: str | None, to_: str | None) -> dict:215 today = datetime.now(TZ).date()216 cov_min, _cov_max = _coverage(con)217 if cov_min is None: # base vide218 return {"updated": datetime.now(TZ).isoformat(),219 "period": {"from": None, "to": None, "label": "—"},220 "kpis": [], "series": [], "breakdowns": [], "tables": [],221 "records": []}222223 d_from, d_to, label = resolve_period(period, from_, to_, cov_min, today)224 ts_from = _day_start_ts(d_from)225 ts_to = _day_start_ts(d_to + timedelta(days=1)) # borne exclusive226 iso_from, iso_to = d_from.isoformat(), d_to.isoformat()227 BASE = " FROM jobs WHERE active=1 AND dup_of IS NULL"228229 # ---- histogrammes journaliers (réutilisés partout) ----------------------230 starts = {r["d"]: r["n"] for r in con.execute(231 "SELECT date(first_seen,'unixepoch','localtime') d, COUNT(*) n"232 " FROM jobs WHERE dup_of IS NULL GROUP BY d")}233 ends = {r["d"]: r["n"] for r in con.execute(234 "SELECT date(last_seen,'unixepoch','localtime') d, COUNT(*) n"235 " FROM jobs WHERE dup_of IS NULL AND active=0 GROUP BY d")}236237 def actives_at(d: date) -> int:238 """Reconstruction : cum(first_seen<=d) − cum(retraits<=d−1)."""239 iso = d.isoformat()240 prev = (d - timedelta(days=1)).isoformat()241 s = sum(n for dd, n in starts.items() if dd <= iso)242 e = sum(n for dd, n in ends.items() if dd <= prev)243 return s - e244245 # ---- KPI -----------------------------------------------------------------246 actives_now = con.execute(f"SELECT COUNT(*) n{BASE}").fetchone()["n"]247 employers_now = con.execute(248 f"SELECT COUNT(DISTINCT employer) n{BASE} AND employer<>''"249 ).fetchone()["n"]250 cities_now = con.execute(251 f"SELECT COUNT(DISTINCT city) n{BASE} AND city<>''").fetchone()["n"]252 sources_now = con.execute(253 f"SELECT COUNT(DISTINCT source) n{BASE}").fetchone()["n"]254 new_in = sum(n for d, n in starts.items() if iso_from <= d <= iso_to)255 removed_in = sum(n for d, n in ends.items() if iso_from <= d <= iso_to)256257 # période précédente de même longueur — deltas SEULEMENT si couverte258 span = (d_to - d_from).days + 1259 p_from, p_to = d_from - timedelta(days=span), d_from - timedelta(days=1)260 prev_ok = p_from >= cov_min261 new_prev = removed_prev = None262 if prev_ok:263 new_prev = sum(n for d, n in starts.items()264 if p_from.isoformat() <= d <= p_to.isoformat())265 removed_prev = sum(n for d, n in ends.items()266 if p_from.isoformat() <= d <= p_to.isoformat())267 actives_prev = actives_at(p_to) if p_to >= cov_min else None268269 row = con.execute(270 f"SELECT AVG({_SAL_MID}) avg_s, COUNT(*) n_s{BASE}{_sal_where()}"271 ).fetchone()272 avg_sal, n_sal = row["avg_s"], row["n_s"]273 median_sal = None274 if n_sal:275 median_sal = con.execute(276 f"SELECT {_SAL_MID} m{BASE}{_sal_where()}"277 f" ORDER BY m LIMIT 1 OFFSET ?", (n_sal // 2,)).fetchone()["m"]278279 remote_n = con.execute(280 f"SELECT COUNT(*) n{BASE} AND work_mode='teletravail'").fetchone()["n"]281 pct_remote = round(100.0 * remote_n / actives_now, 1) if actives_now else None282283 # offres directes (pages carrières + dépôt direct) c. portails agrégateurs284 from .dedup import AGGREGATORS285 _agg = sorted(AGGREGATORS) or ["__aucun__"]286 _ph = ",".join("?" * len(_agg))287 direct_n = con.execute(288 f"SELECT COUNT(*) n{BASE} AND source NOT IN ({_ph})", _agg).fetchone()["n"]289 pct_direct = round(100.0 * direct_n / actives_now, 1) if actives_now else None290291 days = _days(d_from, d_to)292 spark_act = _spark([{"t": d.isoformat(), "v": actives_at(d)} for d in days]) \293 if len(days) >= 2 else None294 spark_new = _spark([{"t": d.isoformat(), "v": starts.get(d.isoformat(), 0)}295 for d in days]) if len(days) >= 2 else None296 spark_rem = _spark([{"t": d.isoformat(), "v": ends.get(d.isoformat(), 0)}297 for d in days]) if len(days) >= 2 else None298299 kpis = [300 {"id": "actives", "label": "Offres actives", "value": actives_now,301 "delta_pct": _pct(actives_now, actives_prev) if actives_prev else None,302 "direction": ("up" if actives_now >= actives_prev else "down")303 if actives_prev else None,304 "spark": spark_act},305 {"id": "nouvelles", "label": "Nouvelles offres (période)",306 "value": new_in,307 "delta_pct": _pct(new_in, new_prev) if prev_ok else None,308 "direction": ("up" if new_in >= (new_prev or 0) else "down")309 if prev_ok else None,310 "spark": spark_new},311 {"id": "retirees", "label": "Offres retirées ou expirées (période)",312 "value": removed_in,313 "delta_pct": _pct(removed_in, removed_prev) if prev_ok else None,314 "direction": ("down" if removed_in >= (removed_prev or 0) else "up")315 if prev_ok else None,316 "spark": spark_rem},317 {"id": "employeurs", "label": "Employeurs avec offres actives",318 "value": employers_now},319 {"id": "villes", "label": "Villes couvertes (offres actives)",320 "value": cities_now},321 {"id": "sources", "label": "Sources avec offres actives",322 "value": sources_now},323 ]324 if median_sal:325 kpis.append({"id": "salaire_median",326 "label": "Salaire annuel médian publié",327 "value": round(median_sal), "unit": "$"})328 if avg_sal:329 kpis.append({"id": "salaire_moyen",330 "label": "Salaire annuel moyen publié",331 "value": round(avg_sal), "unit": "$"})332 if pct_remote is not None:333 kpis.append({"id": "teletravail", "label": "Offres en télétravail",334 "value": pct_remote, "unit": "%"})335 if pct_direct is not None:336 kpis.append({"id": "directes", "label": "Offres directes employeur",337 "value": pct_direct, "unit": "%"})338 kpis = [{k: v for k, v in kpi.items() if v is not None} for kpi in kpis]339340 # ---- jauges (complétude des fiches actives) --------------------------------341 gauges = []342 if actives_now:343 comp = con.execute(344 f"""SELECT345 SUM(CASE WHEN (salary_year_min IS NOT NULL346 OR salary_year_max IS NOT NULL347 OR salary_hour_min IS NOT NULL348 OR salary_hour_max IS NOT NULL)349 THEN 1 ELSE 0 END) sal,350 SUM(CASE WHEN work_mode IS NOT NULL AND work_mode<>''351 THEN 1 ELSE 0 END) mode,352 SUM(CASE WHEN lat IS NOT NULL AND lng IS NOT NULL353 THEN 1 ELSE 0 END) geo,354 SUM(CASE WHEN category<>'' THEN 1 ELSE 0 END) cat355 {BASE}""").fetchone()356 gauges = [357 {"id": "salaire", "label": "Offres avec salaire affiché",358 "value": round(100.0 * comp["sal"] / actives_now, 1), "max": 100,359 "unit": "%",360 "help": "Part des offres actives dont l'employeur publie un salaire"},361 {"id": "mode", "label": "Mode de travail précisé",362 "value": round(100.0 * comp["mode"] / actives_now, 1), "max": 100,363 "unit": "%",364 "help": "Présentiel, hybride ou télétravail explicitement indiqué"},365 {"id": "geoloc", "label": "Offres géolocalisées",366 "value": round(100.0 * comp["geo"] / actives_now, 1), "max": 100,367 "unit": "%",368 "help": "Offres positionnées sur la carte (lat/lng)"},369 {"id": "categorie", "label": "Offres catégorisées",370 "value": round(100.0 * comp["cat"] / actives_now, 1), "max": 100,371 "unit": "%",372 "help": "Offres rattachées à un secteur de la taxonomie Job·Ka"},373 ]374375 # ---- séries temporelles ----------------------------------------------------376 series = []377 if len(days) >= 2:378 s_act = {"id": "actives_jour", "title": "Offres actives par jour",379 "unit": "offres", "kind": "line",380 "points": [{"t": d.isoformat(), "v": actives_at(d)} for d in days]}381 s_new = {"id": "nouvelles_jour", "title": "Nouvelles offres par jour",382 "unit": "offres", "kind": "bar",383 "points": [{"t": d.isoformat(),384 "v": starts.get(d.isoformat(), 0)} for d in days]}385 s_rem = {"id": "retraits_jour",386 "title": "Offres retirées ou expirées par jour",387 "unit": "offres", "kind": "bar",388 "points": [{"t": d.isoformat(),389 "v": ends.get(d.isoformat(), 0)} for d in days]}390 if prev_ok:391 pdays = _days(p_from, p_to)392 s_act["compare"] = [{"t": d.isoformat(), "v": actives_at(d)}393 for d in pdays]394 series = [s_act, s_new, s_rem]395396 # dates de publication AFFICHÉES par les employeurs — données réelles qui397 # précèdent la mise en service de Job·Ka : la fenêtre demandée est prise398 # SANS la borner à la couverture d'observation (first_seen).399 rq_from, rq_to, _ = resolve_period(period, from_, to_, date(2000, 1, 1), today)400 rq_from = max(rq_from, today - timedelta(days=365)) # 12 mois max (lisibilité)401 posted_rows = con.execute(402 f"SELECT date_posted d, COUNT(*) n{BASE}"403 " AND date_posted IS NOT NULL AND date_posted>=? AND date_posted<=?"404 " GROUP BY date_posted ORDER BY date_posted",405 (rq_from.isoformat(), rq_to.isoformat())).fetchall()406 if len(posted_rows) >= 2:407 by_day = {r["d"]: r["n"] for r in posted_rows}408 series.append({"id": "publiees_jour",409 "title": "Offres publiées par jour "410 "(date affichée par l'employeur)",411 "unit": "offres", "kind": "line",412 "points": [{"t": d.isoformat(),413 "v": by_day.get(d.isoformat(), 0)}414 for d in _days(rq_from, rq_to)]})415416 # ---- multi-courbes : publications par top catégories (≤ 4) -----------------417 multiseries = []418 top_cats = [r["c"] for r in con.execute(419 f"SELECT category c, COUNT(*) n{BASE} AND category<>''"420 " AND date_posted IS NOT NULL AND date_posted>=? AND date_posted<=?"421 " GROUP BY category ORDER BY n DESC LIMIT 4",422 (rq_from.isoformat(), rq_to.isoformat()))]423 if top_cats:424 rows = con.execute(425 f"SELECT category c, date_posted d, COUNT(*) n{BASE}"426 f" AND category IN ({','.join('?' * len(top_cats))})"427 " AND date_posted IS NOT NULL AND date_posted>=? AND date_posted<=?"428 " GROUP BY category, date_posted",429 (*top_cats, rq_from.isoformat(), rq_to.isoformat())).fetchall()430 grid: dict[str, dict[str, int]] = {c: {} for c in top_cats}431 for r in rows:432 grid[r["c"]][r["d"]] = r["n"]433 mdays = _days(rq_from, rq_to)434 ms = [{"label": c,435 "points": [{"t": d.isoformat(), "v": grid[c].get(d.isoformat(), 0)}436 for d in mdays]}437 for c in top_cats]438 if len(mdays) >= 2 and any(sum(p["v"] for p in s["points"]) for s in ms):439 multiseries.append({440 "id": "cat_pub",441 "title": "Offres publiées par jour — top secteurs "442 "(date affichée par l'employeur)",443 "unit": "offres", "series": ms})444445 # ---- barres empilées : ajouts observés par source (sync_log) ---------------446 stacked = []447 add_rows = con.execute(448 "SELECT date(ts,'unixepoch','localtime') d, source, SUM(added) n"449 " FROM sync_log WHERE ts>=? AND ts<? AND added>0"450 " GROUP BY d, source", (ts_from, ts_to)).fetchall()451 if add_rows:452 names = _source_names()453 totals: dict[str, int] = {}454 for r in add_rows:455 totals[r["source"]] = totals.get(r["source"], 0) + r["n"]456 top_src = [s for s, _ in sorted(totals.items(), key=lambda kv: -kv[1])[:5]]457 keys = [names.get(s, s) for s in top_src]458 others = len(totals) > len(top_src)459 if others:460 keys.append("Autres")461 grid2: dict[str, list[int]] = {}462 for r in add_rows:463 vals = grid2.setdefault(r["d"], [0] * len(keys))464 if r["source"] in top_src:465 vals[top_src.index(r["source"])] += r["n"]466 elif others:467 vals[-1] += r["n"]468 pts = [{"t": d.isoformat(),469 "values": grid2.get(d.isoformat(), [0] * len(keys))}470 for d in days]471 if any(sum(p["values"]) for p in pts):472 stacked.append({"id": "ajouts_source",473 "title": "Offres ajoutées par source (journal de "474 "synchronisation)",475 "unit": "offres", "keys": keys, "points": pts})476477 # ---- répartitions ----------------------------------------------------------478 # deltas de répartition : reconstruction des actifs à p_to par dimension,479 # seulement si la période précédente est couverte (deltas honnêtes)480 def _dim_delta(col: str) -> dict[str, float]:481 if p_to < cov_min:482 return {}483 ts_prev = _day_start_ts(p_to + timedelta(days=1))484 s_prev = {r["k"]: r["n"] for r in con.execute(485 f"SELECT {col} k, COUNT(*) n FROM jobs WHERE dup_of IS NULL"486 " AND first_seen<? GROUP BY k", (ts_prev,))}487 e_prev = {r["k"]: r["n"] for r in con.execute(488 f"SELECT {col} k, COUNT(*) n FROM jobs WHERE dup_of IS NULL"489 " AND active=0 AND last_seen<? GROUP BY k",490 (_day_start_ts(p_to),))}491 cur = {r["k"]: r["n"] for r in con.execute(492 f"SELECT {col} k, COUNT(*) n{BASE} GROUP BY k")}493 out = {}494 for k, n in cur.items():495 prev = s_prev.get(k, 0) - e_prev.get(k, 0)496 d = _pct(n, prev)497 if d is not None:498 out[k] = d499 return out500501 breakdowns = []502 cat_delta = _dim_delta("category")503 cat_items = []504 for r in con.execute(505 f"SELECT category c, COUNT(*) n{BASE} AND category<>''"506 " GROUP BY category ORDER BY n DESC LIMIT 12"):507 it = {"label": r["c"], "value": r["n"]}508 if r["c"] in cat_delta:509 it["delta_pct"] = cat_delta[r["c"]]510 cat_items.append(it)511 if cat_items:512 breakdowns.append({"id": "categories",513 "title": "Offres actives par secteur",514 "kind": "bar", "items": cat_items})515516 type_items = [{"label": TYPE_FR.get(r["t"], r["t"] or "Non précisé"),517 "value": r["n"]}518 for r in con.execute(519 f"SELECT employment_type t, COUNT(*) n{BASE}"520 " GROUP BY employment_type ORDER BY n DESC")]521 if type_items:522 breakdowns.append({"id": "types",523 "title": "Temps plein, temps partiel, contrat…",524 "kind": "donut", "items": type_items})525526 mode_items = [{"label": MODE_FR.get(r["m"], r["m"] or "Non précisé"),527 "value": r["n"]}528 for r in con.execute(529 f"SELECT work_mode m, COUNT(*) n{BASE}"530 " GROUP BY work_mode ORDER BY n DESC")]531 if mode_items:532 breakdowns.append({"id": "modes",533 "title": "Télétravail, hybride, présentiel",534 "kind": "donut", "items": mode_items})535536 ats_items = [{"label": ATS_FR.get(r["a"] or "", (r["a"] or "").title()),537 "value": r["n"]}538 for r in con.execute(539 f"SELECT ats a, COUNT(*) n{BASE}"540 " GROUP BY ats ORDER BY n DESC LIMIT 10")]541 if ats_items:542 breakdowns.append({"id": "ats",543 "title": "Offres actives par plateforme ATS",544 "kind": "donut", "items": ats_items})545546 # ---- distributions -----------------------------------------------------------547 distributions = []548 sal_bins = []549 for lo, hi, blabel in SAL_BUCKETS:550 n = con.execute(551 f"SELECT COUNT(*) n{BASE}{_sal_where()}"552 f" AND {_SAL_MID} >= ? AND {_SAL_MID} < ?", (lo, hi)).fetchone()["n"]553 sal_bins.append({"label": blabel, "value": n})554 if any(b["value"] for b in sal_bins):555 distributions.append({"id": "salaires_annuels",556 "title": "Distribution des salaires annuels "557 "publiés (offres actives)",558 "unit": "offres", "bins": sal_bins})559560 salh_bins = []561 for lo, hi, blabel in SALH_BUCKETS:562 n = con.execute(563 f"SELECT COUNT(*) n{BASE}{_salh_where()}"564 f" AND {_SALH_MID} >= ? AND {_SALH_MID} < ?", (lo, hi)).fetchone()["n"]565 salh_bins.append({"label": blabel, "value": n})566 if any(b["value"] for b in salh_bins):567 distributions.append({"id": "salaires_horaires",568 "title": "Distribution des taux horaires "569 "publiés (offres actives)",570 "unit": "offres", "bins": salh_bins})571572 age_bins = []573 for lo, hi, blabel in AGE_BUCKETS:574 n = con.execute(575 f"""SELECT COUNT(*) n{BASE} AND date_posted IS NOT NULL576 AND CAST(julianday('now','localtime')577 - julianday(date_posted) AS INTEGER) >= ?578 AND CAST(julianday('now','localtime')579 - julianday(date_posted) AS INTEGER) < ?""",580 (lo, hi)).fetchone()["n"]581 age_bins.append({"label": blabel, "value": n})582 if any(b["value"] for b in age_bins):583 distributions.append({"id": "age_offres",584 "title": "Âge des offres actives (jours depuis "585 "la publication affichée)",586 "unit": "offres", "bins": age_bins})587588 # ---- géographie (région administrative peu remplie -> villes) --------------589 geo_items = [{"label": r["c"], "value": r["n"]} for r in con.execute(590 f"SELECT city c, COUNT(*) n{BASE} AND city<>''"591 " GROUP BY city ORDER BY n DESC LIMIT 14")]592 geo = ({"title": "Top villes (offres actives)", "items": geo_items}593 if geo_items else None)594595 # ---- heatmap calendrier (nouvelles offres observées par jour) --------------596 heat_cells = [{"date": d.isoformat(), "value": starts.get(d.isoformat(), 0)}597 for d in days if starts.get(d.isoformat())]598 heatmap = ({"title": "Nouvelles offres par jour", "cells": heat_cells}599 if heat_cells else None)600601 # ---- heatmap horaire 7×24 (ajouts observés par les connecteurs) ------------602 hourly = None603 hr_rows = con.execute(604 """SELECT CAST(strftime('%w', ts,'unixepoch','localtime') AS INTEGER) w,605 CAST(strftime('%H', ts,'unixepoch','localtime') AS INTEGER) h,606 SUM(added) n607 FROM sync_log WHERE ts>=? AND ts<? AND added>0608 GROUP BY w, h""", (ts_from, ts_to)).fetchall()609 hr_cells = [{"dow": (r["w"] + 6) % 7, "hour": r["h"], "value": r["n"]}610 for r in hr_rows if r["n"]]611 if hr_cells:612 hourly = {"title": "Offres ajoutées par heure d'observation",613 "cells": hr_cells}614615 # ---- tableaux ----------------------------------------------------------------616 tables = []617 top_emp = con.execute(618 f"""SELECT employer, COUNT(*) n, COUNT(DISTINCT city) nc,619 AVG(CASE WHEN {_SAL_MID} BETWEEN {SAL_MIN} AND {SAL_MAX}620 AND (salary_year_min IS NOT NULL621 OR salary_year_max IS NOT NULL)622 THEN {_SAL_MID} END) avg_s,623 SUM(CASE WHEN first_seen>=? AND first_seen<? THEN 1 ELSE 0 END) new_n624 {BASE} AND employer<>''625 GROUP BY employer ORDER BY n DESC LIMIT 50""",626 (ts_from, ts_to)).fetchall()627 if top_emp:628 tables.append({629 "id": "top_employeurs", "title": "Top employeurs",630 "columns": ["Employeur", "Offres actives", "Villes",631 "Salaire annuel moyen publié", "Nouvelles (période)"],632 "rows": [[r["employer"], r["n"], r["nc"],633 _fr_money(r["avg_s"]) if r["avg_s"] else "—",634 r["new_n"]] for r in top_emp]})635636 top_cities = con.execute(637 f"""SELECT city, COUNT(*) n, COUNT(DISTINCT employer) ne,638 AVG(CASE WHEN {_SAL_MID} BETWEEN {SAL_MIN} AND {SAL_MAX}639 AND (salary_year_min IS NOT NULL640 OR salary_year_max IS NOT NULL)641 THEN {_SAL_MID} END) avg_s,642 SUM(CASE WHEN first_seen>=? AND first_seen<? THEN 1 ELSE 0 END) new_n643 {BASE} AND city<>''644 GROUP BY city ORDER BY n DESC LIMIT 50""",645 (ts_from, ts_to)).fetchall()646 if top_cities:647 tables.append({648 "id": "top_villes", "title": "Top villes",649 "columns": ["Ville", "Offres actives", "Employeurs",650 "Salaire annuel moyen publié", "Nouvelles (période)"],651 "rows": [[r["city"], r["n"], r["ne"],652 _fr_money(r["avg_s"]) if r["avg_s"] else "—",653 r["new_n"]] for r in top_cities]})654655 cat_rows = con.execute(656 f"""SELECT category, COUNT(*) n,657 AVG(CASE WHEN {_SAL_MID} BETWEEN {SAL_MIN} AND {SAL_MAX}658 AND (salary_year_min IS NOT NULL659 OR salary_year_max IS NOT NULL)660 THEN {_SAL_MID} END) avg_s,661 SUM(CASE WHEN first_seen>=? AND first_seen<? THEN 1 ELSE 0 END) new_n662 {BASE} AND category<>''663 GROUP BY category ORDER BY n DESC LIMIT 30""",664 (ts_from, ts_to)).fetchall()665 if cat_rows:666 def _dlt(c):667 d = cat_delta.get(c)668 return (("+" if d >= 0 else "") + f"{d:.1f}".replace(".", ",") + " %") \669 if d is not None else "—"670 tables.append({671 "id": "secteurs", "title": "Secteurs d'emploi",672 "columns": ["Secteur", "Offres actives",673 "Salaire annuel moyen publié", "Nouvelles (période)",674 "Δ actives"],675 "rows": [[r["category"], r["n"],676 _fr_money(r["avg_s"]) if r["avg_s"] else "—",677 r["new_n"], _dlt(r["category"])] for r in cat_rows]})678679 # sources & connecteurs : offres actives + journal de synchronisation680 names = _source_names()681 src_jobs = {r["s"]: r["n"] for r in con.execute(682 f"SELECT source s, COUNT(*) n{BASE} GROUP BY source")}683 src_sync = {r["s"]: r for r in con.execute(684 """SELECT source s, SUM(CASE WHEN ts>=? AND ts<? THEN added ELSE 0 END) a,685 MAX(ts) last_ts686 FROM sync_log GROUP BY source""", (ts_from, ts_to))}687 src_last_ok = {r["s"]: r["ok"] for r in con.execute(688 """SELECT source s, ok FROM sync_log689 WHERE id IN (SELECT MAX(id) FROM sync_log GROUP BY source)""")}690 src_rows = []691 for s, n in sorted(src_jobs.items(), key=lambda kv: -kv[1])[:60]:692 sy = src_sync.get(s)693 last = (datetime.fromtimestamp(sy["last_ts"], TZ)694 .strftime("%Y-%m-%d %H:%M") if sy and sy["last_ts"] else "—")695 src_rows.append([names.get(s, s), n,696 sy["a"] if sy else 0, last,697 "OK" if src_last_ok.get(s, 1) else "Échec"])698 if src_rows:699 tables.append({700 "id": "sources", "title": "Sources & connecteurs",701 "columns": ["Source", "Offres actives", "Ajouts (période)",702 "Dernière synchro", "Statut"],703 "rows": src_rows})704705 new_rows = con.execute(706 f"""SELECT title, employer, city, salary_label, date_posted707 {BASE} AND first_seen>=? AND first_seen<?708 ORDER BY date_posted IS NULL, date_posted DESC, first_seen DESC709 LIMIT 50""", (ts_from, ts_to)).fetchall()710 if new_rows:711 tables.append({712 "id": "nouvelles_offres", "title": "Nouvelles offres (période)",713 "columns": ["Titre", "Employeur", "Ville", "Salaire publié",714 "Publiée le"],715 "rows": [[r["title"][:70], r["employer"], r["city"] or "—",716 r["salary_label"] or "—", r["date_posted"] or "—"]717 for r in new_rows]})718719 # ---- records & faits marquants -------------------------------------------------720 records = []721 in_period = {d: n for d, n in starts.items() if iso_from <= d <= iso_to}722 if in_period:723 best = max(in_period, key=in_period.get)724 records.append({"label": "Jour record de nouvelles offres",725 "value": _fr_int(in_period[best]) + " offres",726 "date": best})727 rem_period = {d: n for d, n in ends.items() if iso_from <= d <= iso_to}728 if rem_period:729 worst = max(rem_period, key=rem_period.get)730 records.append({"label": "Jour record de retraits d'offres",731 "value": _fr_int(rem_period[worst]) + " offres",732 "date": worst})733 top_sal = con.execute(734 f"""SELECT title, employer, salary_year_max s735 {BASE} AND salary_year_max BETWEEN {SAL_MIN} AND {SAL_MAX}736 ORDER BY salary_year_max DESC LIMIT 1""").fetchone()737 if top_sal:738 records.append({739 "label": "Salaire publié le plus élevé (borné, offres actives)",740 "value": f"{_fr_money(top_sal['s'])} / an — "741 f"{top_sal['title'][:40]} ({top_sal['employer']})"})742 if top_emp:743 emp = max(top_emp, key=lambda r: r["new_n"])744 if emp["new_n"]:745 records.append({"label": "Employeur le plus actif "746 "(nouvelles offres, période)",747 "value": f"{emp['employer']} — "748 f"{_fr_int(emp['new_n'])} offres"})749 records.append({"label": "Employeur au plus grand nombre "750 "d'offres actives",751 "value": f"{top_emp[0]['employer']} — "752 f"{_fr_int(top_emp[0]['n'])} offres"})753 if top_cities:754 dyn = max(top_cities, key=lambda r: r["new_n"])755 if dyn["new_n"]:756 records.append({"label": "Ville la plus dynamique "757 "(nouvelles offres, période)",758 "value": f"{dyn['city']} — "759 f"{_fr_int(dyn['new_n'])} offres"})760 if cat_rows:761 records.append({"label": "Secteur dominant (offres actives)",762 "value": f"{cat_rows[0]['category']} — "763 f"{_fr_int(cat_rows[0]['n'])} offres"})764 if src_rows:765 records.append({"label": "Source la plus fournie (offres actives)",766 "value": f"{src_rows[0][0]} — "767 f"{_fr_int(src_rows[0][1])} offres"})768 oldest = con.execute(769 f"""SELECT title, employer, date_posted{BASE}770 AND date_posted IS NOT NULL AND date_posted >= '2000-01-01'771 ORDER BY date_posted ASC LIMIT 1""").fetchone()772 if oldest:773 records.append({"label": "Offre active la plus ancienne "774 "(date de publication affichée)",775 "value": f"{oldest['title'][:40]} "776 f"({oldest['employer']})",777 "date": oldest["date_posted"]})778 total_obs = con.execute(779 "SELECT COUNT(*) n FROM jobs WHERE dup_of IS NULL").fetchone()["n"]780 records.append({"label": "Offres observées depuis le lancement",781 "value": _fr_int(total_obs) + " offres",782 "date": cov_min.isoformat()})783 dups = con.execute(784 "SELECT COUNT(*) n FROM jobs WHERE active=1"785 " AND dup_of IS NOT NULL").fetchone()["n"]786 if dups:787 records.append({"label": "Doublons inter-sources masqués",788 "value": _fr_int(dups) + " offres"})789790 out = {791 "updated": datetime.now(TZ).isoformat(),792 "period": {"from": iso_from, "to": iso_to, "label": label},793 "kpis": kpis,794 "series": series,795 "breakdowns": breakdowns,796 "tables": tables,797 "records": records,798 }799 if gauges:800 out["gauges"] = gauges801 if multiseries:802 out["multiseries"] = multiseries803 if stacked:804 out["stacked"] = stacked805 if distributions:806 out["distributions"] = distributions807 if geo:808 out["geo"] = geo809 if heatmap:810 out["heatmap"] = heatmap811 if hourly:812 out["hourly"] = hourly813 return out814