File "EvaluacionModelo.php"
Full Path: C:/wamp64/www/Formaciones/Modelos/EvaluacionModelo.php
File size: 11.81 KB
MIME-type: text/x-php
Charset: utf-8
<?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();
}
}