# Product requirements — pernodstaff

Evidence date: 2026-10-07.

Source: `git@bitbucket.org:overdrive/pernodstaff.git` at `2ec41f8` (2026-08-27) on `main`.

Requirements below are recovered from the implementation. Each one is labelled:

- **Documented** — stated in a committed readme, comment that describes intended behaviour, or config template.
- **Implemented** — present in code or SQL that runs for a user action.
- **Inferred** — a reasonable reading of that behaviour, not written down as a requirement.
- **Unknown** — not established.

The Bootstrapious `readme.txt` does not state product requirements.

## Product purpose

Staff of Pernod Ricard use the site to order products against an employee account, and authorised staff use a separate merchandising till. Administrators maintain users and the staff catalogue. Sales reps see their clients.

Provenance: **Inferred** from the four master pages, the Employee# (`DSVAccount`) gate, and the merch POS. The site title and footer name Pernod Ricard. The configured website is `https://www.pernodstaff.co.za`. Provenance: **Implemented** (`Web.config.template`, master pages).

## Users and roles

| Role | How they sign in | What the code allows |
|---|---|---|
| Staff order user | Azure AD on `login.aspx`. The email must already exist in `USERS`. `DSVAccount` (labelled Employee#) must be non-blank. | Catalogue, cart, send order, order history |
| Administrator | Azure AD on `admin/admin_login.aspx`. `USERS.accesslevel` must be 1. | Admin navigation: Users, Products. Missing Employee# sends them to edit their own user. |
| Merch clerk | Merch login. AD users use Azure AD. Non-AD users use password then email OTP. `merchaccesslevel` 3. | POS, invoices, reports |
| Merch admin | Same login. `merchaccesslevel` 4. | Clerk screens plus products, stock, categories, brands, settings |
| Sales rep | Email and password on `rep/rep_login.aspx` via `REPS_spLOGIN` | Client list (`rep_users.aspx`) |

Access-level labels on the admin user form (`admin/admin_users_mini.aspx`):

- Order `accesslevel`: 1 Administrator, 2 User, 3 Rep Manager.
- Merch `merchaccesslevel`: blank = no access, 3 Merch Clerk, 4 Merch Admin.

`App_Data/DbScripts/Merch/001_create_tables.sql` comments say access levels 3, 4, and 5 are merch roles on `USERS.accesslevel`. The later script `002_add_merchaccesslevel.sql` and the admin form use a separate `merchaccesslevel` column, and the form does not offer level 5. Login accepts only 3 and 4. Provenance: **Implemented**, and the older comment disagrees with the form. Which description is the business rule is **Unknown / Requires Confirmation**.

SSO does not create a `USERS` row. `oauth_callback.aspx.vb` `GetOrCreateUser` returns the existing row or leaves it empty, and the login page then shows "Your account is not set up." `CreateUserRecord` remains in the file and is not called. Provenance: **Implemented**. Commit `a5d297c` (2026-04-22) is "Disable sso auto account creation".

Logout clears the application session and does not sign the user out of Microsoft. Provenance: **Implemented** (comments on `login.aspx.vb`, `admin_login.aspx.vb`, `merch_login.aspx.vb`).

## Workflows

### Staff order

1. User opens `login.aspx` and chooses Microsoft sign-in.
2. Azure AD returns to `oauth_callback.aspx`. The app loads `USERS` by email (`USERS_spSSO`).
3. If `DSVAccount` is blank, the session is cleared and the user sees "Error, missing Employee code, please contact an Admin".
4. Otherwise the session is stored and the user lands on `dashboard.aspx` (the cart and recent orders).
5. `main.master` repeats the Employee# check on later requests and redirects to `login.aspx?error=missing_employee_code` when it is missing.
6. The user opens Products, searches or filters, and adds a quantity (`SHOPPING_CART_SpIns`).
7. On the dashboard they change quantities, remove lines, and choose Send Order (`SHOPPING_CART_SpSend`).
8. On success the app emails an order confirmation. On a stored-procedure `ERROR` it emails a failure notice and returns the error text.

Provenance: **Implemented**.

`AllowSsoBypass=true` plus `login.aspx?bypass=email` skips Azure and signs in that email. The template sets the key to `false` and says it must not be true in production. Provenance: **Implemented** (`functions.IsSsoBypassAllowed`, `Web.config.template`).

### Admin

1. Administrator signs in with Microsoft from `admin/admin_login.aspx`.
2. Callback allows the session only when `accesslevel` is 1. Otherwise it returns "Access Denied" and does not save tokens.
3. Missing Employee# opens `admin_users_mini.aspx` for that user. Otherwise the landing page is `admin_products.aspx`.
4. Users and Products are the only items in the admin navigation, and only when `Session("accesslevel")` is 1.

Provenance: **Implemented**.

`admin.master` redirects to the admin login when `Session("userid")` is missing. It does not check `accesslevel` on the pages themselves. A person who already has an order-portal `userid` session can request an admin URL directly. Provenance: **Implemented**. Whether that is accepted is **Unknown / Requires Confirmation**.

These admin pages exist, use `admin.master`, and are not linked from that navigation: `admin_users.aspx`, `admin_prices.aspx`, `admin_reports.aspx`, `admin_reps.aspx`, `admin_unresponded.aspx`, `admin_orderstoday.aspx`, `admin_data.aspx`, `GenerateXML.aspx`, `admin/default.aspx`. Provenance: **Implemented**. Whether operators still use them is **Unknown / Requires Confirmation**.

### Merchandising sale

1. User opens `merch/merch_login.aspx`.
2. AD users go to Azure AD and return to `merch/pos.aspx` when `merchaccesslevel` is 3 or 4.
3. Non-AD users submit email and password. AD users who try the password form are told to use Microsoft. An empty password hash is told to use Forgot Password. A matching password goes to `merch_mfa.aspx` for an email code.
4. `merch.master` requires `MERCH.userid` and merch access (default check: level 3 or 4). Level 4 pages (products, stock, categories, brands, settings) check level 4 again.
5. POS holds a cart in session, blocks inactive products and quantities above stock on hand, writes `MERCH_INVOICE` with payment method `card` and status `paid`, writes lines and an inventory movement, and builds a PDF.

Provenance: **Implemented** (`merch_login.aspx.vb`, `merch_mfa.aspx.vb`, `pos.aspx.vb`).

### Sales rep

1. Rep submits email and password. `REPS_spLOGIN` receives the password as entered.
2. Success stores `REP_ID` and `REP_FULLNAME` and opens `rep_users.aspx`.
3. Optional "remember me" stores the email in cookie `RepInfo` for 35 days.
4. `rep.master` requires `REP_ID` except on the first postback check.

Provenance: **Implemented**. How `REPS_spLOGIN` checks the password is in the database and is **Unknown** (the procedure body is not in this repository).

### Password reset and activation

`forgot.aspx` calls `USERS_spFORGOT`. The service emails a link to `resetpassword.aspx?token=...`. A merch return adds `&return=merch`. Reset calls `USERS_spRESET_PASSWORD_WITH_TOKEN` after the app hashes the new password.

`admin/admin_login.aspx` still calls `USERS_spREGISTER` from the page script, and `activate.aspx?g=` calls `USERS_spACTIVATE`. Order and admin sign-in buttons are SSO-only. Provenance: **Implemented**. Whether self-registration is still offered in the admin login markup, and whether activation is still used, needs a look at the live page. The register call is present in `admin_login.aspx`.

`profile.aspx` loads and updates the current user (`USERS_SpSel`, `USERS_SpUpd`) and requires Employee# in the page script. No link to `profile.aspx` was found from `main.master` or `dashboard.aspx`. Provenance: **Implemented** page, navigation **Unknown**.

## Functional requirements

### FR-001 — Staff sign-in

The order portal signs the user in with Azure AD and an existing `USERS` email.

- Actor: staff user.
- Trigger: Sign in with Microsoft on `login.aspx`.
- Behaviour: authorization-code flow with PKCE; email taken from the ID token or Graph; `USERS_spSSO`; session fields `userid`, `accesslevel`, `DisplayName`, `isADUser`, `DSVAccount`, `CashAccount`, `ReceiveStatements`.
- Error: `auth_failed`, `no_email`, `user_not_found`, `callback_error`, `missing_employee_code`.
- Provenance: **Implemented** (`OAuthService.vb`, `oauth_callback.aspx.vb`, `login.aspx.vb`).

### FR-002 — Employee number required to order

An active order user must have Employee# (`DSVAccount`).

- The order master refuses a session with a blank Employee#.
- `SHOPPING_CART_SpSend` is rejected in `JSON_DATA_SERVICE` when the session Employee# is blank, with the message "Error, missing Employee code, please contact an Admin".
- `USERS_SpUpd` rejects an active user with a blank Employee# ("Employee# is required for active users.").
- A duplicate Employee# returns `DSV_DUP_CONFIRM|<company name>` unless `ConfirmDsvReassign` is set, in which case the other user is set `active = 0`.
- Provenance: **Implemented**.

### FR-003 — Catalogue shows active products

`PRODUCTS_SpSel` and `PRODUCTS_SpSearch` results are filtered to rows whose `active` or `isactive` value is true, when that column is present. A null or unrecognised value is treated as active.

Provenance: **Implemented** (`JSON_DATA_SERVICE.FilterDataTableToActivePortalProductsOnly`).

### FR-004 — Shopping cart

The user adds, updates, and removes cart lines through `SHOPPING_CART_SpIns`, `SHOPPING_CART_SpUpd`, and `SHOPPING_CART_SpDel`. The basket count uses `CART_TOTALS_SpSel` and `js/cart-sync.js`.

A delete that returns no row is treated as success ("Item removed from cart."). Provenance: **Implemented**. Commit `ede1f26` (2026-06-23).

### FR-005 — Twelve bottles per calendar month

Sending, inserting, or updating a cart is limited to 12 bottles.

- The web method sums current cart quantities and rejects a proposed total above 12 with "Your cart is limited to 12 bottles in total."
- The latest stored procedures (`015`, `016`, `017` under `App_Data/DbScripts/StaffPortal/`) count the active cart plus order lines in the current calendar month, and reject with "Only 12 bottles allowed for the month."
- A header whose status is `99` and that has an `ERROR_LOG` row for component `PernodIntegration` and operation `CreateOrder` is left out of that month count. A status-99 header without that error still counts.
- The same limit applies to every user. Birthday handling was removed (`7b7b95d`, and the comment in `JSON_DATA_SERVICE`).

The web-method check counts the cart only. The procedure also counts the month's orders. Both run. Provenance: **Implemented**. If the procedure body on the server is an older script, the month count can differ. Provenance of the live procedure: **Unknown / Requires Confirmation**.

### FR-006 — Checkout cutoff

`SHOPPING_CART_SpSend` reads `CLOSE_TIME` from `ORDER_TIMES` where `ORDER_TIME_ID = 1`. If the count is over 12 or `GETDATE()` is after `CLOSE_TIME`, it does not insert the order. If the clock is already past `CLOSE_TIME`, the returned error is "Sorry, you missed the 17:00 cut off." If the clock is before `CLOSE_TIME` and the count failed, the error is the 12-bottle message.

Provenance: **Implemented** in `015_SHOPPING_CART_SpSend_exclude_jde_errors.sql`. The message says 17:00 even though the comparison uses `CLOSE_TIME`. Whether those are always the same is **Unknown / Requires Confirmation**.

On success the procedure inserts `ORDER_HEADER` and `ORDER_DETAIL` with status `99`, sets cart status to `2`, and sets totals with VAT as `price * quantity * 1.15`. It returns the new `Orderid`.

### FR-007 — Order confirmation and failure mail

After a successful send, the app loads the header, status description, and active-product lines, and sends `EmailService.SendOrderConfirmationEmail`. Failure to send mail is reported to the user as an order that was placed but not emailed.

When the procedure returns a column named `ERROR`, the app emails `SendOrderFailureNotification` to the user and, when `OrderFailureAlertEmail` is set, to those addresses. The template leaves that key commented out, and then the failure mail goes to the user only when the profile has an email.

Provenance: **Implemented**. Queries contact in config: `Gloria - gloria.kok@pernod-ricard.com`. From address: `no-reply@pernodstaff.co.za`. BCC in the template: `maanie@overdrive.co.za`.

### FR-008 — Order history and returns

`history.aspx` lists orders (`ORDER_HEADER_SpSel`, `ORDER_DETAIL_SpSel`) and can insert a return (`ORDER_RETURNS_SpIns`). The header search on `main.master` opens `history.aspx?search=`. `returns.aspx` calls the same procedures. `history_details.aspx` loads one header.

Provenance: **Implemented**. The return rules inside `ORDER_RETURNS_SpIns` are **Unknown** (procedure body not in the repo).

### FR-009 — Admin user maintenance

An administrator lists users with `USERS_MINI_SpSel` (including inactive users, per `Merch/004_users_mini_spsel_include_inactive.sql`) and saves with `USERS_SpUpd`. New users default `accesslevel` to 2 when the parameter is omitted (`Merch/003_update_user_stored_procedures.sql`).

Provenance: **Implemented** in the page and in the script. The script must be applied for that default to exist in the database.

### FR-010 — Admin product maintenance

`admin_products.aspx` lists categories and products and updates them (`ADMIN_PRODUCTS_SpUpd`). `admin_prices.aspx` updates prices (`ADMIN_PRODUCTS_spUpd`) and is not in the navigation.

Provenance: **Implemented**.

### FR-011 — Merch sign-in and MFA

Non-AD merch users must have a stored password hash and must pass an email OTP (`EMAILAUTH_spSAVE_CODE`, `EMAILAUTH_spVERIFY_CODE`) before `MERCH.userid` is set. `MFAService` generates a 6-digit code. `CodeExpiryMinutes` is 5 in the class and is not sent to the procedure. Expiry is whatever `EMAILAUTH_spVERIFY_CODE` does. Provenance: **Implemented** in the class; expiry in the database is **Unknown**.

### FR-012 — Merch POS

A clerk or admin can sell in-stock, active products. Checkout refuses a line whose quantity exceeds stock on hand, removes inactive products from the cart, assigns an invoice number from settings `invoiceprefix` and an incremented seed, stores payment method `card` and status `paid`, and writes a PDF under the invoice file path.

Provenance: **Implemented**. Prices are described as VAT-inclusive on `MERCH_INVOICE_LINE.unitprice` in `001_create_tables.sql`, and the POS comment says prices are VAT-exclusive and takes VAT from setting `vatrate`. Provenance: **Conflict — Requires Confirmation**.

### FR-013 — Merch stock and reports

Merch admin (level 4) can edit products, import opening stock from Excel, receive stock, and adjust stock. Clerks and admins can open stock-on-hand, daily sales, and transaction reports and download Excel via ClosedXML. Invoices can be printed and downloaded; a missing file is regenerated or the user is sent back with an error.

Provenance: **Implemented**.

### FR-014 — Rep client list

A signed-in rep sees clients from `REP_USERS_SpSel`, including `DSVAccount`. Provenance: **Implemented** (`rep/rep_users.aspx`).

### FR-015 — Login audit

Successful and failed sign-ins are written through `LoginLogger` with user id (when known), email, login type (`SSO`, `PASSWORD`, `DEBUG_BYPASS`), module (`ORDER_PORTAL`, `ADMIN`, `MERCH`), IP address, and user agent. The SSO script `014_create_login_history_table.sql` creates the table the logger uses. Provenance: **Implemented**. The exact columns are in that script and in `LoginLogger.vb`.

## Integrations

| System | Direction | Trigger | Data | Failure |
|---|---|---|---|---|
| Azure AD | Out then in | Sign-in | Auth code, tokens, email, profile | Redirect with `auth_failed` / `callback_error`. Tokens are not saved when the module check fails. |
| Microsoft Graph | Out | User info and token refresh | `GET https://graph.microsoft.com/v1.0/me` | Caught and logged. SSO can continue from the ID token email. |
| SMTP | Out | Reset, activation, MFA, order placed, order failed | HTML mail | User-facing failure text. Certificate validation accepts every certificate (`EmailService.ValidateServerCertificate` returns true). |
| SQL Server | Both | Every business action | Stored procedures and direct SQL | JSON `{ success: false }` or a SQL error string. `ErrorLogger` writes `ERROR_LOG`. |
| JDE `PernodIntegration.CreateOrder` | Not in this repo | After an order exists, inferred | `ERROR_LOG.orderid` when create fails | Those orders are excluded from the 12-bottle month count |

`App_Code/SMS.vb` posts JSON to `http://api.payless4sms.co.za/sms/json` and contains gateway credentials in source. No caller was found. Provenance: **Implemented** unused class. The credentials should be treated as exposed and rotated. They are not repeated here.

## Constraints

- Session state in the template is SQL Server, cookieless, `timeout="1000000"`, `regenerateExpiredSessionId="true"`. Whether production `Web.config` matches the template is **Unknown**.
- JSON web methods require `Session("Key")` to equal app setting `KEY`. After that check, `spAlias` from the browser is the stored-procedure name.
- VAT for staff orders is hardcoded as 15% (`* 1.15`) in `SHOPPING_CART_SpSend`. App setting `VAT` is `15`. Merch VAT comes from `MERCH_SETTINGS.vatrate`.
- Custom errors: remote-only, 404 to `404.html`, other errors to `errorPages/GeneralError.aspx` (that page was not in the file list that was read).
- Response headers in the template: `X-Frame-Options: sameorigin`, `X-XSS-Protection`, `Strict-Transport-Security` one year, `X-Content-Type-Options: nosniff`.

## Non-functional notes recovered from the repo

- SonarQube runs on `main` and on pull requests. There is no test step in `bitbucket-pipelines.yml`.
- Publish profile writes a filesystem publish to `bin\Debug\Publish`. Hosting beyond that is **Unknown**.
- Terms page states English law, a privacy contact `info@pernodstaff.co.za`, and a 2015 copyright line, with a 2022 copyright in the footer. Provenance: **Documented** on `terms.aspx`. Whether that text is the current legal position is **Unknown / Requires Confirmation**.