dev-chiefworks
124 lines
4.9 KiB
PHP
124 lines
4.9 KiB
PHP
<?php
|
|
|
|
echo "[".date("Y-m-d H:i:s")."] update_holiday_data 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}";
|
|
$con = pg_connect($connstr) or die ("Could not connect to server\n");
|
|
|
|
$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);
|
|
|
|
/*
|
|
// Init
|
|
INSERT INTO global_settings (key,description,value) VALUES ('last_holiday_parsedemail_item','',0);
|
|
INSERT INTO global_settings (key,description,value) VALUES ('last_holiday_quote','',0);
|
|
// Reset
|
|
TRUNCATE TABLE trip_holidays;
|
|
UPDATE global_settings SET value='0' WHERE key='last_holiday_parsedemail_item';
|
|
UPDATE global_settings SET value='0' WHERE key='last_holiday_quote';
|
|
*/
|
|
|
|
// 1. Box the data set (select min id from trip_holidays and select max id on parsedemail_item and quotes)
|
|
$min_parsedemail_item_id = get_global_setting('last_holiday_parsedemail_item',0);
|
|
$min_quote_id = get_global_setting('last_holiday_quote',0);
|
|
$max_parsedemail_item_id = get_max_id('parsedemail_item');
|
|
$max_quote_id = get_max_id('quotes');
|
|
|
|
// 1.1. Select all the trips order by date from the oldest to the newest inside the boxed set
|
|
$q = "SELECT a.data_source_id AS data_source_id,
|
|
a.data_source AS data_source,
|
|
a.cost AS normal_cost,
|
|
h.id AS holiday_id";
|
|
$q.= " FROM union_trip_and_quote_view_table a ";
|
|
$q.= " LEFT JOIN address a1 ON a1.id = a.location_start_id ";
|
|
$q.= " LEFT JOIN address a2 ON a2.id = a.location_end_id ";
|
|
$q.= " LEFT JOIN holidays h ON (h.date=travel_date::DATE AND h.country=a1.country) ";
|
|
$q.= " WHERE ((a.data_source=1 AND a.data_source_id>${min_parsedemail_item_id} AND a.data_source_id<=${max_parsedemail_item_id})";
|
|
$q.= " OR (a.data_source=2 AND a.data_source_id>${min_quote_id} AND a.data_source_id<=${max_quote_id}))";
|
|
$q.= " AND a.cost>0 AND h.id IS NOT NULL ORDER BY a.travel_date ASC";
|
|
|
|
$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_holidays (data_source_id, data_source, holiday_id, normal_cost)
|
|
VALUES( '".((int)$f["data_source_id"])."', '".((int)$f["data_source"])."',
|
|
'".((int)$f["holiday_id"])."', NULL) 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 holiday 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 holidays 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_holiday_parsedemail_item',$max_parsedemail_item_id);
|
|
update_global_setting('last_holiday_quote',$max_quote_id);
|
|
|
|
pg_close($con);
|
|
pg_close($readOnlyReplicaConn);
|
|
|
|
echo "[".date("Y-m-d H:i:s")."] update_holiday_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;
|
|
}
|
|
|
|
function get_max_id($what) {
|
|
global $readOnlyReplicaConn;
|
|
$q = "SELECT max(id) FROM ${what}";
|
|
$r = pg_query($readOnlyReplicaConn,$q);
|
|
if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
|
|
return $f[0];
|
|
}
|
|
return 0;
|
|
}
|
|
?>
|