<?php if (! defined('BASEPATH')) exit('No direct script access allowed');

class Mgeneral extends CI_Model
{
    public function get_data($campos, $tabla = false, $where = false, $orden = false)
    {
        $this->db->select($campos, false);
        if ($tabla != false)
            $this->db->from($tabla);
        if ($where != false) {
            foreach ($where as $w) {
                $this->db->where($w);
            }
        }
        if ($orden != false)
            $this->db->order_by($orden);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        }
        return array();
    }
    public function actualizar($tabla, $where, $data)
    {
        $this->db->where($where);
        $this->db->update($tabla, $data);
        return $this->db->affected_rows();
    }
    public function insertar($tabla, $data)
    {
        $this->db->insert($tabla, $data);
        return $this->db->affected_rows();
    }
    public function get_fecha()
    {
        $this->db->select("
            now() as fecha
        ", false);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            $result = $query->result();
            return $result[0]->fecha;
        }
        return array();
    }
    public function ultimo_id()
    {
        $this->db->select("
            last_insert_id() as id
        ", false);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            $result = $query->result();
            return $result[0]->id;
        }
        return false;
    }
    public function get_dres()
    {
        $this->db->select("*", false);
        $this->db->from("dre,estados");
        $this->db->where("dre_esta_ide = esta_ide");
        $this->db->order_by("esta_ide asc,dre_nombre asc");
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        }
        return array();
    }
    public function get_ugels($dre)
    {
        $this->db->select("*", false);
        $this->db->from("ugel,estados");
        $this->db->where("ugel_esta_ide = esta_ide");
        $this->db->where("ugel_dre_ide = $dre");
        $this->db->order_by("esta_ide asc,ugel_nombre asc");
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        }
        return array();
    }
    public function get_admins($ugel_ide)
    {
        $this->db->select("*", false);
        $this->db->from("usuarios,estados");
        $this->db->where("usua_esta_ide = esta_ide");
        $this->db->where("usua_ugel_ide = $ugel_ide");
        $this->db->where("usua_tipo_nombre = 'ADMIN'");
        $this->db->order_by("esta_ide asc,usua_user asc");
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        }
        return array();
    }
    public function get_usuarios($buscar, $ini, $fin)
    {
        $ugel_ide = $this->session->userdata("ugel_ide");
        $this->db->select("*", false);
        $this->db->from("usuarios u");
        $this->db->where("usua_ugel_ide = $ugel_ide");
        $this->db->where("usua_tipo_nombre = 'USUARIO'");
        $this->db->where("
            (
            usua_user like '%$buscar%'
            or
            usua_nombres like '%$buscar%'
            or
            usua_apellidos like '%$buscar%'
            or
            usua_correo like '%$buscar%'
            or
            usua_cell like '%$buscar%'
            )
        ");
        $this->db->limit($fin, $ini);
        $this->db->order_by("usua_apellidos,usua_nombres");
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        }
        return array();
    }
    public function tabla_data($anio, $ugel_ide)
    {
        $sql = "
            CREATE TABLE `data_" . $ugel_ide . "_" . $anio . "` (
                `data_ide` INT(11) NOT NULL AUTO_INCREMENT,
                `data_ugel_ide` INT(11) NULL DEFAULT NULL,
                `data_usua_ide` INT(11) NULL DEFAULT NULL,
                `data_mes` INT(11) NULL DEFAULT NULL,
                `data_boleta` TEXT NULL,
                `data_form_ide` INT(11) NULL DEFAULT NULL,
                `data_esta_ide` INT(11) NULL DEFAULT NULL,
                `data_freg` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (`data_ide`),
                INDEX `fk_data" . $anio . "_estados" . $ugel_ide . "_idx` (`data_esta_ide`),
                INDEX `fk_data" . $anio . "_formas" . $ugel_ide . "_idx` (`data_form_ide`),
                INDEX `fk_data" . $anio . "_ugel" . $ugel_ide . "_idx` (`data_ugel_ide`),
                INDEX `fk_data" . $anio . "_usuarios" . $ugel_ide . "_idx` (`data_usua_ide`),
                CONSTRAINT `fk_data" . $anio . "_estados" . $ugel_ide . "` FOREIGN KEY (`data_esta_ide`) REFERENCES `estados` (`esta_ide`) ON UPDATE NO ACTION ON DELETE NO ACTION,
                CONSTRAINT `fk_data" . $anio . "_formas" . $ugel_ide . "` FOREIGN KEY (`data_form_ide`) REFERENCES `formas` (`form_ide`) ON UPDATE NO ACTION ON DELETE NO ACTION,
                CONSTRAINT `fk_data" . $anio . "_ugel" . $ugel_ide . "` FOREIGN KEY (`data_ugel_ide`) REFERENCES `ugel` (`ugel_ide`) ON UPDATE NO ACTION ON DELETE NO ACTION,
                CONSTRAINT `fk_data" . $anio . "_usuarios" . $ugel_ide . "` FOREIGN KEY (`data_usua_ide`) REFERENCES `usuarios` (`usua_ide`) ON UPDATE NO ACTION ON DELETE NO ACTION
            )
            COLLATE='utf8_general_ci'
            ENGINE=InnoDB
            AUTO_INCREMENT=1
            ;
        ";
        $query = $this->db->query($sql);
        return $this->db->affected_rows();
    }
    public function get_tablas($anio)
    {
        $this->db->select("TABLE_NAME", false);
        $this->db->from("information_schema.TABLES");
        $this->db->like('Table_Name', $anio);
        $this->db->where("table_schema = 'eboleta'");
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        }
        return array();
    }
    public function get_boleta($tabla)
    {
        $this->db->select("
            *
        ", false);
        $this->db->from("
            $tabla t,ugel u,formas f
        ");
        $user = $this->session->userdata("usua_user");
        $user = substr($user, 2, 8);
        $this->db->where("t.data_esta_ide = 1");
        $this->db->where("t.data_ugel_ide = u.ugel_ide");
        $this->db->where("t.data_form_ide = f.form_ide");
        $this->db->like('t.data_boleta', $user);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        }
        return array();
    }
    public function get_boleta_pdf($data_ide, $tabla)
    {
        $this->db->select("
            *
        ", false);
        $this->db->from("
            $tabla
        ");
        $this->db->where("data_ide", $data_ide);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        }
        return array();
    }
}
