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

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

Related Controllers

• • sellbillController.php - Sales operations
• • billsController.php - Optical bills
• • clientController.php - Customer management
• • clientareaController.php - Customer area management
• • salesreportbills.md - Sales reporting

---

🗄️ Database Tables

Primary Tables (Analysis Sources)

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

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

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

Smart Date Defaulting:

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:

function getData($datefrom, $dateto, $queryString, $queryStringR, 
                 $queryString1, $queryString1R, $queryString1SR)

Core Analysis Logic:

1. Area Filtering Setup:

if (isset($_POST['clientareaid']) && !empty($_POST['clientareaid'])) {
    $areaQueryString = ' AND client.clientareaid = ' . $_POST['clientareaid'] . ' ';
} else {
    $areaQueryString = '';
}

2. Inactive Customer Discovery:

// Get customers without bills in the specified period
$allBillsData = $clientExt->clientsWithoutBillsWithArea($areaQueryString, $queryString1, 
                                                         $queryString, $queryStringR, 
                                                         $queryString1R, $queryString1SR);

3. Customer Data Processing:

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

---

🌐 URL Routes & Actions

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

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

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

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

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

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

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

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

-- 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 - PHP 8.2 migration guide
• • 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