# Addendum 74 — MED: Branches, Regions & ERP Sync

**Scope:** Newer "center/hospital" edition discovered at `/home/medgreennatureco/public_html/med` (live `med.greennature.com.sa`). This digest covers the multi-branch / multi-region core, the Organizations (contracted parties) layer, the `syncstructure` DB-migration tool, the Moon-ERP bridge (`erp_common`), and the external `api_web` API. Read-only analysis; schema inferred from RedBeanPHP/raw SQL only. Cross-checked against the original 312-table dump `/home/amrtechogate/public_html/obgy/_db/obgy_12-7-2024.sql`.

## Files analyzed
- `core/controllers/beauty_branches.php` — Branch CRUD on `branches`.
- `core/controllers/branch_common.php` — Branch permission helpers (`branchInfos`, `branchInfosVisit`, `showBranchCol`).
- `core/controllers/regions.php` — `regions` / `sub_regions` tree CRUD.
- `core/controllers/center_reports.php` — Temporary-cases & "Center's Knowledge" cross-branch reports.
- `core/controllers/commons.php`, `fn_common.php` — Generic update/delete and helper functions.
- `core/controllers/erp_common.php` — Moon-ERP bridge + NasLab integration + PDF.
- `core/controllers/syncstructure.php` — Old↔new DB structure sync / DBA maintenance.
- `core/controllers/api_web.php` — External JSON API (lab-device results + organizations gateway).
- `core/controllers/excelread.php` — One-shot Excel patient import.

---

## 1. Multi-branch / multi-region core

Activation flags live in `programesetting`:
- `branches` — **already existed** in original dump (`programesetting` DDL line in dump contains `erpdb`, `erpdbsave`, `branches`).
- `regions`, `organizations` — **NEW columns** (set in `programesetting.php:316-317,237`; absent from dump).

Per-user branch scoping uses the **NEW column** `awusers.related_branches` (CSV of branch IDs). Logic centralized in `branch_common.php`:
- `branchInfos($branchesActivate, $smarty)` (lines 6-49) — report screens.
- `branchInfosVisit(...)` (51-89) — visit/entry screens (also assigns `allBranches`).
- `showBranchCol(...)` (91-113) — whether to render the branch column.

Inferred rule (from `branch_common.php:16-40`): `related_branches == 0/empty` → all branches; single value → locked to one branch (no selector); multiple → selector restricted via `R::findAll('branches', 'id IN (...)')`.

Report filtering helpers `branchSql()` / `billBranchSql()` (`financialreport.php:116-130`, also in `financiallab.php`, `cashtransferdectorreport.php`) append `and visits.branch_id = ?` / `and bill_paying.branch_id = ?`; sentinel `-1` = all branches.

`beauty_branches.php` — full CRUD on `branches`. **`branches` is KNOWN** (dump line 4549) with original cols `id, name, created_at, updated_at`; this edition writes the **NEW columns** `phone1, phone2, location` (`beauty_branches.php:74-76`). Optional push to external site `royal-fc.net/appv/visit_details/branches` via `sendToSite()` (currently commented out).

### Regions hierarchy
`regions.php`: `regions` (parent) → `sub_regions` (`sub_regions.region_id`, `regions.php:80`). Generic inline-edit via `R::xdispense($tableName)` where the table name comes from POST (`addRow()` line 101; `update/updateajax/del` operate on arbitrary `tableName`). **Both tables NEW.**

## 2. Organizations (contracted parties / insurers)

NEW entity. Linked to a visit via **NEW column** `visits.organization_id` (`visits.php:768,919,960`). Per-organization patient number stored in junction `organizations_patient_no` (`patients.php:1743,4135`). Activation `programesetting.organizations` has 3 inferred levels (1/2/3) gating org binding & debt disclosure (`visits.php:919,941`).

External gateway in `api_web.php`:
- `ac=orglogin` (632) → `checkUserLogin()`; `password_verify` against `organizations.user_password_hash`; returns `org_id`, `org_id` (org number), `organization_name`.
- `ac=orgpatientinfo` (681) → `checkPatientVisits()`; joins `visits.organization_id` + `organizations_patient_no` (`organization_id`, `patient_id`, `patient_no`); counts delivered results.

## 3. syncstructure — DB structure migration tool

`syncstructure.php` is a DBA one-shot, not a live sync. `sync()` adds `R::addDatabase("old"/"new")` and iterates new-DB tables from `information_schema.tables`:
- `checkTableExist()` → if present, `compareTables()` diffs columns via `information_schema.columns`; on diff, `copyTable()` builds `{table}_temp LIKE new.{table}`, inserts old rows by shared columns, then `DROP`+`RENAME`.
- If missing, `createTable()` runs `SHOW CREATE TABLE` DDL on the old DB.

**Operational hazard:** `sync()` is gated by `if ($i == 203)` (line 120) — limits execution to one table (manual debug residue). Must be removed before real use.

Hardcoded DB/environment names reveal multiple deployments (inference): `dr_app_old`, `obgy_app` (`check_missing`), `obor_db` (`check_auto_increment`), `alielhad_obgy` (`improve`). Maintenance methods: `check_missing`, `check_auto_increment`, `set_auto_increment`, `calcBalanceAll` (recompute `patients.balance` from `visits`), `improve` (copy specific columns between DBs).

## 4. Moon-ERP bridge — erp_common

`orderFromErp($erpApiLink, $data=0)` (`erp_common.php:3`) cURLs `localhost/erp/controllers/api_web.php?do={endpoint}` with header `Open-Key: {hardcoded JWT}`. **The only active call** is client-debt retrieval:
- `visits.php:3120` `getrestvaluesP()` branches on `programesetting.erpdb`: if set → `getrestvaluesPErp()` → `orderFromErp('clientdept&id=0&royalid='.$patientId)` (line 3137); else local debt calc from `visits` (`getrestvaluesPRoyal`).
- Target ERP is **Moon ERP** (inference from `localhost/erp/...` path and `clientdept` endpoint).

Also in `erp_common.php`: NasLab integration `getPatientLink()` → `api.naslab.gt4it.com/api/patient`, stores result URL in **NEW column** `patients.inv_link` (line 90); TCPDF `generatePdf()`; file upload `sendFileWithData()`. JWT tokens are hardcoded (security risk).

## 5. api_web — external lab-device API

`api_web.php` (JWT auth currently commented out, lines 12-39). Endpoints:
- `ac=databysample` → `dataBySampleId()` joins `investigationresults` + `invests` + **NEW** `lab_devices_ranges` (`investid↔device_id`, `send_code/receive_code`); returns tests + patient demographics.
- `ac=saveresults` / `ac=saveresultsMulti` → `saveResultsCommon()` logs every call to **NEW** `saveresultslog` (cols: `sample_id, device_id, device_name, result, receiving_code, sending_code, lab_devices_ranges_id, investigationresults_id, element_name, element_table_name, element_table_id, create_date`). `invTypeSwitch()` routes by `special_inv_type` (16 types) to NEW per-type tables `investigationresults_blood/urine/stool/culture/esr/cross/times/pt/aborh/custom/lipid/egfr/semen`. Derived calcs: `lipidCalculations()`, `egfrCalculations()`.
- `ac=orglogin` / `ac=orgpatientinfo` → organizations gateway (section 2).

## 6. excelread — one-shot import

`excelread.php` reads `excel_backups/newcases.xlsx` via PHPExcel, dispenses `patients`, auto-creates doctors in `awusers` (`checkDrTable`/`addToDrTable`) and `nationality` rows (`checkNatTable`). Hardcoded `row <= 8789`. Migration script, not an operational feature.

---

## Entity classification (vs original 312-table dump)

| Table / Column | Status | Notes |
|---|---|---|
| `branches` | KNOWN (dump:4549) | extended with NEW cols `phone1, phone2, location` |
| `regions` | NEW | `id, name, deleted` |
| `sub_regions` | NEW | `region_id → regions.id` |
| `organizations` | NEW | `org_id, organization_name, user_name, user_password_hash` |
| `organizations_patient_no` | NEW | junction `organization_id, patient_id, patient_no` |
| `lab_devices_ranges` | NEW | `investid, device_id, invest_child, special_inv_type, send_code, receive_code` |
| `saveresultslog` | NEW | device-result audit log |
| `investigationresults_*` (13 types) | NEW | per-type lab result detail |
| `bill_paying` | NEW | payment vouchers (`branch_id`); not in dump |
| `knownfrom` | NEW | patient-acquisition sources |
| `nationality` | NEW | auto-created on import |
| `awusers.related_branches` | NEW column | branch scoping CSV |
| `awrole.custom_detections`, `awrole.is_doctor` | NEW columns | per-dept tests + doctor flag |
| `programesetting.regions`, `programesetting.organizations` | NEW columns | center-mode flags |
| `programesetting.branches`, `erpdb`, `erpdbsave` | KNOWN columns | already in dump |
| `visits.organization_id` | NEW column | org link (`visits.branch_id` was KNOWN, dump line 33 of DDL) |
| `patients.branch_id`, `patients.inv_link` | NEW columns | branch + NasLab URL |

## ERP / HIS plan impact
1. Original 312-table model is incomplete: ≥7 new entities + ≥5 new columns on known tables must extend the Modules/Obgy migration blueprint to cover the multi-branch center layer.
2. Multi-tenant/branch dimension is mandatory in the HIS: branch/region/organization as first-class dimensions on visits, patients, billing; permission model built on `related_branches`.
3. Organizations = the insurance/corporate module: model "patient number per org" and external API gateway; ties directly to billing & debt.
4. Moon-ERP financial integration already exists (`clientdept`): the ERP should be the financial source of truth, not local calc; unify auth and remove hardcoded tokens.
5. Lab-device bridge (`lab_devices_ranges` + `saveresultslog` + `investigationresults_*`) is a full bidirectional LIS unit; adopt as a standalone HIS module.
6. Security debt: hardcoded JWTs in `erp_common.php`/`api_web.php`, commented-out API auth, explicit DB/env names in `syncstructure.php` — move to env vars and enable auth before production.
7. One-shot migration tools (`syncstructure`, `excelread`) carry hand-edited limiters (`if($i==203)`, `row<=8789`) and must not ship in the production HIS codebase.