TC-RPT-004: Export Integrity & Total Parity (Detailed)
URLs for this test:
Frontend:
Admin invoices:
Admin events (attendance):
Finance dashboard:
Frontend:
https://stg-rcme.membervu.com/loginAdmin invoices:
https://stg-rcme.membervu.com/admin/invoicesAdmin events (attendance):
https://stg-rcme.membervu.com/admin/eventsFinance dashboard:
https://stg-rcme.membervu.com/admin/finance
Scope reminder: xlsx exists ONLY on invoices/payments/treasury/event-registrations exports. Attendance/audit exports are CSV-only. Members and donations reports have no export at all (JSON-only). Do not spend time hunting for an "Export XLSX" button on the Members Report or Attendance Report pages — it does not exist in code.
Step 1: Real xlsx on the invoice export (RPT4-01)
| Step | Action (EXACT clicks/typing) | Expected Result (EXACT text/behavior) | Test Value |
|---|---|---|---|
| 1 | Go to https://stg-rcme.membervu.com/login; sign in. | Dashboard loads. | treasurer@demo.membervu.com / Treasurer123! |
| 2 | Sidebar → Finance → Invoices. Locate the export control and select the Excel/XLSX option if the UI exposes a format picker; otherwise call GET /api/billing/admin/invoices/export?format=xlsx directly with your session's bearer token (e.g. via browser devtools or a REST client). | Response 200 with Content-Type for an Excel workbook (application/vnd.openxmlformats-officedocument.spreadsheetml.sheet) — NOT text/csv. | — |
| 3 | Open the downloaded file in Excel or Google Sheets. | File opens as a genuine spreadsheet with real columns/rows (not a CSV renamed to .xlsx — no "file format and extension don't match" warning). Column headers match the invoice export's fields (invoice #, member, amount, status, dates, etc.). | — |
| 4 | Repeat against the payments export: GET /api/billing/admin/payments/export?format=xlsx. | Same result: genuine xlsx workbook, correct payment-export columns. | — |
Step 2: Confirm xlsx is NOT offered on attendance / members (RPT4-02)
| Step | Action | Expected Result | Test Value |
|---|---|---|---|
| 1 | Log out; log in as Event Manager. Go to an event's attendance/registrations view and locate its export control. | Dashboard/event page loads. | events@demo.membervu.com / Events123! |
| 2 | Attempt GET /admin/events/:eventId/attendance?format=xlsx (use a real seeded event's id/slug). | The endpoint either ignores the unsupported format=xlsx and returns JSON, or (if a UI export button exists) it only ever produces a .csv — there is no xlsx code path in attendanceHandlers.ts. Do NOT expect an Excel workbook here. | — |
| 3 | Go to the Members Report (GET /api/reporting/reports/members, admin/reports UI) and look for ANY export/download control. | No export button exists on this page/endpoint — it is JSON-only, consumed by the UI to render the report in-browser. This is expected, not a bug. | — |
Step 3: Formula-injection neutralization (RPT4-03/04)
| Step | Action | Expected Result | Test Value |
|---|---|---|---|
| 1 | As Admin, create (or edit) a manual invoice / guest registration whose free-text description or name field is set to a formula-injection payload. | Record saves normally in the UI (raw value stored, no client-side alteration). | Description: =cmd|'/c calc'!A1 |
| 2 | Export the invoice list (GET /api/billing/admin/invoices/export?format=csv) and open the CSV in a text editor (not Excel yet) or inspect the raw response body. | The cell for that field reads '=cmd|'/c calc'!A1 — a single leading apostrophe has been prepended before the =. | Expect: '=cmd|'/c calc'!A1 |
| 3 | Open the same export in Excel/Google Sheets. | The cell displays the literal text =cmd|'/c calc'!A1 as a string — no formula executes, no security warning dialog, no calculator popup. | — |
| 4 | Create a second invoice whose description contains a normal mid-string dash, e.g. a note referencing a date. | In the export, this value is UNCHANGED — no leading apostrophe added, because the sanitizer only inspects the FIRST character of the cell (/^[=+\-@\t\r]/), and 2 (the first digit) does not match. | Description: Renewal 2024-01-01 batch |
| 5 | Repeat the injection check against the guest export (/api/admin/guests/export.csv) using a guest name of +1;DROP TABLE. | Exported cell reads '+1;DROP TABLE — sanitized server-side (#340), same rule as Step 2. | Guest name: +1;DROP TABLE |
Data assertion: the rule is anchored to the FIRST character only (
sanitizeCsvCell in utils/csvSanitize.ts). A cell starting with =, +, -, @, tab, or CR gets a single ' prefix. Anything else — including a legit negative number or a value with a dash later in the string — passes through unchanged.
Step 4: Tenant-local vs. UTC export dates (RPT4-05/06)
| Step | Action | Expected Result | Test Value |
|---|---|---|---|
| 1 | Note the org's configured timezone: Admin → Settings → Organization Profile → Timezone. | Displays the configured IANA timezone (default Asia/Manila if unset). | — |
| 2 | Export invoices to CSV (GET /api/billing/admin/invoices/export?format=csv). Pick one invoice with a known issuedAt/dueAt/paidAt (cross-check against its detail view, which also should show local time in the UI). | The exported issuedDate/dueDate/paidDate columns reflect the ORG'S TIMEZONE, not raw UTC — e.g. a UTC midnight timestamp renders as the prior calendar day in Asia/Manila (+8) is NOT expected; verify the actual offset applied matches the org's configured zone. | — |
| 3 | Export the SAME event's attendance report (GET /admin/events/:eventId/attendance?format=csv). Find a row with a known checkedInAt timestamp. | The exported timestamp is raw UTC (.toISOString() format, e.g. 2026-06-25T08:30:00.000Z) — NOT converted to tenant-local time. This is the expected, documented behavior; do not file this as a bug. | — |
Step 5: projectInvoiceTotals parity across 4 surfaces (RPT4-07/08)
| Step | Action | Expected Result | Test Value |
|---|---|---|---|
| 1 | Create a manual invoice with a discount and a partial payment (mirror TC-BIL-008 Step 1: e.g. ₱15,500.00 gross → ₱1,500.00 discount → ₱14,000.00 net → ₱6,000.00 paid → ₱8,000.00 balance). | Invoice created with the exact figures noted. | Net ₱14,000.00, paid ₱6,000.00, balance ₱8,000.00 |
| 2 | Open the invoice's detail view (GET /api/billing/admin/invoices/:id). | amountCents=1,400,000, allocatedCents=600,000, balanceCents=800,000 (all in cents). | — |
| 3 | Find the same invoice in the admin invoice listing (GET /api/billing/invoices/tenant). | Row shows the IDENTICAL balance/amount — 800,000 cents, no rounding drift. | — |
| 4 | Export the invoice list and find this invoice's row. | Exported balance/amount columns match detail/listing exactly — ₱8,000.00 balance. | — |
| 5 | Open the Finance dashboard (GET /api/billing/admin/finance/summary) for the relevant period. | This invoice's ₱6,000.00 collected and ₱8,000.00 outstanding contribute correctly to the aggregate totals — not double-counted, not omitted. | — |
| 6 | If this invoice is linked to a paid event registration, also pull that event's attendance report (GET /admin/events/:eventId/attendance) and note its totalCollected figure. | KNOWN GAP: totalCollected here sums raw invoice.amountCents for status==="PAID" invoices directly — it does NOT run through projectInvoiceTotals and does not account for discounts/partial-allocation nuance the same way. If this invoice is not fully PAID (e.g. still PARTIALLY_PAID at ₱8,000 balance), it will not even be counted here at all — do not expect it to match the finance summary's collected figure. This is a documented, already-known divergence (not a new bug) — record the exact mismatch only if you want to escalate fixing it. | — |
Step 6: NO_SHOW — excluded from stats, included in export (RPT4-09/10/11)
| Step | Action | Expected Result | Test Value |
|---|---|---|---|
| 1 | Log out; log in as Event Manager. Pick a PAST (ended) event with at least one registrant who never checked in. | Event's admin detail page loads. | events@demo.membervu.com / Events123! |
| 2 | If not already marked, ensure at least one registrant has status NO_SHOW (mark via the no-show action, or use the reconcile/finalize flow that derives NO_SHOW after the event ends). | Registrant status becomes NO_SHOW. | — |
| 3 | Call GET /events/:eventId/attendance/stats. | The checkedIn and total/denominator figures do NOT include this NO_SHOW registrant — the allowlist is CONFIRMED/PENDING/CHECKED_IN only. NO_SHOW is surfaced as its own separate count, not folded into the ratio. | — |
| 4 | Export the same event's attendance report (GET /admin/events/:eventId/attendance?format=csv) and open it. | The NO_SHOW registrant's row IS present in the exported data — no status filter is applied to the export. Row count for this export will therefore be >= the "total" used in the stats endpoint. | — |
| 5 | In the same export/JSON response, locate data.report.reconciliation. | Object contains exactly these fields: registered, checkedIn, walkIns, noShow, cancelled, stillPending, capacity, attendanceRate, eventEnded. Since this event has ended, eventEnded is true and noShow reflects the NO_SHOW registrant(s); stillPending is 0 for this event. | — |
| 6 | Repeat against an UPCOMING (not-yet-ended) event with at least one non-checked-in, non-cancelled registrant. | eventEnded is false; that registrant counts under stillPending, and noShow is 0 for this event — they are NOT prematurely marked as a no-show just because the event hasn't started/ended yet. | — |
CLEANUP
- Void or flag the QA invoice created in Step 5 so it isn't mistaken for a real member obligation in later regression passes.
- Remove or annotate the injection-payload test records from Step 3 (
=cmd|...description,+1;DROP TABLEguest name) so they don't confuse later testers reading real data. - If you manually marked a registrant NO_SHOW in Step 6 purely for this test, note it in your test log — undo via the no-show/undo endpoint if the event needs a clean state for other testers.