# PRD — snell-crondaily

Evidence date: 2026-10-03.

Source: `git@bitbucket.org:overdrive/snell-crondaily.git`, `master` at `60aeb26` (2023-11-17).

There is no prior PRD in the source repository or in Silicon Ark. Requirements below are recovered from the VB.NET project, `App.config`, the form designer, and git history. Provenance is labelled. Credential values are omitted.

Related products that name the same SQL Server catalog `snell_2015`: `snell-freestock` (staff requisitions) and `snell-cronjob` (free-stock batch, SAP export, requisition mail). This executable does not call those programs. It does contain an uncalled method that reads `fsrRequisition`.

## Product purpose

**Implemented.** Import two delimited extracts into SQL Server staging tables and apply warehouse statements that live in `sysQuery`. Then email an HTML copy of the batch log.

**Documented** only as inline version comments, the form title `Snell CronDaily`, and the footer built at startup. There is no README.

**Unknown / Requires Confirmation:** who schedules the process, whether production sets application setting `Debug` to false, and which system produces `pda_crm.txt` and `pdaprod.txt`.

## Users and interactions

This program has no login screen.

| Actor | What they do in this system | Status |
|---|---|---|
| Process host | Starts `SNELL CronDaily.exe`. When application setting `Debug` is false, `Run` executes and the form closes. When it is true, the form stays open | **Implemented**. The scheduler is **Unknown**. The value in git is `True` |
| Operator | On the open form: Run, Email Log, and the Customer / Product checkboxes. Partial Import is visible and has no effect | **Implemented** |
| File drop | Leaves `pda_crm.txt` and `pdaprod.txt` in the compile-specific download directory | **Inferred** from the paths and filenames. The producer is not in this repo |
| Log recipients | Receive the HTML log. Addresses come from `EmailRgbc` | **Implemented** as recipients. They do not use this UI |
| Developer | DEBUG compile uses a different database, SMTP host, and download folder from the Release compile | **Implemented** |

Config mailboxes present in git (addresses, not proof that the mailbox is still monitored):

| Setting | Value in `App.config` / `Settings.settings` | Value in tracked `bin\Debug\SNELL CronDaily.exe.config` | Read by |
|---|---|---|---|
| `EmailRgbc` | lloyd@overdrive.co.za; maanie@overdrive.co.za; dimphoz@esnell.co.za | pj@overdrive.co.za; maanie@overdrive.co.za | `EmailLog` |
| `EmailBfbe` | maanie@overdrive.co.za; | pj@overdrive.co.za; | Nothing in this solution |
| Un-invoiced mail To | Literal `Genevieve@esnell.co.za` | same source file | `EmailUnIinvoicedRequisitions`, which `Run` does not call |
| Un-invoiced mail CC | Literals `pj@overdrive.co.za` and `maanie@overdrive.co.za` | same source file | same uncalled method |

`234cedd` (2023-11-17) replaced `EmailRgbc` (`pj@overdrive.co.za; maanie@overdrive.co.za`) and `EmailBfbe` (`pj@overdrive.co.za`). The tracked Debug `exe.config` still has the previous values. A process that runs that output without a rebuild mails the old list.

## Systems this product talks to

1. **SQL Server.** Release compile: `ESFSPHP02\SQLEXPRESS`, catalog `snell_2015`, integrated security, timeout 60. DEBUG compile: `SQL-SERVER`, catalog `snell_2015`, SQL authentication, timeout 60. Passwords are omitted here.
2. **SMTP.** Release compile: host `172.16.2.22` from `SMTPHostRelease`, SSL off, username `EDWARDSNELL\phpadmin`, password from `SMTP_Password` after `unsalt` (the substring `ASP` is replaced with `php`). From address `SNELL CronDaily <administrator@esnell.co.za>`. DEBUG compile: `smtp.office365.com` port 587, SSL on, username written in `ProjectFunctions.EmailLog`. The password is omitted here. From address `SNELL CronDaily <smtp@overdrive.co.za>`. `SMTPHostDebug` (`192.168.100.6`) remains in settings and is not used. The line that constructed a client from it is commented out.
3. **Local disk.** Release download directory `C:\SAPREPORTS\`. A comment on that line says SQL Server 2014 Express and SQL Server Management Studio 2014 Express are installed. DEBUG download directory `D:\Documents\Work Stuff\ESnell\`. Working copies `{exe}\Imports\`, `{exe}\Imported\`, `{exe}\Exports\`.

SonarQube project files and Bitbucket Pipelines are not in this repository. The onboarding scan named `sonar-project.properties` and `sonarqube_scan.bat`. They are not in git.

## Major workflows

### WF-1 — Start and stop

Trigger: process start. `frmMain_Load` sets:

- `dirImports` = `{BaseDirectory}\Imports\`
- `dirImported` = `{BaseDirectory}\Imported\`
- `dirExports` = `{BaseDirectory}\Exports\`

Those three labels are shown on the form. `SaveMySettingsOnExit` is true, so the user-scoped copies are written on exit.

The footer becomes `SNELL CronDaily Version 1.` plus assembly build, a dot, assembly revision, ` - `, and the constant `17 Nov 2023`. With `AssemblyVersion` `1.0.1.1` that text is `SNELL CronDaily Version 1.1.1 - 17 Nov 2023`.

| Condition | Behaviour |
|---|---|
| `My.Settings.Debug` is false | `Run` executes, then `Me.Close()` |
| `My.Settings.Debug` is true | Form stays open. Run is the Run button. Email Log is a separate button |

The compile symbol `DEBUG` does not decide this. It decides the download directory, the SQL connection, the SMTP client, and whether archive copies or moves the file. A Release build can still have setting `Debug` = true. The value committed in `App.config` is true, with a comment that production should set it to false.

`SingleInstance` is true. `ShutdownMode` is 0 (when the startup form closes). Provenance: **Implemented** (`Application.myapp`).

### WF-2 — Batch log frame

Trigger: `Run`.

1. `TRUNCATE TABLE stgSuperbowlLog`.
2. Insert an info row: process `SNELL CronDaily`, action `Batch Start`.
3. Run each checked import (WF-3, WF-4).
4. Walk `lstExport`. The list is empty, so this walk does nothing.
5. Insert an info row: action `Batch Completed`.
6. Call `EmailLog` (WF-5).

Before `234cedd`, `EmailLog` ran before the truncate. The comment in source still says the 2016 change sent mail before the truncate. The live order is mail after the batch. The copy from `stgSuperbowlLog` into `logSuperbowl` is commented out. A truncate at the start of the next run discards the previous batch log.

Provenance: **Implemented**.

### WF-3 — Import customers

Trigger: `Run`, and checkbox `chkCustomer` is checked. The designer sets that checkbox checked.

Process name `Customer`, file `pda_crm.txt`, primary-key field list `ENTITY_CODE`. Staging table `stgCustomer`. Dimension table used by the in-repo SQL: `dwdCustomer`.

1. Log process start.
2. If `{download dir}\pda_crm.txt` exists, copy it over `{exe}\Imports\pda_crm.txt`. If it does not exist, log a warning and continue. An older file already in `Imports\` can still be imported.
3. Accept the Imports file only when the extension is `.txt` or `.csv`. Count lines and subtract 1. The comment says the count is always one too high.
4. If the file is valid: `TRUNCATE TABLE stgCustomer`, parse comma-separated quoted fields, skip the first record as a header, insert each later record, then archive.
5. If the file is valid: log duplicate `ENTITY_CODE` values, set `dwdCustomer.blnActive = 0` on every row, run `sysQuery` 23 (logged as updated), `sysQuery` 24 (logged as inserted), set `blnPromotionalAccounts = 0` on every `dwdCustomer` row, run `sysQuery` 25 (logged as promo accounts), then set `strRegionDesc2 = 'Gauteng'` where `strRegionDesc2` matches `Gauteng %`.
6. Log process completed even when the file was missing or invalid.

`SetDeletes` and `DeleteDW` are not called. The partial-import argument that used to be passed into `SetDeletes` is commented out.

Provenance: **Implemented**. The SQL text of queries 23–25 is **Unknown**.

### WF-4 — Import products

Trigger: `Run`, and checkbox `chkProduct` is checked. The designer sets that checkbox checked.

Process name `Product`, file `pdaprod.txt`, primary-key field list `ITEM_CODE`. Staging table `stgProduct`. Dimension table used by the in-repo SQL: `dwdProduct`.

Steps match WF-3 through staging import and archive, then:

1. Log duplicate `ITEM_CODE` values.
2. Set `dwdProduct.blnActive = 0` on every row.
3. Run `sysQuery` 21 (updated) and `sysQuery` 22 (inserted). Failures are logged as warning, not error. Customer warehouse failures are logged as error.

There is no Gauteng rewrite and no promotional-account step on products. `SetDeletes` and `DeleteDW` are not called.

Provenance: **Implemented**. Query text is **Unknown**.

### WF-5 — Email the log

Trigger: end of `Run`, or the Email Log button.

Load `sysQuery` ID 43 and run `memQuery`. The commented SQL above that call filters out `strType = 'debug'`, excludes rows already emailed, and groups `Free Stock Requisition` lines. That comment is not the executed statement.

Build an HTML table (date, process, action, type, log). Row colour: error `#FFC2B7`, warning `#FFDBB7`, debug `#B7FFBC`, info from `getHexColourString` of the process name, anything else `#E0E0E0`. An empty result prints `- No Log items found -`.

Send one HTML message per non-empty token of `EmailRgbc` split on `;`. Tokens are not trimmed, so a leading space stays on the address. Subject: `SNELL CronDaily Log [` + universal sortable timestamp + `]`.

On success, `Finally` sets `blnEmail = 1` where `blnEmail = 0`. On a general failure, the catch sets `blnEmail = -1` where `blnEmail = 0`, writes an error log row (itself `blnEmail = 0`), then `Finally` marks that new row as emailed. An `OutOfMemoryException` tries the send again and does not set `blnEmail = -1`.

Provenance: **Implemented**. Which rows query 43 returns is **Unknown**.

## Functional requirements

### FR-001 — Stay open when Debug is true

The system closes itself after `Run` only when application setting `Debug` is false.

- Trigger: form load.
- Input: `My.Settings.Debug`.
- Output: either a completed batch and a closed window, or an open window.
- Evidence: `frmMain_Load`. Implementation: **Implemented**. Confidence: High.
- The committed config value is true. Whether production overrides it is **Unknown / Requires Confirmation**.

### FR-002 — Register the two imports

On load the system registers Customer (`pda_crm.txt`, key `ENTITY_CODE`) and Product (`pdaprod.txt`, key `ITEM_CODE`) against the compile-specific download directory.

- Evidence: `frmMain_Load`. Implementation: **Implemented**. Confidence: High.

### FR-003 — Run only checked processes

`Run` executes Customer only when a control named `chkCustomer` is checked, and Product only when `chkProduct` is checked. If the control cannot be found, the code treats the process as unchecked.

- Designer defaults: both checked.
- Evidence: `frmMain.Run`. Implementation: **Implemented**. Confidence: High.

### FR-004 — Replace the batch log at start

`Run` truncates `stgSuperbowlLog` before writing `Batch Start`.

- Evidence: `frmMain.Run`. Implementation: **Implemented**. Confidence: High.
- Persistence of older batches in another table is not performed. The insert into `logSuperbowl` is commented out.

### FR-005 — Copy the extract before reading it

For each started process, if `{download dir}{filename}` exists, copy it to `{exe}\Imports\{filename}`, overwriting. If it does not exist, write a warning and do not fail the batch.

- Evidence: `Process.DownloadFile`. Implementation: **Implemented**. Confidence: High.
- A missing source plus a leftover Imports file still allows import of the leftover. **Inferred** from the two methods being separate. Confidence: High that the code does this. Whether operators rely on it is **Unknown**.

### FR-006 — Accept only text or CSV

The Imports file is valid only when its extension is `.txt` or `.csv`. Any other extension logs an error and skips staging and warehouse steps. A missing Imports file sets the process invalid with no extra log line from `CheckLocalFile`.

- Evidence: `Process.CheckLocalFile`. Implementation: **Implemented**. Confidence: High.
- The error branch for an unsupported type reads `Me.fiImport.FullName` before `Me.fiImport` is assigned. That can throw. The outer catch then logs "Error reading file".

### FR-007 — Parse a header plus comma-separated rows

A valid file is read with `TextFieldParser`, delimited by comma, fields allowed in quotes. The first record is consumed and not inserted. Each following record is inserted with the process-specific SQL. Invalid cast, malformed line, and SQL errors are logged as warnings and the loop continues. Other exceptions are logged as error and the loop continues. SQL errors on record 1 are not logged.

- Evidence: `Process.ImportLocalFile`. Implementation: **Implemented**. Confidence: High.
- After the loop the system logs `count(*)` from the staging table against the line count from FR-006.

### FR-008 — Customer row rules

Each customer data row must supply 22 fields. After `CleanString`:

| Index | Column | Rule |
|---|---|---|
| 0 | `ENTITY_CODE` | Required. Stored as `CAST(CAST(RIGHT(value, 7) AS INTEGER) AS VARCHAR)` |
| 1 | `RECORD_EXTERNAL_REFERENCE` | Stored. Not required by this method |
| 2 | `HEADER_CustName` | Required |
| 3 | `HEADER_ChannelDesc` | Required |
| 4 | `HEADER_Premise` | Stored |
| 5 | `HEADER_Area` | Stored |
| 6 | `HEADER_AreaName` | Stored |
| 7 | `HEADER_RepName` | Stored |
| 8 | `HEADER_RegName` | Required |
| 9 | `HEADER_DelDay` | Stored |
| 10 | `HEADER_KeyAccName` | Stored |
| 11 | `HEADER_KeyRegName` | Stored |
| 12 | `HEADER_TeleName` | Stored |
| 13 | `HEADER_PriceGroup` | Stored |
| 14 | `HEADER_RepManName` | Stored |
| 15 | `HEADER_CredLimit` | Stored |
| 16 | `HEADER_ClassDesc` | Stored |
| 17 | `HEADER_TelephoneNumber` | Stored |
| 18 | `HEADER_FaxNumber` | Stored |
| 19 | `HEADER_Email` | Stored |
| 20 | `HEADER_Contact` | Stored |
| 21 | `HEADER_DebtorsSubcat` | Stored |

A blank required field throws `InvalidCastException` with a message that names the field. That row is not inserted.

- Evidence: `Customer_Process.sqlInsertStaging`. Implementation: **Implemented**. Confidence: High.
- The integer cast of the last 7 characters of `ENTITY_CODE` happens in SQL, not in VB. A non-numeric tail fails as a SQL exception on that row.

### FR-009 — Product row rules

`CleanString` is applied to indexes 0–3 only. Indexes 4 and 5 are validated as supplied.

| Index | Column | Rule |
|---|---|---|
| 0 | `ITEM_CODE` | Required. Stored as `CAST(CAST(RIGHT(value, 6) AS INTEGER) AS VARCHAR)` |
| 1 | `ITEM_NAME` | Required |
| 2 | `ITEM_CATEGORY_CODE` | Stored. Not required |
| 3 | `ITEM_BRAND` | Required |
| 4 | `ITEM_WSPCASE` | Required, converted with `CDbl` |
| 5 | `ITEM_WSPBOTTLE` | Required, converted with `CDbl` |

If case price plus bottle price equals 0, the row is rejected as invalid prices.

- Evidence: `Product_Process.sqlInsertStaging`. Implementation: **Implemented**. Confidence: High.
- Fewer than 6 fields throws before those checks. The catch in `ImportLocalFile` logs it and continues.

### FR-010 — Warn on duplicate keys, still load them

After a valid import, rows whose key appears more than once (`COUNT(ID) > 1`) are logged as warning `Duplicate Records`. They are not deleted.

- Customer key: `ENTITY_CODE`. Product key: `ITEM_CODE`.
- Evidence: `Process.LogDuplicates`. Implementation: **Implemented**. Confidence: High.
- This requires a column `ID` on `stgCustomer` and `stgProduct`. The column is not created in this repository. **Unknown / Requires Confirmation** that it exists.

### FR-011 — Deactivate the dimension before warehouse SQL

For a valid customer file, `UPDATE dwdCustomer SET blnActive = 0`. For a valid product file, `UPDATE dwdProduct SET blnActive = 0`. There is no filter.

- Evidence: `Process.SetInactives`, called from both `Run` methods. Implementation: **Implemented**. Confidence: High.
- Whether `sysQuery` 21–24 sets `blnActive` back to 1 for rows present in the file is **Unknown**. If those statements fail, the dimension stays inactive. That outcome is what the code does today. It is not a documented business approval.

### FR-012 — Apply warehouse statements from sysQuery

| ID | Caller | Log line on success |
|---|---|---|
| 23 | Customer | `{n} record(s) Updated.` |
| 24 | Customer | `{n} record(s) Inserted.` |
| 25 | Customer, after promo flags are cleared | `{n} promo account(s) updated.` |
| 21 | Product | `{n} record(s) Updated.` |
| 22 | Product | `{n} record(s) Inserted.` |

If `getRow` returns nothing, the following `dr!memQuery` throws. Customer logs that as error. Product logs it as warning.

- Evidence: `Customer_Process.ProcessDW`, `Product_Process.ProcessDW`. Implementation: **Implemented** as a lookup. The statement text is **Unknown / Requires Confirmation**.

### FR-013 — Reset promotional accounts, then reapply them

On a valid customer import, every `dwdCustomer.blnPromotionalAccounts` is set to 0, then `sysQuery` 25 runs. The reset is not inside the try block that wraps query 25. If query 25 fails, the flags remain 0 and an error is logged.

- Evidence: `Customer_Process.ProcessDW`. Implementation: **Implemented**. Confidence: High for the reset. Confidence: Low for what query 25 considers a promotional account.

### FR-014 — Collapse Gauteng region labels

After the promo step, `UPDATE dwdCustomer SET strRegionDesc2 = 'Gauteng' WHERE strRegionDesc2 like 'Gauteng %'`. The number of rows is logged. Exceptions are swallowed with an empty catch.

- Evidence: `Customer_Process.ProcessDW`, comment dated 2015-11-24. Implementation: **Implemented**. Confidence: High.
- The match is case-insensitive only to the extent SQL Server's collation makes `LIKE` case-insensitive. Collation is **Unknown**.

### FR-015 — Archive the imported file

DEBUG compile: copy Imports file to `{exe}\Imported\{filename}.{yyyyMMdd_HHmmss}{extension}`, overwrite allowed. Release compile: move it to the same pattern. Failure logs a warning. Warehouse steps still run if the file had already been marked valid.

Example name: `pda_crm.txt.20231117_145603.txt`.

- Evidence: `Process.ArchiveFile`. Implementation: **Implemented**. Confidence: High.

### FR-016 — Email the log to each EmailRgbc address

See WF-5.

- Evidence: `ProjectFunctions.EmailLog`. Implementation: **Implemented**. Confidence: High for the send loop. The selected rows depend on `sysQuery` 43.

### FR-017 — Allow a manual log send

Button Email Log calls `EmailLog` without truncating or importing.

- Evidence: `btnEmailLog_Click`. Implementation: **Implemented**. Confidence: High.
- If `Run` has not populated `stgSuperbowlLog`, the mail is whatever query 43 returns from the current table contents.

## Integrations

| System | Direction | Trigger | Data | Auth | Failure |
|---|---|---|---|---|---|
| File drop (`C:\SAPREPORTS\` or the DEBUG path) | In | Each checked process | `pda_crm.txt`, `pdaprod.txt` | Filesystem ACL. **Unknown** | Warning, process continues, leftover Imports file may still load |
| SQL Server `snell_2015` | Both | Batch start, each import, log mail | Staging rows, dimension updates, `sysQuery`, log rows | Release: Windows integrated. DEBUG: SQL login (secret omitted) | Per-row SQL warnings; warehouse catch logs error (customer) or warning (product); batch still attempts the email |
| SMTP | Out | End of `Run`, or Email Log | HTML log | Release: domain account on `172.16.2.22`. DEBUG: Office 365 account in source (secret omitted) | `blnEmail` set to -1, error row written, batch does not retry |

No HTTP API, queue, or message bus is referenced by the live path.

## Constraints

- Windows, .NET Framework 4.6.1, WinForms. `Option Strict` is Off.
- One instance (`SingleInstance` true).
- Requested execution level `asInvoker`. ClickOnce zone `LocalIntranet`. Manifests are not signed.
- Non-query SQL uses `CommandTimeout = 0` (no timeout). The SELECT path uses the connection timeout of 60 seconds. A SQL timeout error code `-2146232060` on a SELECT returns an empty dataset and does not throw.
- `CleanString` turns `Nothing` into empty, replaces `"` with `'`, doubles `'`, collapses double spaces, and trims. It is not a parameterised query. Values are concatenated into SQL.
- Directories `Imports`, `Imported`, and `Exports` must already exist. The code does not create them. `File.Copy` failure is logged as a download error.
- No automated test project is in the solution.

## Assumptions (not requirements)

- That "CronDaily" means a daily schedule. The repository does not define a clock or a task-scheduler entry.
- That `C:\SAPREPORTS\` means the files are SAP reports. The folder name and the SQL Server 2014 comment are the only evidence. The filenames and a commented export labelled `AdvPDA CRM` also fit a PDA extract. Both readings need confirmation.
- That `sysQuery` 23 updates and 24 inserts, because the log sentences say Updated and Inserted. The SQL itself is not in git.
- That this database is the same physical database as `snell-cronjob` and `snell-freestock`. The connection strings name the same server and catalog. Deployment is not proven by this repo alone.