Admin Fill Bill Department Type SQL

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:

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.

Fast Fix — Native SQL

This is the recommended approach. A single UPDATE statement completes in seconds regardless of how many bills are affected.

Step 1 — Verify the scope

Run this first to see how many rows will be changed, broken down by bill type:

SELECT BILLTYPEATOMIC, COUNT(*) AS cnt
FROM bill
WHERE DEPARTMENTTYPE IS NULL
  AND RETIRED = 0
GROUP BY BILLTYPEATOMIC
ORDER BY cnt DESC;

Step 2 — Backfill pharmacy bills

All PHARMACY_* bill types belong to the Pharmacy department type:

UPDATE bill
SET DEPARTMENTTYPE = 'Pharmacy'
WHERE DEPARTMENTTYPE IS NULL
  AND RETIRED = 0
  AND BILLTYPEATOMIC LIKE 'PHARMACY_%';

Step 3 — Backfill store bills (if applicable)

UPDATE bill
SET DEPARTMENTTYPE = 'Store'
WHERE DEPARTMENTTYPE IS NULL
  AND RETIRED = 0
  AND BILLTYPEATOMIC LIKE 'STORE_%';

Step 4 — Verify nothing remains (optional sanity check)

SELECT BILLTYPEATOMIC, COUNT(*) AS cnt
FROM bill
WHERE DEPARTMENTTYPE IS NULL
  AND RETIRED = 0
  AND BILLTYPEATOMIC LIKE 'PHARMACY_%'
     OR BILLTYPEATOMIC LIKE 'STORE_%'
GROUP BY BILLTYPEATOMIC;

Expected: zero rows returned.

DepartmentType Enum → String Mapping

The Bill.DEPARTMENTTYPE column stores the Java enum name exactly as declared (EnumType.STRING). Use these exact string values:

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 Is Slow

The previous "Fill Bill Department Type" button in admin_functions.xhtml called DataAdministrationController.fillBillDepartmentTypeFromBillItems(), which:

1. Loads all NULL-departmentType bills into JVM memory with one JPQL query.
2. For each bill, fires a separate JPQL query to fetch its bill items.
3. Updates each bill individually via JPA.

This is an O(n) round-trip pattern — thousands of JPA calls for a large database.
The native UPDATE … WHERE … LIKE 'PHARMACY_%' replaces all of that with a single server-side operation.

Notes

• The bill table name is lowercase on all known HMIS deployments (Linux MySQL).
• Column names are UPPERCASE (DEPARTMENTTYPE, BILLTYPEATOMIC, RETIRED).
• The enum values in BILLTYPEATOMIC are UPPER_SNAKE_CASE (e.g., PHARMACY_ISSUE).
• The enum values in DEPARTMENTTYPE are CamelCase (e.g., Pharmacy, Store).
• The LIKE 'PHARMACY_%' pattern is safe: all pharmacy-service bill types use this prefix and all of them belong to the Pharmacy department type.
• Safe to run multiple times — the WHERE DEPARTMENTTYPE IS NULL guard is idempotent.