Maintenance Products Controller Documentation

File: /controllers/maintenanceproducts.php

Purpose: Manages product catalog and categories specifically for maintenance service operations

Last Updated: December 20, 2024

Total Functions: 4

Lines of Code: ~209

---

📋 Overview

The Maintenance Products Controller handles product and product category management within the maintenance service context. It provides comprehensive CRUD operations for both products and product categories, with hierarchical category support and special maintenance-specific features like custom query execution. This controller is essential for organizing the products that require maintenance services.

Primary Functions

✅ Manage product categories with hierarchical structure
✅ Add and edit maintenance-related products
✅ Product categorization and organization
✅ Custom query execution for products
✅ AJAX data grid functionality
✅ Product search and filtering
✅ Integration with maintenance workflow

Related Controllers

• productController.php - Main product management system
• productCatController.php - Category management
• maintenanceclients.php - Client management
• maintenancereceipts.php - Maintenance processing

---

🗄️ Database Tables

Primary Tables (Direct Operations)

Table Name	Purpose	Key Columns
product	Product master data	productId, productName, productDescription, productCatId, productDate, conditions, userId, runquery
productcat	Product categories	productCatId, productCatName, productCatDate, productCatParent, userId, conditions

### Reference Tables

Table Name	Purpose	Key Columns
user	System users	userid, employeename
youtubelink	Tutorial videos	youtubelinkid, title, url

---

🔑 Key Functions

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

Location: Lines 19-24

Purpose: Display the product addition form for maintenance products

Process Flow:

1. Display header template

2. Load maintenanceproductview/add.html template

3. Set maintenance flag to 1

4. Display footer template

Template: maintenanceproductview/add.html

---

2. show - Display Product Grid

Location: Lines 24-32

Purpose: Show maintenance products 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 maintenanceproductview/show.html template

5. Set maintenance flag to 1

Security: Includes authentication check

---

3. edit - Edit Product Form

Location: Lines 32-43

Purpose: Display product 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 product ID and deletion flag from GET parameters

3. Load product data from database using parameterized query

4. Assign product data and deletion flag to template

5. Display maintenanceproductview/edit.html template

SQL Query:

SELECT product.* FROM `product` WHERE productId = ?

---

4. saveproductcat() - Save Product Category

Location: Lines 54-81

Purpose: Handle product category creation and updates

Function Signature:

function saveproductcat()

Input Parameters:

• productCatId - Category ID (for updates)
• productCatName - Category name
• productCatParent - Parent category ID (for hierarchy)
• selectitr - Form iteration selector

Process Flow:

1. Get current date and user session data

2. Filter and sanitize input parameters

3. Set parent to 0 if not specified (root level)

4. INSERT (New Category):

   INSERT INTO `productcat`(`productCatName`, `productCatDate`, `productCatParent`, `userId`, `conditions`) 
   VALUES ('$productCatName','$date', $productCatParent, $userid, 0)
   ```
5. **UPDATE** (Existing Category):
   ```sql
   UPDATE `productcat` SET `productCatName`='$productCatName', `productCatParent`= $productCatParent 
   WHERE productCatId = $productCatId
   ```
6. Return JSON response with category data

**Hierarchical Support**:
- `productCatParent = 0` - Root level category
- `productCatParent > 0` - Sub-category

**Return Format**:

json

{

"id": 123,

"selectname": "productCatId",

"selectid": "productCatId1",

"text": "Category Name",

"selectitr": 1

}

---

### 5. **savedata()** - Save/Update Product
**Location**: Lines 84-112  
**Purpose**: Handle product creation and updates

**Function Signature**:

php

function savedata()

**Input Parameters**:
- `productId` - Product ID (for updates)
- `productName` - Product name
- `productDescription` - Product description
- `productcat` - Product category ID
- `productDate` - Product date
- `runquery` - Custom query execution field
- `selectitr` - Form iteration selector

**Process Flow**:
1. Get current date and user session data
2. Filter and sanitize all input parameters
3. **INSERT** (New Product):
   ```sql
   INSERT INTO `product`(`productName`, `productDescription`, `productCatId`, `productDate`, `conditions`, `userId`,`runquery`) 
   VALUES ('$productName','$productDescription','$productCatId','$date', 0 ,'$userid','$runquery')
   ```
4. **UPDATE** (Existing Product):
   ```sql
   UPDATE `product` SET `productName`='$productName',`productDescription`='$productDescription',`productCatId`='$productCatId',`runquery`= '$runquery' 
   WHERE productId = $productId
   ```
5. Return JSON response with product data

**Special Features**:
- **runquery** field allows custom SQL query execution for specialized products
- **conditions = 0** for active products (soft delete support)
- Automatic date stamping for creation

---

### 6. **showajax()** - AJAX Data Grid
**Location**: Lines 115-203  
**Purpose**: Provide server-side DataTables processing for product grid

**Function Signature**:

php

function showajax()

**Filter Parameters**:
- `start_date` / `end_date` - Product creation date range
- `del` - Deletion status filter
- `data1` - Product ID filter
- `data3` - Product category filter

**Process Flow**:
1. Define column mapping for DataTables
2. Build dynamic WHERE clause based on filters
3. Apply soft delete filter (`conditions = 0`)
4. Handle multi-column search functionality
5. Execute main query with user and category JOINs
6. Format each row with action buttons
7. Return JSON response for DataTables

**Main Query Structure**:

sql

SELECT product.* ,employeename ,productCatName FROM product

LEFT JOIN user ON product.userid = user.userid

LEFT JOIN productcat ON product.productCatId = productcat.productCatId

WHERE 1 [ADDITIONAL FILTERS]

**Search Fields**:
- Product ID
- Product name
- Product date
- Product category name
- Employee name

**Column Mapping**:

php

$columns = array('productId', 'productName', 'productDate', 'productCatName', 'employeename', 'productId', 'productId');

**Date Range Filtering**:

php

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

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

}

**Action Buttons**:

php

// For active products

if($row["conditions"] == 0){

$sub_array[] = '';

}else{

// For deleted products

$sub_array[] = 'تفاصيل';

$sub_array[] = 'محذوف ';

}

---

## 🔄 Workflows

### Workflow 1: Product Category Management

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

│ START: Manage Categories │

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

│

▼

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

│ 1. Create Category Hierarchy │

│ - Define root categories (parent = 0) │

│ - Create sub-categories (parent = categoryId) │

│ - Set category names and descriptions │

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

│

▼

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

│ 2. Save Category (saveproductcat) │

│ - Validate category name │

│ - Set parent relationship │

│ - Insert/update productcat table │

│ - Return category data for form updates │

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

│

▼

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

│ 3. Category Available for Products │

│ - Appears in product category dropdown │

│ - Maintains hierarchical structure │

│ - Used for product organization │

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

---

### Workflow 2: Product Management Process

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

│ START: Add/Edit Product │

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

│

▼

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

│ 1. Product Form Entry │

│ - Enter product name and description │

│ - Select product category │

│ - Set custom query (if needed) │

│ - Specify product date │

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

│

▼

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

│ 2. Save Product (savedata) │

│ - Validate required fields │

│ - Associate with category │

│ - Set creation metadata │

│ - Store custom query if specified │

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

│

▼

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

│ 3. Product Integration │

│ - Available for maintenance operations │

│ - Searchable in product grid │

│ - Categorized for organization │

│ - Ready for service workflows │

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

---

### Workflow 3: Product Search and Management

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

│ START: Browse Products │

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

│

▼

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

│ 1. Load Product Grid │

│ - Display maintenanceproductview/show.html │

│ - Initialize DataTables with AJAX │

│ - Load filter options │

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

│

▼

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

│ 2. Apply Filters and Search │

│ - Filter by category │

│ - Date range filtering │

│ - Search across name, description │

│ - Sort by various columns │

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

│

▼

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

│ 3. AJAX Data Loading (showajax) │

│ - Process filter parameters │

│ - Execute JOIN query with categories │

│ - Apply pagination and sorting │

│ - Return formatted data │

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

│

▼

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

│ 4. Display Results │

│ - Show product information │

│ - Display category associations │

│ - Provide edit/delete actions │

│ - Enable further filtering │

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

---

## 🌐 URL Routes & Actions

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

### Required Parameters by Action

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

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

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

**Save Product** (`do=savedata`):
- `productName` - Product name (required)
- `productDescription` - Product description
- `productcat` - Product category ID
- `productDate` - Product creation date
- `runquery` - Custom query field
- `productId` - For updates only
- `selectitr` - Form iteration selector

**Save Category** (`do=saveproductcat`):
- `productCatName` - Category name (required)
- `productCatParent` - Parent category ID (optional, defaults to 0)
- `productCatId` - For updates only
- `selectitr` - Form iteration selector

**AJAX Data** (`do=showajax`):
- DataTables parameters for pagination and sorting
- Optional filters: `data1` (product ID), `data3` (category)
- Date range: `start_date`, `end_date`
- Search terms in `search[value]`

---

## 🧮 Calculation Methods

### No Complex Financial Calculations
This controller focuses on data management rather than calculations. Key processing includes:

### 1. **Hierarchical Category Structure**

php

// Root level categories

if (!$productCatParent) {

$productCatParent = 0; // Set as root level

}

// Category relationship

INSERT INTO productcat(..., productCatParent, ...) VALUES (..., $productCatParent, ...)

### 2. **Date Processing**

php

// Current date for new records

$today = date("Y-m-d H:i:s");

$date = date('Y-m-d');

// Date range filtering

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

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

}

### 3. **Search Query Building**

php

// Dynamic WHERE clause construction

$searchQuery = " ";

if($data1 != ''){

$searchQuery .= " and product.productId = ".$data1. " ";

}

if($del == ''){

$searchQuery .= " and product.conditions = 0 ";

}

if($data3 != ''){

$searchQuery .= " and product.productCatId = ".$data3. " ";

}

---

## 🔒 Security & Permissions

### Authentication Requirements
- **show** and **edit** actions require authentication via `../public/authentication.php`
- **savedata** and **saveproductcat** should include authentication checks

### Input Sanitization

php

// All inputs properly filtered

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

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

$productCatId = filter_input(INPUT_POST, 'productcat');

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

### SQL Injection Prevention
- **SECURITY ISSUE**: Direct string concatenation in INSERT/UPDATE queries

**Vulnerable Code**:

php

// ⚠️ POTENTIAL SQL INJECTION RISK

R::exec("INSERT INTO product(productName, productDescription, productCatId, productDate, conditions, userId,runquery) VALUES ('$productName','$productDescription','$productCatId','$date', 0 ,'$userid','$runquery')");

**Recommended Fix**:

php

// ✅ SECURE VERSION

R::exec("INSERT INTO product(productName, productDescription, productCatId, productDate, conditions, userId,runquery) VALUES (?, ?, ?, ?, 0, ?, ?)", [$productName, $productDescription, $productCatId, $date, $userid, $runquery]);

### Custom Query Security Risk
The `runquery` field allows custom SQL execution, which could be a significant security vulnerability if not properly controlled.

### Session Security

php

$userid = $_SESSION['userid'];

$today = date("Y-m-d H:i:s");

$date = date('Y-m-d');

---

## 📊 Performance Considerations

### Database Optimization
1. **Critical Indexes**:
   - `product(productId)` - Primary key
   - `product(productCatId)` - Category foreign key
   - `product(conditions)` - Soft delete filtering
   - `product(productDate)` - Date range queries
   - `productcat(productCatId)` - Primary key
   - `productcat(productCatParent)` - Hierarchy queries

2. **Query Performance**:
   - LEFT JOINs are efficient for display data
   - Category hierarchy queries could become complex
   - Date range filtering is properly implemented

### Potential Performance Issues
- **Deep Category Hierarchies**: Could require recursive queries
- **Large Product Catalogs**: Need proper pagination
- **Text Searches**: LIKE operations on product names/descriptions
- **Custom Queries**: `runquery` field execution could be resource-intensive

---

## 🐛 Common Issues & Troubleshooting

### 1. **Category Hierarchy Problems**
**Issue**: Circular references or orphaned categories  
**Cause**: Incorrect parent-child relationships

**Debug**:

sql

-- Check for circular references

WITH RECURSIVE cat_hierarchy AS (

SELECT productCatId, productCatName, productCatParent, 1 as level

FROM productcat WHERE productCatParent = 0

UNION ALL

SELECT p.productCatId, p.productCatName, p.productCatParent, ch.level + 1

FROM productcat p

INNER JOIN cat_hierarchy ch ON p.productCatParent = ch.productCatId

WHERE ch.level < 10 -- Prevent infinite recursion

)

SELECT * FROM cat_hierarchy ORDER BY level, productCatName;

### 2. **Product Save Failures**
**Issue**: Products not saving properly  
**Cause**: Missing category references or constraint violations

**Debug**:

sql

-- Check category exists

SELECT * FROM productcat WHERE productCatId = [CATEGORY_ID];

-- Check for constraint violations

SHOW ENGINE INNODB STATUS;

### 3. **Custom Query Issues**
**Issue**: runquery field causing problems  
**Cause**: Invalid SQL or security restrictions

**Fix**:

php

// Validate runquery before execution

if (!empty($runquery)) {

// Add validation logic

$allowed_keywords = ['SELECT', 'UPDATE', 'INSERT'];

$query_upper = strtoupper($runquery);

$is_valid = false;

foreach ($allowed_keywords as $keyword) {

if (strpos($query_upper, $keyword) === 0) {

$is_valid = true;

break;

}

if (!$is_valid) {

$runquery = ''; // Clear invalid query

}

### 4. **DataTable Loading Issues**
**Issue**: Grid shows no data  
**Cause**: AJAX query problems or JOIN failures

**Debug**:

php

// Add to showajax() function

error_log("Product Search Query: " . $searchQuery);

error_log("Product Result Count: " . count($rResult));

---

## 🧪 Testing Scenarios

### Test Case 1: Category Management

1. Create root level category

2. Create sub-categories with proper parent relationships

3. Test category editing and updates

4. Verify hierarchy displays correctly

5. Test category deletion (soft delete)

### Test Case 2: Product CRUD Operations

1. Create product with valid category

2. Test all form fields including description and runquery

3. Edit existing product and verify updates

4. Test product with no category assigned

5. Verify soft delete functionality

### Test Case 3: Product Grid Functionality

1. Load product grid with various products

2. Test search across different fields

3. Apply category filters

4. Test date range filtering

5. Verify sorting and pagination

6. Check action buttons work correctly

### Test Case 4: Security Testing

1. Test SQL injection in product names

2. Test XSS in product descriptions

3. Validate runquery field security

4. Test unauthorized access

5. Verify session handling

```

---

📚 Related Documentation

• CLAUDE.md - PHP 8.2 migration guide
• productController.md - Main product management system
• maintenanceclients.md - Client management
• maintenancereceipts.md - Maintenance workflow
• Database Schema Documentation - Product table relationships

---

Documented By: AI Assistant

Review Status: ✅ Complete

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

Special Features: Hierarchical categories, custom query execution

Next Review: After security fixes and custom query validation

📚 ERP Docs

Maintenanceproducts Documentation

Maintenance Products Controller Documentation

📋 Overview

Primary Functions

Related Controllers

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

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

2. show - Display Product Grid

3. edit - Edit Product Form

4. saveproductcat() - Save Product Category

📚 Related Documentation