# Project plan — bi_tool_php

Evidence date: 2026-10-19.

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

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

The task-creation path `~/projects/sonar_scan/repos/bi_tool_php` was not present on this machine. Evidence is a clone of `origin/master`.

## Product

The repository is a set of PHP scripts that read SAWIS extract files and write rows into a local MySQL database named `sawis_bitool`. The active script, `import.php`, scans a hard-coded Windows folder, parses pipe-delimited lines, normalises the litre quantity, looks up country and product text, and inserts into `mapper_data`.

Provenance: **Inferred** from `import.php`, `header.csv`, and the commit subject "Dump from BI Server". There is no README and no product specification in the repository or in Silicon Ark. The word SAWIS is not printed by the scripts. It appears in the folder path inside `import.php` and `math.php`, and the column titles in `header.csv` use Afrikaans wine-export names (certificate number, producer, cultivar, total litres, bottles).

`index.php` updates existing `mapper_data` rows. `math.php` stops before it imports anything. `bt_getquestion.pseudo` is pseudocode for a student question flow and does not reference the import tables.

## Branch reality

Default branch is `master`. `origin/HEAD` points at `master`. No other local or remote branch was present in the clone.

| Commit | Date | Author | Subject |
|---|---|---|---|
| `0486b70` | 2023-10-25 | Jaco Brits | Initial commit (Bitbucket `.gitignore` only) |
| `85a0822` | 2023-10-25 | Jaco Brits | Dump from BI Server |
| `90cccc9` | 2023-11-01 | Dewald Stander | amendments to the import script |
| `b440d76` | 2023-11-18 | Dewald Stander | total litres calculation fix |

This documentation set is based on `master` at `b440d76`.

The task-creation scan listed `sonar-project.properties` and `sonarqube_scan.bat` at the repository root. Neither file is in `master`. Provenance: **Unknown** — they may have been untracked files on the scan host.

## Scope in the repository

In scope of what is committed on `master`:

- `import.php` — directory import into `mapper_data`, with litre handling and lookup enrichment at insert time.
- `index.php` — batch update of `mapper_data` rows where `processed = 0`, using different lookup tables from `import.php`.
- `math.php` — a script that prints the first file path in a test folder and then exits. Later lines are unreachable.
- `header.csv` — one semicolon-separated title row of 43 columns. No script reads this file.
- `bt_getquestion.pseudo` — unfinished pseudocode for lesson questions. Not wired to the PHP scripts.
- `.gitignore` — the Bitbucket template (Node, Java, Python, logs, IDE, media). It does not mention PHP or CSV.

Out of scope of the code that was found:

- A reporting UI, dashboard, or API.
- A schema, migration, or seed for `sawis_bitool`.
- Authentication, user accounts, and roles.
- A queue, scheduler, or deployment manifest.
- Tests, Composer, or a documented PHP version.
- The SonarQube files named at task creation.

## System architecture

```text
Operator runs a PHP file (CLI or web host — which one is unknown)
  import.php
    → scandir of a hard-coded OneDrive folder (2023_data_file)
    → pipe-split each line after the first
    → mysqli lookups: country_group_lookup, product_type_lookup
    → INSERT mapper_data (processed = 0)
  index.php
    → SELECT mapper_data WHERE processed = 0
    → mysqli lookups: country_groups, produk_kodes
    → UPDATE mapper_data (processed = 1), including a second litre negation when rk = R
  math.php
    → scandir of a different OneDrive folder
    → die() on the first path (no insert)

MySQL database sawis_bitool on localhost
  Tables named by SQL but not defined in this repo:
    mapper_data, country_group_lookup, product_type_lookup,
    country_groups, produk_kodes, uploaded_file
```

A commented block in `import.php` names an earlier connection: host `10.0.0.145`, user `silicon`, database `sawis_bi`. A password is in that comment. It is not copied into Silicon Ark. The live statements use `localhost`, user `root`, an empty password, and database `sawis_bitool`.

## Major components

| Component | Role | Last change |
|---|---|---|
| `import.php` | Reads every file in `...\SAWIS\2023_data_file\`, skips one line, inserts enriched rows into `mapper_data` | `b440d76` (2023-11-18) |
| `index.php` | Walks unprocessed `mapper_data` rows and writes lookup columns plus year, month, and `datumtyd` | `85a0822` (2023-10-25). Unchanged since the dump |
| `math.php` | Debug entry. Exits on the first file path under `...\SAWIS\Phase 1\Import Script Testing\` | `90cccc9` (2023-11-01) |
| `header.csv` | Column-title dictionary, 43 fields, semicolon separated | `85a0822` |
| `bt_getquestion.pseudo` | Pseudocode: next question for a student, then next lesson, then next level | `85a0822` |

## Milestones and implementation plan

No milestone plan is documented. History on `master` is four commits, ending 2023-11-18.

What the commits show:

1. **2023-10-25 — repository created.** Only `.gitignore`.
2. **2023-10-25 — dump from a BI server.** Adds `import.php`, `index.php`, `math.php` (six lines at that point; the file grew on 1 November), `header.csv`, and `bt_getquestion.pseudo`. The import at that point resumed from `uploaded_file.last_row`, looked up `country_groups` and `produk_kodes`, inserted into `mapper_data`, and logged each row in `uploaded_file`. Litre values had dots removed, then a comma turned into a decimal point. An `rk` value of `R` multiplied litres by `-1` with no rounding.
3. **2023-11-01 — import rewritten.** `import.php` scans a folder instead of resuming from `uploaded_file`. Lookups move to `country_group_lookup` and `product_type_lookup`. Inserts go to `imported_mapper_data`. Returns are rounded to 0 decimal places and then negated. `math.php` becomes a dumping script against a Phase 1 test folder.
4. **2023-11-18 — litre fix and insert target.** Execution time limit rises from 3600 to 12000 seconds. The litre formula stops stripping dots. The product lookup key changes from column 11 (`PRD_KDE`) to column 39 (`NPR_KDE`) with spaces removed, and `TYPE` is selected. Inserts go back to `mapper_data`, with column names aligned to that table (`prod_kode`, `bottleweight`, and so on). English and Afrikaans country names are stored in separate variables.

No commit after 2023-11-18 is on `origin/master`.

## Known dependencies

| Dependency | Evidence | Notes |
|---|---|---|
| PHP with `mysqli` | `import.php`, `index.php`, `math.php` | Version is **Unknown** |
| MySQL or MariaDB database `sawis_bitool` | Connection string in all three PHP files | Schema is not in the repo |
| Lookup data already loaded in `country_group_lookup` and `product_type_lookup` | Queries in `import.php` | Who loads those tables is **Unknown** |
| Lookup data in `country_groups` and `produk_kodes` | Queries in `index.php` | Used only by the older updater |
| A Windows folder under Dewald Stander's OneDrive, SAWIS `2023_data_file` | `$dirPath` in `import.php` | The path is absolute |
| Pipe-delimited extract files with one header line | Parser in `import.php` | `header.csv` itself is semicolon-separated and is not read |
| Operator who can run PHP against that machine and database | No scheduler is in the repo | How the run is started is **Unknown** |

## Current project status

The Git snapshot is dormant. The last change is the litre calculation on 18 November 2023. Nothing in the tree says the import is scheduled, deployed, or retired.

`import.php` is the only script edited after the BI dump's enrichment logic was replaced. `index.php` still contains the older lookup and a second litre negation. Running both on the same rows would overwrite enrichment and can negate a return twice. Whether anyone still runs `index.php` is **Unknown / Requires Confirmation**.

There is no open work branch, no issue tracker in the repo, and no planned feature list.

## Human confirmation

- Which script is the live path: `import.php` alone, `index.php` alone, or both in sequence.
- Whether `rk = R` means a return or some other reversal.
- Whether the 18 November litre formula is the approved rule.
- Whether product type should be keyed by `NPR_KDE` (column 39) or `PRD_KDE` (column 11).
- What `BO`, `BU`, and `BB` mean, and what the replacements `9A` and `3A` mean.
- Whether `bt_getquestion.pseudo` belongs to this product.
- Whether the SonarQube files from the task-creation scan should be committed.
- Which database is current: `sawis_bitool` on localhost, or `sawis_bi` on `10.0.0.145`.
- Whether the OneDrive path is still the drop folder, and who runs the script.
- The real table definitions (types, keys, indexes). They are not in this repository.
- PHP version, and whether the scripts are started from the CLI or through a web server.