Tiny File Manager

Back Back Up Advanced Editor

<?php
// models/Candidato.php

class Candidato {
    private $conn;
    private $table = 'candidatos';

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

// ✅ NUEVO: Obtener historial completo de un candidato por cédula
    public function obtenerHistorialPorCedula($cedula) {
        $query = "SELECT 
                    c.id,
                    c.cedula,
                    c.nombres_apellidos,
                    c.antecedentes,
                    c.celular,
                    c.estado,
                    c.fase_actual,
                    c.fecha_registro,
                    r.codigo as requisicion_codigo,
                    r.nombre_cargo,
                    r.area
                  FROM " . $this->table . " c
                  LEFT JOIN requisiciones r ON c.requisicion_id = r.id
                  WHERE c.cedula = :cedula
                  ORDER BY c.fecha_registro DESC";
        
        $stmt = $this->conn->prepare($query);
        $stmt->bindValue(':cedula', $cedula);
        $stmt->execute();
        return $stmt->fetchAll();
    }

public function crear($datos) {
        // Verificar si el candidato ya existe
        $existe = $this->obtenerPorCedula($datos['cedula']);
        
        if($existe) {
            // Crear alerta de proceso repetido
            $this->crearAlerta($existe['id'], $datos['requisicion_id'], $datos['cedula']);
        }

$query = "INSERT INTO " . $this->table . " 
                  (requisicion_id, cedula, nombres_apellidos, antecedentes, celular)
                  VALUES 
                  (:requisicion_id, :cedula, :nombres_apellidos, :antecedentes, :celular)";

$stmt = $this->conn->prepare($query);
        $stmt->bindValue(':requisicion_id', $datos['requisicion_id']);
        $stmt->bindValue(':cedula', $datos['cedula']);
        $stmt->bindValue(':nombres_apellidos', $datos['nombres_apellidos']);
        $stmt->bindValue(':antecedentes', $datos['antecedentes']);
        $stmt->bindValue(':celular', $datos['celular']);

if($stmt->execute()) {
            return $this->conn->lastInsertId();
        }
        return false;
    }

public function obtenerPorCedula($cedula) {
        $query = "SELECT * FROM " . $this->table . " WHERE cedula = :cedula ORDER BY fecha_registro DESC LIMIT 1";
        $stmt = $this->conn->prepare($query);
        $stmt->bindValue(':cedula', $cedula);
        $stmt->execute();
        return $stmt->fetch();
    }

public function obtenerPorRequisicion($requisicion_id) {
        $query = "SELECT * FROM " . $this->table . " 
                  WHERE requisicion_id = :requisicion_id 
                  ORDER BY fecha_registro DESC";
        $stmt = $this->conn->prepare($query);
        $stmt->bindValue(':requisicion_id', $requisicion_id);
        $stmt->execute();
        return $stmt->fetchAll();
    }

public function obtenerCompleto($id) {
        $query = "SELECT * FROM vista_candidatos_completa WHERE candidato_id = :id";
        $stmt = $this->conn->prepare($query);
        $stmt->bindValue(':id', $id);
        $stmt->execute();
        return $stmt->fetch();
    }

public function obtenerTodosPorFase($fase) {
        $query = "SELECT c.*, r.codigo as requisicion_codigo, r.nombre_cargo, r.area
                  FROM " . $this->table . " c
                  LEFT JOIN requisiciones r ON c.requisicion_id = r.id
                  WHERE c.fase_actual = :fase AND c.estado = 'Activo'
                  ORDER BY c.fecha_registro DESC";
        $stmt = $this->conn->prepare($query);
        $stmt->bindValue(':fase', $fase);
        $stmt->execute();
        return $stmt->fetchAll();
    }

public function actualizarFase($id, $fase) {
        $query = "UPDATE " . $this->table . " SET fase_actual = :fase WHERE id = :id";
        $stmt = $this->conn->prepare($query);
        $stmt->bindValue(':fase', $fase);
        $stmt->bindValue(':id', $id);
        return $stmt->execute();
    }

public function actualizarEstado($id, $estado) {
        $query = "UPDATE " . $this->table . " SET estado = :estado WHERE id = :id";
        $stmt = $this->conn->prepare($query);
        $stmt->bindValue(':estado', $estado);
        $stmt->bindValue(':id', $id);
        return $stmt->execute();
    }

public function obtenerAlertas($cedula) {
        $query = "SELECT a.*, r.codigo, r.nombre_cargo, r.area 
                  FROM alertas_candidato a
                  LEFT JOIN requisiciones r ON a.requisicion_id = r.id
                  WHERE a.cedula = :cedula
                  ORDER BY a.fecha_alerta DESC";
        $stmt = $this->conn->prepare($query);
        $stmt->bindValue(':cedula', $cedula);
        $stmt->execute();
        return $stmt->fetchAll();
    }

private function crearAlerta($candidato_id, $requisicion_id, $cedula) {
        $query = "INSERT INTO alertas_candidato (candidato_id, requisicion_id, cedula, descripcion)
                  VALUES (:candidato_id, :requisicion_id, :cedula, :descripcion)";
        $stmt = $this->conn->prepare($query);
        $stmt->bindValue(':candidato_id', $candidato_id);
        $stmt->bindValue(':requisicion_id', $requisicion_id);
        $stmt->bindValue(':cedula', $cedula);
        $stmt->bindValue(':descripcion', 'Candidato ya procesado anteriormente');
        $stmt->execute();
    }

public function obtenerVecesProceso($cedula) {
        $query = "SELECT COUNT(*) as veces FROM " . $this->table . " WHERE cedula = :cedula";
        $stmt = $this->conn->prepare($query);
        $stmt->bindValue(':cedula', $cedula);
        $stmt->execute();
        $result = $stmt->fetch();
        return $result['veces'];
    }
}
?>