Float/FloatAppGate
1
0
Fork 0
Code Issues Pull Requests Releases Activity
Files
master
FloatAppGate/public/core1/SAVVY/trips/Trips.php
T
dev-chiefworks 47f4fad75c Added Other AP
2022-04-26 11:30:34 -04:00

238 lines
9.7 KiB
PHP
Raw Permalink Blame History

<?php
class Trips {
public function getAll($db, $members_id, $date1, $date2, $limit, $offset) {
$result = array();
$total = 0;
$q = "SELECT count(*) FROM trackedemail_item, parsedemail_item ";
$q.= " WHERE trackedemail_item.id=parsedemail_item.trackedemail_item_id AND trackedemail_item.member_id IN (${members_id})";
$q.= " AND ((parsedemail_item.travel_date BETWEEN '${date1}' AND '${date2}') OR (parsedemail_item.travel_date_end BETWEEN '${date1}' AND '${date2}')) ";
//$q.= " AND parsedemail_item.location_start_tz='Asia/Singapore';";
$q.= " AND parsedemail_item.dup_id IS NULL";
$r = pg_query($db, $q);
if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
$total = $f[0];
}
if ($total==0) {
return array(0,null);
}
$q = "SELECT parsedemail_item.*,parsedemail_item_advice_google.id AS gid,parsedemail_item_advice_google.routes,trackedemail_item.id as tid,trackedemail_item.member_id ";
$q.= " FROM trackedemail_item, parsedemail_item ";
$q.= " LEFT JOIN parsedemail_item_advice_google ON parsedemail_item.id=parsedemail_item_advice_google.parsedemail_item_id ";
$q.= " WHERE trackedemail_item.id=parsedemail_item.trackedemail_item_id AND trackedemail_item.member_id IN (${members_id})";
$q.= " AND ((parsedemail_item.travel_date BETWEEN '${date1}' AND '${date2}') OR (parsedemail_item.travel_date_end BETWEEN '${date1}' AND '${date2}')) ";
//$q.= " AND parsedemail_item.location_start_tz='Asia/Singapore';";
$q.= " AND parsedemail_item.dup_id IS NULL";
$q.= " ORDER BY parsedemail_item.travel_date DESC, parsedemail_item.travel_date_end DESC";
$q.= " LIMIT ${limit} OFFSET ${offset}";
$r = pg_query($db, $q);
if ($r && pg_num_rows($r)) {
while($f=pg_fetch_assoc($r)) {
$result[] = $f;
}
}
return array($total, $result);
}
public function getById($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 function getByLocations($db, $origin, $destination, $limit=1) {
$result = array();
$db_lat = is_numeric($origin["lat"]) ? $origin["lat"] : 0;
$db_lng = is_numeric($origin["lng"]) ? $origin["lng"] : 0;
$db_destination = pg_escape_string(strtolower($destination));
$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 ROUND(b.latitude,3)=ROUND(${db_lat},3) AND ROUND(b.longitude,3)=ROUND(${db_lng},3)
AND LOWER(c.address) LIKE '%${db_destination}%' ORDER BY a.travel_date_end DESC LIMIT ${limit}";
$r = pg_query($db, $q);
if ($r && pg_num_rows($r)) {
if ($limit==1) {
$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");
return $f;
}
$result = [];
while ($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 function getByCoordinates($db, $origin, $destination, $limit=1) {
$result = array();
$dbo_lat = is_numeric($origin["lat"]) ? $origin["lat"] : 0;
$dbo_lng = is_numeric($origin["lng"]) ? $origin["lng"] : 0;
$dbd_lat = is_numeric($destination["lat"]) ? $destination["lat"] : 0;
$dbd_lng = is_numeric($destination["lng"]) ? $destination["lng"] : 0;
$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 ROUND(b.latitude,3)=ROUND(${dbo_lat},3) AND ROUND(b.longitude,3)=ROUND(${dbo_lng},3)
AND ROUND(c.latitude,3)=ROUND(${dbd_lat},3) AND ROUND(c.longitude,3)=ROUND(${dbd_lng},3)
ORDER BY a.travel_date_end DESC LIMIT ${limit}";
$r = pg_query($db, $q);
if ($r && pg_num_rows($r)) {
if ($limit==1) {
$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");
return $f;
}
$result = [];
while ($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 function getOptionsById($db, $id) {
syslog(LOG_WARNING,"Trips::getOptionsById(\$db, $id)");
$result = array("routes" => 0, "gid" => 0);
$q = "SELECT id AS gid, routes FROM parsedemail_item_advice_google WHERE parsedemail_item_id=${id}";
$r = pg_query($db, $q);
if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
$result = $f;
}
if ($result["routes"]>0 && $result["gid"]>0) {
$result["legs"] = array();
} else {
return NULL; // Data not found
}
$timezones = MultiModal::getLegTimezonesForAdvice($db, $result["gid"]);
/* $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=CAST(a.departure_time_zone AS INTEGER) ";
$q.= " LEFT JOIN address_timezone c ON c.id=CAST(a.arrival_time_zone AS INTEGER) ";
$q.= " WHERE a.parsedemail_item_advice_google_id=".$result["gid"]; */
$q = "SELECT * FROM google_directions_legs WHERE parsedemail_item_advice_google_id=".$result["gid"];
syslog(LOG_WARNING,$q);
$r = pg_query($db, $q);
while ($f=pg_fetch_assoc($r)) {
unset($f["parsedemail_item_advice_google_id"]); // We have it as $result["gid"]
if (is_array($timezones) && array_key_exists($f["id"],$timezones)) {
$f["departure_timezone"] = $timezones[$f["id"]][0];
$f["arrival_timezone"] = $timezones[$f["id"]][1];
}
$result["legs"][] = $f;
$q1 = "SELECT a.id AS sid,a.*,b.* FROM google_directions_leg_steps a LEFT JOIN google_directions_leg_step_details b ON a.id=b.google_directions_leg_step_id ";
$q1.= " WHERE a.google_directions_leg_id=".$f["id"];
$r1 = pg_query($db, $q1);
if ($r1 && pg_num_rows($r1)) {
$result["leg_steps"][$f["id"]] = array();
$result["leg_fare"][$f["id"]] = 0;
}
$fare = 0; $i = 0;
while ($f1=pg_fetch_assoc($r1)) {
if (array_key_exists("polyline",$f1) && $f1["polyline"]=="") {
$f1["polyline"] = MultiModal::getPolyline($db, $f1);
}
unset($f1["google_directions_leg_id"]);
unset($f1["google_directions_leg_step_id"]);
foreach ($f1 as $key=>$val) {
if ($val===NULL) unset($f1[$key]);
}
$q2 = "SELECT * FROM leg_step_quote WHERE google_directions_leg_step_id=".$f1["sid"];
$r2 = pg_query($db, $q2);
if ($r2 && pg_num_rows($r2) && $f2 = pg_fetch_assoc($r2)) {
syslog(LOG_WARNING,$q2);
if (is_array($f2) && isset($f2["fare"]) && $f2["fare"]!="") {
$fare += $f2["fare_raw"];
}
$i++;
}
//$f1["q"] = $q2;
$result["leg_steps"][$f["id"]][] = $f1;
}
if ($fare==0) $fare = $f["fare_raw"];
$result["leg_fare"][$f["id"]] = $fare;
}
ActivityApi::debugTrips([['multimodal'=>['options'=>$result]]],'22222222');
return $result;
}
public function create($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
);
$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 function tripByLocations($db, $location_start_id, $location_end_id) {
$q = "SELECT * FROM parsedemail_item WHERE location_start_id=${location_start_id} AND location_end_id=${location_end_id}";
$q.= " AND dup_id IS NULL ORDER BY travel_date 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()];
}
}
// vi:ts=2
Reference in New Issue View Git Blame Copy Permalink