# Project plan — edi_sage_middleware (TransactFlow Document Sync)

Evidence date: 2026-10-16.

Source repository: `git@bitbucket.org:overdrive/edi_sage_middleware.git`.

Inspected revision: `main` at `75823ee` (2026-04-21, "Update to stored proc", author Renier van Rooyen). `origin/HEAD` points at `origin/main`. No other remote branch was present.

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

Credential literals in `SQLConnector.vb`, `Form1.cs`, and `SUN_SAGE2XML.sql` are not copied into this document.

The OVER-102 discovery note (2026-09-28) recorded last commit `5b9772a` (2026-01-19, "BSBC:: OrdersIn Stored procedure updates") and listed `sonar-project.properties` and `sonarqube_scan.bat` at the repository root. On 2026-10-16, `main` is six commits ahead of `5b9772a`, and those two Sonar files are not in the Git tree. Provenance of the discovery note: **Documented** on the onboarding issue. Provenance of the later commits: **Implemented**.

## Product

The Git slug is **edi_sage_middleware**. The maintained Windows program is branded **TransactFlow Document Sync** (assembly title, product, and ClickOnce product name) and **TransactFlow Document Processing** (form text when ClickOnce version text is not applied). The assembly company and ClickOnce publisher are **Silicon Overdrive**. Copyright text is "Silicon Overdrive Copyright © 2025".

Provenance: **Implemented** (`BSBC Source Code/Sage2GS1Xml/Sage2GS1Xml/My Project/AssemblyInfo.vb`, `Sage2GS1Xml.vbproj`, `frmMain.Designer.vb`).

What that program does: an operator on a Windows desktop exports Sage invoices for a date range as pipe-delimited CSV and uploads them by FTP, and downloads order CSV files from a second FTP server and inserts them into Sage as back orders. Shoprite/Checkers and Pick n Pay are the only invoice account codes the export handles. Provenance: **Implemented** (`frmMain.vb`, `SO_GETINVOICES.sql`, `SO_GETINVOICEDETAILS.sql`, `SilOve_INSERTORDER.sql`).

A written product goal, customer contract, or statement of who operates the PC was not found. The folder and default database names use **BSBC**. What BSBC expands to is **Unknown / Requires Confirmation**.

The solution folder is still named `Sage2GS1Xml`. A SQL script `SUN_SAGE2XML.sql` builds a GS1 / EAN.UCC SOAP invoice. The current process buttons do not execute that script. Provenance: **Implemented** as a file, and **Implemented** as unused by `frmMain.vb`.

## What else is in the repository

`.gitignore` says other source directories are excluded because work continues only in `BSBC Source Code`, and it lists `BSBC_FTP_Integration/` and `ADE Source Code/`. Both directories are still tracked. Git does not stop tracking a path that was committed before the ignore rule. Provenance: **Documented** intent in `.gitignore`, **Implemented** presence in `git ls-files`.

| Tree | What it is | Status in this repo |
|---|---|---|
| `BSBC Source Code/Sage2GS1Xml` | VB.NET WinForms app **TransactFlow Document Sync**, .NET Framework 4.8, invoice export and order import | The tree `.gitignore` says is the one being worked on. Last commit `75823ee` changes only `SilOve_INSERTORDER.sql` in this tree |
| `BSBC_FTP_Integration/Sage2GS1Xml` | Older VB.NET WinForms app titled **BSBC Invoice Export**, invoice CSV and FTP only | Tracked, and named by `.gitignore` as excluded from ongoing work. Same Visual Studio project GUID as the maintained project: `{F9BCCF8B-FBD5-43B8-BC38-0999FEEEA881}` |
| `ADE Source Code` | C# WinForms app titled **ADE - PNP Invoice Export - v7**, .NET Framework 4.8, x86 | Tracked, and named by `.gitignore` as excluded. Added in commit `43775ff` (2025-04-23) |

Silicon Ark already documents a separate repository, `sagegs1xml`, whose window title is also **BSBC Invoice Export** and whose invoice routing uses the same account codes `5261` and `6960`. Whether `BSBC_FTP_Integration` was copied from that repository is **Inferred** from the shared title, FTP host pattern, and procedure names `SUN_GETINVOICES` / `SUN_GETINVOICEDETAILS`. It **Requires Confirmation**. This plan does not treat `sagegs1xml` as the source of truth for `edi_sage_middleware`.

## Branch reality

Only `main` exists. History on `main` runs from `4824d57` (2025-04-23, "Initial commit") to `75823ee` (2026-04-21). Authors in `git shortlog`: Renier van Rooyen (17), maanie (3), Wessels De Wet (2), Jaco Brits (1).

There is no README, test project, pipeline file, or milestone file.

## Scope in the maintained application

In scope of what `BSBC Source Code` actually runs:

- Main tab: invoice date range, **Process Invoices**, **Process Orders**, progress bar, on-screen log.
- Settings: SQL server and two database names, separate invoice-upload and order-download FTP accounts, local file folder, SMTP report mail.
- SQL Server stored procedures whose scripts are in the project: `SO_GETINVOICES`, `SO_GETINVOICEDETAILS`, `SO_INSERTORDER` (`SilOve_INSERTORDER.sql`).
- Local folders under the chosen path: retailer invoice folders, `RETRY_UPLOAD`, `ORDERS`, `Backup`.
- ClickOnce install and startup update from `https://overdriveqa.co.za/BSBC_App_Update/`.
- HTML email reports after a process run, when SMTP settings are complete and a ping to the SMTP host succeeds.

Out of scope of what the maintained buttons call:

- `SUN_SAGE2XML` (GS1 SOAP XML). The script is in the project. No VB call was found.
- `InsertOrderIntoDatabase`, which builds `EXEC [SUN_INSERTORDER]`. No caller was found. No `SUN_INSERTORDER` script is in Git.
- `ValidateOrderData`. The call site in `ProcessOrderFile` is commented out.
- `TestFTPConnection` in the maintained form. It has no button handler. The older `BSBC_FTP_Integration` form does call a method of that name.
- Settings `XMLUsername`, `XMLPassword`, `SQLUsername`, `SQLPassword`, `AutoUpdateEnabled`, `UpdateCheckInterval`, `UpdateServerUrl`, `LastUpdateCheck`. They exist on `My.Settings`. `frmMain.vb` does not read them when processing.
- ADE and the FTP-integration tree, if the `.gitignore` comment is the working agreement. They remain in Git.

## System architecture

```text
Operator (Windows desktop, ClickOnce)
  → TransactFlow Document Sync (frmMain)
      Process Invoices
        → SQL Server (invoice database, default name BSBC_Test)
            EXEC SO_GETINVOICES
            EXEC SO_GETINVOICEDETAILS
        → local CSV
            <path>\SHOPRITE\<Reference>.csv    AccountLink 5261
            <path>\PNP\<Reference>.csv         AccountLink 6960
        → FTP upload to ftp://<invoice FTP host>/<file>
            failure → <path>\RETRY_UPLOAD\<SHOPRITE|PNP>\
        → <path>\Backup\Invoices_Uploaded_<yyyyMMddHHmm>.zip
        → SMTP HTML report
      Process Orders
        → order FTP, files named Order_*.csv
            downloaded, then deleted from the FTP server
        → <path>\ORDERS\
            Retry\ then Processed\
        → SQL Server (order database, default name BSBC_Test)
            #SO_INSERTORDER_LINES + EXEC dbo.SO_INSERTORDER
            writes Sage InvNum (DocType 4, DocState 1) and _btblInvoiceLines
        → <path>\Backup\Orders_Uploaded_<yyyyMMddHHmm>.zip
        → SMTP HTML report

Not called by the buttons:
  SUN_SAGE2XML.sql
  ADE PNP IDE V2 (separate executable in this repo)
  BSBC_FTP_Integration Sage2GS1Xml (separate executable in this repo)
```

Provenance: **Implemented** for the arrows the VB and SQL perform.

## Major components

| Component | Path | Role |
|---|---|---|
| Main form | `BSBC Source Code/.../frmMain.vb` | Invoice export, order import, FTP, zip, email |
| SQL worker | `BSBC Source Code/.../Classes/SQLConnector.vb` | Background `SqlCommand` / `SqlDataAdapter` |
| Secret storage | `BSBC Source Code/.../EnhancedSecureSettings.vb` | DPAPI for FTP and email passwords |
| On-screen log | `BSBC Source Code/.../Modules/Globals.vb` | Appends a timestamped line to `txtLog` |
| Invoice list script | `SO_GETINVOICES.sql` | `ALTER` of `[dbo].[SO_GETINVOICES]`, `USE [BSBC]` |
| Invoice line script | `SO_GETINVOICEDETAILS.sql` | `ALTER` of `[dbo].[SO_GETINVOICEDETAILS]`, `USE [BSBC]` |
| Order insert script | `SilOve_INSERTORDER.sql` | Creates stub then `ALTER` of `dbo.SO_INSERTORDER` |
| Legacy GS1 script | `SUN_SAGE2XML.sql` | SOAP invoice XML for account `6960`. Not called |
| Older invoice app | `BSBC_FTP_Integration/.../frmMain.vb` | Calls `SUN_GETINVOICES` / `SUN_GETINVOICEDETAILS` |
| ADE exporter | `ADE Source Code/Project/Form1.cs` | Timer-driven export and FTP upload |

## Milestones visible in Git

| When | Commit | What the message says |
|---|---|---|
| 2025-04-23 | `4b9dd6a`, `43775ff` | Initial commit; ADE source added |
| 2025-04-23 to 2025-05-13 | `3613b34`, `15ae12c` | Dependencies and a test function; FTP details |
| 2025-06-04 to 2025-06-05 | `38e13b9`, `39caae2` | Export columns, styling, progress bar |
| 2025-06-10 | `c728aba`, `faf0b88` | Local copy, daily zip kept 90 days (`#482_BSBC`); retry upload (`#711`) |
| 2025-06-19 | `4698d78`, `8548041` | Error logging and email (`#680_BSBC`); gitignore update |
| 2025-07-03 | `406acc0` | OTA update |
| 2025-08-21 | `c4b8a42`, `6db582e` | Stored procedure used to get data; zip fix |
| 2026-01-14 to 2026-02-12 | `2092bf0`, `5b9772a`, `5ecb54a` | Orders in, then OrdersIn procedure updates |
| 2026-03-05 | `24d196d` | Order FTP, partial orders, order zip, retry, `SO_INSERTORDER` |
| 2026-03-16 | `d444c0c` | Error handling, unit of measure, address, table lock against duplicate SO numbers |
| 2026-03-17 | `f50af70` | Version update after publish |
| 2026-04-21 | `75823ee` | `fQuantityUR` and `fQtyChangeUR` set to the pack-adjusted quantity; `fQtyChangeUR` added to the line insert |

Ticket numbers `#482`, `#680`, and `#711` appear only in commit subjects. The issue tracker they belong to is **Unknown / Requires Confirmation**.

There is no forward implementation plan in the repository.

## Known dependencies

- Windows desktop with .NET Framework 4.8.
- SQL Server databases that expose Sage-shaped tables (`_bvARTransactionsFull`, `Client`, `InvNum`, `_btblInvoiceLines`, `StkItem`, `_etblUnits`, `_etblBarcodes`, `_etblDelAddress`, `_etblPriceListPrices`) and the procedures the app executes.
- Two FTP servers (invoice upload and order download), configured in user settings.
- An SMTP server for reports.
- ClickOnce hosting at `https://overdriveqa.co.za/BSBC_App_Update/` when the app is network-deployed.
- For the ADE tree only: SQL Server catalog `ADE - Live`, procedure `SUN_INVOICE_spSel` (body not in Git), local folders `c:\xprocure\` and `c:\xprocure\uploaded\`, and an FTP folder `PNPInvoices` on `xpliquor.dyndns.org`.

## Current project status

The maintained BSBC application is a manual desktop tool. Nothing in the repository schedules the two process buttons. The last change on `main` is a stored-procedure quantity fix on 2026-04-21. Whether that script has been applied to the live SQL Server, and whether operators run `BSBC_Test` or `BSBC`, is **Unknown / Requires Confirmation**.

`BSBC_FTP_Integration` and `ADE Source Code` are still in the tree. `.gitignore` says they are not the working set. Whether either executable is still installed on a PC is **Unknown / Requires Confirmation**.

## Open questions for humans

- What organisation BSBC is, and who runs the desktop app.
- Whether production uses database `BSBC`, `BSBC_Test`, or another catalog, and whether `SO_GETINVOICES`, `SO_GETINVOICEDETAILS`, and `SO_INSERTORDER` on that server match the scripts in Git.
- Whether invoice FTP consumers expect retailer subfolders. The app uploads both retailers to the FTP root, using the invoice number as the file name.
- Whether `BSBC_FTP_Integration` and `ADE Source Code` are retired.
- Whether this middleware is upstream of the products documented in Silicon Ark as `edi_shopfront` and `sagegs1xml`. No reference to those repositories was found in this Git history.
- Rotation of credentials that are compiled into source. Values are omitted here on purpose.