# Project plan — snell-crondaily

Evidence date: 2026-10-03.

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

Inspected revision: `master` at `60aeb26` (2023-11-17, Lloyd, "debug flag"). That commit is `origin/HEAD`. The only remote branch is `master`. Credential values are omitted.

The scan-host path `~/projects/sonar_scan/repos/snell-crondaily` was not on this machine. The tree below was read from that git revision.

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 CronDaily**. `AssemblyProduct` is **SNELL CronDaily**. `AssemblyCompany` is empty. `AssemblyCopyright` is "Copyright ©  2015". ClickOnce `ProductName` is **Superbowl Daily 2** and `PublisherName` is **Silicon Overdrive**.

## Product

A Windows Forms executable (`SNELL CronDaily.exe`) that copies two delimited text files into a working folder, loads them into SQL Server staging tables, then runs warehouse update and insert statements stored in the database.

On each run that is actually started:

1. Truncate `stgSuperbowlLog` and write a batch-start row.
2. If the Customer checkbox is checked, import `pda_crm.txt` into `stgCustomer`, mark `dwdCustomer` inactive, and run `sysQuery` IDs 23, 24, and 25, then rewrite Gauteng region labels on `dwdCustomer`.
3. If the Product checkbox is checked, import `pdaprod.txt` into `stgProduct`, mark `dwdProduct` inactive, and run `sysQuery` IDs 21 and 22.
4. Write a batch-completed row and email an HTML log to the addresses in `EmailRgbc`.

Provenance: **Implemented** in `SNELL_CronDaily/SNELL_CronDaily/frmMain.vb`, `App_Code/Processes/Customer.process.vb`, and `App_Code/Processes/Product.process.vb`.

The program does not create the source files. The release compile looks for them under `C:\SAPREPORTS\`. The DEBUG compile looks under `D:\Documents\Work Stuff\ESnell\`. Who writes those files, and which scheduler launches the exe, is **Unknown / Requires Confirmation**.

The same server and catalog names appear in Silicon Ark for `snell-cronjob` and `snell-freestock`: release `ESFSPHP02\SQLEXPRESS`, catalog `snell_2015`. This repository's release connection string uses those names. Whether one production database serves all three applications is the reading of those strings. Confirm if CronDaily points at a different instance.

## Branch reality

| Line | Tip | Date | What it is |
|---|---|---|---|
| `master` (default; `origin/HEAD`) | `60aeb26` | 2023-11-17 | Only branch. Five commits from 2020-11-17 to 2023-11-17 |

History:

| Commit | Date | Author | Message |
|---|---|---|---|
| `191e67e` | 2020-11-17 | Abdurahmaan Ismail | Initial commit |
| `28b4054` | 2020-11-17 | maanie | Initial commit |
| `86821ee` | 2023-11-17 | Lloyd | add snell admin, add more logging |
| `234cedd` | 2023-11-17 | Lloyd | improvements |
| `60aeb26` | 2023-11-17 | Lloyd | debug flag |

Inline comments in `frmMain.vb` date product work from 2015-10-21 (project start) through 2018-08-30 (SMTP change, labelled v1.1.2). Those comments are older than the Bitbucket history. `AssemblyVersion` is `1.0.1.1`. The form builds the footer as `SNELL CronDaily Version 1.{Build}.{Revision} - 17 Nov 2023`, which displays **1.1.1** from that assembly version. `AssemblyFileVersion` is `1.0.0.0`. ClickOnce `ApplicationVersion` is `0.0.0.0`.

What `86821ee` changed is logging around the customer and product warehouse steps (catch blocks now write an error or warning line). The commit message says "add snell admin". No new admin screen or role was added in that diff.

`234cedd` moved `EmailLog()` from before the log truncate to after the batch, set `VersionDate` to 17 Nov 2023, and replaced the log recipient list.

`60aeb26` did not change the `Debug` value. It added the comment `IMPORTANT change to false in prod` next to `<value>True</value>`.

**Discrepancy:** the 2026-09-28 onboarding scan listed `sonar-project.properties` and `sonarqube_scan.bat` at the repository root. Neither file is in git history. `60aeb26` is `origin/HEAD`. Provenance of those two files: **Unknown**. They may have been untracked files on the scan host.

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

- Load a customer extract (`pda_crm.txt`) into staging and apply the database warehouse statements for customers.
- Load a product extract (`pdaprod.txt`) into staging and apply the database warehouse statements for products.
- Force every existing `dwdCustomer` or `dwdProduct` row inactive before those statements run.
- Collapse region labels that start with "Gauteng " to the single value `Gauteng` on `dwdCustomer`.
- Clear promotional-account flags on `dwdCustomer`, then let `sysQuery` 25 set them again.
- Email the batch log to the configured addresses.
- Leave the form open when application setting `Debug` is true, so an operator can press Run or Email Log. Close after the run when that setting is false.

## Scope

### In scope of `master`

- One Visual Studio 2017 solution, one VB.NET WinForms project, target .NET Framework 4.6.1.
- Customer and product import processes, shared file/staging helpers, SQL helper, HTML log mail.
- ClickOnce publish metadata and stock Team Foundation Server 11 build-process XAML templates.
- Checked-in `bin\Debug` and `obj\Debug` outputs, including a stale `SNELL CronDaily.exe.config`.

### Out of scope of this repository

- The SQL text of `sysQuery` rows 21, 22, 23, 24, 25, and 43. The application loads `memQuery` from the database.
- Table definitions, indexes, and the producer of `pda_crm.txt` and `pdaprod.txt`.
- A scheduler, installer instructions, or a pipeline.
- Login, roles, and a web UI. Staff free-stock screens live in `snell-freestock`. The free-stock mailer and SAP export live in `snell-cronjob`.
- Methods that exist in `ProjectFunctions.vb` but are not called from `frmMain.Run`: un-invoiced requisition mail, fiscal period top-up, daily dimension counts, Monday warehouse backups.

### Commented or unused inside the project

- Export list. The only export construction (`AdvPDA CRM` / `pda_crm.csv` / `sysQuery` 35) is commented out, and `lstExport` stays empty.
- `SetDeletes` and `DeleteDW` call sites in both processes.
- `chkPartialImport`. The checkbox is on the form and defaults to checked. Nothing reads it.
- `EmailBfbe`. The setting exists. No method reads it.
- `EmailUnIinvoicedRequisitions`, `CheckPeriods`, `LogDWD`, `BackupMintPromos`.

## System architecture

```text
Operator or scheduler
        |
        v
SNELL CronDaily.exe  (WinForms, single instance)
        |
        +-- copy pda_crm.txt / pdaprod.txt
        |     from C:\SAPREPORTS\          (Release compile)
        |     or  D:\Documents\Work Stuff\ESnell\   (DEBUG compile)
        |     into {exe}\Imports\
        |
        +-- SQL Server catalog snell_2015
        |     ESFSPHP02\SQLEXPRESS   integrated security   (Release compile)
        |     SQL-SERVER             SQL login             (DEBUG compile)
        |     tables: stgCustomer, stgProduct, dwdCustomer, dwdProduct,
        |             stgSuperbowlLog, sysQuery
        |
        +-- archive under {exe}\Imported\
        |
        +-- SMTP HTML log
              Release: 172.16.2.22, from administrator@esnell.co.za
              DEBUG:   smtp.office365.com:587, from smtp@overdrive.co.za
```

Warehouse insert and update SQL is not in the repository. Comments in `ProcessDW` call the targets "dwf". The statements that this source does contain update `dwdCustomer` and `dwdProduct` (`tblDwd`). Which tables `sysQuery` 21–25 write is **Unknown / Requires Confirmation**.

## Major components

| Component | Role |
|---|---|
| `frmMain` | Startup, process list, Run, Email Log button |
| `Customer_Process` | `pda_crm.txt` → `stgCustomer` → warehouse queries 23–25 → Gauteng rewrite |
| `Product_Process` | `pdaprod.txt` → `stgProduct` → warehouse queries 21–22 |
| `Process` | Download, validate, truncate staging, delimited import, archive, duplicate log, set inactive |
| `Export_Process` / `dataExport` | Pipe-delimited export. Not started by `Run` |
| `db` | Shared SQL Server connection and `doQuery` / `getRow` |
| `ProjectFunctions` | `WriteLog`, `EmailLog`, plus the uncalled helpers |
| `SystemFunctions` | Date and colour helpers used by the log mail |
| `Duodecimal` | Fiscal month arithmetic. Only `CheckPeriods` uses it, and `CheckPeriods` is not called |

## Milestones visible in git

| When | Evidence |
|---|---|
| 2015-10-21 | Comment: project start, v0.0.0 |
| 2015-11-24 | Comment: Gauteng Main Market renamed to Gauteng |
| 2016-05-25 | Comment: email log added, labelled v1.1.1 |
| 2016-05-31 | `VersionDate` before the 2023 edit |
| 2018-08-30 | Comment: SMTP settings changed, labelled v1.1.2 |
| 2020-11-17 | First commits on Bitbucket |
| 2023-11-17 | Warehouse error logging, log mailed after the batch, recipient list replaced, production-debug comment added |

No milestone after 2023-11-17 is in this repository.

## Implementation plan (as evidenced, not as a proposal)

No forward plan is written in the repository. The latest commit leaves application setting `Debug` at `True` and comments that production should set it to false. A build from the current `App.config` therefore opens the form and waits. It does not run and exit on startup.

The checked-in `bin\Debug\SNELL CronDaily.exe.config` was not updated by `234cedd`. It still lists `EmailRgbc` as `pj@overdrive.co.za; maanie@overdrive.co.za` and `EmailBfbe` as `pj@overdrive.co.za`. `App.config` and `Settings.settings` list `EmailRgbc` as `lloyd@overdrive.co.za; maanie@overdrive.co.za; dimphoz@esnell.co.za` and `EmailBfbe` as `maanie@overdrive.co.za`. Running the committed Debug output without a rebuild uses the older recipient list.

## Known dependencies

- .NET Framework 4.6.1, Windows Forms, `System.Data.SqlClient`. No NuGet `packages.config`.
- SQL Server catalog `snell_2015` with `stgCustomer`, `stgProduct`, `dwdCustomer`, `dwdProduct`, `stgSuperbowlLog`, and `sysQuery` rows 21, 22, 23, 24, 25, and 43.
- A readable drop folder containing `pda_crm.txt` and `pdaprod.txt`.
- Writable folders `{exe}\Imports\`, `{exe}\Imported\`, and `{exe}\Exports\`. The code assigns those paths and does not create the directories.
- SMTP reachability for the log mail.
- Visual Studio 2017 solution format (`VisualStudioVersion = 15.0.26228.43`). A VS 2019 `.suo` is also committed (`.vs\...\v16`).
- Historical TFS binding: `http://tfs01:8080/tfs/vs_projects_2018` in the solution file. Whether that server is still used is **Unknown**.

ClickOnce metadata points `PublishUrl` at `C:\Users\pieter\Desktop\snell_cd\`. `UpdateEnabled` is false. `SignManifests` is false. `ManifestKeyFile` is `Superbowl Daily 2_TemporaryKey.pfx`. That PFX is not in the tree.

## Discrepancies

| Topic | What the repo shows |
|---|---|
| Product name | Window and assembly: SNELL CronDaily. ClickOnce product: Superbowl Daily 2 |
| Version | Footer uses assembly build/revision (displays 1.1.1) plus date 17 Nov 2023. File version 1.0.0.0. ClickOnce version 0.0.0.0. Comments still say v1.1.2 (2018) and, in shared helpers, v2.x |
| `Debug` setting | Value in git is `True`. Comment says change it to false in production. Startup auto-run happens only when the value is false |
| Log recipients | `App.config` was updated on 2023-11-17. The tracked Debug `exe.config` was not |
| Warehouse target | Comments say dwf. In-repo SQL updates `dwd*` |
| `tblDwf` | Both compile symbols set `dwf` + entity name. A comment says to switch live to `tmp` + entity name. That switch is commented out |
| Sonar files | Named by the onboarding scan. Absent from git |
| Framework | Project targets v4.6.1. ClickOnce bootstrapper entries are .NET 4.5 and .NET 3.5 SP1 |
| Commit message "add snell admin" | Diff only adds warehouse catch logging |

## Human confirmation still required

- Which machine runs the exe, on what schedule, and whether the installed `exe.config` has `Debug` set to false.
- Whether the drop folder is still `C:\SAPREPORTS\` and which system writes `pda_crm.txt` and `pdaprod.txt`.
- The live text of `sysQuery` 21, 22, 23, 24, 25, and 43, and whether those statements update `dwd*` or `dwf*`.
- Whether `stgCustomer`, `stgProduct`, `dwdCustomer`, `dwdProduct`, and `stgSuperbowlLog` still exist on `snell_2015` with the columns this code inserts.
- Whether setting every dimension row `blnActive = 0` before the warehouse SQL is still the intended rule.
- Whether the Gauteng label rewrite and the promotional-account reset are still approved.
- Whether the 2023 recipient list (Lloyd, Maanie, dimphoz@esnell.co.za) is who should receive the log, or whether production still uses the older `pj@` list baked into `bin\Debug`.
- Whether the uncalled free-stock, period, and backup methods should stay, or are leftovers from Superbowl Daily.
- Whether secrets committed in `App.config`, `Settings.settings`, `ProjectFunctions.vb`, and `bin\Debug\SNELL CronDaily.exe.config` are still valid and should be rotated. Values are not copied into this document.