<?php 
header("Content-Type:   application/vnd.ms-excel; charset=utf-8");
header('Content-Disposition: attachment; filename=informe_seguimiento.xls');

// Conexión a la base de datos
$servername   = "localhost";
$username = "systemso_usuario";
$password = "Rivera2020*";
$dbname = "systemso_sis-track";

// Crear conexión
$conn = new mysqli($servername, $username, $password, $dbname);

// Verificar conexión
if ($conn->connect_error) {
    die("Conexión fallida: " . $conn->connect_error);
}

$d = $_GET['fd'].' 00:00:00';
$h = $_GET['fh'].' 23:59:59';

// Consulta SQL
$sql = "SELECT * FROM seguimiento_rutas WHERE fecha_registro BETWEEN '$d' AND '$h' order by vehiculo";
$result = $conn->query($sql);

// Verificar si hay resultados
if ($result->num_rows > 0) {
    echo "<table border='1'>";
    echo " <tr>
            <th>VEHICULO</th>
            <th>CONDUCTOR</th>
            <th>NOMBRE CONDUCTOR</th>
            <th>RUTA</th>
            <th>UBICACION DE REFERENCIA</th>
            <th>UBICACION GPS</th>
            <th>NOVEDAD</th>
            <th>TIPO NOVEDAD</th>
            <th>DETALLES</th>
            <th>FECHA REGISTRO</th>
            <th>CORREO</th>
        </tr>";

    $prev_order_id = null;
    $rowspan = 0;
    $rows = []; // Para almacenar filas temporalmente

    while ($row = $result->fetch_assoc()) {
        if ($row['vehiculo'] == $prev_order_id) {
            $rowspan++;
        } else {
            if ($prev_order_id !== null) {
                // Imprimir filas anteriores con rowspan
                echo "<tr>";
                echo "<td rowspan='$rowspan'>" . $rows[0]['vehiculo'] . "</td>";
                echo "<td rowspan='$rowspan'>" . $rows[0]['conductor'] . "</td>";
                echo "<td rowspan='$rowspan'>" . $rows[0]['nombre_conductor'] . "</td>";
                foreach ($rows as $r) {
                    if ($r !== $rows[0]) echo "<tr>";
                    echo "<td>" . $r['tipo_ruta'] . "</td>";
                    echo "<td>" . $r['ubicacion_ref'] . "</td>";
                    echo "<td>" . $r['ubicacion_aut'] . "</td>";
                    echo "<td>" . $r['novedad'] . "</td>";
                    echo "<td>" . $r['tipo_novedad'] . "</td>";
                    echo "<td>" . $r['detalles'] . "</td>";
                    echo "<td>" . $r['fecha_registro'] . "</td>";
                    echo "<td>" . $r['correos_confirma'] . "</td>";
                    echo "</tr>";
                }
            }
            // Reiniciar variables para el nuevo grupo
            $rows = [];
            $rowspan = 1;
            $prev_order_id = $row['vehiculo'];
        }
        // Agregar fila actual a la colección
        $rows[] = $row;
    }

    // Imprimir las últimas filas
    echo "<tr>";
    echo "<td rowspan='$rowspan'>" . $rows[0]['vehiculo'] . "</td>";
    echo "<td rowspan='$rowspan'>" . $rows[0]['conductor'] . "</td>";
    echo "<td rowspan='$rowspan'>" . $rows[0]['nombre_conductor'] . "</td>";
    foreach ($rows as $r) {
        if ($r !== $rows[0]) echo "<tr>";
        
        echo "<td>" . $r['tipo_ruta'] . "</td>";
        echo "<td>" . $r['ubicacion_ref'] . "</td>";
        echo "<td>" . $r['ubicacion_aut'] . "</td>";
        echo "<td>" . $r['novedad'] . "</td>";
        echo "<td>" . $r['tipo_novedad'] . "</td>";
        echo "<td>" . $r['detalles'] . "</td>";
        echo "<td>" . $r['fecha_registro'] . "</td>";
        echo "<td>" . $r['correos_confirma'] . "</td>";
        echo "</tr>";
    }

    echo "</table>";
} else {
    echo "0 resultados";
}

// Cerrar conexión
$conn->close();