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

class Parcelas_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;
	}

	/**
	 * geocolonia
	 *
	 * @return object
	 * @author Ben Edmunds
	 **/
	public function view($dpto = NULL, $dist = NULL, $col = NULL)
	{
		$this->trigger_events('geocolonia');

		if (isset($dpto) AND isset($dist) AND isset($col))
		{
			$this->where($this->tables['geocolonias'].'.dpto_cod', $dpto);
			$this->where($this->tables['geocolonias'].'.dist_cod', $dist);
			$this->where($this->tables['geocolonias'].'.col_cod', $col);
		}

		$this->limit(1);
		$this->order_by('gid');
		
		//$this->db->query("SELECT FROM INDERT.GEOCOLONIAS WHERE gid = $id")

		return $this->view_all();
	}

	/**
	 * create_geocolonia
	 *
	 * @author aditya menon
	*/
	public function create_geocolonia($geocolonia_dpto_cod = '', $geocolonia_dist_cod = '', $geocolonia_col_cod = '', $geocolonia_id_codgeo = '', $geocolonia_col_desc = '', $additional_data = array())
	{
		// bail if the dpto_cod was not passed ** Se utiliza la etiqueta de lenguaje spanish
		if(!$geocolonia_dpto_cod)
		{
			$this->set_error('geocolonia_dpto_cod_required');
			return FALSE;
		}
		// bail if the dist_cod was not passed
		if(!$geocolonia_dist_cod)
		{
			$this->set_error('geocolonia_dist_cod_required');
			return FALSE;
		}
		// bail if the col_cod was not passed
		if(!$geocolonia_col_cod)
		{
			$this->set_error('geocolonia_col_cod_required');
			return FALSE;
		}
		// bail if the id_codgeo was not passed
		if(!$geocolonia_id_codgeo)
		{
			$this->set_error('geocolonia_id_codgeo_required');
			return FALSE;
		}
		// bail if the col_desc was not passed
		if(!$geocolonia_col_desc)
		{
			$this->set_error('geocolonia_col_desc_required');
			return FALSE;
		}		
		// bail if the group name already exists
		$existing_geocolonia = $this->db->get_where($this->tables['geocolonias'], array('col_desc' => $geocolonia_col_desc))->num_rows();

		
		if($existing_geocolonia !== 0)
		{
			$this->set_error('geocolonia_already_exists');
			return FALSE;
		}
		
		$data = array('dpto_cod'=>$geocolonia_dpto_cod, 'dist_cod'=>$geocolonia_dist_cod, 'col_cod'=>$geocolonia_col_cod, 'id_codgeo'=>$geocolonia_id_codgeo, 'col_desc'=>$geocolonia_col_desc, 'col_desc'=>$geocolonia_col_desc);
		
		// filter out any data passed that doesnt have a matching column in the groups table
		// and merge the set group data and the additional data
		if (!empty($additional_data)) $data = array_merge($this->_filter_data($this->tables['geocolonias'], $additional_data), $data);

		$this->trigger_events('extra_geocolonia_set');

		// insert the new group
		$this->db->insert($this->tables['geocolonias'], $data);
		$geocolonia_gid = $this->db->insert_id();

		// report success
		$this->set_message('geocolonia_creation_successful');
		// return the brand new group id
		return $geocolonia_gid;
	}
	
	/**
	 * update_gepcolonia
	 *
	 * @return bool
	 * @author aditya menon
	 **/

    public function update_geocolonia($gid = FALSE, $geocolonia_dpto_cod = FALSE, $geocolonia_dist_cod = FALSE, $geocolonia_col_cod = FALSE, $geocolonia_id_codgeo = FALSE, $geocolonia_col_desc = FALSE, $additional_data = array())
	{
		if (empty($gid) AND empty($geocolonia_dpto_cod) AND empty($geocolonia_dist_cod) AND empty($geocolonia_col_cod) AND empty($geocolonia_id_codgeo) AND empty($geocolonia_col_desc)) return FALSE;

		$data = array();

		if (!empty($gid) AND !empty($geocolonia_dpto_cod) AND !empty($geocolonia_dist_cod) AND !empty($geocolonia_col_cod) AND !empty($geocolonia_id_codgeo) AND !empty($geocolonia_col_desc))
		{
			// we are changing the col_desc, so do some checks

			// bail if the geocolonia col_desc already exists
			$existing_geocolonia = $this->db->get_where($this->tables['geocolonias'], array('dpto_cod' => $geocolonia_dpto_cod, 'dist_cod' => $geocolonia_dist_cod, 'col_cod' => $geocolonia_col_cod, 'id_codgeo' => $geocolonia_id_codgeo))->row();

			if(isset($existing_geocolonia->gid) && $existing_geocolonia->gid != $gid)
			{
				$this->set_error('geocolonia_already_exists');
				return FALSE;
			}
			/*
			if (strlen($geocolonia_dpto_cod) > 2) 
			{
				$this->set_error('geocolonia_already_exists');
				return FALSE;
			}
			*/
			$data['dpto_cod'] = $geocolonia_dpto_cod;
			$data['dist_cod'] = $geocolonia_dist_cod;
			$data['col_cod'] = $geocolonia_col_cod;
			$data['id_codgeo'] = $geocolonia_id_codgeo;
			$data['col_desc'] = $geocolonia_col_desc;
		}

		// restrict change of name of the admin group
        $geocolonia = $this->db->get_where($this->tables['geocolonias'], array('gid' => $gid))->row();
        
        /*if($this->config->item('admin_group', 'ion_auth') === $geocolonia->dpto_cod && $geocolonia_dpto_cod !== $geocolonia->dpto_cod)
        {
            $this->set_error('group_name_admin_not_alter');
            return FALSE;
        }*/

		// IMPORTANT!! Third parameter was string type $description; this following code is to maintain backward compatibility
		// New projects should work with 3rd param as array
		if (is_string($additional_data)) $additional_data = array('col_cod' => $additional_data);


		// filter out any data passed that doesnt have a matching column in the groups table
		// and merge the set group data and the additional data
		if (!empty($additional_data)) $data = array_merge($this->_filter_data($this->tables['geocolonias'], $additional_data), $data);


		$this->db->update($this->tables['geocolonias'], $data, array('gid' => $gid));

		$this->set_message('geocolonias_update_successful');

		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,$datos)
	{
		if ($this->uri->segment(5) == null | $this->uri->segment(5) == '') {
			if ($datos !== null) {
				$this->response = $this->db->get_where($this->tables['parcelas'],$datos,$number_per_page, 0);
			}else{

				$this->response = $this->db->get($this->tables['parcelas'],$number_per_page, 0);
			}
			
		}else
		{
			if ($datos !== null) {
				$this->response = $this->db->get($this->tables['parcelas'],$datos,$number_per_page, $this->uri->segment(5));
			}else{
				
				$this->response = $this->db->get($this->tables['parcelas'],$number_per_page, $this->uri->segment(5));
			}
			
		}
		
		return $this;
	}

	public function num_post_parcelas($where)
	{	

		$number = $this->db->query("SELECT count(*) as number FROM i3geo.parcelas")->row()->number; //rápido
		return intval($number);
	}

	public function bb($objectid)
	{



			$i_sql = $i_sql. "select ";
			$i_sql = $i_sql. "(regexp_split_to_array(bb, E','))[1] as campo1,";
			$i_sql = $i_sql. "(regexp_split_to_array(bb, E','))[2] as campo2,";
			$i_sql = $i_sql. "(regexp_split_to_array(bb, E','))[3] as campo3,";
			$i_sql = $i_sql. "(regexp_split_to_array(bb, E','))[4] 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.parcelas ";
            $i_sql = $i_sql . "where objectid=" . $objectid . " ";
            $i_sql = $i_sql . "group by objectid) as a) as t1 ";
            $i_sql = $i_sql . ";";
			//$i_sql = $i_sql . ") as x;";

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

            return $response;

	}


	public function bblote($objectid)
	{
			$i_sql = $i_sql. "select ";
			$i_sql = $i_sql. "(regexp_split_to_array(bb, E','))[1] as campo1,";
			$i_sql = $i_sql. "(regexp_split_to_array(bb, E','))[2] as campo2,";
			$i_sql = $i_sql. "(regexp_split_to_array(bb, E','))[3] as campo3,";
			$i_sql = $i_sql. "(regexp_split_to_array(bb, E','))[4] 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.lotes ";
            $i_sql = $i_sql . "where objectid=" . $objectid . " ";
            $i_sql = $i_sql . "group by objectid) as a) as t1 ";
            $i_sql = $i_sql . ";";
			//$i_sql = $i_sql . ") as x;";
            $response = $this->db->query($i_sql)->result_array();
            return $response;
	}


	public function bbaldea($objectid)
	{
			$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.aldeas ";
            $i_sql = $i_sql . "where objectid=" . $objectid . " ";
            $i_sql = $i_sql . "group by objectid) as a) as t1 ";
            //$i_sql = $i_sql . ";";
			$i_sql = $i_sql . ") as x;";
            $response = $this->db->query($i_sql)->result_array();

            return $response;
	}



	public function bbbloque($objectid)
	{
			$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.bloques ";
            $i_sql = $i_sql . "where objectid=" . $objectid . " ";
            $i_sql = $i_sql . "group by objectid) as a) as t1 ";
            //$i_sql = $i_sql . ";";
			$i_sql = $i_sql . ") as x;";
            $response = $this->db->query($i_sql)->result_array();

            return $response;
	}


	public function mapabb($objectid)
	{


			$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.parcelas ";
            $i_sql = $i_sql . "where objectid=" . $objectid . " ";
            $i_sql = $i_sql . "group by objectid) as a) as t1 ";
            //$i_sql = $i_sql . ";";
			$i_sql = $i_sql . ") as x;";

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

            return $response;

	}

	public function mapabb_lote($lote_n)
	{


			$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 ";
			$i_sql = $i_sql."(select ST_Extent(the_geom) bb from ";
			$i_sql = $i_sql."(select 1 objectid, st_union(st_snaptogrid(the_geom,0.00000000000001)) the_geom ";
		    $i_sql = $i_sql."from i3geo.parcelas t ";
            $i_sql = $i_sql."where t.lote_n='".$lote_n."' and ST_IsValid(the_geom)) e ";
	        $i_sql = $i_sql.") as a) as t1) as x;  ";

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

            return $response;

	}

	public function mapafincabb($objectid)
	{

		$sq = "select  ";
		$sq = $sq."round(cast((x.campo1+x.campo1*(0.0002)) as decimal),13) as ext1, ";
		$sq = $sq."round(cast((x.campo2+x.campo2*(0.0002)) as decimal),13) as ext2, ";
		$sq = $sq."round(cast((x.campo3-x.campo3*(0.0002)) as decimal),13) as ext3, "; 
		$sq = $sq."round(cast((x.campo4-x.campo4*(0.0002)) as decimal),13) as ext4 "; 
		$sq = $sq."from "; 
		$sq = $sq."( ";
		$sq = $sq."select "; 
		$sq = $sq."cast((regexp_split_to_array(bb, E','))[1] as decimal) as campo1, ";
		$sq = $sq."cast((regexp_split_to_array(bb, E','))[2] as decimal) as campo2, ";
		$sq = $sq."cast((regexp_split_to_array(bb, E','))[3] as decimal) as campo3, ";
		$sq = $sq."cast((regexp_split_to_array(bb, E','))[4] as decimal) as campo4 "; 
		$sq = $sq."from ( ";						
		$sq = $sq."select "; 
		$sq = $sq."replace( ";
		$sq = $sq."replace(substr(cast(a.bb as varchar),5),' ',','),')','') as bb "; 
		$sq = $sq."FROM (select ST_Extent(st_makevalid(u.the_geom)) as bb "; 
		$sq = $sq."from (select 1 objectid, st_union(st_snaptogrid(f.the_geom,0.00000000000001)) the_geom  ";
		$sq = $sq."from i3geo.v_fincas f where f.objectid_p=".$objectid." ) u ";
		$sq = $sq.") as a) as t1 ) as x; ";

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

        return $response;

	}

	public function mapafincabb_lote($lote_n)
	{

		$sq = "select  ";
		$sq = $sq."round(cast((x.campo1+x.campo1*(0.0002)) as decimal),13) as ext1, ";
		$sq = $sq."round(cast((x.campo2+x.campo2*(0.0002)) as decimal),13) as ext2, ";
		$sq = $sq."round(cast((x.campo3-x.campo3*(0.0002)) as decimal),13) as ext3, "; 
		$sq = $sq."round(cast((x.campo4-x.campo4*(0.0002)) as decimal),13) as ext4 "; 
		$sq = $sq."from "; 
		$sq = $sq."( ";
		$sq = $sq."select "; 
		$sq = $sq."cast((regexp_split_to_array(bb, E','))[1] as decimal) as campo1, ";
		$sq = $sq."cast((regexp_split_to_array(bb, E','))[2] as decimal) as campo2, ";
		$sq = $sq."cast((regexp_split_to_array(bb, E','))[3] as decimal) as campo3, ";
		$sq = $sq."cast((regexp_split_to_array(bb, E','))[4] as decimal) as campo4 "; 
		$sq = $sq."from ( ";						
		$sq = $sq."select "; 
		$sq = $sq."replace( ";
		$sq = $sq."replace(substr(cast(a.bb as varchar),5),' ',','),')','') as bb "; 
		$sq = $sq."FROM (select ST_Extent(u.the_geom) as bb "; 
		$sq = $sq."from (select 1 objectid, st_union(st_snaptogrid(st_makevalid(f.the_geom),0.00000000000001)) the_geom  ";
		$sq = $sq."from i3geo.v_fincas f where f.lote_n='".$lote_n."' ) u ";
		$sq = $sq.") as a) as t1 ) as x; ";

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

        return $response;

	}

}