dev-chiefworks
136 lines
5.1 KiB
PHP
136 lines
5.1 KiB
PHP
<?php
|
|
|
|
echo "[" . date("Y-m-d H:i:s") . "] member_lifecyle_emails job is starting.\n";
|
|
|
|
require('../backend.php');
|
|
|
|
define('EMAIL_PENDING', 1);
|
|
define('EMAIL_SENT', 5);
|
|
|
|
// 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;
|
|
|
|
echo $email_templates_dir = $savvyext->cfgReadChar('mailsend.templates_dir');
|
|
$template_name = "How_it_works.html"; // This is test template.
|
|
|
|
$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);
|
|
|
|
// send peding emsils
|
|
$notice_type = 'EMAILALERT';
|
|
//savvy=> SELECT l.id AS life_id,l.* FROM members_lifecycle_emails l LEFT JOIN members m ON m.id=l.member_id WHERE l.status = 1 ;
|
|
// life_id | id | member_id | cycle_type | subject | status | added | completed
|
|
|
|
$mysql = "SELECT l.id AS life_id,l.*,m.email AS reciever_email FROM members_lifecycle_emails l LEFT JOIN members m ON m.id=l.member_id WHERE l.status = 1";
|
|
$r0 = pg_query($readOnlyReplicaConn, $mysql);
|
|
|
|
if ($r0 && pg_num_rows($r0)) {
|
|
|
|
while ($f0 = pg_fetch_assoc($r0)) {
|
|
$member_id = $f0["member_id"];
|
|
$life_id = $f0["life_id"];
|
|
$reciever_email = $f0["reciever_email"];
|
|
$subject = $f0["subject"];
|
|
|
|
$qup = "UPDATE members_lifecycle_emails SET status = 4 WHERE id=$life_id";
|
|
pg_query($conn, $qup);
|
|
|
|
|
|
if ($notice_type == 'EMAILALERT') {
|
|
|
|
$htm_template = file_get_contents($email_templates_dir . "/lifecycle/" . $template_name); // note read evertime in case we need to change some parameters
|
|
|
|
if ($reciever_email != '' && $htm_template) {
|
|
$values = [
|
|
"to_emails" => "{" . $reciever_email . "}",
|
|
"member_id" => "" . $member_id . "",
|
|
"subject" => $subject,
|
|
"html_body" => pg_escape_string($htm_template),
|
|
"status" => "0",
|
|
"created_at" => (new DateTime())->format('c'),
|
|
"updated_at" => (new DateTime())->format('c')
|
|
];
|
|
|
|
$values = array_filter($values, 'strlen');
|
|
$q = "INSERT INTO emails ";
|
|
$q .= " (" . implode(", ", array_keys($values)) . ")";
|
|
$q .= " VALUES ('" . implode("', '", $values) . "') ";
|
|
$rs = pg_query($conn, $q);
|
|
if ($rs) {
|
|
$q = "UPDATE members_lifecycle_emails SET status = " . EMAIL_SENT . ", completed=now() WHERE id = " . $life_id;
|
|
$rs = pg_query($conn, $q);
|
|
}
|
|
}
|
|
} // if EMAIl ALERT IS NEEDED
|
|
}
|
|
}
|
|
|
|
|
|
|
|
|
|
//genedate new list of emails
|
|
|
|
$mysql = "SELECT *, id AS member_id FROM members WHERE id > 0 AND now() BETWEEN added+'1 minute' AND added+'2 day' AND id NOT IN (SELECT member_id FROM members_lifecycle_emails WHERE cycle_type ='HOWITWORKS' )";
|
|
|
|
// TEST echo
|
|
//$mysql = "SELECT *, id AS member_id FROM members WHERE id < 5 AND id> 0 AND id NOT IN (SELECT member_id FROM members_lifecycle_emails WHERE cycle_type ='HOWITWORKS' ) ORDER BY id ASC";
|
|
$r = pg_query($readOnlyReplicaConn, $mysql);
|
|
|
|
if ($r && pg_num_rows($r)) {
|
|
|
|
while ($f = pg_fetch_assoc($r)) {
|
|
$member_id = $f["member_id"];
|
|
|
|
$values = [
|
|
"member_id" => "" . $member_id . "",
|
|
"subject" => "Float - How it Works",
|
|
"cycle_type" => "HOWITWORKS"
|
|
];
|
|
|
|
$values = array_filter($values, 'strlen');
|
|
$q = "INSERT INTO members_lifecycle_emails ";
|
|
$q .= " (" . implode(", ", array_keys($values)) . ")";
|
|
echo $q .= " VALUES ('" . implode("', '", $values) . "') ";
|
|
|
|
|
|
$rs = pg_query($conn, $q);
|
|
}
|
|
}
|
|
|
|
/*
|
|
*
|
|
*
|
|
* CREATE TABLE members_lifecycle_emails (
|
|
id SERIAL,
|
|
member_id INT REFERENCES members(id),
|
|
cycle_type VARCHAR(15) NOT NULL REFERENCES email_cycle_types(cycle_type),
|
|
subject VARCHAR(100),
|
|
status integer DEFAULT 1,
|
|
added timestamp without time zone DEFAULT now(),
|
|
completed timestamp without time zone DEFAULT null
|
|
);
|
|
ALTER TABLE ONLY members_lifecycle_emails
|
|
ADD CONSTRAINT members_lifecycle_emails_id_key UNIQUE (id);
|
|
*/
|
|
|
|
pg_close($conn);
|
|
pg_close($readOnlyReplicaConn);
|
|
echo "[" . date("Y-m-d H:i:s") . "] member_lifecyle_emails job complete.\n";
|
|
?>
|