# Module: Investigations & Lab Orders (investigations)

## Purpose
Manages the lab-test catalog (categories + test items), per-sheet/per-visit lab ordering and result entry for the OB/GYN clinic (antenatal, gyna, infertility sheets), a dynamic user-defined "other investigations" grid (EAV pattern), and a batch Excel export of full patient records. Also (misleadingly) hosts the application's license-serial table (`excelinfo`).

No declared foreign keys anywhere; all relationships below are inferred from column names and controller queries (RedBeanPHP `R::` calls).

## Controllers & Views (file paths)
- `/home/amrtechogate/public_html/obgy/core/controllers/investigation.php` — per-patient "Investigations" screen (controller class, actions: `index`, `addRow`, `delData`, `update`, `otherinvests`, `otherinvestsAdd`, `otherinvestsRow`, `delotherinvestsRow`, `delotherinvest`, plus a maintenance `onesetup`).
- `/home/amrtechogate/public_html/obgy/core/controllers/addinvestigation.php` — catalog admin (actions: `index`, `addnewcat`, `addnewinv`, `update`, `del`, `getinvbycatid`, `addtofavorite`).
- `/home/amrtechogate/public_html/obgy/core/controllers/excel.php` — NOT a controller class: a procedural script (`startexcel()`/`createexcel()`) run directly; consumes `excelinfopatients`, builds an HTML table of the full patient record, converts to `.xlsx` via PHPExcel, saves to `../excel_backups/{statusno}/{Y-m-d}.xlsx`, then TRUNCATEs the queue. No auth check.
- Views: `/home/amrtechogate/public_html/obgy/core/views/obgy/investigation/` (investigation.html, investigation1.html, newrow.html, otherinvests.html) and `/home/amrtechogate/public_html/obgy/core/views/obgy/addinvestigation/` (show.html, invests.html).
- Cross-module consumers: `ancsheet.php` (~line 905 writes `ancsheetinvest`), `gynasheet.php` (~635 writes `gynasheetinvest`), `infertilitysheet.php` (~698 writes `infertilitysheetinvest`), `ancsheet00.php` / `antenalvisit.php` (read catalog grouped by `displayorder` ranges; read legacy `investigations`), `completereport.php` / `Completesreport.php` (reports; load `invests` names by `investid`), `patienthistory.php` + `completereport.php` (`checkpatientlist()` enqueues into `excelinfopatients`), `login.php` (reads/writes `excelinfo` serial), `setup.php` (protects `excelinfo` from deletion).
- SQL dump: `/home/amrtechogate/public_html/obgy/_db/obgy_12-7-2024.sql`.

## Tables

### investcats (lookup)
Purpose: categories of lab tests; `displayorder` drives hardcoded column grouping in ordering UIs.
Columns:
- `id` int(11) PK AI
- `name` varchar(150) NULL
- `deleted` int(11) NOT NULL DEFAULT 0 (soft delete)
- `displayorder` int(11) NOT NULL DEFAULT 0 (-1 = hidden "Old Favorite"; 0 = "others" bucket)

Seeded values (id, name, deleted, displayorder): (1,'3- Chemistry',0,2), (2,'10- Urine & Stool',0,7), (3,'1- Hematology',0,1), (4,'7- virology',0,5), (5,'4- Hormonal profile',0,3), (6,'2- coagulation profile',0,8), (7,'9- auto ab (Immunology)',0,4), (8,'5- tumor markers',0,6), (9,'12- Genetics',0,9), (10,'pre operative / ICSI',0,0), (11,'11- semen - Male',0,10), (12,'Others',0,0), (13,'Old Favorite',0,-1), (14,'Radiological',0,11), (15,'',1,12), (16,'aNC (booking)',0,13), (17,'',1,14), (18,'',1,15), (19,'ANC (27 w)',0,16), (20,'6- aneuploidy screening',0,17), (21,'8- bacteriology',0,18), (22,'13- pathology',1,19).

### invests (lookup — the central test catalog)
Purpose: individual lab test items; referenced by every `*sheetinvest` order table via `investid`.
Columns:
- `id` int(11) PK AI
- `investcatid` int(11) NOT NULL -> investcats.id
- `name` varchar(150) NULL
- `favorite` int(11) NOT NULL DEFAULT 0
- `deleted` int(11) NOT NULL DEFAULT 0

Seeded: 276 rows (IDs 1–276). Representative clinical vocabulary: Hematology (ABO group, Rh factor, CBC, Hb%, RBCs indices, platelets, WBCs, reticulocytic count, indirect coombs, Serum Ferritin, CRP, ESR, Hct, hb electrophoresis); Chemistry (SGPT, SGOT, Bilirubin, Albumin, alk ph, bile acids, Creatinine, urea, uric acid, Na, K, Ca, lipid profile, FBS, RBS, OGTT 75gm, HbA1C, FG/FI ratio); Hormonal (FSH, LH, E2, Progesterone, AMH, FT3, FT4, TSH, PRL, cortisol, testosterone, DHEA-S, 17-OHP, HCG qual/quant, PTH); Immunology (Lupus anticoagulant, Anticardiolipin IgG/IgM, Anti B2GP, ANA, Anti dsDNA, RF, anti-CCP, anti-thyroid TSI/TPO, C3, C4); Virology (TORCH IgM/IgG, HBs Ag/Ab, HCV Ab, HIV Ab); Coagulation (BT-CT, APTT, PT INR, VWF, F VIII/IX, fibrinogen, FDPs, d-dimer, thrombophilia panel); Tumor markers (CA125, CEA, CA15-3, CA19-9, AFP, LDH); Urine/Stool; Genetics (karyotyping, amniocentesis, CVS, FMR-1, FVL, prothrombin gene, AZF, RT-PCR); Semen (conventional analysis, CASA DNA fragmentation, SDF); Radiological (scrotal US, mammography, breast US, pelvic MRI, brain MRI sella); aneuploidy screening (b-HCG, PAPP-A, AFP, UE3, inhibin A); bacteriology (Widal, Brucella, ASOT, VDRL, RPR, vag discharge C/S); pre-operative/ICSI panel; ANC booking and ANC 27w panels. Dirty data present: empty names, test rows ('vvv','new33','gg edit','res3'), rows with `investcatid = 0` (orphans).

### investigations (legacy, empty)
Purpose: legacy fixed-column antenatal booking lab results — one column pair per analyte: `invesd*` = date, `invest*` = result. AUTO_INCREMENT=1 (no data). Read-only usage found in `ancsheet00.php` (`'ancsheetid = ?'`) and `Completesreport.php` (queried both with `ancsheet->id` and `ivfsheet->id` — sloppy ID reuse). No write path found in current code.
Columns:
- `id` int(11) PK AI
- `ancsheetid` int(11) unsigned NULL -> antenatal sheet id (inferred; mainantenental/ancsheet)
- `invesdhb`, `invesdplat`, `invesdfbs`, `invesdogtt`, `invesdhbs`, `invesdhcv`, `invesdcua`, `invesdferr`, `invesdogtt2` varchar(191) NULL (dates per analyte)
- `investhb`, `investplat`, `investfbs`, `investhbs`, `investhcv`, `investcua`, `investferr` varchar(191) NULL (results per analyte)

### otherinvestigations
Purpose: user-defined custom investigation definitions (the "columns" of the dynamic grid). No seeded data.
Columns:
- `id` int(11) PK AI
- `name` varchar(255) NULL
- `deleted` int(11) DEFAULT 0
Note: charset latin1 (vs utf8mb4 elsewhere) — Arabic text risk.

### otherinvestigationsrows
Purpose: EAV header — one row per patient per date of custom investigations. No seeded data.
Columns:
- `id` int(11) PK AI
- `date` date NULL
- `patientid` int(11) NULL -> patients.id
- `doctorid` int(11) NULL -> awusers.user_id
- `deleted` int(11) DEFAULT 0

### otherinvestigationsvalues
Purpose: EAV values — one record per (row x definition); auto-created empty for every active definition when a row is added (`otherinvestsRow()` in a transaction).
Columns:
- `id` int(11) PK AI
- `investrowid` int(11) NULL -> otherinvestigationsrows.id
- `investid` int(11) NOT NULL -> otherinvestigations.id
- `value` varchar(255) NULL (free text, no units/ranges/types)
- `date` date NULL

### excelinfo (misnamed — license store)
Purpose: stores the application activation serial (SHA-512 hash tied to server MAC + salt) and activation time. Checked in `login.php` (`index()` creates the row if missing; `activate()` validates POSTed serial against `serialcheck()`). Listed among protected tables in `setup.php`. Nothing to do with Excel.
Columns:
- `id` int(11) PK AI
- `time` datetime NULL
- `serial` text NULL

Seeded: 1 row — (1, '2020-07-21 03:29:34', 'd2dc0d4a...995267' [128-char hex serial]).

### excelinfopatients
Purpose: work queue of patients whose full record should be exported to Excel. Enqueued by `checkpatientlist()` in `patienthistory.php` and `completereport.php` (dedup by patientid); consumed and fully TRUNCATEd by `excel.php`. No seeded data.
Columns:
- `id` int(11) PK AI
- `patientid` int(11) NOT NULL -> patients.id
- `statusno` int(11) NOT NULL (copied from patients.statusno; used as export subfolder `excel_backups/{statusno}/`)

## Relationships (explicit list)
- invests.investcatid -> investcats.id
- ancsheetinvest.investid -> invests.id; ancsheetinvest.patientid -> patients.id; ancsheetinvest.ancsheetid -> mainantenental.id (inferred, "main Antenatal Id" comment in ancsheet.php); ancsheetinvest.doctorid -> awusers.user_id
- gynasheetinvest.investid -> invests.id; gynasheetinvest.patientid -> patients.id; gynasheetinvest.doctorid -> awusers.user_id
- infertilitysheetinvest.investid -> invests.id; same patientid/doctorid pattern
- investigations.ancsheetid -> antenatal sheet id (inferred; also queried with ivfsheet ids in Completesreport.php — data-integrity smell)
- otherinvestigationsrows.patientid -> patients.id; otherinvestigationsrows.doctorid -> awusers.user_id
- otherinvestigationsvalues.investrowid -> otherinvestigationsrows.id; otherinvestigationsvalues.investid -> otherinvestigations.id
- excelinfopatients.patientid -> patients.id; excelinfopatients.statusno copied from patients.statusno (denormalized)
- excelinfo: standalone singleton (license)
- Related sibling tables following the same catalog pattern (other modules): followupinvest, gynainvestigation, infertilityinvest, mainantenentalinvest, mointoringsheetinvestigation — all carry `investid` pointing at invests.id (inferred from structure).

## Business Workflows (traced from code)
1. Catalog administration (`addinvestigation.php`): admin lists categories (`investcats` where deleted=0 and displayorder != -1), adds a category (`addnewcat` — next displayorder), adds a test under a category (`addnewinv`), inline-edits any cell via `update(tableName, colName, value)` (AJAX), soft-deletes (`del` sets deleted=1), toggles favorites (`addtofavorite`), fetches tests per category (`getinvbycatid`).
2. Ordering from clinical sheets (`ancsheet.php`/`gynasheet.php`/`infertilitysheet.php`): UI shows categories grouped by hardcoded `displayorder` ranges (1–2 | 3 | 4–5 | 6–11 | >11 | 0=others | favorites list), duplicated across `ancsheet00.php` and `antenalvisit.php`. Doctor checks tests, picks date and wife/husband; controller dispenses one `*sheetinvest` bean per checked `investid` (patientid, sheetid, date, doctorid, forhusband), stores all, then renders a printable order using patient/husband name and title from `wifetypes`/`husbandtypes`.
3. Result entry & display: `investresult` updated per order row; sheets and `completereport.php` group orders by DISTINCT date, split wife (forhusband=0) vs husband (forhusband=1), and resolve test names via `R::load('invests', investid)`.
4. Patient investigations screen (`investigation.php?patientid=`): for each of semen, hsg, ustv, laparoscopy, hysteroscopy, mrict, pathology (infertility-module tables) it auto-creates an empty row dated today if none exists, then lists all rows with doctor names; hormon listed without auto-create. `addRow`/`delData`/`update` are generic AJAX endpoints taking tableName/colName from the client. Template switches between investigation.html / investigation1.html based on `programesetting.investshow`.
5. Dynamic other-investigations: `otherinvestsAdd` creates a new definition; `otherinvestsRow` creates a header row + one empty `otherinvestigationsvalues` per active definition (transaction); index page computes per-row column widths (presentation logic in controller); values edited inline; row/definition soft-deleted.
6. Excel backup pipeline: opening a patient in `patienthistory.php`/`completereport.php` enqueues into `excelinfopatients`; `excel.php` (run directly, presumably via cron — inferred) reads the queue, TRUNCATEs it, and per patient builds a giant HTML table (menstrual/contraception/obstetric/medical/surgical/gyn/ART/family history, examination, semen/HSG/US/laparoscopy/hysteroscopy/MRI/hormones/pathology, gyna visits/folliculometry/investigations/treatment, antenatal visits) -> PHPExcel_Reader_HTML -> styled XLSX (hardcoded row coordinates) -> `excel_backups/{statusno}/{Y-m-d}.xlsx` (same-day exports overwrite).
7. License gate: `login.php` ensures one `excelinfo` row exists; `activate()` compares POSTed serial to `serialcheck()` (sha512 of MAC + salt) and persists it. The enforcement branch is currently commented out.

## ERP Migration Notes
Proposed Laravel models:
- `LabTestCategory` (from `investcats`): name, sort_order, is_active. Drop the -1/0 magic displayorder semantics; make grouping configurable.
- `LabTest` (from `invests`): category_id FK, name, code (new), unit (new), reference_range (new), result_type (new), is_favorite, is_active. Cleanse seeds: remove empty names, test junk ('vvv','new33', etc.), reassign `investcatid = 0` orphans.
- `LabOrder` + `LabOrderItem`: unify `ancsheetinvest`, `gynasheetinvest`, `infertilitysheetinvest` (and siblings `mainantenentalinvest`, `followupinvest`, `gynainvestigation`, `infertilityinvest`, `mointoringsheetinvestigation`) into one polymorphic structure: LabOrder{patient_id, doctor_id, orderable_type/orderable_id (sheet/visit), ordered_at, subject (wife|husband)}; LabOrderItem{lab_order_id, lab_test_id, result, result_at}. Keep an old-id mapping table for historical reports.
- `CustomInvestigation` + `CustomInvestigationResult` (from otherinvestigations/rows/values) — or better, fold user-defined tests into `LabTest` with a `is_custom` flag and store results as LabOrderItems, eliminating the parallel EAV system. Convert latin1 data carefully.
- DROP: `investigations` (legacy fixed-column, empty in dump — verify empty in production first), `excelinfo` (desktop licensing — replace with ERP subscription/tenancy), `excelinfopatients` (replace with Laravel queued jobs + export log table; use Laravel Excel/PhpSpreadsheet or PDF reports instead of PHPExcel HTML hack).
Security fixes required during migration: remove client-supplied tableName/colName endpoints (arbitrary-table write); parameterize all queries (doctorid is string-concatenated into SQL in several controllers); add auth to export jobs; stop auto-creating empty placeholder rows (creates phantom records in 7 tables per first page view).