# PRD — snell-cronjob

Evidence date: 2026-10-03.

Source: `git@bitbucket.org:overdrive/snell-cronjob.git`, `master` at `f092e9b` (2026-06-26).

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 SQL script, the PowerShell probe, and git history. Provenance is labelled. Credential values are omitted.

Related product: `snell-freestock` is the web application that staff use to draft, submit, and approve the same requisitions. This executable is the batch side. `snell-freestock` documentation still calls the queue consumer unknown.

## Product purpose

**Implemented.** On startup in a release build, `frmCronjob` imports an AdvM free-stock CSV, optionally exports approved requisitions to SAP, chases unapproved requisitions during a midday window, then emails PDFs for queued new and approved requisitions.

**Documented** only as inline version comments and the form title `Snell Cronjob v1.0`. There is no README.

**Unknown / Requires Confirmation:** who schedules the process, whether SAP export is enabled on the machine that runs it, and whether operators still expect the stale-file email.

## Users and interactions

This program has no login screen. `SystemFunctions.LogginUser` looks up `mstUser` by domain login for a "Forecast" group and writes `mstUsageLog`. Nothing in the cron path calls it.

| Actor | What they do in this system | Status |
|---|---|---|
| Process host | Starts `Snell Cronjob.exe`. Release builds with setting `Debug` = false run once and close. Debug builds show the form until someone clicks Run | **Implemented**. The scheduler is **Unknown** |
| AdvM / file drop | Leaves `Free Stock Requisition.csv` in the download directory | **Inferred** from the path comment and the filename. The producer is not in this repo |
| Sales manager, alternate manager, executive manager | Receive mail. Addresses come from `vieRequsitionManager` | **Implemented** as recipients. They do not use this UI |
| Requester (`mstUser` on the requisition) | CC on the new-requisition mail (release only) and on the approved mail | **Implemented** |
| Orders / clerk mailboxes | `SnellEmailAddress` is the To line of the approved mail. `SnellEmailAddress2` is an extra CC | **Implemented** from config values in git |
| SAP / ESnell | Receives the SOAP order when export is enabled | **Implemented** in code. Default settings leave it off |
| Developer | Debug build, Run button, hardcoded debug mailbox | **Implemented** |

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

| Setting | Value in git | Used for |
|---|---|---|
| `StaleFileNotifyManagerEmail` | aimmieg@esnell.co.za | Stale CSV notice, To |
| `StaleFileNotifyCCEmail` | justink@esnell.co.za | Stale CSV notice, CC |
| `SnellEmailAddress` | orders@esnell.co.za, Nondumisom@esnell.co.za, lloyd@overdrive.co.za | Approved requisition, To |
| `SnellEmailAddress2` | espda@esnell.co.za in `app.config`. Designer default is sherezades@esnell.co.za | Approved requisition, CC |
| `Emails` | maanie@overdrive.co.za | No sender in the cron path reads `Emails` |
| `SapFailureNotifyEmail` | Designer default maanie@overdrive.co.za. Not present in `app.config` | SAP failure mail |
| Approved-mail BCC | Literal `maanie@overdrive.co.za` in `frmCronjob.vb` | Always added on the approved mail, including release |
| Debug To | Literal `exiledbandit@gmail.com` | Replaces the live To when the project is compiled DEBUG |

`SnellEmailAddress3` is mentioned in a 2021 comment and the CC line is commented out. The setting does not exist.

## Systems this product talks to

1. **SQL Server.** Release: `ESFSPHP02\SQLEXPRESS`, catalog `snell_2015`, integrated security, timeout 60. Debug: `SQL-SERVER`, catalog `snell_2015`, SQL authentication. A reference string still names catalog `Superbowl`. Passwords are omitted here.
2. **SMTP.** Release: `mail.esnell.co.za`, SSL disabled, username and password from settings. Both are empty in git. The password is passed through `unsalt`, which replaces `ASP` with `php`. Debug compiles: `smtp.office365.com` port 587, SSL on, with a username and password written in source. The password is omitted here.
3. **ESnell SOAP.** HTTP POST, content type `application/soap+xml; charset=utf-8`, header `SOAPAction` from `ESnellSoapAction` (default `ZRfcEdiIdocInputOrders`), HTTP Basic authentication, timeout 60 seconds. Endpoint, username, and password default to empty.
4. **Local disk.** Release download directory `C:\am_ftpdownload\downloads\`. Debug download directory `D:\Documents\Work Stuff\ESnell\`. Working copy `{exe directory}\Imports\`. PDFs `{exe directory}\Reports\PDF\`. Report definition `{exe directory}\Reports\RDLC\Requisition.rdlc`.
5. **AdvM.** Named in comments and in the class `FreeStockRequisition_AM_Process`. No API client. The interface is the CSV file.

SonarQube and Bitbucket Pipelines are not in this repository.

## Major workflows

### WF-1 — Start and stop

Trigger: process start. `frmCronjob_Load` sets `dirImports` to `{BaseDirectory}\Imports\`.

| Build | Behaviour |
|---|---|
| DEBUG compile | Download directory is the `D:\Documents\...` path. The form stays open. Run is manual |
| Release compile and `My.Settings.Debug` is false | Download directory is `C:\am_ftpdownload\downloads\`. `Run` executes, then the form closes |
| Release compile and `My.Settings.Debug` is true | Form stays open and waits for Run |

`SingleInstance` is true. A second copy does not start a second run. Provenance: **Implemented** (`Application.myapp`).

`Run` stops pulling further queue rows when elapsed time reaches 40 minutes (`40 * 60` seconds, stored as ticks). Rows already left at status `New` wait for the next launch. After the loop, any row whose status contains `Processing` is set back to `New`.

### WF-2 — Import the AdvM CSV

Trigger: every `Run`, before the queue. Class `FreeStockRequisition_AM_Process`, name `Free Stock Requisition`, file `Free Stock Requisition.csv`.

1. If `{download dir}\Free Stock Requisition.csv` exists, copy it over `{exe}\Imports\Free Stock Requisition.csv`. A missing source does not log a warning. That log line is commented out.
2. If the Imports copy exists, run the stale-file check (WF-3), then accept the file only when the extension is `.csv`.
3. Count lines and subtract 1 (treated as the header).
4. `TRUNCATE TABLE stgFreeStockRequisition`.
5. Parse comma-separated fields, quotes allowed. Skip the header row. Validate and `INSERT` each data row (see business rules). A bad row is logged and skipped. Other rows continue.
6. Delete the Imports file. The method is named `ArchiveFile`. The copy-to-archive and move-to-archive lines are commented out. Only `File.Delete` remains.
7. Run database query `sysQuery` ID 41 (comment: insert request types that are not already in `fsrRequestType`, last user `SB CRON: AM Import`).
8. Run `sysQuery` ID 40. For each returned row whose `strPinkSlipNumber` is blank: insert `fsrRequisition` with status `Pending`, set pink slip `ES` plus `MAX(RequisitionID)` zero-padded to 7 digits, insert `fsrItem` by joining `stgFreeStockRequisition.SKU` to `dwdProduct.strProdCode`, insert `sysCronJob` type `FSR_NewRequisition`, status `New`, param `RequisitionID={id}`.
9. Run `sysQuery` ID 42. Each row is logged as `Record not imported` with UniqueID, RepCode, and Date.

The live SQL for IDs 40, 41, and 42 is not in git. Steps 8 and 9 follow the column names the VB reads and the comments above those calls. If ID 40 does not return `strPinkSlipNumber`, the import throws and the error is logged.

Output: new `Pending` requisitions and queue rows, plus log lines. The web application's Draft → Submit path is not used for these rows.

### WF-3 — Stale CSV notice

Trigger: inside the file check, when the Imports file exists.

The code compares `Date.Today` (midnight today) with the literals `#07:45:00 AM#` and `#08:00:00 AM#`. In VB a time-only literal is 1 January year 0001. Today is always after 07:45 in year 0001 and never on or before 08:00 in year 0001, so the branch does not run.

Settings `StaleFileNotifyStartTime` and `StaleFileNotifyEndTime` hold those same clock times and are not read.

If the branch did run, and the file's last write is at least `StaleFileNotifyPeriodHours` hours old (git value `1`), it would send a plain-text body marked `IsBodyHtml = true` from `FreeStock Requisition <noreply@esnell.co.za>` with subject `ATTENTION: Free Stock Requisition File`.

Provenance: **Implemented** comparison. The conclusion that the mail never sends is **Inferred** from VB date-literal rules. **Requires Confirmation** against a running release if a different compiler behaviour is in use.

### WF-4 — Chase a pending requisition

Trigger: `PreProcess`, only when `TimeOfDay` is after 12:00:00 and before 12:19:00.

Select requisitions where `StrStatus = 'Pending'`, `DATEDIFF(day, dtDate, getDate()) > 1`, and `dtEmailSentToAltManager` is null. Join user and request type.

For each row, read `vieRequsitionManager`. Write `refManagerID`, `refAlternateManagerID`, and `refExecutiveManagerID` back onto `fsrRequisition`.

If `TotalRequisitionValue` is less than or equal to `SalesManagerMaxValue` (git value `10000`), the recipient is the alternate manager. Otherwise the recipient is the executive manager. An empty address skips the send.

Body template: `sysQuery` ID 38, with tokens `[ToName]`, `[RequestedBy]`, `[Date]`, `[PinkSlipNumber]`, `[BillToAccount]`, `[ShipToAccount]`, `[RequestType]`, `[DetailOfRequest]`, `[ContactName]`, `[ContactNo]`, `[Delivery]`, `[RequisitionID]`.

PDF: `SubmittedRequisition_{pink slip}.pdf` via `createPDF` with job type `FSR_NewRequisition`. Subject: `New Freestock Requisition {pink slip}`. CC: the requester's `strEmail`. On success, set `dtEmailSentToAltManager` to `getdate()`.

A 2014 comment says "not approved within 3 days". A 2016 comment says "datediff > 48h". The SQL uses a calendar-day difference greater than 1. Provenance: **Implemented** SQL. The comments disagree with it.

### WF-5 — Email a new requisition from the queue

Trigger: `sysCronJob.Type = FSR_NewRequisition` and `Status = New`. `snell-freestock` inserts this type on Submit. WF-2 inserts it for an AdvM row.

Param format: `key=value` pairs separated by `|`. The code requires `RequisitionID`.

1. Set the job to `Processing`.
2. Read `vieRequsitionManager` and copy the three manager ids onto the requisition.
3. If total `<= SalesManagerMaxValue`, mail the primary manager. Otherwise mail the executive manager.
4. No manager email: job status `Warning`, result starts `No manager email address`.
5. No view row: job status `Warning`, result starts `No manager found`.
6. Body is `sysQuery` 38, including `[TotalValue]` rounded to 2 decimal places, midpoint away from zero.
7. PDF `SubmittedRequisition_{pink slip}.pdf`. Subject `New Freestock Requisition {pink slip}`. Release builds CC the requester. Debug builds do not add that CC.
8. Success: status `Processed`, result `Successfully Emailed [...]`.
9. `SmtpException` while sending: status `Processed_MailError`. `SmtpException` before the send flag: status `Error`.
10. Any other exception is logged. The status update in that catch is not present, so the row stays `Processing` until the end-of-run reset to `New`.

### WF-6 — Email an approved requisition

Trigger: `sysCronJob.Type = FSR_RequisitionApproved` and `Status = New`. This executable does not insert that type. `snell-freestock` inserts it on Approve.

Same manager lookup and threshold as WF-5, but the threshold only chooses the manager name stored on the requisition. The To address is `SnellEmailAddress`, not the manager.

Body: `sysQuery` ID 39. `[ToName]` is replaced with the literal `Snell Clerk`.

PDF: `ApprovedRequisition_{pink slip}.pdf`. Subject: `Approved Freestock Requisition {pink slip}`.

CC: requester `strEmail`, then `SnellEmailAddress2`. BCC: `maanie@overdrive.co.za`.

Status values match WF-5 (`Processed`, `Warning`, `Processed_MailError`, `Error`).

### WF-7 — Export an approved requisition to SAP

Trigger: every `Run`, after the CSV import and before the queue. Class `FsrOrder_ESnell_Process`.

1. If `FsrSapExportEnabled` is not the string `True` (case-insensitive), log and return. Git default is `False`, and `app.config` does not contain the key.
2. If endpoint, username, or password is empty, log an error and return. Git defaults are empty.
3. Select `fsrRequisition` joined to `mstUser` where `strStatus = 'Approved'`, `blnSentToSap` is null or 0, and `intSapRetryCount` is below `SapMaxRetryCount` (default 5 if missing or below 1).
4. For each requisition, load lines from `fsrItem` joined to `dwdProduct`. Missing lines, all-zero quantities, or a missing `dwdCustomer.strSAPCode` for bill-to or ship-to: store `strSapLastError` (500 characters), do not increment the retry count, send the failure mail, leave the row eligible next run.
5. Build SOAP XML (`IdocGenerator.BuildFsrSoapXml`) and POST it.
6. HTTP success and a body that does not contain a SOAP fault marker: set `blnSentToSap = 1`, `dtSentToSap = GETDATE()`, clear `strSapLastError`.
7. Fault, non-success, or exception: increment `intSapRetryCount`, store the error, and mail when the count has reached the maximum. An exception message is prefixed `ESnell SOAP request failed`.

The failure mail says the PDF email to the orders team was still sent. That sentence is fixed text. In the same `Run`, this export finishes before WF-6 reads the queue, so a requisition approved since the previous launch has not yet had its PDF sent when the failure mail goes out. Provenance: **Implemented** order of calls. **Requires Confirmation** that operators understand the sentence.

`strStatus` is not changed to `Completed` or anything else. `snell-freestock` also never writes `Completed`.

### WF-8 — Manual SOAP probe

`Scripts/SendEsnellFsrTest.ps1` posts a sample ORDERS envelope (pink slip `ES0000999`, date `20260612`, one case line) or an XML file passed with `-XmlFilePath`. Credentials come from parameters or `ESnellUser` / `ESnellPass`. `-TestConnection` resolves DNS and opens a 5-second TCP connection, then exits without posting. The script tells the operator to run it from a host whose IP is allowed. It is not called by the exe.

## Functional requirements

### FR-001 — One-shot release run

The release executable performs one `Run` and closes when setting `Debug` is false.

Evidence: `frmCronjob_Load`. Implementation: **Implemented**. Confidence: High.

### FR-002 — AdvM file name and place

The import looks for `Free Stock Requisition.csv` in `C:\am_ftpdownload\downloads\` (release) or `D:\Documents\Work Stuff\ESnell\` (debug), then works on a copy under `Imports\`.

Evidence: `PreProcess`, `DownloadFile`. Implementation: **Implemented**. Confidence: High.

### FR-003 — CSV acceptance

Only `.csv` is valid. The parser is comma-delimited with quoted fields. The first record is the header and is not inserted. Row errors do not stop the file.

Evidence: `Process.CheckLocalFile`, `Process.ImportLocalFile`, `sqlInsertStaging`. Implementation: **Implemented**. Confidence: High.

### FR-004 — Staging then master

A valid file truncates `stgFreeStockRequisition`, inserts staged rows, deletes the local file, then promotes rows through `sysQuery` 40 and 41.

Evidence: `FreeStockRequisition_AM_Process.Run`. Implementation: **Implemented**. The exact promote SQL is **Unknown** beyond the VB that consumes it. Confidence: Medium.

### FR-005 — Pending pink slip from AdvM

A promoted row with a blank pink slip is inserted as `strStatus = 'Pending'` and given `ES` plus a 7-digit id. A `FSR_NewRequisition` job is inserted in the same path.

Evidence: `sqlInsertFSR` and the update that follows it. The same `ES` plus 7 digits is what `snell-freestock` documents for `AssignPinkSlipNumber`. Implementation: **Implemented**. Confidence: High for the format. Confidence: Medium that `sysQuery` 40 only returns rows that should become new requisitions.

### FR-006 — New-requisition email

A `New` job of type `FSR_NewRequisition` emails the manager or the executive, attaches `SubmittedRequisition_{pink slip}.pdf`, and records `Processed` or a warning/error status.

Evidence: `frmCronjob.Run` case `FSR_NewRequisition`. Implementation: **Implemented**. Confidence: High. The HTML body text is **Unknown** (`sysQuery` 38).

### FR-007 — Approved-requisition email

A `New` job of type `FSR_RequisitionApproved` emails `SnellEmailAddress`, CCs the requester and `SnellEmailAddress2`, BCCs maanie@overdrive.co.za, and attaches `ApprovedRequisition_{pink slip}.pdf`.

Evidence: case `FSR_RequisitionApproved`. Implementation: **Implemented**. Confidence: High. Body text is **Unknown** (`sysQuery` 39).

### FR-008 — Midday chase

Between 12:00 and 12:19 the program emails the alternate or the executive for pending requisitions older than one calendar day that have not yet been chased, then stamps `dtEmailSentToAltManager`.

Evidence: `PreProcess`. Implementation: **Implemented**. Confidence: High.

### FR-009 — PDF from the requisition report

`createPDF` renders `Reports\RDLC\Requisition.rdlc` with parameter `RequisitionID`, datasets `dsRequisition` and `dsItem`, to `Reports\PDF\`. The submitted name is used for new-requisition jobs. Any other status string, including the approved job, uses the approved file name.

Evidence: `ProjectFunctions.createPDF`. The rdlc is committed at `Snell Cronjob/bin/Release/Reports/RDLC/Requisition.rdlc` and embedded from that path in the vbproj. Implementation: **Implemented**. Confidence: High that the code does this. Confidence: Medium that a published folder always contains the rdlc beside the exe.

### FR-010 — SAP export switch

Export runs only when `FsrSapExportEnabled` equals `True` and endpoint, username, and password are non-empty.

Evidence: `FsrOrder_ESnell_Process.Run`. Implementation: **Implemented**. The git defaults keep it off. Whether production overrides them is **Unknown**. Confidence: High for the code, Low for production state.

### FR-011 — SAP payload and retry

A successful post sets `blnSentToSap` and `dtSentToSap`. Failures store `strSapLastError`. Transport failures count toward `SapMaxRetryCount`. Data failures do not, and they notify every run while the row stays unsent.

Evidence: `ProcessOneRequisition`, `HandleFailure`. Implementation: **Implemented**. Confidence: High.

### FR-012 — Logging

`WriteLog` inserts `strStamp`, `dtLog`, `strType`, `strProcess`, `strAction`, `txtLog`, `blnEmail` into `stgSuperbowlLog` unless a caller passes another table name. Callers in this solution use the default.

Evidence: `ProjectFunctions.WriteLog`. Implementation: **Implemented**. Whether the table exists in `snell_2015` is **Unknown / Requires Confirmation**.

### FR-013 — Queue types with no handler

`Run` loads every `New` row. The `Select Case` handles only the two FSR types. Other types are counted toward the 40-minute loop and left at `New`.

Evidence: `frmCronjob.Run`. A 2013 comment names `ArchiveProduct`. That case is absent. Implementation: **Implemented**. Confidence: High.

## Integrations

| System | Direction | Trigger | Data | Auth in git | Failure |
|---|---|---|---|---|---|
| AdvM file drop | Inbound file | Every run | CSV of free-stock lines | None | Missing file: import skipped. Bad rows logged |
| SQL Server `snell_2015` | Both | Every step | Requisitions, items, users, products, customers, queue, templates, log | Release: Windows integrated. Debug: SQL login (secret omitted) | Exceptions logged. Several catches continue |
| SMTP `mail.esnell.co.za` | Outbound | Mails above | HTML body, PDF attachment | Username/password settings; git values empty; password passed through `unsalt` | Job `Processed_MailError` or `Error`; SAP failure mail logs and swallows |
| ESnell `ZRfcEdiIdocInputOrders` | Outbound HTTP | Approved, unsent, under retry cap, export enabled | SOAP ORDERS IDoc | HTTP Basic. Secrets not in git | Retry and/or failure mail. See WF-7 |
| `snell-freestock` | Indirect, via database | That app inserts queue rows and sets `Approved` | `sysCronJob`, `fsrRequisition` | Not in this repo | This exe does not call the web app |

## Constraints

- .NET Framework 4.8 Windows Forms, `Option Strict Off`, VB WinExe.
- One instance.
- Release run is synchronous on the UI thread. `bgwJob` is constructed and never started. `bgwTimer` starts only from `Button1_Click_1`, and the designer has no Button1.
- SQL is concatenated. `db.CleanString` doubles single quotes and turns double quotes into single quotes. Numeric fragments such as quantities are not quoted.
- ReportViewer 10 assemblies are required at build time from a `packages` folder that is not committed.
- User-scoped settings (`dirImports`, stale-file addresses, `SnellEmailAddress`, `SnellEmailAddress2`) can be overridden by a `user.config` for the Windows user that runs the exe. `SaveMySettingsOnExit` is true.
- SAP application settings exist on `Settings.Designer.vb` with `DefaultSettingValue` and are absent from `Settings.settings` and `app.config`. Regenerating the designer from `Settings.settings` would drop them.

## Edge cases

| Situation | Behaviour in code |
|---|---|
| Header-only CSV | Truncate staging, import zero data rows, delete the file, promote queries still run |
| Both quantities `0` | Row rejected as invalid cast, logged as warning |
| Expense type blank | Allowed (mandatory check removed, comment 2015-01-13) |
| SKU not on `dwdProduct` | Header can still be inserted; item insert joins the product table and may insert nothing; a `FSR_NewRequisition` job is still queued |
| Two imports choose `MAX(RequisitionID)` | Pink slip is not taken from `SCOPE_IDENTITY`. Concurrent web saves can collide. **Inferred** risk |
| Job type other than the two FSR types | Left `New` forever |
| Non-SMTP exception on a new-requisition job | Reset to `New` at end of run and retried next launch |
| SAP data error | Emailed every run until someone fixes the data or the row is no longer `Approved` and unsent |
| SOAP HTTP 200 whose body is not a fault and not a business acknowledgement | Treated as success |
| `createPDF` cannot find the rdlc or cannot attach the PDF | Exception path of the calling mail. The PDF method itself has an empty catch around delete |
| Stale-file CC list | The loop splits on comma and then constructs `MailAddress` from the whole string, not from each piece |
| Empty `SnellEmailAddress` | Approved mail is not sent (`strTo.Length > 0` fails after the debug substitution). Status handling then depends on whether an exception was thrown |

## Requirements that need a person

- Confirm the production schedule, host, and Windows account.
- Confirm whether SAP export is on, and whether the column script has been applied.
- Confirm the live text of `sysQuery` 38, 39, 40, 41, and 42.
- Confirm whether the stale-file mail is still a required behaviour. The comparison as written does not enter the send.
- Confirm the intended recipient when the line total is exactly 10000. This exe uses `<= 10000` for the manager or alternate. `snell-freestock` uses `< 10000` for the manager band and `>= 10000` for the executive.
- Confirm that `Completed` is intentionally never set by this worker.