User Manual – Tracer Medicine Tracking

1. Dashboard Overview

The Dashboard (index.php) is the main screen of the system. It provides a complete view of tracer medicine stock status for the 2026/27 financial year (April 2026 to March 2027). The system pulls data from two tables: pdr_table (current data) and audit_pdr_table (historical records), and intelligently matches medicine descriptions to a fixed list of 106 tracer medicines.

What you can do here:

View a matrix showing 1 (out of stock), 0 (in stock), or – (no data) for each medicine per month.
Apply filters to focus on specific facilities, districts, NSNs, medicine names, or a single month.
See summary cards with key indicators: total medicines, medicines with data, total out‑of‑stock count, and annual stock‑out rate.
Visualise monthly trends via a bar chart.
Export the current view to CSV, Excel, or PDF.

2. Filters Explained

The filter bar allows you to narrow down the data without reloading the core logic. Each filter works independently and they combine with AND logic (i.e. all conditions must match).

Filter	What it does	Technical behaviour
Facility	Show data for a specific clinic or hospital.	Searches the `Facility` and `Facility_Name` columns using `LIKE` with partial matching. Auto‑complete suggests existing facilities.
District	Show data for all facilities within a selected district.	Matches the `District` column exactly. Auto‑complete lists districts from the `facility_clinic` table.
NSN	Filter by National Stock Number (a unique product code).	Uses `LIKE` on the `NSN` column, so you can search partial numbers.
Medicine	Search for a specific tracer medicine by name.	This filters the tracer list itself using `stripos` (case‑insensitive). Only medicines whose full name contains your search text will appear in the table.
Month	Limit the view to a single calendar month (e.g. Jul‑26).	Restricts the `week_start_date` to the first and last day of the selected month. If omitted, the default range is April 2026 – March 2027.

            💡 Tip: Click Apply Filters after changing any field. Use Reset to clear all filters and show the full dataset.
        

3. Data Table & Colour Coding

The main table lists each tracer medicine as a row. Each month from Apr‑26 to Mar‑27 is a column. The value in each cell tells you the stock status for that medicine in that month.

1 (Red) – Medicine was out of stock (On_Hand = 0) for that month. This is a critical alert.
0 (Green) – Medicine was in stock (On_Hand > 0).
– (Grey) – No data reported for that month. This could mean the facility did not submit data, or the medicine was not recorded.

The Total O/S column at the end counts how many months that medicine was out of stock. A high number (e.g. 8 or more) indicates a chronic shortage and should be investigated.

            📌 Note: The system uses an intelligent keyword‑matching algorithm to assign database descriptions (which may vary) to the correct tracer medicine. This ensures accurate aggregation even if the text is not identical.
        

4. The Monthly Chart

A bar chart visualises the total number of medicines out of stock per month. This gives a quick overview of how stock availability is trending over time.

X‑axis – Months (Apr‑26 to Mar‑27).
Y‑axis – Count of medicines out of stock (range 0 to 106).
Red bars – Indicate months where at least one medicine was out of stock.
Green bars – Indicate months with zero stock‑outs (perfect availability).

Use this chart to spot seasonal patterns, the impact of supply chain disruptions, or improvements over time. Hover over a bar to see the exact number.

5. Metrics & Calculations

Below the main table, three rows show the core performance indicators. These exactly replicate the official KZN Tracer Medicines List Excel formulas.

5.1 Numerator (Total for Numerator)

This is the total number of tracer medicines that were out of stock. For each month, we count every medicine cell that equals 1. The “Total” column sums all months to give the annual numerator.

Monthly Numerator (for a specific month) = Σ (All tracer medicines where On_Hand = 0 for that month) Annual Numerator = Σ (Monthly Numerators for all 12 months)

Example: If 5 medicines are out of stock in Apr‑26, the numerator for Apr‑26 is 5.

5.2 Denominator (Tracer Medicines Expected)

The denominator is the total number of tracer medicines that should be available. This is fixed at 106 (the full list). For each month, the denominator is 106. For the annual total, it is 106 × 12 = 1,272.

Monthly Denominator = 106 Annual Denominator = 106 × 12 = 1,272

5.3 Indicator (Tracer Medicine Stock‑out Rate)

This is the percentage of tracer medicines that were out of stock. It is calculated by dividing the numerator by the denominator and multiplying by 100. Lower percentages are better.

Monthly Stock‑out Rate = (Monthly Numerator / 106) × 100 Annual Stock‑out Rate = (Annual Numerator / 1,272) × 100

Example: If 10 medicines are out of stock in May‑26, the rate = (10 / 106) × 100 = 9.43%.

            ✅ Interpretation: A rate of 0% means perfect stock availability. 
            A rate above 10% is considered concerning and may require intervention.
        

6. Quarterly & Annual Performance

The system groups months into four quarters to match the reporting structure:

Quarter 1 (Q1): Apr‑26, May‑26, Jun‑26
Quarter 2 (Q2): Jul‑26, Aug‑26, Sep‑26
Quarter 3 (Q3): Oct‑26, Nov‑26, Dec‑26
Quarter 4 (Q4): Jan‑27, Feb‑27, Mar‑27

For each quarter, the numerator is the sum of the three monthly numerators. The denominator is 3 × 106 = 318. The quarterly rate is then calculated as (Quarterly Numerator / 318) × 100.

The Annual Performance row uses the total annual numerator and the annual denominator (1,272) to give the overall stock‑out rate for the entire financial year.

Quarterly Numerator = Sum of Monthly Numerators for the 3 months in that quarter Quarterly Denominator = 318 Quarterly Rate = (Quarterly Numerator / 318) × 100

7. Exporting Data

You can export the current view (including all filters and metrics) in three formats using the Export dropdown.

CSV – Comma‑separated values. Opens in any spreadsheet software (Excel, Google Sheets, LibreOffice). Ideal for further analysis.
Excel – Generates an HTML‑based `.xls` file that is compatible with Microsoft Excel. It preserves column widths and bold formatting.
PDF – Creates a print‑ready document with all tables and charts (chart is omitted in PDF, but metrics are fully visible).

            🖨️ Print: You can also click the Print button to generate a printer‑friendly version of the dashboard.
        

8. Redistribution Advice (`trace_advice.php`)

The Redistribution Advice page helps you balance stock between facilities. It compares current stock levels (On_Hand) across facilities for each tracer medicine.

Surplus – Facilities with stock above a defined maximum threshold (e.g., more than 3 months of consumption).
Deficit – Facilities with stock below a defined minimum threshold (e.g., less than 1 month of consumption).

The system then suggests transfers from surplus facilities to deficit facilities. The suggested quantity is the smaller of the surplus amount and the deficit amount, so that no facility loses essential supply.

You can apply the same filters (Facility, District, NSN, Medicine) to focus the advice on a specific area or product. The output table shows:

Medicine – The tracer medicine to be moved.
From Facility – The facility with surplus.
To Facility – The facility with deficit.
Quantity to Transfer – The recommended number of units.
Reason – A brief explanation (e.g., surplus 200, deficit 150).

            ⚠️ Important: Redistribution suggestions are advisory only. Always verify stock counts physically and consult with the pharmacy manager before moving stock.
        

9. Troubleshooting & Common Questions

Issue	Solution
“No data” for all medicines	Check that the database connection is working. Ensure that the `pdr_table` and `audit_pdr_table` have data for the selected period.
Filters do not seem to work	Make sure you click Apply Filters after typing. The filters are case‑insensitive but require an exact match for District.
Export takes too long or times out	Large datasets (all facilities, all months) may take a few seconds. To speed up, apply a filter (e.g., a specific month) before exporting.
Chart does not appear	Chart.js is loaded from a CDN. Check your internet connection. If offline, download the library and host it locally.
Medicine names are mismatched	The system uses keyword matching. If a description is very generic, it might be misassigned. Contact the system administrator to adjust the keyword list.
I see “Out of Stock” but I know we had stock	Data is sourced from submitted reports. Verify the source data or ask the data clerk to correct the entry.

KwaZulu-Natal Department of Health