# PRD — stock_control (RGBC Stock Control)

Evidence date: 2026-10-02.

Source: `git@bitbucket.org:overdrive/stock_control.git`, `master` at `02bce8c` (2026-03-16).

There is no prior PRD in Silicon Ark and no product requirements document in the source tree. Requirements below are recovered from pages, code-behind, SQL strings, `Web.config`, `.env.example`, and `Stock_Control/Scripts/README.md`. Provenance is labelled.

The `.env.example` connection string is a template. No live password is stored in git. Sample workbooks under `Stock_Control/Imports/` and `Stock_Control/Imported/` were not opened. Their contents are not reproduced here.

## Product purpose

**Implemented.** The master page title is "RGBC Stock Control". After login the user lands on `ProductPeriod.aspx`, a 15-column monthly grid for one SKU or for all products of a brand expressed as 9-litre cases.

**Inferred.** The product exists so brand and supply staff can set forecast adjustments and orders, import actuals and opening stock, and see closing stock and months of cover.

**Unknown / Requires Confirmation:** the business owner, the expansion of RGBC, and whether this site is a module of the Superbowl application or a standalone tool that shares the Superbowl database.

## Users and interactions

Login (`index.aspx.vb`) reads `Request.LogonUserIdentity.Name`, upper-cases it, and looks up `mstUser` joined to `mstUserGroup` where `SystemStockControl = 'StockControl'`. On a match it stores the display name, domain login, user id, group name, and group id, writes `mstUsageLog` with `strSystem = 'StockControl'`, and redirects to `ProductPeriod.aspx`.

On no match the page shows "Insufficient Access" and the sentence "Login is controlled through the Active Directory". The contact name in that sentence is the character `_`. That reads as an unfinished placeholder. **Requires Confirmation.**

`Web.config` sets `authentication mode="Windows"`. The code also calls `FormsAuthentication.SignOut()`. There is no forms-login ticket check on later pages.

The signed-in user is stored in `WebConfigurationManager.AppSettings` (`UNAME`, `UDOMAIN`, `UID`, `UGROUP`, `UGROUPID`). Those keys are application settings, not session values. **Inferred:** two concurrent users overwrite each other's identity. Whether that is accepted in production is **Requires Confirmation**.

`Admin.Master` behaviour:

- `blnDebug` is true in the committed `Web.config`.
- When `blnDebug` is true and the assembly is compiled with `DEBUG`, an empty `UDOMAIN` is replaced with user name `a`, domain `x`, user id `1`, group `admin`.
- When `blnDebug` is false, an empty domain or group redirects to `Index.aspx`. If the group name contains `admin` (case-insensitive), menu item index 1 (Imports) is enabled. Otherwise that item is disabled.

Group names are not an enum in this repository. They are substrings of `mstUserGroup.strUserGroup`:

| Substring | Where it changes behaviour | Provenance |
|---|---|---|
| `admin` | Imports menu enabled. On the grid, orders are editable from the current month through the end of the window, and BMA is editable from the current month through the end of the window. Save writes both BMA and orders. | **Implemented** |
| `brand` | Brand dropdown limited to brands where this user is `refBrandManagerID` or `refBrandManager2ID`. BMA editable from next month onward, and only when `mstUser.UserID` on the product row equals the signed-in id. Confirm writes `stkConfirm`. | **Implemented** |
| `shipping` | Orders editable from the current month through the end of the window. Confirm writes `stkConfirmSM`. | **Implemented** |
| `order` | Same orders edit window as shipping. The save SQL also treats a group containing `order` like shipping. The confirm button does not. | **Implemented** |
| anything else | BMA and orders are read-only. The code comment calls this "9Litre". | **Implemented** |

`strGroupSC` can match more than one branch if the name contains more than one of these words. Admin is tested first, then brand, then shipping, then orders. **Inferred:** a name containing both `admin` and `brand` is treated only as admin.

Brand manager identity on the product query is `mstBrand.refBrandManagerID`, not `refBrandManager2ID`. The second manager can see the brand in the dropdown but the BMA edit check compares `UID` to `UserID` from the first manager only. **Implemented.** Whether the second manager is meant to edit is **Requires Confirmation**.

`UserList.aspx` and `BrandList.aspx` use the master page, so they inherit the login gate, and they do not add their own role check. They are not in the menu. **Unknown** how a user is expected to open them.

## Major workflows

### FR-001 — Sign in

- **Who:** a Windows user whose domain login exists on `mstUser` and whose Stock Control group flag matches.
- **Trigger:** Login button on `index.aspx`. The page does not read the text boxes for the lookup. `txtUser` is focused and is not used in `btnLogin_click`. **Implemented.**
- **Inputs:** Windows logon name.
- **Behaviour:** query, set app settings, insert usage log, redirect to the grid.
- **Errors:** no row → insufficient access. Exception → message plus `ex.Message` and `ex.StackTrace` on the page. `customErrors mode="Off"`.
- **Output:** redirect, or an error on the login page.

### FR-002 — Open the planning grid

- **Who:** any signed-in group.
- **Trigger:** menu Home, or the post-login redirect. Query string `B`, `P`, and `PC` (brand, period, product code) loads the grid after render.
- **Inputs:** brand, period (`yyyyMM`, periods from `200809` upward), product. Optional varietal and category. Optional "Show Decimals". Optional "Use Projected Figures for the current month".
- **Behaviour:** builds a window from three months before the selected period through eleven months after it (`sqlDB.getGridStartDate` / `getGridEndDate`). Shows two prior-year sales rows, an average, then opening, QF, BMA, actual, orders, receipts, exports, damages, repack, closing, months cover, and direct sales. Product code `0` is the all-products 9-litre view for the brand, filtered by varietal and category when set. A missing `stkProductPeriod` row for a real SKU is inserted as zeros.
- **Output:** the grid. Principal name, brand-manager mail link, target months of cover, and fiscal-year-to-date 9-litre actuals when the principal query succeeds.

### FR-003 — Edit BMA or orders and save

- **Who:** admin, brand, shipping, or orders, inside the month window in the users section.
- **Trigger:** Save, or Calculate (recalculates on screen only).
- **Inputs:** edited BMA (`txtBMA`) and orders (`txtOrders`). Other columns are labels.
- **Behaviour:** `Save` compares truncated BMA and orders to the values loaded from the database and updates `stkProductPeriod` through `sql_Update_PP`. Brand groups write BMA and `strLastUser`. Shipping or order groups write orders and `strLastUserSM`. Admin writes both. After save, `Import.updatePPBalance` runs for that product from the grid start period.
- **Validation:** `Page.Validate()` on product or period change. No numeric range check was found on the text boxes beyond the later `CDbl` conversion, which becomes 0 on failure.
- **Output:** updated period rows. The screen closing and months figures are recomputed in `reTotal` and are not identical to `updatePPBalance` (see business rules).

### FR-004 — Confirm a forecast

- **Who:** a group whose name contains `brand` or `shipping`, with a product selected (index greater than 0).
- **Trigger:** button text `Confirm [yyyyMM]` (`btnEmail`).
- **Behaviour:** calls `Save`. Brand users delete and insert `stkConfirm` for the product and current `yyyyMM`, storing twelve BMA values from columns 4 through 15. Shipping users do the same into `stkConfirmSM` with order values. Then `LoadSKUReport` renders an HTML table of BMA by SKU for the brand and a prior confirm row when `stkConfirm` has one.
- **Output:** confirm tables and an on-page HTML report. No email is sent. **Implemented.**
- **Gap:** admin and orders groups do not write a confirm row. Month window for the HTML report treats calendar months 1 and 2 as `period + 11`, which is not a valid `yyyyMM` for February. **Implemented** in `LoadSKUReport`. The stock-control report was changed so February ends in January of the next year (`d2bf192`). This confirm report was not.

### FR-005 — Import files

- **Who:** the Imports menu item is enabled only for admin. The page itself does not re-check the group. **Inferred** that a non-admin who knows `Import.aspx` can still post it.
- **Trigger:** Import on `ImportList.aspx` opens `Import.aspx`. The page lists files already sitting in the application `Imports` folder. The user ticks files and clicks import.
- **Inputs:** files named and shaped as in the technical document. Period files must match a row in `mstPeriod`. Excel sheet name must be `Import`.
- **Behaviour:** selected files update `stkProductPeriod` (and `stgCognos14` for Cognos 14). Comp-product rows are skipped. Product `05016` on an opening file is stored as nine times the workbook quantity. An import log flag is set on `stkImportLog`. A clean opening import copies the file when the site is compiled DEBUG and moves it when compiled Release. A clean Cognos 14 import moves `cognos14.txt` only when app setting `blnDebug` is `True`. `updatePPBalance` runs from the earliest period touched.
- **Errors:** missing folders, bad column counts, unknown product codes, and row exceptions are written into the page message. `updatePPBalance` swallows exceptions.
- **Output:** updated stock rows, a results message, files left in `Imported`.

### FR-006 — Import log and quota forecast

- **Who:** same menu gate as import.
- **Trigger:** Imports menu. Optional QF button.
- **Behaviour:** the log renders every column of `stkImportLog` except the last three, with `yes.gif` / `no.gif`. Those image files are not in the repository listing. The QF button runs `RepQF` for periods `200807` through `200906` only.
- **Output:** HTML table, and `dwfQF` rows if QF completes.

### FR-007 — Variance report

- **Who:** any signed-in user. No extra role check.
- **Trigger:** Variance Report menu, then View.
- **Inputs:** period, optional brand manager, optional brand, checkbox to keep rows inside the variance band.
- **Behaviour:** lists active non-comp finished-goods products. Fills twelve month columns with `dblMonths`. Default variance is 2.5 when the brand value is null. A row is removed when every month is inside target ± 0.5, unless the checkbox asks for all rows. Export downloads an HTML file named as `.xls`.
- **Output:** grid or file download.
- **Gap:** the end period uses `period + 11` for calendar months 1 and 2. Same February defect as FR-004.

### FR-008 — Stock control report

- **Who:** any signed-in user.
- **Trigger:** Stock Control Report menu, then View.
- **Inputs:** period, optional brand manager, optional brand, display field (`ddlDisplayField` selects which `stkProductPeriod` column is shown).
- **Behaviour:** same product population as the variance report. Each month cell is the selected field. Total and average are computed. February (`month = 2`) sets the end period to January of the following year. January uses `period + 11`. Other months use `period + 99`, which spans into the next year because `yyyyMM + 99` lands on month 99 of the numeric add (for example `202603 + 99 = 202702`).
- **Output:** grid or `.xls` HTML download.

### FR-009 — Maintain users

- **Who:** anyone who can open `UserList.aspx`. Not on the menu. **Unknown** intended role.
- **Trigger:** Search, Add User, row link, or the grid delete button.
- **Inputs:** user name, domain login, user group id.
- **Validation:** both text fields required. Domain login must be unique (`sql_get_Users` excluding the current id).
- **Behaviour:** insert or update `mstUser`. Delete is `DELETE FROM mstUser WHERE UserID = @UserID` with no extra confirm in the code-behind.
- **Output:** return to the list, or a message.
- Password columns exist only on unused functions `sql_insert_Users_OLD_WITH_PASSWORD` and `sql_update_Users_OLD_WITH_PASSWORD`. The live save does not write a password.

### FR-010 — Maintain brands

- **Who:** anyone who can open `BrandList.aspx`. Not on the menu.
- **Inputs:** brand name, lead months, variance, two brand managers, active flag.
- **Validation:** name required and unique. Lead months integer 0 through 12. Variance a non-negative number.
- **Behaviour:** insert (always active = 1) or update `mstBrand`.
- **Output:** return to the list, or a message.

## Integrations

| System | Direction | What moves | Auth | Failure |
|---|---|---|---|---|
| Active Directory / Windows | Inbound identity only | Logon name | IIS Windows auth | "Insufficient Access" or stack trace |
| SQL Server `csStockControl` | Read and write | Users, brands, products, periods, stock rows, sales facts, confirmations, import staging | SQL login in `STOCK_CONTROL_CONNECTION_STRING` | Login page shows the exception. Many later calls have empty `Catch` blocks |
| Excel `.xls` via Jet | Inbound files from disk | Opening, forecast, direct receipts, customer reps, BM adjustments | File system of the app pool identity | Message on the import page |
| `cognos14.txt` | Inbound | Sales lines into `stgCognos14`, then `dblActual` | Same | Row-level fail list. Comment in code: "Thank you SAP" on a column swap. SAP itself is not called |
| Brand manager mailbox | None | `mailto:` link only | n/a | n/a |

No outbound HTTP client was found.

Cognos here is a dropped text file, not a live Cognos API. **Implemented.**

## Important constraints

- Product identity on the grid and on `stkProductPeriod.mstProdCode` is the first five characters of `mstProduct.strProdCode`.
- Finished goods in 9-litre and quota-forecast SQL are product codes containing `F`.
- Comp products (`blnCompProduct = 1`, or a code whose first five characters start with `COMP` during import) are excluded from dropdowns, 9-litre inserts, brand product lists, and import row processing.
- Period keys are strings `yyyyMM`.
- 9-litre conversion is `pack * size * quantity / 9000`.
- Months of cover are capped at 15 in `updatePPBalance`.
- `stk9L` is deleted and rebuilt for the brand fiscal window whenever the grid loads a brand that has a principal. **Implemented.** That is a shared table. Concurrent users can delete each other's 9-litre snapshot. **Inferred** hazard.

## Requirements that could not be confirmed

- Production URL, hosting server, and whether Windows authentication is enabled on that IIS site.
- The allowed `mstUserGroup.strUserGroup` values as data, not as substrings the code searches for.
- Whether brand-manager 2 may edit BMA.
- Whether user and brand admin screens are still in use.
- Whether the QF button's 2008–2009 date range is intentional.
- Whether Cognos 24 and the commented Receipts, Damages, and Repack file imports are retired.
- Which balance formula is authoritative: on-screen `reTotal` (includes direct sales) or `updatePPBalance` (does not).
- Whether committed files under `Imports/` and `Imported/` are fixtures or live extracts that should be removed.