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

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

Related Controllers

• clientReportsController.php - Individual client reports
• sellingReportByArea.php - Area-based sales analysis
• reportClientAndSupplier.php - 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:

// 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:

   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 - PHP 8.2 migration guide
• clientReportsController.md - Individual client reporting
• 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

📚 ERP Docs

Reportprovinces Documentation

Report Provinces Controller Documentation

📋 Overview

Primary Functions

Related Controllers

🗄️ Database Tables

Geographic Hierarchy Tables

🔑 Key Functions

1. Default Action - Province Report Display

2. getdata - Geographic Data Analysis

📚 Related Documentation