dev-chiefworks
555 lines
19 KiB
PHP
555 lines
19 KiB
PHP
<?php
|
|
|
|
class MultiModal {
|
|
|
|
public function getLegById($db, $id) {
|
|
syslog(LOG_WARNING,"MultiModal::getLegById(\$db, $id)");
|
|
list($departure_time_zone,$arrival_time_zone) = self::getLegTimezones($db, $id);
|
|
$q = "SELECT a.*,b.timezone AS departure_timezone,c.timezone AS arrival_timezone FROM google_directions_legs a ";
|
|
$q.= " LEFT JOIN address_timezone b ON b.id=${departure_time_zone} ";
|
|
$q.= " LEFT JOIN address_timezone c ON c.id=${arrival_time_zone} ";
|
|
$q.= " WHERE a.id=".((int)$id);
|
|
syslog(LOG_WARNING,$q);
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
|
|
return [$f, NULL];
|
|
}
|
|
return [NULL, pg_last_error($db)];
|
|
}
|
|
|
|
public function getAdviceLegs($db, $advice_id) {
|
|
syslog(LOG_WARNING,"MultiModal::getAdviceLegs(\$db, $advice_id)");
|
|
list($departure_time_zone,$arrival_time_zone) = self::getLegTimezones($db, $advice_id);
|
|
$q = "SELECT a.*,b.timezone AS departure_timezone,c.timezone AS arrival_timezone FROM google_directions_legs a ";
|
|
$q.= " LEFT JOIN address_timezone b ON b.id=${departure_time_zone} ";
|
|
$q.= " LEFT JOIN address_timezone c ON c.id=${arrival_time_zone} ";
|
|
$q.= " WHERE a.parsedemail_item_advice_google_id=".((int)$advice_id);
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r)) {
|
|
$result = [];
|
|
while ($f=pg_fetch_assoc($r)) {
|
|
|
|
$result[$f['id']] = $f;
|
|
}
|
|
return [$result, NULL];
|
|
}
|
|
return [NULL, pg_last_error($db)];
|
|
}
|
|
|
|
public function getLegSteps($db, $id) {
|
|
$result = NULL;
|
|
$q = "SELECT a.id AS sid, a.*,b.*,c.* FROM google_directions_leg_steps a ";
|
|
$q.= " LEFT JOIN google_directions_leg_step_details b ON (b.google_directions_leg_step_id=a.id) ";
|
|
$q.= " LEFT JOIN leg_step_quote c ON (c.google_directions_leg_step_id=a.id) ";
|
|
$q.= " WHERE a.google_directions_leg_id=".((int)$id);
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r)) {
|
|
while ($f=pg_fetch_assoc($r)) {
|
|
if ($f["polyline"]=="") {
|
|
$f["polyline"] = self::getPolyline($db, $f);
|
|
}
|
|
$result[] = $f;
|
|
}
|
|
return [$result, NULL];
|
|
}
|
|
return [NULL, pg_last_error($db)];
|
|
}
|
|
|
|
public function getStepQuote($db, $id) {
|
|
$q = "SELECT * FROM leg_step_quote WHERE google_directions_leg_step_id=${id} ORDER BY id DESC LIMIT 1";
|
|
error_log($q);
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
|
|
return [$f, NULL];
|
|
}
|
|
return [NULL, pg_last_error($db)];
|
|
}
|
|
|
|
public function getLegAdviceGoogleById($db, $id) {
|
|
syslog(LOG_WARNING,"MultiModal::getLegAdviceGoogleById(\$db, $id)");
|
|
$q = "SELECT * FROM parsedemail_item_advice_google WHERE id=${id}";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
|
|
return [$f, NULL];
|
|
}
|
|
return [NULL, pg_last_error($db)];
|
|
}
|
|
|
|
public function getLastGoogleAdviceByTrip($db, $id) {
|
|
$q = "SELECT *,EXTRACT(EPOCH FROM (NOW()-updated)) AS elapsed FROM parsedemail_item_advice_google WHERE parsedemail_item_id=${id} ORDER BY updated DESC LIMIT 1";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
|
|
return [$f, NULL];
|
|
}
|
|
return [NULL, pg_last_error($db)];
|
|
}
|
|
|
|
public function getTimezoneById($db, $id) {
|
|
$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;
|
|
}
|
|
return $id;
|
|
}
|
|
|
|
public function create($db, $trip) {
|
|
list ($id, $err) = self::saveParsedemailItemAdviceGoogle($db, [
|
|
'parsedemail_item_id' => $trip['id'],
|
|
'routes' => count($trip['options']['legs'])
|
|
]);
|
|
if ($id && $id>0) {
|
|
$trip['options']['gid'] = $id;
|
|
error_log('New parsedemail_item_advice_google.id='.$id);
|
|
} else {
|
|
return [NULL, 'Failed to create advice record'];
|
|
}
|
|
$timezones = [];
|
|
$n = count($trip['options']['legs']);
|
|
for ($i=0; $i<$n; $i++) {
|
|
$leg = $trip['options']['legs'][$i];
|
|
$leg_id = $leg['id'];
|
|
$leg_steps = $trip['options']['leg_steps'][$leg_id];
|
|
$leg_fare = $trip['options']['leg_fare'][$leg_id];
|
|
|
|
// Timezones
|
|
foreach(array("departure","arrival") as $tzid) {
|
|
$key1 = "${tzid}_time_zone";
|
|
$key2 = "${tzid}_timezone";
|
|
if (array_key_exists($leg[$key1],$timezones)) {
|
|
$leg[$key2] = $timezones[$leg[$key1]];
|
|
} else {
|
|
$tz = self::getTimezoneById($db, $leg[$key1]);
|
|
$timezones[$leg[$key1]] = $tz;
|
|
$leg[$key2] = $tz;
|
|
}
|
|
}
|
|
|
|
//error_log(json_encode($leg));
|
|
list ($id, $err) = self::saveGoogleDirectionsLeg($db, [
|
|
'parsedemail_item_advice_google_id' => $trip['options']['gid'],
|
|
'arrival_time' => $leg["arrival_time"],
|
|
'arrival_time_zone' => $leg["arrival_time_zone"],
|
|
'departure_time' => $leg["departure_time"],
|
|
'departure_time_zone' => $leg["departure_time_zone"],
|
|
'distance' => $leg["distance"],
|
|
'duration' => $leg["duration"],
|
|
'steps' => $leg["steps"],
|
|
'fare_raw' => $leg["fare_raw"],
|
|
'polyline' => self::processPolyline($leg["polyline"])
|
|
]);
|
|
if ($id && $id>0) {
|
|
$old_leg_id = $leg_id;
|
|
$leg_id = $id;
|
|
$leg['parsedemail_item_advice_google_id'] = $trip['options']['gid'];
|
|
// Legs
|
|
$leg['id'] = $id;
|
|
$trip['options']['legs'][$i] = $leg;
|
|
// Leg steps
|
|
unset($trip["options"]["leg_steps"][$old_leg_id]);
|
|
$trip['options']['leg_steps'][$leg_id] = $leg_steps;
|
|
// Leg fare
|
|
unset($trip['options']['leg_fare'][$old_leg_id]);
|
|
$trip['options']['leg_fare'][$leg_id] = $leg_fare;
|
|
// ...
|
|
error_log('New google_directions_legs.id='.$id);
|
|
} else {
|
|
self::deleteAdviceGoogle($db, $trip['options']['gid']);
|
|
return [NULL, 'Failed to create leg record: '.$err];
|
|
}
|
|
$m = count($leg_steps);
|
|
for ($j=0; $j<$m; $j++) {
|
|
$step = $leg_steps[$j];
|
|
$data = [
|
|
'google_directions_leg_id' => $leg_id,
|
|
'distance' => $step["distance"],
|
|
'duration' => $step["duration"],
|
|
'travel_mode' => $step["travel_mode"],
|
|
'location_start_lat' => $step["location_start_lat"],
|
|
'location_start_lng' => $step["location_start_lng"],
|
|
'location_end_lat' => $step["location_end_lat"],
|
|
'location_end_lng' => $step["location_end_lng"],
|
|
'html_instructions' => $step["html_instructions"],
|
|
'polyline' => self::processPolyline($step["polyline"])
|
|
];
|
|
list($id, $err) = self::saveGoogleDirectionsLegStep($db, $data);
|
|
if ($id && $id>0) {
|
|
$sid = $id;
|
|
$step['sid'] = $id;
|
|
$leg_steps[$j] = $step;
|
|
if ($data['polyline']=="") { // We do not have a polyline - try get one...
|
|
$data['id'] = $id;
|
|
$data['sid'] = $sid;
|
|
$polyline = self::getPolyline($db, $data);
|
|
}
|
|
error_log('New google_directions_leg_steps.id='.$sid);
|
|
} else {
|
|
self::deleteAdviceGoogle($db, $trip['options']['gid']);
|
|
return [NULL, 'Failed to create leg step record'];
|
|
}
|
|
if (count($step)>20) {
|
|
list($id, $err) = self::saveGoogleDirectionsLegStepDetail($db, [
|
|
'google_directions_leg_step_id' => $sid,
|
|
'num_stops' => $step["num_stops"],
|
|
'line' => $step["line"],
|
|
'vehicle' => $step["vehicle"],
|
|
'departure_stop' => $step["departure_stop"],
|
|
'departure_stop_lat' => $step["departure_stop_lat"],
|
|
'departure_stop_lng' => $step["departure_stop_lng"],
|
|
'arrival_stop' => $step["arrival_stop"],
|
|
'arrival_stop_lat' => $step["arrival_stop_lat"],
|
|
'arrival_stop_lng' => $step["arrival_stop_lng"],
|
|
'headsign' => $step["headsign"],
|
|
'headway' => $step["headway"]
|
|
]);
|
|
if ($id && $id>0) {
|
|
$step['id'] = $id;
|
|
$leg_steps[$j] = $step;
|
|
error_log('New google_directions_leg_step_details.id='.$id);
|
|
error_log(json_encode($step));
|
|
if (array_key_exists("fare_raw",$step)) {
|
|
self::saveLegStepQuote($db, [
|
|
'google_directions_leg_step_id' => $sid,
|
|
'name' => $step["line"],
|
|
'service' => $step["agency"],
|
|
'board' => $step["departure_stop"],
|
|
'alight' => $step["arrival_stop"],
|
|
'distance' => sprintf("%0.02f",$step["distance"]/1000),
|
|
'fare' => $step["fare"],
|
|
'fare_raw' => $step["fare_raw"],
|
|
'distance_raw' => $step["distance"]
|
|
]);
|
|
}
|
|
} else {
|
|
self::deleteAdviceGoogle($db, $trip['options']['gid']);
|
|
return [NULL, 'Failed to create leg step detail record'];
|
|
}
|
|
} else {
|
|
error_log('Step #'.$sid.' does not have details');
|
|
}
|
|
} // for
|
|
$trip['options']['leg_steps'][$leg_id] = $leg_steps;
|
|
} // for
|
|
return [$trip, NULL];
|
|
}
|
|
|
|
public function getLegStepQuotes($db, $id) {
|
|
$q = "SELECT * FROM leg_step_quote WHERE google_directions_leg_step_id ";
|
|
$q.= " IN (SELECT id FROM google_directions_leg_steps WHERE google_directions_leg_id=${id})";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r)) {
|
|
$quotes = [];
|
|
while ($f=pg_fetch_assoc($r)) {
|
|
$quotes[] = $f;
|
|
}
|
|
return [$quotes, NULL];
|
|
}
|
|
return [NULL, pg_last_error($db)];
|
|
}
|
|
|
|
public function delete($db, $id) {
|
|
$result = 0;
|
|
$q = "DELETE FROM leg_step_quote WHERE google_directions_leg_step_id ";
|
|
$q.= " IN (SELECT id FROM google_directions_leg_steps WHERE google_directions_leg_id ";
|
|
$q.= " IN (SELECT id FROM google_directions_legs WHERE parsedemail_item_advice_google_id ";
|
|
$q.= " IN (SELECT id FROM parsedemail_item_advice_google WHERE parsedemail_item_id=${id})))";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_affected_rows($r)) {
|
|
$result++;
|
|
}
|
|
//error_log(pg_last_error());
|
|
$q = "DELETE FROM google_directions_leg_step_details WHERE google_directions_leg_step_id ";
|
|
$q.= " IN (SELECT id FROM google_directions_leg_steps WHERE google_directions_leg_id ";
|
|
$q.= " IN (SELECT id FROM google_directions_legs WHERE parsedemail_item_advice_google_id ";
|
|
$q.= " IN (SELECT id FROM parsedemail_item_advice_google WHERE parsedemail_item_id=${id})))";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_affected_rows($r)) {
|
|
$result++;
|
|
}
|
|
//error_log(pg_last_error());
|
|
$q = "DELETE FROM google_directions_leg_steps WHERE google_directions_leg_id ";
|
|
$q.= " IN (SELECT id FROM google_directions_legs WHERE parsedemail_item_advice_google_id ";
|
|
$q.= " IN (SELECT id FROM parsedemail_item_advice_google WHERE parsedemail_item_id=${id}))";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_affected_rows($r)) {
|
|
$result++;
|
|
}
|
|
//error_log(pg_last_error());
|
|
$q = "DELETE FROM google_directions_legs WHERE parsedemail_item_advice_google_id ";
|
|
$q.= " IN (SELECT id FROM parsedemail_item_advice_google WHERE parsedemail_item_id=${id})";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_affected_rows($r)) {
|
|
$result++;
|
|
}
|
|
//error_log(pg_last_error());
|
|
$q = "DELETE FROM parsedemail_item_advice_google WHERE parsedemail_item_id=${id}";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_affected_rows($r)) {
|
|
$result++;
|
|
}
|
|
//error_log(pg_last_error());
|
|
$q = "DELETE FROM parsedemail_item WHERE id=${id}";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_affected_rows($r)) {
|
|
$result++;
|
|
}
|
|
//error_log(pg_last_error());
|
|
return $result;
|
|
}
|
|
|
|
public function deleteAdviceGoogle($db, $id) {
|
|
$result = 0;
|
|
$q = "DELETE FROM leg_step_quote WHERE google_directions_leg_step_id ";
|
|
$q.= " IN (SELECT id FROM google_directions_leg_steps WHERE google_directions_leg_id ";
|
|
$q.= " IN (SELECT id FROM google_directions_legs WHERE parsedemail_item_advice_google_id ";
|
|
$q.= " IN (SELECT id FROM parsedemail_item_advice_google WHERE parsedemail_item_id=${id})))";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_affected_rows($r)) {
|
|
$result++;
|
|
}
|
|
$q = "DELETE FROM google_directions_leg_step_details WHERE google_directions_leg_step_id
|
|
IN (SELECT id FROM google_directions_leg_steps WHERE google_directions_leg_id
|
|
IN (SELECT id FROM google_directions_legs WHERE parsedemail_item_advice_google_id
|
|
IN (SELECT id FROM parsedemail_item_advice_google WHERE id=${id})))";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_affected_rows($r)) {
|
|
$result++;
|
|
}
|
|
$q = "DELETE FROM google_directions_leg_steps WHERE google_directions_leg_id
|
|
IN (SELECT id FROM google_directions_legs WHERE parsedemail_item_advice_google_id
|
|
IN (SELECT id FROM parsedemail_item_advice_google WHERE id=${id}))";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_affected_rows($r)) {
|
|
$result++;
|
|
}
|
|
$q = "DELETE FROM google_directions_legs WHERE parsedemail_item_advice_google_id
|
|
IN (SELECT id FROM parsedemail_item_advice_google WHERE id=${id})";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_affected_rows($r)) {
|
|
$result++;
|
|
}
|
|
$q = "DELETE FROM parsedemail_item_advice_google WHERE id=${id}";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_affected_rows($r)) {
|
|
$result++;
|
|
}
|
|
return $result;
|
|
}
|
|
|
|
public function saveParsedemailItem($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
|
|
);
|
|
return self::saveGeneric($db, $fields, $data, 'parsedemail_item');
|
|
}
|
|
|
|
public function saveParsedemailItemAdviceGoogle($db, $data) {
|
|
$fields = array(
|
|
'parsedemail_item_id' => true,
|
|
'routes' => true
|
|
);
|
|
return self::saveGeneric($db, $fields, $data, 'parsedemail_item_advice_google');
|
|
}
|
|
|
|
public function saveGoogleDirectionsLeg($db, $data) {
|
|
$fields = array(
|
|
'parsedemail_item_advice_google_id' => true,
|
|
'arrival_time' => false,
|
|
'arrival_time_zone' => false,
|
|
'departure_time' => false,
|
|
'departure_time_zone' => false,
|
|
'distance' => false,
|
|
'duration' => false,
|
|
'steps' => false,
|
|
'fare_raw' => false,
|
|
'polyline' => false
|
|
);
|
|
return self::saveGeneric($db, $fields, $data, 'google_directions_legs');
|
|
}
|
|
|
|
public function saveGoogleDirectionsLegStep($db, $data) {
|
|
$fields = array(
|
|
'google_directions_leg_id' => true,
|
|
'distance' => false,
|
|
'duration' => false,
|
|
'travel_mode' => false,
|
|
'location_start_lat' => false,
|
|
'location_start_lng' => false,
|
|
'location_end_lat' => false,
|
|
'location_end_lng' => false,
|
|
'html_instructions' => false,
|
|
'polyline' => false
|
|
);
|
|
return self::saveGeneric($db, $fields, $data, 'google_directions_leg_steps');
|
|
}
|
|
|
|
public function saveGoogleDirectionsLegStepDetail($db, $data) {
|
|
$fields = array(
|
|
'google_directions_leg_step_id' => true,
|
|
'num_stops' => false,
|
|
'line' => false,
|
|
'vehicle' => false,
|
|
'departure_stop' => false,
|
|
'departure_stop_lat' => false,
|
|
'departure_stop_lng' => false,
|
|
'arrival_stop' => false,
|
|
'arrival_stop_lat' => false,
|
|
'arrival_stop_lng' => false,
|
|
'headsign' => false,
|
|
'headway' => false
|
|
);
|
|
return self::saveGeneric($db, $fields, $data, 'google_directions_leg_step_details');
|
|
}
|
|
|
|
public function saveLegStepQuote($db, $data) {
|
|
error_log('public function saveLegStepQuote($db, $data)');
|
|
$fields = array(
|
|
'google_directions_leg_step_id' => true,
|
|
'name' => false,
|
|
'service' => false,
|
|
'board' => false,
|
|
'alight' => false,
|
|
'distance' => false,
|
|
'fare' => false,
|
|
'fare_raw' => true,
|
|
'distance_raw' => false
|
|
);
|
|
return self::saveGeneric($db, $fields, $data, 'leg_step_quote');
|
|
}
|
|
|
|
private function saveGeneric($db, $fields, $data, $table) {
|
|
$q = "select column_name, data_type, character_maximum_length from INFORMATION_SCHEMA.COLUMNS where table_name = '${table}'";
|
|
$r = pg_query($db, $q);
|
|
$columns = [];
|
|
while ($f=pg_fetch_assoc($r)) {
|
|
$columns[$f['column_name']] = $f;
|
|
}
|
|
$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}";
|
|
if (isset($columns[$key]) && ($columns[$key]["data_type"]=='integer' || $columns[$key]["data_type"]=='bigint')) {
|
|
$field_val_list[] = ($data[$key]==NULL || strlen($data[$key])==0)?"NULL":((int)$data[$key]);
|
|
} else {
|
|
$field_val_list[] = (($data[$key]==NULL || $data[$key]=="")?"NULL":"'".pg_escape_string($data[$key])."'");
|
|
}
|
|
}
|
|
$q = "INSERT INTO ${table} (".implode(",",$field_key_list).") VALUES(".implode(",",$field_val_list).") RETURNING id";
|
|
//error_log($q);
|
|
$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 function processPolyline($data) {
|
|
if (!is_array($data)) {
|
|
return $data;
|
|
}
|
|
$points = [];
|
|
foreach ($data as $item) {
|
|
$points = array_merge($points, Polyline::decode($item));
|
|
}
|
|
return Polyline::encode($points);
|
|
}
|
|
|
|
public function getPolyline($db, $f) {
|
|
$q = "SELECT polyline FROM google_directions_leg_steps WHERE ";
|
|
$q.= " location_start_lat='".$f["location_start_lat"]."' AND ";
|
|
$q.= " location_start_lng='".$f["location_start_lng"]."' AND ";
|
|
$q.= " location_end_lat='".$f["location_end_lat"]."' AND ";
|
|
$q.= " location_end_lng='".$f["location_end_lng"]."' AND ";
|
|
$q.= " polyline<>'' ORDER BY ID desc LIMIT 1";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $polyline=pg_fetch_row($r)) {
|
|
$f["polyline"] = $polyline[0];
|
|
$q = "UPDATE google_directions_leg_steps SET polyline='".pg_escape_string($f["polyline"])."' WHERE id=".$f['sid']." RETURNING *";
|
|
$r = pg_query($db, $q); // Do not quite care...
|
|
return $f["polyline"];
|
|
}
|
|
$res = MultiModalApi::getPolyline($f["sid"]);
|
|
if (is_array($res) && isset($res["code"]) && $res["code"]==0) {
|
|
if (isset($res["data"]) && isset($res["data"]["polyline"])) {
|
|
return $res["data"]["polyline"]; // No need to update
|
|
}
|
|
}
|
|
return $f["polyline"];
|
|
}
|
|
|
|
public 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 function getLegTimezones($db, $id) {
|
|
$departure_time_zone = 1; // Asia/Singapore
|
|
$q = "SELECT departure_time_zone FROM google_directions_legs WHERE id=".((int)$id);
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
|
|
if (is_integer($f[0]) && $f[0]>0) {
|
|
$departure_time_zone = $f[0];
|
|
} else {
|
|
$q = "SELECT * FROM address_timezone WHERE lower(timezone)=lower('".$f[0]."')";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
|
|
$departure_time_zone = $f[0];
|
|
}
|
|
}
|
|
}
|
|
$arrival_time_zone = 1; // Asia/Singapore
|
|
$q = "SELECT arrival_time_zone FROM google_directions_legs WHERE id=".((int)$id);
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
|
|
if (is_integer($f[0]) && $f[0]>0) {
|
|
$arrival_time_zone = $f[0];
|
|
} else {
|
|
$q = "SELECT * FROM address_timezone WHERE lower(timezone)=lower('".$f[0]."')";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
|
|
$arrival_time_zone = $f[0];
|
|
}
|
|
}
|
|
}
|
|
return [$departure_time_zone, $arrival_time_zone];
|
|
}
|
|
|
|
public function getLegTimezonesForAdvice($db, $id) {
|
|
$result = [];
|
|
$q = "SELECT id FROM google_directions_legs WHERE parsedemail_item_advice_google_id=".((int)$id);
|
|
$r = pg_query($db, $q);
|
|
while ($f=pg_fetch_row($r)) {
|
|
$result[$f[0]] = self::getLegTimezones($db, $f[0]);
|
|
}
|
|
return $result;
|
|
}
|
|
|
|
}
|
|
|
|
// vi:ts=2
|
|
|