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

     protected $_table;
     protected $_table_parent;
     protected $_table_parent2;
     protected $_table_parent3;
     protected $_table_parent4;
     protected $_table_parent5;
     private $_column;
     private $_order;

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

     private function _get_datatables_query()
     {
         $this->db->select("$this->_table.ID_CANDID, $this->_table.TEX_NOMCAN,"
           . " $this->_table.TEX_APECAN, $this->_table.TEXT_TIPLIST,"
           . " AES_DECRYPT(NUM_TOVOCA, '$this->_key') as 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", 'INNER');
         $this->db->join($this->_table_parent2, "$this->_table_parent2.COD_CIUDAD=$this->_table.COD_CIUDAD", 'INNER');
         $this->db->join($this->_table_parent3, "$this->_table_parent3.COD_AGENCIA=$this->_table.COD_AGENCIA", 'INNER');
         $this->db->where("$this->_table.TEXT_TIPLIST !=", "LCL");
         $this->db->where("$this->_table.BOL_ACTIVO", 1);
         $this->db->where("$this->_table.IS_DELETE", 0);
         $this->db->order_by("NUM_TOVOCA", 'DESC');

         $i = 0;
         foreach ($this->_column as $item) {
             if (isset($_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()
     {
         $this->_get_datatables_query();
         if (isset($_POST['length']) != -1):
             $this->db->limit(isset($_POST['length']), isset($_POST['start']));
         endif;
         $this->db->where("$this->_table.TEXT_TIPLIST !=", "LCL");
         $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->where("$this->_table.BOL_ACTIVO", 1);
         $this->db->where("$this->_table.IS_DELETE", 0);
         $this->db->where("$this->_table.TEXT_TIPLIST !=", 'LCL');
         return $this->db->count_all_results();
     }

     public function get_all_data()
     {
         $this->db->select("$this->_table.ID_CANDID, $this->_table.TEX_NOMCAN,"
           . " $this->_table.TEX_APECAN,"
           . " AES_DECRYPT(NUM_TOVOCA, '$this->_key') as 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.TEXT_TIPLIST !=", "LCL");
         $this->db->where("$this->_table.BOL_ACTIVO", 1);
         $this->db->where("$this->_table.IS_DELETE", 0);
         $this->db->where("$this->_table_parent.ID_INSTAN", 1);
         $this->db->order_by("NUM_TOVOCA", 'DESC');
         return $this->db->get()->result();
     }

     public function get_candidatos_con_votos()
     {
         $this->db->select(""
           . "$this->_table.ID_CANDID, $this->_table.TEX_NOMCAN,"
           . "$this->_table.TEX_APECAN,evi05partic.COD_AGENCIA");
         $this->db->from($this->_table);
         $this->db->join('evi07audpro', "evi07audpro.ID_CANDID=$this->_table.ID_CANDID", 'RIGTH');
         $this->db->join('evi05partic', "evi05partic.NUM_IDETER=evi07audpro.NUM_IDETER");
         $this->db->where("$this->_table.BOL_ACTIVO", 1);
         $this->db->where("$this->_table.IS_DELETE", 0);
         $this->db->group_by("evi07audpro.ID_CANDID");
         $resultado = $this->db->get()->result();
         return $resultado;
     }

     public function all_agencias()
     {
         $this->db->select(""
           . "gen01agencias.COD_AGENCIA AS agencia,gen01agencias.TEX_NOMAGE as agencia_nombre");
         $this->db->from('gen01agencias');
         return $this->db->get()->result();
     }

     public function get_votos_candidato_por_agencia($id_candidato, $cod_agencia)
     {
         $agencias = $this->all_agencias();


         $arr = [];
         foreach ($agencias as $agen) {
             $votos = $this->evalua_votos($agen->agencia, $id_candidato);
             if ($votos) {
                 $arr[] = ['votos' => $votos];
             }
             else {
                 $arr[] = ['votos' => 0];
             }
         }

         return $arr;
     }

     public function evalua_votos($agencia, $id_candidato)
     {
         $this->db->select("COUNT(evi07audpro.ID_CANDID) AS votos,evi07audpro.ID_CANDID ");
         $this->db->join('evi05partic', "evi05partic.NUM_IDETER=evi07audpro.NUM_IDETER");
         $this->db->join('gen01agencias', "gen01agencias.COD_AGENCIA=evi05partic.COD_AGENCIA");
         $this->db->where("evi07audpro.ID_CANDID", $id_candidato);
         $this->db->where("gen01agencias.COD_AGENCIA", $agencia);
         $votos = $this->db->get('evi07audpro')->row();
         return $votos->votos;
     }

     public function get_votos_agencia()
     {
         $this->db->select("$this->_table_parent3.TEX_NOMAGE AS agencia,"
           . "COUNT(evi07audpro.ID_CANDID) AS votos");
         $this->db->from('evi07audpro');
         $this->db->join('evi05partic', "evi05partic.NUM_IDETER=evi07audpro.NUM_IDETER", 'RIGHT');
         $this->db->join('gen01agencias', "gen01agencias.COD_AGENCIA=evi05partic.COD_AGENCIA", 'RIGHT');
         $this->db->group_by("evi05partic.COD_AGENCIA");
         $votos_agencias = $this->db->get()->result();
        return $votos_agencias;
       //var_dump($votos_agencias);exit;

     }

 }
 