# Module: IVF / ICSI / IUI & Cycle Monitoring (ivf)

## Purpose
Manages assisted-reproduction treatment cycles in the OB/GYN clinic system:

1. **IVF/ICSI Sheet** (`ivfsheet` + child tables `ovst`, `eprep`, plus runtime table `sefo`): one active sheet per patient covering pre-stimulation evaluation (AMH, AFC, protocol), the ovarian-stimulation grid (follicle counts by diameter 10-20mm, endometrium, E2), trigger (TOO), oocyte pickup (OPU), semen data, fertilization, fresh embryo transfer, freezing, frozen-cycle endometrial preparation/thawing/transfer, and pregnancy outcome (HCG / gestational sac / delivery). Archiving a sheet generates denormalized summary row(s) in `icsi` attached to the patient's infertility sheet.
2. **Monitoring Sheet** (`mointoringsheet` + `mointoringsheetvisits` + 9 lookup tables + drugs/investigations tables): an independent cycle-monitoring workflow with attempt number, procedure, stimulation protocol, sperm source (ejaculate/PESA/TESE/cryo), HMG/agonist/HCG drug choices, daily monitoring visits (cycle day, HMG dose, endometrium, right/left follicles, E2), wife/husband prescriptions and investigations, OPU/embryology summary, and outcome, then archive + trial history.
3. **Folliculometry** (`folliculom`): simple ovulation-induction tracking rows attached to the infertility sheet.
4. **Reports**: IVF statistics (per-period/per-patient cycle outcomes) and a daily/period IUI report (reads `infertilitysheet.sheetdate`/`sheetresult`).

ORM: RedBeanPHP (`R::`) in fluid mode; templating: Smarty. No declared foreign keys anywhere; all relationships inferred.

## Controllers & Views (file paths)
Controllers (`/home/amrtechogate/public_html/obgy/core/controllers/`):
- `ivfsheet.php` — main IVF/ICSI sheet (active sheet, AJAX cell saves, ovst/eprep/sefo row append, cycle-day date math, archive on end-of-trial, history). Current version.
- `ivfsheet00.php` — older duplicate of `ivfsheet.php` (no `sefo` support, no BMI copy). Dead/legacy code.
- `monitoring.php` — monitoring sheet (auto-open sheet, 9 lookup dropdowns w/ inline add, daily visit rows, prescriptions, investigations, archive/trials/trialdetail with 9-way LEFT JOIN, financial `search()` that actually belongs to the finance report).
- `Ivfstatistics.php` — IVF statistics search/print over `ivfsheet` by `lmpfresh`/`lmpfrozen` range.
- `iui.php` — IUI daily list / period search / print over `infertilitysheet` (`sheetdate`, `sheetresult`).
- Read-only consumers: `infertilitysheet.php` / `infertilitysheet00.php` (display `icsi` and `folliculom`, append `folliculom` rows, cycle-day calc), `Completesreport.php`, `sh.php`, `patienthistory.php` (full patient report).

Views (`/home/amrtechogate/public_html/obgy/core/views/obgy/`):
- `ivfsheet/`: `add.html` (main sheet: Infertility/ICSI-trial header, OVST grid CD|Date|Ag-Antag|HMG|F(10..20,N)|E|E2|Note, TOO, OPU, Semen, F/E D2, fresh & frozen ET, eprep, outcomes), `archive.html`, `ivfhistory.html`, `append.html`, `select.html`, `delselect.html`.
- `monitoring/`: `add.html`, `newrow.html`, `newrowUS.html`, `trials.html`, `archive.html`, `drugmodal.html`, `newrowdrugedit.html`, `showPrescriptions.html`, `showInvestigation.html`, `editmodel.html`.
- `reports2/`: `Ivfstatistics.html`, `Ivfstatisticssearch.html`, `Ivfstatisticsprint.html`, `iui.html`, `iuisearch.html`, `iuiprint.html`.
- Print templates reused from gyna module: `gyna/print.html`, `gyna/printinv.html`.

## Tables
Schema source: `/home/amrtechogate/public_html/obgy/_db/obgy_12-7-2024.sql`. **None of the 24 assigned tables has INSERT rows in this dump** — all lookup vocabularies are user-managed at runtime (`addNewItem` / `getselectajax`), so seed data must be exported from production.

### ivfsheet — active/archived IVF-ICSI trial sheet (one active per patient, `endpreg = 0`)
All columns `varchar(191)` unless noted.
- `id` int(11) PK AI
- `patientid` int unsigned — FK → patients.id
- `endpreg` int unsigned — 0 = active, 1 = archived
- Evaluation: `anevaluation`, `anevaluations`, `antype` (FK → antype.id), `anduration`, `anamh` (AMH), `annn` (AFC), `antypes` (FK → antypes.id), `anprotocol` (FK → icsiprotocol.id), `anttt` (treatment), `annotes`
- Fresh cycle: `lmpfresh`, `toocd`/`toodate`/`toodrug` (trigger of ovulation), `opun`/`opumll`/`opuml`/`opugv`/`opuat`/`oopuu`/`opunote` (OPU: total, MII, MI, GV, anesthesia/time, notes), `ssemen`/`sseemen` (FK → sseemen.id)/`sseemmen` (FK → icsisemen.id)/`sseemmeen` (semen data), `fefn`/`fefa`/`fefb`/`fefc` (fertilization/embryos D2 by grade), `ettcd`-family: `ettn`/`ettg`/`ettdate`/`ettnote`/`ettcancel` (fresh ET number/grade/date/cancel), `frrcd`/`frrst`/`frren` (freezing: cycle day, stage, embryo count), `sssnofte`/`sssresult`/`ssscd` (post-ET test), fresh outcomes `ooutcomehsgd`/`ooutcomehsgn` (HCG), `ooutcometvsd`/`ooutcometvsn` (TVS sac), `ooutcomedeld`/`ooutcomedeln` (delivery)
- Frozen cycle: `lmpfrozen`, `ssnofte`/`ssresult`, `etcd`/`etdate`/`etn`/`etg`/`etnote`/`etcancel` (frozen ET), `refren` (re-freeze count), frozen outcomes `outcomehsgd`/`outcomehsgn`, `outcometvsd`/`outcometvsn`, `outcomedeld`/`outcomedeln`
- Closure/meta: `frozenor` ("fresh"/"frozen"), `historytoday` datetime, `e2d2`, `pla2ce`, `pla2cen` (FK → pla2cen.id, ICSI place), `importantnote`, `sheetyear` int, `sheetmonth` int, `bmi` varchar(255) (copied from `examination.bmi` on archive)

### ovst — ovarian stimulation grid rows (child of ivfsheet)
- `id` int PK AI; `ivfsheetid` int unsigned — FK → ivfsheet.id
- `ovstcdfh` int unsigned (cycle day), `ovstdate`, `ovstag` (agonist/antagonist), `ovsthmg` (HMG dose)
- 22 cryptic varchar columns `ovstfr`, `ovstfrr`, `ovstfrrr`, `ovstfrrrr`, `ovstffrrrr`, `ovstfffrrr`, `ovstffffrr`, `ovstfffffr`, `ovstffffff`, `ovstfrrffff`, `ovstfrfffff`, `ovstdfr` … `ovstdffffff`, `ovstdfrrffff`, `ovstdfrfffff` = grid cells for follicle counts per diameter 10,11,12,…,20 mm + N (mapping defined only by column order in `ivfsheet/add.html`; `d`-prefixed set is the second row of the grid)
- `eovst` (endometrium), `eeovst` (E2), `noteovst`

### eprep — endometrial preparation for frozen ET (child of ivfsheet)
- `id` PK AI; `ivfsheetid` int unsigned — FK → ivfsheet.id
- `epreplps` — FK → epreplps.id (luteal phase support regimen)
- `eprepcd`, `cdlps`, `lmpfrozen`, `eprepdate`, `eprepttt` (treatment), `eprepe` (endometrium), `eprepro`/`epreplo` (right/left ovary), `eprepcancel`
- Thawing/transfer: `thawingdate`, `thawingstn` (thawed n), `thawingen` (transferred n)

### epreplps — lookup: LPS regimens
- `id` PK AI, `title` varchar(191), `del` tinyint. Empty in dump. Soft-delete convention: `del is null` = active.

### icsi — denormalized ICSI trial history (generated by `endpreg()`, displayed on infertility sheet)
- `id` PK AI; `infertilitysheetid` int unsigned — FK → infertilitysheet.id
- `icsiprotocol`, `icsiplace`, `icsisemen`, `icsiresult`, `icsiss` — **text snapshots** of lookup titles (not FKs)
- `refr` (re-freeze), `fr` (frozen n), `vd`, `et` (transferred n), `fert`, `opu`, `date`, `e2d2`
- Runtime-added column `sefoid` (FK → sefo.id) used by `ivfsheet.php` but absent from the dump (RedBean fluid mode).

### icsiplace / icsiprotocol / icsiresult / icsisemen / icsiss — lookups
All: `id` PK AI, `title` varchar(191), `del` (int or tinyint). All empty in dump.
- `icsiprotocol` — stimulation protocols; referenced by id from `ivfsheet.anprotocol`, copied as text into `icsi.icsiprotocol`.
- `icsisemen` — semen source/state; referenced by id from `ivfsheet.sseemmen`.
- `icsiplace` — ICSI center/lab; loaded by id in reports (`Completesreport.php`, `sh.php`) from `icsi.icsiplace` (mixed id/text usage).
- `icsiresult` — trial result vocabulary.
- `icsiss` — post-transfer pregnancy-test vocabulary (stored into `icsi.icsiss`).

### mointoringsheet — cycle monitoring sheet header (note misspelling, latin1 table)
- `id` PK AI, `doctorid` int — FK → awusers (session user), `patientid` int — FK → patients.id
- `monitordate` date (cycle start), `lmp` date, `attempno` int
- Lookup FKs (int): `procedure` → mointoringsheetprocedure.id, `protocol` → mointoringsheetprotocol.id, `ejac` → mointoringsheetejac.id, `pesa` → mointoringsheetpesa.id, `tese` → mointoringsheettese.id, `sryo` → mointoringsheetsryo.id, `hmg` → mointoringsheethmg.id, `agonist` → mointoringsheetagonist.id, `hcg` → mointoringsheethcg.id
- `remarks` text, `place`, `amp` (HCG ampoules)
- Embryology summary: `oocytestotal`, `oocytesm1`, `oocytesm2`, `oocytesgv`, `ettotal`, `etg1`, `cyroday`, `cyrow`, `p4`, `outcome` (all varchar(255))
- `status` int (0 open, 1 archived — comment in schema), `sysdate` date, `deleted` int, `notes` text

### mointoringsheetvisits — daily monitoring visit rows
- `id` PK AI, `mointoringsheetid` int — FK → mointoringsheet.id
- `visitdate` date, `cycleday` varchar (e.g. "5th", computed from monitordate), `hmg` text, `endo` text, `follicright` text, `follicleft` text, `e2` text, `sysdate` date, `deleted` int

### mointoringsheetdrugs — monitoring prescriptions (wife/husband)
- `id` PK AI, `patientid` int — FK → patients.id, `date` date, `drugid` varchar(150) — FK → drugs.id, `drugtype`, `drugdos`, `drugname`, `deleted` int, `doctorid` int — FK → awusers, `forhusband` int (0 wife / 1 husband), `recepittmpid` int / `recepitdrugid` int — pharmacy receipt links (inferred), `mointoringsheetid` int — FK → mointoringsheet.id

### mointoringsheetinvestigation — monitoring lab orders (wife/husband)
- `id` PK AI, `patientid` int — FK → patients.id, `date` date, `investid` int — FK → invests.id, `investresult` varchar(250), `deleted` int, `doctorid` int — FK → awusers, `forhusband` int, `mointoringsheetid` int — FK → mointoringsheet.id

### mointoringsheetprocedure / mointoringsheetprotocol / mointoringsheetejac / mointoringsheetpesa / mointoringsheettese / mointoringsheetsryo / mointoringsheethmg / mointoringsheetagonist / mointoringsheethcg — 9 lookups
All identical: `id` int PK AI, `name` varchar(255), `deleted` int default 0. All empty in dump; values entered live from the monitoring screen (`addNewItem`). Semantics: procedure type (IVF/ICSI/IUI…), stimulation protocol, ejaculate option, PESA option, TESE option, cryo-sperm option ("sryo" = cryo), HMG drug, agonist/antagonist drug, HCG trigger drug.

### folliculom — folliculometry rows (ovulation induction, child of infertility sheet)
- `id` PK AI, `infertilitysheetid` int unsigned — FK → infertilitysheet.id
- `lmp`, `ttt` (induction drug), `cd` (cycle day), `date`, `ro`/`lo` (right/left ovary follicles), `endo`, `pelvis`, `too` (trigger), `result` — all varchar(191)

## Relationships (explicit list: table.column -> table.column)
- ivfsheet.patientid -> patients.id
- ivfsheet.antype -> antype.id ; ivfsheet.antypes -> antypes.id
- ivfsheet.anprotocol -> icsiprotocol.id
- ivfsheet.sseemmen -> icsisemen.id ; ivfsheet.sseemen -> sseemen.id
- ivfsheet.pla2cen -> pla2cen.id (ICSI place lookup, other module)
- ovst.ivfsheetid -> ivfsheet.id
- eprep.ivfsheetid -> ivfsheet.id ; eprep.epreplps -> epreplps.id
- sefo.ivfsheetid -> ivfsheet.id (runtime table, NOT in dump)
- icsi.infertilitysheetid -> infertilitysheet.id ; icsi.sefoid -> sefo.id (runtime column)
- folliculom.infertilitysheetid -> infertilitysheet.id
- mointoringsheet.patientid -> patients.id ; mointoringsheet.doctorid -> awusers.user_id
- mointoringsheet.{procedure,protocol,ejac,pesa,tese,sryo,hmg,agonist,hcg} -> mointoringsheet{procedure,protocol,ejac,pesa,tese,sryo,hmg,agonist,hcg}.id (confirmed by 9-way LEFT JOIN in `monitoring.php::trialdetail`)
- mointoringsheetvisits.mointoringsheetid -> mointoringsheet.id
- mointoringsheetdrugs.patientid -> patients.id ; .drugid -> drugs.id ; .mointoringsheetid -> mointoringsheet.id ; .doctorid -> awusers ; .recepittmpid/.recepitdrugid -> pharmacy receipts (inferred)
- mointoringsheetinvestigation.patientid -> patients.id ; .investid -> invests.id ; .mointoringsheetid -> mointoringsheet.id
- ivfsheet.bmi <- copied from examination.bmi (latest, on archive)
- IUI report: reads infertilitysheet.sheetdate / sheetresult / patientid -> patients.id
- lastvisit.patientid -> patients.id (screen-entry tracking written by ivfsheet/iui controllers)

## Business Workflows (traced from code)
1. **Open IVF sheet** (`ivfsheet.php::index?patientid=`): finds `ivfsheet` with `endpreg=0` or auto-creates it; also auto-creates `infertilitysheet` and `gynasheet` if missing; loads child `ovst`, `eprep`, `sefo` rows; records entry in `lastvisit`.
2. **Field-level autosave**: every input posts to `Add()` with `{table, id, name, value}` → direct `R::store`. No save button, no validation, table/column names come from the client.
3. **Stimulation rows**: `append()` dispenses a new `ovst`/`eprep`/`sefo` row bound to the sheet; entering a cycle day calls `ovstcdfh()`/`newcd()` which compute the date as `lmpfresh + (cd-1) days` (raw `R::exec` UPDATE in `newcd`).
4. **Trial closure** (`endpreg(kind)`): sets `endpreg=1`, `frozenor=fresh|frozen`, `historytoday=now`, copies latest `examination.bmi`; then dispenses an `icsi` summary row (frozen branch copies `lmpfrozen/fefn/etn/frren/refren/ssnofte/outcomehsgn`; fresh branch copies `lmpfresh/semen title/opun/fefn/ettn/frren/sssnofte/ooutcomehsgn/ssscd`), resolving `icsiprotocol`/`icsisemen`/`pla2cen` ids to **titles**, linked to `infertilitysheet.id`; fresh branch additionally writes one `icsi` row per `sefo` row. Patient can then start a fresh `ivfsheet`.
5. **IVF archive/history**: `archive()` lists `endpreg=1` sheets; `historys(ivfsheetid)` renders a past sheet read-only with its ovst/eprep/sefo children.
6. **Monitoring sheet** (`monitoring.php::index`): finds/creates the open sheet (`status=0`) per patient; loads all 9 lookup lists, today's prescriptions, investigation catalog (`investcats`/`invests` grouped by displayorder). Lookups support inline create (`addNewItem`, optionally setting the parent FK), edit (`editselect` + generic `update`), soft delete (`del`).
7. **Daily visits**: `addNewVisit()` creates a `mointoringsheetvisits` row with cycle day = days(monitordate→today)+1 with ordinal suffix; cells (hmg/endo/follicright/follicleft/e2) update via generic `update()`; `getCycleDay()` recomputes on date edits.
8. **Prescriptions / investigations**: `mointoringsheetdrugs` and `mointoringsheetinvestigation` per date and per partner (`forhusband` 0/1), with history modals (`showprescription`, `showInvs`) and print pages reusing gyna templates; partner name/title resolved from `patients` + `wifetypes`/`husbandtypes`.
9. **Monitoring closure**: `remarkSheet()` fetches hcg/remarks for the trigger modal; `trigSheet()` sets `status=1` (archive). `trials()` lists archived sheets (resolving procedure/protocol names); `trialdetail(id)` renders full detail via a 9-way LEFT JOIN to lookup tables plus its visits.
10. **Folliculometry**: rows appended from the infertility sheet screen (`infertilitysheet.php::append` with tablename=folliculom); `folliculom()` AJAX computes date = latest non-null `lmp` + cd days.
11. **IVF statistics** (`Ivfstatistics.php::search`): filters `ivfsheet` by `lmpfresh`/`lmpfrozen` between dates (optionally per patient); per row computes wife's age at trial from `patients.dateofbirth`, loads `antype`, `icsiprotocol`, `sseemen`, `icsisemen`, first/last `ovst`, `eprep` count and first/last. `showprint()` mistakenly queries `ancsheet` by `sheetedd` (copy-paste from ANC report — broken).
12. **IUI report** (`iui.php`): daily list of `infertilitysheet` rows with `sheetdate = today`; `search`/`showprint` filter by patient and date range via string-concatenated SQL; displays `sheetdate` and `sheetresult` (IUI clinical data itself lives in the infertility module).

## ERP Migration Notes (proposed Laravel model names, normalization advice, what to merge/drop)
Proposed models (Laravel + Angular):
- `IvfCycle` — unifies `ivfsheet` + `mointoringsheet`: patient_id, doctor_id, attempt_no, cycle_type (fresh/frozen/iui/monitoring-only), procedure_id, protocol_id, lmp, start_date, status (active/archived), bmi, amh, afc, notes, outcome fields. Enforce one-active-cycle-per-patient with a partial unique index.
- `IvfCycleVisit` — unifies `ovst` + `mointoringsheetvisits` + `folliculom`: cycle_id, visit_date, cycle_day, agonist_dose, hmg_dose, endometrium_mm, e2, p4, notes. Child `FollicleMeasurement` (visit_id, side enum R/L, diameter_mm, count) replaces the 26 cryptic `ovst` columns and the free-text follicle fields — map column order from `ivfsheet/add.html` (F columns = 10..20 mm + N; `d`-prefixed = second grid row) during ETL.
- `OocyteRetrieval` (cycle_id, date, total, mii, mi, gv, anesthesia, notes) ← `opun/opumll/opuml/opugv/opuat`, `oocytestotal/m1/m2/gv`.
- `SemenSample` (cycle_id, source enum ejaculate/pesa/tese/cryo, quality fields) ← `ssemen*` columns + `ejac/pesa/tese/sryo` lookups.
- `EmbryoTransfer` (cycle_id, kind fresh/frozen, transfer_date, cycle_day, n_transferred, grade, cancelled, notes) ← `ett*`, `et*`, plus extra fresh transfers from runtime table `sefo`.
- `Cryopreservation` (cycle_id, date/cycle_day, n_frozen, stage, re_frozen) ← `frr*`, `refren`, `cyroday/cyrow`.
- `EndometrialPrep` (cycle_id, lmp, treatment, endometrium, lps_id, thawing_date, n_thawed, n_transferred, cancelled) ← `eprep` + `epreplps`.
- `CycleOutcome` (cycle_id, hcg_date/value, tvs_date/sacs, delivery_date/babies, result_id) ← `*outcome*` columns + `icsiresult`/`icsiss`.
- `Lookup` (type, name, is_active) — merge all 15 lookup tables (`icsiprotocol`, `icsiplace`, `icsisemen`, `icsiss`, `icsiresult`, `epreplps`, 9× `mointoringsheet*`); seed from **production DB**, not this dump (all empty here). Consider enums for clinically fixed sets.
- Prescriptions/labs: fold `mointoringsheetdrugs` into the ERP-wide `prescriptions` (add cycle_id, for_partner) and `mointoringsheetinvestigation` into `lab_orders` (invest_id → investigations catalog, result, for_partner).

Drop / do not migrate as tables:
- `icsi` — derived denormalized archive; replace with a query/View over the new entities (but ETL its historical rows into `IvfCycle` records where no matching `ivfsheet` exists).
- `ivfsheet00.php` legacy controller; `mointoringsheet` vs `ivfsheet` duplication; `monitoring.php::search()` (misplaced finance report).
- `sefo` exists only in production (RedBean fluid mode) — dump it from production before ETL; same for any other runtime-added columns (e.g. `icsi.sefoid`, `ovstfrrffffn`).

ETL cautions:
- All dates in `ivfsheet`/`ovst`/`eprep`/`folliculom` are `varchar` in `Y/m/d` format (plus `0000-00-00` and empty strings) — parse defensively.
- Numbers (oocytes, embryos, E2) stored as varchar/text — cast with validation.
- Mixed id-vs-text storage for the same concepts (`ivfsheet.anprotocol` = id, `icsi.icsiprotocol` = title) — reconcile by title match.
- Inconsistent soft-delete conventions (`del is null` vs `deleted = 0`).
- Security debt to eliminate: generic `Add`/`update`/`del`/`addNewItem` endpoints accept arbitrary table/column names from POST (mass write primitive); `newcd()` and `iui.php` searches build SQL by string concatenation (SQL injection). Replace with FormRequest-validated, policy-guarded endpoints.
- Bug to not carry over: `Ivfstatistics::search` unparenthesized `AND/OR` date filter; `Ivfstatistics::showprint` queries `ancsheet` (broken print).