FloatBackOfffice/CRONS/geocode_fix_timezone.php

<?php

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

require('../backend.php');

$httpAuthToken = $savvyext->cfgReadChar('system.oauth2_token');

$db_host = $savvyext->cfgReadChar('database.host');
$db_name = $savvyext->cfgReadChar('database.name');
$db_user = $savvyext->cfgReadChar('database.user');
$db_pass = $savvyext->cfgReadChar('database.pass');
$db_port = $savvyext->cfgReadLong('database.port');
$connstr = "host=${db_host} port=${db_port} dbname=${db_name} user=${db_user} password=${db_pass}";
$conn = pg_connect($connstr);

$db_host = $savvyext->cfgReadChar('database_replica.host');
$db_name = $savvyext->cfgReadChar('database_replica.name');
$db_user = $savvyext->cfgReadChar('database_replica.user');
$db_pass = $savvyext->cfgReadChar('database_replica.pass');
$db_port = $savvyext->cfgReadLong('database_replica.port');
$readOnlyReplicaConnstr = "host=${db_host} port=${db_port} dbname=${db_name} user=${db_user} password=${db_pass}";
$readOnlyReplicaConn = pg_connect($readOnlyReplicaConnstr);


$q = "SELECT * FROM address WHERE timezone IS NULL OR timezone<1";
$r = pg_query($readOnlyReplicaConn, $q);

while ($f=pg_fetch_assoc($r)) {
    if ($f["address"]=="") continue;
    $q0 = "SELECT * FROM address WHERE lower(address)=lower('".pg_escape_string($f["address"])."') AND id<>".$f["id"];
    $q0.= " AND timezone>0 ORDER BY geocoding_date DESC LIMIT 1";
    $r0 = pg_query($readOnlyReplicaConn, $q0);
    if ($r0 && pg_num_rows($r0) && $f0=pg_fetch_assoc($r0) && $f0["country"]!="") {
        echo "[".date("Y-m-d H:i:s")."] Already geocoded: ".$f["id"]." => ".$f0["id"].".\n";
        unset($f0["id"]);
        list($res,$err) = updateAddress($f["id"], $f0);
        if ($err && $err!="") {
            echo "[".date("Y-m-d H:i:s")."] Failure: ${err} (".$f["id"].")\n";
        } else {
            echo "[".date("Y-m-d H:i:s")."] Complete: ".$f["id"]."\n";
        }
    } else {
        // Geocode
        list($res,$err) = geocodeAddress($f["address"]);
        if ($res && $err==NULL) {
            if (array_key_exists("lat",$res) && array_key_exists("lng",$res)) {
                $tz = $res["timeZoneId"];
                if ($tz=="" || !is_int($tz)) {
                    $q1 = "SELECT id FROM address_timezone WHERE lower(timezone)=lower('".pg_escape_string($tz)."')";
                    $r1 = pg_query($readOnlyReplicaConn, $q1);
                    if ($r1 && pg_num_rows($r1) && $f1=pg_fetch_row($r1)) {
                        $tz = $f1[0];
                    } else {
                        echo "[".date("Y-m-d H:i:s")."] Invalid timezone: '${tz}' (".$f["id"].")\n";
                        $tz = 1;
                    }
                } else {
                    echo "[".date("Y-m-d H:i:s")."] Timezone is OK: tz=${tz}\n";
                }
                echo "[".date("Y-m-d H:i:s")."] tz=${tz}\n";
                $data = [
                    "latitude" => $res["lat"],
                    "longitude" => $res["lng"],
                    /*"address" => $res["address"],*/
                    "postal" => $res["postal"],
                    "country" => $res["country"],
                    "timezone" => $tz,
                    "geocoding_date" => date("Y-m-d"),
                    "geometry" => "ST_SetSRID(ST_MakePoint(".$res["lng"].",".$res["lat"]."), 4326)"
                ];
                list($res,$err) = updateAddress($f["id"], $data);
                if ($err && $err!="") {
                    echo "[".date("Y-m-d H:i:s")."] Failure: ${err} (".$f["id"].")\n";
                } else {
                    echo "[".date("Y-m-d H:i:s")."] Complete: ".$f["id"]."\n";
                }
            } else {
                echo "[".date("Y-m-d H:i:s")."] Invalid result: ".json_encode($res)." (".$f["id"].")\n";
            }
        } else {
            echo "[".date("Y-m-d H:i:s")."] Failure: ${err} (".$f["id"].")\n";
        }
    }
}

function updateAddress($id, $data) {
    global $conn, $readOnlyReplicaConn;
    $columns = [];
    $q = "select column_name, data_type, character_maximum_length from INFORMATION_SCHEMA.COLUMNS where table_name = 'address'";
    $r = pg_query($readOnlyReplicaConn, $q);
    while ($f=pg_fetch_assoc($r)) {
        $columns[$f['column_name']] = $f['data_type'];
    }
    $q = "UPDATE address SET id=${id}";
    foreach ($data as $key=>$val) {
        if ($key!="timezone") continue;
        if (array_key_exists($key,$columns) && $columns[$key]!='character varying' && $columns[$key]!='date') {
            $q.= ", ${key} = ".($val==""?"NULL":$val);
        } else {
            $q.= ", ${key} = '".pg_escape_string($val)."'";
        }
    }
    $q.= " WHERE id=${id} RETURNING *";
    $r = pg_query($conn, $q);
    if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
        return [$f, NULL];
    }
    return [NULL, pg_last_error($conn)];
}

function geocodeAddress($address) {
    global $httpAuthToken;
    // Call geocoding service
    $data = http_build_query(
        array(
            'address' => $address,
        )
    );
    $url = "http://oauth2.service/api/v1/geocode?" . $data;
    $opts = array(
        'http' => array(
            'method' => "GET",
            'timeout' => 60, /* 1 minute */
            'header'  => 
                "Content-Type: application/x-www-form-urlencoded\r\n" .
                "Accept: application/json\r\n" .
                "Authorization: Server-Token ${httpAuthToken}\r\n",
        )
    );
    $context = stream_context_create($opts);
    $body = file_get_contents($url, false, $context);
    $geocoded = json_decode($body,true);
    if (is_array($geocoded) && is_array($geocoded["data"]) && !isset($geocoded["error"])) {
        // Cache the result in DB
        return array($geocoded["data"], NULL);
    } else if (is_array($geocoded) && isset($geocoded["error"])) {
        $body = $geocoded["error"];
    }
    return array(NULL, "Geocoding service call error: ".$body);
}

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