Examination results import (APE)

Imports an Annual Physical Examination results spreadsheet after a review on the import workbench: each person becomes a patient and gets a visit on the examination date. Someone with recorded findings gets an in-progress visit with vitals, complaint tags, structured lab results and a medical-record summary, left open so the doctor can review and complete it; someone without findings gets a scheduled (pending) visit. No visit is added when an earlier import already made one for that person in the 15 days up to the examination date — the results go onto that visit instead. Nothing is written until Import is pressed at the bottom of the review screen, and the result page opens with a one-paragraph summary of what happened (ApeImportReport::summary(), also printed by the CLI).

• Web: GET /settings/imports/ape → POST /settings/imports/ape/preview → (POST /settings/imports/ape/preview/decide, JSON) → POST /settings/imports/ape/import. Gated settings.manage.
• CLI: php bin/import-ape-results.php --tenant=<subdomain> --file=<path> [--sheet=] [--recipe=] [--map=] [--id-field=] [--company=] [--doctor=] [--date=YYYY-MM-DD] [--limit=N] [--dry-run] [--quiet] — exit 0 done / 1 error / 2 finished with problems or failed rows. Run php bin/import-ape-results.php --help (or -h) for the current flag list.
• Code: ApeImportService (decide() + import()), ApeImportCatalog, ApeRowParser::parseExam() → ApeExam, ApeFindingParser, ApeImportSettings, ApeImportWebController (on AbstractWorkbenchImportController), templates in templates/settings/imports/ape/.
• No migration. Deploy: clear storage/cache/container-*.

Run settings

Examining doctor (required, must be active), examination date (required, Y-m-d), organisation (optional). They are validated on upload, on every grid refresh and on import (ApeImportSettings), and travel through the review page as hidden fields. Without an organisation no company registry entry or association is made.

Columns

Every patient field (see docs/features/patient-import.md, custom fields and record ID included) plus the exam columns: Age, BMI, Blood pressure, ECG, Chest X-ray, CBC, Urinalysis, HBsAg, Remarks. Sex is optional here. A name and at least one exam column (a summary column, or any APE or Preliminary field) must be mapped.

The column picker also lists two sections of its own (FieldDefinition::$group, rendered as optgroups by import-workbench.js), both defined in ApeWorksheetFields:

• APE (ape_… keys) — every field of the APE worksheet: application details, family history (each condition and its comment), social history and education, the 28-item medical-history checklist and its details, COVID-19 doses, OB-GYN history, physical exam (marks, height, weight, BMI, built, vision matrix, Ishihara, hearing, dental), each body system's status and remarks, each laboratory panel's result, and the assessment (final diagnosis, doctor's remarks, recommendation, physician). The fields are plain text and read leniently: a tick is 1/Y/Yes/X/✓; a yes/no item also takes N/No/0; a system takes N/Normal/WNL or A/Ab/Abnormal, but a system with no remarks is always saved Normal (and prints "Normal"; a system with remarks prints the remarks); a final diagnosis of just A–D, Class B or pending becomes the matching fitness class, anything longer is kept verbatim. The cells are turned into a form body and run through ApeFormMapper::toData() under the Standard APE, so the stored worksheet is exactly what the on-screen form would have saved (lab panels as free-text results, lab_display snapshotted from them).
• Preliminary (pre_… keys) — the visit intake: height, weight, BP systolic/diastolic, temperature, pulse, respiratory rate and oxygen saturation (number fields — a non-numeric cell is a warning and is dropped), plus complaints, observations and other information. Blood pressure (systolic/diastolic) (pre_blood_pressure, text) takes one cell such as 120/80, 130 / 85 or BP: 110/70 mmHg and splits it on the / into the systolic and diastolic readings (ApeWorksheetFields::splitBloodPressure(), two- or three-digit readings). A cell with no / between two readings is a row warning and records nothing. A filled BP systolic or BP diastolic column wins over the reading split from it.

Any filled APE or Preliminary cell makes the row examined. On import the intake takes the Preliminary values, falling back to the summary Blood pressure column for a reading they leave empty; observations join the BMI note and the Preliminary observations, complaints join the Remarks terms and the Preliminary complaints (only Remarks terms become complaint tags). The APE worksheet is written only when the visit has none yet — like the medical-record summary, a re-run never overwrites a worksheet the clinic has since edited.

Clinical parsing (unchanged): BMI codes OW/OB/OB1–3/N; blood pressure 140/100; HBsAg NR/REACTIVE; remarks that are an employment status (RESIGNED, ON VL, MED DONE RECENTLY) are stored on the patient as custom_fields.employment_status rather than tagged as complaints; other remarks become complaint tags. A row is examined when it has any finding, complaint, age or sex. Total/summary lines ("TOTAL: 64") are ignored.

Row outcomes

An "Already imported" row is not a no-op when it still has exam data: it re-enters the same intake, complaint, lab and medical-record writers as a fresh import, and each writer only fills in what is missing (upsert intake, sync tags, skip labs/summary once present). This is what lets a real import finish a row that failed partway on an earlier run — re-running is always safe. Dry runs never write anything, regardless of outcome.

Visits and the 15-day window

ApeImportService::VISIT_WINDOW_DAYS (15). For the people already on file, decide() runs one query (AppointmentRepositoryInterface::visitsForPatientsBetween(), chunked at 500 patients) for their non-cancelled, non-deleted visits scheduled from 00:00 fifteen days before the examination date through 23:59:59 on it. Only visits an import made count, and they are recognised by id alone: a visit counts when its id is uuid5("<tenant>|ape-appt|<key>|<its own Y-m-d>"), or "…|<its own year>" for a visit made before visits were dated. Walk-ins, bookings and visits imported for someone else never match. When several match, the latest wins.

• New visits are dated <exam date> 09:00, keyed <key>|<exam date Y-m-d>, in progress with started_at = the slot when the row has findings, otherwise scheduled. Importing the same file for the same date again finds them, so nothing is duplicated; a second examination more than 15 days later gets its own visit.
• A pending visit (scheduled/confirmed) that receives findings is set in progress directly — status and started_at = its own slot — not through Appointment::start(), which would pull a past slot forward to today.
• The examining doctor chosen for the run is carried onto the visit. New visits are created with it, and a matched visit that is still open (scheduled, confirmed or in progress) is reassigned to it when it differs. A completed visit keeps the doctor it was closed under.
• Visits are never completed by the import. A doctor completes them from the appointment as usual.
• Lab orders are one per visit: "…|ape-laborder|<visit key>". For a visit made before visits were dated the visit key is <key>|<year>, exactly the key its order was filed under, so its results are still found.

Pending visits sit on the doctor's schedule on the examination date like any other booking, and in-progress visits count as visits in progress (for example on the dashboard's ushered figures and a Health Board doctor's "Consulting" state) until completed.

Identity (do not change)

Every row that passes the name check gets a key, and its ids are derived from it: patient uuid5(NAMESPACE_URL, "<tenant>|ape-patient|<key>"), visit "…|ape-appt|<key>|<exam date>", lab order "…|ape-laborder|<key>|<exam date>" (visits and orders made before visits were dated used the exam year instead, and are still recognised — see above). The patient formula is the one the importer has always used; what can differ between the old importer and this one is the key, so decide() tries more than one.

Current key. id|<record id, lower-cased> when a record ID field is chosen (the review screen chooses the idnumber field automatically when a column maps to it) and the row's cell is filled; otherwise <last>|<first> lower-cased, from the name as the workbench splits it (NameSplitter, or the separate Last/First name columns).

Fallbacks. When no patient is on file under the current key's patient id, these are tried in order, and the first one on file wins:

1. <last>|<first> from the workbench split — only when the current key is a record ID. This recognises people an earlier import filed by name before an ID column was used.
2. The pre-workbench importer's key — only when names come from a single full-name column (no Last/First name columns mapped). It is computed from the raw cell, before column functions, exactly as that importer did: split on the first comma only, no suffix or middle-name handling, asterisks, quotes and trailing commas kept, and a non-breaking space kept as part of the name. So CRUZ, JUAN, JR is cruz|juan, jr (the workbench key is cruz|juan jr), CRUZ, JUAN, REYES is cruz|juan, reyes (workbench: cruz|juan), and SANTOS, MARIA is santos|maria.

A row matched by a fallback uses that key's patient, visit and lab-order ids for everything decide() reports and import() writes: it is "On file — add exam" or "Already imported" exactly as a current-key match would be. The patient's stored first and last names are never taken from the fallback — they are always the workbench split. A row with no match anywhere is "New" under its current key. Lookups stay batched: one patient lookup for current ids, at most one more for fallback ids, and one window query for the visits of the people on file.

Duplicates. A row claims both its current key and the key it matched under; a later row claiming either is "Duplicate row". Two rows with different record IDs that both fall back to the same name on file are therefore duplicates — the earlier importer treated them as one person, so the reviewer decides.

Not recognised.

• Names the workbench cannot split: a cell without a comma (JUAN CRUZ) or with an empty surname or first name (CRUZ,) is "Unreadable name", although the old importer imported it (keying JUAN CRUZ as juan cruz|juan cruz). Fix the cell or use Last/First name columns; the fixed row gets a new key.
• Separate Last/First name columns: only the current key (and the name fallback for a record ID) is tried, since the old importer never read such columns.
• Any record-ID import where the ID cell changed, or a name that changed spelling between files.
• The patient importer: APE ids are separate from its student-profile-patient namespace, so the same person imported through both is two records.

Failures

A row whose writes throw is counted as "could not be saved" and logged at warning level with tenant id, row number and exception class only — never the exception message, object or any cell value. Other rows continue. Re-running is safe.

Limits

Files: .xlsx, .xls or .csv, up to 5 MB. Sheets over 2000 data rows are rejected.

Verifying a change

``bash vendor/bin/phpunit tests/Unit/Clinic/Import tests/Unit/Shared/Import ``

Manual browser checklist

Outstanding — needs a signed-in tenant admin session:

1. Settings → Import exam results, with no organisation, a doctor and a date. Upload a sheet with columns NAME, AGE, SEX, BMI, BP, CBC, URINALYSIS, HBSAG, REMARKS and a TOTAL: n last line.
2. Columns auto-map, and the total line shows "Blank". A comma-less name shows "Unreadable name"; remove it.
3. Change a column function. Badges refresh without a 403 or 422 (the run settings reach decide).
4. Press Import. The result page opens with the summary paragraph, then shows visits, lab results and records, with no organisation rows. Examined people have an in-progress visit on the examination date; the others a scheduled one.
5. Upload the same file again with the same date. Every row is "Already imported" and Import is disabled.
6. Upload it once more dated 10 days later: still "Already imported". Dated 20 days later: rows are "On file — add exam" / "On file — add visit".
7. Repeat with an organisation. The organisation links appear, and Settings → Companies shows it unactivated.