# Technical documentation — bi_tool_php

Evidence date: 2026-10-19. Revision: `master` `b440d76` (2023-11-18).

A password is committed inside a comment in `import.php`. It is not copied here. The comment also names host `10.0.0.145`, user `silicon`, and database `sawis_bi`. The statements that execute use different values, listed below.

## Architecture

Three PHP scripts and two data files sit at the repository root. There is no framework, autoloader, or shared include. Each script opens its own `mysqli` connection. Nothing calls `require` or `include`.

```text
bi_tool_php/
  import.php                 folder → pipe rows → lookups → INSERT mapper_data
  index.php                  SELECT mapper_data (processed = 0) → other lookups → UPDATE
  math.php                   scandir test folder → die on first path
  header.csv                 43 column titles, semicolon separated, unread by PHP
  bt_getquestion.pseudo      student question/lesson/level pseudocode
  .gitignore                 Bitbucket default template
```

```text
import.php ── mysqli ── localhost / sawis_bitool
                ├── read  country_group_lookup, product_type_lookup
                └── write mapper_data

index.php ── mysqli ── localhost / sawis_bitool
                ├── read  mapper_data, country_groups, produk_kodes
                └── write mapper_data

math.php  ── mysqli ── localhost / sawis_bitool
                └── no query is reached (die on first file path)
                unreachable: read uploaded_file
```

The active import path is a single request or CLI process with `memory_limit` `16G` and `max_execution_time` `12000`. It holds the whole directory listing, then reads each file as a stream of `fgetcsv` lines. It does not batch commits. Each line is its own `INSERT`.

## Languages and frameworks

| Layer | What the tree shows | Evidence |
|---|---|---|
| Language | PHP, `<?php` scripts, `mysqli` object API | The three PHP files |
| PHP version | Not pinned. No `composer.json`, no Docker image, no `.php-version` | Repository root |
| Database client | `new mysqli(...)`. Results read with `mysqli_fetch_assoc` and `$result->fetch_assoc` / `num_rows` | Mixed style in the same files |
| Database server | Named `sawis_bitool`. Engine (MySQL or MariaDB) and version are **Unknown** | Connection code |
| Frontend | None. `index.php` and error strings contain `<br>`, so a browser can display them if the file is served as PHP | Echo statements |
| Packaging | None | No Composer, npm, or NuGet |

`error_reporting(E_ALL ^ E_NOTICE)` is set in `import.php` and `index.php`. `math.php` does not change error reporting.

## Application structure

### `import.php`

Connection constants at the top of the file:

| Setting | Active value |
|---|---|
| Host | `localhost` |
| User | `root` |
| Password | empty string |
| Database | `sawis_bitool` |

On `connect_error` the script `die`s with `Connection failed:` and the message.

`$dirPath` is `C:\Users\DewaldStander\OneDrive - Silicon Overdrive\Documents\_Projects\SAWIS\2023_data_file\`.

Parser:

1. `scandir`.
2. Skip `.` and `..` only. The comment mentions hidden files. The code does not skip them. A subdirectory is not filtered out.
3. `fopen` the path. On success, `fgets` once (`$skip = 1`).
4. The `while` that calls `fgetcsv($handle, 1000, "\n")` is outside the `fopen` success block. A failed open still reaches `fgetcsv`.
5. `explode("|", $data[0])`.

`fgetcsv` length `1000` is PHP's maximum line length for that call. The enclosure and escape are the PHP defaults (`"` and `\`). A line that itself contains quotes can therefore be parsed as more than one field before the pipe split, which then sees only field 0.

The script never calls `$conn->close()`.

### `index.php`

Same active database settings as `import.php` (empty password written as `""`).

The loop is:

```php
$x = 11;
$e = $x + 10;          // 21
while ($x <= $e) {
    $lowerLimit = $x * 500;
    // SELECT ... WHERE processed = 0 LIMIT $lowerLimit
    $x++;
}
```

Limits in order: 5500, 6000, 6500, 7000, 7500, 8000, 8500, 9000, 9500, 10000, 10500.

Rows updated to `processed = 1` drop out of the next select, so this is not an offset page. It is eleven pulls of whatever is still unprocessed, each allowed to be larger than the last. Zero rows on a pull prints `0 results :` plus `$x` and `exit`s. The connection is closed after a full pass.

`group_name` on the update is `$rl['group_name']` with no `isset`. The Afrikaans name is guarded. `white_or_red` and `type` are not guarded.

### `math.php`

Same database settings.

`$dirPath` is `C:\Users\DewaldStander\OneDrive - Silicon Overdrive\Documents\_Projects\SAWIS\Phase 1\Import Script Testing\`. A comment above it names `...\SAWIS\mapper_data\`.

Line 30 is `die(var_dump($filePath))` inside the file loop, so the first real directory entry ends the process. The `uploaded_file` query, the header skip, and the litre `var_dump` below that line are dead. The dead litre line is the pre-`b440d76` formula: strip `.`, then replace `,` with `.`.

### `header.csv`

One line. 43 fields separated by `;`. Leading spaces pad several titles. Index map used by the importer:

| Index | Title in `header.csv` | `mapper_data` column written by `import.php` |
|---|---|---|
| 0 | DATUM | `datum` (unquoted raw) plus derived `datumtyd`, `year`, `month` |
| 1 | SERTIFIKAAT_NOMMER | `sertifikaat_nommer` |
| 2 | PROD_KODE | `prod_kode` (unquoted) |
| 3 | PRODUSENT NAAM | `produsent_naam` |
| 4 | LND_KDE | `lnd_kde`. Also the key for `country_group_lookup.lnd_kde` |
| 5 | LND_GRP | `lnd_grp` |
| 6 | SAWIS_LANDNAAM | `sawis_landnaam` |
| 7 | REEKSNOMMER | `reeksnommer` |
| 8 | OESJ | `oesj` |
| 9 | VARIETEIT | `varieteit` |
| 10 | PRODUKNAAM | `produknaam` |
| 11 | PRD_KDE | `prd_kde`. Not the product-lookup key at `b440d76` |
| 12 | TOTALE LITERS | `totale_liters` after the float and `R` rule. Raw text is not stored |
| 13 | BOTTELS_BULK | `bottels_bulk` |
| 14 | BOTT_GRTE | `bott_grte` |
| 15 | VER_PAK | `ver_pak` |
| 16 | OORSPRONG | `oorsprong` |
| 17 | WAARDE | `waarde` |
| 18 | SAWIS_PROD | `sawis_prod` |
| 19 | SAWIS PRODUKNAAM | `sawis_produknaam` |
| 20 | LANDNAAM | `landnaam` |
| 21 | RK | `rk`. Also the `R` test, on the raw value |
| 22 | HANDELSNAAM | `handelsnaam` |
| 23 | HOEV_PPAK | `hoev_ppak` |
| 24 | WSNO | `wsno` |
| 25 | WSBNO | `wsbno` |
| 26 | WSBEXTRANO | `wsbextrano` |
| 27 | mic_waarde | `mic_waarde` |
| 28 | BottleWeight | `bottleweight` |
| 29 | BottleDescription | `bottledescription` |
| 30 | ClosureType | `closuretype` |
| 31 | Colour | `colour` |
| 32 | CUR | `cur` |
| 33 | Foreign_Value | `foreignvalue` |
| 34 | INC_TRM | `inc_trm` |
| 35 | VON_MCC | `von_mcc` |
| 36 | EURValue | `eurvalue` |
| 37 | GBPValue | `gbpvalue` |
| 38 | USDValue | `usdvalue` |
| 39 | NPR_KDE | `npr_kde` with spaces removed. Also the key for `product_type_lookup.prd_kde` |
| 40 | CF | `cf` |
| 41 | SAWIS_APPL_NO | `sawis_appl_no` |
| 42 | ClassName | `classname` |

Columns filled from lookups, not from the file:

| Column | Source at `b440d76` |
|---|---|
| `group_name` | `country_group_lookup.ENGELSE_LAND_BESKRYWING` |
| `group_name_afrikaans` | `country_group_lookup.AFRIKAANSE_LAND_BESKRYWING` |
| `beskrywing_vir_verslag` | Result key `beskrywing_vir_verslag`, which the `SELECT` does not return, so the inserted value is `""` |
| `description_for_reports` | ``DESCRIPTION FOR REPORTS`` |
| `white_or_red` | `WHITE_OR_RED` |
| `type` | `TYPE` |
| `processed` | literal `0` |
| `bo`, `bu`, `bb` | `country_group_lookup` columns `BO`, `BU`, `BB` |

`index.php` updates a subset: `group_name`, `group_name_afrikaans`, `beskrywing_vir_verslag`, `description_for_reports`, `white_or_red`, `type`, `totale_liters`, `year`, `month`, `datumtyd`, `processed`. It does not update `bo`, `bu`, or `bb`.

### `bt_getquestion.pseudo`

Not valid PHP. Identifiers have no `$`. `if` lines omit parentheses. Table names in the text: `student`, `lession_flow` (spelled that way), `lessons`, `levels`. Functions: `getQuestion`, `student_model_currentStstus`, `lession_flow_model`. No overlap with the SAWIS tables.

## Data

No `CREATE TABLE` is in the repository. The column lists above are the names the PHP sends. Types, nullability, primary keys, and indexes are **Unknown**.

Tables named in current or historical SQL:

| Table | Used by current code | Role shown by the SQL |
|---|---|---|
| `mapper_data` | `import.php` insert, `index.php` select and update | Fact rows |
| `country_group_lookup` | `import.php` | Country text, `BO`, `BU`, `BB` |
| `product_type_lookup` | `import.php` | Report description, colour class, type |
| `country_groups` | `index.php` | Older country text (`group_name`, `group_name_afrikaans`) keyed by `kde` |
| `produk_kodes` | `index.php` | Older product text keyed by `kd` |
| `uploaded_file` | Unreachable `math.php` only. Removed from `import.php` in `90cccc9` | Filename and `last_row` |
| `imported_mapper_data` | Not in current code. Insert target at `90cccc9` only | Staging name used for three weeks of history |
| `mapper_data_20230416` | Not in current code. Commented samples removed in `90cccc9` | A dated copy name inside comments |

## Authentication

There is no application authentication.

Database authentication is the `mysqli` constructor. Active credentials are local `root` with an empty password. A second set is commented in `import.php` and includes a password. That password should be treated as exposed in Git history and rotated if the account still exists. It is intentionally absent from this document.

## Background work

No queue, cron definition, Windows Task Scheduler XML, or worker process is in the tree.

The scripts themselves are batch programs an operator starts. `import.php` is built to walk a folder in one process. `index.php` is built to walk a limited number of unprocessed rows in one process. How either is started (CLI `php import.php`, a browser, or a scheduled task outside the repo) is **Unknown**.

## Storage

Source files are expected on the local Windows disk, under a specific user's OneDrive path. The repository does not store extracts. `header.csv` is the only data file committed, and it is a title row.

Database storage is whatever server answers on `localhost` for `sawis_bitool`. Disk, backup, and hosting are **Unknown**.

## Monitoring and logging

| Mechanism | Behaviour |
|---|---|
| Import errors | Concatenate `$conn->error` and `<br>` into `$err_string`. `var_dump` after each file. Success is an empty dump |
| Updater progress | Echo `Starting :` plus the counter, then `UR:{id}<br>` or `Error updating record:` plus the SQL |
| Updater end of data | Echo `0 results :` plus the counter and `exit` |
| Probe | `var_dump` of the first path, then `die` |
| PHP notices | Suppressed in the two batch scripts |
| File log, metrics, health check | None |

`uploaded_file` used to be a per-row log. Current `import.php` increments `$row_count` and never reads it.

## Security notes (from the tree, not a penetration test)

- SQL is built by concatenating strings. `cleanString` is not `mysqli_real_escape_string`. It removes a few characters and leaves the rest. `index.php` concatenates `lnd_kde` and `prd_kde` with no cleaning.
- `datum` and `prod_kode` are interpolated without quotes, so their contents change SQL syntax rather than only the bound value.
- Database user is `root` with an empty password, hard-coded.
- A password for user `silicon` is in a Git commit (`b440d76`) inside a comment.
- There is no authentication in front of the scripts. If a web server maps the directory to PHP, the import runs for anyone who can request the URL, using that database account, and error output includes SQL text (`index.php`).
- `error_reporting` hides notices, including undefined indexes when a lookup misses.
- Absolute paths identify a person and a client folder name on a workstation.

## Development and testing

| Item | Evidence |
|---|---|
| History | Four commits on `master`, 2023-10-25 to 2023-11-18. Authors Jaco Brits and Dewald Stander |
| Branches | `master` only |
| Tests | No test directory, no PHPUnit, no fixture extract |
| `math.php` | Manual probe with `die` and `var_dump`. The commit that expanded it is "amendments to the import script" |
| Local data | Not in Git. The import expects files already sitting in the OneDrive folder |
| CI | No pipeline file in the repository. `sonar-project.properties` and `sonarqube_scan.bat` were listed on the scan host on 2026-09-28 and are not in `b440d76` |

The `.gitignore` is the Bitbucket starter list (Node, Java, Python, logs, JetBrains, media, executables). It does not ignore `*.php` or `*.csv`.

## Environment configuration

All configuration is in source. There is no environment variable, config file, or CLI flag.

| Concern | Value in code |
|---|---|
| Import drop folder | `...\SAWIS\2023_data_file\` |
| Probe folder | `...\SAWIS\Phase 1\Import Script Testing\` |
| Commented older probe folder | `...\SAWIS\mapper_data\` |
| Database | `sawis_bitool` on `localhost` |
| Commented database | `sawis_bi` on `10.0.0.145` |
| Import time limit | 12000 seconds (was 3600 before `b440d76`) |
| Updater time limit | 3600 seconds |
| Memory | `16G` on import and updater |

Changing the folder or the database means editing the PHP file.

## Dependencies that failed or were left unfinished

- `beskrywing_vir_verslag` is written from a column the product query does not select.
- `BO`, `BU`, and `BB` are read even when the country query returns no row.
- The `fgetcsv` loop does not depend on `fopen` succeeding.
- `index.php` and `import.php` encode different lookup tables and a second litre negation. Running both is possible and is not guarded.
- `math.php` cannot complete an import while the `die` on line 30 remains.
- `bt_getquestion.pseudo` does not compile and is not referenced.
- Table DDL is not in the project, so the insert cannot be checked against a schema from this tree alone.
- The task-creation Sonar files are not in Git. If a scan host still has them, they were never committed.

## Historical technical surface

Useful when reading blame, not when describing the current insert.

| Revision | Technical fact |
|---|---|
| `85a0822` | First import resumed via `uploaded_file`, looked up `country_groups` and `produk_kodes`, inserted `mapper_data`, and inserted `uploaded_file` per line. `index.php` already had today's updater. `math.php` was six lines |
| `90cccc9` | Import scans the 2023 folder, uses `country_group_lookup` and `product_type_lookup` (key = column 11, no `TYPE`), rounds then negates `R`, inserts `imported_mapper_data`. Drops the `uploaded_file` write. `math.php` becomes the probe |
| `b440d76` | Current behaviour. Litre formula keeps dots. Lookup key becomes column 39. `TYPE` added. Insert returns to `mapper_data` with non-underscored column names. Execution limit 12000. Commented `sawis_bi` connection added |