# PRD — prsa_cio

Evidence date: 2026-10-06.

Source: `git@bitbucket.org:overdrive/prsa_cio.git`, `master` at `2832986` (2023-09-20).

There is no prior PRD in Silicon Ark and no `README` in the source repository. Requirements below are recovered from the solution, Razor pages, API, service, EF models, pipeline, and Git history. Provenance is labelled.

No secrets are committed. Connection strings are gitignored and are not reproduced because they are not in the tree.

## Product purpose

**Implemented name.** The solution is `prsa_cio`. The login chrome says "PRSA - CIO". The home page says "Reporting System".

**Implemented behaviour.** A person can open a page that offers an Excel file picker and four tabs (Tracker, SalesForecast, StockBalance, QlikviewSales). Submitting a non-empty file sets a success message and does not read the workbook. Separately, an API can open an Excel file from a path on the API host and append rows from the first worksheet into `Imports.tracker`, and can return tracker rows as JSON.

**Unknown / Requires Confirmation:** the business purpose of PRSA CIO, who operates it, and whether the reporting page was meant to become the front end of the tracker API.

## Users and roles

No role is enforced in code.

| Actor | Evidence | Status |
|---|---|---|
| Anonymous browser | `launchSettings.json` sets `anonymousAuthentication: true` and `windowsAuthentication: false` on both hosts. No `[Authorize]`. No cookie or JWT setup. | **Implemented** |
| Person who uses Sign In | Email and password inputs have no `name`. Button `formaction` is `/Index`. `LoginModel` has `OnGet` only. | **Implemented** markup. Effect of posting to `/Index` was not executed. The reporting page route is `/`, not `/Index`. |
| Person who uploads on `/` | `IndexModel.OnPostAsync` | **Implemented** |
| Caller of the API | No identity is read. `created_by` and `updated_by` are the literal `1`. | **Implemented** |
| Row in `Core.users` | Columns include `username`, `email`, `ad_token`, `role_id`, `active` | **Implemented** schema only. No query uses `users`. |

`role_id` has no `roles` table in the EF model. What it points at is **Unknown / Requires Confirmation**.

`ad_token` suggests a directory token. No Active Directory, LDAP, or OAuth code exists. Whether sign-in was planned to use Active Directory is **Unknown / Requires Confirmation**.

## Systems the product talks to

| System | Direction | Evidence | Status |
|---|---|---|---|
| SQL Server | App and API each register `PRSAContext` with connection string name `PRSA` | `Program.cs` in both hosts | **Implemented** registration. The string value is not in Git. |
| Microsoft Excel | API host opens a workbook with COM interop | `FileUploadService` | **Implemented**. Requires Excel on that machine. Not stated as a documented requirement. |
| SonarQube | Pipeline sends analysis | `bitbucket-pipelines.yml` | **Implemented** pipeline step. Host URL comes from `SONAR_HOST_URL`. |
| DataTables, Bootstrap, jQuery CDNs | Browser loads scripts on `/` | `Index.cshtml` | **Implemented** |
| QlikView | Table name `Imports.qlikview_sales` | EF model only | **Unknown**. No client, file drop, or job reads or writes that table. |

EF Power Tools `UiHint` is `nb-dewald.prsa_cio.dbo`. Provenance: **Documented** inside `src/dal/PRSA.DAL/efpt.config.json` as the design-time source hint. It is not proof of the runtime server.

## Major workflows

### Open the reporting page

1. Browser requests `/`.
2. `IndexModel.OnGet` sets `SheetNames` to `Tracker`, `SalesForecast`, `StockBalance`, `QlikviewSales` and the title to "Reporting System".
3. The page renders one empty DataTable per name and a file input `accept=".xlsx"`.
4. The DataTables constructor in the page script is commented out, so the tables are not initialised and no AJAX load runs.

Provenance: **Implemented** in `src/app/PRSA.App/Pages/Index.cshtml` and `Index.cshtml.cs`.

### Upload a file in the browser

1. The form posts multipart data to the same page.
2. Tag helpers are registered in `_ViewImports.cshtml`, so the form includes an antiforgery token.
3. If `File` is non-null and `Length > 0`, `UploadSuccess` is set to true. The stream is not copied, and no service or API is called.
4. If the file is missing or empty, neither success nor error is set.
5. The `catch` sets `UploadError`. The `try` body cannot throw, so that branch is unreachable as written.
6. The page is returned with the same four hard-coded tabs.

Provenance: **Implemented**. Comments in `OnPostAsync` say the body is for demonstration and that processing would be added later. Those comments are the only statement of intent.

### Sign in

1. Browser requests `/Login`.
2. The page shows email and password boxes and a Sign In button.
3. Remember-me, Facebook, Google, forgot-password, and register blocks are commented out.
4. Sign In posts to `/Index`.

Provenance: **Implemented** markup. No credential check exists. Whether `/Index` is a reachable route was not run; no `@page` directive uses that path.

### Import a tracker workbook through the API

1. Caller sends `POST api/FileUpload/uploadFile` with a `filePath` string (a path the API process can open, not a multipart file).
2. `FileUploadService.UploadTrackerSheet` starts Excel, opens the workbook, and uses worksheet index 1.
3. It walks rows from 9 through `UsedRange.Rows.Count - 8`.
4. Each row is mapped from columns 1–14 into a `tracker` entity.
5. `created_by` and `updated_by` are set to `1`. Timestamps are `DateTime.Now`.
6. All mapped rows are `AddRange`d and `SaveChanges` is called. Existing tracker rows are not deleted.
7. On success the API returns HTTP 200 and the text "Your file has been uploaded successfully."
8. If the loop or save throws, the service closes the workbook and returns HTTP 200 with a sentence that includes the exception message. The controller only returns HTTP 400 when the service itself throws. Opening the workbook happens before the service `try`, so a failure to open the file is a 400 from the controller.

Provenance: **Implemented** in `FileUploadController` and `FileUploadService`. The method comment says "read each sheet". The code reads one sheet. That mismatch is recorded under [Discrepancies](#discrepancies).

### Read tracker rows

1. Caller sends `GET api/FileUpload/getTrackerData`.
2. The service projects every `tracker` row into `TrackerModel` and returns the list.
3. The controller returns HTTP 200, or HTTP 400 with `ex.Message` if the query throws.

The projection sets `id`, `item_number`, `item_description`, `pack_size`, `stocking_type`, `stock_cover`, `stock_balance`, `sales_forecast`, `mtd_sales`, `open_orders`, `perc_sold_incl`, and `perc_sold_excl`. It does not set `brand_group`, `fy_1`, `rf1`, or the audit fields, even though `TrackerModel` declares `brand_group` and the audit fields, and the table stores `fy-1` and `rf1`.

Provenance: **Implemented**. No paging, filter, or auth.

### SonarQube on push

On branch `master` or `development`, the pipeline installs OpenJDK 17 and `dotnet-sonarscanner`, begins analysis with key `overdrive_prsa_cio_AYpSpeQrQY-eSvjrqMo7`, runs `dotnet build`, and ends the analysis.

Provenance: **Implemented** in `bitbucket-pipelines.yml`. Only `master` exists. Whether the Linux image can build the Excel COM reference was not executed in this inspection. See technical documentation.

## Functional requirements

### FR-001 — Reporting page

The system serves a page at `/` titled Reporting System with tabs Tracker, SalesForecast, StockBalance, and QlikviewSales, and an Excel file input limited in the browser to `.xlsx`.

- Trigger: `GET /`.
- Output: HTML. Tables have an empty header row. No data is loaded.
- Validation: none.
- Errors: unhandled exceptions outside Development go to `/Error`.

Evidence: `Index.cshtml`, `Index.cshtml.cs`, `Program.cs`. Implementation: **Implemented**. Confidence: High.

### FR-002 — Browser upload acknowledgement

If the posted file is non-empty, the same page shows "File uploaded successfully!" and does not store or parse the file. If the file is absent or empty, the page redisplays with no alert.

Evidence: `IndexModel.OnPostAsync`. Implementation: **Implemented**. Confidence: High.

The comments that call this a demonstration are **Documented** in source comments, not a separate requirements document.

### FR-003 — Login screen

The system serves `/Login` with the heading PRSA - CIO, an email box, a password box, and a Sign In button that posts to `/Index`. Credentials are not read.

Evidence: `Login.cshtml`, `Login.cshtml.cs`. Implementation: **Implemented** as a screen. Authentication is not implemented. Confidence: High.

### FR-004 — Tracker import API

`POST api/FileUpload/uploadFile` accepts a filesystem path, reads the first worksheet, and inserts one `Imports.tracker` row per visited data row.

Inputs and column map (1-based Excel columns):

| Column | Entity field | Notes |
|---|---|---|
| 1 | `brand_group` | text |
| 2 | `item_number` | `Convert.ToInt32` |
| 3 | `item_description` | text |
| 4 | `pack_size` | text |
| 5 | `stocking_type` | text |
| 6 | `stock_cover` | decimal rules below |
| 7 | `stock_balance` | decimal rules below |
| 8 | `fy_1` (column `fy-1`) | decimal rules below |
| 9 | `rf1` | decimal rules below |
| 10 | `sales_forecast` | decimal rules below |
| 11 | `mtd_sales` | decimal rules below |
| 12 | `open_orders` | decimal rules below |
| 13 | `perc_sold_incl` | decimal rules below, plus `%` stripped |
| 14 | `perc_sold_excl` | decimal rules below, plus `%` stripped |

Row window: start at row 9, stop at `UsedRange.Rows.Count - 8` inclusive. What those skipped rows contain is **Unknown / Requires Confirmation**.

Decimal rules, **Implemented**:

- If the cell text contains `-`, the value stored is `0`. This includes a lone hyphen. It also includes any text that contains a hyphen.
- Else if the cell text contains `(` or `)`, parentheses are removed and the number is multiplied by `-1`.
- Else if the column is 13 or 14 and the text contains `%`, the `%` is removed and the remaining text is converted. The code does not divide by 100.
- Else the text is `Convert.ToDecimal`.
- Column 6 only: null, empty, or whitespace is stored as `0` before the other tests. Other numeric columns do not have that blank check, so a blank cell falls through to `Convert.ToDecimal` and fails the upload.

Audit fields on every inserted row: `created_by = 1`, `updated_by = 1`, `created_at` and `updated_at` = local `DateTime.Now`.

The insert appends. It does not clear `tracker` first.

Output: a string message. HTTP status is 200 for both the success sentence and the service's caught-error sentence. HTTP 400 is used when an exception escapes the service.

Evidence: `FileUploadController.cs`, `FileUploadService.cs`, `tracker.cs`. Implementation: **Implemented**. Confidence: High.

### FR-005 — Tracker read API

`GET api/FileUpload/getTrackerData` returns every tracker row as JSON using the projection listed in the read workflow. No authentication, filter, or page size.

Evidence: `GetTrackerData`. Implementation: **Implemented**. Confidence: High.

### FR-006 — Database shape available to the app

The EF model includes these tables. Application code queries only `tracker`.

| Schema | Table | Used by C# besides the DbSet |
|---|---|---|
| Core | `settings` | No |
| Core | `users` | No |
| Imports | `le3` | No |
| Imports | `mapping` | No |
| Imports | `master_data` | No |
| Imports | `open_orders` | No |
| Imports | `qlikview_sales` | No |
| Imports | `sales_forecast` | No |
| Imports | `stock_balance` | No |
| Imports | `tracker` | Yes, read and insert |

Evidence: `PRSAContext.cs`, `efpt.config.json`, and a search of non-generated C#. Implementation: schema **Implemented** as a model. Behaviour for the unused tables is **Unknown**. Confidence: High that the app does not use them.

### FR-007 — Privacy and error pages

`/Privacy` shows the ASP.NET template sentence "Use this page to detail your site's privacy policy." `/Error` shows a request id and is the exception handler when the web app is not Development. The API has no exception page. Swagger UI is registered only when the API environment is Development, at `/swagger`.

Evidence: `Privacy.cshtml`, `Error.cshtml.cs`, both `Program.cs` files, API `launchSettings.json` (`launchUrl` is `swagger`). Implementation: **Implemented**. Confidence: High.

### FR-008 — Static analysis pipeline

Pushes to `master` or `development` run a SonarQube scan around `dotnet build`. There is no test step and no deploy step.

Evidence: `bitbucket-pipelines.yml`. Implementation: **Implemented** as YAML. Confidence: High for the file. Pipeline success was not re-run for this document.

## Important constraints

- Both hosts call `UseSqlServer` with connection string `PRSA` during service registration. `appsettings.json` is gitignored, so a checkout does not contain it. Whether a missing string prevents process start was not executed. The name `PRSA` is **Implemented**.
- Excel import uses COM on the API machine. A path is opened locally. The API does not accept an uploaded byte stream.
- Text columns on `tracker` are `varchar(255)`. The importer does not truncate before save.
- `item_number` must parse as a 32-bit integer or the import fails.
- Decimal conversion uses the default `Convert.ToDecimal` culture of the process. Which culture production uses is **Unknown / Requires Confirmation**.
- Projects target `net6.0`. The pipeline image is the .NET 7 SDK.
- No repository tests exist.
- Web and API launch profiles bind localhost only (see technical documentation). No hosted URL is in the repo.

## Discrepancies

| Topic | A | B |
|---|---|---|
| Upload | Page comments and UI imply the user uploads an `.xlsx` | API requires a server filesystem path and is not called by the page |
| Sheets | Service comment: "read each sheet". Page tabs name four sheets. | Code opens `Worksheets[1]` only |
| Tracker fields | Table and `TrackerModel` include brand, FY-1, and RF1 | `GetTrackerData` does not copy `brand_group`, `fy_1`, or `rf1` |
| Login | A login page exists and `Core.users` has credentials-shaped columns | Nothing checks a password or `ad_token` |
| Error HTTP status | Service returns an error sentence after a caught exception | Controller wraps that string in HTTP 200 |
| Scan-host file list (2026-09-28 onboarding issue) | `sonar-project.properties` and `sonarqube_scan.bat` | Not in Git history |
| Layout | `Pages/Shared/_Layout.cshtml` is an AdminLTE 3 demo (brand "AdminLTE 3", user "Alexander Pierce") | `_ViewStart.cshtml` sets `Layout = null`, and `/` is a standalone document |

None of these were resolved by a later commit. They need a human decision before anyone treats the page, the API, or the unused tables as one product.