# Module 7 — Head-Office Reports, Charts & Settings

> **KB version:** 2.0 · **Code baseline:** `b71c156` · **Last reviewed:** 2026-10-26
> **Epic:** EP-TBD · **Status:** Pending PO review
> **Pending PO review:**
> - Add-on lines count as their own item rows (own qty, own gross, own name) in the branch items report and in the per-order item list of the invoice export — never folded into the meal they were sold with.
> - The top-items chart counts add-on quantities alongside meal quantities, so an add-on can win "best-selling item".
> - Replacement extra charges are reported inside the customised meal's own gross (via the stored line price) and are not attributable to the replacement that caused them; ingredient removals never reduce the reported line value.
> **Related flags:** TBD (requires As-Built Spec cross-reference, out of scope this pass) · **Open decisions:** TBD (requires As-Built Spec OD register, out of scope this pass)

---

## Module Overview
- **Purpose:** Give head office period-based sales analytics, exports, dashboard KPIs/charts, and control of global business settings.
- **Business Goal:** Monitor multi-branch performance (sales, discounts, tax, payments, item mix), spot open/closed branches, export data for accounting.
- **Main Actors:** Admin, Superadmin.
- **Dependencies:** Orders (`related_date` from day close), Order payments, Branches, Items/Categories, Payment types, Dashboard settings.
- **Entry Points:**
  - `GET /v1/dashboard/reports/branches-items`, `/reports/payment-gateway-summary`, `/reports/ordersReport` (all accept `period`, `branches[]`, `startDate`, `endDate`, `export=1`)
  - `GET /v1/dashboard/charts/list`
  - `GET/POST /v1/dashboard/settings/*`
  - `GET /v1/dashboard/users/*` (staff management — rules in Module 1)

## Business Workflow
1. Admin picks a period (`day | week | month | 6month | year | custom` + dates) and optional branch filter.
2. Reports/charts query orders by **`related_date`** (the business-day key). For any non-custom period, **today's not-yet-closed orders are additionally included** (orders with `related_date NULL`, `day_closed 0`, created today) so dashboards are live before day close.
3. `export=1` turns a report into a CSV stored under `exports/{Y}/{m}/{type}/` and returns its public URL.
4. Charts endpoint returns KPI totals, two line charts (Total vs Net sales; Tax vs Discount) bucketed by hour/day/week/month depending on period, the open/closed status of each branch, and the top-selling branch.

## Business Rules
- **Excluded from all sales aggregates:** `cancelled` and `returned` orders (they still appear in the KPI counters as their own counts).
- **KPI totals:** total orders (all statuses in period), cancelled count, returned count, "delivered" count = completed count, gross = Σ(before_discount+vat), total sales = Σ(final), discount = Σ(before−after), net = Σ(final−vat), VAT total — computed only over non-cancelled/non-returned rows.
- **Orders report (daily rows):** per `related_date`: customers = orders count (again 1 guest/order), vat, gross (= before+vat), net (= final−vat), discount, final amount.
- **Payment gateway summary:** built from `order_payments` joins — split payments attribute the exact tendered amount per method; count = distinct orders per method. Export variant pivots per-day × payment-method amounts.
- **Branch items report:** per item per branch — qty = Σ qty, gross = Σ(qty × line price), first category name; only completed orders.
- **Add-ons are reported as items, not as modifiers.** The branch items report groups `order_items` rows by branch and item with **no filter on `order_items.type`**, so an add-on sold with a meal — its own `order_items` row with `type = addon` and `parent_order_item_id` pointing at the meal line — surfaces as its own report row, under its own item name, with its own qty and its own gross. Nothing in the report ties it back to the meal it accompanied, and a meal's reported qty/gross is unchanged by the add-ons sold with it. The per-order item list in the invoice export behaves the same way: every line of the order is listed flat, add-on lines included, with no meal/add-on distinction.
- **Top-items chart counts add-on quantities as items sold.** The best-selling-item query joins `order_items` to `items` and takes Σ `order_items.qty` grouped by item, again without filtering `order_items.type` — so an add-on competes for the top spot against meals, and a meal's rank is unaffected by how many add-ons were sold with it.
- **Replacement extra charges are reported inside the meal's own gross.** Both report and chart money figures read the line's stored `order_items.item_price`, which is written at order time as the item's price for the selected price mode **plus** the sum of the chosen replacements' `extra_charge` **plus** that line's payment-surcharge share. Consequences for head office: a customised meal's gross sales exceed its menu price × qty; two lines of the same item inside one reporting window can carry different unit values; and no report or chart attributes that uplift to the replacement that produced it. An ingredient **removal** never lowers the line price, so a stripped-down meal reports at full value.
- **Period definitions:** 🚩 **day = the business day, not the calendar day (SPRINT 1 · 09-08-2026, 2026-08-13)** — matches orders whose `related_date` is today, **plus the day that is still open**, whichever calendar date its orders were created on; week = current ISO week; month = current calendar month; 6month = last 6 months clamped to the current year's start if it crosses years; year = current calendar year; custom = `[startDate, endDate]` inclusive on `related_date`.
- 🚩 **SPRINT 1 · 09-08-2026 (2026-08-13):** The still-open business day is now identified **exactly as the POS closes one** (`closed_at IS NULL`, `day_closed = 0`, `related_date IS NULL`) and never by `created_at` — so a shift running past midnight is not half-counted, and the dashboard agrees with the DSR the branch prints. This open-day inclusion applies to **every** period, custom ranges included, but only for branches whose open day actually belongs to that window (decided per branch by the day's *opening* order) — a branch that opened on the 31st is not double-counted into the next month. **Previously `day` had no case of its own and silently fell through to `week` — "today" reported a whole week's numbers.**
- **Chart bucketing:** day → hourly buckets; week → daily; month → "Week n"-of-month; 6month/year → monthly; custom → daily (≤ 30 days) or monthly (> 30 days).
- **Open/closed branch:** a branch is "open" if its most recent order has `closed_at IS NULL` (shows opened-at = earliest open order time); "closed" otherwise (shows closed-at, business date and that day's order count).
- **Top branch:** the branch with the highest Σ(final_price) in the period (excludes cancelled/returned, excludes branch_id 0).
- 🚩 **SPRINT 1 · 09-08-2026 (2026-08-07) — Order History report:** a new order-by-order ledger sits behind the aggregate reports above — `GET /dashboard/reports/orders-history` returns order number, branch, timestamp, business date, order type, cashier, all payment methods, status, net, discount, sub total, additional fees, delivery fee, VAT, total and invoice link, one row per order. Same shared period presets as other reports (`day|week|month|6month|year|custom`, default `month`, resolved window echoed back as `filters`), filterable on `branches[]` and `status[]` (enum names **or** stored codes), paged (`page`/`per_page`, default 25, max 500), and `export=1` writes a CSV of the **whole** matching selection (not just the current page).
- 🚩 **SPRINT 1 · 09-08-2026 (2026-08-07) — Invoice ZIP export:** `POST /dashboard/reports/orders-history/invoices` zips the invoice PDFs of a picked set of orders, or of every order matching the current filters if none are picked — one folder per branch inside the zip, files named `<business date>_order_<number>.pdf`, non-ASCII branch names preserved. **Caps:** 10,000 invoices per download, up to 100 missing invoices regenerated per request, disk flushed every 1,000 files; anything still missing after that is reported back in `missing_invoices`.
- **Dashboard settings** (see Module list in Constants section): key/value store; keys derived from label (`lowercase, spaces→underscores`); types include text/image/editor/checkbox/time; file/image values stored on the public disk. Settings drive invoice branding, TRN display, order-number display, order-number start, and the (unimplemented) automatic close.

## Hidden Rules
- Anything **cancelled after a day is closed** stays cancelled in a closed day — reports recompute from statuses live, so historical report figures can drift if statuses are changed retroactively (no snapshotting except the DSR PDFs).
- "deliveredOrders"/"totalSales" KPI duplication: `totalSales` is set to the *delivered count* (copy-paste bug) — front-end must use `total_sales` instead.
- ~~`topItemSellings` in the charts response is actually a duplicate of `topBranchSelling` (the top-item query exists but returns nothing — dead code).~~ **Fixed 🚩 SPRINT 1 · 09-08-2026 (2026-08-10):** it's now a real query — the item with the highest total quantity sold in the period, excluding cancelled/returned orders, returned as `{item_name, total_quantity_sold}`. `totalSales`/`deliveredOrders` duplication (below) is **still open**.
- Settings update endpoint iterates *all* request keys — sending an unknown key aborts with "Invalid Setting key" after possibly updating earlier keys (partial update, no transaction rollback since commit/rollback calls are unbalanced).
- CSV exports are publicly accessible URLs on the public disk — anyone with the link (guessable timestamped path) can read revenue data.
- `ReportsRepository2` is an older unused copy (kept in repo).
- 🚩 **SPRINT 1 · 09-08-2026 — risk to flag:** the Order History invoice ZIPs land on the **public disk at guessable paths** and bundle **customer names, phones and addresses** for external orders — same public-disk exposure pattern as the CSV exports noted above, but with PII this time.
- Charts "day" period line chart groups today's live orders by creation hour; after day close the same orders re-bucket by `related_date` (which has no time) — intraday charts of closed days collapse.

## Decision Tree (period resolution — shared by reports & charts)
```
period := request.period or 'month'
IF period == 'custom' → WHERE related_date BETWEEN startDate AND endDate
ELSE → (WHERE period-window on related_date) OR (today's open orders: related_date NULL AND day_closed=0 AND created today)
IF branches[] given → AND branch_id IN branches
Aggregates exclude status IN (cancelled, returned) where noted
IF export == 1 → write CSV, return URL ELSE return JSON rows
```

## Database Understanding
- Reporting reads only denormalized order money columns + `order_payments`; there is no separate fact table — the orders table *is* the ledger. This is why day close stamps `related_date`: it converts an operational table into a reportable one.
- `daily_sales_reports` provides the immutable PDF audit trail; JSON reports are always recomputed live.

## State Machine
None beyond order states (Module 4) and day state (Module 6).

## APIs
| Endpoint | Purpose | Input | Output | Errors | Side effects |
|---|---|---|---|---|---|
| `GET /dashboard/reports/branches-items` | Item mix per branch | period/branches/export | rows or CSV URL | 500 | CSV file when export=1 |
| `GET /dashboard/reports/payment-gateway-summary` | Tender totals | period/branches/export | rows or CSV URL | 500 | CSV |
| `GET /dashboard/reports/ordersReport` | Daily sales table | period/branches/export | rows or CSV URL | 500 | CSV |
| `GET /dashboard/charts/list` | KPIs + line charts + branch status + top branch | period/branches | composite JSON | 500 | — |
| 🚩 `GET /dashboard/reports/orders-history` | Order-by-order ledger | period/branches/status/page/per_page/export | paged rows or CSV of full selection | 500 | CSV when export=1 |
| 🚩 `POST /dashboard/reports/orders-history/invoices` | ZIP invoice PDFs | order ids[] or current filters | ZIP download URL + `missing_invoices` | 500 | regenerates up to 100 missing PDFs; writes ZIP to public disk |
| `GET /dashboard/settings/list`, `view/{key}` | Read settings | — | settings | 500 | — |
| `POST /dashboard/settings/update` | Change settings | map of key→value | success | 404 unknown key (partial) | file uploads stored |
| `POST /dashboard/settings/create` (route `update` group; store) | New setting | label (unique), type, value | success | 500 | key derived from label |

## Edge Cases
- Custom period without dates → nulls → BETWEEN null → empty results (no validation).
- Branch filter values are raw request input straight into `whereIn` (array expected; scalar breaks).
- Reports on a day currently trading mix stamped and unstamped orders correctly *only* for non-custom periods (custom periods miss today's live orders).
- Division by zero avoided in DSR but **not** relevant here; `average` fields absent.
- An item that exists on the menu both as a standalone meal and as an add-on reports under one single name/row — grouping is by item, so standalone sales and add-on sales of that item are summed together and cannot be separated in the report output.
- Per-item gross in the branch items report is Σ(qty × stored line price), so it cannot be reconciled against the menu price list once any line on the order carried a replacement extra charge or a payment surcharge.

## Assumptions
- Head office trusts live recomputation over snapshots for JSON reports.
- Excel/CSV consumers accept the CSV formats produced by `fputcsv` defaults.

## TODOs
- Fix `totalSales` KPI duplication (still open). ~~`topItemSellings` duplication; finish `topItemSelling`.~~ **Done 🚩 SPRINT 1 · 09-08-2026.**
- Validate custom period dates; validate `branches` input.
- Protect export URLs (signed URLs or auth).
- Delete `ReportsRepository2`.

## Open Questions
- Which figures does accounting treat as canonical: DSR PDFs (frozen) or dashboard JSON (live)? They can disagree after retroactive status changes.
- Should head-office reports and charts split meals from add-ons using `order_items.type` (separate rows, separate rankings, or an add-ons-only view), or is counting add-ons as ordinary items the intended reading of item mix and best sellers? See M04 and M06, which raise the same question from the order and day-close sides.
- Should replacement revenue be reportable in its own right — per replacement ingredient, or at least as a separate uplift column — rather than buried in the customised meal's gross? See M04 and M06.

---