# Project plan — snell-cronjob

Evidence date: 2026-10-03.

Source repository: `git@bitbucket.org:overdrive/snell-cronjob.git`.

Inspected revision: `master` at `f092e9b` (2026-06-26, maanie, "FSR SAP integration"). The scan-host path `~/projects/sonar_scan/repos/snell-cronjob` was not on this machine. The tree below was read from that git revision. Credential values are omitted.

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

There is no README and no prior Silicon Ark set for this repository. The window title is **Snell Cronjob v1.0**. `AssemblyCompany` is **ESnell & Co**. `AssemblyProduct` is **Snell Cronjob**.

## Product

A Windows Forms executable that runs a batch of free-stock requisition (FSR) work against SQL Server database `snell_2015`, then exits.

On each run it:

1. Copies `Free Stock Requisition.csv` from an Advantage / AdvM download folder, loads valid rows into `fsrRequisition` and `fsrItem`, and queues a new-requisition job.
2. Optionally posts manager-approved requisitions to SAP through the ESnell SOAP operation `ZRfcEdiIdocInputOrders`.
3. During a midday window, re-mails pending requisitions that are still unapproved.
4. Drains `sysCronJob` rows of type `FSR_NewRequisition` and `FSR_RequisitionApproved`: builds a PDF from `Requisition.rdlc` and emails it.

Provenance: **Implemented** in `Snell Cronjob/frmCronjob.vb`, `FreeStockRequisition_AM_Process.vb`, and `FsrOrder_ESnell_Process.vb`.

The staff application that creates, submits, and approves requisitions is not in this repository. Silicon Ark documents that application under `snell-freestock/`. That set still says the `sysCronJob` consumer is unknown. This repository is that consumer for the two FSR job types. A second PHP tree is documented under `snell-freestock-5.6/`. How the two PHP trees relate in production is **Unknown / Requires Confirmation**.

## Branch reality

| Line | Tip | Date | What it is |
|---|---|---|---|
| `master` (default; `origin/HEAD`) | `f092e9b` | 2026-06-26 | Only branch. 7 commits from 2020-09-09 to 2026-06-26 |

History:

| Commit | Date | Author | Message |
|---|---|---|---|
| `4877dec` | 2020-09-09 | Jaco Brits | Initial commit |
| `5fe885a` | 2020-09-09 | Alex Kedian | Initial Commit of Cron Job Code |
| `c3e892e` | 2020-09-09 | Alex Kedian | EmailManager subject, body, CC split, hour threshold |
| `e1d8785` | 2021-11-24 | Lloyd | added email - lerato |
| `6341e5b` | 2024-04-17 | Lloyd | email update |
| `b626d84` | 2024-10-07 | Dewald Stander | changing from email address to noreply@esnell.co.za |
| `f092e9b` | 2026-06-26 | maanie | FSR SAP integration |

Inline comments in `frmCronjob.vb` date product work from 2013 (Superbowl-era `ArchiveProduct`) through `v1s.1.5` on 2026-06-12 (FSR SAP export). Those comments are older than the Bitbucket history. The form title remains `Snell Cronjob v1.0`. `AssemblyVersion` is `1.0.1.1` and `AssemblyFileVersion` is `1.0.0.0`. They were not moved with the SAP commit.

**Discrepancy:** the 2026-09-28 onboarding scan recorded last commit `b626d84` (2024-10-07) and listed `sonar-project.properties` and `sonarqube_scan.bat` at the repository root. Neither file is in git history. `f092e9b` (2026-06-26) is on `master` and was already committed before that scan. Provenance of the two sonar files: **Unknown** — they may have been untracked files on a scan host that had not fetched `f092e9b`.

## Objectives (inferred from the implementation, not from a written plan)

- Turn an AdvM CSV of free-stock requests into pending pink slips and line items in `snell_2015`.
- Tell the responsible manager, by email plus PDF, that a new requisition is waiting.
- Chase an alternate or executive manager when a pending requisition is still open after the coded day threshold, once inside a midday window.
- Tell the orders mailbox, by email plus PDF, when a requisition has been approved.
- When SAP export is switched on and credentials are present, send approved requisitions that have not yet been sent into SAP as an ORDERS IDoc, and mail a failure notice when that send cannot proceed.

## Scope

### In scope of `master`

1. VB.NET Windows Forms project `Snell Cronjob/Snell Cronjob.vbproj`, solution `Snell Cronjob.sln`, target .NET Framework 4.8.
2. AdvM CSV import (`FreeStockRequisition_AM_Process`).
3. Queue worker for `FSR_NewRequisition` and `FSR_RequisitionApproved`, including ReportViewer PDF generation.
4. Midday alternate-manager resend.
5. Stale-file check and email (`Process.EmailManager`). Whether the time comparison can ever succeed is documented in the business rules.
6. ESnell SOAP export (`FsrOrder_ESnell_Process`, `IdocGenerator`) and the SQL script that adds SAP columns.
7. A manual PowerShell SOAP probe, `Snell Cronjob/Scripts/SendEsnellFsrTest.ps1`.
8. Committed build output under `bin/` and `obj/`, including `bin/Release/Reports/RDLC/Requisition.rdlc`.

### Out of scope / not in this repository

- The Freestock Requisition web UI, login, and approval buttons (`snell-freestock`).
- Whatever writes `sysCronJob` type `FSR_RequisitionApproved`. This executable only reads that type.
- A Windows Task Scheduler definition, service, or installer script. How often the exe is started is **Unknown**.
- The live text of `sysQuery` rows 38, 39, 40, 41, and 42. The application loads them from the database. Comments in `FreeStockRequisition_AM_Process.vb` sketch queries 41 and 42 only.
- Automated tests, a pipeline, and Sonar configuration in git.
- A statement of which host runs the exe, or whether `Scripts/AlterFsrRequisition_SapColumns.sql` has been applied to `snell_2015`.

## System architecture

```text
Scheduler or operator (not defined in this repo)
        |
        |  starts "Snell Cronjob.exe" (single instance)
        v
frmCronjob.Run
        |
        |-- 1. FreeStockRequisition_AM_Process
        |        copy CSV from download folder
        |        -> {exe}\Imports\
        |        -> stgFreeStockRequisition
        |        -> fsrRequestType / fsrRequisition / fsrItem
        |        -> sysCronJob (FSR_NewRequisition, Status New)
        |
        |-- 2. FsrOrder_ESnell_Process   (only if FsrSapExportEnabled = True)
        |        fsrRequisition Status Approved, blnSentToSap = 0
        |        dwdCustomer.strSAPCode, dwdProduct, mstUser.strEmail
        |        -> HTTP POST SOAP  ZRfcEdiIdocInputOrders
        |        -> ESnell / SAP
        |
        |-- 3. Midday chase (12:00–12:19)
        |        pending requisitions -> PDF -> SMTP
        |
        |-- 4. sysCronJob Status = New
                 FSR_NewRequisition      -> manager or executive + PDF
                 FSR_RequisitionApproved -> orders mailboxes + PDF
                 any other Type          -> no handler

SQL Server  ESFSPHP02\SQLEXPRESS  catalog snell_2015   (release connection)
SMTP        mail.esnell.co.za, SSL off                 (release)
Logs        INSERT into stgSuperbowlLog
```

Release builds auto-run and close when application setting `Debug` is false. Debug builds leave the form open and wait for the Run button. `Application.myapp` sets `SingleInstance` true and `MainForm` to `frmCronjob`.

## Major components

| Component | Role |
|---|---|
| `frmCronjob` | Startup form, preprocess, queue loop, both FSR emails, 40-minute stop |
| `Process` | CSV download, stale-file mail, staging truncate, delimited import, file delete |
| `FreeStockRequisition_AM_Process` | AdvM column checks and insert into FSR tables |
| `FsrOrder_ESnell_Process` | Select approved unsent requisitions and POST SOAP |
| `IdocGenerator` | ORDERS IDoc XML. Comment: adapted from superbowl_cronjob Repsly/PDA |
| `ProjectFunctions.createPDF` | LocalReport render of `Requisition.rdlc` |
| `ProjectFunctions.WriteLog` | Insert into `stgSuperbowlLog` |
| `db` (`db.generic.vb`) | Shared `SqlConnection` and string-built SQL |
| `dsRequisition` / `dsItem` | Typed datasets for the PDF |
| `SuperbowlDataSet` | Generated dataset compiled into the project. No non-designer file constructs it |

## Milestones visible in comments and git

These are change notes, not a signed plan.

| Marker | Date | What the source says |
|---|---|---|
| v1.0.2 | 2013-11-05 | Comment: `ArchiveProduct` job added. No `Case` for that type remains |
| v1.0.3 | 2014-09 to 2015-01 | FSR import, new-requisition mail, approved mail, alt-manager resend; expense type no longer mandatory |
| v1s.0.3 | 2015-09-28 | Comment: "Project start SNELL" |
| v1s.0.3 | 2016-02-04 | Alt-manager resend changed from a 3-day comment to `DATEDIFF(day) > 1` |
| v1s.1.1 | 2016-05-31 | Import-issue logging |
| v1s.1.2 | 2018-08-30 | Debug SMTP pointed at Office 365 |
| v1s.1.3 | 2020-09-01 | SMTP host change; BCC removed from the two FSR mails |
| v1s.1.4 | 2021-10-20 | Third Snell CC added, later commented out in the approved mail |
| v1s.1.5 | 2026-06-12 | FSR SAP export. Git commit `f092e9b` is 2026-06-26 |

## Implementation plan

No forward plan is written in the repository. The SAP commit is the latest change. Its script says to run `AlterFsrRequisition_SapColumns.sql` against `snell_2015` before deploying `FsrOrder_ESnell_Process`. Whether that has happened, and whether a deployed `exe.config` sets `FsrSapExportEnabled` to True, is **Unknown / Requires Confirmation**. The copy of settings in git leaves export disabled and the endpoint blank.

## Known dependencies

- .NET Framework 4.8 (`Snell Cronjob.vbproj`). `app.config` `supportedRuntime` is `v4.0` sku `.NETFramework,Version=v4.8`. Committed `obj/` assembly attributes still say v4.6.2. `OldToolsVersion` is 3.5.
- NuGet: `Microsoft.ReportViewer.Common` and `Microsoft.ReportViewer.WebForms` 10.0.40219.1 (`packages.config`). The `packages/` folder is not in git. Hint paths point at `..\packages\`.
- SQL Server catalog `snell_2015` on `ESFSPHP02\SQLEXPRESS` for a release build (integrated security).
- SMTP host `mail.esnell.co.za` for a release build. Username and password settings in git are empty.
- ESnell HTTP SOAP endpoint, only when export is enabled. The endpoint value is not in git.
- AdvM (or an FTP drop) placing `Free Stock Requisition.csv` in `C:\am_ftpdownload\downloads\` on the release machine. Comment cites "RE: snell: Paths Requested".
- Tables and the view `vieRequsitionManager` created outside this repository, except the four SAP columns in the SQL script.
- `snell-freestock` (or another writer) inserting `FSR_RequisitionApproved` jobs and setting `strStatus` to `Approved`.

## Current project status

**Implemented and on `master`.** Last change 2026-06-26. No open branch, no pipeline, no test project.

Release behaviour depends on configuration that is not fully in git: the Windows schedule, the live SMTP password (the in-repo value is empty; `unsalt` only rewrites the letters `ASP` to `php`), the SAP on/off switch on the deployed exe, and whether the SAP columns exist.

ClickOnce metadata points `PublishUrl` at `C:\Users\pieter\Desktop\snell_cj\`. `UpdateEnabled` is false. `SignManifests` is false. A file named `Superbowl Cronjob_TemporaryKey.pfx` is committed. That path and the Superbowl names are historical leftovers, not evidence of a current publish process.

## Human confirmation

- Which machine runs the exe, on what schedule, and as which Windows user (user-scoped settings follow that user).
- Whether production `exe.config` or a user.config turns SAP export on and supplies ESnell credentials.
- Whether `AlterFsrRequisition_SapColumns.sql` has been applied.
- Whether `stgSuperbowlLog`, `vieRequsitionManager`, and `sysQuery` 38–42 exist in `snell_2015` and match the comments.
- Whether the scan-host sonar files should be treated as part of the product. They are not in git.
- The R10 000 boundary disagrees with `snell-freestock` at exactly 10000. See business rule BR-008.