Tiny File Manager

Back Back Up Advanced Editor

<?php
// Modelos/EvaluacionModelo.php

class EvaluacionModelo {
    private $db;

public function __construct($db) {
        $this->db = $db;
    }

public function listarTodas() {
        $query = "SELECT q.*, c.title as course_title 
                  FROM quizzes q 
                  JOIN courses c ON q.course_id = c.id 
                  ORDER BY q.id DESC";
        return $this->db->query($query);
    }

public function crearCabecera($curso_id, $titulo, $intentos) {
        $stmt = $this->db->prepare("INSERT INTO quizzes (course_id, title, attempts_allowed, created_at) VALUES (?, ?, ?, NOW())");
        $stmt->bind_param("isi", $curso_id, $titulo, $intentos);
        return $stmt->execute();
    }

public function obtenerPorId($id) {
        $stmt = $this->db->prepare("SELECT * FROM quizzes WHERE id = ?");
        $stmt->bind_param("i", $id);
        $stmt->execute();
        return $stmt->get_result()->fetch_assoc();
    }

public function eliminar($id) {
        // Primero eliminamos preguntas (si tu BD no tiene borrado en cascada)
        $this->db->query("DELETE FROM questions WHERE quiz_id = $id");
        // Luego la evaluación
        $stmt = $this->db->prepare("DELETE FROM quizzes WHERE id = ?");
        $stmt->bind_param("i", $id);
        return $stmt->execute();
    }
        
        // Dentro de la clase EvaluacionModelo

public function agregarPregunta($quiz_id, $texto, $tipo, $puntos) {
                $stmt = $this->db->prepare("INSERT INTO questions (quiz_id, question_text, question_type, points) VALUES (?, ?, ?, ?)");
                $stmt->bind_param("issi", $quiz_id, $texto, $tipo, $puntos);
                if ($stmt->execute()) {
                        return $this->db->insert_id; // Retorna el ID de la pregunta creada
                }
                return false;
        }

public function agregarOpcion($question_id, $texto, $es_correcta) {
                $stmt = $this->db->prepare("INSERT INTO options (question_id, option_text, is_correct) VALUES (?, ?, ?)");
                $stmt->bind_param("isi", $question_id, $texto, $es_correcta);
                return $stmt->execute();
        }

// También agregamos esta para que el botón de borrar pregunta funcione
        public function eliminarPregunta($id) {
                $this->db->query("DELETE FROM options WHERE question_id = $id");
                $stmt = $this->db->prepare("DELETE FROM questions WHERE id = ?");
                $stmt->bind_param("i", $id);
                return $stmt->execute();
        }
        
        // MODELO OBTENER RESULTADOS
        public function obtenerResultados($quiz_id = null) {
                // Ajustado a tu tabla 'quiz_attempts'
                $query = "SELECT qa.id, u.name as estudiante, u.cedula, q.title as evaluacion, 
                                                 c.title as curso, qa.score, qa.submitted_at as completed_at 
                                  FROM quiz_attempts qa 
                                  JOIN users u ON qa.user_id = u.id 
                                  JOIN quizzes q ON qa.quiz_id = q.id 
                                  JOIN courses c ON q.course_id = c.id
                                  WHERE qa.submitted_at IS NOT NULL"; // Solo mostrar los que ya terminaron
                
                if ($quiz_id) {
                        $query .= " AND qa.quiz_id = " . (int)$quiz_id;
                }
                
                $query .= " ORDER BY qa.submitted_at DESC";
                return $this->db->query($query);
        }
        
        /**
         * Contar resultados para paginación con filtros avanzados
         * Filtros: búsqueda (nombre/cédula), evaluación/curso, fecha desde/hasta, estado
         */
        public function contarResultados($busqueda = '', $quiz_id = null, $filtro_eval = '', $fecha_desde = '', $fecha_hasta = '', $estado = '') {
                $sql = "SELECT COUNT(*) as total 
                        FROM quiz_attempts qa 
                        JOIN users u ON qa.user_id = u.id 
                        JOIN quizzes q ON qa.quiz_id = q.id 
                        JOIN courses c ON q.course_id = c.id
                        WHERE qa.submitted_at IS NOT NULL";
                
                if ($quiz_id) $sql .= " AND qa.quiz_id = " . (int)$quiz_id;
                if (!empty($busqueda)) {
                        $busqueda = $this->db->real_escape_string($busqueda);
                        $sql .= " AND (u.name LIKE '%$busqueda%' OR u.cedula LIKE '%$busqueda%')";
                }
                if (!empty($filtro_eval)) {
                        $filtro_eval = $this->db->real_escape_string($filtro_eval);
                        $sql .= " AND (q.title LIKE '%$filtro_eval%' OR c.title LIKE '%$filtro_eval%')";
                }
                if (!empty($fecha_desde)) {
                        $fecha_desde = $this->db->real_escape_string($fecha_desde);
                        $sql .= " AND DATE(qa.submitted_at) >= '$fecha_desde'";
                }
                if (!empty($fecha_hasta)) {
                        $fecha_hasta = $this->db->real_escape_string($fecha_hasta);
                        $sql .= " AND DATE(qa.submitted_at) <= '$fecha_hasta'";
                }
                if ($estado === 'aprobado') {
                        $sql .= " AND qa.score >= 70";
                } elseif ($estado === 'reprobado') {
                        $sql .= " AND qa.score < 70";
                }

$res = $this->db->query($sql);
                $fila = $res->fetch_assoc();
                return $fila['total'];
        }

/**
         * Obtener resultados paginados con filtros avanzados
         * Incluye user_id, quiz_id, course_id necesarios para el botón de diploma
         */
        public function obtenerResultadosPaginados($busqueda = '', $limit = 20, $offset = 0, $quiz_id = null, $filtro_eval = '', $fecha_desde = '', $fecha_hasta = '', $estado = '') {
                $sql = "SELECT qa.id, qa.user_id, qa.quiz_id, c.id as course_id,
                                   u.name as estudiante, u.cedula, q.title as evaluacion, 
                                   c.title as curso, qa.score, qa.submitted_at as completed_at 
                                FROM quiz_attempts qa 
                                JOIN users u ON qa.user_id = u.id 
                                JOIN quizzes q ON qa.quiz_id = q.id 
                                JOIN courses c ON q.course_id = c.id
                                WHERE qa.submitted_at IS NOT NULL";

if ($quiz_id) $sql .= " AND qa.quiz_id = " . (int)$quiz_id;
                if (!empty($busqueda)) {
                        $busqueda = $this->db->real_escape_string($busqueda);
                        $sql .= " AND (u.name LIKE '%$busqueda%' OR u.cedula LIKE '%$busqueda%')";
                }
                if (!empty($filtro_eval)) {
                        $filtro_eval = $this->db->real_escape_string($filtro_eval);
                        $sql .= " AND (q.title LIKE '%$filtro_eval%' OR c.title LIKE '%$filtro_eval%')";
                }
                if (!empty($fecha_desde)) {
                        $fecha_desde = $this->db->real_escape_string($fecha_desde);
                        $sql .= " AND DATE(qa.submitted_at) >= '$fecha_desde'";
                }
                if (!empty($fecha_hasta)) {
                        $fecha_hasta = $this->db->real_escape_string($fecha_hasta);
                        $sql .= " AND DATE(qa.submitted_at) <= '$fecha_hasta'";
                }
                if ($estado === 'aprobado') {
                        $sql .= " AND qa.score >= 70";
                } elseif ($estado === 'reprobado') {
                        $sql .= " AND qa.score < 70";
                }

$sql .= " ORDER BY qa.submitted_at DESC LIMIT $limit OFFSET $offset";
                return $this->db->query($sql);
        }

/**
         * Obtener el detalle de respuestas de un intento de evaluación.
         * Incluye la pregunta, la opción seleccionada, la opción correcta y si fue correcta.
         */
        public function obtenerRespuestasIntento($attempt_id) {
                $sql = "SELECT a.id, a.question_id, a.selected_option_id, a.is_correct, a.awarded_points, a.answer_text,
                               q.question_text, q.question_type, q.points,
                               o_selected.option_text AS selected_option_text,
                               o_correct.option_text AS correct_option_text
                        FROM answers a
                        JOIN questions q ON q.id = a.question_id
                        LEFT JOIN options o_selected ON o_selected.id = a.selected_option_id
                        LEFT JOIN options o_correct ON o_correct.question_id = q.id AND o_correct.is_correct = 1
                        WHERE a.attempt_id = ?
                        ORDER BY q.id ASC";

$stmt = $this->db->prepare($sql);
                $stmt->bind_param('i', $attempt_id);
                $stmt->execute();
                $result = $stmt->get_result();

$respuestas = [];
                while ($row = $result->fetch_assoc()) {
                        $respuestas[] = $row;
                }

return $respuestas;
        }

/**
         * Obtener todas las opciones de una pregunta (para mostrar en el detalle).
         */
        public function obtenerOpcionesPregunta($question_id) {
                $sql = "SELECT id, option_text, is_correct FROM options WHERE question_id = ? ORDER BY id ASC";
                $stmt = $this->db->prepare($sql);
                $stmt->bind_param('i', $question_id);
                $stmt->execute();
                $result = $stmt->get_result();

$opciones = [];
                while ($row = $result->fetch_assoc()) {
                        $opciones[] = $row;
                }
                return $opciones;
        }

/**
         * Obtener evaluaciones y cursos disponibles (para poblar el filtro dropdown).
         */
        public function obtenerEvaluacionesYCursos() {
                $sql = "SELECT q.id, q.title as evaluacion, c.title as curso 
                        FROM quizzes q 
                        JOIN courses c ON q.course_id = c.id 
                        ORDER BY c.title, q.title";
                $result = $this->db->query($sql);

$lista = [];
                while ($row = $result->fetch_assoc()) {
                        $lista[] = $row;
                }
                return $lista;
        }

/**
         * Obtener datos de completación de curso para generar diploma (modo instructor).
         * Requiere user_id y course_id explícitos (no usa sesión).
         */
        public function obtenerCompletacionCursoDiploma($user_id, $course_id) {
                $sql = "SELECT c.*, u.name, u.cedula, u.cargo, m.namePro, m.cargoPro, qa.submitted_at,
                               (SELECT COUNT(*) FROM quiz_attempts qa_sub 
                                WHERE qa_sub.quiz_id IN (SELECT id FROM quizzes WHERE course_id = c.id) 
                                AND qa_sub.user_id = u.id AND qa_sub.score >= 70) as quizzes_passed
                        FROM courses c 
                        JOIN users u ON u.id = ?
                        LEFT JOIN quiz_attempts qa ON qa.user_id = u.id 
                            AND qa.quiz_id IN (SELECT id FROM quizzes WHERE course_id = c.id)
                            AND qa.score >= 70
                        LEFT JOIN materials m ON m.course_id = c.id
                        WHERE c.id = ?
                        ORDER BY qa.submitted_at DESC 
                        LIMIT 1";
                
                $stmt = $this->db->prepare($sql);
                $stmt->bind_param('ii', $user_id, $course_id);
                $stmt->execute();
                return $stmt->get_result()->fetch_assoc();
        }
}