# Technical documentation — superbowl_cronjob

Evidence date: 2026-10-02. Revision: `master` `986893e` (2026-09-11).

Credential values that appear in git are intentionally omitted.

## Architecture

Desktop batch program. `OutputType` is `WinExe`. Startup object is `Superbowl_Cronjob.My.MyApplication`. The only form that performs work is `frmCronjob`.

Compile symbol `DEBUG` selects the SQL connection and the SMTP client. That is independent of the application setting `My.Settings.Debug`, which only decides whether load calls `Run`. A release build can still have the setting `Debug` true, and then it will not auto-run. A debug build always uses `SQLConnectionDebug` and the Office 365 SMTP client, even if the setting is false.

## Application structure

```text
superbowl_cronjob/
  .gitignore
  BuildProcessTemplates/          TFS 11 XAML (AzureContinuousDeployment, DefaultTemplate, LabDefaultTemplate, UpgradeTemplate)
  .cursor/plans/repsly_order_integration_faa35b01.plan.md
  Superbowl Cronjob/
    Superbowl Cronjob.sln
    packages/                     committed NuGet packages
    Superbowl Cronjob/
      frmCronjob.vb               queue and mail
      *Process.vb                 imports and exports
      QFGenerator.vb              class QFGen
      Branding.vb
      db.generic.vb               class db
      app.config
      packages.config
      vendorDone.rdlc
      Scripts/                    SQL, not executed by the app
      App_code/VB/                RepslyApiClient, IdocGenerator, ProjectFunctions, dataExport, Duodecimal, SystemFunctions
      SqlServerTypes/Loader.cs
      *.xsd                       typed DataSets (also large generated Designer.vb files)
      bin/ and obj/               committed build outputs
```

Solution folder `$tf` holds Team Foundation local version metadata. `.vs` workspace state is committed. `Superbowl Cronjob_TemporaryKey.pfx` is committed. No README, no test project, no `bitbucket-pipelines.yml`.

`sonar-project.properties` and `sonarqube_scan.bat` were on the scan-host listing and are not in git.

## Language and runtime

| Item | Value |
|---|---|
| Language | VB.NET, plus `SqlServerTypes/Loader.cs` |
| Framework | .NET Framework 4.8 (`app.config` `supportedRuntime`; `vbproj` `TargetFrameworkVersion`) |
| UI | Windows Forms |
| JSON | `System.Web.Script.Serialization.JavaScriptSerializer` |
| HTTP | `HttpWebRequest` |
| Reports | Microsoft ReportViewer, local PDF render |
| IDE history | Visual Studio solution, TFS binding (`*.vspscc`, `*.vssscc`, `$tf`) |

`packages.config` targets `net462` for the ReportViewer and SQL types packages while the project targets 4.8.

## Database

One catalog, name `Superbowl`.

| Setting | Committed server | Security |
|---|---|---|
| `SQLConnectionRelease` | `Benriach` | Integrated security, timeout 60. Used when the project is not compiled `DEBUG` |
| `SQLConnectionDebug` | `NB-MAANIE2\SQLEXPRESS` | SQL authentication. Used when compiled `DEBUG` |
| `SQLConnectionRPT` and `_LIVE_REFERENCE` | `Benriach` | Integrated security |
| `SQLConnectionRPT_DEBUG_REFERENCE` | `SQL-SERVER` | SQL authentication |

`db.doQuery` uses a shared `SqlConnection`. Statements whose trimmed text starts with `SELECT` are filled through a data adapter. Other statements use `ExecuteNonQuery` with `CommandTimeout` 0. Errors on the non-select path return `Nothing`. Select SQL exceptions with error code `-2146232060` return an empty dataset.

### Tables this repository writes or reads by name

Created by scripts in-repo (must be applied by hand):

- `repslyOrder`, `repslyOrderItem`, `repslyImportRun` — `Scripts/CreateRepslyTables.sql`
- Columns on `fsrRequisition`: `blnSentToSap`, `dtSentToSap`, `intSapRetryCount`, `strSapLastError`, `strSapLastRequest`, `strSapLastResponse` — `Scripts/AlterFsrRequisition_SapColumns.sql`
- `Scripts/AlterRepslyOrder_FsrColumns.sql` and `DropRepslyOrderSalesDocument.sql` — referenced by process comments for `DocumentTypeName` and `refFsrRequisitionID`

Used and not created here: `sysCronJob`, `sysQuery`, `stgSuperbowlLog`, `fsrRequisition`, `fsrItem`, `fsrRequestType`, `porRequisition`, `porReceipt`, `porReceiptItem`, `porItem`, `porDocument`, `porVendor`, `mstUser`, `mstRep`, `mstVendor`, `dwdProduct`, `dwfSales`, `dwfQF`, `viePORequsitionManager`, `viePOR_UGAccess`, `mstUsageLog`.

`sysQuery.ID` values referenced from `frmCronjob.vb`: 38, 39, 43, 46, 47, 48, 49, 54, 55, 56, 57, 61, 63, 64, 65, 66, 67, 68, 69, 70. Bodies are HTML with bracket tokens such as `[ToName]` and `[PinkSlipNumber]`.

## APIs

### Repsly

`GET {RepslyBaseUrl}/export/purchaseorders/{lastDocumentId}`

Committed base URL: `https://api.repsly.com/v3`. Basic authentication. The client rewrites selected JSON keys to Pascal case before deserialising, because `JavaScriptSerializer` is case-sensitive. Pagination stops when `TotalCount` is 0 or `LastID` is not positive.

### Edward Snell

`POST` to `ESnellSoapEndpoint`. Committed URL:

`http://ediqa.esnell.co.za:8000/sap/bc/srt/rfc/sap/zso_idoc_input/100/zso_idoc_input/zso_idoc_input`

SOAP action header: configured `ESnellSoapAction` (committed `ZRfcEdiIdocInputOrders`), quoted. Content type `application/soap+xml; charset=utf-8`. Timeout 60 seconds. Basic authentication. Success is HTTP success and no SOAP fault element in the body. The free-stock path stores request and response on the requisition. The order path does not.

## External integrations

| Integration | Config keys | Notes |
|---|---|---|
| SMTP release | `SMTPHost` (committed `10.0.0.2`) | No credentials in the release branch. From address `administrator@rgbc.co.za` with a function-specific display name |
| SMTP debug | Hardcoded host `smtp.office365.com` port 587, SSL | Mailbox and password are literals in `frmCronjob.vb` and `FsrOrder_ESnell_Process.vb`. Values omitted |
| File shares | Compile-time constants | Release: `\\Hennessy\Sales\ADM\`, `\\Ruinart\documents\`, `\\Hennessy\Sage\Afw\PO-Adinga\`. Debug: `C:\Documents\RGBC\`, `C:\Documents\POR\`, `C:\Documents\PO Exports\` |
| Local drop folders | `dirImports`, `dirImported`, `dirExports` | Load overwrites imports and exports to `{exe}\Imports\` and `{exe}\Exports\` |

## Infrastructure and deployment

No Dockerfile, no pipeline, no installer project. Deployment is a copied `bin` folder plus config. `bin/Release` and `bin/Debug` are already in git, including `Superbowl Cronjob.exe.config` copies of settings.

`BuildProcessTemplates/*.xaml` are Visual Studio 11 / TFS build definitions. Nothing in the later commits references them. Treat them as unused legacy. Provenance: **Inferred**.

How the exe is scheduled is **Unknown / Requires Confirmation**.

## Environment configuration

Settings live in `app.config` under `Superbowl_Cronjob.My.MySettings` (application and user scopes). Names that affect behaviour:

`dwfQFTable`, `intDecimals`, `UNAME` (`SuperbowlCron`), `SMTPHost`, `Debug`, `SalesManagerMaxValue`, `Emails`, `FinanceAccountant`, `SQLConnectionDebug`, `SQLConnectionRelease`, `CompleteIncomplete`, `RepslyBaseUrl`, `RepslyUsername`, `RepslyPassword`, `ESnellSoapEndpoint`, `ESnellUsername`, `ESnellPassword`, `ESnellSoapAction`, `RepslyFsrBillToAttributeTitle`, `RepslyFsrRequestTypeName`, `FsrSapExportEnabled`, `FsrSapAugru`, `FsrSapBsart`, `FsrSapZ1Email`, `SapMaxRetryCount`, `SapFailureNotifyEmail`, `dirImports`, `dirImported`, `dirExports`, `SuperUserAddresses`, `FinancialAccountant`, `FinanceExecutive`, `Accounts`, `SnellEmailAddress`, `BCCAddresses`, `PorEmailAddress`.

`RepslyFsrBillToAttributeTitle` is set to `Western Cape | Bill-to Account No`, but `RepslyFSR_AM_Process` scans five regional titles in code and does not read that setting for the scan list. Provenance: **Discrepancy** between the setting and the process. The code path is the one that runs.

## Auth

No interactive login. Release SQL uses the Windows account. Repsly and Edward Snell use HTTP Basic from settings. Debug SMTP uses a hardcoded mailbox. Several recipient lists are fixed addresses in config or in source (`accountspayable@rgbc.co.za` on `POR_Activate`).

## Background work

There is no separate worker service and no queue broker. `BackgroundWorker` `bgwTimer` only updates a progress label. The job queue is the `sysCronJob` table. `PreProcess` is synchronous on the UI thread before the queue loop.

## Storage

- SQL Server, as above.
- PDF files under `{exe}\Reports\PDF\`.
- CSV files written locally by `Export_Process` and copied to the Sage share (`UploadFile`).
- Import archive folder `{exe}\Imported\` for the unused CSV import classes.

## Monitoring

`WriteLog` inserts into `stgSuperbowlLog`. A code comment says production clears that table daily; the purge is not in this repo. `app.config` also attaches a `FileLogTraceListener` for `My.Application.Log`. Event Log listener is commented out. Failed SAP exports can send mail. There is no health endpoint.

## Security observations

These are facts about the tree, not a penetration test.

- Database, Repsly, and Edward Snell secrets are committed in `app.config` and in copied `exe.config` files under `bin/`.
- A debug SMTP password is committed in VB source in more than one class.
- `Superbowl Cronjob_TemporaryKey.pfx` is committed.
- SQL statements are string-built. `CleanString` escapes quotes. Identifiers and numeric ids are concatenated directly.
- The vendor reminder query is syntactically invalid as concatenated (`porVendorWHERE`).
- `.gitignore` does not ignore `bin/`, `obj/`, `packages/`, `app.config`, or `*.pfx`.

## Development and testing

Open `Superbowl Cronjob/Superbowl Cronjob.sln` in Visual Studio, restore is unnecessary if the committed `packages` folder is present, target .NET Framework 4.8.

No unit tests, no integration tests, and no CI definition are in git. The Repsly plan's test todos are still `pending`.

`Scripts/InsertFourOrdersFromPdf.sql` and `InsertSapOrder2955000.sql` are data scripts. They were not executed during this inspection. Treat them as manual fixtures. Provenance: **Unknown** whether they are safe to re-run.

## Logging and support fields

Free-stock SAP debugging is designed to survive the daily log purge by writing `strSapLastRequest` and `strSapLastResponse` on `fsrRequisition`. Those columns exist only after the ALTER script.

## Discrepancies to confirm

1. Scan-host revision `ce672a6` (2023-05-29) versus `origin/master` `986893e` (2026-09-11).
2. Sonar files on the scan host versus their absence from git.
3. SQL server name `Benriach` versus file servers renamed to Hennessy and Ruinart.
4. Setting `Debug=True` and `FsrSapExportEnabled=False` versus whatever is deployed.
5. Plan file says there are no HTTP clients and that live tests are pending; the clients are merged.
6. Header comments name job types that the `Select Case` does not use.
7. `RepslyFsrBillToAttributeTitle` is unused by the five-region scan.
8. Vendor pending SQL missing a space.