HEX

File: /var/www/html/gmartinmartin.sumar.com.py-2/tiempos_conexion4.php
<?php
// Configuración de la conexión a la base de datos
$dbhost = 'localhost';
$dbname = 'mdl_gmm-cpcndr';
$dbuser = 'root';
$dbpass = '(!Xbp;rhu4oq18';

try {
    $pdo = new PDO("mysql:host=$dbhost;dbname=$dbname;charset=utf8", $dbuser, $dbpass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die("Error al conectar a la base de datos: " . $e->getMessage());
}

// Paginación
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$perPage = 50;
$offset = ($page - 1) * $perPage;

// Búsqueda por usuario
$search = isset($_GET['search']) ? trim($_GET['search']) : '';
$searchQuery = $search ? " AND u.username LIKE :search " : "";

// Exportar a Excel
if (isset($_GET['export']) && $_GET['export'] == 'excel') {
    header("Content-Type: application/vnd.ms-excel");
    header("Content-Disposition: attachment; filename=tiempos_conexion.xls");
    echo "Usuario\tNombre\tTiempo de Conexión\n";
    
    $stmt = $pdo->prepare("SELECT u.username, CONCAT(u.firstname, ' ', u.lastname), REPLACE(FORMAT(IFNULL((
        SELECT SUM(diferencia) / 60 FROM (
            SELECT userid, 
                   (LEAD(timecreated) OVER (PARTITION BY userid ORDER BY timecreated) - timecreated) AS diferencia
            FROM mdl_logstore_standard_log
            WHERE timecreated > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 3 MONTH))
        ) AS subquery
        WHERE subquery.userid = u.id AND subquery.diferencia BETWEEN 60 AND 43200
    ), 0), 2), '.', ',') FROM mdl_user u WHERE u.suspended = 0 AND u.deleted = 0");
    $stmt->execute();
    while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
        echo implode("\t", $row) . "\n";
    }
    exit;
}

// Consulta optimizada con filtrado de tiempos excesivos
$sql = "
    SELECT 
        u.id AS userid,
        u.username AS Usuario,
        CONCAT(u.firstname, ' ', u.lastname) AS Nombre,
        REPLACE(FORMAT(IFNULL((
            SELECT SUM(diferencia) / 60 FROM (
                SELECT userid, 
                       (LEAD(timecreated) OVER (PARTITION BY userid ORDER BY timecreated) - timecreated) AS diferencia
                FROM mdl_logstore_standard_log
                WHERE timecreated > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 3 MONTH))
            ) AS subquery
            WHERE subquery.userid = u.id AND subquery.diferencia BETWEEN 60 AND 43200
        ), 0), 2), '.', ',') AS Tiempo_de_Conexión_Minutos
    FROM 
        mdl_user u
    WHERE 
        u.suspended = 0 AND u.deleted = 0
        $searchQuery
    ORDER BY 
        u.username
    LIMIT :perPage OFFSET :offset;
";

try {
    $stmt = $pdo->prepare($sql);
    if ($search) {
        $stmt->bindValue(':search', "%$search%", PDO::PARAM_STR);
    }
    $stmt->bindValue(':perPage', $perPage, PDO::PARAM_INT);
    $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
    $stmt->execute();
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    die("Error al ejecutar la consulta: " . $e->getMessage());
}

// Función para convertir minutos a horas y minutos
function formatoTiempo($minutos) {
    $horas = floor($minutos / 60);
    $restoMinutos = $minutos % 60;
    
    return $horas . ' hora' . ($horas != 1 ? 's' : '') . ' ' . $restoMinutos . ' minuto' . ($restoMinutos != 1 ? 's' : '');
}

?>
<!DOCTYPE html>
<html lang="es">
<head>
    <meta charset="UTF-8">
    <title>Tiempos de Conexión de Usuarios en Moodle</title>
    <style>
        body {
            font-family: Arial, sans-serif;
            background-color: #f4f4f4;
            text-align: center;
        }
        .container {
            width: 80%;
            margin: 20px auto;
            background: #fff;
            padding: 20px;
            border-radius: 10px;
            box-shadow: 0px 0px 10px #ccc;
        }
        table { width: 100%; border-collapse: collapse; margin-top: 20px; }
        th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
        th { background-color: #007BFF; color: white; }
        input, button {
            padding: 10px;
            margin: 10px;
            border-radius: 5px;
            border: 1px solid #ddd;
        }
        .btn-export {
            background-color: #28a745;
            color: white;
            text-decoration: none;
            padding: 10px 15px;
            border-radius: 5px;
            display: inline-block;
            margin-top: 10px;
        }
    </style>
</head>
<body>
    <div class="container">
        <h1>Tiempos de Conexión de Usuarios en Moodle</h1>
        <form method="GET">
            <input type="text" name="search" placeholder="Buscar usuario..." value="<?php echo htmlspecialchars($search); ?>">
            <button type="submit">Buscar</button>
        </form>
        <a href="?export=excel" class="btn-export">Exportar a Excel</a>
        <table>
            <thead>
                <tr>
                    <th>Usuario</th>
                    <th>Nombre del Usuario</th>
                    <th>Tiempo de Conexión</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($results as $row): ?>
                    <tr>
                        <td><?php echo htmlspecialchars($row['Usuario']); ?></td>
                        <td><?php echo htmlspecialchars($row['Nombre']); ?></td>
                        <td><?php echo formatoTiempo($row['Tiempo_de_Conexión_Minutos']); ?></td>
                    </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </div>
</body>
</html>