# Addendum 71 — MED: Embryology Lab & Cryo (Full IVF Wet-Lab)

**Deployment analyzed:** `/home/medgreennatureco/public_html/med` (live `med.greennature.com.sa`), a newer CENTER/HOSPITAL edition.
**Baseline:** original 312-table dump `/home/amrtechogate/public_html/obgy/_db/obgy_12-7-2024.sql`. Every table named below was cross-checked against that list — **all embryology/cryo tables are NEW**. The original only had a flat `ivfsheet` plus `semen`/`icsisemen`; it had **no wet-lab workflow tables at all**.

## Controllers (read in full)
- `core/controllers/tanks.php`
- `core/controllers/embryoslab.php` (~1670 lines)
- `core/controllers/embryofreezing.php`
- `core/controllers/spermfreezing.php`
- `core/controllers/ovumpickupembryotrans.php`
- `core/controllers/embryology.php`
- `core/controllers/ivf_reports.php` (print/aggregation; read-only)

---

## 1. Cryo Tank Inventory — two parallel models

Selected by `programesetting.tanks_view` (1 or 2); per-user toggle `awusers.tanks_settings`.

### Model A — pre-generated 3-level grid (`tanks` / `tankcells`)
`tanks.php::add()` dispenses `tanks` (`name`, `tank_type`) then nested loops generate every cell up front into `tankcells`:

| tank_type | up_level_name | cell_name | cell_part_name | generated | UI total |
|---|---|---|---|---|---|
| 1 | 1..10 | 1..20 | 1..5 | 1000 | 20 |
| 2 | 1..10 | 1..20 | 1..5 | 1000 | 100 |
| 3 | 1..10 | 1..30 | 1..10 | 3000 | 300 |

`tankcells` fields (inferred): `tank_id, up_level_name, cell_name, cell_part_name, patient_id (0=empty), created_at`.
Addressing = **Level (cane) → Canister → Position**. Display aggregation in `cells()` / `tankLabCells()` rolls up occupancy across the 3 levels (`tanks/cells.html`, `tanks/lab_cells.html.tpl`).

Reservation: `saveTankLabCell()` sets `tankcells.patient_id` then writes a custody row. Removal: `removePatientCell()` resets `patient_id=0` and writes a custody row.

### Model B — colour-coded fixed positions (`tanks_general` / `embryoslab_tank_cells`)
Used when `tanks_view == 1`. `tankLocationSetting()` seeds `tanks_general` with `location IN ('A','B')` × `btn_no 1..21`. Each `tanks_general` row holds 9 colour slots `row1_color..row9_color` (FK to `colors.color_name`/`color_letter`) and `rows_letter`.
`storeTankCell()` writes `embryoslab_tank_cells`: `embryoslab_id, location, btn_no, row_no, col_no, cell_no, tank_cell (composite string "A_3_2_1_4"), user_id, date_add, userid_edit, date_edit, deleted`.
Conflict check (`saveTankLocation()`) blocks a cell already held by another `embryoslab_id` ("reserved to another Patient").

`embryoslab.php` + `spermfreezing.php` both contain identical `loadCellsDisplay()` / `tankStyle2()` helpers that join `tankcells` → `tanks` filtered by `patient_id`.

## 2. Chain of Custody & Witnessing
- **`tankcellhistory`** (`tanks.php::removePatientCell` / `saveTankLabCell`): `tank_id, cell_id, patient_id, action_type ('Add patient'|'Remove patient'), done_date, by_user_id`. This is the custody ledger.
- **Double witnessing (partial):** `spermfreezingreport.embryologist1` + `embryologist2`; lab sheet groups `injection_embrologist`, plus `icsi_by`, `embryologist_id`, `freeze_embryologist_id` (+ `*_tb` flags marking IDs sourced from the `embryologist` table vs `awusers`).
- **Edit trail:** `embryoslab_tank_cells.{user_id,date_add,userid_edit,date_edit}`; soft-delete `deleted=1` everywhere (no physical delete on custody data).
- **Cancellation trail:** `embryoslab.cancelled_user` / `cancelled_reverse_user` (`embryoslab.php::updateit`).
- Embryologist role = `awusers.role_id = 11`; clinician = `role_id = 4`. External embryologists live in table `embryologist` (referenced with a `*` prefix and merged via SQL UNION).

## 3. End-to-end workflows

1. **OPU** — `ovumpickup` (`ovumpickupembryotrans.php:183`): `patientid, date, consultant, anaesthetist, nurse, operation, postoperative, complications`.
2. **Operation follow-up** — `followup_operation` (`R::xdispense` line ~1160): `patient_id, nurse_id, anaesthetist_id, clinician_id, embryologist_id, ut_cx, avf_rvf, easy_difficult, postoperative, complications, date`. Lookups `ut`, `avf`, `easydifficult`, `operationotherth`, plus `follow_*` lists.
3. **Semen analysis** — `embryologyreport` (`embryology.php:180`): `patient_id, date, sperm_source, count, motility, morphology`.
4. **Lab sheet head** — `embryoslab` (one draft per patient: `not_confirm=0` → `1` on `savenow()`). Fields: `patient_id, visit_id, clinician_id, referred_dr_id, refer_doctor_tb, media, oil, incubator (CSV-in-column), injection_embrologist, icsi_by(_tb), embryologist_id(_tb), freeze_embryologist_id(_tb), date, cancelled`.
5. **Grading day2→day6** — `embryoscoring` (`scoring()` / `fertilizedData()`): row per embryo `itr`, columns `media1..6, oil1..6, incubator1..6, embrologist1..6`, plus `stage`/`grade`, `deleted`. Row count derived from fertilized count; rows soft-recycled (`itr=0` reuse).
6. **Selection** — `embryoselection` (`embryoslab_id, deleted`), lookups `embryo_result`, `embryo_emb`.
7. **Transfer (ET)** — `embryotransfer` (1 per sheet): `embryoslab_id, clinician_id, embryologist_id(_tb), et_day` + lookups `embryocathetertype, embryodifficulty, embryoblood, embryojetplace, embryolah, embryotype`. Independent OPU-flow transfer: `embryotransferovum` (`patientid, date, day, consultant, nurse, embryologist, no_of_embryos, quality, notes`).
8. **Embryo freezing** — `embryofreezingreport` (`embryofreezing.php:621`): `patient_id, date, embryologist, ref_by_prof_dr, no_of_straws, no_of_embryos, day_of_freezing, kit, device, lot_no, location, note, collapsed, completed, deleted`. Detail rows `embryo` (`embryofreezing_id, stage1..6, grade1..6`). Thaw rows `embryothawing` (`embryofreezing_id, no_of_embryos, thawing_date, recovered, lost, e_t_date, embryos, grades, embryol_oigist, clinician, cathetr`). NOTE: `updateit()` does `DELETE FROM embryothawing/embryo` then re-inserts on every save.
9. **Sperm freezing** — `spermfreezingreport` (`spermfreezing.php:204`): `patient_id, date, sperm_source, ref_by_dr_id, count, motility, a_plus_b, morphology_abn, comment, samples_no, freezing_media_id, freezing_protocol_id, purpose_of_freezing, color_of_pen, embryologist1, embryologist2, location`. Lookups `freezingmedia`, `freezingprotocol`.

## 4. NEW lookup/reference tables
`stage, grade, growthmedia, growthoil, growthincubator, growthco2, oocytequality, oocytezona, oocytecytoplasm, oocytepvs, oocyteother, embryocathetertype, embryodifficulty, embryoblood, embryojetplace, embryolah, embryotype, embryo_device, embryo_result, embryo_emb, embryo_lot_select, embryo_catheter_select, embryo_tank, embryo_kit_type, injection, injection_location, sperum_source, semen_process, freezingmedia, freezingprotocol, kit, device, cathetr, colors, detections, cancel_reason, embryologist` — all NEW. Most carry `name` + `deleted`; edited via generic `R::xdispense($table)` endpoints (`addToTable`, `addRow`, `addNewRow`).

## 5. Data-quality / security notes
- **SQL injection**: pervasive string concatenation, e.g. `R::exec('DELETE from embryoslab WHERE id = ' . $id)`, `"select * from awusers where user_id=$ref_dr_id->refer_doctor"`. Must be parameterized before migration.
- **CSV-in-column**: `media/oil/incubator/injection_embrologist` stored comma-joined in one column (`updateit()` joins JSON arrays with commas) — no junction tables.
- **Capacity mismatch**: generator creates 1000–3000 `tankcells` while UI shows 20/100/300.
- Soft-delete preserved across custody tables (good for audit).
- `spermfreezing.php::showallreports()` has a broken query (`SELECT* spermfreezingreport from where ...`) — dead/buggy path.

## 6. ERP/HIS blueprint impact
- New first-class **Embryology & IVF Lab** module absent from the 312-table baseline.
- Model cryo inventory as `Tank → Canister/Cane → Position` with `tankcellhistory` as the custody-ledger seed (CAP/JCI/ESHRE chain-of-custody).
- Unify the two parallel storage models (`tankcells` grid vs `embryoslab_tank_cells` colour) into one source of truth for specimen location.
- Promote **double-witnessing** to a mandatory field on freeze/thaw/transfer/relocation events.
- Normalize ~30 lookup tables into a single `lookup(type,value)` reference.
- Link `embryoslab.visit_id` to the HIS treatment cycle to close clinical→lab→reporting loop (`ivf_reports.php`).
- Fix SQLi and decompose CSV-in-column fields as migration prerequisites.
- Integrate lab consumables (`kit, device, cathetr, lot_no`) with the ERP inventory/procurement module (stock + expiry).