"""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 @contextlib.contextmanager 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}