dev-chiefworks
241 lines
9.9 KiB
PHP
241 lines
9.9 KiB
PHP
<?php
|
|
|
|
class Activity {
|
|
|
|
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 MIN_ACTIVITY_DISTANCE = 0.05; // 50m
|
|
const MAX_ACTIVITY_DISTANCE = 50.0; // 50km
|
|
|
|
const ACTIVITY_RADIUS = 30000; // 30km
|
|
|
|
public function getActivity($db, $gpsdb, $member_id, $lat, $lng, $radius, $time, $time_delta=self::TIME_DELTA, $country=self::DEFAULT_COUNTRY) {
|
|
syslog(LOG_WARNING,"Activity::getActivity(\$db, \$gpsdb, $member_id, $lat, $lng, $radius, $time, $time_delta, $country)");
|
|
$all_time_days = self::getAllTimeDays();
|
|
|
|
list($res, $err) = self::activityByRangeAndTimeAndRadius(
|
|
$gpsdb, $db, $member_id, $all_time_days, $lat, $lng, $radius, 'start', $time, $time_delta);
|
|
if ($res && count($res)>0) {
|
|
return [$res, NULL];
|
|
}
|
|
syslog(LOG_WARNING,$err);
|
|
|
|
list($res, $err) = self::activityByRangeAndTimeAndRadius(
|
|
$gpsdb, $db, $member_id, $all_time_days, $lat, $lng, $radius, 'end', $time, $time_delta);
|
|
if ($res && count($res)>0) {
|
|
return [$res, NULL];
|
|
}
|
|
syslog(LOG_WARNING,$err);
|
|
|
|
list($res, $err) = self::activityByRangeAndRadius(
|
|
$gpsdb, $db, $member_id, $all_time_days, $lat, $lng, $radius, 'start');
|
|
if ($res && count($res)>0) {
|
|
return [$res, NULL];
|
|
}
|
|
syslog(LOG_WARNING,$err);
|
|
|
|
list($res, $err) = self::activityByRangeAndRadius(
|
|
$gpsdb, $db, $member_id, $all_time_days, $lat, $lng, $radius, 'end');
|
|
if ($res && count($res)>0) {
|
|
return [$res, NULL];
|
|
}
|
|
syslog(LOG_WARNING,$err);
|
|
|
|
if ($country=='SG') {
|
|
// For US it may produce the results too far apart
|
|
list($res, $err) = self::activityByRangeAndTime($db, $member_id, 7, $time, $time_delta, $country);
|
|
if ($res && count($res)>0) {
|
|
return [$res, NULL];
|
|
}
|
|
list($res, $err) = self::activityByRangeAndTime($db, $member_id, 30, $time, $time_delta, $country);
|
|
if ($res && count($res)>0) {
|
|
return [$res, NULL];
|
|
}
|
|
list($res, $err) = self::activityByRangeAndTime($db, $member_id, $all_time_days, $time, $time_delta, $country);
|
|
if ($res && count($res)>0) {
|
|
return [$res, NULL];
|
|
}
|
|
list($res, $err) = self::activityByRange($db, $member_id, 7, $country);
|
|
if ($res && count($res)>0) {
|
|
return [$res, NULL];
|
|
}
|
|
list($res, $err) = self::activityByRange($db, $member_id, 30, $country);
|
|
if ($res && count($res)>0) {
|
|
return [$res, NULL];
|
|
}
|
|
list($res, $err) = self::activityByRange($db, $member_id, $all_time_days, $country);
|
|
if ($res && count($res)>0) {
|
|
return [$res, NULL];
|
|
}
|
|
}
|
|
//*/
|
|
return [NULL, "We could not find any recent activity in this area"];
|
|
}
|
|
|
|
public function getAddressActivity($db, $address, $member_id, $time, $time_delta=self::TIME_DELTA) {
|
|
// TODO: radius search
|
|
|
|
}
|
|
|
|
public function activityByRange($db, $member_id, $days, $country=self::DEFAULT_COUNTRY) {
|
|
$db_days = date("w",$db_time) + (int)$days;
|
|
$db_country = pg_escape_string($country);
|
|
$q = "SELECT b.*, 60*date_part('hour', b.travel_date)+date_part('minute',b.travel_date) AS dm ";
|
|
$q.= " FROM trackedemail_item a, parsedemail_item b ";
|
|
$q.= " LEFT JOIN address c ON (c.id=b.location_start_id) ";
|
|
$q.= " LEFT JOIN address d ON (d.id=b.location_end_id) ";
|
|
$q.= " WHERE a.id=b.trackedemail_item_id AND a.member_id=".((int)$member_id);
|
|
$q.= " AND b.dup_id IS NULL AND b.travel_date_end > (now() - interval '${db_days} days') ";
|
|
$q.= " AND (c.country='${db_country}' OR d.country='${db_country}') ";
|
|
$q.= " ORDER BY b.travel_date DESC";
|
|
syslog(LOG_WARNING,$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 static function activityByRangeAndRadius($gpsdb, $db, $member_id, $days, $lat, $lng, $radius, $what) {
|
|
syslog(LOG_WARNING,"Activity::activityByRangeAndRadius(\$gpsdb, \$db, $member_id, $days, $lat, $lng, $radius, $what)");
|
|
$limit_lat = (float)$lat;
|
|
$limit_lng = (float)$lng;
|
|
$limit_rad = $radius;
|
|
$db_days = (int)$days;
|
|
$q = "SELECT b.*, 60*date_part('hour', b.travel_date)+date_part('minute',b.travel_date) AS dm, ";
|
|
$q.= " c.geometry AS location_start_geometry, d.geometry AS location_end_geometry ";
|
|
$q.= " FROM trackedemail_item a, parsedemail_item b ";
|
|
$q.= " LEFT JOIN address c ON (c.id=b.location_start_id) ";
|
|
$q.= " LEFT JOIN address d ON (d.id=b.location_end_id) ";
|
|
$q.= " WHERE a.id=b.trackedemail_item_id AND a.member_id=".((int)$member_id);
|
|
$q.= " AND b.dup_id IS NULL AND b.travel_date_end > (now() - interval '${db_days} days') ";
|
|
//$q.= " AND (b.location_start_id IN (%d,%d) OR b.location_end_id IN (%d,%d)) ";
|
|
$q.= " AND (";
|
|
$q.= "ST_DWithin(c.geometry,ST_SetSRID(ST_MakePoint(${limit_lng},${limit_lat}),4326)::geography,${limit_rad})";
|
|
$q.= " OR ";
|
|
$q.= "ST_DWithin(d.geometry,ST_SetSRID(ST_MakePoint(${limit_lng},${limit_lat}),4326)::geography,${limit_rad})";
|
|
$q.= ") ORDER BY b.travel_date DESC";
|
|
syslog(LOG_WARNING,$q);
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r)) {
|
|
$cache_from = [];
|
|
$cache_to = [];
|
|
$result = [];
|
|
while ($f = pg_fetch_assoc($r)) {
|
|
if (array_key_exists($f["location_start_geometry"],$cache_from) && $f["location_end_geometry"]==$cache_from[$f["location_start_geometry"]]) {
|
|
continue; // We have similar trip in result set - let's skip it
|
|
}
|
|
if (array_key_exists($f["location_end_geometry"],$cache_to) && $f["location_start_geometry"]==$cache_to[$f["location_end_geometry"]]) {
|
|
continue; // We have similar trip in result set - let's skip it
|
|
}
|
|
$result[] = $f;
|
|
$cache_from[$f["location_start_geometry"]] = $f["location_end_geometry"];
|
|
$cache_to[$f["location_end_geometry"]] = $f["location_start_geometry"];
|
|
}
|
|
unset($cache_from); // clear
|
|
unset($cache_to); // clear
|
|
return [$result, NULL];
|
|
}
|
|
return [NULL,pg_last_error()];
|
|
}
|
|
|
|
public function activityByRangeAndTimeAndRadius($gpsdb, $db, $member_id, $days, $lat, $lng, $radius, $what, $time, $time_delta=self::TIME_DELTA) {
|
|
syslog(LOG_WARNING,"Activity::activityByRangeAndTimeAndRadius(\$gpsdb, \$db, $member_id, $days, $lat, $lng, $radius, $what, $time, $time_delta)");
|
|
$limit_lat = (float)$lat;
|
|
$limit_lng = (float)$lng;
|
|
$limit_rad = $radius;
|
|
$db_time = strtotime($time);
|
|
$db_days = date("w",$db_time) + (int)$days;
|
|
$db_time_delta = (int)$time_delta;
|
|
syslog(LOG_WARNING,"${db_time}=".date("Y-m-d H:i",$db_time));
|
|
// Ranges
|
|
$dh = 60*date("G",$db_time)+date("i",$db_time);
|
|
$d1 = $dh - $db_time_delta;
|
|
$d2 = $dh + $db_time_delta;
|
|
syslog(LOG_WARNING,"${d1} <= x <= ${d2}");
|
|
$q = "SELECT e.* FROM (SELECT b.*, 60*date_part('hour', b.travel_date)+date_part('minute',b.travel_date) AS dm, ";
|
|
$q.= " c.geometry AS location_start_geometry, d.geometry AS location_end_geometry ";
|
|
$q.= " FROM trackedemail_item a, parsedemail_item b ";
|
|
$q.= " LEFT JOIN address c ON (c.id=b.location_start_id) ";
|
|
$q.= " LEFT JOIN address d ON (d.id=b.location_end_id) ";
|
|
$q.= " WHERE a.id=b.trackedemail_item_id AND a.member_id=".((int)$member_id);
|
|
$q.= " AND b.dup_id IS NULL AND b.travel_date_end > (now() - interval '${db_days} days') ";
|
|
$q.= " AND (";
|
|
$q.= "ST_DWithin(c.geometry,ST_SetSRID(ST_MakePoint(${limit_lng},${limit_lat}),4326)::geography,${limit_rad})";
|
|
$q.= " OR ";
|
|
$q.= "ST_DWithin(d.geometry,ST_SetSRID(ST_MakePoint(${limit_lng},${limit_lat}),4326)::geography,${limit_rad})";
|
|
$q.= ")) AS e ";
|
|
$q.= " WHERE e.dm>=${d1} AND e.dm<=${d2} ";
|
|
$q.= " ORDER BY e.travel_date DESC";
|
|
syslog(LOG_WARNING,$q);
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r)) {
|
|
syslog(LOG_WARNING,"parsedemail_item(s) to process: ".pg_num_rows($r));
|
|
$cache_from = [];
|
|
$cache_to = [];
|
|
$result = [];
|
|
while ($f=pg_fetch_assoc($r)) {
|
|
if (array_key_exists($f["location_start_geometry"],$cache_from) && $f["location_end_geometry"]==$cache_from[$f["location_start_geometry"]]) {
|
|
continue; // We have similar trip in result set - let's skip it
|
|
}
|
|
if (array_key_exists($f["location_end_geometry"],$cache_to) && $f["location_start_geometry"]==$cache_to[$f["location_end_geometry"]]) {
|
|
continue; // We have similar trip in result set - let's skip it
|
|
}
|
|
$result[] = $f;
|
|
}
|
|
unset($cache_from); // clear
|
|
unset($cache_to); // clear
|
|
return [$result, NULL];
|
|
}
|
|
return [NULL,pg_last_error()];
|
|
}
|
|
|
|
public function activityByRangeAndTime($db, $member_id, $days, $time, $time_delta=self::TIME_DELTA, $country=self::DEFAULT_COUNTRY) {
|
|
syslog(LOG_WARNING,"Activity::activityByRangeAndTime(\$db, $member_id, $days, $time, $time_delta");
|
|
$db_time = strtotime($time);
|
|
$db_days = date("w",$db_time) + (int)$days;
|
|
$db_time_delta = (int)$time_delta;
|
|
$db_country = pg_escape_string($country);
|
|
syslog(LOG_WARNING,"${db_time}=".date("Y-m-d H:i",$db_time));
|
|
// Ranges
|
|
$dh = 60*date("G",$db_time)+date("i",$db_time);
|
|
$d1 = $dh - $db_time_delta;
|
|
$d2 = $dh + $db_time_delta;
|
|
syslog(LOG_WARNING,"${d1} <= x <= ${d2}");
|
|
$q = "SELECT e.* FROM (SELECT b.*, 60*date_part('hour', b.travel_date)+date_part('minute',b.travel_date) AS dm ";
|
|
$q.= " FROM trackedemail_item a, parsedemail_item b ";
|
|
$q.= " LEFT JOIN address c ON (c.id=b.location_start_id) ";
|
|
$q.= " LEFT JOIN address d ON (d.id=b.location_end_id) ";
|
|
$q.= " WHERE a.id=b.trackedemail_item_id AND a.member_id=".((int)$member_id);
|
|
$q.= " AND (c.country='${db_country}' OR d.country='${db_country}') ";
|
|
$q.= " AND b.dup_id IS NULL AND b.travel_date_end > (now() - interval '${db_days} days')) AS e ";
|
|
$q.= " WHERE e.dm>=${d1} AND e.dm<=${d2} ";
|
|
$q.= " ORDER BY e.travel_date DESC";
|
|
syslog(LOG_WARNING,$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 getAllTimeDays() {
|
|
$datediff = time() - self::TIME_LIMIT;
|
|
return round($datediff / (60 * 60 * 24));
|
|
}
|
|
}
|
|
// vi:ts=2
|
|
|