<?php
class SGI {
    private static $sistema = 8;
    public static $foto_pefil = 'nico.jpg';
/********* GENERALES **********/
    public static function periodoValido($pdo, $idnivel){
        $periodo = array();
        try {
            $stmt = $pdo->prepare('SELECT "Periodo_id", "Periodo_desc" from fs_periodo(null, 1, :nivel,null) WHERE "Periodo_calendario_activo" IS TRUE');
            $stmt->bindParam(':nivel', $idnivel);
            if(!$stmt->execute())
                print_r($stmt->errorInfo());
            else 
                $periodo = $stmt->fetch();
        } catch (\PDOException $ex) {}
        $stmt->closeCursor();
        $stmt = null;
        return $periodo;
    }
    public static function claveEsSuperAdmin($pdo, $clave){
        $admin = false;
        try {
            $stmt = $pdo->prepare('SELECT * FROM fs_usuario_administradorxclave(:clave)');
            $stmt->bindParam(':clave', $clave);
            if($stmt->execute()){
                $temp = $stmt->fetch();
                if (is_bool($temp))
                    $admin = $temp;
                else
                    $admin = boolval($temp['SGI_administrador']);
            }
        } catch (\PDOException $ex) {}
        $stmt->closeCursor();
        $stmt = null;
        return $admin;
    }
    public static function esAdministrativo($pdo, $id){
        $admin = array();
        try {
            $stmt = $pdo->prepare('SELECT UPPER("UsuarioEstado_desc") AS "estatus" FROM fs_usuario(:id)');
            $stmt->bindParam(':id', $id);
            if($stmt->execute()){
                $temp = $stmt->fetch();
                if (!is_bool($temp))
                    $admin = $temp;
            }
        } catch (\PDOException $ex) {}
        $stmt->closeCursor();
        $stmt = null;
        return $admin;
    }
    public static function usuarioTieneAcceso($pdo, $id){        
        $admin = false;
        try {
            $stmt = $pdo->prepare('SELECT * FROM fs_validasistema(:id, :sistema)');
            $stmt->bindParam(':id', $id);
            $stmt->bindParam(':sistema', self::$sistema);
            if($stmt->execute()){
                $temp = $stmt->fetch();
                if (is_bool($temp))
                    $admin = $temp;
                else
                    $admin = boolval($temp['permiso']);
            }
        } catch (\PDOException $ex) {}
        $stmt->closeCursor();
        $stmt = null;
        return $admin;
    }
    public static function getRol($pdo, $nom_rol){
        $rol = '';
        try {
            $stmt = $pdo->prepare('SELECT "RolUsuario_id" FROM fs_rolusuario(:sistema,null) WHERE UPPER("RolUsuario_desc") = \'' . $nom_rol . '\'');
            $stmt->bindParam(':sistema', self::$sistema);
            if($stmt->execute()){
                $temp = $stmt->fetch();
                $rol = $temp['RolUsuario_id'];
            }
        } catch (\PDOException $ex) {}
        $stmt->closeCursor();
        $stmt = null;
        return $rol;
    }
    public static function getValidacion($pdo, $nom_rol){
        $rol = '';
        try {
            $stmt = $pdo->prepare('SELECT "RolUsuario_id" FROM fs_rolusuario(:sistema,null) WHERE UPPER("RolUsuario_desc") = \'' . $nom_rol . '\'');
            $stmt->bindParam(':sistema', self::$sistema);
            if($stmt->execute()){
                $temp = $stmt->fetch();
                $rol = $temp['RolUsuario_id'];
            }
        } catch (\PDOException $ex) {}
        $stmt->closeCursor();
        $stmt = null;
        return $rol;
    }
    
/********* ALUMNOS **********/
    public static function getAlumnos($pdo){
        $alumnos = array();
        try {
            $stmt = $pdo->prepare('SELECT DISTINCT "Usuario_id" AS id, "Usuario_nombre" AS nombre, "Usuario_apellidos" AS apellidos FROM fs_alumno(null, null) ORDER BY apellidos, nombre');
            if($stmt->execute())
                $alumnos = $stmt->fetchAll();
        } catch (\PDOException $ex) {}
        $stmt->closeCursor();
        $stmt = null;
        return $alumnos;
    }
    public static function buscaAlumno($pdo, $idalumno, $clave){
        $alumno = array();
        try {
            if ($idalumno == 0){
                $stmt = $pdo->prepare('SELECT * FROM fs_alumno(NULL,:clave)');
                $stmt->bindParam(':clave', $clave);
            } else {
                $stmt = $pdo->prepare('SELECT * FROM fs_alumno(:id,null)');
                $stmt->bindParam(':id', $idalumno);
            }
            if($stmt->execute())
                $alumno = $stmt->fetch();
        } catch (\PDOException $ex) {}
        $stmt->closeCursor();
        $stmt = null;
        return $alumno;
    }    
    public static function validacionDatosAlumno($pdo, $idalumno) {
        $validando = false;
        try {
            $stmt = $pdo->prepare('SELECT * FROM fs_checkusuario_estado(:Usuario_id)');
            $stmt->bindParam(':Usuario_id', $idalumno);
            if($stmt->execute()){
                $temp = $stmt->fetch();
                if (isset($temp['CheckUsuario_Estado_id']) && $temp['CheckUsuario_Estado_id'] == 1)
                    $validando = true;

            }
        } catch (\PDOException $ex) {}
        $stmt->closeCursor();
        $stmt = null;
        return $validando;
    }
    public static function checkDatosAlumno($pdo, $idalumno) {
        $datos = array();
        try {
            $stmt = $pdo->prepare('SELECT "Usuario_nombre_new", "Usuario_apellidos_new", "Usuario_curp_new" FROM fs_checkusuario(1,null,null) WHERE "Usuario_id" = ' . $idalumno);
            if($stmt->execute())
                $datos = $stmt->fetch();
        } catch (\PDOException $ex) {}
        $stmt->closeCursor();
        $stmt = null;
        return $datos;
    }

/*** Datos Personales ***/
    public static function nivelAlumno($pdo, $idalumno) {
        $nivel = array();
        try {
            $stmt = $pdo->prepare('SELECT "Nivel_id", "EstadoAlumno_id" from fs_alumno(:Usuario_id, NULL) ORDER BY "Usuario_claveULSA" DESC');
            $stmt->bindParam(':Usuario_id', $idalumno);
            if($stmt->execute())
                $nivel = $stmt->fetch();
        } catch (\PDOException $ex) {}
        $stmt->closeCursor();
        $stmt = null;
        return $nivel;
    }
    public static function clavesAlumno($pdo, $idalumno){
        $claves = array();
        try {
            $stmt = $pdo->prepare('SELECT "Usuario_nombre", "Usuario_apellidos", "Usuario_curp", "Usuario_claveULSA", "Carrera_desc", "EstadoAlumno_id" FROM fs_alumno(:Usuario_id, NULL) ORDER BY "Usuario_claveULSA" DESC');
            $stmt->bindParam(':Usuario_id', $idalumno);
            if($stmt->execute())
                $claves = $stmt->fetchAll();
        } catch (\PDOException $ex) {}
        $stmt->closeCursor();
        $stmt = null;
        return $claves;
    }
    public static function fotoAlumno($pdo, $idalumno) {
        $foto = self::$foto_pefil;
        try {
            $stmt = $pdo->prepare('SELECT "Usuario_foto" FROM fs_alumno(:Usuario_id, NULL) ORDER BY "Usuario_claveULSA" LIMIT 1');
            $stmt->bindParam(':Usuario_id', $idalumno);
            if($stmt->execute()){
                $temp = $stmt->fetch();
                if(isset($temp['Usuario_foto']) && !is_null($temp['Usuario_foto']))
                    $foto = $temp['Usuario_foto'];
            }
        } catch (\PDOException $ex) {}
        $stmt->closeCursor();
        $stmt = null;
        return $foto;
    }
    public static function semblanzaAlumno($pdo, $idalumno) {
        $semblanza = '';
        try {
            $stmt = $pdo->prepare('SELECT * FROM fs_usuariosemblanza(:Usuario_id)');
            $stmt->bindParam(':Usuario_id', $idalumno);
            if($stmt->execute()){
                $temp = $stmt->fetch();
                if(isset($temp['Usuario_semblanza']))
                    $semblanza = $temp['Usuario_semblanza'];
            }
        } catch (\PDOException $ex) {}
        $stmt->closeCursor();
        $stmt = null;
        return $semblanza;
    }
    public static function actualizaDatosAlumno($pdo, $nombre, $apellidos, $curp, $idalumno){
        $exito = false;
        try {
            $stmt = $pdo->prepare('SELECT * FROM fi_checkusuario(:Usuario_id,\'t\',null,:Nombre,:Apellidos,:CURP,null,null)');
            $stmt->bindParam(':Nombre', $nombre);
            $stmt->bindParam(':Apellidos', $apellidos);
            $stmt->bindParam(':CURP', $curp);
            $stmt->bindParam(':Usuario_id', $idalumno);
            if($stmt->execute())
                $exito = true;
        } catch (\PDOException $ex) {}
        $stmt->closeCursor();
        $stmt = null;
        return $exito;
    }
    public static function actualizaFotoAlumno($pdo, $idalumno, $foto=null){
        $exito = false;
        try {
            if (is_null($foto))
                $stmt = $pdo->prepare('SELECT * FROM fu_usuariofoto(:Usuario_id, NULL)');
            else {
                $stmt = $pdo->prepare('SELECT * FROM fu_usuariofoto(:Usuario_id, :foto)');
                $stmt->bindParam(':foto', $foto);
            }
            $stmt->bindParam(':Usuario_id', $idalumno);
            if($stmt->execute())
                $exito = true;
        } catch (\PDOException $ex) {}
        $stmt->closeCursor();
        $stmt = null;
        return $exito;
    }
    public static function actualizaSemblanzaAlumno($pdo, $acerca, $idalumno){
        $exito = false;
        try {
            $stmt = $pdo->prepare('SELECT * FROM fu_usuariosemblanza(:Usuario_id,:Acerca)');
            $stmt->bindParam(':Acerca', $acerca);
            $stmt->bindParam(':Usuario_id', $idalumno);
            if($stmt->execute())
                $exito = true;
        } catch (\PDOException $ex) {}
        $stmt->closeCursor();
        $stmt = null;
        return $exito;
    }
    
/********* REDES **********/
    public static function contactosAlumno($pdo, $idalumno){
        $contactos = array();
        try {
            $stmt = $pdo->prepare('SELECT "Contacto_id", "Contacto_valor", "TipoContacto_id", "PerfilContacto_desc", "SubtipoContacto_desc" from fs_contacto(:Usuario_id, null, null) WHERE "Contacto_activo" IS TRUE ORDER BY "TipoContacto_id"');
            $stmt->bindParam(':Usuario_id', $idalumno);
            if($stmt->execute())
                $contactos = $stmt->fetchAll();
        } catch (\PDOException $ex) {}
        $stmt->closeCursor();
        $stmt = null;
        return $contactos;
    }
    public static function getSubTipoContacto($pdo, $idtipocontacto){
        $subtipo = array();
        try {
            $stmt = $pdo->prepare('SELECT * FROM fs_subtipocontacto(:TipoContacto,null) ORDER BY "SubtipoContacto_desc"');
            $stmt->bindParam(':TipoContacto', $idtipocontacto);
            if($stmt->execute())
                $subtipo = $stmt->fetchAll();
        } catch (\PDOException $ex) {}
        $stmt->closeCursor();
        $stmt = null;
        return $subtipo;
    }
    public static function emergenciaAlumno($pdo, $idalumno){
        $contactos = array();
        try {
            $stmt = $pdo->prepare('SELECT * from fs_contactoemergencia(:Usuario_id) ORDER BY "ContactoEmergencia_nombre"');
            $stmt->bindParam(':Usuario_id', $idalumno);
            if($stmt->execute())
                $contactos = $stmt->fetchAll();
        } catch (\PDOException $ex) {}
        $stmt->closeCursor();
        $stmt = null;
        return $contactos;
    }
    public static function getRedes($pdo){
        $redes = array();
        try {
            $stmt = $pdo->prepare('SELECT * FROM fs_tipocontacto(null) ORDER BY "TipoContacto_desc"');
            if($stmt->execute())
                $redes = $stmt->fetchAll();
        } catch (\PDOException $ex) {}
        $stmt->closeCursor();
        $stmt = null;
        return $redes;
    }    
    public static function eliminaContacto($pdo, $idalumno, $idcontacto){
        $status = false;
        try {
            $stmt = $pdo->prepare('SELECT * FROM fd_contacto(:Usuario_id, :Red_id)');
            $stmt->bindParam(':Red_id', $idcontacto);
            $stmt->bindParam(':Usuario_id', $idalumno);
            if($stmt->execute())
                $status = true;
        } catch (\PDOException $ex) {}
        $stmt->closeCursor();
        $stmt = null;
        return $status;
    }
    public static function eliminaContactoEmergencia($pdo, $idalumno){
        $status = false;
        try {
            $stmt = $pdo->prepare('SELECT * FROM fd_contactoemergencia(:Usuario_id)');
            $stmt->bindParam(':Usuario_id', $idalumno);
            if($stmt->execute())
                $status = true;
        } catch (\PDOException $ex) {}
        $stmt->closeCursor();
        $stmt = null;
        return $status;
    }
    public static function agregaContacto($pdo, $red, $dato, $uso, $idalumno, $subtipostatus, $subtipo){
        $exito = false;
        try {
            if ($_POST['subtipostatus']) {
                $stmt = $pdo->prepare('SELECT * FROM fi_contacto(:Dato,:Red,:Usuario_id,:Uso,:Subtipo)');
                $stmt->bindParam(':Subtipo', $_POST['subtipo']);
            } else
                $stmt = $pdo->prepare('SELECT * FROM fi_contacto(:Dato,:Red,:Usuario_id,:Uso,null)');
            $stmt->bindParam(':Red', $_POST['red']);
            $stmt->bindParam(':Dato', $_POST['dato']);
            $stmt->bindParam(':Uso', $_POST['uso']);
            $stmt->bindParam(':Usuario_id', $idalumno);
            if($stmt->execute())
                $exito = true;
        } catch (\PDOException $ex) {}
        $stmt->closeCursor();
        $stmt = null;
        return $exito;
    }
    public static function agregaContactoEmergencia($pdo, $nombre, $dato, $idalumno){
        $exito = false;
        try {
            $stmt = $pdo->prepare('SELECT * FROM fi_contactoemergencia(:Usuario_id,:Nombre,:Dato)');
            $stmt->bindParam(':Dato', $dato);
            $stmt->bindParam(':Nombre', $nombre);
            $stmt->bindParam(':Usuario_id', $idalumno);
            if($stmt->execute())
                $exito = true;
        } catch (\PDOException $ex) {}
        $stmt->closeCursor();
        $stmt = null;
        return $exito;
    }

/********* HORARIOS **********/
    public static function getDias($pdo){
        $dias = array();
        try {
            $stmt = $pdo->prepare('SELECT * from fs_dia(NULL)');
            if($stmt->execute())
                $dias = $stmt->fetchAll();
        } catch (\PDOException $ex) {}
        $stmt->closeCursor();
        $stmt = null;
        return $dias;
    }
    public static function getHorarioGrupo($pdo, $grupo){
        $horario = array();
        try {
            $stmt = $pdo->prepare('SELECT * from fs_horariogrupo(:gpo, NULL, false)');
            $stmt->bindParam(':gpo', $grupo);
            if($stmt->execute())
                $horario = $stmt->fetchAll();
        } catch (\PDOException $ex) {}
        $stmt->closeCursor();
        $stmt = null;
        return $horario;
    }    
    public static function getProfesorHorarioGrupo($pdo, $grupo){
        $horario = array();
        try {
            $stmt = $pdo->prepare('SELECT * from fs_profesorhorariogrupo(:gpo)');
            $stmt->bindParam(':gpo', $grupo);
            if($stmt->execute())
                $horario = $stmt->fetchAll();
        } catch (\PDOException $ex) {}
        $stmt->closeCursor();
        $stmt = null;
        return $horario;
    }
    public static function getSubMateriaHorarioGrupo($pdo, $grupo){
        $horario = array();
        try {
            $stmt = $pdo->prepare('SELECT * from fs_submateriahorariogrupo(:gpo)');
            $stmt->bindParam(':gpo', $grupo);
            if($stmt->execute())
                $horario = $stmt->fetchAll();
        } catch (\PDOException $ex) {}
        $stmt->closeCursor();
        $stmt = null;
        return $horario;
    }
    public static function getSubMateriaHorarioGrupoProfesor($pdo, $grupo, $sub){
        $horario = array();
        try {
            $stmt = $pdo->prepare('SELECT * from fs_submateriahorariogrupoprofesor(:gpo,:sub)');
            $stmt->bindParam(':gpo', $grupo);
            $stmt->bindParam(':sub', $sub);
            if($stmt->execute())
                $horario = $stmt->fetchAll();
        } catch (\PDOException $ex) {}
        $stmt->closeCursor();
        $stmt = null;
        return $horario;
    }
    public static function getHorarioProfesor($pdo, $usr, $fecha){
        $horario = array();
        try {
            $stmt = $pdo->prepare('SELECT * from fs_mihorariofull(:user, :fecha, 3)');
            $stmt->bindParam(':user', $usr);
            $stmt->bindParam(':fecha', $fecha);
            if($stmt->execute())
                $horario = $stmt->fetchAll();
        } catch (\PDOException $ex) {}
        $stmt->closeCursor();
        $stmt = null;
        return $horario;
    }
    public static function getExtraordinarios($pdo, $periodo, $semestre, $plan){
        $horario = array();
        try {
            $stmt = $pdo->prepare('SELECT * FROM fs_examenextraordinario( NULL, :per, :semestre, :plan_estudio)');
            $stmt->bindParam(":per", $periodo);
            $stmt->bindParam(":semestre", $semestre);
            $stmt->bindParam(":plan_estudio", $plan);
            if($stmt->execute())
                $horario = $stmt->fetchAll();
        } catch (\PDOException $ex) {}
        $stmt->closeCursor();
        $stmt = null;
        return $horario;
    }
    public static function getGrupos($pdo, $periodo){
        $grupos = array();
        try {
            $stmt = $pdo->prepare('SELECT * from fs_grupo(NULL, :periodo)');
            $stmt->bindParam(':periodo', $periodo);
            if($stmt->execute())
                $grupos = $stmt->fetchAll();
        } catch (\PDOException $ex) {}
        $stmt->closeCursor();
        $stmt = null;
        return $grupos;
    }
    public static function getDocentes($pdo, $periodo){
        $docentes = array();
        try {
            $stmt = $pdo->prepare('SELECT * FROM fs_profesoresperiodo_busca(:periodo, null, null, 0, null)');
            $stmt->bindParam(':periodo', $periodo);
            if($stmt->execute())
                $docentes = $stmt->fetchAll();
        } catch (\PDOException $ex) {}
        $stmt->closeCursor();
        $stmt = null;
        return $docentes;
    }
    public static function getPlanes($pdo){
        $planes = array();
        try {
            $stmt = $pdo->prepare('SELECT * FROM fs_planestudio(1, NULL, NULL, true, 0, NULL)');
            if($stmt->execute())
                $planes = $stmt->fetchAll();
        } catch (\PDOException $ex) {}
        $stmt->closeCursor();
        $stmt = null;
        return $planes;
    }

/********* INSIGNIAS **********/
    public static function getInsignias($pdo, $user){
        $insigias = array();
        try {
            $stmt = $pdo->prepare('SELECT * FROM fs_insigniareportealumno(:Usuario_id)');
            $stmt->bindParam(':Usuario_id', $user);
            if($stmt->execute())
                $insigias = $stmt->fetchAll();
        } catch (\PDOException $ex) {}
        $stmt->closeCursor();
        $stmt = null;
        return $insigias;
    }
}