File "EvaluacionAsigModelo.php"
Full Path: C:/wamp64/www/Formaciones/Modelos/EvaluacionAsigModelo.php
File size: 11.36 KB
MIME-type: text/x-php
Charset: utf-8
<?php
// Modelos/EvaluacionAsigModelo.php
class EvaluacionAsigModelo {
private $db;
public function __construct($db) {
$this->db = $db;
}
// Obtener cursos asignados a un usuario
public function obtenerCursosAsignados($user_id) {
$sql = "SELECT c.*, ca.assigned_at
FROM courses c
JOIN course_assignments ca ON ca.course_id = c.id
WHERE ca.user_id = ?
ORDER BY ca.assigned_at DESC";
$stmt = $this->db->prepare($sql);
$stmt->bind_param('i', $user_id);
$stmt->execute();
return $stmt->get_result();
}
// Verificar acceso de un usuario a un curso
public function verificarAccesoCurso($user_id, $course_id) {
$sql = "SELECT c.*
FROM courses c
JOIN course_assignments ca ON ca.course_id = c.id
WHERE c.id = ? AND ca.user_id = ?";
$stmt = $this->db->prepare($sql);
$stmt->bind_param('ii', $course_id, $user_id);
$stmt->execute();
return $stmt->get_result()->fetch_assoc();
}
// Obtener materiales de un curso
public function obtenerMaterialesCurso($course_id) {
$sql = "SELECT * FROM materials
WHERE course_id = ?
ORDER BY created_at DESC";
$stmt = $this->db->prepare($sql);
$stmt->bind_param('i', $course_id);
$stmt->execute();
return $stmt->get_result();
}
// Obtener evaluación de un curso
public function obtenerEvaluacionCurso($course_id) {
$sql = "SELECT * FROM quizzes
WHERE course_id = ?
LIMIT 1";
$stmt = $this->db->prepare($sql);
$stmt->bind_param('i', $course_id);
$stmt->execute();
return $stmt->get_result()->fetch_assoc();
}
// Obtener estadísticas de evaluación de un usuario
public function obtenerEstadisticasEvaluacion($quiz_id, $user_id) {
// Contar intentos
$sql = "SELECT COUNT(*) as attempt_count
FROM quiz_attempts
WHERE quiz_id = ? AND user_id = ? AND submitted_at IS NOT NULL";
$stmt = $this->db->prepare($sql);
$stmt->bind_param('ii', $quiz_id, $user_id);
$stmt->execute();
$result = $stmt->get_result()->fetch_assoc();
$attempt_count = $result['attempt_count'] ?? 0;
// Mejor puntaje
$sql = "SELECT MAX(score) as best_score
FROM quiz_attempts
WHERE quiz_id = ? AND user_id = ? AND submitted_at IS NOT NULL";
$stmt = $this->db->prepare($sql);
$stmt->bind_param('ii', $quiz_id, $user_id);
$stmt->execute();
$result = $stmt->get_result()->fetch_assoc();
$best_score = $result['best_score'] ?? 0;
// Último puntaje
$sql = "SELECT score
FROM quiz_attempts
WHERE quiz_id = ? AND user_id = ? AND submitted_at IS NOT NULL
ORDER BY submitted_at DESC LIMIT 1";
$stmt = $this->db->prepare($sql);
$stmt->bind_param('ii', $quiz_id, $user_id);
$stmt->execute();
$result = $stmt->get_result()->fetch_assoc();
$last_score = $result['score'] ?? 0;
return [
'attempt_count' => $attempt_count,
'best_score' => $best_score,
'last_score' => $last_score
];
}
// Obtener intentos de evaluación de un usuario en un curso
public function obtenerIntentosEvaluacion($user_id, $course_id) {
$sql = "SELECT qa.*, q.title as quiz_title
FROM quiz_attempts qa
JOIN quizzes q ON q.id = qa.quiz_id
WHERE qa.user_id = ? AND q.course_id = ?
AND qa.submitted_at IS NOT NULL
ORDER BY qa.submitted_at DESC";
$stmt = $this->db->prepare($sql);
$stmt->bind_param('ii', $user_id, $course_id);
$stmt->execute();
return $stmt->get_result();
}
// Obtener detalle de un intento específico
public function obtenerDetalleIntento($attempt_id, $user_id) {
$sql = "SELECT qa.*, q.title as quiz_title
FROM quiz_attempts qa
JOIN quizzes q ON q.id = qa.quiz_id
WHERE qa.id = ? AND qa.user_id = ?";
$stmt = $this->db->prepare($sql);
$stmt->bind_param('ii', $attempt_id, $user_id);
$stmt->execute();
return $stmt->get_result()->fetch_assoc();
}
// Obtener respuestas de un intento
public function obtenerRespuestasIntento($attempt_id) {
$sql = "SELECT a.*, q.question_text, q.question_type, q.points,
o.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 ON o.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";
$stmt = $this->db->prepare($sql);
$stmt->bind_param('i', $attempt_id);
$stmt->execute();
$result = $stmt->get_result();
$answers = [];
while ($row = $result->fetch_assoc()) {
$answers[] = $row;
}
return $answers;
}
// Verificar completación de curso para diploma
public function verificarCompletacionCurso($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();
}
// Obtener quiz con información del curso
public function obtenerQuizConCurso($quiz_id) {
$sql = "SELECT q.*, c.title as course_title, c.limite_at, q.course_id
FROM quizzes q
JOIN courses c ON c.id = q.course_id
WHERE q.id = ?";
$stmt = $this->db->prepare($sql);
$stmt->bind_param('i', $quiz_id);
$stmt->execute();
return $stmt->get_result()->fetch_assoc();
}
// Obtener preguntas de un quiz
public function obtenerPreguntasQuiz($quiz_id) {
$sql = "SELECT * FROM questions WHERE quiz_id = ? ORDER BY id";
$stmt = $this->db->prepare($sql);
$stmt->bind_param('i', $quiz_id);
$stmt->execute();
return $stmt->get_result();
}
// Obtener opciones de una pregunta
public function obtenerOpcionesPregunta($question_id) {
$sql = "SELECT * FROM options WHERE question_id = ? ORDER BY id";
$stmt = $this->db->prepare($sql);
$stmt->bind_param('i', $question_id);
$stmt->execute();
return $stmt->get_result();
}
// Crear intento de quiz
public function crearIntento($quiz_id, $user_id, $attempt_number, $firma) {
$sql = "INSERT INTO quiz_attempts (quiz_id, user_id, attempt_number, firma, started_at)
VALUES (?, ?, ?, ?, NOW())";
$stmt = $this->db->prepare($sql);
$stmt->bind_param('iiis', $quiz_id, $user_id, $attempt_number, $firma);
$stmt->execute();
return $this->db->insert_id;
}
// Obtener una opción por ID
public function obtenerOpcion($option_id) {
$sql = "SELECT is_correct FROM options WHERE id = ?";
$stmt = $this->db->prepare($sql);
$stmt->bind_param('i', $option_id);
$stmt->execute();
return $stmt->get_result()->fetch_assoc();
}
// Guardar respuesta de opción múltiple
public function guardarRespuestaMCQ($attempt_id, $question_id, $selected_option_id, $is_correct, $points) {
$sql = "INSERT INTO answers (attempt_id, question_id, selected_option_id, is_correct, awarded_points)
VALUES (?, ?, ?, ?, ?)";
$ic = $is_correct ? 1 : 0;
$stmt = $this->db->prepare($sql);
$stmt->bind_param('iiiii', $attempt_id, $question_id, $selected_option_id, $ic, $points);
return $stmt->execute();
}
// Guardar respuesta abierta
public function guardarRespuestaAbierta($attempt_id, $question_id, $text) {
$sql = "INSERT INTO answers (attempt_id, question_id, answer_text, is_correct, awarded_points)
VALUES (?, ?, ?, 0, 0)";
$stmt = $this->db->prepare($sql);
$stmt->bind_param('iis', $attempt_id, $question_id, $text);
return $stmt->execute();
}
// Actualizar intento con puntaje final
public function actualizarIntento($attempt_id, $score) {
$sql = "UPDATE quiz_attempts SET submitted_at = NOW(), score = ? WHERE id = ?";
$stmt = $this->db->prepare($sql);
$stmt->bind_param('di', $score, $attempt_id);
return $stmt->execute();
}
/** Contar cursos asignados con filtro de búsqueda */
public function contarCursosAsignados($user_id, $busqueda = '') {
$sql = "SELECT COUNT(*) as total
FROM courses c
JOIN course_assignments ca ON ca.course_id = c.id
WHERE ca.user_id = ?";
if (!empty($busqueda)) {
$busqueda = "%" . $busqueda . "%";
$sql .= " AND (c.title LIKE ? OR c.description LIKE ?)";
}
$stmt = $this->db->prepare($sql);
if (!empty($busqueda)) {
$stmt->bind_param('iss', $user_id, $busqueda, $busqueda);
} else {
$stmt->bind_param('i', $user_id);
}
$stmt->execute();
$res = $stmt->get_result()->fetch_assoc();
return $res['total'];
}
/** Obtener cursos con paginación y búsqueda */
public function obtenerCursosAsignadosPaginados($user_id, $busqueda = '', $limit = 10, $offset = 0) {
$sql = "SELECT c.*, ca.assigned_at
FROM courses c
JOIN course_assignments ca ON ca.course_id = c.id
WHERE ca.user_id = ?";
if (!empty($busqueda)) {
$busqueda = "%" . $busqueda . "%";
$sql .= " AND (c.title LIKE ? OR c.description LIKE ?)";
}
$sql .= " ORDER BY ca.assigned_at DESC LIMIT ? OFFSET ?";
$stmt = $this->db->prepare($sql);
if (!empty($busqueda)) {
$stmt->bind_param('issii', $user_id, $busqueda, $busqueda, $limit, $offset);
} else {
$stmt->bind_param('iii', $user_id, $limit, $offset);
}
$stmt->execute();
return $stmt->get_result();
}
}