dev-chiefworks
108 lines
4.0 KiB
PHP
108 lines
4.0 KiB
PHP
<?php
|
|
|
|
echo "[".date("Y-m-d H:i:s")."] GPS member_tracking_duration job is starting.\n";
|
|
|
|
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;
|
|
// GPS coordinate precision to assume the same location
|
|
$precision = 3;
|
|
$id = 0;
|
|
|
|
$db_host = $savvyext->cfgReadChar('gpsdatabase.host');
|
|
$db_name = $savvyext->cfgReadChar('gpsdatabase.name');
|
|
$db_user = $savvyext->cfgReadChar('gpsdatabase.user');
|
|
$db_pass = $savvyext->cfgReadChar('gpsdatabase.pass');
|
|
$db_port = $savvyext->cfgReadLong('gpsdatabase.port');
|
|
$connstr = "host=${db_host} port=${db_port} dbname=${db_name} user=${db_user} password=${db_pass}";
|
|
$conn = pg_connect($connstr);
|
|
|
|
$q = 'select max(last_id) from members_tracking_duration';
|
|
$r = pg_query($q);
|
|
if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
|
|
//$id = (int)$f[0];
|
|
}
|
|
echo "[".date("Y-m-d H:i:s")."] Last processed ID is ${id}\n";
|
|
|
|
/*
|
|
0 - id
|
|
1 - lat
|
|
2 - lng
|
|
3 - ttime
|
|
4 - member_id
|
|
5 - device_id
|
|
6 - gps
|
|
*/
|
|
$q = "select id,round(lat,${precision}) as lat,round(lng,${precision}) as lng,ttime,member_id,device_id,gps ";
|
|
$q.= " from members_tracking where id>${id} ";
|
|
$q.= " and member_id=22"; // DEBUG!
|
|
$q.= " order by member_id,device_id,ttime limit ${hard_limit}";
|
|
echo "\n$q\n";
|
|
$r = pg_query($q);
|
|
|
|
if ($r && pg_num_rows($r)) {
|
|
echo "[".date("Y-m-d H:i:s")."] Records to process: ".pg_num_rows($r)."\n";
|
|
} else {
|
|
echo "[".date("Y-m-d H:i:s")."] No new records to process. Stop.\n";
|
|
pg_close($conn);
|
|
exit();
|
|
}
|
|
|
|
echo "[".date("Y-m-d H:i:s")."] Processing";
|
|
$first_data = NULL;
|
|
$last_data = NULL;
|
|
$pool_data = [];
|
|
while ($f=pg_fetch_row($r)) {
|
|
echo ".";
|
|
if ($last_data!=NULL) {
|
|
// member id
|
|
if ($last_data[4]!=$f[4] || $last_data[5]!=$f[5] || // device_id
|
|
$last_data[1]!=$f[1] || $last_data[2]!=$f[2]) { // gps coordinates
|
|
// Process
|
|
$q = "INSERT INTO members_tracking_duration (member_id,lat,lng,gps,first_id,last_id,first_time,last_time,duration,device_id) VALUES(";
|
|
$q.= $last_data[4].",".$last_data[1].",".$last_data[2].","; // member_id,lat,lng
|
|
$q.= "ST_SetSRID(ST_MakePoint(".$last_data[1].",".$last_data[2]."), 4326)::geography"; // GPS
|
|
$q.= ",".$first_data[0].",".$last_data[0].",'".$first_data[3]."','".$last_data[3]."',"; // fist_id,last_id,first_time,last_time,duration
|
|
$q.= "'".$last_data[3]."'::timestamp - '".$first_data[3]."'::timestamp,".($f[5]>0?$f[5]:"NULL").")"; // duration, device_id
|
|
pg_query($q);
|
|
//echo pg_last_error()."\n";
|
|
$clear_data = [];
|
|
foreach ($pool_data as $id) {
|
|
if ($id!=$first_data[0] && $id!=$last_data[0]) {
|
|
$clear_data[] = $id;
|
|
}
|
|
}
|
|
if (count($clear_data)>0) {
|
|
$q1 = "DELETE FROM members_tracking WHERE id IN (".implode(",",$clear_data).")";
|
|
$r1 = pg_query($q1);
|
|
if ($r1 && pg_affected_rows($r1)) {
|
|
$q2 = "UPDATE members_tracking SET previous_id=".$first_data[0].",";
|
|
$q2.= "distance=ST_Distance(gps,'".$first_data[6]."'::geometry),";
|
|
$q2.= "duration=EXTRACT(epoch FROM ttime-'".$first_data[3]."'::timestamp)";
|
|
$q2.= " WHERE previous_id IN (".implode(",",$clear_data).",".$last_data[0].")";
|
|
$r2 = pg_query($q2);
|
|
echo "\n${q2}\n";
|
|
//echo pg_last_error()."\n";
|
|
} else {
|
|
echo "\n";
|
|
echo "[".date("Y-m-d H:i:s")."] Failed - ".pg_last_error()."\n";
|
|
echo "${q1}\n";
|
|
}
|
|
}
|
|
$first_data = NULL;
|
|
}
|
|
}
|
|
if ($first_data==NULL) {
|
|
$first_data = $f;
|
|
$pool_data = [];
|
|
}
|
|
$pool_data[] = $f[0]; // id
|
|
$last_data = $f;
|
|
}
|
|
echo "\n";
|
|
|
|
pg_close($conn);
|
|
echo "[".date("Y-m-d H:i:s")."] GPS member_tracking_duration job complete.\n";
|