# 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
- [x] Generate patient group statements
- [x] Create insurance company billing reports
- [x] Process client area groupings
- [x] Calculate insurance discounts and net amounts
- [x] Multi-language month name conversion
- [x] Branch-based filtering
- [x] Date range analysis
- [x] 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)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **bills** | Healthcare service bills | billid, clientid, productstotalprice, finalnetbillvalue, billdate, branchid, insurancecompanyid |
| **client** | Patient/client information | clientid, clientname, clientareaid |
| **clientarea** | Client area/region groupings | id, name, description |
| **insurancecompanie** | Insurance companies | insurancecompanyid, name, deleted |
| **branch** | Medical branches/locations | branchid, branchname |

### Supporting Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **youtubelink** | Tutorial video links | youtubelinkid, title, url |

---

## 🔑 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**:
```php
// 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**:
```php
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**:
```php
$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**:
```php
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**:
```php
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()`):
```php
function getmonthname($monthId) {
    switch ($monthId) {
        case 1: return 'January';
        case 2: return 'February';
        // ... continues for all months
    }
}
```

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

---

## 🔄 Workflows

### Workflow 1: Patient Report Generation
```
┌─────────────────────────────────────────────────────────────┐
│                START: Patient Report Request                │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Display Report Selection Form                           │
│     - Load client areas                                     │
│     - Load insurance companies                              │
│     - Load branches                                         │
│     - Load patient list                                     │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Process Report Parameters                               │
│     - Validate area, insurance, branch selections          │
│     - Parse date range                                      │
│     - Set patient filter                                    │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Execute getBillsPat()                                   │
│     - Build dynamic SQL filters                             │
│     - Query bills with insurance/area filters              │
│     - Calculate insurance discounts                         │
│     - Aggregate financial totals                            │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Format and Display Report                              │
│     - Convert dates to localized format                    │
│     - Display patient group statement                       │
│     - Show financial summaries                              │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=` (empty) | Default | Redirect to authentication |
| `do=patreport` | Patient form | Display patient report selection form |
| `do=printpatreport` | `getBillsPat()` | Generate patient group statement |
| `do=companyreport` | Company form | Display company report selection |
| `do=printcompanyreport` | `getBillsCompany()` | Generate company summary report |
| `do=inscompanyreport` | Insurance form | Display insurance company selection |
| `do=printinscompanyreport` | `getBillsInsCompany()` | Generate insurance company report |
| `do=paymethod` | Payment method form | Display payment method analysis |

### Required Parameters by Action

**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
```php
// 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
```php
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
```php
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
```php
// 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
```sql
-- 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**:
```sql
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**:
```php
// 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**:
```php
// 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](/Applications/AMPPS/www/erp19/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