Maintenance Clients Controller Documentation

File: /controllers/maintenanceclients.php

Purpose: Manages client data specifically for the maintenance service operations module

Last Updated: December 20, 2024

Total Functions: 3

Lines of Code: ~181

---

📋 Overview

The Maintenance Clients Controller is a specialized client management module designed specifically for the maintenance service system. It provides essential client CRUD operations but with a focus on the maintenance workflow context. Unlike the main client controller, this one integrates with YouTube tutorial links and maintains a clear separation for maintenance-specific client operations.

Primary Functions

✅ Add new maintenance clients
✅ Edit existing maintenance clients
✅ Display maintenance client listings
✅ AJAX data grid functionality
✅ Client search and filtering
✅ Integration with branch and user systems
✅ Soft delete functionality
✅ Date range filtering

Related Controllers

• clientController.php - Main customer management
• maintenancereceipts.php - Maintenance receipt handling
• maintenancedeliverys.php - Maintenance delivery operations

---

🗄️ Database Tables

Primary Tables (Direct Operations)

Table Name	Purpose	Key Columns
client	Customer master data	clientid, clientname, clientaddress, clientphone, clientmobile, clientdate, userid, branchId, conditions

### Reference Tables

Table Name	Purpose	Key Columns
user	System users	userid, employeename
branch	Branch information	branchId, branchName
youtubelink	Tutorial videos	youtubelinkid, title, url

---

🔑 Key Functions

1. Default Action (Empty $do) - Add Client Form

Location: Lines 18-23

Purpose: Display the client addition form for maintenance operations

Process Flow:

1. Display header template

2. Load maintenanceclientview/add.html template

3. Set maintenance flag to 1

4. Display footer template

Template: maintenanceclientview/add.html

---

2. show - Display Client Grid

Location: Lines 23-31

Purpose: Show maintenance clients in a data table format

Process Flow:

1. Include authentication check

2. Load all YouTube tutorial links

3. Assign YouTube data to template

4. Display maintenanceclientview/show.html template

5. Set maintenance flag to 1

Security: Includes authentication check

---

3. edit - Edit Client Form

Location: Lines 31-42

Purpose: Display client editing form with existing data

Function Signature:

$id = filter_input(INPUT_GET, 'id');
$del = filter_input(INPUT_GET, 'del');

Process Flow:

1. Include authentication check

2. Get client ID and deletion flag from GET parameters

3. Load client data from database

4. Assign client data and deletion flag to template

5. Display maintenanceclientview/edit.html template

SQL Query:

SELECT client.* FROM `client` WHERE clientid = ?

---

4. savedata() - Save/Update Client

Location: Lines 49-77

Purpose: Handle client creation and updates via AJAX

Function Signature:

function savedata()

Input Parameters:

• clientname - Client name
• clientphone - Phone number
• clientmobile - Mobile number
• clientaddress - Address
• clientid - Client ID (for updates)
• selectitr - Iteration selector for forms

Process Flow:

1. Get current date and user/branch session data

2. Filter and sanitize input parameters

3. Check if clientid exists to determine INSERT vs UPDATE

4. INSERT (New Client):

   INSERT INTO `client`(`clientname`, `clientaddress`, `clientphone`, `clientmobile`, `clientdate`, `userid`, `branchId`) 
   VALUES ('$clientname', '$clientaddress', '$clientphone', '$clientmobile','$date', $userid, $branchid)
   ```
5. **UPDATE** (Existing Client):
   ```sql
   UPDATE `client` SET `clientname`='$clientname',`clientaddress`='$clientaddress',`clientphone`='$clientphone',`clientmobile`='$clientmobile',`branchId`= $branchid WHERE clientid = $clientid
   ```
6. Return JSON response with client data

**Return Format**:

json

{

"id": 123,

"selectname": "clientid",

"selectid": "clientid1",

"text": "Client Name",

"selectitr": 1

}

---

### 5. **showajax()** - AJAX Data Grid
**Location**: Lines 80-173  
**Purpose**: Provide server-side DataTables processing for client grid

**Input Parameters**:
- `start_date` / `end_date` - Date range filter
- `del` - Deletion status filter
- `data1` - Client ID filter
- `data3` - Branch ID filter
- `search[value]` - Global search term
- `order` - Column sorting
- `start` / `length` - Pagination

**Process Flow**:
1. Define column mapping array
2. Build dynamic search query based on filters
3. Apply user input sanitization
4. Handle search across multiple columns
5. Apply sorting and pagination
6. Execute main query with JOINs
7. Format output for DataTables

**Main Query**:

sql

SELECT client.* ,employeename ,branchName FROM client

LEFT JOIN user ON client.userid = user.userid

LEFT JOIN branch ON client.branchId = branch.branchId

WHERE 1 [FILTERS] [ORDER BY] [LIMIT]

**Search Fields**:
- Client ID, name, phone, mobile
- Client date
- Branch name
- Employee name

**Output Actions**:
- Edit button: Links to edit form
- Delete button: AJAX soft delete functionality
- Conditional display based on deletion status

---

## 🔄 Workflows

### Workflow 1: Adding New Client

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

│ START: Add New Client │

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

│

▼

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

│ 1. Display Add Form │

│ - Load maintenanceclientview/add.html │

│ - Include maintenance navigation │

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

│

▼

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

│ 2. User Submits Form │

│ - Client fills out name, address, phone, mobile │

│ - Form posts to ?do=savedata │

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

│

▼

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

│ 3. Process savedata() │

│ - Validate input parameters │

│ - Insert into client table │

│ - Set userid from session │

│ - Set branchId from session │

│ - Set current date │

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

│

▼

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

│ 4. Return JSON Response │

│ - Include new client ID │

│ - Return client name for UI update │

│ - Provide selector information │

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

---

### Workflow 2: Client Data Grid Display

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

│ START: View Client List │

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

│

▼

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

│ 1. Load Show Page │

│ - Check authentication │

│ - Load YouTube tutorial links │

│ - Display maintenanceclientview/show.html │

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

│

▼

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

│ 2. DataTables AJAX Request │

│ - Browser initiates AJAX call to ?do=showajax │

│ - Includes search, filter, and pagination params │

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

│

▼

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

│ 3. Process showajax() │

│ - Build dynamic WHERE clause │

│ - Apply search across multiple fields │

│ - Join with user and branch tables │

│ - Apply pagination and sorting │

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

│

▼

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

│ 4. Format and Return Data │

│ - Create action buttons for each row │

│ - Handle soft-deleted items differently │

│ - Return JSON for DataTables rendering │

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

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=` (empty) | Default display | Show add client form |
| `do=show` | show action | Display clients data grid |
| `do=edit` | edit action | Show client edit form |
| `do=savedata` | `savedata()` | Save/update client via AJAX |
| `do=showajax` | `showajax()` | DataTables AJAX endpoint |

### Required Parameters by Action

**Add Client** (empty `do`):
- No parameters required

**Show Clients** (`do=show`):
- Authentication required
- No additional parameters

**Edit Client** (`do=edit`):
- `id` - Client ID to edit
- `del` - Deletion status flag

**Save Data** (`do=savedata`):
- `clientname` - Client name (required)
- `clientaddress` - Client address
- `clientphone` - Phone number
- `clientmobile` - Mobile number  
- `clientid` - For updates only
- `selectitr` - Form iteration selector

**AJAX Data** (`do=showajax`):
- DataTables standard parameters
- Optional filters: `start_date`, `end_date`, `data1`, `data3`

---

## 🧮 Calculation Methods

### No Complex Calculations
This controller primarily handles basic CRUD operations without financial calculations. The main processing involves:

1. **Data Filtering**:

php

if($data1 != ''){

$searchQuery .= " and client.clientid = ".$data1. " ";

}

2. **Date Range Processing**:

php

if($start_date != '' && $end_date != ''){

$searchQuery .=' and client.clientdate >= "' . $start_date . ' 00-00-00" and client.clientdate <= "' . $end_date . ' 23-59-55" ';

}

3. **Search Term Processing**:

php

if (isset($_POST['search']['value']) && $_POST['search']['value'] != "") {

$searchQuery .= "and ( client.clientid LIKE \"%'.$_POST["search"]["value"].'%\"

OR client.clientname LIKE \"%'.$_POST["search"]["value"].'%\"

OR client.clientmobile LIKE \"%'.$_POST["search"]["value"].'%\"

OR client.clientphone LIKE \"%'.$_POST["search"]["value"].'%\"

OR client.clientdate LIKE \"%'.$_POST["search"]["value"].'%\"

OR branchName LIKE \"%'.$_POST["search"]["value"].'%\"

OR employeename LIKE \"%'.$_POST["search"]["value"].'%\"

)

}

---

## 🔒 Security & Permissions

### Authentication Requirements
- **show** and **edit** actions require authentication via `../public/authentication.php`
- **savedata** and **showajax** rely on session data but don't explicitly check authentication

### Input Sanitization

php

$clientname = filter_input(INPUT_POST, 'clientname');

$clientphone = filter_input(INPUT_POST, 'clientphone');

$clientmobile = filter_input(INPUT_POST, 'clientmobile');

$clientaddress = filter_input(INPUT_POST, 'clientaddress');

$clientid = filter_input(INPUT_POST, 'clientid');

### SQL Injection Prevention
- Uses parameterized queries for SELECT operations
- **SECURITY ISSUE**: Direct string concatenation in INSERT/UPDATE queries

**Vulnerable Code**:

php

// ⚠️ POTENTIAL SQL INJECTION RISK

R::exec("INSERT INTO client(clientname, clientaddress, clientphone, clientmobile, clientdate, userid, branchId) VALUES

('$clientname', '$clientaddress', '$clientphone', '$clientmobile','$date', $userid, $branchid)");

**Recommended Fix**:

php

// ✅ SECURE VERSION

R::exec("INSERT INTO client(clientname, clientaddress, clientphone, clientmobile, clientdate, userid, branchId) VALUES

(?, ?, ?, ?, ?, ?, ?)", [$clientname, $clientaddress, $clientphone, $clientmobile, $date, $userid, $branchid]);

### Session Security
- Relies on `$_SESSION['userid']` and `$_SESSION['branchId']`
- No explicit session validation in savedata functions

---

## 📊 Performance Considerations

### Database Optimization
1. **Indexes Recommended**:
   - `client(clientid)` - Primary key (likely exists)
   - `client(conditions)` - For soft delete filtering
   - `client(branchId)` - For branch filtering
   - `client(clientdate)` - For date range queries

2. **Query Performance**:
   - JOINs with user and branch tables are efficient for display
   - Search across multiple text fields could be slow on large datasets
   - Consider full-text indexing for client names and addresses

### Potential Issues
- **N+1 Query Problem**: Not present in this controller
- **Large Result Sets**: DataTables pagination helps but large date ranges could still be slow
- **Wildcard Searches**: `LIKE "%search%"` searches can be performance-intensive

---

## 🐛 Common Issues & Troubleshooting

### 1. **AJAX Save Failures**
**Issue**: Client save returns 0 instead of success  
**Cause**: Database connection issues or constraint violations

**Debug**:

php

// Add to savedata() function

try {

// existing code

} catch (Exception $e) {

error_log("Client save error: " . $e->getMessage());

echo json_encode(['error' => $e->getMessage()]);

}

### 2. **DataTable Not Loading**
**Issue**: Client grid shows "No data available"  
**Cause**: AJAX endpoint issues or query problems

**Debug**:

sql

-- Test the main query manually

SELECT client.* ,employeename ,branchName FROM client

LEFT JOIN user ON client.userid = user.userid

LEFT JOIN branch ON client.branchId = branch.branchId

WHERE 1 AND client.conditions = 0

LIMIT 10;

### 3. **Permission Issues**
**Issue**: Users see unauthorized data  
**Cause**: Missing authentication checks

**Fix**: Add authentication to all AJAX endpoints:

php

if ($do == 'savedata' || $do == 'showajax') {

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

// existing code

}

### 4. **YouTube Links Not Loading**
**Issue**: Tutorial videos don't appear  
**Cause**: YouTube DAO initialization problems

**Debug**:

php

// Check if YouTube DAO is properly initialized

if (!$youtubeLinkDAO) {

error_log("YouTube DAO not initialized");

}

---

## 🧪 Testing Scenarios

### Test Case 1: Basic Client CRUD

1. Navigate to maintenanceclients.php

2. Verify add form loads properly

3. Submit new client with all fields filled

4. Check database for inserted record

5. Verify client appears in grid

6. Edit client and save changes

7. Confirm updates in database and grid

### Test Case 2: Data Grid Functionality

1. Load client show page

2. Test search functionality with various terms

3. Apply date range filters

4. Test column sorting

5. Navigate pagination if multiple pages

6. Verify action buttons work properly

### Test Case 3: Error Handling

1. Submit form with empty required fields

2. Test with special characters in names

3. Try to save very long client names

4. Test duplicate client handling

5. Verify error messages are appropriate

### Test Case 4: Security Testing

1. Test without proper authentication

2. Try SQL injection in search fields

3. Test XSS in client name fields

4. Verify session handling

5. Test unauthorized access to edit forms

```

---

📚 Related Documentation

• CLAUDE.md - PHP 8.2 migration guide
• clientController.md - Main client management system
• maintenancereceipts.md - Maintenance receipts handling
• Database Schema Documentation - Table relationships

---

Documented By: AI Assistant

Review Status: ✅ Complete

Security Issues Found: ⚠️ SQL Injection vulnerability in savedata()

Next Review: After security fixes applied

📚 ERP Docs

Maintenanceclients Documentation

Maintenance Clients Controller Documentation

📋 Overview

Primary Functions

Related Controllers

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

1. Default Action (Empty $do) - Add Client Form

2. show - Display Client Grid

3. edit - Edit Client Form

4. savedata() - Save/Update Client

📚 Related Documentation