# Fix: Bills Missing Department Type (departmentType = NULL) ## Problem When a department type filter is selected on reports such as the **Stock Transfer Report** (`stock_transfer_report.xhtml`), the JPQL query adds: ```sql AND b.departmentType IN :departmentTypes ``` Bills created **before the `departmentType` feature was introduced** have `NULL` in that column and are therefore excluded, causing the report to return no data even though transfers exist for the selected date range. ## Root Cause The `Bill` entity stores `departmentType` as a string enum column (`DEPARTMENTTYPE` in MySQL). Bills created by older versions of the application (pharmacy transfer requests/issues/receives, GRNs, store issues, etc.) were never backfilled with this value. > **Important:** `PHARMACY_*` bill types are **not** exclusively pharmacy bills. A > `PHARMACY_ISSUE` or `PHARMACY_TRANSFER_REQUEST` bill can carry store items (e.g. LIQUID > NITROGEN) or lab items. The correct department type must be derived from the items on the > bill — not assumed from the bill type prefix. ## Fast Fix — Native SQL (Two-Step) This is the recommended approach. Run the two steps in order. --- ### Step 1 — Verify scope See how many NULL bills exist and which bill types are affected: ```sql SELECT BILLTYPEATOMIC, COUNT(*) AS cnt FROM bill WHERE DEPARTMENTTYPE IS NULL AND RETIRED = 0 GROUP BY BILLTYPEATOMIC ORDER BY cnt DESC; ``` --- ### Step 2 — Backfill from item department type (single-type bills only) This updates every bill whose items all share the same department type. Bills with mixed item types (e.g. Pharmacy + Store on one bill) are safely skipped by the `HAVING COUNT(DISTINCT …) = 1` guard. ```sql UPDATE bill b JOIN ( SELECT bi.BILL_ID, MAX(i.DEPARTMENTTYPE) AS derived_dept FROM billitem bi JOIN item i ON bi.ITEM_ID = i.ID AND i.DEPARTMENTTYPE IS NOT NULL WHERE bi.RETIRED = 0 GROUP BY bi.BILL_ID HAVING COUNT(DISTINCT i.DEPARTMENTTYPE) = 1 ) sub ON b.ID = sub.BILL_ID SET b.DEPARTMENTTYPE = sub.derived_dept WHERE b.DEPARTMENTTYPE IS NULL AND b.RETIRED = 0; SELECT ROW_COUNT() AS rows_updated; ``` --- ### Step 3 — Handle remaining NULLs (items all null or no items) Some bills may still have NULL after Step 2 — either they have no bill items, or their items also have NULL department type. For the pharmacy transfer workflow these are safe to default to `'Pharmacy'`: ```sql -- Preview what remains SELECT BILLTYPEATOMIC, COUNT(*) AS cnt FROM bill WHERE DEPARTMENTTYPE IS NULL AND RETIRED = 0 GROUP BY BILLTYPEATOMIC ORDER BY cnt DESC; -- Default pharmacy-service bills that couldn't be resolved from items UPDATE bill SET DEPARTMENTTYPE = 'Pharmacy' WHERE DEPARTMENTTYPE IS NULL AND RETIRED = 0 AND BILLTYPEATOMIC LIKE 'PHARMACY_%'; -- Default store-service bills UPDATE bill SET DEPARTMENTTYPE = 'Store' WHERE DEPARTMENTTYPE IS NULL AND RETIRED = 0 AND BILLTYPEATOMIC LIKE 'STORE_%'; ``` --- ### Step 4 — Sanity check After running Steps 2–3, verify the specific bill types used in the stock transfer report are clean: ```sql SELECT BILLTYPEATOMIC, DEPARTMENTTYPE, COUNT(*) AS cnt FROM bill WHERE BILLTYPEATOMIC IN ( 'PHARMACY_ISSUE', 'PHARMACY_RECEIVE', 'PHARMACY_TRANSFER_REQUEST', 'PHARMACY_TRANSFER_REQUEST_PRE', 'PHARMACY_ISSUE_CANCELLED', 'PHARMACY_RECEIVE_CANCELLED', 'PHARMACY_TRANSFER_REQUEST_CANCELLED' ) AND RETIRED = 0 GROUP BY BILLTYPEATOMIC, DEPARTMENTTYPE ORDER BY BILLTYPEATOMIC; ``` No row should have `DEPARTMENTTYPE = NULL`. --- ## DepartmentType Enum → String Mapping Both `Bill.DEPARTMENTTYPE` and `Item.DEPARTMENTTYPE` store the Java enum **name** as a string. Use these exact values in SQL: | Enum constant | SQL string value | |-----------------|--------------------| | `Pharmacy` | `'Pharmacy'` | | `Store` | `'Store'` | | `Lab` | `'Lab'` | | `Clinical` | `'Clinical'` | | `NonClinical` | `'NonClinical'` | | `Opd` | `'Opd'` | | `Inward` | `'Inward'` | | `Theatre` | `'Theatre'` | | `Etu` | `'Etu'` | | `Kitchen` | `'Kitchen'` | | `Other` | `'Other'` | ## Why the Java Admin Function Was Slow The previous "Fill Bill Department Type" button in `admin_functions.xhtml` called `DataAdministrationController.fillBillDepartmentTypeFromBillItems()`, which: 1. Loaded **all** NULL-departmentType bills into JVM memory with one JPQL query. 2. For **each** bill, fired a separate JPQL query to fetch its bill items. 3. Updated **each** bill individually via JPA. This is an O(n) round-trip pattern — thousands of JPA calls for a large database. The native SQL JOIN-based `UPDATE` replaces all of that with a single server-side operation. ## Notes - The `bill` and `billitem` table names are **lowercase** on all known HMIS deployments (Linux MySQL). - Column names are **UPPERCASE** (`DEPARTMENTTYPE`, `BILLTYPEATOMIC`, `RETIRED`, `BILL_ID`, `ITEM_ID`). - Safe to run multiple times — all `WHERE DEPARTMENTTYPE IS NULL` guards are idempotent. - Bills where items span multiple department types (e.g. a stock adjustment bill containing both Pharmacy and Store items) cannot be auto-classified. Review these manually if needed.