570 lines
21 KiB
PHP
570 lines
21 KiB
PHP
<?php
|
|
$CurrentDomain = "hotlunches.net";
|
|
$BASEDIR = "/m/data1/app.$CurrentDomain";
|
|
|
|
chdir("$BASEDIR");
|
|
|
|
|
|
$lockFile = "{$BASEDIR}/email.lock";
|
|
if (file_exists($lockFile)) {
|
|
// cronjob is already running for this domain
|
|
// check the age of the lockfile... if it's more than 30 minutes old,
|
|
// the other cronjob probably crashed and we can ignore it
|
|
$t = filemtime($lockFile);
|
|
if (time() - $t > 60*30)
|
|
touch($lockFile);
|
|
else
|
|
return;
|
|
}
|
|
else {
|
|
touch($lockFile);
|
|
}
|
|
|
|
if (!is_file("$BASEDIR/db.php")) {
|
|
echo("ERROR: $BASEDIR/db.php not found\n");
|
|
@unlink($lockFile);
|
|
exit();
|
|
}
|
|
include "$BASEDIR/db.php";
|
|
include "$BASEDIR/include/school-functions.php";
|
|
include "$BASEDIR/include/date-time.php";
|
|
|
|
$customWhere = "";
|
|
|
|
// db_query("use hotlunch_2013");
|
|
// db_query("use hotlunch_2014");
|
|
// db_query("use hotlunch_prev");
|
|
db_query("use hotlunch_20181129");
|
|
$customWhere = "school_num IN (467,461,464,19243,18396,468,466,463) ";
|
|
|
|
// ---------- WILL BE A SERVER-WIDE SCRIPT ----------
|
|
if (!is_file("$BASEDIR/include/sendmail2.php")) {
|
|
echo("ERROR: $BASEDIR/include/sendmail2.php not found\n");
|
|
@unlink($lockFile);
|
|
exit();
|
|
}
|
|
include "$BASEDIR/include/sendmail2.php";
|
|
|
|
|
|
// Preprocess the email header records to reformat date values
|
|
$query = "SELECT * FROM email_header " .
|
|
"WHERE $customWhere " .
|
|
"AND status = 'open' " .
|
|
"AND school_num NOT IN (37)";
|
|
echo($query);
|
|
$results = db_query($query);
|
|
|
|
if (db_num_rows($results) > 0) {
|
|
while ($row = db_fetch_array($results)) {
|
|
if ($row['school_num'] == 3 && $row['category'] == 'Parents and Staff Broadcast') continue;
|
|
|
|
$record_num = $row['record_num'];
|
|
$send_after_pst = date("Y-m-d H:i:s",$row['send_after_time']);
|
|
db_query("UPDATE email_header SET send_after_pst = '$send_after_pst' WHERE record_num = $record_num");
|
|
}
|
|
}
|
|
|
|
|
|
$max_per_run = 500;
|
|
date_default_timezone_set("Canada/Pacific");
|
|
$time = date("H",time());
|
|
// if ($time >= '08' && $time <= '19') $max_per_run = 100;
|
|
|
|
|
|
// Define arrays
|
|
$fields = array();
|
|
$hfields = array();
|
|
|
|
|
|
// Get the first email header record to be processed
|
|
$now = time();
|
|
$query = "select * from email_header " .
|
|
"where $customWhere " .
|
|
"AND status = 'open' " .
|
|
"order by send_after_pst limit 0,1";
|
|
$results = db_query($query);
|
|
// echo("$query\n");
|
|
|
|
|
|
// Get the list of fields in the email_header table
|
|
$cresults = db_query("show columns from email_header");
|
|
while ($crow = db_fetch_array($cresults)) {
|
|
if ($crow['Field'] == 'record_num') continue;
|
|
$hfields[] = $crow['Field'];
|
|
}
|
|
|
|
|
|
if ($results !== FALSE) {
|
|
$num_rows = db_num_rows($results);
|
|
|
|
if ($num_rows == 1) {
|
|
$hrow = db_fetch_array($results);
|
|
|
|
$batchLog = "";
|
|
|
|
// Process the records and send the emails
|
|
$school_num = $hrow['school_num'];
|
|
$log_id = $hrow['record_num'];
|
|
$matching = $hrow['matching_records'];
|
|
$processed = $hrow['processed_records'];
|
|
$equery = $hrow['query'] . " limit $processed,$max_per_run";
|
|
$table_name = $hrow['primary_table'];
|
|
$EmailSubject = stripslashes($hrow['subject']);
|
|
$EmailSubject = str_replace(chr(150),'-',$EmailSubject);
|
|
$EmailMessage = $hrow['message_text'];
|
|
|
|
// Get the school record
|
|
$tquery = "select * from schools where record_num = $school_num";
|
|
$tresults = db_query($tquery);
|
|
$school_record = db_fetch_array($tresults);
|
|
|
|
$school_css = $school_record['colour_scheme'];
|
|
$school_name = $school_record['school_name'];
|
|
$school_url = $school_record['url'];
|
|
$pac_name = $school_record['pac_name'];
|
|
$timezone = $school_record['timezone'];
|
|
$email_title = $school_record['email_title'];
|
|
$email_footer = nl2br($school_record['email_footer']);
|
|
|
|
date_default_timezone_set($timezone);
|
|
|
|
$batchLog .= "----------------------------------------\n";
|
|
$batchLog .= "School Name " . $school_name . "\n";
|
|
$batchLog .= "Category: " . $hrow['category'] . "\n";
|
|
$batchLog .= "Description: " . $hrow['description'] . "\n";
|
|
$batchLog .= "Log ID: " . $hrow['record_num'] . "\n";
|
|
$batchLog .= "Start Run " . date("Y-m-d H:i:s",time()) . "\n";
|
|
|
|
if (strpos($school_url, "hotlunches.net") !== false) $url = "https://$school_url";
|
|
else $url = "https://www.$school_url";
|
|
$url = "";
|
|
$email_footer = str_replace("{URL}","<a href='$url'>$url</a>",$email_footer);
|
|
|
|
// Get the set of email records to process this run
|
|
$eresults = db_query($equery);
|
|
|
|
// If the get was successful process the set
|
|
if ($eresults) {
|
|
|
|
// Determine how many records there are to process
|
|
$number = db_num_rows($eresults);
|
|
|
|
// If there are records to process the loop through them
|
|
if ($number) {
|
|
|
|
// Initialize the SMTP class and standard variables
|
|
include "smtp.php";
|
|
|
|
$smtp = new smtp_class;
|
|
// $smtp->host_name = "mail1.rainforestwebservices.com";
|
|
// $smtp->localhost = "localhost";
|
|
|
|
$batchLog .= "\n";
|
|
$batchLog .= "Records to Process: $number\n\n";
|
|
|
|
// Get other parameters from the header record
|
|
$ReplyTo = $hrow['reply_address'];
|
|
$ReplyDescription = $hrow['reply_description'];
|
|
$Message = $hrow['email_message_id'];
|
|
$EmailTemplate = $hrow['email_template'];
|
|
$EmailField = $hrow['email_field_name'];
|
|
|
|
$EmailAttach = array();
|
|
if ($hrow['attachments'] != '') {
|
|
$EmailAttach = explode(',', $hrow['attachments']);
|
|
}
|
|
|
|
//
|
|
// Get the list of fields in the primary table
|
|
//
|
|
$cresults = db_query("show columns from $table_name");
|
|
while ($crow = db_fetch_array($cresults)) {
|
|
$fields[] = $crow['Field'];
|
|
}
|
|
|
|
$fields[] = "school_name";
|
|
$fields[] = "system_configuration";
|
|
|
|
|
|
// Get the template file if required
|
|
if ($EmailTemplate != "TextMessage") {
|
|
if (!is_file("$EmailTemplate") && !is_file("email_templates/$EmailTemplate")) {
|
|
echo("Error Opening Template: $EmailTemplate\n");
|
|
@unlink($lockFile);
|
|
exit();
|
|
}
|
|
|
|
if (is_file($EmailTemplate)) $fp = fopen("$EmailTemplate","r");
|
|
else if (is_file("email_templates/$EmailTemplate")) $fp = fopen("email_templates/$EmailTemplate","r");
|
|
|
|
// If file opened successfully
|
|
if ($fp) {
|
|
|
|
while (!feof($fp)) {
|
|
$line = fgets($fp,1000);
|
|
$EmailTemplateText .= $line;
|
|
}
|
|
|
|
// Merge the message to send with the template
|
|
$EmailMessage = str_replace("{EMAIL_BODY_TEXT}",$EmailMessage,$EmailTemplateText);
|
|
}
|
|
}
|
|
|
|
|
|
// Process generic variables into the email body
|
|
$EmailMessage = str_replace("{DATE1}",date("D M j, Y",time()),$EmailMessage);
|
|
$EmailMessage = str_replace("{DATE2}",date("M j, Y",time()),$EmailMessage);
|
|
$EmailMessage = str_replace("{DATE3}",date("l F j, Y",time()),$EmailMessage);
|
|
$EmailMessage = str_replace("{DATE4}",date("F j, Y",time()),$EmailMessage);
|
|
$EmailMessage = str_replace("{TIME1}",date("H:i",time()),$EmailMessage);
|
|
$EmailMessage = str_replace("{TIME2}",date("h:i a",time()),$EmailMessage);
|
|
|
|
|
|
// if (strlen($school_css) && is_file("css/$school_css")) $css_code = file_get_contents("css/$school_css");
|
|
|
|
|
|
// Process school colour scheme and header into template
|
|
$EmailMessage = str_replace("{SCHOOL_CSS}",$school_css,$EmailMessage);
|
|
$EmailMessage = str_replace("{SCHOOL_NAME}",$school_name,$EmailMessage);
|
|
$EmailMessage = str_replace("{SCHOOL_URL}",$school_url,$EmailMessage);
|
|
$EmailMessage = str_replace("{PAC_NAME}",$pac_name,$EmailMessage);
|
|
$EmailMessage = str_replace("{EMAIL_TITLE}",$email_title,$EmailMessage);
|
|
|
|
// Create the footer message
|
|
$MessageFooter = $email_footer .
|
|
"<br><br>\n" .
|
|
"View in a browser <a href='http://$school_url/mview.php?e={email}&s=$school_num&i=$log_id'>click here</a>";
|
|
$EmailMessage = str_replace("{FOOTER_TEXT}",$MessageFooter,$EmailMessage);
|
|
|
|
|
|
// Process email_header values into the email template
|
|
for ($i = 0; $i < count($hfields); $i++) {
|
|
$fname = $hfields[$i];
|
|
$EmailMessage = preg_replace('/({|%7[Bb])' . $fname . '(}|%7[Dd])/',$hrow[$fname],$EmailMessage);
|
|
}
|
|
|
|
|
|
|
|
$count = 0;
|
|
$skipped = 0;
|
|
$sent = 0;
|
|
$errors = 0;
|
|
|
|
//
|
|
// Process each record from the result set
|
|
//
|
|
while ($row = db_fetch_array($eresults)) {
|
|
$email = trim($row[$EmailField]);
|
|
$email2 = trim($row['email2']);
|
|
$email3 = trim($row['email3']);
|
|
$count++;
|
|
|
|
// Check if this email address has already received a message
|
|
$num = 0;
|
|
if (strlen($email)) {
|
|
$query = "select * from email_send_log " .
|
|
"where email_log_id = '$log_id' " .
|
|
"and school_num = $school_num " .
|
|
"and email = '$email'";
|
|
$results = db_query($query);
|
|
if (is_resource($results)) $num = db_num_rows($results);
|
|
}
|
|
|
|
if ($num >= 1) {
|
|
$skipped++;
|
|
$status = "Skipped";
|
|
|
|
// Insert record with Skipped status
|
|
|
|
$query = "insert into email_send_log (school_num,email_log_id,email,processed,status) " .
|
|
"values ($school_num,'$log_id','$email',now(),'$status')";
|
|
db_query($query);
|
|
}
|
|
else {
|
|
|
|
// Replace variables from current email record
|
|
$Message = $EmailMessage;
|
|
$Message = preg_replace('/{<span [^>]*>([^<]*)<\/span>/i','{\1',$Message);
|
|
|
|
// Preprocess the account_balance value
|
|
$Message = preg_replace('/({|%7[Bb])' . 'account_balance' . '(}|%7[Dd])/',number_format($row['balance'],2),$Message);
|
|
$Message = preg_replace('/({|%7[Bb])' . 'balance' . '(}|%7[Dd])/',number_format($row['balance'],2),$Message);
|
|
$Message = preg_replace('/({|%7[Bb])' . 'balance_owing' . '(}|%7[Dd])/',number_format($row['balance'],2),$Message);
|
|
|
|
for ($i = 0; $i < count($fields); $i++) {
|
|
$fname = $fields[$i];
|
|
if (array_key_exists($fname,$row)) $Message = preg_replace('/({|%7[Bb])' . $fname . '(}|%7[Dd])/',$row[$fname],$Message);
|
|
}
|
|
|
|
// Replace system variables
|
|
$Message = preg_replace('/({|%7[Bb])TABLE_NAME(}|%7[Dd])/',$table_name,$Message);
|
|
$Message = preg_replace('/({|%7[Bb])RECORD_NUM(}|%7[Dd])/',$row['record_num'],$Message);
|
|
$Message = preg_replace('/({|%7[Bb])EMAIL(}|%7[Dd])/',$email,$Message);
|
|
$Message = preg_replace('/({|%7[Bb])EMAIL_COLUMN(}|%7[Dd])/',$EmailField,$Message);
|
|
|
|
$Message = str_replace(""",'"',$Message);
|
|
$Message = stripslashes($Message);
|
|
|
|
|
|
// Parse image references to force full URL paths
|
|
$Message = str_replace("src=\"/","src=\"http://$CurrentDomain/",$Message);
|
|
$Message = str_replace("src='/","src='http://$CurrentDomain/",$Message);
|
|
|
|
if (strpos($Message, 'http://') == false) {
|
|
$Message = preg_replace("/href=\"([a-z0-9])/","href=\"http://$CurrentDomain/${1}",$Message);
|
|
$Message = preg_replace("/href='([a-z0-9])/","href='http://$CurrentDomain/${1}",$Message);
|
|
$Message = str_replace("href=\"/","href=\"http://$CurrentDomain/",$Message);
|
|
$Message = str_replace("href='/","href='http://$CurrentDomain/",$Message);
|
|
}
|
|
|
|
// Insert the record now to get the record_num of the insert if there is one
|
|
$status = "Sent";
|
|
$email_message = db_escape_string($Message);
|
|
$query = "insert into email_send_log (school_num,email_log_id,email,processed,status,email_message) " .
|
|
"values ($school_num,'$log_id','$email',now(),'$status','$email_message')";
|
|
db_query($query);
|
|
$send_id = db_insert_id();
|
|
|
|
// Replace if has record_num field in DB
|
|
if ($send_id) {
|
|
$Message = str_replace('{LOG_ID}',strval($send_id),$Message);
|
|
}
|
|
|
|
|
|
|
|
//
|
|
// Send message
|
|
//
|
|
|
|
// Sets bounce back email if not already set
|
|
if (!$bounceReply) $bounceReply = $ReplyTo;
|
|
|
|
// Now send the email
|
|
//$rec = array($email);
|
|
for ($i = 1; $i <= 3; $i++) {
|
|
$recipient = "";
|
|
switch ($i) {
|
|
case '1':
|
|
if (strlen($email) > 0) $recipient = $email;
|
|
break;
|
|
|
|
case '2':
|
|
if (strlen($email2) > 0) $recipient = $email2;
|
|
break;
|
|
|
|
case '3':
|
|
if (strlen($email3) > 0) $recipient = $email3;
|
|
break;
|
|
|
|
}
|
|
|
|
if (strlen($recipient) > 0) {
|
|
|
|
$error_message = "";
|
|
if (SendEmail($recipient, $ReplyTo, $EmailSubject, $Message, $ReplyDescription, '', $EmailAttach, $bounceReply)) {
|
|
$status = "Sent";
|
|
$sent++;
|
|
}
|
|
else {
|
|
$status = "Error";
|
|
$errors++;
|
|
|
|
// Update the record status if there is an error
|
|
$query = "update email_send_log " .
|
|
"set status = 'Error', " .
|
|
"error_message = '$error_message' " .
|
|
"where email_log_id = '$log_id' " .
|
|
"and school_num = $school_num " .
|
|
"and email = '$email'";
|
|
db_query($query);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
//
|
|
// Update the header record with the stats from this run
|
|
//
|
|
$query = "update email_header " .
|
|
"set processed_records = processed_records + $count, " .
|
|
"sent_successfully = sent_successfully + $sent, " .
|
|
"skipped = skipped + $skipped, " .
|
|
"errors = errors + $errors " .
|
|
"where record_num = '$log_id'";
|
|
db_query($query);
|
|
|
|
|
|
//
|
|
// Determine if the batch is complete
|
|
//
|
|
$query = "select * from email_header " .
|
|
"where record_num = '$log_id'";
|
|
$results = db_query($query);
|
|
$header = db_fetch_array($results);
|
|
|
|
$matching = $header['matching_records'];
|
|
$processed = $header['processed_records'];
|
|
}
|
|
|
|
|
|
//
|
|
// Display stats if any emails were sent
|
|
//
|
|
if ($count) {
|
|
$batchLog .= "\n";
|
|
$batchLog .= "Processed: $count\n";
|
|
$batchLog .= "Emails Sent: $sent\n";
|
|
$batchLog .= "Skipped: $skipped\n";
|
|
$batchLog .= "Errors: $errors\n";
|
|
}
|
|
|
|
|
|
if ($number == 0 || $matching == $processed) {
|
|
//
|
|
// If there are no more records to process then close the batch
|
|
//
|
|
$now = time();
|
|
$date = date("Y-m-d H:i:s",time());
|
|
$query = "update email_header " .
|
|
"set completed = now(), " .
|
|
"completed_time = $now, " .
|
|
"status = 'completed' " .
|
|
"where record_num = '$log_id'";
|
|
db_query($query);
|
|
echo("$query\n\n");
|
|
|
|
$batchLog .= "\n";
|
|
$batchLog .= "Batch Closed $date\n";
|
|
}
|
|
}
|
|
|
|
$batchLog .= "End Run: " . date("Y-m-d H:i:s",time()) . "\n";
|
|
$batchLog .= "----------------------------------------\n\n";
|
|
|
|
$recipient = $ReplyTo;
|
|
$ReplyTo = "notifications@hotlunches.net";
|
|
$EmailSubject = "Hot Lunches Email Log";
|
|
$Message = $batchLog;
|
|
$ReplyDescription = "Hot Lunches Email Log";
|
|
$EmailAttach = array();
|
|
$bounceReply = $ReplyTo;
|
|
|
|
SendEmail($recipient, $ReplyTo, $EmailSubject, $Message, $ReplyDescription, '', $EmailAttach, $bounceReply);
|
|
|
|
// $recipient = "mark@hotlunches.net";
|
|
// SendEmail($recipient, $ReplyTo, $EmailSubject, $Message, $ReplyDescription, '', $EmailAttach, $bounceReply);
|
|
}
|
|
}
|
|
@unlink($lockFile);
|
|
|
|
|
|
|
|
// *************************
|
|
// ** BOUNCE BACK HANDLING
|
|
// *************************
|
|
|
|
if (isset($bounceUser) && isset($bouncePass)) {
|
|
|
|
// Configuration
|
|
$filePath = "$BASEDIR/email.bounce.ts";
|
|
$imapServer = "mail1.rainforestwebservices.com";
|
|
$imapPort = 143;
|
|
$imapMailboxToCheck = "INBOX";
|
|
$imapUser = $bounceUser;
|
|
$imapPass = $bouncePass;
|
|
$subjectWords = array(
|
|
'returned',
|
|
'failure',
|
|
'failed',
|
|
'undeliverable',
|
|
'unrecognized'
|
|
);
|
|
$subjectPattern = "(" . join("|", $subjectWords) . ")";
|
|
$email_pattern = '[-a-z0-9+._]+@([-a-z0-9]+\.)+[a-z]{2,3}';
|
|
|
|
// get last date run if exists or set to 0
|
|
if (file_exists($filePath)) $lastD = file_get_contents($filePath);
|
|
else $lastD = '0';
|
|
|
|
// get todays date with 1 hour delay (runs after 1 a.m.)
|
|
date_default_timezone_set("America/Vancouver");
|
|
$currD = date('Ymd', time()-3600);
|
|
|
|
// run the below if dates don't match
|
|
if ($currD != $lastD) {
|
|
|
|
// Connect to server
|
|
$connectString = "{{$imapServer}:{$imapPort}/imap/novalidate-cert}";
|
|
if ($showDebugging) echo("$connectString <br>\n");
|
|
$box = imap_open($connectString, $imapUser, $imapPass);
|
|
if ($box === false) {
|
|
echo "BounceCron: Failed to connect to server\n";
|
|
exit();
|
|
}
|
|
|
|
// Open 'to check' mailbox and get message count
|
|
if (!imap_reopen($box, "{$connectString}{$imapMailboxToCheck}")) {
|
|
echo "BounceCron: Failed to connect to POP account\n";
|
|
exit();
|
|
}
|
|
$numToCheck = imap_num_msg($box);
|
|
|
|
// loop through all messages
|
|
$numMatched = 0;
|
|
$numMatchedEmails = 0;
|
|
for ($i=1; $i<=$numToCheck; $i++) {
|
|
|
|
// get the subject line
|
|
$h = imap_headerinfo($box, $i);
|
|
$subject = $h->subject;
|
|
|
|
// check if the subject matches one of our search words
|
|
if (strpos($subject, $subjectPattern) !== false) {
|
|
$numMatched++;
|
|
$emailFound = false;
|
|
|
|
// try to find the bad e-mail address
|
|
$msg = imap_body($box, $i, FT_PEEK);
|
|
if (preg_match("/$email_pattern/", $subject, $matches)) $email = $matches[0];
|
|
elseif (preg_match("/$email_pattern/", $msg, $matches)) $email = $matches[0];
|
|
else $email = false;
|
|
|
|
// if e-mail address found, check if in the logs
|
|
if ($email !== false) {
|
|
$emailDb = db_escape_string($email);
|
|
$query = "select record_num AS rid from email_send_log " .
|
|
"where school_num = $school_num " .
|
|
"and email='$emailDb' " .
|
|
"AND status!='Skipped' " .
|
|
"ORDER BY record_num DESC LIMIT 1";
|
|
$result = db_query($query);
|
|
|
|
// if exists in logs, update logs & mark message for deletion
|
|
if (count($result) > 0) {
|
|
$d = db_fetch_assoc($result);
|
|
extract($d);
|
|
$emailFound = true;
|
|
$numMatchedEmails++;
|
|
$query = "UPDATE email_send_log SET status='Bounced' WHERE record_num='{$rid}'";
|
|
db_query($query);
|
|
|
|
imap_delete ($box, $i);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
// delete marked messages and close mailbox
|
|
imap_close($box, CL_EXPUNGE);
|
|
|
|
// write current date to file
|
|
$f = fopen("$BASEDIR/email.bounce.ts", 'w+');
|
|
fputs($f, $currD);
|
|
fclose($f);
|
|
}
|
|
}
|
|
|
|
|
|
?>
|