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

class Expedientes_model extends Ion_auth_model {
	
	public function __construct()
	{
		parent::__construct();
	}
	
	/**
	 * geocolonias
	 *
	 * @return object
	 * @author Ben Edmunds
	 **/
	public function view_all()
	{
		//$this->trigger_events('geocolonias');

		// run each where that was passed
		
		
		if (isset($this->_ion_where) && !empty($this->_ion_where))
		{
			foreach ($this->_ion_where as $where)
			{
				$this->db->where($where);
			}
			$this->_ion_where = array();
		}

		if (isset($this->_ion_limit) && isset($this->_ion_offset))
		{
			$this->db->limit($this->_ion_limit, $this->_ion_offset);

			$this->_ion_limit  = NULL;
			$this->_ion_offset = NULL;
		}
		else if (isset($this->_ion_limit))
		{
			$this->db->limit($this->_ion_limit);

			$this->_ion_limit  = NULL;
		}

		// set the order
		if (isset($this->_ion_order_by) && isset($this->_ion_order))
		{
			$this->db->order_by($this->_ion_order_by, $this->_ion_order);
		}

		$this->response = $this->db->get($this->tables['geocolonias']);
		
		return $this;
	}
 
	public function dropdown_data($tabla = NULL, $where = NULL, $order_by = NULL)
	{
		$this->response = $this->db->select('*');
		$this->response = $this->db->from($this->tables[$tabla]);
		
		if ($where != NULL) {
			foreach ($where as $k => $v) {

				$this->response = $this->db->where($k, $v);
			}
		}

		$this->response = $this->db->order_by($order_by);
		$this->response = $this->db->get();
		//$this->response = $this->db->get_where($this->tables[$tabla],$where);

		return $this;
	}


	public function select_data($tabla = NULL, $where = NULL)
	{
		$this->response = $this->db->get_where($this->tables[$tabla],$where);

		//return $this;
	}


	/**
	 * Expedientes
	 *
	 * @return object
	 * @author Ben Edmunds
	 **/
	public function view($tabla = NULL, $where = NULL)
	{
		$this->trigger_events('expediente');

		$this->response = $this->db->get_where($this->tables[$tabla], $where);

		return $this;
	}

	/**
	 * existe_grafico
	 *
	 * @return int
	 * @author Diego Ferreira
	 **/
	public function existe_grafico($exp = NULL, $gest = NULL)
	{
		$resultado = $this->db->query("SELECT SEAM_IDE.F_EXISTE_GRAFICO('".$exp."',".$gest.");");

		return $resultado;
	}


	/**
	 * existe_grafico_expe_id
	 *
	 * @return int
	 * @author Jaime Rojas
	 **/
	public function existe_grafico_expe_id($expe_id = NULL)
	{
		$resultado = $this->db->query("SELECT SEAM_IDE.F_EXISTE_GRAFICO_EXPE_ID(".$expe_id.");");

		return $resultado;
	}







	/**
	 * f_existe_grafico_expe_id
	 *
	 * @return int
	 * @author Jaime Rojas
	 **/
	public function f_existe_grafico_expe_id($expe_id = NULL)
	{
		$resultado = $this->db->query("SELECT SEAM_IDE.F_EXISTE_GRAFICO_EXPE_ID(".$expe_id.");")->row()->f_existe_grafico_expe_id;

		return $resultado;
	}


	/**
	 * f_existe_licencia_expe_id
	 *
	 * @return int
	 * @author Jaime Rojas
	 **/
	public function f_existe_licencia_expe_id($expe_id = NULL)
	{
		$resultado = $this->db->query("SELECT SEAM.f_existe_licencia_expe_id(".$expe_id.");")->row()->f_existe_licencia_expe_id;

		return $resultado;
	}

	/**
	 * f_existe_licencia_expe_id
	 *
	 * @return int
	 * @author Jaime Rojas
	 **/
	public function f_existe_resolucion_expe_id($expe_id = NULL)
	{
		$resultado = $this->db->query("SELECT SEAM.f_existe_resolucion_expe_id(".$expe_id.");")->row()->f_existe_resolucion_expe_id;

		return $resultado;
	}


	/**
	 * f_existe_grafico
	 *
	 * @return int
	 * @author Jaime Rojas
	 **/
	public function f_existe_grafico($exp = NULL, $gest = NULL)
	{
		$resultado = $this->db->query("SELECT SEAM_IDE.F_EXISTE_GRAFICO('".$exp."',".$gest.");")->row()->f_existe_grafico;

		return $resultado;
	}


	/**
	 * actualizar_shapes
	 *
	 * @return int
	 * @author Jaime Rojas
	 **/
	public function actualizar_shapes($exp = NULL, $gest = NULL, $expe_id = NULL, $id_epsg = NULL)
	{

		$resultado = $this->db->query("SELECT seam_ide.actualizar_shapes('".$exp."',".$gest.",".$expe_id.",".$id_epsg.");");

		return $resultado;
	}


	/**
	 * eliminar_geometrias
	 *
	 * @return int
	 * @author Jaime Rojas
	 **/
	public function eliminar_geometrias($exp = NULL, $gest = NULL, $expe_id = NULL)
	{
		$resultado = $this->db->query("SELECT SEAM_IDE.F_ELIMINAR_GEOMETRIAS('".$exp."',".$gest.",".$expe_id.");");

		return $resultado;
	}


	/**
	 * contador_registros
	 *	Obtiene la cantidad de registro de una tabla y condición dada
	 * @return int
	 * @author Diego Ferreira
	 **/
	public function contador_registros ($tabla = NULL, $arraywhere = NULL)
	{
		foreach ($arraywhere as $k => $v) {

			$this->db->where($k, $v);
		}
		$this->db->from($this->tables[$tabla]);
		return $this->db->count_all_results();
	}

	/**
	 * obtener_nextval
	 *
	 * @return int
	 * @author Diego Ferreira
	 **/
	public function obtener_nextval($sequence)
	{
		$seq = $this->db->query("SELECT NEXTVAL('" . $sequence . "');")->row()->nextval;

		return $seq;
	}
	
	public function buscar_geo($abuscar)
	{
        //usamos after para decir que empiece a buscar por
        //el principio de la cadena
        //ej SELECT poblacion from poblacion
        //WHERE poblacion LIKE '%$abuscar' limit 12
        //$this->db->select('geocolonias');

        //$this->db->like('poblacion',$abuscar,'after');

		$resultados = $this->db->get('geocoloniaS');

        //si existe algún resultado lo devolvemos
		if($resultados->num_rows() > 0)
		{

			return $resultados->result();

        //en otro caso devolvemos false
		}else{

			return FALSE;

		}
	}


	/**
	 * crear_licencia
	 *
	 * @return integer
	 * @author Jaime Rojas
	*/
	public function crear_licencia($arraycampos = NULL)
	{
		// bail if the group name already exists
		$existe_licencia = $this->db->get_where($this->tables['declaraciones'], $arraycampos)->num_rows();

		
		if($existe_licencia !== 0)
		{
			$this->ion_auth->set_error('expedientes_already_exists');
			return FALSE;
		}		
		else 
		{
			// insert the new expediente
			$this->db->insert($this->tables['declaraciones'], $arraycampos);
			//$expe_id = $this->db->insert_id();

			// report success
			$this->ion_auth->set_message('expedientes_creation_successful');
			// return the brand new group id
			return  TRUE;
		}
	}


	/**
	 * actualizar_licencia
	 *
	 * @return bool
	 * @author Jaime Rojas
	 **/
	public function actualizar_licencia($expe_id = FALSE, $arraycampos = NULL)
	{

		$this->db->update($this->tables['declaraciones'], $arraycampos, array('decl_expe_id' => $expe_id));

        $this->set_message('expedientes_update_successful');

        return 'TRUE';
    }

	/**
	 * actualizar_dictamen
	 *
	 * @return bool
	 * @author Jaime Rojas
	 **/
	public function actualizar_dictamen($expe_id = FALSE, $arraycampos = NULL)
	{

		$this->db->update($this->tables['exp'], $arraycampos, array('expe_id' => $expe_id));

        $this->set_message('expedientes_update_successful');

        return 'TRUE';
    }


	/**
	 * crear_resolucion
	 *
	 * @return integer
	 * @author Jaime Rojas
	*/
	public function crear_resolucion($arraycampos = NULL)
	{
		// bail if the group name already exists
		$existe_resolucion = $this->db->get_where($this->tables['resoluciones'], $arraycampos)->num_rows();

		
		if($existe_resolucion !== 0)
		{
			$this->ion_auth->set_error('expedientes_already_exists');
			return FALSE;
		}		
		else 
		{
			// insert the new expediente
			$this->db->insert($this->tables['resoluciones'], $arraycampos);
			//$expe_id = $this->db->insert_id();

			// report success
			$this->ion_auth->set_message('expedientes_creation_successful');
			// return the brand new group id
			return  TRUE;
		}
	}


	/**
	 * actualizar_resolucion
	 *
	 * @return bool
	 * @author Jaime Rojas
	 **/
	public function actualizar_resolucion($expe_id = FALSE, $arraycampos = NULL)
	{

		$this->db->update($this->tables['resoluciones'], $arraycampos, array('reso_expe_id' => $expe_id));

        $this->set_message('expedientes_update_successful');

        return 'TRUE';
    }


	/**
	 * crear_expediente
	 *
	 * @return integer
	 * @author Diego Ferreira
	*/
	public function crear_expediente($arraycampos = NULL)
	{
		// bail if the group name already exists
		$existe_expediente = $this->db->get_where($this->tables['exp'], $arraycampos)->num_rows();

		
		if($existe_expediente !== 0)
		{
			$this->ion_auth->set_error('expedientes_already_exists');
			return FALSE;
		}
		


		// insert the new expediente
		$this->db->insert($this->tables['exp'], $arraycampos);
		$expe_id = $this->db->query("SELECT MAX(expe_id) FROM SEAM.EXP;")->row()->max;


		//$expe_id = $this->db->insert_id();

		// report success
		$this->ion_auth->set_message('expedientes_creation_successful');
		// return the brand new group id
		return $expe_id;
	}

	
	/**
	 * actualizar_expediente
	 *
	 * @return bool
	 * @author Diego Ferreira
	 **/
	public function actualizar_expediente($expe_id = FALSE, $arraycampos = NULL)
	{

		$this->db->update($this->tables['exp'], $arraycampos, array('expe_id' => $expe_id));

        $this->set_message('expedientes_update_successful');

        return 'TRUE';
    }

    /**
	 * valor_maximo
	 *	Obtiene el valor máximo de una columna y tabla específica
	 * @return int
	 * @author Diego Ferreira
	 **/
	public function valor_maximo ($tabla = NULL, $columna = NULL, $arraywhere = NULL)
	{
		$this->db->select("MAX(".$columna.") max");

		if ($arraywhere != NULL) {
			foreach ($arraywhere as $k => $v) {

				$this->db->where($k, $v);
			}
		}

		$this->db->from($this->tables[$tabla]);

		return $this->db->get()->row()->max;
	}


	/**
	 * campo_valor_maximo
	 *	Obtiene el valor máximo de una columna y tabla específica
	 * @return int
	 * @author Jaime Rojas
	 **/
	public function obtener_valor_campo ($tabla = NULL, $columna = NULL, $arraywhere = NULL)
	{
		$this->db->select($columna." campo");

		foreach ($arraywhere as $k => $v) {

			$this->db->where($k, $v);
		}

		$this->db->from($this->tables[$tabla]);

		return $this->db->get()->row()->campo;
	}

	/**
	 * campo_valor_maximo
	 *	Obtiene el valor máximo de una columna y tabla específica
	 * @return int
	 * @author Jaime Rojas
	 **/
	public function campo_valor_maximo ($tabla = NULL, $columna = NULL, $arraywhere = NULL)
	{
		$this->db->select("MAX(".$columna.") max");

		foreach ($arraywhere as $k => $v) {

			$this->db->where($k, $v);
		}

		$this->db->from($this->tables[$tabla]);

		return $this->db->get()->row()->max;
	}

	/**
	 * eliminar_expediente
	 *	Obtiene el valor máximo de una columna y tabla específica
	 * @return int
	 * @author Jaime Rojas
	 **/
    public function eliminar_expediente($expe_id)
    {
    	$this->db->delete($this->tables['exp'],array('expe_id' => $expe_id));
		return TRUE;
    }

	/**
	 * eliminar_licencia
	 *	Obtiene el valor máximo de una columna y tabla específica
	 * @return int
	 * @author Jaime Rojas
	 **/
    public function eliminar_licencia($expe_id)
    {
    	$this->db->delete($this->tables['declaraciones'],array('decl_expe_id' => $expe_id));
		return TRUE;
    }

	/**
	 * eliminar_licencia
	 *	Obtiene el valor máximo de una columna y tabla específica
	 * @return int
	 * @author Jaime Rojas
	 **/
    public function eliminar_resolucion($expe_id)
    {
    	$this->db->delete($this->tables['resoluciones'],array('reso_expe_id' => $expe_id));
		return TRUE;
    }

    public function departamentos()
    {
    	$this->response = $this->db->get($this->tables['departamentos']);

    	return $this;
    }

    public function distritos()
    {
    	$this->response = $this->db->query("SELECT * FROM INDERT.DISTRITOS ORDER BY DISTRITO");
    	return $this;
    }

	//PAGINATION
    public function get_pagination($number_per_page)
    {
    	if ($this->uri->segment(5) == null | $this->uri->segment(5) == '') {

    		$this->response = $this->db->get($this->tables['geocolonias'],$number_per_page, 0);
    	}else
    	{
    		$this->response = $this->db->get($this->tables['geocolonias'],$number_per_page, $this->uri->segment(5));
    	}

    	return $this;
	}

	public function num_post()
	{			
		$number = $this->db->query("SELECT count(*) as number FROM indert.geocolonia")->row()->number; //rápido
		return intval($number);
	}



	public function mapabb($expe_id)
	{


			$i_sql = "select ";
			$i_sql = $i_sql. "round(cast((x.campo1+x.campo1*(0.0002)) as decimal),13) as ext1, ";
			$i_sql = $i_sql. "round(cast((x.campo2+x.campo2*(0.0002)) as decimal),13) as ext2, ";
			$i_sql = $i_sql. "round(cast((x.campo3-x.campo3*(0.0002)) as decimal),13) as ext3, ";
			$i_sql = $i_sql. "round(cast((x.campo4-x.campo4*(0.0002)) as decimal),13) as ext4 ";
			$i_sql = $i_sql. "from ";
			$i_sql = $i_sql. "(";

			$i_sql = $i_sql. "select ";
			$i_sql = $i_sql. "cast((regexp_split_to_array(bb, E','))[1] as decimal) as campo1,";
			$i_sql = $i_sql. "cast((regexp_split_to_array(bb, E','))[2] as decimal) as campo2,";
			$i_sql = $i_sql. "cast((regexp_split_to_array(bb, E','))[3] as decimal) as campo3,";
			$i_sql = $i_sql. "cast((regexp_split_to_array(bb, E','))[4] as decimal) as campo4";
			$i_sql = $i_sql. " from ( ";
				
			$i_sql = $i_sql . "select ";
            $i_sql = $i_sql . "replace(";
            $i_sql = $i_sql . "replace(substr(cast(a.bb as varchar),5),' ',',')";
            $i_sql = $i_sql . ",')','') as bb ";
            $i_sql = $i_sql . "FROM (select ST_Extent(the_geom) as bb ";
            $i_sql = $i_sql . "from i3geo.seam_poligono ";
            $i_sql = $i_sql . "where expe_id=" . $expe_id . " ";
            $i_sql = $i_sql . "group by expe_id) as a) as t1 ";
            //$i_sql = $i_sql . ";";
			$i_sql = $i_sql . ") as x;";

            $response = $this->db->query($i_sql)->result_array();

            return $response;

	}
}