# Project plan — superbowl_daily_2 (RGBC Superbowl Daily 2)

Evidence date: 2026-10-02.

Source repository: `git@bitbucket.org:overdrive/superbowl_daily_2.git`.

Inspected revision: `master` at `30eb35e` (2026-06-10, "update the rep territory", author maanie).

This plan describes what the repository contains. It does not decide what the product should become.

## Product

The solution file is `rgbc Superbowl Daily 2.sln`. The Windows Forms title is **Superbowl Daily 2**. The load label reads **RGBC Superbowl Daily**. Assembly title and product are `Superbowl Daily 2`. Assembly copyright is `Copyright ©  2012`. Assembly version is `2.0.1.5`. Assembly file version is `1.0.0.0`. The form version string is hardcoded as `03 February 2026` and is combined with the assembly build and revision (`frmMain.VersionDate`).

Provenance: **Implemented** in `AssemblyInfo.vb` and `frmMain.vb`.

What "Superbowl" means as a product name, and who at RGBC owns the daily run, is **Unknown / Requires Confirmation**. The code treats RGBC as the company row in `mstCompany` (`strCompany = 'RGBC'`) used when extending fiscal periods.

The application is a desktop batch. It copies CSV files from file shares (or SQL views), loads them into SQL Server staging tables, applies warehouse updates from SQL stored in `sysQuery`, writes CSV exports from other `sysQuery` rows back to a share, and pushes product, promotion, and customer data to Repsly and outlet data to Track it All.

There is no login screen. Whoever can start the executable and reach the configured SQL Server and file shares can run the batch.

## Branch reality

Default branch is `master`. `origin/HEAD` points at `master`. No other remote branches were present at inspection.

Git history in this clone is 21 commits, from `54f368b` (2020-01-14, "initial commit") through `30eb35e` (2026-06-10).

The in-file changelog at the top of `frmMain.vb` starts at `20120830 - v2.0.0 - project start`. Those earlier dates are **Documented** in source comments. They are not commits in this Git history.

Recent commits that change product behaviour:

| Commit | Date | Subject |
|---|---|---|
| `32e6df6` | 2025-11-27 | Implemented Repsly integration for products and promotions |
| `7dad146` | 2025-12-04 | disabled Repsly promotions export + fixed Repsly field mappings and date conversion issue |
| `4d0d625` | 2026-01-07 | Added Repsly Customer API + batch processing to Product API |
| `a3e8011` | 2026-01-14 | include only active rgbc and non-rgbc customers, added tags |
| `c89fc02` | 2026-02-04 | Track it all outlet automation API + added package and size to Repsly product description |
| `e2d82f6` | 2026-02-06 | updated the get outlets sql to use the mstRepManager table instead |
| `083efec` | 2026-02-13 | added tags to Repsly Customer and Product export + testing flag for track-it-all API |
| `e7848b1` | 2026-02-23 | added and updated Repsly Customer fields |
| `20845a7` | 2026-03-16 | added Repsly Promotions sync |
| `9fce61f` | 2026-03-31 | allow inactive customers to be synced to Repsly as well |
| `48845c0` | 2026-05-04 | Data Transfer off: re-queue completed products so Repsly receives inactive |
| `30eb35e` | 2026-06-10 | update the rep territory |

At `30eb35e`, Repsly promotions export is wired and enabled in the UI. The 2025-12-04 commit subject says promotions export was disabled; a later commit (2026-03-16) added promotions sync again. Current code is the later state.

## Scope in the repository

In scope of what is present on `master`:

- One VB.NET Windows Forms project, `Superbowl Daily 2`, target framework .NET Framework 4.8 (`Superbowl Daily 2.vbproj`). `App.config` `supportedRuntime` sku is `.NETFramework,Version=v4.6.2`. Those two declarations disagree. Which runtime is used in production is **Unknown / Requires Confirmation**.
- A checked process list. Checked processes run in a fixed order: imports, then exports.
- CSV imports from UNC or local folders into `stg*` tables, then `sysQuery` SQL into `dwf*` fact tables, with optional period delete.
- Vendor and GL imports from SQL views on a second connection (`db_trgbc`).
- CSV exports whose SQL lives in `sysQuery` (IDs 35, 60, 37, 36, 59), copied to a share.
- Repsly product, promotion, and customer exports: queue tables, XLSX, FTP upload, then JSON POST.
- Track it All outlet XLSX upload, with a Wednesday / Thursday retry state.
- Batch logging to `stgSuperbowlLog` then `logSuperbowl`, and an HTML log email.
- Idempotent SQL scripts under `Database_Scripts/` for the Repsly and Track it All queue objects. They are not applied by the application.
- Visual Studio 11 build-process XAML under `BuildProcessTemplates/`. No pipeline definition was found in the tree.

Out of scope of the code that runs today, or present only as UI, comments, or unused methods:

- The `Run()` comment block that says the batch updates `dwdCustomer`, updates and recalculates SC, generates BFBE exports, and emails BFBE exports. Those calls are not in `Run()`.
- `BackupMintPromos()`. The Monday call in `Run()` is commented out.
- `EmailUnIinvoicedRequisitions()` is not called from `Run()` (disabled in the 2016 changelog comment). The **Email Requisitions** button still calls it.
- `EmailBfbe` is an application setting. No caller was found.
- Checkbox **Mint Customer Segmentation** is disabled and has no process class.
- `RepslyAPIClient.GetImportStatus`. No caller was found.
- Non-RGBC customers. `Database_Scripts/13_Add_CustomerType_Column.sql` documents `CustomerType` values `RGBC` and `NonRGBC` and table `mstNot_RGBC_Customer`. Current customer export inserts only `CustomerType = 'RGBC'` from `mstCustomer`. A search of `*.vb` found no `mstNot_RGBC` or `NonRGBC`. Git last touched `mstNot_RGBC` in `083efec` (2026-02-13). At HEAD that path is gone.
- Queue table `sysCronJobPromotions`. Older scripts create and alter it. Current promotion export uses `sysCronJobPromotionActivities`.
- SonarQube files named in the onboarding snapshot (`sonar-project.properties`, `sonarqube_scan.bat`) and folders `Database_Scripts_20260403` and `Superbowl Daily 2_20260403`. They are not in the Git tree at `30eb35e`.
- Automated tests. None were found.

## System architecture

```text
Operator starts Superbowl Daily 2.exe
  → frmMain
      If My.Settings.Debug = False: Run() then close
      If My.Settings.Debug = True: form stays open; Run is the Run button

Run()
  → SQL Server catalog Superbowl (db)
      startup: truncate stgSuperbowlLog, CheckPeriods, LogDWD
      imports: CSV or view → stg* → sysQuery → dwf* or mst*
      file exports: sysQuery → CSV under Exports\ → copy to share
      Repsly: queue tables → XLSX → FTP → HTTPS JSON
      Track it All: mstCustomer snapshot → XLSX → HTTPS multipart
  → SQL Server catalog TRGBC (db_trgbc), Release builds only
      vendor view vwAPVendors, GL view vwGLAccounts
  → SMTP log email to EmailRgbc

Compile symbol DEBUG (not the same as My.Settings.Debug)
  → which SQL connection, SMTP client, download folder, and archive Copy vs Move
```

File shares in a Release compile (`frmMain_Load`):

- `\\Hennessy\Sales\ADM\`
- `\\Hennessy\Sales\2WaySystem\`

A DEBUG compile uses `C:\Work\RGBC\` and `E:\work\RGBC\2WaySystem\`. Comments record older share names `\\Benriach\Sales\...`. Changelog line `20241127` says server references were updated from Benriach to Hennessy.

Local working folders are created under the executable directory: `Imports\`, `Imported\`, `Exports\`.

## Major components

| Component | Role |
|---|---|
| `frmMain` | Process list, Run, log email, requisition email button |
| `Process` | CSV download, staging truncate, CSV parse, archive, duplicate log, period delete |
| `Export_Process` | Run `sysQuery` SQL, write CSV, copy to the configured share |
| `*Import*.process.vb` / survey and promo processes | Per-file staging INSERT and `sysQuery` IDs |
| `RepslyProductExport_Process`, `RepslyPromotionExport_Process`, `RepslyCustomerExport_Process` | Queue, XLSX, FTP, Repsly POST |
| `TrackItAllOutletExport_Process` | Weekday gate, XLSX, Track it All POST, retry state |
| `RepslyAPIClient`, `TrackItAllAPIClient`, `FTPClient` | HTTP and FTP |
| `db`, `db_trgbc` | ADO.NET SQL helpers |
| `ProjectFunctions` | Log insert, log email, period extension, DWD count, unused promo backup, requisition email |
| `Database_Scripts` | Manual SQL for queue objects |

## Milestones visible in the repository

These are dates recorded in `frmMain.vb` comments or Git subjects. They are not a signed project plan.

| When | What the repository records |
|---|---|
| 2012-08-30 | Comment: project start v2.0.0 |
| 2014-02-20 | AdvPDA CRM and product CSV exports |
| 2014-04-30 | Partial import checkbox (day files rather than month-to-date) |
| 2014-10-20 | Monday email of approved requisitions with no invoice after 14 days |
| 2016-01-28 | That requisition email disabled in the batch |
| 2017-06-09 | Vendor import |
| 2018-03-13 | GL account code import |
| 2019-02-19 | Planned promotions export |
| 2019-08-19 | 2WaySystem off-consumption promo import |
| 2020-01-14 | First Git commit |
| 2020-09-09 | AdvPDA CRM inactive export |
| 2023-01-20 | Call cycle data report import |
| 2024-11-27 | Share paths changed from Benriach to Hennessy (comment) |
| 2025-11-27 onward | Repsly product, promotion, and customer sync; Track it All outlets |

## Known dependencies

- .NET Framework. Project file says 4.8. `App.config` startup says 4.6.2.
- NuGet in the project `packages.config`: ClosedXML 0.105.0, Newtonsoft.Json 13.0.4, DocumentFormat.OpenXml 3.1.1, and their dependencies. A second `packages.config` at the repository root lists older versions (ClosedXML 0.102.2, Newtonsoft.Json 13.0.3, DocumentFormat.OpenXml 3.0.0). Which file a build uses is **Unknown / Requires Confirmation**. The project file is the one next to the `.vbproj`.
- SQL Server. Queue scripts comment that date conversion avoids `TRY_CONVERT` for SQL Server 2008 compatibility. Whether production is still SQL Server 2008 is **Unknown / Requires Confirmation**.
- Repsly HTTP API (Basic authentication). Base URL, username, and password are read from `My.Settings` properties that are **not** declared in `Settings.settings`.
- An FTP server, also read from undeclared `My.Settings` (`FTPHost`, `FTPPort`, `FTPUsername`, `FTPPassword`, `FTPFolder`).
- Track it All HTTP API (`X-API-KEY`, `POST {base}/rgbc/updateOutlets`). Base URL, key, failure emails, and `TrackItAllAllowTestRun` are likewise undeclared in `Settings.settings`.
- File shares on host Hennessy (Release compile).
- SMTP: Release uses `My.Settings.SMTPHost` (default `10.0.0.17`) and from-address `administrator@rgbc.co.za`. A DEBUG compile uses Office 365 SMTP with credentials compiled into `ProjectFunctions.vb`.

Credential values are in source and in `App.config` / `Settings.settings`. They are intentionally omitted here. Human review is required for secrets committed to Git, including `Superbowl Daily 2_TemporaryKey.pfx` and `db.generic.pw.123.rar`.

## Current project status

**Implemented** and checked by default on the form (so a non-debug launch runs them):

- 2Way off-consumption promo import, Adv off-consumption survey, Adv on-consumption promo, Adv on-consumption survey, Por vendor import, Por GL code import, call cycle data import.
- 2Way CRM export, 2Way CRM inactive export, Adv exposure CRM export, Adv PROD export, planned promotions export.
- Repsly products, promotions, and customers.
- Track it All outlets. On a non-debug run this still skips unless the day is Wednesday, or Thursday with a pending retry.

**Implemented** and unchecked by default:

- Mint rep calls (`chkRepCalls` has no `Checked = True` in the designer).

**Present but not executed by the batch:**

- Email Requisitions button.
- Mint Customer Segmentation checkbox (disabled).
- Partial Import checkbox (default checked). It is read by survey, promo, and rep-call imports.

`My.Settings.Debug` default is `True`. While that stays true, `frmMain_Load` does not auto-run and does not close. A production launch that should run unattended depends on that setting being false in the deployed config. Whether the deployed config sets it false is **Unknown / Requires Confirmation**.

## Implementation plan in the repository

There is no separate roadmap file. The only forward-looking document in the tree is `.cursor/plans/repsly_promotions_product_export_1a09c3f0.plan.md`. It describes moving promotions onto `POST /import/productList`. That behaviour is **Implemented** in `RepslyPromotionExport.process.vb` at HEAD (product-shaped query, batches of 200, code `PR` plus zero-padded `ActivityID`). The plan's open point about promotion ID format is resolved in code as `'PR' + RIGHT('0000' + ActivityID, 4)`. The plan's open point about Repsly treating promotions as products is still **Unknown / Requires Confirmation** as a business decision; the code does send them as products.

## Discrepancies that need a human

- `Settings.settings` does not declare Repsly, FTP, or Track it All settings that the export classes read. A clean compile of this tree is expected to fail on those `My.Settings` members until settings are added. Confirm where production stores those values.
- `App.config` debug SQL server is `NB-DIRK`, catalog `superbowl`. `Settings.settings` design-time default is `sqlsrv02`, catalog `Superbowl`. Release Superbowl server in both is `SAPPHIRE`. TRGBC release server is `BenriachE`, catalog `TRGBC`, which was not renamed when file shares moved to Hennessy.
- Task-creation snapshot listed Sonar and dated backup folders that are not in Git.
- CustomerType `NonRGBC` is documented in SQL script 13 and absent from application code.
- `frmMain` version date `03 February 2026` is earlier than commits through 2026-06-10.