FloatBackOfffice/CRONS/geocode_fix_address.php

<?php

echo "[" . date("Y-m-d H:i:s") . "] GPS geocode_fix_address job is starting.\n";

require 'common/utils.php';
require 'common/config.php';
require 'common/address_city.php';
require 'common/Geocode.php';

$tables = getForeignTables('address');

$address_conditions = [
    "POSITION('Your driver' in address)>0",
];
foreach ($address_conditions as $condition) {
    $r = getAddressesByCondition($condition);
    while ($fa = pg_fetch_assoc($r)) {
        $address_id = $fa['id'];
        $address = $fa['address'];
        echo $address;
        $pos_driver = strpos(strtolower($address),'your driver');
        if($pos_driver!=false){
            $address = substr($address,0, $pos_driver);
        }
        $update_address = [
            'address' => $address
        ];
        updateDataTable('address', 'id=' . $address_id, $update_address);
        
    }
}

$duplicates = getDuplicatedAddresses();


while ($f = pg_fetch_assoc($duplicates)) {
    $latitude = $f['latitude'];
    $longitude = $f['longitude'];
    $condition = 'latitude = ' . $latitude . ' AND longitude=' . $longitude;
    $addresses = getAddressesByCondition($condition);
    $ids = [];
    while ($fa = pg_fetch_assoc($addresses)) {
        $ids[] = $fa['id'];
    } 
    $id_keep = array_shift($ids);
    if (count($ids) > 0) {
        $sql = '';
        foreach($ids as $foreign_id){
            foreach ($tables as $ft) {
                $table_name = $ft['table_name'];
                $field = $ft['column_name'];
                if ($table_name == 'tourist_attraction') {
                    continue;
                }

                $data_update = [
                    $field => $id_keep,
                ];
                $condition_update_foreigns = "{$field} = " . $foreign_id;
                updateDataTable($table_name, $condition_update_foreigns, $data_update);
            }
            deleteDataTable('tourist_attraction', ' address_id =' . $foreign_id);
            deleteDataTable('address', ' id =' . $foreign_id);
        }
    }
}
//
$empty_time_zones = getAddressEmptyTimezone();
while ($fe = pg_fetch_assoc($empty_time_zones)) {
    $address_id = $fe['id'];
    $address = $fe['address'];
    $timezone_id = $fe['timezone_id'];
    $geoinfo = Geocode::geocodeAddress($address);
    if (isset($geoinfo) && is_array($geoinfo)) {
        $postal = $geoinfo['postal'];
        $timezoneID = getTimeZoneID($geoinfo['timeZoneId']);
        $update_address = [
            'timezone' => $timezoneID
        ];
        updateDataTable('address', 'id=' . $address_id, $update_address);
        if ($timezoneID == $timezone_id) {
            $timezoneName = $geoinfo['timeZoneId'];
            $update_address_timezone = [
                'timezone' => $timezoneName,
            ];
            updateDataTable('address_timezone', 'id=' . $timezone_id, $update_address_timezone);
        }
    }
}
//get invalid address by condition
$address_conditions = [
    #"address NOT ILIKE '%singap%' and country='SG'",
];
foreach ($address_conditions as $condition) {
    $r = getAddressesByCondition($condition);
    while ($fa = pg_fetch_assoc($r)) {
        $address_id = $fa['id'];
        $address = $fa['address'];
        $latitude = $fa['latitude'];
        $longitude = $fa['longitude'];
        $geoinfo = Geocode::reverseGPS($latitude,$longitude);
       
        if (isset($geoinfo) && is_array($geoinfo)) {
            $postal = $geoinfo['postal'];
            $timezone = getTimeZoneID($geoinfo['timeZoneId']);
            $country = $geoinfo['country'];
            $update_address = [
                'postal' => $postal,
                'timezone' => $timezone,
                'country' => $country
            ];
            updateDataTable('address', 'id=' . $address_id, $update_address);
        }
    }
}
//invalid postal
$address_conditions = [
    #"postal<>'Singapore' and left(postal,1)<>'0' and left(postal,1)<>'1' and left(postal,1)<>'2' and left(postal,1)<>'3' and left(postal,1)<>'4' and left(postal,1)<>'5' and left(postal,1)<>'6' and left(postal,1)<>'7' and left(postal,1)<>'8' and left(postal,1)<>'9' and country<>'GB' and country<>'CA' and country<>'IE'",
    #"postal is NULL or postal=''"
];
foreach ($address_conditions as $condition) {
    $r = getAddressesByCondition($condition);
    while ($fa = pg_fetch_assoc($r)) {
        $address_id = $fa['id'];
        $address = $fa['address'];
        $latitude = $fa['latitude'];
        $longitude = $fa['longitude'];
        $geoinfo = Geocode::reverseGPS($latitude,$longitude);
        
        if (isset($geoinfo) && is_array($geoinfo)) {
            $postal = $geoinfo['postal'];
            $country = isset($geoinfo['country'])?$geoinfo['country']:$fa['country'];
            if(empty($postal)){
                $postal = getPostalCode($latitude,$longitude,$country);
            }
            $timezone =  getTimeZoneID($geoinfo['timeZoneId']);
            $update_address = [
                'postal' => $postal,
                'timezone' => $timezone,
                'country' => $country
            ];
            updateDataTable('address', 'id=' . $address_id, $update_address);
        }
    }
}
echo "[" . date("Y-m-d H:i:s") . "] GPS geocode_fix_address job complete.\n";

function getDuplicatedAddresses()
{
    global $readOnlyReplicaConn;
    $q = "SELECT latitude, longitude, count(*)
            FROM address
            GROUP BY latitude, longitude
            HAVING count(*) >1
            ORDER BY count(*) DESC";
    $r = pg_query($readOnlyReplicaConn, $q);
    return $r;
}

function getAddressEmptyTimezone()
{
    global $readOnlyReplicaConn;
    $q = "SELECT a.id, a.address, a.latitude, a.longitude, b.id as timezone_id
            FROM address a
            LEFT JOIN address_timezone b ON a.timezone=b.id
            WHERE b.timezone ='' OR b.timezone IS NULL";
    $r = pg_query($readOnlyReplicaConn, $q);
    return $r;
}

function getAddressesByCondition($condition)
{
    global $readOnlyReplicaConn;
    $q = "SELECT *
            FROM address
            WHERE " . $condition;
    $r = pg_query($readOnlyReplicaConn, $q);
    return $r;
}