spb/lou-ka Public
Lou·Ka — tous les logements à louer du Québec, un seul endroit.
HTML 99.7%
1# -----------------------------------------------------------------------------2# Lou-Ka — Agrégateur de logements à louer (province de Québec)3# Auteur : Simon-Pierre Boucher — contact@spboucher.ai4# marketstats.py : agrégats du marché partagés par /api/stats/detailed (page5# Statistiques) et par le rapport PDF (pdfgen.rapport_pdf) — une seule6# source de vérité pour tous les chiffres.7# -----------------------------------------------------------------------------8from __future__ import annotations910import json11import time1213from . import db1415# Régions administratives simplifiées (villes réellement présentes en base)16REGIONS: list[tuple[str, set[str]]] = [17 ("Québec métro", {"Québec", "Lévis", "Saint-Augustin-de-Desmaures",18 "L'Ancienne-Lorette", "Pont-Rouge", "Shannon",19 "Sainte-Brigitte-de-Laval", "Saint-Raphaël", "La Malbaie"}),20 ("Outaouais", {"Gatineau", "Chelsea", "Thurso", "Perkins", "Maniwaki",21 "Val-des-Monts"}),22 ("Estrie / Montérégie-Est", {"Sherbrooke", "Magog", "Orford", "East Angus",23 "Waterville", "Granby", "Waterloo", "Bromont",24 "Cowansville", "Richmond"}),25 ("Mauricie / Centre-du-Québec", {"Trois-Rivières", "Bécancour", "Shawinigan",26 "Drummondville", "Victoriaville", "Nicolet",27 "Notre-Dame-du-Bon-Conseil", "Wickham",28 "Saint-Léonard-d'Aston", "Louiseville",29 "Saint-Narcisse", "Saint-Nicéphore"}),30 ("Lanaudière / Laurentides", {"Joliette", "Saint-Jérôme", "Berthierville",31 "Saint-Ambroise-de-Kildare", "Charlemagne",32 "Saint-Gabriel-de-Brandon", "Lachute",33 "Brownsburg-Chatham", "Saint-Charles-Borromée",34 "Mirabel", "Sainte-Agathe-des-Monts",35 "Sainte-Thérèse", "Blainville",36 "Notre-Dame-des-Prairies"}),37 ("Bas-Saint-Laurent / Gaspésie", {"Rimouski", "Rivière-du-Loup", "Matane",38 "Saint-Ulric", "Amqui", "Le Bic",39 "New Richmond", "Carleton-sur-Mer", "Gaspé",40 "Pointe-au-Père"}),41 ("Saguenay–Lac-Saint-Jean", {"Saguenay", "Alma", "Chicoutimi", "Jonquière",42 "Chambord", "La Baie", "Laterrière"}),43 ("Abitibi-Témiscamingue", {"Rouyn-Noranda", "Val-d'Or", "Amos", "Malartic"}),44 ("Côte-Nord", {"Sept-Îles", "Port-Cartier", "Baie-Comeau", "Forestville"}),45 ("Chaudière-Appalaches", {"Saint-Georges", "Sainte-Marie", "Thetford Mines",46 "Montmagny", "Vallée-Jonction", "Scott",47 "Saint-Isidore", "La Guadeloupe",48 "Saint-Joseph-de-Beauce"}),49]505152def region_for(city: str) -> str:53 for nom, villes in REGIONS:54 if city in villes:55 return nom56 return "Grand Montréal & environs"575859def _median(v: list) -> float | None:60 n = len(v)61 if n == 0:62 return None63 return v[n // 2] if n % 2 else (v[n // 2 - 1] + v[n // 2]) / 2646566def compute() -> dict:67 """Tous les agrégats du marché sur les annonces actives."""68 con = db.connect()69 rows = con.execute(70 """SELECT uid, city, source, price, unit_type, area_sqft, furnished,71 pets, availability_date, lat, details72 FROM listings WHERE active=1""").fetchall()73 now = time.time()7475 total = len(rows)76 prix = sorted(r["price"] for r in rows77 if r["price"] and 300 <= r["price"] <= 10000)7879 # -- groupes simples ------------------------------------------------------80 def grouper(cle_fn):81 g: dict[str, dict] = {}82 for r in rows:83 k = cle_fn(r)84 if not k:85 continue86 d = g.setdefault(k, {"count": 0, "prix": [], "sources": set()})87 d["count"] += 188 d["sources"].add(r["source"])89 if r["price"] and 300 <= r["price"] <= 10000:90 d["prix"].append(r["price"])91 out = []92 for k, d in sorted(g.items(), key=lambda kv: -kv[1]["count"]):93 p = d["prix"]94 out.append({"key": k, "count": d["count"],95 "sources": len(d["sources"]),96 "avg_price": round(sum(p) / len(p)) if p else None,97 "min_price": min(p) if p else None})98 return out99100 by_type = grouper(lambda r: r["unit_type"])101 by_city = grouper(lambda r: r["city"])102 by_source = grouper(lambda r: r["source"])103 by_region = grouper(lambda r: region_for(r["city"] or ""))104105 # -- histogramme des loyers ------------------------------------------------106 lo, hi, step = 400, 3200, 200107 hist = [{"lo": a, "hi": a + step, "count": 0} for a in range(lo, hi, step)]108 under = over = 0109 for p in prix:110 if p < lo:111 under += 1112 elif p >= hi:113 over += 1114 else:115 hist[int((p - lo) // step)]["count"] += 1116 if under:117 hist.insert(0, {"lo": 0, "hi": lo, "count": under})118 if over:119 hist.append({"lo": hi, "hi": None, "count": over})120121 # -- offre : inclusions, animaux, meublé, dispo, superficie ----------------122 def pct(n, d):123 return round(100 * n / d, 1) if d else None124125 inc_counts = {"heating": 0, "electricity": 0, "hot_water": 0, "internet": 0}126 ac = parking = balcon = 0127 with_details = 0128 for r in rows:129 try:130 det = json.loads(r["details"] or "{}")131 except ValueError:132 det = {}133 if det:134 with_details += 1135 inc = det.get("inclusions") or {}136 for k in inc_counts:137 if inc.get(k):138 inc_counts[k] += 1139 if det.get("ac"):140 ac += 1141 if (det.get("parking") or {}).get("available"):142 parking += 1143 if det.get("balcony"):144 balcon += 1145146 pets_vals = [r["pets"] for r in rows if r["pets"]]147 furn = sum(1 for r in rows if r["furnished"])148 dispo_now = sum(1 for r in rows if r["availability_date"] == "now")149 dispo_date = sum(1 for r in rows150 if r["availability_date"] and r["availability_date"] != "now")151 aires = [r["area_sqft"] for r in rows if r["area_sqft"]]152153 # prix au pi² par taille (annonces ayant les deux)154 pi2: dict[str, list] = {}155 for r in rows:156 if (r["price"] and r["area_sqft"] and r["unit_type"]157 and 300 <= r["price"] <= 10000 and r["area_sqft"] >= 200):158 pi2.setdefault(r["unit_type"], []).append(r["price"] / r["area_sqft"])159 prix_pi2 = [{"key": k, "count": len(v),160 "val": round(sum(v) / len(v), 2)}161 for k, v in sorted(pi2.items(), key=lambda kv: -len(kv[1]))162 if len(v) >= 8][:8]163164 offre = {165 "furnished_pct": pct(furn, total),166 "pets_oui_pct": pct(sum(1 for p in pets_vals if p in ("oui", "conditions")),167 len(pets_vals)),168 "pets_connu": len(pets_vals),169 "chauffage_pct": pct(inc_counts["heating"], total),170 "electricite_pct": pct(inc_counts["electricity"], total),171 "eau_chaude_pct": pct(inc_counts["hot_water"], total),172 "internet_pct": pct(inc_counts["internet"], total),173 "clim_pct": pct(ac, total),174 "stationnement_pct": pct(parking, total),175 "balcon_pct": pct(balcon, total),176 "dispo_now": dispo_now,177 "dispo_date": dispo_date,178 "dispo_inconnue": total - dispo_now - dispo_date,179 "superficie_moyenne": round(sum(aires) / len(aires)) if aires else None,180 "superficie_connue": len(aires),181 "prix_pi2": prix_pi2,182 }183184 # -- baisses de prix récentes (30 jours) -----------------------------------185 baisses = []186 for r in con.execute(187 """SELECT p1.uid, l.title, l.city, l.price, p1.price nouveau, p1.ts188 FROM price_log p1189 JOIN listings l ON l.uid = p1.uid AND l.active=1190 WHERE p1.ts > ? AND p1.price IS NOT NULL191 ORDER BY p1.ts DESC LIMIT 400""", (now - 30 * 86400,)).fetchall():192 prev = con.execute(193 "SELECT price FROM price_log WHERE uid=? AND ts<? AND price IS NOT NULL"194 " ORDER BY ts DESC LIMIT 1", (r["uid"], r["ts"])).fetchone()195 if prev and prev["price"] and r["nouveau"] and r["nouveau"] < prev["price"]:196 baisses.append({"uid": r["uid"], "title": r["title"],197 "city": r["city"], "avant": prev["price"],198 "apres": r["nouveau"],199 "pct": round(100 * (r["nouveau"] - prev["price"])200 / prev["price"], 1)})201 baisses.sort(key=lambda b: b["pct"])202 baisses = baisses[:12]203204 # -- santé des sources ------------------------------------------------------205 sync24 = con.execute(206 "SELECT COUNT(DISTINCT source) c FROM sync_log WHERE ok=1 AND ts>?",207 (now - 86400,)).fetchone()["c"]208 alertes = [dict(r) for r in con.execute(209 """SELECT source, message, ts FROM sync_log210 WHERE ts > ? AND message NOT IN ('ok') ORDER BY ts DESC LIMIT 10""",211 (now - 86400,)).fetchall()]212 gps_pct = con.execute(213 "SELECT ROUND(100.0*SUM(lat IS NOT NULL)/COUNT(*),1) p"214 " FROM listings WHERE active=1").fetchone()["p"]215216 out = {217 "totals": {218 "total": total,219 "with_price": len(prix),220 "avg": round(sum(prix) / len(prix)) if prix else None,221 "median": round(_median(prix)) if prix else None,222 "min": prix[0] if prix else None,223 "max": prix[-1] if prix else None,224 "sources": len(by_source),225 "cities": len(by_city),226 "regions": len([r for r in by_region if r["count"] > 0]),227 "gps_pct": gps_pct,228 "superficie_moyenne": offre["superficie_moyenne"],229 "dispo_now": dispo_now,230 },231 "histogram": hist,232 "by_type": by_type,233 "by_city": by_city,234 "by_source": by_source,235 "by_region": by_region,236 "offre": offre,237 "baisses": baisses,238 "sante": {"sources_sync_24h": sync24, "alertes_24h": alertes},239 }240 con.close()241 return out242