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

281 lines
13 KiB
PHP
Raw Permalink Blame History

<?php
class ActivityDirect {
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 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,"ActivityDirect::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);
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, "No activity found"];
}
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.= " c.geometry AS location_start_geometry, d.geometry AS location_end_geometry ";
$q.= ",c.address AS location_start,c.latitude AS location_start_lat,c.longitude AS location_start_lng";
$q.= ",c.timezone AS location_start_tz,c.postal AS location_start_postal,c.country AS location_start_country";
$q.= ",c.description AS location_start_description";
$q.= ",d.address AS location_end,d.latitude AS location_end_lat,d.longitude AS location_end_lng";
$q.= ",d.timezone AS location_end_tz,d.postal AS location_end_postal,d.country AS location_end_country";
$q.= ",d.description AS location_end_description";
$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)) {
$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, NULL];
}
return [NULL,pg_last_error()];
}
public static function activityByRangeAndRadius($gpsdb, $db, $member_id, $days, $lat, $lng, $radius, $what) {
syslog(LOG_WARNING,"ActivityDirect::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.= ",c.address AS location_start,c.latitude AS location_start_lat,c.longitude AS location_start_lng";
$q.= ",c.timezone AS location_start_tz,c.postal AS location_start_postal,c.country AS location_start_country";
$q.= ",c.description AS location_start_description";
$q.= ",d.address AS location_end,d.latitude AS location_end_lat,d.longitude AS location_end_lng";
$q.= ",d.timezone AS location_end_tz,d.postal AS location_end_postal,d.country AS location_end_country";
$q.= ",d.description AS location_end_description";
$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
}
$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;
$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,"ActivityDirect::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.= ",c.address AS location_start,c.latitude AS location_start_lat,c.longitude AS location_start_lng";
$q.= ",c.timezone AS location_start_tz,c.postal AS location_start_postal,c.country AS location_start_country";
$q.= ",c.description AS location_start_description";
$q.= ",d.address AS location_end,d.latitude AS location_end_lat,d.longitude AS location_end_lng";
$q.= ",d.timezone AS location_end_tz,d.postal AS location_end_postal,d.country AS location_end_country";
$q.= ",d.description AS location_end_description";
$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
}
$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;
}
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,"ActivityDirect::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.= " c.geometry AS location_start_geometry, d.geometry AS location_end_geometry ";
$q.= ",c.address AS location_start,c.latitude AS location_start_lat,c.longitude AS location_start_lng";
$q.= ",c.timezone AS location_start_tz,c.postal AS location_start_postal,c.country AS location_start_country";
$q.= ",c.description AS location_start_description";
$q.= ",d.address AS location_end,d.latitude AS location_end_lat,d.longitude AS location_end_lng";
$q.= ",d.timezone AS location_end_tz,d.postal AS location_end_postal,d.country AS location_end_country";
$q.= ",d.description AS location_end_description";
$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)) {
$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, NULL];
}
return [NULL,pg_last_error()];
}
protected function getAllTimeDays() {
$datediff = time() - self::TIME_LIMIT;
return round($datediff / (60 * 60 * 24));
}
}
// vi:ts=2
Reference in New Issue View Git Blame Copy Permalink