# Pending Work — Report, Pivot & Analytics

Reference document for future implementation sessions.
Covers everything not yet done after the Pivot/DataPivot BE implementation (May 2026).

---

## What Was Completed (for context)

- `REPORTVIEWTYPE=2` renamed from `Preview` to `Pivot` in enum
- `GET_REPORT_VIEW_META` and `GET_PIVOT_FIELD_CONFIG` query constants added
- `ReportViewMetaDTO` updated with `ReportViewType` property
- `PivotFieldConfigDTO` created
- `ICommonReportDAL` + `CommonReportDAL` extended with two new methods
- `GET /CommonReport/GetPivotConfig` endpoint created
- `DataPivotEngine` — in-memory C# flat→pivot transform with field projection
- `PivotExcelExport` — ClosedXML cross-tab XLSX with grand total
- `PivotPdfExport` — Handlebars/Puppeteer PDF with inline fallback; uses `GlobalBrowser.GetAsync()` singleton
- `CommonReportBLL` — pivot guards in all 4 methods (ExcelExport, CSVExport, PdfExport, JsonReport)
- Hardcoded `C:\Users\GoodBooks\Documents\Reports\*` file writes removed
- DI registration for pivot engine, Excel, PDF exporters
- `GlobalBrowser` extracted to `GB5Shared/Export/HybridReport/GlobalBrowser.cs` — shared singleton
- `ExcelExport` fixed to use `IHttpClientFactory` (named "ReportClient") — anti-pattern resolved
- `pivot-report.html` Handlebars template created in `FrameworkSL/Templates/`
- `GetPivotConfig` cache key updated to use `KeyGenerator.KeyGeneration(OBJECTREPORTVIEW, CLIENT_LEVEL)`
- Flat `JsonReport` path fixed: drains `IAsyncEnumerable` to `List<>` before serializing (§4.2)

---

## Section 1 — Report Module (Backend)

### ~~1.1 HttpClient Anti-Pattern in ExcelExport~~ ✅ DONE

Fixed: `ExcelExport` now uses `IHttpClientFactory` with named "ReportClient" client.
Named client registered in `Program.cs` with 10-minute timeout.

---

### ~~1.2 PivotPdfExport — Shared Browser Instance~~ ✅ DONE

Fixed: `GlobalBrowser` extracted to `GB5Shared/Export/HybridReport/GlobalBrowser.cs`.
`PivotPdfExport` uses `GlobalBrowser.GetAsync()`. `CommonReportBLL` also updated to use it.

---

### ~~1.3 Module-Specific ICommonReportBLL Implementations~~ N/A

Verified: there is only ONE implementation of `ICommonReportBLL` — `CommonReportBLL.cs`.
No other module BLLs implement this interface. No action needed.

---

### ~~1.4 Pivot PDF Handlebars Template~~ ✅ DONE

Template created at `GB5Framework/FrameworkSL/Templates/pivot-report.html`.
`appsettings.json` `PDFEXPORT:PivotView` key added pointing to deployed location.

---

### ~~1.5 TENANTID / CLIENTID Filter in New Queries~~ N/A

Verified: GB5 uses **DB-level tenant isolation** — `IQueryExecutor` routes each query to the
correct tenant database via `LoginDTO.DatabaseName`. Master data tables (`MREPORTVIEW`,
`MREPORTVIEWFIELDS`, `MREPORTVSFIELDS`) are per-tenant databases; there is no shared DB with
a TENANTID/CLIENTID row filter. No changes needed to the new queries.

---

### ~~1.6 GetPivotConfig Cache Key Pattern~~ ✅ DONE

Fixed: `GetPivotConfig.cs` now uses:
```csharp
KeyGenerator.KeyGeneration(req.ReportViewId, EntityConstant.OBJECTREPORTVIEW, CacheKeyLevel.CLIENT_LEVEL, login)
```

---

### 1.7 GetReportViewMeta Caching (Optional, Low Priority)

**Context:** Every export/JSON call makes a 1-row DB query to `MREPORTVIEW` to fetch the meta.
Fast, but adds one extra round-trip per report call.

**Option:** Add `GetReportViewMeta` result to the Dapr cache at CLIENT_LEVEL.
Needs invalidation when admin updates `MREPORTVIEW` for that view.

**Priority:** Low — only matters at very high call volume. Leave until perf profiling shows it.

---

### 1.8 Streaming JSON Deserialization in ReportURICalling (Large Datasets)

**File:** `GB5Shared/ExcelExport/ExcelExport.cs` — `ReportURICalling()`

**Problem:** Current implementation reads entire HTTP response into a string
(`ReadAsStringAsync`) then parses with `JsonDocument`. For 100k+ row responses this
creates a large in-memory string spike before streaming begins.

**Fix:** Use `System.Text.Json.JsonSerializer.DeserializeAsyncEnumerable` with streaming:
```csharp
using var stream = await httpClient.GetStreamAsync(uri, cancellationToken);
await foreach (var row in JsonSerializer.DeserializeAsyncEnumerable<Dictionary<string,object?>>(
    stream, options, cancellationToken))
{
    yield return row;
}
```

**Trade-off:** Requires careful handling of the `ResponseStandardDTO` wrapper (`Body` array
unwrapping) in streaming mode. More complex to implement correctly. Significant memory saving
for large pivots.

**Priority:** Medium — field projection in DataPivotEngine already helps pivot path; this
helps the flat Excel/PDF export path.

---

## Section 2 — Report Module (Frontend)

### 2.1 FE Pivot Component — BE DataPivot Mode

**Trigger:** `REPORTVIEWTYPE=2`, `IsPivotView=1` (default)

**What BE returns:**
```json
{
  "rowHeaders": ["Department", "Month"],
  "pivotColumns": ["Jan_Amount", "Jan_Count", "Feb_Amount", "Feb_Count"],
  "rows": [
    { "Department": "HR", "Month": "Q1", "Jan_Amount": 5000, "Jan_Count": 12, ... }
  ],
  "grandTotal": { "Jan_Amount": 95000, "Jan_Count": 230, ... }
}
```

**FE responsibilities:**
- Display `rows` as a grid with `pivotColumns` as dynamic column headers
- Pin `rowHeaders` columns to the left (sticky)
- Display `grandTotal` as a fixed bottom row
- User can: sort columns, filter, resize — same as flat grid
- User can: chart any numeric pivot column on X/Y axis
- **No** further dimension re-assignment (that is FE-driven mode)

**Grid→Pivot switch:**
- If grid was loaded with pagination (page 1 only), switching to pivot triggers a new BE call
  with `IsPivotView=1` and no pagination
- FE caches the pivot result (all rows in memory) for local sort/filter without re-fetching

---

### 2.2 FE Pivot Component — FE-Driven Mode

**Trigger:** `REPORTVIEWTYPE=2`, `IsPivotView=0`

**What BE returns:**
```json
{
  "fields": [ PivotFieldConfigDTO[] ],
  "rows": [ all flat rows — no transformation ]
}
```

**FE responsibilities:**
- Full Excel-style pivot UI:
  - Field list panel showing all fields with their `DisplayType` role defaults from DB config
  - Drag-and-drop: move fields between Row/Column/Value/Filter zones
  - Re-pivot on each field assignment change (in-memory JS operation on cached flat rows)
- Aggregation controls: SUM / AVG / COUNT / MAX / MIN per value field
- Chart: pivot result → chart type selector → chart render
- FE must cache the flat rows — re-fetch only when criteria changes

**Field metadata from `GET /CommonReport/GetPivotConfig`:**
- Call this endpoint once when activating pivot mode
- `DisplayType` from DB gives the default role for each field (can be overridden by user drag-drop)
- `FieldType` tells FE how to format cells (date, numeric, string)

---

### 2.3 FE Pivot — Chart Support

Both pivot modes should support charting:

| Mode | Chart source | X axis | Y axis |
|------|-------------|--------|--------|
| BE DataPivot | `rows` (pivot-shaped) | any row-dimension column | any pivot value column |
| FE-driven | FE-pivoted result | user-assigned row dim | user-assigned value field |

Chart types to support: bar, line, pie, stacked bar (standard BI set).
FE chart library choice: to be decided (Chart.js / ApexCharts / ECharts — check existing FE stack).

---

## Section 3 — Analytics Module

### 3.1 New Analytics Requirements (Not Yet Gathered)

**Status:** User confirmed new analytics requirements exist — discussion deferred to a
separate planning session.

**Existing analytics infrastructure (for reference):**

| Table | Purpose |
|-------|---------|
| `MANALYSIS` | Analysis definitions (name, type, module) |
| `MANALYSISFIELDS` | Fields available for an analysis |
| `MANALYSISMASTERFIELDS` | Master field registry |
| `MANALYSISOBJECT` | Objects (views/tables) used by analysis |
| `MANALYSISQUERY` | Saved analysis queries |
| `MANALYSISQUERYFIELDS` | Field selections per saved query |

**Existing BE code:**
- `AnalysisDAL.DynamicOutput()` — builds dynamic SQL selecting only required fields
  (`AnalysisQB.GET_REPORTVIEW_VISIBLE_FIELDS_BASED_ON_REPORTVIEWID`)
- Supports: `QueryEditor` type (raw SQL) and `DesignMode` type (drag-drop field selector)

**Start the next analytics session by:**
1. Reviewing legacy analytics screens to understand existing capabilities
2. Gathering new requirements from user
3. Checking if new requirements overlap with the FE-driven pivot feature (they may share UI patterns)

---

### 3.2 Analytics → Report Field-Selection Integration (Deferred)

**Context:** Analytics module already does field projection at the DB query level
(only selects needed columns). Standard reports fetch all columns and whitelist at display layer.

**Problem:** For large report views (MV_MMREGISTER — 100+ columns), the HTTP response from
`MREPORT.REPORTURI` carries all columns even when only 10 are needed for a pivot.
DataPivotEngine currently projects at intake (reduces memory) but the network transfer is wasteful.

**Full fix requires:**
1. Report URI endpoints (the targets of `MREPORT.REPORTURI`) to accept a `fields` query parameter
2. `ReportURICalling()` to pass the required field list when calling
3. Report service endpoints to build dynamic SELECT using the field list

**This is a cross-service architectural change** — it affects every microservice that exposes
a report URI endpoint. Needs:
- API contract design (`fields=Field1,Field2,...` query param, or POST body extension)
- Change to all report endpoint implementations
- Backward compatibility (no `fields` param = return all, as now)

**Scope:** Separate project, not part of report pivot. Start with one high-value report
(e.g. MV_MMREGISTER) as a pilot, then roll out pattern.

---

## Section 4 — Report Infrastructure (General)

### ~~4.1 ReportQB.GETREPORTVIEW Missing REPORTVIEWTYPE~~ ✅ DONE

Added `c.REPORTVIEWTYPE AS ReportViewType` to the `GETREPORTVIEW` SELECT.
`ReportViewDTO.ReportViewType` is now populated (confirmed the property already existed).

---

### ~~4.2 JsonReport — IAsyncEnumerable Not Serializable~~ ✅ DONE

Fixed in `CommonReportBLL.JsonReport()`: flat path now drains `IAsyncEnumerable` to
`List<Dictionary<string,object?>>` before calling `JsonConvert.SerializeObject`.
The pivot FE-driven path was already correctly draining when the guard was written.

---

### 4.3 Report View Admin Cache Invalidation

**Context:** `GET /CommonReport/GetPivotConfig` caches per ClientId+ReportViewId.

**Gap:** When an admin updates `MREPORTVIEWFIELDS` (changing DisplayType, AggregationType, field
order, etc.), the pivot config cache becomes stale.

**Fix location:** Not in FrameworkBLL — no INSERT/UPDATE on `MREPORTVIEWFIELDS` exists there.
The write is in a separate Report Admin module. When that module is identified/built, add:
```csharp
await _keyInvalidate.InvalidateAsync("OBJECTREPORTVIEW", login.ClientId, CacheKeyLevel.CLIENT_LEVEL);
```
after the successful save of a `MREPORTVIEWFIELDS` row.

---

## Section 5 — Quick Reference: Key Files

| Area | File |
|------|------|
| Pivot engine | `GB5Shared/Export/Pivot/DataPivotEngine.cs` |
| Pivot Excel | `GB5Shared/Export/Pivot/PivotExcelExport.cs` |
| Pivot PDF | `GB5Shared/Export/Pivot/PivotPdfExport.cs` |
| Pivot result DTO | `GB5Shared/Export/Pivot/PivotResultDTO.cs` |
| Pivot field config DTO | `GB5Shared/DTO/Report/PivotFieldConfigDTO.cs` |
| Report view meta DTO | `GB5Shared/DTO/Report/ReportViewMetaDTO.cs` |
| BLL routing guards | `GB5Framework/FrameworkBLL/CommonReport/CommonReportBLL.cs` |
| DAL pivot methods | `GB5Framework/FrameworkDAL/CustomCode/CommonReport/CommonReportDAL.cs` |
| New SQL queries | `GB5Framework/FrameworkDAL/Query/Report/ReportQB.cs` |
| GetPivotConfig endpoint | `GB5Framework/FrameworkSL/Endpoints/CommonReport/GetPivotConfig.cs` |
| DB migration | `DB/Migrations/Report_Pivot_Schema.sql` |
| HttpClient anti-pattern | `GB5Shared/ExcelExport/ExcelExport.cs` — `ReportURICalling()` |

---

## Section 6 — Priority Order for Next Sessions

All P1 and P2 BE items are now complete. Remaining work is FE, analytics, and infrastructure.

| Priority | Item | Effort | Status |
|----------|------|--------|--------|
| ~~**P1**~~ | ~~Fix IAsyncEnumerable serialization bug (§4.2)~~ | Small | ✅ Done |
| ~~**P1**~~ | ~~Check TENANTID filter (§1.5)~~ | Small | ✅ N/A |
| ~~**P1**~~ | ~~Module ICommonReportBLL impls (§1.3)~~ | Small | ✅ N/A |
| ~~**P2**~~ | ~~Fix HttpClient anti-pattern (§1.1)~~ | Medium | ✅ Done |
| ~~**P2**~~ | ~~Fix PivotPdfExport shared browser (§1.2)~~ | Small | ✅ Done |
| ~~**P2**~~ | ~~Create pivot-report.html template (§1.4)~~ | Medium | ✅ Done |
| ~~**P2**~~ | ~~Fix GetPivotConfig cache key (§1.6)~~ | Small | ✅ Done |
| **P3** | FE pivot component — BE DataPivot grid mode (§2.1) | Large | Pending |
| **P3** | FE pivot component — FE-driven drag-drop mode (§2.2) | Large | Pending |
| **P3** | Analytics new requirements gathering (§3.1) | Planning | Pending |
| **P4** | Report view admin cache invalidation (§4.3) | Small | Pending — needs admin module location |
| ~~**P4**~~ | ~~ReportQB.GETREPORTVIEW add REPORTVIEWTYPE column (§4.1)~~ | Small | ✅ Done |
| **P4** | GetReportViewMeta caching (§1.7) | Small | Pending |
| **P4** | Streaming JSON deserialization for large reports (§1.8) | Large | Pending |
| **P4** | Field-selection parameter for report URIs (§3.2) | Very Large | Pending |