72 lines
3.5 KiB
Markdown
72 lines
3.5 KiB
Markdown
# Amazon Accounts Receivable Aging Dashboard
|
||
|
||
Automates the Finance team's month-end **Amazon Accounts Receivable Aging** workbook:
|
||
upload the month's Amazon *Custom Unified Transaction* files → get a structurally- and
|
||
financially-identical Accounts Receivable Aging workbook, with validation, settlement
|
||
reconciliation, exceptions, drill-down, and an audit trail.
|
||
|
||
**Amazon only** (no Walmart/TikTok/Temu/Shein/eBay/Shopify). Reverse-engineered from the
|
||
Jan-2026 files; the USA receivable reproduces to the penny (**11,110,433 = `Detail!D11`**).
|
||
See [docs/accounts-receivable-logic.md](docs/accounts-receivable-logic.md).
|
||
|
||
## Layout
|
||
```
|
||
backend/
|
||
app/core/ # streaming parser + receivable engine (stdlib + openpyxl only)
|
||
app/api/ # FastAPI app (uploads, jobs, endpoints) — Phase 3
|
||
tests/ # unit + Jan-2026 reconciliation integration test
|
||
cli.py # process files from the command line
|
||
frontend/ # React + TS + Vite dashboard — Phase 4
|
||
docs/
|
||
```
|
||
|
||
## Run the app
|
||
```bash
|
||
# 1. Configure MySQL (hosted RDS) + data paths
|
||
cp example.env .env # then fill in MYSQL_* credentials
|
||
|
||
# Option A — Docker (backend + Vite hot reload)
|
||
docker compose up --build
|
||
# → http://localhost:5173 (API on :8000; uploads/exports on the ar_data volume)
|
||
|
||
# Option B — local processes
|
||
make install # backend deps + npm install
|
||
make backend # terminal 1 → FastAPI on :8000
|
||
make frontend # terminal 2 → dashboard on http://localhost:5173
|
||
```
|
||
Then open http://localhost:5173 → **New Closing** → pick the month → drag in the three Amazon
|
||
files → **Run processing** → review → **Download Full A/R Aging Excel**.
|
||
|
||
Uploads and exports are stored under `AR_DATA_DIR` (default `backend/data` locally, `/data` in Docker).
|
||
The app connects to MySQL using `MYSQL_*` variables from `.env`.
|
||
|
||
## Run the engine headless (CLI)
|
||
```bash
|
||
cd backend && pip install -r requirements.txt
|
||
python cli.py "/path/USA…01 to 10 January,2026.xlsx" \
|
||
"/path/USA…11 to 20 January,2026.xlsx" \
|
||
"/path/USA…21 to 31 January,2026.xlsx" \
|
||
--month-end 2026-01-31 --lag 2 --reserve-standard 125.44 --out AR_Aging_Jan26.xlsx
|
||
```
|
||
|
||
## Tests
|
||
```bash
|
||
make test # 16 fast tests (engine, export, API, robustness)
|
||
make test-all # + Jan-2026 reconciliation & sample-comparison (integration, ~4 min)
|
||
# point tests at the sample files if not in the repo root:
|
||
AR_SAMPLE_DIR="/path/to/samples" make test-all
|
||
```
|
||
|
||
## Status — all phases complete (incl. v2 multi-market)
|
||
- ✅ **Engine** — settlement classification + receivable; USA = **11,110,433** verified to the penny
|
||
- ✅ **Multi-marketplace** — all 13 markets (CA/UK/AU/IE + localized FR/DE/IT/ES/NL/PL/SV/TR)
|
||
reconcile **to the penny** vs the Jan-26 workbook; per-market currency & FX; settlement-owner
|
||
logic for cross-market EU chains; helper-row & pivot-sheet detection
|
||
- ✅ **AR roll-forward** — opening balance (auto carry-forward) + net revenue − payouts = closing;
|
||
AR Ledger · Finance Summary · Journal Entry (per-marketplace) · Reconciliation Control w/ sign-off
|
||
- ✅ **Header mapping** — localized alias tables + admin rules UI (`/api/mapping-rules`); unmapped
|
||
amounts are never silently excluded
|
||
- ✅ **Storage-fee detection** — canonical + description-based (potential/missing storage exceptions)
|
||
- ✅ **Excel** — Full audit workbook + Summary Finance pack
|
||
- ✅ **Tests** — ~60 fast + integration (USA reconciliation, sample comparison, 13-market benchmark)
|