<?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_personas_registradas extends MY_Model
 {

     protected $_table;
     protected $_table_parent;
     protected $_table_parent2;
     private $_column;
     private $_order;

     public function __construct()
     {
         parent::__construct();
         $this->load->database();
         $this->_table         = 'evi05partic';
         $this->_table_parent  = 'gen04ciudad';
         $this->_table_parent2 = 'gen01agencias';
         $this->primary_key    = 'NUM_IDETER';
         $this->_column        = array('evi05partic.NUM_IDETER', 'evi05partic.TEX_NOMPAR',
           'gen04ciudad.TEX_NOMCIU', 'gen01agencias.TEX_NOMAGE','usr.TEX_USUARIO');
         $this->_order         = array('evi05partic.NUM_IDETER' => 'desc');
     }

  private function _get_datatables_query()
{
    // --- subquery para tomar el ÚLTIMO registro por participante (según ID_INGRE) ---
    $join_ing = "(SELECT i.NUM_IDETER, i.COD_CREAUSU
                  FROM evi09ingre i
                  JOIN (
                        SELECT NUM_IDETER, MAX(ID_INGRE) AS max_id
                        FROM evi09ingre
                        GROUP BY NUM_IDETER
                  ) ix ON ix.NUM_IDETER = i.NUM_IDETER AND ix.max_id = i.ID_INGRE
                 ) ing";

    // SELECT (agregamos alias de usuario registrador)
    $this->db->select("
        {$this->_table}.*,
        {$this->_table_parent}.TEX_NOMCIU,
        {$this->_table_parent2}.TEX_NOMAGE,
        usr.TEX_USUARIO   AS TEX_USUARIO_REG,  -- login del usuario que registró
        usr.TEX_NOMUSUA   AS NOM_USUARIO_REG   -- nombre descriptivo (por si lo necesitas)
    ");
    $this->db->from($this->_table);

    // joins existentes
    $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");

    // NUEVOS joins (LEFT)
    $this->db->join($join_ing, "ing.NUM_IDETER = {$this->_table}.NUM_IDETER", 'left');
    $this->db->join('auth03usuarios usr', 'usr.COD_USUARIO = ing.COD_CREAUSU', 'left');

    // filtros existentes
    $this->db->where("{$this->_table}.IS_DELETE", 0);
    $this->db->where("{$this->_table}.BOL_ACTIVO", 1);
    $this->db->where("{$this->_table}.FEC_REGIST IS NOT NULL", null, false);
    $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);
    }

    // búsqueda/orden (igual a lo tuyo)
    $i = 0;
    foreach ($this->_column as $item) {
        if (!empty($_POST['search']['value'])) {
            $search = $_POST['search']['value'];
            $paren = ($i == (count($this->_column) - 1)) ? ')' : '';
            ($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()
     {
         $this->_get_datatables_query();
         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();
         $query = $this->db->get();
         return $query->num_rows();
     }

     public function count_all()
     {
         $this->db->from($this->_table);
         $this->db->join($this->_table_parent2, "$this->_table_parent2.COD_AGENCIA=$this->_table.COD_AGENCIA");
         $this->db->where("$this->_table.BOL_ACTIVO", 1);
         $this->db->where("$this->_table.FEC_REGIST !=", null);
               if ($this->_agencia_usuario !== 0) {
             $this->db->where("$this->_table_parent2.COD_AGENCIA", $this->_agencia_usuario);
         }
         return $this->db->count_all_results();
     }

 }
 