# Technical documentation — sagegs1xml (Sage2GS1Xml)

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

Only `master` exists on the remote. Credential literals are named by location and are not copied.

## Stack

| Item | Value | Evidence |
|---|---|---|
| Language | VB.NET | `Sage2GS1Xml/*.vb` |
| UI | Windows Forms | `MyType` WindowsForms, `OutputType` WinExe |
| Runtime | .NET Framework 4.5, startup `v4.0` | `Sage2GS1Xml.vbproj`, `App.config` |
| IDE metadata | Solution format Visual Studio 2013 (`VisualStudioVersion` 12.0.40629.0). `vbproj.user` ToolsVersion 15.0. `.vs/Sage2GS1Xml/v15/` | `Sage2GS1Xml.sln`, `Sage2GS1Xml.vbproj.user` |
| Compiler options | `Option Explicit` On, `Option Strict` Off, `Option Infer` On, `Option Compare` Binary | `vbproj` |
| SQL client | `System.Data.SqlClient` | `SQLConnector.vb` |
| Distribution metadata | ClickOnce (`IsWebBootstrapper` true, `System.Deployment` reference) | `vbproj` |
| Database script | T-SQL `ALTER PROCEDURE` | `SUN_SAGE2XML.sql` |

Assembly version and file version in `AssemblyInfo.vb` are `1.0.0.0`. ClickOnce `ApplicationVersion` is `1.0.0.*` with `ApplicationRevision` 44. The committed manifest identity is `1.0.0.44`. Provenance: **Implemented**.

`AssemblyTitle` and `AssemblyProduct` are `Sage2GS1Xml`. `AssemblyCompany` and `AssemblyDescription` are empty. Copyright is "Copyright ©  2015". COM visible is false. GUID `ec0c4fa6-8327-4127-9071-c38531e465c1`.

## Repository layout

```text
Sage2GS1Xml.sln
Sage2GS1Xml.vssscc
desktop.ini
Sage2GS1Xml/
  Sage2GS1Xml.vbproj
  Sage2GS1Xml.vbproj.user
  Sage2GS1Xml.vbproj.vspscc
  App.config
  frmMain.vb
  frmMain.Designer.vb
  frmMain.resx
  SUN_SAGE2XML.sql
  Sage2GS1Xml_TemporaryKey.pfx
  XP.ico
  Classes/SQLConnector.vb
  Modules/Globals.vb
  My Project/          Application, AssemblyInfo, Resources, Settings
  Resources/bg12.jpg
  bin/Debug/           committed exe, pdb, vshost, manifest, sql copy
  obj/Debug/           committed build intermediates
.vs/Sage2GS1Xml/v15/   committed .suo
```

There is no `.gitignore`. Build outputs, user options, `desktop.ini` files, and a ClickOnce temporary key file are in the commit. Provenance: **Implemented**.

`bg12.jpg` is a project resource. `Resources.resx` embeds it. No form statement in `frmMain.Designer.vb` assigns it as `BackgroundImage` (the Main tab sets `BackgroundImageLayout` only). Provenance: **Implemented**.

## Application structure

Startup object is `Sage2GS1Xml.My.MyApplication`. `OnCreateMainForm` sets `MainForm` to `frmMain`. Single-instance is false. Shutdown is after the main form closes. Visual styles are enabled. Settings save on exit.

`Application.myapp` still says `<MainForm>Form1</MainForm>`. The generated `OnCreateMainForm` is the path that runs.

| Type | File | Responsibility |
|---|---|---|
| `frmMain` | `frmMain.vb` | Load, settings buttons, PROCESS, folder picker, detail query, FTP |
| `SQLConnector` | `Classes/SQLConnector.vb` | Connection string, background `SqlDataAdapter.Fill`, dataset buffer |
| `Globals` | `Modules/Globals.vb` | `Log` writes to `frmMain.txtLog` |

`SQLConnector` constructs two `BackgroundWorker` fields. Only `QueryExecutor` has a `DoWork` handler. `ProcExecutor` is never started. `HasConnection` is never called from the form.

PROCESS uses two instances: `SQLHeader` for the invoice list, `SQLFooter` for lines. Each `RunQuery` clears tables, starts the worker, and the UI thread loops `Application.DoEvents` until `QueryExecuterIsIdle` and `QueryHasCompleted`. The worker opens the connection, fills a `DataSet`, and closes it. On exception it shows a message box, closes the connection if open, and sets the completed flag.

Connection string shape (password omitted):

```text
Server={server argument};Database={database argument};User ID=pnp;Password=<literal omitted>;Min Pool size=50;Packet Size=32767;Connect Timeout=4096;
```

The form passes database `BSBC`. The user-name and password arguments are not interpolated. Provenance: **Implemented**.

`Minimum Pool size=50` and `Connect Timeout=4096` (seconds) are in that string. Provenance: **Implemented**.

## Process sequence

1. `frmMain_Load` sets dates, optional ClickOnce title, copies settings onto the form, and calls `SetConnectionString` when `SQLServer` is non-empty.
2. `cmdProcess_Click` rejects an empty `XMLPath`.
3. Header query: `EXEC [SUN_GETINVOICES] @EFFECTIVE_FROM='yyyy-MM-dd', @EFFECTIVE_TO='yyyy-MM-dd'`.
4. For each row, route on `AccountLink`, then `EXEC [SUN_GETINVOICEDETAILS] @Reference='...'`.
5. Build the pipe-delimited body and append it with `FileSystem.OpenTextFileWriter(path, True)`.
6. `FTPFile` reads all bytes and `FtpWebRequest` uploads them (`WebRequestMethods.Ftp.UploadFile`).
7. On success, `File.Delete`.

URLs built in code:

| AccountLink | URL |
|---|---|
| `5261` | `ftp://BLUESKY@xpliquor.dyndns.org/ShopriteInvoices/{Reference}.csv` |
| `6960` | `ftp://BLUESKY@xpliquor.dyndns.org/Invoices/{Reference}.csv` |

`NetworkCredential` is constructed with account `BLUESKY` and a password literal in `FTPFile`. The password is omitted here. Settings-tab FTP values are not used.

Provenance: **Implemented**.

## SQL script `SUN_SAGE2XML`

Path: `Sage2GS1Xml/SUN_SAGE2XML.sql` (duplicate under `bin/Debug` because `CopyToOutputDirectory` is Always).

The script is `ALTER PROCEDURE [SUN_SAGE2XML]`. It does not contain `CREATE`, and it does not define `SUN_GETINVOICES` or `SUN_GETINVOICEDETAILS`.

Objects named by the script:

| Object | How it is used |
|---|---|
| `_bvARTransactionsFull` | Invoice header alias `T`. Columns touched include `Reference`, `TxDate`, `TrCode`, `AccountLink`, `LinkAccCode`, `ExtOrderNum`, `Outstanding`, `Tax_Amount` |
| `_bvARAccountsFull` | Join `F.DCLink = T.LinkAccCode` |
| `CLIENT` | Join `C.DCLink = F.DCLink`. `Post5`, `Tax_Number` |
| `_bvSTTransactionsPivot` | Lines alias `P`, filtered by `P.Reference = T.Reference` |
| `STKITEM` | Join `S.StockLink = P.StockLink`. `Description_1` |

`Bar_Code`, `Code`, and `ActualSalesValue` are unqualified. Provenance: **Implemented** text. Binding **Requires Confirmation** on the server.

The result column alias is `XML_DATA`. The value is one SOAP envelope per selected invoice: security header, UN/CEFACT standard business document header, EAN.UCC message, `pay:invoice` version 2.5. Seller GLN literal `6009900156101`. Line tax uses `* 1.14` and `<rate>14</rate>`. See `business-rules.md` BR-016 through BR-022.

The form never executes this procedure. Provenance: **Implemented**.

## Settings and configuration

`App.config` user settings, all strings, default empty:

`XMLUsername`, `XMLPassword`, `SQLServer`, `SQLUsername`, `SQLPassword`, `FTPServer`, `FTPUsername`, `FTPPassword`, `XMLPath`.

| Setting | Written by | Read by PROCESS or upload |
|---|---|---|
| `XMLPath` | Folder picker | Yes. Empty value blocks PROCESS |
| `SQLServer` | SQL SAVE | Yes, but only from the value loaded at startup |
| `FTPServer`, `FTPUsername`, `FTPPassword` | FTP SAVE | No. Upload uses literals |
| `SQLUsername`, `SQLPassword` | No UI writes them. Load passes them into `SetConnectionString`, which ignores them | No |
| `XMLUsername`, `XMLPassword` | Only `cmdSaveXML_Click`, which is not wired and whose controls are not constructed | No |

Provenance: **Implemented**.

## ClickOnce and deployment

From `Sage2GS1Xml.vbproj` and `bin/Debug/Sage2GS1Xml.application`:

| Setting | Value |
|---|---|
| Product / suite / publisher | Sage2XML / Sage2XML / Sungula |
| Install | Web, `Install` true, desktop shortcut true |
| Install URL | `http://sage2xml.sungulademo.co.za/` |
| Deployment provider | `http://sage2xml.sungulademo.co.za/Sage2GS1Xml.application` |
| Publish URL | `ftp://ftp.sungulademo.co.za/public_html/sage2xml/` |
| Update | Enabled, mode Foreground, `beforeApplicationStartup` in the manifest. `UpdatePeriodically` false. `UpdateRequired` false. Interval 7 days is stored and not the active mode |
| Version | `1.0.0.44` in the committed application manifest |
| Framework | 4.5 full, runtime `4.0.30319` |
| Signing | `SignManifests` false. `ManifestKeyFile` is `Sage2GS1Xml_TemporaryKey.pfx`. `UseApplicationTrust` false. Manifest `publicKeyToken` is `0000000000000000` |
| Bootstrapper | .NET Framework 4.5 install true. .NET 3.5 client and 3.5 SP1 install false |
| Publish wizard | `PublishWizardCompleted` true. `CreateWebPageOnPublish` true, page `publish.htm` (that page is not in Git) |

Provenance: **Implemented** as configuration. Reachability of the host on 2026-10-04 was not tested. **Unknown** whether the site is live.

Icon: `XP.ico`.

## Source control history beside Git

`Sage2GS1Xml.sln` records `SccTeamFoundationServer` = `https://sungula.visualstudio.com/defaultcollection` and a Team Foundation Version Control global section. Project SCC properties in the `vbproj` are `SAK`. `Sage2GS1Xml.vssscc` sets `ENLISTMENT_CHOICE` = `NEVER`.

Provenance: **Implemented** as leftover binding data. It is not evidence that this Bitbucket remote still syncs with that collection.

## Security notes present in the tree

- SQL password literal in `SQLConnector.SetConnectionString`. Login id `pnp`. Password omitted from this document.
- FTP password literal in `frmMain.FTPFile`, account `BLUESKY`. Password omitted. The account name is also in the FTP URL.
- Procedure default `@USERNAME` / `@PASSWORD` and `ISNULL` fallbacks in `SUN_SAGE2XML.sql`. Values omitted.
- `Sage2GS1Xml_TemporaryKey.pfx` is committed. `SignManifests` is false, so the key is not what signs the committed manifest. The file is still private key material in Git. This document does not extract it.
- `edtFTPPassword` uses a password character on screen. The value is stored in user-scoped settings in clear configuration if the operator clicks FTP SAVE. PROCESS does not read it.
- Invoice reference and dates are concatenated into SQL command text.
- FTP uses `ftp://` (not FTPS) as written in the URL. Provenance: **Implemented**.
- No tests cover these paths.

Rotation and removal are a human decision. The values are already in commit `fc601bb`.

## Development and testing

No test project, no README, no build script beyond the VB project, no Bitbucket pipeline file. Opening the solution expects Visual Studio with VB.NET and .NET Framework 4.5 targeting. The `Pastel.Evolution` reference points at `..\..\..\..\PastelSDK\Pastel.Evolution.dll` relative to the project file, which is outside the repository. No source file uses that assembly. A build may still fail if MSBuild cannot resolve the hint path. That outcome was not executed here. Provenance: **Inferred** from the reference; the build was not run.

`bin\Debug\Sage2GS1Xml.exe` and related outputs are already in Git, so a previously successful Debug build was imported. Provenance: **Implemented**.

## Background work, queues, storage

| Topic | What the tree shows |
|---|---|
| Background work | `BackgroundWorker` inside `SQLConnector` for SQL fills. No queue, no Windows service, no scheduled task |
| UI wait | `Application.DoEvents` until the worker finishes |
| Storage | Local folder chosen by the operator, then FTP. Success deletes the local CSV |
| Database | SQL Server, catalog `BSBC`, procedures named above |
| Monitoring | None in the repo. The onboarding issue named Sonar files that are not in `fc601bb` |
| Logging | In-memory rich text box only. Not written to a file |

## What this document does not establish

- The bodies and result shapes of `SUN_GETINVOICES` and `SUN_GETINVOICEDETAILS`.
- Whether `SUN_SAGE2XML` is installed on any server.
- Whether `sage2xml.sungulademo.co.za` or `xpliquor.dyndns.org` still answer.
- Whether the Pastel SDK DLL is required to compile.
- Contents of the Sonar files listed at task creation.
- A production operations owner.

## Human confirmation

- Confirm the live host, database, and branch before anyone treats `fc601bb` as production.
- Confirm whether the unused `Pastel.Evolution` reference can be dropped, or whether a DLL must be restored to build.
- Confirm rotation of the SQL login, the FTP account, and the procedure credential defaults, and removal of `Sage2GS1Xml_TemporaryKey.pfx` from the tree if it is no longer required.
- Point to the missing procedure definitions if the CSV column contract is needed.