# Addendum 70 — MED: Inpatient & OR Management

**Source deployment:** `/home/medgreennatureco/public_html/med` (live: med.greennature.com.sa) — newer CENTER/HOSPITAL edition.
**Baseline:** original 312-table dump `/home/amrtechogate/public_html/obgy/_db/obgy_12-7-2024.sql`.
**Method:** read controllers fully; tables/fields inferred from RedBeanPHP (`R::dispense`/`xdispense`/`findAll`/`findOne`/`getAll`) and raw SQL. Read-only; no DB queries.

Controllers in scope (all under `core/controllers/`): `operations_rooms.php`, `residence_rooms.php`, `operation_types.php`, `operations_reserve.php`, `operations_reserve00.php`, `operations_calendar.php`, `operations_reports.php`, `operationreport.php`, `roomsreport.php`, `today_list.php`. Smarty views under `core/views/obgy/`.

---

## 1. Architecture: TWO parallel booking engines

There are two independent reservation engines writing to different tables for the same conceptual "operation booking":

### A. Hourly engine (older) — `operations_reserve.php` / `operations_reserve00.php`
- Day split into 30-min (OR) / 60-min (residence) slots.
- `reserve()` (operations_reserve.php:292-348) creates a header row in **`operations_main`**, then ONE row per hour in **`doctors_reserves`** (OR) and **`residence_reserves`** (residence), all linked via `operation_main_id`.
- `operations_reserve00.php` is the prior variant: room set filtered by `detections.operations_rooms` (CSV of room IDs or `-1`=all), slot grid bounded by room `start_time`/`end_time`. The newer `operations_reserve.php::search()` (lines 182-228) replaced this with a per-floor view querying `operations_rooms`/`residence_rooms` and overlaying `doctors_reserves`/`residence_reserves`.

### B. Calendar engine (newer) — `operations_calendar.php`
- FullCalendar with room resources. ONE row per operation in **`operations_rooms_cal`** holding BOTH the OR slot (`room_id`,`start_time`,`end_time`) and the residence slot (`res_room_id`,`res_start_time`,`res_end_time`).
- `events()` (lines 111-235) colors by floor/VIP and detects conflicts by self-join overlap test (`start_time < end AND end_time > start AND room_id = ? AND id != ?`).
- `saveOperation()`/`updateOperation()` write the single row; `getOperationTime()` (line 306) auto-fills duration/cost/instructions from the operation type.

**Implication:** two unsynchronized sources of truth for the same domain concept.

---

## 2. Critical schema finding — extensions on EXISTING tables

Verified against the dump (`grep`/`sed` on the CREATE statements):

- **`detections`** in the original dump has only `id, title, detectionval, del` (dump line 4791+). The MED edition reuses it as the **operation-types** catalog by adding (inferred from `operation_types.php::add()` lines 116-127): `for_operation`, `operation_time`, `residence_time`, `operations_rooms` (CSV of OR ids or `-1`), `cost_details`, `instructions`, `sort`. Selected via `del != 1 and for_operation = 1`.
- **`visits`** in the original dump (line 11469+) has NO inpatient/OR columns. The MED edition adds (inferred from usage across `roomsreport.php`, `operationreport.php`, `today_list.php`): `for_department`, `for_doctor`, `for_husband`, `room_no`, `res_room_no`, `bed_no`, `enterance`, `p_exit`, `res_enterance`, `res_enterance_date`, `res_p_exit`, `recep_enterance`, `recep_p_exit`, `operation_attend`, `operation_result`, `operation_time`, `op_card_add`, `rec_card_add`, `today_list`, `today_list_dept`, `today_list_sort`, `in_clinic`, `clinic_entered`, `child`.
- Operations department = magic constant `for_department = 13`; operation doctors via `awrole(id=13).related_dr_depts` (CSV of role ids).

---

## 3. NEW tables (absent from 312-table dump)

| Table | Created in | Purpose / key fields |
|---|---|---|
| `operations_rooms` | operations_rooms.php:113 | OR rooms. `name, start_time, end_time, floor_no, branch_id, create_date, deleted` |
| `residence_rooms` | residence_rooms.php:105 | Inpatient/residence rooms. `name, start_time, end_time, floor_no, create_date, deleted` |
| `operations_rooms_cal` | operations_calendar.php:260 | Calendar-engine operation row (OR+residence combined). See §4 |
| `operations_main` | operations_reserve.php:306 | Hourly-engine booking header. `doctor_id, the_date, operation_types_id, operation_hours, residence_hours, operations_rooms_id, residence_rooms_id, create_date` |
| `doctors_reserves` | operations_reserve.php:323 | Per-hour OR slot. `doctor_id, operations_room_id, hour_text, the_date, operation_type, operation_main_id` |
| `residence_reserves` | operations_reserve.php:339 | Per-hour residence slot. `doctor_id, residence_room_id, hour_text, the_date, operation_type, operation_main_id` |
| `operation_data` | operationreport.php:738 | Per-visit operation "board" (anaesthesia/nursing). `visit_id, deleted` (+ staff cols, inferred) |
| `clinic_rooms` | today_list.php:93 | Clinic rooms for today's-list department routing. `today_list, id` |
| `clinic_reserves` | today_list.php:275 | Clinic on-call doctor schedule. `room_id, reserve_day_no, reserve_hour, doctor_id, is_active, deleted` |

`operations_rooms_cal` full field set (operations_calendar.php:260-281, operations_reports.php usage):
`room_id, res_room_id, patient_id, patient_name, doctor_id, assistant_id, operation_type_id, date, start_time, end_time, res_start_time, res_end_time, cost, cost_details, instructions, for_husband, vip, notes, contacted_patient, contacted_user_id, sms_message, created_by, updated_by, created_at, updated_at`.

Note: `patients_childs`, `investigationresults`, `raysresults_img` are also referenced (operationreport.php) and are NOT in the dump, but they belong to the patient/lab/rays domains rather than inpatient; flagged here for cross-domain reconciliation.

---

## 4. Workflows traced end-to-end

**Calendar booking:** `index()` → `rooms()`/`residenceRooms()` (JSON resources) → `events()` (read+color+conflict) → `searchPatients()` → `saveOperation()` (single `operations_rooms_cal` row) → `getOperation()`/`updateOperation()`/`removeOperation()`.

**Hourly booking:** `index()` → `search()` (OR slot grid vs `doctors_reserves`) → `residence()` (residence grid vs `residence_reserves`) → `reserve()` (writes `operations_main` + N×`doctors_reserves` + N×`residence_reserves`) → `removeRoom()`.

**Live residence occupancy** (`roomsreport.php::index()` lines 57-174): joins `residence_rooms` to current `visits` where `for_department=13 AND res_enterance_date IS NOT NULL AND res_p_exit IS NULL`; enriches with patient/doctor/operation-type and entry/exit times. `show()` filters by `floor_no`.

**Live OR occupancy** (`operationreport.php::operation_data()` lines 552-636): per-floor OR rooms, marks room busy if a `visits` row's `enterance<=now<=p_exit` today; auto-refresh 300s.

**Today's list** (`today_list.php`): patients of the day grouped by `clinic_rooms`, drag-and-drop ordering (`today_list_sort`), current-in-room tracking (`in_clinic`/`clinic_entered`), on-call doctor from `clinic_reserves` by weekday/hour.

**Operations reports** (`operations_reports.php`): `index()` OR grid floors [8,6]; `resReport()` residence grid floors [8,7,6]; `listReport()`/`opReport()` grouped by operation type + doctor (raw SQL join `operations_rooms_cal`⨝`detections`); `supportReport()` + `contactedPatient()`/`sendSms()` patient-contact follow-up.

---

## 5. Pricing

Loaded from the operation type: `detections.detectionval` (cost), `cost_details` (free text), `operation_time`/`residence_time` (durations). On calendar save these are copied into `operations_rooms_cal.cost`/`cost_details` (operations_calendar.php:273-274). No independent billing engine in this module — pricing is descriptive and type-bound (inference).

---

## 6. Security / tech debt (must address before migration)

- **SQL injection:** pervasive string-concatenation of inputs into `WHERE` (e.g. operations_reserve.php:227 `role_id in ( $related_dr_depts )`; operationreport.php:390 `operations_rooms_id in (...)`; operations_reports.php:359 `doctor_id = $doctorId`).
- **Hardcoded secret:** smsmisr gateway password in `operations_reports.php::sendSmsMisr()` lines 302-303 — rotate + move to env.
- **Auth bypass:** `operationreport.php::operation_details()` line 644 sets `$_SESSION['user_id']=123` and disables permission checks.
- **Magic constants:** `for_department=13`, `awrole id=13`, floors 6/7/8, `detections.id in (55,152,217,228)` (operations_reports.php:364).
- **Hard deletes:** calendar/hourly removals use `R::trash` (no soft-delete audit), unlike the `deleted` flag used elsewhere.

---

## 7. ERP/HIS roadmap impact

1. This module is the ready seed for the HIS **Inpatient/Bed Management (IPD)** unit — but requires first-class Room/Bed/Admission/OR-Slot entities instead of overloading `visits`/`detections`.
2. Unify the two booking engines into one Reservation entity with explicit status and DB-level conflict prevention.
3. Promote "operation type" out of `detections` into a dedicated `OperationType` with a real pricing/billing link.
4. Model residence/bed occupancy as an explicit time-bounded admission record, not `res_enterance_date`/`res_p_exit` columns on the visit.
5. Add an OR staff entity (surgeon/assistant/anaesthetist/nurse) — embryonic in `operation_data` + `assistant_id`.
6. Today's-list + live occupancy prove the need for a real-time bed/room occupancy board with patient location tracking.
7. Security remediation (SQLi, exposed secret, auth-bypass endpoints) is an acceptance gate.