# Order Delivery Report Controller Documentation

**File**: `/controllers/orderdeliveryreport.php`  
**Purpose**: Restaurant delivery tracking and reporting system with driver performance analysis  
**Last Updated**: December 20, 2024  
**Total Functions**: 1  
**Lines of Code**: ~180

---

## 📋 Overview

The Order Delivery Report Controller manages delivery reporting for restaurant operations. It provides functionality to:
- Track delivery orders assigned to drivers
- Generate driver performance reports
- Monitor delivery times and statistics
- Print delivery summaries for accounting
- Filter deliveries by date ranges and drivers
- Calculate driver earnings and order counts

### Primary Functions
- [x] Display driver delivery reports with filtering
- [x] Track orders assigned to specific drivers
- [x] Calculate delivery statistics and totals
- [x] Generate printable delivery reports
- [x] Monitor driver performance metrics
- [x] Filter deliveries by date and driver

### Related Controllers
- [ordersreport.php](ordersreport.md) - Restaurant order management
- [orderstatusreport.php](orderstatusreport.md) - Order status tracking
- [restaurantorderController.php](#) - Restaurant order processing
- [userController.php](#) - Driver/user management

---

## 🗄️ Database Tables

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

### Reference Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **user** | Drivers and system users | userid, username, employeename, usergroupid |
| **usergroup** | User group definitions | usergroupid, usergroupname |
| **programsettings** | System configuration | programsettingsid, reportsPlusHours |

---

## 🔑 Key Functions

### 1. **Default Action / show** - Delivery Report Dashboard
**Location**: Lines 101-148  
**Purpose**: Display delivery reports with driver filtering and date ranges

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

**Process Flow**:
1. Load drivers from "سائقين" (Drivers) user group
2. Parse search parameters (driver, date range)
3. Apply default date filtering if no parameters provided
4. Build dynamic query with filters
5. Execute query to get delivery data
6. Display results via template

**Key Features**:

#### Driver Loading:
```php
$drivers = $userEX->queryWithGroupName("سائقين");
$smarty->assign("drivers", $drivers);
```

#### Date Processing with Program Settings:
```php
if (empty($datefrom) && empty($dateto) && empty($sellBillId) && empty($orderId)) {
    $Programsetting = $ProgramsettingDAO->load(1);
    $datefrom = $dateto = date('Y-m-d');
    if (isset($Programsetting->reportsPlusHours) && !empty($Programsetting->reportsPlusHours)) {
        $reportsPlusHours = $Programsetting->reportsPlusHours + 24; // 24 to get end of day
        $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";
    }
}
```

#### Dynamic Query Building:
```php
$queryString = " where 1 ";

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

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

// Delivery orders only (tableId = -2 indicates delivery)
$queryString .= ' and restaurantorder.tableId = -2';
```

---

### 2. **print** - Delivery Report Printing
**Location**: Lines 149-174  
**Purpose**: Generate printable delivery reports for specific drivers

**Function Signature**:
```php
// Triggered when: $do == "print"
// Parameters: id (driver ID), datefrom, dateto
```

**Process Flow**:
1. Extract driver ID and date range from parameters
2. Load program settings and driver information
3. Calculate delivery statistics
4. Generate printable report template

**Report Statistics Calculated**:
```php
// Count total orders for driver in date range
$countorder = R::count("restaurantorderdeliverydetails", "driverId = $id $queryString");

// Calculate totals for driver deliveries
$totals = R::getRow("SELECT 
    sum(totalBill) as totalBills, 
    sum(tax) as taxs,
    sum(netBill) as netBills 
FROM restaurantorderdeliverydetails 
left join restaurantorder ON restaurantorderdeliverydetails.orderId = restaurantorder.id   
WHERE driverId = $id $queryString");
```

**Template Variables**:
- `$programsettings` - System configuration
- `$userdata` - Driver information
- `$countorder` - Total delivery count
- `$totals` - Financial totals for deliveries
- `$datefrom` / `$dateto` - Report date range

---

## 🔄 Workflows

### Workflow 1: Delivery Report Generation
```
┌─────────────────────────────────────────────────────────────┐
│              START: Delivery Report Request               │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Load Driver Data                                        │
│     - Query users in "سائقين" (Drivers) group              │
│     - Assign to template for dropdown selection            │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Process Filter Parameters                               │
│     - Extract driver ID from request                       │
│     - Parse date range parameters                           │
│     - Apply default date (today) if no dates provided      │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Apply Program Settings                                  │
│     - Check for reportsPlusHours setting                   │
│     - Adjust date ranges for business hours                │
│     - Handle timezone considerations                        │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Build Delivery Query                                    │
│     - Filter by driverReciveDate range                     │
│     - Filter by specific driver ID                         │
│     - Include only delivery orders (tableId = -2)          │
│     - Join with restaurant order details                   │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Execute Query & Display Results                        │
│     - Run query to get delivery data                       │
│     - Assign results to template                           │
│     - Display via orderdeliveryreport/show.html            │
└─────────────────────────────────────────────────────────────┘
```

---

### Workflow 2: Printable Report Generation
```
┌─────────────────────────────────────────────────────────────┐
│              START: Print Report Request                   │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Parse Print Parameters                                  │
│     - Extract driver ID from URL                           │
│     - Get date range for report                            │
│     - Build query string for filtering                     │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Load Reference Data                                     │
│     - Get program settings for report header               │
│     - Load driver information by ID                        │
│     - Prepare report metadata                              │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Calculate Delivery Statistics                          │
│     - Count total deliveries for driver                    │
│     - Sum totalBill amounts                                │
│     - Sum tax amounts                                      │
│     - Sum netBill amounts                                  │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Generate Print Template                                │
│     - Assign all data to template variables                │
│     - Display via orderdeliveryreport/print.html           │
│     - Format for printer-friendly output                   │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=` (empty) or `do=show` | Default action | Delivery report dashboard |
| `do=print` | Print action | Generate printable delivery report |

### Required Parameters by Action

**Delivery Report** (`do=show` or empty):
- `driver` - Driver ID (POST, optional)
- `datefrom` - Start date (POST, optional)
- `dateto` - End date (POST, optional)

**Print Report** (`do=print`):
- `id` - Driver ID (GET, required)
- `datefrom` - Start date (GET, optional)
- `dateto` - End date (GET, optional)

---

## 🧮 Calculation Methods

### Date Range Processing
```php
// Default to today if no dates provided
if (empty($datefrom) && empty($dateto)) {
    $datefrom = $dateto = date('Y-m-d');
}

// Apply business hour adjustments
if (isset($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 Statistics
```php
// Total order count
$countorder = R::count("restaurantorderdeliverydetails", "driverId = $id $queryString");

// Financial totals
$totals = R::getRow("SELECT 
    sum(totalBill) as totalBills,     // Gross order total
    sum(tax) as taxs,                 // Total tax amount  
    sum(netBill) as netBills          // Net bill amount
FROM restaurantorderdeliverydetails 
LEFT JOIN restaurantorder ON restaurantorderdeliverydetails.orderId = restaurantorder.id   
WHERE driverId = $id $queryString");
```

---

## 🔒 Security & Permissions

### Authentication Requirements
- No explicit authentication check in this controller
- Relies on session-based access control from calling system

### Input Sanitization
```php
$driver = (int) filter_input(INPUT_POST, 'driver');
$datefrom = filter_input(INPUT_POST, 'datefrom');
$dateto = filter_input(INPUT_POST, 'dateto');
$id = $_GET['id']; // Should be sanitized
```

### Data Access Control
- Filters drivers by user group membership
- Only shows delivery orders (tableId = -2)
- Date-based access restrictions

---

## 📊 Performance Considerations

### Database Optimization Tips
1. **Required Indexes**:
   - `restaurantorderdeliverydetails(driverId, driverReciveDate)`
   - `restaurantorder(tableId, id)`
   - `user(usergroupid)`
   - `usergroup(usergroupname)`

2. **Query Optimization**:
   ```sql
   -- Efficient delivery query
   SELECT rod.*, ro.* 
   FROM restaurantorderdeliverydetails rod
   LEFT JOIN restaurantorder ro ON rod.orderId = ro.id
   WHERE rod.driverId = ? 
   AND rod.driverReciveDate BETWEEN ? AND ?
   AND ro.tableId = -2;
   ```

3. **Memory Management**:
   - Limit date ranges for large datasets
   - Use appropriate LIMIT clauses for reporting
   - Efficient driver group queries

---

## 🐛 Common Issues & Troubleshooting

### 1. **No Delivery Data Showing**
**Issue**: Report shows no deliveries despite having orders  
**Cause**: Incorrect tableId filtering or missing delivery assignments

**Debug**:
```sql
-- Check if orders are marked as delivery
SELECT COUNT(*) FROM restaurantorder WHERE tableId = -2;

-- Check delivery detail records
SELECT COUNT(*) FROM restaurantorderdeliverydetails;

-- Verify driver assignments
SELECT DISTINCT driverId FROM restaurantorderdeliverydetails;
```

### 2. **Driver List Empty**
**Issue**: No drivers showing in dropdown  
**Cause**: Missing "سائقين" user group or no users assigned

**Fix**:
```sql
-- Check user group exists
SELECT * FROM usergroup WHERE usergroupname = 'سائقين';

-- Check users in driver group
SELECT u.* FROM user u 
JOIN usergroup ug ON u.usergroupid = ug.usergroupid 
WHERE ug.usergroupname = 'سائقين';
```

### 3. **Date Range Issues**
**Issue**: Report not showing orders for specified date range  
**Cause**: Date format mismatch or timezone issues

**Fix**:
```php
// Debug date processing
echo "Original dates: $datefrom - $dateto";
echo "Processed dates: " . $queryString;

// Ensure proper date format
$datefrom = date('Y-m-d H:i:s', strtotime($datefrom . ' 00:00:00'));
$dateto = date('Y-m-d H:i:s', strtotime($dateto . ' 23:59:59'));
```

### 4. **Calculation Totals Incorrect**
**Issue**: Print report totals don't match individual orders  
**Cause**: Missing JOIN conditions or cancelled orders included

**Verification Query**:
```sql
SELECT 
    COUNT(*) as order_count,
    SUM(rod.totalBill) as total_bills,
    SUM(rod.tax) as total_tax,
    SUM(rod.netBill) as total_net
FROM restaurantorderdeliverydetails rod
LEFT JOIN restaurantorder ro ON rod.orderId = ro.id
WHERE rod.driverId = [ID]
AND rod.driverReciveDate BETWEEN '[START]' AND '[END]'
AND ro.tableId = -2;
```

---

## 🧪 Testing Scenarios

### Test Case 1: Basic Delivery Report
```
1. Create test driver in "سائقين" group
2. Create delivery orders assigned to driver
3. Access delivery report interface
4. Select driver and date range
5. Verify orders appear correctly
6. Check order details and totals
```

### Test Case 2: Print Report Generation
```
1. Generate delivery report for driver
2. Click print option with driver ID
3. Verify print template loads
4. Check all statistics calculated correctly:
   - Order count
   - Total bill amounts
   - Tax totals
   - Net bill totals
5. Verify driver information displays
```

### Test Case 3: Date Filtering
```
1. Create deliveries on different dates
2. Test various date range filters:
   - Single day
   - Week range
   - Month range
   - Custom ranges
3. Verify only orders in range appear
4. Test edge cases (midnight boundaries)
```

### Test Case 4: Driver Performance Comparison
```
1. Create multiple drivers
2. Assign orders to different drivers
3. Generate reports for each driver
4. Compare performance metrics:
   - Order counts
   - Total earnings
   - Average order value
   - Delivery efficiency
```

---

## 📚 Related Documentation

- [CLAUDE.md](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [ordersreport.md](ordersreport.md) - Restaurant order management
- [orderstatusreport.md](orderstatusreport.md) - Order status tracking
- [Database Schema Documentation](#) - Table relationships

---

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