# Module: Ultrasound & Imaging (imaging)

## Purpose
Captures all diagnostic-imaging activity of the OB/GYN clinic: structured obstetric ultrasound reports (per-fetus anatomy survey + biometry), gynecological ultrasound reports, transvaginal scans (TVS / Doppler TVS / SIS) inside the infertility sheet, HSG (hysterosalpingography), MRI/CT findings, 4D-scan booking lists tied to the antenatal sheet, raw image/video attachments (sonar), and an annotatable anatomical diagram tool (GT Image). Source app: legacy PHP + Smarty + RedBeanPHP (`R::`) "aw framework", DB dump `/home/amrtechogate/public_html/obgy/_db/obgy_12-7-2024.sql`.

## Controllers & Views (file paths)
Dedicated controllers (`/home/amrtechogate/public_html/obgy/core/controllers/`):
- `ultrasound.php` — legacy combined obstetric US report (tables `ultrasound` + `ultrasounddetail`). Views: `core/views/obgy/ultrasound/{show,add,babymodel,print,selects}.html`
- `ultrasoundobst.php` — newer obstetric US report (tables `ultrasoundobst` + `ultrasoundobstdetail`). Views: `core/views/obgy/ultrasoundobst/*`
- `ultrasoundgyna.php` — gynecological US report (table `ultrasoundgyna`). Views: `core/views/obgy/ultrasoundgyna/*`
- `sonar.php` — "Pictures and Videos": image/video attachment manager (table `sonar`). Views: `core/views/obgy/sonar/{sonar,sonar1,print,result,scrollimg}.html`
- `gtimage.php` — "GT Image" drag-and-drop annotation over an anatomical base image (tables `gtimage` + `gtdetail`). Views: `core/views/obgy/gtimage/{add,show,showdata}.html`

Shared controllers that read/write this module's tables:
- `investigation.php` — auto-creates and lists `hsg`, `ustv`, `mrict` rows per patient (investigations hub, alongside semen/laparoscopy/hysteroscopy/pathology).
- `infertilitysheet.php`, `infertilitysheet00.php`, `gynasheet.php`, `gynasheet00.php`, `sh.php`, `Completesreport.php` — read `tvs`, `dtvs`, `sis`, `hsginfertility` linked to `infertilitysheetid` and resolve their CSV lookup columns.
- `ancsheet.php`, `operations.php` — create/manage `op_4d_list` 4D-scan bookings linked to `ancsheet`; `operations.php` action `four_d()` shows the booking list with `four_d` and `place` lookups.
- `Completesreport.php` (line ~503) resolves `ancnewvisit.usn / usplace / usaf` against lookup tables `usn`, `usplace`, `usaf` (ANC follow-up visit ultrasound fields).

Upload/storage directories (file-system storage model):
- `/home/amrtechogate/public_html/obgy/upload/sonar/` — ultrasound images, md5-random filenames (~1,694 files at analysis time); DB keeps only filename in `sonar.imagename`.
- `/home/amrtechogate/public_html/obgy/upload/sonarvedio/1/` — videos uploaded via `uploadHEX()` with md5 random names; original filename preserved in `sonar.originalname`, stored name in `sonar.vediourl` (longtext). Download served via `readfile()` in `sonar.php::downloadfile()`.
- `/home/amrtechogate/public_html/obgy/upload/gtimage/` — base diagram images for the GT Image tool (`1.jpg` default, or custom from `programesetting.gtimage`).

## Tables
All from CREATE TABLE statements in the dump. No declared foreign keys anywhere; all relations are inferred from column names and controller SQL. NOTE: the dump contains NO INSERT seed data for any of this module's lookup tables (only 23 INSERTs exist in the whole dump, none for imaging lookups), so seeded vocabulary values could not be extracted — vocabularies live only in the production DB.

### ultrasound (legacy obstetric US report header)
Columns: `id` int PK, `patientid` int, `del` int (soft delete), `done` int (finalized flag), `edate` date, `stype` varchar (scan type), `based` varchar (dating based on), `tcondition` int, `babyno` varchar (number of fetuses), `sid` int (sonographer = awusers.user_id where positionid=4), `indication` varchar, `gage` varchar (gestational age), `physician` varchar, `otherbased` varchar, `limit` varchar (study limitations).
FKs: `patientid -> patients.id`, `sid -> awusers.user_id`.

### ultrasounddetail (per-fetus detail of legacy report)
Columns: `id` PK, `del`, `ultrasoundid` int -> ultrasound.id, anatomy-survey coded ints (0/1/2 enums hardcoded in views): `relationc, appearance, fluid, movement, shape, cavum, falx, thalami, ventricle, cerebellum, magna, u_lip, profile, orbits, nose, nostrils, neck, thoraxshape, mass, heart, heartactivity, hsize, haxix, chamberview, loutflow, routflow, stomach, bowel, kidney, bladder, cordinsertion, cordvessels, spine, rarm, rleg, larm, lleg`; `gender` double (-1 default), `gendern` int, `conclusion` int, `plans` int; biometry varchars: `position, diameterm/diameterp` (BPD measured/percentile), `headcm/headcp` (HC), `abdominalm/abdominalp` (AC), `lengthm/lengthp` (FL), `otherm/otherp`, `othernormal` text, `finding`, `weeks`, `referred`, `oplans`, `cover`; conclusion flags `abconclusion, pconclusion, inconclusion` tinyint.
FKs: `ultrasoundid -> ultrasound.id`.

### ultrasoundobst (newer obstetric US report header)
Columns: `id` PK, `patientid`, `del`, `done`, `edate` date, `stype` int, `sid` int, `tcondition` int, `babyno` double, `physician`, `limit`, `indication`.
FKs: `patientid -> patients.id`, `sid -> awusers.user_id`.

### ultrasoundobstdetail (per-fetus detail, includes 1st-trimester fields)
Columns: `id` PK, `del`, `ultrasoundobstid` -> ultrasoundobst.id, coded ints: `appearance, shape, cranial, falx, choroid, profile, orbits, neck, pulm, diaphragm, heart, heartactivity, hsize, haxix, chamberview, stomach, bowel, kidney, bladder, cordinsertion, cordvessels, spine, rarm, rleg, larm, lleg`, `gender` double, `gendern`, `conclusion`, `plans`; varchars: `anomaly`, `crownm/crownmc` (CRL measured/centile), `nuchalm/nuchalc` (NT), `diameterm/diameterc` (BPD), `headcm/headcc` (HC), `abdominalm/abdominalc` (AC), `flengthm/flengthc` (FL), `finding`, `gaestimatew/gaestimated` (GA estimate weeks/days), `othernormal`, `oplans`, `weeks` double, `referred`; flags `pconclusion, inconclusion, abconclusion`.
FKs: `ultrasoundobstid -> ultrasoundobst.id`.

### ultrasoundgyna (gynecological US report — single flat row)
Columns: `id` PK, `patientid`, `del`, `done`, `edate` date, `etype` int (exam type), `sid` int, `tcondition` int, `position` int (uterus position), `tz` int, `myo` int (myometrium), `myowall` int, `douglas` int (Douglas pouch), `conclusion` int; varchars: `rtsize, physician, indication, measurments, thickness` (endometrium), `flesion` (focal lesion), `rtshape, rtfolicles, ltfolicles, ltshape, ltsize` (ovaries), `pmass` (pelvic mass), `lymph`, `rtarteries, ltarteries` (Doppler), `score`, `subspace`, `anymass`, `normalcon`, `limit`, `describ`, `abnormalcon`.
FKs: `patientid -> patients.id`, `sid -> awusers.user_id`.

### sonar (image/video attachments — MyISAM!)
Columns: `id` PK, `patientid` int NOT NULL, `sonardate` date, `imagename` varchar (md5 filename in upload/sonar), `notes` varchar, `vediourl` longtext (filename in upload/sonarvedio/1), `tempdelete` char(1) default '0' (soft delete), `type` int (0=image, 1=video), `originalname` varchar (original upload filename for videos).
FKs: `patientid -> patients.id`. Storage: files on disk, DB stores names only.

### tvs (TVS exam row inside infertility sheet)
Columns: `id` PK, `infertilitysheetid` int, `tvsut`, `tvsro`, `tvslo`, `tvscx`, `tvspelvis`, `dtvsplace` — all varchar holding **comma-separated lookup ids** resolved via `id in (0$csv)`, `comment`, `date` varchar.
FKs: `infertilitysheetid -> infertilitysheet.id`; CSV multi-FKs to `tvsut/tvsro/tvslo/tvscx/tvspelvis/dtvsplace`.

### tvscx / tvslo / tvspelvis / tvsro / tvsut (lookups: cervix / left ovary / pelvis / right ovary / uterus findings)
Identical structure: `id` PK, `title` varchar, `del` tinyint. No seed rows in dump.

### dtvs (Doppler TVS exam row in infertility sheet)
Columns: `id` PK, `infertilitysheetid` int, `dtvsresult` varchar (CSV of dtvsresult ids), `date` varchar.
FKs: `infertilitysheetid -> infertilitysheet.id`; CSV -> `dtvsresult.id`. Note: `tvs.dtvsplace` carries the Doppler place (CSV -> `dtvsplace.id`).

### dtvsplace / dtvsresult (lookups: Doppler TVS place / result)
`id`, `title`, `del`. No seed rows in dump.

### ustv (US TV — transvaginal US findings record from investigations hub)
Columns: `id` PK, `sdate` date, `patientid` **varchar** (type mismatch vs int elsewhere), `del` int, `uterus`, `radnexa`, `ladnexa`, `pelvis`, `notes` varchar (free text), `doctorid` int.
FKs: `patientid -> patients.id`, `doctorid -> awusers.user_id`. Auto-dispensed empty row by `investigation.php` when patient has none.

### usn / usaf / usplace (lookups used by ANC follow-up visits, NOT by ustv)
`id`, `title`, `del`. Referenced from `ancnewvisit.usn`, `ancnewvisit.usaf` (amniotic fluid, per view field label), `ancnewvisit.usplace` (placenta site) — resolved in `Completesreport.php` and ANC sheet views (`ancsheet/add.html` select boxes with `data-table="ancnewvisit"`). Inference: usn = US notes/normality, usaf = US amniotic fluid, usplace = US placenta site (استنتاج). No seed rows in dump.

### four_d (lookup: 4D scan service items)
Columns: `id` PK, `title` varchar, `del` varchar, `name` varchar. Loaded with `del is null` in `operations.php::four_d()`. No seed rows in dump.

### op_4d_list (4D scan booking/worklist row)
Columns: `id` PK, `patientid` int def 0, `ancsheetid` int def 0, `opdate` date, `four_d` varchar (CSV of four_d ids), `place` int (-> `place` lookup, operations module), `notes` text, `deleted` int, `user_id` int (creator), `createdate` datetime, `t1,t11,t12,t2,t21,t22` int flags (1st/2nd scan booking-done-paid stage flags; `ancsheet.tscandate`/`ttscandate` updates set `t11`/`t21` — exact semantics استنتاج), `done` int, `confirm` int, `t1_userid`, `t2_userid` int (staff who performed scan 1/2).
FKs: `patientid -> patients.id`, `ancsheetid -> ancsheet.id`, CSV `four_d -> four_d.id`, `place -> place.id`, `user_id/t1_userid/t2_userid -> awusers.user_id`. Reverse link: `ancsheet.4d_list_id -> op_4d_list.id` (set in `ancsheet.php::eedlmp()` when EDD is entered).

### gtimage (annotated diagram session header; "GT Image")
Columns: `id` PK, `gdate` date, `patientid`, `doctorid`, `gtid` int (unused/legacy in code paths seen), `comment` varchar, `del`, `pic` varchar (base image filename; `1.jpg` default or `programesetting.gtimage`), `programview` tinyint (copied from programesetting).
FKs: `patientid -> patients.id`, `doctorid -> awusers.user_id`. A new row is auto-created on every visit to `gtimage.php?ac=index`.

### gtdetail (positioned annotation markers on the diagram)
Columns: `id` PK, `gtid` int -> gtimage.id, `posx` double, `posy` double, `width`, `height` int, `imgno` int (which marker icon, e.g. aw/images/{1,3,8,...}.png), `del`, `comment` varchar.
FKs: `gtid -> gtimage.id`.

### mrict (MRI/CT findings record)
Columns: identical to `ustv`: `id`, `sdate` date, `patientid` varchar, `del`, `uterus`, `radnexa`, `ladnexa`, `pelvis`, `notes`, `doctorid`.
FKs: `patientid -> patients.id`, `doctorid -> awusers.user_id`. Auto-dispensed by `investigation.php`.

### sis (Saline Infusion Sonography row in infertility sheet)
Columns: `id` PK, `infertilitysheetid` int, `sisresult` varchar (CSV -> `sisresult` lookup table, which belongs to the infertility module's lookups, not assigned here), `date` varchar.
FKs: `infertilitysheetid -> infertilitysheet.id`.

### hsg (HSG findings record from investigations hub)
Columns: `id` PK, `sdate` date, `patientid` varchar, `del`, `uterus` varchar, `ltube` text (left tube), `notes`, `smear`, `rtube` varchar (right tube), `doctorid` int.
FKs: `patientid -> patients.id`, `doctorid -> awusers.user_id`. Auto-dispensed by `investigation.php`.

### hsginfertility (HSG exam row inside infertility sheet)
Columns: `id` PK, `infertilitysheetid` int, `hsgut` (single id -> hsgut), `hsgplace` (single id -> hsgplace), `hsgrt` (CSV -> hsgrt), `hsglt` (CSV -> hsglt), `hsgpelvis` (single id -> hsgpelvis), `date` varchar. Single vs CSV resolution per `Completesreport.php` (`R::load` vs `id in (0...)`).
FKs: `infertilitysheetid -> infertilitysheet.id` + lookup FKs above.

### hsglt / hsgpelvis / hsgplace / hsgrt / hsgut (lookups: HSG left tube / pelvis / place / right tube / uterus findings)
Identical structure: `id`, `title`, `del`. No seed rows in dump.

## Relationships (explicit list)
- ultrasound.patientid -> patients.id
- ultrasound.sid -> awusers.user_id (sonographer; controllers filter `positionid = 4`)
- ultrasounddetail.ultrasoundid -> ultrasound.id (1 header : N fetuses)
- ultrasoundobst.patientid -> patients.id ; ultrasoundobst.sid -> awusers.user_id
- ultrasoundobstdetail.ultrasoundobstid -> ultrasoundobst.id
- ultrasoundgyna.patientid -> patients.id ; ultrasoundgyna.sid -> awusers.user_id
- sonar.patientid -> patients.id
- gtimage.patientid -> patients.id ; gtimage.doctorid -> awusers.user_id
- gtdetail.gtid -> gtimage.id
- tvs.infertilitysheetid -> infertilitysheet.id ; tvs.{tvsut,tvsro,tvslo,tvscx,tvspelvis,dtvsplace} (CSV) -> {tvsut,tvsro,tvslo,tvscx,tvspelvis,dtvsplace}.id
- dtvs.infertilitysheetid -> infertilitysheet.id ; dtvs.dtvsresult (CSV) -> dtvsresult.id
- sis.infertilitysheetid -> infertilitysheet.id ; sis.sisresult (CSV) -> sisresult.id (infertility-module lookup)
- hsginfertility.infertilitysheetid -> infertilitysheet.id ; hsginfertility.{hsgut,hsgplace,hsgpelvis} -> single lookup ids ; hsginfertility.{hsgrt,hsglt} (CSV) -> {hsgrt,hsglt}.id
- hsg.patientid -> patients.id ; hsg.doctorid -> awusers.user_id
- ustv.patientid -> patients.id ; ustv.doctorid -> awusers.user_id
- mrict.patientid -> patients.id ; mrict.doctorid -> awusers.user_id
- op_4d_list.patientid -> patients.id ; op_4d_list.ancsheetid -> ancsheet.id ; op_4d_list.four_d (CSV) -> four_d.id ; op_4d_list.place -> place.id ; op_4d_list.{user_id,t1_userid,t2_userid} -> awusers.user_id
- ancsheet.`4d_list_id` -> op_4d_list.id (reverse pointer, set on EDD entry)
- ancnewvisit.{usn,usaf,usplace} -> {usn,usaf,usplace}.id (ANC module consumes imaging lookups)

## Business Workflows (traced from code)
1. **Obstetric US report (ultrasound.php / ultrasoundobst.php)**: `ac=add` loads the latest open report for the patient; if none or last is `done=1`, it auto-inserts a new `ultrasound(obst)` header (today's date, defaults 0) plus one default `ultrasound(obst)detail` row (first fetus, all anatomy codes 0, gender -1). Every field on the form saves immediately via AJAX `update()`/`updateradio()` (generic table/column/value writer). Extra fetuses are added with `addDeteail()` (one detail row per fetus); deleting a fetus (`delData`) soft-deletes the detail and decrements `babyno`. `showprint()` marks the header `done=1` and renders the print template; `index` lists only finalized (`done=1`) reports; `delRow` soft-deletes a whole report.
2. **Gyna US report (ultrasoundgyna.php)**: same open-or-create pattern on a single flat `ultrasoundgyna` row (uterus/endometrium/ovaries/Doppler/conclusion), AJAX field-by-field save, print finalizes with `done=1`.
3. **Images & videos (sonar.php)**: per-patient gallery split by `type` (0 images / 1 videos). `uploadmulti()` loops `file-i` POST files: images go through `uploadnew()` into `upload/sonar/` with md5 names; videos through `uploadHEX()` into `upload/sonarvedio/1/` keeping `originalname`. Single-slot `upload()` replaces a file and unlinks the old one from disk. `del()` sets `tempdelete=1` (file stays on disk). `printpic()` prints a selected set of images with clinic header; `downloadfile()` streams the video back with its original filename.
4. **GT Image (gtimage.php)**: `index` ALWAYS inserts a fresh `gtimage` row (base picture from `programesetting`) then redirects to `ac=add`, where the user drags marker icons (`imgno`) onto the diagram; each drop/resize/comment is persisted to `gtdetail` (posx/posy/width/height/comment) via AJAX. `ac=show` lists sessions; `delRow` soft-deletes header + markers.
5. **Infertility-sheet imaging (TVS/D.TVS/SIS/HSG)**: inside `infertilitysheet.php` (and reports `sh.php`, `Completesreport.php`) rows in `tvs`, `dtvs`, `sis`, `hsginfertility` are keyed by `infertilitysheetid`; multi-select findings are stored as CSV id strings and resolved with `id in (0$csv)` against the title lookups.
6. **Investigations hub (investigation.php)**: when a patient first opens the investigations page, empty `hsg`, `ustv`, `mrict` rows are auto-dispensed (sdate=today, doctorid=session user); findings are then typed as free text (uterus / rt-lt adnexa / pelvis / tubes / notes) and listed date-desc with doctor name.
7. **4D scan booking (ancsheet.php + operations.php)**: when EDD/LMP is entered on the antenatal sheet (`eedlmp`), an `op_4d_list` row is auto-created and its id is stored back in `ancsheet.4d_list_id`. Reception/operations staff manage the 4D worklist (`operations.php::four_d()`): choose service items (`four_d` CSV), place, date; entering `tscandate`/`ttscandate` on the ANC sheet flips `t11`/`t21` flags; `done`/`confirm` and `t1_userid`/`t2_userid` track execution.

## ERP Migration Notes (Laravel + Angular)
Proposed models/tables:
- `ImagingStudy` (polymorphic header): patient_id, visit_id, study_type enum [OBST_US, GYNA_US, TVS, DOPPLER_TVS, SIS, HSG, MRI, CT, 4D], study_date, sonographer_id, physician, indication, limitations, status (draft/finalized), conclusion fields. Replaces headers of `ultrasound`, `ultrasoundobst`, `ultrasoundgyna`, `ustv`, `mrict`, `hsg`, and exam rows `tvs`, `dtvs`, `sis`, `hsginfertility`.
- `FetalScanFinding` (hasMany from ImagingStudy, one per fetus): merge `ultrasounddetail` + `ultrasoundobstdetail` (they overlap ~80%; keep superset incl. CRL/NT first-trimester fields). Model anatomy survey as JSON or an EAV `finding_code/finding_value` child table referencing a `scan_finding_catalog`.
- `GynaScanFinding` (hasOne): from `ultrasoundgyna` body fields.
- `ImagingFindingOption` (single lookup table with `domain` column): replaces 16 micro-lookups `tvsut/tvsro/tvslo/tvscx/tvspelvis/dtvsplace/dtvsresult/hsgut/hsgrt/hsglt/hsgpelvis/hsgplace/usn/usaf/usplace/four_d` (+ `sisresult`). CRITICAL: export lookup titles from the LIVE database — the dump has no seed data.
- Pivot tables (e.g. `imaging_study_finding_option`) to replace ALL comma-separated id columns (`tvs.*`, `dtvs.dtvsresult`, `sis.sisresult`, `hsginfertility.hsgrt/hsglt`, `op_4d_list.four_d`).
- `MediaAttachment` via Spatie MediaLibrary (or S3 disk): replaces `sonar`; migrate `upload/sonar` + `upload/sonarvedio/1` files, keep original filename + capture date + notes; soft deletes; serve via signed URLs instead of raw `readfile()`.
- `AnnotatedDiagram` + `DiagramMarker`: replace `gtimage`/`gtdetail` (store marker icon id, x/y as percentages, comment); fix the bug where a new `gtimage` row is inserted on EVERY index visit (creates orphan empty sessions).
- `FourDScanBooking`: replaces `op_4d_list` with proper FKs to patient, antenatal_record, place, service items pivot, and explicit status enum instead of t1/t11/t12/t2/t21/t22 bit-flags; drop the back-pointer `ancsheet.4d_list_id` in favor of a hasOne relation.
Normalization / data-quality actions:
- Cast `ustv.patientid`, `mrict.patientid`, `hsg.patientid` from varchar to int FK during ETL.
- Convert `tvs.date`, `dtvs.date`, `sis.date`, `hsginfertility.date` varchar dates to DATE.
- Convert all numeric measurements stored as varchar (biometry m/p/c columns) to decimal columns.
- Unify soft-delete flags (`del`, `tempdelete`, `deleted`) into Laravel SoftDeletes.
- `sonar` is MyISAM and the only non-InnoDB table here; controllers also build SQL by string concatenation of `patientid`/`id` (SQL-injection surface) and the generic AJAX `update(table, column, value)` endpoint allows writing arbitrary tables/columns — must be replaced by validated FormRequests per resource.
- Decide deprecation: `ultrasound`/`ultrasounddetail` appear superseded by `ultrasoundobst`/`ultrasoundobstdetail` (استنتاج: both active in code; migrate both datasets into the unified model, keep source_table tag).