dev-chiefworks
94 lines
2.7 KiB
PHP
94 lines
2.7 KiB
PHP
<?php if (!defined('BASEPATH')) {
|
|
exit('No direct script access allowed');
|
|
}
|
|
|
|
class Geofence_area_country_model extends CI_Model
|
|
{
|
|
|
|
private $read_replica;
|
|
|
|
public function __construct()
|
|
{
|
|
parent::__construct();
|
|
$this->read_replica = $this->load->database('savvy_replica', TRUE);
|
|
}
|
|
|
|
public function getAllWithPagination($page = 1, $perPage = 10)
|
|
{
|
|
$offset = ($page - 1) * $perPage;
|
|
if ($offset < 0) {
|
|
$offset = 0;
|
|
}
|
|
|
|
$totalQueryString =
|
|
'SELECT
|
|
geo.id, geo.country as country_code, geo.latitude, geo.longitude, geo.radius,
|
|
country.country as country_name
|
|
FROM
|
|
geofence_area_country as geo
|
|
JOIN country ON country.code = geo.country
|
|
';
|
|
|
|
$queryString =
|
|
'SELECT
|
|
geo.id, geo.country as country_code, geo.latitude, geo.longitude, geo.radius,
|
|
country.country as country_name
|
|
FROM
|
|
geofence_area_country as geo
|
|
JOIN country ON country.code = geo.country
|
|
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.country as country_code, geo.latitude, geo.longitude, geo.radius,
|
|
country.country as country_name
|
|
FROM
|
|
geofence_area_country 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_country', $inputData);
|
|
|
|
$insertId= $this->db->insert_id();
|
|
|
|
// update location field
|
|
$this->db->query("UPDATE geofence_area_country 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_country', $inputData);
|
|
|
|
// update location field
|
|
$this->db->query("UPDATE geofence_area_country SET location = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326) WHERE id = {$id} ");
|
|
}
|
|
|
|
public function delete($id)
|
|
{
|
|
$this->db->delete('geofence_area_country', ['id' => $id]);
|
|
}
|
|
}
|