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

class lotes_model extends Ion_auth_model {
	
    public function __construct()
    {
        parent::__construct();
	}
	

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

		// run each where that was passed
		
		$this->response = $this->db->get_where($this->tables[$tabla], $where)->num_rows();
		
		return $this;
	}
	*/

	/**
	 * lotes
	 *
	 * @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['lotes']);
		
		return $this;
	}
	


	/**
	 * Lotes
	 *
	 * @return object
	 * @author Ben Edmunds
	 **/


	/*
	public function view($gid)
	{
		$this->trigger_events('lotes');

		if (isset($gid))
		{
			$this->where($this->tables['lotes'].'.gid', $gid);

		}

		$this->limit(1);
		$this->order_by('gid');
		
		
		return $this->view_all();
	}
	*/


	 /**
	 * d_view
	 *
	 * @return object
	 * @author Jaime rojas
	 **/
			
	public function view($gid)
	{

		$nom_tabla = 'v_lotes_index';

		$this->trigger_events($this->tables[$nom_tabla]);

		$this->response = $this->db->get_where($this->tables[$nom_tabla], array('gid' => $gid));
		
		return $this;
	}
	
	

	/**
	 * municipios
	 *
	 * @return object
	 * @author Ben Edmunds
	 **/
	
	public function gview($tabla, $where)
	{
		$this->trigger_events($this->tables[$tabla]);

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


	/**
	 * create_lotes
	 *
	 * @author marina albornoz
	*/
	public function create_lotes($arraycampos = NULL)
	{
		// bail if the group name already exists
		$existing_lotes = $this->db->get_where($this->tables['lotes'], $arraycampos)->num_rows();

		
		if($existing_lotes !== 0)
		{
			$this->ion_auth->set_error('lotes_already_exists');
			return FALSE;
		}
		
		// insert the new group
		$this->db->insert($this->tables['lotes'], $arraycampos);
		$lotes_gid = $this->db->insert_id();

		// report success
		$this->set_message('lotes_creation_successful');
		// return the brand new group id
		return $lotes_gid;
	}

      	/**
     	* eliminar_lotes
     	* @author Marina Albornoz
     	**/
    	public function eliminar_lotes($gid)
   	{
        	//$this->db->delete($this->tables['historial_meu'], array('expediente_id' => $expe_id));
        	//$this->db->delete($this->tables['historial_fecha_ingreso'], array('expediente_id' => $expe_id));
        	//$this->db->delete($this->tables['historial_notificacion'], array('expediente_id' => $expe_id));
        	//$this->db->delete($this->tables['exp_observ'], array('expediente_id' => $expe_id));
        	//$this->db->delete($this->tables['documentos'], array('expediente_id' => $expe_id));
        	//$this->db->delete($this->tables['documentos_sge'], array('id_expediente' => $expe_id));
       	 	//$this->db->delete($this->tables['coordenadas'], array('id_expediente' => $expe_id));
        	$this->db->delete($this->tables['lotes'], array('gid' => $gid));
        	return true;
        	
	}	

	/**
	 * update_Lotes
	 *
	 * @return bool
	 * @author aditya menon
	 **/

    	public function update_lotes($gid, $arraycampos)
	{
		$this->db->update($this->tables['lotes'], $arraycampos, array('gid' => $gid));

        $this->ion_auth->set_message('lotes_update_successful');

        return TRUE;
	}

	 /**
	 * departamentos
	 *
	 * @return object
	 * @Jaime rojas
	 **/
	public function departamentos()
	{
		$this->response = $this->db->get($this->tables['departamentos']);

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

	//PAGINATION
	 /**
	 * get_pagination
	 *
	 * @return object
	 * @Jaime rojas
	 **/	
	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['lotes'],$datos,$number_per_page, 0);
			}else{

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

	 /**
	 * get_pagination
	 *
	 * @return object
	 * @Jaime rojas
	 **/
	public function num_post_lotes($where)
	{	

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

	 /**
	 * bb
	 *
	 * @return object
	 * @Jaime rojas
	 **/
	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 municipio.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;

	}

	 /**
	 * bblote
	 *
	 * @return object
	 * @Jaime rojas
	 **/
	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 municipal.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;
	}


	
	 /**
	 * mapabb
	 *
	 * @return object
	 * @Jaime rojas
	 **/
	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.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;

	}

	 /**
	 * mapabb_lote
	 *
	 * @return object
	 * @Jaime rojas
	 **/
	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.lotes 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;

	}

	/**
	 * actualizar_cuest
	 *	Función encargada de actualizar el cuestionario en la tabla y con la condición propuesta
	 * @return bool
	 * @author Jaime Rojas
	 **/
    public function actualizar_fichas($where = NULL, $tabla = NULL, $arraycampos = NULL)
	{
		if (empty($arraycampos)) return FALSE;

		$this->db->update($this->tables[$tabla], $arraycampos, $where);

		$this->set_message('update_successful');

		return TRUE;
	}
	


}