# Technical documentation — snell-cronjob

Evidence date: 2026-10-03. Revision: `master` `f092e9b` (2026-06-26).

Credential values that appear in source are not copied here. Server names, catalog names, mailbox addresses, and setting names are.

## Architecture

One Visual Studio solution, one Windows Forms executable, one SQL Server catalog, optional outbound SOAP.

```text
Snell Cronjob.sln
└── Snell Cronjob/Snell Cronjob.vbproj    WinExe, root namespace Snell_Cronjob
    ├── frmCronjob                 UI + PreProcess + queue
    ├── Process                    file + staging base class
    ├── FreeStockRequisition_AM_Process
    ├── FsrOrder_ESnell_Process
    ├── App_code/VB/IdocGenerator.vb
    ├── App_code/VB/ProjectFunctions.vb    PDF, log, unsalt
    ├── App_code/VB/SystemFunctions.vb     unused by the run path
    ├── App_code/VB/Duodecimal.vb          unused by the run path
    ├── db.generic.vb                      class db
    ├── db.cls.vb                          empty class xdb
    ├── dsRequisition.* / dsItem.*         report data
    ├── SuperbowlDataSet.*                 compiled, no external caller
    ├── app.config                         the settings that ship in git
    └── Scripts/                           SQL column script + SOAP probe
```

Startup object: `Snell_Cronjob.My.MyApplication`. Main form: `frmCronjob`. `Option Strict` is Off. `Option Infer` is On. `Option Explicit` is On.

Call order inside `Run`:

1. `PreProcess` → AdvM import → SAP export → midday chase.
2. `SELECT * FROM sysCronJob WHERE Status = 'New' ORDER BY dtCronJob DESC`.
3. Handle `FSR_NewRequisition` and `FSR_RequisitionApproved`.
4. `UPDATE` rows whose status contains `Processing` back to `New`.

There is no dependency injection, no app server, and no message bus. The queue is the table `sysCronJob`.

## Language, framework, and tooling

| Item | Evidence |
|---|---|
| Language | VB.NET |
| UI | Windows Forms (`MyType` WindowsForms) |
| Target framework | `TargetFrameworkVersion` v4.8 in the vbproj. `app.config` startup sku `.NETFramework,Version=v4.8` |
| Stale build artifacts | `obj/Debug` and `obj/Release` `AssemblyAttributes` still say v4.6.2 |
| Historical tools version | `OldToolsVersion` 3.5. `ProductVersion` 9.0.30729 |
| IDE metadata | `SccProjectName` and related fields are `SAK` |
| Report control | `Microsoft.ReportViewer.WebForms` and `.Common` 10.0.40219.1, `targetFramework` net48 in `packages.config` |
| Assembly | Title `Snell Cronjob`. Version `1.0.1.1`. File version `1.0.0.0`. Company `ESnell & Co`. COM not visible. GUID `a741899e-bae6-479a-a51f-8571ee51e228` |
| Platform | `AnyCPU`, `Prefer32Bit` false, both Debug and Release |

`bin/` and `obj/` are committed. `.gitignore` is a generic template (Node, Java, Python, logs). It does not ignore `bin/`, `obj/`, `*.pfx`, or `*.config`.

NuGet packages are not in the tree. Restore must produce `packages/Microsoft.ReportViewer.Common.10.0.40219.1/` beside the solution, because hint paths are `..\packages\...` relative to the vbproj folder.

## Configuration

`app.config` is copied to `Snell Cronjob.exe.config` (both Debug and Release copies are committed and match the source settings for the keys they contain).

Connection strings:

| Name | Git target | Security |
|---|---|---|
| `SQLConnectionRelease` | `ESFSPHP02\SQLEXPRESS`, catalog `snell_2015`, timeout 60 | Integrated Security |
| `SQLConnectionRPT` and `SQLConnectionRPT_LIVE_REFERENCE` | Same server and catalog | Integrated Security |
| `SQLConnectionDebug`, `SQLConnectionRPT_DEBUG`, `SQLConnectionRPT_DEBUG_REFERENCE` | `SQL-SERVER`. Debug catalog `snell_2015`. The `DEBUG_REFERENCE` catalog is `Superbowl` | SQL login. Password omitted |

Which connection the code opens:

```vb
#If DEBUG Then
  SqlConnection(My.Settings.SQLConnectionDebug)
#Else
  SqlConnection(My.Settings.SQLConnectionRelease)
#End If
```

The shared `db.conn` is created when the type is first used. Report datasets use `SQLConnectionRPT` (`dsRequisition.xsd` connection ref). A DEBUG compile of the exe still uses the release integrated-security string for the report if that is what `SQLConnectionRPT` contains. The report connection is not switched by `#If DEBUG`.

Application settings present in `app.config`:

| Setting | Git value | Scope in Settings.settings |
|---|---|---|
| `Debug` | `False` | Application |
| `SalesManagerMaxValue` | `10000` | Application |
| `SMTPHost` | `mail.esnell.co.za` | Application |
| `SMTP_Username` | empty | Application |
| `SMTP_Password` | empty | Application |
| `UNAME` | `SnellCron` | Application. No reader in the FSR path |
| `Emails` | maanie@overdrive.co.za | Application. No reader in the FSR path |
| `intDecimals` | `1` | Application |
| `Setting` | `0` | Application |

User-scoped values in the same file (a per-user `user.config` overrides these when `SaveMySettingsOnExit` runs):

| Setting | Git value |
|---|---|
| `dirImports` | `Imports\` — overwritten in `Load` to `{BaseDirectory}Imports\` |
| `StaleFileNotifyManagerEmail` | aimmieg@esnell.co.za |
| `StaleFileNotifyCCEmail` | justink@esnell.co.za |
| `StaleFileNotifyPeriodHours` | `1` |
| `StaleFileNotifyStartTime` | `#07:45:00 AM#` — unread |
| `StaleFileNotifyEndTime` | `#08:00:00 AM#` — unread |
| `SnellEmailAddress` | orders@esnell.co.za,Nondumisom@esnell.co.za,lloyd@overdrive.co.za |
| `SnellEmailAddress2` | espda@esnell.co.za |
| `BCCAddresses` | empty. The reader is commented out |

SAP settings exist only as `DefaultSettingValue` attributes on `My Project/Settings.Designer.vb`. They are not in `Settings.settings` or `app.config`.

| Property | Default attribute |
|---|---|
| `FsrSapExportEnabled` | `False` |
| `ESnellSoapEndpoint` | empty |
| `ESnellUsername` | empty |
| `ESnellPassword` | empty |
| `ESnellSoapAction` | `ZRfcEdiIdocInputOrders` |
| `SapMaxRetryCount` | `5` |
| `FsrSapBsart` | `OR` |
| `FsrSapAugru` | `279` |
| `FsrSapZ1Email` | rgbcpda@esnell.co.za |
| `SapFailureNotifyEmail` | maanie@overdrive.co.za |

Paths compiled into the form, not into settings:

| Build | Download directory |
|---|---|
| DEBUG | `D:\Documents\Work Stuff\ESnell\` |
| Release | `C:\am_ftpdownload\downloads\` |

## Database

Catalog name in the release connection: `snell_2015`. There is no schema project and no migration tool. One idempotent script adds columns.

### Tables and objects this executable names

| Object | How it is used |
|---|---|
| `stgFreeStockRequisition` | Truncated and inserted by the CSV import. Columns in the INSERT: UniqueID, RepCode, RequestType, Date, Region, ExpenseType, BillToAccount, ShipToAccount, RequestDetail, ContactPerson, ContactNumber, Delivery, SKU, QTY_CASE, QTY_BOTTLE |
| `fsrRequisition` | Insert on import. Update pink slip, manager ids, SAP columns, `dtEmailSentToAltManager`. Read for SAP and mail |
| `fsrItem` | Insert on import. Read for SAP and for the PDF |
| `fsrRequestType` | Via `sysQuery` 41 (comment only) and joins in the mail queries |
| `dwdProduct` | Join on `strProdCode` for price, brand, and description |
| `dwdCustomer` | `strSAPCode` by `strCustomerNo`. Also the PDF's bill-to and ship-to names |
| `mstUser` | Rep email on SAP (`strEmail`). Name and email on the mail queries |
| `sysCronJob` | Queue. Columns the code reads or writes: CronJobID, dtCronJob, Type, param, Status, Result |
| `sysQuery` | IDs 38 and 39 mail HTML. IDs 40, 41, 42 import SQL. Column `memQuery` |
| `vieRequsitionManager` | Spelled that way in source. One row per requisition for manager emails and `TotalRequisitionValue` |
| `stgSuperbowlLog` | Default log table |
| `mstCompany`, `mstPeriod`, `mstUserGroup` + domain, `mstUsageLog` | Referenced only by unused helpers |

`fsrRequisition` columns this program writes, gathered from INSERT and UPDATE text: `refUserID`, `UniqueID`, `refRequestTypeID`, `dtDate`, `strRegion`, `strExpense`, `strBillToAccount`, `strShipToAccount`, `txtRequestDetail`, `strContactPerson`, `strContactNumber`, `blnDelivery`, `strStatus`, `strPinkSlipNumber`, `refManagerID`, `refAlternateManagerID`, `refExecutiveManagerID`, `dtEmailSentToAltManager`, `blnSentToSap`, `dtSentToSap`, `intSapRetryCount`, `strSapLastError`.

PDF reads additional columns (`strInvoiceNumber`, `dtLastEdit`, `strAuthorizedBy`) that this program does not write.

`sysCronJob.Status` values this program writes: `New`, `Processing`, `Processed`, `Warning`, `Error`, `Processed_MailError`. It also resets `LIKE '%Processing%'` to `New`. `snell-freestock` can set `Removed`. This program does not.

`db.doQuery` behaviour:

- SQL whose trimmed upper-case form starts with `SELECT` is filled through `SqlDataAdapter`. `intRows` becomes the row count.
- Anything else uses `SqlCommand.ExecuteNonQuery` with `CommandTimeout` 0. The connection is closed if it was open, then opened, then closed in `Finally`.
- `getRow` returns the first row, or `Nothing` on any exception.

`CleanString`: null becomes empty; `"` becomes `'`; `'` is doubled; result is trimmed.

## SAP client

`PostToESnell`:

- `HttpWebRequest`, method POST, `KeepAlive` false, timeout 60000 ms.
- Header `Authorization: Basic` of UTF-8 `username:password`.
- Header `SOAPAction` is the setting wrapped in quotes.
- Body UTF-8.
- `WebException` reads the error body and rethrows `ESnell SOAP request failed: ...`.

`ParseSoapResponse` is a substring search, not an XML parse. Fault detection is case-insensitive for `<faultcode>` and `<soap:fault>`, and a bare `:fault>` match.

`IdocGenerator` builds one envelope per requisition. Namespace `urn:sap-com:document:sap:soap:functions:mc-style`. SOAP 1.2 envelope URI `http://www.w3.org/2003/05/soap-envelope`. Element names are the shortened E1EDK / E1EDP segments listed in `features.md`. `XmlEscape` replaces `& < > " '`.

The PowerShell script uses the same content type, SOAPAction quoting, and Basic header. Sample partner `0000107037`, material `10325`, pink slip `ES0000999`. It does not update SQL.

## PDF

`LocalReport` from `Microsoft.Reporting.WebForms`.

- Report path: `{BaseDirectory}Reports\RDLC\Requisition.rdlc`.
- Device info: `<DeviceInfo><SimplePageHeaders>True</SimplePageHeaders></DeviceInfo>`.
- Parameter `RequisitionID` (string in the method, integer on the table adapter).
- Data sources named `dsRequisition` and `dsItem`.
- Render format `PDF`.
- Output directory created if missing: `{BaseDirectory}Reports\PDF\`.

The vbproj embeds `bin\Release\Reports\RDLC\Requisition.rdlc`. There is no `Reports` folder next to the vbproj. A run from `bin\Release` can find the file because that is where it is committed. A run from another output directory cannot, unless something copies it.

`dsRequisitionTableAdapter` and `dsItemTableAdapter` open `SQLConnectionRPT` themselves. They do not use `db.conn`.

## Email

Release client, repeated in each mail block:

- `SmtpClient(My.Settings.SMTPHost)` — no port argument, so the framework default (25).
- `EnableSsl = False`.
- `NetworkCredential(SMTP_Username, unsalt(SMTP_Password))`.

Debug client, inside `#If DEBUG`:

- Host `smtp.office365.com`, port 587, SSL true, default credentials false.
- Username and password are literals in `frmCronjob.vb` (three times) and in `FsrOrder_ESnell_Process.SendFailureEmail`. The password is not recorded in Silicon Ark.

`MailMessage` is HTML (`IsBodyHtml = true`) for every send, including the stale-file body which is a single sentence without tags.

Attachments use `Attachment` on the PDF path. The attachment is not disposed separately from the message.

## Authentication and access control

No interactive login on the run path.

Release SQL uses the Windows identity of the process. Debug SQL uses a SQL login stored in config.

ESnell uses HTTP Basic.

SMTP uses the configured username and password. Empty in git.

`LogginUser` would match `Request.LogonUserIdentity.Name` to `mstUser.strDomainLogin` through `mstUserGroupForecast` (the domain argument is the literal `Forecast`) and insert `mstUsageLog` with `strSystem` `'Forecast'`. It is not called.

`unsalt` is the only transform on the SMTP password.

## Security notes for review

These are observations from the tree, not a penetration test.

- Debug SQL passwords and a debug SMTP password are stored in the repository. Values are omitted here. They should be treated as exposed. **Requires Confirmation** that they are not production credentials and that they have been rotated.
- `Superbowl Cronjob_TemporaryKey.pfx` is committed. `SignManifests` and `SignAssembly` are false, so the build does not sign with it. A private key in git is still a secret-management problem.
- Release SMTP does not use SSL.
- SQL is string-built. `CleanString` is applied to some fields and not to others (quantities, ids taken from the database, SAP error text goes through `CleanString`).
- The approved-mail BCC address is hard-coded.
- SAP success does not require a business-level acknowledgement.
- `sysQuery` HTML is inserted into mail without further encoding of the replaced field values.

## Deployment

ClickOnce properties:

| Property | Value |
|---|---|
| `PublishUrl` | `C:\Users\pieter\Desktop\snell_cj\` |
| `Install` | true |
| `InstallFrom` | Disk |
| `UpdateEnabled` | false |
| `ApplicationRevision` | 1 |
| `ApplicationVersion` | `1.0.0.*` |
| `PublishWizardCompleted` | true |
| `GenerateManifests` | true |
| `SignManifests` | false |
| Manifest key file | `Superbowl Cronjob_TemporaryKey.pfx` |
| Thumbprint | `A858F5107FCC41B574042F1EE9C9D5403AFC01E3` |

Committed ClickOnce manifests exist under `bin/Debug` and `bin/Release` (`Snell Cronjob.application`, `.exe.manifest`). They are build outputs, not a hosting setup.

No Dockerfile, no pipeline YAML, no installer project, no service wrapper, no scheduled-task XML.

**Unknown / Requires Confirmation:** the host that runs the exe, the account, the schedule, and whether production config differs from `app.config`.

Deploying the SAP feature, as the script comment states, means applying `Scripts/AlterFsrRequisition_SapColumns.sql` and putting endpoint plus credentials into the exe config (or the designer will keep export off and credentials empty). The keys must be added by hand. They are not generated from `Settings.settings`.

## Logging and monitoring

| Channel | What it receives |
|---|---|
| `stgSuperbowlLog` via `WriteLog` | info, warning, error, debug, and dev lines from import, mail catch blocks, and SAP |
| `sysCronJob.Result` | Short outcome text and a timestamp `yyyyMMdd HH:mm:ss` |
| `app.config` `FileLogTraceListener` | `My.Application.Log`, initializeData `FileLogWriter`. The FSR code does not call `My.Application.Log` |
| Form labels | Queue `n/m` and elapsed `HH:mm:ss` during `UpdateProgress` |

There is no health endpoint, no metrics sink, and no alert except the SMTP mails.

`My.Settings.Debug = True` adds extra `dev` log lines of the import SQL. That flag is independent of the compile-time `DEBUG` constant.

## Development and testing

- Open `Snell Cronjob.sln` in Visual Studio with .NET Framework 4.8 targeting pack and the ReportViewer 10 packages restored.
- DEBUG compile uses the debug SQL string, the `D:\` download folder, Office 365 SMTP, and does not auto-run.
- `btnRun` calls `Run`. A commented line in the click handler would reset every job to `New`. It is not active.
- No unit-test project, no solution configuration for tests, and no CI definition.
- `SendEsnellFsrTest.ps1` is the only repeatable external check, and it can create a SAP order if pointed at a live endpoint with a real payload. The built-in sample uses pink slip `ES0000999`.

## Data flow for one approved pink slip that began in the web app

This sequence crosses repositories. The web steps are documented in `snell-freestock` and are not re-derived here.

1. Staff save and submit in `snell-freestock`. That app sets `Pending` and inserts `FSR_NewRequisition`.
2. This exe's next `Run` sends WF-5 mail and marks the job `Processed`.
3. A manager or executive approves in the web app. That app sets `Approved` and inserts `FSR_RequisitionApproved`.
4. This exe's next `Run` attempts SAP export first (no-op while the switch is off), then sends the approved PDF mail.

An AdvM CSV skips step 1's web Draft. This exe inserts `Pending` and `FSR_NewRequisition` itself.

## Inherited Superbowl surface

Names that still say Superbowl: log table `stgSuperbowlLog`, dataset `SuperbowlDataSet`, PFX `Superbowl Cronjob_TemporaryKey.pfx`, XSD annotation `Superbowl.dbo.fsrRequisition`, debug reference catalog `Superbowl`, comment on `IdocGenerator` ("Adapted from superbowl_cronjob Repsly/PDA integration").

`Duodecimal`, fiscal helpers, `MergeDataSets`, and `doQueryXTab` match a reporting cron more than the FSR mailer. They are compiled into `Snell Cronjob.exe` and have no caller on `frmCronjob`'s path.