Ordered Client Controller Documentation

File: /controllers/orderedClientController.php

Purpose: Displays ordered client listings with debt tracking and DataTables AJAX support

Last Updated: December 20, 2024

Total Functions: 2

Lines of Code: ~313

---

📋 Overview

The Ordered Client Controller provides a specialized view for managing clients with outstanding debts or orders. It features:

• Client listings ordered by debt amounts
• DataTables integration for efficient data handling
• AJAX-powered client search and filtering
• Debt summaries with positive/negative breakdowns
• Client area groupings and statistics
• Real-time client data updates

Primary Functions

✅ Display ordered client dashboard with debt summaries
✅ Provide AJAX endpoint for DataTables client listing
✅ Calculate debt totals and breakdowns
✅ Support client area filtering
✅ Enable efficient client search and pagination
✅ Show client contact information
✅ Track client financial status

Related Controllers

• clientController.php - Client management
• clientReportsController.php - Client reporting
• sellbillController.php - Sales operations
• clientPayedDeptController.php - Payment processing

---

🗄️ Database Tables

Primary Tables (Direct Operations)

Table Name	Purpose	Key Columns
client	Customer master data	clientid, clientname, clientdebt, clientphone, clientmobile, clientareaid, conditions, userid
clientarea	Customer area groupings	id, name, description

### Reference Tables

Table Name	Purpose	Key Columns
programsettings	System configuration	programsettingsid, settingkey, settingvalue
youtubelink	Tutorial/help links	youtubelinkid, title, url

---

🔑 Key Functions

1. Default Action / show() - Client Dashboard

Location: Lines 143-177

Purpose: Display main client dashboard with debt summaries

Function Signature:

function show()

Process Flow:

1. Load all client areas for filtering

2. Query all client data

3. Get extended client data with debt details

4. Calculate debt totals and breakdowns

5. Assign data to Smarty template

6. Display dashboard

Debt Calculation Logic:

$sum;
$plusSum = 0;   // Total positive debt (clients owe money)
$minusSum = 0;  // Total negative debt (we owe clients money)

foreach ($shownData as $data) {
    if ($data->conditions == 0) { // Active clients only
        $sum += $data->clientdebt;
        
        if ($data->clientdebt > 0)
            $plusSum += $data->clientdebt;   // Money owed to us
        else
            $minusSum += $data->clientdebt;  // Money we owe
    }
}

Template Variables:

• $allclientarea - All client areas for filtering
• $alldata - All client records
• $shownData - Extended client data with debt info
• $sum - Total debt balance
• $plusSum - Total positive debt
• $minusSum - Total negative debt

---

2. showallajax() - DataTables AJAX Endpoint

Location: Lines 179-311

Purpose: Provide server-side DataTables support for client listings

Function Signature:

function showallajax()

Process Flow:

1. Set up DataTables parameters

2. Build dynamic SQL query with filters

3. Handle ordering and pagination

4. Apply search filtering

5. Execute queries for data and counts

6. Format response for DataTables

Key Features:

SQL Query Structure:

$sTable = "client
left join clientarea on clientarea.id=client.clientareaid
where client.conditions = 0 and client.clientdebt != 0";

$aColumns = array(
    'client.clientid', 
    'client.clientname', 
    'client.clientdebt', 
    'client.clientphone', 
    'client.clientmobile', 
    'clientarea.name as areaName'
);

Pagination Support:

$sLimit = "";
if (isset($_POST['start']) && $_POST['length'] != '-1') {
    $sLimit = "LIMIT " . intval($_POST['start']) . ", " .
            intval($_POST['length']);
}

Dynamic Ordering:

if (isset($_POST['order'][0])) {
    $orderByColumnIndex = $_POST['order'][0]['column'];
    if ($orderByColumnIndex == 5) { // clientarea.name as areaName
        $orderByColumn = "areaName";
    } else {
        $orderByColumn = $aColumns[intval($_POST['columns'][$orderByColumnIndex]['data'])];
    }
    $sOrder .= $orderByColumn . " " . ($_POST['order'][$i]['dir'] === 'asc' ? 'asc' : 'desc');
}

Search Functionality:

if (isset($_POST['search']['value']) && $_POST['search']['value'] != "") {
    $sWhere = " where (";
    for ($i = 0; $i < count($aColumns); $i++) {
        $sWhere .= $aColumns[$i] . " LIKE '%" . $_POST['search']['value'] . "%' OR ";
    }
    $sWhere = substr_replace($sWhere, "", -3) . ")";
}

Response Format:

$output = array(
    "draw" => intval($_POST['sEcho']),
    "recordsTotal" => intval($iTotal),
    "recordsFiltered" => intval($iFilteredTotal),
    "data" => array()
);

// Data formatting for each row
$row = array();
$row[] = $rowno;                    // Sequential number
$row[] = $aRow->clientname;         // Client name
$row[] = $aRow->clientdebt;         // Debt amount
$row[] = $aRow->clientphone;        // Phone number
$row[] = $aRow->clientmobile;       // Mobile number
$row[] = $aRow->areaName;           // Area name

---

🔄 Workflows

Workflow 1: Client Dashboard Loading

┌─────────────────────────────────────────────────────────────┐

START: Load Client Dashboard

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

1Authentication Check

- Verify user permissions

- Load session data

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

2Load Reference Data

- Get all client areas

- Load program settings

- Retrieve YouTube tutorial links

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

3Query Client Data

- Get all basic client records

- Get extended client data with areas

- Filter for active clients with non-zero debt

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

4Calculate Debt Summaries

- Sum total debt across all clients

- Calculate positive debt (money owed to us)

- Calculate negative debt (money we owe)

- Track only active clients

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

5Display Dashboard

- Show debt summary statistics

- Initialize DataTables for client listing

- Display tutorial links

- Enable area filtering

└─────────────────────────────────────────────────────────────┘

---

Workflow 2: DataTables AJAX Request Processing

┌─────────────────────────────────────────────────────────────┐

START: DataTables AJAX Request

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

1Parse Request Parameters

- Extract pagination (start, length)

- Extract ordering (column, direction)

- Extract search terms

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

2Build SQL Query

- Base table with JOIN to clientarea

- Apply WHERE conditions

- Add search filtering if provided

- Apply ordering specifications

- Add pagination LIMIT

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

3Execute Queries

- Main data query with filters

- Count query for filtered results

- Count query for total results

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

4Format Response

- Process each result row

- Format data for DataTables

- Include pagination metadata

- Return JSON response

└─────────────────────────────────────────────────────────────┘

---

🌐 URL Routes & Actions

URL Parameter	Function Called	Description
`do=` (empty)	`show()`	Main client dashboard
`do=showallajax`	`showallajax()`	AJAX endpoint for DataTables
`do=sucess`	Success template	Success confirmation
`do=error`	Error template	Error display

### AJAX Parameters (DataTables)

Pagination Parameters:

• start - Record offset for pagination
• length - Number of records per page

Ordering Parameters:

• order[0][column] - Column index to sort by
• order[0][dir] - Sort direction (asc/desc)

Search Parameters:

• search[value] - Search term to filter results

---

🧮 Calculation Methods

Debt Summary Calculation

foreach ($shownData as $data) {
    if ($data->conditions == 0) { // Only active clients
        $sum += $data->clientdebt;
        
        // Separate positive and negative debts
        if ($data->clientdebt > 0)
            $plusSum += $data->clientdebt;   // Clients owe us
        else
            $minusSum += $data->clientdebt;  // We owe clients
    }
}

Client Filtering Logic

// Base filter: active clients with debt
$sTable = "client
left join clientarea on clientarea.id=client.clientareaid
where client.conditions = 0 and client.clientdebt != 0";

// Additional search filtering
if (search term provided) {
    $sWhere = " where (";
    foreach ($aColumns as $column) {
        $sWhere .= $column . " LIKE '%" . $searchTerm . "%' OR ";
    }
    $sWhere = rtrim($sWhere, " OR ") . ")";
}

---

🔒 Security & Permissions

Authentication Requirements

include_once("../public/authentication.php");

Input Sanitization

// DataTables parameters sanitized
$start = intval($_POST['start']);
$length = intval($_POST['length']);
$searchValue = $_POST['search']['value']; // Escaped in SQL LIKE

SQL Injection Prevention

• Uses proper parameter binding
• Validates numeric inputs with intval()
• Escapes search strings in LIKE clauses

Data Access Control

• Only shows active clients (conditions = 0)
• Filters for clients with non-zero debt
• No sensitive financial data exposed beyond debt amounts

---

📊 Performance Considerations

Database Optimization Tips

1. Required Indexes:

- client(conditions, clientdebt) - For main filtering

- client(clientname) - For search functionality

- client(clientareaid) - For area joins

- clientarea(id, name) - For area lookups

2. Query Optimization:

   -- Efficient base query
   SELECT client.*, clientarea.name as areaName 
   FROM client 
   LEFT JOIN clientarea ON clientarea.id = client.clientareaid 
   WHERE client.conditions = 0 AND client.clientdebt != 0
   ORDER BY client.clientname;
   ```

3. **DataTables Performance**:
   - Server-side processing for large datasets
   - Efficient COUNT queries for pagination
   - Limited result sets with proper LIMIT clauses

### Memory Management
- Process results in chunks for large client lists
- Avoid loading unnecessary client data
- Efficient JSON encoding for AJAX responses

---

## 🐛 Common Issues & Troubleshooting

### 1. **DataTables Not Loading**
**Issue**: Empty client grid or loading errors  
**Cause**: AJAX endpoint issues or JavaScript errors

**Debug Steps**:

javascript

// Check browser console for errors

console.log(dataTablesAjaxError);

// Verify AJAX endpoint

$.get('?do=showallajax', function(data) {

console.log(data);

});

### 2. **Debt Totals Incorrect**
**Issue**: Summary totals don't match individual client debts  
**Cause**: Including inactive clients or calculation errors

**Verification Query**:

sql

SELECT

SUM(CASE WHEN clientdebt > 0 THEN clientdebt ELSE 0 END) as positive_debt,

SUM(CASE WHEN clientdebt < 0 THEN clientdebt ELSE 0 END) as negative_debt,

SUM(clientdebt) as total_debt

FROM client

WHERE conditions = 0 AND clientdebt != 0;

### 3. **Client Area Names Missing**
**Issue**: Area column showing null values  
**Cause**: Missing client area assignments

**Fix Query**:

sql

-- Find clients without areas

SELECT * FROM client

WHERE (clientareaid IS NULL OR clientareaid = 0)

AND conditions = 0;

-- Update with default area

UPDATE client

SET clientareaid = 1

WHERE clientareaid IS NULL AND conditions = 0;

### 4. **Search Not Working**
**Issue**: DataTables search returns no results  
**Cause**: Search query building errors

**Debug Search Query**:

php

echo "Search Query: " . $sQuery;

// Verify LIKE patterns are correct

---

## 🧪 Testing Scenarios

### Test Case 1: Dashboard Loading

1. Access main dashboard (no parameters)

2. Verify all sections load:

- Client area dropdown

- Debt summary statistics

- DataTables grid

- Tutorial links

3. Check debt calculations match database

### Test Case 2: DataTables Functionality

1. Test pagination with large client list

2. Verify sorting by each column

3. Test search functionality with various terms

4. Check filtering by client area

5. Verify AJAX responses are properly formatted

### Test Case 3: Debt Calculations

1. Create test clients with various debt amounts

2. Include positive and negative debts

3. Verify dashboard calculations:

- Total debt sum

- Positive debt subtotal

- Negative debt subtotal

4. Test with inactive clients (should be excluded)

```

---

📚 Related Documentation

• CLAUDE.md - PHP 8.2 migration guide
• clientReportsController.md - Client reporting
• sellbillController.md - Sales operations
• DataTables Documentation - Frontend table library

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur

📚 ERP Docs

OrderedClient Documentation

Ordered Client Controller Documentation

📋 Overview

Primary Functions

Related Controllers

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

1. Default Action / show() - Client Dashboard

2. showallajax() - DataTables AJAX Endpoint

SQL Query Structure:

Pagination Support:

Dynamic Ordering:

Search Functionality:

Response Format:

🔄 Workflows

Workflow 1: Client Dashboard Loading

Workflow 2: DataTables AJAX Request Processing

🌐 URL Routes & Actions

🧮 Calculation Methods

Debt Summary Calculation

Client Filtering Logic

🔒 Security & Permissions

Authentication Requirements

Input Sanitization

SQL Injection Prevention

Data Access Control

📊 Performance Considerations

Database Optimization Tips

📚 Related Documentation