dev-chiefworks
138 lines
4.7 KiB
PHP
138 lines
4.7 KiB
PHP
<?php
|
|
|
|
class Popular {
|
|
|
|
const TIME_DELTA = 180; // min (3 hrs)
|
|
const TIME_LIMIT = 1262322000; // 2010-01-01
|
|
const DEFAULT_COUNTRY = 'SG';
|
|
const DEFAULT_OPTIONS = 3;
|
|
const CITY_CURVATURE = 1.25;
|
|
const CITY_SPEED = 0.667; // km/min (0.0667 = 40 km/h)
|
|
|
|
const POPULAR_RADIUS = 5000; // 5km
|
|
|
|
public function getPopular($db, $gpsdb, $member_id, $lat, $lng, $time, $time_delta=self::TIME_DELTA, $radius=self::POPULAR_RADIUS, $country=self::DEFAULT_COUNTRY) {
|
|
error_log('Popular::getPopular()');
|
|
if ($member_id<1) {
|
|
$loc = self::popularRadiusLocationsMock();
|
|
} else {
|
|
list($loc, $err) = self::popularRadiusLocations($gpsdb, $lat, $lng, $radius, 'start');
|
|
}
|
|
error_log('popularRadiusLocations='.($loc==NULL?"NULL":count($loc)));
|
|
if (!is_array($loc) || count($loc)<1) {
|
|
return [NULL, $err];
|
|
}
|
|
$result = [];
|
|
foreach ($loc as $f) { // location_end, location_start
|
|
$id = self::getPopularTripsID($gpsdb, $f["location_start"], $f["location_end"]);
|
|
if ($id<1) continue;
|
|
$q = "SELECT * FROM parsedemail_item WHERE id=(SELECT dup_id FROM parsedemail_item WHERE id=${id}) UNION SELECT * FROM parsedemail_item WHERE id=${id} ORDER BY dup_id DESC";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
|
|
$result[] = $f;
|
|
}
|
|
}
|
|
if (count($result)) {
|
|
return [$result, NULL];
|
|
}
|
|
return [NULL, "No popular trips nearby found"];
|
|
}
|
|
|
|
public function getPopularTripsID($db, $location_start, $location_end) {
|
|
$q = "SELECT id FROM parsedemail_item WHERE location_start='${location_start}' AND location_end='${location_end}' ORDER BY id DESC LIMIT 1";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
|
|
return $f[0];
|
|
}
|
|
return 0;
|
|
}
|
|
|
|
public static function popularRadiusLocations($db, $lat, $lng, $radius, $what) {
|
|
$limit_lat = (float)$lat;
|
|
$limit_lng = (float)$lng;
|
|
$limit_rad = $radius;
|
|
$q = "SELECT location_end, location_start, count(*) AS num FROM parsedemail_item WHERE ";
|
|
$q.= "ST_DWithin(location_${what},ST_SetSRID(ST_MakePoint(${limit_lng},${limit_lat}),4326)::geography,${limit_rad})";
|
|
$q.= " GROUP BY location_end, location_start ORDER BY count(*) DESC";
|
|
error_log($q);
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r)) {
|
|
$result = [];
|
|
while ($f=pg_fetch_assoc($r)) {
|
|
$result[] = $f;
|
|
}
|
|
return [$result, NULL];
|
|
}
|
|
return [NULL,pg_last_error()];
|
|
}
|
|
|
|
/*
|
|
public function popularByRange($db, $member_id, $days, $ids) {
|
|
$db_days = date("w",$db_time) + (int)$days;
|
|
$q = "SELECT b.*, 60*date_part('hour', b.travel_date)+date_part('minute',b.travel_date) AS dm
|
|
FROM trackedemail_item a, parsedemail_item b
|
|
WHERE a.id=b.trackedemail_item_id AND a.member_id<>".((int)$member_id)."
|
|
AND b.dup_id IS NULL AND b.travel_date_end > (now() - interval '${db_days} days')
|
|
AND b.id IN (".implode(",",$ids).")
|
|
ORDER BY b.travel_date DESC";
|
|
file_put_contents("/home/savvy/tmp/popularByRange-${member_id}-${days}.sql",$q);
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r)) {
|
|
$result = [];
|
|
while ($f=pg_fetch_assoc($r)) {
|
|
$result[] = $f;
|
|
}
|
|
return [$result, NULL];
|
|
}
|
|
return [NULL,pg_last_error()];
|
|
}
|
|
|
|
public function popularByRangeAndTime($db, $member_id, $days, $ids, $time, $time_delta=self::TIME_DELTA) {
|
|
$db_time = strtotime($time);
|
|
$db_days = date("w",$db_time) + (int)$days;
|
|
$db_time_delta = (int)$time_delta;
|
|
// Ranges
|
|
$dh = 60*date("G")+date("i");
|
|
$d1 = $dh - $db_time_delta;
|
|
$d2 = $dh + $db_time_delta;
|
|
$q = "SELECT c.* FROM (SELECT b.*, 60*date_part('hour', b.travel_date)+date_part('minute',b.travel_date) AS dm
|
|
FROM trackedemail_item a, parsedemail_item b
|
|
WHERE a.id=b.trackedemail_item_id AND a.member_id<>".((int)$member_id)."
|
|
AND b.dup_id IS NULL AND b.travel_date_end > (now() - interval '${db_days} days')
|
|
AND b.id IN (".implode(",",$ids).")) AS c
|
|
WHERE c.dm>=${d1} AND c.dm<=${d2} ORDER BY c.travel_date DESC";
|
|
file_put_contents("/home/savvy/tmp/popularByRangeAndTime-${member_id}-${days}.sql",$q);
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r)) {
|
|
$result = [];
|
|
while ($f=pg_fetch_assoc($r)) {
|
|
$result[] = $f;
|
|
}
|
|
return [$result, NULL];
|
|
}
|
|
return [NULL,pg_last_error()];
|
|
}
|
|
*/
|
|
protected function popularRadiusLocationsMock() {
|
|
return [
|
|
[
|
|
"location_end" => "0101000020E6100000892650C4A2985EC0C029070EC3CE4240",
|
|
"location_start" => "0101000020E61000004A5B012A419E5EC066A4390DACE34240",
|
|
"num" => 100
|
|
],
|
|
[
|
|
"location_end" => "0101000020E61000007F3CAA5013995EC0A6D590B8C7CE4240",
|
|
"location_start" => "0101000020E61000004A5B012A419E5EC066A4390DACE34240",
|
|
"num" => 50
|
|
],
|
|
[
|
|
"location_end" => "0101000020E610000023707F89D3985EC098A02BB6EECE4240",
|
|
"location_start" => "0101000020E61000008B02D8DB419E5EC03360DA8184E34240",
|
|
"num" => 10
|
|
]
|
|
];
|
|
}
|
|
}
|
|
// vi:ts=2
|
|
|