<?php

class Matricula extends Conexion {

    public function Matricula_por_ID($matriculaId) {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "
                SELECT * FROM matricula                  
                WHERE matriculadbk = ?
                ";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $matriculaId);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }
    public function PlataformasMatricula($periodo) {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "                
                SELECT carreradbk,asignaturadbk,carrera.codigo as codigo, carrera.descripcion as carrera, asignatura.descripcion as asignatura 
                FROM matricula,detalle,carrera,asignatura
                WHERE matriculadbk = matricula
                AND matricula.periodo = ?
                AND matricula.codcar=carreradbk
                AND detalle.asignatura = asignaturadbk
                AND matricula.estado='ACTIVO'
                AND detalle.estado='ACTIVO'
                GROUP BY asignatura
                ORDER BY codigo
                ";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $periodo);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }
    public function mdlConsultarUltimaMatriculaPorEstudiante($personadbk,$carrera) {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "
                SELECT codcar,descripcion,periodo,descrip 
                FROM matricula,carrera,periodo 
                WHERE periodo = periodbk AND codcar = carreradbk 
                AND estudiante = ?
                AND codcar = ?
                AND periodo = (SELECT MAX(periodo) 
                FROM matricula,carrera,periodo 
                WHERE periodo = periodbk AND codcar = carreradbk 
				AND estudiante = ?
				AND codcar = ?)
                ";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $personadbk);
        $sql->bindValue(2, $carrera);
        $sql->bindValue(3, $personadbk);
        $sql->bindValue(4, $carrera);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    } 
    
    public function mdlBuscarItinerario($estudiante, $carrera, $itinerario) {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "SELECT count(*)total
                FROM matricula
                WHERE estudiante = ? and codcar = ? and itinerario =?";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $estudiante);
        $sql->bindValue(2, $carrera);
        $sql->bindValue(3, $itinerario);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    } 
    
    public function MatriculaDetalle_por_ID($matriculaId) {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "
                SELECT matriculadbk,estudiante,CONCAT(papellido,' ',sapellido,' ',pnombre,' ',snombre)as nombre,
                codcar,carrera.descripcion as carrera,periodbk, periodo.descrip as periodo,catalogo.descripcion as tipo,
                itinerario,fecha
                FROM matricula                  
                INNER JOIN persona ON personadbk = estudiante
                INNER JOIN carrera ON carreradbk = codcar
                INNER JOIN periodo ON periodbk = periodo
                LEFT JOIN catalogo ON catalogodbk = matricula.tipo
                WHERE matriculadbk = ?
                ";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $matriculaId);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }

    public function Eliminar_por_ID($matriculaId) {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "
                DELETE from detalle where matricula = ?
                ";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $matriculaId);
        $sql->execute();
        $sql = "
                DELETE from matricula where matriculadbk = ?
                ";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $matriculaId);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }

    public function Consultar($fil) {
        $conectar = parent::conectar();
        parent::set_names();
        switch ($fil) {
            case "all":
                $sql = "SELECT * FROM matricula Order by matriculadbk";
                break;
            case "dt":
                $sql = "SELECT matriculadbk, 
                        CONCAT(papellido,' ',sapellido,' ',pnombre,' ',snombre)as estudiante, 
                        carrera.descripcion as carrera, 
                        periodo.descrip as periodo, 
                        matricula.estado 
                        FROM matricula 
                        INNER JOIN persona ON personadbk = estudiante 
                        INNER JOIN carrera ON carreradbk = codcar 
                        INNER JOIN periodo ON periodbk = periodo 
                    ";
                break;
            case "M":
                $sql = "";
                break;
            case "D":
                $sql = "";
                break;
        }
        $sql = $conectar->prepare($sql);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }

    public function ConsultarAportes() {
        $conectar = parent::conectar();
        parent::set_names();        
        $sql = "SELECT matriculadbk,carrera.descripcion as carrera,periodo.descrip as periodo,periodbk
                        FROM matricula 
                        INNER JOIN persona ON personadbk = estudiante
                        INNER JOIN carrera ON carreradbk = codcar 
                        INNER JOIN periodo ON periodbk = periodo 
                        WHERE personadbk = ".$_SESSION['personadbk']."
                        ORDER BY periodbk";
        $sql = $conectar->prepare($sql);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }

    public function mdlActualizarMatricula($datos) {
        $conectar = parent::conectar();
        parent::set_names();
        if ($datos["matriculadbk"] == "") {
            $sql = "INSERT INTO " . $datos["tabla"] . "  
                (estudiante,codcar,periodo,estado,fecha,itinerario,tipo)
                VALUES (?,?,?,?,?,?,?);
                ";
            $sql = $conectar->prepare($sql);
            $sql->bindValue(1, $datos["estudiante"]);
            $sql->bindValue(2, $datos["codcar"]);
            $sql->bindValue(3, $datos["periodo"]);
            $sql->bindValue(4, 'ACTIVO');
            $sql->bindValue(5, $datos["fecha"]);
            $sql->bindValue(6, $datos["itinerario"]);
            $sql->bindValue(7, $datos["tipo"]);

            $sql->execute();
        } else {
            $sql = "UPDATE " . $datos["tabla"] . " SET
                periodo = ?, fecha = ?, itinerario = ?, tipo = ? 
                WHERE matriculadbk = ?;
                ";
            $sql = $conectar->prepare($sql);
            $sql->bindValue(1, $datos["periodo"]);
            $sql->bindValue(2, $datos["fecha"]);
            $sql->bindValue(3, $datos["itinerario"]);
            $sql->bindValue(4, $datos["tipo"]);
            $sql->bindValue(5, $datos["matriculadbk"]);
            $sql->execute();
        }
        return $resultado = $sql->fetchAll();
    }

    public function ConsultarFiltro($carreradbk) {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "SELECT personadbk, CONCAT(papellido,' ',sapellido,' ',pnombre,' ',snombre)as nombre 
                FROM matricula
                INNER JOIN persona ON personadbk = estudiante
                WHERE codcar = ?
                GROUP BY nombre
                ORDER BY nombre";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $carreradbk);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }

    public function mdlListarCalificaciones($estudiante, $asignatura, $carrera) {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "SELECT max(nfinal)as nfinal,aprada,detalle.tipo as tipo,codper,periodbk
                FROM matricula,detalle,periodo
                WHERE estudiante = ? 
                AND matricula.estado = 'ACTIVO' 
                AND detalle.estado = 'ACTIVO' 
                AND matricula = matriculadbk 
                AND asignatura = ? 
                AND codcar = ? 
                AND matricula.periodo = periodo.periodbk";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $estudiante);
        $sql->bindValue(2, $asignatura);
        $sql->bindValue(3, $carrera);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }
    
    public function mdlListarCalificacionesConReprobados($estudiantedbk,$asignatura,$carrera) {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "SELECT nfinal,aprada,detalle.tipo as tipo,codper,periodbk
                FROM matricula,detalle,periodo
                WHERE estudiante = ? 
                AND matricula.estado='ACTIVO' 
                AND detalle.estado='ACTIVO' 
                AND matricula=matriculadbk 
                AND asignatura=?
                AND codcar=? 
                AND matricula.periodo = periodo.periodbk";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $estudiantedbk);
        $sql->bindValue(2, $asignatura);
        $sql->bindValue(3, $carrera);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }
    
    public function MdlListarNumeroMatriculasPorAsignatura($estudiante,$asignatura,$carrera,$periodo) {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "SELECT count(*) AS numtr
                FROM matricula,detalle 
                WHERE matricula.matriculadbk = detalle.matricula
                AND matricula.codcar = ?
                AND detalle.asignatura = ?
                AND matricula.estudiante = ?
                AND matricula.estado = 'ACTIVO'
                AND detalle.estado = 'ACTIVO'
                AND matricula.periodo <= ?
        ";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $carrera);
        $sql->bindValue(2, $asignatura);
        $sql->bindValue(3, $estudiante);
        $sql->bindValue(4, $periodo);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }
    
    public function MdlListarPeriodosMatriculados($carrera, $estudiante) {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "SELECT periodbk,descrip 
                FROM matricula,periodo
                WHERE matricula.estado = 'ACTIVO'
                AND matricula.periodo = periodo.periodbk
                AND matricula.codcar = ?
                AND matricula.estudiante = ?
                GROUP BY descrip
                ORDER BY periodbk";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $carrera);
        $sql->bindValue(2, $estudiante);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    } 

    public function MdlListarCreditosAprobadosPorPeriodo($carrera,$estudiante,$periodo) {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "SELECT periodbk,descrip,sum(creditos)AS creditos 
                FROM matricula,periodo,detalle,mallacurricular
                WHERE matricula.estado = 'ACTIVO'
                AND matricula.periodo = periodo.periodbk
                AND matricula.codcar = ?
                AND matricula.estudiante = ?
                AND matricula.matriculadbk = detalle.matricula
                AND detalle.estado = 'ACTIVO'
                AND nfinal >= 7
                AND mallacurricular.carrera = matricula.codcar
                AND mallacurricular.periodo = periodo.periodbk
                AND mallacurricular.asignatura = detalle.asignatura
                AND mallacurricular.periodo = ?
                GROUP BY descrip
                ORDER BY periodbk";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $carrera);
        $sql->bindValue(2, $estudiante);
        $sql->bindValue(3, $periodo);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    } 

    public function MdlListarCreditosNoAprobadosPorPeriodo($carrera,$estudiante,$periodo) {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "SELECT periodbk,descrip,sum(creditos)as creditosna 
                FROM matricula,periodo,detalle,mallacurricular
                WHERE matricula.estado = 'ACTIVO'
                AND matricula.periodo = periodo.periodbk
                AND matricula.codcar = ?
                AND matricula.estudiante = ?
                AND matricula.matriculadbk = detalle.matricula
                AND detalle.estado = 'ACTIVO'
                AND nfinal < 7
                AND mallacurricular.carrera = matricula.codcar
                AND mallacurricular.periodo = periodo.periodbk
                AND mallacurricular.asignatura = detalle.asignatura
                AND mallacurricular.periodo = ?
                GROUP BY descrip
                ORDER BY periodbk";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $carrera);
        $sql->bindValue(2, $estudiante);
        $sql->bindValue(3, $periodo);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }
    
    public function mdlListarTablaMatriculaPorDocente($carrera,$asignatura,$paralelo,$periodo){
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "
           SELECT concat(papellido,' ',sapellido,' ',pnombre,' ',snombre)as nombre,n11,n12,n13,n14,nfinal
           FROM matricula,detalle,persona
           WHERE matriculadbk=matricula 
           AND personadbk=estudiante 
           AND codcar = ?
           AND asignatura = ?
           AND periodo = ?
           AND paralelo = ?
           AND detalle.tipo = 1 
           ORDER BY nombre";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $carrera);
        $sql->bindValue(2, $asignatura);
        $sql->bindValue(3, $periodo);
        $sql->bindValue(4, $paralelo);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }     
}

?>
