Source for file BitNewsletterMailer.php
Documentation is available at BitNewsletterMailer.php
* @copyright (c) 2004 bitweaver.org
* All Rights Reserved. See below for details and a complete list of authors.
* Licensed under the GNU LESSER GENERAL PUBLIC LICENSE. See http://www.gnu.org/copyleft/lesser.html for details
* Class that handles editions of newsletters
* @date created 2005/12/08
* @author spiderr <spider@steelsun.com>
require_once( NEWSLETTERS_PKG_PATH. 'BitNewsletter.php' );
require_once( UTIL_PKG_PATH. 'phpmailer/class.phpmailer.php' );
// Set default variables for all new objects
var $Mailer; // Alternative to IsSMTP()
global $gBitDb, $gBitSystem, $gBitLanguage;
// Replace the default error_handler
bit_error_handler( NULL, NULL, NULL, "FULFILLMENT ERROR: MISSSING PDF for ORDER $pOrderId CID ". $prod->mInfo['related_content_id'], $pdfInfo['pdf_file'], '', $prod->mDb );
if( BitBase::verifyId( $pRecipientMixed ) ) {
return( $this->mDb->getOne( "SELECT COUNT(*) FROM `". BIT_DB_PREFIX. "mail_queue` WHERE `content_id`=? AND `$lookupCol`=?", array( $pContentId, $pRecipientMixed ) ) );
function queueRecipients( $pContentId, $pNewsletterContentId, $pRecipients, $pRequeue= FALSE ) {
if( !empty( $pRecipients ) && BitBase::verifyId( $pContentId ) ) {
$lookup = !empty( $pRecipients[$email]['user_id'] ) ? $pRecipients[$email]['user_id'] : $email;
$insertHash['mail_queue_id'] = $this->mDb->GenID( 'mail_queue_id' );
$insertHash['email'] = $email;
if( !empty( $pRecipients[$email]['user_id'] ) ) {
$insertHash['user_id'] = $pRecipients[$email]['user_id'];
$insertHash['content_id'] = $pContentId;
$insertHash['nl_content_id'] = $pNewsletterContentId;
$insertHash['queue_date'] = $queueTime;
$this->mDb->associateInsert( BIT_DB_PREFIX. 'mail_queue', $insertHash );
} elseif( empty( $unsub ) && $pRequeue ) {
$bindVars = array( $queueTime, $pContentId );
if( !empty( $pRecipients[$email]['user_id'] ) ) {
$bindVars[] = $pRecipients[$email]['user_id'];
$rs = $this->mDb->query( "UPDATE `". BIT_DB_PREFIX. "mail_queue` SET `queue_date`=?, `begin_date`=NULL, `sent_date`=NULL, `last_read_date`=NULL, `mail_error`=NULL, `hits`=0 WHERE `content_id`=? AND `$lookupCol`=?", array( $bindVars ) );
$this->mDb->StartTrans();
WHERE `sent_date` IS NULL AND `mail_error` IS NULL
ORDER BY `queue_date`,`user_id`,`email` ". $this->mDb->SQLForUpdate();
if( $rs = $this->mDb->query( $query, NULL ) ) {
while( $pick = $rs->fetchRow() ) {
$this->mDb->CompleteTrans();
$this->mDb->StartTrans();
$this->mDb->CompleteTrans();
global $gBitSmarty, $gBitSystem, $gBitLanguage;
if( is_array( $pQueueMixed ) ) {
} elseif( is_numeric( $pQueueMixed ) ) {
$pick = $this->mDb->GetRow( "SELECT * FROM `". BIT_DB_PREFIX. "mail_queue` mq WHERE `mail_queue_id` = ? ". $this->mDb->SQLForUpdate(), array( $pQueueMixed ) );
$startTime = microtime( TRUE );
$this->mDb->query( "UPDATE `". BIT_DB_PREFIX. "mail_queue` SET `begin_date`=? WHERE `mail_queue_id` = ? ", array( time(), $pick['mail_queue_id'] ) );
if( !empty( $pick['user_id'] ) ) {
$userHash = $this->mDb->getRow( "SELECT * FROM `". BIT_DB_PREFIX. "users_users` WHERE `user_id`=?", array( $pick['user_id'] ) );
$pick['full_name'] = BitUser::getDisplayName( FALSE, $userHash );
$pick['full_name'] = NULL;
if( !isset ( $body[$pick['content_id']] ) ) {
$gBitSmarty->assign( 'sending', TRUE );
// We only support sending of newsletters currently
$body[$pick['content_id']]['body'] = $content->render();
$body[$pick['content_id']]['subject'] = $content->getTitle();
$body[$pick['content_id']]['reply_to'] = $content->getField( 'reply_to', $gBitSystem->getConfig( 'site_sender_email', $_SERVER['SERVER_ADMIN'] ) );
$body[$pick['content_id']]['object'] = $content;
// $content[$pick['content_id']] = LibertyBase::getLibertyObject();
print "[ $pick[mail_queue_id] ] $pick[content_id] TO: $pick[email]\t";
print " SKIPPED (unsubscribed) <br/>\n";
$this->mDb->query( "DELETE FROM `". BIT_DB_PREFIX. "mail_queue` WHERE `mail_queue_id`=?", array( $pick['mail_queue_id'] ) );
} elseif( !empty( $body[$pick['content_id']] ) ) {
$pick['url_code'] = md5( $pick['content_id']. $pick['email']. $pick['queue_date'] );
if( $body[$pick['content_id']]['object']->mNewsletter->getField('unsub_msg') ) {
$gBitSmarty->assign( 'url_code', $pick['url_code'] );
$gBitSystem->preDisplay('');
$gBitSmarty->assign( 'sending', TRUE );
$gBitSmarty->assign( 'unsubMessage', $unsub );
$gBitSmarty->assign( 'trackCode', $pick['url_code'] );
$gBitSmarty->assign( 'mid', 'bitpackage:newsletters/view_edition.tpl' );
$htmlBody = $gBitSmarty->fetch( 'bitpackage:newsletters/mail_edition.tpl' );
$mailer = new PHPMailer();
if( $gBitSystem->getConfig( 'bitmailer_errors_to' ) ) {
$mailer->Sender = $gBitSystem->getConfig( 'bitmailer_errors_to' );
$mailer->addCustomHeader( "Errors-To: ". $gBitSystem->getConfig( 'bitmailer_errors_to' ) );
$mailer->From = $gBitSystem->getConfig( 'bitmailer_sender_email', $gBitSystem->getConfig( 'site_sender_email', $_SERVER['SERVER_ADMIN'] ) );
$mailer->Host = $gBitSystem->getConfig( 'bitmailer_servers', $gBitSystem->getConfig( 'kernel_server_name', '127.0.0.1' ) );
$mailer->Mailer = $gBitSystem->getConfig( 'bitmailer_protocol', 'smtp' ); // Alternative to IsSMTP()
$mailer->CharSet = 'UTF-8';
if( $gBitSystem->getConfig( 'bitmailer_smtp_username' ) ) {
$mailer->SMTPAuth = TRUE;
$mailer->Username = $gBitSystem->getConfig( 'bitmailer_smtp_username' );
if( $gBitSystem->getConfig( 'bitmailer_smtp_password' ) ) {
$mailer->Password = $gBitSystem->getConfig( 'bitmailer_smtp_password' );
if( !$mailer->SetLanguage( $gBitLanguage->getLanguage(), UTIL_PKG_PATH. 'phpmailer/language/' ) ) {
$mailer->SetLanguage( 'en' );
$mailer->ClearReplyTos();
$mailer->AddReplyTo( $body[$pick['content_id']]['reply_to'], $gBitSystem->getConfig( 'bitmailer_from' ) );
$mailer->Body = $htmlBody;
$mailer->Subject = $body[$pick['content_id']]['subject'];
$mailer->AddAddress( $pick['email'], $pick["full_name"] );
$updateQuery = "UPDATE `". BIT_DB_PREFIX. "mail_queue` SET `sent_date`=?,`url_code`=? WHERE `content_id`=? AND `email`=?";
$this->mDb->query( $updateQuery, array( time(), $pick['url_code'], $pick['content_id'], $pick['email'] ) );
$updateQuery = "UPDATE `". BIT_DB_PREFIX. "mail_queue` SET `mail_error`=?,`sent_date`=? WHERE `content_id`=? AND `email`=?";
$this->mDb->query( $updateQuery, array( $mailer->ErrorInfo, time(), $pick['content_id'], $pick['email'] ) );
$pick['error'] = $mailer->ErrorInfo;
$query = "UPDATE `". BIT_DB_PREFIX. "mail_queue` SET `hits`=`hits`+1, `last_read_date`=?, `last_read_ip`=? WHERE `url_code`=? ";
$gBitDb->query( $query, array( time(), $_SERVER['REMOTE_ADDR'], $pUrlCode ) );
if( !empty( $pInfo['url_code'] ) && !$this->mDb->getOne( "SELECT `url_code` FROM `". BIT_DB_PREFIX. "mail_errors` WHERE `url_code`=?", array( $pInfo['url_code'] ) ) ) {
$store['url_code'] = $pInfo['url_code'];
$store['user_id'] = !empty( $pInfo['user_id'] ) ? $pInfo['user_id'] : NULL;
$store['content_id'] = !empty( $pInfo['content_id'] ) ? $pInfo['content_id'] : NULL;
$store['email'] = !empty( $pInfo['email'] ) ? $pInfo['email'] : NULL;
$store['error_message'] = $pInfo['error'];
$store['error_date'] = time();
$this->mDb->associateInsert( BIT_DB_PREFIX. "mail_errors", $store );
print "ERROR: ". $pInfo['error']. "\n";
// Looks up the code from the url to determine if the unsubscribe URL is valid.
// Can be statically called
$query = "SELECT mq.*, lc.title, tct.*, uu.`real_name`, uu.`login`, uu.`email` FROM `". BIT_DB_PREFIX. "mail_queue` mq
INNER JOIN `". BIT_DB_PREFIX. "liberty_content` lc ON( mq.`content_id`=lc.`content_id` )
INNER JOIN `". BIT_DB_PREFIX. "liberty_content_types` tct ON( tct.`content_type_guid`=lc.`content_type_guid` )
LEFT OUTER JOIN `". BIT_DB_PREFIX. "users_users` uu ON( mq.`user_id`=uu.`user_id` )
WHERE mq.`". key( $pLookup ). "`=? ";
$ret = $gBitDb->getRow( $query, array( current( $pLookup ) ) );
// Accepts a single row has containing the column of mail_subscriptions as the key to lookup the unsubscription info
// Can be statically called
$query = "SELECT ms.`content_id` AS `hash_key`, ms.*, uu.*, lc.title
LEFT OUTER JOIN `". BIT_DB_PREFIX. "users_users` uu ON( ms.`user_id`=uu.`user_id` )
LEFT OUTER JOIN `". BIT_DB_PREFIX. "liberty_content` lc ON( ms.`content_id`=lc.`content_id` )
$ret = $gBitDb->getAssoc( $query, $bindVars );
return $gBitDb->getRow( "SELECT * FROM `". BIT_DB_PREFIX. "mail_subscriptions` ms LEFT JOIN `". BIT_DB_PREFIX. "users_users` uu ON (uu.`user_id`=ms.`user_id`) WHERE (ms.`content_id`=? OR `unsubscribe_all`='y') AND (ms.`email`=? OR uu.`email`=?)", array( $pNewsletterContentId, $pEmail, $pEmail ) );
global $gBitSystem, $gBitDb;
$query = "delete from `". BIT_DB_PREFIX. "mail_subscriptions` where `". key( $pSubHash['sub_lookup'] ). "`=?";
$result = $gBitDb->query($query, array( current( $pSubHash['sub_lookup'] ) ) );
if( !empty( $pSubHash['unsub_content'] ) ) {
foreach( $pSubHash['unsub_content'] as $conId ) {
$storeHash['content_id'] = $conId;
$storeHash['unsubscribe_all'] = !empty( $pSubHash['unsubscribe_all'] ) ? 'y' : NULL;
$storeHash['unsubscribe_date'] = time();
$storeHash[key( $pSubHash['sub_lookup'] )] = current( $pSubHash['sub_lookup'] );
if( !empty( $pSubHash['response_content_id'] ) ) {
$storeHash['response_content_id'] = $pSubHash['response_content_id'];
$gBitDb->associateInsert( BIT_DB_PREFIX. "mail_subscriptions", $storeHash );
} elseif( !empty( $pSubHash['unsubscribe_all'] ) ) {
// unsub all with no reference content_id
$storeHash['unsubscribe_all'] = !empty( $pSubHash['unsubscribe_all'] ) ? 'y' : NULL;
$storeHash['unsubscribe_date'] = time();
$storeHash[key( $pSubHash['sub_lookup'] )] = current( $pSubHash['sub_lookup'] );
if( !empty( $pSubHash['response_content_id'] ) ) {
$storeHash['response_content_id'] = $pSubHash['response_content_id'];
$gBitDb->associateInsert( BIT_DB_PREFIX. "mail_subscriptions", $storeHash );
$query = "SELECT mq.`mail_queue_id` AS `hash_key`, mq.*, lc.`title`, lc2.`title` AS newsletter_title
INNER JOIN `". BIT_DB_PREFIX. "liberty_content` lc ON (lc.content_id=mq.content_id)
LEFT OUTER JOIN `". BIT_DB_PREFIX. "liberty_content` lc2 ON (lc2.content_id=mq.nl_content_id)
if( $rs = $this->mDb->query( $query ) ) {
if( BitBase::verifyId( $pQueueId ) ) {
$this->mDb->query( "DELETE FROM `". BIT_DB_PREFIX. "mail_queue` WHERE `mail_queue_id`=?", array( $pQueueId ) );
$uri = substr( preg_replace( '/[&\?]?ct=[a-z0-9]{32}/', '', 'http://'. $_SERVER['HTTP_HOST']. $_SERVER['REQUEST_URI'] ), 0, 250 );
$query = "SELECT mc.`clicks`, mq.`content_id`, mq.`user_id`, mc.`clicked_url` FROM `". BIT_DB_PREFIX. "mail_queue` mq
LEFT JOIN `". BIT_DB_PREFIX. "mail_clickthrough` mc ON (mq.`user_id`=mc.`user_id` AND mq.`content_id`=mc.`content_id` AND mc.`clicked_url`=?)
if( $row = $gBitDb->getRow( $query, array( $uri, $pUrlCode ) ) ) {
if( $row['clicked_url'] ) {
$gBitDb->query( "UPDATE `". BIT_DB_PREFIX. "mail_clickthrough` SET `clicks`=`clicks`+1 WHERE `user_id`=? AND `content_id`=? AND `clicked_url`=? ", array( $row['user_id'], $row['content_id'], $row['clicked_url'] ) );
$row['clicked_url'] = $uri;
$gBitDb->associateInsert( BIT_DB_PREFIX. 'mail_clickthrough', $row );
// This will insert a ticket on all template URL's that have GET parameters.
global $gBitUser, $gUrlCode;
$pcre = '%href[\s]*=[\s]*["\']*((http|https)://[^\s"\']+?)(.*)%sU';
if( strpos( $matches[0], '?' ) ) {
$ret .= '&ct='. $gUrlCode;
$ret .= '?ct='. $gUrlCode;
|