# Technical documentation — PernodIntegration

Evidence date: 2026-10-07.

Source: `main` at `ecbc272`.

Secret values from config are not copied here. The files that contain them are named so they can be rotated.

## Application type

VB.NET console executable.

| Item | Value |
|---|---|
| Solution | `PernodIntegration.sln` (Visual Studio 16 format) |
| Startup project | `PernodIntegration/PernodIntegration.vbproj` |
| Output | `OutputType` Exe, `MyType` Console, startup object `PernodIntegration.Module1` |
| Target | .NET Framework 4.7.2 |
| Language options | `OptionExplicit` On, `OptionStrict` Off, `OptionInfer` On, `OptionCompare` Binary |
| Assembly | Title `PernodIntegration`, version `1.0.0.0`, copyright 2022, `ComVisible` false |
| Tests | SDK-style `PernodIntegration.Tests/PernodIntegration.Tests.vbproj`, `net472` |

`Application.myapp` sets `SingleInstance` false and `MySubMain` false. `InternalsVisibleTo` includes `PernodIntegration.Tests`.

`docs/integration-flow.md` calls the same executable an Azure WebJob (`OnDemand`). The repository has no WebJobs package, `settings.job`, or `run.cmd`.

## Repository layout

```text
PernodIntegration.sln
bitbucket-pipelines.yml
sonar-project.properties
docs/
  integration-flow.md
  tests.md
  create_test_schema.sql
  test-reports/.gitkeep
packages/Newtonsoft.Json.13.0.1/     committed package
PernodIntegration/
  Module1.vb                          run loop, SQL, HTTP, logging
  OrderClass.vb                       orchestrator JSON
  TokenClass.vb                       AIS token response
  App.config                          settings used by the exe
  web.config                          duplicate settings, not in the vbproj
  packages.config
  My Project/
  bin/Debug/                          committed build output, including exe.config
  obj/Debug/                          committed build intermediates
PernodIntegration.Tests/
  IntegrationTests.vb
  OverflowMathTests.vb
  StubJdeHandler.vb
  TestDatabase.vb
  TestReport.vb
  TestBase.vb
  TestRunHooks.vb
  App.config                          LocalDB and stub URLs
.vs/                                  committed Visual Studio user files (v16 and v17)
```

`.gitignore` ignores `docs/test-reports/test-report-*.md`, `TEST*.xml`, and common junk. It does not ignore `bin/`, `obj/`, or `.vs/`. Those directories are tracked.

There is no README. The behaviour notes are `docs/integration-flow.md` and `docs/tests.md`.

## Configuration keys

Read in `Module1` from `ConfigurationManager.AppSettings`:

| Key | Used for | In committed `App.config` / `web.config` |
|---|---|---|
| `ConnectionString` | All SQL | Yes. Catalog `PSOrder`. Data source is a named SQL Express instance on a developer machine. SQL authentication |
| `OAuthTokenUrl` | Azure AD token | Yes. Host `login.microsoftonline.com`, tenant-specific path |
| `JdeTokenUrl` | AIS token | Yes. Host `api-staging.pernod-ricard.io`, path under `/v1/jde/prsouthafrica/rest/v2/tokenrequest` |
| `JdeOrderUrl` | Orchestrator | Yes. Same staging host, orchestrator `COR55ZA0012_AddEmployeeSales` |
| `ClientId` | OAuth form | Yes |
| `ClientSecret` | OAuth form | Yes |
| `ApiKey` | Header `Ocp-Apim-Subscription-Key` | Yes |
| `JdeUsername` | Token JSON | Yes |
| `JdePassword` | Token JSON | Yes |
| `JdeEnvironment` | Token JSON | Yes |
| `JdeRole` | Token JSON | Yes |
| `JdeDeviceName` | Token JSON `deviceName` | Yes |
| `OAuthScope` | OAuth form `scope` | No. Test config sets a stub scope |
| `OAuthGrantType` | OAuth form `grant_type` | No. Test config sets `client_credentials` |

`web.config` repeats the application settings and omits the `startup` / `supportedRuntime` element that `App.config` has. The vbproj includes `App.config` only. MSBuild uses that file as the exe config. `web.config` is still tracked.

The test `App.config` points at `(localdb)\MSSQLLocalDB`, catalog `PernodIntegrationTests`, integrated security, and host `jde-stub.invalid`. `TestDatabase.ConnectionString` throws if the string is not LocalDB and that database name.

## Runtime sequence

`Main`:

1. `GetJdeToken`.
2. `GetOrders2Send` with that token (possibly nothing).
3. `Console.WriteLine("Done")`.
4. `Console.ReadLine()`.

`GetOrders2Send`:

1. `ExpirePriorPeriodUnresolvedOrders`.
2. `SELECT DISTINCT [userid] FROM [ORDER_HEADER] WHERE [status] = '99' AND [userid] IS NOT NULL`, then `CancelOverflowFailedOrders` per id.
3. `SELECT * FROM [ORDER_HEADER] WHERE [status] = '99'`, then `CreateOrder` per row.

HTTP goes through `CreateHttpClient`. When `HttpMessageHandler` is set, tests inject `StubJdeHandler` and the handler is not disposed with the client. Production leaves the field unset and uses a normal `HttpClient`.

Order-detail SQL and the status-`20` update concatenate `sOrderId` into the command text. Overflow and error-log statements use parameters.

## Authentication

Two tokens:

1. Azure AD. `POST` form body. Response type `OAuthTokenResponse`. The code reads `access_token`. A `Cookie` header is hardcoded on this request (cookie names `fpc`, `stsservicecookie`, `x-ms-gateway-slice`). The value is not in config.
2. AIS. `POST` JSON. Headers: `jde-AIS-Auth-Device: BRIDGE`, subscription key, `Authorization: Bearer` plus the OAuth token. Response type `TokenClass`. The code reads `userInfo.token` only. Other properties on that class (`aisSessionCookie`, `passwordAboutToExpire`, `userAuthorized`, and the rest) are unused.

The order POST does not send the AIS token as a header. It sends a new OAuth bearer and puts the original AIS token in JSON property `token`.

No interactive user authentication exists in this process.

## Database

Production name used by the committed connection string and by the docs: `PSOrder`.

Tables this job reads or writes:

| Table | Use |
|---|---|
| `ORDER_HEADER` | Read `Orderid`, `CustomerId`, `BusinessUnit`, `userid`, `status`, `Orderdate`. Update `status` to `20` or `22` |
| `ORDER_DETAIL` | Read `clientcode` and `Quantity` for an order. Sum `Quantity` for the cap |
| `ERROR_LOG` | Insert. Existence of a row for `orderid` changes the cap |

`docs/create_test_schema.sql` creates only those columns, and the comments say production `PSOrder` has more. Test header columns: `Orderid` int identity, `Orderdate` datetime, `userid` int null, `status` varchar(10), `CustomerId` varchar(50), `BusinessUnit` varchar(50). Detail: `orderid`, `clientcode` varchar(50), `Quantity` int. Error log: `component`, `operation`, `message` varchar(4000), `exceptionmessage` varchar(2000), `stacktrace` varchar(max), `useremail` varchar(320), `orderid` int null.

No foreign keys, indexes, or status lookup table are defined in that script. No migration runner is in the repo.

`ORDER_HEADER` column name in SQL is sometimes `Orderid` and sometimes `orderid`. The queries use both. SQL Server default collation behaviour for those identifiers was not executed here.

## Integrations

| Call | Method | URL key | Notable headers |
|---|---|---|---|
| Azure AD token | POST form | `OAuthTokenUrl` | Hardcoded `Cookie` |
| JDE token | POST JSON | `JdeTokenUrl` | `Authorization`, `Ocp-Apim-Subscription-Key`, `jde-AIS-Auth-Device: BRIDGE` |
| Add employee sales | POST JSON | `JdeOrderUrl` | Same three headers. Body is `OrderClass` |

Orchestrator name in the committed URL: `COR55ZA0012_AddEmployeeSales`.

`HttpClient.SendAsync(...).Result` blocks the caller. There is no timeout set on the client. Provenance: **Implemented**.

## Background work

The process is the job. Nothing in the repo schedules it, queues it, or hosts it.

`Console.ReadLine()` after `Done` blocks until a line is read. An Azure WebJob or task scheduler that does not close stdin will not see the process exit.

## Security notes (as implemented)

- `App.config`, `web.config`, and the committed `PernodIntegration/bin/Debug/PernodIntegration.exe.config` hold a SQL password, an Azure AD client secret, an API subscription key, and a JDE password. Rotate them and stop tracking those files. The values are omitted from Silicon Ark on purpose.
- The OAuth `Cookie` header is a fixed Microsoft identity cookie baked into source.
- `clientcode` is not concatenated into SQL. The order id is. A non-numeric order id can change the detail query and the status update. Ids coming from the integer identity column are numeric when the column is an int. Production column type is **Unknown**.
- `LogError` redacts three token shapes. It does not redact `client_secret`, `password`, or the subscription key if those strings appear in an exception message.
- `.vs` user options and debug symbols are tracked.
- Tests refuse a connection string that is not LocalDB `PernodIntegrationTests`, and they assert the stub host is not `pernod-ricard.io`.

## Development and testing

From `docs/tests.md`:

```text
sqllocaldb create MSSQLLocalDB
sqllocaldb start MSSQLLocalDB
MSBuild PernodIntegration.sln /p:Configuration=Debug
vstest.console PernodIntegration.Tests\bin\Debug\net472\PernodIntegration.Tests.dll
```

Manual schema apply, optional:

```text
sqlcmd -S "(localdb)\MSSQLLocalDB" -E -i docs\create_test_schema.sql
```

The test assembly creates that database itself and drops it in `AssemblyCleanup` after writing the report. Drop failure is printed and does not hide the report write (`TestRunHooks`).

Nine tests: six integration, three overflow-math. See `docs/tests.md` for the case table. This document does not repeat it.

`Friend` members used by tests: `HttpMessageHandler`, `GetOrders2Send`, `SelectOldestFailedOrdersToCancel`.

## Pipeline and monitoring

`bitbucket-pipelines.yml`:

- Docker service memory 3072 MB.
- Clone depth full.
- On `main` and on every pull request: pipe `sonarsource/sonarqube-scan:3.0.2`.
- Variables `SONAR_HOST_URL` and `SONAR_TOKEN` from the repository or workspace. `EXTRA_ARGS` includes `-Dsonar.scanner.forceEmbeddedJava=true`.
- Cache `~/.sonar`.

`sonar-project.properties` sets only:

```text
sonar.projectKey=overdrive_pernodintegration_2e67c903-cb43-492a-b11d-72f12b50a5bd
```

The pipeline does not restore NuGet, build, or run MSTest. No application-insights, health, or log-shipping config is in the repo. Operational logs are console lines plus `ERROR_LOG`.

## Dependencies

| Package | Version | Where |
|---|---|---|
| Newtonsoft.Json | 13.0.1 | `packages.config` and committed `packages/Newtonsoft.Json.13.0.1`. Hint path `..\packages\Newtonsoft.Json.13.0.1\lib\net45\Newtonsoft.Json.dll` |
| MSTest.TestFramework | 3.6.3 | Test project `PackageReference` |
| MSTest.TestAdapter | 3.6.3 | Test project `PackageReference` |

Framework references on the exe project include `System.Configuration`, `System.Data.SqlClient`, and `System.Net.Http`.

## Deployment

No publish profile, transform, Dockerfile, or environment-specific config is in the repository.

The committed API host is staging (`api-staging.pernod-ricard.io`). The committed SQL server is a workstation instance. How, or whether, those are replaced in a hosted environment is **Unknown / Requires Confirmation**.