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 
        o.order_id,
        t.table_number,
        u.user_name,
        o.order_name,
        o.order_created_at,
        o.order_flag,
        oi.order_item_id,
        oi.order_item_flag,
        p.product_name,
        oi.order_item_kitchen_note
    FROM `order` o
    INNER JOIN order_item oi ON o.order_id = oi.order_id
    INNER JOIN product p ON oi.product_id = p.product_id
    INNER JOIN `table` t ON o.table_id = t.table_id
    INNER JOIN user u ON o.user_id = u.user_id
    WHERE o.company_id = :company_id
      AND o.order_flag IN ('a', 'p')
      AND p.product_is_kitchen = 1
    ORDER BY o.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_number'     => $row['table_number'],
    'user_name'        => $row['user_name'],
    'order_name'       => $row['order_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);
    }
}