#!/usr/bin/env python3 """Reconcile Northline Workshops October 2026 results per inputs/policy.md. Stdlib only.""" import csv, re, os from datetime import datetime, date from decimal import Decimal, ROUND_HALF_UP BASE = os.path.dirname(os.path.dirname(os.path.abspath(__file__))) INP, OUT = os.path.join(BASE, "inputs"), os.path.join(BASE, "output") P_START, P_END = date(2026, 10, 1), date(2026, 10, 31) PRODUCTS = {"workshop": "Workshop", "course": "Course"} def read(name): with open(os.path.join(INP, name), newline="", encoding="utf-8") as f: return list(csv.DictReader(f)) def parse_date(s): s = (s or "").strip() if not s: return None for fmt in ("%Y-%m-%d", "%d/%m/%Y", "%b %d %Y", "%B %d %Y", "%d %b %Y", "%d %B %Y"): try: return datetime.strptime(s, fmt).date() except ValueError: pass return None def parse_money(s): s = (s or "").strip() if not s: return None t = re.sub(r"(?i)cad|\$|,|\s", "", s) try: return Decimal(t).quantize(Decimal("0.01"), ROUND_HALF_UP) except Exception: return None def m(d): return "" if d is None else f"{d:.2f}" def write(name, rows, fields): with open(os.path.join(OUT, name), "w", newline="", encoding="utf-8") as f: w = csv.DictWriter(f, fieldnames=fields) w.writeheader() w.writerows(rows) exceptions = [] def exc(file, row, key, reason, action, raw): flagged = action.startswith("Flagged") exceptions.append({"source_file": file, "row_id": row, "record_id": key, "category": "flagged_unresolved" if flagged else "excluded_by_policy", "needs_follow_up": "yes" if flagged else "no", "issue": reason, "treatment": action, "raw_value": raw}) # ---------------- Sales ---------------- sales_raw = read("sales.csv") seen_exact, groups = {}, {} sales_clean = [] for r in sales_raw: sig = tuple(r[k] for k in r if k != "row_id") d, g = parse_date(r["date"]), parse_money(r["gross_cad"]) prod = PRODUCTS.get(r["product"].strip().lower()) rec = {"row_id": r["row_id"], "order_id": r["order_id"].strip(), "date": d.isoformat() if d else r["date"], "product": prod or r["product"], "status": r["status"].strip().lower(), "gross_cad": m(g) if g is not None else r["gross_cad"], "updated_at": r["updated_at"], "included": "", "note": ""} rec["_d"], rec["_g"], rec["_p"], rec["_raw"] = d, g, prod, r["gross_cad"] if sig in seen_exact: rec["included"], rec["note"] = "no", f"exact duplicate of {seen_exact[sig]}" exc("sales.csv", r["row_id"], rec["order_id"], f"Exact duplicate of {seen_exact[sig]}", "Collapsed; not counted", r["gross_cad"]) else: seen_exact[sig] = r["row_id"] groups.setdefault(rec["order_id"], []).append(rec) sales_clean.append(rec) paid_orders = {} # order_id -> rec for oid, recs in groups.items(): recs.sort(key=lambda x: x["updated_at"]) latest = recs[-1] for old in recs[:-1]: old["included"], old["note"] = "no", f"superseded by {latest['row_id']} (later updated_at)" exc("sales.csv", old["row_id"], oid, f"Conflicting record for same order; {latest['row_id']} has later updated_at {latest['updated_at']}", "Superseded; not counted", old["_raw"]) r = latest reason = None if r["_d"] is None: reason, act = "Unparseable/missing date", "Excluded; value preserved" elif not (P_START <= r["_d"] <= P_END): reason, act = f"Order date {r['date']} outside October 2026", "Excluded from October" elif r["status"] != "paid": reason, act = f"Status '{r['status']}' is not revenue", "Excluded" elif r["_p"] is None: reason, act = "Unmapped product", "Flagged; not assigned" elif r["_g"] is None: reason, act = "Missing/unparseable gross", "Excluded; value preserved" if reason: r["included"], r["note"] = "no", reason exc("sales.csv", r["row_id"], oid, reason, act, r["_raw"]) else: r["included"], r["note"] = "yes", "paid October order" paid_orders[oid] = r # ---------------- Refunds ---------------- ref_raw = read("refunds.csv") seen_exact, ref_ids = {}, {} ref_clean = [] provisional = [] for r in ref_raw: sig = tuple(r[k] for k in r if k != "row_id") d, a = parse_date(r["settled_date"]), parse_money(r["amount_cad"]) order = paid_orders.get(r["order_id"].strip()) rec = {"row_id": r["row_id"], "refund_id": r["refund_id"].strip(), "order_id": r["order_id"].strip(), "product": order["_p"] if order else "", "settled_date": d.isoformat() if d else r["settled_date"], "status": r["status"].strip().lower(), "amount_cad": m(a) if a is not None else r["amount_cad"], "applied": "no", "note": ""} rec["_a"] = a if sig in seen_exact: rec["note"] = f"exact duplicate of {seen_exact[sig]}" exc("refunds.csv", r["row_id"], rec["refund_id"], f"Exact duplicate of {seen_exact[sig]}", "Collapsed; not counted", r["amount_cad"]) elif rec["refund_id"] in ref_ids: rec["note"] = f"conflicting duplicate refund ID ({ref_ids[rec['refund_id']]})" exc("refunds.csv", r["row_id"], rec["refund_id"], "Conflicting duplicate refund ID", "Excluded pending review", r["amount_cad"]) else: seen_exact[sig] = r["row_id"] ref_ids[rec["refund_id"]] = r["row_id"] reason = None if rec["status"] != "settled": reason, act = f"Status '{rec['status']}' — policy: do not subtract pending refunds", "Excluded" elif order is None: reason, act = f"Order {rec['order_id']} is not a valid paid October order", "Flagged; excluded from calculable total" elif d is None: reason, act = "Missing/unparseable settlement date", "Flagged; excluded" elif not (P_START <= d <= P_END): reason, act = f"Settled {rec['settled_date']}, outside October", "Excluded from October" elif a is None: reason = "Settled refund with missing amount (unknown, not zero)" act = "Flagged; excluded from calculable total; net revenue marked provisional" provisional.append(rec) elif a > order["_g"]: reason, act = "Refund exceeds paid gross", "Flagged; excluded" if reason: rec["note"] = reason exc("refunds.csv", r["row_id"], rec["refund_id"], reason, act, r["amount_cad"]) else: rec["applied"], rec["note"] = "yes", "settled in October against valid paid order" ref_clean.append(rec) # ---------------- Ads ---------------- ads_raw = read("ads.csv") seen_exact, ad_ids = {}, {} ads_clean = [] for r in ads_raw: sig = tuple(r[k].strip().lower() for k in r if k != "row_id") d, s = parse_date(r["date"]), parse_money(r["spend_cad"]) prod = PRODUCTS.get(r["product"].strip().lower()) rec = {"row_id": r["row_id"], "ad_id": r["ad_id"].strip(), "date": d.isoformat() if d else r["date"], "product": prod or r["product"], "channel": r["channel"].strip().title(), "spend_cad": m(s) if s is not None else r["spend_cad"], "included": "no", "note": ""} rec["_s"], rec["_p"] = s, prod reason = None if sig in seen_exact: reason, act = f"Exact duplicate of {seen_exact[sig]} (same ad ID)", "Collapsed; not counted" elif rec["ad_id"] in ad_ids: reason, act = "Conflicting duplicate ad ID", "Excluded pending review" else: seen_exact[sig] = r["row_id"]; ad_ids[rec["ad_id"]] = r["row_id"] if d is None: reason, act = "Unparseable/missing date", "Excluded" elif not (P_START <= d <= P_END): reason, act = f"Ad date {rec['date']} outside October", "Excluded from October" elif prod is None: reason, act = "Unmapped product", "Flagged; not assigned" elif s is None: reason, act = "Missing spend (unknown, not zero)", "Flagged" if reason: rec["note"] = reason exc("ads.csv", r["row_id"], rec["ad_id"], reason, act, r["spend_cad"]) else: rec["included"], rec["note"] = "yes", "October ad spend" ads_clean.append(rec) # ---------------- Totals ---------------- Z = Decimal("0.00") tot = {} for p in ("Workshop", "Course"): orders = [o for o in paid_orders.values() if o["_p"] == p] gross = sum((o["_g"] for o in orders), Z) refunds = sum((x["_a"] for x in ref_clean if x["applied"] == "yes" and x["product"] == p), Z) ads = sum((x["_s"] for x in ads_clean if x["included"] == "yes" and x["_p"] == p), Z) tot[p] = dict(n=len(orders), gross=gross, ref=refunds, ads=ads) tot["Combined"] = {k: sum(tot[p][k] for p in ("Workshop", "Course")) for k in ("n", "gross", "ref", "ads")} for t in tot.values(): t["net"] = t["gross"] - t["ref"] t["contrib"] = t["net"] - t["ads"] t["per"] = (t["net"] / t["n"]).quantize(Decimal("0.01"), ROUND_HALF_UP) if t["n"] else None # ---------------- Write CSVs ---------------- strip = lambda rows: [{k: v for k, v in r.items() if not k.startswith("_")} for r in rows] write("cleaned-sales.csv", strip(sales_clean), ["row_id", "order_id", "date", "product", "status", "gross_cad", "updated_at", "included", "note"]) write("cleaned-refunds.csv", strip(ref_clean), ["row_id", "refund_id", "order_id", "product", "settled_date", "status", "amount_cad", "applied", "note"]) write("cleaned-ads.csv", strip(ads_clean), ["row_id", "ad_id", "date", "product", "channel", "spend_cad", "included", "note"]) write("exceptions.csv", exceptions, ["source_file", "row_id", "record_id", "category", "needs_follow_up", "issue", "treatment", "raw_value"]) # ---------------- Checks ---------------- n_s_in = sum(r["included"] == "yes" for r in sales_clean) n_r_in = sum(r["applied"] == "yes" for r in ref_clean) n_a_in = sum(r["included"] == "yes" for r in ads_clean) ex_by = {f: sum(e["source_file"] == f for e in exceptions) for f in ("sales.csv", "refunds.csv", "ads.csv")} assert n_s_in + ex_by["sales.csv"] == len(sales_raw) assert n_r_in + ex_by["refunds.csv"] == len(ref_raw) assert n_a_in + ex_by["ads.csv"] == len(ads_raw) assert tot["Combined"]["contrib"] == tot["Combined"]["gross"] - tot["Combined"]["ref"] - tot["Combined"]["ads"] counts = dict(sales=(len(sales_raw), n_s_in, ex_by["sales.csv"]), refunds=(len(ref_raw), n_r_in, ex_by["refunds.csv"]), ads=(len(ads_raw), n_a_in, ex_by["ads.csv"])) print(counts) for p, t in tot.items(): print(p, {k: (str(v) if v is not None else None) for k, v in t.items()}) print("provisional refunds:", [(x["row_id"], x["refund_id"], x["order_id"], x["product"]) for x in provisional]) for p in provisional: print("order gross bound:", p["order_id"], paid_orders[p["order_id"]]["_g"])