Tiny File Manager

Back Back Up Advanced Editor

<?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();
	}
}