# Technical documentation — stock_control (RGBC Stock Control)

Evidence date: 2026-10-02.

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

## Application type

ASP.NET Web Forms site, Visual Basic, one project `Stock_Control` in `Stock_Control.sln`.

| Item | Value | Evidence |
|---|---|---|
| Solution format | Visual Studio 15 (`VisualStudioVersion = 15.0.26228.43`) | `Stock_Control.sln` |
| Project flavour | Web application (`{349c5851-65df-11da-9384-00065b846f21}`) plus VB | `Stock_Control.vbproj` |
| Language | Visual Basic, `OptionStrict` off, `OptionExplicit` on, `OptionInfer` on | project file |
| Target framework | `v4.6.2` on the project. `Web.config` compilation `targetFramework="4.8"` | both files. **Requires Confirmation** which runtime the server uses |
| Old tools version | 4.0 | project file |
| Output | class library into `bin\` | project file |
| Root namespace | `Stock_Control` | project file |
| Assembly version | 1.2.4.0 (file version 1.0.0.0) | `AssemblyInfo.vb` |
| UI version string | "Version 2.0.0" dated 1 January 2026 | `Admin.Master` |
| Web root | the `Stock_Control` project folder | pages live beside the code-behind |

`UseIIS` is false. `DevelopmentServerPort` is 4130. `NTLMAuthentication` in the project flavour is false, while `Web.config` sets `authentication mode="Windows"`. **Requires Confirmation** how the deployed site is authenticated. The login code depends on `Request.LogonUserIdentity`.

## Repository layout

```text
stock_control/
  .env.example
  .gitignore                  # ignores .env only
  Stock_Control.sln
  Stock_Control.v11.suo       # binary Visual Studio user options, committed
  Stock_Control.vssscc
  Stock_Control/
    *.aspx, Admin.Master, Styles.css, rgbc_logo.jpg, Web.config, Global.asax
    *.vb                      # code-behind and helpers
    My Project/               # assembly info, settings, publish profile
    Scripts/                  # README and three SQL scripts
    Imports/                  # sample or leftover source files, including cognos14.txt and xls
    Imported/                 # renamed copies of an opening workbook
```

No `packages.config`, NuGet `PackageReference`, `Dockerfile`, pipeline YAML, or test project is in the tree. References are framework assemblies only (`System`, `System.Data`, `System.Web`, `System.Configuration`, and related).

The solution still records Team Foundation Server:

```text
SccTeamFoundationServer = http://tfs01:8080/tfs/vs_projects_2018
```

The git remote is Bitbucket. Whether TFS is still in use is **Unknown / Requires Confirmation**.

## Request pipeline

`Global.asax.vb` `Application_Start` does not open the database and does not write `Web.config`. The comment says a missing environment variable should surface on the first page that needs the database.

`Default.aspx` redirects to `index.aspx`.

`index.aspx` is not on `Admin.Master`. Every other page in the project uses `Admin.Master`. The master `Page_Load` is the only request gate. It does not run when `blnDebug` is true, except for the DEBUG auto-login block.

`customErrors mode="Off"`. `compilation debug="true"`.

There is no `authorization` element, no request validation override, and no custom HTTP module.

## Configuration and secrets

`Web.config` `connectionStrings` is empty. `appSettings` holds `blnDebug` (`true`), empty user keys, `PAGESIZE` 20, and list-index defaults.

`SecureConfig` loads a `.env` file once per process:

- Application path from `HttpRuntime.AppDomainAppPath`, else `AppDomain.CurrentDomain.BaseDirectory`.
- If that path ends with `\bin`, it goes up one more level.
- The `.env` file is the parent of that directory (comment: solution root, parent of the project folder).
- Lines `KEY=VALUE`. `#` comments. Optional surrounding double quotes are stripped.
- Values are set with `EnvironmentVariableTarget.Process`.

`GetConnectionString("csStockControl")` reads `STOCK_CONTROL_CONNECTION_STRING`. Any other name throws if the variable is empty. A missing variable throws `ConfigurationErrorsException`.

`sqlDB.GetConnectionString` prefers a non-empty `Web.config` connection string named `csStockControl`, otherwise `SecureConfig`. The expression builder `EnvConnectionStrings` does the same for `<%$ EnvConnectionStrings:csStockControl %>`. A search of the aspx files did not show that expression in use. The data layer uses `sqlDB.conn`.

`.env.example` documents the variable and an example SQL Server connection to catalog `Superbowl`, with `Persist Security Info=True` and a 60-second connect timeout. Commented keys `SQL_CONNECTION_DEBUG` and `SQL_CONNECTION_RELEASE` are described as unused. `My.Settings` in `Web.config` has empty `SQLConnectionDebug`, `SQLConnectionRelease`, and `SQLConnectionReleaseWSBS`.

`.gitignore` contains only `.env`.

**Do not commit a filled `.env`.** The example file has no real password.

## Data access

`sqlDB` holds `Public Shared conn As New SqlConnection(GetConnectionString())`. The connection object is created when the type is first used. There is no `Open`/`Close` wrapper in the helper. `SqlDataAdapter.Fill` opens and closes as needed. **Inferred:** a stale connection string requires an app-domain recycle because the connection is built once.

Queries are concatenated SQL strings. `CleanString` escapes quotes for some user-typed values (brand name, domain login, user name). Product codes, periods, and several filters are concatenated without it. This is **Implemented** behaviour, not a recommendation.

`getRow` swallows fill exceptions and then reads `Tables(0).Rows(0)`, which throws if the fill failed. `updatePPBalance` swallows all exceptions.

Culture helpers (`dbDate`, `sqlDate`, `appDate`) assume `yyyyMMddHHmm` or `dd-MM-yyyy` layouts. Several `Substring` calls assume the string is long enough.

## Database objects the code names

No `CREATE TABLE` script is in the repository. Columns below are those the SQL strings read or write. Types are **Inferred** from usage (strings for periods, doubles for quantities, integers for ids and flags).

| Object | Used for |
|---|---|
| `mstUser` | `UserID`, `strUserName`, `strDomainLogin`, `refUserGroupID`, `refUserGroupSuperbowlID`, `strLastUser`, `dtStamp`. Password column only in unused functions |
| `mstUserGroup` | `UserGroupID`, `strUserGroup`, `SystemStockControl` |
| `mstUserGroupSuperbowl` | joined on login for Superbowl group name, `SystemSuperbowl`, `intLevel` |
| `mstBrand` | `BrandID`, `strBrand`, `intLeadMonths`, `dblVariance`, `refBrandManagerID`, `refBrandManager2ID`, `refPrincipalID`, `blnActive`, `strLastUser`, `dtStamp` |
| `mstBrandManager` | `BrandManagerID`, `strBrandManager`, `strEmail` (report SQL) |
| `mstPrincipal` | `PrincipalID`, `strPrincipal`, `strFYStart`, `strFYEnd` |
| `mstProduct` | `strProdCode`, `strLongProdDesc`, `strProductDesc`, `refBrandID`, `intPack`, `intSize`, `dblFlat`, `blnActive`, `blnCompProduct`, `strCategory2`, `strBrandVarietal` |
| `mstPeriod` | `strPeriod`, `intCalendarYear`, `intCalendarMonth`, `strMonth` |
| `stkProductPeriod` | `mstProdCode`, `mstPeriod`, `dblOpening`, `dblQF`, `dblBMA`, `dblActual`, `dblOrders`, `dblReceipts`, `dblExports`, `dblDamages`, `dblRepack`, `dblClosing`, `dblMonths`, `dblOpeningImports`, `strLastUser`, `dtStamp`, `strLastUserSM`, `dtStampSM`, `strLastUserUpdate`, `dtStampUpdate` |
| `stk9L` | scratch aggregate by brand, period, category, varietal |
| `stkConfirm`, `stkConfirmSM` | twelve `dbl1`…`dbl12` values, product, period, user, stamp |
| `stkImportLog` | `mstPeriod` plus boolean flag columns updated by name (`blnOpening`, `blnCognos14`, `blnBMA`, and others). Exact column list is **Unknown** because it is rendered dynamically |
| `stgCognos14` | sales staging. Deleted at the start of a Cognos import |
| `dwfSales` | `lngUnitsTotal`, `dbl9l`, `dbl9lTotal`, `refPeriod`, `refProdCode`, `refCustomerNo`, `refDocumentNo` |
| `dwfQF` | `strPeriod`, `strProdCode`, `strCustomerNo`, `dblBudget9l`, `dblPercentage`, `strLastUser`, `dtStamp` |
| `tmpQFSC` | scratch for quota forecast |
| `mstCustomer`, `mstBranch` | customer and branch codes for quota forecast and the customer-dimension update |
| `dwdCustomer` and many `mst*` dimensions | only in `sql_update_Customer_dw`, which has no caller in the pages |
| `mstUsageLog` | login audit |
| `dlyImportLog` | read for `MAX(dtStamp)` during Cognos post-processing |

Catalog in the example connection string: `Superbowl`.

## Pages

| Path | Class | Notes |
|---|---|---|
| `Default.aspx` | `_Default` | redirect |
| `index.aspx` | `index` | login, no master |
| `Home.aspx` | `Home` | unused by the menu |
| `ProductPeriod.aspx` | `ProductPeriod` | home grid. `Trace="false"` |
| `ImportList.aspx` | `ImportList` | log and QF button |
| `Import.aspx` | `Import` | file import |
| `BMAReport.aspx` | `BMAReport` | variance |
| `SCReport.aspx` | `SCReport` | column pivot |
| `UserList.aspx`, `UserDetails.aspx` | user maintenance | `SqlDataSource` delete |
| `BrandList.aspx`, `BrandDetails.aspx` | brand maintenance | |

Menu (`Admin.Master`): Product Period, Imports, Variance Report, Stock Control Report.

`ProductPeriod.FindControls` walks a fixed control index (`Master.Controls(3)` … `Controls(23)`). Comment at the top: adding a control before the grid requires that index to change. **Implemented** fragility.

## Import file contract

Folder: application base directory plus `Imports`. After import, `Imported`.

Excel provider: `Microsoft.Jet.OLEDB.4.0`, Excel 8.0, sheet `Import$`. Text provider: Jet text, `HDR=No`, `FMT=Delimited`.

| File | Recogniser | Non-empty columns required | Period |
|---|---|---|---|
| `OpeningYYYYMM.xls` | prefix `Opening` | 3 | `YYYYMM` must exist in `mstPeriod` |
| `ForecastYYYYMM.xls` | prefix `Forecast` | 13 | same |
| `Direct ReceiptsYYYYMM.xls` | prefix `Direct Receipts` | 5 | same |
| `CustomerReps.xls` | prefix `CustomerReps` | 5 | none |
| `BM AdjustmentsYYYYMM.xls` | prefix `BM Adjustments` | 3 | characters 15–20 of the file name (`Substring(14, 6)`) |
| `cognos14.txt` | exact name | comment: 17 columns for Cognos 14 (v1.2.7 reads nett from column 17 and `dblNettEx` from column 14) | many periods inside the file |

Commented recognisers, not called: Receipts (9 columns), Damages (8), Repack (8 or 9).

`CheckColumns` counts cells in the first data row that are non-empty strings. It does not compare header names.

Opening row: column 0 is the product code, column 2 is the quantity. Column 1 is not named in code. Product `05016` is multiplied by 9. See business rules.

BM adjustment row: item 0 product code, item 2 the adjustment added to `dblBMA`.

Cognos insert argument order after the swap: doctype, docno, cusno, cusname, rep, period, movement, prodcode, proddesc, packs, packsize, units, ninelce, ordernum, nett from column 17, measure, gsv, dblNettEx from column 14. `sql_insert_stgCognos14`.

Opening files, when the fail count is 0: a DEBUG compile copies the file into `Imported` with a timestamp suffix. A Release compile moves it. Cognos 14 uses a different test: it moves the file only when the fail count is 0 and app setting `blnDebug` equals the string `True`. Otherwise it copies. The committed `Web.config` has `blnDebug` true, so a clean Cognos import moves `cognos14.txt`. Other file types were not traced line by line. **Implemented** for the two paths above.

`yes.gif` and `no.gif` are referenced by the import log and are not in the file list. The log images will 404 unless they are deployed beside the site and not committed.

## Background work, queues, storage, monitoring

None in this repository. Import and `RepQF` run on the request thread. `RepQF.Insert` can emit one insert per product × customer × period.

Disk storage is the `Imports` and `Imported` folders under the application directory. Sample files are committed:

- `Stock_Control/Imports/cognos14.txt`
- `Stock_Control/Imports/Opening201707.xls`, `Opening201808.xls`, `Opening201809.xls`, `Opening201901.xls`
- `Stock_Control/Imported/Opening201707.xls` plus two timestamped copies

Those files were not parsed. They may contain operational data. **Requires Confirmation** before they stay in git.

## Deployment

Publish profile `My Project/PublishProfiles/sc.pubxml`:

- Method: file system
- `publishUrl`: `C:\Users\pj.SILICON\Desktop\sc`
- `DeleteExistingFiles`: false

That path is a desktop folder, not a server. **Unknown** whether it is still how releases are made. No Web Deploy publish profile, no transform, and no Bitbucket pipeline file.

`Stock_Control.vbproj.user` is committed. It records the last solution configuration `Debug|Any CPU` and ASP.NET debugging flags. It does not contain a start URL or a connection string.

## Security notes (as implemented)

- Windows authentication is configured. The application then trusts `appSettings` for the rest of the session-equivalent.
- `customErrors` off and login `Catch` print the stack trace.
- SQL is concatenated.
- `blnDebug` true is committed.
- DEBUG builds impersonate user id 1 as admin when no domain user is set.
- No CSRF token beyond the Web Forms view state. No rate limit. No password hashing path on the live user save.
- `Persist Security Info=True` is in the example connection string.

These are observations for people who maintain the site. They are not a penetration-test report.

## Development and testing

- Open `Stock_Control.sln` in Visual Studio 2017 or a later Visual Studio that still builds .NET Framework 4.6.2 / 4.8 Web Forms.
- Copy `.env.example` to `.env` at the solution root and set `STOCK_CONTROL_CONNECTION_STRING`.
- The project expects the Visual Studio development server on port 4130, or IIS pointed at the project folder with Windows authentication.
- Jet OLEDB 4.0 must be installed for import. On 64-bit Windows that usually means a 32-bit app pool. **Requires Confirmation** for the host.
- No automated test suite exists. `Scripts/README.md` tells the reader to back up `stkProductPeriod` and run the recalc script in SSMS. That script does not match `updatePPBalance` after `02bce8c`.

## Source-control history worth keeping next to the code

| Commit | Why it matters |
|---|---|
| `1c27535` 2020-01-14 | Initial commit. The copyright year is 2008, so this git history is not the start of the product |
| `ee027b3` 2020-09-11 | Zero stock balance: DBNull check, then value > 0, then compare opening import to opening |
| `68021e4`–`775036d` 2021-03 | Direct sales |
| `7854d52` 2024-06-19 | `ISNULL` in `sql_get_PP` |
| `9ab3391`–`1d856ae` 2026-02 | Environment connection string |
| `d2bf192` 2026-02-12 | February end-period on the stock control report only |
| `6550ac6` 2026-02-26 | Previous-month closing. Reverted |
| `02bce8c` 2026-03-16 | Current opening-balance behaviour |

## SonarQube

Not configured in git at this revision. The onboarding issue's top-level listing included `sonar-project.properties` and `sonarqube_scan.bat`. They are not in the clone of `02bce8c`. **Unknown** whether a scanner is applied from outside the repository.