Spaces:
Running on CPU Upgrade
Running on CPU Upgrade
Download platform/modules/payouts_forecast.py from fsanyoto/runloopable: direct link, hf CLI and curl.
- Browser
- Download file 31.2 kB
-
https://huggingface.co/spaces/fsanyoto/runloopable/resolve/main/platform/modules/payouts_forecast.py
- Command line
-
hf download hf://spaces/fsanyoto/runloopable/platform/modules/payouts_forecast.py
-
curl -L -o payouts_forecast.py https://huggingface.co/spaces/fsanyoto/runloopable/resolve/main/platform/modules/payouts_forecast.py
31.2 kB
| """Forecast cash by currency from settlement deposits and unpaid mirror obligations.""" | |
| from __future__ import annotations | |
| import contextlib | |
| import datetime as dt | |
| import math | |
| import statistics | |
| from core.connector_spec import odoo_model_table_key | |
| from modules import payouts as pay | |
| KEY_SETTINGS = pay.KEY_SETTINGS | |
| SOURCES = {"settlements": pay.SOURCE_KEY, "economics": "ut_amzn_economics", | |
| **{table: odoo_model_table_key(model) for table, model in ( | |
| ("account_move", "account.move"), ("purchase_order", "purchase.order"), | |
| ("purchase_order_line", "purchase.order.line"), ("account_bank_statement", "account.bank.statement"), | |
| ("res_company", "res.company"), ("res_currency", "res.currency"))}} | |
| _UNSET = object() | |
| def _currency(value): | |
| text = str(value or "").strip().upper() | |
| return text if len(text) == 3 and text.isascii() and text.isalpha() else None | |
| def settings(tenant_key, *, st=None): | |
| """Read typed App assumptions without treating an absent cash amount as zero.""" | |
| doc = pay._doc(pay._rt(tenant_key, st), KEY_SETTINGS) | |
| cash = doc.get("opening_cash") | |
| if isinstance(cash, bool) or not isinstance(cash, (float, int)) or not math.isfinite(cash): | |
| cash = None | |
| terms = doc.get("vendor_terms_days") | |
| if isinstance(terms, bool) or not isinstance(terms, int) or terms < 0: | |
| terms = None | |
| return {"openingCash": cash, "openingCashCurrency": _currency(doc.get("opening_cash_currency")), | |
| "vendorTermsDays": terms, "basis": "Typed Payouts settings", "openingCashEditableBy": "admin", | |
| "purchaseOrderBasis": "Posted bills replace named purchase orders, including partial bills."} | |
| def set_settings(tenant_key, *, st=None, admin=False, opening_cash=_UNSET, | |
| opening_cash_currency=_UNSET, vendor_terms_days=_UNSET): | |
| """Save typed assumptions and reserve opening cash edits for administrators.""" | |
| if (opening_cash is not _UNSET or opening_cash_currency is not _UNSET) and not admin: | |
| raise PermissionError("Only an administrator can change opening cash.") | |
| if opening_cash is not _UNSET and opening_cash is not None and ( | |
| isinstance(opening_cash, bool) or not isinstance(opening_cash, (int, float)) | |
| or not math.isfinite(opening_cash)): | |
| raise ValueError("Opening cash must be a finite amount or unset.") | |
| if opening_cash_currency is not _UNSET and opening_cash_currency is not None: | |
| opening_cash_currency = _currency(opening_cash_currency) | |
| if opening_cash_currency is None: | |
| raise ValueError("Opening cash currency must be a three-letter currency.") | |
| if vendor_terms_days is not _UNSET and vendor_terms_days is not None and ( | |
| isinstance(vendor_terms_days, bool) or not isinstance(vendor_terms_days, int) or vendor_terms_days < 0): | |
| raise ValueError("Vendor terms days must be a whole number of zero or more.") | |
| st = pay._rt(tenant_key, st) | |
| def apply(doc): | |
| doc = doc if isinstance(doc, dict) else {} | |
| for key, value in (("opening_cash", opening_cash), ("opening_cash_currency", opening_cash_currency), | |
| ("vendor_terms_days", vendor_terms_days)): | |
| if value is not _UNSET: | |
| doc[key] = value | |
| return doc | |
| st.update(KEY_SETTINGS, apply) | |
| return settings(tenant_key, st=st) | |
| def _allowed(access, name): | |
| return access is None or access.get(name) is True | |
| def _label_sql(expressions): | |
| choices = [f"CASE WHEN regexp_full_match(upper(trim(CAST({e} AS VARCHAR))), '[A-Z]{{3}}') " | |
| f"THEN upper(trim(CAST({e} AS VARCHAR))) END" for e in expressions] | |
| return "COALESCE(" + ", ".join([*choices, "NULL"]) + ")" if choices else "CAST(NULL AS VARCHAR)" | |
| def _currency_sql(cur, table, alias, access, *, company=False): | |
| """Resolve signed bill currency from company metadata, never the invoice currency.""" | |
| cols = pay._columns(cur, table) | |
| names = ("company_currency_name", "company_currency_id_name") if company else ("currency_name", "currency_id_name") | |
| labels = [f"{alias}.{n}" for n in names if n in cols] | |
| joins = "" | |
| currency_cols = pay._columns(cur, "res_currency") if _allowed(access, "res_currency") else set() | |
| relation = "company_currency_id" if company else "currency_id" | |
| if relation in cols and {"id", "name"} <= currency_cols: | |
| joins += f" LEFT JOIN res_currency rc ON rc.id = {alias}.{relation}" | |
| labels.append("rc.name") | |
| companies = pay._columns(cur, "res_company") if company and _allowed(access, "res_company") else set() | |
| if "company_id" in cols and "id" in companies: | |
| joins += f" LEFT JOIN res_company co ON co.id = {alias}.company_id" | |
| labels += [f"co.{n}" for n in ("currency_name", "currency_id_name") if n in companies] | |
| if "currency_id" in companies and {"id", "name"} <= currency_cols: | |
| joins += " LEFT JOIN res_currency cc ON cc.id = co.currency_id" | |
| labels.append("cc.name") | |
| return _label_sql(labels), joins | |
| def _not_billed(): | |
| return """NOT EXISTS (SELECT 1 FROM account_move m WHERE m.state = 'posted' | |
| AND m.move_type = 'in_invoice' AND list_contains( | |
| regexp_split_to_array(trim(COALESCE(m.invoice_origin, '')), '[,;\\s]+'), p.name))""" | |
| def _events_sql(cur, assumptions, access, *, independent=False): | |
| """Build obligation rows in SQL before aggregating or paging their evidence.""" | |
| missing, parts = [], [] | |
| specs = {"account_move": {"id", "name", "state", "move_type", "amount_residual_signed", | |
| "invoice_date_due", "invoice_origin"}, | |
| "purchase_order": {"id", "name", "state"}, | |
| "purchase_order_line": {"id", "order_id", "date_planned", "price_total", "product_qty", "qty_invoiced"}} | |
| ready = {} | |
| for table, required in specs.items(): | |
| ready[table] = _allowed(access, table) and required <= pay._columns(cur, table) | |
| if not ready[table]: | |
| missing.append(f"{table} unavailable: source grant, fields or mirror missing.") | |
| if ready["account_move"]: | |
| currency, joins = _currency_sql(cur, "account_move", "m", access, company=True) | |
| amount = ("-TRY_CAST(m.amount_residual_signed AS DECIMAL(38, 6))" if independent | |
| else "0 - TRY_CAST(m.amount_residual_signed AS DECIMAL(38, 6))") | |
| parts.append(f"""SELECT 'vendorBills' AS kind, CAST(m.id AS VARCHAR) AS id, m.name, | |
| TRY_CAST(m.invoice_date_due AS DATE) AS due, {currency} AS currency, {amount} AS amount | |
| FROM account_move m{joins} WHERE m.state = 'posted' AND m.move_type = 'in_invoice' | |
| AND (m.amount_residual_signed IS NULL OR m.amount_residual_signed <> 0)""") | |
| if all(ready.values()): | |
| if assumptions["vendorTermsDays"] is None: | |
| missing.append("purchase_order_line unavailable: typed vendor terms days are missing.") | |
| else: | |
| currency, joins = _currency_sql(cur, "purchase_order", "p", access) | |
| terms = assumptions["vendorTermsDays"] | |
| predicate = _not_billed() | |
| if independent: | |
| predicate = """p.name NOT IN (SELECT token FROM account_move b, | |
| UNNEST(regexp_split_to_array(COALESCE(b.invoice_origin, ''), '[,;\\s]+')) AS origin(token) | |
| WHERE b.state = 'posted' AND b.move_type = 'in_invoice' AND token <> '')""" | |
| amount = ("TRY_CAST(l.price_total AS DECIMAL(38, 6)) * " | |
| "greatest(l.product_qty - l.qty_invoiced, 0) / NULLIF(l.product_qty, 0)") | |
| if independent: | |
| amount = ("TRY_CAST(l.price_total AS DECIMAL(38, 6)) - " | |
| "TRY_CAST(l.price_total AS DECIMAL(38, 6)) * least(l.qty_invoiced / NULLIF(l.product_qty, 0), 1)") | |
| amount = ("CASE WHEN l.product_qty IS NULL OR l.qty_invoiced IS NULL " | |
| "OR NOT isfinite(l.product_qty) OR NOT isfinite(l.qty_invoiced) " | |
| "OR l.product_qty <= 0 OR l.qty_invoiced < 0 THEN NULL ELSE " + amount + " END") | |
| parts.append(f"""SELECT 'purchaseOrders' AS kind, CAST(l.id AS VARCHAR) AS id, p.name, | |
| CAST(TRY_CAST(l.date_planned AS TIMESTAMP) AS DATE) + {terms} AS due, | |
| {currency} AS currency, {amount} AS amount | |
| FROM purchase_order p JOIN purchase_order_line l ON l.order_id = p.id{joins} | |
| WHERE p.state IN ('purchase', 'done') AND {predicate} | |
| AND (l.product_qty IS NULL OR l.qty_invoiced IS NULL OR l.product_qty > l.qty_invoiced)""") | |
| orphan = cur.execute("SELECT count(*) FROM purchase_order p WHERE p.state IN ('purchase', 'done') " | |
| f"AND {predicate} AND NOT EXISTS (SELECT 1 FROM purchase_order_line l " | |
| "WHERE l.order_id = p.id)").fetchone()[0] | |
| if orphan: | |
| missing.append(f"purchase_order_line unavailable: {orphan} open purchase orders have no lines.") | |
| empty = ("SELECT CAST(NULL AS VARCHAR) AS kind, CAST(NULL AS VARCHAR) AS id, CAST(NULL AS VARCHAR) AS name, " | |
| "CAST(NULL AS DATE) AS due, CAST(NULL AS VARCHAR) AS currency, CAST(NULL AS DOUBLE) AS amount WHERE false") | |
| return " UNION ALL ".join(parts) or empty, missing | |
| def _aggregate(cur, events, today, weeks): | |
| sql = f"""WITH events AS ({events}), scheduled AS ( | |
| SELECT *, CASE WHEN due IS NULL THEN NULL ELSE greatest(0, | |
| least({weeks}, CAST(floor(date_diff('day', DATE '{today}', due) / 7.0) AS INTEGER))) END AS slot FROM events) | |
| SELECT kind, currency, slot, count(*), sum(amount), | |
| count(*) FILTER (WHERE amount IS NULL OR NOT isfinite(amount)) | |
| FROM scheduled GROUP BY kind, currency, slot ORDER BY kind, currency, slot""" | |
| return [{"kind": k, "currency": c, "slot": slot, "count": int(n), | |
| "amount": None if amount is None or not math.isfinite(float(amount)) else pay._money(amount), | |
| "invalidAmounts": int(bad)} for k, c, slot, n, amount, bad in cur.execute(sql).fetchall()] | |
| def _evidence_page(cur, events, limit, offset, today): | |
| """Page original due dates in SQL without limiting any forecast source sum.""" | |
| total = int(cur.execute(f"SELECT count(*) FROM ({events}) obligations").fetchone()[0]) | |
| rows = cur.execute(f"SELECT * FROM ({events}) obligations ORDER BY due NULLS FIRST, kind, id LIMIT ? OFFSET ?", | |
| [limit, offset]).fetchall() | |
| evidence = [{"kind": k, "id": ident, "name": name, "dueDate": due.isoformat() if due else None, | |
| "currency": currency, "amount": pay._money(amount) if amount is not None and math.isfinite(float(amount)) else None, | |
| "overdue": due is not None and due < today} for k, ident, name, due, currency, amount in rows] | |
| return evidence, total | |
| def _odoo_source(tenant_key, st, con): | |
| if con is not None: | |
| yield con | |
| else: | |
| with pay._reader(tenant_key, st) as cur: | |
| yield cur | |
| def _stream_sql(cur): | |
| """Keep source connection, store and marketplace identities distinct when the mirror names them.""" | |
| cols = pay._columns(cur, pay.SETTLEMENTS) | |
| fields = [f"COALESCE(CAST({name} AS VARCHAR), '')" for name in ("_connection_id", "store", "amazon_store") if name in cols] | |
| return "to_json(list_value(" + ", ".join(fields) + "))" if fields else "'[]'" | |
| def _deposits(tenant_key, st, con, today, weeks, access): | |
| """Reuse the settlement reader and derive dated projections from its stored source.""" | |
| if not _allowed(access, "settlements"): | |
| return [], [], ["settlements unavailable: source grant refused."] | |
| if not _allowed(access, "economics"): | |
| return [], [], ["settlements unavailable: the settlement reader's economics source grant is refused."] | |
| rows, cursor = [], None | |
| try: | |
| while True: | |
| page = pay.settlements(tenant_key, st=st, con=con, cursor=cursor, limit=pay.PAGE) | |
| rows.extend(page["settlements"]) | |
| cursor = page["cursor"] | |
| if cursor is None: | |
| break | |
| with pay._source(tenant_key, st, con) as (cur, _source): | |
| stream_sql = _stream_sql(cur) | |
| # Line currency comes from its settlement, not a marketplace or numeric currency ID. | |
| sql = f"""WITH currencies AS (SELECT settlement_id, | |
| any_value(NULLIF(upper(trim(currency)), '')) AS currency, | |
| any_value({stream_sql}) AS stream FROM amzn_settlements GROUP BY 1) | |
| SELECT c.currency, c.stream, COALESCE(sum(s.amount), 0) FROM amzn_settlements s | |
| JOIN currencies c USING (settlement_id) | |
| WHERE {pay._POSTED.replace('posted_date', 's.posted_date')} >= DATE '{today - dt.timedelta(days=28)}' | |
| AND {pay._POSTED.replace('posted_date', 's.posted_date')} < DATE '{today}' | |
| AND {pay._bucket_sql()} = 'sales' GROUP BY c.currency, c.stream""" | |
| pace = {(c, stream): pay._money(a) for c, stream, a in cur.execute(sql).fetchall()} | |
| metadata = {sid: (currency, start, end, first, last, total, amount, stream) | |
| for sid, currency, start, end, first, last, total, amount, stream in cur.execute( | |
| f"SELECT CAST(settlement_id AS VARCHAR), any_value(NULLIF(upper(trim(currency)), '')), " | |
| f"min(CAST(settlement_start_date AS VARCHAR)), max(CAST(settlement_end_date AS VARCHAR)), " | |
| f"min({pay._POSTED}), max({pay._POSTED}), max(total_amount), sum(amount), any_value({stream_sql}) " | |
| f"FROM amzn_settlements GROUP BY 1").fetchall()} | |
| # The settlement reader omits undated groups, including open lines without a final header. | |
| listed = {r["id"] for r in rows} | |
| for sid, meta in metadata.items(): | |
| if sid not in listed and meta[6] is not None: | |
| rows.append({"id": sid, "currency": meta[0], "start": meta[1], "end": meta[2], | |
| "amazonTotal": meta[5], "linesTotal": pay._money(meta[6])}) | |
| groups = {} | |
| missing = [] | |
| for row in rows: | |
| meta = metadata.get(row["id"], (None,) * 8) | |
| row = {**row, "start": row["start"] or meta[1], "end": row["end"] or meta[2]} | |
| currency = _currency(row["currency"] or meta[0]) | |
| if currency is None: | |
| missing.append("settlements unavailable: a settlement currency is missing.") | |
| continue | |
| groups.setdefault((currency, meta[7]), []).append(row) | |
| if any(currency is None for currency, _stream in pace): | |
| missing.append("settlements unavailable: sales pace has no currency.") | |
| events, inputs = [], [] | |
| end = today + dt.timedelta(weeks=weeks) | |
| for (currency, stream), mine in groups.items(): | |
| dated = [r for r in mine if pay.parse_date(r["start"]) and pay.parse_date(r["end"])] | |
| cycles = [(pay.parse_date(r["end"]) - pay.parse_date(r["start"])).days for r in dated] | |
| cycles = [n for n in cycles if n > 0] | |
| if not cycles or not dated: | |
| missing.append(f"settlements unavailable: {currency} settlement cycle is missing.") | |
| continue | |
| cycle = max(1, int(statistics.median(cycles))) | |
| opened = [r for r in dated if pay.parse_date(r["start"]) <= today < pay.parse_date(r["end"])] | |
| close_basis = "Settlement end day" | |
| if not opened: | |
| closed = [pay.parse_date(r["end"]) for r in dated if pay.parse_date(r["end"]) <= today] | |
| start = max(closed) if closed else None | |
| if start and start <= today < start + dt.timedelta(days=cycle): | |
| for row in mine: | |
| meta = metadata[row["id"]] | |
| if (row["amazonTotal"] is None and meta[3] and meta[4] | |
| and start <= meta[3] <= meta[4] <= today): | |
| opened.append({**row, "end": (start + dt.timedelta(days=cycle)).isoformat()}) | |
| close_basis = "Last closed settlement plus median cycle" | |
| if not opened: | |
| missing.append(f"settlements unavailable: {currency} open settlement running total or close day is missing.") | |
| continue | |
| if len({pay.parse_date(r["end"]) for r in opened}) > 1: | |
| missing.append(f"settlements unavailable: {currency} has conflicting open close days within one source stream.") | |
| continue | |
| close = max(pay.parse_date(r["end"]) for r in opened) | |
| running = pay._money(sum(r["linesTotal"] for r in opened)) | |
| sales = pace.get((currency, stream), 0.0) | |
| inputs.append({"currency": currency, "stream": stream, "settlementIds": sorted(r["id"] for r in opened), | |
| "runningTotal": running, "sales28d": sales, "cycleDays": cycle, | |
| "expectedClose": close.isoformat(), "closeBasis": close_basis, | |
| "basis": "Open running total, then 28-day sales pace"}) | |
| if close < end: | |
| events.append({"date": close.isoformat(), "currency": currency, "stream": stream, "amount": running, "basis": "open"}) | |
| next_day = close + dt.timedelta(days=cycle) | |
| while next_day < end: | |
| events.append({"date": next_day.isoformat(), "currency": currency, "stream": stream, | |
| "amount": pay._money(sales * cycle / 28), "basis": "sales pace"}) | |
| next_day += dt.timedelta(days=cycle) | |
| if not groups: | |
| missing.append("settlements unavailable: no dated settlement stream is stored.") | |
| return events, inputs, missing | |
| except pay.SourceUnavailable as exc: | |
| return [], [], [f"settlements unavailable: {exc}"] | |
| def forecast(tenant_key, weeks=13, *, st=None, con=None, now=None, source_access=None, | |
| cursor=None, limit=50, _validate=True): | |
| """Return weekly currency aggregates, the first short week and paged original obligation evidence.""" | |
| if isinstance(weeks, bool) or not isinstance(weeks, int) or weeks < 1: | |
| raise ValueError("Weeks must be a positive whole number.") | |
| pay._page([], cursor, 1) | |
| offset = int(cursor or 0) | |
| if isinstance(limit, bool) or not isinstance(limit, int) or limit < 1: | |
| raise ValueError("Evidence page size must be a positive whole number.") | |
| moment = pay.parse_moment(now) if now is not None else pay._now() | |
| today = moment.date() | |
| st = pay._rt(tenant_key, st) | |
| assumptions = settings(tenant_key, st=st) | |
| unavailable = [] | |
| deposits, inputs, missing = _deposits(tenant_key, st, con, today, weeks, source_access) | |
| unavailable.extend(missing) | |
| aggregates, evidence, total, bank_date = [], [], 0, None | |
| with _odoo_source(tenant_key, st, con) as cur: | |
| if cur is None: | |
| unavailable.extend(f"{t} unavailable: mirror missing." for t in ( | |
| "account_move", "purchase_order", "purchase_order_line", "account_bank_statement")) | |
| else: | |
| events, missing = _events_sql(cur, assumptions, source_access) | |
| unavailable.extend(missing) | |
| aggregates = _aggregate(cur, events, today, weeks) | |
| evidence, total = _evidence_page(cur, events, limit, offset, today) | |
| if _allowed(source_access, "account_bank_statement") and "date" in pay._columns(cur, "account_bank_statement"): | |
| bank_date = cur.execute("SELECT max(TRY_CAST(date AS DATE)) FROM account_bank_statement").fetchone()[0] | |
| if bank_date is None: | |
| unavailable.append("account_bank_statement unavailable: latest bank date is missing.") | |
| else: | |
| unavailable.append("account_bank_statement unavailable: source grant, date or mirror missing.") | |
| for row in aggregates: | |
| if row["currency"] is None: | |
| unavailable.append(f"{row['kind']} unavailable: authoritative currency is missing for {row['count']} obligations.") | |
| if row["slot"] is None: | |
| unavailable.append(f"{row['kind']} unavailable: due dates are missing for {row['count']} obligations.") | |
| if row["invalidAmounts"]: | |
| unavailable.append(f"{row['kind']} unavailable: obligation amounts are missing or invalid.") | |
| if assumptions["openingCash"] is None: | |
| unavailable.append("Typed opening cash amount is missing.") | |
| if assumptions["openingCashCurrency"] is None: | |
| unavailable.append("Typed opening cash currency is missing.") | |
| currencies = sorted({r["currency"] for r in [*aggregates, *deposits] if r["currency"]} | |
| | ({assumptions["openingCashCurrency"]} if assumptions["openingCashCurrency"] else set())) | |
| schedule = [] | |
| first = [] | |
| for currency in currencies: | |
| typed = currency == assumptions["openingCashCurrency"] and assumptions["openingCash"] is not None | |
| balance = assumptions["openingCash"] if typed and not unavailable else None | |
| if not typed and currency != assumptions["openingCashCurrency"]: | |
| unavailable.append(f"Typed opening cash is unavailable for {currency}.") | |
| short = None | |
| for index in range(weeks): | |
| start = today + dt.timedelta(weeks=index) | |
| stop = start + dt.timedelta(days=7) | |
| deposit = pay._money(sum(r["amount"] for r in deposits if r["currency"] == currency | |
| and start <= dt.date.fromisoformat(r["date"]) < stop)) | |
| values = {kind: pay._money(sum(r["amount"] or 0 for r in aggregates if r["kind"] == kind | |
| and r["currency"] == currency and r["slot"] == index)) for kind in ("vendorBills", "purchaseOrders")} | |
| # An unavailable source is never displayed as zero obligations or a proved balance. | |
| bills_ready = not any("account_move unavailable" in m or "vendorBills unavailable" in m for m in unavailable) | |
| pos_ready = not any("purchase_order" in m or "account_move unavailable" in m | |
| or "purchaseOrders unavailable" in m for m in unavailable) | |
| deposits_ready = not any("settlements unavailable" in m for m in unavailable) | |
| if balance is not None: | |
| balance = pay._money(balance + deposit - values["vendorBills"] - values["purchaseOrders"]) | |
| row = {"week": index + 1, "start": start.isoformat(), "end": (stop - dt.timedelta(days=1)).isoformat(), | |
| "currency": currency, "expectedDeposits": deposit if deposits_ready else None, | |
| "vendorBills": values["vendorBills"] if bills_ready else None, | |
| "purchaseOrders": values["purchaseOrders"] if pos_ready else None, "runningBalance": balance} | |
| schedule.append(row) | |
| if balance is not None and balance < 0 and short is None: | |
| short = {"week": index + 1, "start": start.isoformat(), "currency": currency, "shortfall": -balance} | |
| if short: | |
| first.append(short) | |
| warnings = [] | |
| if bank_date and any(dt.date.fromisoformat(d["date"]) > bank_date for d in deposits): | |
| warnings.append(f"Bank data is through {bank_date.isoformat()}, before the expected deposits.") | |
| out = {"asOf": today.isoformat(), "weekCount": weeks, | |
| "weeks": [{"week": n + 1, "start": (today + dt.timedelta(weeks=n)).isoformat(), | |
| "currencies": [r for r in schedule if r["week"] == n + 1]} for n in range(weeks)], | |
| "currencies": currencies, "firstShortWeek": min(first, key=lambda r: r["week"]) if first else None, | |
| "firstShortWeeks": first, "assumptions": assumptions, "depositBasis": inputs, | |
| "deposits": deposits, "sourceAggregates": aggregates, | |
| "evidence": {"items": evidence, "total": total, | |
| "cursor": str(offset + limit) if offset + limit < total else None}, | |
| "bankDate": bank_date.isoformat() if bank_date else None, | |
| "warnings": warnings, "unavailable": sorted(set(unavailable)), "balanceAvailable": not unavailable} | |
| if _validate: | |
| out["validation"] = validate(tenant_key, weeks, st=st, con=con, now=moment, | |
| source_access=source_access, result=out) | |
| if not out["validation"]["ok"]: | |
| out["balanceAvailable"] = False | |
| out["firstShortWeek"], out["firstShortWeeks"] = None, [] | |
| for week in out["weeks"]: | |
| for row in week["currencies"]: | |
| row["runningBalance"] = None | |
| return out | |
| def validate(tenant_key, weeks=13, *, st=None, con=None, now=None, source_access=None, result=None): | |
| """Reconcile the forecast against independent settlement and Odoo mirror sums.""" | |
| result = result or forecast(tenant_key, weeks, st=st, con=con, now=now, | |
| source_access=source_access, _validate=False) | |
| checks = [] | |
| today = dt.date.fromisoformat(result["asOf"]) | |
| if result["unavailable"]: | |
| return {"ok": False, "state": "unavailable", "basis": "Independent mirror sums", "checks": [], | |
| "unavailable": result["unavailable"]} | |
| with _odoo_source(tenant_key, st, con) as cur: | |
| reference, missing = _events_sql(cur, result["assumptions"], source_access, independent=True) | |
| expected = _aggregate(cur, reference, today, weeks) | |
| checks.append({"source": "Odoo obligations", "ok": not missing and expected == result["sourceAggregates"]}) | |
| for week in result["weeks"]: | |
| for row in week["currencies"]: | |
| for kind in ("vendorBills", "purchaseOrders"): | |
| amount = pay._money(sum(r["amount"] or 0 for r in expected if r["kind"] == kind | |
| and r["slot"] == week["week"] - 1 and r["currency"] == row["currency"])) | |
| checks.append({"source": kind, "week": week["week"], "currency": row["currency"], | |
| "mirror": amount, "forecast": row[kind], "ok": amount == row[kind]}) | |
| with pay._source(tenant_key, st, con) as (cur, _source): | |
| stream_sql = _stream_sql(cur) | |
| for basis in result["depositBasis"]: | |
| ids = basis["settlementIds"] | |
| marks = ",".join("?" for _ in ids) | |
| cycle_sql = f"""WITH source AS (SELECT settlement_id, | |
| {_label_sql(['any_value(currency)'])} AS currency, | |
| any_value({stream_sql}) AS stream, | |
| CAST({pay._moment_sql('min(settlement_start_date)')} AS DATE) AS start, | |
| CAST({pay._moment_sql('max(settlement_end_date)')} AS DATE) AS finish | |
| FROM amzn_settlements GROUP BY settlement_id) | |
| SELECT median(date_diff('day', start, finish)) FILTER (WHERE finish > start), | |
| max(finish) FILTER (WHERE finish <= ?), | |
| max(finish) FILTER (WHERE CAST(settlement_id AS VARCHAR) IN ({marks})) | |
| FROM source WHERE currency = ? AND stream = ?""" | |
| cycle, last, close = cur.execute(cycle_sql, [today, *ids, basis["currency"], basis["stream"]]).fetchone() | |
| cycle = max(1, int(cycle)) if cycle else None | |
| close = close or (last + dt.timedelta(days=cycle) if last and cycle else None) | |
| expected_dates = [] | |
| day = close | |
| while day and cycle and day < today + dt.timedelta(weeks=weeks): | |
| expected_dates.append(day.isoformat()) | |
| day += dt.timedelta(days=cycle) | |
| checks.append({"source": "Settlement cycle", "currency": basis["currency"], | |
| "ok": cycle == basis["cycleDays"] and close is not None | |
| and close.isoformat() == basis["expectedClose"] | |
| and expected_dates == [d["date"] for d in result["deposits"] | |
| if d["currency"] == basis["currency"] and d["stream"] == basis["stream"]]}) | |
| running = cur.execute(f"SELECT COALESCE(sum(amount), 0) FROM amzn_settlements " | |
| f"WHERE CAST(settlement_id AS VARCHAR) IN ({marks})", ids).fetchone()[0] | |
| sales = cur.execute(f"""SELECT COALESCE(sum(s.amount), 0) FROM amzn_settlements s | |
| WHERE upper(trim(s.transaction_type)) = 'ORDER' AND upper(trim(s.amount_type)) IN ('ITEMPRICE', 'PROMOTION') | |
| AND {pay._POSTED.replace('posted_date', 's.posted_date')} >= ? | |
| AND {pay._POSTED.replace('posted_date', 's.posted_date')} < ? | |
| AND s.settlement_id IN (SELECT settlement_id FROM amzn_settlements | |
| WHERE upper(trim(currency)) = ? AND {stream_sql} = ?)""", | |
| [today - dt.timedelta(days=28), today, basis["currency"], basis["stream"]]).fetchone()[0] | |
| checks.append({"source": "Settlement mirror", "currency": basis["currency"], | |
| "runningTotal": pay._money(running), "sales28d": pay._money(sales), | |
| "ok": pay._money(running) == basis["runningTotal"] and pay._money(sales) == basis["sales28d"]}) | |
| for event in (d for d in result["deposits"] | |
| if d["currency"] == basis["currency"] and d["stream"] == basis["stream"]): | |
| amount = pay._money(running if event["basis"] == "open" else float(sales) * basis["cycleDays"] / 28) | |
| checks.append({"source": "Expected deposit", "currency": basis["currency"], | |
| "date": event["date"], "mirror": amount, "forecast": event["amount"], | |
| "ok": amount == event["amount"]}) | |
| firsts = [] | |
| for currency in result["currencies"]: | |
| balance = result["assumptions"]["openingCash"] | |
| first = None | |
| for week in result["weeks"]: | |
| row = next(r for r in week["currencies"] if r["currency"] == currency) | |
| deposits = pay._money(sum(d["amount"] for d in result["deposits"] if d["currency"] == currency | |
| and row["start"] <= d["date"] <= row["end"])) | |
| balance = pay._money(balance + deposits - row["vendorBills"] - row["purchaseOrders"]) | |
| checks.append({"source": "Running balance", "week": week["week"], "currency": currency, | |
| "ok": row["expectedDeposits"] == deposits and row["runningBalance"] == balance}) | |
| if balance < 0 and first is None: | |
| first = {"week": week["week"], "start": row["start"], "currency": currency, "shortfall": -balance} | |
| observed = next((r for r in result["firstShortWeeks"] if r["currency"] == currency), None) | |
| checks.append({"source": "First short week", "currency": currency, "ok": observed == first}) | |
| if first: | |
| firsts.append(first) | |
| checks.append({"source": "Earliest short week", "ok": result["firstShortWeek"] == ( | |
| min(firsts, key=lambda r: r["week"]) if firsts else None)}) | |
| return {"ok": bool(checks) and all(c["ok"] for c in checks), "basis": "Independent mirror sums", "checks": checks} | |