Aravind swamy Majjuri requested to merge prescription into develop Apr 04, 2026

Merge Request

Overview

This MR updates the backend analytics logic to correctly aggregate medicine prescription quantities for medical camps.

Previously, the system was counting prescription records instead of summing actual prescribed quantities, leading to incorrect analytics data.

What does this MR do and why?

The main goal of this change is to ensure accurate medicine analytics by:

Replacing COUNT-based logic with SUM of quantities
Providing real-time insights into:
- Total prescribed quantity
- Total quantity given
- Remaining stock (quantity left)

This ensures that analytics reflect actual medicine usage instead of number of prescription records.

Changes Made

Backend Changes

Updated aggregation logic in analytics_service.py
Replaced:
- COUNT(prescriptions) → ❌
- SUM(prescriptions.quantity) → ✅
Added support for:
- quantity_given aggregation
- quantity_left calculation

screenshots

Before:

After:

Technical Details

Root Cause

Previously:

prescribed_count was calculated using COUNT
This only counted rows, not actual prescribed quantities

Example:

2 prescriptions (5 + 7 units)
Old result → 2
Correct result → 12

Fix Implemented

Aggregated using:

total_prescribed = SUM(quantity)
quantity_given = SUM(quantity_given)
quantity_left = stock_quantity - quantity_given

Used COALESCE to handle null values

Updated API Response

{
  "camp_id": 4,
  "camp_date": "2026-04-03",
  "camp_location": "swecha",
  "total_quantity_given": 0,
  "total_prescribed": 85,
  "medicines_breakdown": [
    {
      "medicine_id": "A0001",
      "medicine_formulation": "Amoxicillin 250mg",
      "total_prescribed": 25,
      "quantity_given": 0,
      "quantity_left": 112
    },
    {
      "medicine_id": "A0002",
      "medicine_formulation": "Cetirizine 10mg",
      "total_prescribed": 30,
      "quantity_given": 0,
      "quantity_left": 71
    },
    {
      "medicine_id": "A0005",
      "medicine_formulation": "Amoxicillin 250mg",
      "total_prescribed": 30,
      "quantity_given": 0,
      "quantity_left": 144
    }
  ]
}

Type of Change

🐛 Bug fix (incorrect aggregation logic)
♻️ Refactor (improved data handling)

How to Validate Locally

Start backend server:
```
uvicorn app.main:app --reload
```
Call analytics API:
```
GET /admin/analytics?camp_id=4
```
Verify:
- No duplicate rows per medicine (if grouped by ID)
- total_prescribed = sum of quantity
- quantity_given = correct sum
- quantity_left = stock - given

Testing Done

API endpoint tested manually
Aggregation verified with database values

Test Cases Covered:

Scenario	Expected Result	Status
Multiple prescriptions same medicine	Quantities summed	✅
No given quantity	quantity_given = 0	✅
Stock calculation	correct remaining value	✅

Known Limitations

medicine_formulation duplicates may appear if different medicine_id share same name
Grouping is currently based on medicine_id

Additional Notes

Field renamed internally to total_prescribed (semantic correctness)
Backward compatibility can be maintained if required

MR Acceptance Checklist

Quality & Correctness

Aggregation logic fixed
No breaking changes in API response

Maintainability

Code is clean and readable
Follows FastAPI + SQLAlchemy best practices

fix: fixing the prescribed count