# Technical documentation — forecast (RGBC Forecast Control)

Evidence date: 2026-10-14.

Source: `git@bitbucket.org:overdrive/forecast.git`, `master` at `6aac1d2` (2026-09-01, merge of pull request #1).

Credential values are not copied. `.env.example` contains placeholders only.

## Stack

| Layer | What the repo shows |
|---|---|
| Language | Visual Basic, `OptionExplicit` on, `OptionStrict` off, `OptionInfer` on |
| UI | ASP.NET Web Forms, master page `Admin.Master`, ASP.NET Menu and GridView |
| Runtime | .NET Framework. Project `TargetFrameworkVersion` is `v4.6.2`. `Web.config` `compilation targetFramework` is `4.6.1`. `pages controlRenderingCompatibilityVersion` is `3.5`. `httpRuntime` comment mentions 4.6.2 and is not applied as an attribute |
| IDE | Solution format Visual Studio 15 (`RGBC Forecast.sln`). `OldToolsVersion` in the project is `4.0` |
| Database | SQL Server via `System.Data.SqlClient` |
| Host | IIS implied by Windows authentication, `system.webServer/defaultDocument`, and Web Forms. Site URL is not in the repo |
| Tests | None. No test project, no test assembly reference |
| CI | Bitbucket Pipelines, SonarQube scan pipe `sonarsource/sonarqube-scan:3.0.2` on branch `master`, clone depth full, step size `4x` |
| Source control history | Git on Bitbucket since init `e13eb5e` (2023-01-23). Solution still contains a Team Foundation binding to `http://tfs01:8080/tfs/vs_projects_2018` |

Framework references only: `System`, `System.Data`, `System.Web`, `System.Web.Extensions`, `System.Configuration`, `System.Xml.Linq`, `System.Drawing`, `System.EnterpriseServices`, `System.Web.Mobile`, and related framework assemblies. No `packages.config`.

## Repository layout

```text
forecast/
  RGBC Forecast.sln
  bitbucket-pipelines.yml
  sonar-project.properties          project key overdrive_forecast
  .env.example
  .gitignore                        ignores .env, bin, obj, .vs, *.user, *.suo
  RGBC Forecast/
    *.aspx, Admin.Master            pages
    Branding.vb                     brand grids and fiscal calendar
    QFGenerator.vb                  class QFGen
    brand.reports.vb                class BrandReport
    db.generic.vb                   class db
    db.cls.vb                       empty subclass xdb
    Web.config
    scripts.js, Styles.css
    App_Code/VB/                    SecureConfig, expression builder, shared functions, Duodecimal
    content/                        logo, loader, planning screenshot, demo xls
    My Project/                     assembly info, settings, publish profile
```

`RGBC Forecast.v11.suo` and `RGBC Forecast.vssscc` are in the tree. `.gitignore` lists `*.suo` but the suo file is already tracked. `RGBC Forecast.vbproj.user` is also present.

There is no README.

## Application structure

Root namespace `RGBC_Forecast`. Web project output type Library, assembly name `RGBC Forecast`.

| Page | Master | Class | Role |
|---|---|---|---|
| `Index.aspx` | none | `Index` | Login |
| `Home.aspx` | yes | `Home` | Landing. Report call commented out |
| `Planning.aspx` | yes | `Planning` | Future-year plan |
| `Adjustments.aspx` | yes | `Adjestments` | Spelling is the compiled class name |
| `ReportConsole.aspx` | yes | `ReportConsole` | Two reports |
| `OpenSeason.aspx` | yes | `OpenSeason` | Season dates |
| `MyBrands.aspx` | yes | `MyBrands` | User brands and region |
| `Comments.aspx` | yes | `Comments` | Adjustment notes |
| `Help.aspx` | yes | `Help` | Empty |
| `HelpPlanning.aspx` | yes | `HelpPlanning` | Spreadsheet link and image |
| `AdjustmentsComments.aspx` | yes | `AdjustmentsComments` | Empty |
| `zSandbox.aspx` | yes | `zSandbox` | Dev menu. Generate path disabled |
| `TestDbConnection.aspx` | none | `TestDbConnection` | SQL and identity probe. Not in the menu |

`Global.asax` class `Global_asax` calls `SecureConfig.LoadEnvironmentVariables()` on start. Other application events are empty.

`Duodecimal` converts `yyyyMM` and dates to a month index (`year * 12 + month - 1`) for period arithmetic.

## Configuration and environments

Shipped `Web.config`:

- `<connectionStrings>` empty.
- `SQLConnectionDebug` and `SQLConnectionRelease` settings empty.
- `intDecimals` = 1, column widths 56 and 105, `dwfQFTable` = `dwfQF`.
- Session `InProc`, `cookieless="false"`, `timeout="720"`.
- `authentication mode="Windows"`.
- `customErrors mode="Off"`.
- `compilation debug="true"`.
- `httpRuntime executionTimeout="43200"` `maxRequestLength="104856"`.
- Default document `Index.aspx`.
- Expression builder prefix `EnvConnectionStrings` → `RGBC_Forecast.RGBC_Forecast.EnvConnectionStringsExpressionBuilder`.

`db.GetConnectionString` and the expression builder use `WebConfigurationManager.ConnectionStrings("SuperbowlConnectionString")` when that value is non-empty, otherwise `SecureConfig`.

`SecureConfig` resolution for that name:

1. `RGBC_FORECAST_CONNECTION_STRING`
2. else `SUPERBOWL_CONNECTION_STRING`
3. else `ConfigurationErrorsException`

Load order: `.env` file, then system environment variables overwrite the same keys. The `.env` path is the parent of `HttpRuntime.AppDomainAppPath` (the directory above the web application). If that path is unavailable, it walks up from `AppDomain.BaseDirectory`, stripping a trailing `bin`. A missing or unreadable `.env` is swallowed. A missing variable fails when a connection is requested.

`.env.example` documents the same two names and optional `SQL_CONNECTION_DEBUG` / `SQL_CONNECTION_RELEASE`. `GetSQLConnectionDebug` and `GetSQLConnectionRelease` have no other callers.

Debug versus release in the running site is the VB `#If DEBUG` symbol, not those SQL settings. Debug adds `[Dev]` to the version label and, when session is empty, loads `SILICON\PJ` through `sql_get_Users`. Release redirects to login. `DefineDebug` is true for the Debug configuration and false for Release.

Publish profile `Profile1.pubxml` is a file-system publish to `C:\Users\pj\Desktop\fc` with `DeleteExistingFiles` false. That path is a developer machine path from the profile, not evidence of the current server. **Unknown / Requires Confirmation** where the site is deployed.

`Application_Start` comments say the app must not write `Web.config`, so it can run without config write permission.

## Authentication and session

IIS is expected to authenticate the request (`mode="Windows"`). The application then authorises by a database lookup of `LogonUserIdentity.Name`.

`FormsAuthentication.SignOut()` and `Session.Clear()` run on `Index` load. No `FormsAuthentication.SetAuthCookie` exists. The forms API is used only to sign out.

Session key is written as `USER` and read as `User` and `USER`. ASP.NET session keys are compared case-insensitively, so both reach the same item. Provenance: **Inferred** from platform behaviour of `HttpSessionState`, consistent with the code using both spellings.

Master page order comment: content `Page_Load` runs before the master `Page_Load`, so some pages call `LogginUser` themselves when session is empty and catch a missing user when enabling buttons.

`TestDbConnection.aspx` does not use the master page. Any caller who can open that URL under the site's IIS authentication gets a connection test, `mstUser` count, and the Windows identity. It is not linked from the menu.

## Data access

`db` holds one shared `SqlConnection` created on first use from `GetConnectionString()`. `doQuery`:

- If the trimmed SQL starts with `SELECT`, fills a `DataSet` through `SqlDataAdapter`.
- Otherwise opens the shared connection, runs `ExecuteNonQuery`, and closes it.
- On many exceptions, writes the SQL text and `ex.Message` to `My.Response`.

`getRow` returns the first row or `Nothing`. `doQueryAsync` opens a second connection with `Asynchronous Processing=true` and can return before completion when `blnGooi` is true (the default).

`CleanString` turns `Nothing` into empty, replaces double quotes with single quotes, doubles single quotes, and trims. Queries still concatenate that text. This is **Implemented** and is not parameterised SQL.

`doQueryXTab` rewrites a grouped query into a cross-tab in memory. The sandbox button calls it and discards the return value.

`nz` treats `Nothing`, `DBNull`, empty string, and numeric 0 as the default. A legitimate zero therefore becomes the default. **Implemented.**

## Database objects the code names

No `CREATE TABLE` script is in the repository. Columns below are the ones the queries read or write. Types are **Unknown** except where a conversion in code forces a reading (dates stored as strings in `yyyyMM`, `yyyyMMdd`, or `yyyyMMddHHmm`).

| Object | Use |
|---|---|
| `mstCompany` | `CompanyID = 1`. `strFYStart`, `strFYEnd` |
| `mstPeriod` | `strPeriod` (`yyyyMM`), `intFiscalYear`, `strMonth`, `intFiscalMonth` |
| `mstUser` | `UserID`, `strDomainLogin`, `strUserName`, `strRegion`, `refUserGroupForecastID`, `refBrandManagerID` target |
| `mstUserGroupForecast` | `UserGroupID`, `strUserGroup`. Login joins `UserGroupID` to `mstUser.refUserGroupForecastID` |
| `mstUserBrand` | `refUserID`, `refBrandID` |
| `mstBrand` | `BrandID`, `strBrand`, `blnActive`, `refPrincipalID`, `refBrandManagerID`, `refGroupingDailyID` |
| `mstPrincipal` | `PrincipalID`, `strPrincipal`, and fiscal fields selected with the brand (`strFYStart`, `strFYEnd` appear on the brand label query) |
| `mstGroupingDaily` | `GroupingDailyID`, `strGroupingDaily` |
| `mstCustomer` | `strCustomerNo`, `strCustomerGroup`, `refCustomerGroupID` |
| `dwdCustomer` | `strCustomerNo`, `strCustomerName`, `strGroupName`, `strRegionOrder`. Older comments also mention `strRegionDesc2` |
| `dwdProduct` | `strProdCode`, `refBrandID`, `blnQF`, `intPack`, `intSize` |
| `dwfSales` | `refProdCode`, `refCustomerNo`, `refPeriod`, `dbl9lTotal` |
| `dwfQFPlan` | `strPeriod`, `refBrandID`, `strCustomerNo`, `dblQF`, `strLastUser`, `dtStamp` |
| `dwfQFAdjustment` | Plan columns plus `blnApplied`, `strNotes`, `strLog` |
| `dwfQF` | Name from setting `dwfQFTable`. `strPeriod`, `strProdCode`, `strCustomerNo`, `dblBudget9l`, `percRegion`, `percProduct`, `strLastUser`, `dtStamp` |
| `stkProductPeriod` | `dblQF`, `mstPeriod`, `mstProdCode` |
| `mstOpenSeason` | `OpenSeasonID`, `refBrandID`, `strDateStart`, `strDateEnd`, `strLastUser`, `strLastEdit` |
| `mstUsageLog` | `mstUserID`, `dtStamp`, `strSystem` |
| `sysCronJob` | `dtCronJob`, `Type`, `param`, `Status`, `Result`. This app inserts and updates. It does not select |

`Branding.New` updates `dwdCustomer` from `mstCustomer` on every construction, which is every brand load and every report brand. That is a write on a read path. **Implemented.**

## QF generation path

`QFGen` is constructed with a fiscal year and brand id. It loads company fiscal dates and `Branding.LoadCustomers`.

`GenerateBrand` calls `Generate(brand, True)`.

Sales base is the last completed fiscal (`refPeriod` between those months). Adjustment base is the plan fiscal year (`strPeriod` between those months).

Group key is `dwdCustomer.strGroupName`.

Stock update uses `DateAdd` from `DateFinYearStart` by the period index, not the adjustment's own `strPeriod` string, when writing `stkProductPeriod.mstPeriod`. **Implemented.** If the period collection order is not fiscal order, the stock month can diverge from the QF month. **Requires Confirmation** against production data.

`CopyProductQF` joins plan customers to fact customers on `strGroupName`. A commented statement still shows the old `strRegionDesc2` join.

The class writes progress with `My.Response.Write` even outside a debug compile (`#If DEBUG` around those writes is commented so the writes are active).

## Background work

There is no `System.Timers`, Windows service, hangfire, or SQL Agent script in the repo.

The only queue is `sysCronJob`:

- Insert: `dtCronJob` = `yyyyMMddHHmm`, `Type` = `BrandQF`, `param` = `BrandID={id}|Finyear={year}|CopyBrandID={id}|BrandName={name}`, `Status` = `New`.
- Duplicate removal: same `Type`, `param like` that string, `Status = 'New'` → `Status = 'Removed'` and a result sentence.

Who executes `New` rows is **Unknown / Requires Confirmation**. `QFGen` is the in-repo implementation of the work the buttons describe.

`db.doQueryAsync` is used for the planning delete of `dwfQF` rows and for deleting zero `percRegion` rows. With the default `blnGooi = True` the method returns as soon as the async result is not yet complete, so those deletes may still be running after the method returns. **Implemented.**

## Security observations

These are facts about the code, not a remediation plan.

- SQL text is built by concatenation. `CleanString` doubles quotes. Integer ids from dropdowns are concatenated without a numeric parse on every path (`Planning` brand id, `MyBrands` user id).
- `doQuery` can echo SQL and exception text to the browser. `customErrors` is off. `compilation debug` is true in the committed config.
- One shared connection is not isolated per request.
- `TestDbConnection.aspx` has no session gate.
- Debug builds impersonate a fixed domain account when session is missing.
- `.gitignore` excludes `.env`. `Web.config` no longer holds a connection string (change in `f95e71a` / `072a6da`).
- `Index.aspx.vb` still contains a commented diagnostic string from an earlier debug session. It is not executed.
- Session is in-process, so it is not shared across servers. A web farm is not described.
- `window.showModalDialog` is used for notes. Support in current browsers is **Unknown / Requires Confirmation**.

## Client script

`scripts.js` and the master page supply `jsQFFocus`, `jsQFBlur`, `removeAlpha`, and `addCommas` for numeric cells. `Planning.aspx` computes a forecast growth percent in the browser on target blur. `Adjustments.aspx` opens comments with `showModalDialog`. Report and adjustment exports depend on the server re-rendering the grid, not on a spreadsheet library. The planning demo file under `content/` is a static `.xls` download, not generated.

## Build, scan, and release

Local build is Visual Studio or MSBuild of `RGBC Forecast.sln`, configurations Debug and Release, platform Any CPU.

`bitbucket-pipelines.yml` does not compile the solution and does not run tests. It runs SonarQube when `master` changes. `SONAR_HOST_URL` and `SONAR_TOKEN` are repository or workspace variables. `sonar.projectKey=overdrive_forecast`.

No Docker file, no AWS template, no web deploy package settings beyond `Profile1.pubxml`.

Tracked user file `RGBC Forecast.vbproj.user` and the Visual Studio suo file are not required to build. They are present anyway.

## Version identifiers that disagree

| Place | Value |
|---|---|
| `Admin.Master.vb` | `1.0.3`, date 4 April 2012 |
| `AssemblyInfo.vb` | `AssemblyVersion` `1.0.0.0`, `AssemblyFileVersion` `1.0.0.1`, description `3 Sep 2010`, copyright 2010 |
| `Branding.vb` header | comments through 2015-05-29 and a 2026-08-28 sync comment |
| Git | customer-group merge 2026-09-01 |

The footer is not a release record of `master`.

## Development and testing process

No test project, no scripted smoke test, and no seed data. `TestDbConnection.aspx` is the only diagnostic page. `zSandbox.aspx` is a manual hook for `QFGen` and `doQueryXTab`, and generation is behind `If False`.

A developer needs:

- Visual Studio with VB.NET web workload and .NET Framework 4.6.2 targeting pack (4.6.1 is what `Web.config` names).
- IIS or IIS Express with Windows authentication.
- A `.env` beside the solution (parent of the web folder) or the environment variable `RGBC_FORECAST_CONNECTION_STRING`.
- The SQL Server database that already contains the tables above. This repo does not create it.

`UseIISExpress` in the project is false. Anonymous and Windows authentication IIS Express flags are empty.

## Discrepancies

| Topic | What the repo shows |
|---|---|
| Framework | Project 4.6.2, `Web.config` compilation 4.6.1 |
| Onboarding snapshot | Issue text cited `072a6da` (2026-02-05). `master` is `6aac1d2` (2026-09-01) |
| QF apply | Buttons queue `sysCronJob`. Generator exists but is not called from those buttons |
| Period lock | Comment says unlocked; condition is `And False` |
| Open season | Insert always brand 0; grid can show a brand; uniqueness check disabled |
| Forecast vs RSM brand activity | Only the RSM report filters `blnActive = 1` |
| Home report | Prepared and not rendered |
| Publish target | `C:\Users\pj\Desktop\fc` |
| TFS vs Git | Solution still names `tfs01` |