# Sales Analytics Dashboard — Developer & Rollout Guide

Audience: a developer extending this feature or rolling the Sales Overview Dashboard out to a
client beyond GB5DEMO. For the end-user-facing "how do I use it" doc, see
`SalesDashboards-User-Guide.md`. For the detailed, gotcha-by-gotcha build recipe this doc
summarizes, see `PortletDashboardFramework/PortletDashboardFramework_ModuleRecipe.md`.

Sales is the third functional area built on GB5's Portlet/Page/Dashboard framework, after Finance
and Procurement (Inventory shipped the same week). It follows the exact same recipe: inventory
what already exists, decide bridgeable vs. bespoke per report, wire the
`MWEBSERVICE → MREPORT → MMENU → MPORTLET → MROLEVSPORTLET` chain plus `MROLEVSMENU` grants, add
drilldown only where a real target exists.

## 1. Architecture headline — Sales has no dedicated module

GB5 has no `SalesOrder`/`SalesInvoice`/`SalesQuotation`/`SalesEnquiry` tables. All of it is the
generic `MMHead`/`MMDetail` document under **MM**, differentiated by `BizTransactionClassId`:

| BizTransactionClassId | Code | Name |
|---|---|---|
| -1799999907 | SALESENQ | Sales Enquiry |
| -1799999906 | SALESQUO | Sales Quotation |
| -1799999905 | SALESORD | Sales Order |
| -1799999904 | SALESINVC | Sales Invoice |
| -1799999903 | SALESRET | Sales Return |

All under `MODULEID = -1899999994` (Sales), a sibling of Purchase (`-1899999995`). This is the same
pattern Procurement already used for PR/PO reporting via the generic `MMReportsBLL`/`MMReportsDAL`
endpoints (`GetMMPendingStatusReport`, `GetMMRegisterPartyWiseSummaryReport`,
`GetMMRegisterItemWiseReport`) — those endpoints are reused verbatim for Sales, parameterized with
the Sales class IDs above instead of Procurement's.

`GetPRToPOTrackerDashboard`'s funnel logic (`GetFunnelStage` → `GET_PENDING_COUNT_BY_TRANSACTIONCLASS`
+ `GET_PR_AGING_BUCKETS`) is also fully generic — confirmed by reading the actual SQL, which filters
only on `@BizTransactionClassId`, nothing procurement-specific. `GetSalesFunnelDashboard`
(`GB5Solution/MM/MMSL/EndPoints/MMReports/GetSalesFunnelDashboard.cs`) mirrors it 1:1 for Sales
Enquiry→Quotation→Order.

## 2. MM, CRM, and Marketing are all on BusinessHost — unlike Finance

`GB5Solution/Hosts/Business/BusinessHost/BusinessHost.csproj` references `MMSL`, `CRMSL`,
`MarketingSL`, `AnalyticsSL` — the same host hardcoded as `GB5_SELF_HOST` (port 5102) for the BI
Catalog `ApiService` bridge. Accounts (Receivable, Collection) is on a different host,
**HRFinanceHost**. This only matters for the BI Catalog bridge (same-host-only); it does **not**
block reusing Accounts' Receivable/Collection reports as "Report"-type portlets on the Sales
dashboard — that path works cross-host, per the recipe.

**Bridgeability check, done not assumed:** a live query against GB5DEMO's `MREPORTVIEW`/`MREPORT`/
`MWEBSERVICE` for every Sales-keyword-matching report (346 view rows / 90 distinct reports) found
`ISDATAPIVOT=1` on all but one, all 90 routed through legacy GB4 `.svc` paths, and only 2 of 90 have
`SECONDURITEMPLATE` populated — and those 2 are Finance's own reports, not genuine Sales reports.
**Every Sales tile in this rollout uses the "Report"-type portlet path — none attempt the BI
Catalog bridge.**

## 3. Endpoint contract — same non-negotiable rule as Finance/Procurement

Every "Report"-type portlet endpoint accepts `[FromBody] ReportCallingDTO` as its entire POST body.
`GetSalesSummaryDashboard` and `GetOUWiseMonthlyGSTDashboard` predate this dashboard and take
`[FromBody] CriteriaDTO` directly — a thin `ReportCallingDTO`-accepting wrapper was added for each
(`PostSalesSummaryDashboard.cs`, `PostOUWiseMonthlyGSTDashboard.cs`), same pattern as
`PostFilingStatusSummaryForDashboard`/`LoadBRSForDrilldown`. The underlying BLL/DAL logic is
untouched — this is presentation wiring only.

## 4. Tiles shipped in Phase 1

| # | Tile | Endpoint | Source |
|---|---|---|---|
| 1 | Sales Summary (KPI + OU breakdown, incl. Returns) | `POST /DutyTable/SalesSummaryDashboardForDashboard` | Wrapped existing `GetSalesSummaryDashboard` |
| 2 | Sales Enquiry → Quotation → Order Funnel | `POST /MMReports/GetSalesFunnelDashboard` | New, mirrors `GetPRToPOTrackerDashboard` |
| 3 | Sales Invoice / GST Dashboard | `POST /Register/OUWiseMonthlyGSTDashboardForDashboard` | Wrapped existing `GetOUWiseMonthlyGSTDashboard` |
| 4 | Sales Register — Party Wise Summary | `POST /MMReports/GetMMRegisterPartyWiseSummaryReport` | Reused generic endpoint, Sales class IDs |
| 5 | Sales Register — Item Wise Report | `POST /MMReports/GetMMRegisterItemWiseReport` | Reused generic endpoint, Sales class IDs |
| 6 | Sales Pending Status Report | `POST /MMReports/GetMMPendingStatusReport` | Reused generic endpoint, Sales class IDs |
| 7 | Receivable Dashboard | (Finance's existing `AccountReceivableDashboardReport`) | Reused — same `MREPORT`, second placement |
| 8 | Collection Projection | (Finance's existing `AccountCollectionProjectionReport`) | Reused — same `MREPORT`, second placement |

Plus two pre-existing legacy portlets ("Sales Summary" / `PERWSMARSU`, "Sales Trend Analysis" /
`SALESTREND`, wired to the legacy "Sales Analysis Summary" / `Register/MarginSummary` report) are
re-homed onto the new dashboard page — they already existed, wired, but sat on no dashboard page.

Migration files:
- `DB/Migrations/20260902_SalesOverviewDashboard_Full_SqlServer.sql` — dashboard/page/portlet wiring
- `DB/Migrations/20260902_FSale_PostingRule_Seed_SqlServer.sql` — FSALE DW posting rules (parallel track, below)

**Postgres:** deliberately not produced for this rollout — matching the actual precedent set by
Procurement's and Inventory's own dashboard migrations (neither has a Postgres twin either),
diverging only from Finance's. Author one when a Postgres target actually needs this dashboard.

## 5. The parallel track — FSALE Data Warehouse posting

FSALE already exists as `MWAREHOUSEFACT.FactId=1` (44 physical columns, 15 already-cataloged
`MWAREHOUSEMEASURE` rows, 6,384 rows of frozen legacy data ported byte-for-byte from GB4's own
warehouse) but had **zero posting rules** — nothing populated it going forward. This rollout adds
posting rules for the 8 of those 15 measures that fit the generic engine's `SOURCETYPE` contracts:

| Measure | Rule shape |
|---|---|
| SALESQUANTITY, SALESVALUE, COGS | `SOURCETYPE=2` over a new view (`VSALESDETAILFORWAREHOUSE`), filtered `BizTransactionClassId=SALESINVC` |
| NETSALESVALUE | Two `SOURCETYPE=2` rules on the same measure (Invoice Add, Return Subtract) — the engine's `CombinationMode` mechanism |
| GROSSMARGINVALUE | `SOURCETYPE=1` derived formula: `SALESVALUE-COGS` |
| NUMBEROFBILLS, NUMBEROFORDERS, ORDERVALUE | `SOURCETYPE=2` over a header-level view (`VSALESHEADERFORWAREHOUSE`), `SUM(ONE)` for counts |

Both helper views bake the `BizTransactionClassId` filter (via a join to `MBIZTRANSACTIONTYPE`)
into the source, since `SOURCETYPE=2`'s contract is a single table + one equality `FILTERJSON` — it
cannot join. `WarehouseFactPostingDAL.ValidateIdentifier` only regex-validates the identifier
string; it never checks `sys.tables`, so a plain SQL view works exactly like a physical table.

**Deliberately not posted** (see Known Limitations below): `NUMBEROFCUSTOMERS` (needs
`COUNT(DISTINCT PartyId)`, the engine only `SUM`s), `COLLECTIONVALUE`/`RECEIVABLEVALUE`/
`ODRECEIVABLEVALUE` (Accounts-side, not derivable from MM), `AVERAGEBILLVALUE`/`MINBILLVALUE`/
`MAXBILLVALUE` (already-aggregate measures, the engine has no MIN/MAX/AVG source type).

Column mapping was confirmed against a surviving legacy view (`VMMDATEITEMCLASSSUMMARY`, feeding
sibling fact `FITEMCLASS`) that computes the same measure family from `TMMHEAD`/`TMMDETAIL` — not
guessed.

**Code-side change**: `MMHeadBLL.PublishSalesPostedEventAsync` (mirrors the existing
`PublishStockPostedEventAsync` → `"mm.stock.posted"` pattern exactly) fires `"mm.sales.posted"` from
both the pending-allocation branch (covers Enquiry/Quotation/Order) and the stock-posting branch
(covers Invoice/Return) of `SaveMMHeadIncrementalAsync`/`SaveBatchAsync`. `WarehouseChangeSubscriber`
gets one new `[Topic("pubsub","mm.sales.posted")]` handler
(`HandleMMSalesPostedAsync` → `Accounts/Subscribe/MMSalesPosted`) that reuses the exact same
`EnqueueVoucherChangeAsync` call the `mm.stock.posted`/`accounting.voucher.saved` handlers already
use — that method is already fact-generic (loops every `MWAREHOUSEFACT` with `POSTINGMODE=1`), so
no new `AccountsBLL` code was needed. `POSTINGMODE` for `FactId=1` is set to `1` (delta) to match
FINVENTORY's own choice.

**Known engine gap worth knowing:** `WarehousePostingRuleBLL.ValidateRuleAsync` only supports
`SOURCETYPE` 0/1 through the admin API — `SOURCETYPE=2` rules (all of the above) had to be inserted
via the migration directly, same as FINVENTORY's did. No admin-UI authoring path exists for these
yet.

**Not yet live-verified.** Trigger a save on a real Sales document, confirm `TWAREHOUSECHANGEQUEUE`
gets a row, confirm the drain cron (or a manual delta-process call) writes a new `FSALE` row with
sane values, cross-check against the OLTP wrapper tile's own numbers for the same OU/date, before
trusting this track on any target.

## 6. Extending — add a new tile, or push the deferred items into a Phase 2

1. **Inventory existing reports first** (as this rollout did) — most of a functional area's report
   logic already exists; the gap is almost always presentation wiring.
2. **Decide the rendering path** — default to "Report"-type portlet; only reach for the BI Catalog
   bridge after confirming a report view is flat (`ISDATAPIVOT=0`) *and* served by BusinessHost.
3. **Fix the endpoint contract** if needed — a thin `ReportCallingDTO` wrapper, not a rewrite.
4. **Wire the chain** — `MWEBSERVICE→MREPORT→MMENU→MPORTLET→MROLEVSPORTLET`, plus `MROLEVSMENU` for
   every role — the step most likely to be silently skipped (a menu with zero `MROLEVSMENU` rows
   for the viewing role returns an empty array, not an error).
5. **Verify** per §7 below before calling it done.

## 7. Verification checklist

- [ ] `dotnet build` clean on `MMSL`/`MMBLL`/`MMDAL`, `AccountsSL` (for the subscriber change), and
      the actual serving hosts — `BusinessHost` and `HRFinanceHost` — not just the module projects.
      **Done this session**: all four build clean, zero errors.
- [ ] Live HTTP call to every new/wrapped endpoint directly.
- [ ] `GET /Menu/ReportMenuDetailsForMenu?MenuId=<id>&UserId=<real-user>&IsFromScreen=0` for every
      new menu (portlet targets + the left-nav dashboard entry) — confirms `MROLEVSMENU` rights
      resolve, not just that the endpoint itself returns data.
- [ ] `GET /Portlet/GetPortlet?PortletId=<id>` for each new/re-homed portlet.
- [ ] Dashboard still editable afterward (add/remove/reorder via existing admin screens).
- [ ] A real browser click-through on GB5DEMO: open from left nav, confirm every tile renders real
      data (Sales Enquiry/Quotation/Order tiles should show non-zero counts — 24/25/21 live header
      rows respectively were confirmed this session), Export/Print work, Receivable/Collection
      tiles render despite being cross-host.
- [ ] For the FSALE track specifically: trigger a Sales document save, confirm the delta queue and
      a fresh `FSALE` row, cross-check against the OLTP tile.

**None of the above live/browser verification has been done yet this session** — only `dotnet build`
across all four touched projects/hosts. Treat browser click-through as the final gate before calling
any part of this rollout complete, exactly as the Finance rollout's own guide insists.

## 8. Known limitations (honest, as of this write-up)

- **Target vs Actual KPI** — `MTARGET`/`MTARGETDATA`/`MTARGETDATADETAIL`/`MTARGETSHARE` are a real,
  fairly rich target-definition schema (splittable by Item/Category/SubCategory/Brand/Party/
  Employee/Department/CostCenter) already in the base schema, but **zero C# code anywhere**
  references `MTARGETDATA`/`MTARGETDATADETAIL`/`MTARGETSHARE` — not even read logic exists yet, let
  alone an actual-vs-target computation. Comparable in size to Finance's Bank Reconciliation
  Phase 2, not a quick wrapper. Deferred.
- **Product/Customer/Salesperson/OU-hierarchy dimension breakdowns** — deferred. This needs real
  engine work: reviving the legacy `FITEMPARTY` table (item×customer×OU×day grain, physically
  present but explicitly excluded from the original DW port), building the missing `DIMITEM`/
  `DIMPARTY` ETL (no populate query exists for either, unlike `DIMOU`'s working
  `WarehouseDimQB.POPULATE_DIMOU`), and extending `WarehouseFactPostingDAL`'s `ModuleAggregation`
  path past its current one-row-per-(OU,date) contract to group by item/party too.
- **Stage-to-stage conversion rate %** (e.g. Quotation win-rate) — not computed anywhere; only
  open-per-stage counts exist. The SQL pattern (a `PreDocumentId` lineage self-join) exists as a
  template in `GET_PR_TO_PO_CYCLE_TIME_TREND` but isn't wired for Sales.
- **Value/spend trend and cycle-time-in-days** — depends on `MV_MMRegister`, confirmed **empty (0
  rows)** on GB5DEMO for Sales *and* Purchase alike. A pre-existing environment gap already open on
  the Procurement dashboard, not Sales-specific.
- **FSALE's Collection/Receivable/OD-Receivable measures** — not derivable from MM; need a separate
  Accounts-side posting-rule scoping pass (`TVOUCHER`/`PartyOUQB.cs`), not guessed at in this round.
- **Sales Invoice item/customer-level detail** — data is thin on GB5DEMO (2 header rows) so this
  wasn't prioritized this round.
- **CRM Lead → Sales conversion analysis** — `TMMHEAD.LEADID`/`TMMDETAIL.LEADID` are real,
  FK-enforced columns (`→ TLEAD`), exposed through MM's DTO/QueryBuilder/BLL layers, but a live
  check across **14 reachable client databases** (GB5DEMO + 13 others, up to 41,850 `TMMHEAD` rows
  on `elkayem`) found **0% population** — every row is the `-1` sentinel, everywhere. No application
  code writes this field when an Enquiry/Order is created from a Lead. Not buildable on real data
  today; activating the write-path is separate future work.
- **Marketing's dead `EnquiryDTO`/`LeadStageSummary*` Lead-pipeline DTOs** — a separate, unrelated,
  still-dead concept (Marketing/Lead module, not MM's Sales Enquiry `BizTransactionClass`). Not
  touched, not to be confused with genuine Sales Enquiry.
- **The broken `GB5AWDGT1` "Sales by OU (Pilot)" portlet** — a stray row on GB5DEMO wired to the
  wrong menu (`Leave Request`/ESS). Flagged, not fixed, since it wasn't blocking this rollout.
- **No self-service "assign this dashboard to a role" screen** — same gap Finance already
  documented; done via direct `MROLEVSDASHBOARD` inserts today.