bartender/models/ReportsModel.php

<?php

namespace Models;

class ReportsModel
{
    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);
        $this->pdo->exec('PRAGMA journal_mode = WAL;');
        $this->pdo->exec('PRAGMA busy_timeout = 5000;');
    }

    public function getReports(int $companyId, int $page = 1, int $limit = 10): array
    {
        $offset = ($page - 1) * $limit;

        $sql = "
            SELECT o.order_id,
                o.order_finished_at,
                o.table_id,
                o.order_name,
                o.order_flag,
                p.product_name,
                p.product_price
            FROM 'order' o
            NATURAL JOIN order_item
            NATURAL JOIN product p
            NATURAL JOIN status
            WHERE o.company_id = :company_id
              AND status_status = 'Finalizada'
            ORDER BY o.order_finished_at DESC
            LIMIT :limit OFFSET :offset;
        ";

        $stmt = $this->pdo->prepare($sql);
        $stmt->bindValue(':company_id', $companyId, \PDO::PARAM_INT);
        $stmt->bindValue(':limit', $limit, \PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, \PDO::PARAM_INT);
        $stmt->execute();

        $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC);

        // Agrupamento por pedido
        $orders = [];
        foreach ($rows as $row) {
            $orderId = $row['order_id'];

            if (!isset($orders[$orderId])) {
                $orders[$orderId] = [
                    'order_id'         => $row['order_id'],
                    'order_finished_at'=> $row['order_finished_at'],
                    'table_id'         => $row['table_id'],
                    'order_name'       => $row['order_name'],
                    'order_flag'       => $row['order_flag'],
                    'items'            => []
                ];
            }

            $orders[$orderId]['items'][] = [
                'product_name'  => $row['product_name'],
                'product_price' => $row['product_price']
            ];
        }

        return array_values($orders);
    }
}