Orders Report Controller Documentation

File: /controllers/ordersreport.php

Purpose: Comprehensive restaurant order reporting with filtering, search capabilities, and order type management

Last Updated: December 20, 2024

Total Functions: 6 AJAX endpoints

Lines of Code: ~255

---

📋 Overview

The Orders Report Controller provides comprehensive reporting capabilities for restaurant orders with advanced filtering and search features. It handles:

• • Restaurant order listing with complex filtering
• • Order type management (table, delivery, takeaway)
• • Date range filtering with business hours support
• • Multi-criteria search across orders, halls, tables, and users
• • Order status tracking and management
• • AJAX-powered Select2 dropdowns for efficient data selection

Primary Functions

• ✅ Display comprehensive order reports with filtering
• ✅ Support multiple order types (table service, delivery, takeaway)
• ✅ Provide advanced search capabilities
• ✅ Handle order status changes via AJAX
• ✅ Manage date range filtering with business hours
• ✅ Supply AJAX endpoints for Select2 dropdowns
• ✅ Track order completion and deletion status

Related Controllers

• • orderdeliveryreport.php - Delivery-specific reporting
• • orderstatusreport.php - Order status management
• • restaurantorderController.php - Order processing
• • restauranthallController.php - Hall management

---

🗄️ Database Tables

Primary Tables (Direct Operations)

🔑 Key Functions

1. Default Action - Orders Report Dashboard

Location: Lines 99-182

Purpose: Main order reporting interface with comprehensive filtering

Function Signature:

// Triggered when: empty $do

Process Flow:

1. Load user group permissions for bill deletion

2. Set up filtering parameters from POST data

3. Build dynamic query string with multiple criteria

4. Apply date filtering with default to today

5. Execute query to get filtered orders

6. Display results via template

Key Features:

User Permission Setup:

$usergroupid = $_SESSION['usergroupid'];
$usergroup = R::getRow("SELECT usergroup.* FROM usergroup WHERE usergroupid = $usergroupid");
$_SESSION['passworduse'] = $usergroup['billDeletePassword'];
$smarty->assign("usergroup", $usergroup);

Multi-Criteria Filtering:

$queryString = " where 1 ";

// Order ID filtering
if (isset($orderId) && !empty($orderId) && $orderId != -1) {
    $queryString .= 'and restaurantorder.id = ' . $orderId . ' ';
} else {
    // Sales bill ID filtering
    if (isset($sellBillId) && !empty($sellBillId) && $sellBillId != -1) {
        $queryString .= 'and restaurantorder.sellBillId = ' . $sellBillId . ' ';
    }
    
    // Hall filtering
    if (isset($hallId) && !empty($hallId) && $hallId != -1) {
        $queryString .= 'and restauranthall.id = ' . $hallId . ' ';
    }
    
    // Table filtering
    if (isset($tableId) && !empty($tableId) && $tableId != -1) {
        $queryString .= 'and restaurantorder.tableId = ' . $tableId . ' ';
    }
    
    // User filtering
    if (isset($userId) && !empty($userId) && $userId != -1) {
        $queryString .= 'and restaurantorder.userId = ' . $userId . ' ';
    }
    
    // Order type filtering
    if (isset($orderType) && !empty($orderType)) {
        if ($orderType == -1 || $orderType == -2) { // delivery, takeaway
            $queryString .= 'and restaurantorder.tableId = ' . $orderType . ' ';
        } else if ($orderType == 1) { // table service
            $queryString .= 'and restaurantorder.tableId > 0 ';
        }
    }
}

Default Date Filtering:

if ($queryString == ' where 1 ') {
    $queryString .= 'and restaurantorder.sysdate >= "' . date('Y-m-d') . ' 00:00:00" ';
    $queryString .= 'and restaurantorder.sysdate <= "' . date('Y-m-d') . ' 23:59:59" ';
}

Order Status and Sorting:

$queryString .= ' and restaurantorder.finished=1 order by restaurantorder.id desc, restaurantorder.daySreial desc';
$ordersData = $restaurantOrderEX->queryOrdersQueryString($queryString);

---

2. changetype - Order Type Management

Location: Lines 166-178

Purpose: Toggle order type between delivery and takeaway

Function Signature:

// Triggered when: $do == "changetype"

Process Flow:

1. Extract order ID and current table ID

2. Toggle between delivery (-1) and takeaway (-2)

3. Update order record directly

Logic:

$orderId = filter_input(INPUT_POST, 'orderid');
$tableid = filter_input(INPUT_POST, 'tableid');

if($tableid == -1){
    $tableid = -2;  // Change to takeaway
} else {
    $tableid = -1;  // Change to delivery
}

R::exec("UPDATE restaurantorder SET tableId = $tableid WHERE id = $orderId");

---

3. getOrders - Order ID Search

Location: Lines 184-196

Purpose: AJAX endpoint for Select2 order ID search

Function Signature:

// Triggered when: $do == "getOrders"

Process Flow:

1. Extract search term from POST

2. Query orders matching search criteria

3. Format results for Select2 dropdown

4. Return JSON response

Implementation:

$search = $_POST["searchTerms"];
$results = $restaurantOrderEX->getOrdersIdSimple($search);
if (count($results) > 0) {
    foreach ($results as $data) {
        $row_array['id'] = $data->id;
        $row_array['text'] = $data->id;
        array_push($return_arr, $row_array);
    }
}
echo json_encode($return_arr);

---

4. getSellBillIds - Sales Bill ID Search

Location: Lines 197-209

Purpose: AJAX endpoint for Select2 sales bill ID search

Process Flow:

1. Extract search term from POST

2. Query sales bill IDs matching criteria

3. Format for Select2 response

4. Return JSON data

---

5. getHalls - Hall Search

Location: Lines 210-222

Purpose: AJAX endpoint for restaurant hall selection

Process Flow:

1. Extract search term from POST

2. Query restaurant halls matching criteria

3. Return hall ID and name pairs

4. Format for Select2 dropdown

---

6. getTables - Table Search

Location: Lines 223-236

Purpose: AJAX endpoint for table selection within halls

Process Flow:

1. Extract search term and hall ID from POST

2. Query tables within specific hall

3. Return table ID and name pairs

4. Support hall-specific filtering

Implementation:

$search = $_POST["searchTerms"];
$hallId = (int) $_POST["hallId"];
$results = $restaurantTableEX->gettableIds($search, $hallId);

---

7. getUsers - User Search

Location: Lines 237-249

Purpose: AJAX endpoint for user/staff selection

Process Flow:

1. Extract search term from POST

2. Query system users matching criteria

3. Return user ID and employee name pairs

4. Format for Select2 response

---

🔄 Workflows

Workflow 1: Order Report Generation

---

Workflow 2: AJAX Search Processing

---

🌐 URL Routes & Actions

Order Report (do= empty):

• • orderId - Specific order ID (POST, optional)
• • sellBillId - Sales bill ID (POST, optional)
• • hallId - Restaurant hall ID (POST, optional)
• • tableId - Table ID (POST, optional)
• • userId - User ID (POST, optional)
• • orderType - Order type filter (POST, optional)
• • datefrom - Start date (POST, optional)
• • dateto - End date (POST, optional)

Order Type Change (do=changetype):

• • orderid - Order ID (POST, required)
• • tableid - Current table ID (POST, required)

AJAX Searches:

• • searchTerms - Search string (POST, required)
• • hallId - Hall ID for table search (POST, optional)

---

🧮 Calculation Methods

Order Type Classification

// Order type mapping
// tableId > 0  : Table service
// tableId = -1 : Delivery
// tableId = -2 : Takeaway

if ($orderType == -1 || $orderType == -2) {
    // Delivery or takeaway orders
    $queryString .= 'and restaurantorder.tableId = ' . $orderType . ' ';
} else if ($orderType == 1) {
    // Table service orders
    $queryString .= 'and restaurantorder.tableId > 0 ';
}

Date Range Processing

// Default to today if no filters specified
if ($queryString == ' where 1 ') {
    $today = date('Y-m-d');
    $queryString .= 'and restaurantorder.sysdate >= "' . $today . ' 00:00:00" ';
    $queryString .= 'and restaurantorder.sysdate <= "' . $today . ' 23:59:59" ';
}

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

Order Type Toggle Logic

// Toggle between delivery (-1) and takeaway (-2)
if($tableid == -1){
    $tableid = -2;  // Delivery to takeaway
} else {
    $tableid = -1;  // Takeaway to delivery
}

---

🔒 Security & Permissions

User Group Permission Management

$usergroupid = $_SESSION['usergroupid'];
$usergroup = R::getRow("SELECT usergroup.* FROM usergroup WHERE usergroupid = $usergroupid");
$_SESSION['passworduse'] = $usergroup['billDeletePassword'];

Input Sanitization

$orderId = filter_input(INPUT_POST, 'orderId');
$sellBillId = filter_input(INPUT_POST, 'sellBillId');
$hallId = filter_input(INPUT_POST, 'hallId');
$tableId = filter_input(INPUT_POST, 'tableId');
$userId = filter_input(INPUT_POST, 'userId');
$orderType = (int) filter_input(INPUT_POST, 'orderType');

SQL Injection Prevention

// Direct SQL execution (potential security risk)
R::exec("UPDATE restaurantorder SET `tableId`= $tableid WHERE id = $orderId");

// Recommended: Use parameterized queries
$stmt = $pdo->prepare("UPDATE restaurantorder SET tableId = ? WHERE id = ?");
$stmt->execute([$tableid, $orderId]);

---

📊 Performance Considerations

Database Optimization Tips

1. Required Indexes:

- restaurantorder(finished, sysdate, id)

- restaurantorder(tableId, finished)

- restaurantorder(sellBillId)

- restaurantorder(userId, finished)

- restauranthall(id, name)

- restauranttable(hallId, id, name)

2. Query Optimization:

- Efficient WHERE clause ordering

- Proper use of composite indexes

- Limit AJAX search results

3. Memory Management:

- Limit date ranges for large datasets

- Efficient AJAX response sizes

- Proper query result handling

---

🐛 Common Issues & Troubleshooting

1. No Orders Showing in Report

Issue: Report shows empty despite having orders

Cause: Incorrect date filtering or finished status

Debug:

-- Check for orders in database
SELECT COUNT(*) FROM restaurantorder WHERE finished = 1;

-- Check date filtering
SELECT COUNT(*) FROM restaurantorder 
WHERE finished = 1 
AND sysdate >= '2024-01-01 00:00:00' 
AND sysdate <= '2024-12-31 23:59:59';

2. Order Type Toggle Not Working

Issue: Order type changes not saving

Cause: AJAX request failures or validation issues

Debug:

// Check AJAX response
$.post('?do=changetype', {orderid: 123, tableid: -1}, function(response) {
    console.log('Response:', response);
}).fail(function(xhr) {
    console.error('Error:', xhr.responseText);
});

3. Select2 Dropdowns Not Loading

Issue: AJAX dropdowns showing no results

Cause: Search method failures or data issues

Debug:

-- Check if search methods return data
SELECT id, name FROM restauranthall WHERE name LIKE '%search%';
SELECT id, name FROM restauranttable WHERE hallId = 1;
SELECT userid, employeename FROM user WHERE employeename LIKE '%search%';

4. Permission Issues

Issue: Users cannot access certain features

Cause: User group permission problems

Debug:

-- Check user group setup
SELECT * FROM usergroup WHERE usergroupid = [USER_GROUP_ID];

-- Verify user group assignment
SELECT u.*, ug.usergroupname FROM user u
JOIN usergroup ug ON u.usergroupid = ug.usergroupid
WHERE u.userid = [USER_ID];

---

🧪 Testing Scenarios

Test Case 1: Basic Order Filtering

1. Create test orders with different types:
   - Table service (tableId > 0)
   - Delivery (tableId = -1)
   - Takeaway (tableId = -2)
2. Test each order type filter
3. Verify correct orders appear
4. Check date range filtering

Test Case 2: AJAX Search Functionality

1. Test each AJAX endpoint:
   - Order ID search
   - Sales bill ID search
   - Hall search
   - Table search (with hall filtering)
   - User search
2. Verify Select2 integration works
3. Check search term filtering
4. Test empty result handling

Test Case 3: Order Type Management

1. Create delivery order (tableId = -1)
2. Use changetype action to toggle
3. Verify order becomes takeaway (tableId = -2)
4. Toggle again and verify back to delivery
5. Check database updates correctly

Test Case 4: Permission Testing

1. Test with different user groups
2. Verify bill deletion permissions
3. Check session variable settings
4. Test access control for different features

---

📚 Related Documentation

• • CLAUDE.md - PHP 8.2 migration guide
• • orderdeliveryreport.md - Delivery reporting
• • orderstatusreport.md - Status management
• • Select2 Documentation - Frontend dropdown library

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur