# GB5 Report & Analytics Platform
## Technical Reference — All Stakeholders

> **Version:** May 2026  |  **Status:** Current  |  **Audience:** Product, Frontend, Backend, DBA, Architecture

---

## Table of Contents

1. [Platform Overview](#1-platform-overview)
2. [Architecture & Layers](#2-architecture--layers)
3. [Report Modes](#3-report-modes)
4. [Execution Flows](#4-execution-flows)
5. [Export Formats](#5-export-formats)
6. [Pivot Engine](#6-pivot-engine)
7. [Async Job System (My Reports)](#7-async-job-system-my-reports)
8. [Report Orchestration Platform](#8-report-orchestration-platform)
9. [API Endpoints Reference](#9-api-endpoints-reference)
10. [Database Schema](#10-database-schema)
11. [Configuration Reference](#11-configuration-reference)
12. [Security & Multi-Tenancy](#12-security--multi-tenancy)
13. [Performance Guide](#13-performance-guide)
14. [Developer Guide — Adding a Report](#14-developer-guide--adding-a-report)
15. [Key File Index](#15-key-file-index)
16. [Roadmap & Known Gaps](#16-roadmap--known-gaps)

---

## 1. Platform Overview

The GB5 Report & Analytics Platform is a **unified, multi-tenant reporting infrastructure** built on .NET 9 FastEndpoints that handles everything from a simple filtered grid to a cross-tabulated PDF report generated in the background and delivered via a user inbox.

### What it does

| Capability | Detail |
|---|---|
| **Live Grid Reports** | Stream flat data rows to the Angular grid; user sorts, filters, paginates in-browser |
| **Pivot Reports** | Transform flat rows into cross-tabulated grids server-side (BE DataPivot) or send raw data for client-side drag-drop pivot (FE-driven) |
| **Excel Export** | Cross-tab `.xlsx` for pivot views; flat column `.xlsx` for standard views; report header (logo, title, criteria) included |
| **PDF Export** | Handlebars + Puppeteer-rendered PDF with pivot-aware table layout, page numbers, header/footer |
| **CSV Export** | Flat reports only; UTF-8 comma-separated |
| **Async Batch** | Long-running reports submitted as background jobs; user notified via "My Reports" inbox when file is ready |
| **Multi-DB Routing** | Each report can be directed to OLTP, a dedicated Report DB, or an Archive DB, per configuration — automatically selected by date range rules |
| **Analytics** | Dynamic SQL generation via AnalysisDAL for ad-hoc queries |

### Key design principles

- **No SQL string concatenation.** All queries are parameterized Dapper statements.
- **DB-level multi-tenancy.** `LoginDTO.DatabaseName` routes every query to the correct tenant database — no row-level tenant filter on report tables.
- **Streaming IO.** Report data is consumed as `IAsyncEnumerable<Dictionary<string,object?>>` from the module endpoint over HTTP, then piped directly to the export engine. Large datasets (100k+ rows) are never fully loaded into memory at once.
- **Configurable per report.** Async threshold, allowed export formats, datasource preference, pivot mode, and timeout are all stored in `MREPORTCONFIG` — no code changes needed to tune a report.
- **Purely additive orchestration.** The new Report Orchestration Platform is layered on top of the existing `CommonReportBLL` paths. Every existing report continues to work exactly as before.

---

## 2. Architecture & Layers

```
┌─────────────────────────────────────────────────────────────────────┐
│  Angular Frontend                                                    │
│  Grid component · Export buttons · My Reports inbox · Pivot UI      │
└───────────────────────────────┬─────────────────────────────────────┘
                                │ HTTP (FastEndpoints)
┌───────────────────────────────▼─────────────────────────────────────┐
│  Service Layer  (FrameworkSL / ModuleSL)                            │
│  /CommonReport/Report                                                │
│  /CommonReport/GetMyReports                                          │
│  /CommonReport/GetPivotConfig                                        │
│  /CommonReport/ExecuteAsyncJob                                       │
└──────────┬─────────────────────────────────────────────────────────┘
           │
┌──────────▼─────────────────────────────────────────────────────────┐
│  Business Logic Layer  (FrameworkBLL / ModuleBLL)                   │
│  CommonReportBLL  ·  DataPivotEngine  ·  IQueryOrchestrator        │
│  PivotExcelExport · PivotPdfExport  ·  ExcelExport                 │
│  AsyncDecisionEngine · DataSourceRuleEvaluator                      │
└──────────┬──────────────────────┬──────────────────────────────────┘
           │                      │ HTTP (report data streaming)
┌──────────▼──────────┐  ┌────────▼────────────────────────────────┐
│  Framework DAL      │  │  Module Report Endpoint                  │
│  CommonReportDAL    │  │  e.g. GET /MM/GetMRNReport               │
│  SystemJobDAL       │  │  Returns IAsyncEnumerable<row>           │
│  ServerConfigCache  │  └─────────────────────────────────────────┘
└──────────┬──────────┘
           │ Dapper / IQueryExecutor
┌──────────▼─────────────────────────────────────────────────────────┐
│  Database  (SQL Server / PostgreSQL)                                 │
│  MREPORT · MREPORTVIEW · MREPORTVIEWFIELDS · MREPORTCONFIG         │
│  TSYSJOB · MSERVERCONFIG · MREPORTDATASOURCERULE                   │
└─────────────────────────────────────────────────────────────────────┘
```

### Layer responsibilities

| Layer | What it owns |
|---|---|
| **SL (Service Layer)** | HTTP endpoint declaration, request binding, `GetCacheKey()`, response dispatch |
| **BLL (Business Logic)** | Export orchestration, pivot transformation, async decision, connection resolution |
| **DAL (Data Access)** | Dapper queries for report metadata; never contains business logic |
| **GB5Shared** | DTOs, export engines (Excel, PDF, CSV, Pivot), enums, `IQueryExecutor` |
| **Module endpoints** | Own their SQL query; return flat `IAsyncEnumerable<Dictionary<string,object?>>` — no export or connection management |

---

## 3. Report Modes

Every report is identified by a `REPORTVIEWID`. The view's `REPORTVIEWTYPE` column determines the mode:

| `REPORTVIEWTYPE` | Mode | Data shape to FE |
|---|---|---|
| `0` | **Standard (flat)** | Paginated rows, column headers from `MREPORTVIEWFIELDS` |
| `1` | **Analysis** | Dynamic SQL result; field list varies per query |
| `2` | **Pivot** | Either BE-pivoted grid (dynamic columns) or raw rows for FE pivot |

### Pivot sub-modes (both under `REPORTVIEWTYPE = 2`)

The field `IsPivotView` in the request (`ReportCallingDTO.IsPivotView`) selects the sub-mode:

| `IsPivotView` | Mode | What the backend returns |
|---|---|---|
| `1` | **BE DataPivot** | Server runs `DataPivotEngine`; returns `{rowHeaders, pivotColumns, rows, grandTotal}` — FE displays as a grid with dynamic column headers |
| `0` | **FE-Driven** | Server returns `{fields, rows}` — all flat rows plus field-role config; Angular pivot component handles layout, drag-drop, charting |

---

## 4. Execution Flows

### 4.1 Synchronous flat report (Excel export)

```
POST /CommonReport/Report
  { ReportViewId: 55, IsExcelImport: 2, CriteriaDTO: { FromDate, ToDate, ... } }
  │
  ▼
CommonReportBLL.ExcelExport()
  │
  ├─ GetReportViewMeta(55) ──────────────────────────► MREPORTVIEW
  │    ReportViewType = 0 (flat)
  │
  ├─ GetReportViewField(55) ─────────────────────────► MREPORTVIEWFIELDS + MREPORTVSFIELDS
  │    → List<ReportViewFieldsDTO> (column order, widths, titles)
  │
  ├─ FillReportStandardDTO() ────────────────────────► MREPORT, MOU, MCompany, MBranch
  │    → Company logo, title, header context
  │
  ├─ IExcelExport.ReportURICalling() ────────────────► HTTP GET module endpoint
  │    → IAsyncEnumerable<Dictionary<string,object?>>  (streaming rows)
  │
  ├─ ExcelExport.Export(rowStream, headers, columns)
  │    → ClosedXML / OpenXml → MemoryStream (.xlsx)
  │
  └─ return byte[]
       ↓
     Response: Content-Disposition: attachment; filename="Report.xlsx"
```

### 4.2 Synchronous BE DataPivot (Excel export)

```
POST /CommonReport/Report
  { ReportViewId: 77, IsExcelImport: 2, IsPivotView: 1 }
  │
  ▼
CommonReportBLL.ExcelExport()
  │
  ├─ GetReportViewMeta(77) → ReportViewType = 2 (Pivot) ◄── PIVOT GUARD FIRES
  │
  ├─ GetPivotFieldConfig(77) ────────────────────────────► MREPORTVIEWFIELDS + MREPORTVSFIELDS
  │    → List<PivotFieldConfigDTO>
  │      (FieldName, DisplayType: Row/Column/Summary, AggregationType, ...)
  │
  ├─ IExcelExport.ReportURICalling() ────────────────────► HTTP streaming flat rows
  │
  ├─ DataPivotEngine.TransformAsync(rows, fieldConfig)
  │    Step 1: Partition fields → rowFields, colFields, sumFields
  │    Step 2: Project rows to only configured fields (memory optimisation)
  │    Step 3: Discover distinct column values
  │    Step 4: Group by row dimensions, aggregate per (colValue × sumField)
  │    Step 5: Compute grand total row
  │    → PivotResultDTO
  │
  ├─ PivotExcelExport.ExportAsync(pivotResult, reportStandard, headers)
  │    → ClosedXML: header rows + dynamic pivot columns + data + grand total
  │    → MemoryStream (.xlsx)
  │
  └─ return byte[]
```

### 4.3 JSON response (grid display or FE pivot)

```
POST /CommonReport/Report  { IsExcelImport: 1, IsPivotView: 0 or 1 }
  │
  ├─ IsPivotView == 1  (BE DataPivot for grid display)
  │    → TransformAsync → return JSON:
  │      { rowHeaders: [...], pivotColumns: [...], rows: [...], grandTotal: {...} }
  │
  └─ IsPivotView == 0  (FE-driven; FE gets all raw rows + config)
       → GetPivotFieldConfig + drain all flat rows → return JSON:
         { fields: [PivotFieldConfigDTO...], rows: [Dict...] }
         FE caches `rows` in-memory; user can reassign dimensions without re-fetching
```

### 4.4 Async batch flow (long-running report → My Reports inbox)

```
User requests report with large date range
  │
  POST /CommonReport/Report
  │
  ▼
IQueryOrchestrator.ExecuteReportAsync(context)
  ├─ Stage 1: Load MREPORTCONFIG (AsyncThresholdDays = 90)
  ├─ Stage 2: Validate format
  ├─ Stage 3: Resolve datasource (Auto rules → ArchiveDb for >365 days)
  ├─ Stage 4: Resolve DB connection
  ├─ Stage 5: AsyncDecisionEngine → DateRangeDays (245) > threshold (90) → IsAsync = true
  │
  └─ Stage 6b: SubmitAsyncJobAsync()
       Serialize context to JSON →
       INSERT TSYSJOB { PARAMETERS = JSON, RUNSTATUS = 0 (Pending), PRIORITY = 5 }
       return { SysJobId: 9999 }
         │
         ▼
       Frontend shows "Your report is being generated"
       Navigates to My Reports inbox
       Polls GET /CommonReport/GetMyReports → shows Pending job

                        [Background SysJob Worker]
                         │
                         ▼
                        Dequeues job 9999
                        POST /CommonReport/ExecuteAsyncJob?SysJobId=9999
                         │
                         ▼
                        Orchestrator.ExecuteAsyncJobAsync(9999, loginDTO, ct)
                         ├─ Load TSYSJOB row
                         ├─ Claim job (RUNSTATUS: 0 → 1)
                         ├─ Deserialize context from PARAMETERS JSON
                         ├─ Re-run pipeline stages 1–4 (force sync)
                         │    Uses AsyncQueryTimeoutSeconds (300s)
                         ├─ Execute report, export to file
                         │    Writes to: {AsyncReport:OutputPath}/{clientId}/Report_9999_timestamp.xlsx
                         └─ Update TSYSJOB:
                              RESULTLOCATION = "/reports/42/Report_9999_20260519_143022.xlsx"
                              RUNSTATUS = 2 (Completed)
                              COMPLETEDON = GETDATE()
                         │
                         ▼
                        My Reports inbox → shows Completed + Download link
                        User clicks Download →
                        GET /SystemJob/GetSystemJobResult?SysJobId=9999
                        → File streamed from RESULTLOCATION
```

### 4.5 PDF generation (Puppeteer-based)

```
CommonReportBLL.PdfExport()
  │
  ├─ GetReportViewMeta → check for pivot
  │    (Pivot path): PivotPdfExport.ExportAsync()
  │    (Flat path):  chunked rendering (8,000 rows per chunk)
  │
  ├─ Load Handlebars template (.html from FrameworkSL/Templates/)
  │    pivot-report.html  OR  report-standard.html
  │
  ├─ Register custom Handlebars helpers:
  │    formatDate(value, format)
  │    formatCurrency(value)          → N2 decimal
  │    formatAuto(value)              → intelligent type detection
  │    renderSubRows(rows, fields)    → nested row expansion
  │    eachByPath(obj, path)          → dot-notation field traversal
  │    eq(a,b), gt(a,b), add(a,b), multiply(a,b)
  │
  ├─ Compile + render template → HTML string
  │
  ├─ GlobalBrowser.GetAsync()
  │    → Shared Puppeteer browser instance (singleton; avoids per-request spin-up)
  │    → NewPageAsync() → SetContentAsync(html)
  │    → PdfDataAsync({ landscape, margin, headerTemplate, footerTemplate })
  │    → byte[]
  │
  ├─ (Flat multi-chunk): Repeat per 8,000-row chunk → merge via PdfSharpCore
  │
  └─ return byte[]
```

---

## 5. Export Formats

The `IsExcelImport` byte value in `ReportCallingDTO` selects the export format. (This name is legacy; the field controls all formats.)

| `IsExcelImport` value | Format | Content-Type | Notes |
|---|---|---|---|
| `1` | **JSON (Grid)** | `application/json` | Used for in-browser display. Pivot mode selected by `IsPivotView` flag |
| `2` | **Excel (.xlsx)** | `application/vnd.openxmlformats-officedocument.spreadsheetml.sheet` | Flat or pivot cross-tab |
| `3` | **CSV** | `text/csv` | Flat only; throws for pivot views |
| `4` | **PDF** | `application/pdf` | Puppeteer-rendered; pivot-aware layout; chunked for large datasets |

### Allowed formats per report

`MREPORTCONFIG.ALLOWEDEXPORTFORMATS` stores a comma-separated list of allowed byte values, e.g. `"0,1,2,3"` (Grid, PDF, Excel, CSV). The orchestrator rejects requests for disallowed formats before executing.

---

## 6. Pivot Engine

### 6.1 Field roles

Every field in a pivot view is classified via `MREPORTVIEWFIELDS.DISPLAYTYPE`:

| `DISPLAYTYPE` | Role | Description |
|---|---|---|
| `0` or `2` | **Row** (dimension) | Pinned on the left; forms the composite row key for grouping |
| `1` | **Column** (dimension) | Distinct values become column headers |
| `3` | **Summary** (value) | Aggregated per (row group × column value) cell |

### 6.2 Aggregation types

`MREPORTVIEWFIELDS.AGGREGATIONTYPE`:

| Value | Operation | Supported field types |
|---|---|---|
| `0` | None (first value) | Any |
| `1` | SUM | INT, LONG, DOUBLE |
| `3` | AVG | INT, LONG, DOUBLE |
| `5` | COUNT | Any (counts non-null) |
| `7` | MAX | INT, LONG, DOUBLE, DATE |
| `8` | MIN | INT, LONG, DOUBLE, DATE |

### 6.3 DataPivotEngine algorithm

```
Input:  IAsyncEnumerable<Dictionary<string, object?>>   ← flat rows from module endpoint
        IReadOnlyList<PivotFieldConfigDTO>              ← field roles from MREPORTVIEWFIELDS
        int maxRows = 100,000                           ← OOM guard

Step 1  Partition fieldConfig:
          rowFields  = [DisplayType == 0 or 2]
          colFields  = [DisplayType == 1]
          sumFields  = [DisplayType == 3]

Step 2  Stream rows, project each to only field names in fieldConfig
          → retains ~10% of columns for a 100-col source with 10-field pivot
          → aborts with descriptive exception if count > maxRows

Step 3  Discover distinct column values per colField
          → sorted alphabetically
          → column header name = "{colValue}_{sumField.FieldTitle}"

Step 4  Group rows by composite rowField key
          For each group × each (colValue, sumField):
            filter rows where row[colField] == colValue
            apply aggregation in-memory (C# LINQ, not SQL)

Step 5  Grand total row
          If any sumField.IsGrandTotal == 0:
            aggregate entire dataset per sumField × all colValues

Output: PivotResultDTO {
          RowHeaders:    ["Department", "Month"]
          PivotColumns:  ["Sales_Amount", "Sales_Count", "Returns_Amount"]
          Rows:          [{ Department:"Finance", Month:"Jan", Sales_Amount:12500, ... }, ...]
          GrandTotal:    { Sales_Amount:187300, ... }
          OriginalRowCount: 45000
        }
```

### 6.4 Column and row totals

| Config field | Meaning |
|---|---|
| `ISGRANDTOTAL = 0` | Include this field in grand total row |
| `ISCOLUMNTOTAL = 0` | Show column totals at bottom of each pivot column |
| `ISROWTOTAL = 0` | Show row totals at right of each row |
| `SUPPRESSIFEMPTY = 0` | Hide pivot column entirely when all values are null/zero |

### 6.5 Row nesting (sub-rows)

`MREPORTVIEWFIELDS.ROWLEVEL` controls visual nesting depth:
- `0` — main row
- `1` — first sub-row (indented under parent)
- `2` — second-level sub-row
- etc.

Used for hierarchical reports (e.g., Department → Team → Individual).

---

## 7. Async Job System (My Reports)

### 7.1 When a report becomes async

A report is submitted as an async SysJob when **any** of these conditions are met:

| Condition | Config |
|---|---|
| `ASYNCMODE = 1` (AlwaysAsync) | `MREPORTCONFIG.ASYNCMODE` |
| `ASYNCMODE = 0` (Auto) AND `DateRangeDays > ASYNCTHRESHOLDDAYS` | default threshold: 90 days |
| `ASYNCMODE = 3` (PolicyBased) | evaluates same as Auto; MREPORTPOLICY not yet implemented |

`ASYNCMODE = 2` (AlwaysSync) forces synchronous execution regardless of date range.

### 7.2 TSYSJOB run status values

| `RUNSTATUS` | Meaning | Action |
|---|---|---|
| `0` | **Pending** | Waiting in queue |
| `1` | **Running** | Worker has claimed it |
| `2` | **Completed** | `RESULTLOCATION` is set; file is ready |
| `3` | **Failed** | `ERRORMESSAGE` contains reason; retry exhausted |
| `4` | **Cancelled** | User cancelled before execution |
| `5` | **OnHold** | Manually paused |

### 7.3 Retry logic

When a job fails and `RETRYCOUNT < MAXRETRIES`:
- `RUNSTATUS` resets to `0` (Pending) → re-queued automatically
- `RETRYCOUNT` increments by `1`

When `RETRYCOUNT >= MAXRETRIES`:
- `RUNSTATUS` sets to `3` (Failed)
- `ERRORMESSAGE` contains the last error (truncated to 2,000 chars)

Default `MAXRETRIES = 1` (one retry on failure). Configurable per job.

### 7.4 Result delivery

Once completed:
1. `TSYSJOB.RESULTLOCATION` = absolute file path on the server
2. Frontend polls `/CommonReport/GetMyReports` → sees `RUNSTATUS = 2`
3. User clicks download → `GET /SystemJob/GetSystemJobResult?SysJobId=9999`
4. Endpoint reads file, streams it to browser with correct Content-Type
5. File is retained until `EXPIRYAT` (default: 24 hours after completion)

---

## 8. Report Orchestration Platform

The **Report Orchestration Platform** is a new infrastructure layer introduced to standardise how all future report endpoints are built. It is **purely additive** — every existing `CommonReportBLL` path is untouched.

### 8.1 Orchestration pipeline (6 stages)

```
IQueryOrchestrator.ExecuteReportAsync(context)
│
├─ Stage 1: Load MREPORTCONFIG
│    If no config row → safe defaults (AlwaysSync, 30s timeout, all formats allowed)
│
├─ Stage 2: Validate export format
│    Check RequestedFormat ∈ ALLOWEDEXPORTFORMATS set
│    Throws InvalidOperationException if disallowed
│
├─ Stage 3: Resolve DataSourceType
│    DataSourceType.Auto → IDataSourceRuleEvaluator.EvaluateAsync()
│      Load MREPORTDATASOURCERULE rows, evaluate in RULEPRIORITY order:
│        RuleType 0 (DateRangeDays): context.DateRangeDays > ThresholdValue → route to TargetDataSource
│        RuleType 2 (TimeOfDay):     UtcNow.Hour >= ThresholdValue → route to TargetDataSource
│      First matching rule wins; fallback = Oltp
│
├─ Stage 4: Resolve DB connection
│    IReportConnectionResolver.ResolveConfigAsync(loginDTO, resolvedSource)
│      Oltp    → LoginDTO.ServerConfigId (existing connection)
│      ReportDb→ MSERVERCONFIG.REPORTSERVERCONFIGID; falls back to OLTP if -1
│      ArchiveDb→ MSERVERCONFIG.ARCHIVESERVERCONFIGID; falls back to OLTP if -1
│    Applies NOLOCK if: SqlServer + dedicated analytics DB + ISNOLOCKALLOWED = true
│
├─ Stage 5: Async vs sync decision
│    IAsyncDecisionEngine.ShouldRunAsyncAsync(context)
│      AlwaysAsync   → true
│      AlwaysSync    → false
│      Auto/Policy   → DateRangeDays > AsyncThresholdDays
│
└─ Stage 6:
     ├─ IsAsync = false (sync):
     │    Load REPORTCODE → resolve module endpoint from registry
     │    Open connection → call endpoint.GetReportDataAsync(context, connection, ct)
     │    Route to export handler (Grid/Excel/PDF/CSV)
     │    → ReportExecutionResult.SyncCompleted
     │
     └─ IsAsync = true:
          Serialize context → INSERT TSYSJOB (PARAMETERS = JSON)
          → ReportExecutionResult.AsyncSubmitted { SysJobId }
```

### 8.2 IReportEndpoint — contract for migrated report endpoints

Module teams migrate their report endpoints to this pattern:

```csharp
[ReportEndpoint(ReportCode = "SALES001", PreferredDataSource = DataSourceType.ReportDb)]
public class SalesReportEndpoint : ReportEndpointBase
{
    public SalesReportEndpoint(IReportConnectionResolver resolver) : base(resolver) { }

    public override async IAsyncEnumerable<IReadOnlyDictionary<string, object?>>
        GetReportDataAsync(
            ReportExecutionContext context,
            IDbConnection connection,
            [EnumeratorCancellation] CancellationToken ct = default)
    {
        var p = BuildBaseParameters(context);     // ClientId, WorkOUId, RoleId, WorkDate
        p.Add("FromDate", GetParameter<DateTime?>(context, "FromDate"));
        p.Add("ToDate",   GetParameter<DateTime?>(context, "ToDate"));

        await foreach (var row in connection
            .QueryUnbufferedAsync<Dictionary<string, object?>>(Sql, p,
                commandTimeout: context.QueryTimeoutSeconds)
            .WithCancellation(ct))
        {
            yield return row;
        }
    }

    private const string Sql = @"
        SELECT  I.INVOICENO, I.INVOICEDATE, C.CUSTOMERNAME, I.NETAMOUNT
        FROM    TINVOICE I
        JOIN    MCUSTOMER C ON C.CUSTOMERID = I.CUSTOMERID
        WHERE   I.CLIENTID    = @ClientId
        AND     I.INVOICEDATE BETWEEN @FromDate AND @ToDate";
}
```

**What the module endpoint provides:** SQL + business parameters  
**What the orchestrator handles:** connection, export, pivot, SysJob, caching, timeout — all automatically

### 8.3 DataSourceType routing rules

`MREPORTDATASOURCERULE` rows control automatic datasource selection when `DATASOURCETYPE = 3` (Auto):

| `RULETYPE` | Trigger condition | Example |
|---|---|---|
| `0` DateRangeDays | `context.DateRangeDays > THRESHOLDVALUE` | > 365 days → ArchiveDb |
| `2` TimeOfDay | `UtcNow.Hour >= THRESHOLDVALUE` | ≥ 18 → ReportDb (off-peak) |
| `1` EstimatedRows | Manual evaluation (not auto-triggered) | — |
| `3` Manual | Never auto-triggered; external override only | — |

Rules are evaluated in `RULEPRIORITY` order (lower = first). The first matching rule wins.

### 8.4 Result types

`ReportExecutionResult` wraps the outcome of every orchestrated call:

```
ReportResultType.SyncCompleted  → GridData or ExportStream ready
ReportResultType.AsyncSubmitted → SysJobId returned; result pending
ReportResultType.Failed         → ErrorMessage + ErrorCode (never exposes raw exception to FE)
```

Module endpoints call `await result.SendResponseAsync(HttpContext, ct)` which automatically serialises the correct HTTP response for each type.

---

## 9. API Endpoints Reference

All endpoints are in the **FrameworkSL** project under `Endpoints/CommonReport/`. They accept the `Login` header (encrypted `LoginDTO`).

---

### `POST /CommonReport/Report`

**Purpose:** Execute a report synchronously (or submit async). This is the primary report endpoint used by Angular.

**Request body:** `ReportCallingDTO`

| Field | Type | Description |
|---|---|---|
| `ReportViewId` | int | Target view (identifies datasource, pivot mode, field config) |
| `ReportId` | int | Report definition ID (maps to module endpoint URI) |
| `IsExcelImport` | byte | Export format: 1=JSON, 2=Excel, 3=CSV, 4=PDF |
| `IsPivotView` | int | 0=FE-driven, 1=BE DataPivot (only when ReportViewType=2) |
| `CriteriaDTO` | object | Filter parameters (FromDate, ToDate, dimension filters) |
| `ReportTitle` | string | Override report title (optional) |
| `FieldIsDisplay` | int | 0=visible fields only, 1=all fields |
| `RecordsPerPage` | int | Page size for paginated grid calls |

**Response:** `ResponseStandardDTO<object>` wrapping:
- **JSON (format 1, flat):** `{ Data: [...], TotalRows: N }`
- **JSON (format 1, BE pivot):** `{ rowHeaders: [...], pivotColumns: [...], rows: [...], grandTotal: {...} }`
- **JSON (format 1, FE pivot):** `{ fields: [...], rows: [...] }`
- **Excel/CSV/PDF:** `byte[]` with appropriate `Content-Disposition` header

---

### `GET /CommonReport/GetMyReports`

**Purpose:** Returns the current user's async report jobs for the "My Reports" inbox.

**Query params:** `PageNumber` (default 1), `PageSize` (default 20)

**Response:**
```json
[
  {
    "sysJobId": 9999,
    "jobType": "REPORT",
    "runStatus": 2,
    "resultLocation": "/reports/42/Report_9999_20260519.xlsx",
    "submittedOn": "2026-05-19T10:00:00Z",
    "completedOn": "2026-05-19T10:03:17Z",
    "parameters": "{ ... context JSON ... }"
  }
]
```

`runStatus` values: 0=Pending, 1=Running, 2=Completed, 3=Failed, 4=Cancelled

---

### `GET /CommonReport/GetPivotConfig`

**Purpose:** Returns field role configuration for a pivot view. The Angular pivot UI calls this once when entering pivot mode.

**Query params:** `ReportViewId` (int, required)

**Response:** `PivotFieldConfigDTO[]`

```json
[
  {
    "reportVsFieldsId": 101,
    "fieldName": "DEPARTMENT",
    "fieldTitle": "Department",
    "fieldType": 1,
    "displayType": 0,
    "aggregationType": 0,
    "pivotLevel": 0,
    "isGrandTotal": 1,
    "isColumnTotal": 1,
    "isRowTotal": 1,
    "suppressIfEmpty": 1,
    "displaySlNo": 1
  },
  {
    "fieldName": "MONTH",
    "fieldTitle": "Month",
    "displayType": 1,
    ...
  },
  {
    "fieldName": "NETAMOUNT",
    "fieldTitle": "Net Amount",
    "fieldType": 6,
    "displayType": 3,
    "aggregationType": 1,
    ...
  }
]
```

**Cache:** `CLIENT_LEVEL` (keyed by `ClientId + ReportViewId`) — stable master data.

---

### `POST /CommonReport/ExecuteAsyncJob`

**Purpose:** Called exclusively by the SysJob background worker to execute a previously queued async report.

**Query params:** `SysJobId` (int)

**How it works:**
1. Loads `TSYSJOB` row, validates status
2. Claims job (RUNSTATUS: 0→1)
3. Deserialises `PARAMETERS` JSON → reconstructs `ReportExecutionContext`
4. Re-runs pipeline stages 1–4, forces sync execution
5. Writes result to file at `{AsyncReport:OutputPath}/{clientId}/Report_{sysJobId}_timestamp.{ext}`
6. Sets `TSYSJOB.RESULTLOCATION`, marks Completed (or Failed with ErrorMessage)

**Response:** `{ ResultLocation: "/reports/42/Report_9999_20260519.xlsx", SysJobId: 9999 }`

---

### `GET /SystemJob/GetSystemJobResult`

**Purpose:** Streams the result file for a completed async job to the browser.

**Query params:** `SysJobId` (int)

**Security:** Checks ACL — must be job owner or have CanViewResult share.

**Response:** Binary file stream with inferred `Content-Type` based on file extension.

---

## 10. Database Schema

### Report master tables (existing)

#### `MREPORT` — Report definitions

| Column | Type | Description |
|---|---|---|
| `REPORTID` | INT PK | Unique report identifier |
| `REPORTCODE` | VARCHAR | Unique code used by the orchestrator registry |
| `REPORTNAME` | VARCHAR | Display name |
| `REPORTTYPE` | TINYINT | Report category |
| `REPORTURI` | VARCHAR | HTTP endpoint of the module report data provider |
| `DEFAULTREPORTFORMATID` | INT FK | Default template |
| `STATUS` | TINYINT | 0=Active, 1=Inactive |

#### `MREPORTVIEW` — View configurations

| Column | Type | Description |
|---|---|---|
| `REPORTVIEWID` | INT PK | |
| `REPORTID` | INT FK | Parent report |
| `REPORTVIEWNAME` | VARCHAR | Display label |
| `REPORTVIEWTYPE` | TINYINT | **0**=Standard, **1**=Analysis, **2**=Pivot |
| `ISDATAPIVOT` | TINYINT | 0=Yes (BE DataPivot), 1=No (FE pivot) |
| `ISHEADERREQUIRED` | TINYINT | 0=Include report header (logo, title) |
| `REPORTHEADERID` | INT FK | Header template reference |
| `TEMPLATEID` | INT FK | Primary Handlebars template (PDF) |
| `SECONDTEMPLATEID` | INT FK | Secondary template (dual-view reports) |
| `DYNAMICFOOTER` | VARCHAR | Handlebars expression for footer text |
| `ORDERBYFIELDS` | VARCHAR | Default sort fields |

#### `MREPORTVIEWFIELDS` — Column display configuration

| Column | Type | Description |
|---|---|---|
| `REPORTVIEWFIELDSID` | INT PK | |
| `REPORTVIEWID` | INT FK | |
| `REPORTVSFIELDSID` | INT FK | Links to `MREPORTVSFIELDS` (field definition) |
| `FIELDTITLE` | VARCHAR | Display label override |
| `FIELDWIDTH` | INT | Column width (pixels) |
| `ISDISPLAY` | TINYINT | **0**=Visible, **1**=Hidden |
| `DISPLAYSLNO` | INT | Column order (ascending) |
| `ALIGNMENT` | TINYINT | 0=Left, 1=Centre, 2=Right |
| `DISPLAYTYPE` | TINYINT | **0/2**=Row dim, **1**=Column dim, **3**=Summary value |
| `AGGREGATIONTYPE` | TINYINT | **0**=None, **1**=SUM, **3**=AVG, **5**=COUNT, **7**=MAX, **8**=MIN |
| `ISSUBTOTAL` | TINYINT | Show sub-total for this field |
| `ISGRANDTOTAL` | TINYINT | **0**=Include in grand total |
| `ISCOLUMNTOTAL` | TINYINT | **0**=Show column totals |
| `ISROWTOTAL` | TINYINT | **0**=Show row totals |
| `PIVOTLEVEL` | TINYINT | Nesting level for multi-level column dims |
| `ROWLEVEL` | SMALLINT | **0**=Main row, **1+**=Sub-row nesting depth |
| `SUPPRESSIFEMPTY` | TINYINT | **0**=Hide empty pivot columns |
| `CALFORMAT` | VARCHAR | Date format string for this field |

#### `MREPORTVSFIELDS` — Field definitions

| Column | Type | Description |
|---|---|---|
| `REPORTVSFIELDSID` | INT PK | |
| `FIELDNAME` | VARCHAR | DB column name (key in row dict) |
| `FIELDTYPE` | TINYINT | **0**=INT, **1**=STRING, **2**=LONG, **3**=DATE, **4**=BOOL, **6**=DOUBLE |
| `DISPLAYNAME` | VARCHAR | Default display label |

---

### Orchestration tables (new — `ReportOrchestration_Analytics_DDL.sql`)

#### `MREPORTCONFIG` — Per-report orchestration settings

| Column | Type | Default | Description |
|---|---|---|---|
| `REPORTCONFIGID` | INT PK | — | |
| `REPORTID` | INT FK UNIQUE | — | One config per report |
| `DATASOURCETYPE` | TINYINT | 0 | 0=OLTP, 1=ReportDB, 2=ArchiveDB, 3=Auto |
| `ASYNCMODE` | TINYINT | 0 | 0=Auto, 1=AlwaysAsync, 2=AlwaysSync, 3=PolicyBased |
| `ASYNCTHRESHOLDDAYS` | INT | 90 | DateRangeDays threshold for auto async |
| `QUERYTIMEOUTSECONDS` | INT | 30 | Sync query timeout |
| `ASYNCQUERYTIMEOUTSECONDS` | INT | 300 | Async worker query timeout |
| `CACHEDURATIONMINUTES` | INT | 0 | 0=no cache |
| `CACHEKEYPARAMS` | VARCHAR | NULL | Comma-separated param names for cache key |
| `ALLOWEDEXPORTFORMATS` | VARCHAR | "0,1,2,3" | Comma-separated ExportFormat byte values |
| `PIVOTENGINE` | TINYINT | 0 | 0=None, 1=Backend, 2=Frontend |
| `REPORTCATEGORY` | TINYINT | 0 | 0=Operational, 1=Analytical, 2=KPI, 3=Archive, 4=Scheduled |
| `ISNOLOCKALLOWED` | TINYINT | 1 | 0=Apply NOLOCK on ReportDB/ArchiveDB reads (SQL Server only) |
| `DEFAULTPRIORITY` | TINYINT | 5 | SysJob priority (1–10; higher = earlier) |
| `RESULTEXPIRYHOURS` | INT | 24 | File TTL in hours |
| `STATUS` | TINYINT | 1 | 0=Inactive, 1=Active |

#### `MREPORTDATASOURCERULE` — Auto datasource rules

| Column | Type | Description |
|---|---|---|
| `DATASOURCERULEID` | INT PK | |
| `REPORTID` | INT FK | |
| `SLNO` | INT | Display order |
| `RULETYPE` | TINYINT | 0=DateRangeDays, 1=EstimatedRows, 2=TimeOfDay, 3=Manual |
| `THRESHOLDVALUE` | INT | Threshold (days / hour / row count) |
| `TARGETDATASOURCE` | TINYINT | 0=OLTP, 1=ReportDB, 2=ArchiveDB |
| `RULEPRIORITY` | INT | Lower = evaluated first |

#### `MSERVERCONFIG` — Amended with new columns

| New Column | Type | Default | Description |
|---|---|---|---|
| `REPORTSERVERCONFIGID` | INT | -1 | FK to MSERVERCONFIG for dedicated ReportDB; -1 = use OLTP |
| `ARCHIVESERVERCONFIGID` | INT | -1 | FK to MSERVERCONFIG for archive DB; -1 = use OLTP |

#### `TSYSJOB` — Async job tracking (existing, key columns)

| Column | Type | Description |
|---|---|---|
| `SYSJOBID` | INT PK | |
| `SYSJOBTYPE` | VARCHAR | "REPORT" for report jobs |
| `PARAMETERS` | NVARCHAR(MAX) | JSON-serialised `ReportExecutionContext` |
| `SUBMITTEDBYID` | INT | User who submitted the job |
| `RUNSTATUS` | TINYINT | 0=Pending, 1=Running, 2=Completed, 3=Failed, 4=Cancelled |
| `PRIORITY` | INT | Higher = executed first |
| `RETRYCOUNT` | INT | Increments on each failure |
| `MAXRETRIES` | INT | Max allowed retries |
| `RESULTLOCATION` | VARCHAR | Absolute file path on server (set on completion) |
| `ERRORMESSAGE` | VARCHAR | Last error message (truncated to 2,000 chars) |
| `STARTTIME` | DATETIME | When worker claimed the job |
| `ENDTIME` | DATETIME | When job completed or failed |
| `EXPIRYAT` | DATETIME | When file can be deleted |
| `SYSJOBTENANTID` | INT | ClientId (tenant isolation) |

---

## 11. Configuration Reference

### `appsettings.json` — relevant keys

```json
{
  "AsyncReport": {
    "OutputPath": "/var/gb5-reports"
  }
}
```

| Key | Default | Description |
|---|---|---|
| `AsyncReport:OutputPath` | `{TempPath}/gb5-async-reports` | Base directory for async report output files. Per-tenant subdirectory is created automatically: `{OutputPath}/{ClientId}/` |

### `MREPORTCONFIG` — per-report tuning

To configure a report's orchestration behaviour, insert or update a row in `MREPORTCONFIG`:

```sql
-- Example: Sales Analysis report — ArchiveDB for >365 days, async if >90 days
INSERT INTO MREPORTCONFIG (
    REPORTID, DATASOURCETYPE, ASYNCMODE, ASYNCTHRESHOLDDAYS,
    QUERYTIMEOUTSECONDS, ASYNCQUERYTIMEOUTSECONDS,
    ALLOWEDEXPORTFORMATS, PIVOTENGINE, REPORTCATEGORY,
    ISNOLOCKALLOWED, DEFAULTPRIORITY, RESULTEXPIRYHOURS, STATUS)
VALUES (
    101,   -- REPORTID
    3,     -- Auto datasource (evaluated by MREPORTDATASOURCERULE)
    0,     -- Auto async (date-range based)
    90,    -- Async if date range > 90 days
    30,    -- 30s sync timeout
    300,   -- 300s async timeout
    '0,1,2,3',  -- All formats allowed
    1,     -- Backend pivot engine
    1,     -- Analytical category
    0,     -- Allow NOLOCK on ReportDB/ArchiveDB
    5,     -- Default priority
    48,    -- Keep result file 48 hours
    1);    -- Active

-- Auto-routing rule: >365 days → ArchiveDB
INSERT INTO MREPORTDATASOURCERULE (REPORTID, SLNO, RULETYPE, THRESHOLDVALUE, TARGETDATASOURCE, RULEPRIORITY)
VALUES (101, 1, 0, 365, 2, 10);   -- DateRangeDays > 365 → ArchiveDB, priority 10

-- Auto-routing rule: >90 days → ReportDB
INSERT INTO MREPORTDATASOURCERULE (REPORTID, SLNO, RULETYPE, THRESHOLDVALUE, TARGETDATASOURCE, RULEPRIORITY)
VALUES (101, 2, 0, 90, 1, 20);    -- DateRangeDays > 90 → ReportDB, priority 20
```

### DI registration (once per API host)

```csharp
// In Program.cs of each SL project that hosts report endpoints:
builder.Services.AddReportOrchestration(
    typeof(SalesReportEndpoint).Assembly);  // assembly containing ReportEndpointBase subclasses
```

---

## 12. Security & Multi-Tenancy

### DB-level tenant isolation

Every query is executed via `IQueryExecutor` which routes to the database identified by `LoginDTO.DatabaseName`. Report metadata tables (`MREPORT`, `MREPORTVIEW`, etc.) are per-tenant — there is no shared global report catalogue. This means:

- A user from Tenant A can never see or access Tenant B's reports
- `MREPORTCONFIG` rows are tenant-specific
- No `WHERE TENANTID = @ClientId` filter is needed on the report tables because they are in separate databases

### Async job ownership

`TSYSJOB` rows are filtered by `SUBMITTEDBYID = LoginDTO.UserId` in the My Reports endpoint. Users can only see their own jobs.

`/SystemJob/GetSystemJobResult` performs an ACL check (ownership or explicit share) before streaming the file.

### Parameterized queries — mandatory

All SQL is defined in `*QB.cs` constants with Dapper parameters. No string interpolation in SQL anywhere in the report stack. This is enforced by architecture review.

### Sensitive data

- Connection strings are stored in HashiCorp Vault, not appsettings.json
- `LoginDTO` is never cached or logged
- Stack traces are caught in the orchestrator and logged server-side; the FE receives only `ErrorMessage` (user-friendly) and `ErrorCode` (for structured handling)

---

## 13. Performance Guide

### When to use async (SysJob)

| Date Range | Recommendation |
|---|---|
| < 90 days | Synchronous — fast enough for grid/Excel |
| 90–365 days | Auto mode: sync for simple reports, async for complex pivots |
| > 365 days | Async recommended; route to ArchiveDB via datasource rule |
| Scheduled delivery | Always async (ASYNCMODE = 1, REPORTCATEGORY = 4) |

### Memory optimisations

| Technique | Where used | Impact |
|---|---|---|
| `IAsyncEnumerable` streaming | `ReportURICalling()` + module endpoints | Flat rows never fully buffered |
| Field projection in DataPivotEngine | Pivot transformation | 100-column source with 10-field pivot → ~10× memory reduction |
| PDF chunking (8,000 rows/chunk) | `PdfExport()` for large flat reports | Prevents OOM on 200k+ row PDFs |
| Row cap (default 100,000) in pivot | `DataPivotEngine` | Blocks runaway pivots with descriptive exception |
| Shared Puppeteer browser | `GlobalBrowser.GetAsync()` | No per-request browser spin-up |

### Caching

| Data | Cache level | TTL |
|---|---|---|
| Pivot field config (`GetPivotConfig`) | `CLIENT_LEVEL` | Until cache invalidation |
| Report execution result | `NOT_REQUIRED` (default) | — |
| Report execution result (configured) | `CLIENT_LEVEL` | `MREPORTCONFIG.CACHEDURATIONMINUTES` |
| Standard fields (OU, company) | `CLIENT_LEVEL` | Per session |

### HTTP client pooling

`IExcelExport.ReportURICalling()` uses a named `IHttpClientFactory` client (`"ReportClient"`) configured with:
- 10-minute timeout
- Connection pooling (managed by ASP.NET Core)
- No `new HttpClient()` anywhere in the export stack

---

## 14. Developer Guide — Adding a Report

### Step 1: Module endpoint (data provider)

Create a module endpoint that returns flat rows. This is the only file the module team owns:

```csharp
// In YourModuleSL/Endpoints/
[ReportEndpoint(ReportCode = "INV001", PreferredDataSource = DataSourceType.Oltp)]
public class InvoiceReportEndpoint : ReportEndpointBase
{
    public InvoiceReportEndpoint(IReportConnectionResolver resolver) : base(resolver) { }

    public override async IAsyncEnumerable<IReadOnlyDictionary<string, object?>>
        GetReportDataAsync(ReportExecutionContext context, IDbConnection connection,
            [EnumeratorCancellation] CancellationToken ct = default)
    {
        var p = BuildBaseParameters(context);
        p.Add("FromDate", GetParameter<DateTime?>(context, "FromDate"));
        p.Add("ToDate",   GetParameter<DateTime?>(context, "ToDate"));
        p.Add("CustomerId", GetParameter<int?>(context, "CustomerId"));

        await foreach (var row in connection
            .QueryUnbufferedAsync<Dictionary<string, object?>>(Sql, p,
                commandTimeout: context.QueryTimeoutSeconds).WithCancellation(ct))
            yield return row;
    }

    private const string Sql = @"
        SELECT I.INVOICENO, I.INVOICEDATE, C.CUSTOMERNAME, I.NETAMOUNT, I.TAXAMOUNT
        FROM   TINVOICE I
        JOIN   MCUSTOMER C ON C.CUSTOMERID = I.CUSTOMERID
        WHERE  I.CLIENTID    = @ClientId
        AND    I.INVOICEDATE BETWEEN @FromDate AND @ToDate
        AND    (@CustomerId IS NULL OR I.CUSTOMERID = @CustomerId)
        ORDER  BY I.INVOICEDATE DESC";
}
```

### Step 2: Register the assembly (once per SL project)

```csharp
// In your module's Program.cs
builder.Services.AddReportOrchestration(
    typeof(InvoiceReportEndpoint).Assembly);
```

### Step 3: Insert `MREPORT` row

```sql
INSERT INTO MREPORT (REPORTCODE, REPORTNAME, REPORTTYPE, MODULEID, REPORTURI, STATUS)
VALUES ('INV001', 'Invoice Register', 0, 5, '/Invoice/GetInvoiceReport', 1);
-- REPORTURI points to the existing module endpoint that returns flat data
```

### Step 4: Insert `MREPORTVIEW` row (repeat for each view)

```sql
-- Standard flat view
INSERT INTO MREPORTVIEW (REPORTID, REPORTVIEWNAME, REPORTVIEWTYPE, ISDATAPIVOT, ISHEADERREQUIRED, STATUS)
VALUES (@@IDENTITY, 'Invoice Register - Standard', 0, 1, 0, 1);

-- Pivot view (BE DataPivot)
INSERT INTO MREPORTVIEW (REPORTID, REPORTVIEWNAME, REPORTVIEWTYPE, ISDATAPIVOT, ISHEADERREQUIRED, STATUS)
VALUES (@@IDENTITY, 'Invoice Register - By Customer × Month', 2, 0, 0, 1);
```

### Step 5: Insert `MREPORTVIEWFIELDS` rows (columns and pivot roles)

```sql
-- For the standard view: define display columns
INSERT INTO MREPORTVIEWFIELDS (REPORTVIEWID, REPORTVSFIELDSID, FIELDTITLE, ISDISPLAY, DISPLAYSLNO, FIELDWIDTH)
VALUES (@ViewId, @FieldId_InvoiceNo, 'Invoice No.', 0, 1, 120);

-- For the pivot view: define row dimension, column dimension, summary
INSERT INTO MREPORTVIEWFIELDS (REPORTVIEWID, REPORTVSFIELDSID, DISPLAYTYPE, AGGREGATIONTYPE, ISGRANDTOTAL)
VALUES (@PivotViewId, @FieldId_Customer, 0, 0, 1);       -- Row dimension
VALUES (@PivotViewId, @FieldId_Month,    1, 0, 1);        -- Column dimension
VALUES (@PivotViewId, @FieldId_Amount,   3, 1, 0);        -- Summary: SUM, include in grand total
```

### Step 6: (Optional) Insert `MREPORTCONFIG` row

Only needed to override defaults:

```sql
INSERT INTO MREPORTCONFIG (REPORTID, ASYNCMODE, ASYNCTHRESHOLDDAYS, ALLOWEDEXPORTFORMATS, STATUS)
VALUES (@ReportId, 0, 60, '0,1,2,3', 1);  -- Auto async if > 60 days
```

### Step 7: Test with the existing endpoint

```
POST /CommonReport/Report
{
  "ReportViewId": 55,
  "ReportId": 101,
  "IsExcelImport": 1,
  "CriteriaDTO": { "FromDate": "2026-01-01", "ToDate": "2026-03-31" }
}
```

---

## 15. Key File Index

| File | Layer | Purpose |
|---|---|---|
| [`FrameworkBLL/CommonReport/CommonReportBLL.cs`](../GB5Framework/FrameworkBLL/CommonReport/CommonReportBLL.cs) | BLL | Core export orchestration — Excel, CSV, PDF, JSON; pivot guards; chunked PDF |
| [`FrameworkDAL/CustomCode/CommonReport/ICommonReportDAL.cs`](../GB5Framework/FrameworkDAL/CustomCode/CommonReport/ICommonReportDAL.cs) | DAL | Report metadata fetch interface |
| [`FrameworkDAL/Query/Report/ReportQB.cs`](../GB5Framework/FrameworkDAL/Query/Report/ReportQB.cs) | DAL | All SQL constants for MREPORT, MREPORTVIEW, MREPORTVIEWFIELDS |
| [`FrameworkSL/Endpoints/CommonReport/Report.cs`](../GB5Framework/FrameworkSL/Endpoints/CommonReport/Report.cs) | SL | `POST /CommonReport/Report` — main export endpoint |
| [`FrameworkSL/Endpoints/CommonReport/GetMyReports.cs`](../GB5Framework/FrameworkSL/Endpoints/CommonReport/GetMyReports.cs) | SL | `GET /CommonReport/GetMyReports` — My Reports inbox |
| [`FrameworkSL/Endpoints/CommonReport/GetPivotConfig.cs`](../GB5Framework/FrameworkSL/Endpoints/CommonReport/GetPivotConfig.cs) | SL | `GET /CommonReport/GetPivotConfig` — pivot field roles for FE |
| [`FrameworkSL/Endpoints/CommonReport/ExecuteAsyncJob.cs`](../GB5Framework/FrameworkSL/Endpoints/CommonReport/ExecuteAsyncJob.cs) | SL | `POST /CommonReport/ExecuteAsyncJob` — SysJob worker callback |
| [`FrameworkSL/Endpoints/SystemJob/GetSystemJobResult.cs`](../GB5Framework/FrameworkSL/Endpoints/SystemJob/GetSystemJobResult.cs) | SL | `GET /SystemJob/GetSystemJobResult` — stream completed file |
| [`GB5Shared/Export/Pivot/DataPivotEngine.cs`](../GB5Shared/Export/Pivot/DataPivotEngine.cs) | Shared | Flat-to-pivot transformation (grouping, aggregation, grand total) |
| [`GB5Shared/Export/Pivot/PivotExcelExport.cs`](../GB5Shared/Export/Pivot/PivotExcelExport.cs) | Shared | ClosedXML cross-tab `.xlsx` export |
| [`GB5Shared/Export/Pivot/PivotPdfExport.cs`](../GB5Shared/Export/Pivot/PivotPdfExport.cs) | Shared | Handlebars + Puppeteer pivot PDF |
| [`GB5Shared/Export/Pivot/PivotResultDTO.cs`](../GB5Shared/Export/Pivot/PivotResultDTO.cs) | Shared | Pivot output structure |
| [`GB5Shared/ExcelExport/ExcelExport.cs`](../GB5Shared/ExcelExport/ExcelExport.cs) | Shared | Flat report HTTP streaming + OpenXml `.xlsx` export |
| [`GB5Shared/Export/HybridReport/GlobalBrowser.cs`](../GB5Shared/Export/HybridReport/GlobalBrowser.cs) | Shared | Shared Puppeteer browser singleton (PDF generation) |
| [`GB5Shared/Enums/ReportOrchestration/ReportOrchestrationEnums.cs`](../GB5Shared/Enums/ReportOrchestration/ReportOrchestrationEnums.cs) | Shared | DataSourceType, AsyncMode, ExportFormat, PivotEngine, etc. |
| [`GB5Shared/DTO/ReportOrchestration/ReportExecutionContext.cs`](../GB5Shared/DTO/ReportOrchestration/ReportExecutionContext.cs) | Shared | Pipeline context DTO; carried through all orchestration stages |
| [`GB5Shared/DTO/ReportOrchestration/ReportExecutionResult.cs`](../GB5Shared/DTO/ReportOrchestration/ReportExecutionResult.cs) | Shared | Typed result envelope (SyncCompleted / AsyncSubmitted / Failed) |
| [`FrameworkBLL/ReportOrchestration/IQueryOrchestrator.cs`](../GB5Framework/FrameworkBLL/ReportOrchestration/IQueryOrchestrator.cs) | BLL | Orchestrator + endpoint registry interfaces |
| [`FrameworkBLL/ReportOrchestration/QueryOrchestrator.cs`](../GB5Framework/FrameworkBLL/ReportOrchestration/QueryOrchestrator.cs) | BLL | 6-stage pipeline implementation; `ExecuteAsyncJobAsync` |
| [`FrameworkBLL/ReportOrchestration/ReportConnectionResolver.cs`](../GB5Framework/FrameworkBLL/ReportOrchestration/ReportConnectionResolver.cs) | BLL | OLTP/ReportDB/ArchiveDB connection resolution |
| [`FrameworkBLL/ReportOrchestration/ServerConfigCache.cs`](../GB5Framework/FrameworkBLL/ReportOrchestration/ServerConfigCache.cs) | BLL | Per-request lazy cache of MSERVERCONFIG rows |
| [`FrameworkBLL/ReportOrchestration/DataSourceRuleEvaluator.cs`](../GB5Framework/FrameworkBLL/ReportOrchestration/DataSourceRuleEvaluator.cs) | BLL | MREPORTDATASOURCERULE evaluation |
| [`FrameworkBLL/ReportOrchestration/AsyncDecisionEngine.cs`](../GB5Framework/FrameworkBLL/ReportOrchestration/AsyncDecisionEngine.cs) | BLL | Sync vs async decision logic |
| [`FrameworkSL/ReportOrchestration/ReportEndpointBase.cs`](../GB5Framework/FrameworkSL/ReportOrchestration/ReportEndpointBase.cs) | SL | Base class for migrated module report endpoints |
| [`FrameworkSL/ReportOrchestration/ReportOrchestrationServiceExtensions.cs`](../GB5Framework/FrameworkSL/ReportOrchestration/ReportOrchestrationServiceExtensions.cs) | SL | `AddReportOrchestration()` DI helper; contains full migration guide |
| [`FrameworkSL/ReportOrchestration/ReportResultExtensions.cs`](../GB5Framework/FrameworkSL/ReportOrchestration/ReportResultExtensions.cs) | SL | `result.SendResponseAsync(HttpContext, ct)` helper |
| [`DB/Migrations/ReportOrchestration_Analytics_DDL.sql`](../DB/Migrations/ReportOrchestration_Analytics_DDL.sql) | DB | New tables: MWORKSPACE, MREPORTCONFIG, MREPORTDATASOURCERULE, MDATASOURCE |
| [`DB/Migrations/ReportOrchestration_Amendments.sql`](../DB/Migrations/ReportOrchestration_Amendments.sql) | DB | MSERVERCONFIG + MREPORTCONFIG column additions (guarded ALTER TABLEs) |
| [`Pending_Report_Analytics_Work.md`](../Pending_Report_Analytics_Work.md) | Docs | Full backlog with priorities |

---

## 16. Roadmap & Known Gaps

### Recently completed (May 2026)

| Feature | Detail |
|---|---|
| ✅ DataPivotEngine | In-memory flat-to-pivot with SUM/AVG/COUNT/MAX/MIN |
| ✅ PivotExcelExport | ClosedXML cross-tab with grand total row |
| ✅ PivotPdfExport | Handlebars + Puppeteer with pivot-aware table layout |
| ✅ GlobalBrowser singleton | Shared Puppeteer instance (no per-request spin-up) |
| ✅ Pivot guards in CommonReportBLL | All 4 export methods protected |
| ✅ GetPivotConfig endpoint | CLIENT_LEVEL cached; FE field-role metadata |
| ✅ Report Orchestration Platform | 6-stage pipeline, datasource routing, async decision |
| ✅ ExecuteAsyncJobAsync | Full async worker path with TSYSJOB claim/complete/fail |
| ✅ GetMyReports endpoint | Paginated user inbox for async jobs |
| ✅ MREPORTCONFIG schema | Orchestration config per report (DDL + amendments) |

### Pending — Frontend (P3)

| Item | Description | Effort |
|---|---|---|
| **FE Pivot — BE DataPivot grid mode** | Angular grid with pinned row-dimension columns + dynamic pivot columns; grand total row; sort/filter/resize; chart any numeric column | Large |
| **FE Pivot — FE-driven drag-drop mode** | Full pivot UI: field list panel, drag-drop Row/Column/Value/Filter; re-pivot on field change using cached flat rows; aggregation controls; bar/line/pie/stacked-bar charts | Large |

### Pending — Backend (P4)

| Item | Description | Effort |
|---|---|---|
| **Report view cache invalidation** | Invalidate `GetPivotConfig` cache when `MREPORTVIEWFIELDS` updated via admin UI | Small |
| **`REPORTVIEWTYPE` in GETREPORTVIEW query** | `ReportQB.GETREPORTVIEW` is missing this column from its SELECT | Small |
| **Streaming JSON deserialization** | Use `JsonSerializer.DeserializeAsyncEnumerable` in `ReportURICalling` for 100k+ row memory saving | Large |
| **Field-selection for report URIs** | Allow callers to pass `?fields=col1,col2` to module endpoints; endpoints build dynamic SELECT (already done in AnalysisDAL) — eliminates over-fetch for large views | Very large |
| **Module endpoint migration** | Migrate existing module report endpoints to `ReportEndpointBase` to use orchestration routing and async | Ongoing |
| **`MREPORTPOLICY` table** | Implement `AsyncMode.PolicyBased` (currently falls back to Auto) | Medium |

---

*Document generated from codebase inspection — May 2026. Update this document when major report features are added or changed.*