dodil/erp-brick-timesheets
DODIL ERP brick: timesheets
src/brick_timesheets/application/migration.py
"""The Odoo slice (TS-22): Odoo 17's validated timesheets as approved weeks, reconciled per project, per order line and per employee, then signed by the owner
(workflow ts_migration_run).
The load is READ-ONLY on Odoo's side (a reader port, infrastructure/odoo.py) and idempotent here: every Odoo timesheet line is recorded in ts_migration_map, so a
second load writes nothing new. History is never re-priced: hours, days and Odoo's cost are carried as they stand. NOTHING is sent to a neighbour: no journal
(gl's migration carries the books), no delivery (the order's delivered quantity is the order's own migration), no invoice. A week is carried only when every
line of it is validated in Odoo. A SIGNED run is frozen.
Doors answered up front (no brick offers a map for them): employee_map (Odoo employee id -> hr person_id: hr has no migration map), project_map (Odoo project id
-> projects' project_id), so_line_map (Odoo order line id -> the order's id and line number in sales-order), upstream_signed (hr's, projects', sales-order's and
gl's migrations are signed: attested, no route says so)."""
import json
from contextlib import contextmanager
from datetime import date
from decimal import Decimal
from typing import Annotated, Literal
from erp_platform.bricks import Refused
from erp_platform.types import BigId
from pydantic import BaseModel, Field
from brick_timesheets.application import use_cases as hub
from brick_timesheets.application.inputs import Code, Day, Reason, Ref
from brick_timesheets.domain import odoo as od
from brick_timesheets.domain import sheet as sh
from brick_timesheets.domain.money import hours4
from brick_timesheets.infrastructure import odoo as odoo_port
TS = hub.TS
MIG_LOAD, MIG_RECONCILE, MIG_READ, MIG_SIGN = "ts:migration:load", "ts:migration:reconcile", "ts:migration:read", "ts:migration:sign"
CHECKS = ("unbilled_hours_per_order_line", "approved_hours_per_project", "weeks_carried_per_employee")
ODOO_KEY = Annotated[str, Field(max_length=20)]
class OrderLineIn(BaseModel):
"""Where an Odoo order line lives in sales-order: the order's id and its line number."""
sales_order_id: Ref
so_line_no: Annotated[int, Field(ge=1, le=9999)]
EmployeeMap = Annotated[dict[ODOO_KEY, BigId], Field(max_length=20000)]
ProjectMap = Annotated[dict[ODOO_KEY, Ref], Field(max_length=20000)]
OrderLineMap = Annotated[dict[ODOO_KEY, OrderLineIn], Field(max_length=50000)]
@contextmanager
def _odoo():
"""The reader, or the reply that there is none (409) / that Odoo failed (502)."""
try:
yield odoo_port.ports.get()
except odoo_port.OdooUnavailable as down:
raise Refused(down.http, down.reason) from None
def sign_gates(checks: list) -> list:
"""The gate of sign_ts_migration as {gate, met, reason, fix}: every check equal, or explained by the owner."""
open_ = [c["check"] for c in checks if not c["equal"] and not c.get("explained")]
return [{"gate": "checks_reconciled", "met": not open_ and bool(checks), "reason": ("checks neither equal nor explained: " + "; ".join(open_)) if open_ else
("the run has not been reconciled" if not checks else ""), "fix": "fix the mapping and load again, or explain each difference"}]
def _run_view(run: dict) -> dict:
checks = json.loads(run["checks_json"] or "[]")
doors = json.loads(run["doors_json"] or "{}")
return {"run_id": run["run_id"], "odoo_db": run["odoo_db"], "cutover_date": run["cutover_date"].isoformat(), "company_code": run["company_code"],
"status": run["status"], "doors": {"employees": len(doors.get("employee_map", {})), "projects": len(doors.get("project_map", {})),
"order_lines": len(doors.get("so_line_map", {})), "years": doors.get("years"), "activity_code": doors.get("activity_code"),
"upstream_signed": doors.get("upstream_signed")},
"checks": checks, "summary": json.loads(run["summary_json"] or "{}"), "loaded_by": run["loaded_by"], "signed_by": run["signed_by"],
"signed_on": run["signed_on"].isoformat() if run["signed_on"] else None, "discard_reason": run["discard_reason"],
"readiness": sign_gates(checks) if run["status"] == "reconciled" else None}
def _run_id(repo, db: str, cutover: date) -> str:
base = f"odoo:{db}:{cutover.isoformat()}"
n, rid = 1, base
while (run := repo.migration.run(rid)) is not None and run["status"] == "discarded":
n += 1
rid = f"{base}:{n}"
return rid
def _need_run(repo, run_id: str) -> dict:
run = repo.migration.run(run_id)
if run is None:
raise Refused(404, f"no migration run {run_id}")
return run
def _sheet_ids(repo, run_id: str) -> list:
"""The weeks a run carried, from its map: ours is <sheet_id>:1:<line_no>:<work_date>."""
return sorted({m["ours"].rsplit(":", 3)[0] for m in repo.migration.of_run(run_id)})
def _years_ok(rows: list, years: list) -> list:
return [r for r in rows if od.iso(r.get("date")) is not None and od.iso(r.get("date")).year in years]
# ── load ──────────────────────────────────────────────────────────────────────────────────────
@TS.use_case(kind="command", route="POST /migration/odoo/load", permission=MIG_LOAD, idempotent=True)
def load_ts_from_odoo(caller: dict, odoo_db: Code, cutover_date: Day, company_code: Code, years: Annotated[list[Annotated[int, Field(ge=2000, le=2100)]],
Field(min_length=1, max_length=6)], employee_map: EmployeeMap, project_map: ProjectMap, so_line_map: OrderLineMap, upstream_signed: bool,
activity_code: Code = "DEV") -> dict:
"""Load Odoo's timesheets READ-ONLY: each employee-week dated in `years` whose lines are ALL validated becomes an approved revision-1 week
odoo:<db>:<employee id>:<iso week> (one line per project and order line, hours per day, Odoo's own cost as the rate); a week with an unvalidated line is not
carried. Hours on an order line are billable and recorded as already delivered (the order's delivered quantity is the order's own migration); hours Odoo
invoiced are marked billed, so no run bills them again. Nothing is posted to gl or sent to an order: gl's migration carries the books. The doors are answered
up front (employee_map, project_map, so_line_map, upstream_signed). Idempotent on the Odoo line id; a signed run is never rewritten."""
if not upstream_signed:
raise Refused(409, "the hr, projects, sales-order and gl migrations must be signed first: Odoo's people, projects, orders and books are theirs",
gate="upstream_signed", fix="sign them, then load")
cutover = hub._date(cutover_date, "cutover_date")
who = hub._who(caller)
with _odoo() as reader, hub._decide():
currency = hub._cur(reader.company_currency(odoo_db))
rows = _years_ok(reader.timesheet_lines(odoo_db, list(years)), list(years))
found = od.problems(rows, employee_map, project_map, {k: v.model_dump() for k, v in so_line_map.items()}, list(years))
with hub.repository() as repo:
if not repo.activities.in_force(company_code, {activity_code}, cutover):
found.append(f"activity {activity_code} is not an activity of {company_code}: name one that is (activity_code)")
if found:
raise Refused(409, "The doors are not answered for everything Odoo holds: " + "; ".join(found[:20]) + (f" (and {len(found) - 20} more)" if len(found) > 20 else ""),
gate="doors_decided", fix="complete employee_map, project_map and so_line_map, then load again")
rid = _run_id(repo, odoo_db, cutover)
run = repo.migration.run(rid)
if run is not None and run["status"] == "signed":
raise Refused(409, f"run {rid} is signed: signed history is never rewritten - start a new run for what changed after the cut-over", gate="not_signed")
have = repo.migration.mapped(odoo_db, "account.analytic.line", [r["id"] for r in rows])
by_week = od.weeks(rows)
new, stats = [], {"odoo_lines": len(rows), "weeks_carried": 0, "weeks_unchanged": 0, "weeks_not_validated": 0, "late_lines": 0}
for (emp, monday), week in sorted(by_week.items()):
if not od.carried(week):
stats["weeks_not_validated"] += 1
continue
mapped = [r for r in week if str(r["id"]) in have]
if len(mapped) == len(week):
stats["weeks_unchanged"] += 1
elif mapped:
stats["late_lines"] += len(week) - len(mapped) # an approved week is never edited: the reconciliation shows what Odoo changed
else:
new.append((emp, monday, week))
now = hub._now()
plain_map = {k: v.model_dump() for k, v in so_line_map.items()}
sheets, claims, map_rows, historic = [], [], [], {}
delivered = invoiced = Decimal(0)
for emp, monday, week in new:
person = int(employee_map[str(emp)])
built = od.build_week(odoo_db, emp, monday, week, person, project_map, plain_map, activity_code)
s = sh.Sheet(built["sheet_id"], person, company_code, monday, sh.Identity("", ""), revision=1, status="approved", expected={}, approval_route="odoo",
currency=currency, approved_by=f"odoo:{odoo_db}", approved_sub=f"odoo:{odoo_db}", approved_at=now, approved_on=cutover,
last_line_no=len(built["lines"]))
for spec in built["lines"]:
order = plain_map.get(str(spec["so_line"])) if spec["so_line"] else None
entries = [sh.Entry(d, hours4(h), "", od.unit_cost(h, spec["cost_by_day"][d])) for d, h in sorted(spec["hours_by_day"].items())]
s.lines.append(sh.Line(spec["line_no"], "project", activity_code=activity_code, project_id=spec["project_id"], billable=order is not None,
bill_via="sales_order" if order else "none", sales_order_id=order["sales_order_id"] if order else "",
so_line_no=order["so_line_no"] if order else None, billing="time_and_materials" if order else "", entries=entries))
for oid, d in spec["odoo_ids"]:
map_rows.append({"odoo_db": odoo_db, "odoo_model": "account.analytic.line", "odoo_id": str(oid), "ours": f"{s.sheet_id}:1:{spec['line_no']}:{d.isoformat()}", "run_id": rid})
total = hours4(sum(spec["hours_by_day"].values(), Decimal(0)))
base = {"sheet_id": s.sheet_id, "revision": 1, "line_no": spec["line_no"], "sales_order_id": order["sales_order_id"] if order else None,
"so_line_no": order["so_line_no"] if order else None, "unit_price": None, "priced_from": None, "product_id": None, "tax_category": None}
if order:
claims.append({**base, "purpose": "delivery", "run_id": None, "delivery_ref": f"odoo:{odoo_db}:{s.sheet_id}:{spec['line_no']}", "quantity": total})
delivered += total
if spec["invoiced_hours"] > 0:
hist = f"tsbill:{spec['project_id']}:{rid}"
claims.append({**base, "purpose": "ar_run", "run_id": hist, "delivery_ref": None, "quantity": spec["invoiced_hours"]})
historic[hist] = (spec["project_id"], historic.get(hist, (None, 0))[1] + 1)
invoiced += spec["invoiced_hours"]
sheets.append(s)
runs = [{"run_id": h, "project_id": p, "period_id": rid, "run_no": 1, "company_code": company_code, "bp_id": None, "currency": currency, "status": "released",
"source_id": f"odoo:{odoo_db}:{p}", "lines_json": "[]", "total_amount": None, "claim_count": n, "invoice_id": None,
"reason": f"invoiced in Odoo before the cut-over (migration run {rid})", "created_by": who, "released_by": f"odoo:{odoo_db}",
"released_sub": f"odoo:{odoo_db}", "released_at": now, "released_on": cutover, "ended_by": None} for h, (p, n) in sorted(historic.items())]
stats |= {"weeks_carried": len(sheets), "sheets_written": len(sheets), "lines_written": sum(len(s.lines) for s in sheets), "hours_delivered_in_odoo": f"{delivered:.4f}",
"hours_invoiced_in_odoo": f"{invoiced:.4f}"}
if sheets:
repo.migration.add_sheets(sheets, now)
repo.billing.add_many(claims, now) if claims else None
if runs:
for r in runs:
repo.runs.add(r, now)
repo.migration.add_map(map_rows, now)
doors = {"employee_map": employee_map, "project_map": project_map, "so_line_map": plain_map, "years": sorted(set(years)), "upstream_signed": True,
"activity_code": activity_code}
row = {"run_id": rid, "odoo_db": odoo_db, "cutover_date": cutover, "company_code": company_code, "status": "loaded", "doors_json": json.dumps(doors, sort_keys=True),
"checks_json": "[]", "summary_json": json.dumps(stats, sort_keys=True), "loaded_by": who, "signed_by": None, "signed_sub": None, "signed_on": None,
"discarded_by": None, "discard_reason": None}
if run is None:
repo.migration.add_run(row, now)
elif sheets:
row = {**run, "status": "loaded", "checks_json": "[]", "summary_json": json.dumps(stats, sort_keys=True)} # new weeks: reconcile again
repo.migration.save_run(row, now)
else:
row = run
shown = {**row, "summary_json": json.dumps(stats, sort_keys=True)} if run is None or sheets else row
return _run_view(shown) | {"stats": stats}
# ── reconcile, explain, sign, discard ─────────────────────────────────────────────────────────────
def _odoo_side(rows: list, doors: dict) -> tuple:
"""(unbilled hours per Odoo order line, hours per OUR project, carried weeks per Odoo employee): Odoo's own numbers, from the lines now."""
unbilled, projects, weeks = {}, {}, {}
for (emp, _monday), week in od.weeks(rows).items():
if not od.carried(week):
continue
weeks[emp] = weeks.get(emp, 0) + 1
for r in week:
h = hours4(od.num(r.get("unit_amount"), "unit_amount"))
p = doors["project_map"].get(str(od.ref_id(r.get("project_id"))))
projects[p] = projects.get(p, Decimal(0)) + h
so = od.ref_id(r.get("so_line"))
if so is not None and not od.invoiced(r):
unbilled[so] = unbilled.get(so, Decimal(0)) + h
return unbilled, projects, weeks
@TS.use_case(kind="command", route="POST /migration/odoo/{run_id}/reconcile", permission=MIG_RECONCILE, idempotent=True)
def reconcile_ts_migration(caller: dict, run_id: Code) -> dict:
"""Compare what was loaded with Odoo, read again, and keep the checks on the run (loaded -> reconciled): the hours not yet invoiced per order line (Odoo's
own, against delivered less billed here), the approved hours per project and the carried weeks per employee. Each check says Odoo's number, ours and whether
they are equal; every one must be equal or explained by the owner before it is signed. A signed run is not touched."""
with _odoo() as reader, hub._decide():
with hub.repository() as repo:
run = _need_run(repo, run_id)
if run["status"] not in ("loaded", "reconciled"):
raise Refused(409, f"run {run_id} is {run['status']}: only a loaded run is reconciled")
doors = json.loads(run["doors_json"])
rows = _years_ok(reader.timesheet_lines(run["odoo_db"], doors["years"]), doors["years"])
ids = _sheet_ids(repo, run_id)
claims = repo.migration.claims_of(ids)
ours_projects = repo.migration.hours_by_project(ids)
ours_weeks: dict = {}
for r in repo.migration.sheets_of(ids):
ours_weeks[r["person_id"]] = ours_weeks.get(r["person_id"], 0) + 1
o_unbilled, o_projects, o_weeks = _odoo_side(rows, doors)
# 1. hours not yet invoiced, per order line (compared by Odoo order line)
held: dict = {}
for c in claims:
if c["sales_order_id"] is None:
continue
key = (c["sales_order_id"], c["so_line_no"])
sign = 1 if c["purpose"] == "delivery" else -1 if c["purpose"] == "ar_run" else 0
held[key] = held.get(key, Decimal(0)) + sign * hours4(c["quantity"] or 0)
line_of = {int(k): (v["sales_order_id"], v["so_line_no"]) for k, v in doors["so_line_map"].items()}
per_line = {}
for so in sorted(set(o_unbilled) | {k for k, v in line_of.items() if v in held}):
per_line[str(so)] = {"odoo": f"{o_unbilled.get(so, Decimal(0)):.4f}", "ours": f"{held.get(line_of.get(so), Decimal(0)):.4f}"}
c1 = {"check": CHECKS[0], "must": "equal", "odoo": f"{sum(o_unbilled.values(), Decimal(0)):.4f}", "ours": f"{sum(held.values(), Decimal(0)):.4f}", "detail": per_line,
"equal": all(v["odoo"] == v["ours"] for v in per_line.values())}
# 2. approved hours per project
per_project = {str(p): {"odoo": f"{o_projects.get(p, Decimal(0)):.4f}", "ours": f"{ours_projects.get(p, Decimal(0)):.4f}"} for p in sorted({*o_projects, *ours_projects}, key=str)}
c2 = {"check": CHECKS[1], "must": "equal", "odoo": f"{sum(o_projects.values(), Decimal(0)):.4f}", "ours": f"{sum(ours_projects.values(), Decimal(0)):.4f}",
"detail": per_project, "equal": all(v["odoo"] == v["ours"] for v in per_project.values())}
# 3. carried weeks per employee
emp_of = {int(p): e for e, p in doors["employee_map"].items()}
ours_by_emp = {emp_of.get(p, f"person {p}"): n for p, n in ours_weeks.items()}
per_emp = {str(e): {"odoo": str(o_weeks.get(int(e), 0) if str(e).isdigit() else 0), "ours": str(ours_by_emp.get(str(e), 0))}
for e in sorted({*(str(k) for k in o_weeks), *ours_by_emp}, key=str)}
c3 = {"check": CHECKS[2], "must": "equal", "odoo": str(sum(o_weeks.values())), "ours": str(sum(ours_weeks.values())), "detail": per_emp,
"equal": all(v["odoo"] == v["ours"] for v in per_emp.values())}
with hub.repository() as repo:
run = _need_run(repo, run_id)
old = {c["check"]: c for c in json.loads(run["checks_json"] or "[]")}
checks = [{**c, "explained": (old.get(c["check"]) or {}).get("explained") if not c["equal"] else None} for c in (c1, c2, c3)]
row = {**run, "status": "reconciled", "checks_json": json.dumps(checks, sort_keys=True)}
repo.migration.save_run(row, hub._now())
return _run_view(row)
@TS.use_case(kind="command", route="POST /migration/odoo/{run_id}/explain", permission=MIG_SIGN, idempotent=True)
def explain_ts_check(caller: dict, run_id: Code, check: Literal["unbilled_hours_per_order_line", "approved_hours_per_project", "weeks_carried_per_employee"],
reason: Reason) -> dict:
"""The owner explains one difference the reconciliation found (a week validated in Odoo after the load, an order line edited by hand), so that it can be
signed with it. A check that is equal needs no explanation; the reason is kept on the run."""
if not reason.strip():
raise Refused(422, "an explanation needs a reason", gate="bounds")
with hub.repository() as repo, hub._decide():
run = _need_run(repo, run_id)
if run["status"] != "reconciled":
raise Refused(409, f"run {run_id} is {run['status']}: only a reconciled run has differences to explain")
checks = json.loads(run["checks_json"])
c = next(x for x in checks if x["check"] == check)
if c["equal"]:
raise Refused(409, f"{check} is equal: there is nothing to explain")
c["explained"] = f"{reason.strip()} ({hub._who(caller)}, {hub._today(repo)})"
row = {**run, "checks_json": json.dumps(checks, sort_keys=True)}
repo.migration.save_run(row, hub._now())
return _run_view(row)
@TS.use_case(kind="command", route="POST /migration/odoo/{run_id}/sign", permission=MIG_SIGN, idempotent=True)
def sign_ts_migration(caller: dict, run_id: Code) -> dict:
"""The owner signs the reconciliation (reconciled -> signed, terminal): that nothing billable was lost, every check equal or explained. A signed run is
frozen - a later load never rewrites it. Signing twice is one signature."""
with hub.repository() as repo, hub._decide():
run = _need_run(repo, run_id)
if run["status"] == "signed":
return _run_view(run)
if run["status"] != "reconciled":
raise Refused(409, f"run {run_id} is {run['status']}: only a reconciled run is signed", gate="checks_reconciled", fix="reconcile it first")
gates = sign_gates(json.loads(run["checks_json"]))
unmet = [g for g in gates if not g["met"]]
if unmet:
raise Refused(409, "Cannot sign: " + "; ".join(f"{g['gate']}: {g['reason']}" for g in unmet), gate=unmet[0]["gate"], fix=unmet[0]["fix"], fields={"gates": gates})
me = hub._me(caller)
repo.claims.once("migration_sign", run_id, hub._now(), hub._who(caller))
row = {**run, "status": "signed", "signed_by": me.email or me.sub, "signed_sub": me.sub, "signed_on": hub._today(repo)}
repo.migration.save_run(row, hub._now())
return _run_view(row)
@TS.use_case(kind="command", route="POST /migration/odoo/{run_id}/discard", permission=MIG_SIGN, destructive=True)
def discard_ts_migration(caller: dict, run_id: Code, reason: Reason) -> dict:
"""The owner discards a run that is loaded or reconciled (a reason is required; terminal): the weeks, lines, entries, claims, historic billing runs and map it
wrote are taken back out. Refused once anything has been done to its weeks since - a correction opened, hours delivered or billed again - and for a signed
run, which is history."""
if not reason.strip():
raise Refused(422, "a reason is required to discard a run", gate="bounds")
with hub.repository() as repo, hub._decide():
run = _need_run(repo, run_id)
if run["status"] not in ("loaded", "reconciled"):
raise Refused(409, f"run {run_id} is {run['status']}: only a loaded or reconciled run is discarded")
ids = _sheet_ids(repo, run_id)
roots = repo.migration.sheets_of(ids)
hist = {r["run_id"] for r in repo.migration.historic_runs(run_id)}
foreign = [c for c in repo.migration.claims_of(ids) if (c["purpose"] == "ar_run" and c["run_id"] not in hist)
or (c["purpose"] == "delivery" and not str(c["delivery_ref"] or "").startswith("odoo:"))]
if any(r["revision"] != 1 or r["status"] != "approved" for r in roots) or foreign:
raise Refused(409, "a week this run carried has been corrected, delivered or billed since the load: it cannot be discarded", gate="nothing_moved",
fix="reconcile and fix instead")
repo.migration.delete_run(run_id, ids)
row = {**run, "status": "discarded", "discarded_by": hub._who(caller), "discard_reason": reason.strip()}
repo.migration.save_run(row, hub._now())
return _run_view(row)
@TS.use_case(kind="query", route="GET /migration/odoo/{run_id}", permission=MIG_READ)
def get_ts_migration_run(caller: dict, run_id: Code) -> dict:
"""One migration run: its status, the doors it was loaded with, the reconciliation's checks (Odoo's number, ours, explained) and what signing still needs."""
with hub.repository() as repo:
return _run_view(_need_run(repo, run_id))
@TS.use_case(kind="query", route="GET /migration/odoo", permission=MIG_READ)
def list_ts_migration_runs(caller: dict) -> dict:
"""Every migration run, oldest first: id, Odoo database, cut-over, status."""
with hub.repository() as repo:
return {"runs": [{"run_id": r["run_id"], "odoo_db": r["odoo_db"], "cutover_date": r["cutover_date"].isoformat(), "status": r["status"]} for r in repo.migration.runs()]}
@TS.use_case(kind="query", route="GET /migration-map/{db}/{model}", permission=MIG_READ)
def get_ts_migration_map(caller: dict, db: Code, model: Literal["account.analytic.line"], ids: Annotated[str, Field(max_length=8000)]) -> list:
"""The one map route every migrating brick offers (shared.yaml conventions.odoo_migration): for a comma-separated list of Odoo ids, [{odoo_model, odoo_id,
ours}] - our key (<sheet_id>:1:<line_no>:<work_date>, the day's entry). An id not migrated is omitted, never null and never an error."""
want = [i.strip() for i in ids.split(",") if i.strip()]
if not want or not all(i.isdigit() and len(i) <= 18 for i in want):
raise hub._bounds("ids are Odoo ids, comma-separated digits such as 12,13")
with hub.repository() as repo:
got = repo.migration.mapped(db, model, want)
return [{"odoo_model": model, "odoo_id": i, "ours": got[i]["ours"]} for i in want if i in got]