dev-chiefworks
620 lines
24 KiB
PHP
620 lines
24 KiB
PHP
<?php
|
|
|
|
class GeofenceAreaModel
|
|
{
|
|
public static function getCountryListWithPagination($db, $page = 1, $perPage = 10)
|
|
{
|
|
$offset = ($page - 1) * $perPage;
|
|
if ($offset < 0) {
|
|
$offset = 0;
|
|
}
|
|
|
|
$query_total = "SELECT count(*) FROM geofence_area_country";
|
|
|
|
$query = "SELECT
|
|
id, country as country_code, latitude, longitude, location, radius
|
|
FROM
|
|
geofence_area_country
|
|
ORDER BY
|
|
id desc limit '" . pg_escape_string($perPage) . "' offset '" . pg_escape_string($offset) . "'";
|
|
|
|
$rs = pg_query($db, $query);
|
|
$rs_total = pg_query($db, $query_total);
|
|
|
|
$data = pg_fetch_all($rs);
|
|
|
|
return [
|
|
'success' => true,
|
|
'data' => !empty($data) ? $data : [],
|
|
'total' => pg_fetch_result($rs_total, 'count'),
|
|
'page' => $page,
|
|
];
|
|
}
|
|
|
|
public static function getCountryListWithoutPagination($db)
|
|
{
|
|
$query = "SELECT
|
|
id, country as country_code, latitude, longitude, location, radius
|
|
FROM
|
|
geofence_area_country
|
|
ORDER BY
|
|
id desc";
|
|
|
|
$rs = pg_query($db, $query);
|
|
$data = pg_fetch_all($rs);
|
|
|
|
return [
|
|
'success' => true,
|
|
'data' => !empty($data) ? $data : []
|
|
];
|
|
}
|
|
|
|
public static function getCityListWithPagination($db, $page = 1, $perPage = 10)
|
|
{
|
|
$offset = ($page - 1) * $perPage;
|
|
if ($offset < 0) {
|
|
$offset = 0;
|
|
}
|
|
|
|
$query_total = "SELECT count(*) FROM geofence_area_city";
|
|
|
|
$query = "SELECT
|
|
id, city, country as country_code, latitude, longitude, location, radius
|
|
FROM
|
|
geofence_area_city
|
|
ORDER BY
|
|
id desc limit '" . pg_escape_string($perPage) . "' offset '" . pg_escape_string($offset) . "'";
|
|
|
|
$rs = pg_query($db, $query);
|
|
$rs_total = pg_query($db, $query_total);
|
|
|
|
$data = pg_fetch_all($rs);
|
|
|
|
return [
|
|
'success' => true,
|
|
'data' => !empty($data) ? $data : [],
|
|
'total' => pg_fetch_result($rs_total, 'count'),
|
|
'page' => $page,
|
|
];
|
|
}
|
|
|
|
public static function getCityListWithoutPagination($db, $search = [])
|
|
{
|
|
$_self = new self;
|
|
|
|
$query = "SELECT
|
|
id, city, country as country_code,
|
|
latitude, longitude, location,
|
|
radius
|
|
FROM
|
|
geofence_area_city";
|
|
|
|
$whereQuery = '';
|
|
$order = " ORDER BY id desc";
|
|
|
|
if (!empty($search["country_code"])) {
|
|
$condition = " LOWER(country) = '" . pg_escape_string(strtolower($search["country_code"])) . "'";
|
|
$whereQuery = $_self->checkCondition($condition, $whereQuery);
|
|
|
|
$query .= $whereQuery;
|
|
|
|
}
|
|
if (!empty($search["latitude"]) &&
|
|
!empty($search["longitude"]) &&
|
|
!empty($search["radius"])) {
|
|
|
|
$earth_radius = 6371;
|
|
$km_per_degree_lat = 111.2;
|
|
$pi = 3.14/180;
|
|
$latitude = $search["latitude"];
|
|
$longitude = $search["longitude"];
|
|
$distance_in_km = $search["radius"];
|
|
|
|
$query = " SELECT id
|
|
, city
|
|
, country as country_code
|
|
, latitude
|
|
, longitude
|
|
, location
|
|
, radius
|
|
FROM (
|
|
SELECT (
|
|
$earth_radius * acos(
|
|
cos( radians( {$latitude}) )
|
|
* cos( radians( latitude ) )
|
|
* cos( radians( longitude ) - radians( {$longitude} ) )
|
|
+ sin( radians( {$latitude}) )
|
|
* sin( radians( latitude ) ))
|
|
) AS distance
|
|
, id
|
|
, city
|
|
, country
|
|
, latitude
|
|
, longitude
|
|
, location
|
|
, radius
|
|
FROM geofence_area_city
|
|
WHERE
|
|
latitude BETWEEN " . ($latitude - ($distance_in_km / $km_per_degree_lat)) .
|
|
" AND " . ($latitude + ($distance_in_km / $km_per_degree_lat));
|
|
|
|
$delta = round($distance_in_km / ($km_per_degree_lat * COS(deg2rad($longitude))), 10);
|
|
|
|
// Bounding box for speed - latitude within range (longtitude to km not linear)
|
|
if(cos($longitude * $pi) > 0) {
|
|
|
|
$from = $longitude - $delta;
|
|
$to = $longitude + $delta;
|
|
|
|
$query .=
|
|
" AND longitude" .
|
|
" BETWEEN " . $from .
|
|
" AND " . $to;
|
|
|
|
} else {
|
|
|
|
$from = $longitude + $delta;
|
|
$to = $longitude - $delta;
|
|
|
|
$query .=
|
|
" AND longitude" .
|
|
" BETWEEN " . $from .
|
|
" AND " . $to;
|
|
|
|
}
|
|
|
|
// distance limit for circle
|
|
$query .= " ) geofence_area_city
|
|
WHERE distance < " . $distance_in_km;
|
|
}
|
|
|
|
$query .= $order;
|
|
|
|
$rs = pg_query($db, $query);
|
|
$data = pg_fetch_all($rs);
|
|
|
|
return [
|
|
'success' => true,
|
|
'data' => !empty($data) ? $data : []
|
|
];
|
|
}
|
|
|
|
public static function getAreaListWithPagination($db, $page = 1, $perPage = 10, $search = [])
|
|
{
|
|
$_self = new self;
|
|
|
|
$offset = ($page - 1) * $perPage;
|
|
if ($offset < 0) {
|
|
$offset = 0;
|
|
}
|
|
|
|
$whereQuery = '';
|
|
if (isset($search["city_id"])) {
|
|
$condition = " geofence_area_city.id = '" . pg_escape_string($search["city_id"]) . "'";
|
|
$whereQuery = $_self->checkCondition($condition, $whereQuery);
|
|
}
|
|
|
|
$query_total = "SELECT count(*)
|
|
FROM
|
|
geofence_area
|
|
JOIN geofence_area_city ON geofence_area_city.id = geofence_area.city_id " . $whereQuery;
|
|
|
|
$query = "SELECT
|
|
geofence_area.id, geofence_area.name, geofence_area_city.city, geofence_area.country as country_code, 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
|
|
" . $whereQuery . "
|
|
ORDER BY
|
|
id desc limit '" . pg_escape_string($perPage) . "' offset '" . pg_escape_string($offset) . "'";
|
|
|
|
$rs = pg_query($db, $query);
|
|
$rs_total = pg_query($db, $query_total);
|
|
|
|
$data = pg_fetch_all($rs);
|
|
|
|
return [
|
|
'success' => true,
|
|
'data' => !empty($data) ? $data : [],
|
|
'total' => pg_fetch_result($rs_total, 'count'),
|
|
'page' => $page,
|
|
];
|
|
}
|
|
|
|
public static function getAreaListWithoutPagination($db, $search = [])
|
|
{
|
|
$_self = new self;
|
|
$whereQuery = '';
|
|
|
|
if (!empty($search["city_id"])) {
|
|
$condition = " geofence_area_city.id = '" . pg_escape_string($search["city_id"]) . "'";
|
|
$whereQuery = $_self->checkCondition($condition, $whereQuery);
|
|
}
|
|
|
|
if (!empty($search["country_code"])) {
|
|
$condition = " LOWER(geofence_area.country) = '" . pg_escape_string(strtolower($search["country_code"])) . "'";
|
|
$whereQuery = $_self->checkCondition($condition, $whereQuery);
|
|
}
|
|
|
|
$query = "SELECT
|
|
geofence_area.id, geofence_area.name, geofence_area_city.city, geofence_area.country as country_code, 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
|
|
" . $whereQuery . "
|
|
ORDER BY
|
|
id desc";
|
|
|
|
$rs = pg_query($db, $query);
|
|
$data = pg_fetch_all($rs);
|
|
|
|
return [
|
|
'success' => true,
|
|
'data' => !empty($data) ? $data : []
|
|
];
|
|
}
|
|
|
|
public static function getTripListBetweenArea($db, $startAreaId, $endAreaId, $search = [])
|
|
{
|
|
$_self = new self;
|
|
$whereQuery = '';
|
|
if (!empty($search["transport_provider_id"])) {
|
|
$condition = " parsedemail_item.transport_provider_id = '" . pg_escape_string($search["transport_provider_id"]) . "'";
|
|
$whereQuery = $_self->checkCondition($condition, $whereQuery);
|
|
}
|
|
|
|
// 1) Define geofenced areas, get list of areas
|
|
$startArea = $_self->getArea($db, $startAreaId);
|
|
$endArea = $_self->getArea($db, $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
|
|
$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 = $_self->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 = $_self->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 = $_self->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 = $_self->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 = $_self->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 = $_self->checkCondition($condition, $whereQuery);
|
|
} else {
|
|
return [
|
|
'success' => false,
|
|
'message' => 'Missing polygon in boundaries field.'
|
|
];
|
|
}
|
|
}
|
|
|
|
// only get trips within last year
|
|
$numberOfDays = 365;
|
|
$condition = " parsedemail_item.travel_date BETWEEN NOW() - INTERVAL '" . $numberOfDays . " days' AND NOW() ";
|
|
$whereQuery = $_self->checkCondition($condition, $whereQuery);
|
|
|
|
$tripQuery =
|
|
"SELECT
|
|
parsedemail_item.id as trip_id, parsedemail_item.transport_provider_id, startAddr.address as start_address, endAddr.address as end_address
|
|
FROM
|
|
parsedemail_item
|
|
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;
|
|
|
|
$data = pg_fetch_all(pg_query($db, $tripQuery));
|
|
|
|
$result = [
|
|
'from_area' => $startArea,
|
|
'to_area' => $endArea,
|
|
'trip_list' => $data ?: []
|
|
];
|
|
|
|
return [
|
|
'success' => true,
|
|
'data' => $result ?: []
|
|
];
|
|
}
|
|
|
|
public static function priceComparison($db, $startAreaId, $endAreaId)
|
|
{
|
|
$_self = new self;
|
|
|
|
// 1) Define geofenced areas, get list of areas
|
|
$startArea = $_self->getArea($db, $startAreaId);
|
|
$endArea = $_self->getArea($db, $endAreaId);
|
|
|
|
if (!$startArea || !$endArea) {
|
|
return [
|
|
'success' => false,
|
|
'message' => 'Can not find area.'
|
|
];
|
|
}
|
|
|
|
$tripQuery = "SELECT ROUND(SUM(average_total)/SUM(average_count), 2) as avg_price, transport_provider_id";
|
|
$tripQuery.= " FROM geofence_area_average_quotes ";
|
|
$tripQuery.= " WHERE area_start_id=${startAreaId} AND area_end_id=${endAreaId}";
|
|
$tripQuery.= " GROUP BY transport_provider_id";
|
|
|
|
$data = pg_fetch_all(pg_query($db, $tripQuery));
|
|
error_log($tripQuery);
|
|
|
|
$result = [
|
|
'from_area' => $startArea,
|
|
'to_area' => $endArea,
|
|
'price_comparison' => $data ?: []
|
|
];
|
|
|
|
return $result;
|
|
}
|
|
|
|
public static function priceComparisonOld($db, $startAreaId, $endAreaId)
|
|
{
|
|
$_self = new self;
|
|
|
|
// 1) Define geofenced areas, get list of areas
|
|
$startArea = $_self->getArea($db, $startAreaId);
|
|
$endArea = $_self->getArea($db, $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 = $_self->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 = $_self->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 = $_self->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 = $_self->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 = $_self->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 = $_self->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 = $_self->checkCondition($condition, $whereQuery);
|
|
|
|
$tripQuery =
|
|
"SELECT
|
|
ROUND(AVG(parsedemail_item.cost), 2) as avg_price, parsedemail_item.transport_provider_id
|
|
FROM
|
|
parsedemail_item
|
|
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
|
|
";
|
|
|
|
|
|
|
|
$data = pg_fetch_all(pg_query($db, $tripQuery));
|
|
|
|
$result = [
|
|
'from_area' => $startArea,
|
|
'to_area' => $endArea,
|
|
'price_comparison' => $data ?: []
|
|
];
|
|
|
|
return $result;
|
|
}
|
|
|
|
private function checkCondition($condition, $query)
|
|
{
|
|
if (strpos($query, 'WHERE') === false) {
|
|
$query .= " WHERE $condition";
|
|
} else {
|
|
$query .= " AND $condition";
|
|
}
|
|
return $query;
|
|
}
|
|
|
|
public static function getArea($db, $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
|
|
";
|
|
|
|
$rs = pg_query($db, $query);
|
|
$data = pg_fetch_assoc($rs);
|
|
|
|
return $data;
|
|
}
|
|
}
|