# Driver Optic Report Controller Documentation

**File**: `/controllers/driveropticreport.php`  
**Purpose**: Manages workshop delivery operations tracking and reporting for optical/manufacturing drivers  
**Last Updated**: December 20, 2024  
**Total Functions**: 4 main functions  
**Lines of Code**: ~386

---

## 📋 Overview

The Driver Optic Report Controller is a specialized tracking and reporting system for workshop delivery operations in the optical manufacturing industry. It manages the complete lifecycle of orders from branch to workshop and back, providing drivers with real-time status tracking and product information.

### Primary Functions
- [x] Track workshop delivery orders with multiple status levels
- [x] Monitor orders at different stages (delivery to workshop, manufacturing, return to branch)
- [x] Filter orders by driver, date range, and order number
- [x] Provide detailed product listings for orders
- [x] Support Arabic interface for Middle East operations
- [x] Generate comprehensive delivery reports
- [x] Track order status transitions and completion

### Related Controllers
- Workshop management system
- Order management system
- Branch operations
- User/driver management

---

## 🗄️ Database Tables

### Primary Tables (Direct Operations)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **toworkshoporder** | Workshop orders | toworkshoporderid, orderNo, branchId, workshopId, driverId, driverIdBack, orderStatus, deliverWorkshopDate, recieveWorkshopDate, deliverBranchDate, recieveBranchDate |
| **toworkshoporderbill** | Order-bill relationships | toworkshoporderbillid, toworkshoporderid, billid |

### Supporting Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **workshop** | Workshop information | workshopid, name |
| **bills** | Service bills | id, clientid, productstotalprice |
| **billsproducts** | Bill line items | billsproductsid, billid, productid, productno, service, deleted |
| **client** | Customer information | clientid, clientname |
| **user** | System users/drivers | userid, employeename |
| **branch** | Company branches | branchid, branchName |

---

## 🔑 Key Functions

### 1. **Default Dashboard** (empty `do`) - Multi-Tab Order Tracking
**Location**: Lines 136-280  
**Purpose**: Main dashboard showing order status across multiple tabs with search functionality

**Process Flow**:
1. Include authentication check
2. Process search parameters for 3 different tabs
3. Query orders by status and driver restrictions
4. Load related data (driver names, branch names, workshop names)
5. Assign data to template for display

**Tab Structure**:

**Tab 1**: Orders in delivery/manufacturing phase
```php
// Orders at workshop (status 0 - delivery to workshop)
$ordersAtWorkshop = $toWorkshopOrderEX->queryByOrderStatusEX(0, $queryString . $driverQueryString);

// Orders at workshop stage 2 (status 2 - return delivery)
$ordersAtWorkshop_2 = $toWorkshopOrderEX->queryByOrderStatusEX(2, $queryString2 . $driverQueryString);
```

**Tab 2**: Completed deliveries
```php
// Delivered to workshop (status 1)
$deliveredToWorkshop = $toWorkshopOrderEX->queryByOrderStatusEX(1, $queryString . $driverQueryString);

// Delivered to branch (status 3)
$deliveredToBranch = $toWorkshopOrderEX->queryByOrderStatusEX(3, $queryString2 . $driverQueryString);
```

**Tab 3**: All orders overview
```php
$allOrders = $toWorkshopOrderEX->queryAllEX($queryString . $driverQueryString);
```

**Driver Restriction Logic**:
```php
$userid = $_SESSION['userid'];
$driverQueryString = ' and (driverIdBack=' . $userid . ' or driverId= ' . $userid . ') ';
```

**Search Parameters**:
- `orderNo` - Order number filter
- `datefrom` - Date range start
- `dateto` - Date range end
- Default: Last 10 days if no dates provided

**Status Enhancement**:
```php
foreach ($allOrders as $data) {
    $data->orderStatus = getStatus($data->orderStatus);
}
```

---

### 2. **Order Products Detail** (`do=orderproducts`) - Product Breakdown
**Location**: Lines 281-329  
**Purpose**: AJAX endpoint providing detailed product information for specific orders

**Process Flow**:
1. Get order ID from POST data
2. Load order and related bills
3. Aggregate product information across all bills
4. Build product hierarchy with category paths
5. Return aggregated product data

**Data Aggregation Logic**:
```php
class productData {
    public $id;
    public $productName;
    public $productNo;
}

// Aggregate products across multiple bills
foreach ($products as $pro) {
    if (in_array($pro->productid, $existId)) {
        // Add to existing product
        $key = array_search($pro->productid, $existId);
        $myproduct = $orderproducts[$key];
    } else {
        // Create new product entry
        $myproduct = new productData();
        $myproduct->id = $pro->productid;
        $pathArr = getProductPath_recursive($pro->service, $categories, 0);
        $myproduct->productName = $pro->deleted . '/' . $pathArr;
        array_push($existId, $pro->productid);
    }
    $myproduct->productNo += $pro->productno;
}
```

**Bill Processing**:
```php
$order = $toWorkshopOrderDAO->load($id);
$orderBillsSimple = $toWorkshopOrderBillDAO->queryByToworkshoporderid($id);
foreach ($orderBillsSimple as $myBill) {
    $bill = $billsDAO->load($myBill->billid);
    $client = $clientDAO->load($bill->clientid);
    $bill->clientname = $client->clientname;
    
    $products = $billsProductsEX->getproducts($bill->id, 0);
}
```

---

### 3. **Status Translation Function** (`getStatus()`)
**Location**: Lines 348-369  
**Purpose**: Convert numeric status codes to Arabic descriptions

**Status Mapping**:
```php
function getStatus($status) {
    $stMsg = "";
    switch ($status) {
        case 0: $stMsg = "تحت التوصيل للورشة"; break;    // Under delivery to workshop
        case 1: $stMsg = "تحت التصنيع فى الورشة"; break;   // Under manufacturing in workshop  
        case 2: $stMsg = "تحت التوصيل للفرع"; break;      // Under delivery to branch
        case 3: $stMsg = "منتهى"; break;                 // Finished
        default: break;
    }
    return $stMsg;
}
```

---

### 4. **Product Path Builder** (`getProductPath_recursive()`)
**Location**: Lines 371-384  
**Purpose**: Build hierarchical product category path using recursive function

**Recursive Logic**:
```php
function getProductPath_recursive($parentid, $categories, $level) {
    global $productCatExt;
    
    $catData = $productCatExt->getCategoryAndParentByCatId($parentid);
    
    if (count($catData) > 0 && $level < 2) {
        $categories .= $catData->productCatName . '/';
        $newParentId = $catData->productCatParent;
        return getProductPath_recursive($newParentId, $categories, ($level + 1));
    }
    
    $categories = substr($categories, 0, strlen($categories) - 1); // Remove trailing slash
    return $categories;
}
```

**Features**:
- Maximum depth of 2 levels to prevent infinite recursion
- Builds category path like "Electronics/Computers/Laptops"
- Handles missing categories gracefully

---

## 🔄 Workflows

### Workflow 1: Order Status Tracking
```
┌─────────────────────────────────────────────────────────────┐
│                START: Order Created                         │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  Status 0: Under Delivery to Workshop                      │
│     - Driver picks up order from branch                     │
│     - Order shows in "Orders at Workshop" tab               │
│     - deliverWorkshopDate set when dispatched              │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  Status 1: Under Manufacturing in Workshop                  │
│     - Order received at workshop                            │
│     - Manufacturing process begins                          │
│     - recieveWorkshopDate recorded                          │
│     - Shows in "Delivered to Workshop" tab                  │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  Status 2: Under Delivery to Branch                        │
│     - Manufacturing completed                               │
│     - Driver picks up finished products                     │
│     - deliverBranchDate set                                │
│     - Shows in "Orders at Workshop 2" tab                   │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  Status 3: Finished                                        │
│     - Order delivered back to branch                        │
│     - recieveBranchDate recorded                            │
│     - Shows in "Delivered to Branch" tab                    │
│     - Process complete                                      │
└─────────────────────────────────────────────────────────────┘
```

---

### Workflow 2: Product Detail Retrieval
```
┌─────────────────────────────────────────────────────────────┐
│            START: View Order Products (AJAX)                │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Load Order Information                                  │
│     - Get order ID from POST                                │
│     - Load order record                                     │
│     - Initialize product aggregation                        │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Process Associated Bills                                │
│     FOR EACH bill linked to order:                         │
│       │                                                     │
│       ├─→ Load bill details                                │
│       ├─→ Load customer information                        │
│       └─→ Query bill products                              │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Aggregate Product Information                           │
│     FOR EACH product in each bill:                         │
│       │                                                     │
│       ├─→ Check if product already processed               │
│       ├─→ If new: Create product entry with category path  │
│       ├─→ If existing: Add to quantity total               │
│       └─→ Build hierarchical category path                 │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Format and Return Data                                  │
│     - Assign aggregated data to template                    │
│     - Display via orderproducts.html                        │
│     - Show product names, quantities, categories            │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=` (empty) | Default dashboard | Multi-tab order tracking interface |
| `do=orderproducts` | Product detail AJAX | Return product breakdown for order |
| `do=sucess` | Success page | Generic success message |
| `do=error` | Error page | Generic error message |

### Required Parameters by Action

**Default Dashboard** (empty `do`):
- Search filters (all optional):
  - `orderNo` - Filter by order number
  - `datefrom` - Date range start 
  - `dateto` - Date range end
  - Multiple sets for different tabs (orderNo2, datefrom2, etc.)

**Order Products** (`do=orderproducts`):
- `id` - Order ID (POST parameter)

---

## 🔒 Security & Permissions

### Authentication Requirements
```php
include_once ("../public/authentication.php");
```

### Driver Access Restrictions
```php
// Users can only see orders where they are assigned as driver
$userid = $_SESSION['userid'];
$driverQueryString = ' and (driverIdBack=' . $userid . ' or driverId= ' . $userid . ') ';
```

**Permission Levels**:
- Users can only view orders assigned to them as driver
- Either as main driver (`driverId`) or return driver (`driverIdBack`)
- No access to orders handled by other drivers

### Input Sanitization
```php
// Search parameters filtered
$orderNo = filter_input(INPUT_POST, "orderNo");
$datefrom = filter_input(INPUT_POST, "datefrom");  
$dateto = filter_input(INPUT_POST, "dateto");
$id = filter_input(INPUT_POST, "id");
```

---

## 📊 Performance Considerations

### Database Optimization Tips
1. **Indexes Required**:
   - `toworkshoporder(driverId, orderStatus)`
   - `toworkshoporder(driverIdBack, orderStatus)`
   - `toworkshoporder(deliverWorkshopDate)`
   - `toworkshoporder(orderNo)`
   - `toworkshoporderbill(toworkshoporderid)`

2. **Query Performance**:
   - Default 10-day date range prevents large result sets
   - Status-based filtering reduces query load
   - Driver restrictions limit data exposure

3. **AJAX Performance**:
   - Product detail loading only on demand
   - No automatic loading of all order details
   - Efficient aggregation of duplicate products

### Known Performance Issues
```sql
-- This query may be slow without proper indexing:
SELECT * FROM toworkshoporder 
WHERE (driverIdBack=? OR driverId=?) 
AND orderStatus=? 
AND date(deliverWorkshopDate) >= ?;

-- Optimize with composite indexes:
CREATE INDEX idx_driver_status_date ON toworkshoporder(driverId, orderStatus, deliverWorkshopDate);
CREATE INDEX idx_driver_back_status_date ON toworkshoporder(driverIdBack, orderStatus, deliverWorkshopDate);
```

---

## 🐛 Common Issues & Troubleshooting

### 1. **Missing Driver Data**
**Issue**: Driver names showing as IDs instead of names  
**Cause**: User record not found or deleted

**Debug**:
```sql
-- Check for missing user records
SELECT DISTINCT driverId FROM toworkshoporder 
WHERE driverId NOT IN (SELECT userid FROM user);
```

**Fix**:
```php
// Add null checking in driver loading
$driver = $userDAO->load($data->driverId);
$data->driverId = $driver ? $driver->employeename : 'Unknown Driver';
```

### 2. **Incorrect Status Display**
**Issue**: Status showing in English or as numbers  
**Cause**: getStatus() function not called properly

**Debug**:
```php
// Check status values in database
SELECT orderStatus, COUNT(*) FROM toworkshoporder GROUP BY orderStatus;
```

### 3. **Empty Product Lists**
**Issue**: Order products not showing  
**Cause**: Missing bill relationships or product data

**Debug**:
```sql
-- Check order-bill relationships
SELECT COUNT(*) FROM toworkshoporderbill WHERE toworkshoporderid = ?;

-- Check bill products
SELECT COUNT(*) FROM billsproducts bp 
JOIN toworkshoporderbill tob ON bp.billid = tob.billid 
WHERE tob.toworkshoporderid = ?;
```

### 4. **Date Range Issues**
**Issue**: Orders not appearing in date filters  
**Cause**: Date format mismatches or null date fields

**Debug**:
```sql
-- Check date formats and nulls
SELECT orderNo, deliverWorkshopDate, recieveWorkshopDate 
FROM toworkshoporder 
WHERE deliverWorkshopDate IS NULL OR deliverWorkshopDate = '0000-00-00';
```

---

## 🧪 Testing Scenarios

### Test Case 1: Order Status Progression
```
1. Create test order with status 0
2. Verify appears in "Orders at Workshop" tab
3. Update status to 1
4. Verify moves to "Delivered to Workshop" tab
5. Continue through all status levels
6. Verify proper Arabic status messages
```

### Test Case 2: Driver Access Control
```
1. Login as Driver A
2. Verify only sees orders assigned to Driver A
3. Login as Driver B  
4. Verify different set of orders
5. Test both driverId and driverIdBack assignments
```

### Test Case 3: Product Aggregation
```
1. Create order with multiple bills
2. Add same products to different bills with different quantities
3. Call orderproducts AJAX endpoint
4. Verify quantities properly aggregated
5. Check category path building
```

### Test Case 4: Date Range Filtering
```
1. Create orders across different date ranges
2. Test various date filter combinations
3. Verify default 10-day range works
4. Test edge cases (same day, large ranges)
```

### Debug Mode Enable
```php
// Add at top of controller for debugging
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Debug SQL queries
echo "Query: " . $queryString . "<br>";

// Debug data arrays
echo "<pre>";
print_r($ordersAtWorkshop);
echo "</pre>";
```

---

## 📚 Related Documentation

- [CLAUDE.md](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- Workshop management documentation
- Order management system docs
- Arabic interface guidelines
- Database Schema Documentation

---

**Documented By**: AI Assistant  
**Review Status**: ✅ Complete  
**Next Review**: When major changes occur