bartender/models/KitchenModel.php

<?php

namespace Models;

class KitchenModel
{
    private \PDO $pdo;

    public function __construct()
    {
        $dbFile = $_ENV['DB_FILE'];
        $dbPath = __DIR__ . '/../' . $dbFile;
        $this->pdo = new \PDO("sqlite:" . $dbPath);
        $this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
    }

    public function getKitchenOrders(int $companyId): array
    {
        $sql = "
            SELECT 
                order_id,
                table_id,
                user_name,
                order_created_at,
                order_flag,
                order_item_id,
                order_item_flag,
                product_name,
                order_item_kitchen_note
            FROM 'order' 
            NATURAL JOIN order_item
            NATURAL JOIN user
            NATURAL JOIN product 
            WHERE company_id = :company_id
              AND order_flag IN ('a', 'p')
              AND product_is_kitchen = 1
            ORDER BY order_created_at ASC;   
        ";
    
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['company_id' => $companyId]);
        $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC);
    
        $orders = [];
        foreach ($rows as $row) {
            $orderId = $row['order_id'];
        
            if (!isset($orders[$orderId])) {
                $orders[$orderId] = [
                    'order_id'         => $row['order_id'],
                    'table_id'         => $row['table_id'],
                    'user_name'       => $row['user_name'],
                    'order_created_at' => $row['order_created_at'],
                    'order_flag'       => $row['order_flag'],
                    'items'            => []
                ];
            }
        
            $orders[$orderId]['items'][] = [
                'order_item_id'    => $row['order_item_id'],
                'order_item_flag'  => $row['order_item_flag'],
                'product_name'     => $row['product_name'],
                'kitchen_note'     => $row['order_item_kitchen_note'] ?? ''
            ];
        }
    
        return array_values($orders);
    }
}