<?php
class Exportar extends Conexion {
    public function ConsultarPersona() {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "SELECT 
                personadbk,matriculadbk,
                CASE tipoDocumentoId WHEN 7 THEN 1 WHEN 8 THEN 2 WHEN 0 THEN 'NA' END AS tipoDocumentoId,
                identificacion,papellido,sapellido,pnombre,snombre,
                CASE sexoId WHEN 9 THEN 1 WHEN 10 THEN 2 WHEN 0 THEN 'NA' END AS sexoId,
                CASE generoId WHEN 11 THEN 1 WHEN 12 THEN 2 WHEN 0 THEN 'NA' END AS generoId,
                CASE estadocivilId WHEN 13 THEN 1 WHEN 14 THEN 2 WHEN 15 THEN 3 WHEN 16 THEN 4 WHEN 17 THEN 5 WHEN 0 THEN 'NA' END AS estadocivilId,
                CASE etniaId WHEN 18 THEN 1 WHEN 19 THEN 2 WHEN 20 THEN 3 WHEN 21 THEN 4 WHEN 22 THEN 5 WHEN 23 THEN 6 WHEN 24 THEN 7 WHEN 25 THEN 8 WHEN 26 THEN 9 WHEN 0 THEN 'NA' END AS etniaId,
                CASE puebloNacionalidadId WHEN 27 THEN 1 WHEN 28 THEN 2 WHEN 29 THEN 3 WHEN 30 THEN 4 WHEN 31 THEN 5 WHEN 32 THEN 6 WHEN 33 THEN 7 WHEN 34 THEN 8 WHEN 35 THEN 9 WHEN 36 THEN 10 
                    WHEN 37 THEN 11 WHEN 38 THEN 12 WHEN 39 THEN 13 WHEN 40 THEN 14 WHEN 41 THEN 15 WHEN 42 THEN 16 WHEN 43 THEN 17 WHEN 44 THEN 18 WHEN 45 THEN 19 WHEN 46 THEN 20 WHEN 47 THEN 21
                    WHEN 48 THEN 22 WHEN 49 THEN 23 WHEN 50 THEN 24 WHEN 51 THEN 25 WHEN 52 THEN 26 WHEN 53 THEN 27 WHEN 54 THEN 28 WHEN 55 THEN 29 WHEN 56 THEN 30 WHEN 57 THEN 31 WHEN 58 THEN 32 
                    WHEN 59 THEN 33 WHEN 60 THEN 34 WHEN 0 THEN 'NA' END AS puebloNacionalidadId,
                CASE tipoSangre WHEN 61 THEN 1 WHEN 62 THEN 2 WHEN 63 THEN 3 WHEN 64 THEN 4 WHEN 65 THEN 5 WHEN 66 THEN 6 WHEN 67 THEN 7 WHEN 68 THEN 8 WHEN 0 THEN 'NA' END AS tipoSangre,
                CASE discapacidadId WHEN 76 THEN 1 WHEN 77 THEN 2 WHEN 0 THEN 'NA' END AS discapacidad,
                IF(porcentajeDiscapacidad=0, 'NA', porcentajeDiscapacidad) AS porcentajeDiscapacidad,
                numCarnetConadis,
                CASE tipoDiscapacidadId WHEN 83 THEN 1 WHEN 84 THEN 2 WHEN 85 THEN 3 WHEN 86 THEN 4 WHEN 87 THEN 5 WHEN 88 THEN 6 WHEN 89 THEN 7 END AS tipoDiscapacidad,
                fechaNacimiento,emailInstitucional,numcel              
                FROM persona          
                INNER JOIN matricula ON estudiante = personadbk
                LEFT JOIN discapacidad ON dis_personadbk = personadbk
                WHERE matricula.estado = 'ACTIVO'";
                //AND matricula.periodo = 31";
        $sql = $conectar->prepare($sql);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }
    public function ConsultarProcedencia($personadbk) {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "SELECT * FROM procedencia
                INNER JOIN pais ON proc_pais = pais_codigo
                WHERE proc_personadbk = ?";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $personadbk);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }
    public function ConsultarResidencia($personadbk) {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "SELECT * FROM residencia
                INNER JOIN pais ON res_pais = pais_codigo
                WHERE res_personadbk = ?";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $personadbk);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }    
    public function ConsultarAcademico($personadbk) {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "SELECT 
                CASE tipoColegioId WHEN 70 THEN 1 WHEN 71 THEN 2 WHEN 72 THEN 3 WHEN 73 THEN 4 WHEN 74 THEN 5 WHEN 75 THEN 6 WHEN 0 THEN 'NA' END AS tipoColegioId
                FROM academico
                WHERE aca_personadbk = ?";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $personadbk);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }  
    public function ConsultarMatricula($personadbk,$matriculadbk) {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "SELECT 
                matriculadbk, estudiante, codcar, periodo, estado, fechacrea, DATE_FORMAT(MIN(fecha), '%Y-%m-%d ')as fecha, itinerario
                FROM matricula
                WHERE estudiante = ? and matriculadbk = ?";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $personadbk);
        $sql->bindValue(2, $matriculadbk);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }    
    public function ConsultarInicioCarrera($personadbk) {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "SELECT DATE_FORMAT(MIN(fecha), '%Y-%m-%d ')as fecha FROM matricula
                WHERE estudiante = ?";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $personadbk);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }  
    public function ConsultarNivelAcademicoQueCursa($personadbk,$matriculadbk) {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "SELECT MIN(mallacurricular.nivel) AS nivel  FROM
                matricula, mallacurricular, detalle 
                WHERE matricula.matriculadbk = detalle.matricula
                AND matricula.codcar = mallacurricular.carrera
                AND detalle.asignatura = mallacurricular.asignatura
                AND matricula.estado = 'ACTIVO'
                AND mallacurricular.estado = 'ACTIVO'
                AND estudiante = ?
                AND matriculadbk = ?
                ";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $personadbk);
        $sql->bindValue(2, $matriculadbk);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }    
    public function ConsultarHaRepetidoAlMenosUnaMateria($personadbk,$matriculadbk) {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "SELECT asignatura, count(*)AS cantidad FROM
                matricula, detalle
                WHERE matriculadbk = detalle.matricula                
                AND estudiante = ?
                AND matricula = ?
                GROUP BY asignatura
                ";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $personadbk);
        $sql->bindValue(2, $matriculadbk);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }    
}
?>