runloopable / platform /modules /payouts_forecast.py
fsanyoto's picture
Deploy AIOS web (React glide grid + FastAPI slice)
f462120 verified
Raw History Blame Contribute Delete
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
@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}