<?php
include_once '../conexion.php';

header("Content-Type: application/vnd.ms-excel; charset=UTF-8");
header("Content-Disposition: attachment; filename=reporte_creditos.xls");
header("Pragma: no-cache");
header("Expires: 0");

echo "<table border='1'>";
echo "<tr>
        <th>ID Cliente</th>
        <th>Nombre Cliente</th>
        <th>Total Galones</th>
        <th>Total Crédito (S/.)</th>
      </tr>";

try {
    $sql = "SELECT 
                d.cliente_id,
                c.nombre_cliente,
                SUM(d.galones) AS total_galones,
                SUM(d.soles) AS total_credito
            FROM despachos d
            INNER JOIN ventas v ON v.despacho_id = d.id
            INNER JOIN clientes c ON c.id = d.cliente_id
            WHERE d.operacion = 'CRD'
            GROUP BY d.cliente_id, c.nombre_cliente
            ORDER BY c.nombre_cliente";

    $stmt = $conn->prepare($sql);
    $stmt->execute();
    $resultados = $stmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($resultados as $row) {
        echo "<tr>";
        echo "<td>{$row['cliente_id']}</td>";
        echo "<td>" . utf8_decode($row['nombre_cliente']) . "</td>";
        echo "<td align='right'>" . number_format($row['total_galones'], 2) . "</td>";
        echo "<td align='right'>" . number_format($row['total_credito'], 2) . "</td>";
        echo "</tr>";
    }

    echo "</table>";

} catch (Exception $e) {
    echo "<tr><td colspan='4'>Error: " . $e->getMessage() . "</td></tr>";
    echo "</table>";
}
?>
