# Report Provinces Controller Documentation

**File**: `/controllers/reportprovinces.php`  
**Purpose**: Geographic reporting system for customer debt analysis by provinces, governorates, and client areas  
**Last Updated**: December 21, 2024  
**Total Functions**: 8  
**Lines of Code**: ~471

---

## 📋 Overview

The Report Provinces Controller provides comprehensive geographic analysis of customer accounts and debt distribution across different administrative regions. It offers:
- Multi-level geographic reporting (Government → Area → Client)
- Customer debt analysis by geographic region
- Bill-by-bill debt breakdown with aging analysis
- Client bill tracking with payment history
- AJAX-based province/area filtering
- Select2 integration for searchable dropdowns
- Special pharmacy/ERP system integration
- Dynamic client and area selection

### Primary Functions
- [x] Geographic customer debt reporting by provinces/governorates
- [x] Hierarchical area and client analysis
- [x] Customer bill aging and debt tracking
- [x] Payment history analysis per bill
- [x] AJAX-based geographic filtering
- [x] Select2 searchable dropdowns for large datasets
- [x] Pharmacy system integration for specialized reporting
- [x] Multi-database support for distributed systems

### Related Controllers
- [clientReportsController.php](clientReportsController.md) - Individual client reports
- [sellingReportByArea.php](sellingReportByArea.md) - Area-based sales analysis
- [reportClientAndSupplier.php](reportClientAndSupplier.md) - Client/supplier reporting

---

## 🗄️ Database Tables

### Geographic Hierarchy Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **government** | Province/governorate master | governmetid, governmentname |
| **goverarea** | Areas within governorates | governmentid, clientareaid |
| **clientarea** | Client area definitions | id, name, description |
| **client** | Customer master with area | clientid, clientname, clientdebt, clientareaid |

### Transaction Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **clientdebtchange** | Customer debt transactions | clientdebtchangeid, clientid, clientdebtchangeamount, clientdebtchangedate |
| **sellbill** | Sales bills | sellbillid, sellbillclientid, sellbilldate, sellbillaftertotalbill, sellbillfinalbill, conditions, obgyBillType |
| **returnsellbill** | Sales return bills | returnsellbillid, returnsellbillclientid, returnsellbilldate, returnsellbillaftertotalbill, returnsellbillfinalbill |

### Supporting Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **programsettings** | System configuration | programsettingsid, obgyPharmacyGroups |
| **user** | System users | userid, usergroupid |
| **youtubelink** | Tutorial videos | youtubelinkid, title, url |

---

## 🔑 Key Functions

### 1. **Default Action** - Province Report Display
**Location**: Line 51 (`empty($do)`)  
**Purpose**: Main geographic reporting interface

**Process Flow**:
1. Load first government and area for defaults
2. Load YouTube tutorial links
3. Display main reporting interface
4. Initialize dropdown data for province selection

**Template Variables**:
- `$firstGov` - Default government for initial display
- `$firstGovArea` - Default area within first government
- `$youtubes` - Tutorial video links

---

### 2. **getdata** - Geographic Data Analysis
**Location**: Line 69 (`$do == 'getdata'`)  
**Purpose**: Generate comprehensive geographic debt analysis

**Function Signature**:
```php
// POST parameters: fromdate, todate, clientid, governmentid, clientareaid
$fromdate = $_POST['fromdate'];
$todate = $_POST['todate'];  
$clientid = $_POST['clientid'];
$governmentid = $_POST['governmentid'];
$clientareaid = $_POST['clientareaid'];
```

**Process Flow**:

**Individual Client Analysis** (when only clientid provided):
1. Load client with geographic and area information
2. Get latest client debt change record
3. Load all sales bills for client
4. Calculate bill aging and remaining debt per bill:
   ```php
   foreach ($sellbills as $sellbill) {
       $diff = (strtotime($todays) - strtotime($sellbill->sellbilldate)) / (60 * 60 * 24);
       $sellbill->days = round($diff); // Days since bill
       
       if ($clientdebt > $sellbill->sellbillaftertotalbill) {
           $sellbill->sellbillaftertotalbill2 = $sellbill->sellbillaftertotalbill;
       } else {
           $sellbill->sellbillaftertotalbill2 = $clientdebt;
       }
       $clientdebt -= $sellbill->sellbillaftertotalbill;
   }
   ```

**Geographic Area Analysis** (when government/area provided):
1. Load government information
2. Get areas within government (filtered by clientareaid if provided)
3. For each area:
   - Load customers in area with debt > 0
   - Calculate area debt totals
   - For each customer, load and analyze bills
   - Apply date range filters if specified
   - Calculate bill aging analysis
4. Generate hierarchical report structure

**Key Calculations**:
- Bill aging in days from creation date
- Debt allocation across bills (FIFO basis)
- Area-level debt summations
- Grand totals across all areas

---

### 3. **getParmacyDataRoyal** - Specialized Pharmacy Integration
**Location**: Line 192 (`$do == 'getParmacyDataRoyal'`)  
**Purpose**: Specialized reporting for pharmacy ERP integration

**Features**:
- Multi-database support via `$_POST["erpDB"]`
- Specialized bill filtering (`obgyBillType = 1`)
- Payment tracking per bill
- Return bill handling with negative debt
- JSON output for external system integration

**Process Flow**:
1. Handle database switching if different ERP database specified
2. Load pharmacy-specific bills with payments
3. Calculate remaining debt per bill after payments
4. Process return bills separately
5. Return JSON data structure:
   ```php
   $data = array();
   $data['sell'] = $sellbillsn;      // Bills with remaining debt
   $data['ret'] = $sellbillsnRet;    // Return bills with credits
   ```

---

### 4. **clientBills** - Complete Client Bill Analysis  
**Location**: Line 286 (`$do == 'clientBills'`)
**Purpose**: Comprehensive client bill breakdown with payment tracking

**Features**:
- All bills for client (not just those with remaining debt)
- Payment tracking per individual bill
- Bills categorized into "with remain" and "without remain"
- Detailed payment history per bill
- Return bill processing

**Output Structure**:
```php
$data = array(
    'sellWithRemain' => $sellbillWithRemain,           // Bills with outstanding debt
    'sellWithoutRemain' => $sellbillWithoutRemain,     // Fully paid bills
    'retWithRemain' => $sellbillRetWithRemain,         // Returns with credits
    'retWithoutRemain' => $sellbillRetWithoutRemain    // Processed returns
);
```

---

### 5. **select2government()** - Government Dropdown AJAX
**Location**: Line 413  
**Purpose**: AJAX endpoint for searchable government selection

**Function Signature**:
```php
function select2government()
```

**Process Flow**:
1. Get search term from POST
2. Query governments matching search term
3. Return Select2-formatted JSON response:
   ```php
   $return_arr = array();
   foreach ($productsData as $pro) {
       $row_array['id'] = $pro['governmetid'];
       $row_array['text'] = $pro['governmentname'];
       array_push($return_arr, $row_array);
   }
   echo json_encode($return_arr);
   ```

---

### 6. **select2client()** - Client Search AJAX
**Location**: Line 431  
**Purpose**: AJAX endpoint for searchable client selection

**Query**: 
```sql
SELECT clientid, clientname as name
FROM client  
WHERE clientname LIKE '%{searchTerm}%' 
LIMIT 50
```

---

### 7. **select2clientarea()** - Area Search AJAX
**Location**: Line 449  
**Purpose**: AJAX endpoint for area selection within government

**Features**:
- Filtered by government ID
- Joins clientarea and goverarea tables
- Returns areas only within specified government

---

## 🔄 Workflows

### Workflow 1: Geographic Debt Analysis
```
┌─────────────────────────────────────────────────────────────┐
│                START: Select Geographic Filters             │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Determine Analysis Type                                 │
│     IF only clientid provided:                             │
│       └─→ Individual Client Analysis                       │
│     ELSE:                                                   │
│       └─→ Geographic Area Analysis                         │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2A. Individual Client Analysis                            │
│      - Load client with area information                   │
│      - Get latest debt change record                       │
│      - Load all sales bills for client                     │
│      - Calculate bill aging (days since creation)          │
│      - Allocate debt across bills (FIFO)                  │
│      - Display single client analysis                      │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2B. Geographic Area Analysis                              │
│      1. Load government information                        │
│      2. Get areas within government                        │
│      3. FOR EACH area:                                     │
│         │                                                   │
│         ├─→ Load customers with debt > 0                   │
│         │                                                   │
│         ├─→ FOR EACH customer:                             │
│         │   ├─ Load sales bills                            │
│         │   ├─ Apply date range filter                     │
│         │   ├─ Calculate aging analysis                    │
│         │   ├─ Allocate debt to bills                      │
│         │   └─ Sum area totals                             │
│         │                                                   │
│         └─→ Calculate area debt summary                    │
│      4. Calculate grand totals                             │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Generate Report Output                                  │
│     - Format hierarchical data                             │
│     - Display geographic breakdown                         │
│     - Show aging analysis                                  │
│     - Present summary totals                               │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| No `do` parameter | Default action | Main province reporting interface |
| `do=getdata` | Geographic analysis | Generate debt analysis by geography |
| `do=getParmacyDataRoyal` | Pharmacy integration | Specialized pharmacy reporting |
| `do=clientBills` | Complete client analysis | All bills for client with payments |
| `do=select2client` | `select2client()` | AJAX client search |
| `do=select2government` | `select2government()` | AJAX government search |
| `do=select2clientarea` | `select2clientarea()` | AJAX area search |

### Required Parameters by Action

**Geographic Analysis** (`do=getdata`):
- `governmentid` - Government/province ID (required for area analysis)
- `clientareaid` - Specific area ID (optional, filters within government)
- `clientid` - Specific client ID (optional, for individual analysis)
- `fromdate` - Start date filter (optional)
- `todate` - End date filter (optional)

**Pharmacy Integration** (`do=getParmacyDataRoyal`):
- `clientid` - Client ID (required)
- `erpDB` - Database name for multi-DB systems (optional)

**Client Bills** (`do=clientBills`):
- `clientid` - Client ID (required)
- `erpDB` - Database name (optional)

---

## 🧮 Calculation Methods

### Bill Aging Calculation
```php
$todays = date("Y-m-d H:i:s");
$diff = (strtotime($todays) - strtotime($sellbill->sellbilldate)) / (60 * 60 * 24);
$sellbill->days = round($diff);
```

### Debt Allocation to Bills (FIFO Method)
```php
$clientdebt = $client->clientdebt;
foreach ($sellbills as $sellbill) {
    if ($clientdebt > $sellbill->sellbillaftertotalbill) {
        // Client debt covers this entire bill
        $sellbill->sellbillaftertotalbill2 = $sellbill->sellbillaftertotalbill;
    } else {
        // Partial payment - allocate remaining debt
        $sellbill->sellbillaftertotalbill2 = $clientdebt;
    }
    
    if ($clientdebt > 0) {
        array_push($sellbillsn, $sellbill);
    }
    
    $clientdebt -= $sellbill->sellbillaftertotalbill;
}
```

### Payment Tracking Per Bill
```php
// Get payments on specific bill
$client_dept_change = $clientDeptChangeExt->sumByBillIdAndControl(
    $sellbill->sellbillid, 
    'clientPayedDeptSellBillsController.php'
);

// Adjust remaining debt
$sellbill->sellbillfinalbill = $sellbill->sellbillfinalbill - 
                               ((float) $client_dept_change->clientdebtchangeamount);
```

---

## 🔒 Security & Permissions

### Database Security
- Multi-database support with dynamic database switching
- Uses RedBean ORM with prepared statements
- Session-based database name storage

### AJAX Endpoint Security
- Limited result sets (LIMIT 50) to prevent data exposure
- Search term filtering for SQL injection prevention
- No authentication checks on AJAX endpoints (potential security issue)

### Recommended Security Improvements
```php
// Add authentication to AJAX endpoints
if (!isset($_SESSION['userid']) || empty($_SESSION['userid'])) {
    http_response_code(403);
    exit(json_encode(['error' => 'Unauthorized']));
}

// Sanitize search terms
$searchTerm = filter_input(INPUT_POST, 'searchTerm', FILTER_SANITIZE_STRING);
if (strlen($searchTerm) < 2) {
    exit(json_encode([]));
}
```

---

## 📊 Performance Considerations

### Database Performance
1. **Heavy Queries**: Geographic analysis can involve thousands of customers
2. **N+1 Problem**: Individual bill queries for each customer
3. **Date Range Processing**: Large date ranges can be expensive

### Optimization Opportunities
```sql
-- Current approach (N+1 queries)
-- For each client: SELECT * FROM sellbill WHERE sellbillclientid = ?

-- Better approach (single query)
SELECT c.*, sb.* 
FROM client c 
LEFT JOIN sellbill sb ON c.clientid = sb.sellbillclientid 
WHERE c.clientareaid = ? AND c.clientdebt > 0
```

### Memory Considerations
- Large geographic areas can load thousands of records into memory
- Consider pagination or progressive loading for large datasets
- JSON responses can be very large for areas with many customers

---

## 🐛 Common Issues & Troubleshooting

### 1. **Empty Geographic Reports**
**Issue**: No data shows for known areas with customers  
**Cause**: Geographic table relationships incomplete

**Debug**:
```sql
-- Check government-area relationships
SELECT g.governmentname, ga.*, ca.name 
FROM government g
JOIN goverarea ga ON g.governmetid = ga.governmentid  
JOIN clientarea ca ON ga.clientareaid = ca.id
LIMIT 10;

-- Check clients assigned to areas
SELECT c.clientname, ca.name as area_name, c.clientdebt
FROM client c
JOIN clientarea ca ON c.clientareaid = ca.id
WHERE c.clientdebt > 0
LIMIT 10;
```

### 2. **Bill Aging Calculation Issues**
**Issue**: Incorrect day counts or negative values  
**Cause**: Date format issues or timezone problems

**Fix**:
```php
// Ensure consistent date handling
$sellbilldate = new DateTime($sellbill->sellbilldate);
$today = new DateTime();
$diff = $today->diff($sellbilldate);
$sellbill->days = $diff->days;
```

### 3. **AJAX Dropdowns Not Loading**
**Issue**: Select2 dropdowns show no results  
**Cause**: Missing authentication or database connection issues

**Debug**: Check browser network tab for failed AJAX requests

---

## 🧪 Testing Scenarios

### Test Case 1: Geographic Hierarchy Display
```
1. Access main province report interface
2. Verify government dropdown loads
3. Select government and verify areas load
4. Select area and verify clients load
5. Test geographic drill-down functionality
```

### Test Case 2: Client Debt Aging Analysis
```
1. Select client with multiple bills
2. Verify aging calculation accuracy
3. Check debt allocation across bills
4. Test with various bill date ranges
5. Verify FIFO debt allocation logic
```

### Test Case 3: Multi-Database Integration
```
1. Test pharmacy integration with different database
2. Verify database switching functionality
3. Check data isolation between databases
4. Test error handling for invalid databases
```

### Test Case 4: AJAX Search Performance
```
1. Test client search with large customer base
2. Verify search response time < 2 seconds
3. Test with special characters in search terms
4. Verify result limitation (50 records max)
```

---

## 📚 Related Documentation

- [CLAUDE.md](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [clientReportsController.md](clientReportsController.md) - Individual client reporting
- [sellingReportByArea.md](sellingReportByArea.md) - Area-based sales reports
- [Database Schema Documentation](#) - Geographic table relationships

---

**Documented By**: AI Assistant  
**Review Status**: ✅ Complete  
**Next Review**: When major changes occur