422 lines
18 KiB
Python
422 lines
18 KiB
Python
"""
|
||
Analytics surfaces layered on top of the stored results — read-only views that never
|
||
change the receivable:
|
||
|
||
* date-filtered AR ledger (daily / weekly / monthly / custom range)
|
||
* daily FX table per marketplace (local, rate used, USD)
|
||
* expanded reconciliation metrics with an explicit sign convention
|
||
* all-markets roll-up (per marketplace + combined total)
|
||
|
||
Everything is derived from the persisted `transactions` rows plus the same
|
||
opening/payout inputs the AR Ledger uses, so every tab ties back to the Overview.
|
||
"""
|
||
from __future__ import annotations
|
||
|
||
import datetime as dt
|
||
import json
|
||
from collections import defaultdict
|
||
|
||
from fastapi import APIRouter, Depends, HTTPException
|
||
from pydantic import BaseModel
|
||
from sqlalchemy.orm import Session as OrmSession
|
||
|
||
from ...core.i18n import currency_for_region, default_fx_for_region
|
||
from ...db import models
|
||
from ..deps import db_dep, get_session_or_404
|
||
from .ar import _market_list, _movement_for, _payouts_for
|
||
|
||
router = APIRouter(prefix="/api/sessions", tags=["analytics"])
|
||
|
||
TRANSFER = "Transfer"
|
||
|
||
|
||
# --------------------------------------------------------------------------- helpers
|
||
def _session_cutoff(s: models.Session) -> dt.date | None:
|
||
"""A payout counts as received on/before month_end - clearing_lag (same rule as the engine)."""
|
||
if not s.month_end_date:
|
||
return None
|
||
return s.month_end_date - dt.timedelta(days=s.clearing_lag_days or 0)
|
||
|
||
|
||
UNDATED_KEY = "0000-00-00"
|
||
|
||
|
||
def _bucket(d: dt.date | None, granularity: str) -> tuple[str, str]:
|
||
"""Return (sort_key, label) for a date at the requested granularity."""
|
||
if d is None:
|
||
# Rows with no usable transaction date (e.g. a manually appended total row).
|
||
# Kept in their own bucket so the running balance still reconciles.
|
||
return UNDATED_KEY, "Undated / no transaction date"
|
||
if granularity == "week":
|
||
monday = d - dt.timedelta(days=d.weekday())
|
||
end = monday + dt.timedelta(days=6)
|
||
return monday.isoformat(), f"{monday:%d %b} – {end:%d %b %Y}"
|
||
if granularity == "month":
|
||
first = d.replace(day=1)
|
||
return first.isoformat(), f"{first:%b %Y}"
|
||
return d.isoformat(), f"{d:%d %b %Y}"
|
||
|
||
|
||
def _parse_date(v: str | None, field: str) -> dt.date | None:
|
||
if not v:
|
||
return None
|
||
try:
|
||
return dt.date.fromisoformat(v)
|
||
except ValueError:
|
||
raise HTTPException(400, f"{field} must be YYYY-MM-DD.")
|
||
|
||
|
||
def _fx_for(db: OrmSession, session_id: int, marketplace: str) -> tuple[float, dict[dt.date, tuple[float, str]]]:
|
||
"""(month rate, {date: (rate, source)}) for a marketplace."""
|
||
row = db.query(models.FxRate).filter(
|
||
models.FxRate.session_id == session_id,
|
||
models.FxRate.marketplace == marketplace).first()
|
||
month_rate = row.rate if row else default_fx_for_region(marketplace)
|
||
daily = {r.rate_date: (r.rate, r.source or "manual")
|
||
for r in db.query(models.FxRateDaily).filter(
|
||
models.FxRateDaily.session_id == session_id,
|
||
models.FxRateDaily.marketplace == marketplace) if r.rate_date}
|
||
return month_rate, daily
|
||
|
||
|
||
def _daily_rows(db: OrmSession, session_id: int, marketplace: str,
|
||
frm: dt.date | None, to: dt.date | None) -> list[tuple[dt.date, float, float, int]]:
|
||
"""Per-day (date, revenue_total, payout_total, row_count) for one marketplace."""
|
||
q = db.query(
|
||
models.Transaction.posted_date,
|
||
models.Transaction.txn_type_en,
|
||
models.Transaction.total,
|
||
).filter(
|
||
models.Transaction.session_id == session_id,
|
||
models.Transaction.marketplace == marketplace,
|
||
)
|
||
if frm:
|
||
q = q.filter(models.Transaction.posted_date >= frm)
|
||
if to:
|
||
q = q.filter(models.Transaction.posted_date <= to)
|
||
if frm or to: # an explicit range excludes undated rows
|
||
q = q.filter(models.Transaction.posted_date.isnot(None))
|
||
|
||
per: dict[dt.date | None, list[float]] = defaultdict(lambda: [0.0, 0.0, 0])
|
||
for posted, type_en, total in q:
|
||
if posted is None:
|
||
d = None
|
||
else:
|
||
d = posted if isinstance(posted, dt.date) else dt.date.fromisoformat(str(posted))
|
||
slot = per[d]
|
||
if type_en == TRANSFER:
|
||
slot[1] += total or 0.0
|
||
else:
|
||
slot[0] += total or 0.0
|
||
slot[2] += 1
|
||
return [(d, v[0], v[1], int(v[2]))
|
||
for d, v in sorted(per.items(), key=lambda kv: (kv[0] is not None, kv[0]))]
|
||
|
||
|
||
# --------------------------------------------------------------------------- ledger
|
||
@router.get("/{session_id}/ledger-detail")
|
||
def ledger_detail(session_id: int, marketplace: str | None = None, granularity: str = "day",
|
||
date_from: str | None = None, date_to: str | None = None,
|
||
db: OrmSession = Depends(db_dep)) -> dict:
|
||
"""Date-filtered AR ledger. Running balance ends exactly at the tab's closing receivable."""
|
||
s = get_session_or_404(session_id, db)
|
||
if granularity not in ("day", "week", "month"):
|
||
raise HTTPException(400, "granularity must be day, week or month.")
|
||
mv = _movement_for(db, session_id, marketplace)
|
||
if not mv.get("available"):
|
||
return {"available": False}
|
||
mv.pop("journal", None)
|
||
mkt = mv["marketplace"]
|
||
cutoff = _session_cutoff(s)
|
||
|
||
frm, to = _parse_date(date_from, "date_from"), _parse_date(date_to, "date_to")
|
||
rows = _daily_rows(db, session_id, mkt, frm, to)
|
||
|
||
# Bucket, splitting payouts into received (hit the balance) and in-transit (memo only).
|
||
buckets: dict[str, dict] = {}
|
||
for d, revenue, payout, n in rows:
|
||
key, label = _bucket(d, granularity)
|
||
b = buckets.setdefault(key, {
|
||
"key": key, "label": label, "revenue": 0.0,
|
||
"payouts_received": 0.0, "payouts_in_transit": 0.0, "rows": 0,
|
||
})
|
||
b["revenue"] += revenue
|
||
if payout:
|
||
received = cutoff is not None and d is not None and d <= cutoff
|
||
b["payouts_received" if received else "payouts_in_transit"] += payout
|
||
b["rows"] += n
|
||
|
||
opening = mv["opening"]
|
||
running = opening
|
||
out = []
|
||
for key in sorted(buckets):
|
||
b = buckets[key]
|
||
running += b["revenue"] + b["payouts_received"]
|
||
out.append({
|
||
"key": b["key"], "label": b["label"],
|
||
"revenue": round(b["revenue"], 2),
|
||
"payouts_received": round(b["payouts_received"], 2),
|
||
"payouts_in_transit": round(b["payouts_in_transit"], 2),
|
||
"rows": b["rows"],
|
||
"balance": round(running, 2),
|
||
})
|
||
|
||
filtered = bool(frm or to)
|
||
return {
|
||
"available": True,
|
||
"marketplace": mkt,
|
||
"marketplaces": mv["marketplaces"],
|
||
"currency": mv["currency"],
|
||
"granularity": granularity,
|
||
"date_from": frm.isoformat() if frm else None,
|
||
"date_to": to.isoformat() if to else None,
|
||
"opening": opening,
|
||
"periods": out,
|
||
"closing": round(running, 2),
|
||
# Unfiltered, this equals the AR Ledger / Overview closing for the marketplace.
|
||
"session_closing": mv["closing"],
|
||
"filtered": filtered,
|
||
"in_transit_total": round(sum(p["payouts_in_transit"] for p in out), 2),
|
||
}
|
||
|
||
|
||
# --------------------------------------------------------------------------- daily FX
|
||
class DailyFxIn(BaseModel):
|
||
marketplace: str
|
||
rate_date: str
|
||
rate: float
|
||
source: str = "manual"
|
||
|
||
|
||
@router.get("/{session_id}/fx-daily")
|
||
def fx_daily(session_id: int, marketplace: str | None = None,
|
||
date_from: str | None = None, date_to: str | None = None,
|
||
db: OrmSession = Depends(db_dep)) -> dict:
|
||
"""Per-date local value, the USD rate applied, and the USD equivalent."""
|
||
get_session_or_404(session_id, db)
|
||
mv = _movement_for(db, session_id, marketplace)
|
||
if not mv.get("available"):
|
||
return {"available": False}
|
||
mv.pop("journal", None)
|
||
mkt = mv["marketplace"]
|
||
month_rate, daily = _fx_for(db, session_id, mkt)
|
||
frm, to = _parse_date(date_from, "date_from"), _parse_date(date_to, "date_to")
|
||
|
||
rows = []
|
||
tot_local = tot_usd = 0.0
|
||
for d, revenue, payout, n in _daily_rows(db, session_id, mkt, frm, to):
|
||
if d is None:
|
||
continue
|
||
local = revenue + payout
|
||
rate, source = daily.get(d, (month_rate, "month rate"))
|
||
usd = local * rate
|
||
tot_local += local
|
||
tot_usd += usd
|
||
rows.append({
|
||
"date": d.isoformat(), "local": round(local, 2), "rate": rate,
|
||
"usd": round(usd, 2), "source": source, "rows": n,
|
||
"revenue": round(revenue, 2), "payouts": round(payout, 2),
|
||
})
|
||
return {
|
||
"available": True,
|
||
"marketplace": mkt,
|
||
"marketplaces": mv["marketplaces"],
|
||
"currency": currency_for_region(mkt),
|
||
"month_rate": month_rate,
|
||
"rows": rows,
|
||
"total_local": round(tot_local, 2),
|
||
"total_usd": round(tot_usd, 2),
|
||
}
|
||
|
||
|
||
@router.put("/{session_id}/fx-daily")
|
||
def put_fx_daily(session_id: int, items: list[DailyFxIn],
|
||
db: OrmSession = Depends(db_dep)) -> dict:
|
||
get_session_or_404(session_id, db)
|
||
for it in items:
|
||
d = _parse_date(it.rate_date, "rate_date")
|
||
row = db.query(models.FxRateDaily).filter(
|
||
models.FxRateDaily.session_id == session_id,
|
||
models.FxRateDaily.marketplace == it.marketplace,
|
||
models.FxRateDaily.rate_date == d).first()
|
||
if row is None:
|
||
db.add(models.FxRateDaily(session_id=session_id, marketplace=it.marketplace,
|
||
rate_date=d, rate=it.rate, source=it.source))
|
||
else:
|
||
row.rate, row.source = it.rate, it.source
|
||
db.commit()
|
||
return fx_daily(session_id, items[0].marketplace if items else None, None, None, db)
|
||
|
||
|
||
# --------------------------------------------------------------- expanded reconciliation
|
||
# Sign convention shown in the UI so positives/negatives are never ambiguous.
|
||
SIGN_LEGEND = {
|
||
"positive": "Increases the receivable — money Amazon owes you (sales, credits, reimbursements).",
|
||
"negative": "Decreases the receivable — money deducted or already paid out "
|
||
"(refunds, fees, adjustments against you, settlements received).",
|
||
}
|
||
|
||
|
||
@router.get("/{session_id}/reconciliation-detail")
|
||
def reconciliation_detail(session_id: int, marketplace: str | None = None,
|
||
db: OrmSession = Depends(db_dep)) -> dict:
|
||
"""Line-by-line explanation of every component, with its sign and meaning."""
|
||
get_session_or_404(session_id, db)
|
||
mv = _movement_for(db, session_id, marketplace)
|
||
if not mv.get("available"):
|
||
return {"available": False}
|
||
journal = mv.pop("journal")
|
||
mkt = mv["marketplace"]
|
||
comps = {c["key"]: c for c in journal.get("components", [])}
|
||
|
||
def val(key: str) -> float:
|
||
return comps[key]["total"] if key in comps else 0.0
|
||
|
||
def line(key: str, label: str, amount: float, effect: str, note: str) -> dict:
|
||
return {"key": key, "label": label, "amount": round(amount, 2),
|
||
"effect": effect, "note": note}
|
||
|
||
revenue = [
|
||
line("product_sales", "Product / order sales", val("product_sales"), "increase",
|
||
"Gross value of orders Amazon collected on your behalf."),
|
||
line("shipping_credits", "Shipping credits", val("shipping_credits"), "increase",
|
||
"Delivery charges collected from buyers."),
|
||
line("gift_wrap_credits", "Gift-wrap credits", val("gift_wrap_credits"), "increase",
|
||
"Gift-wrap charges collected from buyers."),
|
||
line("other_sales_credits", "Other sales credits", val("other_sales_credits"), "increase",
|
||
"Regulatory and other credits billed to buyers."),
|
||
line("refunds", "Refunds", val("refunds"), "decrease",
|
||
"Order value returned to buyers — always negative."),
|
||
]
|
||
fees = [
|
||
line("selling_fees", "Amazon selling fees", val("selling_fees"), "decrease",
|
||
"Referral and closing fees withheld by Amazon."),
|
||
line("fba_fees", "FBA fulfilment fees", val("fba_fees"), "decrease",
|
||
"Pick, pack and delivery charges."),
|
||
line("storage_fees", "Storage fees", val("storage_fees"), "decrease",
|
||
"Monthly and long-term FBA inventory storage."),
|
||
line("promotional_rebates", "Promotional rebates", val("promotional_rebates"), "decrease",
|
||
"Discounts and coupons funded by you."),
|
||
line("advertising", "Advertising charges", val("advertising"), "decrease",
|
||
"Sponsored-ads charges billed through the settlement."),
|
||
line("other_transaction_fees", "Other transaction fees", val("other_transaction_fees"),
|
||
"decrease", "Per-item, chargeback and tax-collection fees."),
|
||
line("freight", "Outward freight / shipping", val("freight"), "decrease",
|
||
"Partnered-carrier and shipping-service charges."),
|
||
line("other_service_charges", "Other service charges", val("other_service_charges"),
|
||
"mixed", "Remaining Amazon service items not classified above."),
|
||
line("adjustments", "Adjustments", val("adjustments"), "mixed",
|
||
"Reimbursements (positive) and claw-backs (negative)."),
|
||
line("tax_net", "Tax (net of marketplace-withheld)", val("tax_net"), "mixed",
|
||
"Tax collected less tax Amazon remits — nets to ~0 under marketplace facilitator."),
|
||
]
|
||
memo = [
|
||
line("liquidations", "Liquidations", val("liquidations"), "mixed",
|
||
"Net value of liquidated inventory. Memo only — these amounts are already "
|
||
"included in the sales and fee lines above."),
|
||
]
|
||
settlements = [
|
||
line("payouts_received", "Settlements received", mv["received_payouts"], "decrease",
|
||
"Bank disbursements cleared by month-end — reduce the receivable."),
|
||
line("payouts_in_transit", "Settlements in transit", mv["in_transit_payouts"], "neutral",
|
||
"Initiated but not cleared by month-end — stay inside the receivable."),
|
||
]
|
||
|
||
gross = sum(l["amount"] for l in revenue if l["key"] != "refunds")
|
||
net_revenue = mv["net_revenue"]
|
||
variance = mv["difference_vs_settlement"]
|
||
|
||
return {
|
||
"available": True,
|
||
"marketplace": mkt,
|
||
"marketplaces": mv["marketplaces"],
|
||
"currency": mv["currency"],
|
||
"sign_legend": SIGN_LEGEND,
|
||
"groups": [
|
||
{"key": "revenue", "title": "Revenue", "lines": revenue, "subtotal": round(gross, 2),
|
||
"subtotal_label": "Gross revenue"},
|
||
{"key": "fees", "title": "Amazon fees & other charges", "lines": fees,
|
||
"subtotal": round(sum(l["amount"] for l in fees), 2),
|
||
"subtotal_label": "Total fees & charges"},
|
||
{"key": "memo", "title": "Memo — disclosure only (not added to the totals)",
|
||
"lines": memo, "subtotal": round(sum(l["amount"] for l in memo), 2),
|
||
"subtotal_label": "Liquidation activity"},
|
||
{"key": "settlements", "title": "Settlements", "lines": settlements,
|
||
"subtotal": round(mv["received_payouts"], 2),
|
||
"subtotal_label": "Settlements received"},
|
||
],
|
||
"net_revenue": net_revenue,
|
||
"opening": mv["opening"],
|
||
"closing": mv["closing"],
|
||
"settlement_closing": mv["settlement_closing"],
|
||
"variance": variance,
|
||
# The settlement-based closing is rounded to whole units.
|
||
"variance_status": ("matched" if variance is not None and abs(variance) < 1
|
||
else "review" if variance is not None else "pending"),
|
||
}
|
||
|
||
|
||
# --------------------------------------------------------------------------- all markets
|
||
@router.get("/{session_id}/all-markets")
|
||
def all_markets(session_id: int, db: OrmSession = Depends(db_dep)) -> dict:
|
||
"""Every marketplace side by side, plus the combined total (USD)."""
|
||
get_session_or_404(session_id, db)
|
||
j = db.query(models.JournalEntry).filter(
|
||
models.JournalEntry.session_id == session_id).first()
|
||
if not j or not j.data:
|
||
return {"available": False}
|
||
markets = _market_list(json.loads(j.data))
|
||
|
||
fx = {r.marketplace: (r.rate, r.currency)
|
||
for r in db.query(models.FxRate).filter(models.FxRate.session_id == session_id)}
|
||
settle = {r.marketplace: r for r in db.query(models.ReceivableResultRow).filter(
|
||
models.ReceivableResultRow.session_id == session_id,
|
||
models.ReceivableResultRow.account_type == "TOTAL")}
|
||
|
||
rows = []
|
||
tot = defaultdict(float)
|
||
for mkt in markets:
|
||
mv = _movement_for(db, session_id, mkt)
|
||
if not mv.get("available"):
|
||
continue
|
||
mv.pop("journal", None)
|
||
rate, currency = fx.get(mkt, (default_fx_for_region(mkt), currency_for_region(mkt)))
|
||
st = settle.get(mkt)
|
||
closing_local = mv["closing"]
|
||
row = {
|
||
"marketplace": mkt,
|
||
"currency": currency,
|
||
"fx_rate": rate,
|
||
"opening": mv["opening"],
|
||
"net_revenue": mv["net_revenue"],
|
||
"gross_revenue": mv["gross_revenue"],
|
||
"payouts_received": mv["received_payouts"],
|
||
"in_transit": mv["in_transit_payouts"],
|
||
"closing_local": closing_local,
|
||
"closing_usd": round(closing_local * rate, 2),
|
||
"settlement_closing": mv["settlement_closing"],
|
||
"settlement_closing_usd": round(st.receivable_usd, 2) if st else None,
|
||
"variance": mv["difference_vs_settlement"],
|
||
}
|
||
rows.append(row)
|
||
for k in ("opening", "net_revenue", "gross_revenue", "payouts_received", "in_transit"):
|
||
tot[k] += row[k] * rate
|
||
tot["closing_usd"] += row["closing_usd"]
|
||
tot["settlement_closing_usd"] += row["settlement_closing_usd"] or 0.0
|
||
|
||
return {
|
||
"available": True,
|
||
"markets": rows,
|
||
"total": {
|
||
"currency": "USD",
|
||
"opening": round(tot["opening"], 2),
|
||
"gross_revenue": round(tot["gross_revenue"], 2),
|
||
"net_revenue": round(tot["net_revenue"], 2),
|
||
"payouts_received": round(tot["payouts_received"], 2),
|
||
"in_transit": round(tot["in_transit"], 2),
|
||
"closing_usd": round(tot["closing_usd"], 2),
|
||
"settlement_closing_usd": round(tot["settlement_closing_usd"], 2),
|
||
"variance": round(tot["closing_usd"] - tot["settlement_closing_usd"], 2),
|
||
},
|
||
}
|