dev-chiefworks
50 lines
2.2 KiB
PHP
50 lines
2.2 KiB
PHP
<?php
|
|
require '../backend.php';
|
|
|
|
// We will not limit the SQL we will exhaust the allowed memory
|
|
// we will process data in ${hard_limit} batches and hopefully catch up
|
|
$hard_limit = 500000;
|
|
|
|
//PostgreSQL
|
|
$gps_db_host = $savvyext->cfgReadChar('gpsdatabase.host');
|
|
$gps_db_port = $savvyext->cfgReadLong('gpsdatabase.port');
|
|
$gps_db_name = $savvyext->cfgReadChar('gpsdatabase.name');
|
|
$gps_db_user = $savvyext->cfgReadChar('gpsdatabase.user');
|
|
$gps_db_pass = $savvyext->cfgReadChar('gpsdatabase.pass');
|
|
$conn_string_gps = "host=${gps_db_host} port=${gps_db_port} dbname=${gps_db_name} user=${gps_db_user} password=${gps_db_pass}";
|
|
$pgconn_gps = pg_connect($conn_string_gps);
|
|
|
|
$q = "select * from members_tracking where duration<0";
|
|
$r = pg_query($pgconn_gps, $q);
|
|
if ($r && pg_num_rows($r)) {
|
|
while ($f=pg_fetch_assoc($r)) {
|
|
echo "[".date("Y-m-d H:i:s")."] Processing member_id=".$f["member_id"]."...\n";
|
|
$nid = $f["id"];
|
|
$gps = $f["gps"];
|
|
$ttime = $f["ttime"];
|
|
$device_id = $f["device_id"];
|
|
$member_id = $f["member_id"];
|
|
//if ($f[0]==22) continue; // Crash!
|
|
$q1 = "SELECT id AS pid, ST_Distance(gps,'${gps}'::geometry) AS distance,";
|
|
$q1.= "EXTRACT(epoch FROM '${ttime}'::timestamp-ttime) AS duration ";
|
|
$q1.= "FROM members_tracking ";
|
|
$q1.= "WHERE member_id=${member_id} AND ttime<'${ttime}'::timestamp AND ";
|
|
$q1.= ($device_id>0 ? "device_id=${device_id}" : "device_id IS NULL");
|
|
$q1.= " ORDER BY ttime DESC LIMIT 1";
|
|
$r1 = pg_query($pgconn_gps, $q1);
|
|
if ($r1 && pg_num_rows($r1) && $f1=pg_fetch_assoc($r1)) {
|
|
$q2 = "UPDATE members_tracking SET previous_id=".$f1["pid"].",distance=".((int)$f1["distance"]).",duration=".((int)(1000*$f1["duration"]));
|
|
$q2.= " WHERE id=${nid}";
|
|
$r2 = pg_query($pgconn_gps, $q2);
|
|
} else {
|
|
echo "[".date("Y-m-d H:i:s")."] Failure - ".pg_last_error($pgconn_gps)."\n";
|
|
//echo "$q1\n";
|
|
$q2 = "UPDATE members_tracking SET previous_id=NULL,distance=NULL,duration=NULL WHERE id=${nid}";
|
|
$r2 = pg_query($pgconn_gps, $q2);
|
|
}
|
|
}
|
|
}
|
|
|
|
pg_close($pgconn_gps);
|
|
|