dev-chiefworks
434 lines
19 KiB
PHP
434 lines
19 KiB
PHP
<?php
|
|
|
|
class Options {
|
|
|
|
public static function getTransportOptions($db, $origin, $destination) {
|
|
return Options::getServicesByLatitudeLongitude($db, $origin["lat"],$origin["lng"]);
|
|
}
|
|
|
|
public static function getServicesByLatitudeLongitude($db, $lat, $lng) {
|
|
// $lat,$lng => geofence_area_country
|
|
$geofence_area_country = [];
|
|
$q = "SELECT * FROM (SELECT *,ST_DistanceSphere(location::geometry,ST_SetSRID(ST_MakePoint(";
|
|
$q.= $lng.",".$lat."),4326)::geometry) AS distance FROM geofence_area_country ";
|
|
$q.= " WHERE ST_DWithin(location, ST_SetSRID(ST_MakePoint(";
|
|
$q.= $lng.",".$lat."),4326)::geography,radius)) AS a ORDER BY a.distance ASC LIMIT 1";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
|
|
$geofence_area_country = $f;
|
|
} else {
|
|
// Fatal
|
|
$err = pg_last_error($db);
|
|
$err = $err ? $err : $q;
|
|
error_log('Options::getTransportOptions => '.$err);
|
|
throw new Exception("Failed to match input origin to a country");
|
|
}
|
|
// geofence_area_country => country_servies
|
|
$country_services = [];
|
|
if (array_key_exists("id",$geofence_area_country) && $geofence_area_country["id"]>0) {
|
|
$q = "SELECT * FROM country_services WHERE country_id=".$geofence_area_country["id"]." AND status=1";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r)) {
|
|
while ($f=pg_fetch_assoc($r)) {
|
|
$country_services[] = $f;
|
|
}
|
|
} else {
|
|
// Non-fatal, try to get city services next
|
|
$err = pg_last_error($db);
|
|
$err = $err ? $err : $q;
|
|
error_log('Options::getTransportOptions => '.$err);
|
|
}
|
|
}
|
|
// $lat,$lng => geofence_area_city
|
|
$geofence_area_city = [];
|
|
$q = "SELECT * FROM (SELECT *,ST_DistanceSphere(location::geometry,ST_SetSRID(ST_MakePoint(";
|
|
$q.= $lng.",".$lat."),4326)::geometry) AS distance FROM geofence_area_city ";
|
|
$q.= " WHERE ST_DWithin(location, ST_SetSRID(ST_MakePoint(";
|
|
$q.= $lng.",".$lat."),4326)::geography,radius)) AS a ORDER BY a.distance ASC LIMIT 1";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
|
|
$geofence_area_city = $f;
|
|
} else {
|
|
// Non-fatal, at least we know the country
|
|
$err = pg_last_error($db);
|
|
$err = $err ? $err : $q;
|
|
error_log('Options::getTransportOptions => '.$err);
|
|
}
|
|
// geofence_area_city => city_services
|
|
$city_services = [];
|
|
if (array_key_exists("id",$geofence_area_city) && $geofence_area_city["id"]>0) {
|
|
$q = "SELECT * FROM city_services WHERE city_id=".$geofence_area_city["id"]." AND status=1";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r)) {
|
|
while ($f=pg_fetch_assoc($r)) {
|
|
$city_services[] = $f;
|
|
}
|
|
} else {
|
|
// Non-fatal, if we have country services (let upstream decide what to do)
|
|
$err = pg_last_error($db);
|
|
$err = $err ? $err : $q;
|
|
error_log('Options::getTransportOptions => '.$err);
|
|
}
|
|
}
|
|
if (is_array($country_services) && count($country_services)>0 && (!is_array($city_services) || count($city_services)<1)) {
|
|
$services = $country_services;
|
|
} else if (is_array($city_services) && count($city_services)>0 && (!is_array($country_services) || count($country_services)<1)) {
|
|
$services = $city_services;
|
|
} else {
|
|
$services = $city_services;
|
|
foreach ($country_services as $country_service) {
|
|
$exists = false;
|
|
foreach($services as $service) {
|
|
if ($service["transport_provider_id"]>0
|
|
&& $service["transport_provider_id"]==$country_service["transport_provider_id"]) {
|
|
// The service is available on city level - skip
|
|
$exists = true;
|
|
break;
|
|
}
|
|
}
|
|
if (!$exists) {
|
|
// Add country service
|
|
$services[] = $country_service;
|
|
}
|
|
}
|
|
}
|
|
return [
|
|
$services,
|
|
[
|
|
'geofence_area_country' => $geofence_area_country,
|
|
'country_services' => $country_services,
|
|
'geofence_area_city' => $geofence_area_city,
|
|
'city_services' => $city_services
|
|
]
|
|
];
|
|
}
|
|
|
|
public static function processGooglePlace($db, $data) {
|
|
// data.address
|
|
// data.place
|
|
// data.lat
|
|
// data.lng
|
|
// data.utc_offset_minutes
|
|
// data.postal
|
|
// data.vicinity
|
|
// data.city
|
|
// data.country
|
|
$db_address = pg_escape_string(html_entity_decode($data["address"],ENT_QUOTES|ENT_HTML5,"UTF-8"));
|
|
$db_lat = floatval($data["lat"]);
|
|
$db_lng = floatval($data["lng"]);
|
|
$db_city = pg_escape_string($data["city"]);
|
|
$db_postal = pg_escape_string($data["postal"]);
|
|
$db_country = pg_escape_string($data["country"]);
|
|
|
|
// Step 1: Check address
|
|
$q = "SELECT a.*,b.timezone AS \"timeZoneId\" FROM address a LEFT JOIN address_timezone b ON (b.id=a.timezone) ";
|
|
$q .= " WHERE LOWER(a.address)=LOWER('${db_address}')";
|
|
$q .= "AND latitude<>0 AND longitude<>0 AND geocoding_date IS NOT NULL AND postal IS NOT NULL ";
|
|
$q .= "ORDER BY geocoding_date DESC LIMIT 1";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
|
|
$f["address"] = html_entity_decode ($f["address"],ENT_QUOTES|ENT_HTML5,"UTF-8");
|
|
foreach ($f as $key=>$val) {
|
|
$data[$key] = $val;
|
|
}
|
|
return $data;
|
|
}
|
|
// Step 2: Get timezone
|
|
date_default_timezone_set('UTC');
|
|
$seconds = $data["utc_offset_minutes"] * 60;
|
|
$timezone = timezone_name_from_abbr('', $seconds, 1);
|
|
$q = "SELECT * FROM address_timezone WHERE LOWER(timezone)=LOWER('${timezone}')";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
|
|
$data["timezone"] = $f["id"];
|
|
$data["timeZoneId"] = $f["timezone"];
|
|
} else {
|
|
$q = "INSERT INTO address_timezone (timezone) VALUES('".pg_escape_string($timezone)."') RETURNING *";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
|
|
$data["timezone"] = $f["id"];
|
|
$data["timeZoneId"] = $f["timezone"];
|
|
} else { // Most likely this is very wrong...
|
|
$data["timezone"] = 1; // Singapore
|
|
$data["timeZoneId"] = 'Asia/Singapore';
|
|
}
|
|
}
|
|
// Step 3: Get city
|
|
$q = "SELECT id FROM geofence_area_city WHERE LOWER(city) ILIKE '" . strtolower($db_city) . "' AND country='${db_country}' LIMIT 1";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $f = pg_fetch_row($r)) {
|
|
$data["city_id"] = $f[0];
|
|
} else {
|
|
$q = "INSERT INTO geofence_area_city (city,country,latitude,longitude,location) VALUES (";
|
|
$q.= "'${db_city}','${db_country}',${db_lat},${db_lng},ST_SetSRID(ST_MakePoint(${db_lng},${db_lat}), 4326)) RETURNING id";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $f = pg_fetch_row($r)) {
|
|
$data["city_id"] = $f[0];
|
|
} else {
|
|
// Most likely this is very wrong...
|
|
$data["city_id"] = 1; // Singapore
|
|
}
|
|
}
|
|
// Step 4: Get closest postal if missing...
|
|
$db_tz = $data["timezone"];
|
|
if (empty($db_postal)) {
|
|
$distance = 50000; // 50 km
|
|
$q = "SELECT postal,
|
|
ST_DistanceSphere(geometry::geometry, ST_SetSRID(ST_MakePoint(${db_lng},${db_lat}),4326)) AS distance
|
|
FROM geoname_postal_code
|
|
WHERE ST_DistanceSphere(geometry::geometry, ST_SetSRID(ST_MakePoint(${db_lng},${db_lat}),4326)) BETWEEN 0 AND ${distance} AND country='${db_country}'
|
|
ORDER BY distance
|
|
LIMIT 1;";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
|
|
$db_postal = $f["postal"];
|
|
$data["postal"] = $f["postal"];
|
|
}
|
|
}
|
|
// Step 5: Save address
|
|
$q = "INSERT INTO address (address,latitude,longitude,timezone,geocoding_date,postal,country,geometry, city_id) VALUES (";
|
|
$q.= "'${db_address}',${db_lat},${db_lng}," . ($db_tz == null ? "NULL" : $db_tz);
|
|
$q.= ",now(),'${db_postal}','${db_country}',ST_SetSRID(ST_MakePoint(${db_lng},${db_lat}), 4326),'".$data["city_id"]."')";
|
|
$q.= " RETURNING id, address, latitude as lat, longitude as lng, postal, country";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
|
|
$data["id"] = $f["id"];
|
|
$data["address"] = $f["address"];
|
|
$data["lat"] = $f["lat"];
|
|
$data["lng"] = $f["lng"];
|
|
$data["geometry"] = $f["geometry"];
|
|
$data["postal"] = $f["postal"];
|
|
}
|
|
return $data;
|
|
}
|
|
|
|
public static function checkValidLocations($db, $origin, $destination) {
|
|
// Check for Google places result
|
|
if (is_array($origin) && array_key_exists('place',$origin) && is_array($origin['place'])) {
|
|
$origin = Options::processGooglePlace($db, $origin);
|
|
}
|
|
// Basic input sanity check: origin
|
|
if (!is_array($origin) || !array_key_exists('address',$origin)
|
|
|| !array_key_exists('lat',$origin) || !array_key_exists('lng',$origin)
|
|
|| !array_key_exists('id',$origin) || $origin['id']<1) {
|
|
throw new Exception('Invalid input origin');
|
|
}
|
|
// Check for Google places result
|
|
if (is_array($destination) && array_key_exists('place',$destination) && is_array($destination['place'])) {
|
|
$destination = Options::processGooglePlace($db, $destination);
|
|
}
|
|
// Basic input sanity check: destination
|
|
if (!is_array($destination) || !array_key_exists('address',$destination)
|
|
|| !array_key_exists('lat',$destination) || !array_key_exists('lng',$destination)
|
|
|| !array_key_exists('id',$destination) || $destination['id']<1) {
|
|
throw new Exception('Invalid input destination');
|
|
}
|
|
// Basic input sanity check: GPS coordinates
|
|
if ($origin['lat']==null || $origin['lng']==null || $destination['lat']==null || $destination['lng']==null
|
|
|| ($origin['lat']==0 && $origin['lng']==0) || ($destination['lat']==0 && $destination['lng']==0)) {
|
|
throw new Exception('Invalid origin and/or destination coordinates');
|
|
}
|
|
return [$origin, $destination];
|
|
}
|
|
|
|
public static function checkValidOption($db, $option) {
|
|
// Basic input sanity check: option
|
|
if (!is_array($option) || !array_key_exists('name',$option)
|
|
|| !array_key_exists('transport_provider_id',$option) || $option['transport_provider_id']<1
|
|
|| !array_key_exists('id',$option) || $option['id']<1) {
|
|
throw new Exception('Invalid transport option');
|
|
}
|
|
}
|
|
|
|
public static function checkValidTrip($db, $trip) {
|
|
// TODO!
|
|
}
|
|
|
|
public static function createTrip($db, $country, $duration, $distance, $location_start_id, $location_end_id) {
|
|
error_log("Options::createTrip(\$db, $country, $duration, $distance, $location_start_id, $location_end_id)");
|
|
$data = [
|
|
'travel_date' => Options::getDate($country),
|
|
'duration' => ((int)($duration/60)),
|
|
'cost_raw' => NULL,
|
|
'trackedemail_item_id' => NULL,
|
|
'cost' => NULL,
|
|
'distance' => sprintf("%0.02f",$distance/1000.0),
|
|
'transport_provider_id' => NULL,
|
|
'scheduled' => NULL,
|
|
'travel_date_end' => Options::getDate($country),
|
|
'dup_id' => NULL,
|
|
'location_start_id' => $location_start_id,
|
|
'location_end_id' => $location_end_id,
|
|
'private' => 't', /* this is a private entry without the source e-mail */
|
|
'booking' => 't' /* this is a booking entry */
|
|
];
|
|
list($id, $err) = Options::saveTrip($db->getConnect(), $data);
|
|
if ($id && $id>0) {
|
|
$trip = Options::getTripById($db->getConnect(), $id);
|
|
return $trip;
|
|
}
|
|
return NULL; // WTF?
|
|
}
|
|
|
|
public static function getTripById($db, $id) {
|
|
$result = array();
|
|
$q = "SELECT a.*,
|
|
b.address AS location_start, b.latitude AS location_start_lat, b.longitude AS location_start_lng,
|
|
c.address AS location_end, c.latitude AS location_end_lat, c.longitude AS location_end_lng,
|
|
a.id AS parsedemail_item_id
|
|
FROM parsedemail_item a
|
|
LEFT JOIN address b ON (b.id=a.location_start_id)
|
|
LEFT JOIN address c ON (c.id=a.location_end_id)
|
|
WHERE a.id=${id}";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
|
|
$f["location_start"] = html_entity_decode ($f["location_start"],ENT_QUOTES|ENT_HTML5,"UTF-8");
|
|
$f["location_end"] = html_entity_decode ($f["location_end"],ENT_QUOTES|ENT_HTML5,"UTF-8");
|
|
$result = $f;
|
|
}
|
|
return $result;
|
|
}
|
|
|
|
public static function saveTrip($db, $data) {
|
|
$fields = array(
|
|
'travel_date' => false,
|
|
'duration' => false,
|
|
'cost_raw' => false,
|
|
'trackedemail_item_id' => false,
|
|
'cost' => false,
|
|
'updated' => false,
|
|
'distance' => false,
|
|
'transport_provider_id' => false,
|
|
'scheduled' => false,
|
|
'travel_date_end' => false,
|
|
'dup_id' => false,
|
|
'location_start_id' => true,
|
|
'location_end_id' => true,
|
|
'private' => false,
|
|
'booking' => false
|
|
);
|
|
$field_key_list = [];
|
|
$field_val_list = [];
|
|
foreach ($fields as $key=>$required) {
|
|
if ($required && !isset($data[$key])) {
|
|
return [NULL, "Missing required field '${key}'"];
|
|
}
|
|
if (!isset($data[$key])) continue;
|
|
$field_key_list[] = "${key}";
|
|
$field_val_list[] = (($data[$key]==NULL || $data[$key]=="")?"NULL":"'".pg_escape_string($data[$key])."'");
|
|
}
|
|
$q = "INSERT INTO parsedemail_item (".implode(",",$field_key_list).")
|
|
VALUES(".implode(",",$field_val_list).") RETURNING id";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r)) {
|
|
$f = pg_fetch_row($r);
|
|
return [$f[0], NULL];
|
|
}
|
|
return [NULL, pg_last_error($db)];
|
|
}
|
|
|
|
public static function validateTrip($db, $trip) {
|
|
if (!is_array($trip) || !array_key_exists("id",$trip) || $trip["id"]<1) {
|
|
return false;
|
|
}
|
|
if (!array_key_exists("options",$trip) || !is_array($trip["options"]) || !array_key_exists("legs",$trip["options"])
|
|
|| !is_array($trip["options"]["legs"]) || count($trip["options"]["legs"])<1
|
|
|| !array_key_exists("id",$trip["options"]["legs"][0]) || $trip["options"]["legs"][0]["id"]<1) {
|
|
return false;
|
|
}
|
|
return true;
|
|
}
|
|
|
|
public static function saveTripAdvice($db, $trip) {
|
|
error_log('Options::saveTripAdvice($db, $trip)');
|
|
if (!is_array($trip) || !array_key_exists("id",$trip) || $trip["id"]<1) {
|
|
return NULL;
|
|
}
|
|
$q = "INSERT INTO parsedemail_item_advice_google (parsedemail_item_id,routes,source) VALUES (".$trip["id"].",1,'".$trip["source"]."') RETURNING id";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
|
|
return $f[0];
|
|
}
|
|
return NULL;
|
|
}
|
|
|
|
public static function saveTripAdviceLeg($db, $trip, $leg) {
|
|
error_log('Options::saveTripAdviceLeg($db, $trip, $leg)');
|
|
$fare_raw = "NULL";
|
|
if (array_key_exists("fare_raw",$leg) && $leg["fare_raw"]>0) {
|
|
$fare_raw = (int)$leg["fare_raw"];
|
|
}
|
|
$q = "INSERT INTO google_directions_legs (parsedemail_item_advice_google_id,arrival_time,arrival_time_zone,";
|
|
$q.= "departure_time,departure_time_zone,distance,duration,steps,fare_raw,polyline) VALUES(";
|
|
$q.= $leg["parsedemail_item_advice_google_id"].",'".pg_escape_string($leg["arrival_time"])."',".((int)$leg["arrival_time_zone"]);
|
|
$q.= ",'".pg_escape_string($leg["departure_time"])."',".((int)$leg["departure_time_zone"]).",";
|
|
$q.= ((int)$leg["distance"]).",".((int)$leg["duration"]).",1,${fare_raw},'".pg_escape_string($leg["polyline"])."') RETURNING id";
|
|
$r = pg_query($db, $q);
|
|
error_log($q);
|
|
if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
|
|
return $f[0];
|
|
}
|
|
return NULL;
|
|
}
|
|
|
|
public static function saveTripAdviceLegStep($db, $trip, $leg, $step) {
|
|
error_log('Options::saveTripAdviceLegStep($db, $trip, $leg, $step)');
|
|
$q = "INSERT INTO google_directions_leg_steps (google_directions_leg_id,distance,duration,travel_mode,";
|
|
$q.= "location_start_lat,location_start_lng,location_end_lat,location_end_lng,html_instructions,polyline) VALUES (";
|
|
$q.= $leg["id"].",".((int)$step["distance"]).",".((int)$step["duration"]).",";
|
|
$q.= "'".pg_escape_string($step["travel_mode"])."',".$step["location_start_lat"].",".$step["location_start_lng"].",";
|
|
$q.= $step["location_end_lat"].",".$step["location_end_lng"].",'".pg_escape_string($step["html_instructions"])."',";
|
|
$q.= "'".pg_escape_string($step["polyline"])."') RETURNING id";
|
|
$r = pg_query($db, $q);
|
|
error_log($q);
|
|
if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
|
|
return $f[0];
|
|
}
|
|
return NULL;
|
|
}
|
|
|
|
public static function saveTripAdviceLegStepQuote($db, $trip, $leg, $step) {
|
|
$fare_raw = NULL;
|
|
if (array_key_exists("fare_raw",$step) && $step["fare_raw"]>0) {
|
|
$fare_raw = (int)$step["fare_raw"];
|
|
}
|
|
if ($fare_raw>0) {
|
|
$q = "INSERT INTO leg_step_quote (google_directions_leg_step_id,name,service,board,alight,";
|
|
$q.= "distance,fare,fare_raw,distance_raw) VALUES (".$step["id"].",";
|
|
$q.= "'".pg_escape_string($trip["source"])."','".pg_escape_string($trip["source"])."',";
|
|
$q.= "'".pg_escape_string($trip["location_start"])."','".pg_escape_string($trip["location_end"])."',";
|
|
$q.= "'".sprintf("%0.02f",$step["distance"]/1000.0)."','".sprintf("%0.02f",$fare_raw/100.0)."',";
|
|
$q.= ((int)$fare_raw).",".((int)$step["distance"]).") RETURNING id";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
|
|
return $f[0];
|
|
}
|
|
}
|
|
return NULL;
|
|
}
|
|
|
|
public static function getDate($country) {
|
|
$tz = 'Asia/Singapore';
|
|
if ($country=='US') {
|
|
$tz = 'America/Los_Angeles';
|
|
}
|
|
$timestamp = time();
|
|
$dt = new DateTime("now", new DateTimeZone($tz)); //first argument "must" be a string
|
|
$dt->setTimestamp($timestamp); //adjust the object to correct timestamp
|
|
return $dt->format('Y-m-d, H:i:s');
|
|
}
|
|
|
|
public static function getTimezoneById($db, $id, $def='Asia/Singapore') {
|
|
$q = "SELECT timezone FROM address_timezone WHERE id=".((int)$id);
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
|
|
return $f['timezone'];
|
|
}
|
|
return $def; //$id;
|
|
}
|
|
|
|
}
|
|
|
|
// vi:ts=2
|
|
|