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

348 lines
17 KiB
PHP
Raw Permalink Blame History

<?php
class Destinations {
const TIME_DELTA = 180; // min (3 hrs)
const TIME_LIMIT = 1262322000; // 2010-01-01
const DEFAULT_COUNTRY = 'SG';
const DEFAULT_OPTIONS = 5;
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 = 100000; // 100km
public static function byActivity($db, $gpsdb, $member_id, $lat, $lng, $radius, $time, $time_delta=Destinations::TIME_DELTA, $country=Destinations::DEFAULT_COUNTRY) {
syslog(LOG_WARNING,"Activity::getActivity(\$db, \$gpsdb, $member_id, $lat, $lng, $radius, $time, $time_delta, $country)");
$all_time_days = Destinations::getAllTimeDays();
$res = [];
list($res1, $err1) = Destinations::activityByRangeAndTimeAndRadius(
$gpsdb, $db, $member_id, $all_time_days, $lat, $lng, $radius, 'start', $time, $time_delta);
if ($res1 && count($res1)>0) {
$res = Destinations::uniqueTrips(array_merge($res,$res1));
if (count($res)>=Destinations::DEFAULT_OPTIONS) {
return [$res, NULL];
}
}
syslog(LOG_WARNING,$err1);
list($res2, $err2) = Destinations::activityByRangeAndTimeAndRadius(
$gpsdb, $db, $member_id, $all_time_days, $lat, $lng, $radius, 'end', $time, $time_delta);
if ($res2 && count($res2)>0) {
$res = Destinations::uniqueTrips(array_merge($res,$res2));
if (count($res)>=Destinations::DEFAULT_OPTIONS) {
return [$res, NULL];
}
}
syslog(LOG_WARNING,$err2);
list($res3, $err3) = Destinations::activityByRangeAndRadius(
$gpsdb, $db, $member_id, $all_time_days, $lat, $lng, $radius, 'start');
if ($res3 && count($res3)>0) {
$res = Destinations::uniqueTrips(array_merge($res,$res3));
if (count($res)>=Destinations::DEFAULT_OPTIONS) {
return [$res, NULL];
}
}
syslog(LOG_WARNING,$err3);
list($res4, $err4) = Destinations::activityByRangeAndRadius(
$gpsdb, $db, $member_id, $all_time_days, $lat, $lng, $radius, 'end');
if ($res4 && count($res4)>0) {
$res = Destinations::uniqueTrips(array_merge($res,$res4));
if (count($res)>=Destinations::DEFAULT_OPTIONS) {
return [$res, NULL];
}
}
syslog(LOG_WARNING,$err4);
if ($country=='SG') {
// For US it may produce the results too far apart
list($res1, $err1) = Destinations::activityByRangeAndTime($db, $member_id, 7, $time, $time_delta, $country);
if ($res1 && count($res1)>0) {
$res = Destinations::uniqueTrips(array_merge($res,$res1));
if (count($res)>=Destinations::DEFAULT_OPTIONS) {
return [$res, NULL];
}
}
list($res2, $err2) = Destinations::activityByRangeAndTime($db, $member_id, 30, $time, $time_delta, $country);
if ($res2 && count($res2)>0) {
$res = Destinations::uniqueTrips(array_merge($res,$res2));
if (count($res)>=Destinations::DEFAULT_OPTIONS) {
return [$res, NULL];
}
}
list($res3, $err3) = Destinations::activityByRangeAndTime($db, $member_id, $all_time_days, $time, $time_delta, $country);
if ($res3 && count($res3)>0) {
$res = Destinations::uniqueTrips(array_merge($res,$res3));
if (count($res)>=Destinations::DEFAULT_OPTIONS) {
return [$res, NULL];
}
}
list($res4, $err4) = Destinations::activityByRange($db, $member_id, 7, $country);
if ($res4 && count($res4)>0) {
$res = Destinations::uniqueTrips(array_merge($res,$res4));
if (count($res)>=Destinations::DEFAULT_OPTIONS) {
return [$res, NULL];
}
}
list($res5, $err5) = Destinations::activityByRange($db, $member_id, 30, $country);
if ($res5 && count($res5)>0) {
$res = Destinations::uniqueTrips(array_merge($res,$res5));
if (count($res)>=Destinations::DEFAULT_OPTIONS) {
return [$res, NULL];
}
}
list($res6, $err6) = Destinations::activityByRange($db, $member_id, $all_time_days, $country);
if ($res6 && count($res6)>0) {
$res = Destinations::uniqueTrips(array_merge($res,$res6));
if (count($res)>=Destinations::DEFAULT_OPTIONS) {
return [$res, NULL];
}
}
}
//*/
if ($res && count($res)>0) {
return [$res, NULL];
}
return [NULL, "We could not find any recent activity in this area"];
}
public static function getAddressActivity($db, $address, $member_id, $time, $time_delta=Destinations::TIME_DELTA) {
// TODO: radius search
}
/*
id | bigint | not null default nextval('address_id_seq'::regclass)
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) |
city_id | integer |
*/
public static function activityByRange($db, $member_id, $days, $country=Destinations::DEFAULT_COUNTRY) {
$db_time = strtotime($time);
$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 ";
/////// Start Locations ///////
$q.= ", c.address AS location_start_address, c.latitude AS location_start_lat, c.longitude AS location_start_lng";
$q.= ", c.timezone AS location_start_timezone, c.postal AS location_start_postal, c.country AS location_start_country";
$q.= ", c.description AS location_start_description, c.city_id AS location_start_city_id, c.geometry AS location_start_geometry ";
$q.= ", d.address AS location_end_address, d.latitude AS location_end_lat, d.longitude AS location_end_lng";
$q.= ", d.timezone AS location_end_timezone, d.postal AS location_end_postal, d.country AS location_end_country";
$q.= ", d.description AS location_end_description, d.city_id AS location_end_city_id, d.geometry AS location_end_geometry ";
/////// End Locations ///////
$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 LIMIT ".Destinations::DEFAULT_OPTIONS;
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_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");
$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 ";
/////// Start Locations ///////
$q.= ", c.address AS location_start_address, c.latitude AS location_start_lat, c.longitude AS location_start_lng";
$q.= ", c.timezone AS location_start_timezone, c.postal AS location_start_postal, c.country AS location_start_country";
$q.= ", c.description AS location_start_description, c.city_id AS location_start_city_id, c.geometry AS location_start_geometry ";
$q.= ", d.address AS location_end_address, d.latitude AS location_end_lat, d.longitude AS location_end_lng";
$q.= ", d.timezone AS location_end_timezone, d.postal AS location_end_postal, d.country AS location_end_country";
$q.= ", d.description AS location_end_description, d.city_id AS location_end_city_id, d.geometry AS location_end_geometry ";
/////// End Locations ///////
$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 LIMIT ".Destinations::DEFAULT_OPTIONS;
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_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");
$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 static function activityByRangeAndTimeAndRadius($gpsdb, $db, $member_id, $days, $lat, $lng, $radius, $what, $time, $time_delta=Destinations::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 ";
/////// Start Locations ///////
$q.= ", c.address AS location_start_address, c.latitude AS location_start_lat, c.longitude AS location_start_lng";
$q.= ", c.timezone AS location_start_timezone, c.postal AS location_start_postal, c.country AS location_start_country";
$q.= ", c.description AS location_start_description, c.city_id AS location_start_city_id, c.geometry AS location_start_geometry ";
$q.= ", d.address AS location_end_address, d.latitude AS location_end_lat, d.longitude AS location_end_lng";
$q.= ", d.timezone AS location_end_timezone, d.postal AS location_end_postal, d.country AS location_end_country";
$q.= ", d.description AS location_end_description, d.city_id AS location_end_city_id, d.geometry AS location_end_geometry ";
/////// End Locations ///////
$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 LIMIT ".Destinations::DEFAULT_OPTIONS;
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_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");
$result[] = $f;
}
unset($cache_from); // clear
unset($cache_to); // clear
return [$result, NULL];
}
return [NULL,pg_last_error()];
}
public static function activityByRangeAndTime($db, $member_id, $days, $time, $time_delta=Destinations::TIME_DELTA, $country=Destinations::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 ";
/////// Start Locations ///////
$q.= ", c.address AS location_start_address, c.latitude AS location_start_lat, c.longitude AS location_start_lng";
$q.= ", c.timezone AS location_start_timezone, c.postal AS location_start_postal, c.country AS location_start_country";
$q.= ", c.description AS location_start_description, c.city_id AS location_start_city_id, c.geometry AS location_start_geometry ";
$q.= ", d.address AS location_end_address, d.latitude AS location_end_lat, d.longitude AS location_end_lng";
$q.= ", d.timezone AS location_end_timezone, d.postal AS location_end_postal, d.country AS location_end_country";
$q.= ", d.description AS location_end_description, d.city_id AS location_end_city_id, d.geometry AS location_end_geometry ";
/////// End Locations ///////
$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 LIMIT ".Destinations::DEFAULT_OPTIONS;
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_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");
$result[] = $f;
}
return [$result, NULL];
}
return [NULL,pg_last_error()];
}
protected static function getAllTimeDays() {
$datediff = time() - Destinations::TIME_LIMIT;
return round($datediff / (60 * 60 * 24));
}
protected static function uniqueTrips($res) {
$cache = [];
$result = [];
foreach ($res as $trip) {
$key1ids = $trip["location_start_id"]."_".$trip["location_end_id"];
$key1gps = $trip["location_start_lat"]."_".$trip["location_start_lng"]."_".$trip["location_end_lat"]."_".$trip["location_end_lng"];
$key2ids = $trip["location_end_id"]."_".$trip["location_start_id"];
$key2gps = $trip["location_end_lat"]."_".$trip["location_end_lng"]."_".$trip["location_start_lat"]."_".$trip["location_start_lng"];
if (array_key_exists($key1ids, $cache) || array_key_exists($key2ids, $cache)) {
continue;
}
if (array_key_exists($key1gps, $cache) || array_key_exists($key2gps, $cache)) {
continue;
}
$cache[$key1ids] = $key2ids;
$cache[$key1gps] = $key2gps;
$result[] = $trip;
}
return $result;
}
}
// vi:ts=2
Reference in New Issue View Git Blame Copy Permalink