obj =& get_instance(); $this->ci =& get_instance(); $this->ci->load->library('encrypt'); } // ////////////////////////////////////////////////////////////////////////// // Function: findAll() // // Description: Retrieves and returns data listing from the database // // ////////////////////////////////////////////////////////////////////////// function findAll($start = NULL, $count = NULL) { return $this->find(NULL, $start, $count); } // function findById($key_value) { // return $this->find(array('id' => '$key_value')); // } function findByFilter($filter_rules, $start = NULL, $count = NULL) { return $this->find($filter_rules, $start, $count); } function find($filters = NULL, $start = NULL, $count = NULL) { $results = array(); // Load the database library $this->load->database(); // /////////////////////////////////////////////////////////////////////// // Make a note of the current table record count // /////////////////////////////////////////////////////////////////////// $this->table_record_count = $this->db->count_all( 'cla_clasificado' ); $ciu=$this->ubica->ciu; //ciu=$ciu // Filter could be an array or filter values or an SQL string. $where_clause = ''; if ($filters) { if ( is_string($filters) ) { $where_clause = $filters; } elseif ( is_array($filters) ) { // Build your filter rules if ( count($filters) > 0 ) { foreach ($filters as $field => $value) { $filter_list[] = " $field = '$value' "; } $where_clause = "WHERE ciu=$ciu and " . join(' AND ', $filter_list ); } } } $limit_clause = ''; if ($start) { if ($count) { $limit_clause = " LIMIT $start, $count "; } else { $limit_clause = " LIMIT $start "; } } $order=' order by id '; // Build up the SQL query string and run the query $sql = 'SELECT *,fecha_creacion, fecha_creacion + 30 as fecha_fin, (fecha_creacion + 30)-CURRENT_DATE as dias_fin FROM cla_clasificado ' . $where_clause . $order .$limit_clause; $query = $this->db->query($sql); if ($query->num_rows() > 0) { // //////////////////////////////////////////////////////////////////// // NOTE: At this stage you could return the entire result set, like: // NOTE: ...return $query->result_array(); // NOTE: ...The generated code loops through the result set to provide // NOTE: ...the oppurtunity to provide further customisations on the // NOTE: ...code (especially if you are generating in verbose mode). // //////////////////////////////////////////////////////////////////// foreach ($query->result_array() as $row) // Go through the result set { // Build up a list for each column from the database and place it in // ...the result set $query_results['id'] = $row['id']; $query_results['titulo'] = $row['titulo']; $query_results['telefono'] = $row['telefono']; $query_results['direccion'] = $row['direccion']; $query_results['foto'] = $row['foto']; $query_results['precio'] = $row['precio']; $query_results['contenido'] = $row['contenido']; $query_results['idusuario'] = $row['idusuario']; $query_results['id_cla_subcategoria'] = $row['id_cla_subcategoria']; $query_results['activo'] = $row['activo']; $query_results['fecha_creacion'] = $row['fecha_creacion']; $query_results['eliminado'] = $row['eliminado']; $query_results['idencode'] = $this->encrypt->encode($row['id']); $estado=""; if($row['activo'] == 0 ){ $estado='DESACTVADO'; }else if($row['activo'] == 1 ){ $estado='PENDIENTE'; }else if($row['activo'] == 2 ){ $estado='ACTIVO'; }else if($row['activo'] == 3 ){ $estado='CADUCADO'; }else{ $estado='DESACTVADO'; } $query_results['activo_desc'] = $estado; $query_results['fecha_fin'] = $row['fecha_fin']; $query_results['dias_fin'] = $row['dias_fin']; $query_results['x'] = $row['x']; $query_results['y'] = $row['y']; $query_results['distancia'] = 0; $query_results['visitas'] = $row['visitas']; $query_results['key'] = $row['key']; $query_results['tour_swf'] = $row['tour_swf']; if($query_results['idusuario']){ $this->ci->load->model('dx_auth/users', 'users'); $query_results['user'] = $rol=$this->ci->users->getUserUC($query_results['idusuario']); }else $query_results['user'] =null; $results[] = $query_results; } } return $results; } // TODO: this won't be possible if there is no primary key for the table. function retrieve_by_pkey($idField) { $results = array(); // Load the db library $this->load->database(); $ciu=$this->ubica->ciu; //ciu=$ciu $query = $this->db->query("SELECT *,fecha_creacion, fecha_creacion + 30 as fecha_fin, (fecha_creacion + 30)-CURRENT_DATE as dias_fin FROM cla_clasificado WHERE ciu=$ciu and eliminado=0 and id = $idField LIMIT 1"); // echo "SELECT *,fecha_creacion, fecha_creacion + 30 as fecha_fin, (fecha_creacion + 30)-CURRENT_DATE as dias_fin FROM cla_clasificado WHERE ciu=$ciu and eliminado=0 and id = $idField LIMIT 1"; if ($query->num_rows() > 0) { $row = $query->row_array(); $query_results['id'] = $row['id']; $query_results['titulo'] = $row['titulo']; $query_results['telefono'] = $row['telefono']; $query_results['direccion'] = $row['direccion']; $query_results['foto'] = $row['foto']; $query_results['precio'] = $row['precio']; $query_results['contenido'] = $row['contenido']; $query_results['idusuario'] = $row['idusuario']; $query_results['id_cla_subcategoria'] = $row['id_cla_subcategoria']; $query_results['activo'] = $row['activo']; $query_results['fecha_creacion'] = $row['fecha_creacion']; $query_results['eliminado'] = $row['eliminado']; $query_results['idencode'] = $this->encrypt->encode($row['id']); $estado=""; if($row['activo'] == 0 ){ $estado='DESACTVADO'; }else if($row['activo'] == 1 ){ $estado='PENDIENTE'; }else if($row['activo'] == 2 ){ $estado='ACTIVO'; }else if($row['activo'] == 3 ){ $estado='CADUCADO'; }else{ $estado='DESACTVADO'; } $query_results['activo_desc'] = $estado; $query_results['fecha_fin'] = $row['fecha_fin']; $query_results['dias_fin'] = $row['dias_fin']; $query_results['x'] = $row['x']; $query_results['y'] = $row['y']; $query_results['distancia'] = 0; $query_results['visitas'] = $row['visitas']; $query_results['key'] = $row['key']; $query_results['idusuario_temp'] = $row['idusuario_temp']; $query_results['activation_key'] = $row['activation_key']; $query_results['tour_swf'] = $row['tour_swf']; if($query_results['idusuario']){ $this->ci->load->model('dx_auth/users', 'users'); $query_results['user'] = $rol=$this->ci->users->getUserUC($query_results['idusuario']); $query_results['user_temp'] =null; }else{ $query_results['user'] =null; if($query_results['idusuario_temp']){ $this->ci->load->model('dx_auth/user_temp', 'user_temp'); $query_results['user_temp'] = $rol=$this->ci->user_temp->getUserUC($query_results['idusuario_temp']); }else{ $query_results['user_temp'] =null; } } $this->ci->load->model('cla_sub_categoria', 'clasubcat127'); $query_results['sub_categoria']= $this->ci->clasubcat127->retrieve_by_pkey($row['id_cla_subcategoria']); $results = $query_results; } else { $results = false; } return $results; } // TODO: this won't be possible if there is no primary key for the table. function retrieve_by_key($idField) { $results = array(); // Load the db library $this->load->database(); $ciu=$this->ubica->ciu; //ciu=$ciu $query = $this->db->query("SELECT *,fecha_creacion, fecha_creacion + 30 as fecha_fin, (fecha_creacion + 30)-CURRENT_DATE as dias_fin FROM cla_clasificado WHERE ciu=$ciu and eliminado=0 and key = '$idField' LIMIT 1"); // echo "SELECT *,fecha_creacion, fecha_creacion + 30 as fecha_fin, (fecha_creacion + 30)-CURRENT_DATE as dias_fin FROM cla_clasificado WHERE ciu=$ciu and eliminado=0 and id = $idField LIMIT 1"; if ($query->num_rows() > 0) { $row = $query->row_array(); $query_results['id'] = $row['id']; $query_results['titulo'] = $row['titulo']; $query_results['telefono'] = $row['telefono']; $query_results['direccion'] = $row['direccion']; $query_results['foto'] = $row['foto']; $query_results['precio'] = $row['precio']; $query_results['contenido'] = $row['contenido']; $query_results['idusuario'] = $row['idusuario']; $query_results['id_cla_subcategoria'] = $row['id_cla_subcategoria']; $query_results['activo'] = $row['activo']; $query_results['fecha_creacion'] = $row['fecha_creacion']; $query_results['eliminado'] = $row['eliminado']; $query_results['idencode'] = $this->encrypt->encode($row['id']); $estado=""; if($row['activo'] == 0 ){ $estado='DESACTVADO'; }else if($row['activo'] == 1 ){ $estado='PENDIENTE'; }else if($row['activo'] == 2 ){ $estado='ACTIVO'; }else if($row['activo'] == 3 ){ $estado='CADUCADO'; }else{ $estado='DESACTVADO'; } $query_results['activo_desc'] = $estado; $query_results['fecha_fin'] = $row['fecha_fin']; $query_results['dias_fin'] = $row['dias_fin']; $query_results['x'] = $row['x']; $query_results['y'] = $row['y']; $query_results['distancia'] = 0; $query_results['visitas'] = $row['visitas']; $query_results['key'] = $row['key']; $query_results['idusuario_temp'] = $row['idusuario_temp']; $query_results['activation_key'] = $row['activation_key']; $query_results['tour_swf'] = $row['tour_swf']; if($query_results['idusuario']){ $this->ci->load->model('dx_auth/users', 'users'); $query_results['user'] = $rol=$this->ci->users->getUserUC($query_results['idusuario']); $query_results['user_temp'] =null; }else{ $query_results['user'] =null; if($query_results['idusuario_temp']){ $this->ci->load->model('dx_auth/user_temp', 'user_temp'); $query_results['user_temp'] = $rol=$this->ci->user_temp->getUserUC($query_results['idusuario_temp']); }else{ $query_results['user_temp'] =null; } } $this->ci->load->model('cla_sub_categoria', 'clasubcat127'); $query_results['sub_categoria']= $this->ci->clasubcat127->retrieve_by_pkey($row['id_cla_subcategoria']); $results = $query_results; } else { $results = false; } return $results; } // TODO: this won't be possible if there is no primary key for the table. function retrieve_by_activation_key($idField) { $results = array(); // Load the db library $this->load->database(); $ciu=$this->ubica->ciu; //ciu=$ciu $query = $this->db->query("SELECT *,fecha_creacion, fecha_creacion + 30 as fecha_fin, (fecha_creacion + 30)-CURRENT_DATE as dias_fin FROM cla_clasificado WHERE ciu=$ciu and eliminado=0 and activation_key = '$idField' LIMIT 1"); // echo "SELECT *,fecha_creacion, fecha_creacion + 30 as fecha_fin, (fecha_creacion + 30)-CURRENT_DATE as dias_fin FROM cla_clasificado WHERE ciu=$ciu and eliminado=0 and id = $idField LIMIT 1"; if ($query->num_rows() > 0) { $row = $query->row_array(); $query_results['id'] = $row['id']; $query_results['titulo'] = $row['titulo']; $query_results['telefono'] = $row['telefono']; $query_results['direccion'] = $row['direccion']; $query_results['foto'] = $row['foto']; $query_results['precio'] = $row['precio']; $query_results['contenido'] = $row['contenido']; $query_results['idusuario'] = $row['idusuario']; $query_results['id_cla_subcategoria'] = $row['id_cla_subcategoria']; $query_results['activo'] = $row['activo']; $query_results['fecha_creacion'] = $row['fecha_creacion']; $query_results['eliminado'] = $row['eliminado']; $query_results['idencode'] = $this->encrypt->encode($row['id']); $estado=""; if($row['activo'] == 0 ){ $estado='DESACTVADO'; }else if($row['activo'] == 1 ){ $estado='PENDIENTE'; }else if($row['activo'] == 2 ){ $estado='ACTIVO'; }else if($row['activo'] == 3 ){ $estado='CADUCADO'; }else{ $estado='DESACTVADO'; } $query_results['activo_desc'] = $estado; $query_results['fecha_fin'] = $row['fecha_fin']; $query_results['dias_fin'] = $row['dias_fin']; $query_results['x'] = $row['x']; $query_results['y'] = $row['y']; $query_results['distancia'] = 0; $query_results['visitas'] = $row['visitas']; $query_results['key'] = $row['key']; $query_results['idusuario_temp'] = $row['idusuario_temp']; $query_results['activation_key'] = $row['activation_key']; $query_results['tour_swf'] = $row['tour_swf']; if($query_results['idusuario']){ $this->ci->load->model('dx_auth/users', 'users'); $query_results['user'] = $rol=$this->ci->users->getUserUC($query_results['idusuario']); $query_results['user_temp'] =null; }else{ $query_results['user'] =null; if($query_results['idusuario_temp']){ $this->ci->load->model('dx_auth/user_temp', 'user_temp'); $query_results['user_temp'] = $rol=$this->ci->user_temp->getUserUC($query_results['idusuario_temp']); }else{ $query_results['user_temp'] =null; } } $this->ci->load->model('cla_sub_categoria', 'clasubcat127'); $query_results['sub_categoria']= $this->ci->clasubcat127->retrieve_by_pkey($row['id_cla_subcategoria']); $results = $query_results; } else { $results = false; } return $results; } function add( $data ) { // Load the database library $this->load->database(); $data["ciu"]=$this->ubica->ciu; // Build up the SQL query string $sql = $this->db->insert_string('cla_clasificado', $data); $query = $this->db->query($sql); return $this->db->insert_id(); } function modify($keyvalue, $data) { // Load the database library $this->load->database(); // Build up the SQL query string $where = "id = $keyvalue"; $sql = $this->db->update_string('cla_clasificado', $data, $where); $query = $this->db->query($sql); } function delete_by_pkey($idField) { // Load the db library $this->load->database(); // /////////////////////////////////////////////////////////////////////// // TODO: Just to eliminate nasty mishaps, the delete query has been // TODO: ...deliberately disabled. Enable it if you mean to by uncommenting // TODO: ...the query function call below // /////////////////////////////////////////////////////////////////////// // $query = $this->db->query("DELETE FROM cla_clasificado WHERE id = '$idField' "); return true; } // Function used to initilialise class variables. // NOTE: Not particularly useful unless you are using model persistence // NOTE: You may want to add default values here. function getByUser($idUsuario) { $results = array(); $this->load->database(); $ciu=$this->ubica->ciu; $sql="select *,fecha_creacion, fecha_creacion + 30 as fecha_fin, (fecha_creacion + 30)-CURRENT_DATE as dias_fin from cla_clasificado where ciu=$ciu and idusuario=$idUsuario and eliminado=0 order by id"; $query = $this->db->query($sql); if ($query->num_rows() > 0) { foreach ($query->result_array() as $row) // Go through the result set { $query_results['id'] = $row['id']; $query_results['titulo'] = $row['titulo']; $query_results['telefono'] = $row['telefono']; $query_results['direccion'] = $row['direccion']; $query_results['foto'] = $row['foto']; $query_results['precio'] = $row['precio']; $query_results['contenido'] = $row['contenido']; $query_results['idusuario'] = $row['idusuario']; $query_results['id_cla_subcategoria'] = $row['id_cla_subcategoria']; $query_results['activo'] = $row['activo']; $query_results['fecha_creacion'] = $row['fecha_creacion']; $query_results['eliminado'] = $row['eliminado']; $query_results['idencode'] = $this->encrypt->encode($row['id']); $estado=""; if($row['activo'] == 0 ){ $estado='DESACTVADO'; }else if($row['activo'] == 1 ){ $estado='PENDIENTE'; }else if($row['activo'] == 2 ){ $estado='ACTIVO'; }else if($row['activo'] == 3 ){ $estado='CADUCADO'; }else{ $estado='DESACTVADO'; } $query_results['activo_desc'] = $estado; $query_results['fecha_fin'] = $row['fecha_fin']; $query_results['dias_fin'] = $row['dias_fin']; $query_results['x'] = $row['x']; $query_results['y'] = $row['y']; $query_results['distancia'] = 0; $query_results['visitas'] = $row['visitas']; if($query_results['idusuario']){ $this->ci->load->model('dx_auth/users', 'users'); $query_results['user'] = $rol=$this->ci->users->getUserUC($query_results['idusuario']); $query_results['user_temp'] =null; }else{ $query_results['user'] =null; if($query_results['idusuario_temp']){ $this->ci->load->model('dx_auth/user_temp', 'user_temp'); $query_results['user_temp'] = $rol=$this->ci->user_temp->getUserUC($query_results['idusuario_temp']); }else{ $query_results['user_temp'] =null; } } $results[] = $query_results; } } return $results; } function verificarPropietario($idUsuario,$idClasificado) { $results = array(); $this->load->database(); $ciu=$this->ubica->ciu; $sql="select * ,fecha_creacion, fecha_creacion + 30 as fecha_fin, (fecha_creacion + 30)-CURRENT_DATE as dias_fin from cla_clasificado where ciu=$ciu and idusuario=$idUsuario and id=$idClasificado and eliminado=0 "; $query = $this->db->query($sql); if ($query->num_rows() > 0) { foreach ($query->result_array() as $row) { $query_results['id'] = $row['id']; $results[] = $query_results; } $res=true; }else $res=false; return $res; } function eliminar_logica($idClasificado){ // Load the db library $this->load->database(); // /////////////////////////////////////////////////////////////////////// // TODO: Just to eliminate nasty mishaps, the delete query has been // TODO: ...deliberately disabled. Enable it if you mean to by uncommenting // TODO: ...the query function call below // /////////////////////////////////////////////////////////////////////// $query = $this->db->query("UPDATE cla_clasificado SET eliminado=1 WHERE id = $idClasificado "); return true; } //Buscar Ventas con distancia medida desde (x,y) function searchVentas($cadena,$max, $px, $py) { $results = array(); $this->load->database(); $ciu=$this->ubica->ciu; //$sql="select * from buscarsubcategoria_distance('$cadena',$px,$py)"; $sql="SELECT *,X(the_geom) as x, Y(the_geom) as y, CAST('0.0' AS REAL) as rank ,cast(st_distance(the_geom,GeomFromText( 'POINT('||$px||' '|| $py ||')',32717)) as numeric(10,2)) as distancia FROM cla_clasificado WHERE ciu=$ciu and eliminado=0 and activo=2 and id_cla_subcategoria=$cadena ORDER BY distancia"; //echo $sql; $query = $this->db->query($sql); if ($query->num_rows() > 0) { $this->ci->load->model('dx_auth/users', 'users'); foreach ($query->result_array() as $row) // Go through the result set { $query_results['id'] = $row['id']; $query_results['titulo'] = $row['titulo']; $query_results['telefono'] = $row['telefono']; $query_results['direccion'] = $row['direccion']; $query_results['foto'] = $row['foto']; $query_results['precio'] = $row['precio']; $query_results['contenido'] = $row['contenido']; $query_results['idusuario'] = $row['idusuario']; $query_results['id_cla_subcategoria'] = $row['id_cla_subcategoria']; $query_results['activo'] = $row['activo']; $query_results['fecha_creacion'] = $row['fecha_creacion']; $query_results['eliminado'] = $row['eliminado']; $query_results['activo'] = $row['activo']; $query_results['x'] = $row['x']; $query_results['y'] = $row['y']; $query_results['rank'] = $row['rank']; $query_results['distancia'] = $row['distancia']; $query_results['x'] = $row['x']; $query_results['y'] = $row['y']; $query_results['distancia'] = 0; $query_results['visitas'] = $row['visitas']; if($query_results['idusuario']){ $query_results['user'] = $rol=$this->ci->users->getUserUC($query_results['idusuario']); }else $query_results['user'] =null; $results[] = $query_results; } } return $results; } //Buscar Clasificados de una Categoria(inmo, vehiculos, varios, arriendos) con distancia medida desde (x,y) function searchClaCategoria($categoria, $px, $py, $max=30) { $results = array(); $this->load->database(); $ciu=$this->ubica->ciu; $order="distancia"; if($px==0 && $py==0){ $order="cla_clasificado.id desc"; } //$sql="select * from buscarsubcategoria_distance('$cadena',$px,$py)"; $sql="SELECT *,cla_clasificado.id as idcla,X(the_geom) as x, Y(the_geom) as y, CAST('0.0' AS REAL) as rank ,cast(st_distance(the_geom,GeomFromText( 'POINT('||$px||' '|| $py ||')',32717)) as numeric(10,2)) as distancia FROM cla_clasificado, cla_categoria cat, cla_sub_categoria subcat WHERE ciu=$ciu and eliminado=0 and activo=2 and id_cla_subcategoria=subcat.id and cat.id=subcat.id_cla_categoria and cat.id=$categoria ORDER BY distancia limit $max"; //echo $sql; $query = $this->db->query($sql); if ($query->num_rows() > 0) { $this->ci->load->model('dx_auth/users', 'users'); foreach ($query->result_array() as $row) // Go through the result set { $query_results['id'] = $row['idcla']; $query_results['titulo'] = $row['titulo']; $query_results['telefono'] = $row['telefono']; $query_results['direccion'] = $row['direccion']; $query_results['foto'] = $row['foto']; $query_results['precio'] = $row['precio']; $query_results['contenido'] = $row['contenido']; $query_results['idusuario'] = $row['idusuario']; $query_results['id_cla_subcategoria'] = $row['id_cla_subcategoria']; $query_results['activo'] = $row['activo']; $query_results['fecha_creacion'] = $row['fecha_creacion']; $query_results['eliminado'] = $row['eliminado']; $query_results['activo'] = $row['activo']; $query_results['x'] = $row['x']; $query_results['y'] = $row['y']; $query_results['rank'] = $row['rank']; $query_results['distancia'] = $row['distancia']; $query_results['x'] = $row['x']; $query_results['y'] = $row['y']; $query_results['distancia'] = $row['distancia']; $query_results['visitas'] = $row['visitas']; if($query_results['idusuario']){ $query_results['user'] = $rol=$this->ci->users->getUserUC($query_results['idusuario']); }else{ $query_results['user'] =null; } $results[] = $query_results; } } return $results; } function searchClaCategoriaSimilares($idcategoria,$idsubcategoria, $px, $py, $max=30) { $results = array(); $this->load->database(); $ciu=$this->ubica->ciu; $order="distancia"; if($px==0 && $py==0){ $order="cla_clasificado.id desc"; } //$sql="select * from buscarsubcategoria_distance('$cadena',$px,$py)"; $sql="SELECT *,cla_clasificado.id as idcla,X(the_geom) as x, Y(the_geom) as y, CAST('0.0' AS REAL) as rank ,cast(st_distance(the_geom,GeomFromText( 'POINT('||$px||' '|| $py ||')',32717)) as numeric(10,2)) as distancia FROM cla_clasificado, cla_categoria cat, cla_sub_categoria subcat WHERE ciu=$ciu and eliminado=0 and activo=2 and id_cla_subcategoria=subcat.id and cat.id=subcat.id_cla_categoria and cat.id=$idcategoria and subcat.id<>$idsubcategoria ORDER BY distancia limit $max"; //echo $sql; $query = $this->db->query($sql); if ($query->num_rows() > 0) { $this->ci->load->model('dx_auth/users', 'users'); foreach ($query->result_array() as $row) // Go through the result set { $query_results['id'] = $row['idcla']; $query_results['titulo'] = $row['titulo']; $query_results['telefono'] = $row['telefono']; $query_results['direccion'] = $row['direccion']; $query_results['foto'] = $row['foto']; $query_results['precio'] = $row['precio']; $query_results['contenido'] = $row['contenido']; $query_results['idusuario'] = $row['idusuario']; $query_results['id_cla_subcategoria'] = $row['id_cla_subcategoria']; $query_results['activo'] = $row['activo']; $query_results['fecha_creacion'] = $row['fecha_creacion']; $query_results['eliminado'] = $row['eliminado']; $query_results['activo'] = $row['activo']; $query_results['x'] = $row['x']; $query_results['y'] = $row['y']; $query_results['rank'] = $row['rank']; $query_results['distancia'] = $row['distancia']; $query_results['x'] = $row['x']; $query_results['y'] = $row['y']; $query_results['distancia'] = $row['distancia']; $query_results['visitas'] = $row['visitas']; if($query_results['idusuario']){ $query_results['user'] = $rol=$this->ci->users->getUserUC($query_results['idusuario']); }else $query_results['user'] =null; $results[] = $query_results; } } return $results; } //Buscar Clasificados de una Categoria(inmo, vehiculos, varios, arriendos) con distancia medida desde (x,y) function searchClaSubCategoria($subcategoria, $px, $py) { $results = array(); $this->load->database(); $ciu=$this->ubica->ciu; //$sql="select * from buscarsubcategoria_distance('$cadena',$px,$py)"; $order="distancia"; if($px==0 && $py==0){ $order="cla_clasificado.id desc"; } $sql="SELECT *,cla_clasificado.id as idcla,X(the_geom) as x, Y(the_geom) as y, CAST('0.0' AS REAL) as rank ,cast(st_distance(the_geom,GeomFromText( 'POINT('||$px||' '|| $py ||')',32717)) as numeric(10,2)) as distancia FROM cla_clasificado, cla_categoria cat, cla_sub_categoria subcat WHERE ciu=$ciu and eliminado=0 and activo=2 and id_cla_subcategoria=subcat.id and cat.id=subcat.id_cla_categoria and subcat.id=$subcategoria ORDER BY $order"; //echo $sql; $query = $this->db->query($sql); if ($query->num_rows() > 0) { $this->ci->load->model('dx_auth/users', 'users'); foreach ($query->result_array() as $row) // Go through the result set { $query_results['id'] = $row['idcla']; $query_results['titulo'] = $row['titulo']; $query_results['telefono'] = $row['telefono']; $query_results['direccion'] = $row['direccion']; $query_results['foto'] = $row['foto']; $query_results['precio'] = $row['precio']; $query_results['contenido'] = $row['contenido']; $query_results['idusuario'] = $row['idusuario']; $query_results['id_cla_subcategoria'] = $row['id_cla_subcategoria']; $query_results['activo'] = $row['activo']; $query_results['fecha_creacion'] = $row['fecha_creacion']; $query_results['eliminado'] = $row['eliminado']; $query_results['activo'] = $row['activo']; $query_results['x'] = $row['x']; $query_results['y'] = $row['y']; $query_results['rank'] = $row['rank']; $query_results['distancia'] = $row['distancia']; $query_results['x'] = $row['x']; $query_results['y'] = $row['y']; $query_results['distancia'] = $row['distancia']; $query_results['visitas'] = $row['visitas']; if($query_results['idusuario']){ $query_results['user'] = $rol=$this->ci->users->getUserUC($query_results['idusuario']); }else $query_results['user'] =null; $results[] = $query_results; } } return $results; } function searchUltimos($px, $py,$max=30) { $results = array(); $this->load->database(); $ciu=$this->ubica->ciu; //$sql="select * from buscarsubcategoria_distance('$cadena',$px,$py)"; $sql="SELECT *,X(the_geom) as x, Y(the_geom) as y, CAST('0.0' AS REAL) as rank ,cast(st_distance(the_geom,GeomFromText( 'POINT('||$px||' '|| $py ||')',32717)) as numeric(10,2)) as distancia FROM cla_clasificado WHERE ciu=$ciu and eliminado=0 and activo=2 ORDER BY id desc,distancia limit $max"; $query = $this->db->query($sql); if ($query->num_rows() > 0) { $this->ci->load->model('dx_auth/users', 'users'); foreach ($query->result_array() as $row) // Go through the result set { $query_results['id'] = $row['id']; $query_results['titulo'] = $row['titulo']; $query_results['telefono'] = $row['telefono']; $query_results['direccion'] = $row['direccion']; $query_results['foto'] = $row['foto']; $query_results['precio'] = $row['precio']; $query_results['contenido'] = $row['contenido']; $query_results['idusuario'] = $row['idusuario']; $query_results['id_cla_subcategoria'] = $row['id_cla_subcategoria']; $query_results['activo'] = $row['activo']; $query_results['fecha_creacion'] = $row['fecha_creacion']; $query_results['eliminado'] = $row['eliminado']; $query_results['activo'] = $row['activo']; $query_results['x'] = $row['x']; $query_results['y'] = $row['y']; $query_results['rank'] = $row['rank']; $query_results['distancia'] = $row['distancia']; $query_results['x'] = $row['x']; $query_results['y'] = $row['y']; $query_results['distancia'] = 0; $query_results['visitas'] = $row['visitas']; if($query_results['idusuario']){ $query_results['user'] = $rol=$this->ci->users->getUserUC($query_results['idusuario']); }else $query_results['user'] =null; $results[] = $query_results; } } return $results; } function searchRandom() { $results = array(); $this->load->database(); $ciu=$this->ubica->ciu; //$sql="select * from buscarsubcategoria_distance('$cadena',$px,$py)"; $sql="SELECT *,X(the_geom) as x, Y(the_geom) as y, CAST('0.0' AS REAL) as rank ,0 as distancia FROM cla_clasificado WHERE ciu=$ciu and eliminado=0 and activo=2 ORDER BY id,distancia limit 15"; $query = $this->db->query($sql); if ($query->num_rows() > 0) { $this->ci->load->model('dx_auth/users', 'users'); foreach ($query->result_array() as $row) // Go through the result set { $query_results['id'] = $row['id']; $query_results['titulo'] = $row['titulo']; $query_results['telefono'] = $row['telefono']; $query_results['direccion'] = $row['direccion']; $query_results['foto'] = $row['foto']; $query_results['precio'] = $row['precio']; $query_results['contenido'] = $row['contenido']; $query_results['idusuario'] = $row['idusuario']; $query_results['id_cla_subcategoria'] = $row['id_cla_subcategoria']; $query_results['activo'] = $row['activo']; $query_results['fecha_creacion'] = $row['fecha_creacion']; $query_results['eliminado'] = $row['eliminado']; $query_results['activo'] = $row['activo']; $query_results['x'] = $row['x']; $query_results['y'] = $row['y']; $query_results['rank'] = $row['rank']; $query_results['distancia'] = $row['distancia']; $query_results['x'] = $row['x']; $query_results['y'] = $row['y']; $query_results['distancia'] = 0; $query_results['visitas'] = $row['visitas']; if($query_results['idusuario']){ $query_results['user'] = $rol=$this->ci->users->getUserUC($query_results['idusuario']); }else $query_results['user'] =null; $results[] = $query_results; } } return $results; } function searchUltimos2() { $results = array(); $this->load->database(); $ciu=$this->ubica->ciu; //$sql="select * from buscarsubcategoria_distance('$cadena',$px,$py)"; $sql="SELECT *,X(the_geom) as x, Y(the_geom) as y, CAST('0.0' AS REAL) as rank ,0 as distancia FROM cla_clasificado WHERE ciu=$ciu and eliminado=0 and activo=2 ORDER BY id,distancia limit 15"; $query = $this->db->query($sql); if ($query->num_rows() > 0) { $this->ci->load->model('dx_auth/users', 'users'); foreach ($query->result_array() as $row) // Go through the result set { $query_results['id'] = $row['id']; $query_results['titulo'] = $row['titulo']; $query_results['telefono'] = $row['telefono']; $query_results['direccion'] = $row['direccion']; $query_results['foto'] = $row['foto']; $query_results['precio'] = $row['precio']; $query_results['contenido'] = $row['contenido']; $query_results['idusuario'] = $row['idusuario']; $query_results['id_cla_subcategoria'] = $row['id_cla_subcategoria']; $query_results['activo'] = $row['activo']; $query_results['fecha_creacion'] = $row['fecha_creacion']; $query_results['eliminado'] = $row['eliminado']; $query_results['activo'] = $row['activo']; $query_results['x'] = $row['x']; $query_results['y'] = $row['y']; $query_results['rank'] = $row['rank']; $query_results['distancia'] = $row['distancia']; $query_results['x'] = $row['x']; $query_results['y'] = $row['y']; $query_results['distancia'] = 0; $query_results['visitas'] = $row['visitas']; if($query_results['idusuario']){ $query_results['user'] = $rol=$this->ci->users->getUserUC($query_results['idusuario']); }else $query_results['user'] =null; $results[] = $query_results; } } return $results; } //todos los clasificados para SITEMAP CLASIFICADOS function todos() { $results = array(); $this->load->database(); $ciu=$this->ubica->ciu; //$sql="select * from buscarsubcategoria_distance('$cadena',$px,$py)"; $sql="SELECT *,X(the_geom) as x, Y(the_geom) as y, CAST('0.0' AS REAL) as rank ,0 as distancia FROM cla_clasificado WHERE ciu=$ciu and eliminado=0 ORDER BY id,distancia"; $query = $this->db->query($sql); if ($query->num_rows() > 0) { $this->ci->load->model('dx_auth/users', 'users'); foreach ($query->result_array() as $row) // Go through the result set { $query_results['id'] = $row['id']; $query_results['titulo'] = $row['titulo']; $query_results['telefono'] = $row['telefono']; $query_results['direccion'] = $row['direccion']; $query_results['foto'] = $row['foto']; $query_results['precio'] = $row['precio']; $query_results['contenido'] = $row['contenido']; $query_results['idusuario'] = $row['idusuario']; $query_results['id_cla_subcategoria'] = $row['id_cla_subcategoria']; $query_results['activo'] = $row['activo']; $query_results['fecha_creacion'] = $row['fecha_creacion']; $query_results['eliminado'] = $row['eliminado']; $query_results['activo'] = $row['activo']; $query_results['x'] = $row['x']; $query_results['y'] = $row['y']; $query_results['rank'] = $row['rank']; $query_results['distancia'] = $row['distancia']; $query_results['x'] = $row['x']; $query_results['y'] = $row['y']; $query_results['distancia'] = 0; $query_results['visitas'] = $row['visitas']; if($query_results['idusuario']){ $query_results['user'] = $rol=$this->ci->users->getUserUC($query_results['idusuario']); }else $query_results['user'] =null; $results[] = $query_results; } } return $results; } /***** * Para generar todo un KML para el sitemap de todos los Clasificados * */ function getAllKml() { $results = array(); $this->load->database(); //$sql="select st_askml(the_geom) as kml, * from informacion i "; $ciu=$this->ubica->ciu; $sql="select *,st_askml(the_geom) as kml from ciu=$ciu and cla_clasificado c order by c.id"; //echo $sql; $query = $this->db->query($sql); if ($query->num_rows() > 0) { foreach ($query->result_array() as $row) // Go through the result set { $query_results['id'] = $row['id']; $query_results['titulo'] = $row['titulo']; $query_results['telefono'] = $row['telefono']; $query_results['direccion'] = $row['direccion']; $query_results['foto'] = $row['foto']; $query_results['precio'] = $row['precio']; $query_results['contenido'] = $row['contenido']; $query_results['idusuario'] = $row['idusuario']; $query_results['id_cla_subcategoria'] = $row['id_cla_subcategoria']; $query_results['activo'] = $row['activo']; $query_results['fecha_creacion'] = $row['fecha_creacion']; $query_results['eliminado'] = $row['eliminado']; $query_results['activo'] = $row['activo']; $query_results['kml'] = $row['kml']; $results[] = $query_results; } } return $results; } function ultimosCercanos($x,$y,$notInActividades="",$limite=6) { $results = array(); $this->load->database(); if($notInActividades!= "") $notInActividades= "and id not in (".$notInActividades.")"; $ciu=$this->ubica->ciu; //$sql="select * from buscarsubcategoria_distance('$cadena',$px,$py)"; $sql=" select *,cast(st_distance(the_geom,GeomFromText( 'POINT($x $y )',32717)) as numeric(10,2)) as distancia from cla_clasificado where ciu=$ciu and activo=2 and eliminado=0 $notInActividades order by distancia limit $limite"; $query = $this->db->query($sql); if ($query->num_rows() > 0) { $this->ci->load->model('dx_auth/users', 'users'); foreach ($query->result_array() as $row) // Go through the result set { $query_results['id'] = $row['id']; $query_results['titulo'] = $row['titulo']; $query_results['telefono'] = $row['telefono']; $query_results['direccion'] = $row['direccion']; $query_results['foto'] = $row['foto']; $query_results['precio'] = $row['precio']; $query_results['contenido'] = $row['contenido']; $query_results['idusuario'] = $row['idusuario']; $query_results['id_cla_subcategoria'] = $row['id_cla_subcategoria']; $query_results['activo'] = $row['activo']; $query_results['fecha_creacion'] = $row['fecha_creacion']; $query_results['eliminado'] = $row['eliminado']; $query_results['activo'] = $row['activo']; $query_results['x'] = $row['x']; $query_results['y'] = $row['y']; $query_results['rank'] = 0; $query_results['distancia'] = $row['distancia']; $query_results['x'] = $row['x']; $query_results['y'] = $row['y']; $query_results['distancia'] = $row['distancia']; $query_results['visitas'] = $row['visitas']; $query_results['user'] = null;//$rol=$this->ci->users->getUserUC($query_results['idusuario']); $results[] = $query_results; } } return $results; } function incrementarVisita($idClasificado){ // Load the db library $this->load->database(); // /////////////////////////////////////////////////////////////////////// // TODO: Just to eliminate nasty mishaps, the delete query has been // TODO: ...deliberately disabled. Enable it if you mean to by uncommenting // TODO: ...the query function call below // /////////////////////////////////////////////////////////////////////// $query = $this->db->query("UPDATE cla_clasificado SET visitas=visitas+1 WHERE id = $idClasificado "); return true; } function searchUltimosDestacados($px, $py,$max=5) { $results = array(); $this->load->database(); $ciu=$this->ubica->ciu; //$sql="select * from buscarsubcategoria_distance('$cadena',$px,$py)"; $sql="SELECT *,X(the_geom) as x, Y(the_geom) as y, CAST('0.0' AS REAL) as rank ,cast(st_distance(the_geom,GeomFromText( 'POINT('||$px||' '|| $py ||')',32717)) as numeric(10,2)) as distancia FROM cla_clasificado WHERE ciu=$ciu and eliminado=0 and activo=2 and destacado=1 ORDER BY id desc,distancia limit $max"; $query = $this->db->query($sql); if ($query->num_rows() > 0) { $this->ci->load->model('dx_auth/users', 'users'); foreach ($query->result_array() as $row) // Go through the result set { $query_results['id'] = $row['id']; $query_results['titulo'] = $row['titulo']; $query_results['telefono'] = $row['telefono']; $query_results['direccion'] = $row['direccion']; $query_results['foto'] = $row['foto']; $query_results['precio'] = $row['precio']; $query_results['contenido'] = $row['contenido']; $query_results['idusuario'] = $row['idusuario']; $query_results['id_cla_subcategoria'] = $row['id_cla_subcategoria']; $query_results['activo'] = $row['activo']; $query_results['fecha_creacion'] = $row['fecha_creacion']; $query_results['eliminado'] = $row['eliminado']; $query_results['activo'] = $row['activo']; $query_results['x'] = $row['x']; $query_results['y'] = $row['y']; $query_results['rank'] = $row['rank']; $query_results['distancia'] = $row['distancia']; $query_results['x'] = $row['x']; $query_results['y'] = $row['y']; $query_results['distancia'] = 0; $query_results['visitas'] = $row['visitas']; if($query_results['idusuario']){ $query_results['user'] = $rol=$this->ci->users->getUserUC($query_results['idusuario']); }else $query_results['user'] =null; $results[] = $query_results; } } return $results; } function searchUltimosConTour($max=5) { $results = array(); $this->load->database(); $ciu=$this->ubica->ciu; $px=$this->ubica->x; $py=$this->ubica->y; //$sql="select * from buscarsubcategoria_distance('$cadena',$px,$py)"; $sql="SELECT *,X(the_geom) as x, Y(the_geom) as y, CAST('0.0' AS REAL) as rank ,cast(st_distance(the_geom,GeomFromText( 'POINT('||$px||' '|| $py ||')',32717)) as numeric(10,2)) as distancia FROM cla_clasificado WHERE ciu=$ciu and eliminado=0 and activo=2 and tour_swf<>'' ORDER BY id desc,distancia limit $max"; $query = $this->db->query($sql); if ($query->num_rows() > 0) { $this->ci->load->model('dx_auth/users', 'users'); foreach ($query->result_array() as $row) // Go through the result set { $query_results['id'] = $row['id']; $query_results['titulo'] = $row['titulo']; $query_results['telefono'] = $row['telefono']; $query_results['direccion'] = $row['direccion']; $query_results['foto'] = $row['foto']; $query_results['precio'] = $row['precio']; $query_results['contenido'] = $row['contenido']; $query_results['idusuario'] = $row['idusuario']; $query_results['id_cla_subcategoria'] = $row['id_cla_subcategoria']; $query_results['activo'] = $row['activo']; $query_results['fecha_creacion'] = $row['fecha_creacion']; $query_results['eliminado'] = $row['eliminado']; $query_results['activo'] = $row['activo']; $query_results['x'] = $row['x']; $query_results['y'] = $row['y']; $query_results['rank'] = $row['rank']; $query_results['distancia'] = $row['distancia']; $query_results['x'] = $row['x']; $query_results['y'] = $row['y']; $query_results['distancia'] = 0; $query_results['visitas'] = $row['visitas']; if($query_results['idusuario']){ $query_results['user'] = $rol=$this->ci->users->getUserUC($query_results['idusuario']); }else $query_results['user'] =null; $results[] = $query_results; } } return $results; } } ?>