# Reporting and analytics — Phase 3D

## Architecture and entry points

`/reports/` is the operational dashboard. `/reports/service/`, `/reports/inventory/`,
`/reports/commercial/` and `/reports/management/` expose the corresponding read-only
reports. The interface uses Django Admin styling, ordinary HTML forms, KPI cards,
tables, pagination and links; no JavaScript or frontend framework is required.
`/reports/login/` accepts the existing Django authentication credentials. Authentication
does not itself grant reporting permission. These pages are not transactional Admin
edit screens and do not replace the frozen Admin/domain services.

Authoritative transactional tables remain the only source of operational truth.
Reporting uses lazy QuerySets, SQL grouping, distinct counts, correlated subqueries
and `Exists`. It introduces no reporting records, operational signals, cache, stored
totals, synthetic taxonomy values or business write paths. GET-only report views
reject POST. CSV uses the same query builders as the screen.

`ReportAccess` is an **unmanaged content-type anchor**, with no table or instances.
`reporting.0001_initial` records its model state so Django's standard post-migrate
permission creation supplies six custom permissions. There are no default model
CRUD permissions. This migration does not alter historical migrations or domain
tables. Fresh PostgreSQL test-database creation exercises the migration.

## Authorization and privacy

Capabilities are:

- `reporting.view_operational_dashboard`
- `reporting.view_service_analytics`
- `reporting.view_inventory_analytics`
- `reporting.view_commercial_analytics`
- `reporting.view_management_reports`
- `reporting.export_reports`

Grant them through existing Roles and active organizational assignments. Each report
uses the frozen `authorized_queryset` engine for its capability. Service data is
limited to authorized ServiceCenters. Inventory locations use the frozen inventory
location authorization: company-owned warehouses require company scope; center-owned
locations require that center's scope. Transfer documents require both endpoints,
as the frozen inventory document queries do. Ledger reporting exposes only authorized
location entries, rather than disclosing an unauthorized opposite endpoint.

Permission and scope cannot be borrowed from unrelated paths. Native Django direct
permissions, Groups and `is_staff` do not grant business reporting access. Active
superuser behavior is exactly the existing engine's behavior, including its requirement
that permission identifiers exist. Inactive user/path/role/hierarchy checks remain
database-fresh. Center+Department assignments retain their existing scope semantics.

Management permission is an explicit **cross-domain reporting capability** within
its authorized scope. It is not a combination of three unrelated domain permission
paths, nor permission to mutate any domain. Normal domain screens keep their existing
authorization unchanged.

CSV requires both the report capability and `export_reports` on each disclosed
scope. Queries intersect the two authorized scope sets. Export permission at a
different center/company cannot broaden a report. Unknown table names return 404;
malformed/repeated/unsupported query parameters return 400. Forged valid UUID filters
intersect authorized rows and yield no records rather than widening the query.

Reports exclude customer phone/email/address, recipient name/mobile/identity evidence,
credentials and free-text technical notes. Case/job IDs, part identifiers and engineer
IDs/names are limited to operationally useful rows. Handovers aggregate recipient
**types**, never recipient personal details. Responses use private/no-store caching.

## Filters and time

Common filters are date-from/to, Company, Region, ServiceCenter, Brand, **Product
Category**, Model, Variant, current ServiceCase status, engineer, intake channel,
case UUID, intake warranty snapshot and explicit commercial responsibility.
Analytical reports additionally accept relevant ComplaintSymptom, FaultDiagnosis,
RootCause, unknown-root-cause, repair-action taxonomy and event-outcome filters.
Only filters meaningful for the selected table are offered. Unsupported supplied
dimensions reject instead of silently being treated as classification or scope.
Identifiers are UUIDs available from existing authorized Admin records. Empty fields
mean no additional restriction; all authorization predicates still apply.

There is **no ServiceCase Department attribution**. Accordingly no Department filter
is fabricated from an engineer's current posting. Department-bearing authorization
paths still work through the frozen engine. Location-only inventory reports accept
only organizational and date filters, because unallocated warehouse stock has no
serviced Device/Product/engineer relationship. Parts usage supports actual serviced
Product dimensions. Reservation reporting does not infer a historical engineer.

Date filters are complete dates in the active Django timezone (project default
Asia/Dhaka), implemented as aware `[local start, local day after end)` boundaries.
Reversed ranges, invalid dates and an end date without a representable next day reject.
No naive `23:59:59` boundary is used. Omitted bounds are unbounded; omitted dates do
not implicitly mean today. “Received today” is independently the current local date.

Current status filters mean **current** status, even on historical event tables.
Product UUID filters use current protected Device→ProductModel→Brand/ProductCategory
and optional Device→ProductVariant relationships. Where no label snapshots exist,
current master labels are intentionally used without filtering out inactive masters.
Historical records are not erased when a master becomes inactive.

Engineer filters on current queues mean current active assignment. Diagnosis, repair,
QC and usage event reports use the recorded event's engineer assignment (QC uses the
linked repair engineer, not an inferred inspector-performance score). Case-based
commercial and intake filters use current assignment; they do not claim historical
engineer attribution. Recovery uses its linked repair execution assignment.

Warranty filter reads the **intake warranty snapshot** only. Responsibility filter
means the case has that explicit payer in an active line of its finalized invoice or
current quotation. It selects cases; it does not prorate an entire invoice metric to
only those lines. Payer splits themselves always use authoritative commercial amounts.
No quotation/invoice means no inferred financial classification. Warranty evidence
does not imply WARRANTY responsibility or free service.

## Current service dashboard and drill-downs

| Metric/table | Definition and timestamp |
| --- | --- |
| Received today | Scoped cases with received_at in today's complete local date |
| Received in period / cases / received trend | Scoped cases selected by received_at; daily trend uses local received date |
| Current open / open cases | Current cases excluding CLOSED and CANCELLED; includes DELIVERED awaiting closure; date bounds do not restrict |
| Workflow | Count by actual frozen status; absent status groups represent zero, not a new status |
| Closed in period / closure trend | Cases with closure evidence, selected by closure.closed_at |
| Cancelled in period | CANCELLED cases selected by cancelled_at |
| Ready for delivery | Current READY_FOR_DELIVERY count |
| Currently delivered | Current DELIVERED count, excluding already CLOSED |
| Delivered in period | Cases with handover evidence selected by delivered_at, including subsequently closed cases |
| Open aging | Elapsed time received_at→now for nonterminal current cases |
| Awaiting-delivery aging | Current READY_FOR_DELIVERY, elapsed delivery_release.readied_at→now |
| Recipient types | Count handovers by recorded type, selected by delivered_at |
| Accessory discrepancies | Count handover accessory rows with returned_quantity < intake quantity, selected by handover.delivered_at; not total missing units |

Aging uses elapsed 24-hour intervals `[0,2)`, `[2,4)`, `[4,8)`, `[8,15)`, `[15,31)`,
`[31,∞)` days, labeled 0–1, 2–3, 4–7, 8–14, 15–30, 31+. Exact boundary instants
belong to the next bucket. Future timestamps are excluded. No SLA target exists;
these are age buckets, **not SLA breaches**. Configurable SLA policy is future work.

The current engineer workload groups current non-ended assignments for nonterminal
cases by engineer and center. It counts all assigned cases, DIAGNOSING, DIAGNOSED
awaiting repair and REPAIRING. Its queue contains case/job, engineer, center, model
and status with stable ordering. Completed-engineer reporting selects completed_at,
counts completed executions, attempts and distinct cases after an earlier failed QC, and averages
completed_at−started_at. These are operational facts, not employee scores.

The table selector provides scoped underlying cases, engineer queues, complaints,
findings, completed repair executions, QC attempts, parts dispositions, quotation
revisions, invoices, payments and outstanding invoices. Important group rows link
to corresponding filtered records where an exact drill-down is available. Complaint
NULL-variant groups deliberately have no broader all-variant drill-down link.
Section and table navigation retain compatible filters and explicitly label any
unsupported filters that a link clears. Pagination/export links preserve
the selected table and its filters. Every paginated query has deterministic grouped
keys or timestamp/UUID tie-breakers; pages contain at most 50 rows.

## Turnaround

Each metric reports count, average, median and P90 of **completed nonnegative
durations**, selected by its ending timestamp. Empty populations return no duration,
not zero. PostgreSQL `PERCENTILE_CONT` is used through a fixed ORM Aggregate template;
only the constants 0.5 and 0.9 are permitted. No user SQL is interpolated.

| Transition | Authoritative link |
| --- | --- |
| Received→diagnosis completed | Each completed assessment, linked case.received_at→assessment.completed_at |
| Diagnosis→successful repair | Each COMPLETED/REPAIRED execution, its referenced assessment.completed_at→execution.completed_at |
| Repair→QC pass | Each completed PASSED QC, its referenced execution.completed_at→QC.completed_at |
| QC pass→delivery | Handover's delivery release's referenced QC.completed_at→handover.delivered_at |
| Received→delivery | Case.received_at→handover.delivered_at |
| Received→closure | Case.received_at→closure.closed_at |
| Quotation submission→approval | Every retained approved decision, quotation.submitted_at→decision.created_at, including later-superseded approved history |

Repeated completed assessments/repair/QC transitions count as separate evidence
events. Delivery and closure are one-to-one per case. Incomplete transitions never
receive an invented ending timestamp.

## Complaint and diagnosis analytics

**The taxonomy has no Complaint→ServiceCategory relationship. ComplaintSymptom is
the authoritative complaint dimension. Product Category is a device/product dimension.
“Complaint category” is intentionally not reported as a separate classification.**
Product-category applicability mappings are selection rules, not complaint
classification; these queries never join them or ServiceCategory. No Unknown Complaint
Category or synthetic mapping is introduced, and SERVICE_TAXONOMY.md is unchanged.

Intake frequency counts retained, nonremoved ServiceCaseComplaint records in the
case's received-date cohort. Breakdown dimensions are ComplaintSymptom, actual serviced
Product Category, Brand, Model, optional Variant and ServiceCenter; daily trend uses
received_at. Intake complaints are not confirmed diagnoses. Removal is reflected in
this current retained-evidence cohort, not reconstructed as an arbitrary past snapshot.

Diagnosis frequency counts retained, nonremoved findings on completed assessments,
selected by assessment.completed_at. It groups by FaultDiagnosis, RootCause, model,
center and local completion date. NULL RootCause is rendered **Unknown / Unconfirmed**
without creating a master. Diagnosis/complaint breakdown is same-case co-occurrence
with retained intake complaints: distinct findings per group, not claimed causality.
An assessment can contain several findings and a case several complaints, so those
co-occurrence groups overlap.

The ComplaintSymptom filter restricts both the case cohort and the displayed
complaint grouping in diagnosis/complaint co-occurrence. Other complaints retained
on the selected cases do not appear as additional groups when a specific complaint
is selected. This reporting-only correction is covered by the Phase 3D audit.

## Repair Action / Diagnosis and Root Cause Co-occurrence

These are **assessment-level co-occurrence analytics, not causal attribution**.
The authoritative boundary is ServiceRepairExecution→its referenced diagnostic
assessment. There is no individual action→finding relationship and none is inferred,
persisted, backfilled or added to the schema.

Only ServiceRepairAction rows with performed_at are performed actions. Planned rows
are excluded. Retained performed evidence still counts if subsequently removed from
the active plan; that removal does not erase performance. The period is performed_at.

For each repair-action taxonomy and diagnosis, cause, or diagnosis+cause grouping,
count **distinct ServiceRepairAction IDs** linked to assessments containing that
grouping. Several findings equivalent at the selected analytical granularity count
the action once. Optional further dimensions use the actual case's Brand, Model,
Variant, Product Category and ServiceCenter. NULL cause has its explicit Unknown /
Unconfirmed bucket. Analytical finding filters narrow groups, not causal attribution.

One action may occur in several groups. **Do not add grouped counts to obtain unique
performed actions.** A separate distinct performed-action KPI gives the overall count.
The Performed repair actions table groups distinct
action IDs by their own single taxonomy identity and supplies the nonoverlapping
overall count. Diagnostic group filters do not redefine that independent action total.

## Repair and QC outcomes

Repair-attempt tables use started_at and show the attempt's current OPEN/COMPLETED/
ABANDONED status and actual REPAIRED/NOT_REPAIRED outcome. A repeat attempt has an
earlier same-case execution ordered by started_at then UUID. An “after failed QC”
attempt has a same-case FAILED QC completed strictly before its start. This is
temporal rework evidence, not an invented direct repair-to-failure relationship.
Completed-repair tables instead use completed_at.

- Repair success = successful completed REPAIRED executions / all completed
  REPAIRED or NOT_REPAIRED executions in the completion period. OPEN/ABANDONED excluded.
- First-pass QC yield = PASSED first completed QC attempts / all first completed
  QC attempts in the completion period. First is determined per case across all
  history by completed_at then UUID; abandoned attempts are not completed attempts.
- Rework attempt rate = attempts started after earlier failed QC / all repair
  attempts started in period. This is attempt-based, not a unique-case or employee score.

Zero denominators produce no percentage. Ratio cards use organizational/product/
engineer filters, not complaint/finding/action grouping filters or selected outcome
filters; otherwise a selected success outcome would manufacture a 100% denominator.

QC attempt tables use started_at for attempts and completed_at for completed outcomes,
failed checklist items, unresolved complaint checks and failure trends. Checklist
failure counts use stored code/name snapshots and result FAIL; complaint-resolution
failures use NOT_RESOLVED (NOT_TESTABLE is not silently a failure). Failure breakdowns
by model, center and local completion date count distinct QC attempts. Failure/action
breakdown uses performed actions in the QC's explicitly referenced repair execution,
counts distinct failed QC attempts per action taxonomy, and is co-occurrence, not
causality or negligence. Those action groups may overlap.

## Inventory definitions

| Metric/table | Source and formula |
| --- | --- |
| On hand | Sum of StockLedgerEntry.quantity_delta for authorized location/part |
| Reserved | Sum of ACTIVE StockReservation.quantity for that position |
| Available | On hand−reserved only for active WAREHOUSE/STORE, active hierarchy and active part/category; otherwise zero, matching frozen available_stock |
| In transfer / defective / quarantine / custody | Current on-hand grouped by actual location_type, with TRANSIT/DEFECTIVE/QUARANTINE/CUSTODY kept distinct |
| Serialized units | Count units at that location/part in IN_STOCK/IN_TRANSIT/IN_CUSTODY |
| Nonserialized quantity | Ledger on hand−serialized units, including the nonserialized portion of OPTIONAL_SERIAL stock |
| Lowest available | Recorded positions ordered by available then location/part/UUID; no invented threshold |
| Zero stock | Recorded position anchors whose on hand=0; not a cross-product of every global part and every location |
| Consumption / unused returns | Sum PartsDisposition.quantity by CONSUMED / RETURNED, selected by recorded_at |
| Net consumption / velocity | CONSUMED quantity in period; unused returns do not reverse consumption |
| Posted movements | Authorized location ledger entries grouped by movement.kind/part/location; signed delta sum and entry count, selected by movement.posted_at |
| Transfers | Dispatched StockTransferLine.quantity, both endpoints authorized, selected by dispatch movement.posted_at; receipt does not count as a second dispatch |
| Reservations | Actual StockReservation quantities by current status, selected by reserved_at; draft request documents excluded |
| Defective recovery | DefectiveRecovery.quantity at authorized location and case, selected by recovered_at; removed customer components are separate from replacement stock |
| Count variance | RECONCILED StockCount.counted_quantity−expected_quantity, selected by finished_at; draft/counting/cancelled counts excluded |

Parts usage groups by part and actual serviced Model/Variant/Brand/Product Category,
center, recorded engineer assignment, case/job and recorded repair action. Record
groupings include authoritative IDs: repeated job numbers at different centers,
location codes in different companies and identical action labels do not merge
distinct records. Inventory position rows display company as well as location. Daily
trend uses disposition recorded_at. The frozen domain has no consumed-stock reversal;
an unused RETURNED disposition is a different issue resolution. Subtracting it from
CONSUMED would understate actual consumption. Reports never reconstruct physical
inventory from invoice or quotation lines.

Only real ledger movements count as posted movement history. Draft receipts/transfers
do not appear as posted stock. RECEIPT, MOVE, CONSUME, ADJUST_IN and ADJUST_OUT retain
their actual kinds; defective/quarantine location entries remain identifiable.
Internal MOVE produces two endpoint ledger entries, so absolute endpoint amounts
must not be summed as unique units moved. There is no authoritative cost valuation,
reorder policy or FAST/SLOW threshold; none is invented.

## Commercial definitions

Every monetary group includes currency. There is no implicit currency conversion or
cross-currency total. All invoice metrics require FINALIZED, never draft invoices.

Quotation-created/replacement counts use created_at (replacement means revision>1).
Submitted counts use non-NULL submitted_at. Approved/rejected event counts use retained
QuotationDecision.created_at and its outcome, including decisions on later-superseded
revisions. Business-value totals, by contrast, include **only current APPROVED** revisions
selected by their approval date. Active approved lines split CUSTOMER/WARRANTY/COMPANY
value; superseded versions are not added to current approved business value.

Invoice value is labeled **Invoiced Service Value**, never accounting revenue. Selected
by finalized_at: count, Sum(grand_total), Sum(customer_pay_total), Sum(warranty_covered_total),
Sum(company_covered_total), Avg(grand_total), and local daily totals. Center/model
breakdowns use immutable invoice context snapshots (model inside the quotation
snapshot), not renamed current labels. Identical historical model display labels
share a snapshot-label group; protected relationship UUIDs remain the filter identities.
Center groups also include the authoritative ServiceCenter ID. Receipt center groups
use that ID and the stored center code snapshot, so repeated codes cannot merge centers.

Payment reporting is a **received-date cohort**:

- Gross received = sum amount for payments received in period, including later VOIDED.
- Reversed from cohort = sum retained reversal amounts for those payments, including
  reversals recorded after the selected period.
- Net valid collections = sum amount for those payments still POSTED = gross−cohort
  reversals under the frozen full-reversal model.
- Valid allocations = sum allocation.amount for those POSTED payments only.
- Receipt count = retained receipts issued for payments in the cohort, including
  receipts whose payment was later reversed.

These definitions apply to center/method/local received-date trends. Separate
reversal-date activity selects reversed_at and may relate to older payments. It is
not subtracted a second time from received-cohort net. This is a current validity view
of a received cohort, not an immutable cash-book or historical accounting statement.

Current settlement uses the frozen SQL settlement projection over scoped finalized
invoices: valid paid=sum allocations whose payment is POSTED; outstanding=customer
liability−valid paid. Settlement states are NO_CUSTOMER_DUE, UNPAID, PARTIALLY_PAID and
PAID. Outstanding counts include positive balances only. Aging uses elapsed time
since invoice.finalized_at with the same explicit age buckets as service aging.
Date bounds intentionally do not discard older current debt.

Financial clearance is separate: NO_CUSTOMER_DUE for zero liability, PAID for zero
balance, DUE_RELEASE where retained authorized release limit covers current balance,
otherwise UNCLEARED. This mirrors the frozen settlement/release rule. Due-release
**does not reduce debt**. Delivered-with-outstanding counts require handover evidence,
positive balance and due-release clearance. VOIDED payments do not count as settlement.
Invoice-free cases have no invented financial-clearance classification.

## Management, CSV and performance

Management reuses the same scoped query definitions for workflow, received/closure
trends, turnaround, rework, QC outcomes, consumption, zero-stock positions, recovery,
current approved quotation value, invoiced value, net collections and outstanding.
There is no arbitrary combined performance score.

CSV exports the selected table's entire filtered scope with a UTF-8 BOM, conventional
CSV quoting and 1,000-row iterator chunks. Formula-sensitive prefixes =, +, -, @,
including whitespace/control-prefixed forms, are prefixed with an apostrophe. A CSV
does not bypass scope because it is an export. Numeric negative values are also escaped
conservatively. No whole operational table is materialized for a page or export.

Representative page budgets (including authentication/session/scope checks) are 24
queries for operational dashboard/engineer queue, 10 for complaint/diagnosis, 8 for
inventory, and 10 for invoice/outstanding pages. Query-count tests compare small and
larger displayed populations; record rendering uses projected values, not per-row
related-object access. Inventory position projections execute in one query. Individual
report tables are lazy; constructing the table selector does not evaluate all reports.
The dashboard deliberately uses several bounded aggregate queries rather than a
large multiplicative join. Measured requests in the final focused run: cases 17,
engineer queue 17, complaint frequency 8, diagnosis frequency 9, inventory positions 5,
invoices 6, outstanding 6. Empty complaint pages can save the row-fetch query.

Normal PostgreSQL committed reads are used. A multi-query dashboard is near-current,
not a globally atomic snapshot. No operational write locks or reporting caches are
added. There is no BI warehouse, materialized cube, arbitrary historical as-of stock
reconstruction, accounting/general ledger, revenue recognition, forecasting, employee
scoring or SLA/reorder policy. Persisted projections should be proposed only after
measurement demonstrates a need.

## Verification and file inventory

Frozen baseline: master `58c9768`, 1,986 tests. Baseline tests, historical migrations
and all frozen domain files remain unchanged. New tests cover real paid/closed,
warranty, mixed-payer, partial-payment, due-release, QC rework and inventory histories;
read-only snapshots, CSV security, scope isolation, co-occurrence deduplication,
authoritative complaint dimensions, date/age boundaries, stable pagination and query
growth. The reporting app contributes 62 new tests, including direct-login routing,
filter-preserving navigation, explicit co-occurrence labels and cross-organization
label-collision protection. The final focused run passed all 119 reporting and existing
authorization/security tests in 113.669 seconds. The complete post-change regression
passed **2,048 tests in 3,351.005 seconds** (55 minutes 51.005 seconds), with **0 failures
and 0 skipped tests**, in one `python manage.py test --noinput` run after the final
code/test change. This includes the unchanged 1,986-test baseline and all 62 new tests.
The full run created a fresh PostgreSQL test database, applied migrations and destroyed
that database on successful completion (exit code 0). Earlier interrupted runs are not
counted as verification.

Post-run `python manage.py check` reported no issues; `python manage.py makemigrations
--check` reported no changes. `python manage.py showmigrations` confirmed all migrations
applied, including `reporting.0001_initial` and unchanged `service.0006_handover_closure`.
The new permission migration creates no reporting table, as verified by the tests.
Final `git diff --check` passed. `git diff --stat` reported only the two tracked
configuration edits (3 insertions, 1 deletion); the 27 new files listed below are
untracked and therefore not included in that statistic. The working branch remains
master, there are no staged changes, and no staging, commit or push was performed.

Modified existing files: `config/settings/base.py` (app registration), `config/urls.py`
(report routes). New files:

- `apps/reporting/__init__.py`
- `apps/reporting/apps.py`
- `apps/reporting/admin.py`
- `apps/reporting/models.py`
- `apps/reporting/permissions.py`
- `apps/reporting/filters.py`
- `apps/reporting/scope.py`
- `apps/reporting/query.py`
- `apps/reporting/service_dashboard.py`
- `apps/reporting/service_analytics.py`
- `apps/reporting/inventory_analytics.py`
- `apps/reporting/commercial_analytics.py`
- `apps/reporting/management_reports.py`
- `apps/reporting/exports.py`
- `apps/reporting/views.py`
- `apps/reporting/urls.py`
- `apps/reporting/migrations/__init__.py`
- `apps/reporting/migrations/0001_initial.py`
- `apps/reporting/templates/reporting/report.html`
- `apps/reporting/templates/reporting/error.html`
- `apps/reporting/templates/reporting/login.html`
- `apps/reporting/tests/__init__.py`
- `apps/reporting/tests/test_reports.py`
- `apps/reporting/tests/test_cooccurrence.py`
- `apps/reporting/tests/test_boundaries.py`
- `apps/reporting/tests/test_scenarios.py`
- `docs/REPORTING_ANALYTICS.md`