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,
                order_created_at,
                order_flag,
                product_name,
                order_item_kitchen_note
            FROM 'order' 
            NATURAL JOIN order_item 
            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'],
                    'order_created_at'=> $row['order_created_at'],
                    'order_flag'=> $row['order_flag'],
                    'items'           => []
                ];
            }

            $orders[$orderId]['items'][] = [
                'product_name' => $row['product_name'],
                'kitchen_note' => $row['order_item_kitchen_note'] ?? ''
            ];
        }

        return array_values($orders);
    }
}