# Order Status Report Controller Documentation

**File**: `/controllers/orderstatusreport.php`  
**Purpose**: Restaurant delivery order status tracking and driver assignment management with real-time status updates  
**Last Updated**: December 20, 2024  
**Total Functions**: 4  
**Lines of Code**: ~215

---

## 📋 Overview

The Order Status Report Controller manages delivery order status tracking and driver assignment operations. It provides comprehensive functionality for:
- Tracking delivery order statuses in real-time
- Managing driver assignments for delivery orders
- Monitoring delivery times and progress
- Bulk driver assignment operations
- Order completion tracking
- Client and driver filtering capabilities

### Primary Functions
- [x] Display delivery order status dashboard with filtering
- [x] Manage driver assignments via AJAX
- [x] Track delivery times and calculate duration
- [x] Provide bulk driver assignment capabilities
- [x] Support order completion marking
- [x] Filter orders by clients, drivers, and dates
- [x] Monitor delivery status progression

### Related Controllers
- [orderdeliveryreport.php](orderdeliveryreport.md) - Delivery reporting
- [ordersreport.php](ordersreport.md) - General order management
- [restaurantorderController.php](#) - Order processing
- [userController.php](#) - Driver management

---

## 🗄️ Database Tables

### Primary Tables (Direct Operations)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **restaurantorderdeliverydetails** | Delivery assignment and status | id, orderId, driverId, driverReciveDate, deliveryStatus |
| **restaurantorder** | Restaurant orders | id, tableId, clientId, finished, del, sysdate |

### Reference Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **client** | Customer information | clientid, clientname |
| **user** | Drivers and system users | userid, employeename, username |
| **usergroup** | User group definitions (drivers) | usergroupid, usergroupname |
| **programsettings** | System configuration | programsettingsid, reportsPlusHours |

---

## 🔑 Key Functions

### 1. **Default Action / show** - Order Status Dashboard
**Location**: Lines 105-175  
**Purpose**: Display delivery order status tracking interface

**Function Signature**:
```php
// Triggered when: empty $do or $do == "show"
```

**Process Flow**:
1. Parse filtering parameters from POST
2. Apply default date filtering with business hours
3. Build dynamic query with multiple filters
4. Execute query to get delivery orders
5. Calculate delivery time durations
6. Load reference data (drivers, clients)
7. Display results via template

**Key Features**:

#### Default Date Processing:
```php
if (empty($datefrom) && empty($dateto) && empty($sellBillId) && empty($orderId) && empty($driver) && empty($clientId)) {
    $Programsetting = $ProgramsettingDAO->load(1);
    $datefrom = $dateto = date('Y-m-d');
    if (isset($Programsetting->reportsPlusHours) && !empty($Programsetting->reportsPlusHours)) {
        $reportsPlusHours = $Programsetting->reportsPlusHours + 24;
        $dateto = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour', strtotime($dateto)));
        $datefrom = date('Y-m-d H:i:s', strtotime('-' . $Programsetting->reportsPlusHours . ' hour', strtotime($datefrom)));
    } else {
        $dateto = $dateto . ' 23:59:59';
        $datefrom = $datefrom . " 00:00:00";
    }
}
```

#### Multi-Criteria Filtering:
```php
$queryString = " where 1 ";

// Order ID filtering
if (isset($orderId) && !empty($orderId) && $orderId != -1) {
    $queryString .= 'and restaurantorder.id = ' . $orderId . ' ';
}

// Sales Bill ID filtering
if (isset($sellBillId) && !empty($sellBillId) && $sellBillId != -1) {
    $queryString .= 'and restaurantorder.sellBillId = ' . $sellBillId . ' ';
}

// Date range filtering
if (isset($datefrom) && !empty($datefrom)) {
    $queryString .= 'and restaurantorder.sysdate >= "' . $datefrom . '" ';
}
if (isset($dateto) && !empty($dateto)) {
    $queryString .= 'and restaurantorder.sysdate <= "' . $dateto . '" ';
}

// Driver filtering
if (isset($driver) && !empty($driver)) {
    $queryString .= 'and restaurantorderdeliverydetails.driverId = ' . $driver . ' ';
}

// Client filtering
if (isset($clientId) && !empty($clientId)) {
    $queryString .= 'and client.clientid = ' . $clientId . ' ';
}

// Delivery orders only, finished and not deleted
$queryString .= ' and restaurantorder.finished=1 and restaurantorder.tableId =-2 and restaurantorder.del = 0';
```

#### Delivery Time Calculation:
```php
$now = new DateTime(date('Y-m-d H:i:s'));
foreach ($ordersData as $value) {
    if ($value->deliveryStatus == 1) { // Order received by driver
        $recDate = new DateTime($value->driverReciveDate);
        $interval = $now->diff($recDate);
        $value->diff = $interval->format('%h') . " ساعة و " . $interval->format('%i') . " دقيقة";
    }
}
```

---

### 2. **changeDriver** - AJAX Driver Assignment
**Location**: Lines 176-183  
**Purpose**: Update driver assignment and delivery status via AJAX

**Function Signature**:
```php
// Triggered when: $do == "changeDriver"
```

**Process Flow**:
1. Extract order ID, driver ID, and status from POST
2. Update delivery details record directly
3. No response handling (silent update)

**Implementation**:
```php
$orderId = filter_input(INPUT_POST, 'orderid');
$driver = filter_input(INPUT_POST, 'driver');
$status = filter_input(INPUT_POST, 'status');

R::exec("UPDATE restaurantorderdeliverydetails 
         SET `driverId`= '".$driver."', `deliveryStatus`= '".$status."' 
         WHERE orderId = '" . $orderId . "'");
```

---

### 3. **saveDriver** - Bulk Driver Assignment
**Location**: Lines 184-200  
**Purpose**: Process multiple driver assignments from form submission

**Function Signature**:
```php
// Triggered when: $do == "saveDriver"
```

**Process Flow**:
1. Iterate through POST data for driver assignments
2. Process fields matching pattern `driver_{orderid}`
3. Update delivery records for valid assignments
4. Set delivery status and receive date
5. Redirect to status dashboard

**Implementation**:
```php
foreach ($_POST as $key => $value) {
    // Read POST data starting with driver_
    if (strpos($key, 'driver_') === 0) {
        $orderId = explode("_", $key)[1];
        $driver = (int) $_POST["driver_" . $orderId];
        if ($orderId > 0 && $driver > 0) {
            $restaurantOrderDeliveryDetail = $restaurantOrderDeliveryDetailDAO->queryByOrderId($orderId);
            $deliveryData = clone $restaurantOrderDeliveryDetail[0];
            $deliveryData->driverId = $driver;
            $deliveryData->driverReciveDate = date('Y-m-d H:i:s');
            $deliveryData->deliveryStatus = 1; // Received by driver
            $restaurantOrderDeliveryDetailDAO->update($deliveryData);
        }
    }
}
header("location:?do=show");
```

---

### 4. **markAsDelivered** - Bulk Completion Marking
**Location**: Lines 201-208  
**Purpose**: Mark multiple orders as delivered

**Function Signature**:
```php
// Triggered when: $do == "markAsDelivered"
```

**Process Flow**:
1. Process selected order IDs from POST array
2. Mark each order as delivered via DAO
3. Redirect to status dashboard

**Implementation**:
```php
foreach ($_POST["choosedItem"] as $orderId) {
    if ((int) $orderId > 0) {
        $restaurantOrderDeliveryDetailEX->markOrderAsDelivered($orderId);
    }
}
header("location:?do=show");
```

---

## 🔄 Workflows

### Workflow 1: Order Status Monitoring
```
┌─────────────────────────────────────────────────────────────┐
│              START: Load Order Status Dashboard            │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Parse Filter Parameters                                 │
│     - Extract order ID, sales bill ID                      │
│     - Get date range parameters                             │
│     - Parse driver and client filters                      │
│     - Validate parameter formats                           │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Apply Default Date Filtering                           │
│     - Check if any filters provided                        │
│     - If no filters, default to today                      │
│     - Apply business hours from program settings           │
│     - Handle timezone adjustments                          │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Build Delivery Query                                    │
│     - Filter for delivery orders only (tableId = -2)       │
│     - Include finished orders only                         │
│     - Exclude deleted orders                               │
│     - Apply all search criteria                            │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Execute Query & Process Results                        │
│     - Get delivery orders with details                     │
│     - Calculate delivery time durations                    │
│     - Format time differences for display                  │
│     - Enrich data with status information                  │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Load Reference Data                                     │
│     - Get list of available drivers                        │
│     - Load client information                              │
│     - Prepare dropdown data                                │
│     - Assign all data to template                          │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  6. Display Status Dashboard                               │
│     - Show order status grid                               │
│     - Display driver assignment controls                   │
│     - Show delivery time tracking                          │
│     - Enable bulk operations                               │
└─────────────────────────────────────────────────────────────┘
```

---

### Workflow 2: Driver Assignment Process
```
┌─────────────────────────────────────────────────────────────┐
│              START: Driver Assignment Request              │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Process Assignment Type                                 │
│     ├─ Individual Assignment (changeDriver)                │
│     │   ├─→ Extract single order and driver                │
│     │   ├─→ Update via direct SQL                          │
│     │   └─→ Return immediately                             │
│     │                                                      │
│     └─ Bulk Assignment (saveDriver)                        │
│         ├─→ Parse form POST data                           │
│         ├─→ Identify driver assignment fields              │
│         └─→ Process multiple assignments                   │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Validate Assignment Data                               │
│     - Check order ID is valid                              │
│     - Verify driver ID exists                              │
│     - Ensure driver is in correct user group               │
│     - Validate assignment permissions                      │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Update Delivery Records                                │
│     FOR EACH assignment:                                    │
│       ├─→ Load existing delivery detail record             │
│       ├─→ Clone record for modification                    │
│       ├─→ Set driver ID and assignment time                │
│       ├─→ Update delivery status to "Assigned"            │
│       └─→ Save updated record                              │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Complete Assignment Process                            │
│     - Verify all updates successful                        │
│     - Log assignment activities                            │
│     - Redirect to status dashboard                         │
│     - Display updated assignments                          │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=` (empty) or `do=show` | Default action | Order status dashboard |
| `do=changeDriver` | AJAX driver update | Individual driver assignment |
| `do=saveDriver` | Bulk assignment | Multiple driver assignments |
| `do=markAsDelivered` | Bulk completion | Mark orders as delivered |

### Required Parameters by Action

**Status Dashboard** (`do=show` or empty):
- `orderId` - Specific order ID (POST, optional)
- `sellBillId` - Sales bill ID (POST, optional)
- `datefrom` - Start date (POST, optional)
- `dateto` - End date (POST, optional)
- `driver` - Driver filter (POST, optional)
- `clientId` - Client filter (POST, optional)

**Individual Driver Assignment** (`do=changeDriver`):
- `orderid` - Order ID (POST, required)
- `driver` - Driver ID (POST, required)
- `status` - Delivery status (POST, required)

**Bulk Driver Assignment** (`do=saveDriver`):
- `driver_{orderid}` - Driver ID for order (POST, multiple)

**Bulk Completion** (`do=markAsDelivered`):
- `choosedItem[]` - Array of order IDs (POST, required)

---

## 🧮 Calculation Methods

### Delivery Time Duration
```php
// Calculate time elapsed since driver received order
$now = new DateTime(date('Y-m-d H:i:s'));
foreach ($ordersData as $value) {
    if ($value->deliveryStatus == 1) {
        $recDate = new DateTime($value->driverReciveDate);
        $interval = $now->diff($recDate);
        $value->diff = $interval->format('%h') . " ساعة و " . $interval->format('%i') . " دقيقة";
    }
}
```

### Business Hours Date Processing
```php
if (isset($Programsetting->reportsPlusHours) && !empty($Programsetting->reportsPlusHours)) {
    $reportsPlusHours = $Programsetting->reportsPlusHours + 24;
    $dateto = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour', strtotime($dateto)));
    $datefrom = date('Y-m-d H:i:s', strtotime('-' . $Programsetting->reportsPlusHours . ' hour', strtotime($datefrom)));
}
```

### Delivery Status Values
```php
// Delivery status enumeration
// 0 = Not assigned
// 1 = Assigned to driver (received)
// 2 = Delivered to customer
```

---

## 🔒 Security & Permissions

### Input Sanitization
```php
$orderId = filter_input(INPUT_POST, 'orderId');
$sellBillId = filter_input(INPUT_POST, 'sellBillId');
$driver = (int) filter_input(INPUT_POST, 'driver');
$clientId = (int) filter_input(INPUT_POST, 'clientId');
```

### SQL Injection Prevention
```php
// Direct SQL execution (potential security risk)
R::exec("UPDATE restaurantorderdeliverydetails 
         SET `driverId`= '".$driver."', `deliveryStatus`= '".$status."' 
         WHERE orderId = '" . $orderId . "'");

// Recommended: Use parameterized queries
$stmt = $pdo->prepare("UPDATE restaurantorderdeliverydetails 
                       SET driverId = ?, deliveryStatus = ? 
                       WHERE orderId = ?");
$stmt->execute([$driver, $status, $orderId]);
```

### Data Access Control
- Only shows delivery orders (tableId = -2)
- Filters for finished orders only
- Excludes deleted orders (del = 0)
- Driver group validation through user system

---

## 📊 Performance Considerations

### Database Optimization Tips
1. **Required Indexes**:
   - `restaurantorderdeliverydetails(orderId, driverId, deliveryStatus)`
   - `restaurantorder(tableId, finished, del, sysdate)`
   - `restaurantorder(sellBillId)`
   - `client(clientid)`
   - `user(usergroupid)`

2. **Query Optimization**:
   - Efficient filtering with proper WHERE clauses
   - Minimize N+1 queries for driver/client data
   - Use appropriate date range filtering

3. **Memory Management**:
   - Process bulk operations efficiently
   - Limit date ranges for large datasets
   - Optimize driver dropdown loading

---

## 🐛 Common Issues & Troubleshooting

### 1. **No Orders Showing in Status Dashboard**
**Issue**: Status dashboard shows no delivery orders  
**Cause**: Incorrect filtering or missing delivery assignments

**Debug**:
```sql
-- Check for delivery orders
SELECT COUNT(*) FROM restaurantorder 
WHERE tableId = -2 AND finished = 1 AND del = 0;

-- Check delivery detail records
SELECT COUNT(*) FROM restaurantorderdeliverydetails rod
JOIN restaurantorder ro ON rod.orderId = ro.id
WHERE ro.tableId = -2;
```

### 2. **Driver Assignment Not Saving**
**Issue**: Driver assignments not updating correctly  
**Cause**: Missing delivery detail records or validation failures

**Debug**:
```sql
-- Check if delivery detail record exists
SELECT * FROM restaurantorderdeliverydetails WHERE orderId = [ORDER_ID];

-- Verify driver exists in correct group
SELECT u.*, ug.usergroupname FROM user u
JOIN usergroup ug ON u.usergroupid = ug.usergroupid
WHERE u.userid = [DRIVER_ID] AND ug.usergroupname = 'سائقين';
```

### 3. **Time Calculations Incorrect**
**Issue**: Delivery time durations showing wrong values  
**Cause**: Timezone issues or date format problems

**Fix**:
```php
// Ensure proper timezone setting
date_default_timezone_set('Asia/Baghdad'); // or appropriate timezone

// Debug date calculations
echo "Current time: " . date('Y-m-d H:i:s');
echo "Driver receive date: " . $value->driverReciveDate;
echo "Calculated diff: " . $value->diff;
```

### 4. **Bulk Operations Not Working**
**Issue**: Bulk driver assignment or completion marking fails  
**Cause**: Form data parsing issues or permission problems

**Debug**:
```php
// Debug POST data processing
foreach ($_POST as $key => $value) {
    if (strpos($key, 'driver_') === 0) {
        echo "Found assignment: $key = $value\n";
    }
}

// Check choosedItem array
if (isset($_POST["choosedItem"])) {
    print_r($_POST["choosedItem"]);
}
```

---

## 🧪 Testing Scenarios

### Test Case 1: Basic Status Monitoring
```
1. Create delivery orders with different statuses:
   - Unassigned orders
   - Orders assigned to drivers
   - Completed deliveries
2. Load status dashboard
3. Verify all orders appear correctly
4. Check time calculations for assigned orders
5. Test filtering by driver and client
```

### Test Case 2: Individual Driver Assignment
```
1. Load order status dashboard
2. Use AJAX changeDriver action
3. Verify database updates correctly
4. Check status changes reflected immediately
5. Test with different drivers and statuses
```

### Test Case 3: Bulk Operations
```
1. Create multiple unassigned delivery orders
2. Use bulk driver assignment form
3. Assign different drivers to different orders
4. Submit form and verify assignments
5. Test bulk completion marking
6. Verify all operations succeed
```

### Test Case 4: Time Tracking
```
1. Assign orders to drivers at known times
2. Wait for measurable time period
3. Load status dashboard
4. Verify time calculations are accurate
5. Test with different time zones
6. Check Arabic time format display
```

---

## 📚 Related Documentation

- [CLAUDE.md](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [orderdeliveryreport.md](orderdeliveryreport.md) - Delivery reporting
- [ordersreport.md](ordersreport.md) - General order management
- [Database Schema Documentation](#) - Table relationships

---

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