#!/usr/bin/env python3 """Reconstitue les chiffrages (Estimates) depuis l'export projets (une feuille par projet). Cascade par feuille : 1. bloc TJM → profils (idx, personne, TJM, jours-H éventuels) ; 2. phases, par ordre de préférence : a. RÉSUMÉ ÉTAPES : en-tête 'ÉTAPES' (ou B='DESCRIPTION'), lignes code=PHASE n / lettre / entier, jours par profil en D/E/F/G → method=summary b. RÉGIE jours vendus : 'JOURS VENDUS' / 'Nombre de jours vendus' → une phase enveloppe → method=regie_days c. REPLI bloc TJM : une phase 'Prestation' = jours-H par profil → method=tjm_fallback 3. total = Σ jours×TJM ; comparé au montant du devis (passé via --devis json) pour contrôle. N'ÉCRIT RIEN EN BASE. Produit un JSON {estimates:[{number, method, profiles, steps}]} + un rapport. """ import json, sys, re sys.path.insert(0, "scripts") import dump_xlsx as dx import zipfile from xml.etree import ElementTree as ET NS = dx.NS STOP = re.compile(r"^(PROJET|DATE DE|MONTANT|TOTAL|CHEF DE PROJET|EQUIPE PROJET|MISSION|SOUS[- ]TRAIT)", re.I) CODE = re.compile(r"^(PHASE\s*\d+|TRANCHE\s*\d+|[A-Z]|\d{1,2})(\.0)?$", re.I) def sheet_rows(z, target, shared): sx = ET.fromstring(z.read(target)) sd = sx.find(f"{NS}sheetData") rows = [] for row in sd.findall(f"{NS}row"): cells = {} for c in row.findall(f"{NS}c"): ref = c.get("r", ""); t = c.get("t") v = c.find(f"{NS}v"); inline = c.find(f"{NS}is") if t == "s" and v is not None and v.text is not None: val = shared[int(v.text)] elif t == "inlineStr" and inline is not None: val = "".join(x.text or "" for x in inline.iter(f"{NS}t")) elif v is not None: val = v.text else: val = None if val is not None and str(val).strip() != "": cells[dx.col_letter(ref)] = str(val).strip() rows.append(cells) return rows def num(v): try: return round(float(str(v).strip().replace(",", ".")), 4) except (ValueError, TypeError, AttributeError): return 0.0 def parse_profiles(rows): """Bloc TJM : lignes A='TJM n' (ou 'TJMn'), B=taux, C='Personne - Rôle', H=jours éventuels.""" profiles = [] for r in rows: a = r.get("A", "") pm = re.match(r"^TJM\s*(\d+)$", a) if not pm: continue rate = num(r.get("B")) c = r.get("C", "") if rate <= 0 or not c or c.upper().startswith("NOM"): continue # ligne profil vide / placeholder 'NOM - RÔLE' person = re.sub(r"\s*-\s*.*$", "", c).strip() profiles.append({ "idx": int(pm.group(1)), "label": person or a, "rate": rate, "h_days": num(r.get("H")), }) return profiles def parse_summary(rows): """Résumé ÉTAPES : jours par profil en colonnes D/E/F/G. Renvoie [] si introuvable.""" hi = None for i, r in enumerate(rows): a = r.get("A", "").upper() if a.startswith("ÉTAPES") or a.startswith("ETAPES") or r.get("B", "").upper() == "DESCRIPTION": # en-tête valable seulement si D/E/F/G ressemblent à des colonnes profil if any(r.get(col) for col in ("D", "E", "F", "G")): hi = i break if hi is None: return [] steps = [] for r in rows[hi + 1:]: a = r.get("A", "") b = r.get("B", "") if STOP.match(a): break is_code = bool(CODE.match(a)) days = [num(r.get(col)) for col in ("D", "E", "F", "G")] if not is_code and not b and not any(days): break # ligne de total / vide → fin du résumé if b and any(d > 0 for d in days): steps.append({"name": b, "days": days}) return steps def chef_de_projet(rows): for r in rows: if r.get("A", "").upper().startswith("CHEF DE PROJET"): return r.get("C", "").strip() return "" def parse_regie_days(rows): """Régie : nombre de jours vendus + TJM → (jours, taux, personne) pour une phase enveloppe.""" for i, r in enumerate(rows): a = r.get("A", "").upper() if a == "JOURS VENDUS": # INTM : ligne suivante = [jours, tjm, total] nxt = rows[i + 1] if i + 1 < len(rows) else {} d, rate = num(nxt.get("A")), num(nxt.get("B")) if d > 0: return d, rate, chef_de_projet(rows) if a.startswith("NOMBRE DE JOURS"): # D26004 : C=jours ; ligne 'TJM' C=taux d = num(r.get("C")) rate = 0.0 for rr in rows: if rr.get("A", "").upper() == "TJM": rate = num(rr.get("C")) if d > 0: return d, rate, chef_de_projet(rows) return None def build(number, profiles, rows): # a. résumé ÉTAPES steps = parse_summary(rows) if profiles and steps: return {"number": number, "method": "summary", "profiles": profiles, "steps": steps} # b. régie jours vendus (synthétise un profil depuis le CP si le bloc TJM est absent) rd = parse_regie_days(rows) if rd: d, rate, person = rd if profiles: p = dict(profiles[0]); p["rate"] = rate or p["rate"] else: p = {"idx": 1, "label": person or "Chef de projet", "rate": rate, "h_days": d} if p["rate"] > 0: return {"number": number, "method": "regie_days", "profiles": [p], "steps": [{"name": "Régie (enveloppe)", "days": [d, 0, 0, 0]}]} # c. repli bloc TJM (jours-H par profil) if profiles and any(p["h_days"] > 0 for p in profiles): days = [p["h_days"] for p in profiles[:4]] + [0, 0, 0, 0] return {"number": number, "method": "tjm_fallback", "profiles": profiles, "steps": [{"name": "Prestation", "days": days[:4]}]} return None def total_ht(est): return sum(s["days"][i] * (est["profiles"][i]["rate"] if i < len(est["profiles"]) else 0) for s in est["steps"] for i in range(4)) def main(): z = zipfile.ZipFile(sys.argv[1]) shared = dx.load_shared_strings(z) devis = {} if len(sys.argv) > 3 and sys.argv[3]: devis = json.load(open(sys.argv[3])) out, report, seen = [], [], set() for name, target in dx.sheet_targets(z): m = re.search(r"(OF|D)\s*\d{5,6}", name.upper().replace(" ", "")) if not m: report.append((name, "—", "", 0, 0, "", "pas de n°")); continue number = m.group(0) of = "OF" + number.lstrip("ODF")[-5:] if number.startswith("D") else number dnum = "D" + number[-5:] if number.startswith("OF") else number rows = sheet_rows(z, target, shared) profiles = parse_profiles(rows) est = build(of, profiles, rows) tot = round(total_ht(est)) if est else 0 amt = devis.get(dnum) delta = (f"{tot - amt:+}" if amt is not None else "?") note = "" if dnum in seen: note = "DOUBLON feuille"; est = None seen.add(dnum) if est: out.append(est) report.append((name[:33], of, (est["method"] if est else "—"), len(profiles), (len(est["steps"]) if est else 0), tot, amt, delta, note)) json.dump({"estimates": out}, open(sys.argv[2], "w"), ensure_ascii=False, indent=2) print(f"{len(out)} chiffrages exploitables → {sys.argv[2]}\n") print(f"{'FEUILLE':34} {'OFFRE':8} {'MÉTHODE':13} {'PRO':3} {'ÉT':3} {'TOTAL':>7} {'DEVIS':>7} {'Δ':>7} NOTE") for row in report: nm, of, meth, p, s, tot, amt, delta, note = row print(f"{nm:34} {of:8} {meth:13} {p:<3} {s:<3} {tot:>7} {str(amt or '—'):>7} {str(delta):>7} {note}") if __name__ == "__main__": main()