#!/usr/bin/env python3 # -*- coding: utf-8 -*- """xlsx -> src/data/zonesGenerated.json. Inclut le MAILLAGE précalculé : - ville : 16 villes les plus proches (haversine), scope 'alentours' si hors dept - departement: villes du dept d'abord, complétées par les plus proches jusqu'à 16 - region : liste des départements de la région - CH/BE : villes du pays disponibles""" import openpyxl, json, re, math XLSX="public/documents/covalba-zones-seo-DEMO.xlsx" OUT="src/data/zonesGenerated.json" GEO=json.load(open("scripts/geocode-cache.json")) wb=openpyxl.load_workbook(XLSX,data_only=True) zs=wb["Zones"]; hdr=[c.value for c in zs[1]] vt=wb["Villes departement"] mesh={} for r in vt.iter_rows(min_row=2,values_only=True): if not r[0]: continue mesh.setdefault(r[0],[]).append({"nom":r[1],"slug":r[2],"typeZone":r[3] or ""}) def strip_html(s): return re.sub(r"<[^>]+>","",s or "") def num(v,d=0): try: return float(v) if v not in (None,"") else d except (TypeError,ValueError): return d raw=[dict(zip(hdr,r)) for r in zs.iter_rows(min_row=2,values_only=True)] raw=[d for d in raw if (d.get("slugVille") or d.get("slugDepartement") or d.get("slugRegion")) and d.get("pageType")] # --- index géo des villes FR (avec coords) --- fr_villes=[] for d in raw: if d["pageType"]=="ville" and (d.get("pays") or "FR")=="FR": sv=d.get("slugVille") if sv in GEO: fr_villes.append({"nom":d["ville"],"slug":sv,"typeZone":d.get("typeZone") or "", "slugDept":d.get("slugDepartement"),"lat":GEO[sv]["lat"],"lon":GEO[sv]["lon"]}) def hav(a,b,c,e): R=6371; p=math.radians return 2*R*math.asin(math.sqrt(math.sin(p(c-a)/2)**2+math.cos(p(a))*math.cos(p(c))*math.sin(p(e-b)/2)**2)) # centroïdes dépts dept_centroid={} from collections import defaultdict byd=defaultdict(list) for v in fr_villes: byd[v["slugDept"]].append(v) for sd,vs in byd.items(): dept_centroid[sd]=(sum(v["lat"] for v in vs)/len(vs), sum(v["lon"] for v in vs)/len(vs)) # région -> départements (ordre alpha) reg_depts=defaultdict(dict) for d in raw: if d["pageType"]=="departement" and (d.get("pays") or "FR")=="FR": reg_depts[d.get("slugRegion")][d["slugDepartement"]]={"nom":d["departementNom"],"slug":d["slugDepartement"],"typeZone":d.get("typeZone") or ""} # CH/BE par pays pays_villes=defaultdict(list) for d in raw: if (d.get("pays") or "FR")!="FR" and d["pageType"]=="ville": pays_villes[d["pays"]].append({"nom":d["ville"],"slug":d["slugVille"],"typeZone":d.get("typeZone") or ""}) def nearest(lat,lon,exclude_slug=None,n=16): cand=[v for v in fr_villes if v["slug"]!=exclude_slug] cand.sort(key=lambda v:hav(lat,lon,v["lat"],v["lon"])) return cand[:n] def maillage_for(d): pt=d["pageType"]; pays=d.get("pays") or "FR" if pays!="FR": if pt=="region": lst=pays_villes.get("CH" if "suisse" in (d.get("slugRegion") or "") else "BE",[]) else: others=[v for v in pays_villes.get(pays,[]) if v["slug"]!=d.get("slugVille")] lst=others return [{"nom":x["nom"],"slug":x["slug"],"typeZone":x["typeZone"]} for x in lst], "pays" if pt=="region": return list(reg_depts.get(d.get("slugRegion"),{}).values()), "region" if pt=="ville": sv=d.get("slugVille") if sv not in GEO: return mesh.get(d.get("slugDepartement"),[])[:16], "departement" near=nearest(GEO[sv]["lat"],GEO[sv]["lon"],exclude_slug=sv,n=16) scope="alentours" if any(v["slugDept"]!=d.get("slugDepartement") for v in near) else "departement" return [{"nom":v["nom"],"slug":v["slug"],"typeZone":v["typeZone"]} for v in near], scope if pt=="departement": sd=d.get("slugDepartement") own=byd.get(sd,[]) own_sorted=sorted(own,key=lambda v:hav(*dept_centroid[sd],v["lat"],v["lon"])) if sd in dept_centroid else own out=[{"nom":v["nom"],"slug":v["slug"],"typeZone":v["typeZone"]} for v in own_sorted] scope="departement" if len(out)<16 and sd in dept_centroid: have={v["slug"] for v in out} extra=[v for v in nearest(*dept_centroid[sd],n=60) if v["slug"] not in have and v["slugDept"]!=sd] for v in extra[:16-len(out)]: out.append({"nom":v["nom"],"slug":v["slug"],"typeZone":v["typeZone"]}) scope="alentours" if len(out)>len(own) else "departement" return out, scope return [], "departement" zones={} for d in raw: slug=d.get("slugVille") or d.get("slugDepartement") or d.get("slugRegion") pageType=d["pageType"]; deptNom=d.get("departementNom") or ""; deptNum=str(d.get("departementNum") or "") secteurs=[{"label":d[f"secteur{i}.label"],"stat":d.get(f"secteur{i}.stat") or "","description":d.get(f"secteur{i}.description") or ""} for i in (1,2) if d.get(f"secteur{i}.label")] sections=[{"h3":d[f"seo.section{i}.h3"],"content":d.get(f"seo.section{i}.content") or ""} for i in range(1,9) if d.get(f"seo.section{i}.h3")] maillage,scope=maillage_for(d) zones[slug]={ "ville":d.get("ville") or None, "departement":f"{deptNom} ({deptNum})" if deptNum else deptNom, "departementNom":deptNom,"departementNum":deptNum,"region":d.get("region") or "", "villesDepartement":mesh.get(d.get("slugDepartement"),[]), "villesProches":[v.strip() for v in (d.get("villesProches") or "").split(",") if v.strip()], "maillage":maillage,"maillageScope":scope, "typeZone":d.get("typeZone") or "","climatDescription":strip_html(d.get("intro.accroche")), "zoneClimatique":d.get("zoneClimatique") or "H1", "slugVille":d.get("slugVille") or None,"slugDepartement":d.get("slugDepartement") or "","slugRegion":d.get("slugRegion") or None, "isDepartementPage":pageType=="departement","pageType":pageType,"pays":d.get("pays") or "FR", "zoneClimatiqueCEE":d.get("zoneClimatique") or "H1","descriptionClimatique":strip_html(d.get("panel2.paragraph")), "joursSup30C":num(d.get("joursSup30C")),"tempMoyEstivale":num(d.get("tempMoyEstivale")),"picHistorique":num(d.get("picHistorique")), "joursSup30C10ans":num(d.get("joursSup30C10ans")),"tempMoyEstivale10ans":num(d.get("tempMoyEstivale10ans")), "joursSup30C20ans":num(d.get("joursSup30C20ans")),"tempMoyEstivale20ans":num(d.get("tempMoyEstivale20ans")), "reductionSousToiture":num(d.get("reductionSousToiture"),10), "introSection":{"hook":d.get("intro.hook") or "","accroche":d.get("intro.accroche") or "", "kpis":[{"value":d.get("intro.kpi1Value") or "","label":d.get("intro.kpi1Label") or ""}, {"value":d.get("intro.kpi2Value") or "","label":d.get("intro.kpi2Label") or ""}], "contexte":"","coutCache":d.get("intro.coutCache") or "","trustLine":"","urgenceLine":""}, "content":{"activitesIntro":d.get("panel1.paragraph") or "","secteurs":secteurs, "problemeTitlePart1":d.get("panel2.titlePart1") or "","problemeTitlePart2":d.get("panel2.titlePart2") or "", "problemeParagraph":d.get("panel2.paragraph") or "","solutionTitlePart1":d.get("panel4.titlePart1") or "", "solutionTitlePart2":d.get("panel4.titlePart2") or "","solutionParagraph":d.get("panel4.paragraph") or "", "preuveBigStat1":d.get("panel5.bigStat1") or "","preuveBigStat2":d.get("panel5.bigStat2") or "","preuveParagraph":d.get("panel5.paragraph") or ""}, "seoContent":{"h2":d.get("seo.h2") or "","intro":d.get("seo.intro") or "","sections":sections} if d.get("seo.h2") else None, } json.dump(zones,open(OUT,"w",encoding="utf-8"),ensure_ascii=False,indent=1) import statistics mv=[len(z["maillage"]) for z in zones.values() if z["pageType"]=="ville" and z["pays"]=="FR"] print(f"{len(zones)} zones | maillage villes FR: min={min(mv)} médiane={statistics.median(mv)} max={max(mv)}") print("ex Aurillac:", [v["nom"] for v in zones["cool-roof-aurillac"]["maillage"]][:16] if "cool-roof-aurillac" in zones else "?") print("scope ex:", zones["cool-roof-aurillac"]["maillageScope"] if "cool-roof-aurillac" in zones else "?")