<?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 
  *
  * @author Jaime Bravo.
  */
 class Inf_cociente_agencia extends MY_Model
 {

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

     public function __construct()
     {
         parent::__construct();
         $this->load->database();
         $this->_table         = 'evi04candidat_mr_mr';
         $this->_table_parent  = 'evi02instan';
         $this->_table_parent2 = 'gen04ciudad';
         $this->_table_parent3 = 'gen01agencias';
         $this->primary_key    = 'ID_CANDID';
         $this->_column        = array(
           'ID_CANDID',
           "$this->_table.TEX_NOMCAN",
           "$this->_table.TEX_APECAN", 'NUM_TOVOCA',
           "$this->_table_parent.TEX_NOMINS", "$this->_table_parent2.TEX_NOMCIU",
           "$this->_table_parent3.TEX_NOMAGE");
         $this->_order         = array("$this->_table_parent.TEX_NOMINS" => 'ASC');
     }

     private function _get_datatables_query($instancia)
     {
         $this->_instancia = $instancia;

         $this->db->select(""
           . " $this->_table.ID_CANDID, "
           . " $this->_table.TEX_NOMCAN,"
           . " $this->_table.TEX_APECAN,"
           . " NUM_TOVOCA,"
           . ", $this->_table_parent.TEX_NOMINS,"
           . " $this->_table_parent2.TEX_NOMCIU, $this->_table_parent3.TEX_NOMAGE");
         $this->db->from($this->_table);
         $this->db->join($this->_table_parent, "$this->_table_parent.ID_INSTAN=$this->_table.ID_INSTAN");
         $this->db->join($this->_table_parent2, "$this->_table_parent2.COD_CIUDAD=$this->_table.COD_CIUDAD");
         $this->db->join($this->_table_parent3, "$this->_table_parent3.COD_AGENCIA=$this->_table.COD_AGENCIA", 'LEFT');
         $this->db->where("$this->_table.BOL_ACTIVO", 1);
         $this->db->where("$this->_table.IS_DELETE", 0);
         $this->db->where("$this->_table.ID_INSTAN", $this->_instancia);
         $this->db->order_by("$this->_table_parent.ID_INSTAN", 'ASC');
         $this->db->order_by("NUM_TOVOCA", 'DESC', false);

         $i = 0;
         foreach ($this->_column as $item) {
             if ($_POST['search']['value']) {
                 ($i === 0) ? $this->db->like($item, $_POST['search']['value']) : $this->db->or_like($item, $_POST['search']['value']);
             }

             $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 ($_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()
     {
         $this->db->from($this->_table);
         $this->db->where("$this->_table.BOL_ACTIVO", 1);
         $this->db->where("$this->_table.IS_DELETE", 0);
         return $this->db->count_all_results();
     }

     public function get_all_data($instancia)
     {

         $this->db->select(""
           . " $this->_table.ID_CANDID, "
           . " $this->_table.TEX_NOMCAN,"
           . " $this->_table.TEX_APECAN,"
           . " NUM_TOVOCA,"
           . ", $this->_table_parent.TEX_NOMINS,"
           . " $this->_table_parent2.TEX_NOMCIU, $this->_table_parent3.TEX_NOMAGE");
         $this->db->from($this->_table);
         $this->db->join($this->_table_parent, "$this->_table_parent.ID_INSTAN=$this->_table.ID_INSTAN");
         $this->db->join($this->_table_parent2, "$this->_table_parent2.COD_CIUDAD=$this->_table.COD_CIUDAD");
         $this->db->join($this->_table_parent3, "$this->_table_parent3.COD_AGENCIA=$this->_table.COD_AGENCIA", 'LEFT');
         $this->db->where("$this->_table.BOL_ACTIVO", 1);
         $this->db->where("$this->_table.IS_DELETE", 0);
         $this->db->where("$this->_table.ID_INSTAN", $instancia);
         $this->db->order_by("$this->_table_parent.ID_INSTAN", 'ASC');
         $this->db->order_by("NUM_TOVOCA", 'DESC', false);
         return $this->db->get()->result();
     }

     public function get_listas()
     {
         $sql = "SELECT 
                evi03listas.ID_LISTA, 
                evi03listas.TEX_NOMLIS,
                COUNT(evi07audpro.ID_CANDID) as votos
                FROM evi04candidat_mr_mr
                JOIN evi03listas ON evi03listas.ID_LISTA = evi04candidat_mr_mr.ID_LISTA
                LEFT JOIN evi07audpro ON evi07audpro.ID_CANDID = evi04candidat_mr_mr.ID_CANDID
                WHERE evi04candidat_mr_mr.TEXT_TIPLIST IS NOT NULL
                AND evi03listas.TEX_TIPLIS = 'LCC'
                AND evi03listas.BOL_ACTIVO = 1
                AND evi03listas.IS_DELETE = 0
                GROUP BY evi03listas.ID_LISTA
                ORDER BY evi03listas.NUM_ORDEN ASC";
         $res = $this->db->query($sql);
         return $res->result();
     }

     public function get_total_votos_listas()
     {
         $sql       = "SELECT 
                COUNT(evi07audpro.ID_CANDID) as votos
                FROM evi04candidat_mr_mr
                JOIN evi03listas ON evi03listas.ID_LISTA = evi04candidat_mr_mr.ID_LISTA
                JOIN evi07audpro ON evi07audpro.ID_CANDID = evi04candidat_mr_mr.ID_CANDID
                WHERE evi04candidat_mr_mr.TEXT_TIPLIST IS NOT NULL
                AND evi03listas.TEX_TIPLIS = 'LCC'
                AND evi03listas.BOL_ACTIVO = 1
                AND evi03listas.IS_DELETE = 0
                ORDER BY votos DESC";
         $res       = $this->db->query($sql);
         $resultado = $res->row();
         return $resultado;
     }

     public function get_candidatos_por_lista($id_lista, $limit)
     {
         $sql = "SELECT evi04candidat_mr_mr.ID_CANDID,evi04candidat_mr.NUM_ORDEN,evi04candidat_mr.TEX_NOMCAN,
            evi04candidat_mr.TEX_APECAN
            FROM evi04candidat_mr
            LEFT JOIN evi07audpro aud ON aud.ID_CANDID=evi04candidat_mr.ID_CANDID
            WHERE evi04candidat_mr.TEXT_TIPLIST IS NOT NULL AND evi04candidat_mr.ID_LISTA = $id_lista
            AND evi04candidat_mr.TEXT_TIPLIST = 'LCC'
            GROUP BY evi04candidat_mr.ID_CANDID
            ORDER BY cast(evi04candidat_mr.NUM_ORDEN as unsigned) 
            LIMIT $limit";


         $res       = $this->db->query($sql);
         $resultado = $res->result();

         return $resultado;

     }

     public function get_datos_instancia()
     {
         $sql = "SELECT 
                instancia.NUM_TOTELE as cargos_a_suplir,
                instancia.NUM_DECRES as decimales_size
                FROM 
                evi02instan instancia
                WHERE
                instancia.NUM_TOTELE > 0";
         $res = $this->db->query($sql);
         return $res->row();
     }

 }
 