# Business rules — bi_tool_php

Evidence date: 2026-10-19. Revision: `master` `b440d76`.

Rules below are taken from the PHP that runs today, or from a behaviour that Git history shows was removed. Each rule is marked **Documented** or **Inferred**. There is no separate business-rules document in the source repository. Nothing in this file is **Documented** from a specification. An inferred rule is what the code does. It is not a confirmed business decision.

`index.php` and `import.php` do not implement the same litre rule or the same lookup tables. Both are listed. They are not merged into one rule.

## Source files and rows

| ID | Rule | Source | Label |
|---|---|---|---|
| BR-001 | The import reads every directory entry in the hard-coded `2023_data_file` folder except the names `.` and `..` | `import.php` | Inferred |
| BR-002 | The first line of each file is skipped. Later lines are data | `import.php` (`$start_counter = 1`, then `fgets`) | Inferred |
| BR-003 | A data line is split on the pipe character after `fgetcsv` has read it with a newline delimiter and a maximum length of 1000 | `import.php` | Inferred |
| BR-004 | Column positions follow the 43 titles in `header.csv`, from `DATUM` at index 0 through `ClassName` at index 42. The import does not open `header.csv` | `header.csv`, insert list in `import.php` | Inferred. The semicolon delimiter of the dictionary versus the pipe delimiter of the data is unexplained |
| BR-005 | Running the import again inserts more rows. The script has no "already loaded" check and no longer writes `uploaded_file` | `import.php` at `b440d76`. The progress insert existed before `90cccc9` | Inferred |

## Litres and the RK flag

| ID | Rule | Source | Label |
|---|---|---|---|
| BR-006 | On import, a comma in the litre field is a decimal point. Full stops are not removed | `import.php` at `b440d76` | Inferred |
| BR-007 | On import, when column 21 is exactly the character `R`, the litre value is rounded to 0 decimal places and then multiplied by `-1`. The comparison uses the raw field, before `cleanString` | `import.php` | Inferred. That `R` means a commercial return is **Inferred** and **Requires Confirmation** |
| BR-008 | On import, a line whose column 21 is not exactly `R` keeps the unrounded float from BR-006 | `import.php` | Inferred |
| BR-009 | The updater (`index.php`) multiplies the stored litre value by `-1` when `rk` is exactly `R`, and does not round | `index.php` | Inferred |
| BR-010 | Before 18 November 2023 the import deleted every `.` from the litre field and then turned `,` into `.`. That formula is no longer in `import.php`. It remains in the unreachable section of `math.php` | Commit `b440d76`, `math.php` | Inferred historical rule |
| BR-011 | Before 1 November 2023 an `R` line was negated without rounding | `import.php` parent of `90cccc9` | Inferred historical rule |

BR-007 and BR-009 together mean a row imported as a return and then passed through `index.php` is negated twice. Provenance: **Inferred** from reading both files. Whether that sequence is used is **Unknown / Requires Confirmation**.

## Lookups and stored text

| ID | Rule | Source | Label |
|---|---|---|---|
| BR-012 | Import country text comes from `country_group_lookup` matched on `lnd_kde` = cleaned column 4 (`LND_KDE`) | `import.php` | Inferred |
| BR-013 | The English description is stored in `group_name`. The Afrikaans description is stored in `group_name_afrikaans`. A missing key becomes an empty string | `import.php` at `b440d76`. The 1 November version assigned the Afrikaans description into the English variable | Inferred |
| BR-014 | `BO`, `BU`, and `BB` are copied from the country lookup onto the inserted row | `import.php` | Inferred. Business meaning **Unknown / Requires Confirmation** |
| BR-015 | Import product text comes from `product_type_lookup` matched on `prd_kde` = column 39 (`NPR_KDE`) with spaces removed | `import.php` at `b440d76` | Inferred. The match key changed from column 11 on this date. **Requires Confirmation** |
| BR-016 | The import stores ``DESCRIPTION FOR REPORTS`` as `description_for_reports`, plus `WHITE_OR_RED` and `TYPE`. It does not select an Afrikaans product description, and the insert writes an empty `beskrywing_vir_verslag` | `import.php` | Inferred |
| BR-017 | The updater reads `country_groups` on `kde` and `produk_kodes` on `kd` equal to the row's `prd_kde` (the stored column 11), not `NPR_KDE` | `index.php` | Inferred |
| BR-018 | The updater can fill `beskrywing_vir_verslag` and `description_for_reports` from `produk_kodes`, and it overwrites `group_name` and `group_name_afrikaans` | `index.php` | Inferred |
| BR-019 | Most stored strings are trimmed. The exact values `9:00 am` and `3:00 am` become `9A` and `3A`. Quotes, `&`, `$`, parentheses, and backticks are removed. A backslash becomes a space | `import.php` `cleanString` | Inferred. Meaning of `9A` and `3A` is **Unknown / Requires Confirmation** |
| BR-020 | `datum` (column 0) and `prod_kode` (column 2) are placed into the `INSERT` without surrounding quotes. Other source fields are quoted after `cleanString` | `import.php` | Inferred |

## Processing flag and batch size

| ID | Rule | Source | Label |
|---|---|---|---|
| BR-021 | Every row inserted by the current import is stored with `processed = 0` | `import.php` | Inferred |
| BR-022 | The updater treats `processed = 0` as the set of rows still to update, and sets `processed = 1` after its own update | `index.php` | Inferred |
| BR-023 | The updater's counter starts at 11 and runs while it is less than or equal to 21. Each select's limit is counter × 500. A select that returns no rows ends the run | `index.php` | Inferred. Why the counter starts at 11 is **Unknown / Requires Confirmation** |
| BR-024 | One updater run therefore issues at most 11 selects. If each batch is full, that is 88000 rows. Further unprocessed rows stay unprocessed until another run | `index.php` limits 5500 through 10500 | Inferred |

BR-021 and BR-022 disagree about what `0` means once `import.php` has already copied lookup text. Provenance: **Implemented** in each file. The intended flag meaning is **Requires Confirmation**.

## Dates

| ID | Rule | Source | Label |
|---|---|---|---|
| BR-025 | `year`, `month`, and `datumtyd` are derived with `strtotime` from the date field. `datumtyd` is formatted `Y-m-d H:i:s`. `month` has no leading-zero suppression beyond PHP's `m` (01–12) | `import.php` and `index.php` | Inferred |
| BR-026 | The import also stores the raw date field in `datum`. The updater does not change `datum` | Both files | Inferred |

The accepted source format of `DATUM` is **Unknown / Requires Confirmation**.

## Access

| ID | Rule | Source | Label |
|---|---|---|---|
| BR-027 | The scripts open MySQL as `root` with an empty password on `localhost`, database `sawis_bitool`. There is no application user or role | All three PHP files | Inferred |
| BR-028 | A commented connection in `import.php` names host `10.0.0.145`, user `silicon`, and database `sawis_bi`. It is not executed. The password in the comment is omitted here | `import.php` | Inferred historical target |
| BR-029 | No script checks an operator identity, a file checksum, or a tenant | All PHP files | Inferred |

## Student question flow (pseudocode only)

| ID | Rule | Source | Label |
|---|---|---|---|
| BR-030 | If `getQuestion` is called with answer `-1`, the next step is the student's current question and the following item in `lession_flow` | `bt_getquestion.pseudo` | Inferred from pseudocode. Not implemented. Scope **Unknown / Requires Confirmation** |
| BR-031 | When a lesson has no later question, the text moves to the next lesson by `order + 1`, and when a level has no later lesson, to the next level | `bt_getquestion.pseudo` | Inferred from pseudocode. Not implemented |
| BR-032 | The branch for an answer other than `-1` contains no statements | `bt_getquestion.pseudo` | Inferred from pseudocode |

These three rules are not connected to `mapper_data` or to SAWIS column names.

## Removed rules (history only)

| ID | Rule | Source | Label |
|---|---|---|---|
| BR-033 | The import resumed a file at `uploaded_file.last_row` for that filename | `import.php` before `90cccc9` | Inferred historical |
| BR-034 | Each imported line also inserted `filename`, `last_row`, litre figures, `lnd_kde`, year, month, and `prd_kde` into `uploaded_file` | `import.php` before `90cccc9` | Inferred historical |
| BR-035 | For a short period after 1 November 2023, inserts went to `imported_mapper_data` rather than `mapper_data` | `import.php` at `90cccc9`, reverted as a target in `b440d76` | Inferred historical |

## Unknown / requires confirmation

- Is `R` a return, a credit, or another code, and should the test ignore surrounding spaces.
- Is rounding only on `R` rows an approved rule, or a side effect of the 18 November fix.
- Should the product lookup use `NPR_KDE` or `PRD_KDE`.
- What `BO`, `BU`, `BB`, `9A`, and `3A` mean.
- What `processed` should be after an import that already enriches the row.
- Whether `index.php` is still an approved step.
- Whether a file may be imported more than once.
- The legal or reporting definition of total litres (including bulk versus bottles). The code only transforms column 12.