# Technical documentation — edi_sage_middleware (TransactFlow Document Sync)

Evidence date: 2026-10-16.

Source: `git@bitbucket.org:overdrive/edi_sage_middleware.git`, `main` at `75823ee` (2026-04-21).

This document describes the tree that was inspected. It does not prescribe a redesign. Secrets found in source are named by location and are not copied.

## Stack

| Area | Maintained app (`BSBC Source Code`) | Older tree (`BSBC_FTP_Integration`) | ADE tree |
|---|---|---|---|
| Language | VB.NET | VB.NET | C# (decompiled style: `Microsoft.VisualBasic.CompilerServices`) |
| UI | WinForms | WinForms | WinForms, one log text box |
| Framework | .NET Framework 4.8 | .NET Framework 4.8 | .NET Framework 4.8, platform x86 |
| Project | `Sage2GS1Xml.vbproj`, output type `WinExe` | Same file name, assembly `Sage2GS1Xml` | `ADE PNP IDE V2.csproj`, assembly `ADE PNP IDE V2` |
| Startup | `TransactFlow_Document_Sync.frmMain` | `Sage2GS1Xml.frmMain` | `MyApplication.Main` |
| SQL client | `System.Data.SqlClient` | `System.Data.SqlClient` | `System.Data.SqlClient`. `Pervasive.Data.SqlClient` is imported and a DLL is in the folder. `Form1.cs` uses `SqlConnection`, not the Pervasive type |
| Option Strict | Off | Not re-checked line by line; project style matches the older Sage2GS1Xml layout | n/a |

IDE metadata: solution tools suggest Visual Studio. `.vs\...\v17\` folders are committed, so the trees were opened with Visual Studio 2022. `SccProjectName` and related properties are `SAK`.

Both Sage2GS1Xml projects share Visual Studio project GUID `{F9BCCF8B-FBD5-43B8-BC38-0999FEEEA881}`. Provenance: **Implemented** in both `vbproj` files.

## Application structure

```text
.gitignore
ADE Source Code/
  ADE PNP IDE V2.exe.config
  Pervasive.Data.SqlClient.dll
  Project/                    C# WinForms project, Form1.cs
BSBC Source Code/Sage2GS1Xml/
  Sage2GS1Xml.sln
  Sage2GS1Xml/                maintained VB project
    frmMain.vb                invoice and order workflows
    frmMain.Designer.vb
    Classes/SQLConnector.vb
    EnhancedSecureSettings.vb
    Modules/Globals.vb
    ApplicationEvents.vb      empty partial class
    SO_GETINVOICES.sql
    SO_GETINVOICEDETAILS.sql
    SilOve_INSERTORDER.sql    dbo.SO_INSERTORDER
    SUN_SAGE2XML.sql          not called
    My Project/               settings, manifest, assembly info
BSBC_FTP_Integration/Sage2GS1Xml/
  Sage2GS1Xml/                older invoice-only VB project
    frmMain.vb
    SUN_SAGE2XML.sql
```

`bin\`, `obj\`, and `.vs\` are listed in `.gitignore` and are also still tracked, including PDB files and a Pervasive DLL. Provenance: **Implemented**.

## Maintained form

Tabs from `frmMain.Designer.vb`:

| Tab | Controls that have handlers |
|---|---|
| Main | Date from, date to, Process Invoices, Process Orders, progress bar, `txtLog` |
| Settings / SQL Settings | Server name, invoice database, order database, SAVE |
| Settings / FTP Settings | Invoice server, username, password; order server, username, password; SAVE |
| Settings / General Settings | File location (label text is `File Lcation:`), Browse |
| Settings / Email Settings | SMTP host, port, username, password, from, to, Enable SSL, SAVE, TEST EMAIL |

Designer fields `edtXMLUsername` and `edtXMLPassword` exist. `frmMain.vb` never reads or saves them.

`Application.myapp`: main form `frmMain`, single instance false, visual styles on, save settings on exit. `app.manifest` requests `asInvoker`.

## Data access

`SQLConnector` runs one query on a `BackgroundWorker`. The UI waits with `Application.DoEvents` until `QueryExecuterIsIdle` and `QueryHasCompleted`.

`SetConnectionString(server, user, password, database)` builds:

`Server=<server>;Database=<database>;User ID=<compiled login>;Password=<compiled secret>;Min Pool size=50;Packet Size=32767;Connect Timeout=4096;`

The `user` and `password` parameters are not interpolated. Call sites pass the literal strings `.SQLUsername` and `.SQLPassword`. Three instances exist: `SQLHeader` and `SQLFooter` share the invoice database; `SQLOrder` uses the order database.

On failure the worker stores `LastException`, closes the connection, and does not show a message box. `HasConnection` still shows a message box. No caller of `HasConnection` was found in `frmMain.vb`.

The form does not deploy the SQL scripts. They are source artefacts an operator would run in SQL Server. `SO_GETINVOICES.sql` and `SO_GETINVOICEDETAILS.sql` contain `USE [BSBC]`. `SilOve_INSERTORDER.sql` creates a stub procedure if `dbo.SO_INSERTORDER` is missing, then `ALTER`s it. It expects the caller to have created `#SO_INSERTORDER_LINES` in the same session. The VB batch creates that temp table and then `EXEC`s the procedure as one script, which keeps the session.

Sage tables named by the scripts:

- Read for invoices: `_bvARTransactionsFull`, `_bvARAccountsFull`, `CLIENT`, `_bvSTTransactionsPivot`, `_bvStockFull`
- Read and write for orders: `Client`, `_etblDelAddress`, `InvNum`, `StkItem`, `_etblBarcodes`, `_etblUnits`, `_etblPriceListPrices`, `_btblInvoiceLines`

## Configuration

User-scoped `My.Settings` (`Settings.settings`). Values below are the committed defaults, not a live PC.

| Setting | Committed default | Used by the process |
|---|---|---|
| `SQLServer` | empty; UI fallback `localhost` | Yes |
| `SQLDatabase` | `BSBC_Test` | Yes, invoice database |
| `SQLOrderDatabase` | empty; code fallback `BSBC_Test` | Yes, order database |
| `SQLUsername`, `SQLPassword` | empty | No |
| `FTPServer` | `http://dedi1534.jnb2.host-h.net` | Yes, as the host string. The code prefixes `ftp://`, so this default would produce a URL that starts `ftp://http://` |
| `FTPUsername`, `FTPPassword` | empty / encrypted when saved | Yes |
| `OrderFTPServer`, `OrderFTPUsername`, `OrderFTPPassword` | empty | Yes |
| `XMLPath` | empty; UI fallback `C:\Temp\BSBC_Export` | Yes |
| `SMTPServer`, `SMTPPort`, `EmailUsername`, `EmailPassword`, `FromAddress` | empty | Yes, for mail |
| `ToAddress` | `renier@overdrive.co.za` | Yes |
| `EnableSSL` | true | Yes |
| `XMLUsername`, `XMLPassword` | empty | No |
| `AutoUpdateEnabled` | true | No |
| `UpdateCheckInterval` | `0` | No |
| `UpdateServerUrl` | `http://overdriveqa.co.za/BSBC_App_Update/` | No. ClickOnce install URL is the `https` form of a similar path |
| `LastUpdateCheck` | empty | No |

`App.config` repeats these settings under the usual .NET `userSettings` / `applicationSettings` sections. It was not copied here.

DPAPI entropy is the Unicode bytes of `BSBC_Invoice_Export_v2_Salt`. Scope is current user. A failed decrypt returns an empty string.

## ClickOnce

From `BSBC Source Code/.../Sage2GS1Xml.vbproj`:

| Property | Value |
|---|---|
| `InstallUrl` | `https://overdriveqa.co.za/BSBC_App_Update/` |
| `PublishUrl` | FTP path under `public_html/BSBC_App_Update/` on the same site. The FTP account embedded in that URL is not repeated here |
| `ProductName` / `PublisherName` / `SuiteName` | TransactFlow Document Sync / Silicon Overdrive / TransactFlow Document Sync |
| `ApplicationVersion` | `2.0.0.*` |
| `ApplicationRevision` | 17 |
| `MinimumRequiredVersion` | `1.0.0.59` |
| `UpdateEnabled` / `UpdateMode` | true / Foreground |
| `UpdateRequired` | false |
| `InstallFrom` | Web |
| `CreateDesktopShortcut` | true |
| `IsWebBootstrapper` | true |
| Assembly version | `1.0.0.0` in `AssemblyInfo.vb` |

A manifest certificate thumbprint is set on the project. The thumbprint is not copied here.

The older tree's publish metadata is different: product `Sage2XML`, publisher `Sungula`, install URL `http://sage2xml.sungulademo.co.za/`, update disabled, install from disk. Provenance: **Implemented** in `BSBC_FTP_Integration/.../Sage2GS1Xml.vbproj`.

## External systems

| System | How the code reaches it |
|---|---|
| SQL Server | `SqlConnection` as above. Database name is a setting. Login is compiled |
| Invoice FTP | `FtpWebRequest` method `UploadFile` |
| Order FTP | `ListDirectory`, `DownloadFile`, `DeleteFile` on `ftp://<OrderFTPServer>/` |
| SMTP | `SmtpClient`, TLS 1.2, 60-second timeout. `ServerCertificateValidationCallback` returns true for every certificate. A ping must succeed first |
| ClickOnce | `ApplicationDeployment.CheckForUpdate` and `Update` when `IsNetworkDeployed` |

No `appsettings.json`, Docker file, Terraform, or Bitbucket pipeline file is in the repository.

The OVER-102 discovery note listed `sonar-project.properties` and `sonarqube_scan.bat` at the root. They are not in `git ls-files` at `75823ee`.

## ADE technical notes

`Form1.cs` opens SQL Server catalog `ADE - Live` with login `sa` and a password literal, command type stored procedure, command text `SUN_INVOICE_spSel`. The password is not copied here.

Timer interval is `600000` milliseconds. The comment above it says 5 seconds.

Export path `c:\xprocure\<InvoiceNumber>.csv`. Skip when `c:\xprocure\uploaded\<InvoiceNumber>.csv` exists. Upload target is `ftp://xpliquor.dyndns.org/PNPInvoices/` with a compiled FTP username and password. Those secrets are not copied here.

`Pervasive.Data.SqlClient.dll` is in the ADE folder and is not the type `Form1` uses for the connection.

## Security notes (implemented posture)

These are observations from the tree, not a pentest and not a remediation plan.

- A SQL password is compiled into `BSBC Source Code/.../Classes/SQLConnector.vb`. The settings screen cannot rotate it.
- ADE `Form1.cs` compiles an `sa` password and an FTP password.
- `SUN_SAGE2XML.sql` has default parameters for a SOAP username and password. The procedure is not called by the form. The defaults are still in Git.
- The ClickOnce `PublishUrl` contains an FTP account.
- Invoice SQL is concatenated. Order text uses `EscapeSql` (single-quote doubling) for some strings. Numbers are parsed before they are appended.
- SMTP certificate checks are disabled by a callback that returns true.
- FTP and email passwords at rest use DPAPI for the current user. FTP usernames are stored in clear text.
- `bin` and `obj` outputs are in Git, including config copies under `bin\Debug`.
- `.gitignore` does not remove the ADE and FTP-integration trees that contain the older secrets.

## Development and tests

No test project, no PHPUnit/NUnit/MSTest file that is a real test suite, and no README. `TestFTPConnection` and the email TEST button are manual operator actions.

`BSBC_FTP_Integration` asks "Do you want to run in FTP test mode?" before the invoice process. The maintained form does not.

Build evidence in Git is the committed `bin` and `obj` artefacts, not a CI log. Whether a developer can build with Visual Studio 2022 against .NET Framework 4.8 was not executed during this documentation pass.

## Monitoring

The on-screen log and the HTML email are the only monitoring implemented. There is no health endpoint, event log write, or metrics client. `Globals.Log` swallows its own exceptions and returns false.

## Related Silicon Ark documents

| Directory | Relationship |
|---|---|
| `silicon_ark/sagegs1xml/` | Separate repository `sagegs1xml`. Also a BSBC invoice CSV exporter with accounts `5261` and `6960`, titled BSBC Invoice Export. Documented there from `master` at `fc601bb` (2019-11-20). The FTP-integration tree in this repo resembles that product and uses publisher Sungula in its own `vbproj`. Lineage is **Inferred** and **Requires Confirmation** |
| `silicon_ark/edi_shopfront/` | Separate product that exchanges EDI with Pick n Pay and Shoprite. This repository does not name it |

## Local folders the maintained app creates

| Path under the chosen root | Who writes it |
|---|---|
| `SHOPRITE\`, `PNP\` | Invoice CSV before upload or zip |
| `RETRY_UPLOAD\SHOPRITE\`, `RETRY_UPLOAD\PNP\` | Failed invoice uploads |
| `ORDERS\` | Downloaded `Order_*.csv` |
| `ORDERS\Retry\` | Order files that should be tried again |
| `ORDERS\Processed\` | Cleared at the end of Process Orders |
| `Backup\` | `Invoices_Uploaded_*.zip` and `Orders_Uploaded_*.zip` |