# PRD — bi_tool_php

Evidence date: 2026-10-19. Source: `git@bitbucket.org:overdrive/bi_tool_php.git`, `master` at `b440d76` (2023-11-18, Dewald Stander, "total litres calculation fix").

There is no prior PRD in the repository or in Silicon Ark. Every requirement below was recovered from the files on `master`. Provenance is marked on each item.

## Product purpose

**Implemented:** a PHP process can read pipe-delimited files from one hard-coded folder and insert rows into MySQL table `mapper_data` in database `sawis_bitool`, with a normalised litre value and text copied from country and product lookup tables.

**Implemented beside that:** `index.php` can update rows already stored in `mapper_data`. `math.php` can list a test folder and stop. Neither file is called by `import.php`.

**Inferred:** the files are SAWIS wine-export certificate lines. The inference comes from the folder name `SAWIS`, the Afrikaans column titles in `header.csv`, and a sample `INSERT` that used to sit in comments in `import.php` (removed in `90cccc9`). That sample is not copied here. It showed a dated certificate line with cultivar, bottle size, origin, and currency amounts.

**Unknown / Requires Confirmation:** the product name users would recognise, who the audience of `mapper_data` is, and whether this loader is still operated. The repository name `bi_tool_php` and the commit "Dump from BI Server" say the files were taken from a BI server. They do not state a reporting product.

## Users and roles

| Role | What they can do | Evidence | Provenance |
|---|---|---|---|
| Operator | Run `import.php` on a machine that can open the OneDrive folder and MySQL on localhost as `root` | Hard-coded path and connection in `import.php` | **Inferred**. No login, role, or audit user exists in the script |
| Operator | Run `index.php` against the same database | `index.php` | **Inferred**. The file has not changed since the dump |
| Operator | Run `math.php` and see the first file path, then the process ends | `math.php` line 30 `die` | Implemented |
| Reader of BI reports | None in this repository | No report, query UI, or API | Implemented absence |

No account, session, or permission check exists. Anyone who can execute the PHP file uses the database credentials compiled into it.

Git history names two authors: Jaco Brits (dump) and Dewald Stander (later import edits). The hard-coded path uses the Windows profile `DewaldStander`. That identifies a machine layout. It does not by itself assign an ongoing owner.

## Main workflows

### W1 — Import a folder of extract files

Trigger: a PHP runtime executes `import.php`.

Inputs:

- MySQL on `localhost`, database `sawis_bitool`, user `root`, empty password.
- Every directory entry except `.` and `..` under `C:\Users\DewaldStander\OneDrive - Silicon Overdrive\Documents\_Projects\SAWIS\2023_data_file\`.
- Each file is read as text. The first line is skipped. Each later line is split on `|`.

Steps the script performs for every data line:

1. Read column 12 as litres. Replace `,` with `.`, then cast with `floatval`.
2. When column 21 is exactly `R`, round that number to 0 decimal places and multiply by `-1`. Other rows are not rounded.
3. Query `country_group_lookup` where `lnd_kde` equals `cleanString` of column 4. Read Afrikaans and English descriptions plus `BO`, `BU`, and `BB`.
4. Query `product_type_lookup` where `prd_kde` equals column 39 with spaces removed. Read ``DESCRIPTION FOR REPORTS``, `WHITE_OR_RED`, and `TYPE`.
5. `INSERT` one row into `mapper_data`. `processed` is the literal `0`. Derived `datumtyd`, `year`, and `month` come from `strtotime` of column 0.
6. On a failed insert, append the MySQL error and `<br>` to a string. The loop continues.
7. After each file, `var_dump` the error string.

Outputs: new `mapper_data` rows, and a dumped error string per file (empty when every insert succeeded). The script does not move, rename, or delete the source file. It does not record a high-water mark.

Connection failure ends the process with `Connection failed:` and the driver message.

Provenance: **Implemented** in `import.php` at `b440d76`.

### W2 — Enrich unprocessed mapper rows

Trigger: a PHP runtime executes `index.php`.

Inputs: rows in `mapper_data` with `processed = 0`. The script reads `id`, `lnd_kde`, `prd_kde`, `totale_liters`, `rk`, and `datum`.

Steps:

1. Repeat a select up to 11 times. The counter starts at 11 and stops after 21. Each select uses `LIMIT` of counter × 500, so the limits are 5500, 6000, …, 10500. There is no `OFFSET`.
2. When a select returns no rows, print `0 results :` plus the counter and `exit`.
3. For each row, when `rk` is exactly `R`, replace litres with `totale_liters * -1`. There is no rounding in this file.
4. Look up `country_groups` on `kde = lnd_kde` and `produk_kodes` on `kd = prd_kde`.
5. `UPDATE` the row: group names, report descriptions, white/red, type, the litre value from step 3, `year`, `month`, `datumtyd`, and `processed = 1`.
6. Print `UR:` and the id, plus `<br>`, when the update succeeds. Print the driver error and the SQL when it fails.

The connection is closed after the loop.

Provenance: **Implemented** in `index.php`. This file is the dump from `85a0822` and was not part of the November 2023 import amendments.

Interaction with W1: **Inferred risk, requires confirmation.** W1 already stores a negated litre value and group text, and it stores `processed = 0`. W2 selects those rows, negates litres again when `rk` is `R`, and overwrites the group and product text from different tables.

### W3 — Probe the test folder

Trigger: a PHP runtime executes `math.php`.

Inputs: the same localhost database, and the folder `C:\Users\DewaldStander\OneDrive - Silicon Overdrive\Documents\_Projects\SAWIS\Phase 1\Import Script Testing\`.

Behaviour: `scandir`, then `die(var_dump($filePath))` on the first entry that is not `.` or `..`. No row is inserted.

Lines after that `die` still mention `uploaded_file.last_row` and the older litre formula (strip `.`, then turn `,` into `.`). They do not run.

Provenance: **Implemented**.

### W4 — Next student question (pseudocode only)

`bt_getquestion.pseudo` describes `getQuestion(userId, awsner = -1)`. When the answer argument is `-1`, it would load `question_id`, `lesson_id`, and `level_id` from `student`, then the next row of `lession_flow` by `order + 1`. If the lesson has no further question it would move to the next lesson, then the next level. The `else` branch of `getQuestion` is empty. The file is not PHP and is not included by the other scripts.

Provenance: **Planned** only in the sense that the text describes a flow. Nothing in the repository implements it. Whether it was ever a requirement of this BI loader is **Unknown / Requires Confirmation**.

## Functional requirements

### FR-001 — Load pipe-delimited extract lines into mapper_data

Requirement: executing `import.php` inserts one `mapper_data` row per data line in each file under the hard-coded `2023_data_file` folder.

Who: the operator who runs the script.

Trigger: PHP execution.

Inputs: files in that folder. First line skipped. Fields separated by `|`. Column positions follow `header.csv` (43 titles, indices 0–42). The script does not open `header.csv`.

Expected behaviour: an `INSERT` listing the source columns plus lookup columns. See the column map in `technical-documentation.md`.

Outputs: rows in `mapper_data`. Failed statements are collected and dumped per file.

Validation: `cleanString` trims and strips quotes, `&`, `$`, parentheses, and backticks, and turns `\` into a space. It also maps the exact strings `9:00 am` and `3:00 am` to `9A` and `3A` before those strips. The litre column and the raw `datum` value are not passed through `cleanString`. `datum` and `prod_kode` are interpolated without SQL quotes.

Error conditions: database connection failure aborts. A failed `INSERT` is recorded and the next line is attempted. There is no transaction and no rollback. A path that is not a readable file still reaches `fgetcsv` because the read loop sits outside the `fopen` success check.

Evidence: `import.php`, `header.csv`.

Implementation: Implemented.

Confidence: High for the control flow. Medium for the business meaning of each column.

### FR-002 — Normalise total litres, and negate when RK is R

Requirement: column 12 (`TOTALE LITERS`) is stored as a float. A comma is treated as a decimal point. Existing full stops stay in the string and `floatval` parses what it can. When column 21 (`RK`) is exactly `R`, the value is rounded to an integer and multiplied by `-1`.

Who: `import.php` during W1.

Trigger: each data line.

Inputs: raw column 12 and raw column 21 (not trimmed before the `R` test).

Expected behaviour at `b440d76`: `floatval(str_replace(',', '.', $row[12]))`, then `round(..., 0) * -1` only for `R`.

Outputs: `mapper_data.totale_liters`.

Validation: no check for an empty litre field, a second comma, or a thousands separator. `floatval` of an unparsable string is `0`.

Error conditions: none specific. A non-numeric litre becomes `0` and is still inserted.

Evidence: `import.php` lines that set `$new_totale_liters`. Commit `b440d76` replaced the previous formula, which deleted `.` before converting `,`.

Implementation: Implemented.

Confidence: High for the code path. The business meaning of `R` is **Inferred** (a reversal of volume). **Requires confirmation.**

### FR-003 — Attach country-group text at insert

Requirement: for each line, `import.php` copies English and Afrikaans country descriptions and the columns `BO`, `BU`, and `BB` from `country_group_lookup` where `lnd_kde` matches column 4 after `cleanString`.

Who: `import.php`.

Trigger: each data line, one query per line.

Inputs: column 4 (`LND_KDE`).

Expected behaviour: missing description keys become empty strings. `BO`, `BU`, and `BB` are read without an `isset` check. Notices are suppressed (`E_ALL ^ E_NOTICE`), so a missing lookup row becomes empty strings via `cleanString` on null.

Outputs: `group_name`, `group_name_afrikaans`, `bo`, `bu`, `bb`.

Error conditions: a failed lookup query is not tested. `mysqli_fetch_assoc` is called on the result immediately.

Evidence: `import.php` query on `country_group_lookup`.

Implementation: Implemented.

Confidence: High for the copy. Meaning of `BO`, `BU`, and `BB` is **Unknown / Requires Confirmation**.

### FR-004 — Attach product-type text at insert, keyed by NPR code

Requirement: `import.php` copies ``DESCRIPTION FOR REPORTS``, `WHITE_OR_RED`, and `TYPE` from `product_type_lookup` where `prd_kde` equals column 39 (`NPR_KDE`) with spaces removed.

Who: `import.php`.

Trigger: each data line.

Inputs: column 39, not column 11. Column 11 is still stored in `mapper_data.prd_kde`.

Expected behaviour: the three selected fields populate `description_for_reports`, `white_or_red`, and `type`. The variable `beskrywing_vir_verslag` is filled only when the result contains `beskrywing_vir_verslag`. The `SELECT` does not contain that column, so the inserted Afrikaans report description is an empty string on this path.

Outputs: those `mapper_data` columns. `processed` is `0` even though this enrichment has already run.

Error conditions: same as FR-003 for a failed query.

Evidence: `import.php` at `b440d76`. The previous commit `90cccc9` looked up column 11 and did not select `TYPE`.

Implementation: Implemented.

Confidence: High for the key change. Whether column 39 is the correct product key is **Unknown / Requires Confirmation**.

### FR-005 — Derive year, month, and a timestamp from the date column

Requirement: `import.php` stores `datumtyd` as `Y-m-d H:i:s`, `year` as `Y`, and `month` as `m`, all from `strtotime` of column 0. The raw column 0 is also stored in `datum` without quotes.

Who: `import.php`.

Trigger: each insert.

Inputs: column 0 (`DATUM`).

Expected behaviour: PHP's `strtotime` parse. Unparsable input yields the formatted epoch-failure result that this PHP build produces (typically a warning suppressed as a notice, and a 1970 date, depending on version). The version is **Unknown**.

Outputs: `datum`, `datumtyd`, `year`, `month`.

Validation: no format check.

Evidence: `import.php` insert list.

Implementation: Implemented.

Confidence: Medium. The source date format is **Unknown**. The historical commented sample used a value like `2020-01-30`, which would be invalid SQL when interpolated without quotes. Whether current files are numeric dates or quoted-safe tokens is **Unknown / Requires Confirmation**.

### FR-006 — Clean selected strings before insert

Requirement: `cleanString` trims, maps `9:00 am` to `9A` and `3:00 am` to `3A`, removes `'`, `"`, `&`, `$`, `(`, `)`, and `` ` ``, and replaces `\` with a space.

Who: `import.php`, applied to most source fields and to lookup text.

Trigger: each stored string listed in the insert.

Inputs: one field.

Expected behaviour: the substitutions above, in that order. The time-of-day map runs before punctuation stripping, and only on the whole trimmed value.

Outputs: the cleaned string.

Evidence: `import.php` function `cleanString`.

Implementation: Implemented.

Confidence: High for the substitutions. Why those two times become `9A` and `3A` is **Unknown / Requires Confirmation**.

### FR-007 — Batch-update unprocessed rows from the older lookup tables

Requirement: `index.php` sets `processed` to `1` and writes country group names from `country_groups` and product text from `produk_kodes`, using `lnd_kde` and `prd_kde` already stored on the row. It also rewrites litres, year, month, and `datumtyd`.

Who: operator running `index.php`.

Trigger: PHP execution.

Inputs: `mapper_data` rows with `processed = 0`.

Expected behaviour: see W2. One run issues at most 11 selects. The largest theoretical pull, if every batch is full, is 5500+6000+…+10500 = 88000 rows. Remaining rows stay `processed = 0`.

Outputs: updated rows. HTML fragments `UR:{id}<br>` or an error line.

Validation: Afrikaans group name and the two report-description fields use `isset`. `group_name`, `white_or_red`, and `type` do not.

Error conditions: connection failure aborts. An update failure is printed and the loop continues. A batch of zero rows ends the process.

Evidence: `index.php`.

Implementation: Implemented.

Confidence: High for the control flow. Whether this script is still in the operating procedure is **Unknown / Requires Confirmation**.

### FR-008 — Stop the maths probe before import

Requirement: `math.php` does not insert. It dumps the first candidate path and terminates.

Who: operator running `math.php`.

Trigger: PHP execution.

Inputs: the Phase 1 test folder.

Expected behaviour: process ends at the first `die`.

Outputs: a dumped path string.

Evidence: `math.php`.

Implementation: Implemented.

Confidence: High.

### FR-009 — Keep a column-title dictionary

Requirement: `header.csv` holds one line of 43 semicolon-separated titles matching the pipe-column positions used by `import.php`.

Who: a reader of the repository. No runtime requirement. No script loads the file.

Trigger: none.

Inputs: the file itself.

Expected behaviour: documentation of positions 0–42, from `DATUM` through `ClassName`. Titles are padded with spaces in the file. Position 12 is `TOTALE LITERS`. Position 21 is `RK`. Position 39 is `NPR_KDE`.

Outputs: none at runtime.

Evidence: `header.csv`.

Implementation: Implemented as a file. It is not an application feature that executes.

Confidence: High for the titles. Why the dictionary uses `;` while the loader splits on `|` is **Unknown / Requires Confirmation**.

## Integrations

| System | Purpose | Trigger | Direction | Auth | Response | Failure |
|---|---|---|---|---|---|---|
| MySQL `sawis_bitool` on localhost | Store `mapper_data` and serve lookups | Script start | Read lookups, write `mapper_data` | User `root`, empty password, in source | `mysqli` result or `true`/`false` on statement | Connection: process dies. Statement: error string or echo, loop continues |
| Commented MySQL `sawis_bi` at `10.0.0.145` | Earlier target, commented out | Not called | — | User `silicon` and a password are in the comment. Password not recorded here | — | Not active code |
| Windows folder on OneDrive (`2023_data_file`) | Source extracts | `import.php` start | Read files | The OS user's rights to that path | `scandir` / `fopen` | A bad path: `scandir` warning, then the foreach still runs on `false` in the way PHP warns. Not wrapped |
| Windows folder `Phase 1\Import Script Testing` | Debug listing | `math.php` | Read directory | OS rights | First path, then exit | Same class of uncaught `scandir` failure |

No HTTP API, SMTP, queue, or third-party SaaS client is present.

`index.php` and the pre-November import also named tables `country_groups`, `produk_kodes`, and `uploaded_file`. Those are further tables in the same database, not separate systems. `uploaded_file` is not written by the current `import.php`.

## Important constraints

- Memory limit is set to `16G` in `import.php` and `index.php`. `import.php` allows 12000 seconds. `index.php` allows 3600 seconds. Provenance: **Implemented**.
- Notices are hidden (`E_ALL ^ E_NOTICE`) in both of those files. Provenance: **Implemented**.
- `fgetcsv` is called with length `1000` and delimiter newline, then the line is split on `|`. A line longer than 1000 characters is split by PHP's length limit before the pipe split. Provenance: **Implemented**. Whether real extracts exceed 1000 characters is **Unknown**.
- Source paths and database names are hard-coded. There is no `.env` and no CLI argument. Provenance: **Implemented**.
- Re-running `import.php` inserts again. No uniqueness check is in the script. Whether the table has a unique key is **Unknown**.
- `processed = 0` after an enriched insert (FR-004) disagrees with `index.php`, which treats `0` as "not yet enriched". Provenance: **Implemented** on each file. The combined rule is **Requires Confirmation**.

## Unknown / requires confirmation

- Live script: `import.php`, `index.php`, or both.
- Meaning of `RK = R`, `BO` / `BU` / `BB`, `9A` / `3A`, and `processed`.
- Correct product lookup key after `b440d76` (column 39 versus column 11).
- Approved litre format (comma-decimal only, versus the older thousands-dot stripping).
- Date format of `DATUM`, given the unquoted insert.
- Schema, indexes, and which database host is production.
- PHP version and the start method (CLI versus web).
- Whether `bt_getquestion.pseudo` is in scope.
- Whether SonarQube files belong in the repository. They are not on `master`.
- Whether the OneDrive drop folder and the empty `root` password are still how this is operated.