# Legacy OBGY (amrtechogate) — IVF / ART workflow inventory

> **Source:** read-only examination on 2026-10-24 of `/home/amrtechogate/public_html/obgy` (PHP + RedBeanPHP + Smarty) and DB `amrtechogate_obgy` (MariaDB 10.11).
> **Method:** I read the controllers, Smarty views and `_assets/custom/elemam.js`. On the DB I ran `SELECT`/`DESCRIBE`/`information_schema` only, through the root mysql CLI in `SET SESSION TRANSACTION READ ONLY` sessions. **I did not read any app credentials, did not log in to the web UI and did not write anything.**
> **Privacy:** this file holds schema, option/lookup values, counts and aggregates only. Lookup tables that list referring doctors or external centers are given as counts only. No patient data and no free-text notes appear here.
> Related: [`../clinical-platform/legacy-obgy-options-analysis.md`](../clinical-platform/legacy-obgy-options-analysis.md) (pick-list patterns), `moon-current.md` (this folder).

## ملخص سريع (TL;DR)
- مفيش «موديول IVF» حقيقي. فيه **3 شاشات حيّة**: Infertility visits (`infertilitysheet`) و IVF visits (`ivfsheet`) وتقريرين (IVF register و iUI list).
- **دورة الـIVF = صف واحد** في `ivfsheet` فيه ~75 عمود `varchar`. الـfresh والـfrozen عيلتين أعمدة على نفس الصف. تحت الصف ده 3 جداول أبناء: `ovst` (أيام التنشيط)، `eprep` (تحضير البطانة للـFET)، `sefo` (ET إضافي).
- **مفيش embryology لكل بويضة/جنين، مفيش cryo inventory، مفيش ربط FET بالـOPU الأصلي.** نتيجة الحمل نص حر.
- **شاشة "IVF statistics" مابتحسبش ولا KPI واحد.** هي register بصف لكل دورة.
- الـIUI عبارة عن **3 أعمدة على شيت المريضة اللي واحد مدى الحياة**، وكل محاولة جديدة بتكتب فوق اللي قبلها.

---

## 0. Live vs dead screens (from `awmenu`)

| Menu entry | Controller | Status |
|---|---|---|
| Infertility visits | `infertilitysheet.php` → `infertilitysheet/add.html` | **LIVE**: the infertility hub |
| IVF visits | `ivfsheet.php` → `ivfsheet/add.html` (+ `archive.html`, `ivfhistory.html`) | **LIVE**: one IVF/ICSI cycle sheet |
| Monitoring sheet | `monitoring.php` → `monitoring/*` | **Abandoned v2**: 4 sheets, 0 visits, all 9 lookup tables empty |
| Infertility | `infertility.php` → `infertility/*` | **Dead v1**: tables `infertility*` hold 0–1 rows |
| REPORTS › IVF | `Ivfstatistics.php` → `reports2/Ivfstatistics*.html` | LIVE: cycle register |
| REPORTS › iUI | `iui.php` → `reports2/iui*.html` | LIVE: IUI list |
| — | `ivfsheet00.php`, `infertilitysheet00.php` | **Dead copies**: not in the menu and not referenced |

Every live sheet uses the same generic AJAX pattern:
- `append` (POST `tablename`, `tablep`, `id`) dispenses a new child row.
- `update`/`Add` (POST `tableName`, `colName`, `value`) writes one cell.
- `getselect` loads a lookup into a select or select2. Multi-selects are stored as **CSV of lookup ids**.
- `getselectajax` inline-adds a lookup value. `deldataselect` soft-deletes one (`del=1`).

Patient-level data (husband and wife) sits on the patient record (`patients`). The IVF and infertility sheets hang off `patientid`.

---

## 1. Screens and flow, mapped to the ART pathway

### 1.1 Stage map

| ART stage | Legacy screen / block | Storage | Repeats | Structured? |
|---|---|---|---|---|
| Couple work-up: history, exam, diagnosis | Infertility sheet: husband / wife panels | `infertilitysheet` (1 row per patient for life) | no (overwritten) | lookups, often **CSV multi** |
| Semen analysis | Infertility sheet › Semen | `semeninfertility` | row per test | numbers as varchar |
| Male advanced work-up (scrotal US, TRUS, karyotype, Yq, SDF, semen freezing, TESE) | Infertility sheet › Other Inv | `semen2` (one wide row holds 8 tests) | row per "set" | date+place+result per test |
| Male hormones | › Hormonal Profile | `hormonalprofile` | row | FSH/LH/TT/PRL/E2/Inhibin B/2× other |
| Female hormones | Wife › Hormonal Profile | `hormonalprofile2` | row | AMH/TSH/PRL/FSH/LH/E2/P4/other |
| TVS / 3D TVS / SIS / HSG / hysteroscopy / laparoscopy | Wife › Investigations | `tvs`, `dtvs`, `sis`, `hsginfertility`, `hysteroscopyinfertility`, `laparoscopyinfertility` | row per exam | lookup per anatomical site (CSV multi) |
| Obstetric history / EPC | Wife › (Obst History) p / EPC | `awifep`, `wifeepc` | row per pregnancy | lookups |
| Ovulation induction / COS for timed intercourse / IUI | Infertility sheet › TTT (DA, Metformin, COS type, response) | `infertilitysheet.ttttype` (CSV), `tttresponse` … | **no** | lookup |
| Folliculometry (OI cycles) | Infertility sheet › FOLLICULOM | `folliculom` | row per scan day | **free text** RO/LO/Endo |
| **IUI** | Infertility sheet › IUI (Trials / Date / Result) | `infertilitysheet.sheettrials/sheetdate/sheetresult` | **no: 1 per patient, overwritten** | free text |
| Plan | Infertility sheet › Plan (Husband / Wife) | `sheethusband`, `sheetwife` (CSV of "plan" lookups) | no | lookup |
| Visits | Infertility sheet › New Visit | `newvisit` | row per visit | Date, Cycles(lookup), LMP, BW, UT, OV, TTT |
| Rx / investigation orders | modals on the sheet | `infertilitysheetdrugs`, `infertilitysheetinvest` | row per item, `forhusband` flag | drug catalog / invest catalog |
| Past ICSI attempts (history table) | Infertility sheet › ICSI | `icsi` | row per attempt | mix of lookup ids and copied titles |
| Surgeries | › operations | `operations` | row | lookup op (CSV), place, histopath |
| **IVF cycle: indication, protocol** | IVF sheet › New Attempt | `ivfsheet` | **1 open row per patient** (`endpreg=0`) | lookups + free text |
| **COS daily monitoring** (fresh) | IVF sheet › OV. ST. | `ovst` | row per monitoring day | **all free text**, 24 follicle-bucket columns |
| **Trigger** | IVF sheet › TOO (CD / Date / Drug) | `ivfsheet.toocd/toodate/toodrug` | 1 | free text |
| **OPU** | IVF sheet › OPU (Date, N, MII/MI/GV/At/?, Notes) | `ivfsheet.opu*` | 1 | counts as varchar |
| Sperm prep / source | IVF sheet › Semen (4 selects) | `ivfsheet.ssemen/sseemen/sseemmen/sseemmeen` | 1 | lookups |
| **ICSI lab: fertilization** | IVF sheet › F/E D2 (N, A, B, C) | `ivfsheet.fefn/fefa/fefb/fefc` | 1 | counts: grade A/B/C on day 2 only |
| **Embryology D1–D6, per-embryo** | — | — | — | **MISSING** |
| PGT / sex selection | IVF sheet › S.S (N of T.E, Result) | `ivfsheet.sssnofte/sssresult` | 1 (+`sefo`) | free text, **repurposed** (see §4) |
| **Embryo transfer** (fresh) | IVF sheet › ET (CD, Date, N, G, Notes, Cancel) | `ivfsheet.ssscd/ettdate/ettn/ettg/ettnote/ettcancel` | 1 + `sefo` rows | free text |
| **Embryo freezing** | IVF sheet › FR (CD, ST, EN) | `ivfsheet.frrcd/frrst/frren` | 1 | counts as varchar; **no inventory** |
| **Luteal phase support** | only in FET E-Prep (`eprep.epreplps`, `cdlps`) | `eprep` | — | **MISSING for fresh cycles** |
| **β-hCG / TVS / delivery** | IVF sheet › Outcome (HCG / TVS / Del: date + result each) | `ivfsheet.ooutcome*` (fresh) / `outcome*` (frozen) | 1 (+`sefo`) | **free text** |
| **FET: endometrial preparation** | IVF sheet › Frozen › E Prep (CD, Date, TTT, E, RO, LO, Cancel, CD/LPS, Thawing date/ST.N/EN) | `eprep` | row per prep visit | free text + LPS lookup |
| **FET: ET + outcome** | IVF sheet › Frozen › ET / Re Fr / Outcome | `ivfsheet.etcd/etdate/etn/etg/etnote/etcancel/refren/outcome*` | 1 | free text |
| Close cycle | buttons "End ICSI fresh" / "End ICSI frozen" | `endpreg()` → `ivfsheet.endpreg=1, frozenor`, + summary row(s) into `icsi` | — | — |
| Archive / history | Archive ICSI, `historys` | `ivfsheet where endpreg=1` | — | read-only re-render |
| Statistics | REPORTS › IVF | reads `ivfsheet`+`ovst`+`eprep` | — | no KPIs (see §3) |
| Witnessing, consents, cryo storage, billing per stage | — | — | — | **MISSING** |

### 1.2 Infertility sheet (`infertilitysheet.php`), field inventory
One `infertilitysheet` row per patient, auto-created when the screen opens. Header: **Type** (`sheettype` ← `typeinf`), **Duration** year/month, **Critical Data** (`importantnote`).

**Husband panel**
| Block | Fields → column (lookup) |
|---|---|
| Diagnosis | 3 multi-selects: `husbndiagnosis1` (endocrine axis), `husbndiagnosis2` (semen pattern / testis), `husbndiagnosis3` (severity / aetiology). All CSV |
| History | Travel abroad: Location (`sheetlocation`), Duration, In, Out. Medical (`medicalhistorydm` multi + note). Surgical (`surgicalhistorydm` multi + note) |
| Examination | General BW (text), Local (`examinationlocalse` multi + note) |
| Semen (row per test) `semeninfertility` | Date, Place (`semenplace`), Type (`sementype`: conventional/CASA), C (`nc`), PRM, TM, AF, VIT, Vol, TZI, SDI |
| Other Inv (row) `semen2` | For each of Scrotal U/S, TRUS, Karyo, Yq micro, SDF, Semen Fr, TESE, Other: **Date + Place + Result**. Results come from lookups `semen2resultscrotal` (multi), `semen2resulttrue`, `semen2resultkaryo`, `semen2resultyqmicro`, `semen2resultsdf`; places for semen-freezing/TESE from lookups; semen-freezing result C/M/AF |
| Hormonal profile (row) `hormonalprofile` | date + result for FSH, LH, TT, PRL, E2, Inhibin B, Other×2 |
| TTT | `sheetttt` free text |

**Wife panel**
| Block | Fields → column (lookup) |
|---|---|
| Diagnosis | `diagnosis` multi (CSV) |
| Menstrual / Sexual hx | Reg (`menstrualreg`), C/D, Amount (`menstrualamount`), Dysm (`menstrualdysm`), LMP, Sexual (text) |
| Medical / Surgical / Family hx | `medicalhistory`, `surgicalhistory`, `familyhistory` multi + notes |
| Obst history p (row) `awifep` | Sex (`wifesex`), Name, Date of D, Age (computed), Mode (`wifemodeofd`), Type (`wifetypeofd` FT/PT), W, L (checkbox), Obst (`wifeobst`: doctor/center names), Contraception method (`awifepmethod` multi), Duration, Stopped, Comment |
| EPC (row) `wifeepc` | Date, Type (`wifeepctype`), W, TTT (`wifeepcttt`), Obst (`wifeepcobst`: doctor/center names), Histopath, cost, p, Epc, notes |
| G/P summary | `obstetric()` computes G/P/FT/PT/EPC/Living from `awifep`+`wifeepc` (+1 if an ANC sheet is open) → `obstg…obstliving` |
| Examination | BW, BMI, Breast, Hirsutism, Thyroid, Vulva, Vagina, CX (lookups, mostly multi), Other×2 |
| Hormonal profile (row) `hormonalprofile2` | date + result for AMH, TSH, PRL, FSH, LH, E2, P4, Other |
| TVS (row) `tvs` | Date, UT, CX, RO, LO, Pelvis (all multi lookups) |
| 3D TVS / SIS (rows) | Date, Place, Result (multi lookups) |
| HSG (row) | Date, Place, UT, RT, LT (multi), Pelvis |
| Hysteroscopy (row) | Date, Place, Dil, Introd, CX, Cavity-Fundus, R-Ostium, L-Ostium (findings), then Intervention + `copym*` (intervention per site, multi), cost |
| Laparoscopy (row) | Date, Place, UT, RT, LT, RO, LO, Pelvis (findings), then Intervention + `laparm*` (per site), cost |
| Other (row) | Name, Date, Details |
| TTT | DA (`tttda`), Metformin (`tttmetformin`), **COS Type** (`ttttype` multi: CC/HMG/letrozole…), **Response** (`tttresponse`), **IUI Trials / Date / Result** (3 scalars) |
| ICSI history (row) `icsi` | Date, Place, Protocol, OPU, Semen, Fert, S.S, ET, D, Fr, Re Fr, E2d2, Result |
| FOLLICULOM (row) `folliculom` | LMP, TTT, CD, Date (= LMP + CD−1, server computes), RO, LO, Endo, Pelvis, TOO, Result: **free text** |
| operations (row) | operation (multi), Date, place, histopath (multi), Cost, notes |
| Plan | Husband (`sheethusband` multi), Wife (`sheetwife` multi) |
| New Visit (row) `newvisit` | Date, Cycles (`newvisitcycles`), LMP, BW, US (UT, OV), TTT/Notes |

### 1.3 IVF sheet (`ivfsheet.php`), field inventory
- `index()` loads the patient's **one open** `ivfsheet` (`endpreg=0`). If none exists it **creates one on every GET**, which is why there are ~950 empty rows (§2).
- Fresh and frozen are toggled UI sections over the **same row**.

| Section | UI label → column | Type |
|---|---|---|
| Evaluation | male → `anevaluation`, female → `anevaluations` | text |
| New Attempt · Infertility | Type → `antype` (lookup `antype`); Duration → `sheetyear`/`sheetmonth` | select / text |
| New Attempt · ICSI Trial | AMH `anamh`; N (trial no.) `annn`; Type `antypes` (fresh/frozen lookup); Protocol `anprotocol` (lookup **`icsiprotocol`**); E2d2 `e2d2` | text / select |
| | place `pla2cen` (lookup of centers); TTT `anttt`; Notes `annotes` | |
| Fresh · LMP | `lmpfresh` (datepicker, stored `Y/m/d` string) | |
| **Fresh · OV.ST.** (child `ovst`, row per day) | CD `ovstcdfh` (typing CD auto-fills Date = LMP + CD−1); Date `ovstdate`; Ag/Antag `ovstag`; HMG `ovsthmg`; **Follicles** in a 2×12 grid, R row and L row, with columns for sizes **10,11,…,20 mm + N**: R = `ovstfr, ovstfrr, ovstfrrr, ovstfrrrr, ovstffrrrr, ovstfffrrr, ovstffffrr, ovstfffffr, ovstffffff, ovstfrfffff, ovstfrrffff, ovstfrrffffn`, L = same names with `ovstd…` prefix; E (endometrium) `eovst`; E2 `eeovst`; Note `noteovst` | all varchar free text |
| Fresh · TOO (trigger) | CD `toocd` (auto date), Date `toodate`, Drug `toodrug` | free text |
| Fresh · OPU | Date `opudate`; N `opun`; Diff N: MII `opumll`, MI `opuml`, GV `opugv`, Atretic `opuat`, "?…" `oopuu`; Notes `opunote` | varchar |
| Fresh · Semen (4 selects) | `ssemen` (good/bad), `sseemen` (fresh/frozen), `sseemmen` (source, lookup **`icsisemen`**: sample/TESA/TESE/micro-TESE/−ve micro-TESE), `sseemmeen` (Freezing/no freezing) | lookup |
| Fresh · F/E D2 | N fertilized `fefn`; Day-2 grade counts A `fefa`, B `fefb`, C `fefc` | varchar |
| Fresh · S.S / ET / FR / Outcome (main row + `sefo` child rows) | S.S: N of T.E `sssnofte`, Result `sssresult`. ET: CD `ssscd`, Date `ettdate`, N `ettn`, G `ettg`, Notes `ettnote`, Cancel `ettcancel`. FR: CD `frrcd`, ST (straws) `frrst`, EN (embryo no.) `frren`. Outcome: HCG date/result `ooutcomehsgd/n`, TVS date/result `ooutcometvsd/n`, Delivery date/result `ooutcomedeld/n` | free text |
| Frozen · LMP | `lmpfrozen` | |
| **Frozen · E Prep** (child `eprep`, row per visit) | CD `eprepcd` (auto date), Date `eprepdate`, TTT `eprepttt`, E (endometrium) `eprepe`, RO `eprepro`, LO `epreplo`, Cancel `eprepcancel`, LPS start CD `cdlps`, **LPS regimen** `epreplps` (lookup), Thawing date `thawingdate`, ST.N `thawingstn`, EN `thawingen` | free text + 1 lookup |
| Frozen · S.S / ET / Re-Fr / Outcome | `ssnofte`, `ssresult`, `etcd`, `etdate`, `etn`, `etg`, `etnote`, `etcancel`, `refren` (re-frozen), `outcomehsgd/n`, `outcometvsd/n`, `outcomedeld/n` | free text |
| Close | **End ICSI fresh / frozen** → `endpreg()` | |

**What `endpreg($kind)` does:**
1. Sets `endpreg=1`, `frozenor=fresh|frozen` and `historytoday=now`, and copies the latest `examination.bmi` into `ivfsheet.bmi`.
2. Inserts a summary row into **`icsi`** (the infertility sheet's ICSI history table) holding **titles, not ids**: protocol title, place title, semen title, opu, fert, et, fr, refr, ss, result, e2d2.
3. For fresh cycles, also inserts one extra `icsi` row per `sefo` row.
4. Bug: the main row's `sefoid` is assigned to an undefined variable (`$tableadd2`), so auto-created rows cannot be told apart from manual ones.

### 1.4 Monitoring sheet (`monitoring.php`), an unfinished normalized v2 (worth reusing as an idea)
- **`mointoringsheet`** (one row per attempt): `doctorid`, `monitordate`, `lmp DATE`, `attempno`, `procedure`/`protocol`/`ejac`/`pesa`/`tese`/`sryo`/`hmg`/`agonist`/`hcg` as **int FKs** to 9 dedicated lookup tables (`id,name,deleted`), `place`, `amp`, **`oocytestotal`, `oocytesm1`, `oocytesm2`, `oocytesgv`**, **`ettotal`, `etg1`**, **`cyroday`, `cyrow`** (cryo day / number), `p4`, `outcome`, `status` (open/closed), `remarks`, `notes`, `trigSheet`/`remarkSheet` actions.
- **`mointoringsheetvisits`** (one row per day): `visitdate DATE`, `cycleday` (auto via `getCycleDay`, ordinal), `hmg`, `endo`, `follicright`, `follicleft`, `e2`.
- Usage: 4 sheets, 0 visits, all 9 lookups empty, so it was never adopted. The design is still better than the live sheet: real `DATE` columns, int FKs, a status column, and oocyte maturity split.

### 1.5 IUI
- No IUI screen or table exists. On the infertility sheet's TTT block: **Trials** (`sheettrials`, e.g. "1","2","3"), **Date** (`sheetdate`), **Result** (`sheetresult`).
- These are 3 scalar columns on the one-per-patient sheet, so **each new IUI overwrites the previous one**.
- 237 patients have a value. Result values are mostly `-ve`, often suffixed with a free-text referring-doctor/center name in parentheses.
- No semen-prep parameters (TMSC, post-wash count), no stimulation link and no follicle link.
- Report `iui.php`: `index()` lists sheets whose `sheetdate = today`. `search()` filters `sheetdate` between dates (string comparison) and outputs No / Wife / file no. / Husband / date / result. No rates.

---

## 2. DB tables: columns and row counts
All columns are `varchar(191)` unless noted, and so are **all dates** (strings `Y/m/d`). There are no FKs or indexes beyond the PK.

### 2.1 Row counts (exact, `COUNT(*)`)

| Table | Rows | Real meaning |
|---|---|---|
| `ivfsheet` | **2,294** | **1,032 closed cycles** (615 fresh + 417 frozen) for **482 patients**; 281 open with data; ~953 empty auto-created; 28 NULL patientid |
| `ovst` (COS day rows) | 3,385 | 770 cycles, avg **4.4** monitoring days per cycle (max 9) |
| `eprep` (FET prep rows) | 1,542 | 611 cycles, avg 2.5 rows (max 7) |
| `sefo` (extra ET/outcome rows) | 213 | 133 cycles (max 5 per cycle) |
| `icsi` (ICSI history) | 2,332 | separate denormalized history: manual past attempts + rows copied by `endpreg()`. **Do not add it to the cycle count** |
| `infertilitysheet` | 12,764 | **6,495 orphan rows with `patientid=NULL`** (bug: created when the screen opens without a patient); **6,267 real patients**; 4,621 with any diagnosis |
| `newvisit` | 12,308 | 3,809 sheets |
| `folliculom` | 5,252 | 1,195 sheets; 1,772 rows with a TOO (trigger) value |
| `semeninfertility` | 5,729 | 2,750 sheets |
| `semen2` | 1,080 | |
| `hormonalprofile` (male) / `hormonalprofile2` (female) | 639 / 6,082 | female: 3,398 sheets |
| `tvs` / `dtvs` / `sis` | 3,891 / 1,442 / 11 | |
| `hsginfertility` / `hysteroscopyinfertility` / `laparoscopyinfertility` | 2,219 / 1,312 / 817 | |
| `infertilityother` / `operations` | 1,694 / 2,190 | |
| `awifep` / `wifeepc` | 8,063 / 2,858 | |
| `infertilitysheetdrugs` / `infertilitysheetinvest` | 24,583 / 21,859 | Rx / orders with a `forhusband` flag |
| `mointoringsheet` / `…visits` / `…drugs` | 4 / 0 / 2 | abandoned |
| `infertility`, `infertilitydiagnosis`, `infertilityinvest`, `infertilitylmp`, `infertilitynotes` / `infertilitydrugs`, `gynainfertility`, `gynainfertilityplan` | 1 each / 0 | dead v1 |
| `patients` | 9,817 | |

**Closed cycles by year** (`historytoday` = close date):

| Year | Fresh | Frozen |
|---|---|---|
| 2021 | 74 | 42 |
| 2022 | 127 | 58 |
| 2023 | 151 | 67 |
| 2024 | 117 | 90 |
| 2025 | 89 | 85 |
| 2026 (to Oct) | 57 | 75 |

**Cycles per patient (closed):** 1→207 patients, 2→137, 3→74, 4→26, 5→23, 6→6, 7→4, ≥8→5.

### 2.2 Key table columns (DESCRIBE)
- **`ivfsheet`** (75 cols): `id int`, `patientid int`, `endpreg int`, `frozenor`, `historytoday datetime`, `bmi`.
  - Attempt: `anevaluation, anevaluations, antype, anduration, anamh, annn, antypes, anprotocol, anttt, annotes, e2d2, pla2ce, pla2cen, sheetyear, sheetmonth, importantnote`.
  - Fresh: `lmpfresh, toocd, toodate, toodrug, opudate, opun, opumll, opuml, opugv, opuat, oopuu, opunote, ssemen, sseemen, sseemmen, sseemmeen, fefn, fefa, fefb, fefc, sssnofte, sssresult(text), ssscd, ettdate, ettn, ettg, ettnote, ettcancel, frrcd, frrst, frren, ooutcomehsgd/n, ooutcometvsd/n, ooutcomedeld/n`.
  - Frozen: `lmpfrozen, ssnofte, ssresult, etcd, etdate, etn, etg, etnote, etcancel, refren, outcomehsgd/n, outcometvsd/n, outcomedeld/n`.
- **`ovst`**: `id, ivfsheetid int, ovstcdfh, ovstdate, ovstag, ovsthmg`, 24 follicle-bucket columns (R/L × 10–20 mm + N), `eovst` (endometrium), `eeovst` (E2), `noteovst`.
- **`eprep`**: `id, ivfsheetid (varchar!), lmpfrozen, eprepcd, eprepdate, eprepttt, eprepe, eprepro, epreplo, eprepcancel, cdlps, epreplps, thawingdate, thawingstn, thawingen`.
- **`sefo`**: `id, ivfsheetid`, plus a copy of the fresh ET/FR/outcome column set (`sssnofte, sssresult, ssscd, ettdate, ettn, ettg, ettnote, ettcancel, frrcd, frrst, frren, ooutcome*`).
- **`icsi`**: `id, infertilitysheetid, date, icsiprotocol, icsiplace, icsisemen, opu, fert, icsiss, et, vd, fr(text), refr, e2d2, icsiresult, sefoid int`.
- **`folliculom`**: `id, infertilitysheetid, lmp, ttt, cd, date, ro, lo, endo, pelvis, too, result`.
- **`infertilitysheet`** (62 cols): see §1.2. Multi-select columns hold CSV id lists: `husbndiagnosis1/2/3, diagnosis, ttttype, sheethusband, sheetwife, medicalhistory(dm), surgicalhistory(dm), familyhistory, examinationlocalse, localvulva/vagina/cx, generalbreast`.
- Full DESCRIBE dumps for `semeninfertility, semen2, hormonalprofile(2), tvs, dtvs, sis, hsginfertility, hysteroscopyinfertility, laparoscopyinfertility, awifep, wifeepc, infertilitysheetdrugs/invest, mointoringsheet(+visits)` match the field lists in §1.2 and §1.4 one-to-one (column = UI `name`).

### 2.3 Lookup and option tables (active values; `del IS NULL`)
Every lookup table has the shape `(id, title, del)` and supports inline add.

**Cycle / indication / protocol**
| Lookup | Values |
|---|---|
| `antype` (IVF indication) | 1ry, 2ry, sex selection, 2ry inf + sex selec, 2ry(m2), 1ry(m2), 1ry(m3), PGT-M, PGT-A, f. SS, fert preserv (oocyte fr), Fert Preserv (emb Fr) |
| `typeinf` (infertility type, sheet header; duplicate concept) | 1ry, 2ry, sex selection, 1ry (M2), 2ry(m2), 1ry (M3), 1ry(m4), 2ry(m3), sex selection + PGTA, Sex selection + PGTM, F. SS, fert perservation |
| `icsiprotocol` (used by IVF sheet) | long ag, antag, short ag, aC-fET, pPOS, AC-FET with supp, PP-cohh, SASA, OS-FET, Flexible PPOS, mNC-fET |
| `anprotocol` (unused duplicate) | long ag, antag, short ag |
| `antypes` | fresh, frozen |
| `ttttype` (OI/COS drug, infertility sheet) | CC, nolvadex, letrozole, HMG, tecnovula, femara, trexozola, no folliculometry, +++++, (1 doctor-named entry) |
| `tttresponse` | good, bad, ?? |
| `tttda` / `tttmetformin` | yes / --  ·  yes, --, gluco 500, cidophage |
| `epreplps` (luteal support regimens, FET) | duph1x3-Pronto400 1x2, duph - cyclogest, progest, prontogest, 1 ch - duph - pronto, 2 ch - pronto + duph, cyclo + progest d14, progest + pronto + 2Ch, progest + Cyclogest (+1 entry holding a date) |

**Sperm**
| Lookup | Values |
|---|---|
| `icsisemen` (source) | sample, TESA, TESE, mic TESE, −ve Mic TESE |
| `sseemen` | fresh, frozen |
| `ssemen` | good, bad |
| `sseemmeen` | Freezing, no freezing (+ lorem-ipsum test rows) |
| `sementype` | conventional, CASA |

**Outcome**
| Lookup | Values |
|---|---|
| `icsiresult` | +ve, −ve, +ve then EPL |
| `icsiss` | --, m, f (sex-selection result) |

**Husband diagnosis (3 parallel multi-selects)**
| Lookup | Values |
|---|---|
| `husbndiagnosis1` (axis) | Hypo-Hypo, Hyper-Hypo, Normo-Hypo, OA, N, NOA, borderline |
| `husbndiagnosis2` (pattern / testis) | Oligo, Astheno, Terato, OAT, NOA, small testes, AZO, Aspermia, OA, mod size testes, lt, single, testis, absent, rt |
| `husbndiagnosis3` (severity / aetiology) | mild, severe, sertoli cell only, klinefilter, kallman, yq micro del, Mumps, TB, CBAVD, chemo, impotence, PME, RGE, Pit Stalk Tumor, Erectile dysfunction |

**Wife diagnosis (`diagnosis`, 66 active)**
- N, PCO, PCOD, Bil classic PCO, Hyper PRL, pit micro prolactinoma, POI, AMA, DOR
- PID (Hydrosalpinx), PID (TOC), chronic PID, tubal block (Rt/Lt), bil tubal disconn, Disconnected rt f.t, lt tubal disconnection, bil salpingectomy, Salpingectomy
- suspected Endometriosis, Endometrioma, endometriosis, adenomyosis, pelvic adhesions, frozen pelvis
- ut septum, subseptate ut, corrected arcuate / subseptate / ut septum / T-sh / niche, ut fibroid, ut polyp, ut synechiae, ascherman synd, infantile / unicornuate / hypoplastic ut, mull agenesis, niche
- RIF, repeated implant failure, Repeated ICSI failure, prev failed ICSI, rec misc, RPL
- unexplained inf, single ov, oophrectomy, simple / regressive EH, SEH, ch.endometritis, cx polyp, aPS, cAH, abn TOR, hypothyroid, myomectomy, empty follicle syndrome
- side/qualifier tokens mixed in: bil, lt, rt, partial

**Male advanced work-up results**
| Lookup | Values |
|---|---|
| `semen2resultsdf` | n (<15%), gray (15–30%), abn (>30%), DFI > 30% |
| `semen2resultyqmicro` | n, no deletion, AZF a/b, AZF c, AZF A+B+C, AZF b+C, AZF a+C |
| `semen2resultkaryo` | n, klinefilter, 45 xY der(13,14), 46,x,i(yq), 46,x,inv(y) |
| `semen2resulttrue` | n, OA, bil narrow ejac ducts, prostatitis, calcification |
| `semen2resultscrotal` | 60 mixed tokens: varicocele grades, sides, testis sizes, hydrocele, spermatocele, … |

**Gyn / ultrasound / endoscopy findings (counts and flavour)**
- `tvsut` 91: AVF/RVF I–III, fibroid FIGO class 0–7, adenomyosis, arcuate/subseptate/septate, endometrium thin/thick/trilaminar, niche…
- `tvsro`/`tvslo` 38/41: PCO, classic PCO, cysts (simple/hgic/chocolate/dermoid), endometrioma, hydrosalpinx, TOC, low AFC…
- `tvscx` 18, `tvspelvis` 20, `dtvsresult` 33 (arcuate…didelphys, T-shaped), `sisresult` 6.
- HSG: `hsgut` 28, `hsgrt`/`hsglt` 32 each (patent, proximal/distal block, hydrosalpinx, spill), `hsgpelvis` 2.
- Hysteroscopy: `copycx` 27, `copycavity` 71, ostium seen/not seen/narrow; interventions `copym*` (septum removal cold scissor/resectoscope, polypectomy, adhesiolysis, PRP, scratch…).
- Laparoscopy: `laparul`/`rt`/`lt`/`ro`/`lo`/`pelvis` ~30–50 each (+ve/−ve MBT, hydrosalpinx, endometriosis…); interventions `laparm*` (LOD + drill count, cystectomy, salpingectomy, disconnection, adhesiolysis…).
- Menstrual: `menstrualreg` 16 (Reg, iRReg, oligo, amenorrhea types, PCO-like, perimenopause); `newvisitcycles` 16 is a **duplicate** of the same concept.
- Pregnancy loss: `wifeepctype` (EPL, A T1/T2, C/P VM, EP, PUL, complete/incomplete/therapeutic/illegal A); `wifeepcttt` (conservative, miso, MTX, D&C, lap/laparotomy otomy/ectomy, hysterotomy, NVD±D&C).
- Plans: `sheethusband` 40 / `sheetwife` 82, mostly whole instruction sentences used as pick values (e.g. "hysteroscope then ICSI", "Prepare for FET", "HSG Then HMG").
- History: `medicalhistory` 191, `surgicalhistory` 146, `familyhistory` 50, `medicalhistorydm` 73 / `surgicalhistorydm` 39 (husband).

**Facility / person lists (counts only, not reproduced):**
- `icsiplace` 28, `pla2cen` 7 (IVF centers), `place` 22
- `semenplace` 245, `hsgplace` 120, `copyplace` 84, `laparplace` 82, `dtvsplace` 4
- `semen2placesemenfr` 26, `semen2placetese` 19
- `wifeobst` 349, `wifeepcobst` 135 (doctor/hospital names)
- `sheetlocation` 27 (countries/cities, husband travel)

### 2.4 Aggregates (closed cycles)
**Protocol** (`anprotocol`→`icsiprotocol`)
| Protocol | Cycles |
|---|---|
| aC-fET | 399 |
| antag | 306 |
| pPOS | 165 |
| long ag | 101 |
| (none) | 27 |
| SASA | 9 |
| short ag | 8 |
| Flexible PPOS | 8 |
| AC-FET with supp | 6 |
| PP-cohh, OS-FET, mNC-fET | 1 each |

**Indication** (`antype`)
| Indication | Cycles |
|---|---|
| 1ry | 620 |
| 2ry | 207 |
| sex selection | 120 |
| 1ry(m2) | 26 |
| (none) | 20 |
| 2ry + sex selection | 14 |
| PGT-A | 13 |
| PGT-M | 5 |

**Sperm source** (fresh closed): sample 433, none 141, TESA 16, micro-TESE 15, −ve micro-TESE 5, TESE 5.

**Fill rates, fresh closed (n=615)**
| Field | Filled |
|---|---|
| LMP | 599 |
| trigger | 522 |
| OPU N | 511 |
| MII | 485 |
| fert N | 496 |
| ET N | 420 |
| **ET grade** | **160** |
| frozen N | 465 |
| **hCG** | **193** |
| **TVS** | **80** |
| **delivery** | **80** |
| AMH | 569 |
| E2d2 | 91 |

**Fill rates, frozen closed (n=417)**
| Field | Filled |
|---|---|
| LMP | 410 |
| ET N | 257 |
| ET grade | 202 |
| re-frozen | 238 |
| hCG | 248 |
| TVS | 150 |
| delivery | 148 |

**Outcome tokens**
- Fresh hCG: mostly `-ve`, `+ve`, `<2`, `<1`, `??`, `+ve--> EPL`, or raw β-hCG titers (e.g. "441", "972").
- Frozen hCG: same, plus `_ve` typo variants.
- TVS: narrative ("DCT GS=…w EDD=…", "CRL=…w puls").
- `icsi.icsiresult`: −ve 785, +ve 60, <2 49, +ve→EPL 23, +ve→T1 mis 14, cancel 14, +Ve→DCT 9; 884 empty.

**Repurposed fields**
- `sssresult` ("S.S result") actually holds freeze-all/cancel reasons: PPOS 42, OHSS 28, PP-COH 13, PPOS&OHSS 13, Severe OHSS 4, cancel…
- `ettg` (ET grade) is free text: "2", "bL", "a", "morula", "B.C", "bC", "8C(b)", "8 cell"…
- `toodrug` (trigger) is free text: "2 ch", "2Ch 2am", "2tripto", "2Deca 1am"… (drug + dose + time in one string).
- `ovsthmg` (stim dose) is free text: "same", "f150", "g150+g75+men", "G150+Epig75"…; `ovstag`: "--", "same", "Cetro", "1/5 tripto", "1/2 deca"…

**Infertility sheet**
- `sheettype`: 1ry 2,343, 2ry 937, 1ry(M2) 171, sex selection 122.
- Top wife diagnoses: PCO 890, DOR 339, PCOD 291, corrected arcuate 219, AMA 201, Bil classic PCO 171, prev failed ICSI 143, endometriosis 99, corrected subseptate 89.
- Top husband dx2: Astheno 442, Oligo 330, AZO 103, OAT 96, Terato 84; dx1: N 692, Hyper-Hypo 34, Normo-Hypo 26.
- COS drug use (`ttttype`): CC 1,147, HMG 1,022, letrozole 484, nolvadex 173, tecnovula 115.
- CSV multi usage: 779 of 4,273 wife-dx rows have more than one value; 2,472 of 4,525 wife plans.

---

## 3. "IVF statistics" (`Ivfstatistics.php`): what it actually computes
**It computes no rate or KPI at all.** It is a **per-cycle line listing** (DataTable, 36 columns, printable).

- **Filter:**
  - Without a patient: `ivfsheet WHERE lmpfresh BETWEEN from AND to OR lmpfrozen BETWEEN from AND to`.
  - With a patient: the same, plus `patientid`.
  - The comparison is on **varchar `Y/m/d` strings**, and there is **no `endpreg=1` condition**, so open cycles are included.
- **Derived values (the only "calculations"):**

| Column | Formula |
|---|---|
| Age | `calcAge(patients.dateofbirth, lmpfresh or lmpfrozen)`: whole years |
| BMI | `ivfsheet.bmi`, a snapshot copied from the latest `examination` at `endpreg()` |
| COH · HMG | first `ovst` row's `ovsthmg` (starting dose text) |
| COH · Durat | fresh: `COUNT(ovst)` (number of monitoring visits, **not** stimulation days); frozen: `COUNT(eprep)` |
| COH · E | fresh: last `ovst.eovst` (endometrium); frozen: last `eprep.eprepe` |
| Thawing date / ST.N | fresh: **first** `eprep` row; frozen: **last** `eprep` row |
| everything else | raw column echo: AMH, evaluations, type, duration, trial N, protocol title, TOO drug, OPU date/N/MII, `frozenor`, semen source title, F/E D2, SS, ET date/CD/N/G/cancel, Fr CD/ST/EN, Re-Fr, hCG/TVS/Delivery results |

- **Bugs:**
  - `showprint()` queries **`ancsheet` by EDD** (a copy-paste from the EDD report), so the IVF print action prints the wrong dataset.
  - The "Semen · Fresh/frozen" column shows `frozenor` (the **cycle** type), not the sperm state.
  - The frozen branch shows `frrcd/frrst/frren`, which are fresh-only fields.
- **Reality:** all pregnancy, implantation, fertilization and cumulative rates are computed **by hand in Excel**, if at all. The free-text outcome fields (§2.4) make automatic computation impossible without manual cleanup.

---

## 4. Weaknesses and gaps compared with what an IVF unit needs

### 4.1 Data model
| # | Legacy weakness | Evidence | What an IVF unit needs |
|---|---|---|---|
| 1 | **Cycle = one wide row**; fresh and frozen are column families on the same row; extra ETs go in a copy-paste child (`sefo`) | `ivfsheet` 75 cols, `sefo` duplicates the column set | `art_cycles` with typed cycle kinds (fresh OPU, freeze-all, FET, oocyte freezing, IUI, OI/TI, donor), plus child tables per stage |
| 2 | **FET has no link to the source OPU / embryo cohort** | no FK; thawing = "ST.N/EN" text | embryo → cohort → OPU cycle lineage, required for **cumulative LBR per OPU** |
| 3 | **No embryology**: OPU shows MII/MI/GV/At counts only; fertilization = one number; grading = A/B/C counts on Day 2 only | `opumll/opuml/opugv/opuat`, `fefn/fefa/fefb/fefc` | per-oocyte record (maturity, ICSI/IVF/IMSI/PICSI, 2PN/1PN/3PN), **per-embryo day-by-day** (D1–D6, Gardner/Istanbul grading), fate (ET/freeze/biopsy/discard), embryologist, timestamps |
| 4 | **No cryo inventory** | `frrst` (straws) / `frren` (embryos) as varchar | device/straw ID, tank/canister/cane/position, vitrification date, stage/grade, thaw events + survival, storage consent/expiry, storage billing, audit |
| 5 | **No PGT linkage** despite PGT-A/PGT-M/sex-selection indications | `antype` has PGT values; result is free text in `sssresult`/`icsiss` (m/f) | biopsy per embryo, lab, result (euploid/aneuploid/mosaic/sex/monogenic), transfer eligibility |
| 6 | **Luteal support exists only for FET** (one regimen lookup on `eprep`); fresh LPS is not recorded | `epreplps` 9 regimens; 1,148 of 1,542 eprep rows empty | LPS plan per cycle (drug, route, dose, start/stop) for fresh and FET |
| 7 | **Trigger** is one free-text string | `toodrug` "2Ch 2am" | trigger drug, dose, **datetime** (OPU at trigger + 34–36 h), dual trigger |
| 8 | **Stimulation** doses and drugs are free text per day; GnRH analogue is free text | `ovsthmg` "G150+Epig75", `ovstag` "1/5 tripto" | structured drug/dose/IU per day; start day; total gonadotropin dose; days of stimulation (computed) |
| 9 | **Follicles** = 24 fixed columns (R/L × 10–20 mm + N); <10 mm and >20 mm cannot be recorded; values are text ("1(10.5)", "S") | `ovst` columns | list of follicle diameters per ovary per scan (or size buckets as rows), AFC at baseline, endometrium thickness **and pattern**, E2/LH/P4 numeric |
| 10 | **Outcome** = 3 free-text date/result pairs (HCG, TVS, Delivery); hCG mixes tokens and titers; no coding | 193/615 fresh have hCG, 80 have TVS/delivery | serial β-hCG (numeric mIU/mL, day post-ET), biochemical / clinical (GS) / FH / ongoing (≥12 w) / miscarriage / ectopic / **live birth + number of babies**, coded |
| 11 | **Cancellation / OHSS / freeze-all reasons** buried in the S.S result field | `sssresult` PPOS 42, OHSS 28… | coded cancel reason + stage; OHSS grade; freeze-all reason |
| 12 | **IUI** = 3 scalars per patient, overwritten each time | `sheettrials/sheetdate/sheetresult` | IUI as a cycle: stimulation, trigger, semen prep (pre/post-wash TMSC), double/single, outcome |
| 13 | **OI folliculometry** (`folliculom`) separate from IVF COS (`ovst`) with a different free-text shape | RO/LO/Endo as text | one monitoring model for OI/IUI/IVF/FET |
| 14 | **Every column is varchar, dates included** (`Y/m/d` strings); `eprep.ivfsheetid` itself is varchar | DESCRIBE | typed columns (DATE, INT, DECIMAL), FKs, indexes |
| 15 | **CSV multi-values** for diagnoses, plans, findings | 779/4,273 wife-dx rows multi | normalized link rows (`encounter_diagnoses`), optional ICD-10/ICD-11 / SNOMED, so "how many patients have X" becomes a query |
| 16 | **Duplicate lookups for one concept** | `antype` vs `typeinf`, `icsiprotocol` vs `anprotocol`, `antypes` vs `sseemen`, `menstrualreg` vs `newvisitcycles`; side tokens (rt/lt/bil) mixed into dx lists | one managed catalog keyed by `lookup_key`, with laterality as a separate attribute |
| 17 | **Junk / test data in live lookups and rows** | lorem-ipsum values in `sseemmeen`, `sheethusband`; 6,495 orphan `infertilitysheet` rows; ~953 empty `ivfsheet` rows | validation, no side-effect inserts on GET |
| 18 | **`icsi` history is denormalized**: copied titles, not ids; auto vs manual rows indistinguishable (`sefoid` bug) | `endpreg()` | history = a query over cycles, never a copy |
| 19 | **No KPIs** (§3) | `Ivfstatistics` is a register | ESHRE/Vienna-consensus KPIs (§4.2), age bands, per-doctor/per-center |
| 20 | **No witnessing, consents, roles per stage, or audit** | generic `Add/update/append/newcd` endpoints take **table and column names from POST** (any table is writable); `newcd`/`folliculom` interpolate values into raw SQL | double-witness (patient ↔ gametes ↔ embryos), consent records (ICSI, freezing, storage duration, disposal, PGT), per-stage permissions, immutable audit trail |
| 21 | **Sperm**: source and fresh/frozen are separate lookups; no day-of-ICSI parameters; no donor | 4 selects | sperm sample entity (source, fresh/frozen, cryo link, pre/post-prep params) |
| 22 | **Multi-center**: "place" lists are free lookups of doctor/center names | `pla2cen`, `icsiplace`, `place` | proper facility/clinic entity; the doctor is a user |

### 4.2 KPIs an IVF unit expects (none computed in the legacy system)
**Per started cycle / per OPU:**
- cancellation rate
- oocytes retrieved per OPU
- MII rate (MII / COCs)
- fertilization rate (2PN / MII injected)
- poor fertilization / failed fertilization
- cleavage rate
- blastulation rate (blasts / 2PN)
- usable embryo rate
- OHSS rate
- freeze-all rate

**Per ET:**
- positive β-hCG rate
- implantation rate (GS / embryos transferred)
- clinical pregnancy rate
- ongoing pregnancy rate
- live birth rate
- miscarriage rate
- multiple pregnancy rate

**Cryo:**
- post-thaw survival rate
- FET CPR / LBR

**Cumulative:**
- cumulative live birth rate per OPU (requires the cohort lineage in #2)

**Breakdowns:** age bands (<35, 35–37, 38–40, 41–42, >42), protocol (antag / long / PPOS…), indication (1ry/2ry/PGT/sex selection), sperm source (ejaculate vs TESA/micro-TESE), doctor/embryologist, year/month.

### 4.3 What is worth keeping from the legacy design
- **Pick-don't-type** with inline add and per-site finding lists. These are the real clinical vocabularies the doctor uses: `diagnosis`, `husbndiagnosis1/2/3`, `icsiprotocol`, `antype`, `icsisemen`, `epreplps`, `ttttype`, HSG/hysteroscopy/laparoscopy findings and interventions. Seed them into the new catalog after dedup and laterality split.
- **Auto date from cycle day** (CD → date = LMP + CD − 1) on monitoring/ET rows: doctors think in cycle days.
- **One continuous grid for the cycle** (attempt → COS days → trigger → OPU → lab → ET → freeze → outcome) on one screen. This matches how the doctor reads a cycle and should survive as the **cycle summary view**, but backed by normalized stage tables.
- **Husband and wife side-by-side** on one sheet, with Rx and investigations carrying a `forhusband` flag.
- The abandoned `mointoringsheet` v2 had the right direction: typed dates, FK lookups, oocyte maturity split, cryo day/N, attempt number, and a per-visit child table.