# Product requirements — superbowl

Evidence date: 2026-10-02.

Source: `git@bitbucket.org:overdrive/superbowl.git`, `master` at `a6f4b8b` (2026-09-16).

There is no standalone requirements document. Requirements below are recovered from `docs/tasks/TASK-001.md`, page behaviour, and SQL. Each item is labelled.

Provenance labels: **Documented** (explicit in `docs/` or a script header), **Implemented**, **Inferred**, **Planned** (named and not done), **Unknown**.

## Product purpose

**Documented** (`docs/index.md`): legacy ASP.NET Web Forms application for RGBC master data and operational utilities.

**Implemented.** Signed-in users maintain customers, products, and related lists in SQL Server, capture export sales, queue product archives, and read sales, logs, and promo data loaded by other jobs.

**Unknown.** Business owner, production URL, and the operating schedule of the sibling daily and cronjob executables.

## Users and interactions

**Documented** (TASK-001) and **Implemented** (`UserPermissions.vb`):

| Level | Constant | Who | Edit rights in this app |
|---|---|---|---|
| 5 | `AdministratorLevel` | Full Admin | Unrestricted edit, including Export Sales Process, Month End Process, Integrity mutations, Accounts, Sales Reps, Setup |
| 4 | `SupplyChainAdminLevel` | SC Admin (`strUserGroup = 'SC Admin'`) | Product menu (including brands, companies, principals, archive, competitor sets) and Export Sales add/edit/delete. Process stays Admin-only |
| 2 | `ViewerLevel` | Viewer | Read-only. Mutation buttons disabled and server demands reject changes |
| other / missing | session parse failure returns 0 | not a named role | Treated as not admin and not SC Admin. Master page redirects to login when level is 0 and `blnDebug` is false |

**Documented** (TASK-001 decision, 2026-09-15): legacy level 3 edit behaviour is intentionally not retained. Export Sales edits are levels 4 and 5 only.

**Implemented.** Login page `index.aspx` clears the session, reads `Request.LogonUserIdentity.Name` in upper case, and loads `sql_get_Users` for system `Superbowl`. A matching row stores `Session("User")`, `Session("UGROUP")`, `Session("ULEVEL")`, writes `mstUsageLog`, and redirects to `Home.aspx`. No row shows "Insufficient Access" and tells the user that login is controlled through Active Directory. Exceptions show the exception message and stack trace on the page.

**Implemented.** `Web.config` `authentication mode` is `Windows`. `FormsAuthentication.SignOut()` is still called on login load and on failure. **Inferred.** The Windows identity is the credential; Forms authentication is not the mode that admits the user.

**Implemented** (`Admin.Master.vb`). Page title `RGBC Superbowl`. Header shows `Logout: {Session User}` and the group name. Logout navigates to `index.aspx`, which clears the session. When `appSettings blnDebug` is true **and** the assembly is compiled DEBUG, an empty `UDOMAIN` appSetting signs the browser in as user `a`, group `Debug`, level 5, without a database lookup. Committed `Web.config` has `blnDebug` = `true`.

## FR-001 — Sign in

Requirement: the system admits a Windows user who has a `mstUser` row joined to a Superbowl user group (`SystemSuperbowl = 'Superbowl'`).

Who: any user who can open the site.

Trigger: Login button on `index.aspx`.

Inputs: Windows logon name; `mstUser.strDomainLogin`; group `intLevel`.

Expected behaviour: session identity as above; one `mstUsageLog` row with `strSystem = 'Superbowl'` and stamp `yyyyMMddHHmmss`.

Error conditions: no row → insufficient access. SQL or other exception → message plus stack trace on the page. **Implemented.**

Validation: the logon name is upper-cased before the query. `CleanString` is applied inside `sql_get_Users`.

## FR-002 — Keep a session on operational pages

Requirement: when `blnDebug` is false, pages that use `Admin.Master` redirect to `Index.aspx` if `Session("User")` is missing or `CurrentLevel()` is 0.

**Implemented** in `Admin.Master.vb`. The redirect is inside the non-debug branch only.

**Inferred.** With the committed `blnDebug=true`, a Release build does not auto-login and also does not take the redirect branch. Whether production sets `blnDebug` false is **Unknown**.

## FR-003 — Home sales to date

Requirement: Home shows daily GSV, 9L, and free-stock totals from `dwfSales` joined to `mstCustomer`, for `refPeriod` greater than or equal to the period text (default current `yyyyMM`), excluding promotional-account rows unless `dbl9lFreeStock` is 0. A second union row is the grand total (empty date).

Who: any user who can open Home. No `UserPermissions` call on `home.aspx.vb`.

Evidence: `home.aspx` `sdsZ`, comment v1.2.5 in `Admin.Master.vb`.

Implementation: Implemented.

## FR-004 — Master-data lists and detail pages

Requirement: the menu in `Admin.Master` is the navigation. Each list page searches, pages (default page size `PAGESIZE` 50, "show more" `MAXPAGESIZE` 1000), and exports the grid as an HTML file named with the session user and `yyyyMMdd` and content type `application/vnd.ms-excel`. Detail pages insert or update the matching `mst*` row and, where coded, a `dwd*` warehouse copy.

Who: see the permission column. Add, save, and delete on guarded pages call `DemandFullAdmin`, `DemandProductEdit`, or `DemandDataCapturingEdit`. A failed demand throws HTTP 403.

| Menu | Pages | Mutating role |
|---|---|---|
| Accounts — Customers, Key Accounts, Key Account Groups, Snell Areas, Snell Super Groups, AdCheck Customers (detail), Countries, Customer Groups | `Customer*`, `KeyAccount*`, `KeyGroup*`, `SnellArea*`, `Snellsupergroup*`, `ACCustomerDetails`, `Country*`, `CustomerGroup*` | Full Admin |
| Product — Brands, Companies, Principals, Products, Products Data & Dim, AdCheck Products (detail), Product Archive, Comp Set Products | `Brand*`, `Company*`, `Principal*`, `Product*`, `ProductDataDimension*`, `ACProductDetails`, `ProductArchive*`, `CompSetProduct*` | Full Admin or SC Admin |
| Sales Reps — Sales Reps, Rep Managers | `Rep*`, `RepManager*` | Full Admin |
| Setup — Branches, Channel Segments, Rep Segments, Users | `Branch*`, `Channel*`, `Segment*`, `User*` | Full Admin |
| Data Capturing — Export Sales | `ExportsList`, `ExportsDetails` | Add/edit/delete: Admin or SC Admin. Process and Select All: Full Admin |

AdCheck **list** pages (`ACCustomerList.aspx.vb`, `ACProductList.aspx.vb`) search and export only. They do not call `UserPermissions`. The detail pages do.

Non-RGBC Customers is commented out of the menu. `NonRGBCCustomerList.aspx.vb` `Page_Init` redirects to `CustomerList.aspx` and completes the request. **Documented** in that file: Non-RGBC customers were merged into `mstCustomer`.

## FR-005 — Customer maintenance

Requirement: a full admin can save a customer when customer number, name, branch, channel segment, sales rep, type 1, type 2, and country are present. Tags longer than 500 characters and a liquor licence longer than 25 characters are rejected. Country text is matched to `mstCountry` when the dropdown is not selected. Changing the customer number to one that already exists redirects to that customer.

Duplicate-name check is commented out. **Implemented.**

Save updates or inserts `mstCustomer` and then the `dwdCustomer` copy (`sql_update_Customer` / `sql_insert_Customer` and the `_dw` helpers). An audit snapshot is taken from the previous `mstCustomer` row (`AuditEntity`).

Evidence: `CustomerDetails.aspx.vb`.

## FR-006 — Product maintenance

Requirement: product code shorter than 6 characters is invalid (on-screen example `99999X`). Type, short description, long description, brand, and display order are required. Tags longer than 500 characters are rejected. Duplicate-product checks are commented out.

Save writes `mstProduct` and `dwdProduct`. **Implemented.** `ProductDetails.aspx.vb`.

Product Data and Dimensions requires a selected product (message "SAP Code Required") and a brand, then writes the dimension row and its warehouse copy. **Implemented.** `ProductDataDimensionDetails.aspx.vb`.

## FR-007 — Competitive set

Requirement: a primary RGBC product has a competitor set of at most 10 other products, a required set name, no duplicate competitor codes, and the primary product cannot be one of its own competitors. Active flag applies to the set. Rows are stored in `mstProductCompetitorSet` with `intPosition`.

**Documented** in `Scripts/Create_mstProductCompetitorSet_Table.sql` ("up to 10 per primary product"). **Implemented** in `CompSetProductDetails.aspx.vb`.

Default name when none is stored is the primary product code. **Implemented.**

## FR-008 — Product archive

Requirement: archiving searches products by the first five characters of `strProdCode`, assigns the next code at or above `90001` in the `9%` range, renames the `F` and `S` product codes in `mstProduct`, `dwdProduct`, competitor-set references, and non-completed `sysCronJobProducts` rows, marks the products inactive, stores the old code in `strProdCodeArchive`, deletes completed `sysCronJobProducts` rows for the old codes, and inserts `sysCronJob` type `ArchiveProduct`, status `New`, parameters `strProdCodeNew` and `strProdCodeOld`.

Who: Full Admin or SC Admin (`DemandProductEdit`). TASK-001 decision: Product Archive is part of the Product menu.

**Implemented** in `ProductArchiveDetails.aspx.vb`. Comments state that sales, QF, off-con surveys, stock, AdCheck map, exports, and stock-control period updates were moved to the cron job and are not executed here.

An empty new code throws `Invalid Archive Code @ 1.4.3`.

## FR-009 — Export sales

Requirement: Export Sales rows live in `mstExports` (period, customer, product, units, GSV, NSV, nett ex, country). Add and edit require customer number, product code, period, and export country. SC Admin and Full Admin may add, edit, and delete. The delete button is disabled when `blnProcessed` is true.

**Documented** (TASK-001): Export Sales Process remains full Admin only. **Implemented:** `btnProcess` and `btnSelectAll` are disabled unless `IsFullAdmin()`, and `btnProcess_Click` calls `DemandFullAdmin`.

Process reads a CSV of export ids from hidden field `hfSelected` (comment: avoids the ASP.NET 2000 form-key limit at page size 1000). Each id must parse as a number. Already-processed rows are skipped. A row whose `refBrandID` is 0 or null is not posted; `WriteLog` records "Missing or incomplete product information". Other rows are marked processed with a shared batch tick (`sql_process_Export`). If any row ran, existing `dwfSales` documents for that batch are deleted and re-inserted (`sql_delete_Sales_Exports`, `sql_insert_Sales_Exports`). Document numbers are `Y` plus a 7-digit export id. A failure of that sales write calls `sql_process_Export_undo` and logs "UNDOING PROCESS". Per-row exceptions are swallowed and the loop continues.

**Implemented.** `ExportsList.aspx.vb`, `sqlDB.vb` export helpers.

**Planned / not verified:** `docs/testsprite_run.md` says the Export Sales page could not load because the local database had no `mstExports`. TASK-001 acceptance "verified against a database containing `mstExports`" is unchecked.

**Implemented gap:** `gvExports_RowDeleting` sets `intRows = 0` and therefore never cancels the delete for "in use". The processed-row protection is the disabled button, not a second read of `blnProcessed` inside the delete handler.

## FR-010 — Reference-data validation

Full admin save rules that are present in the detail code-behind:

| Entity | Required | Uniqueness |
|---|---|---|
| Brand | name, principal, company, brand manager; lead months integer 0–12; variance a positive number | brand name |
| Company | name, financial year start, financial year end | company name |
| Principal | name, financial year start, financial year end | principal name |
| Country | country name | country name |
| Customer group | group name | group name |
| Key account | name, price group code | key account name |
| Key account group | group name, key account | group name within the key account |
| Rep | rep id, name, market, branch, segment, email | rep id |
| Rep manager | name | name |
| Segment | name | name |
| Snell area | area name (message also cites the code) | name lookup via `sql_get_SnellArea` |
| Snell super group | code and description | code |
| Branch | branch name, sales office, region, code, SAP code, region description 2. Every failure message text is "Branch name required." | `sql_get_Branch` on code and SAP code |
| User | user name, domain login, email | domain login among other users |
| Channel segment | none in `btnSave_Click` | none in that handler |

Provenance: **Implemented** in the matching `*Details.aspx.vb` files.

## FR-011 — Integrity test

Requirement: the Integrity page lists master rows missing from the warehouse (and the reverse) for customers and products, using the current month and the previous month as column headers via `Duodecimal`. Full Admin can insert the checked customer master rows into `dwdCustomer`, warehouse rows into `mstCustomer`, product master rows into `dwdProduct`, and warehouse products into `mstProduct`. `UpdateBrands` copies brand, principal, company, and grouping fields onto `dwdProduct` and demands full admin; its button handler is commented out.

**Implemented.** `Integrity.aspx.vb`. Non-admins have the four mutation buttons disabled on first load.

## FR-012 — Month-end BFBE file

Requirement: Month End lists sales periods. Process is enabled only for full admin and demands full admin. It loads `sysQuery` named `bfbe-Sales_Month_to_Date`, replaces the literals `200910` and `20091001` with the selected period and today's `yyyyMMdd`, and writes a tab-separated file `BFBE\ZA_{period}_INVW.txt` under the application base directory.

`MailBrandon()` would mail that file and `ZA_{period}_CUST.txt` from `superbowldaily@rgbc.co.za` through SMTP host `localhost` to the hard-coded recipients in that method. The call from `btnProcess_Click` is commented out, so Process does not send mail.

**Implemented.** `MonthEnd.aspx.vb`.

## FR-013 — Logs and promo extracts

| Page | Behaviour | Permission check in code-behind |
|---|---|---|
| Audit Log | Date-range search and Excel export of the change audit | none |
| Daily Log | Date-range search of `dlyImportLog`; row colour from `DailyLog.getHexColour` | none |
| Usage Log | `mstUsageLog` joined to user and stock-control user group, newest first | none |
| Oncon Promo | Date range defaulting to the first of the month through today; reads `dwfOnconPromo`; Excel export | none |
| Offcon Promo | Same pattern on `dwfOffconPromo` | none |

These pages sit on the Utilities menu, so they still require the master-page session when `blnDebug` is false. They do not demand a role before export.

**Implemented.**

## FR-014 — AdCheck mapping

Requirement: AdCheck customer and product lists read `dwfACLisp` joined to `mapACStore` / `mapACProduct` and `mstCustomer` / `mstProduct`. Detail pages load one `dwfACLisp` store or product and save the map. Customer detail demands full admin. Product detail demands product edit.

**Implemented.** `ACCustomer*.aspx.vb`, `ACProduct*.aspx.vb`. What feeds `dwfACLisp` is **Unknown** inside this repository.

## FR-015 — SC Admin group deployment

Requirement: `Scripts/Add_SC_Admin_Superbowl_Group.sql` inserts `mstUserGroupSuperbowl` row `SC Admin` at `intLevel` 4 and `SystemSuperbowl = 'Superbowl'` if that name is absent. If the name exists with a different level or system, the script raises an error and rolls back. It does not assign the group to a user.

**Documented** in the script and in TASK-001. **Planned:** production username is an open question in the task file.

## Integrations

| System | Direction | Trigger | Data | Failure |
|---|---|---|---|---|
| SQL Server `Superbowl` | both | every page that queries | master, warehouse, facts, logs, `sysCronJob` | Login shows the exception. Most save handlers write `lblMessage` with the exception text. Connection missing → `ConfigurationErrorsException` from `SecureConfig` |
| Active Directory / Windows | inbound identity | login | `DOMAIN\user` logon name | No DB row → insufficient access. How the Windows token is issued is **Unknown** |
| `sysCronJob` consumer (separate repo) | outbound queue | Product Archive save | type `ArchiveProduct`, status `New` | This app does not wait for the job. Worker failure behaviour is **Unknown** here |
| SMTP localhost | outbound, not on the active path | `MailBrandon` only | BFBE attachments | Call site commented out. Exceptions while attaching files are empty `Catch` blocks |
| File system `BFBE\` | outbound | Month End Process | `ZA_{period}_INVW.txt` | No explicit failure message in `btnProcess_Click` if the write throws |

Authentication of the SQL login is the connection string. The string is not stored in git.

## Important constraints

- Permission level must come from session, not from `appSettings`, because appSettings are process-wide. **Documented** TASK-001 and comments in `index.aspx.vb` and `UserPermissions.vb`. Login still writes `UDOMAIN`, `UID`, `UGROUP`, and `UGROUPID` into appSettings. List filters (`strPage`, `intListIndex`, `strListFilter`, `ShowMore`) are also appSettings. **Inferred:** two concurrent users can overwrite each other's list position and filter.
- `customErrors mode="Off"` and login catch blocks emit stack traces to the browser. **Implemented.**
- `directoryBrowse enabled="true"` and `compilation debug="true"` are committed. **Implemented.** Whether production transforms these is **Unknown**.
- SQL is concatenated in `sqlDB.vb`. Several statements call `CleanString`. Not every dynamic fragment was audited for this document.
- Import page `Import.aspx.vb` is entirely commented ("COPIED FROM SC IMPORT.ASPX.VB") and is not on the menu. It is not a working import UI.
- `[Sales QF]` menu item has an empty `NavigateUrl`. `SalesRepQF.aspx` and `RepQF.vb` still exist. **Unknown** whether anything links to them.
- `ZZList` / `ZZDetails` are not on the menu. Save logic in `ZZDetails.aspx.vb` is commented. They behave as an unused template.

## Requirements that could not be confirmed

- Production host, IIS site name, and whether `blnDebug` is false in production.
- Which Windows groups, if any, are required in addition to the `mstUser` row.
- Whether level values other than 2, 4, and 5 exist in `mstUserGroupSuperbowl` in production.
- Whether `Add_SC_Admin_Superbowl_Group.sql` and the other scripts under `Scripts/` have been applied.
- Full column lists and constraints of tables that have no `CREATE TABLE` in this repo.
- The business meaning of Snell, BFBE, AdCheck, QF, GSV, NSV, and 9L beyond the labels in the UI. Spellings are taken from the code (`Snell`, `BFBE`, `AdCheck`, `9L`).