133 lines
5.7 KiB
Python
133 lines
5.7 KiB
Python
"""
|
|
CLI: process the month's Amazon transaction files and print the receivable position.
|
|
|
|
Usage:
|
|
python cli.py <file1.xlsx> <file2.xlsx> ... --month-end 2026-01-31 [--lag 2]
|
|
|
|
Prints file metadata, settlement classification, per-account/marketplace receivable,
|
|
and the reconciliation. Exit code 0 on success.
|
|
"""
|
|
from __future__ import annotations
|
|
|
|
import argparse
|
|
import sys
|
|
import time
|
|
from datetime import date, datetime
|
|
|
|
from app.core.pipeline import process
|
|
|
|
|
|
def _parse_date(s: str) -> date:
|
|
return datetime.strptime(s, "%Y-%m-%d").date()
|
|
|
|
|
|
def main(argv: list[str]) -> int:
|
|
ap = argparse.ArgumentParser(description="Amazon A/R Aging — month-end processor")
|
|
ap.add_argument("files", nargs="+", help="Amazon transaction .xlsx files")
|
|
ap.add_argument("--month-end", required=True, type=_parse_date, help="YYYY-MM-DD")
|
|
ap.add_argument("--lag", type=int, default=2, help="clearing-lag days (default 2)")
|
|
ap.add_argument("--reserve-standard", type=float, default=0.0,
|
|
help="USA Standard Net Closing Balance (reserve)")
|
|
ap.add_argument("--reserve-invoiced", type=float, default=0.0,
|
|
help="USA Invoiced Net Closing Balance (reserve)")
|
|
ap.add_argument("--expected", type=float, default=None, help="expected total receivable USD")
|
|
ap.add_argument("--allowance", type=float, default=0.0, help="allowance for sales returns")
|
|
ap.add_argument("--out", default=None, help="write the A/R Aging workbook to this path")
|
|
args = ap.parse_args(argv)
|
|
|
|
reserves = {
|
|
("USA", "Standard Orders"): args.reserve_standard,
|
|
("USA", "Invoiced Orders"): args.reserve_invoiced,
|
|
}
|
|
|
|
def progress(stage: str, pct: float, rows_done: int = 0, rows_total: int = 0) -> None:
|
|
extra = f" ({rows_done:,}/{rows_total:,} rows)" if rows_total else ""
|
|
print(f" [{pct*100:5.1f}%] {stage}{extra}", file=sys.stderr, flush=True)
|
|
|
|
t0 = time.time()
|
|
result = process(
|
|
args.files, month_end=args.month_end, clearing_lag_days=args.lag,
|
|
reserves=reserves, expected_receivable=args.expected, progress=progress,
|
|
)
|
|
dt = time.time() - t0
|
|
|
|
print("\n" + "=" * 78)
|
|
print("FILES")
|
|
print("=" * 78)
|
|
for m in result.file_metas:
|
|
print(f" {m.filename}")
|
|
print(f" sheet={m.data_sheet!r} rows={m.imported_rows:,} "
|
|
f"dates {m.min_date}..{m.max_date} currency={m.currency} mkt={m.marketplace}")
|
|
if m.missing_required:
|
|
print(f" !! missing required columns: {m.missing_required}")
|
|
if m.unmapped_headers:
|
|
print(f" .. unmapped headers: {list(m.unmapped_headers.values())}")
|
|
|
|
agg = result.aggregation
|
|
cls = result.classification
|
|
print("\n" + "=" * 78)
|
|
print("SETTLEMENTS (per marketplace / account / id)")
|
|
print("=" * 78)
|
|
for key in sorted(agg.settlements, key=lambda k: (k[0], k[1], k[2])):
|
|
st = agg.settlements[key]
|
|
flag = "RECEIVABLE" if st.status == "receivable" else "paid"
|
|
print(f" {st.marketplace:<8} {st.account_type:<16} {st.settlement_id:<13} "
|
|
f"orders={st.order_total:16,.2f} transfers={st.transfer_total:16,.2f} "
|
|
f"rows={st.row_count:>8,} {st.first_date}..{st.last_date} [{flag}]")
|
|
|
|
print("\n Transfers (disbursements):")
|
|
for t in sorted(agg.transfers, key=lambda x: (x.marketplace, x.account_type, x.sid_int)):
|
|
rec = "received" if t.received else "IN-TRANSIT (not received)"
|
|
print(f" {t.marketplace:<8} {t.account_type:<16} sid={t.settlement_id:<13} "
|
|
f"{t.txn_date} amount={t.amount:16,.2f} -> {rec}")
|
|
|
|
print("\n" + "=" * 78)
|
|
print("RECEIVABLE")
|
|
print("=" * 78)
|
|
rec = result.receivable
|
|
for mkt, m in sorted(rec.marketplaces.items()):
|
|
print(f"\n {mkt} (currency={m.currency} fx={m.fx_rate})")
|
|
for acct, a in sorted(m.accounts.items()):
|
|
print(f" {acct:<16} additional_sales={a.additional_sales:16,.2f} "
|
|
f"reserve={a.reserve:12,.2f} -> local={a.receivable_local_unrounded:16,.2f}")
|
|
print(f" {'TOTAL':<16} additional_sales={m.additional_sales:16,.2f} "
|
|
f"reserve={m.reserve:12,.2f}")
|
|
print(f" >> RECEIVABLE (local, rounded) = {m.receivable_local:,} "
|
|
f"USD = {m.receivable_usd:,.2f}")
|
|
|
|
print(f"\n GRAND TOTAL RECEIVABLE (USD) = {rec.total_usd:,.2f}")
|
|
|
|
print("\n" + "=" * 78)
|
|
print("RECONCILIATION")
|
|
print("=" * 78)
|
|
r = result.reconciliation
|
|
print(f" uploaded_total = {r.uploaded_total:18,.2f}")
|
|
print(f" receivable_orders = {r.receivable_orders:18,.2f}")
|
|
print(f" paid_orders = {r.paid_orders:18,.2f}")
|
|
print(f" transfers_total = {r.transfers_total:18,.2f}")
|
|
print(f" reserve_total = {r.reserve_total:18,.2f}")
|
|
print(f" manual_adjustments = {r.manual_adjustments:18,.2f}")
|
|
print(f" final_receivable = {r.final_receivable_usd:18,.2f}")
|
|
print(f" identity_difference = {r.identity_difference:18,.2f}")
|
|
if r.expected_difference is not None:
|
|
print(f" vs expected = {r.expected_difference:18,.2f}")
|
|
print(f" STATUS: {r.status}")
|
|
for n in r.notes:
|
|
print(f" - {n}")
|
|
|
|
print(f"\n processed in {dt:.1f}s")
|
|
|
|
if args.out:
|
|
from app.core.excel_export import export_workbook
|
|
print(f"\n generating workbook -> {args.out}", file=sys.stderr, flush=True)
|
|
t1 = time.time()
|
|
export_workbook(result, args.files, args.out, reserves=reserves,
|
|
allowance_for_returns=args.allowance)
|
|
print(f" workbook written in {time.time()-t1:.1f}s "
|
|
f"({__import__('os').path.getsize(args.out)/1e6:.1f} MB)")
|
|
return 0
|
|
|
|
|
|
if __name__ == "__main__":
|
|
raise SystemExit(main(sys.argv[1:]))
|