<?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