# 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
- [x] Manage product categories with hierarchical structure
- [x] Add and edit maintenance-related products
- [x] Product categorization and organization
- [x] Custom query execution for products
- [x] AJAX data grid functionality
- [x] Product search and filtering
- [x] Integration with maintenance workflow

### Related Controllers
- [productController.php](productController.md) - Main product management system
- [productCatController.php](productCatController.md) - Category management
- [maintenanceclients.php](maintenanceclients.md) - Client management
- [maintenancereceipts.php](maintenancereceipts.md) - 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**:
```php
$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**:
```sql
SELECT product.* FROM `product` WHERE productId = ?
```

---

### 4. **saveproductcat()** - Save Product Category
**Location**: Lines 54-81  
**Purpose**: Handle product category creation and updates

**Function Signature**:
```php
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):
   ```sql
   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[] = '<a href="maintenanceproducts.php?do=edit&id='. $row["productId"] .'&del=0" type="button" class="btn btn-default btn-lg editicon"></a>';
    $sub_array[] = '<a href="javascript:;" data-id="'. $row["productId"] .'" data-tableid="productId" data-table="product" type="button" class="btn btn-default btn-lg deleteicon removeteble"></a>';
}else{
    // For deleted products
    $sub_array[] = '<a href="maintenanceproducts.php?do=edit&id='. $row["productId"] .'&del=1" type="button" class="btn btn-default btn-lm">تفاصيل</a>';
    $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](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [productController.md](productController.md) - Main product management system
- [maintenanceclients.md](maintenanceclients.md) - Client management
- [maintenancereceipts.md](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