<?php

class Persona extends Conexion {
    /* =============================================
      ACTUALIZAR INFORMACION PERSONAL
      ============================================= */

    public function mdlActualizarPersona($datos) {
        $conectar = parent::conectar();
        parent::set_names();
        if ($datos["personadbk"] == "") {
            $sql = "INSERT INTO " . $datos["tabla"] . "  
                (tipoDocumentoId, 
                identificacion, 
                pnombre, 
                snombre, 
                papellido, 
                sapellido,
                sexoId,
                generoId, 
                estadoCivilId,
                etniaId,
                puebloNacionalidadId,
                tipoSangre,
                lugarNacimiento,
                fechaNacimiento,
                nacionalidad,
                email,
                emailInstitucional,
                numcel,
                contactoEmergencia,
                telefonoContactoEmergencia,
                tipo,
                estado)
                VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);
                ";
            $sql = $conectar->prepare($sql);
            $sql->bindValue(1, $datos["tipoDocumentoId"]);
            $sql->bindValue(2, $datos["identificacion"]);
            $sql->bindValue(3, $datos["pnombre"]);
            $sql->bindValue(4, $datos["snombre"]);
            $sql->bindValue(5, $datos["papellido"]);
            $sql->bindValue(6, $datos["sapellido"]);
            $sql->bindValue(7, $datos["sexoId"]);
            $sql->bindValue(8, $datos["generoId"]);
            $sql->bindValue(9, $datos["estadoCivilId"]);
            $sql->bindValue(10, $datos["etniaId"]);
            $sql->bindValue(11, $datos["puebloNacionalidadId"]);
            $sql->bindValue(12, $datos["tipoSangre"]);
            $sql->bindValue(13, $datos["lugarNacimiento"]);
            $sql->bindValue(14, $datos["fechaNacimiento"]);
            $sql->bindValue(15, $datos["nacionalidad"]);
            $sql->bindValue(16, $datos["email"]);
            $sql->bindValue(17, $datos["emailInstitucional"]);
            $sql->bindValue(18, $datos["numcel"]);
            $sql->bindValue(19, $datos["contactoEmergencia"]);
            $sql->bindValue(20, $datos["telefonoContactoEmergencia"]);
            $sql->bindValue(21, $datos["tipo"]);
            $sql->bindValue(22, "ACTIVO");
            $sql->execute();
        } else {
            $sql = "UPDATE " . $datos["tabla"] . "  
                SET tipoDocumentoId = ?, 
                identificacion = ?, 
                pnombre = ?, 
                snombre = ?, 
                papellido = ?, 
                sapellido = ?,
                sexoId = ?,
                generoId = ?, 
                estadoCivilId = ?,
                etniaId = ?,
                puebloNacionalidadId = ?,
                tipoSangre = ?,
                lugarNacimiento = ?,
                fechaNacimiento = ?,
                nacionalidad = ?,
                email = ?,
                emailInstitucional = ?,
                numcel = ?,
                contactoEmergencia = ?,
                telefonoContactoEmergencia = ?,
                tipo = ?
                WHERE personadbk = ?";
            $sql = $conectar->prepare($sql);
            $sql->bindValue(1, $datos["tipoDocumentoId"]);
            $sql->bindValue(2, $datos["identificacion"]);
            $sql->bindValue(3, $datos["pnombre"]);
            $sql->bindValue(4, $datos["snombre"]);
            $sql->bindValue(5, $datos["papellido"]);
            $sql->bindValue(6, $datos["sapellido"]);
            $sql->bindValue(7, $datos["sexoId"]);
            $sql->bindValue(8, $datos["generoId"]);
            $sql->bindValue(9, $datos["estadoCivilId"]);
            $sql->bindValue(10, $datos["etniaId"]);
            $sql->bindValue(11, $datos["puebloNacionalidadId"]);
            $sql->bindValue(12, $datos["tipoSangre"]);
            $sql->bindValue(13, $datos["lugarNacimiento"]);
            $sql->bindValue(14, $datos["fechaNacimiento"]);
            $sql->bindValue(15, $datos["nacionalidad"]);
            $sql->bindValue(16, $datos["email"]);
            $sql->bindValue(17, $datos["emailInstitucional"]);
            $sql->bindValue(18, $datos["numcel"]);
            $sql->bindValue(19, $datos["contactoEmergencia"]);
            $sql->bindValue(20, $datos["telefonoContactoEmergencia"]);
            $sql->bindValue(21, $datos["tipo"]);
            $sql->bindValue(22, $datos["personadbk"]);
            $sql->execute();
        }

        return $resultado = $sql->fetchAll();
    }

    public function mdlActualizarEstado($datos) {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "UPDATE " . $datos["tabla"] . "  
                SET estado = ?
                WHERE personadbk = ?";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $datos["estado"]);
        $sql->bindValue(2, $datos["personadbk"]);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }

    public function Consultar($fil) {
        $conectar = parent::conectar();
        parent::set_names();
        switch ($fil) {
            case "all":
                $sql = "SELECT * FROM persona";
                break;
            case "H":
                $sql = "SELECT * FROM persona where sexoId = 9";
                break;
            case "M":
                $sql = "SELECT * FROM persona where sexoId = 10";
                break;
            case "D":
                $sql = "SELECT * from persona 
                        INNER JOIN discapacidad ON personadbk = dis_personadbk
                        INNER JOIN catalogo ON tipoDiscapacidadId = catalogodbk
                        WHERE tipoDiscapacidadId != 89";
                break;
        }
        $sql = $conectar->prepare($sql);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }

    public function Resumen() {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "SELECT prov_descripcion as provincia, can_descripcion as canton, count(*) as total 
                FROM persona 
                INNER JOIN residencia ON res_personadbk = personadbk 
                INNER JOIN pais ON paisId = res_pais 
                INNER JOIN provincia ON prov_codigo = res_provincia 
                INNER JOIN canton ON can_codigo = res_canton 
                GROUP BY provincia, canton 
               ";
        $sql = $conectar->prepare($sql);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }

    public function Residencia() {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "SELECT pnombre,snombre,papellido,sapellido,identificacion,prov_descripcion,can_descripcion,res_parroquia, personadbk
                FROM persona 
                INNER JOIN residencia ON res_personadbk = personadbk 
                INNER JOIN pais ON paisId = res_pais 
                INNER JOIN provincia ON prov_codigo = res_provincia 
                INNER JOIN canton ON can_codigo = res_canton  
               ";
        $sql = $conectar->prepare($sql);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }

    public function ResidenciaE() {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "SELECT pnombre,snombre,papellido,sapellido,identificacion,pais_descripcion,res_provincia,res_canton, personadbk
                FROM persona 
                INNER JOIN residencia ON res_personadbk = personadbk 
                INNER JOIN pais ON res_pais = paisId
                WHERE res_pais != 56
               ";
        $sql = $conectar->prepare($sql);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }

    public function Consultar_por_ID($personadbk) {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "
                SELECT * FROM persona                 
                LEFT JOIN academico ON aca_personadbk = personadbk
                LEFT JOIN discapacidad ON dis_personadbk = personadbk
                LEFT JOIN residencia ON res_personadbk = personadbk
                LEFT JOIN procedencia ON proc_personadbk = personadbk
                WHERE personadbk = ?
                ";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $personadbk);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }

    public function Total_Hombres() {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "SELECT count(*) as hombres FROM persona WHERE sexoId = 9";
        $sql = $conectar->prepare($sql);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }

    public function Total_Mujeres() {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "SELECT count(*) as mujeres FROM persona WHERE sexoId = 10";
        $sql = $conectar->prepare($sql);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }

    public function Eliminar($personadbk) {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "DELETE FROM persona WHERE personadbk = ?";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $personadbk);
        $sql->execute();
        $sql = "DELETE FROM academico WHERE aca_personadbk = ?";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $personadbk);
        $sql->execute();
        $sql = "DELETE FROM discapacidad WHERE dis_personadbk = ?";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $personadbk);
        $sql->execute();
        $sql = "SELECT * FROM matricula WHERE estudiante = ?";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $personadbk);
        $sql->execute();
        $row = $sql->fetchAll();
        foreach ($row as $dato) {
            $sql = "DELETE FROM detalle WHERE matricula = ?";
            $sql = $conectar->prepare($sql);
            $sql->bindValue(1, $dato["matriculadbk"]);
            $sql->execute();
        }
        $sql = "DELETE FROM matricula WHERE estudiante = ?";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $personadbk);
        $sql->execute();
        $sql = "DELETE FROM procedencia WHERE proc_personadbk = ?";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $personadbk);
        $sql->execute();
        $sql = "DELETE FROM residencia WHERE res_personadbk = ?";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $personadbk);
        $sql->execute();
        $sql = "DELETE FROM titulacion WHERE estudiante = ?";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $personadbk);
        $sql->execute();
        $sql = "DELETE FROM usuario WHERE persona = ?";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $personadbk);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }

    public function GraficoEstadoCivil($sexo) {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "SELECT count(*)personas,catalogo.descripcion FROM persona
                INNER JOIN catalogo ON persona.estadoCivilId = catalogo.catalogodbk
                AND persona.sexoId = ?
                AND persona.estado = 'ACTIVO'
                GROUP BY estadoCivilId";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $sexo);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }

    public function GraficoEdad($sexo) {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "SELECT count(*) as cantidad, YEAR(CURDATE())-YEAR(fechaNacimiento) AS edad 
                FROM persona
                WHERE persona.estado='ACTIVO'
                AND persona.sexoId = ?
                GROUP BY edad";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $sexo);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }

    public function GraficoEtnia($sexo) {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "SELECT count(*) AS cantidad, catalogo.descripcion AS etnia 
                FROM persona
                INNER JOIN catalogo ON catalogo.catalogodbk = persona.etniaId
                WHERE persona.estado='ACTIVO'
                AND persona.sexoId = ?
                GROUP BY etnia";
        $sql = $conectar->prepare($sql);
        $sql->bindValue(1, $sexo);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }

    public function GraficoProvincias() {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "SELECT count(*) AS cantidad, prov_descripcion AS provincia
                FROM residencia
                INNER JOIN provincia ON provinciaId = res_provincia
                GROUP BY provincia";
        $sql = $conectar->prepare($sql);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }

    public function GraficoCantones() {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "SELECT count(*) AS cantidad, can_descripcion AS canton
                FROM residencia
                INNER JOIN canton ON can_codigo = res_canton
                GROUP BY canton";
        $sql = $conectar->prepare($sql);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }

    public function ComboPersona() {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "SELECT * FROM persona where estado = 'ACTIVO' order by papellido";
        $sql = $conectar->prepare($sql);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }

    public function ComboDocente() {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "SELECT * FROM persona 
                INNER JOIN usuario ON personadbk = persona
                where usuario.estado = 'ACTIVO' and perfil = 'DOCENTE' order by papellido";
        $sql = $conectar->prepare($sql);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }

    public function ComboEstudiante() {
        $conectar = parent::conectar();
        parent::set_names();
        $sql = "SELECT * FROM persona 
                INNER JOIN usuario ON personadbk = persona
                where usuario.estado = 'ACTIVO' and perfil = 'ESTUDIANTE' order by papellido";
        $sql = $conectar->prepare($sql);
        $sql->execute();
        return $resultado = $sql->fetchAll();
    }
    public function Select($search) 
    {
        $conect = parent::conectar();

        $sql = "SELECT * FROM persona 
                WHERE papellido LIKE '%".$search."%' OR sapellido LIKE '%".$search."%' 
                OR pnombre LIKE '%".$search."%' OR snombre LIKE '%".$search."%'
                LIMIT 10";

        $sql = $conect->prepare($sql);  

        $sql->execute();

        return $sql->fetchAll();
    }

}

?>
