Creating A Common Mail Queue?

I want to create a common mail queue that can be used by potentially multiple yii applications on the same server.

Reading through Using Pear Mail Mail_Mime and Mail_Queue this looks promising.

However I’m trying to get a sense of the big picture. Basically it needs two pieces of code:

  1. code to add an email to the queue (where the queue is database table)

  2. code to periodically pull the emails from the queue and send them.

part two I’m thinking of even writing with a python script and crontab. But since I want to learn the Yii way I’m curious how to do it.

However what Yii constructs (console apps, components, extensions) should I use for each part? It needs to be accessible to multiple applications. Would it be better to make an extension that is installed in each yii app? Or to have a single app that handles emailing from multiple apps?

tbl_mail_queue

  • id
  • address_to
  • address_from
  • message
  • success (bit)
  • attempts (integer)
  • date_added
  • date_sent

I used a console app that basically collects all mail queues that haven’t been sent (success = 0 && attempts < 5 - don’t want it to hang on a malformed message) and uses Yii Mail to send the message. Yii Mail has the ability to use views to generate email body. This is handy and allows me to have multiple templates that can be reused such as a registration email template etc.

Rendering views in a console app can be tricky although it’s possible - for this reason, I generate the message body in my web app controllers and save the entire HTML message into tbl_mail_queue - for example, when a user completes their registration, I generate a new MailQueue with the appropriate message, save it, and I’m done. The console app takes care of the rest.

As for multiple apps using it, there shouldn’t be any trouble - it is generic and the tbl_mail_queue should hold all the necessary info including message body and success flags.

I’m travelling at the moment but I can post my code when I back on Monday.

That’d be great thanks




-- 

-- Structure for table `tbl_email_queue`

-- 

DROP TABLE IF EXISTS `tbl_email_queue`;

CREATE TABLE IF NOT EXISTS `tbl_email_queue` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `from_name` varchar(64) DEFAULT NULL,

  `from_email` varchar(128) NOT NULL,

  `to_email` varchar(128) NOT NULL,

  `subject` varchar(255) NOT NULL,

  `message` text NOT NULL,

  `max_attempts` int(11) NOT NULL DEFAULT '3',

  `attempts` int(11) NOT NULL DEFAULT '0',

  `success` tinyint(1) NOT NULL DEFAULT '0',

  `date_published` datetime DEFAULT NULL,

  `last_attempt` datetime DEFAULT NULL,

  `date_sent` datetime DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `to_email` (`to_email`)

) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;






<?php

/**

 * MailQueueCommand class file.

 *

 * @author Matt Skelton

 * @date 26-Jun-2011

 */


/**

 * Sends out emails based on the retrieved EmailQueue objects. 

 */

class MailQueueCommand extends CConsoleCommand

{


    public function run($args)

    {

        $criteria = new CDbCriteria(array(

                'condition' => 'success=:success AND attempts < max_attempts',

                'params' => array(

                    ':success' => 0,

                ),

            ));


        $queueList = EmailQueue::model()->findAll($criteria);


        /* @var $queueItem EmailQueue */

        foreach ($queueList as $queueItem)

        {

            $message = new YiiMailMessage();

            $message->setTo($queueItem->to_email);

            $message->setFrom(array($queueItem->from_email => $queueItem->from_name));

            $message->setSubject($queueItem->subject);

            $message->setBody($queueItem->message, 'text/html');


            if ($this->sendEmail($message))

            {

                $queueItem->attempts = $queueItem->attempts + 1;

                $queueItem->success = 1;

                $queueItem->last_attempt = new CDbExpression('NOW()');

                $queueItem->date_sent = new CDbExpression('NOW()');


                $queueItem->save();

            }

            else

            {

                $queueItem->attempts = $queueItem->attempts + 1;

                $queueItem->last_attempt = new CDbExpression('NOW()');


                $queueItem->save();

            }

        }

    }


    /**

 	* Sends an email to the user.

 	* This methods expects a complete message that includes to, from, subject, and body

 	*

 	* @param YiiMailMessage $message the message to be sent to the user

 	* @return boolean returns true if the message was sent successfully or false if unsuccessful

 	*/

    private function sendEmail(YiiMailMessage $message)

    {

        $sendStatus = false;


        if (Yii::app()->mail->send($message) > 0)

            $sendStatus = true;


        return $sendStatus;

    }


}

?>






<?php

/**

 * This is the model class for table "{{email_queue}}".

 *

 * The followings are the available columns in table '{{email_queue}}':

 * @property integer $id

 * @property string $from_name

 * @property string $from_email

 * @property string $to_email

 * @property string $subject

 * @property string $message

 * @property integer $max_attempts

 * @property integer $attempts

 * @property integer $success

 * @property string $date_published

 * @property string $last_attempt

 * @property string $date_sent

 */

class EmailQueue extends CActiveRecord

{


    /**

 	* Returns the static model of the specified AR class.

 	* @param string $className active record class name.

 	* @return EmailQueue the static model class

 	*/

    public static function model($className=__CLASS__)

    {

        return parent::model($className);

    }


    /**

 	* @return string the associated database table name

 	*/

    public function tableName()

    {

        return '{{email_queue}}';

    }


    /**

 	* @return array validation rules for model attributes.

 	*/

    public function rules()

    {

        // NOTE: you should only define rules for those attributes that

        // will receive user inputs.

        return array(

            array('from_email, to_email, subject, message', 'required'),

            array('max_attempts, attempts, success', 'numerical', 'integerOnly' => true),

            array('from_name', 'length', 'max' => 64),

            array('from_email, to_email', 'length', 'max' => 128),

            array('subject', 'length', 'max' => 255),

            array('date_published, last_attempt, date_sent', 'safe'),

            // The following rule is used by search().

            // Please remove those attributes that should not be searched.

            array('id, from_name, from_email, to_email, subject, message, max_attempts, attempts, success, date_published, last_attempt, date_sent', 'safe', 'on' => 'search'),

        );

    }


    /**

 	* @return array relational rules.

 	*/

    public function relations()

    {

        // NOTE: you may need to adjust the relation name and the related

        // class name for the relations automatically generated below.

        return array(

        );

    }


    /**

 	* @return array customized attribute labels (name=>label)

 	*/

    public function attributeLabels()

    {

        return array(

            'id' => 'ID',

            'from_name' => 'From Name',

            'from_email' => 'From Email',

            'to_email' => 'To Email',

            'subject' => 'Subject',

            'message' => 'Message',

            'max_attempts' => 'Max Attempts',

            'attempts' => 'Attempts',

            'success' => 'Success',

            'date_published' => 'Date Published',

            'last_attempt' => 'Last Attempt',

            'date_sent' => 'Date Sent',

        );

    }


    /**

 	* Retrieves a list of models based on the current search/filter conditions.

 	* @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions.

 	*/

    public function search()

    {

        // Warning: Please modify the following code to remove attributes that

        // should not be searched.


        $criteria = new CDbCriteria;


        $criteria->compare('id', $this->id);

        $criteria->compare('from_name', $this->from_name, true);

        $criteria->compare('from_email', $this->from_email, true);

        $criteria->compare('to_email', $this->to_email, true);

        $criteria->compare('subject', $this->subject, true);

        $criteria->compare('message', $this->message, true);

        $criteria->compare('max_attempts', $this->max_attempts);

        $criteria->compare('attempts', $this->attempts);

        $criteria->compare('success', $this->success);

        $criteria->compare('date_published', $this->date_published, true);

        $criteria->compare('last_attempt', $this->last_attempt, true);

        $criteria->compare('date_sent', $this->date_sent, true);


        return new CActiveDataProvider($this, array(

                'criteria' => $criteria,

            ));

    }


}

?>






// Typical usage in a controller or model

public function afterSave()

    {

        // We only want to add an email notification if the model's

        // original values were in a valid state - old.quantity >= old.reorder_level

        if ($this->oldAttributes['quantity'] >= $this->oldAttributes['reorder_level'])

        {

            // Then we check to see if the new quantity is below the reorder level

            if ($this->quantity <= $this->reorder_level)

            {

                $organization = $this->organization;

                $owner = $organization->owner;

                $emailAddress = $owner->email;


                $emailQueue = new EmailQueue();

                $emailQueue->from_email = Yii::app()->params['adminEmail'];

                $emailQueue->from_name = Yii::app()->name;

                $emailQueue->subject = "Inventory Quantity Notification";

                $emailQueue->to_email = $emailAddress;

                $emailQueue->date_published = new CDbExpression('NOW()');

                $emailQueue->message = Yii::app()->controller->renderPartial('//mail/inventoryLevel/notify', array(

                    'owner' => $owner,

                    'inventory' => $this,

                    'organization' => $organization

                    ), true);


                $emailQueue->save();

            }

        }


        parent::afterSave();

    }



Matt

Thank you that’s exactly the type of structure I was looking for. If you wanted to make this functionality available to multiple applications, how would you go about doing that? Copy and paste it into each app? Create an extension? Create a single console app that other apps somehow reference?

Matt, why don’t you put your post in a wiki? Great addition to wikis.

So I have another MVC using this script, great! its populating the table with information and the loops and everything are working, great!

The issue i have now, is that I dont know how to make it send the emails! :lol:


<? 


$temp2 = '';

foreach($query as $row){

 

  if($row['adviser_id'] != $temp2){

	$var1 = $row['adviser_id'];

	$var2 = $row['adviser_email'];

	$var3 = $row['adviser_firstname'];

	$var4 = $row['adviser_lastname'];

	$var5 = $row['outstanding_visits'];

	

	//echo "<h2><!--[".$row['adviser_id']."]:--> <a href=\"mailto:".$row['adviser_email']."?subject=".$row['outstanding_amount']."&nbsp;Outstanding Visits&amp;body=Dear ".$row['adviser_firstname'].",\n\n The following Leads are missing a Visit Data:" .str_replace(",", ", ", $row['outstanding_lead_ids'])."\n\nPlease advise.\">".$row['adviser_firstname']." ".$row['adviser_lastname']."</a> <span style='color:#ff0000'>(".$row['outstanding_amount'].")</span></h2>";

    $temp2 = $row['adviser_id'];

  }

  $var6 = str_replace(",", ", ", $row['outstanding_lead_ids']);

 


	$emailQueue = new EmailQueue();

	$emailQueue->from_email = Yii::app()->params['adminEmail'];

	$emailQueue->from_name = Yii::app()->name;

	$emailQueue->subject = $var3.", you have ".$var5." visits outstanding.";

	//$emailQueue->to_email = $emailAddress;

	$emailQueue->to_email = 'me@here.co.uk';

	$emailQueue->date_published = new CDbExpression('NOW()');

	$emailQueue->message = Yii::app()->controller->renderPartial('//emails/missed_visits', array(

		'var1' => $var1,

		'var2' => $var2,

		'var3' => $var3,

		'var3' => $var4,

		'var3' => $var5,

		'var6' => $var6,

		), true);


	$emailQueue->save();

	

}	

?>

Is this meant to send on its own, after adding to the database? or do i need a cron to run something? The emails are going into the DB ok, but they just sit there.

How can i tell it to try sending ever 30 mins for example?

Also, if I use the Command Line to run it, I get:

Which refers to this code and the EmailQueue model is in protected/models/


$queueList = EmailQueue::model()->findAll($criteria);

===================================================

Solved: I hadnt configured the console.php file with import and component!

Now when I run ./yiic MailQueue I get about a billion test emails from the queue!

[color="#FF0000"]Will the emails in the queue get deleted after sent?[/color]

p

[size=“2”]To call scripts (e.g. your email console command) in intervalls it’s best practice to use cronjobs.[/size]

[size="2"]There is already a wiki: Implementing cron job with Yii[/size]

Sure there are a few other solutions (e.g. infinite loop scripts, threads) but I would not recommend them cause cronjobs are very easy to set up.

If you are not allowed to create cronjobs by your provider you can run the scrip[size="2"]t on one/or several user action on your site combined with a time limitation e.g. the last script run have to be at least 5 minutes ago. Keep in mind if no user "triggers" your script no emails are sent.[/size]

[size="2"]In the case of your mail queue I would think about sending the mails if the queue has at least xx entries. Check this after every insert to the mail queue.[/size]

Agreed, Im familiar with CRON also, but I dont know where I need to point to (I’m still learning Yii)… I hope the link you provided shows me how to target a Command - im going to look now thank you :)

Update: Following the WIKI, im getting an email from my server when it runs the cron, so this is progress!

Sorry me again, I’m beginning to spam!

I can send email via the Command Line - and I will sort out CRONs shortly.

My question is why is the EmailQueue duplicating each email in the queue on the database?

If i use the following code as a test (not part of my looping etc) it still goes in the database twice! my Command and Model are as above.




<?php

  $emailQueue = new EmailQueue();

  $emailQueue->from_email = Yii::app()->params['adminEmail'];

  $emailQueue->from_name = Yii::app()->name;

  $emailQueue->subject = "testing 123";

  $emailQueue->to_email = "me@domain.co.uk";

  $emailQueue->date_published = new CDbExpression('NOW()');

  $emailQueue->message = Yii::app()->controller->renderPartial('//emails/missed_visits', array(

    'var1' => "blah1",

    'var2' => "blah2",

    'var3' => "blah3",

    'var3' => "blah4",

    'var3' => "blah5",

    'var6' => "blah6",

  ), true);

  $emailQueue->save();

?>

Hi,

I am in the situation that sometimes I could not rely on the CRON. I need to call the command from the web application. Any idea on how to do it?

Kind regards,

Daniel

[list=1][]http://www.yiiframework.com/extension/webshell/[]http://www.yiiframework.com/wiki/226/run-yiic-directly-from-your-app-without-a-shell/[/list]

Thank you for your quick reply.

I have not tested or implemented it on my application. However, my question is that I used the console command since it will take vary long time almost 5 minutes to run which will result in time out.

Is using webshell can make the call become asynchronous? I do not need the result immediately.

Regards,

Daniel

Long time coming, but done - http://www.yiiframework.com/wiki/498/simple-mail-queue/

Matt