dev-chiefworks
98 lines
4.8 KiB
PHP
98 lines
4.8 KiB
PHP
<?php
|
|
|
|
class TripInsights {
|
|
|
|
public function cheaperAlternatives($db, $gpsdb, $data) {
|
|
error_log('TripInsights::cheaperAlternatives($db, $gpsdb, $data) ');
|
|
|
|
$err = "";
|
|
$member_id = (int)$data['member_id'];
|
|
$trackedemail_item_id = (int)$data['trackedemail_item_id'];
|
|
//$member_id = 3; // DEBUG
|
|
if ($member_id<1) {
|
|
error_log("Invalid member_id='${member_id}'");
|
|
return NULL;
|
|
}
|
|
|
|
$q.= "SELECT b.*,c.name AS transport_provder_name,c.category,d.flag,f.root_id,";
|
|
$q.= " CASE WHEN e.id IS NULL THEN '0' ELSE '1' END AS surge_price, ";
|
|
$q.= " CASE WHEN (f.id IS NOT NULL AND f.cost>f.average) THEN ROUND(f.average/f.cost,2) ELSE 0 END AS cheaper_alternative, ";
|
|
$q.= " (SELECT min(cost) FROM trip_price_comparison WHERE root_id=(SELECT root_id FROM trip_price_comparison WHERE data_source_id=b.id AND data_source=1)) AS min_cost ";
|
|
$q.= ", g.address AS location_start_address,g.description AS location_start_description ";
|
|
$q.= ", h.address AS location_end_address,h.description AS location_end_description ";
|
|
$q.= " FROM trackedemail_item a, transport_providers c, parsedemail_item b ";
|
|
$q.= " LEFT JOIN parsedemail_item_advice_result d ON (d.parsedemail_item_id=b.id) ";
|
|
$q.= " LEFT JOIN trip_surge_price e ON (e.data_source_id=b.id AND e.data_source=1) ";
|
|
$q.= " LEFT JOIN trip_price_comparison f ON (f.data_source_id=b.id AND e.data_source=1) ";
|
|
$q.= " LEFT JOIN address g ON (g.id=b.location_start_id) ";
|
|
$q.= " LEFT JOIN address h ON (h.id=b.location_end_id) ";
|
|
$q.= " WHERE a.id=b.trackedemail_item_id AND a.member_id=".$member_id." AND c.id=b.transport_provider_id ";
|
|
//$q.= " AND b.dup_id IS NULL ";
|
|
$q.= " AND a.id=${trackedemail_item_id} ";
|
|
//$q.= " ORDER BY b.travel_date DESC"; // LIMIT ?
|
|
/*
|
|
address | character varying(200) |
|
|
latitude | numeric |
|
|
longitude | numeric |
|
|
timezone | integer |
|
|
geocoding_date | date |
|
|
postal | character varying(40) |
|
|
country | character varying(2) | default 'SG'::character varying
|
|
geometry | geography(Point,4326) |
|
|
description | character varying(100) |
|
|
*/
|
|
error_log($q);
|
|
$trip = [];
|
|
$alternatives = [];
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $trip=pg_fetch_assoc($r)) {
|
|
$f["location_start_address"] = html_entity_decode ($f["location_start_address"],ENT_QUOTES|ENT_HTML5,"UTF-8");
|
|
$f["location_end_address"] = html_entity_decode ($f["location_end_address"],ENT_QUOTES|ENT_HTML5,"UTF-8");
|
|
|
|
// Cheaper alternatives
|
|
error_log("root_id=".$trip["root_id"]);
|
|
//SELECT min(f.cost),b.transport_provider_id FROM trip_price_comparison f, parsedemail_item b WHERE f.root_id=92 AND f.cost<13.00 AND b.id=f.parsedemail_item_id GROUP BY b.transport_provider_id;
|
|
$q = "SELECT min(f.cost) AS min_cost,b.transport_provider_id FROM trip_price_comparison f, parsedemail_item b ";
|
|
$q.= " WHERE f.root_id=".$trip["root_id"]." AND f.cost<".$trip["cost"]." AND b.id=f.parsedemail_item_id";
|
|
$q.= " GROUP BY b.transport_provider_id";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r)) {
|
|
while ($f=pg_fetch_assoc($r)) {
|
|
// SELECT f.root_id,b.*,c.name AS transport_provder_name,c.category FROM trip_price_comparison f, parsedemail_item b LEFT JOIN transport_providers c ON (c.id=b.transport_provider_id) WHERE f.root_id=92 AND f.cost=9.00 AND b.id=f.parsedemail_item_id ORDER BY b.travel_date DESC LIMIT 1;
|
|
$q0 = "SELECT f.root_id,b.*,c.name AS transport_provder_name,c.category ";
|
|
$q0.= ", g.address AS location_start_address,g.description AS location_start_description ";
|
|
$q0.= ", h.address AS location_end_address,h.description AS location_end_description ";
|
|
$q0.= " FROM trip_price_comparison f, parsedemail_item b ";
|
|
$q0.= " LEFT JOIN transport_providers c ON (c.id=b.transport_provider_id) ";
|
|
$q0.= " LEFT JOIN address g ON (g.id=b.location_start_id) ";
|
|
$q0.= " LEFT JOIN address h ON (h.id=b.location_end_id) ";
|
|
$q0.= " WHERE f.root_id=".$trip["root_id"]." AND f.cost=".$f["min_cost"];
|
|
$q0.= " AND b.id=f.parsedemail_item_id AND b.cost=".$f["min_cost"];
|
|
$q0.= " ORDER BY b.travel_date DESC LIMIT 1";
|
|
|
|
error_log($q0);
|
|
$r0 = pg_query($db, $q0);
|
|
$f0 = pg_fetch_assoc($r0);
|
|
$f["location_start_address"] = html_entity_decode ($f["location_start_address"],ENT_QUOTES|ENT_HTML5,"UTF-8");
|
|
$f["location_end_address"] = html_entity_decode ($f["location_end_address"],ENT_QUOTES|ENT_HTML5,"UTF-8");
|
|
$alternatives[] = $f0;
|
|
}
|
|
}
|
|
} else {
|
|
$err = pg_last_error($db);
|
|
}
|
|
$res = [
|
|
"name" => "Cheaper Alternatives",
|
|
"member_id" => $member_id,
|
|
"trackedemail_item_id" => $trackedemail_item_id,
|
|
"trip" => $trip,
|
|
"alternatives" => $alternatives
|
|
];
|
|
return [$res, $err];
|
|
}
|
|
|
|
}
|
|
|
|
// vi:ts=2
|
|
|