Q6 Plan — roots_hemms_mass_import (Final 1.0)¶

Plan version: Final 1.0 — Approved, ready to execute Approved: 2026-05-22 Module ID: roots_hemms_mass_import Layer: 3 (Hospital lens) — depends on Q1 (PM, for ref (Odoo standard, unique per roots_hemms_asset_master's ref_uniq constraint)), Q5 (HA Report, for hemms.ha.device.class + EM-score fields on equipment) Implementation: Q6.1–Q6.6 ship sequentially, no external blockers (Q5 sequencing ahead of Q6)

0. Executive Summary¶

What we build: A new Layer 3 module roots_hemms_mass_import that imports maintenance.equipment, res.partner (vendors), and hr.department (locations) records from XLSX templates. Every import runs dry-run first with per-row OK / WARN / FAIL verdict, then commits in a single transaction. Every imported record is tagged with import_batch_id so a recent batch can be deleted as a unit. Re-imports are idempotent — Odoo external_id wins, else per-object business-key fallback (equipment by ref (Odoo standard, unique per roots_hemms_asset_master's ref_uniq constraint), vendor by vat/name+country, location by complete_name). Strict exact-match against the Q5 MoPH 82-device seed prevents silent miscategorisation of the HA report.

Why: A 200-bed hospital has 800–2,000 medical devices in existing Excel registers. Without mass import, HEMMS go-live = weeks of manual typing = transcription errors = corrupted HA report (Q5). Odoo's built-in base_import is too generic for non-Odoo-expert biomed staff — no MoPH validation, no dry-run preview, no batch undo. Q6 is the feature that makes HEMMS adoption viable at scale.

Effort: 6 commits / ~1 dev session, 16 tests target, 4-page docs, 1 demo batch (24 equipment / 5 vendors / 3 depts).

Keystone wins (impact-ranked):

1. ⭐ Full-simulation dry-run (Q6.3) — parse → resolve FKs → uniqueness check → MoPH match → per-row verdict with reason, before any DB write
2. ⭐ Batch-tagged delete (Q6.5) — every imported record carries import_batch_id; user can undo a recent batch in one click (within 7 days, if no Q1 PM events or Q3 contracts have referenced them)
3. ⭐ Idempotent re-import (Q6.3) — external_id first, business-key fallback. Re-running the same file = 0 new records, not 800 duplicates
4. ⭐ Strict MoPH 82-class match (Q6.3) — exact match against Q5.2 seed (TH or EN name); fuzzy matches REJECTED with clear error. Protects HA-report data quality.
5. ⭐ Error XLSX export (Q6.5) — failed rows downloadable as XLSX with reason column; user fixes + re-runs only the errors

License: LGPL-3, same boundary as Q5 / Q3 / Q4.

Status of open questions: All 6 resolved 2026-05-22 — OQ-A xml_id header convention; OQ-B vendor business-key vat then name+country; OQ-C no sheet-name auto-detect; OQ-D 1-hour preview session cache; OQ-E 7-day batch-delete window with related-event guard; OQ-F single-sheet error XLSX. See §8.

1. Purpose¶

Hospital HEMMS adoption fails on day 1 if the equipment register can't be loaded quickly. A 200-bed hospital has 800–2,000 devices, plus 30–80 vendors, plus 15–40 departments / locations. Manual entry is unrealistic at that scale and produces transcription errors that silently corrupt the HA report (Q5).

The reasonable assumption is hospitals already have this data in Excel (or a legacy system that exports Excel). Q6 turns "give us your Excel file" into a 5-minute operation that produces clean, validated records with full audit trail.

Scope — in¶

• Q6.1: Module skeleton, manifest, hemms.import.batch model, ir.sequence (IMP/%(year)s/00001), security ACLs
• Q6.2: Server-side XLSX template generator (3 objects: equipment / vendor / location); TH+EN column headers; dropdown lists for MoPH 82-class names + risk tier + EM-score ranges; download action on wizard
• Q6.3: Parser + validator engine:
• openpyxl XLSX reader (.xlsx only)
• Header normalisation (TH+EN aliases → field key)
• Row iteration with 5,000-row cap (system parameter, default 5000)
• Optional external_id resolver — column id / External ID; if provided, it takes precedence; if blank, Odoo auto-generates an ir.model.data entry per new record (standard Odoo behaviour)
• Business-key resolver — primary idempotency path (equipment: ref (Odoo standard, unique per roots_hemms_asset_master's ref_uniq constraint); vendor: vat → name+country_id → name; location: complete_name)
• FK resolution (equipment→vendor, equipment→department, equipment →device_class)
• Strict MoPH 82-class matcher against Q5.2 seed (name_en OR name_th, case-sensitive, whitespace-trimmed)
• Uniqueness check (no two new rows with same business key)
• Per-row verdict (OK / WARN / FAIL) with reason column
• Returns ImportPreview data structure cached in session (1-hour TTL)
• Q6.4: Import wizard (3-step):
• Upload XLSX + pick object type (Equipment / Vendor / Location)
• Dry-run preview — show first 50 rows with verdict badge + summary counts (✅ N OK / ⚠️ M WARN / ❌ K FAIL); "Download error XLSX" button surfaces if any FAIL
• Commit — single transaction, all-or-nothing; on success, opens the new hemms.import.batch record (audit chain)
• Q6.5: Batch-tagged delete + error XLSX export:
• hemms.import.batch.action_delete_batch() — checks 7-day window, no related Q1 PM events, no related Q3 contracts; deletes all records tagged with this import_batch_id
• hemms.import.batch.action_download_error_xlsx() — generates XLSX of FAIL rows from last dry-run preview (columns: row_num, field, severity, message, raw_value)
• Q6.6: Tests (6 unit + 10 integration = 16 tests), 4-page docs under docs/modules/mass-import/, demo batch + this plan

Scope — out (deferred to Q6.x follow-ons)¶

• ❌ Q2 spare parts import (stock.lot, product.product for parts) — lot/serial complexity, Q6.x follow-on
• ❌ Q3 service contracts import (hemms.service.contract) — date-state derivation edge cases, Q6.x follow-on
• ❌ Bulk photo / manual / certificate attach (ZIP + filename match) — attach manually post-import for v1
• ❌ Full rollback (undo modifications, not just delete) — batch-tagged delete covers 90% of cases at 10% of the cost
• ❌ CSV format support — XLSX only; CSV invites encoding chaos for TH
• ❌ Legacy .xls (xlrd) — XLSX only; openpyxl already in Odoo deps
• ❌ Cron-scheduled import from filesystem dropbox — manual upload only
• ❌ Auto-detect object type from sheet name — explicit picker (OQ-C)
• ❌ Multi-sheet XLSX with all 3 objects in one file — one-object-per-file enforces FK order (vendors → locations → equipment)
• ❌ Webhook / API mass import — UI wizard only
• ❌ Diff view (compare imported vs existing record) — pre-commit verdict is enough for v1

2. Design decisions (locked)¶

3. Data model¶

3.1 New models¶

hemms.import.batch                          (Q6.1)
  ├─ name                  Char         readonly, ir.sequence IMP-YYYY-#####
  ├─ object_type           Selection    equipment / vendor / location
  ├─ state                 Selection    draft / previewed / committed / archived / deleted
  ├─ file_name             Char         original .xlsx filename
  ├─ file_data             Binary       uploaded XLSX (kept for audit)
  ├─ data_json             Json         parsed rows + verdicts (preview cache)
  ├─ row_count_total       Integer      computed, stored
  ├─ row_count_ok          Integer      computed, stored
  ├─ row_count_warn        Integer      computed, stored
  ├─ row_count_fail        Integer      computed, stored
  ├─ records_created       Integer      computed post-commit, stored
  ├─ records_updated       Integer      computed post-commit, stored (idempotent upsert hits)
  ├─ committed_at          Datetime
  ├─ committed_by_id       M2O res.users
  ├─ deleted_at            Datetime
  ├─ deleted_by_id         M2O res.users
  ├─ delete_blocked_reason Char         set when delete attempt fails the D7 check
  └─ active                Boolean
  + mail.thread + mail.activity.mixin

(no hemms.import.batch.line model — rows live in data_json blob; per-row
 verdicts are read-only after preview, not Odoo records)

3.2 Inherited models — import_batch_id tag¶

maintenance.equipment (INHERIT, Q6.1)
  └─ roots_hemms_import_batch_id   M2O hemms.import.batch  (null = manual entry)

res.partner (INHERIT, Q6.1)
  └─ roots_hemms_import_batch_id   M2O hemms.import.batch

hr.department (INHERIT, Q6.1)
  └─ roots_hemms_import_batch_id   M2O hemms.import.batch

The field is namespaced roots_hemms_import_batch_id to avoid collision with other modules adding import_batch_id to the same shared models.

4. Module layout¶

addons/roots/roots_hemms_mass_import/
  __init__.py
  __manifest__.py
  README.rst
  data/
    ir_sequence_data.xml                  # IMP-YYYY-#####
    ir_config_parameter_data.xml          # row_cap default 5000
    ir_cron_data.xml                      # cleanup previewed >1h
  demo/
    hemms_mass_import_demo.xml            # Q6.6 — 1 demo batch
  models/
    __init__.py
    hemms_import_batch.py                 # Q6.1 — batch model + actions
    maintenance_equipment.py              # Q6.1 — import_batch_id field
    res_partner.py                        # Q6.1 — import_batch_id field
    hr_department.py                      # Q6.1 — import_batch_id field
  services/
    __init__.py
    template_generator.py                 # Q6.2 — XLSX schema → workbook
    parser.py                             # Q6.3 — XLSX → row dicts
    resolver.py                           # Q6.3 — external_id + business-key
    validator.py                          # Q6.3 — FK + uniqueness + MoPH
    committer.py                          # Q6.4 — transactional commit
    error_export.py                       # Q6.5 — error XLSX generator
  wizard/
    __init__.py
    hemms_mass_import_wizard.py           # Q6.4 — 3-step wizard
    hemms_mass_import_wizard_views.xml
  views/
    hemms_import_batch_views.xml          # list/form + delete + download
    menus.xml
  security/
    ir.model.access.csv
    mass_import_security.xml              # restrict to BME / admin
  tests/
    __init__.py
    test_parser.py                        # Q6.6 — U1, U2
    test_resolver.py                      # Q6.6 — U3, U4
    test_moph_match.py                    # Q6.6 — U5
    test_row_cap.py                       # Q6.6 — U6
    test_dry_run.py                       # Q6.6 — I1, I2
    test_commit.py                        # Q6.6 — I3, I4
    test_idempotency.py                   # Q6.6 — I5
    test_order.py                         # Q6.6 — I6
    test_delete.py                        # Q6.6 — I7, I8
    test_error_xlsx.py                    # Q6.6 — I9
    test_wizard_render.py                 # Q6.6 — I10
  i18n/
    th.po

5. UX touchpoints¶

5.1 Mass Import wizard (Q6.4)¶

Step 1 — Upload + object pick:

┌─ Mass Import — Step 1: Upload ──────────────────────────────────┐
│ Object Type     [ Equipment ▼ ]                                  │
│                 ( Equipment / Vendor / Location )                │
│                                                                  │
│ XLSX File       [ Choose file...  hospital_register_2026.xlsx ]  │
│                                                                  │
│ [ Download Template ]  ← server-generates fresh XLSX             │
│                                                                  │
│ [ Cancel ]                                       [ Next: Preview ]│
└──────────────────────────────────────────────────────────────────┘

Step 2 — Dry-run preview:

┌─ Mass Import — Step 2: Preview ─────────────────────────────────┐
│ Batch: IMP-2026-00042 (draft)                                    │
│ File:  hospital_register_2026.xlsx                               │
│                                                                  │
│ Summary:  ✅ 794 OK   ⚠️ 4 WARN   ❌ 2 FAIL                       │
│                                                                  │
│ First 50 rows:                                                   │
│ | Row | Verdict | Asset No | Name (TH/EN)        | Reason     | │
│ | 2   | ✅ OK   | A-0001   | เครื่อง... / Anesth | —          | │
│ | 3   | ⚠️ WARN | A-0002   | (blank EN)          | EN name... | │
│ | 7   | ❌ FAIL | A-0006   | เครื่อง... / X-ray  | Vendor not | │
│ |     |        |          |                      |   found    | │
│ ...                                                              │
│                                                                  │
│ [ ⬇ Download error XLSX ]   (visible if FAIL > 0)                │
│ [ Cancel ]   [ ← Back ]                          [ Commit Batch ]│
└──────────────────────────────────────────────────────────────────┘

Commit button disabled if row_count_fail > 0 AND user is not admin (admins can commit partial; non-admins must clean first).

Step 3 — Result:

┌─ Mass Import — Step 3: Result ──────────────────────────────────┐
│ Batch: IMP-2026-00042 (committed)                                │
│                                                                  │
│ Records created: 790                                             │
│ Records updated: 4   ← idempotent upsert hits                    │
│ Records failed:  0                                               │
│                                                                  │
│ Committed at: 2026-05-22 14:32:11                                │
│ Committed by: jakapol@roots.tech                                 │
│                                                                  │
│ [ Open Batch ]  [ Done ]                                         │
└──────────────────────────────────────────────────────────────────┘

5.2 hemms.import.batch list view¶

| Reference        | Object    | State      | OK | WARN | FAIL | Created | Committed by |
| IMP-2026-00042   | Equipment | Committed  | 794| 4    | 0    | 790     | jakapol      |
| IMP-2026-00041   | Vendor    | Committed  | 24 | 0    | 0    | 24      | jakapol      |
| IMP-2026-00040   | Equipment | Previewed  | 100| 2    | 8    | —       | —            |
| IMP-2026-00039   | Equipment | Deleted    | 50 | 0    | 0    | 50→0    | (jakapol)    |

5.3 hemms.import.batch form¶

[ Header ]
  IMP-2026-00042                                       [ Committed ]

[ Header buttons ]
  [ ⬇ Download Original XLSX ]
  [ ⬇ Download Error XLSX ]    ← if row_count_fail > 0
  [ 🗑 Delete Batch ]            ← if state='committed' AND <7d AND no related events

[ Notebook tabs ]
  - Summary (counts, file, audit timeline)
  - Preview Rows (read-only table from data_json)
  - Delete Block Reason (visible if delete attempted + blocked)

[ Chatter ]
  Standard mail.thread — commit + delete events post here

6. UX features captured¶

7. Test Cases (16)¶

Unit tests (6)¶

Integration tests (10)¶

Test infrastructure¶

• All TransactionCase, no HttpCase
• Fixtures: minimal XLSX files in tests/fixtures/ (valid_equipment.xlsx, fk_orphan.xlsx, oversized_5001.xlsx, mixed_verdicts.xlsx)
• Use openpyxl directly to construct fixtures inline (avoid binary files in git where possible)
• Green gate: run on hemms_demo AND hemms_private — same dual-DB gate as Q3 / Q4 / Q5

Coverage targets¶

8. Open Questions — resolved¶

9. Plan execution gotchas¶

• Q6.1 namespaced import_batch_id — maintenance.equipment, res.partner, hr.department are SHARED Odoo models; another module may also define import_batch_id. Namespace as roots_hemms_import_batch_id to avoid silent overwrite.
• Q6.1 res.partner extension on multi-company — make sure new field company_id-aware (or company_dependent=False); HEMMS demo data is single-company but res.partner is shared across companies.
• Q6.2 template generation runs in user lang — server-rendered XLSX must use the user's res.lang for column headers so a TH user sees Thai headers, an EN user sees English. Use with_context(lang=...) explicitly.
• Q6.2 dropdown lists — XLSX data validation dropdowns for MoPH class names blow up at 82 entries on some Excel versions; cap dropdown display at first 50 + provide full reference sheet (__moph_classes) in same workbook.
• Q6.3 openpyxl read-only mode — for 5K-row files, use openpyxl.load_workbook(path, read_only=True, data_only=True) to avoid loading formulas + styles into memory.
• Q6.3 whitespace + zero-width characters — Thai Excel exports often contain non-breaking spaces (U+00A0) and zero-width spaces (U+200B); normalise with unicodedata.normalize('NFC', s).strip() before MoPH match comparison. Otherwise "เครื่องดมยาสลบ" with a sneaky NBSP fails strict match.
• Q6.3 MoPH match — case sensitivity — D8 says case-sensitive, but TH characters don't have case. EN names DO. Keep case-sensitive for EN; document that "anesthesia units" ≠ "Anesthesia Units".
• Q6.3 FK orphan detection order — resolve external_id refs FIRST (if id column provided), then layered business-key refs, before FK validation. Otherwise a row whose vendor is identified by external_id (and exists) gets falsely flagged because business-key fallback didn't find it.
• Q6.3 vendor name-only collision (D6 / OQ-B) — if two existing res.partner records share the same name (e.g., medical-imaging Siemens and office-equipment Siemens), the resolver matches the first one and produces a silent miscategorisation. WARN on every name-only match (not FAIL), surface the candidate IDs in the verdict reason, and document the post-import dedupe path (Odoo's standard res.partner merge wizard) in the user docs. Acceptable trade-off for v1; revisit in Q6.x if hospitals report breakage.
• Q6.3 same-file uniqueness — file with rows 5 and 17 both having asset_no=A-0042 must FAIL row 17 (duplicate), not silently last-wins. Track seen business keys during parse pass.
• Q6.4 cr.savepoint() semantics — Odoo's self.env.cr.savepoint() is a context manager; ensure it wraps the ENTIRE record-creation loop, including any computed-field side-effects. Test I4 explicitly verifies rollback.
• Q6.4 commit + computed fields invalidation — equipment records created in batch trigger Q5 risk-tier waterfall recompute; this is expected and adds ~30% to commit time on 1000-row batches. Budget accordingly; consider recompute=False for batch insert + explicit _recompute_done() after.
• Q6.5 delete blocking — check via search count, not loop — for batches of 800 records, use a single self.env['maintenance.request'].search_count([('equipment_id', 'in', ids)]) to detect related PM events. Loop-per-record on a 800-batch is a perf trap.
• Q6.5 error XLSX in user lang — same as template generation; column headers in user's res.lang.
• Q6.6 dual-DB gate — Q5 added MoPH seed; Q6 hard-depends on Q5. Both hemms_demo and hemms_private need Q5 installed before Q6 tests run.
• Q6.6 docs sequencing — docs/modules/mass-import/index.md must be added to mkdocs.yml nav; otherwise it won't render.
• Q6.6 demo data — 24 equipment / 5 vendors / 3 depts as a SINGLE pre-committed batch makes the wizard discoverable; user opens the batch record and sees the audit trail.

10. Commit sequence (Q6.1 → Q6.6)¶

11. Resume cue¶

When user says "เริ่ม Q6.1" / "start Q6" / "ship Q6" / continues this plan:

1. Re-read this plan + project_q6_status.md for full context
2. Confirm dependencies installed cleanly:
3. Q1 (roots_hemms_pm) — for PM event aggregation
4. roots_hemms_asset_master — for the ref_uniq SQL constraint on maintenance.equipment.ref (the equipment business key per D6)
5. Q5 (roots_hemms_ha_report) — for hemms.ha.device.class MoPH seed + EM-score fields on equipment
6. Execute Q6.1 — skeleton + batch model + sequence + cron stub (Shipped 2026-05-22 — installs cleanly on hemms_demo + hemms_private)
7. After each commit: pre-commit run --all-files; run module tests (after Q6.6); smoke-install on both hemms_demo and hemms_private
8. Final check at Q6.6: full end-to-end demo — open wizard → download template → fill 10 rows → upload → preview shows 10 OK → commit → delete batch → confirm records gone