dev-chiefworks
511 lines
21 KiB
PHP
511 lines
21 KiB
PHP
<?php
|
|
|
|
echo "[" . date("Y-m-d H:i:s") . "] member_prepare_notification 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;
|
|
|
|
$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);
|
|
include 'notifications_functions.php';
|
|
|
|
/*
|
|
LEFT us refesh the triggertable
|
|
*/
|
|
|
|
foreach ($trigger_query AS $key => $lrow) {
|
|
$system_id = $key;
|
|
$mysql = $lrow["query"];
|
|
$message = $lrow["message"];
|
|
$active = $lrow["active"];
|
|
$category = $lrow["cat"];
|
|
$dynamic_key = $lrow["dynamic_key"];
|
|
|
|
$send_mail = $lrow["send_mail"];
|
|
$send_notification = $lrow["send_notification"];
|
|
$icon = $lrow["icon"];
|
|
|
|
|
|
|
|
$sq2 = "SELECT * FROM email_trigger WHERE e_trigger='$system_id'";
|
|
$r2 = pg_query($readOnlyReplicaConn, $sq2);
|
|
if ($r2 && pg_num_rows($r2) == 0) {
|
|
|
|
$template_name = trim(strtolower($system_id . ".html"));
|
|
$values = [
|
|
"e_trigger" => "" . $system_id . "",
|
|
"category" => "" . $category . "",
|
|
"message" => pg_escape_string($message),
|
|
"action_detail" => pg_escape_string($message),
|
|
"template_name" => $template_name,
|
|
"dynamic_key" => $dynamic_key,
|
|
"status" => $active,
|
|
"override_text" => "1",
|
|
"expiration" => "EXP00000",
|
|
"icon" => $icon,
|
|
];
|
|
|
|
$values = array_filter($values, 'strlen');
|
|
$q = "INSERT INTO email_trigger ";
|
|
$q .= " (" . implode(", ", array_keys($values)) . ")";
|
|
$q .= " VALUES ('" . implode("', '", $values) . "') ";
|
|
// echo $q;
|
|
$rs = pg_query($conn, $q);
|
|
} else {
|
|
$template_name = trim(strtolower($system_id . ".html"));
|
|
/* $values = [
|
|
"category" => "" . $category . "",
|
|
"message" => pg_escape_string($message),
|
|
"template_name" => $template_name,
|
|
"dynamic_key" => $dynamic_key,
|
|
"send_mail" => $send_mail,
|
|
"send_notification" => $send_notification,
|
|
"status" => $active
|
|
];
|
|
*/
|
|
$values = [
|
|
"message" => pg_escape_string($message),
|
|
"template_name" => $template_name,
|
|
"dynamic_key" => $dynamic_key
|
|
];
|
|
|
|
//echo "jcjcjcjcjjcc";
|
|
$msqUpdt = "";
|
|
$ick = 0;
|
|
foreach ($values as $key => $val) {
|
|
if ($ick > 0) {
|
|
$msqUpdt .= ",";
|
|
}
|
|
$msqUpdt .= " $key = '" . $val . "'";
|
|
$ick++;
|
|
}
|
|
|
|
if ($ick > 0) {
|
|
|
|
$q = "UPDATE email_trigger SET " . $msqUpdt;
|
|
$q .= " WHERE e_trigger ='$system_id' ";
|
|
|
|
// do not update all
|
|
// echo $q;
|
|
// $rs = pg_query($conn, $q);
|
|
}
|
|
}
|
|
}
|
|
|
|
//---------------------ATTEND TO THE CAR POOL NOTIFIFICATION ETRG0020 ----------------------------------------
|
|
/* savvy=> SELECT * FROM members_trips WHERE member_id =1 LIMIT 1;
|
|
id | member_id | trip_name | trip_from | trip_to | country | trip_date | status | added | color
|
|
----+-----------+------------+---------------------------------------------+-------------------------------------+---------+---------------------+--------+----------------------------+--------
|
|
1 | 1 | Trip 62236 | 4201 Defoors Fram Trail, Powder Springs, GA | 3500 Riverwood parkway, Atlanta, GA | US | 2019-12-12 11:30:00 | 1 | 2019-12-16 00:10:33.354752 | 6464e2
|
|
(1 row)
|
|
*/
|
|
|
|
$mysql_trip = "SELECT id AS trip_id, * FROM members_trips WHERE member_id > 0 AND now()+'15 minutes' < trip_date AND trip_date > now()+ '-30 minutes' AND notify = 0 LIMIT 10";
|
|
|
|
$r = pg_query($conn, $mysql_trip);
|
|
|
|
$acc = array();
|
|
if ($r && pg_num_rows($r)) {
|
|
|
|
while ($f = pg_fetch_assoc($r)) {
|
|
$member_id = $f["member_id"];
|
|
$trip_id = $f["trip_id"];
|
|
|
|
$trip_from = $f["trip_from"];
|
|
$trip_to = $f["trip_to"];
|
|
$trip_date = $f["trip_date"];
|
|
$trip_name = $f["trip_name"];
|
|
|
|
$sql_up = "UPDATE members_trips SET notify =notify + 1 WHERE id = $trip_id AND member_id = $member_id ";
|
|
pg_query($conn, $sql_up);
|
|
|
|
$extension_call = true;
|
|
if ($extension_call == true) {
|
|
$outX = array();
|
|
$x = array();
|
|
//$member_id = 1;
|
|
$x["pid"] = 0;
|
|
$x["member_id"] = $member_id;
|
|
$x["notice_type"] = "INSTANT";
|
|
$x["mmode"] = "AUTO";
|
|
$x["trigger_key"] = "ETRG0020";
|
|
$x["msg"] = "You have an upcoming trip $trip_name today at $trip_date to $trip_to";
|
|
$x["action"] = FLOAT_SYSTEM_CREATE_NOTIFICATION;
|
|
//print_r($x);
|
|
Fextension_call($x, $outX);
|
|
}
|
|
}
|
|
}
|
|
|
|
// ------------------------------------------------------------------- ----------------------------------------
|
|
/*
|
|
*
|
|
* "ETRG0007" => array(
|
|
"query" => "SELECT ma.*,m.min_budget,(ma.spend_14 - 2*ma.spend_7) AS week_increase from members_analysis ma LEFT JOIN members m ON m.id = ma.member_id WHERE m.decision_group IN('A1NUS25','USSF001') AND m.id NOT IN (SELECT member_id FROM members_carpool) ORDER BY m.notification_test ASC LIMIT 500",
|
|
"message" => "Deal Carpool Try carpooling and save $X and Y emissions this month",
|
|
* "html_body" => pg_escape_string($htm_template),
|
|
|
|
savvy=> select * from email_trigger;
|
|
id | e_trigger | category | action_detail | template_name | status | added | updated
|
|
----+-----------+------------+------------------------------------------+-----------------+--------+----------------------------+----------------------------
|
|
2 | ETRG0002 | GOACTIVITY | Put Details Here | dealtmp022.html | 1 | 2020-02-18 15:06:46.721542 | 2020-02-18 15:06:46.721542
|
|
3 | ETRG0003 | GOACTIVITY | Put Details Here | dealtmp033.html | 1 | 2020-02-18 15:06:46.725502 | 2020-02-18 15:06:46.725502
|
|
4 | ETRG0004 | GOACTIVITY | Put Details Here | dealtmp044.html | 1 | 2020-02-18 15:06:46.729315 | 2020-02-18 15:06:46.729315
|
|
5 | ETRG0005 | GOACTIVITY | Put Details Here | dealtmp055.html | 1 | 2020-02-18 15:06:46.733011 | 2020-02-18 15:06:46.733011
|
|
1 | ETRG0001 | GOACTIVITY | <p><strong>Put Details Here</strong></p>+| dealtmp011.html | 1 | 2020-02-18 15:06:46.716103 | 2020-02-18 15:06:46.716103
|
|
| | | | | | |
|
|
(5 rows)
|
|
|
|
|
|
$trigger_query = array(
|
|
"SURGESAVE" => array(
|
|
"query" => $mysql,
|
|
"message" => "You are spending too much. Let's save money",
|
|
"active" => 1,
|
|
"cat" => 'GOACTIVITY',
|
|
"dynamic_key" => "",
|
|
"start_save" => false
|
|
),
|
|
*/
|
|
|
|
function Fextension_call($in, &$out) {
|
|
// logToFl("Merchant_Name count->" . $in["merchant_name"]);
|
|
global $savvyext;
|
|
$out = $savvyext->savvyext_api($in);
|
|
$ret = $out["retval"];
|
|
return $ret;
|
|
}
|
|
|
|
// clear out the expired list first
|
|
$sq2 = "UPDATE members_notification SET status =2 WHERE status = 1 AND expire < now() AND notice_type!='INSTANT'";
|
|
$r2 = pg_query($conn, $sq2);
|
|
|
|
|
|
|
|
foreach ($trigger_query AS $key => $lrow) {
|
|
$system_id = $key;
|
|
$mysql = $lrow["query"];
|
|
$message = $lrow["message"];
|
|
$active = $lrow["active"];
|
|
$extension_call = $lrow["start_save"];
|
|
|
|
if (1 == $active && $system_id != '') {
|
|
// echo $system_id. " - <hr> - ".$mysql;
|
|
$mysql_up = "UPDATE email_trigger SET last_run = now() WHERE e_trigger = '$system_id'";
|
|
pg_query($conn, $mysql_up);
|
|
|
|
$r = pg_query($conn, $mysql);
|
|
|
|
$acc = array();
|
|
if ($r && pg_num_rows($r)) {
|
|
// echo $system_id. " - <hr> - ".$mysql;
|
|
// echo 'ddhdhdhd';
|
|
while ($f = pg_fetch_assoc($r)) {
|
|
$member_id = $f["member_id"];
|
|
|
|
$extension_call = false; // does not matter - dontset to send by =default.
|
|
if (array_key_exists($member_id, $acc)) {
|
|
// echo "here " . $member_id;
|
|
// nothing - we dont want duplicate
|
|
} else {
|
|
|
|
$sq2 = "SELECT * FROM members_notification WHERE member_id=$member_id AND trigger_key ='$system_id' AND status IN (1)";
|
|
$r2 = pg_query($readOnlyReplicaConn, $sq2);
|
|
$x["pid"] = 0;
|
|
$x["member_id"] = $member_id;
|
|
$x["notice_type"] = "GENALERT";
|
|
$acc[$member_id] = $member_id;
|
|
if ($r2 && pg_num_rows($r2) == 0) {
|
|
$x["msg"] = $message;
|
|
$extension_call = true;
|
|
// modify the message here
|
|
|
|
|
|
if ($system_id == "SURGESAVE") {
|
|
$extension_call = false; // turn message off
|
|
// print_r($x);
|
|
if ($f["surge_price"] > 0 && $f["cheaper_alternative"] > 0) {
|
|
$x["msg"] = "We have found a way to save time and money for you";
|
|
}
|
|
|
|
if ($f["surge_price"] > 0 && $f["cheaper_alternative"] == 0) {
|
|
$x["msg"] = "We have found a way to save time for you";
|
|
}
|
|
$extension_call = true;
|
|
}
|
|
|
|
if ($system_id == "ETRG0002") {
|
|
$percent_less = 100 - ($f["spend_7"] / $f["min_budget"]) * 100;
|
|
$x["msg"] = "Good Job, you're " . round($percent_less, 2) . "% below your budget. Let's save more.";
|
|
}
|
|
|
|
if ($system_id == "ETRG0004") {
|
|
$rideshare_30 = $f["rideshare_30"] * 0.01;
|
|
$extension_call = false; // turn message off
|
|
if ($rideshare_30 > 0) {
|
|
$x["msg"] = "We can see you spent $ $rideshare_30 on rideshare this month. Here's how Float can save you money";
|
|
$extension_call = true; // turn message off
|
|
}
|
|
}
|
|
|
|
if ($system_id == "ETRG0005") {
|
|
$extension_call = false;
|
|
$x["msg"] = getAccountBenefit($readOnlyReplicaConn, $member_id, $extension_confirm_call);
|
|
if ($x["msg"] != '' && $extension_confirm_call == true) {
|
|
$extension_call = $extension_confirm_call;
|
|
}
|
|
}
|
|
|
|
|
|
if ($system_id == "ETRG0006") {
|
|
$percent_increase = round($f["week_increase"] / $f["spend_7"]) * 100;
|
|
$extension_call = false;
|
|
if ($percent_increase > 0) {
|
|
$x["msg"] = "Your spending is up $percent_increase% this week. Would you like to create a budget?";
|
|
$extension_call = true; // turn message off
|
|
}
|
|
}
|
|
|
|
if ($system_id == "ETRG0007") {
|
|
//$percent_increase = round($f["week_increase"] / $f["spend_7"], 2) * 100;
|
|
$extension_call = false; // turn message off
|
|
$emmission_save = rand(100, 200);
|
|
$emmission_value = rand(100, 200);
|
|
$country = $f["country"];
|
|
|
|
$week_increase = $f["week_increase"] * 0.01;
|
|
if ($week_increase > 0) {
|
|
|
|
$emmission_save = round($week_increase, 2);
|
|
$emisson = getEmissionModel($week_increase, 1, $country);
|
|
$emmission_value = round($emisson[0], 2);
|
|
|
|
$x["msg"] = "Try carpooling and save $$emmission_save and $emmission_value grams of emissions this month";
|
|
$extension_call = true;
|
|
}
|
|
|
|
//"Your spending is up $percent_increase% this week. Let's create a budget together";
|
|
}
|
|
|
|
if ($system_id == "ETRG0014") {
|
|
$extension_call = false; // turn message off
|
|
/*
|
|
id | member_id | updated | spend_7 | rideshare_30 | spend_14 | gas_7 | gas_30 | gas_14 | min_budget | week_increase
|
|
-----+-----------+----------------------------+---------+--------------+----------+-------+--------+--------+------------+---------------
|
|
191 | 202 | 2020-05-06 11:20:04.339684 | 92989 | 75433 | 162604 | 42949 | 174047 | 77361 | 27500 | 23374
|
|
117 | 76 | 2020-05-06 11:05:05.830233 | 1995 | 0 | 1995 | 0 | 0 | 0 | 0 | 1995
|
|
*/
|
|
$week_increase = $f["week_increase"] * 0.01;
|
|
|
|
if ($week_increase >= 20) {
|
|
$emmission_inc = emissonIncrease($member_id, 7);
|
|
if ($emmission_inc > 0) {
|
|
$x["msg"] = "Your emissions output is $emmission_inc% higher this week, lets see how we can lower it";
|
|
$extension_call = true;
|
|
}
|
|
}
|
|
}
|
|
|
|
if ($system_id == "ETRG0015") {
|
|
$extension_call = false; // turn message off
|
|
/*
|
|
id | member_id | updated | spend_7 | rideshare_30 | spend_14 | gas_7 | gas_30 | gas_14 | min_budget | week_decrease
|
|
-----+-----------+----------------------------+---------+--------------+----------+-------+--------+--------+------------+---------------
|
|
194 | 186 | 2020-04-28 17:45:03.507661 | 172933 | 116230 | 315990 | 66650 | 215045 | 128172 | 0 | 29876
|
|
*/
|
|
$week_decrease = $f["week_decrease"] * 0.01;
|
|
|
|
if ($week_decrease >= 20) {
|
|
$less_minutes = getTripMinutes($member_id, 7);
|
|
if ($less_minutes > 0) {
|
|
$x["msg"] = "Hurray, you have spent $less_minutes minutes less on the road this week.";
|
|
$extension_call = true;
|
|
}
|
|
}
|
|
}
|
|
|
|
if ($system_id == "ETRG0019") {
|
|
$extension_call = false; // this is managed by another automation
|
|
}
|
|
}
|
|
}
|
|
|
|
pg_query($conn, "UPDATE members SET notification_test = now()+'1 day' WHERE id = $member_id");
|
|
|
|
if ($extension_call == true) {
|
|
$outX = array();
|
|
$x["mmode"] = "AUTO";
|
|
$x["trigger_key"] = $system_id;
|
|
$x["action"] = FLOAT_SYSTEM_CREATE_NOTIFICATION;
|
|
print_r($x);
|
|
Fextension_call($x, $outX);
|
|
}
|
|
// print_r($acc);
|
|
}
|
|
}
|
|
} // end if active
|
|
}
|
|
exit();
|
|
|
|
function emissonIncrease($member_id, $days_gap) {
|
|
|
|
$emmission_inc = rand(0, 28);
|
|
return $emmission_inc;
|
|
}
|
|
|
|
function getTripMinutes($member_id, $days_gap) {
|
|
|
|
$less_minutes = rand(12, 99);
|
|
return $less_minutes;
|
|
}
|
|
|
|
function getEmissionModel($gas_amount, $gas_number, $gas_country = 'US') {
|
|
|
|
// GAS MODEL ASSUMPTIONS
|
|
|
|
$average_car_CO2_emissions = 168;
|
|
$average_distance_travelled_pre_litre_of_petrol_km = 13;
|
|
$average_price_per_litre_USD = 1.07;
|
|
if ('SG' == $gas_country) {
|
|
$average_price_per_litre_USD = 1.39;
|
|
}
|
|
|
|
|
|
$estimated_litres_of_fuel_purchased_litres = $gas_amount / $average_price_per_litre_USD;
|
|
$estimated_distance_travelled_per_re_fuel_km = $estimated_litres_of_fuel_purchased_litres * $average_distance_travelled_pre_litre_of_petrol_km;
|
|
|
|
$estimated_total_CO2_emissions_per_refuel = $estimated_distance_travelled_per_re_fuel_km * $average_car_CO2_emissions;
|
|
$estimated_CO2_emissions_per_month = $estimated_total_CO2_emissions_per_refuel * $gas_number;
|
|
$estimated_CO2_emissions_per_week = $estimated_CO2_emissions_per_month * 12 / 52;
|
|
|
|
|
|
$data = [];
|
|
|
|
$data[0] = $estimated_total_CO2_emissions_per_refuel;
|
|
$data[1] = $estimated_CO2_emissions_per_month;
|
|
$data[2] = $estimated_CO2_emissions_per_week;
|
|
|
|
return $data;
|
|
}
|
|
|
|
function getAccountBenefit($conn, $member_id, &$extension_confirm_call) {
|
|
$msg_text = "";
|
|
$extension_confirm_call = false;
|
|
// $msql = "SELECT b.id AS bank_id,ccb.benefit from members_bank_accounts mb LEFT JOIN banks b ON b.bank_name = mb.bank_name LEFT JOIN credit_card_benefits ccb ON ccb.credit_card_id = b.id WHERE mb.member_id = $member_id AND ccb.benefit IS NOT NULL LIMIT 1";
|
|
$myqsl = "SELECT b.id AS bank_id,ccb.benefit from members_bank_accounts mb "
|
|
. " LEFT JOIN credit_cards b ON b.card_name= mb.description "
|
|
. " LEFT JOIN credit_card_benefits ccb ON ccb.credit_card_id = b.id "
|
|
. " WHERE mb.member_id = $member_id AND ccb.benefit IS NOT NULL "
|
|
. " ORDER BY random() LIMIT 1";
|
|
$r2 = pg_query($conn, $myqsl);
|
|
if ($r2 && pg_num_rows($r2) > 0) {
|
|
|
|
while ($row = pg_fetch_row($r2)) {
|
|
$msg_text = $row[1];
|
|
// echo 'HERE 22222->' . $msg_text;
|
|
$extension_confirm_call = true;
|
|
}
|
|
}
|
|
return $msg_text;
|
|
}
|
|
|
|
$r = pg_query($conn, $mysql);
|
|
$acc = array();
|
|
if ($r && pg_num_rows($r)) {
|
|
// echo 'ddhdhdhd';
|
|
while ($f = pg_fetch_assoc($r)) {
|
|
$member_id = $f["member_id"];
|
|
|
|
$extension_call = false;
|
|
if (array_key_exists($member_id, $acc)) {
|
|
// echo "here " . $member_id;
|
|
// nothing - we dont want duplicate
|
|
} else {
|
|
|
|
$sq2 = "SELECT * FROM members_notification WHERE member_id=$member_id AND status IN (1)";
|
|
$r2 = pg_query($readOnlyReplicaConn, $sq2);
|
|
$x["pid"] = 0;
|
|
$x["member_id"] = $member_id;
|
|
$x["notice_type"] = "GENALERT";
|
|
$acc[$member_id] = $member_id;
|
|
if ($r2 && pg_num_rows($r2) == 0) {
|
|
$x["msg"] = "We have found a way to save time for you";
|
|
|
|
|
|
// print_r($x);
|
|
if ($f["surge_price"] > 0 && $f["cheaper_alternative"] > 0) {
|
|
$x["msg"] = "We have found a way to save time and money for you";
|
|
}
|
|
|
|
if ($f["surge_price"] > 0 && $f["cheaper_alternative"] == 0) {
|
|
$x["msg"] = "We have found a way to save time for you";
|
|
}
|
|
|
|
$extension_call = true;
|
|
}
|
|
}
|
|
|
|
|
|
if ($extension_call == true) {
|
|
$outX = array();
|
|
$x["mmode"] = "AUTO";
|
|
$x["action"] = FLOAT_SYSTEM_CREATE_NOTIFICATION;
|
|
// Fextension_call($x, $outX);
|
|
}
|
|
print_r($acc);
|
|
}
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
/*
|
|
$q = "SELECT * FROM members_notification WHERE status = 1 LIMIT 5";
|
|
$r = pg_query($conn, $q);
|
|
if ($r && pg_num_rows($r)) {
|
|
while ($f = pg_fetch_row($r)) {
|
|
$member_id = $f[0];
|
|
echo "[" . date("Y-m-d H:i:s") . "] Processing member_id=" . $member_id . " (total member records: " . $f[1] . ")\n";
|
|
//if ($f[0]==22) continue; // Crash!
|
|
// $p = processMemberGPSDataPartition($member_id);
|
|
echo "[" . date("Y-m-d H:i:s") . "] Processed member_id=" . $member_id . " (processed records: " . $p . ")\n";
|
|
}
|
|
}
|
|
|
|
function processMemberGPSDataPartition($member_id) {
|
|
global $conn, $precision, $hard_limit;
|
|
|
|
}
|
|
*/
|
|
|
|
pg_close($conn);
|
|
pg_close($readOnlyReplicaConn);
|
|
echo "[" . date("Y-m-d H:i:s") . "] member_prepare_notification job complete.\n";
|
|
|
|
|
|
|