dev-chiefworks
124 lines
4.6 KiB
PHP
124 lines
4.6 KiB
PHP
<?php
|
|
|
|
echo "[".date("Y-m-d H:i:s")."] update_surge_price_data job is starting.\n";
|
|
|
|
require('./lock.php');
|
|
$lock_file = lock_pid_file();
|
|
|
|
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}";
|
|
$con = 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);
|
|
|
|
if ($con===FALSE || $readOnlyReplicaConn===FALSE) {
|
|
unlock_pid_file($lock_file);
|
|
die ("Could not connect to server\n");
|
|
}
|
|
$rate = 1.1;
|
|
$numberOfDays = 365; // This is handled by update cheapest data job now...
|
|
|
|
/*
|
|
// Init
|
|
INSERT INTO global_settings (key,description,value) VALUES ('last_surge_price_parsedemail_item','',0);
|
|
INSERT INTO global_settings (key,description,value) VALUES ('last_surge_price_quote','',0);
|
|
// Reset
|
|
TRUNCATE TABLE trip_surge_price;
|
|
UPDATE global_settings SET value='0' WHERE key='last_surge_price_parsedemail_item';
|
|
UPDATE global_settings SET value='0' WHERE key='last_surge_price_quote';
|
|
*/
|
|
|
|
// 1. Box the data set (box is previous data and max compared items)
|
|
$min_parsedemail_item_id = get_global_setting('last_surge_price_parsedemail_item',0);
|
|
$min_quote_id = get_global_setting('last_surge_price_quote',0);
|
|
$max_parsedemail_item_id = get_global_setting('last_compared_parsedemail_item',0);
|
|
$max_quote_id = get_global_setting('last_compared_quote',0);
|
|
|
|
if ($min_parsedemail_item_id>=$max_parsedemail_item_id && $min_quote_id>=$max_quote_id) {
|
|
echo "[".date("Y-m-d H:i:s")."] No new records to compare. Did you run the update_cheapest_data job?\n";
|
|
unlock_pid_file($lock_file);
|
|
exit();
|
|
}
|
|
|
|
// 1.1. Select all the trips bove average inside the boxed set
|
|
$q = "SELECT * FROM trip_price_comparison ";
|
|
$q.= " WHERE ";
|
|
$q.= " ((data_source=1 AND data_source_id>${min_parsedemail_item_id} AND data_source_id<=${max_parsedemail_item_id}) ";
|
|
$q.= " OR (data_source=2 AND data_source_id>${min_quote_id} AND data_source_id<=${max_quote_id})) ";
|
|
$q.= " AND cost>=${rate}*average";
|
|
|
|
$r = pg_query($readOnlyReplicaConn, $q);
|
|
$i = 1;
|
|
|
|
// 2. Iterate through the trips
|
|
if ($r && pg_num_rows($r)) {
|
|
$total = pg_num_rows($r);
|
|
echo "[".date("Y-m-d H:i:s")."] Processing ${total} new records\n";
|
|
while ($f = pg_fetch_assoc($r)) {
|
|
|
|
echo "[".date("Y-m-d H:i:s")."] Processing record ${i}/${total} (".sprintf("%0.02f",100.0*$i/$total)."%)\n";
|
|
|
|
// 3. Insert record into trip_surge_price table
|
|
$query = "
|
|
INSERT INTO trip_surge_price (data_source_id, data_source)
|
|
VALUES('".((int)$f["data_source_id"])."', '".((int)$f["data_source"])."') RETURNING id
|
|
";
|
|
$result = pg_query($con, $query);
|
|
if ($result && pg_num_rows($result) && $data=pg_fetch_row($result)) {
|
|
echo "[".date("Y-m-d H:i:s")."] New surge record: id=".$data[0]." (" . $f["data_source_id"] . "," . $f["data_source"] . ")\n";
|
|
} else {
|
|
echo "[".date("Y-m-d H:i:s")."] Failed to create new surge record: ".pg_last_error($con)."\n";
|
|
echo "[".date("Y-m-d H:i:s")."] ".trim($query)."\n";
|
|
}
|
|
$i++;
|
|
//if ($i>100) break; // DEBUG;
|
|
}
|
|
}
|
|
|
|
update_global_setting('last_surge_price_parsedemail_item',$max_parsedemail_item_id);
|
|
update_global_setting('last_surge_price_quote',$max_quote_id);
|
|
|
|
pg_close($con);
|
|
pg_close($readOnlyReplicaConn);
|
|
|
|
echo "[".date("Y-m-d H:i:s")."] update_surge_price_data job complete.\n";
|
|
|
|
/*********************************** Function ***********************************/
|
|
|
|
function get_global_setting($key,$val=0) {
|
|
global $readOnlyReplicaConn;
|
|
$q = "SELECT value FROM global_settings WHERE lower(key)=lower('".pg_escape_string($key)."')";
|
|
$r = pg_query($readOnlyReplicaConn,$q);
|
|
if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
|
|
return $f[0];
|
|
}
|
|
return $val;
|
|
}
|
|
|
|
function update_global_setting($key,$val) {
|
|
global $con;
|
|
$q = "UPDATE global_settings SET value='".pg_escape_string($val)."' WHERE lower(key)=lower('".pg_escape_string($key)."')";
|
|
$r = pg_query($con,$q);
|
|
if ($r) {
|
|
return pg_affected_rows($r);
|
|
}
|
|
return 0;
|
|
}
|
|
|
|
unlock_pid_file($lock_file);
|
|
|
|
?>
|