Tiny File Manager

Back Back Up Advanced Editor

<?php
// Modelos/AsignacionModelo.php

class AsignacionModelo {
    private $db;
    private $db_externa;

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

public function obtenerHistorial($limit, $offset) {
        $query = "SELECT ca.id, u.name as user_name, u.cedula, u.ccosto, c.title as course_title, ca.assigned_at
                  FROM course_assignments ca 
                  JOIN users u ON u.id = ca.user_id 
                  JOIN courses c ON c.id = ca.course_id 
                  ORDER BY ca.id DESC LIMIT $limit OFFSET $offset";
        return $this->db->query($query);
    }

public function obtenerCentrosDeCosto() {
        $query = "SELECT DISTINCT ccosto, descripcion FROM empleados WHERE ccosto IS NOT NULL ORDER BY ccosto ASC";
        return $this->db_externa->query($query);
    }

public function obtenerEmpleadosPorCCosto($ccosto) {
        $stmt = $this->db_externa->prepare("SELECT cedula, nombre, ccosto FROM empleados WHERE ccosto = ?");
        $stmt->bind_param("s", $ccosto);
        $stmt->execute();
        return $stmt->get_result();
    }

public function yaEstaAsignado($usuario_id, $curso_id) {
        $stmt = $this->db->prepare("SELECT id FROM course_assignments WHERE user_id = ? AND course_id = ?");
        $stmt->bind_param("ii", $usuario_id, $curso_id);
        $stmt->execute();
        return $stmt->get_result()->num_rows > 0;
    }

public function registrarAsignacion($usuario_id, $curso_id) {
        $stmt = $this->db->prepare("INSERT INTO course_assignments (user_id, course_id, assigned_at) VALUES (?, ?, NOW())");
        $stmt->bind_param("ii", $usuario_id, $curso_id);
        return $stmt->execute();
    }
	
	/** Contar el total de asignaciones para la paginación */
	public function contarTotal() {
		$sql = "SELECT COUNT(*) as total FROM course_assignments";
		$resultado = $this->db->query($sql);
		$fila = $resultado->fetch_assoc();
		return $fila['total'] ?? 0;
	}	
	
	public function borrar($id) {
        $stmt = $this->db->prepare("DELETE FROM course_assignments WHERE id = ?");
        $stmt->bind_param("i", $id);
        return $stmt->execute();
    }
	
	public function buscarCentrosDeCosto($term) {
		$like = "%$term%";
		$stmt = $this->db_externa->prepare(
			"SELECT DISTINCT ccosto, descripcion FROM empleados 
			 WHERE ccosto LIKE ? OR descripcion LIKE ? 
			 ORDER BY ccosto ASC
			 LIMIT 30"
		);
		$stmt->bind_param("ss", $like, $like);
		$stmt->execute();
		return $stmt->get_result();
	}

public function buscarEmpleadosNomina($term) {
		$like = "%$term%";
		$stmt = $this->db_externa->prepare(
			"SELECT cedula, nombre, apellido FROM empleados 
			 WHERE cedula LIKE ? OR nombre LIKE ? OR apellido LIKE ? 
			 LIMIT 20"
		);
		$stmt->bind_param("sss", $like, $like, $like);
		$stmt->execute();
		return $stmt->get_result();
	}
}