# PRD — sagegs1xml (BSBC Invoice Export)

Evidence date: 2026-10-04. Source: `git@bitbucket.org:overdrive/sagegs1xml.git`, `master` at `fc601bb` (2019-11-20).

There is no prior PRD in the repository or in Silicon Ark for this remote. Requirements below were recovered from the WinForms code, the SQL script, ClickOnce metadata, and the single commit. Provenance is marked on each item.

Secrets that are hardcoded (SQL password, FTP password, procedure credential defaults) are not copied into this document.

## Product purpose

An operator runs a Windows desktop program titled **BSBC Invoice Export**, chooses a from-date and a to-date, and starts **PROCESS**. The program reads invoices from a SQL Server database named `BSBC`, writes one pipe-delimited `.csv` per invoice into a Shoprite or PNP folder, uploads the file by FTP, and deletes the local file when the upload succeeds.

The project and ClickOnce names (Sage2GS1Xml, Sage2XML) and the script `SUN_SAGE2XML.sql` describe a GS1 XML invoice. That XML is not what PROCESS writes. Provenance: **Implemented**.

A written statement of why the business needs the export was not found. Provenance: **Unknown**.

## Users and roles

No login screen, role, or permission check exists in the application. Whoever can launch the executable and whose Windows user profile holds `My.Settings` can run it. SQL and FTP credentials used by PROCESS are compiled in; the settings screen does not supply them to those calls.

| Role | Evidence | Provenance |
|---|---|---|
| Desktop operator | Main tab: dates, PROCESS, status log, helpdesk line | **Implemented** |
| Person who configures the SQL server name, FTP fields, and output folder | Settings tab | **Implemented**. FTP fields and SQL username/password settings are not what PROCESS uses. See FR-004 and FR-005 |
| Helpdesk | Labels "Helpdesk: 0861 XPROCURE or 0861 977-628" on both tabs | **Implemented** as on-screen text. Whether that number still answers is **Unknown** |
| Named application user or role table | Not present | **Unknown** |

Publisher **Sungula** and window title **BSBC** are the only organisation names in the product metadata. FTP paths and the host `xpliquor.dyndns.org` are the only retailer-routing evidence. Which company employs the operator is **Inferred** as BSBC / the Sungula ClickOnce publisher, and **Requires Confirmation**.

## Main workflows

### WF-001 — Export invoices for a date range

Trigger: operator clicks PROCESS (`cmdProcess`).

Preconditions the code enforces:

- `My.Settings.XMLPath` is not empty. Otherwise a critical message tells the operator to set a directory "where XML archives and conversion can be done", and the click returns.
- `SQLHeader.QueryExecuterIsIdle` is true and `chkUploadOnly` is unchecked. Otherwise a message says a SQL request is still open.

Steps the code then takes:

1. Log "PROCESS STARTED" and a connecting line.
2. `EXEC [SUN_GETINVOICES]` with `@EFFECTIVE_FROM` and `@EFFECTIVE_TO` set to the two date pickers formatted `yyyy-MM-dd`, concatenated into the command text.
3. Pump `Application.DoEvents` until the header worker is idle and `QueryHasCompleted` is true.
4. Read `DataBuffer.Tables(0)`. Log the row count as "object/s".
5. For each row, take `Reference` (invoice number) and `AccountLink`.
6. If `AccountLink` is `5261`, ensure `{XMLPath}\SHOPRITE` exists and target `SHOPRITE\{Reference}.csv` plus FTP path `.../ShopriteInvoices/{Reference}.csv`. If `AccountLink` is `6960`, the same with `PNP` and `.../Invoices/{Reference}.csv`. Any other account leaves both paths empty.
7. `EXEC [SUN_GETINVOICEDETAILS] @Reference='{invoice number}'` on a second connector. Wait the same way. Take `Tables(0)`.
8. For each detail row, append columns 0 through 22, each followed by `|`, then a newline. After all detail rows, append one more `|`.
9. Open the target path for append and write that text.
10. FTP-upload the file. On success, log in green and delete the local file. On upload failure, log in red and leave the file. On delete failure, log in red.

Output: a remote CSV when upload succeeds; a local CSV when upload fails; a log on the Main tab.

Provenance: **Implemented** (`frmMain.vb` `cmdProcess_Click`, `GetInvoiceDetails`, `FTPFile`).

The procedure bodies are not in the repository, so the meaning of the 23 columns is **Unknown / Requires Confirmation**.

### WF-002 — Set the output folder

Trigger: Settings → General → button labelled `,,,` (`Button1`).

Behaviour: folder browser. If the operator picks a path, it is shown in `txtPath`, stored in `My.Settings.XMLPath`, and saved. Cancelling or an empty selection does not change the setting.

Provenance: **Implemented**.

### WF-003 — Save SQL server name

Trigger: Settings → SQL Settings → SAVE.

Behaviour: writes `edtSQLServer` to `My.Settings.SQLServer` and saves. Message "SQL settings saved." The connection used by PROCESS is built only in `frmMain_Load`, and only when the saved server name is non-empty at startup. Saving a new server name does not call `SetConnectionString` until the next launch.

Provenance: **Implemented**.

### WF-004 — Save FTP fields

Trigger: Settings → FTP Settings → SAVE.

Behaviour: writes server, username, and password text boxes to `My.Settings` and shows "FTP settings saved." `FTPFile` does not read those settings. It uses a compiled host, account, and password.

Provenance: **Implemented**.

### WF-005 — Startup

Trigger: form load.

Behaviour:

- Both date pickers set to today.
- If ClickOnce `ApplicationDeployment.CurrentDeployment` is available, the title becomes `BSBC Invoice Export V{major}.{minor}.{build}.{revision} (25 June 2018)`. Any exception is swallowed and the designer title `BSBC Invoice Export` remains.
- SQL server and FTP fields are filled from user settings. The output path text box is filled from `XMLPath`.
- If the saved SQL server is non-empty, both connectors are pointed at that server and database `BSBC`.
- Log "Settings loaded."

Provenance: **Implemented**.

`Application.myapp` names `MainForm` as `Form1`. `Application.Designer.vb` `OnCreateMainForm` assigns `frmMain`. The generated designer method is what runs. Provenance: **Implemented**.

## Functional requirements

### FR-001 — Date-ranged invoice export

The operator can export invoices whose header procedure returns them for the selected dates.

| | |
|---|---|
| Who | Desktop operator |
| Trigger | PROCESS |
| Inputs | Date from, date to, saved `XMLPath`, saved SQL server (applied at startup), rows from `SUN_GETINVOICES` and `SUN_GETINVOICEDETAILS` |
| Behaviour | WF-001 |
| Output | One `.csv` per returned invoice that has account `5261` or `6960`, uploaded by FTP, then deleted locally on success |
| Validation | Empty `XMLPath` blocks the run. No check that date-from is on or before date-to. No check that the header query returned a table before `Tables(0)` is read |
| Errors | Empty path: message box, no query. Worker SQL errors: message "SQL Query failed!" from `SQLConnector`, then the UI still reads `Tables(0)` after the wait. File-write exceptions are unhandled. FTP and delete failures are logged |
| Provenance | **Implemented** |

### FR-002 — Retailer folder and FTP route

Invoices are filed and uploaded by `AccountLink`.

| AccountLink | Comment in code | Local folder | FTP directory |
|---|---|---|---|
| `5261` | Checkers | `{XMLPath}\SHOPRITE` | `/ShopriteInvoices/` on `xpliquor.dyndns.org` |
| `6960` | PNP | `{XMLPath}\PNP` | `/Invoices/` on `xpliquor.dyndns.org` |
| anything else | none | path stays empty | path stays empty |

File name is `{Reference}.csv` in both places.

Provenance: **Implemented**. The retailer names are comments, not a lookup table. Whether those account codes are still Checkers and Pick n Pay is **Requires Confirmation**.

### FR-003 — Local file is a staging file

The settings prompt talks about "XML archives". After a successful FTP upload the program deletes the CSV. A failed upload leaves the file. A later successful run opens the same path with append (`OpenTextFileWriter(..., True)`), so a leftover file gains another copy of the rows before upload.

Provenance: **Implemented**. "Archive" in the message is not what the success path does.

### FR-004 — SQL connection target

PROCESS uses server name from settings (captured at startup) and database name `BSBC`. `SetConnectionString` accepts a user name and password and does not put them in the connection string. A fixed SQL login and password are compiled into `SQLConnector.vb`. The password is omitted here.

The settings screen has no SQL user or SQL password boxes. `SQLUsername` and `SQLPassword` exist in `Settings.settings` and are passed on load, then ignored.

Provenance: **Implemented**.

### FR-005 — FTP upload

Each CSV is uploaded with `FtpWebRequest` method `UploadFile` to a URL chosen in FR-002. Credentials on the request are compiled literals, not the Settings tab. The password is omitted here. The FTP account name is also embedded in the URL.

Success deletes the local file and logs "Upload Done..." and "File deleted..." in green. Failure logs "Upload Failed..." plus the exception message in red.

Provenance: **Implemented**.

### FR-006 — Output folder setting

The operator can choose a directory and the program persists it as `XMLPath`. PROCESS refuses to run when that value is empty.

Provenance: **Implemented**.

### FR-007 — On-screen status

`Log` writes `TimeValue(Now)` plus the message into `txtLog` in the requested colour. The time string is whatever VB produces when that value is concatenated. No format string is in the source. A progress bar `pbProgress` is on the Main tab and no statement assigns its value. A rich text box `XMLBUFFER` is on the Main tab with `Visible = False` and no statement writes it.

Provenance: **Implemented**.

### FR-008 — GS1 XML invoice document (script only)

`SUN_SAGE2XML` can be altered on a SQL Server that already has that procedure. For transactions with `TrCode = 'INV'`, `AccountLink = 6960`, and `TxDate` inside the supplied range, it returns a column `XML_DATA` containing a SOAP envelope and an EAN.UCC invoice (standard `EAN.UCC`, type `INVOICE`, version `2.5`, currency `ZAR`, country `ZA`).

The desktop program never executes `SUN_SAGE2XML`. Whether a database still has this procedure, and whether any other job runs it, is **Unknown / Requires Confirmation**.

Provenance: **Implemented** as script text. **Not** a behaviour of PROCESS.

### FR-009 — ClickOnce identity

The project is configured to install from the web, create a desktop shortcut, and check for an update before startup. Product `Sage2XML`, publisher `Sungula`, install URL `http://sage2xml.sungulademo.co.za/`, deployment provider `http://sage2xml.sungulademo.co.za/Sage2GS1Xml.application`, committed version `1.0.0.44`.

Provenance: **Implemented** as project and manifest content. Whether that URL still serves the application is **Unknown**.

## Integrations

| System | Direction | What is exchanged | Auth as implemented | Provenance |
|---|---|---|---|---|
| SQL Server, database `BSBC` | App reads | `SUN_GETINVOICES` (date from, date to) returns rows with at least `Reference` and `AccountLink`. `SUN_GETINVOICEDETAILS` (reference) returns rows with at least 23 columns | Fixed SQL login compiled in `SQLConnector.vb`. Password omitted. Server name is a user setting | **Implemented** call. Procedure results **Unknown** beyond the columns the VB code reads |
| FTP `xpliquor.dyndns.org` | App writes | CSV bytes to `/ShopriteInvoices/` or `/Invoices/` | Fixed FTP account and password compiled in `FTPFile`. Password omitted. Settings-tab FTP values are stored and not used | **Implemented** |
| ClickOnce host `sage2xml.sungulademo.co.za` | Installer reads | Application manifest and updates | No auth in the manifest (`publicKeyToken` is zeros, `UseApplicationTrust` false, `SignManifests` false) | **Implemented** as metadata |
| ClickOnce publish FTP `ftp.sungulademo.co.za/public_html/sage2xml/` | Build publishes | ClickOnce site | Not stored in the project file beyond the URL | **Implemented** as `PublishUrl` |
| SOAP service `http://pnpportal.co.za/ServiceEanUcc` | Named inside the XML the SQL script builds | GS1 invoice envelope | Username and password elements inside the SOAP header, from procedure parameters. Values omitted | **Implemented** as XML text. The WinForms app does not post it. Whether a consumer exists is **Unknown** |
| Sage / Pastel Evolution | Implied by view names in the SQL script and an unused `Pastel.Evolution` reference | Invoice, client, stock | Not a live SDK call | **Inferred** for the database. **Unknown** for the DLL |
| SonarQube | Listed on the onboarding issue as two root files | Not in `fc601bb` | — | **Unknown** |

Failure behaviour:

- SQL worker: message box, connection closed if open, `QueryDone` set true. PROCESS does not catch a missing table afterwards.
- FTP: logged, local file kept.
- ClickOnce version read: failure is ignored.
- GS1 script: no application failure path, because it is not called.

## Constraints

- Windows desktop, .NET Framework 4.5, single form, not a web application and not a service.
- Database name `BSBC` is fixed in the load handler.
- Only two account codes receive a path. Other codes still enter the file-write call with an empty path.
- CSV layout is positional (23 columns). There is no header row in the writer.
- Dates are passed as strings inside the SQL text, not as parameters.
- `Option Strict` is Off for the project (`Sage2GS1Xml.vbproj`).
- No automated test project is in the solution.
- GS1 content rules (GLN `6009900156101`, VAT rate `14`, document status `COPY`) apply to the SQL script, not to the CSV PROCESS writes. See business rules.

## Error and empty behaviour

| Situation | Behaviour | Provenance |
|---|---|---|
| `XMLPath` empty | Critical message, no query | **Implemented** |
| Upload Only checked | Same message as a busy query: "A SQL request is still open. please wait or restart the application." No upload runs | **Implemented** |
| Header worker busy | Same message | **Implemented** |
| Header query returns 0 rows | Log "Found 0 object/s", no files | **Implemented** |
| Header query throws | Message box from the worker; UI then dereferences `Tables(0)` | **Implemented** |
| Account link other than `5261` or `6960` | Empty path passed to the file writer. No guard | **Implemented** |
| Detail query returns a row with fewer than 23 columns | Index access throws. Unhandled on the UI thread | **Implemented** |
| FTP fails | Red log, file kept | **Implemented** |
| Delete after upload fails | Green "Upload Done...", red "File not deleted..." plus message | **Implemented** |
| ClickOnce API unavailable (run from `bin\Debug` rather than a deployed install) | Title stays "BSBC Invoice Export" | **Implemented** |

## Out of scope for this revision

- Defining the 23 CSV columns. The procedure is not in the repository.
- Treating `SUN_SAGE2XML` as the live export format. The executable does not call it.
- Contents of `sonar-project.properties` and `sonarqube_scan.bat`. They are not in this commit.
- A decision to retire, repair, or replace the export.

## Human confirmation

- Confirm the product purpose: retailer invoice drop (CSV over FTP), GS1 XML, or both in different environments.
- Confirm operators, and whether the helpdesk line is still the support path.
- Supply or point to `SUN_GETINVOICES` and `SUN_GETINVOICEDETAILS`.
- Confirm account `5261` and `6960`, the FTP host, and the ClickOnce host.
- Confirm whether credential rotation is required. Literals are in Git; they are not repeated here.