dev-chiefworks
277 lines
8.4 KiB
PHP
277 lines
8.4 KiB
PHP
<?php if (!defined('BASEPATH')) {
|
|
exit('No direct script access allowed');
|
|
}
|
|
|
|
class Geofence_area_city_model extends CI_Model
|
|
{
|
|
// city status
|
|
const ACTIVE_STATUS = 1;
|
|
const INACTIVE_STATUS = 0;
|
|
|
|
private $read_replica;
|
|
|
|
public function __construct()
|
|
{
|
|
parent::__construct();
|
|
$this->read_replica = $this->load->database('savvy_replica', TRUE);
|
|
}
|
|
|
|
public function getAllWithPagination($page = 1, $perPage = 10, $filterData = [])
|
|
{
|
|
$offset = ($page - 1) * $perPage;
|
|
if ($offset < 0) {
|
|
$offset = 0;
|
|
}
|
|
|
|
$whereQuery = 'WHERE 1=1';
|
|
if (!empty($filterData)) {
|
|
if (!empty($filterData['country_code'])) {
|
|
$whereQuery .= ' AND geo.country = ' . $this->read_replica->escape(strtoupper($filterData['country_code'])) . ' ';
|
|
}
|
|
if (!empty($filterData['search_text'])) {
|
|
$search_text = $this->read_replica->escape_like_str($filterData['search_text']);
|
|
$whereQuery .= ' AND geo.city ILIKE \'%' . $search_text . '%\'';
|
|
}
|
|
if (isset($filterData['status']) && $filterData['status'] != "all") {
|
|
$status = $this->read_replica->escape($filterData['status']);
|
|
$whereQuery .= ' AND geo.status =' . $status;
|
|
}
|
|
}
|
|
|
|
$totalQueryString =
|
|
'SELECT
|
|
geo.id, geo.city as city_name, geo.country as country_code, geo.latitude, geo.longitude, geo.radius,
|
|
country.country as country_name, geo.status
|
|
FROM
|
|
geofence_area_city as geo
|
|
JOIN country ON country.code = geo.country
|
|
' . $whereQuery;
|
|
//echo $totalQueryString;exit;
|
|
$queryString =
|
|
'SELECT
|
|
geo.id, geo.city as city_name, geo.country as country_code, geo.latitude, geo.longitude, geo.radius,
|
|
country.country as country_name, geo.status
|
|
FROM
|
|
geofence_area_city as geo
|
|
JOIN country ON country.code = geo.country
|
|
' . $whereQuery . '
|
|
ORDER BY id DESC
|
|
LIMIT ' . pg_escape_string($perPage) . ' OFFSET ' . pg_escape_string($offset);
|
|
|
|
$totalQuery = $this->read_replica->query($totalQueryString);
|
|
$query = $this->read_replica->query($queryString);
|
|
|
|
return [
|
|
'total' => count($totalQuery->result()),
|
|
'data' => $query->result(),
|
|
'page' => $page,
|
|
];
|
|
}
|
|
|
|
public function getItem($id)
|
|
{
|
|
$queryString =
|
|
'SELECT
|
|
geo.id, geo.city as city_name, geo.country as country_code, geo.latitude, geo.longitude, geo.radius,
|
|
country.country as country_name, geo.status
|
|
FROM
|
|
geofence_area_city as geo
|
|
JOIN country ON country.code = geo.country
|
|
WHERE
|
|
geo.id = ?
|
|
LIMIT 1
|
|
';
|
|
|
|
$query = $this->read_replica->query($queryString, $id);
|
|
return $query->row_array();
|
|
}
|
|
|
|
public function create($inputData)
|
|
{
|
|
$this->db->insert('geofence_area_city', $inputData);
|
|
$insertId = $this->db->insert_id();
|
|
|
|
// update location field
|
|
$this->db->query("UPDATE geofence_area_city SET location = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326) WHERE id = {$insertId} ");
|
|
}
|
|
|
|
public function update($id, $inputData)
|
|
{
|
|
$this->db->where('id', $id);
|
|
$this->db->update('geofence_area_city', $inputData);
|
|
|
|
// update location field
|
|
$this->db->query("UPDATE geofence_area_city SET location = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326) WHERE id = {$id} ");
|
|
return $this->db->affected_rows();
|
|
}
|
|
|
|
public function delete($id)
|
|
{
|
|
$this->db->delete('geofence_area_city', ['id' => $id]);
|
|
}
|
|
|
|
public function getCities($condtions = '')
|
|
{
|
|
if ($condtions != '') {
|
|
$q = "SELECT id, city FROM geofence_area_city WHERE ".$condtions." ORDER BY id ASC";
|
|
$r = pg_query($q);
|
|
$query = $this->read_replica->query($q);
|
|
return $query->result();
|
|
}
|
|
return null;
|
|
}
|
|
|
|
public function getDuplicatedCities()
|
|
{
|
|
$q = "SELECT city, country, count(*)
|
|
FROM geofence_area_city
|
|
GROUP BY city,
|
|
country
|
|
HAVING count(*)>1
|
|
ORDER BY count(*) DESC";
|
|
$query = $this->read_replica->query($q);
|
|
return $query->result();
|
|
}
|
|
|
|
public function updateAddresses($condition,$data)
|
|
{
|
|
$this->db->where($condition);
|
|
$this->db->update('address', $data);
|
|
return $this->db->affected_rows();
|
|
}
|
|
|
|
public function deleteUnusedCities()
|
|
{
|
|
global $conn;
|
|
$q = "DELETE
|
|
FROM geofence_area_city
|
|
WHERE id NOT IN (SELECT DISTINCT city_id FROM address);";
|
|
$this->db->query($q);
|
|
return $this->db->affected_rows();
|
|
}
|
|
|
|
public function getCityIDByName($city_name) {
|
|
$q = $this->read_replica->select('id')
|
|
->from('geofence_area_city')
|
|
->where('lower(city)', strtolower($city_name))
|
|
->get();
|
|
|
|
return $q->result_array() ? $q->result_array()['id'] : NULL;
|
|
}
|
|
|
|
public function getCityByCoordindates($params) {
|
|
$res = SQEAPI::get('geofencearea/api/geofencearea/cityApi', $params);
|
|
|
|
if (isset($res['error'])) {
|
|
return ['success' => false, 'error' => $res['error']];
|
|
}
|
|
|
|
$data = array();
|
|
foreach ($res['data'] as $addr) {
|
|
$data[] = (array)($addr);
|
|
}
|
|
|
|
return $data;
|
|
}
|
|
|
|
public function getCityWithFilter($params)
|
|
{
|
|
$page = $params['page'] ?? 1;
|
|
$per_page = $params['per_page'] ?? 20;
|
|
|
|
$offset = ($page - 1) * $per_page;
|
|
if ($offset < 0) {
|
|
$offset = 0;
|
|
}
|
|
|
|
$whereQuery = ' WHERE 1 = 1 ';
|
|
if (!empty($params)) {
|
|
if (!empty($params['city_name'])) {
|
|
$search_text = $this->read_replica->escape_like_str($params['city_name']);
|
|
$whereQuery .= ' AND city ILIKE \'%' . $search_text . '%\'';
|
|
}
|
|
}
|
|
|
|
$totalQueryString = '
|
|
SELECT id
|
|
FROM geofence_area_city
|
|
' . $whereQuery;
|
|
|
|
$queryString = '
|
|
SELECT *
|
|
FROM geofence_area_city
|
|
' . $whereQuery . '
|
|
ORDER BY city ASC
|
|
LIMIT ' . pg_escape_string($per_page) . ' OFFSET ' . pg_escape_string($offset);
|
|
|
|
$totalQuery = $this->read_replica->query($totalQueryString);
|
|
$query = $this->read_replica->query($queryString);
|
|
|
|
return [
|
|
'total' => count($totalQuery->result()),
|
|
'data' => $query->result(),
|
|
'page' => $page,
|
|
];
|
|
}
|
|
|
|
public function getRecordByCityAndCountry($data) {
|
|
$this->read_replica
|
|
->select('id')
|
|
->from('geofence_area_city')
|
|
->where('city', $data['city'])
|
|
->where('country', $data['country']);
|
|
|
|
return $this->read_replica->get()->result_array();
|
|
}
|
|
|
|
public function getCityQueryWithFilter($filters = [], $count_record = true)
|
|
{
|
|
$numeric_array = [
|
|
'status' => 'status',
|
|
];
|
|
|
|
$string_array = [
|
|
'search_text' => 'city',
|
|
'country_code' => 'country'
|
|
];
|
|
|
|
$count_record ?
|
|
$this->read_replica->select(['count(*) AS all_count']) :
|
|
$this->read_replica->select([
|
|
'id',
|
|
'city',
|
|
'country',
|
|
'latitude',
|
|
'longitude',
|
|
'location',
|
|
'radius',
|
|
'status',
|
|
]);
|
|
|
|
$this->read_replica->from('geofence_area_city');
|
|
|
|
foreach($filters as $key => $val) {
|
|
if (array_key_exists($key, $numeric_array)) {
|
|
$this->read_replica->where($numeric_array[$key], $val);
|
|
} else if (array_key_exists($key, $string_array)) {
|
|
$this->read_replica->like('lower(' . $string_array[$key] . ')', strtolower($val));
|
|
}
|
|
}
|
|
|
|
if ($count_record === false) {
|
|
$this->read_replica->order_by('id ASC');
|
|
}
|
|
|
|
return $this->read_replica->get_compiled_select();
|
|
}
|
|
|
|
public function getCountryByCode($data){
|
|
$this->read_replica
|
|
->select('code')
|
|
->from('country')
|
|
->where('code', $data['country']);
|
|
|
|
return $this->read_replica->get()->result_array();
|
|
}
|
|
}
|