dev-chiefworks
329 lines
12 KiB
PHP
329 lines
12 KiB
PHP
<?php if (!defined('BASEPATH')) {
|
|
exit('No direct script access allowed');
|
|
}
|
|
|
|
class Geofence_area_model extends CI_Model
|
|
{
|
|
const GEOFENCING_AREA_TYPES = [
|
|
'postal',
|
|
'radius',
|
|
'polygon'
|
|
];
|
|
|
|
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 = '';
|
|
if (!empty($filterData)) {
|
|
if (!empty($filterData['city_id'])) {
|
|
$whereQuery .= ' WHERE geo_city.id = ' . $this->read_replica->escape($filterData['city_id']) . ' ';
|
|
}
|
|
}
|
|
|
|
$totalQueryString =
|
|
'SELECT
|
|
geo.id, geo.name, geo.area, geo_city.city as city_name, geo_city.id as city_id, geo.type,
|
|
geo.country as country_code, geo.latitude, geo.longitude, geo.boundaries,
|
|
country.country as country_name
|
|
FROM
|
|
geofence_area as geo
|
|
JOIN country ON country.code = geo.country
|
|
JOIN geofence_area_city as geo_city ON geo_city.id = geo.city_id
|
|
' . $whereQuery;
|
|
|
|
$queryString =
|
|
'SELECT
|
|
geo.id, geo.name, geo.area, geo_city.city as city_name, geo_city.id as city_id, geo.type,
|
|
geo.country as country_code, geo.latitude, geo.longitude, geo.boundaries,
|
|
country.country as country_name
|
|
FROM
|
|
geofence_area as geo
|
|
JOIN country ON country.code = geo.country
|
|
JOIN geofence_area_city as geo_city ON geo_city.id = geo.city_id
|
|
' . $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 getAllAreas()
|
|
{
|
|
$query = "SELECT
|
|
geofence_area.id, geofence_area.name, geofence_area_city.city, geofence_area.latitude, geofence_area.longitude,
|
|
geofence_area.location, geofence_area.type, geofence_area.boundaries
|
|
FROM
|
|
geofence_area
|
|
JOIN geofence_area_city ON geofence_area_city.id = geofence_area.city_id
|
|
";
|
|
|
|
$query = $this->read_replica->query($query);
|
|
|
|
return $query->result();
|
|
}
|
|
|
|
public function getItem($id)
|
|
{
|
|
$queryString =
|
|
'SELECT
|
|
geo.id, geo.name, geo.area, geo_city.city as city_name, geo_city.id as city_id, geo.type,
|
|
geo.country as country_code, geo.latitude, geo.longitude, geo.boundaries,
|
|
country.country as country_name
|
|
FROM
|
|
geofence_area as geo
|
|
JOIN country ON country.code = geo.country
|
|
JOIN geofence_area_city as geo_city ON geo_city.id = geo.city_id
|
|
WHERE
|
|
geo.id = ?
|
|
LIMIT 1
|
|
';
|
|
|
|
$query = $this->read_replica->query($queryString, $id);
|
|
return $query->row_array();
|
|
}
|
|
|
|
public function create($inputData)
|
|
{
|
|
$test = $this->db->insert('geofence_area', $inputData);
|
|
$insertId= $this->db->insert_id();
|
|
|
|
// update location field
|
|
$this->db->query("UPDATE geofence_area 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', $inputData);
|
|
|
|
// update location field
|
|
$this->db->query("UPDATE geofence_area SET location = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326) WHERE id = {$id} ");
|
|
}
|
|
|
|
public function delete($id)
|
|
{
|
|
$this->db->delete('geofence_area', ['id' => $id]);
|
|
}
|
|
|
|
public function priceComparison($startAreaId, $endAreaId)
|
|
{
|
|
// validate id
|
|
if (!is_numeric($startAreaId) || !is_numeric($endAreaId)) {
|
|
return false;
|
|
}
|
|
|
|
// 1) Define geofenced areas, get list of areas
|
|
$startArea = $this->getArea($startAreaId);
|
|
$endArea = $this->getArea($endAreaId);
|
|
|
|
if (!$startArea || !$endArea) {
|
|
return [
|
|
'success' => false,
|
|
'message' => 'Can not find area.'
|
|
];
|
|
}
|
|
|
|
// 2. Select trips where "from" falls into origin area and "to" falls into destination area
|
|
$whereQuery = '';
|
|
$startBoundaries = json_decode($startArea['boundaries'], true);
|
|
$endBoundaries = json_decode($endArea['boundaries'], true);
|
|
if ($startArea['type'] === 'radius') {
|
|
if (isset($startBoundaries['radius'])) {
|
|
$radius = $startBoundaries['radius'];
|
|
$condition = " ST_DWithin('" . $startArea['location'] . "', startAddr.geometry, " . $radius . ") ";
|
|
$whereQuery = $this->checkCondition($condition, $whereQuery);
|
|
} else {
|
|
return [
|
|
'success' => false,
|
|
'message' => 'Missing radius in boundaries field.'
|
|
];
|
|
}
|
|
} else if ($startArea['type'] === 'postal') {
|
|
if (is_array($startBoundaries) && array_key_exists("postal_code",$startBoundaries) && is_array($startBoundaries["postal_code"]) && count($startBoundaries["postal_code"]) > 0) {
|
|
$condition = "";
|
|
foreach ($startBoundaries['postal_code'] as $code) {
|
|
if ($condition != "") {
|
|
$condition .= " OR ";
|
|
}
|
|
$condition .= " LEFT(startAddr.postal, " . strlen($code) . ") = '${code}' ";
|
|
}
|
|
|
|
$condition = "(${condition})";
|
|
$whereQuery = $this->checkCondition($condition, $whereQuery);
|
|
} else {
|
|
return [
|
|
'success' => false,
|
|
'message' => 'Missing postal_code in boundaries field.'
|
|
];
|
|
}
|
|
} else if ($startArea['type'] === 'polygon') {
|
|
if (isset($startBoundaries['polygon'])) {
|
|
$polygon = $startBoundaries['polygon'];
|
|
|
|
$polygonCoords = [];
|
|
foreach ($polygon as $coord) {
|
|
$polygonCoords[] = "$coord[0] $coord[1]";
|
|
}
|
|
$polygonCoordsString = implode(",", $polygonCoords);
|
|
|
|
$condition = " ST_Contains(ST_GeomFromText('POLYGON((${polygonCoordsString}))', 4326), startAddr.geometry::geometry) ";
|
|
$whereQuery = $this->checkCondition($condition, $whereQuery);
|
|
} else {
|
|
return [
|
|
'success' => false,
|
|
'message' => 'Missing polygon in boundaries field.'
|
|
];
|
|
}
|
|
}
|
|
|
|
if ($endArea['type'] === 'radius') {
|
|
if (isset($endBoundaries['radius'])) {
|
|
$radius = $endBoundaries['radius'];
|
|
$condition = " ST_DWithin('" . $endArea['location'] . "', endAddr.geometry, " . $radius . ") ";
|
|
$whereQuery = $this->checkCondition($condition, $whereQuery);
|
|
} else {
|
|
return [
|
|
'success' => false,
|
|
'message' => 'Missing radius in boundaries field.'
|
|
];
|
|
}
|
|
} else if ($endArea['type'] === 'postal') {
|
|
if (is_array($endBoundaries) && array_key_exists("postal_code",$endBoundaries) && is_array($endBoundaries["postal_code"]) && count($endBoundaries["postal_code"]) > 0) {
|
|
$condition = "";
|
|
foreach ($endBoundaries['postal_code'] as $code) {
|
|
if ($condition != "") {
|
|
$condition .= " OR ";
|
|
}
|
|
$condition .= " LEFT(endAddr.postal, " . strlen($code) . ") = '${code}' ";
|
|
}
|
|
|
|
$condition = "(${condition})";
|
|
$whereQuery = $this->checkCondition($condition, $whereQuery);
|
|
} else {
|
|
return [
|
|
'success' => false,
|
|
'message' => 'Missing postal_code in boundaries field.'
|
|
];
|
|
}
|
|
} else if ($endArea['type'] === 'polygon') {
|
|
if (isset($endBoundaries['polygon'])) {
|
|
$polygon = $endBoundaries['polygon'];
|
|
|
|
$polygonCoords = [];
|
|
foreach ($polygon as $coord) {
|
|
$polygonCoords[] = "$coord[0] $coord[1]";
|
|
}
|
|
$polygonCoordsString = implode(",", $polygonCoords);
|
|
|
|
$condition = " ST_Contains(ST_GeomFromText('POLYGON((${polygonCoordsString}))', 4326), endAddr.geometry::geometry) ";
|
|
$whereQuery = $this->checkCondition($condition, $whereQuery);
|
|
} else {
|
|
return [
|
|
'success' => false,
|
|
'message' => 'Missing polygon in boundaries field.'
|
|
];
|
|
}
|
|
}
|
|
|
|
// 3. Filter pricing by transport_provider_id and calculate average within last year
|
|
|
|
// only get trips within last year
|
|
$numberOfDays = 365;
|
|
$condition = " parsedemail_item.travel_date BETWEEN NOW() - INTERVAL '" . $numberOfDays . " days' AND NOW() ";
|
|
$whereQuery = $this->checkCondition($condition, $whereQuery);
|
|
|
|
$tripQuery =
|
|
"SELECT
|
|
ROUND(AVG(parsedemail_item.cost), 2) as avg_price, parsedemail_item.transport_provider_id,
|
|
transport_providers.name as transport_provider_name
|
|
FROM
|
|
parsedemail_item
|
|
JOIN transport_providers ON transport_providers.id = parsedemail_item.transport_provider_id
|
|
JOIN address AS startAddr ON (startAddr.id = parsedemail_item.location_start_id AND transport_provider_id IS NOT NULL
|
|
AND LOWER(startAddr.country) = '" . strtolower($startArea['country_code']) . "')
|
|
JOIN address AS endAddr ON (endAddr.id = parsedemail_item.location_end_id
|
|
AND LOWER(endAddr.country) = '" . strtolower($endArea['country_code']) . "')
|
|
" . $whereQuery . "
|
|
GROUP BY parsedemail_item.transport_provider_id, transport_providers.name
|
|
";
|
|
|
|
$query = $this->read_replica->query($tripQuery);
|
|
|
|
$result = [
|
|
'success' => true,
|
|
'from_area' => $startArea,
|
|
'to_area' => $endArea,
|
|
'price_comparison' => $query->result_array() ?: []
|
|
];
|
|
|
|
return $result;
|
|
}
|
|
|
|
private function checkCondition($condition, $query)
|
|
{
|
|
if (strpos($query, 'WHERE') === false) {
|
|
$query .= " WHERE $condition";
|
|
} else {
|
|
$query .= " AND $condition";
|
|
}
|
|
return $query;
|
|
}
|
|
|
|
public function getArea($id)
|
|
{
|
|
$query = "SELECT
|
|
geofence_area.id, geofence_area.name, geofence_area.country as country_code, geofence_area.latitude, geofence_area.longitude,
|
|
geofence_area.location, geofence_area.type, geofence_area.boundaries
|
|
FROM
|
|
geofence_area
|
|
WHERE
|
|
geofence_area.id = $id
|
|
LIMIT 1
|
|
";
|
|
|
|
$query = $this->read_replica->query($query);
|
|
$data = $query->result_array();
|
|
|
|
return !empty($data) ? $data[0] : null;
|
|
}
|
|
|
|
public function getLocationByID($id) {
|
|
$this->read_replica->select('*');
|
|
$this->read_replica->from('geofence_area');
|
|
$this->read_replica->where('id', $id);
|
|
|
|
$query = $this->read_replica->get();
|
|
|
|
return $query->result_array();
|
|
}
|
|
|
|
public function getLocationByCityID($city_id) {
|
|
$this->read_replica->select('ga.id AS id, ga.name AS geo_area_name');
|
|
$this->read_replica->from('geofence_area ga');
|
|
$this->read_replica->join('geofence_area_city gac', 'gac.id = ga.city_id');
|
|
$this->read_replica->where('gac.id', $city_id);
|
|
$this->read_replica->order_by('geo_area_name');
|
|
|
|
return $this->read_replica->get()->result_array();
|
|
}
|
|
}
|