# Module: Andrology & Semen Analysis (andrology)

## Purpose
Male-factor workup of the infertility patient: semen analyses (quick per-patient entries and structured rows inside the infertility sheet), advanced andrology investigations (scrotal ultrasound, "TRUE" exam — likely TRUS, karyotyping, Y-chromosome microdeletion, sperm DNA fragmentation SDF, semen-Fr test, TESE), husband/wife hormonal profiles, and the semen attributes recorded on the IVF cycle sheet. Data is captured through two parallel paths: the generic patient Investigations screen (`semen`, `hormon`) and the infertility sheet (`semeninfertility`, `semen2`, `hormonalprofile`, `hormonalprofile2`).

## Controllers & Views (file paths)
- `/home/amrtechogate/public_html/obgy/core/controllers/investigation.php` — Investigations screen. `index()` auto-creates an empty `semen` row (and `hsg`, `pathology`, etc.) per patient if none exists; lists `semen` and `hormon` rows (`del = 0`, joined to `awusers` for doctor name). Generic `addRow()` (dispenses any `tableName` from POST with `sdate/patientid/doctorid/del`), `delData()` (soft delete `del = 1`), `update()` (generic cell update). Views: `core/views/obgy/investigation/investigation.html` and `investigation1.html` (toggled by `programesetting.investshow`).
- `/home/amrtechogate/public_html/obgy/core/controllers/infertilitysheet.php` — Infertility sheet. `index()` finds/creates `infertilitysheet` by `patientid`, loads `semeninfertility`, `semen2`, `hormonalprofile`, `hormonalprofile2` by `infertilitysheetid`. `append()` creates a child row generically (POST `tablename` + `tablep=infertilitysheetid` + `id`). `update()` is a generic per-cell AJAX save (POST `tableName`/`colName`/`value`). `getselectajax()` inserts a new option (`title`) into any lookup table named in POST `celtable`; `getselect()/getdataselect()/addnewselect()/deldataselect()` manage lookup options; `deleterow()` hard-deletes a row. Views: `core/views/obgy/infertilitysheet/add.html` (semen rows ~lines 220-340, hormonal profile 1 ~367-400, hormonal profile 2 ~708-730), `semen.html` (new-row template), `append.html`, `select.html`.
- `/home/amrtechogate/public_html/obgy/core/controllers/infertilitysheet00.php` — legacy variant; queries `R::findAll('semen', 'infertilitysheetid = ?')` although `semen` has no `infertilitysheetid` column (dead/broken legacy code).
- `/home/amrtechogate/public_html/obgy/core/controllers/sh.php` — read-only "check"/review report. Loads `semeninfertility` + `semen2` resolving all lookups (`semenplace`, `sementype`, `semen2place*`, `semen2result*`; scrotal result resolved with `id in (0$csv)` string interpolation), plus `hormonalprofile`/`hormonalprofile2`; IVF section resolves `ivfsheet.ssemen/sseemen/sseemmen/sseemmeen` against `ssemen`, `sseemen`, `icsisemen`, `sseemmeen`. Records the visit in `lastvisit`. Functions: `index()`, `Add()`, `search()`, `showprint()`, `onesetup()`.
- `/home/amrtechogate/public_html/obgy/core/controllers/ivfsheet.php` + `ivfsheet00.php` — IVF cycle sheet; four selects under the "Semen" column group of the fresh-cycle table write to `ivfsheet.ssemen/sseemen/sseemmen/sseemmeen` via the generic update; option lists come from `ssemen`, `sseemen`, `icsisemen`, `sseemmeen`. Views: `core/views/obgy/ivfsheet/add.html` (~lines 414-428), `ivfhistory.html` (~359-372).
- `/home/amrtechogate/public_html/obgy/core/controllers/drugsex.php` — misleading name; it is the drugs-catalog CRUD (`drugs` table: server-side DataTables `show()`, `adddrug()`, `updatedrug()`, `deletedrug()`, autocomplete `getthisserach()`). Not semen-related; note: DataTables search builds SQL by string concatenation with `LIKE '%$_GET[sSearch]%'` (SQL injection).
- Reporting consumers: `completereport.php`, `fullreport.php`, `Completesreport.php`, `excel.php`, `merge.php` (semen/hormon appear in combined reports), views under `core/views/obgy/reports*/`.

## Tables
All from `/home/amrtechogate/public_html/obgy/_db/obgy_12-7-2024.sql`. No declared foreign keys anywhere; all relations inferred. None of the lookup tables has seed INSERTs in the dump (vocabulary is user-entered at runtime via `getselectajax`).

### semen (dump line 10553)
Per-patient quick semen analysis on the Investigations screen.
- `id` int(11) unsigned PK AI
- `sdate` date
- `patientid` varchar(191) — FK -> patients.id (stored as string)
- `del` int(11) unsigned — soft delete flag (0/1)
- `count` varchar(191) — sperm count
- `motilitya` varchar(191) — progressive motility A
- `motilityab` text — motility A+B
- `normal` varchar(191) — normal forms %
- `puscell` varchar(191)
- `notes` varchar(191)
- `normaltype` int(191), `oligo` int(191), `astheno` int(191), `tetrato` int(191) — checkbox classification flags (Normal/Oligo/Astheno/Teratozoospermia)
- `doctorid` int(11) unsigned — FK -> awusers.user_id

### semen2 (line 10578)
Wide row of advanced andrology investigations per infertility sheet ("Other Inv" section). One trio (date/place/result) per test.
- `id` int(11) PK AI
- `infertilitysheetid` int(11) unsigned — FK -> infertilitysheet.id
- Lookup-id columns (varchar storing lookup ids): `semen2placesemenfr`, `semen2resultscrotal` (comma-separated multi-select), `semen2resulttrue`, `semen2placetese`, `semen2resultyqmicro`, `semen2resultsdf`, `semen2resultkaryo`
- Date columns (varchar, datepicker): `detescrotal`, `detetrue`, `detekaryo`, `deteyqmicro`, `detesdf`, `detesemenfr`, `detetese`, `deteother`
- Free-text place columns: `placescrotal`, `placetrue`, `placeyqmicro`, `placekaryo`, `placesdf`, `placeother`
- Free-text result columns: `resultother`, `resulttese`, `resultsemenfraf`, `resultsemenfrm`, `resultsemenfrc` (three sub-results of the "semen Fr" test — exact clinical meaning uncertain, likely fructose/fractions)

### semen2placesemenfr (10616) — lookup
`id` int unsigned PK, `title` varchar(191), `del` tinyint(1). Place options for the semen-Fr test. No seed data.

### semen2placetese (10629) — lookup
`id`, `title` varchar(191), `del` int. TESE place options. No seed data.

### semen2resultkaryo (10642) — lookup
`id`, `title`, `del` tinyint. Karyotyping result options. No seed data.

### semen2resultscrotal (10655) — lookup
`id`, `title`, `del` tinyint. Scrotal ultrasound findings; referenced as CSV multi-select from `semen2.semen2resultscrotal` and queried via `"id in (0$csv)"` string interpolation in sh.php. No seed data.

### semen2resultsdf (10668) — lookup
`id`, `title`, `del` tinyint. Sperm DNA fragmentation result options. No seed data.

### semen2resulttrue (10681) — lookup
`id`, `title`, `del` tinyint. Result options for the "TRUE" exam (inferred: TRUS — transrectal US). No seed data.

### semen2resultyqmicro (10694) — lookup
`id`, `title`, `del` tinyint. Y-chromosome microdeletion result options. No seed data.

### semeninfertility (10707)
Semen analysis rows inside the infertility sheet (WHO-style parameters).
- `id` int PK AI
- `infertilitysheetid` int unsigned — FK -> infertilitysheet.id
- `semenplace` varchar — FK -> semenplace.id
- `sementype` varchar — FK -> sementype.id
- `date` varchar(191)
- `nc` (count/concentration), `prm` (progressive motility), `tm` (total motility), `af` (abnormal forms), `vit` (vitality), `vol` (volume), `tzi` (teratozoospermia index), `sdi` (sperm deformity index) — all varchar(191)

### semenplace (10730) — lookup
`id`, `title`, `del` int. Labs/places performing semen analysis. No seed data.

### sementype (10743) — lookup
`id`, `title`, `del` int. Semen analysis types. No seed data.

### sseemen (10841) — lookup (typo-named, USED)
`id`, `title`, `del` tinyint. Option list for 2nd "Semen" select on IVF fresh-cycle table; selected id stored in `ivfsheet.sseemen`.

### sseemmeen (10854 area, line 10841/10854) — lookup (typo-named, USED)
`id`, `title`, `del` tinyint. Option list for 4th "Semen" select; stored in `ivfsheet.sseemmeen`.

### sseemmen (10854) — ORPHAN table
`id`, `title`, `del` tinyint. Never read by any controller/view: the select named `sseemmen` loads its options from `icsisemen` (IVF module table) and stores the chosen id into the `ivfsheet.sseemmen` COLUMN. The `sseemmen` TABLE itself is dead — candidate for drop.

### ssemen (10867) — lookup (typo-named, USED)
`id`, `title`, `del` tinyint. Option list for 1st "Semen" select on IVF sheet; stored in `ivfsheet.ssemen`. The four selects sit under a "Semen" column group with blank sub-headers; their clinical semantics (source/preparation/etc.) are user-defined (uncertain — confirm with clinic).

### hormon (7478)
Per-patient hormonal lab panel on the Investigations screen (mixes fertility hormones with tumor markers). All result columns varchar(191).
- `id` int unsigned PK AI, `sdate` date, `patientid` varchar(191) FK -> patients.id, `del` int (soft delete), `doctorid` int FK -> awusers.user_id, `notes` varchar
- Hormones: `lh`, `fsh`, `prl`, `etwo` (E2), `amh`, `freet` (free testosterone), `totalt` (total testosterone), `pfour` (P4), `tsh`, `tthree` (T3), `tfour` (T4), `dht`, `bhcg`, `inhibinb`, `asd` (androstenedione), `dheas`
- Tumor markers / misc: `ca125`, `cea`, `calgg`, `ldh`, `tfehprotein`

### hormonalprofile (7515)
Husband hormonal profile rows in the infertility sheet — date/result pair per hormone, all varchar(191).
- `id` int PK AI, `infertilitysheetid` int unsigned FK -> infertilitysheet.id
- Pairs: `datefsh/resultfsh`, `datelh/resultlh`, `datett/resulttt` (total testosterone), `dateprl/resultprl`, `datee2/resulte2`, `dateinhibin/resultinhibin`, `dateother/resultother`, `dateother1/resultother1`

### hormonalprofile2 (7543)
Second hormonal profile in the infertility sheet (includes AMH/TSH/P4 — inferred to be the wife's panel).
- `id` int PK AI, `infertilitysheetid` int unsigned FK -> infertilitysheet.id, `date` varchar
- Pairs: `dateamh/resultamh`, `datetsh/resulttsh`, `dateprl/resultprl`, `datefsh/resultfsh`, `datelh/resultlh`, `datee2/resulte2`, `datep4/resultp4`, `dateother/resultother`

## Relationships (inferred; no declared FKs)
- semen.patientid -> patients.id (varchar vs int mismatch)
- semen.doctorid -> awusers.user_id
- hormon.patientid -> patients.id
- hormon.doctorid -> awusers.user_id
- semeninfertility.infertilitysheetid -> infertilitysheet.id
- semeninfertility.semenplace -> semenplace.id
- semeninfertility.sementype -> sementype.id
- semen2.infertilitysheetid -> infertilitysheet.id
- semen2.semen2placesemenfr -> semen2placesemenfr.id
- semen2.semen2placetese -> semen2placetese.id
- semen2.semen2resultscrotal -> semen2resultscrotal.id (CSV multi-value)
- semen2.semen2resulttrue -> semen2resulttrue.id
- semen2.semen2resultkaryo -> semen2resultkaryo.id
- semen2.semen2resultyqmicro -> semen2resultyqmicro.id
- semen2.semen2resultsdf -> semen2resultsdf.id
- hormonalprofile.infertilitysheetid -> infertilitysheet.id
- hormonalprofile2.infertilitysheetid -> infertilitysheet.id
- ivfsheet.ssemen -> ssemen.id; ivfsheet.sseemen -> sseemen.id; ivfsheet.sseemmeen -> sseemmeen.id; ivfsheet.sseemmen -> icsisemen.id (NOT the sseemmen table)
- infertilitysheet.patientid -> patients.id (parent of this module's sheet tables)

## Business Workflows (traced from code)
1. **Quick investigations path**: opening `investigation.php?patientid=N` auto-inserts an empty `semen` row (today's date, session doctor) if the patient has none; the screen lists all non-deleted `semen` and `hormon` rows newest-first with doctor names. Cells are saved one-by-one via generic `update` (POST tableName/colName/value); "Add" posts to `addRow` (generic dispense); delete sets `del = 1`.
2. **Infertility sheet path**: `infertilitysheet.php` finds-or-creates the patient's `infertilitysheet`, then renders inline-editable tables. The "Semen" link (`addappend`, `data-add="semeninfertility"`) calls `append()` which dispenses a child row with `infertilitysheetid` set; same mechanism for `semen2`, `hormonalprofile`, `hormonalprofile2`. Every keystroke/select change posts to the generic `update()`.
3. **Runtime vocabulary**: selects with class `getselectajax` load options from the lookup table named in `data-celtable`; typing a new term posts to `getselectajax()` which inserts `{title}` into that lookup table — so semenplace/sementype/semen2* option lists grow organically. Scrotal result is a `multiple` select stored as comma-joined ids.
4. **IVF cycle**: in `ivfsheet/add.html` fresh-cycle table, four selects under the "Semen" header store ids into `ivfsheet.ssemen/sseemen/sseemmen/sseemmeen`; `ivfsheet.php` resolves `icsisemen` title for embryo-transfer table copies.
5. **Review/print**: `sh.php` renders the whole sheet read-only, resolving every lookup id to its title, and logs the access in `lastvisit`. Combined reports (`completereport.php`, `fullreport.php`, `excel.php`) include semen/hormon data.

## ERP Migration Notes
Proposed Laravel models:
- **SemenAnalysis** (`semen_analyses`): merge `semen` + `semeninfertility`. Columns: `patient_id` FK, `infertility_sheet_id` nullable FK, `analyzed_at` DATE, `place_id`, `type_id`, numeric DECIMAL columns `volume`, `concentration`, `progressive_motility`, `total_motility`, `abnormal_forms`, `vitality`, `tzi`, `sdi`, `pus_cells`, `notes`, classification booleans (`is_oligo`, `is_astheno`, `is_terato`, `is_normal`) or derive them, `doctor_id`, `deleted_at`.
- **AndrologyInvestigation** (`andrology_investigations`): normalize wide `semen2` to long format — `infertility_sheet_id`, `type` enum (scrotal_us, trus, karyotype, y_microdeletion, sdf, semen_fr, tese, other), `performed_at` DATE, `place` (or `place_id`), `result_text`, plus pivot `andrology_investigation_result_option` for multi-valued scrotal findings. `semen_fr` sub-results (af/m/c) become three value columns or child rows.
- **AndrologyResultOption** (`andrology_result_options`): merge the 7 lookups `semen2result*`/`semen2place*` plus `semenplace`/`sementype` into typed reference tables (`option_type` discriminator) seeded from production data (dump has no seeds — export live DB values at migration time).
- **HormoneResult** (`hormone_results`): normalize `hormon` + `hormonalprofile` + `hormonalprofile2` to long format — `patient_id`, `infertility_sheet_id` nullable, `hormone_id` FK to a `hormones` reference table (code, unit, reference range, category fertility/thyroid/tumor_marker), `value` DECIMAL, `sampled_at` DATE. Integrate with the ERP lab module rather than duplicating; separate tumor markers (ca125/cea/ldh) into the lab/oncology category.
- **IVF semen lookups**: drop the orphan `sseemmen` table; rename `ssemen`/`sseemen`/`sseemmeen` (+ `icsisemen` from the IVF module) into clearly named lookups after confirming clinical semantics with the clinic (column headers are blank in the UI), and reference them from the IVF cycle model with real FKs.
- Data cleanup during ETL: cast varchar dates (`Y/m/d` strings) to DATE, varchar numerics to DECIMAL, drop auto-created empty `semen` rows (all clinical fields NULL), split CSV scrotal ids into pivot rows, fix `semen.patientid` string -> bigint.
- Replace the generic table/column AJAX endpoints (mass-assignment + SQL-injection surface: `update`, `append`, `getselectajax`, `drugsex.php` DataTables search) with validated Form Requests and per-resource Angular forms.
- `drugsex.php` belongs to the Drugs catalog module, not andrology — exclude from this module's migration scope.