<?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";
include "$BASEDIR/include/logging-functions.php";

// startLog("email-send.inc.php");

$customWhere = "";
// $customWhere = "AND record_num = 38149 ";


// DUKE School processing
// db_query("use hotlunch_20220430");
// $query = "SELECT record_num FROM schools WHERE reseller = 'duke'";
// $results = db_query($query);
// $cSchools = "";
// while ($row = db_fetch_array($results)) {
//    if (strlen($cSchools)) $cSchools .= ",";
//    $cSchools .= $row['record_num'];
// }
// $customWhere = "AND school_num IN ($cSchools) ";


// ---------- WILL BE A SERVER-WIDE SCRIPT ----------
if (!is_file("$BASEDIR/include/queuemail.php")) {
   echo("ERROR: $BASEDIR/include/queuemail.php not found\n");
   @unlink($lockFile);
   exit();
}
include "$BASEDIR/include/queuemail.php";

$QueueDelay = 10;


// Preprocess the email header records to reformat date values
$query = "SELECT * FROM email_header " .
         "WHERE send_after_time IS NOT NULL " .
         "AND send_after_pst IS NULL " .
         "AND school_num != 3 " .
         $customWhere;
writeLog($query);
$results = db_query($query);
writeLog("results: ".db_num_rows($results));

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 send_after_time <= $now " .
         "and status = 'open' " .
         "and (completed is null " .
         "or completed < '1900-01-01' " .
         "or processed_records = 0 " .
         "or matching_records > processed_records) " .
         $customWhere .
         "order by send_after_pst limit 0,1";
writeLog($query);
$results = db_query($query);
$numHeaders = db_num_rows($results);
echo("$numHeaders: $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_city   = $school_record['city'];
      $school_prov   = $school_record['province'];
      $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']);
      $address       = $school_record['pac_name'] . '<br>' .
                       $school_record['bill_to_address'] . ', ' . $school_record['bill_to_city'] . ', ' .
                       $school_record['bill_to_province'] . ', ' . $school_record['bill_to_postal'];

      writeLog("school_name: $school_name - $EmailSubject");
      writeLog("-------------------------------------");
      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")) $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
      writeLog($equery);
      $eresults = db_query($equery);

      // If the get was successful process the set
      $num = db_num_rows($eresults);
      writeLog("Users Found: $num");
      if (db_num_rows($eresults)) {

         // Determine how many records there are to process
         $number = db_num_rows($eresults);
         writeLog("number: $number");

         // If there are records to process the loop through them
         if ($number > 0) {

            $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
               $EmailTemplateText = '';
               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);

            $EmailMessage = str_replace("{ADDRESS}",$address,$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++) {
               unset($fname);
               if (array_key_exists($i, $hfields)) $fname = $hfields[$i];
               if (isset($fname) && array_key_exists($fname, $hrow) && !is_null($hrow[$fname])) {
                  $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 = '';
               $email2 = '';
               $email3 = '';
               if (array_key_exists($EmailField, $row) && !is_null($row[$EmailField])) $email = trim($row[$EmailField]);
               if (array_key_exists('email2', $row) && !is_null($row['email2'])) $email2 = trim($row['email2']);
               if (array_key_exists('email3', $row) && !is_null($row['email3'])) $email3 = trim($row['email3']);
               $count++;

               // Check if this email address has already received a message
               $num = 0;
               if (strlen($email)) {
                  $email = db_escape_string($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
                  $balance = 0.00;
                  if (array_key_exists('balance', $row)) $balance = floatval($row['balance']);
                  $Message = preg_replace('/({|%7[Bb])' . 'account_balance' . '(}|%7[Dd])/',number_format($balance,2),$Message);
                  $Message = preg_replace('/({|%7[Bb])' . 'balance' . '(}|%7[Dd])/',number_format($balance,2),$Message);
                  $Message = preg_replace('/({|%7[Bb])' . 'balance_owing' . '(}|%7[Dd])/',number_format($balance,2),$Message);

                  for ($i = 0; $i < count($fields); $i++) {
                     $fname = $fields[$i];
                     if (array_key_exists($fname, $row) && !is_null($row[$fname])) {
                        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])/i',$table_name,$Message);
                  $Message = preg_replace('/({|%7[Bb])RECORD_NUM(}|%7[Dd])/i',$row['record_num'],$Message);
                  $Message = preg_replace('/({|%7[Bb])EMAIL(}|%7[Dd])/i',$email,$Message);
                  $Message = preg_replace('/({|%7[Bb])EMAIL_COLUMN(}|%7[Dd])/i',$EmailField,$Message);
                 
                  $Message = str_replace("&quot;",'"',$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);
                  }


                  $link = "";
                  if (array_key_exists('contact_name', $row) && !is_null($row['contact_name'])) $link .= "&name=".urlencode($row['contact_name']);
                  if (array_key_exists('contact_email', $row) && !is_null($row['contact_email'])) $link .= "&email=".urlencode($row['contact_email']);
                  if (array_key_exists('school_name', $row) && !is_null($row['school_name'])) $link .= "&school_name=".urlencode($row['school_name']);
                  if (array_key_exists('city', $row) && !is_null($row['city'])) $link .= "&city=".urlencode($row['city']);
                  if (array_key_exists('province', $row) && !is_null($row['province'])) $link .= "&province=".urlencode($row['province']);
                  $Message = str_replace("{link}",$link,$Message);
                  if (array_key_exists('contact_name', $row) && !is_null($row['contact_name'])) $Message = str_replace("{contact_name}",$row['contact_name'],$Message);


                  //
                  // Send message
                  //


                  // 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 = "";
                        writeLog("QueueEmail: $recipient");
                        if (QueueEmail($recipient, $ReplyTo, $EmailSubject, $Message, $ReplyDescription, '', $EmailAttach)) {
                           $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";
         }
      }
      else {
         $query = "update email_header " .
                  "set status = 'completed' " .
                  "where record_num = '$log_id'";
         db_query($query);
      }

      $batchLog .= "End Run:      " . date("Y-m-d H:i:s",time()) . "\n";
      $batchLog .= "----------------------------------------\n\n";

      if (!isset($ReplyTo)) $ReplyTo = $school_record['support_email'];
      if (strlen($ReplyTo) == 0)  $ReplyTo = "notifications@hotlunches.net";
      $recipient        = $ReplyTo;
      $ReplyTo          = "notifications@hotlunches.net";
      $EmailSubject     = "Hot Lunches Email Log";
      $Message          = $batchLog;
      $ReplyDescription = "Hot Lunches Email Log";
      $EmailAttach      = array();

      QueueEmail($recipient, $ReplyTo, $EmailSubject, $Message, $ReplyDescription, '', $EmailAttach);

      // $recipient        = "mark@hotlunches.net";
      // QueueEmail($recipient, $ReplyTo, $EmailSubject, $Message, $ReplyDescription, '', $EmailAttach);
   }
}
@unlink($lockFile);


?>