# Addendum 76 — MED: Stores, Extended Finance & Devices (Technical Digest)

**Scope:** Second deployment at `/home/medgreennatureco/public_html/med` (live: med.greennature.com.sa), the newer **Center/Hospital** edition of the OB/GYN codebase family. This digest covers the Stores / Lab-Supplies, package offers, contract/insurance pricing, financial & cash-transfer reports, services (detections) management with **live external-ERP sync**, pharmacy dispensing, and three separate device registries.

**Method:** Code-only inference (RedBeanPHP `R::*` calls, raw SQL, Smarty views). Tables cross-checked against the original 312-table dump `obgy/_db/obgy_12-7-2024.sql`. Read-only; no DB access.

Controllers read in full (all under `/med/core/controllers/`):
`invsdept.php` (4061), `invsdept_common.php` (4842), `invsdeptapp.php` (1029), `invsdeptapp_1.php` (628), `invoffers.php` (426), `detection.php` (2297), `financiallab.php` (781), `cashtransferdectorreport.php` (731), `financialreport_old.php` (1686), `financialreport_new_old.php` (1832), `pharmacy.php` (181), `labdevices.php` (361), `adddevices.php` (3038), `vote_devices.php` (169).

---

## 1. Stores / Lab Supplies

- `suppliescats` (categories) → `supplies` (`name`, `catid`) → `invests_supplies` junction (`supply_id`, `supply_no` qty, `usedate`, `deleted`).
- Consumption report: `invsdept.php::suppliesPrint($from,$to)` (lines ~3121-3167) — `SELECT sum(invests_supplies.supply_no) ... WHERE usedate BETWEEN ? AND ? AND deleted=0`, grouped by category/supply.
- Sample typing via `sampletype` (`invests.sampletype` FK), `sampletype.name` joined in `printinvs()`/`searchinvs()`.
- Re-normalized lab results: `investigationresults` + specialized child tables `investigationresults_{blood,culture,urine,stool,semen,cross,pt,esr,egfr,lipid,aborh,custom,times}` replace the legacy `*sheet*` model. Reference ranges in `inv_ranges`; lab config in `programesettinglab` (separate from global `programesetting`).
- Barcode per sample via `createbarcode($statusno)`; `investigationresults.sample_id` used in `searchrecord()`.

## 2. Package Offers — `invoffers.php`

- `invoffer` (`name`, `active`, `del`) 1-M `invofferdetails` (`invoffer_id`, `inv_id`, `price`, `del`).
- Offer price computed dynamically: `SELECT sum(price) FROM invofferdetails WHERE invoffer_id=? AND del=0` (`index()` line 77).
- `addit()`/`updateit()` — update soft-deletes all details (`set del=1`) then re-inserts selected.
- Consumed by visit billing via `investigationresults.offer_id`; price = `contract_price - patient_hold`.

## 3. Contract / Insurance Pricing — `detection.php`

- `price_lists` (`name`, `deleted`).
- `organizations` (`org_edit`/org create ~line 1706): `organization_name`, `org_type`, `parent_id`, `location`, `phone`, `contact_person`, `price_list`/`list_id`, `sales_persons`, `contract_start`, `email`, `vat_no`, `credit_limit`, `user_id`, `create_date`, **`user_name` + `user_password_hash`** (portal login), `deleted`.
- `organizations_parents` (hierarchy), `organizations_patient_no` (`patient_id`, `organization_id`, `patient_no`, `deleted`) — `patientOrgCode()` line 2113.
- `organization_discount` (`listDiscountObj()` line 383): `list_id`, `detect_id`, `detect_cat` (1=detection, 2=invest, 3=ray, 4=offer), `offer_id`, `contract_price`, `patient_hold`, `discount_type`, `deleted`. Joined to detections/invests/rays via `LEFT JOIN organization_discount ON x.id = organization_discount.detect_id` (lines 637/803/969).
- `investigationresults` carries `contract_price`, `patient_hold`, `offer_id` for org/patient split (`visitTests()` line 2120+).

## 4. Detections + Live ERP Sync — `detection.php` (KEY FINDING)

- Service catalog `detections` (`title`, `detectionval`, `create_date`, `del`).
- `erpProduct($detection)` (line 169): if `programesetting->erpdb` is set, `R::addDatabase('erpDB', mysql:host=DB_HOST;dbname={erpdb}, DB_USER, DB_PASS)`, `R::selectDatabase('erpDB')`, then `SELECT productId FROM product WHERE obygyDetectionId = {detection.id}`.
  - Not found → `curlAddProduct()` → POST to `HOST_URL_ERP/controllers/buyBillControllerAjax.php?do=temp...` (category fixed `Services`, `isservice=1`).
  - Found → `curlupdateProduct()` → POST to `HOST_URL_ERP/controllers/productControllerAjax.php?do=update`.
  - Delete → `erpProductDel()`/`curlDelProduct()`.
  - Restores `R::selectDatabase('default')`.
- Implication: `detections` is source-of-truth; ERP `product` catalog is mirrored via cURL with a stable `obygyDetectionId ↔ productId` link. **External ERP DB (`product`) is NOT part of the 312-table OB/GYN schema.**

## 5. Financial / Cash-Transfer Reports — `cashtransferdectorreport.php`, `financiallab.php`

- Unified report = `visits` UNION ALL `bill_paying` (pharmacy payments). See `visitSql()` + `billsPayingSql()`.
- `visits` financial columns: `detectionvalue_cash`, `detectionvalue_visa`, `totaldetectionvalue`, `restdetectionvalue`, `discount`, `detectionid`, `dr_salary`, `refer_doctor`, `refer_doctor_tb`, `contract_price`, `branch_id`, `printserial`, `customer_add`, `for_department`, `for_doctor`, `rfc`.
- Magic `detectionid` values: `999` = remaining payment ("دفع متبقي"), `9999` = customer refund ("مرتجع العملاء").
- Visibility gated by `awrole.financial_visits` (1=dept-scoped, 2=doctor-scoped), `awusers.financial_user`, `awusers.cashtransferdoctor_user`.
- `bill_paying`: `type` (1=refund), `user_id`, `patient_id`, `pay_date`, `discount`, `printserial`, `branch_id`, `notes`.
- Aggregation grouped by `branch_id` + referring doctor (`refer`); detail expanded from `investigationresults`/`raysresults`; booking time from `visit_hours`.
- `financialreport_old.php` = legacy variant using `awcontroll/awmenu/awrole*` permission tables; adds no new core entity.

## 6. Pharmacy — `pharmacy.php`

- `prescription` search by date/doctor/patient. Columns: `patient_id`, `doctor_id`, `pres_date`, `for_husband`, `prepared`, `delivered` (+ `prepared_userid`/`delivered_userid` set in `updatePre()`).

## 7. Device Registries

- **Lab devices** (`labdevices.php`): `lab_devices` (`completed`, `user_id`, `deleted`, `delete_userid`, `delete_date`) + `lab_devices_ranges` per-device ranges copied from `inv_ranges` (`device_id`, `investid`, `special_inv_type`, `invest_child`, `range_txt`, `agefrom`, `ageto`, `age_units`, `gender`, `range_low`, `range_high`, `invest_units`, `deleted`).
- **Kiosk/vote devices** (`vote_devices.php`): `vote_devices` (`name`, `local_ip`, `created_at`, `updated_at`); blocks delete if referenced by `votes.vote_device_id`.
- **Room medical devices** (`adddevices.php`): `devices` (`device_name`, `location`, `deleted`) on `floors`/`locations`/`operations_rooms`. NOTE `devices`, `floors`, `locations` already exist in the 312 dump; `operations_rooms` is NEW.

## 8. External Labs & Records Archive — `adddevices.php`

- `external_labs` (`name`, `deleted`); dispatch tracked on `investigationresults.external_lab` + `receive_userid`/`receive_date` (`receiveExternalTest()` line 593) + `delivered_userid`/`delivered_date` (`deliverExternalTest()` line 623).
- `dna_results` (paternity): `receivername`, `delivered_userid`/`date`.
- `archive_request` (records archive workflow, line 1267): `patient_id`, `doctor_id`, `visit_id`, `branch_id`, four-stage audit `request/ready/receive/return _userid`+`_date`, `request_note`, `receive_note`, `request_note_userid`, `receive_note_userid`, `cancel_userid`, `deleted`.
- `manual_operation` (manual ops, `del()` line 415).

---

## NEW tables (not in original 312)

`supplies`, `suppliescats`, `invests_supplies`, `sampletype`, `investigationresults` (+ `_blood/_culture/_urine/_stool/_semen/_cross/_pt/_esr/_egfr/_lipid/_aborh/_custom/_times`), `inv_ranges`, `programesettinglab`, `invoffer`, `invofferdetails`, `price_lists`, `organizations`, `organizations_parents`, `organizations_patient_no`, `organization_discount`, `sales_persons`, `bill_paying`, `prescription`, `visit_hours`, `lab_devices`, `lab_devices_ranges`, `vote_devices`, `votes`, `external_labs`, `dna_results`, `archive_request`, `operations_rooms`, `manual_operation`, `rays`, `raysresults`, `refer`, `patients_childs`. External ERP DB: `product`.

**KNOWN (already in 312):** `detections`, `devices`, `floors`, `locations`, `investcats`, `invests`, `semen`, `hsg`.

## ERP/HIS plan impacts

1. Live ERP integration exists (cURL + `obygyDetectionId↔product.productId`) — replace direct ERP-DB writes with a clean API in the target.
2. Contract/insurance model (`organizations`+`price_lists`+`organization_discount`+VAT/credit_limit) = ready foundation for a standalone Contracts/Insurance ERP module.
3. Income is split across `visits`+`bill_paying` via UNION with magic `detectionid` 999/9999 — build a unified financial-ledger table instead.
4. `supplies`/`invests_supplies` model consumption only (no stock balances/suppliers) — add a full inventory layer.
5. `investigationresults*`+`inv_ranges` replaced legacy `*sheet*` — update lab table maps in the 312 report.
6. Three device registries (`lab_devices`/`vote_devices`/`devices`) should unify into one asset registry.
7. `archive_request` + external-lab dispatch are reusable workflow/state-machine templates.
8. New permission flags (`financial_visits`, `financial_user`, `cashtransferdoctor_user`, `external_lab`) on `awrole`/`awusers` need a proper role model.