Reports Controller Documentation

File: /controllers/reports.php

Purpose: Generates comprehensive patient reports for healthcare/insurance billing system

Last Updated: December 21, 2024

Total Functions: 12

Lines of Code: ~649

---

📋 Overview

The Reports Controller is a specialized reporting module designed for healthcare/medical billing systems that provides comprehensive patient and insurance company reporting capabilities. It handles:

• • Patient group statement reports
• • Insurance company billing summaries
• • Client area-based grouping reports
• • Monthly batch reporting with Arabic/English localization
• • Healthcare service billing analysis
• • Insurance discount calculations
• • Company-wise patient summaries
• • Payment method analysis

Primary Functions

• ✅ Generate patient group statements
• ✅ Create insurance company billing reports
• ✅ Process client area groupings
• ✅ Calculate insurance discounts and net amounts
• ✅ Multi-language month name conversion
• ✅ Branch-based filtering
• ✅ Date range analysis
• ✅ Printable report generation

Related Controllers

• • billsController.php - Healthcare billing operations
• • clientController.php - Patient/client management
• • insuranceController.php - Insurance company management
• • branchController.php - Branch management

---

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

1. patreport - Patient Report Form Display

Location: Line 88

Purpose: Display patient report selection form with filters

Process Flow:

1. Load client areas for dropdown

2. Load insurance companies (non-deleted)

3. Load branches for selection

4. Load client data for patient selection

5. Display patient report form template

Template Variables:

• • $clientArea - Available client areas
• • $insuranceCompanies - Active insurance companies
• • $branches - Available branches
• • $clientData - Patient list

---

2. printpatreport - Generate Patient Report

Location: Line 114

Purpose: Generate and display detailed patient group statement

Function Signature:

// Parameters from POST
$clientAreaId = filter_input(INPUT_POST, "clientArea");
$insuranceCompanyId = filter_input(INPUT_POST, "insuranceCompany");
$branchId = filter_input(INPUT_POST, "branchId");
$clientId = filter_input(INPUT_POST, "clientId");
$searchDateFrom = filter_input(INPUT_POST, "searchDateFrom");

Process Flow:

1. Filter and validate input parameters

2. Load area, insurance company, and branch data

3. Call getBillsPat() for patient billing data

4. Convert date to localized month format

5. Display printable patient report

---

3. getBillsPat() - Patient Bills Data Processor

Location: Line 317

Purpose: Retrieve and calculate patient billing data with insurance discounts

Function Signature:

function getBillsPat($clientAreaId, $insuranceCompanyId, $branchId, $searchDateFrom, $clientId)

Process Flow:

1. Build dynamic query filters for branch, date, client

2. Query bills with client company and insurance filters

3. Calculate insurance discounts and net amounts:

- networkname = Insurance company discount value

- username = Net required from insurance company

4. Aggregate totals: Accept, Discount, Carry, Net

5. Assign results to Smarty template

Calculation Logic:

$bill->networkname = ($bill->companyaccept * ($bill->insdiscountpercent) / 100);
$bill->username = $bill->companyaccept - $bill->networkname - $bill->customercarry;

---

4. getBillsCompany() - Company Summary Report

Location: Line 368

Purpose: Generate company-wise patient billing summaries

Function Signature:

function getBillsCompany($clientAreaId, $insuranceCompanyId, $branchId, $searchDateFrom)

Process Flow:

1. Get distinct companies with bills using getDistinctCompanies()

2. For each company:

- Query company's patient bills

- Calculate totals per company

- Aggregate insurance discounts

3. Calculate grand totals across all companies

4. Assign company summary data to template

---

5. getBillsInsCompany() - Insurance Company Report

Location: Line 570

Purpose: Generate insurance company billing analysis

Function Signature:

function getBillsInsCompany($insuranceCompanyId, $branchId, $searchDateFrom)

Process Flow:

1. Get distinct insurance companies with bills

2. Load insurance company names

3. For each insurance company:

- Calculate billing totals

- Process discount calculations

- Aggregate net amounts

4. Display insurance company summary

---

6. Month Name Conversion Functions

Location: Lines 467, 479, 525

Purpose: Convert month numbers to localized names

English Months (getmonthname()):

function getmonthname($monthId) {
    switch ($monthId) {
        case 1: return 'January';
        case 2: return 'February';
        // ... continues for all months
    }
}

Arabic Months (getmonthnamear()):

function getmonthnamear($monthId) {
    switch ($monthId) {
        case 1: return 'يناير';
        case 2: return 'فبراير';
        // ... continues for all months
    }
}

---

🔄 Workflows

Workflow 1: Patient Report Generation

---

🌐 URL Routes & Actions

Patient Report (do=printpatreport):

• • clientArea - Client area ID (-1 for all)
• • insuranceCompany - Insurance company ID
• • branchId - Branch ID (-1 for all)
• • clientId - Specific client ID (optional)
• • searchDateFrom - Month filter (YYYY-MM format)

Company Report (do=printcompanyreport):

• • clientArea - Client area ID
• • insuranceCompany - Insurance company ID
• • branchId - Branch ID
• • searchDateFrom - Month filter

---

🧮 Calculation Methods

Insurance Discount Calculation

// Network discount (insurance company discount)
$bill->networkname = ($bill->companyaccept * ($bill->insdiscountpercent) / 100);

// Net amount required from insurance
$bill->username = $bill->companyaccept - $bill->networkname - $bill->customercarry;

Monthly Total Aggregation

foreach ($companyClientsBills as $bill) {
    $totalAccept += $bill->companyaccept;      // Total accepted amount
    $totalDiscount += $bill->networkname;       // Total discount
    $totalCarry += $bill->customercarry;        // Customer portion
    $totalNet += $bill->username;               // Net insurance amount
}

Date Format Conversion

function getBatchMonthEn($searchDate) {
    $result = explode("-", $searchDate);
    $monthId = $result[1];
    $month = getmonthname($monthId);
    $array = str_split($result[0], 2);
    $year = $array[1];
    return $month . ' - ' . $year;
}

---

🔒 Security & Permissions

Authentication Requirements

• • All report actions require authentication via include_once("../public/authentication.php")
• • Session-based user validation
• • No specific permission levels implemented

Input Sanitization

// Proper input filtering
$clientAreaId = filter_input(INPUT_POST, "clientArea");
$insuranceCompanyId = filter_input(INPUT_POST, "insuranceCompany");
$branchId = filter_input(INPUT_POST, "branchId");

SQL Injection Prevention

• • Uses parameterized queries through DAO layer
• • Input validation for numeric IDs
• • String escaping for date parameters

---

📊 Performance Considerations

Database Optimization Tips

1. Indexes Required:

- bills(clientid, billdate, branchid, insurancecompanyid)

- client(clientareaid)

- bills(billdate) for date range queries

2. Query Optimization:

- Date filtering with LIKE operator for month-based searches

- Conditional WHERE clause building

- Efficient JOIN operations in extended DAO methods

3. Memory Management:

- Large datasets may require pagination

- Consider result set limits for high-volume months

Known Performance Issues

-- This query pattern may be slow for large datasets
SELECT * FROM bills WHERE billdate LIKE "2024-01-%"

-- Better approach with proper indexes
SELECT * FROM bills 
WHERE billdate >= "2024-01-01 00:00:00" 
  AND billdate <= "2024-01-31 23:59:59"

---

🐛 Common Issues & Troubleshooting

1. Missing Insurance Discounts

Issue: Insurance discount calculations showing 0

Cause: Missing insdiscountpercent in bills table

Debug:

SELECT billid, companyaccept, insdiscountpercent, customercarry 
FROM bills WHERE insdiscountpercent IS NULL OR insdiscountpercent = 0;

2. Incorrect Month Filtering

Issue: Date filter not returning expected results

Cause: Date format mismatch or timezone issues

Fix:

// Ensure proper month format
if ($searchDateFrom) {
    $dateFromQuery = 'and b.billdate like "' . $searchDateFrom . '-%"';
}

3. Template Variable Assignment Errors

Issue: Smarty template variables not displaying

Cause: Missing global variables or incorrect array assignments

Debug:

// Debug template assignments
print_r($companyClientsBills);
$smarty->assign("debug_data", $companyClientsBills);

---

🧪 Testing Scenarios

Test Case 1: Patient Report Generation

1. Select client area, insurance company, branch
2. Set month filter to current month
3. Verify bill count matches database query
4. Check discount calculations are correct
5. Confirm totals match individual bill sums

Test Case 2: Month Name Localization

1. Test both Arabic and English month conversion
2. Verify year extraction is correct (2-digit format)
3. Test edge cases (month 13, invalid dates)

Test Case 3: Multi-Company Aggregation

1. Create test data with multiple companies
2. Run company summary report
3. Verify company totals match individual sums
4. Check grand total accuracy

---

📚 Related Documentation

• • CLAUDE.md - PHP 8.2 migration guide
• • billsController.php - Healthcare billing operations
• • clientController.php - Patient management
• • Database Schema Documentation - Healthcare billing tables

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When healthcare billing changes occur