# 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
- [x] Display comprehensive order reports with filtering
- [x] Support multiple order types (table service, delivery, takeaway)
- [x] Provide advanced search capabilities
- [x] Handle order status changes via AJAX
- [x] Manage date range filtering with business hours
- [x] Supply AJAX endpoints for Select2 dropdowns
- [x] Track order completion and deletion status

### Related Controllers
- [orderdeliveryreport.php](orderdeliveryreport.md) - Delivery-specific reporting
- [orderstatusreport.php](orderstatusreport.md) - Order status management
- [restaurantorderController.php](#) - Order processing
- [restauranthallController.php](#) - Hall management

---

## 🗄️ Database Tables

### Primary Tables (Direct Operations)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **restaurantorder** | Restaurant orders | id, tableId, hallId, userId, sellBillId, finished, sysdate, daySreial, del |
| **restauranthall** | Restaurant halls | id, name, description |
| **restauranttable** | Restaurant tables | id, name, hallId |
| **restaurantorderdetails** | Order line items | id, orderId, productId, quantity, price |

### Reference Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **user** | System users/staff | userid, employeename, username |
| **usergroup** | User group definitions | usergroupid, usergroupname, billDeletePassword |

---

## 🔑 Key Functions

### 1. **Default Action** - Orders Report Dashboard
**Location**: Lines 99-182  
**Purpose**: Main order reporting interface with comprehensive filtering

**Function Signature**:
```php
// 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:
```php
$usergroupid = $_SESSION['usergroupid'];
$usergroup = R::getRow("SELECT usergroup.* FROM usergroup WHERE usergroupid = $usergroupid");
$_SESSION['passworduse'] = $usergroup['billDeletePassword'];
$smarty->assign("usergroup", $usergroup);
```

#### Multi-Criteria Filtering:
```php
$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:
```php
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:
```php
$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**:
```php
// 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**:
```php
$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**:
```php
// 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**:
```php
$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**:
```php
$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
```
┌─────────────────────────────────────────────────────────────┐
│              START: Order Report Request                   │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Load User Permissions                                   │
│     - Get user group information                            │
│     - Set bill deletion password in session                │
│     - Assign permissions to template                       │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Parse Filter Parameters                                 │
│     - Extract order ID, sales bill ID                      │
│     - Get hall ID, table ID, user ID                       │
│     - Parse order type and date range                      │
│     - Validate parameter formats                           │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Build Dynamic Query                                     │
│     - Start with base WHERE clause                         │
│     - Add specific ID filters if provided                  │
│     - Add hall/table/user filters                          │
│     - Add order type filtering                             │
│     - Add date range filtering                             │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Apply Default Filtering                                │
│     - If no filters specified, default to today            │
│     - Set time range (00:00:00 to 23:59:59)               │
│     - Include only finished orders                         │
│     - Sort by ID and daily serial number                   │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Execute Query & Display Results                        │
│     - Run query against restaurant order system            │
│     - Load associated hall and table information           │
│     - Assign data to template                              │
│     - Display via ordersreport/show.html                   │
└─────────────────────────────────────────────────────────────┘
```

---

### Workflow 2: AJAX Search Processing
```
┌─────────────────────────────────────────────────────────────┐
│              START: AJAX Search Request                    │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Parse Search Parameters                                 │
│     - Extract search terms from POST                       │
│     - Identify search type (orders, halls, tables, users)  │
│     - Validate search criteria                             │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Execute Search Query                                    │
│     - Call appropriate DAO search method                   │
│     - Apply search term filtering                          │
│     - Limit results for performance                        │
│     - Handle special cases (hall-specific tables)          │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Format Results for Select2                             │
│     - Create array structure with id/text pairs            │
│     - Handle empty results gracefully                      │
│     - Apply result limiting if necessary                   │
│     - Ensure proper data encoding                          │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Return JSON Response                                    │
│     - Encode array as JSON                                 │
│     - Set appropriate content type                         │
│     - Return to Select2 widget                             │
│     - Handle any encoding errors                           │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=` (empty) | Default action | Main order report dashboard |
| `do=changetype` | Order type toggle | Switch between delivery and takeaway |
| `do=getOrders` | AJAX order search | Order ID selection for Select2 |
| `do=getSellBillIds` | AJAX sales bill search | Sales bill ID selection |
| `do=getHalls` | AJAX hall search | Restaurant hall selection |
| `do=getTables` | AJAX table search | Restaurant table selection |
| `do=getUsers` | AJAX user search | User/staff selection |

### Required Parameters by Action

**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
```php
// 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
```php
// 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
```php
// 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
```php
$usergroupid = $_SESSION['usergroupid'];
$usergroup = R::getRow("SELECT usergroup.* FROM usergroup WHERE usergroupid = $usergroupid");
$_SESSION['passworduse'] = $usergroup['billDeletePassword'];
```

### Input Sanitization
```php
$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
```php
// 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**:
```sql
-- 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**:
```javascript
// 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**:
```sql
-- 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**:
```sql
-- 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](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide
- [orderdeliveryreport.md](orderdeliveryreport.md) - Delivery reporting
- [orderstatusreport.md](orderstatusreport.md) - Status management
- [Select2 Documentation](https://select2.org/) - Frontend dropdown library

---

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