# Clients Without Bills Controller Documentation

**File**: `/controllers/clientsWithoutBills.php`  
**Purpose**: Identifies and reports customers who have not placed orders within specified date ranges  
**Last Updated**: December 20, 2024  
**Total Functions**: 4  
**Lines of Code**: ~254

---

## 📋 Overview

The Clients Without Bills Controller is an analytical reporting tool that helps identify inactive customers by finding those who have not placed any orders within a specified date range. This is crucial for customer retention analysis, sales team follow-up activities, and marketing campaigns targeting dormant customers. The controller features:

- Customer inactivity analysis
- Date range filtering for activity periods
- Geographic filtering by customer area
- Last bill tracking for each customer
- Multi-source bill type analysis
- Customer area-based segmentation
- Sales team performance insights

### Primary Functions
- [x] Identify customers without recent activity
- [x] Track last bill dates for inactive customers
- [x] Filter by customer geographic areas
- [x] Date range analysis for inactivity periods
- [x] Multi-bill-type analysis (sales, optical, returns)
- [x] Customer area segmentation
- [x] Inactivity reporting and insights

### Related Controllers
- [sellbillController.php](sellbillController.md) - Sales operations
- [billsController.php](#) - Optical bills
- [clientController.php](#) - Customer management
- [clientareaController.php](#) - Customer area management
- [salesreportbills.md](salesreportbills.md) - Sales reporting

---

## 🗄️ Database Tables

### Primary Tables (Analysis Sources)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **client** | Customer master data | clientid, clientname, clientareaid, conditions |
| **sellbill** | Sales bills | sellbillid, sellbillclientid, sellbilldate, conditions |
| **bills** | Optical bills | billid, clientid, billdate, deleted |
| **returnsellbill** | Sales returns | returnsellbillid, returnsellbillclientid, returnsellbilldate, conditions |
| **billsreturn** | Optical returns | billid, clientid, date, deleted |
| **sellbillandrutern** | Combined bills | sellbillid, sellbillclientid, sellbilldate, conditions |

### Reference Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **clientarea** | Customer areas/regions | id, name, description |
| **government** | Government/state data | governmentid, governmentname |
| **goverarea** | Government area relationships | goverareaid, governmentid, clientareaid |
| **user** | System users | userid, username, userstoreid, branchId |
| **youtubelink** | Tutorial links | youtubelinkid, title, url |

---

## 🔑 Key Functions

### 1. **Default Action** - Inactive Customer Analysis
**Location**: Line 114-186  
**Purpose**: Display search form and process inactive customer analysis

**Process Flow**:
1. Load search tools (categories, YouTube tutorials, user data)
2. Load store and branch data for filtering (if user has access)
3. Load seller and user data for assignments
4. Parse search parameters (date range, area filter)
5. Build query strings for all bill types
6. Call `getData()` to identify inactive customers
7. Display results via `clientwithoutbills/show.html`

**Search Parameter Processing**:
```php
$datefrom = filter_input(INPUT_POST, 'datefrom');
$dateto = filter_input(INPUT_POST, 'dateto');

// Date filtering for all bill types
if (isset($datefrom) && !empty($datefrom)) {
    $queryString .= ' and date(bills.billdate)  >= "' . $datefrom . '" ';
    $queryStringR .= ' and date(billsreturn.`date`)  >= "' . $datefrom . '" ';
    $queryString1 .= ' and date(sellbill.sellbilldate)  >= "' . $datefrom . '" ';
    $queryString1R .= ' and date(returnsellbill.returnsellbilldate)  >= "' . $datefrom . '" ';
    $queryString1SR .= ' and date(sellbillandrutern.sellbilldate)  >= "' . $datefrom . '" ';
}

if (isset($dateto) && !empty($dateto)) {
    $queryString .= ' and date(bills.billdate)  <= "' . $dateto . '" ';
    $queryStringR .= ' and  date(billsreturn.`date`)  <= "' . $dateto . '" ';
    $queryString1 .= ' and date(sellbill.sellbilldate)  <= "' . $dateto . '" ';
    $queryString1R .= ' and date(returnsellbill.returnsellbilldate)  <= "' . $dateto . '" ';
    $queryString1SR .= ' and date(sellbillandrutern.sellbilldate)  <= "' . $dateto . '" ';
}
```

**Area Filtering**:
```php
if (isset($_POST['clientareaid']) && !empty($_POST['clientareaid'])) {
    $areaData = $ClientareaDAO->load($_POST['clientareaid']);
    $messageData .= 'المنطقة:  ' . $areaData->name . ' ';
}
```

**Smart Date Defaulting**:
```php
if (empty($datefrom) && empty($dateto)) {
    // Default to today if no dates specified
    $today = date("Y-m-d");
    $queryString .= ' and date(bills.billdate)  = "' . $today . '" ';
    $queryStringR .= ' and date(billsreturn.`date`)  = "' . $today . '" ';
    $queryString1 .= ' and  date(sellbill.sellbilldate)  = "' . $today . '" ';
    $queryString1R .= ' and date(returnsellbill.returnsellbilldate)  = "' . $today . '" ';
    $queryString1SR .= ' and date(sellbillandrutern.sellbilldate)  = "' . $today . '" ';
}
```

---

### 2. **getData()** - Inactive Customer Discovery Engine
**Location**: Line 194-253  
**Purpose**: Identify customers who have not placed bills in the specified period

**Function Signature**:
```php
function getData($datefrom, $dateto, $queryString, $queryStringR, 
                 $queryString1, $queryString1R, $queryString1SR)
```

**Core Analysis Logic**:

**1. Area Filtering Setup**:
```php
if (isset($_POST['clientareaid']) && !empty($_POST['clientareaid'])) {
    $areaQueryString = ' AND client.clientareaid = ' . $_POST['clientareaid'] . ' ';
} else {
    $areaQueryString = '';
}
```

**2. Inactive Customer Discovery**:
```php
// Get customers without bills in the specified period
$allBillsData = $clientExt->clientsWithoutBillsWithArea($areaQueryString, $queryString1, 
                                                         $queryString, $queryStringR, 
                                                         $queryString1R, $queryString1SR);
```

**3. Customer Data Processing**:
```php
class clientData {
    public $clientid;
    public $clientname;
    public $lastBillDate;
    public $lastBillId;
}

$existId = array();
$allBillsDataArr = array();

foreach ($allBillsData as $key => $value) {
    if (!in_array($value->clientid, $existId)) {
        $myclient = new clientData();
        $myclient->clientid = $value->clientid;
        $myclient->clientname = $value->clientname;
        
        // Get last bill information for this customer
        $billData = $mySellbillEx->getlastbill($value->clientid);
        $myclient->lastBillDate = $billData->sellbilldate;
        $myclient->lastBillId = $billData->sellbillid;
        
        array_push($existId, $value->clientid);
        array_push($allBillsDataArr, $myclient);
    }
}
```

---

### 3. **getCategoryChilds()** - Category Loading (Referenced)
**Location**: Line 121  
**Purpose**: Load product categories for filtering (function defined elsewhere)

**Note**: This function is called but not defined in this file - it's likely defined in `reportfunctions.php`.

---

### 4. **Load Functions** - Data Loading Utilities (Referenced)
**Location**: Lines 130-140  
**Purpose**: Load various reference data for filtering

**Functions Called**:
- `loadStore()` - Load store data if user has access
- `loadBranch()` - Load branch data if user has access  
- `loadseller()` - Load seller data for assignments
- `loadusers()` - Load user data for assignments

**Note**: These functions are defined elsewhere, likely in `reportfunctions.php`.

---

## 🔄 Workflows

### Workflow 1: Inactive Customer Identification
```
┌─────────────────────────────────────────────────────────────┐
│               START: Inactive Customer Analysis            │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Load Search Interface                                   │
│     - Load product categories                               │
│     - Load YouTube tutorials                                │
│     - Load user, store, branch data                         │
│     - Load seller and user assignments                      │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Process Search Parameters                               │
│     - Parse date range (from/to)                            │
│     - Parse customer area filter                            │
│     - Build message for selected criteria                   │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Build Query Strings                                     │
│     FOR EACH bill type:                                     │
│       │                                                     │
│       ├─→ Optical bills (bills table)                      │
│       ├─→ Optical returns (billsreturn table)              │
│       ├─→ Sales bills (sellbill table)                     │
│       ├─→ Sales returns (returnsellbill table)             │
│       └─→ Combined bills (sellbillandrutern table)         │
│                                                             │
│     Apply date filtering to all query strings              │
│                                                             │
│     IF no dates provided:                                  │
│       └─→ Default to today's date for all queries          │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Execute Inactive Customer Analysis                      │
│     CALL getData():                                         │
│       │                                                     │
│       ├─→ Build area filter if specified                   │
│       │                                                     │
│       ├─→ Execute clientsWithoutBillsWithArea() query:     │
│       │   │                                                │
│       │   ├─ Find all customers in specified area          │
│       │   ├─ Exclude customers with bills in date range    │
│       │   └─ Return customers without activity             │
│       │                                                     │
│       ├─→ Process each inactive customer:                  │
│       │   ├─ Create clientData object                      │
│       │   ├─ Set customer ID and name                      │
│       │   ├─ Find last bill date and ID                    │
│       │   └─ Add to results array                          │
│       │                                                     │
│       └─→ Assign results to template                       │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Display Results                                         │
│     - Show search criteria used                             │
│     - List inactive customers                               │
│     - Show last bill date for each                          │
│     - Display total count of inactive customers             │
│     - Provide last bill ID links                            │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=` (empty) | Default action | Display inactive customer search form and results |

### Form Parameters

**Inactive Customer Analysis**:
- `datefrom` - Start date of analysis period (YYYY-MM-DD)
- `dateto` - End date of analysis period (YYYY-MM-DD)  
- `clientareaid` - Customer area ID filter (optional)

**Example Analysis**:
```
Find all customers who did not place any orders between 2024-01-01 and 2024-12-31
clientsWithoutBills.php?datefrom=2024-01-01&dateto=2024-12-31
```

---

## 🧮 Calculation Methods

### Inactive Customer Identification Logic
```php
// Query logic (executed in DAO layer)
// Find customers who exist but have NO bills in the specified date range

SELECT DISTINCT client.clientid, client.clientname
FROM client 
WHERE client.conditions = 0  -- Active customers only
AND client.clientareaid = [AREA_FILTER]  -- If area specified
AND NOT EXISTS (
    SELECT 1 FROM sellbill 
    WHERE sellbillclientid = client.clientid 
    AND sellbilldate BETWEEN 'datefrom' AND 'dateto'
    AND conditions = 0
)
AND NOT EXISTS (
    SELECT 1 FROM bills 
    WHERE clientid = client.clientid 
    AND billdate BETWEEN 'datefrom' AND 'dateto'
    AND deleted = 0
)
-- Similar NOT EXISTS for other bill types...
```

### Last Bill Discovery
```php
// For each inactive customer, find their most recent bill
$billData = $mySellbillEx->getlastbill($value->clientid);
$myclient->lastBillDate = $billData->sellbilldate;
$myclient->lastBillId = $billData->sellbillid;
```

---

## 🔒 Security & Permissions

### Access Control
- Requires authentication via `../public/authentication.php`
- No specific permission level checks mentioned
- Standard session-based authentication

### User Data Access Control
```php
$user = $userDAO->load($_SESSION['userid']);

// Store access control
if ($user->userstoreid == 0) {
    // User can see all stores
    $storeData = loadStore();
} 
// Otherwise user limited to their assigned store

// Branch access control  
if ($user->branchId == 0) {
    // User can see all branches
    $branchData = loadBranch();
}
// Otherwise user limited to their assigned branch
```

### Input Sanitization
- Uses `filter_input(INPUT_POST, ...)` for form parameters
- Date validation before SQL inclusion
- Area ID validation as integer

---

## 📊 Performance Considerations

### Database Optimization Tips
1. **Indexes Required**:
   - `client(clientareaid, conditions)`
   - `sellbill(sellbillclientid, sellbilldate, conditions)`
   - `bills(clientid, billdate, deleted)`
   - `returnsellbill(returnsellbillclientid, returnsellbilldate, conditions)`
   - `billsreturn(clientid, date, deleted)`
   - `sellbillandrutern(sellbillclientid, sellbilldate, conditions)`

2. **Query Optimization**:
   - NOT EXISTS queries can be expensive without proper indexes
   - Consider using LEFT JOIN with NULL checks for better performance
   - Date filtering should use indexed date columns

3. **Performance Alternatives**:
```sql
-- Instead of multiple NOT EXISTS (slow)
-- Use LEFT JOINs with NULL checks (faster)
SELECT DISTINCT c.clientid, c.clientname
FROM client c
LEFT JOIN sellbill sb ON c.clientid = sb.sellbillclientid 
    AND sb.sellbilldate BETWEEN ? AND ? 
    AND sb.conditions = 0
LEFT JOIN bills b ON c.clientid = b.clientid 
    AND b.billdate BETWEEN ? AND ?
    AND b.deleted = 0
WHERE c.conditions = 0
AND sb.sellbillid IS NULL 
AND b.billid IS NULL
-- Continue for other bill types...
```

### Memory Management
- Large customer bases may return many inactive customers
- Consider pagination for very large datasets
- Process customer data in batches if needed

---

## 🐛 Common Issues & Troubleshooting

### 1. **No Inactive Customers Found**
**Issue**: Report shows no results when inactive customers expected  
**Causes**:
- Date range too narrow
- All customers active in period
- Area filter too restrictive
- Query logic issues

**Debug**:
```sql
-- Check total customers in area
SELECT COUNT(*) FROM client 
WHERE conditions = 0 
AND clientareaid = [AREA_ID];

-- Check total bills in period
SELECT COUNT(*) FROM sellbill 
WHERE sellbilldate BETWEEN 'from' AND 'to' 
AND conditions = 0;
```

### 2. **Performance Issues with Large Date Ranges**
**Issue**: Query runs slowly with wide date ranges  
**Cause**: Inefficient NOT EXISTS queries

**Solutions**:
```php
// Add date range validation
if (!empty($datefrom) && !empty($dateto)) {
    $daysDiff = (strtotime($dateto) - strtotime($datefrom)) / (60*60*24);
    if ($daysDiff > 365) {
        throw new Exception("Date range too large. Maximum 365 days.");
    }
}
```

### 3. **Incorrect Last Bill Information**
**Issue**: Last bill dates/IDs don't match expected values  
**Cause**: `getlastbill()` function logic

**Debug**:
```sql
-- Manually check last bill for customer
SELECT sellbillid, sellbilldate FROM sellbill 
WHERE sellbillclientid = [CUSTOMER_ID] 
AND conditions = 0
ORDER BY sellbilldate DESC LIMIT 1;
```

### 4. **Area Filtering Not Working**
**Issue**: Area filter doesn't limit results correctly  
**Cause**: Client area assignments or filter logic

**Debug**:
```sql
-- Check client area assignments
SELECT c.clientid, c.clientname, c.clientareaid, ca.name
FROM client c
LEFT JOIN clientarea ca ON c.clientareaid = ca.id  
WHERE c.clientid IN (1,2,3);

-- Check if area exists
SELECT * FROM clientarea WHERE id = [AREA_ID];
```

---

## 🧪 Testing Scenarios

### Test Case 1: Basic Inactive Customer Identification
```
1. Create customers with no recent bills
2. Create customers with recent bills  
3. Set date range excluding recent bills
4. Verify only inactive customers appear
5. Check last bill information is accurate
```

### Test Case 2: Date Range Analysis
```
1. Create bills on different dates
2. Set date range excluding some bills
3. Verify customers with excluded bills show as inactive
4. Test edge cases (start/end of range)
5. Test with today's date default
```

### Test Case 3: Area Filtering
```
1. Create customers in different areas
2. Create bills for customers in each area
3. Filter by specific area
4. Verify only customers from selected area appear
5. Test with no area filter (all areas)
```

### Test Case 4: Multiple Bill Type Analysis
```
1. Create customers with different bill types:
   - Sales bills only
   - Optical bills only  
   - Return bills only
   - Mixed bill types
2. Run inactive customer analysis
3. Verify customers with ANY bill type are excluded
4. Verify only truly inactive customers appear
```

---

## 📚 Related Documentation

- [CLAUDE.md](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [sellbillController.md](sellbillController.md) - Sales operations
- [clientController.php](#) - Customer management
- [clientareaController.php](#) - Customer area management
- [Database Schema Documentation](#) - Table relationships

---

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