<?php
class MallaCurricular extends Conexion {
    public function Malla_por_ID($mallaId) {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "
                SELECT * FROM mallacurricular 
                WHERE mallacurriculardbk = ?
                ";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $mallaId);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }
    public function PlataformasMatricula($carrera,$asignatura,$periodo) {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "                
                SELECT CONCAT(papellido,' ',sapellido,' ',pnombre,' ',snombre)as docente, chat, video  FROM mallacurricular,persona
                WHERE carrera=?
                AND asignatura=?
                AND periodo = ?
                AND docente=personadbk
                ";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $carrera);
        $sql->bindValue(2, $asignatura);
        $sql->bindValue(3, $periodo);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }
    public function Consultar($fil, $m) {
        $conectar = parent::conectar();
        parent::set_names();
        switch ($fil) {
            //CONSULTA LA MALLACURRICULAR SEGUN MALLACURRICULARDBK
            case "mc":
                $sql = "SELECT * FROM mallacurricular WHERE mallacurriculardbk =?";
                $sql = $conectar->prepare($sql);
                $sql->bindValue(1, $m);
                $sql->execute();
                $resultado = $sql->fetchAll();
                foreach ($resultado as $row) {
                    $periodo = $row["periodo"];
                    $carrera = $row["carrera"];
                }
                $sql = "SELECT mallacurriculardbk,codigo,nivel,asignatura.descripcion as asignatura,creditos,mallacurricular.tipo as tipo,periodo.descrip as periodo,
                        cd,cp,ca,CONCAT(persona.papellido,' ',persona.sapellido,' ',persona.pnombre,' ',persona.snombre) as docente,calificar
                        FROM mallacurricular 
                        INNER JOIN asignatura ON asignaturadbk = asignatura
                        INNER JOIN periodo ON periodbk = periodo
                        LEFT JOIN persona ON personadbk = docente
                        WHERE periodo =? and carrera =? and mallacurricular.estado = 'ACTIVO'";
                break;
            case "pc":
                //CONSULTAR PERIODO Y CARRERA
                $sql = "SELECT mallacurricular.periodo as periodo,mallacurricular.carrera as carrera,carrera.descripcion as descripcion,
                        catalogo.descripcion as modalidad, periodo.descrip as descrip 
                        FROM mallacurricular 
                        INNER JOIN carrera ON carreradbk = carrera
                        LEFT JOIN catalogo ON catalogodbk = carrera.modalidad
                        INNER JOIN periodo ON periodbk = mallacurricular.periodo
                        WHERE mallacurriculardbk =?";
                break;
            case "confi":
                //CONFIGURACION DE MALLA CURRICULAR POR CARRERA - PERIODO                
                $sql = "";
                break;
            case "percar":
                //Listar Malla Curricular por Periodo - Carrera y Modalidad
                $sql = "SELECT periodbk,mallacurriculardbk,descrip,carrera.descripcion as descripcion,catalogo.descripcion as modalidad 
                        FROM mallacurricular
                        INNER JOIN carrera ON carreradbk = carrera
                        INNER JOIN periodo ON periodbk = mallacurricular.periodo
                        LEFT JOIN catalogo ON catalogodbk = carrera.modalidad
                        GROUP BY periodo, carreradbk
                        ORDER BY descripcion,periodbk";
                break;
            case "pla":                
                $sql = "SELECT mallacurriculardbk, asignatura.descripcion, chat, video,CONCAT(papellido+' '+sapellido+' '+pnombre+' '+snombre)as docente,carrera.descripcion as carrera
                        FROM mallacurricular,asignatura,persona,carrera
                        WHERE docente = ? AND periodo = ?
                        AND asignatura = asignaturadbk
                        AND docente = personadbk
                        AND carrera = carreradbk";
                break;           
            case "plaEst":                
                $sql = "SELECT asignatura,codcar
                        FROM matricula,detalle
                        WHERE matriculadbk = matricula
                        AND estudiante = ?
                        AND matricula.periodo = ?
                        AND detalle.estado='ACTIVO'
			AND detalle.tipo = 1";
                break;           
        }
        $sql = $conectar->prepare($sql);
        if ($fil == "mc") {
            $sql->bindValue(1, $periodo);
            $sql->bindValue(2, $carrera);
        }
        if ($fil == "pc") {
            $sql->bindValue(1, $m);
        }
        if (($fil == "pla") ||($fil == "plaEst")) {
            $sql->bindValue(1, $_SESSION["personadbk"]);
            $sql->bindValue(2, $_SESSION["periodbk"]);
        }
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }
    public function ConsultarEstDetalle($asignatura, $carrera) {
        $conectar = parent::conectar();
        parent::set_names();       
        $sql = "SELECT 
                mallacurriculardbk,asignatura.descripcion as asignatura,carrera.descripcion as carrera,
                concat(papellido,' ',sapellido,' ',pnombre,' ',snombre)as docente,
                chat,
                video
                FROM mallacurricular, asignatura, carrera,persona
                WHERE carrera = carreradbk
                AND docente = personadbk
                AND asignatura = asignaturadbk
                AND asignatura = ?
                AND carrera = ?
                AND periodo = ?
                ";                                 
        $sql = $conectar->prepare($sql);        
        $sql->bindValue(1, $asignatura);
        $sql->bindValue(2, $carrera);
        $sql->bindValue(3, $_SESSION["periodbk"]);        
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }
    public function mdlActualizarMalla($datos) {
        $conectar = parent::conectar();
        parent::set_names();
        if ($datos["mallacurriculardbk"] == "") {
            $sql = "INSERT INTO " . $datos["tabla"] . "  
                (asignatura,nivel,periodo,carrera,creditos,tipo,codigo,estado,paralelo,itinerario,calificar,cd,cp,ca) 
                VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?);
                ";
            $sql = $conectar->prepare($sql);
            $sql->bindValue(1, $datos["asignatura"]);
            $sql->bindValue(2, $datos["nivel"]);
            $sql->bindValue(3, $datos["periodo"]);
            $sql->bindValue(4, $datos["carrera"]);
            $sql->bindValue(5, $datos["creditos"]);
            $sql->bindValue(6, $datos["tipo"]);
            $sql->bindValue(7, $datos["codigo"]);
            $sql->bindValue(8, "ACTIVO");
            $sql->bindValue(9, "A");
            $sql->bindValue(10, "NA");
            $sql->bindValue(11, "ACTIVO");
            $sql->bindValue(12, $datos["cd"]);
            $sql->bindValue(13, $datos["cp"]);
            $sql->bindValue(14, $datos["ca"]);
            $sql->execute();
        } else {
            $sql = "UPDATE " . $datos["tabla"] . " SET
                asignatura = ?, nivel = ?, periodo = ?, carrera = ?, creditos = ?, tipo = ?, codigo = ?,
                cd = ?, cp = ?, ca = ?
                WHERE mallacurriculardbk = ?;
                ";
            $sql = $conectar->prepare($sql);
            $sql->bindValue(1, $datos["asignatura"]);
            $sql->bindValue(2, $datos["nivel"]);
            $sql->bindValue(3, $datos["periodo"]);
            $sql->bindValue(4, $datos["carrera"]);
            $sql->bindValue(5, $datos["creditos"]);
            $sql->bindValue(6, $datos["tipo"]);
            $sql->bindValue(7, $datos["codigo"]);
            $sql->bindValue(8, $datos["cd"]);
            $sql->bindValue(9, $datos["cp"]);
            $sql->bindValue(10, $datos["ca"]);
            $sql->bindValue(11, $datos["mallacurriculardbk"]);
            $sql->execute();
        }
        return $resultado = $sql->fetchAll();
    }
    public function mdlActualizarPlataforma($datos) {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "UPDATE " . $datos["tabla"] . " SET 
            chat = ?, video = ? 
            WHERE mallacurriculardbk = ?;
            ";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $datos["chat"]);
        $sql->bindValue(2, $datos["video"]);        
        $sql->bindValue(3, $datos["mallacurriculardbk"]);
        $sql->execute();        
        return $resultado = $sql->fetchAll();
    }
    public function mdlActualizarAsignar($datos) {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "UPDATE " . $datos["tabla"] . " SET docente = ? 
                WHERE mallacurriculardbk = ?;
                ";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $datos["docente"]);        
        $sql->bindValue(2, $datos["mallacurriculardbk"]);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }

    public function mdlNuevaMalla($datos) {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "INSERT INTO " . $datos["tabla"] . "  
                (asignatura,nivel,periodo,carrera,creditos,tipo,codigo,estado,paralelo,itinerario,calificar) 
                VALUES (?,?,?,?,?,?,?,?,?,?,?);
                ";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $datos["asignatura"]);
        $sql->bindValue(2, $datos["nivel"]);
        $sql->bindValue(3, $datos["periodo"]);
        $sql->bindValue(4, $datos["carrera"]);
        $sql->bindValue(5, $datos["creditos"]);
        $sql->bindValue(6, $datos["tipo"]);
        $sql->bindValue(7, $datos["codigo"]);
        $sql->bindValue(8, "ACTIVO");
        $sql->bindValue(9, "A");
        $sql->bindValue(10, "NA");
        $sql->bindValue(11, "ACTIVO");
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }

    public function Eliminar_por_ID($mallaCurricularId) {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "
                DELETE FROM mallacurricular WHERE mallacurriculardbk = ?
                ";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $mallaCurricularId);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }
    public function mdlActualizarCalificar($datos) {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "UPDATE " . $datos["tabla"] . "  
                 SET calificar = ? 
                WHERE mallacurriculardbk = ?";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $datos["estado"]);
        $sql->bindValue(2, $datos["mallacurriculardbk"]);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }

    public function mdlListarMallaPorCarreraGS($carrera) {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "SELECT codigo,descripcion,nivel,asignatura,creditos,itinerario,asignaturadbk 
                FROM mallacurricular,asignatura
                WHERE carrera = ? 
                AND paralelo = 'A' 
                AND mallacurricular.estado = 'ACTIVO' 
                AND asignatura = asignaturadbk 
                AND asignatura NOT IN (99,19,8,427,429,431)
                GROUP BY codigo,descripcion,nivel,asignatura ";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $carrera);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }    

    public function mdlListarMallaPorCarreraRE($carrera){           
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "SELECT codigo,descripcion,nivel,asignatura,creditos,itinerario,asignaturadbk 
                FROM mallacurricular,asignatura 
                WHERE carrera = ? 
                AND paralelo = 'A' 
                AND mallacurricular.estado='ACTIVO' 
                AND asignatura = asignaturadbk 
                AND asignatura NOT IN (99,19,8,426,428,430) 
                GROUP BY codigo,descripcion,nivel,asignatura";            
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $carrera);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }    

    public function mdlListarMallaPorCarreraNA($carrera) {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "SELECT codigo,descripcion,nivel,asignatura,creditos,itinerario,asignaturadbk 
                FROM mallacurricular,asignatura
                WHERE carrera = ? 
                AND paralelo='A' 
                AND mallacurricular.estado='ACTIVO' 
                AND asignatura=asignaturadbk 
                AND itinerario = 'NA'
                GROUP BY codigo,descripcion,nivel,asignatura";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $carrera);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }   
    
    public function mdlListarMallaPorCarreraTRI($carrera) {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "SELECT codigo,descripcion,nivel,asignatura,creditos,itinerario,asignaturadbk 
                FROM mallacurricular,asignatura
                WHERE carrera = ? 
                AND paralelo='A' 
                AND mallacurricular.estado='ACTIVO' 
                AND asignatura=asignaturadbk 
                AND asignatura NOT IN (22,19,300,435,438,441)
                GROUP BY codigo,descripcion,nivel,asignatura ";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $carrera);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }    
    
    public function mdlListarMallaPorCarreraCOS($carrera) {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "SELECT codigo,descripcion,nivel,asignatura,creditos,itinerario,asignaturadbk 
                FROM mallacurricular,asignatura
                WHERE carrera = ? 
                AND paralelo='A' 
                AND mallacurricular.estado='ACTIVO' 
                AND asignatura=asignaturadbk 
                AND asignatura NOT IN (22,19,300,434,437,400)
                GROUP BY codigo,descripcion,nivel,asignatura ";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $carrera);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }

    public function mdlListarMallaPorCarrera($carrera) {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "SELECT codigo,descripcion,nivel,asignatura,creditos,itinerario,asignaturadbk 
                FROM mallacurricular,asignatura
                WHERE carrera = ? 
                AND paralelo = 'A' 
                AND mallacurricular.estado = 'ACTIVO' 
                AND asignatura = asignaturadbk 
                GROUP BY codigo,descripcion,nivel,asignatura";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $carrera);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }
}
?>