"""Generate a synthetic Project Planning workbook + matching invoice/memory fixtures, shaped exactly like the real `projects` tab per docs/CONTRACT.md ยง5. Running it writes, next to this file: - planning_sample.xlsx the workbook (projects tab, rows 1-138 + an EDI tail) - invoices_sample.json Odoo-style Software invoices for 2026-10 - name_map_sample.json Planning-name -> Odoo-customer memory seed The data is designed so a 2026-10 reconciliation yields a mix of green / amber / red / billed_unplanned outcomes for end-to-end testing. """ from __future__ import annotations import json from datetime import date from pathlib import Path from openpyxl import Workbook HERE = Path(__file__).resolve().parent # Month columns run Jan-2024 .. Mar-2027. They start at worksheet column G (7). MONTH_START_COL = 7 MONTHS: list[date] = [] for year in (2024, 2025, 2026, 2027): for month in range(1, 13): if year == 2027 and month > 3: break MONTHS.append(date(year, month, 1)) TARGET_MONTH = date(2026, 10, 1) TARGET_COL = MONTH_START_COL + MONTHS.index(TARGET_MONTH) # column for Oct-2026 def col_for(month: date) -> int: return MONTH_START_COL + MONTHS.index(month) # Project rows: (name, value, oct_amount). oct_amount None/0 => skipped as a line. # A handful carry quirks straight from the PRD examples. PROJECTS = [ ("Baskit (Duke)", 480000, 40000), ("AuditWhiz (Big Pond)", 360000, 30000), ("E Snell - MS Teams - Reseller", 120000, 10000), ("Rio Africa Portal", 600000, 50000), ("Nimbus Logistics", 240000, 20000), ("Harborview CRM", 180000, 15000), ("Kestrel Analytics", 300000, 25000), ("Meridian Fleet", 144000, 12000), ("Orchard Retail POS", 216000, 18000), ("Summit HR Portal\n(phase 2)", 264000, 22000), # embedded newline quirk ("Delphi Billing", 108000, 9000), ("Vanguard Claims", 420000, 35000), ("Lumen Dashboards", 96000, 8000), ("Trailhead Booking", 132000, 11000), ("Ironclad Docs", 288000, 24000), ("Pinnacle Payroll", 156000, 13000), ("Cobalt Inventory", 348000, 29000), ("Everest LMS", 204000, 17000), ("Marlin Support Desk", 84000, 7000), ("Zephyr Scheduling", 168000, 14000), ("Granite Invoicing", 312000, 26000), ("Falcon Telemetry", 228000, 19000), ("Aurora Field App", 372000, 31000), ("Beacon Notify", 72000, 6000), ("Cedar Onboarding", 192000, 16000), ("Drake Compliance", 276000, 23000), ("Echo Feedback", 60000, 5000), ("Fjord Mapping", 336000, 28000), ("Quarterly Co (bills Q)", 400000, 33000), # quarterly rule exercises this ("Hollow Oak Intranet", 120000, 10000), ("Indigo Surveys", 240000, 20000), ("Juniper Rostering", 180000, 15000), ("Kraken Integrations", 300000, 25000), ("Lantern Alerts", 144000, 12000), ("Mosaic Reporting", 216000, 18000), ("Nectar Loyalty", 264000, 22000), ("Osprey Timesheets", 108000, 9000), # non-billed this month (zero in Oct) -> must be skipped as a line: ("Dormant Project A", 90000, 0), ("Dormant Project B", 90000, 0), ] # Odoo customer directory (id -> name) used to build the name map + invoices. CUSTOMERS = { 101: "Baskit Pty Ltd", 102: "Big Pond Holdings", 103: "E Snell Consulting", 104: "Rio Africa (Pty) Ltd", 105: "Nimbus Logistics SA", 106: "Harborview Software", 107: "Kestrel Analytics Ltd", 108: "Meridian Fleet Services", 109: "Orchard Retail Group", 110: "Summit HR", 111: "Delphi Systems", 112: "Vanguard Insurance", 199: "Walk-in Software Client", # produces a billed_unplanned } NAME_MAP = { "Baskit (Duke)": 101, "AuditWhiz (Big Pond)": 102, "E Snell - MS Teams - Reseller": 103, "Rio Africa Portal": 104, "Nimbus Logistics": 105, "Harborview CRM": 106, "Kestrel Analytics": 107, "Meridian Fleet": 108, "Orchard Retail POS": 109, "Summit HR Portal\n(phase 2)": 110, "Delphi Billing": 111, "Vanguard Claims": 112, # Intentionally NO entry for "Quarterly Co (bills Q)" and most others -> # they resolve to unresolved/red/amber, exercising every tier. } def build_invoices() -> list[dict]: """Software invoices for 2026-10, hand-tuned to span every outcome.""" inv = [] nid = 9000 def add(cust_id, amount_untaxed, day=15, number=None, sent=True): nonlocal nid nid += 1 inv.append({ "odoo_id": nid, "number": number or f"INV/2026/{nid}", "customer": CUSTOMERS[cust_id], "odoo_customer_id": cust_id, "invoice_date": f"2026-10-{day:02d}", "amount_untaxed": round(amount_untaxed, 2), "amount_total": round(amount_untaxed * 1.15, 2), "sent": sent, }) # GREEN: exact amount, resolved client add(101, 40000) # Baskit -> planned 40000 add(104, 50000) # Rio Africa -> 50000 add(105, 20000) # Nimbus -> 20000 add(107, 25000) # Kestrel -> 25000 add(109, 18000) # Orchard -> 18000 add(110, 22000) # Summit HR -> 22000 # GREEN within tolerance (small rounding diff, inside 0.5%) add(106, 15000.40) # Harborview planned 15000 -> delta 0.40 < max(1, 75) # AMBER: amount differs beyond tolerance (partial) add(102, 18000) # AuditWhiz planned 30000 -> partial, amber add(108, 12000 + 500) # Meridian planned 12000 -> over by 500, amber # AMBER: bundled (one invoice covering two planned lines) - E Snell + extra add(103, 10000, number="INV/2026/BUNDLE") # billed_unplanned: a Software invoice with no planned line add(199, 7777) # Note: Vanguard Claims (112) planned 35000 has NO invoice -> RED # Delphi Billing (111) planned 9000 has NO invoice -> RED # Quarterly Co unresolved + no invoice -> RED/amber depending on rule return inv def main() -> None: wb = Workbook() ws = wb.active ws.title = "projects" # Header row (row 1): labels + month-date headers. ws.cell(row=1, column=2, value="Client / Project") ws.cell(row=1, column=6, value="Value") for m in MONTHS: ws.cell(row=1, column=col_for(m), value=m) # real date -> header match # Data rows start at 2. Weave in label/subtotal/blank rows. row = 2 inserted_labels_at = set() for idx, (name, value, oct_amt) in enumerate(PROJECTS): ws.cell(row=row, column=2, value=name) ws.cell(row=row, column=6, value=value) # fill a few neighbouring months so the sheet looks real for m in MONTHS: if m == TARGET_MONTH: if oct_amt: ws.cell(row=row, column=col_for(m), value=oct_amt) elif m.year == 2026: ws.cell(row=row, column=col_for(m), value=value // 12) row += 1 # drop a subtotal + blank line every ~12 projects if idx in (11, 23) and idx not in inserted_labels_at: ws.cell(row=row, column=2, value="SLA Total for each month") ws.cell(row=row, column=col_for(TARGET_MONTH), value=123456) row += 1 row += 1 # blank spacer row # A couple of canonical non-project label rows. ws.cell(row=row, column=2, value="Target") ws.cell(row=row, column=col_for(TARGET_MONTH), value=900000) row += 1 ws.cell(row=row, column=2, value="SLA + Ad Hoc + Project + EDI + Finished Projects") ws.cell(row=row, column=col_for(TARGET_MONTH), value=999999) row += 1 # Grand total pinned at row 138. ws.cell(row=138, column=2, value="Grand Total") ws.cell(row=138, column=col_for(TARGET_MONTH), value=1500000) # EDI tail at 139+ (out of scope) - must be ignored by the parser. ws.cell(row=140, column=2, value="EDI Client Alpha") ws.cell(row=140, column=col_for(TARGET_MONTH), value=88888) out_xlsx = HERE / "planning_sample.xlsx" wb.save(out_xlsx) (HERE / "invoices_sample.json").write_text( json.dumps(build_invoices(), indent=2)) (HERE / "name_map_sample.json").write_text(json.dumps( [{"planning_name": k, "odoo_customer_id": v, "odoo_customer_name": CUSTOMERS[v], "source": "manual"} for k, v in NAME_MAP.items()], indent=2)) print(f"wrote {out_xlsx.name} (target col {TARGET_COL} for Oct-2026)") print(f"wrote invoices_sample.json, name_map_sample.json") if __name__ == "__main__": main()