# Technical documentation — pernodstaff

Evidence date: 2026-10-07.

Source: `main` at `2ec41f8135ed86a034138d7b994c887148e9b45a` (2026-08-27).

## Application type

ASP.NET Web Site (not a Web Application project). Pages are `*.aspx` with `CodeFile` and `App_Code` compiled by ASP.NET. Language is VB.NET. `Web.config.template` sets `targetFramework="4.7.2"`, `explicit="true"`, `strict="false"`, and `compilation debug="true"`.

`website.publishproj` and `App_Data/PublishProfiles/FolderProfile.pubxml` publish to the filesystem at `bin\Debug\Publish`. There is no Docker file, no `web.config` transform set, and no infrastructure-as-code in the repository.

The visual theme is Bootstrapious (Bootstrap 4.3.1 per `readme.txt`), with vendored jQuery, Popper, Font Awesome 4.7, Owl Carousel, SweetAlert2, and jquery.cookie under `vendor/`. Admin pages also load DataTables from a CDN.

## Repository layout

| Path | Role |
|---|---|
| `*.aspx` at the root | Order portal, login, history, profile, terms, password reset, OAuth callback |
| `admin/` | Admin master, login, users, products, and unlinked report/rep/XML pages |
| `merch/` | Merch master, login, MFA, POS, stock, invoices, reports |
| `rep/` | Rep master, login, client list |
| `App_Code/` | Shared VB classes (services, JSON gateway, helpers) |
| `App_Data/DbScripts/` | SQL Server scripts grouped as `StaffPortal`, `SSO`, `Merch` |
| `App_Data/PublishProfiles/` | Filesystem publish profile |
| `js/cart-sync.js` | Cart badge refresh |
| `css/` | Theme and `custom.css` (cache-busted with file timestamp) |
| `vendor/` | Front-end libraries |
| `packages.config` | NuGet packages for .NET Framework 4.7.2 |
| `Bin/` | Committed Newtonsoft.Json build output and XML doc |
| `bitbucket-pipelines.yml` | SonarQube |
| `Web.config.template`, `Web.Secrets.*.config.template` | Configuration templates |

`.gitignore` excludes `Web.config`, `Web.Secrets.ConnectionStrings.config`, `Web.Secrets.AppSettings.config`, `.env`, `Documentation/`, `packages/`, `Publish/`, and `invoices/`. A comment in `.gitignore` says `Web.config` is kept in version control, and the next lines ignore `Web.config`. The clone contains the template only.

`Global.asax` loads a `.env` file from the application root into process environment variables on `Application_Start`. `functions.GetAppSettings` is used for keys; `OAuthService` comments say environment variables and `.env` are preferred, then Web.config. The exact lookup order is in `functions.GetAppSettings`.

## Configuration keys

From `Web.config.template` (non-secret):

| Key | Template value |
|---|---|
| `VAT` | `15` |
| `APPNAME` | `Pernod-Online` |
| `WEBSITE` | `https://www.pernodstaff.co.za` |
| `EMAIL` | `maanie@overdrive.co.za` |
| `SmtpServer` / `SmtpPort` | `smtp.pernodstaff.co.za` / `587` |
| `SmtpUsername`, `FromEmail` | `no-reply@pernodstaff.co.za` |
| `FromName` | `Pernod Ricard` |
| `BccEmail` | `maanie@overdrive.co.za` |
| `OrderQueriesContact` | `Gloria - gloria.kok@pernod-ricard.com` |
| `OrderFailureAlertEmail` | Commented out. Blank means failure mail goes to the user only. |
| `AzureScopes` | `openid email profile offline_access User.Read` |
| `AllowSsoBypass` | `false` |

Secrets template (`Web.Secrets.AppSettings.config.template`): `KEY`, `SmtpPassword`, `AzureClientSecret`.

Connection template: connection name `Pernod_ConnectionString`, provider `System.Data.SqlClient`. Server, catalog, user, and password are placeholders. SQL scripts `USE [PSOrder]`.

Session template: `mode="SQLServer"`, `cookieless="true"`, `timeout="1000000"`, `regenerateExpiredSessionId="true"`. The comment says the session connection string must be kept in sync with the secrets file and cannot use `configSource`. Production values are **Unknown**.

`functions.GetConnectionString` defaults to `Pernod_ConnectionString`.

## Request pipeline

1. Browser loads a Web Forms page.
2. The master page sets `Session("Key")` from app setting `KEY` and enforces module login.
3. Page script posts a JSON object to `JSON_DATA_SERVICE` web methods (`Pernod_MASTER_SERVICE`, `Pernod_MASTER_SERVICE_spSel`, `Pernod_MASTER_SERVICE_spUpd`).
4. The method checks the session key, then runs the stored procedure named by `spAlias`.
5. Values prefixed `SESSION@` are replaced with session entries. `SESSION_` keys do the same.
6. For `Pernod_MASTER_SERVICE`, cart limits, Employee# checks, password hashing, and order emails run in the VB method around the procedure call.

`maxJsonLength` in the template is `500000000`. `customErrors` is `RemoteOnly` with 404 → `404.html`.

Response headers in the template: `X-Frame-Options: sameorigin`, `X-XSS-Protection: 1; mode=block`, `Strict-Transport-Security: max-age=31536000`, `X-Content-Type-Options: nosniff`. `X-Powered-By` is removed.

## Authentication

`OAuthService` uses Azure AD v2.0 authorize and token endpoints, PKCE (`S256`), a nonce, and state stored with `OAUTH` procedures (`App_Data/DbScripts/SSO/`). Return URLs for admin and merch are stored so cookieless session loss still routes the callback. Tokens are encrypted with `TokenEncryption` before they are saved (the class encrypts with the app key). Graph user info is `GET https://graph.microsoft.com/v1.0/me`. HTTP timeout is 30 seconds. User agent is `PernodStaff/1.0`.

Password hashing for non-AD users is `PasswordService` (PBKDF2-SHA256, 100,000 iterations). `Hasher.vb` is a separate AES-and-SHA512 helper (namespace usage includes `HashLibrary` on `rep_login.aspx.vb` and `activate.aspx.vb`). Rep login does not hash in the page; it passes the password into `REPS_spLOGIN`.

MFA codes are 6 digits from `RNGCryptoServiceProvider`, saved with `EMAILAUTH_spSAVE_CODE`, checked with `EMAILAUTH_spVERIFY_CODE`.

## Database

Scripts are idempotent-style `ALTER PROCEDURE` or `IF OBJECT_ID` / `IF NOT EXISTS` changes. They are not run by the website. Apply order is the numeric prefix inside each folder. Later cart scripts replace earlier definitions of the same procedures.

| Folder | What it changes |
|---|---|
| `StaffPortal/001`–`017` | `SHOPPING_CART_SpSend`, `SpIns`, `SpUpd`; `ERROR_LOG.orderid`; an audit query for users over 12 bottles |
| `SSO/000`–`014` | `isADUser`, `PasswordHash`, login and forgot procedures, OAuth tables, email-auth codes, login history |
| `Merch/001`–`004` | Merch tables, `merchaccesslevel`, user procedure updates, invoice file path, mini user select including inactive |

Merch tables: `MERCH_CATEGORY`, `MERCH_BRAND`, `MERCH_PRODUCT`, `MERCH_INVOICE`, `MERCH_INVOICE_LINE`, `MERCH_INVENTORY_MOVEMENT`, `MERCH_STOCK_EDIT_LOG`, `MERCH_SETTINGS`.

Staff tables used by code and not created in these scripts (they already exist in `PSOrder`): `USERS`, `SHOPPING_CART`, `ORDER_HEADER`, `ORDER_DETAIL`, `ORDER_TIMES`, `PRODUCTS`, `STATUS_CODES_HEADER`, `ERROR_LOG`, and the rep tables behind `REPS_spLOGIN`. A full schema diagram is **Unknown** beyond these references.

`JSON_DATA_SERVICE` also runs direct SQL for user lookup, duplicate Employee#, order confirmation reads, and failure-email cart snapshots.

## Integrations

| Integration | Implementation |
|---|---|
| Azure AD / Graph | `OAuthService.vb` |
| SMTP | `EmailService.vb`. TLS 1.2 (`SecurityProtocol` 3072). `ServerCertificateValidationCallback` returns true for every certificate. |
| SQL Server | `System.Data.SqlClient` |
| Excel | ClosedXML on merch product list, stock import, and reports |
| PDF | iTextSharp 5.5.13.3 on POS and invoice download. Comment says iTextSharp is end of life. |
| JDE | Not in this repo. `ERROR_LOG` rows with component `PernodIntegration` and operation `CreateOrder` change the bottle count. |
| SMS | `SMS.vb` → `http://api.payless4sms.co.za/sms/json`. No caller. Credentials are hardcoded in that file and should be rotated. They are not copied into this document. |

## Background work

No Windows service, Hangfire job, queue, or scheduled task is in the repository. `Global.asax` `Application_Start` only loads `.env`. Order creation is synchronous inside the web method. Anything that later calls JDE is **Unknown**.

## Security notes (as implemented)

- Secret files are gitignored. Templates use placeholders.
- `SMS.vb` still contains live-looking gateway credentials in git history and in the current file.
- JSON gateway executes caller-selected procedure names after a shared session key check.
- Admin master checks `userid`, not `accesslevel = 1`.
- Email certificate validation accepts all certificates.
- Session template is cookieless with a very large timeout. Cookieless session ids appear in the URL.
- `compilation debug="true"` is set on the template.
- Login failures and successes are written to login history.
- Password comparison uses a constant-time XOR in `PasswordService`.

These are observations from the source, not a penetration test.

## Development and deployment

- IDE artefacts: `.vs/` is gitignored. A local `applicationhost.config` was present in the working tree under `.vs` and is not product source.
- A comment in `JSON_DATA_SERVICE` mentions testing from `http://localhost:59364`.
- Bitbucket Pipelines (`bitbucket-pipelines.yml`): on `main` and on every pull request, run `sonarsource/sonarqube-scan:3.0.2` with `SONAR_HOST_URL` and `SONAR_TOKEN` from repository variables. Docker service memory is 3072 MB. Clone depth is full. `EXTRA_ARGS` includes `-Dsonar.scanner.forceEmbeddedJava=true`.
- `sonar-project.properties` sets `sonar.projectKey=overdrive_pernodstaff_a4ed109a-0218-4015-9fd5-d03d73aef4ab`.
- No unit or UI test project is in the tree. No coverage gate is configured in the pipeline file.

## Dependencies (`packages.config`)

| Package | Version |
|---|---|
| Newtonsoft.Json | 13.0.3 |
| ClosedXML | 0.95.4 |
| DocumentFormat.OpenXml | 2.12.3 |
| ExcelNumberFormat | 1.0.10 |
| iTextSharp | 5.5.13.3 |
| BouncyCastle | 1.8.9 |
| Microsoft.CSharp | 4.7.0 |

Report Viewer is referenced in the template (`Microsoft.ReportViewer.WebForms` 15.0.0.0) for `Reserved.ReportViewerWebControl.axd`. No `.rdlc` report was found in the search of application files. Whether reports are deployed beside the site is **Unknown**.

## Session keys

| Key | Module |
|---|---|
| `userid`, `accesslevel`, `DisplayName`, `isADUser`, `DSVAccount`, `DSVAccountStatus`, `CashAccount`, `ReceiveStatements`, `REP_ID` | Order portal |
| `Key` | All masters that call the JSON service |
| `ADMIN.ReturnUrl` | Admin SSO |
| `MERCH.userid`, `MERCH.accesslevel`, `MERCH.DisplayName`, `MERCH.isADUser`, `MERCH.MFA.UserId`, `MERCH.MFA.Email`, `MERCH.ReturnUrl`, `MERCH.CART` | Merch |
| `REP_ID`, `REP_FULLNAME` | Rep |

`functions.ClearSessions` clears order and OAuth keys. `ClearMerchSessions` clears merch keys. `ClearAdminSessions` nulls keys that start with `ADMIN.`.