<?php

/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/

/**
* Description of User
*
* @author Developer
*/
class Inf_por_instancias_registrados_sin_participar extends MY_Model
{
    
    protected $_table;
    protected $_table_parent;
    protected $_table_parent2;
    protected $_table_audit;
    private $_column;
    private $_order;
    private $_instancia;
    
    public function __construct()
    {
        parent::__construct();
        $this->load->database();
        $this->_table         = 'evi05partic';
        $this->_table_parent  = 'gen04ciudad';
        $this->_table_parent2 = 'gen01agencias';
        $this->_table_audit = 'evi07audpro';
        $this->_table_ingre = 'evi09ingre';
        $this->primary_key    = 'NUM_IDETER';
        $this->_column        = array('evi05partic.NUM_IDETER',
        'evi05partic.TEX_NOMPAR', 'gen04ciudad.TEX_NOMCIU', 'gen01agencias.TEX_NOMAGE');
        $this->_order         = array('NUM_IDETER' => 'desc');
        $this->_agencia_usuario = $this->session->userdata('COD_AGENCIA');
    }

    public function consulta_instancia_auditoria($instancia)
    {
        $this->db->select("$this->_table_audit.ID_INSTAN");
        $this->db->from($this->_table_audit);
        $this->db->where("$this->_table_audit.ID_INSTAN", $instancia);
        return $this->db->get()->row();

    }

    public function instancias_con_votos($estado=1, $is_delete=0)
    {
        $this->db->select("$this->_table_audit.ID_INSTAN");
        $this->db->from($this->_table_audit);
        $this->db->group_by('ID_INSTAN');
        $instancias_con_votos_result = $this->db->get()->result();

        if(is_null($instancias_con_votos_result)){
         return false;
        }

        $arr_instancias = [];
        foreach ($instancias_con_votos_result as $key => $instancia) {
            $arr_instancias[] = $instancia->ID_INSTAN;
        }

        return $arr_instancias;
    }
    
    private function _get_datatables_query($instancia=null)
    {

        $this->_instancia = $instancia;

        //Evalua si la instancia posee algun voto en la tabla de auditoria. 
        $existe_instancia = $this->consulta_instancia_auditoria($this->_instancia);

        $instancias_con_votos = $this->instancias_con_votos();

        $instancias_con_votos = implode(',',$instancias_con_votos);

        $where = "WHERE ID_INSTAN IN($instancias_con_votos)";
        if(is_null($existe_instancia)){
           $where = ''; 
        }


        $this->db->select("$this->_table.*, $this->_table_parent.TEX_NOMCIU, $this->_table_parent2.TEX_NOMAGE, 
        count($this->_table_ingre.NUM_IDETER) as ingresos");
        $this->db->from($this->_table);
        $this->db->join($this->_table_parent, "$this->_table_parent.COD_CIUDAD=$this->_table.COD_CIUDAD", 'INNER');
        $this->db->join($this->_table_parent2, "$this->_table_parent2.COD_AGENCIA=$this->_table.COD_AGENCIA", 'INNER');
        $this->db->join($this->_table_audit, "$this->_table_audit.NUM_IDETER=$this->_table.NUM_IDETER", 'LEFT');
        $this->db->join($this->_table_ingre, "$this->_table_ingre.NUM_IDETER=$this->_table.NUM_IDETER", 'LEFT');
        
        $this->db->where("$this->_table.IS_DELETE", 0);
        $this->db->where("$this->_table.BOL_ACTIVO", 1);
        
        $this->db->where("evi05partic.NUM_IDETER NOT IN (SELECT NUM_IDETER FROM evi07audpro $where)", NULL, FALSE);
        $this->db->where("evi05partic.NUM_IDETER IN (SELECT NUM_IDETER FROM evi09ingre)", NULL, FALSE);
        $this->db->where("$this->_table.BOL_ACTIVO", 1);
        $this->db->where("$this->_table_parent2.COD_AGENCIA !=", 0);
        if ($this->_agencia_usuario !== 0) {
            $this->db->where("$this->_table_parent2.COD_AGENCIA", $this->_agencia_usuario);
        }

        $this->db->group_by("$this->_table.NUM_IDETER");
        $this->db->having('ingresos %2=', 1, false);
        
        $i = 0;
        foreach ($this->_column as $item) {
            if (!empty($_POST['search']['value'])) {
                $search = $_POST['search']['value']; 
                $paren = '';
                if($i==3) {
                    $paren=')'; 				
                }
                
                ($i === 0) ? 
                $this->db->where("($item LIKE '%$search%'"): $this->db->or_where("$item LIKE '%$search%'$paren");
            }
            
            $column[$i] = $item;
            $i++;
        }
        if (isset($_POST['order'])) {
            $this->db->order_by($column[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
        }
        else if (isset($this->_order)) {
            $order = $this->_order;
            $this->db->order_by(key($order), $order[key($order)]);
        }
    }
    
    public function get_datatables($instancia)
    {
        $this->_get_datatables_query($instancia);
        if(isset($_POST['length'])){
            if ($_POST['length'] != -1):
                $this->db->limit($_POST['length'], $_POST['start']);
            endif;
        }
        $query = $this->db->get();
        return $query->result();
    }
    
    public function count_filtered()
    {
        $this->_get_datatables_query($this->_instancia);
        $query = $this->db->get();
        return $query->num_rows();
    }
    
    public function count_all()
    {

        
        $existe_instancia = $this->consulta_instancia_auditoria($this->_instancia);
        
        $instancias_con_votos = $this->instancias_con_votos();

        $instancias_con_votos = implode(',',$instancias_con_votos);

        $where = "WHERE ID_INSTAN IN($instancias_con_votos)";
        if(is_null($existe_instancia)){
           $where = ''; 
        }


        $this->db->select("$this->_table.*, $this->_table_parent.TEX_NOMCIU, $this->_table_parent2.TEX_NOMAGE");
        $this->db->from($this->_table);
        $this->db->join($this->_table_parent, "$this->_table_parent.COD_CIUDAD=$this->_table.COD_CIUDAD");
        $this->db->join($this->_table_parent2, "$this->_table_parent2.COD_AGENCIA=$this->_table.COD_AGENCIA");
        
        $this->db->join($this->_table_audit, "$this->_table_audit.NUM_IDETER=$this->_table.NUM_IDETER", 'LEFT');
        
        
        $this->db->where("$this->_table.IS_DELETE", 0);
        $this->db->where("$this->_table.BOL_ACTIVO", 1);
        $this->db->where("evi05partic.NUM_IDETER NOT IN (SELECT NUM_IDETER FROM evi07audpro $where)", NULL, FALSE);
        $this->db->where("evi05partic.NUM_IDETER IN (SELECT NUM_IDETER FROM evi09ingre)", NULL, FALSE);
        
        $this->db->where("$this->_table.BOL_ACTIVO", 1);
        
        
        $this->db->where("$this->_table_parent2.COD_AGENCIA !=", 0);
        if ($this->_agencia_usuario !== 0) {
            $this->db->where("$this->_table_parent2.COD_AGENCIA", $this->_agencia_usuario);
        }
        return $this->db->count_all_results();
    }
    
}
