# PRD — bitool_dotnet

Evidence date: 2026-10-19. Source: `git@bitbucket.org:overdrive/bitool_dotnet.git`.

Default branch `master` is `b958e154` (2023-11-15). It does not contain the dashboard. The requirements below are **Implemented Behaviour** on `develop` at `2223f18b` (2024-02-22) unless a row says otherwise.

Silicon Ark had no prior PRD in `bitool_dotnet/`. The source repository has no README and no requirements document. UI copy, route names, and SQL are the evidence.

`bitool_frontend/prd.md` describes a later, separate frontend repository with the same package name ("SAWIS BI") and overlapping endpoint names. It is not this repository.

## Product purpose

**Inferred.** A visitor opens a SAWIS export report, chooses a time window and optional filters, and sees litre totals, charts, and tables for wine exports.

**Documented** only as labels: HTML title "SAWIS Report", package name "SAWIS BI" `0.1.0`, contact address `bi@sawis.co.za`.

On `master` the purpose cannot be read from behaviour. The API starts, registers an empty chart service, and exposes no actions.

## Users

| Actor | What the code allows | Provenance |
|---|---|---|
| Anonymous visitor | Open the single page, change filters, read charts and tables, download a table as `.xlsx` | Implemented on `develop`. No login screen |
| API caller | Call the same routes without a token | Implemented. `Program.cs` calls `UseAuthorization` and does not call `UseAuthentication` or `AddAuthentication` |
| Operator | Not represented | Unknown. No admin UI |

There are no roles.

## FR-001 — Open the report

Requirement: A visitor loads one page. The page shows a banner, a contact link, a filter sidebar, and two tabs: Overview and Tables.

Who: any visitor.

Trigger: browser navigation to the SPA index route.

Inputs: none.

Expected behaviour:

- `AppRoutes.js` renders `Home` at the index route. Counter and Fetch Data routes from the ASP.NET React template are commented out.
- `NavMenu.js` shows `banner.jpg` and a mailto link `bi@sawis.co.za` labelled "Contact".
- `Home.js` starts on the Overview tab. The Tables button switches the visible panel. Both panels stay mounted; the inactive one has class `d-none`.
- The sidebar can be slid off-screen (`Sidebar.js`, width `15vw`).

Outputs: the dashboard shell.

Validation: none.

Errors: if JavaScript is disabled, `index.html` shows "You need to enable JavaScript to run this app."

Evidence: `Home.js`, `NavMenu.js`, `AppRoutes.js`, `index.html`.

Implementation: Implemented on `develop`. Absent on `master`.

Confidence: High.

## FR-002 — Load filter options

Requirement: On first paint the sidebar loads filter options from the API and selects the latest year and month in the fact table, with a 12-month reporting period.

Who: the page, on mount.

Trigger: `Sidebar` `useEffect` calls `getAvailableFilters`.

Inputs: none. HTTP GET.

Expected behaviour:

- Request URL is `https://biadmin.sawisonline.co.za/api/controller/getFilters`. `https://localhost:7003/api/controller/getFilters` is commented out.
- The API action is `FilterController.GetFilters`, route `api/controller/getFilters`.
- The response model includes year names, month names, periods 1 through 12, product types, countries, package types `BULK` and `PACKAGED`, cultivar descriptions, the latest year and month, and `VersionAPI` set to `"1.0"`.
- The store selects `LatestYearMonth[0].LatestYear`, the month name at index `LatestMonth - 1` after the client sorts month names, and reporting period `12`.
- Product type, country, package type, and cultivar start with an empty selection.

Outputs: sidebar controls populated.

Validation: none on the request.

Errors: a failed fetch logs `error.message` and leaves filters empty. `getReportingData` returns immediately when `filters` has no keys.

Evidence: `filterStore.js`, `FilterController.cs`, `FilterService.cs`.

Implementation: Implemented on `develop`.

Confidence: High for the request and the SQL. The live host was not called.

## FR-003 — Apply filters and refresh every widget

Requirement: Submitting the sidebar sends one filter payload to every chart and table endpoint and replaces the displayed data.

Who: the visitor.

Trigger: sidebar form submit, and also any change to the `filters` object (`Home.js` `useEffect`). Selecting or clearing all countries calls `updateFilters` directly.

Inputs: JSON body:

| Field | Source |
|---|---|
| `year` | integer parsed from the Year control |
| `month` | integer parsed from the start of the Ending Month label (`"01 - January"` yields `1`) |
| `period` | reporting-period slider, default 12, range 1 to 12 |
| `productTypeFilterList` | `{ whiteOrRed }` for each selected product type. Empty array when none are selected or when every option is selected |
| `countryFilterList` | `{ engelseLandBeskrywing }`. Same empty-when-all rule |
| `packageTypeFilterList` | `{ packageType }` of `BULK` and/or `PACKAGED`. Same empty-when-all rule |
| `cultivarFilterList` | `{ descriptionForReports }`. Same empty-when-all rule |

Expected behaviour:

- Base URL `https://biadmin.sawisonline.co.za/api/controller`.
- Eleven POSTs fire together. Each sets its own loading flag and clears it in `finally`.
- A non-OK HTTP response is not checked. `res.json()` runs on the body that arrives. Failures log to the console.

Outputs: Zustand fields `data`, `yearByYear`, `totalLitres`, `totalExports`, `cultivarChartData`, `top25Data`, and the five table arrays.

Evidence: `filterStore.js` `getReportingData`.

Implementation: Implemented on `develop`.

Confidence: High.

## FR-004 — Reporting window

Requirement: Queries use a closed month window of `period` months ending at the selected year and month. If the selected year is the latest year in the fact table and the selected month is after the latest month, the end month is the latest month instead.

Who: the API, on every chart and table query that builds a period.

Trigger: a POST body with `Year`, `Month`, and `Period`.

Inputs: those three integers, plus the latest `(year, month)` from `SELECT year, month FROM {table} ORDER BY period DESC LIMIT 1`.

Expected behaviour:

- End date is the last calendar day of the end month.
- Start date is the first day of the month `(Period - 1)` months before that end date.
- SQL bounds are strings `yyyyMM`, compared with `period BETWEEN start AND end`.
- The response label on most queries is `MMM yyyy - MMM yyyy`. The exports series uses `MMM yy`.

Outputs: filtered litre sums.

Validation: none. `DateTime.DaysInMonth` throws if `Month` is outside 1–12 or if there is no latest row (`FirstOrDefault()` then property access). The controller returns HTTP 400 with `ex.Message`.

Evidence: the date block copied at the start of `GetChartData`, `GetCultivarChartData`, `GetYearOverYearComparison`, `GetExports`, `GetTOP25Data`, `GetTotalLitresHelper`, and the matching methods in `TableDataService.cs`.

Implementation: Implemented on `develop`. **Inferred** that `period` in the table is `yyyyMM`. The column is compared to that shape. No schema file confirms the type.

Confidence: High for the algorithm. Medium for the column type.

## FR-005 — Overview totals

Requirement: The overview shows three litre figures and a year-on-year percentage for all exports, packaged exports, and bulk exports.

Who: the visitor, after filters load.

Trigger: POST `api/controller/getTotalLitres`.

Expected behaviour:

- Litres are `ROUND(SUM(totale_liters), 0)` over the reporting window.
- Packaged rows are `bottels_bulk = 'BOTTELS'`. Bulk rows are `bottels_bulk = 'BULK'`.
- Percentage is `(current - previous) / previous * 100`, rounded to 2 decimal places. If the previous total is 0, the percentage is 0.
- The previous window is the same span shifted one year earlier.
- `Overview.jsx` renders the three figures with glass, bottle, and barrel icons (`BIOverview.jsx`).

Outputs: `YearTotalLitresModel`.

Evidence: `GetTotalLitres`, `GetTotalLitresData`, `BIOverview.jsx`.

Implementation: Implemented on `develop`.

Confidence: High.

## FR-006 — Overview charts

Requirement: The overview shows five charts. Headings include the selected package type, or the text `PACKAGED/BULK` when zero or two package types are selected.

| UI title | POST route | What the query groups |
|---|---|---|
| Export market per product type (L) | `getChartData` | `white_or_red`. Pie chart |
| Exports (L) | `getExports` | bulk and packaged series, current window and nine earlier years (ten windows) |
| Cultivar / Type Exported (L) | `getCultivarChartData` | `description_for_reports` |
| Export market volume by product type (L) | `getYearOverYearComparison` | `white_or_red` for the current window and the previous-year window |
| Top Export Countries as Selected | `getTOP25Data` | `sawis_landnaam`, plus a row `OTHER COUNTRIES` |

Package-type flags on `getChartData` and `getTOP25Data` when the package list is empty or has both values: `BB = 'X'`. Packaged only: `BO = 'X'`. Bulk only: `BU = 'X'`. The complement `(flag != 'X' OR flag IS NULL)` is summed as `OTHER COUNTRIES` on the top-countries query.

`getExports` does not use `BB` / `BO` / `BU`. It filters `bottels_bulk`.

`getCultivarChartData` passes the method name into `ApplyFilters`, which skips the product-type and cultivar `IN` clauses for that call. Country and package-type clauses still apply.

Charts are ECharts options built in `Overview.jsx` and drawn by `BIChart.jsx`. Numbers at or above one thousand are shortened (K, M, B). Table-style tooltips use `fr-FR` grouping.

A chart header action calls `changeTab`, which switches the page to the Tables tab. `BIChart.jsx` names this `goToGraph`.

Evidence: `ChartDataController.cs`, `ChartDataService.cs`, `Overview.jsx`, `BIChart.jsx`, `helpers.js`.

Implementation: Implemented on `develop`.

Confidence: High.

The method is named TOP 25 in comments and in `getTOP25Data`. The SQL has no `LIMIT 25` and no `ORDER BY` before a limit. It returns every grouped country that matches the flag, plus `OTHER COUNTRIES`. **Inferred** from the SQL: the "25" is a label, not a row cap.

## FR-007 — Tables and Excel download

Requirement: The Tables tab shows five pivot tables. Each can be downloaded as an `.xlsx` file named from the heading.

| Heading | POST route | Row | Column | Metric |
|---|---|---|---|---|
| Export market per product type (L) | `tables/getExportMarketPerProductTypeTableData` | Country `Sawis_Landnaam` | Cultivar `Description_For_Reports` | `Totale_Litres` |
| Exports (L) | `tables/getExportsTableData` | Package type | Period | `Total_Litres` |
| Cultivar / Type Exported (L) | `tables/getCultivarTypeExportsTableData` | Product type `Description_For_Reports` | Period | `Total_Litres` |
| Export market volume by product type (L) | `tables/getExportMarketVolumeByProductTypeTableData` | Reporting period | Product type `White_Or_Red` | `Total_Litres` |
| Top Export Countries as countries selected (L) | `tables/getTOP25TableData` | Country | Product type | `Total_Litres` |

`PivotTable.jsx` builds the grid in the browser and writes the workbook with the `xlsx` package loaded from the SheetJS CDN tarball in `package.json`.

Evidence: `TableDataController.cs`, `TableDataService.cs`, `Tables.jsx`, `PivotTable.jsx`.

Implementation: Implemented on `develop`.

Confidence: High for the routes and headings. The Excel file bytes were not generated in this investigation.

## FR-008 — Shared filter clauses

Requirement: When a filter list is non-empty, the SQL adds an `IN` list. An empty list adds no clause for that dimension.

| List | Column | Notes |
|---|---|---|
| Product type | `white_or_red` | Skipped when the caller passes method `GetCultivarChartData` |
| Country | `sawis_landnaam` | Values are the English country description from the client |
| Package type | `bottels_bulk` | `PACKAGED` is sent to SQL as `'BOTTELS'`. Anything else in the list is sent as `'BULK'` |
| Cultivar | `description_for_reports` | Skipped when the caller passes method `GetCultivarChartData` |

`ChartDataService` chart methods other than `GetCultivarChartData` pass `""` as the method name, so those skips do not run. `GetTotalLitresHelper` sets a local variable `method = "GetTotalLitresHelper"` and then calls `ApplyFilters` with `""`, so the package-type clause is still appended. The same helper's own SQL also splits packaged and bulk with `bottels_bulk`. **Inferred:** a selected package type can constrain the "ALL" total as well as the split totals. This was not executed.

`TableDataService` has a second copy of `ApplyFilters` and always passes `""`.

Evidence: `ChartDataService.ApplyFilters`, `TableDataService.ApplyFilters`.

Implementation: Implemented on `develop`.

Confidence: High.

## FR-009 — API errors and documents

Requirement: A thrown exception on these actions becomes HTTP 400 with the exception message as the body. There is no other error body.

Swagger is registered as document `v1`, title `sawis_bi_tool.API`. The UI is mapped only when the host environment is Development.

CORS policy `AllowAnyOrigin` allows any origin, header, and method, and is applied for every environment.

Evidence: each controller `catch`, `Program.cs` on `develop`.

Implementation: Implemented on `develop`. `master` `Program.cs` registers Swagger twice (once before `Build`, and again on `builder.Services` after `Build`) and has no CORS policy.

Confidence: High.

## FR-010 — What `master` does

Requirement: `master` starts an ASP.NET Core app that maps controllers and registers `IChartDataService`. No controller class exists. The service methods are empty regions.

This is the default branch. It is not the dashboard described in FR-001 through FR-009.

Evidence: `master` tree at `b958e154`.

Implementation: Implemented.

Confidence: High.

## Integrations

| System | Purpose | Trigger | Direction | Auth | Response | Failure |
|---|---|---|---|---|---|---|
| MySQL via connection string `SAWIS` | Fact and lookup data | Each service method | Read | The connection string. Value not in Git | Row sets mapped to the C# models | Exception message returned as HTTP 400 |
| `https://biadmin.sawisonline.co.za` | Browser calls this host | Page load and filter apply | Browser to that host | None in the client | JSON body | `console.error`. Widgets stay at the previous value or null |
| `https://www.sawisonline.co.za//images/icon.ico` | Favicon | Page load | Browser GET | None | Icon | Browser default icon. Not handled in code |
| Google Analytics `G-1RFV9ZJG9K` | Page analytics | 3 seconds after load | Browser to Google | Measurement id in source | `ga-4-react` initialise | `console.error` |
| `mailto:bi@sawis.co.za` | Contact | Click | Mail client | n/a | n/a | Depends on the mail client |
| SonarQube | Pipeline analysis | Push to `master` or `develop`, if pipelines run | CI to SonarQube | `SONAR_TOKEN`, `SONAR_HOST_URL` | Scanner logs | Pipeline failure. Not visible from Git alone |
| SheetJS CDN tarball | Excel library at install time | `npm install` | npm to `cdn.sheetjs.com` | None | Package `xlsx` 0.20.0 | Install failure |

## Constraints

- Target framework `net7.0` on the API, the class library, and the SPA host.
- Active table name in source is `export_data_qa`. The live name `export_data` is commented in three services.
- Product-type and cultivar lookups are limited to `prd_kde` in `('1', '2', '3A', '3B', '3C', '3D', '4', '5', '6', '7', '8', '9A', '9B', '9C', '9D', '10', '11A', '11B', '36', '90')` against `product_type_lookup`.
- Package types offered to the user are the two hard-coded strings `BULK` and `PACKAGED`, not a query.
- Reporting periods offered are the integers 1 through 12, not a query.
- No authorisation on the API.
- Controllers return `Ok(JsonConvert.SerializeObject(...))`. The host also uses the default JSON formatter. Whether the browser receives one JSON document or a JSON string wrapped again was not executed. **Requires Confirmation.** The client calls `res.json()` once and then reads properties.

## Assumptions that are not requirements

- That `develop` is the product branch. It holds the implementation. `master` is the default branch and does not.
- That `biadmin.sawisonline.co.za` is this codebase. The client names that host. The repository does not prove the deployment.
- That `bitool_frontend` is the successor UI. The names and routes overlap. The repositories are separate.