New email queue manager

Our PHP application was having problems sending mail following an upgrade of our client’s MS Exchange server.

The problem

Emails were going missing because our application was trying to build then send email in one process; when the connection to the SMTP server failed we lost the email.

Our solution

Surprisingly easy: Place emails in a database table  then process it in the background using a timed process.

The benefits:

  • We capture lots of information including all email headers and content, application userID, submitted time, sending duration, any SMTP errors.
  • Reporting on sent messages in case we need to refer to them
  • Instant diagnostics if there are SMTP server problems
  • We never loose an email
  • Instant user response time from the application – no actual sending when email submitted

The PHP code

You MUST respect the Free licence agreement if you choose to copy, change or redistribute.

<?php
/**
 *
 * Copyright (c) 2012 - Karim Ahmed <karim@sweetcode.co.uk>
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 */
/**
* System User Administration model class
*/
require_once APPLICATION_PATH . '/Models/Exception.php';

class Application_Model_Mail
{
  const MAX_TRY_SEND_COUNT = 5;         // Times to try before giving up on sending an email
  /*
  used to avoid resending same message if sendQueue function executed more than once concurrently.
  If time now greater than this value then we can assume the sending failed without clearing the
  startingToSend field and we can send it.
  */
 const MAX_ALLOWED_SEND_TIME = '00:05';
  public function __construct()
  {
  }
  public function addToQueue( $params, $usersId )
  {
    $db = Zend_Registry::get("db");
    if ( isset( $params['subject'] ) ){
      $data['subject']  = $params['subject'];
    }
    if ( isset( $params['sendTo'] ) ){
      $data['sendTo']   = serialize( $params['sendTo'] );
    }
    else{
      throw new Application_Model_Exception( 'Email recipient required' );
    }
    if ( isset( $params['fromName'] ) ){
      $data['fromName']   = $params['fromName'];
    }
    if ( isset( $params['fromAddress'] ) ){
      $data['fromAddress']   = $params['fromAddress'];
    }
    if ( isset( $params['bccTo'] ) ){
      $data['bccTo']    = serialize( $params['bccTo'] );
    }
    if ( isset( $params['ccTo'] ) ){
      $data['ccTo']     = serialize( $params['ccTo'] );
    }
    if ( isset( $params['replyTo'] ) ){
      $data['replyTo']  = serialize( $params['replyTo'] );
    }
    if ( isset( $params['bodyHtml'] ) ){
      $data['bodyHtml'] = $params['bodyHtml'];
    }
    if ( isset( $params['deleteAfterSend'] ) ){
      $data['deleteAfterSend'] = $params['deleteAfterSend'];
    }
    else{
      $data['deleteAfterSend'] = 0;
    }
    $data['createDate'] = new Zend_Db_Expr('NOW()');
    $data['createByUsersId'] = $usersId;
    $data['modifyDate'] = new Zend_Db_Expr('NOW()');
    $data['modifyByUsersId'] = $usersId;

    $db->insert( 'mail_queue', $data );
  }
  public function sendQueue( $usersId )
  {
    $config = Zend_Registry::get( 'config' );  

    $mailTransport =
      new Zend_Mail_Transport_Smtp(
        $config['mailServer']['smtp']['host'],
        $config['mailServer']['smtp']
      );

    $mail = new Zend_Mail();

    $db = Zend_Registry::get("db");
/*
Update any mail messages that have been "sending" for more than given time with a timed-out error.
reseting startedToSend ensures they will get picked up again for resending in the query below.
*/
    $updateStatement =
      "UPDATE
        mail_queue
      SET
        lastSmtpError = 'Timed-out waiting for server response',
        startedToSendDate = NULL
      WHERE
        sentDate IS NULL AND
        startedToSendDate IS NOT NULL AND
        TIMEDIFF( NOW(), startedToSendDate ) > ?
        AND trySendCount < ?";

    $db->query( $updateStatement, array( self::MAX_ALLOWED_SEND_TIME, self::MAX_TRY_SEND_COUNT ) );
/*
Try to send messages that are not currently being sent by another running instance of this method and have
not reached the the trySend limit
*/
    $select =
      "SELECT
        `mailQueueId`,
        `subject`,
        `sendTo`,
        `bccTo`,
        `ccTo`,
        `replyToName`,
        `replyToAddress`,
        `fromName`,
        `fromAddress`,
        `bodyHtml`,
        `deleteAfterSend`,
        `trySendCount`,
        `sentDate`,
        `lastSmtpError`,
        `createDate`,
        `createByUsersId`,
        `modifyDate`,
        `modifyByUsersId`
      FROM
        mail_queue
      WHERE
        sentDate IS NULL
        AND startedToSendDate IS NULL
        AND trySendCount < ?
      ORDER BY
        createDate";      // oldest first

    $messages = $db->fetchAll( $select, self::MAX_TRY_SEND_COUNT );

    foreach ( $messages as $key => $message ){
      $mail->clearSubject();
      if ( $message['subject'] ){
        $mail->setSubject( $message['subject'] );
      }
      $mail->clearFrom();
      /* uses default from if not set */
      if ( $message['fromAddress'] ) {
        $mail->setFrom( $message[ 'fromAddress'], $message[ 'fromName'] );
      }

      $mail->clearRecipients();
      if ( $message['sendTo'] ){
        $sendTo = unserialize( $message['sendTo'] );
        foreach ( $sendTo as $key => $recipient ){
          $mail->addTo( $recipient['address'], $recipient['name'] );
        }
      }
      else{
        throw new Application_Model_Exception( 'Email recipient required' );
      }

      if ( $message['ccTo'] ){
        $ccTo = unserialize( $message['ccTo'] );
        foreach ( $ccTo as $key => $recipient ){
          $mail->addCc( $recipient['address'], $recipient['name'] );
        }
      }

      /* bcc has no name */
      if ( $message['bccTo'] ){
        $bccTo = unserialize( $message['bccTo'] );
        foreach ( $bccTo as $key => $recipient ){
          $mail->addBcc( $recipient );
        }
      }

      $mail->clearReplyTo();
      if ( $message['replyToAddress'] ){
        $mail->setReplyTo( $message['replyToaddress'], $message['replyToaddress'] );
      }

      $mail->setBodyHtml( '' );
      if ( $message['bodyHtml'] ){
        $mail->setBodyHtml( $message[ 'bodyHtml'] );
      }
      /*
      Common to all database operations
      */
      $where[ 'mailQueueId = ?' ] = $message['mailQueueId'];
      $data[ 'modifyByUsersId' ]  = $usersId;

      try{
        /*
        Record time when we started to send the message.

        Only update the try-send count if we send or get an exception. This prevents the count being
        increased if there is an SMTP time-out (in which case we always want to try sending again)
        */
        $data[ 'modifyDate' ]       = new Zend_Db_Expr('NOW()');
        $data[ 'startedToSendDate' ]    = new Zend_Db_Expr('NOW()');
        $db->update( 'mail_queue', $data, $where );

        $status = $mail->send( $mailTransport );
        /*
        We only get here if the message was sent.
        StartedToSendDate and sentDate are useful to keep so we can see how SMTP server performed
        */
        if( $message[ 'deleteAfterSend'] ){
          $db->delete( 'mail_queue', $where );
        }
        else{
          $data[ 'trySendCount' ]     = $message['trySendCount'] + 1;
          $data[ 'lastSmtpError']     = new Zend_Db_Expr('NULL');
          $data[ 'modifyDate' ]       = new Zend_Db_Expr('NOW()');
          $data[ 'sentDate' ]         = new Zend_Db_Expr('NOW()');
          $db->update( 'mail_queue', $data, $where );
        }
      }
      catch( Exception $e ){
        /*
        Failed to send so record error
        */
        $data[ 'trySendCount' ]       = $message['trySendCount'] + 1;
        $data[ 'lastSmtpError']       = $e->getMessage();
        $data[ 'startedToSendDate' ]  = new Zend_Db_Expr('NULL'); // so we try again ASAP
        $data[ 'modifyDate' ]         = new Zend_Db_Expr('NOW()');
        $db->update( 'mail_queue', $data, $where );
      }
    }
  }
}

 

Leave a Comment

*