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

359 lines
15 KiB
PHP
Raw Permalink Blame History

<?php
class Address
{
public function getAddressById($db, $id)
{
syslog(LOG_WARNING, "Address::getAddressById(\$db, $id)");
$result = array();
$q = "SELECT a.*,b.timezone AS \"timeZoneId\" FROM address a LEFT JOIN address_timezone b ON (b.id=a.timezone) ";
$q .= " WHERE a.id=" . ((int) $id);
syslog(LOG_WARNING, $q);
$r = pg_query($db, $q);
if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
$f["address"] = html_entity_decode ($f["address"],ENT_QUOTES|ENT_HTML5,"UTF-8");
$result = $f;
}
return $result;
}
public function getAddress($db, $data, $prefix = "location_start")
{
syslog(LOG_WARNING, "Address::getAddress(\$db, \$data, $prefix)");
$db_address = pg_escape_string(trim(html_entity_decode($data[$prefix],ENT_QUOTES|ENT_HTML5,"UTF-8")));
error_log(json_encode($data));
$db_lat = (float) $data["${prefix}_lat"];
$db_lng = (float) $data["${prefix}_lng"];
$q = "SELECT id FROM address WHERE address='${db_address}' AND latitude=${db_lat} AND longitude=${db_lng}";
$r = pg_query($db, $q);
if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
return $f["id"];
}
$q = "SELECT id FROM address WHERE address='${db_address}'";
$r = pg_query($db, $q);
if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
return $f["id"];
}
$db_timezone = (int) $data["${prefix}_tz"];
if ($db_timezone == 0) {
$db_timezone = 1;
}
// Asia/Singapore
$db_postal = pg_escape_string($data["${prefix}_postal"]);
if ($db_postal == "") {
if (($pos = strrpos($db_address, " ")) !== false) {
$db_postal = trim(substr($db_address, $pos));
}
}
$db_country = ""; // SG?
if (array_key_exists("${prefix}_country", $data)) {
$db_country = pg_escape_string($data["${prefix}_country"]);
}
$q = "INSERT INTO address (address,latitude,longitude,timezone,geocoding_date,postal,country,geometry) VALUES(";
$q .= "'${db_address}',${db_lat},${db_lng},${db_timezone},'" . date("Y-m-d") . "','${db_postal}','${db_country}',ST_SetSRID(ST_MakePoint(${db_lng},${db_lat}), 4326)";
$q .= ") RETURNING id";
syslog(LOG_WARNING, $q);
$r = pg_query($db, $q);
if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
return $f["id"];
}
return null;
}
public function save($db, $result)
{
// Check the address exists
$db_address = pg_escape_string(html_entity_decode($result["address"],ENT_QUOTES|ENT_HTML5,"UTF-8"));
$db_address_input = isset($result["address_input"])?pg_escape_string(html_entity_decode($result["address_input"],ENT_QUOTES|ENT_HTML5,"UTF-8")):"";
$q = "SELECT id, address, latitude as lat, longitude as lng, postal, country, timezone, plus_code FROM address WHERE lower(address)=lower('${db_address}') ";
$q .= "AND latitude<>0 AND longitude<>0 AND geocoding_date IS NOT NULL AND postal IS NOT NULL ";
$q .= "ORDER BY geocoding_date DESC LIMIT 1";
error_log($q);
$r = pg_query($db, $q);
if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
return $f;
}
syslog(LOG_WARNING, 'Address::save($db, $result)');
$db_lat = floatval($result["lat"]);
$db_lng = floatval($result["lng"]);
$db_postal = pg_escape_string($result["postal"]);
$db_plus_code = pg_escape_string($result["plus_code"]);
$db_city = pg_escape_string($result["city"]);
$db_country = pg_escape_string($result["country"]);
$db_tz = Geocode::getTimezone($db, $result["timeZoneId"]);
$db_city_lat = floatval($result["city_lat"]);
$db_city_lng = floatval($result["city_lng"]);
$city_id = Address::getCity($db, $db_city, $db_country,$db_city_lat, $db_city_lng);
if (empty($city_id)) {
$city_id = Address::insertCity($db, $db_city, $db_country, $db_city_lat, $db_city_lng);
}
if (empty($db_postal)) {
$db_postal = Address::getClosestPostalCode($db_lat, $db_lng, $db_country);
}
$q = "INSERT INTO address (address,latitude,longitude,timezone,geocoding_date,postal,country,geometry, city_id, plus_code) VALUES (";
$q .= "'${db_address}',${db_lat},${db_lng}," . ($db_tz == null ? "NULL" : $db_tz) . ",now(),'${db_postal}','${db_country}',ST_SetSRID(ST_MakePoint(${db_lng},${db_lat}), 4326),'${city_id}','${db_plus_code}')";
$q .= " RETURNING id, address, latitude as lat, longitude as lng, postal, country, timezone, city_id, plus_code";
error_log($q);
$r = pg_query($db, $q);
if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
if(!empty($db_address_input) && $db_plus_code!=$db_address_input){
Address::saveAliasAddress($db, $f['id'], $db_address_input);
}
return $f;
}
return null;
}
public function saveAliasAddress($db, $address_id, $address, $source='Google'){
$q = "SELECT id, address_id, name FROM address_alias WHERE lower(name)=lower('${address}') ";
$q .= "ORDER BY geocoding_date DESC LIMIT 1";
error_log($q);
$r = pg_query($db, $q);
if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
return $f;
}
$q = "INSERT INTO address_alias (address_id,name,geocoding_date,source) VALUES (";
$q .= "${address_id},'${address}', now() ,'${source}')";
$q .= " RETURNING address_id, name";
error_log($q);
$r = pg_query($db, $q);
if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
return $f;
}
return null;
}
public function update($db, $condition, $data) {
syslog(LOG_WARNING,'Address::update(): '.json_encode($data));
$q = "select column_name, data_type, character_maximum_length from INFORMATION_SCHEMA.COLUMNS where table_name = 'address'";
$r = pg_query($db, $q);
while ($f = pg_fetch_assoc($r)) {
$columns[$f['column_name']] = $f['data_type'];
}
if(count($data)<1) return null;
$q = "UPDATE address SET id=id ";
foreach ($data as $key => $val) {
if ($key == "id") {
continue;
}
if (array_key_exists($key, $data) && !in_array($columns[$key], ['character varying', 'date', 'timestamp with time zone', 'timestamp without time zone'])) {
$q .= ", ${key} = " . ($val == "" ? "NULL" : $val);
} else {
$q .= ", ${key} = '" . pg_escape_string($val) . "'";
}
}
$q .= " WHERE " . $condition . "";
$r = pg_query($db, $q);
if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
return $f;
}
return array(NULL, pg_last_error($db));
}
public function searchInView($db, $country, $address) {
$db_country = pg_escape_string($country); // is not used for now...
$db_address = pg_escape_string(html_entity_decode($address,ENT_QUOTES|ENT_HTML5,"UTF-8"));
$words = preg_split("/[\s,]+/", $db_address);
$tsquery = implode(':* & ', $words). ':* & '.$db_country;
$q = "SELECT * FROM address_view a
WHERE address_search @@ to_tsquery('simple', '${tsquery}')
ORDER BY ts_rank(address_search,to_tsquery('simple', '${tsquery}')) desc
LIMIT 10";
$r = pg_query($db, $q);
$results = [];
if ($r && pg_num_rows($r)) {
while ($f = pg_fetch_assoc($r)) {
$f["address"] = html_entity_decode($f["address"],ENT_QUOTES|ENT_HTML5,"UTF-8");
$results[] = $f;
}
if (count($results) > 9) {
return $results;
}
}
return $results;
}
public function search($db, $country, $address)
{
//syslog(LOG_WARNING,"Address::search(\$db, $country, $address)");
$db_country = pg_escape_string($country); // is not used for now...
$db_address = pg_escape_string(html_entity_decode($address,ENT_QUOTES|ENT_HTML5,"UTF-8"));
$q = "SELECT a.*, b.timezone AS \"timeZoneId\" FROM address a LEFT JOIN address_timezone b ON (b.id=a.timezone) ";
$q .= " WHERE UPPER(a.address) ILIKE ('%${db_address}%') AND a.country='${db_country}' LIMIT 10";
//error_log($q);
$r = pg_query($db, $q);
$results = [];
if ($r && pg_num_rows($r)) {
while ($f = pg_fetch_assoc($r)) {
$f["address"] = html_entity_decode($f["address"],ENT_QUOTES|ENT_HTML5,"UTF-8");
$results[] = $f;
}
if (count($results) > 9) {
return $results;
}
}
if ($country == 'SG') {
$q = "SELECT id as singapore_buildings_id, address, latitude, longitude, 1 AS timezone, NOW() as geocoding_date, postal, 'SG' AS country, building AS description, 'Asia/Singapore' AS \"timeZoneId\" ";
$q .= " FROM singapore_buildings WHERE UPPER(address) ILIKE ('%${db_address}%') LIMIT 10";
$r = pg_query($db, $q);
if ($r && pg_num_rows($r)) {
while ($f = pg_fetch_assoc($r)) {
$f["address"] = html_entity_decode($f["address"],ENT_QUOTES|ENT_HTML5,"UTF-8");
$results[] = $f;
}
return $results;
}
}
/*
savvy=> \d singapore_buildings
Table "public.singapore_buildings"
Column | Type | Modifiers
-----------+------------------------+------------------------------------------------------------------
id | integer | not null default nextval('singapore_buildings_id_seq'::regclass)
address | character varying(150) | not null
blk_no | character varying(10) |
building | character varying(100) |
latitude | numeric |
longitude | numeric |
postal | character varying(6) | not null
road_name | character varying(40) | not null
searchval | character varying(100) | not null
x | numeric |
y | numeric |
Indexes:
"singapore_buildings_pkey" PRIMARY KEY, btree (id)
savvy=> \d address
Table "public.address"
Column | Type | Modifiers
----------------+------------------------+------------------------------------------------------
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) |
*/
return $results;
}
public function recentTrips($db, $member_id, $country, $address)
{
$db_country = pg_escape_string($country); // is not used for now...
$db_address = pg_escape_string(html_entity_decode($address,ENT_QUOTES|ENT_HTML5,"UTF-8"));
$q = "SELECT a.location_start_id, a.location_end_id ";
$q .= " FROM trackedemail_item b, parsedemail_item a ";
$q .= " LEFT JOIN address c ON (c.id=a.location_start_id) LEFT JOIN address d ON (d.id=a.location_end_id) ";
$q .= " WHERE b.id=a.trackedemail_item_id AND b.member_id=" . ((int) $member_id);
$q .= " AND (c.country='${db_country}' OR d.country='${db_country}')";
$q .= " ORDER BY a.travel_date DESC LIMIT 10";
//error_log($q);
$r = pg_query($db, $q);
$results = [];
$address = [];
if ($r && pg_num_rows($r)) {
while ($f = pg_fetch_assoc($r)) {
if (!array_key_exists($f["location_start_id"], $address)) {
$results[] = self::getAddressById($db, $f["location_start_id"]);
$address[$f["location_start_id"]] = 1;
}
if (!array_key_exists($f["location_end_id"], $address)) {
$results[] = self::getAddressById($db, $f["location_end_id"]);
$address[$f["location_end_id"]] = 1;
}
}
return $results;
}
return null;
}
public function favourites($db, $member_id, $country, $address)
{
// Not implemented
return null;
}
public static function getTzById($db, $id)
{
syslog(LOG_WARNING, "Address::getTzById(\$db, $id)");
$q = "SELECT timezone FROM address_timezone WHERE id=" . ((int) $id);
//syslog(LOG_WARNING,$q);
$r = pg_query($db, $q);
if ($r && pg_num_rows($r) && $f = pg_fetch_row($r)) {
return $f[0];
}
return 'America/Los_Angeles'; // 2
}
public static function getTzByAddressId($db, $id)
{
syslog(LOG_WARNING, "Address::getTzByAddressId(\$db, $id)");
$q = "SELECT timezone FROM address_timezone WHERE id=(SELECT timezone FROM address WHERE id=" . ((int) $id) . ")";
//syslog(LOG_WARNING,"ActivityApi: ".$q);
$r = pg_query($db, $q);
if ($r && pg_num_rows($r) && $f = pg_fetch_row($r)) {
//syslog(LOG_WARNING,"ActivityApi: ".$f[0]);
return $f[0];
} else {
//syslog(LOG_WARNING,"ActivityApi: ".pg_last_error());
}
return 'America/Los_Angeles'; // 2
}
public static function getCity($db, $city, $country_code, $latitude='', $longitude='')
{
$q = "SELECT id FROM geofence_area_city WHERE (LOWER(city) ILIKE '" . strtolower($city) . "' AND country='" . $country_code . "') OR (latitude = '${latitude}' AND longitude ='${longitude}') LIMIT 1";
$r = pg_query($db, $q);
if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
return $f['id'];
} else {
return null;
}
}
public static function insertCity($db, $city, $country_code, $latitude, $longitude)
{
$i = "INSERT INTO geofence_area_city (city,country,latitude,longitude,location) VALUES ('${city}','${country_code}',${latitude},${longitude},ST_SetSRID(ST_MakePoint(${longitude},${latitude}), 4326)) RETURNING id";
$city_id=null;
$r = pg_query($db, $i);
if ($r && pg_num_rows($r) && $f = pg_fetch_row($r)) {
$city_id = $f[0];
}
return $city_id;
}
public function getClosestPostalCode($latitude, $longitude, $country, $distance = 50000)
{
$q = "SELECT postal,
ST_DistanceSphere(geometry::geometry, ST_SetSRID(ST_MakePoint(" . $longitude . ", " . $latitude . "),4326)) AS distance
FROM geoname_postal_code
WHERE ST_DistanceSphere(geometry::geometry, ST_SetSRID(ST_MakePoint(" . $longitude . ", " . $latitude . "),4326)) BETWEEN 0 AND " . $distance . " AND country='" . $country . "'
ORDER BY distance
LIMIT 1;";
$r = pg_query($q);
if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
return $f['postal'];
}
return null;
}
}
// vi:ts=2
Reference in New Issue View Git Blame Copy Permalink