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

• ✅ Display delivery order status dashboard with filtering
• ✅ Manage driver assignments via AJAX
• ✅ Track delivery times and calculate duration
• ✅ Provide bulk driver assignment capabilities
• ✅ Support order completion marking
• ✅ Filter orders by clients, drivers, and dates
• ✅ Monitor delivery status progression

Related Controllers

• • orderdeliveryreport.php - Delivery reporting
• • ordersreport.php - General order management
• • restaurantorderController.php - Order processing
• • userController.php - Driver management

---

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

1. Default Action / show - Order Status Dashboard

Location: Lines 105-175

Purpose: Display delivery order status tracking interface

Function Signature:

// 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:

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:

$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:

$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:

// 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:

$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:

// 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:

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:

// 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:

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

---

🔄 Workflows

Workflow 1: Order Status Monitoring

---

Workflow 2: Driver Assignment Process

---

🌐 URL Routes & Actions

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

// 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

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

// Delivery status enumeration
// 0 = Not assigned
// 1 = Assigned to driver (received)
// 2 = Delivered to customer

---

🔒 Security & Permissions

Input Sanitization

$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

// 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:

-- 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:

-- 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:

// 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:

// 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 - PHP 8.2 migration guide
• • orderdeliveryreport.md - Delivery reporting
• • ordersreport.md - General order management
• • Database Schema Documentation - Table relationships

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur