# Technical documentation — superbowl

Evidence date: 2026-10-02.

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

## What this repository is

One Visual Studio solution, `Superbowl/Superbowl.sln` (format 12.00, Visual Studio 15, minimum 10.0). One web application project, `Superbowl/Superbowl/Superbowl.vbproj`.

| Item | Value | Evidence |
|---|---|---|
| Language | VB.NET | `.aspx.vb` code-behind |
| UI | ASP.NET Web Forms | `.aspx`, `Admin.Master` |
| Framework | .NET Framework 4.8 | `TargetFrameworkVersion` v4.8 |
| Assembly | `Superbowl`, version `1.4.3.0`, file version `1.0.0.0` | `My Project/AssemblyInfo.vb` |
| Namespace | `Superbowl` | `Global.asax.vb`, `SecureConfig.vb` |
| Root namespace in SQL helper | `Superbowl.sqlDB` | `sqlDB.vb` (imported as `Superbowl.sqlDB`) |
| Solution source-control binding | TFS `http://tfs01:8080/tfs/vs_projects_2018` | `Superbowl.sln` `TeamFoundationVersionControl` |
| Current git remote | `git@bitbucket.org:overdrive/superbowl.git` | this clone |

`ProductVersion` in the vbproj is `9.0.21022` (an old project-file marker). It is not the assembly version.

## Repository layout

```text
.gitignore                          ignores Superbowl/.env only
BuildProcessTemplates/              TFS 11.x XAML (AzureContinuousDeployment, DefaultTemplate, LabDefaultTemplate, UpgradeTemplate)
docs/index.md                       project memory; TASK-001 still marked in progress
docs/tasks/TASK-001.md              SC Admin task
docs/testsprite_run.md              2026-09-15 verification notes
Superbowl/Superbowl.sln
Superbowl/.env.example              placeholder connection string
Superbowl/Superbowl/                the web site
  *.aspx / *.aspx.vb                pages listed in features.md
  Admin.Master                      menu and header
  sqlDB.vb                          SQL string builders and execution
  UserPermissions.vb                session role checks
  SecureConfig.vb                   .env and environment connection string
  EnvConnectionStringsExpressionBuilder.vb
  AuditEntity.vb                    audit snapshot helper
  Duodecimal.vb                     year-month arithmetic
  RepQF.vb                          quantity-forecast helper (menu link empty)
  Global.asax                       empty Application_Start
  Web.config
  Scripts/                          hand-run T-SQL
  calendar_control/                 client calendar used by log and promo pages
  My Project/PublishProfiles/Profile1.pubxml
```

Not in git at `a6f4b8b`, although the onboarding scan listed them on the scan host: `sonar-project.properties`, `sonarqube_scan.bat`, `Superbowl_20260403`. There is no README, no test project, and no Bitbucket Pipelines or Azure DevOps YAML file.

`git ls-files` shows `Superbowl/Superbowl/bin/` (dll, pdb, xml, `Superbowl.dll.config`) and `Superbowl/Superbowl/obj/Debug/` are tracked. `obj/Debug` contains assembly-attribute files for .NET Framework 4.6.1, 4.6.2, and 4.8. Those are build outputs checked into git, not a second application. The source of the connection string is `Web.config` plus `SecureConfig`, not the checked-in `bin` config. `Web.config` `connectionStrings` is empty on purpose.

## Request path

1. IIS (or IIS Express) serves `index.aspx` as the default document (`system.webServer/defaultDocument`).
2. Login reads the Windows identity (`authentication mode="Windows"`).
3. `sqlDB` opens `csStockControl`.
4. Later pages use `Admin.Master`. SqlDataSource controls bind with `<%$ EnvConnectionStrings:csStockControl %>`, resolved by `EnvConnectionStringsExpressionBuilder` to `SecureConfig.GetConnectionString`.
5. Mutations call `UserPermissions.Demand*`, which throws `HttpException(403)` on failure.
6. `customErrors mode="Off"`, so ASP.NET error detail is not replaced by a generic page.

`pages controlRenderingCompatibilityVersion="3.5"` and `clientIDMode="AutoID"`. `httpRuntime executionTimeout="36000"` (seconds). `compilation debug="true"` `strict="false"` `explicit="true"`.

## Configuration and secrets

`SecureConfig` (`Superbowl/Superbowl/SecureConfig.vb`):

- Loads a `.env` file from the directory **above** the web application physical path (one level above `Superbowl/Superbowl`, or above `bin` if the base path ends in `bin`).
- Sets each `KEY=value` into the process environment. Lines starting with `#` are skipped. Double-quoted values are unwrapped.
- `GetConnectionString` accepts only the name `csStockControl`.
- The value is environment variable `RGBC_SUPERBOWL_CONNECTION_STRING`.
- If the variable is missing, it throws `ConfigurationErrorsException` naming the variable and the `.env` location.
- The file is loaded once per process (`SyncLock`).

`.env.example` shows the shape: `Data Source`, `Initial Catalog=Superbowl`, SQL authentication user and password, `Connect Timeout=60`. The example is placeholders. `.gitignore` ignores `Superbowl/.env`.

`Web.config` `connectionStrings` is an empty element. AppSettings that remain:

| Key | Committed value | Use |
|---|---|---|
| `blnDebug` | `true` | Gates master-page debug login and the "redirect if anonymous" branch |
| `UNAME`, `UDOMAIN`, `UID`, `UGROUP`, `UGROUPID`, `ULEVEL` | empty or `0` | Legacy shared slots. `ULEVEL` is not the authorization source anymore. Login still writes domain, id, and group into appSettings |
| `PAGESIZE` | `50` | Grid page size |
| `MAXPAGESIZE` | `1000` | "Show more" |
| `ShowMore`, `strPage`, `intListIndex`, `strListFilter`, `intFilterValue` | | Shared list-navigation state |

`Global.asax` does not write configuration. Comment: connection strings come from the environment, not from a Web.config write.

## Authentication and authorization

- IIS Windows authentication supplies `Request.LogonUserIdentity.Name`. The login query upper-cases it and matches `mstUser.strDomainLogin` where `SystemSuperbowl = 'Superbowl'`.
- Session keys used afterwards: `User`, `UGROUP`, `ULEVEL`.
- `UserPermissions.CurrentLevel` parses `Session("ULEVEL")` only.
- `IsFullAdmin`: level = 5. `CanEditProduct` and `CanEditDataCapturing`: level 5 or 4.
- There is no ASP.NET role provider and no `[Authorize]` attribute. Each page opts in by calling the module. Pages that do not call it are listed in `prd.md` FR-013 and the AdCheck lists.
- DEBUG plus `blnDebug` true synthesizes a level-5 session. See business rule 9.

Login failure and several save failures print `ex.Message` and `ex.StackTrace` into the page label.

## Data access

`sqlDB.vb` is the data layer: `SqlConnection` from `SecureConfig`, helpers `doQueryDS`, `doQueryBatch`, `GetRow`, `CountRows`, `CleanString`, `dbDate`, `SqlDate`, `WriteLog`, and a large set of `sql_*` functions that return SQL text.

Statements are string-concatenated. `CleanString` is used on many user fields and not uniformly on every fragment (some filters append dropdown values directly).

`doQueryBatch` runs the product-archive statement array.

`WriteLog` inserts a process log (used by export processing). Parameters include process name, action, message, optional stamp, and `blnEmail` default 1. Whether a mailer reads that flag is **Unknown** in this web project.

Warehouse tables (`dwdCustomer`, `dwdProduct`) are updated beside the master tables on several saves. Fact tables (`dwfSales`, `dwfOnconPromo`, `dwfOffconPromo`, `dwfACLisp`, `dwfQF`) are read, or written only by export processing (`dwfSales`). This application does not load the daily files; `Import.aspx.vb` is commented out.

### Tables named by this code (not a full schema)

Master and maps: `mstUser`, `mstUserGroup`, `mstUserGroupSuperbowl`, `mstCustomer`, `mstProduct`, `mstBrand`, `mstCompany`, `mstPrincipal`, `mstCountry`, `mstCustomerGroup`, `mstExports`, `mstKeyAccount`, `mstKeyAccount_Group` (SQL helper name), `mstRep`, `mstBranch`, `mstSegment`, `mstChannel` / channel-segment table used by `sql_get_ChannelSegment`, `mstProductCompetitorSet`, `mstUsageLog`, `mapACProduct`, `mapACStore`, `mstNot_RGBC_Customer` (migration source; UI retired).

Warehouse and facts: `dwdCustomer`, `dwdProduct`, `dwfSales`, `dwfOnconPromo`, `dwfOffconPromo`, `dwfACLisp`, `dwfQF`.

Operations: `sysCronJob`, `sysCronJobProducts`, `sysQuery`, `dlyImportLog`.

Snell area and super group tables are whatever `sql_get_SnellArea` and `sql_get_Snellsupergroup` address. A full `CREATE TABLE` set is not in the repo except `mstProductCompetitorSet` and the additive `ALTER` scripts.

Period keys in the UI are `yyyyMM` strings. `Duodecimal` converts those to a month index `year * 12 + month - 1`.

## Product archive hand-off

`ProductArchiveDetails.btnArchive_Click` runs one batch (see `prd.md` FR-008) and inserts:

```text
sysCronJob.Type   = ArchiveProduct
sysCronJob.Status = New
sysCronJob.param  = strProdCodeNew={new}|strProdCodeOld={old}
sysCronJob.dtCronJob = yyyyMMddHHmm
```

The consumer is not in this repository. Silicon Ark documents that worker as `superbowl_cronjob`. This file does not restate that worker's behaviour.

## Month-end file and mail

`MonthEnd.btnProcess_Click` runs the `sysQuery` text `bfbe-Sales_Month_to_Date` after replacing `200910` and `20091001`, then writes `{AppDomain base}\BFBE\ZA_{period}_INVW.txt` (tab, no quotes) via `CreateCSVFile`.

`MailBrandon` builds `SmtpClient("localhost")`, from `superbowldaily@rgbc.co.za`, subject `Superbowl Weekly [dd MMM yyyy]`, and attaches `ZA_{period}_INVW.txt` and `ZA_{period}_CUST.txt`. The recipient list is compiled into the method. `btnProcess_Click` does not call it.

## Deployment

The only publish profile is `My Project/PublishProfiles/Profile1.pubxml`:

- Method: file system
- `publishUrl`: `C:\Users\pj\Desktop\sb`
- `DeleteExistingFiles`: true

That path is a developer desktop folder, not a server. **Unknown:** production IIS site, hostname, app-pool identity, and how `.env` or the environment variable is set on the server.

`docs/testsprite_run.md` (2026-09-15): a Visual Studio MSBuild Debug build passed with 0 errors. `dotnet msbuild` failed because `Microsoft.WebApplication.targets` was not installed. Browser checks on IIS Express covered SC Admin product edit, read-only customers/users/month-end, viewer product read-only, and admin customer and month-end. Export Sales was not loaded (no `mstExports`).

No automated test assembly, no PHPUnit/TestSprite plan file, and no pipeline definition are in the tree. `BuildProcessTemplates/*.xaml` are legacy TFS build templates and are not wired to a current pipeline in this repo.

## Security notes (as implemented)

- `Web.config` no longer stores the SQL password. **Implemented.** **Discrepancy:** the tracked build output `Superbowl/Superbowl/bin/Superbowl.dll.config` still has a `csStockControl` connection string, including a SQL password, plus a second connection string in a comment. Those values are not copied here. Runtime code uses `SecureConfig`, so a freshly built site should not read that file for `<%$ EnvConnectionStrings:csStockControl %>`. The checked-in file is still a secret in git. **Requires confirmation** whether it should be removed from the source repository.
- Authorization for guarded postbacks is server-side, not only disabled buttons. **Implemented** on the pages that call `Demand*`.
- `customErrors` Off, `directoryBrowse` enabled, `debug` true are committed. **Implemented.**
- Login and some handlers return stack traces to the client. **Implemented.**
- Several grid filters concatenate selected values into `FilterExpression`. **Implemented.**
- `Add_mstCountry_and_refCountryID_FKs.sql` adds the country foreign keys `WITH NOCHECK`. **Documented** in that script.
- SC Admin deployment script takes a serializable transaction and will not silently rewrite an existing `SC Admin` row that has the wrong level. **Documented.**

## Related repositories

| Repository | Relationship evidenced here |
|---|---|
| `superbowl_cronjob` | This app inserts `sysCronJob` `ArchiveProduct`. Comments say sales and related archive updates moved there |
| `superbowl_daily`, `superbowl_daily_2` | Not referenced by name in this web project. `dlyImportLog` and the commented import page are the local traces of a daily load. Which executable writes the log is **Unknown** from this repo alone |

Their Silicon Ark folders are separate onboarding sets. They were not re-audited for this document.

## Local build

Open `Superbowl/Superbowl.sln` in Visual Studio with the .NET Framework 4.8 targeting pack and web application targets. Copy `Superbowl/.env.example` to `Superbowl/.env` (the directory above the web project folder) and set `RGBC_SUPERBOWL_CONNECTION_STRING` to a Superbowl catalog. Do not commit `.env`.

`docs/index.md` says the review worktree had no `.env`, so Export Sales and the role matrix could not be verified there.