# Technical documentation — superbowl_daily_2 (RGBC Superbowl Daily 2)

Evidence date: 2026-10-02.

Source: `git@bitbucket.org:overdrive/superbowl_daily_2.git`, `master` at `30eb35e` (2026-06-10, author maanie).

Remote: `git@bitbucket.org:overdrive/superbowl_daily_2.git`. Only branch: `master`.

This document describes the tree that was inspected. It does not repeat secret values. Connection strings, SMTP passwords, and API keys are present in source and are a review item, not material to copy onward.

## Application type

Windows Forms executable. Language Visual Basic. `Option Strict` is off in the project file.

| Item | Value | Evidence |
|---|---|---|
| Solution | `rgbc Superbowl Daily 2.sln` | Visual Studio 15, format 12 |
| Project | `Superbowl Daily 2\Superbowl Daily 2.vbproj` | Single project |
| Root namespace | `Superbowl_Daily_2` | vbproj |
| Startup | `Superbowl_Daily_2.My.MyApplication` | vbproj |
| Target framework | `v4.8` | vbproj |
| `supportedRuntime` sku | `.NETFramework,Version=v4.6.2` | `App.config` |
| Assembly version | `2.0.1.5` | `AssemblyInfo.vb` |
| File version | `1.0.0.0` | `AssemblyInfo.vb` |
| COM visible | false | `AssemblyInfo.vb` |
| Configurations | Debug and Release, Any CPU | solution |

The solution still contains a Team Foundation Version Control binding to `http://tfs01:8080/tfs/vs_projects_2018`. The Git remote is Bitbucket. **Inferred:** the project was stored in TFS before this Git history. The binding is not evidence of a current TFS build.

`BuildProcessTemplates/` holds Azure Continuous Deployment, Default, Lab Default, and Upgrade XAML templates for Visual Studio 11. No project or pipeline in this repo references them.

## Tree

```text
rgbc Superbowl Daily 2.sln
packages.config                          # older Newtonsoft/ClosedXML pins
BuildProcessTemplates/*.xaml
Database_Scripts/*.sql
Database_Scripts/README.md
Superbowl Daily 2/
  Superbowl Daily 2.vbproj
  App.config
  packages.config                        # ClosedXML 0.105.0, Newtonsoft.Json 13.0.4
  frmMain.vb
  frmMain.Designer.vb
  Process.vb
  Export_Process.vb
  db.generic.vb                          # class db
  db.trgbc.generic.vb                    # class db_trgbc
  db.cls.vb                              # class xdb inherits db; file is 192 bytes
  App_Code/Processes/*.process.vb
  App_Code/VB/                           # API clients, Excel, FTP, log, Duodecimal
  My Project/
.cursor/plans/repsly_promotions_product_export_1a09c3f0.plan.md
```

`bin/` is named in `.gitignore`. `obj/` is not. A signing file `Superbowl Daily 2_TemporaryKey.pfx` and `db.generic.pw.123.rar` are in the project directory. Their contents were not opened.

Not in Git, although an onboarding scan of a working copy listed them: `sonar-project.properties`, `sonarqube_scan.bat`, `Database_Scripts_20260403`, `Superbowl Daily 2_20260403`.

## Two different Debug flags

| Flag | Effect |
|---|---|
| Compile symbol `DEBUG` | `db` uses `SQLConnectionDebug`. `db_trgbc` also uses `SQLConnectionDebug` (not the TRGBC server). Download folders are `C:\Work\RGBC\` and `E:\work\RGBC\2WaySystem\`. SMTP is Office 365 in code. Import archive is `File.Copy`. |
| Compile not `DEBUG` | `db` uses `SQLConnectionRelease` (`SAPPHIRE` / `Superbowl`, integrated security). `db_trgbc` uses `SQLConnectionTRGBCRelease` (`BenriachE` / `TRGBC`, integrated security). Download folders are `\\Hennessy\Sales\ADM\` and `\\Hennessy\Sales\2WaySystem\`. SMTP host is `My.Settings.SMTPHost`. Import archive is `File.Move`. |
| `My.Settings.Debug` (application setting, default true) | True: form stays open; Track it All preview panel is visible. False: `Run()` on load and the form closes. Does not change the SQL connection. |

A Release build can still have `My.Settings.Debug = True` and then wait for the Run button. A Debug build can have the setting false and auto-run against the debug database.

## Configuration keys that exist

Declared in `Settings.settings` and `App.config`:

| Setting | Scope | Role |
|---|---|---|
| `UNAME` | Application | Value `Superbowl Daily 2`. No other use found |
| `EmailRgbc` | Application | Semicolon-separated log recipients |
| `EmailBfbe` | Application | No caller found |
| `SMTPHost` | Application | Default `10.0.0.17`. Used on non-DEBUG mail |
| `Debug` | Application | See table above. Default true |
| `dirImports`, `dirImported`, `dirExports` | User | Overwritten on load to folders beside the exe |
| `SQLConnectionDebug` | Application | See discrepancy below |
| `SQLConnectionRelease` | Application | `SAPPHIRE`, catalog `Superbowl`, integrated security, timeout 60 |
| `SQLConnectionTRGBCRelease` | Application | `BenriachE`, catalog `TRGBC`, integrated security, timeout 60 |

`App.config` debug connection `Data Source` is `NB-DIRK`, catalog `superbowl`. `Settings.settings` design-time default is `sqlsrv02`, catalog `Superbowl`. For a normal .NET application the `App.config` connection string is the one beside the executable. Both contain a SQL login. Values are omitted here.

### Settings the code reads that are not declared

These `My.Settings` members are used and are absent from `Settings.settings` and from `Settings.Designer.vb`:

| Member | Reader |
|---|---|
| `RepslyAPIBaseUrl`, `RepslyAPIUsername`, `RepslyAPIPassword` | `RepslyAPIClient` |
| `FTPHost`, `FTPPort`, `FTPUsername`, `FTPPassword`, `FTPFolder` | `FTPClient` |
| `TrackItAllAPIBaseUrl`, `TrackItAllAPIKey` | `TrackItAllAPIClient` |
| `TrackItAllFailureNotificationEmails` | `SendTrackItAllFailureNotification` |
| `TrackItAllAllowTestRun` | `TrackItAllOutletExport_Process` |

**Inferred.** A build from this Git tree fails until those settings are added to the settings designer and the generated class. Where production actually stores them is **Unknown / Requires Confirmation**. They may exist only on a machine that was not committed.

## Data access

`db` and `db_trgbc` are parallel classes.

- `doQuery`: if the SQL text starts with `SELECT` (after trim and upper-case), fill a `DataSet`. Otherwise `ExecuteNonQuery` with `CommandTimeout = 0`.
- The connection is opened and closed around non-queries. If it is already open, it is closed first.
- Several `Catch` blocks in `doQuery` fall through without returning and without logging. A SELECT timeout (`ErrorCode = -2146232060`) returns an empty dataset. Behaviour of other SQL exceptions on SELECT is inconsistent (one branch fills again). **Implemented** as written. Callers often assume a dataset.
- `getRow` returns `Nothing` on any exception.
- `CleanString` turns `"` into `'`, doubles single quotes, and trims. It is not a full parameterised query. Process SQL is string-concatenated.
- `xdb` in `db.cls.vb` subclasses `db`. Inserts in child processes often `New db` and read `intRows`. `intRows` is `Shared`, so concurrent use on one process would share the counter. The UI is single-threaded. **Inferred.**

There is no ORM, no migration runner, and no check that `Database_Scripts` have been applied. Missing columns are handled only where a `Catch` looks for a column name (`blnLastSentDataTransfer`, `strLastSentTerritory`, `RequestPayload`).

### Databases and objects the code names

Superbowl catalog (names taken from SQL strings in VB and scripts):

- Staging: `stgOffconPromo`, `stgOffconSurvey`, `stgOnconPromo`, `stgOnconSurvey`, `stgRepCalls`, `stgVendor`, `stgMainFullGLCode`, `stgCallCycleDataReport`, `stgSuperbowlLog`.
- Facts: `dwf` plus the process id (`dwfOffconPromo`, and the same pattern for the other file imports). `Process` sets `tblDwf` to `dwf` + id in both compile modes. A comment says a `tmp` prefix was the live switch and is not active.
- Masters and dimensions touched by name: `mstProduct`, `mstCustomer`, `mstBrand`, `mstRep`, `mstRepManager`, `mstBranch`, `mstKeyAccount`, `mstKeyAccount_Group`, `mstChannelSegment`, `mstSnellArea`, `mstPeriod`, `mstCompany`, `dwdCustomer`, `dwdProduct`, `promoActivity`.
- Queues: `sysCronJobProducts`, `sysCronJobCustomers`, `sysCronJobPromotionActivities`, `sysTrackItAllOutletSyncState`.
- Other: `sysQuery`, `logSuperbowl`, `dlyDataWarehouse`, `fsrRequisition`, `fsrRequestType`, `fsrItem`, `mstUser`.
- `sysCronJobPromotions` appears in SQL scripts only.

TRGBC catalog, release compile of vendor and GL:

- Views `vwAPVendors`, `vwGLAccounts`.

`dtStamp` change detection assumes `nvarchar` length 12, format `YYYYMMDDHHMM`, and builds a `CONVERT(..., 120)` string. Comments say this avoids `TRY_CONVERT` for SQL Server 2008. Server version in production is **Unknown / Requires Confirmation**.

## Repsly client

`RepslyAPIClient` (`App_Code/VB/RepslyAPIClient.vb`):

- Base URL, user, and password from settings.
- `HttpClient` timeout 60 seconds. Header `Accept: application/json`. Basic auth of `username:password`.
- `GetAsync` / `PostAsync` block on `.Result`.
- POST success: HTTP success and JSON deserialised to `RepslyAPIResponse` (`Code`, `Message`).
- POST HTTP failure: try to deserialise the body; otherwise `Code = -1`.
- `GetImportStatus` calls `GET /export/importStatus/{id}` into `RepslyImportStatus` (`ImportStatus`, row counts, warnings, errors). No process calls it.

Endpoints used:

| Process | Method and path | Body |
|---|---|---|
| Products and promotions | `POST /import/productList` | `{"Products":[...]}` |
| Customers | `POST /import/clientList` | `{"Clients":[...]}` |

Batch size constant `200` in all three process classes. `ImportJobID` is a substring of `Message` after `ID:` up to the next space, only when `Code = 0`.

Product and promotion dictionaries include Code, Name, ProductGroupCode, ProductGroupName, Active, UnitPrice, and optional EAN, Tag, and CustomAttributes. Customer payload keys are the names in `GetCustomersToProcess` (Code, Name, Active, RepresentativeCode, RepresentativeName, Territory, State, Country, AccountCode, AccountName, Tag).

## FTP client

`FTPClient`:

- URL `ftp://{host}:{port}{folder}/{file}`.
- `UploadFile`, binary, passive, `KeepAlive` false, timeout 60 seconds.
- `TestConnection` lists the folder, timeout 30 seconds. No caller in the export `Run` methods was found besides the upload itself. **Inferred** unused by the batch unless something else calls `TestConnection`. A search shows the method defined in `FTPClient.vb` only.

## Track it All client

`TrackItAllAPIClient`:

- Base URL trimmed of a trailing slash. Header `X-API-KEY`.
- Timeout 120 seconds.
- `POST {base}/rgbc/updateOutlets`.
- Multipart: file part name `file`, content type the XLSX MIME type, and string part `mode` (`sync` or `preview`).
- Success is `IsSuccessStatusCode`. Body stored on `TrackItAllUploadResult`. No JSON schema check.

State table columns used by the process: `StateID`, `PendingRetry`, `LastRunDate`, `LastError`, `dtLastEdit`. Script 14 seeds `StateID = 1`.

## Excel and CSV writers

- `ExcelExport` uses ClosedXML (project package). Writes the `DataTable` as given, with type switches for string, integer, double/decimal/single, date, and boolean. Row errors are warnings and the export continues. `blnError` fails the caller.
- `dataExport` writes the `sysQuery` CSV exports. A row write error is logged as `error` and counted. Used by `Export_Process`, not by the Repsly XLSX path.
- `Export_Process.UploadFile` copies the CSV from `Exports\` to `Path & Filename` (the share). It does not use `FTPClient`.

## Mail

`ProjectFunctions.EmailLog`, `EmailUnIinvoicedRequisitions`, and `SendTrackItAllFailureNotification`.

DEBUG compile: `SmtpClient("smtp.office365.com", 587)`, SSL, explicit network credential compiled in the method. Non-DEBUG: `SmtpClient(My.Settings.SMTPHost)` with no port, no SSL, and no credential in code (machine default).

From addresses differ by compile and by message type (`Superbowl Daily 2` vs `FreeStock Requisition`, `smtp@overdrive.co.za` vs `administrator@rgbc.co.za`).

## NuGet

Project `Superbowl Daily 2\packages.config` (target `net48`):

- ClosedXML 0.105.0
- ClosedXML.Parser 2.0.0
- DocumentFormat.OpenXml 3.1.1 and Framework 3.1.1
- ExcelNumberFormat 1.1.0
- Newtonsoft.Json 13.0.4
- RBush.Signed 4.0.0
- SixLabors.Fonts 1.0.0
- System.Buffers, System.Memory, System.Numerics.Vectors, System.Runtime.CompilerServices.Unsafe, Microsoft.Bcl.HashCode

Repository root `packages.config` pins older ClosedXML 0.102.2, OpenXml 3.0.0, Newtonsoft.Json 13.0.3. **Inferred** leftover. The vbproj is the authority for the application.

`HttpClient` is used. On .NET Framework 4.8 that comes from the framework or from an implicit reference. No `System.Net.Http` package is listed.

## Security notes (as implemented)

- No authentication inside the application.
- SQL is concatenated. `CleanString` only covers quote characters on some values.
- Secrets are in `App.config`, `Settings.settings`, and `ProjectFunctions.vb` (DEBUG SMTP). A `.pfx` and a `.rar` are committed. This document does not reproduce the secrets.
- Repsly and Track it All credentials are expected in `My.Settings`, which are not in the committed settings file.
- Log email HTML inserts `txtLog` without encoding. Track it All failure mail encodes the error with `HtmlEncode`.
- `ComVisible` is false.

## Build, deploy, test

- Build: Visual Studio, Debug or Release Any CPU. No scripted build file was found.
- Deploy target, Windows service, or Task Scheduler definition: **Unknown / Requires Confirmation**. The auto-close path is the only in-repo hint of an unattended run.
- Tests: no test project, no `*Test*` source.
- CI: no pipeline yaml. Sonar files from the task snapshot are not in Git.
- `Database_Scripts/README.md` says run the scripts in order, in development first, and that they are idempotent (`IF NOT EXISTS`). Script 06 updates existing `Processing` rows and is not only a structure change. Script 08 deletes and inserts test products matching `T[0-9][0-9][0-9]F`. README says review before execution.

## Local paths that are not configuration

Hardcoded in source, not settings:

- DEBUG download `C:\Work\RGBC\` and `E:\work\RGBC\2WaySystem\`.
- Release download `\\Hennessy\Sales\ADM\` and `\\Hennessy\Sales\2WaySystem\`.
- Commented historical paths under `\\Benriach\Sales\`.
- `Button1_Click` writes `c:\temp\info.csv` from `dwdCustomer`. The handler is `Public` and is not wired with `Handles` in the portion inspected. **Inferred** leftover test method.

## Related reading in this folder

- `project-plan.md` — scope, history, status.
- `prd.md` — workflows and FR-001 onward.
- `features.md` — checkbox defaults and what is unused.
- `business-rules.md` — rules inferred from the same code, including conflicts.