/**
* Create a new delivery event.
*
* @param array $params
* Associative array of delivery event values.
*
* @return void
*/
public static function &create(&$params)
{
$q =& CRM_Mailing_Event_BAO_Queue::verify($params['job_id'], $params['event_queue_id'], $params['hash']);
if (!$q) {
return NULL;
}
$q->free();
$delivered = new CRM_Mailing_Event_BAO_Delivered();
$delivered->time_stamp = date('YmdHis');
$delivered->copyValues($params);
$delivered->save();
$queue = new CRM_Mailing_Event_BAO_Queue();
$queue->id = $params['event_queue_id'];
$queue->find(TRUE);
while ($queue->fetch()) {
$email = new CRM_Core_BAO_Email();
$email->id = $queue->email_id;
$email->hold_date = '';
$email->reset_date = date('YmdHis');
$email->save();
}
return $delivered;
}
/**
* @param array $deliveredParams
* @param array $targetParams
* @param $mailing
* @param $job_date
*
* @return bool
* @throws CRM_Core_Exception
* @throws Exception
*/
public function writeToDB(&$deliveredParams, &$targetParams, &$mailing, $job_date)
{
static $activityTypeID = NULL;
static $writeActivity = NULL;
if (!empty($deliveredParams)) {
CRM_Mailing_Event_BAO_Delivered::bulkCreate($deliveredParams);
$deliveredParams = array();
}
if ($writeActivity === NULL) {
$writeActivity = CRM_Core_BAO_Setting::getItem(CRM_Core_BAO_Setting::MAILING_PREFERENCES_NAME, 'write_activity_record', NULL, TRUE);
}
if (!$writeActivity) {
return TRUE;
}
$result = TRUE;
if (!empty($targetParams) && !empty($mailing->scheduled_id)) {
if (!$activityTypeID) {
if ($mailing->sms_provider_id) {
$mailing->subject = $mailing->name;
$activityTypeID = CRM_Core_OptionGroup::getValue('activity_type', 'Mass SMS', 'name');
} else {
$activityTypeID = CRM_Core_OptionGroup::getValue('activity_type', 'Bulk Email', 'name');
}
if (!$activityTypeID) {
CRM_Core_Error::fatal();
}
}
$activity = array('source_contact_id' => $mailing->scheduled_id, 'target_contact_id' => array_unique($targetParams), 'activity_type_id' => $activityTypeID, 'source_record_id' => $this->mailing_id, 'activity_date_time' => $job_date, 'subject' => $mailing->subject, 'status_id' => 2, 'deleteActivityTarget' => FALSE, 'campaign_id' => $mailing->campaign_id);
//check whether activity is already created for this mailing.
//if yes then create only target contact record.
$query = "\nSELECT id\nFROM civicrm_activity\nWHERE civicrm_activity.activity_type_id = %1\nAND civicrm_activity.source_record_id = %2\n";
$queryParams = array(1 => array($activityTypeID, 'Integer'), 2 => array($this->mailing_id, 'Integer'));
$activityID = CRM_Core_DAO::singleValueQuery($query, $queryParams);
if ($activityID) {
$activity['id'] = $activityID;
// CRM-9519
if (CRM_Core_BAO_Email::isMultipleBulkMail()) {
static $targetRecordID = NULL;
if (!$targetRecordID) {
$activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
$targetRecordID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
}
// make sure we don't attempt to duplicate the target activity
foreach ($activity['target_contact_id'] as $key => $targetID) {
$sql = "\nSELECT id\nFROM civicrm_activity_contact\nWHERE activity_id = {$activityID}\nAND contact_id = {$targetID}\nAND record_type_id = {$targetRecordID}\n";
if (CRM_Core_DAO::singleValueQuery($sql)) {
unset($activity['target_contact_id'][$key]);
}
}
}
}
if (is_a(CRM_Activity_BAO_Activity::create($activity), 'CRM_Core_Error')) {
$result = FALSE;
}
$targetParams = array();
}
return $result;
}
/**
* Create a new forward event, create a new contact if necessary
*/
static function &forward($job_id, $queue_id, $hash, $forward_email, $fromEmail = null, $comment = null)
{
$q =& CRM_Mailing_Event_BAO_Queue::verify($job_id, $queue_id, $hash);
$successfulForward = false;
if (!$q) {
return $successfulForward;
}
/* Find the email address/contact, if it exists */
$contact = CRM_Contact_BAO_Contact::getTableName();
$location = CRM_Core_BAO_Location::getTableName();
$email = CRM_Core_BAO_Email::getTableName();
$queueTable = CRM_Mailing_Event_BAO_Queue::getTableName();
$job = CRM_Mailing_BAO_Job::getTableName();
$mailing = CRM_Mailing_BAO_Mailing::getTableName();
$forward = self::getTableName();
$domain =& CRM_Core_BAO_Domain::getDomain();
$dao =& new CRM_Core_Dao();
$dao->query("\n SELECT {$contact}.id as contact_id,\n {$email}.id as email_id,\n {$contact}.do_not_email as do_not_email,\n {$queueTable}.id as queue_id\n FROM ({$email}, {$job} as temp_job)\n INNER JOIN {$contact}\n ON {$email}.contact_id = {$contact}.id\n LEFT JOIN {$queueTable}\n ON {$email}.id = {$queueTable}.email_id\n LEFT JOIN {$job}\n ON {$queueTable}.job_id = {$job}.id\n AND temp_job.mailing_id = {$job}.mailing_id\n WHERE {$queueTable}.job_id = {$job_id}\n AND {$email}.email = '" . CRM_Utils_Type::escape($forward_email, 'String') . "'");
$dao->fetch();
require_once 'CRM/Core/Transaction.php';
$transaction = new CRM_Core_Transaction();
if (isset($dao->queue_id) || $dao->do_not_email == 1) {
/* We already sent this mailing to $forward_email, or we should
* never email this contact. Give up. */
return $successfulForward;
}
require_once 'api/v2/Contact.php';
$contact_params = array('email' => $forward_email);
$count = civicrm_contact_search_count($contact_params);
if ($count == 0) {
require_once 'CRM/Core/BAO/LocationType.php';
/* If the contact does not exist, create one. */
$formatted = array('contact_type' => 'Individual');
$locationType = CRM_Core_BAO_LocationType::getDefault();
$value = array('email' => $forward_email, 'location_type_id' => $locationType->id);
_civicrm_add_formatted_param($value, $formatted);
require_once 'CRM/Import/Parser.php';
$formatted['onDuplicate'] = CRM_Import_Parser::DUPLICATE_SKIP;
$formatted['fixAddress'] = true;
$contact =& civicrm_contact_format_create($formatted);
if (civicrm_error($contact, CRM_Core_Error)) {
return $successfulForward;
}
$contact_id = $contact['id'];
}
$email =& new CRM_Core_DAO_Email();
$email->email = $forward_email;
$email->find(true);
$email_id = $email->id;
if (!$contact_id) {
$contact_id = $email->contact_id;
}
/* Create a new queue event */
$queue_params = array('email_id' => $email_id, 'contact_id' => $contact_id, 'job_id' => $job_id);
$queue =& CRM_Mailing_Event_BAO_Queue::create($queue_params);
$forward =& new CRM_Mailing_Event_BAO_Forward();
$forward->time_stamp = date('YmdHis');
$forward->event_queue_id = $queue_id;
$forward->dest_queue_id = $queue->id;
$forward->save();
$dao->reset();
$dao->query(" SELECT {$job}.mailing_id as mailing_id \n FROM {$job}\n WHERE {$job}.id = " . CRM_Utils_Type::escape($job_id, 'Integer'));
$dao->fetch();
$mailing_obj =& new CRM_Mailing_BAO_Mailing();
$mailing_obj->id = $dao->mailing_id;
$mailing_obj->find(true);
$config =& CRM_Core_Config::singleton();
$mailer =& $config->getMailer();
$recipient = null;
$attachments = null;
$message =& $mailing_obj->compose($job_id, $queue->id, $queue->hash, $queue->contact_id, $forward_email, $recipient, false, null, $attachments, true, $fromEmail);
//append comment if added while forwarding.
if (count($comment)) {
$message->_txtbody = $comment['body_text'] . $message->_txtbody;
if (CRM_Utils_Array::value('body_html', $comment)) {
$message->_htmlbody = $comment['body_html'] . '<br />---------------Original message---------------------<br />' . $message->_htmlbody;
}
}
$body = $message->get();
$headers = $message->headers();
PEAR::setErrorHandling(PEAR_ERROR_CALLBACK, array('CRM_Core_Error', 'nullHandler'));
$result = null;
if (is_object($mailer)) {
$result = $mailer->send($recipient, $headers, $body);
CRM_Core_Error::setCallback();
}
$params = array('event_queue_id' => $queue->id, 'job_id' => $job_id, 'hash' => $queue->hash);
if (is_a($result, PEAR_Error)) {
/* Register the bounce event */
$params = array_merge($params, CRM_Mailing_BAO_BouncePattern::match($result->getMessage()));
CRM_Mailing_Event_BAO_Bounce::create($params);
} else {
$successfulForward = true;
/* Register the delivery event */
CRM_Mailing_Event_BAO_Delivered::create($params);
}
$transaction->commit();
return $successfulForward;
}
/**
* returns all the rows in the given offset and rowCount
*
* @param enum $action the action being performed
* @param int $offset the row number to start from
* @param int $rowCount the number of rows to return
* @param string $sort the sql string that describes the sort order
* @param enum $output what should the result set include (web/email/csv)
*
* @return int the total number of rows for this action
*/
function &getRows($action, $offset, $rowCount, $sort, $output = NULL)
{
switch ($this->_event_type) {
case 'queue':
return CRM_Mailing_Event_BAO_Queue::getRows($this->_mailing_id, $this->_job_id, $offset, $rowCount, $sort);
break;
case 'delivered':
return CRM_Mailing_Event_BAO_Delivered::getRows($this->_mailing_id, $this->_job_id, $this->_is_distinct, $offset, $rowCount, $sort);
break;
case 'opened':
return CRM_Mailing_Event_BAO_Opened::getRows($this->_mailing_id, $this->_job_id, $this->_is_distinct, $offset, $rowCount, $sort);
break;
case 'bounce':
return CRM_Mailing_Event_BAO_Bounce::getRows($this->_mailing_id, $this->_job_id, $this->_is_distinct, $offset, $rowCount, $sort);
break;
case 'forward':
return CRM_Mailing_Event_BAO_Forward::getRows($this->_mailing_id, $this->_job_id, $this->_is_distinct, $offset, $rowCount, $sort);
case 'reply':
return CRM_Mailing_Event_BAO_Reply::getRows($this->_mailing_id, $this->_job_id, $this->_is_distinct, $offset, $rowCount, $sort);
break;
case 'unsubscribe':
return CRM_Mailing_Event_BAO_Unsubscribe::getRows($this->_mailing_id, $this->_job_id, $this->_is_distinct, $offset, $rowCount, $sort, TRUE);
break;
case 'optout':
return CRM_Mailing_Event_BAO_Unsubscribe::getRows($this->_mailing_id, $this->_job_id, $this->_is_distinct, $offset, $rowCount, $sort, FALSE);
break;
case 'click':
return CRM_Mailing_Event_BAO_TrackableURLOpen::getRows($this->_mailing_id, $this->_job_id, $this->_is_distinct, $this->_url_id, $offset, $rowCount, $sort);
break;
default:
return NULL;
}
}
/**
* Generate a report. Fetch event count information, mailing data, and job
* status.
*
* @param int $id
* The mailing id to report.
* @param bool $skipDetails
* Whether return all detailed report.
*
* @param bool $isSMS
*
* @return array
* Associative array of reporting data
*/
public static function &report($id, $skipDetails = FALSE, $isSMS = FALSE)
{
$mailing_id = CRM_Utils_Type::escape($id, 'Integer');
$mailing = new CRM_Mailing_BAO_Mailing();
$t = array('mailing' => self::getTableName(), 'mailing_group' => CRM_Mailing_DAO_MailingGroup::getTableName(), 'group' => CRM_Contact_BAO_Group::getTableName(), 'job' => CRM_Mailing_BAO_MailingJob::getTableName(), 'queue' => CRM_Mailing_Event_BAO_Queue::getTableName(), 'delivered' => CRM_Mailing_Event_BAO_Delivered::getTableName(), 'opened' => CRM_Mailing_Event_BAO_Opened::getTableName(), 'reply' => CRM_Mailing_Event_BAO_Reply::getTableName(), 'unsubscribe' => CRM_Mailing_Event_BAO_Unsubscribe::getTableName(), 'bounce' => CRM_Mailing_Event_BAO_Bounce::getTableName(), 'forward' => CRM_Mailing_Event_BAO_Forward::getTableName(), 'url' => CRM_Mailing_BAO_TrackableURL::getTableName(), 'urlopen' => CRM_Mailing_Event_BAO_TrackableURLOpen::getTableName(), 'component' => CRM_Mailing_BAO_Component::getTableName(), 'spool' => CRM_Mailing_BAO_Spool::getTableName());
$report = array();
$additionalWhereClause = " AND ";
if (!$isSMS) {
$additionalWhereClause .= " {$t['mailing']}.sms_provider_id IS NULL ";
} else {
$additionalWhereClause .= " {$t['mailing']}.sms_provider_id IS NOT NULL ";
}
/* Get the mailing info */
$mailing->query("\n SELECT {$t['mailing']}.*\n FROM {$t['mailing']}\n WHERE {$t['mailing']}.id = {$mailing_id} {$additionalWhereClause}");
$mailing->fetch();
$report['mailing'] = array();
foreach (array_keys(self::fields()) as $field) {
$report['mailing'][$field] = $mailing->{$field};
}
//get the campaign
if ($campaignId = CRM_Utils_Array::value('campaign_id', $report['mailing'])) {
$campaigns = CRM_Campaign_BAO_Campaign::getCampaigns($campaignId);
$report['mailing']['campaign'] = $campaigns[$campaignId];
}
//mailing report is called by activity
//we dont need all detail report
if ($skipDetails) {
return $report;
}
/* Get the component info */
$query = array();
$components = array('header' => ts('Header'), 'footer' => ts('Footer'), 'reply' => ts('Reply'), 'unsubscribe' => ts('Unsubscribe'), 'optout' => ts('Opt-Out'));
foreach (array_keys($components) as $type) {
$query[] = "SELECT {$t['component']}.name as name,\n '{$type}' as type,\n {$t['component']}.id as id\n FROM {$t['component']}\n INNER JOIN {$t['mailing']}\n ON {$t['mailing']}.{$type}_id =\n {$t['component']}.id\n WHERE {$t['mailing']}.id = {$mailing_id}";
}
$q = '(' . implode(') UNION (', $query) . ')';
$mailing->query($q);
$report['component'] = array();
while ($mailing->fetch()) {
$report['component'][] = array('type' => $components[$mailing->type], 'name' => $mailing->name, 'link' => CRM_Utils_System::url('civicrm/mailing/component', "reset=1&action=update&id={$mailing->id}"));
}
/* Get the recipient group info */
$mailing->query("\n SELECT {$t['mailing_group']}.group_type as group_type,\n {$t['group']}.id as group_id,\n {$t['group']}.title as group_title,\n {$t['group']}.is_hidden as group_hidden,\n {$t['mailing']}.id as mailing_id,\n {$t['mailing']}.name as mailing_name\n FROM {$t['mailing_group']}\n LEFT JOIN {$t['group']}\n ON {$t['mailing_group']}.entity_id = {$t['group']}.id\n AND {$t['mailing_group']}.entity_table =\n '{$t['group']}'\n LEFT JOIN {$t['mailing']}\n ON {$t['mailing_group']}.entity_id =\n {$t['mailing']}.id\n AND {$t['mailing_group']}.entity_table =\n '{$t['mailing']}'\n\n WHERE {$t['mailing_group']}.mailing_id = {$mailing_id}\n ");
$report['group'] = array('include' => array(), 'exclude' => array(), 'base' => array());
while ($mailing->fetch()) {
$row = array();
if (isset($mailing->group_id)) {
$row['id'] = $mailing->group_id;
$row['name'] = $mailing->group_title;
$row['link'] = CRM_Utils_System::url('civicrm/group/search', "reset=1&force=1&context=smog&gid={$row['id']}");
} else {
$row['id'] = $mailing->mailing_id;
$row['name'] = $mailing->mailing_name;
$row['mailing'] = TRUE;
$row['link'] = CRM_Utils_System::url('civicrm/mailing/report', "mid={$row['id']}");
}
/* Rename hidden groups */
if ($mailing->group_hidden == 1) {
$row['name'] = "Search Results";
}
if ($mailing->group_type == 'Include') {
$report['group']['include'][] = $row;
} elseif ($mailing->group_type == 'Base') {
$report['group']['base'][] = $row;
} else {
$report['group']['exclude'][] = $row;
}
}
/* Get the event totals, grouped by job (retries) */
$mailing->query("\n SELECT {$t['job']}.*,\n COUNT(DISTINCT {$t['queue']}.id) as queue,\n COUNT(DISTINCT {$t['delivered']}.id) as delivered,\n COUNT(DISTINCT {$t['reply']}.id) as reply,\n COUNT(DISTINCT {$t['forward']}.id) as forward,\n COUNT(DISTINCT {$t['bounce']}.id) as bounce,\n COUNT(DISTINCT {$t['urlopen']}.id) as url,\n COUNT(DISTINCT {$t['spool']}.id) as spool\n FROM {$t['job']}\n LEFT JOIN {$t['queue']}\n ON {$t['queue']}.job_id = {$t['job']}.id\n LEFT JOIN {$t['reply']}\n ON {$t['reply']}.event_queue_id = {$t['queue']}.id\n LEFT JOIN {$t['forward']}\n ON {$t['forward']}.event_queue_id = {$t['queue']}.id\n LEFT JOIN {$t['bounce']}\n ON {$t['bounce']}.event_queue_id = {$t['queue']}.id\n LEFT JOIN {$t['delivered']}\n ON {$t['delivered']}.event_queue_id = {$t['queue']}.id\n AND {$t['bounce']}.id IS null\n LEFT JOIN {$t['urlopen']}\n ON {$t['urlopen']}.event_queue_id = {$t['queue']}.id\n LEFT JOIN {$t['spool']}\n ON {$t['spool']}.job_id = {$t['job']}.id\n WHERE {$t['job']}.mailing_id = {$mailing_id}\n AND {$t['job']}.is_test = 0\n GROUP BY {$t['job']}.id");
$report['jobs'] = array();
$report['event_totals'] = array();
$elements = array('queue', 'delivered', 'url', 'forward', 'reply', 'unsubscribe', 'optout', 'opened', 'bounce', 'spool');
// initialize various counters
foreach ($elements as $field) {
$report['event_totals'][$field] = 0;
}
while ($mailing->fetch()) {
$row = array();
foreach ($elements as $field) {
if (isset($mailing->{$field})) {
$row[$field] = $mailing->{$field};
$report['event_totals'][$field] += $mailing->{$field};
}
}
// compute open total separately to discount duplicates
// CRM-1258
$row['opened'] = CRM_Mailing_Event_BAO_Opened::getTotalCount($mailing_id, $mailing->id, TRUE);
$report['event_totals']['opened'] += $row['opened'];
// compute unsub total separately to discount duplicates
// CRM-1783
$row['unsubscribe'] = CRM_Mailing_Event_BAO_Unsubscribe::getTotalCount($mailing_id, $mailing->id, TRUE, TRUE);
$report['event_totals']['unsubscribe'] += $row['unsubscribe'];
$row['optout'] = CRM_Mailing_Event_BAO_Unsubscribe::getTotalCount($mailing_id, $mailing->id, TRUE, FALSE);
$report['event_totals']['optout'] += $row['optout'];
foreach (array_keys(CRM_Mailing_BAO_MailingJob::fields()) as $field) {
$row[$field] = $mailing->{$field};
}
if ($mailing->queue) {
$row['delivered_rate'] = 100.0 * $mailing->delivered / $mailing->queue;
$row['bounce_rate'] = 100.0 * $mailing->bounce / $mailing->queue;
$row['unsubscribe_rate'] = 100.0 * $row['unsubscribe'] / $mailing->queue;
$row['optout_rate'] = 100.0 * $row['optout'] / $mailing->queue;
} else {
$row['delivered_rate'] = 0;
$row['bounce_rate'] = 0;
$row['unsubscribe_rate'] = 0;
$row['optout_rate'] = 0;
}
$row['links'] = array('clicks' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=click&mid={$mailing_id}&jid={$mailing->id}"), 'queue' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=queue&mid={$mailing_id}&jid={$mailing->id}"), 'delivered' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=delivered&mid={$mailing_id}&jid={$mailing->id}"), 'bounce' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=bounce&mid={$mailing_id}&jid={$mailing->id}"), 'unsubscribe' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=unsubscribe&mid={$mailing_id}&jid={$mailing->id}"), 'forward' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=forward&mid={$mailing_id}&jid={$mailing->id}"), 'reply' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=reply&mid={$mailing_id}&jid={$mailing->id}"), 'opened' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=opened&mid={$mailing_id}&jid={$mailing->id}"));
foreach (array('scheduled_date', 'start_date', 'end_date') as $key) {
$row[$key] = CRM_Utils_Date::customFormat($row[$key]);
}
$report['jobs'][] = $row;
}
$newTableSize = CRM_Mailing_BAO_Recipients::mailingSize($mailing_id);
// we need to do this for backward compatibility, since old mailings did not
// use the mailing_recipients table
if ($newTableSize > 0) {
$report['event_totals']['queue'] = $newTableSize;
} else {
$report['event_totals']['queue'] = self::getRecipientsCount($mailing_id, $mailing_id);
}
if (!empty($report['event_totals']['queue'])) {
$report['event_totals']['delivered_rate'] = 100.0 * $report['event_totals']['delivered'] / $report['event_totals']['queue'];
$report['event_totals']['bounce_rate'] = 100.0 * $report['event_totals']['bounce'] / $report['event_totals']['queue'];
$report['event_totals']['unsubscribe_rate'] = 100.0 * $report['event_totals']['unsubscribe'] / $report['event_totals']['queue'];
$report['event_totals']['optout_rate'] = 100.0 * $report['event_totals']['optout'] / $report['event_totals']['queue'];
} else {
$report['event_totals']['delivered_rate'] = 0;
$report['event_totals']['bounce_rate'] = 0;
$report['event_totals']['unsubscribe_rate'] = 0;
$report['event_totals']['optout_rate'] = 0;
}
/* Get the click-through totals, grouped by URL */
$mailing->query("\n SELECT {$t['url']}.url,\n {$t['url']}.id,\n COUNT({$t['urlopen']}.id) as clicks,\n COUNT(DISTINCT {$t['queue']}.id) as unique_clicks\n FROM {$t['url']}\n LEFT JOIN {$t['urlopen']}\n ON {$t['urlopen']}.trackable_url_id = {$t['url']}.id\n LEFT JOIN {$t['queue']}\n ON {$t['urlopen']}.event_queue_id = {$t['queue']}.id\n LEFT JOIN {$t['job']}\n ON {$t['queue']}.job_id = {$t['job']}.id\n WHERE {$t['url']}.mailing_id = {$mailing_id}\n AND {$t['job']}.is_test = 0\n GROUP BY {$t['url']}.id");
$report['click_through'] = array();
while ($mailing->fetch()) {
$report['click_through'][] = array('url' => $mailing->url, 'link' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=click&mid={$mailing_id}&uid={$mailing->id}"), 'link_unique' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=click&mid={$mailing_id}&uid={$mailing->id}&distinct=1"), 'clicks' => $mailing->clicks, 'unique' => $mailing->unique_clicks, 'rate' => CRM_Utils_Array::value('delivered', $report['event_totals']) ? 100.0 * $mailing->unique_clicks / $report['event_totals']['delivered'] : 0);
}
$report['event_totals']['links'] = array('clicks' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=click&mid={$mailing_id}"), 'clicks_unique' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=click&mid={$mailing_id}&distinct=1"), 'queue' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=queue&mid={$mailing_id}"), 'delivered' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=delivered&mid={$mailing_id}"), 'bounce' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=bounce&mid={$mailing_id}"), 'unsubscribe' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=unsubscribe&mid={$mailing_id}"), 'optout' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=optout&mid={$mailing_id}"), 'forward' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=forward&mid={$mailing_id}"), 'reply' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=reply&mid={$mailing_id}"), 'opened' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=opened&mid={$mailing_id}"));
$actionLinks = array(CRM_Core_Action::VIEW => array('name' => ts('Report')));
if (CRM_Core_Permission::check('view all contacts')) {
$actionLinks[CRM_Core_Action::ADVANCED] = array('name' => ts('Advanced Search'), 'url' => 'civicrm/contact/search/advanced');
}
$action = array_sum(array_keys($actionLinks));
$report['event_totals']['actionlinks'] = array();
foreach (array('clicks', 'clicks_unique', 'queue', 'delivered', 'bounce', 'unsubscribe', 'forward', 'reply', 'opened', 'optout') as $key) {
$url = 'mailing/detail';
$reportFilter = "reset=1&mailing_id_value={$mailing_id}";
$searchFilter = "force=1&mailing_id=%%mid%%";
switch ($key) {
case 'delivered':
$reportFilter .= "&delivery_status_value=successful";
$searchFilter .= "&mailing_delivery_status=Y";
break;
case 'bounce':
$url = "mailing/bounce";
$searchFilter .= "&mailing_delivery_status=N";
break;
case 'forward':
$reportFilter .= "&is_forwarded_value=1";
$searchFilter .= "&mailing_forward=1";
break;
case 'reply':
$reportFilter .= "&is_replied_value=1";
$searchFilter .= "&mailing_reply_status=Y";
break;
case 'unsubscribe':
$reportFilter .= "&is_unsubscribed_value=1";
$searchFilter .= "&mailing_unsubscribe=1";
break;
case 'optout':
$reportFilter .= "&is_optout_value=1";
$searchFilter .= "&mailing_optout=1";
break;
case 'opened':
$url = "mailing/opened";
$searchFilter .= "&mailing_open_status=Y";
break;
case 'clicks':
case 'clicks_unique':
$url = "mailing/clicks";
$searchFilter .= "&mailing_click_status=Y";
break;
}
$actionLinks[CRM_Core_Action::VIEW]['url'] = CRM_Report_Utils_Report::getNextUrl($url, $reportFilter, FALSE, TRUE);
if (array_key_exists(CRM_Core_Action::ADVANCED, $actionLinks)) {
$actionLinks[CRM_Core_Action::ADVANCED]['qs'] = $searchFilter;
}
$report['event_totals']['actionlinks'][$key] = CRM_Core_Action::formLink($actionLinks, $action, array('mid' => $mailing_id), ts('more'), FALSE, 'mailing.report.action', 'Mailing', $mailing_id);
}
return $report;
}
/**
* Fix the reset dates on the email record based on when a mail was last delivered.
*
* We only consider mailings that were completed and finished in the last 3 to 7 days
* Both the min and max days can be set via the params
*
* @param array $params
*
* @return array
*/
function civicrm_api3_mailing_update_email_resetdate($params)
{
CRM_Mailing_Event_BAO_Delivered::updateEmailResetDate(CRM_Utils_Array::value('minDays', $params, 3), CRM_Utils_Array::value('maxDays', $params, 3));
return civicrm_api3_create_success();
}
/**
* Send the mailing
*
* @param object $mailer A Mail object to send the messages
* @return void
* @access public
*/
function deliver(&$mailer)
{
require_once 'CRM/Mailing/BAO/Mailing.php';
$mailing =& new CRM_Mailing_BAO_Mailing();
$mailing->id = $this->mailing_id;
$mailing->find(true);
$eq =& new CRM_Mailing_Event_BAO_Queue();
$eqTable = CRM_Mailing_Event_BAO_Queue::getTableName();
$emailTable = CRM_Core_BAO_Email::getTableName();
$contactTable = CRM_Contact_BAO_Contact::getTableName();
$edTable = CRM_Mailing_Event_BAO_Delivered::getTableName();
$ebTable = CRM_Mailing_Event_BAO_Bounce::getTableName();
$query = " SELECT {$eqTable}.id,\n {$emailTable}.email as email,\n {$eqTable}.contact_id,\n {$eqTable}.hash\n FROM {$eqTable}\n INNER JOIN {$emailTable}\n ON {$eqTable}.email_id = {$emailTable}.id\n LEFT JOIN {$edTable}\n ON {$eqTable}.id = {$edTable}.event_queue_id\n LEFT JOIN {$ebTable}\n ON {$eqTable}.id = {$ebTable}.event_queue_id\n WHERE {$eqTable}.job_id = " . $this->id . "\n AND {$edTable}.id IS null\n AND {$ebTable}.id IS null";
$eq->query($query);
while ($eq->fetch()) {
/* Compose the mailing */
$recipient = null;
$message = $mailing->compose($this->id, $eq->id, $eq->hash, $eq->contact_id, $eq->email, $recipient);
/* Send the mailing */
$body = $message->get();
$headers = $message->headers();
/* TODO: when we separate the content generator from the delivery
* engine, maybe we should dump the messages into a table */
PEAR::setErrorHandling(PEAR_ERROR_CALLBACK, array('CRM_Mailing_BAO_Mailing', 'catchSMTP'));
$result = $mailer->send($recipient, $headers, $body);
CRM_Core_Error::setCallback();
$params = array('event_queue_id' => $eq->id, 'job_id' => $this->id, 'hash' => $eq->hash);
if (is_a($result, PEAR_Error)) {
/* Register the bounce event */
require_once 'CRM/Mailing/BAO/BouncePattern.php';
require_once 'CRM/Mailing/Event/BAO/Bounce.php';
$params = array_merge($params, CRM_Mailing_BAO_BouncePattern::match($result->getMessage()));
CRM_Mailing_Event_BAO_Bounce::create($params);
} else {
/* Register the delivery event */
CRM_Mailing_Event_BAO_Delivered::create($params);
}
}
}
public static function processMandrillCalls($reponse)
{
$events = array('open', 'click', 'hard_bounce', 'soft_bounce', 'spam', 'reject');
$bounceType = array();
//MTE-17
$config = CRM_Core_Config::singleton();
if (property_exists($config, 'civiVersion')) {
$civiVersion = $config->civiVersion;
} else {
$civiVersion = CRM_Core_BAO_Domain::version();
}
if (version_compare('4.4alpha1', $civiVersion) > 0) {
$jobCLassName = 'CRM_Mailing_DAO_Job';
} else {
$jobCLassName = 'CRM_Mailing_DAO_MailingJob';
}
foreach ($reponse as $value) {
//changes done to check if email exists in response array
if (in_array($value['event'], $events) && CRM_Utils_Array::value('email', $value['msg'])) {
$metaData = CRM_Utils_Array::value('metadata', $value['msg']) ? CRM_Utils_Array::value('CiviCRM_Mandrill_id', $value['msg']['metadata']) : NULL;
$header = self::extractHeader($metaData);
$mail = self::getMailing($header, $jobCLassName);
$contacts = array();
if ($mail->find(TRUE)) {
if ($value['event'] == 'click' && $mail->url_tracking === FALSE || $value['event'] == 'open' && $mail->open_tracking === FALSE) {
continue;
}
$emails = self::retrieveEmailContactId($value['msg']['email']);
if (!CRM_Utils_Array::value('contact_id', $emails['email'])) {
continue;
}
$value['mailing_id'] = $mail->id;
// IF no activity id in header then create new activity
if (empty($header[0])) {
self::createActivity($value, NULL, $header);
}
if (empty($header[2])) {
$params = array('job_id' => CRM_Core_DAO::getFieldValue($jobCLassName, $mail->id, 'id', 'mailing_id'), 'contact_id' => $emails['email']['contact_id'], 'email_id' => $emails['email']['id']);
$eventQueue = CRM_Mailing_Event_BAO_Queue::create($params);
$eventQueueID = $eventQueue->id;
$hash = $eventQueue->hash;
$jobId = $params['job_id'];
} else {
$eventQueueID = $header[3];
$hash = explode('@', $header[4]);
$hash = $hash[0];
$jobId = $header[2];
}
if ($eventQueueID) {
$mandrillActivtyParams = array('mailing_queue_id' => $eventQueueID, 'activity_id' => $header[0]);
CRM_Mte_BAO_MandrillActivity::create($mandrillActivtyParams);
}
$msgBody = '';
if (!empty($header[0])) {
$msgBody = CRM_Core_DAO::getFieldValue('CRM_Activity_DAO_Activity', $header[0], 'details');
}
$value['mail_body'] = $msgBody;
$bType = ucfirst(preg_replace('/_\\w+/', '', $value['event']));
$assignedContacts = array();
switch ($value['event']) {
case 'open':
$oe = new CRM_Mailing_Event_BAO_Opened();
$oe->event_queue_id = $eventQueueID;
$oe->time_stamp = date('YmdHis', $value['ts']);
$oe->save();
break;
case 'click':
if (CRM_Utils_Array::value(1, $header) == 'b') {
break;
}
$tracker = new CRM_Mailing_BAO_TrackableURL();
$tracker->url = $value['url'];
$tracker->mailing_id = $mail->id;
if (!$tracker->find(TRUE)) {
$tracker->save();
}
$open = new CRM_Mailing_Event_BAO_TrackableURLOpen();
$open->event_queue_id = $eventQueueID;
$open->trackable_url_id = $tracker->id;
$open->time_stamp = date('YmdHis', $value['ts']);
$open->save();
break;
case 'hard_bounce':
case 'soft_bounce':
case 'spam':
case 'reject':
if (empty($bounceType)) {
CRM_Core_PseudoConstant::populate($bounceType, 'CRM_Mailing_DAO_BounceType', TRUE, 'id', NULL, NULL, NULL, 'name');
}
//Delete queue in delivered since this email is not successfull
$delivered = new CRM_Mailing_Event_BAO_Delivered();
$delivered->event_queue_id = $eventQueueID;
if ($delivered->find(TRUE)) {
$delivered->delete();
}
$bounceParams = array('time_stamp' => date('YmdHis', $value['ts']), 'event_queue_id' => $eventQueueID, 'bounce_type_id' => $bounceType["Mandrill {$bType}"], 'job_id' => $jobId, 'hash' => $hash);
$bounceParams['bounce_reason'] = CRM_Utils_Array::value('bounce_description', $value['msg']);
if (empty($bounceParams['bounce_reason'])) {
$bounceParams['bounce_reason'] = CRM_Core_DAO::getFieldValue('CRM_Mailing_DAO_BounceType', $bounceType["Mandrill {$bType}"], 'description');
}
CRM_Mailing_Event_BAO_Bounce::create($bounceParams);
if (substr($value['event'], -7) == '_bounce') {
$mailingBackend = CRM_Core_BAO_Setting::getItem(CRM_Core_BAO_Setting::MAILING_PREFERENCES_NAME, 'mandrill_smtp_settings');
if (CRM_Utils_Array::value('group_id', $mailingBackend)) {
list($domainEmailName, $domainEmailAddress) = CRM_Core_BAO_Domain::getNameAndEmail();
$mailBody = ts('The following email failed to be delivered due to a') . " {$bType} Bounce :</br>\nTo: {$value['msg']['email']} </br>\nFrom: {$value['msg']['sender']} </br>\nSubject: {$value['msg']['subject']}</br>\nMessage Body: {$msgBody}";
$mailParams = array('groupName' => 'Mandrill bounce notification', 'from' => '"' . $domainEmailName . '" <' . $domainEmailAddress . '>', 'subject' => ts('Mandrill Bounce Notification'), 'text' => $mailBody, 'html' => $mailBody);
$query = "SELECT ce.email, cc.sort_name, cgc.contact_id FROM civicrm_contact cc\nINNER JOIN civicrm_group_contact cgc ON cgc.contact_id = cc.id\nINNER JOIN civicrm_email ce ON ce.contact_id = cc.id\nWHERE cc.is_deleted = 0 AND cc.is_deceased = 0 AND cgc.group_id = {$mailingBackend['group_id']} AND ce.is_primary = 1 AND ce.email <> %1";
$queryParam = array(1 => array($value['msg']['email'], 'String'));
$dao = CRM_Core_DAO::executeQuery($query, $queryParam);
while ($dao->fetch()) {
$mailParams['toName'] = $dao->sort_name;
$mailParams['toEmail'] = $dao->email;
CRM_Utils_Mail::send($mailParams);
$value['assignee_contact_id'][] = $dao->contact_id;
}
}
}
$bType = 'Bounce';
break;
}
// create activity for click and open event
if ($value['event'] == 'open' || $value['event'] == 'click' || $bType == 'Bounce') {
self::createActivity($value, $bType, $header);
}
}
}
}
}
/**
* returns all the rows in the given offset and rowCount
*
* @param enum $action the action being performed
* @param int $offset the row number to start from
* @param int $rowCount the number of rows to return
* @param string $sort the sql string that describes the sort order
* @param enum $output what should the result set include (web/email/csv)
*
* @return int the total number of rows for this action
*/
function &getRows($action, $offset, $rowCount, $sort, $output = null)
{
switch ($this->_event_type) {
case 'queue':
require_once 'CRM/Mailing/Event/BAO/Queue.php';
return CRM_Mailing_Event_BAO_Queue::getRows($this->_mailing_id, $this->_job_id, $offset, $rowCount, $sort);
break;
case 'delivered':
require_once 'CRM/Mailing/Event/BAO/Delivered.php';
return CRM_Mailing_Event_BAO_Delivered::getRows($this->_mailing_id, $this->_job_id, $this->_is_distinct, $offset, $rowCount, $sort);
break;
case 'opened':
require_once 'CRM/Mailing/Event/BAO/Opened.php';
return CRM_Mailing_Event_BAO_Opened::getRows($this->_mailing_id, $this->_job_id, $this->_is_distinct, $offset, $rowCount, $sort);
break;
case 'bounce':
require_once 'CRM/Mailing/Event/BAO/Bounce.php';
return CRM_Mailing_Event_BAO_Bounce::getRows($this->_mailing_id, $this->_job_id, $this->_is_distinct, $offset, $rowCount, $sort);
break;
case 'forward':
require_once 'CRM/Mailing/Event/BAO/Forward.php';
return CRM_Mailing_Event_BAO_Forward::getRows($this->_mailing_id, $this->_job_id, $this->_is_distinct, $offset, $rowCount, $sort);
case 'reply':
require_once 'CRM/Mailing/Event/BAO/Reply.php';
return CRM_Mailing_Event_BAO_Reply::getRows($this->_mailing_id, $this->_job_id, $this->_is_distinct, $offset, $rowCount, $sort);
break;
case 'unsubscribe':
require_once 'CRM/Mailing/Event/BAO/Unsubscribe.php';
return CRM_Mailing_Event_BAO_Unsubscribe::getRows($this->_mailing_id, $this->_job_id, $this->_is_distinct, $offset, $rowCount, $sort);
break;
case 'click':
require_once 'CRM/Mailing/Event/BAO/TrackableURLOpen.php';
return CRM_Mailing_Event_BAO_TrackableURLOpen::getRows($this->_mailing_id, $this->_job_id, $this->_is_distinct, $this->_url_id, $offset, $rowCount, $sort);
break;
default:
return null;
}
}
public function deliverGroup(&$fields, &$mailing, &$mailer, &$job_date, &$attachments)
{
// get the return properties
$returnProperties = $mailing->getReturnProperties();
$params = array();
$targetParams = array();
foreach ($fields as $key => $field) {
$params[] = $field['contact_id'];
}
$details = $mailing->getDetails($params, $returnProperties);
foreach ($fields as $key => $field) {
$contactID = $field['contact_id'];
/* Compose the mailing */
$recipient = null;
$message =& $mailing->compose($this->id, $field['id'], $field['hash'], $field['contact_id'], $field['email'], $recipient, false, $details[0][$contactID], $attachments);
/* Send the mailing */
$body =& $message->get();
$headers =& $message->headers();
// make $recipient actually be the *encoded* header, so as not to baffle Mail_RFC822, CRM-5743
$recipient = $headers['To'];
$result = null;
/* TODO: when we separate the content generator from the delivery
* engine, maybe we should dump the messages into a table */
// disable error reporting on real mailings (but leave error reporting for tests), CRM-5744
if ($job_date) {
CRM_Core_Error::ignoreException();
}
if (is_object($mailer)) {
// hack to stop mailing job at run time, CRM-4246.
$mailingJob = new CRM_Mailing_DAO_Job();
$mailingJob->mailing_id = $mailing->id;
if ($mailingJob->find(true)) {
// mailing have been canceled at run time.
if ($mailingJob->status == 'Canceled') {
return false;
}
} else {
// mailing have been deleted at run time.
return false;
}
$mailingJob->free();
$result = $mailer->send($recipient, $headers, $body, $this->id);
CRM_Core_Error::setCallback();
}
$params = array('event_queue_id' => $field['id'], 'job_id' => $this->id, 'hash' => $field['hash']);
if (is_a($result, 'PEAR_Error')) {
/* Register the bounce event */
require_once 'CRM/Mailing/BAO/BouncePattern.php';
require_once 'CRM/Mailing/Event/BAO/Bounce.php';
$params = array_merge($params, CRM_Mailing_BAO_BouncePattern::match($result->getMessage()));
CRM_Mailing_Event_BAO_Bounce::create($params);
} else {
/* Register the delivery event */
CRM_Mailing_Event_BAO_Delivered::create($params);
}
$targetParams[] = $field['contact_id'];
unset($result);
}
if (!empty($targetParams)) {
// add activity record for every mail that is send
$activityTypeID = CRM_Core_OptionGroup::getValue('activity_type', 'Bulk Email', 'name');
$activity = array('source_contact_id' => $mailing->scheduled_id, 'target_contact_id' => $targetParams, 'activity_type_id' => $activityTypeID, 'source_record_id' => $this->mailing_id, 'activity_date_time' => $job_date, 'subject' => $mailing->subject, 'status_id' => 2, 'deleteActivityTarget' => false);
//check whether activity is already created for this mailing.
//if yes then create only target contact record.
$query = "\nSELECT id \nFROM civicrm_activity\nWHERE civicrm_activity.activity_type_id = %1\nAND civicrm_activity.source_record_id = %2";
$queryParams = array(1 => array($activityTypeID, 'Integer'), 2 => array($this->mailing_id, 'Integer'));
$activityID = CRM_Core_DAO::singleValueQuery($query, $queryParams);
if ($activityID) {
$activity['id'] = $activityID;
}
require_once 'api/v2/Activity.php';
if (is_a(civicrm_activity_create($activity, 'Email'), 'CRM_Core_Error')) {
return false;
}
}
return true;
}
/**
* Create a new forward event, create a new contact if necessary
*
* @param $job_id
* @param $queue_id
* @param $hash
* @param $forward_email
* @param null $fromEmail
* @param null $comment
*
* @return bool
*/
public static function &forward($job_id, $queue_id, $hash, $forward_email, $fromEmail = NULL, $comment = NULL)
{
$q = CRM_Mailing_Event_BAO_Queue::verify($job_id, $queue_id, $hash);
$successfulForward = FALSE;
$contact_id = NULL;
if (!$q) {
return $successfulForward;
}
/* Find the email address/contact, if it exists */
$contact = CRM_Contact_BAO_Contact::getTableName();
$location = CRM_Core_BAO_Location::getTableName();
$email = CRM_Core_BAO_Email::getTableName();
$queueTable = CRM_Mailing_Event_BAO_Queue::getTableName();
$job = CRM_Mailing_BAO_MailingJob::getTableName();
$mailing = CRM_Mailing_BAO_Mailing::getTableName();
$forward = self::getTableName();
$domain = CRM_Core_BAO_Domain::getDomain();
$dao = new CRM_Core_Dao();
$dao->query("\n SELECT {$contact}.id as contact_id,\n {$email}.id as email_id,\n {$contact}.do_not_email as do_not_email,\n {$queueTable}.id as queue_id\n FROM ({$email}, {$job} as temp_job)\n INNER JOIN {$contact}\n ON {$email}.contact_id = {$contact}.id\n LEFT JOIN {$queueTable}\n ON {$email}.id = {$queueTable}.email_id\n LEFT JOIN {$job}\n ON {$queueTable}.job_id = {$job}.id\n AND temp_job.mailing_id = {$job}.mailing_id\n WHERE {$queueTable}.job_id = {$job_id}\n AND {$email}.email = '" . CRM_Utils_Type::escape($forward_email, 'String') . "'");
$dao->fetch();
$transaction = new CRM_Core_Transaction();
if (isset($dao->queue_id) || isset($dao->do_not_email) && $dao->do_not_email == 1) {
/* We already sent this mailing to $forward_email, or we should
* never email this contact. Give up. */
return $successfulForward;
}
require_once 'api/api.php';
$contactParams = array('email' => $forward_email, 'version' => 3);
$contactValues = civicrm_api('contact', 'get', $contactParams);
$count = $contactValues['count'];
if ($count == 0) {
/* If the contact does not exist, create one. */
$formatted = array('contact_type' => 'Individual', 'version' => 3);
$locationType = CRM_Core_BAO_LocationType::getDefault();
$value = array('email' => $forward_email, 'location_type_id' => $locationType->id);
require_once 'CRM/Utils/DeprecatedUtils.php';
_civicrm_api3_deprecated_add_formatted_param($value, $formatted);
$formatted['onDuplicate'] = CRM_Import_Parser::DUPLICATE_SKIP;
$formatted['fixAddress'] = TRUE;
$contact = civicrm_api('contact', 'create', $formatted);
if (civicrm_error($contact)) {
return $successfulForward;
}
$contact_id = $contact['id'];
}
$email = new CRM_Core_DAO_Email();
$email->email = $forward_email;
$email->find(TRUE);
$email_id = $email->id;
if (!$contact_id) {
$contact_id = $email->contact_id;
}
/* Create a new queue event */
$queue_params = array('email_id' => $email_id, 'contact_id' => $contact_id, 'job_id' => $job_id);
$queue = CRM_Mailing_Event_BAO_Queue::create($queue_params);
$forward = new CRM_Mailing_Event_BAO_Forward();
$forward->time_stamp = date('YmdHis');
$forward->event_queue_id = $queue_id;
$forward->dest_queue_id = $queue->id;
$forward->save();
$dao->reset();
$dao->query(" SELECT {$job}.mailing_id as mailing_id\n FROM {$job}\n WHERE {$job}.id = " . CRM_Utils_Type::escape($job_id, 'Integer'));
$dao->fetch();
$mailing_obj = new CRM_Mailing_BAO_Mailing();
$mailing_obj->id = $dao->mailing_id;
$mailing_obj->find(TRUE);
$config = CRM_Core_Config::singleton();
$mailer = $config->getMailer();
$recipient = NULL;
$attachments = NULL;
$message = $mailing_obj->compose($job_id, $queue->id, $queue->hash, $queue->contact_id, $forward_email, $recipient, FALSE, NULL, $attachments, TRUE, $fromEmail);
//append comment if added while forwarding.
if (count($comment)) {
$message->_txtbody = CRM_Utils_Array::value('body_text', $comment) . $message->_txtbody;
if (!empty($comment['body_html'])) {
$message->_htmlbody = $comment['body_html'] . '<br />---------------Original message---------------------<br />' . $message->_htmlbody;
}
}
$body = $message->get();
$headers = $message->headers();
$result = NULL;
if (is_object($mailer)) {
$errorScope = CRM_Core_TemporaryErrorScope::ignoreException();
$result = $mailer->send($recipient, $headers, $body);
unset($errorScope);
}
$params = array('event_queue_id' => $queue->id, 'job_id' => $job_id, 'hash' => $queue->hash);
if (is_a($result, 'PEAR_Error')) {
/* Register the bounce event */
$params = array_merge($params, CRM_Mailing_BAO_BouncePattern::match($result->getMessage()));
CRM_Mailing_Event_BAO_Bounce::create($params);
} else {
$successfulForward = TRUE;
/* Register the delivery event */
CRM_Mailing_Event_BAO_Delivered::create($params);
}
$transaction->commit();
return $successfulForward;
}
/**
* Generate a report. Fetch event count information, mailing data, and job
* status.
*
* @param int $id The mailing id to report
* @param boolean $skipDetails whether return all detailed report
* @return array Associative array of reporting data
* @access public
* @static
*/
public static function &report($id, $skipDetails = false)
{
$mailing_id = CRM_Utils_Type::escape($id, 'Integer');
$mailing = new CRM_Mailing_BAO_Mailing();
require_once 'CRM/Mailing/Event/BAO/Opened.php';
require_once 'CRM/Mailing/Event/BAO/Reply.php';
require_once 'CRM/Mailing/Event/BAO/Unsubscribe.php';
require_once 'CRM/Mailing/Event/BAO/Forward.php';
require_once 'CRM/Mailing/Event/BAO/TrackableURLOpen.php';
require_once 'CRM/Mailing/BAO/Spool.php';
$t = array('mailing' => self::getTableName(), 'mailing_group' => CRM_Mailing_DAO_Group::getTableName(), 'group' => CRM_Contact_BAO_Group::getTableName(), 'job' => CRM_Mailing_BAO_Job::getTableName(), 'queue' => CRM_Mailing_Event_BAO_Queue::getTableName(), 'delivered' => CRM_Mailing_Event_BAO_Delivered::getTableName(), 'opened' => CRM_Mailing_Event_BAO_Opened::getTableName(), 'reply' => CRM_Mailing_Event_BAO_Reply::getTableName(), 'unsubscribe' => CRM_Mailing_Event_BAO_Unsubscribe::getTableName(), 'bounce' => CRM_Mailing_Event_BAO_Bounce::getTableName(), 'forward' => CRM_Mailing_Event_BAO_Forward::getTableName(), 'url' => CRM_Mailing_BAO_TrackableURL::getTableName(), 'urlopen' => CRM_Mailing_Event_BAO_TrackableURLOpen::getTableName(), 'component' => CRM_Mailing_BAO_Component::getTableName(), 'spool' => CRM_Mailing_BAO_Spool::getTableName());
$report = array();
/* Get the mailing info */
$mailing->query("\n SELECT {$t['mailing']}.*\n FROM {$t['mailing']}\n WHERE {$t['mailing']}.id = {$mailing_id}");
$mailing->fetch();
$report['mailing'] = array();
foreach (array_keys(self::fields()) as $field) {
$report['mailing'][$field] = $mailing->{$field};
}
//mailing report is called by activity
//we dont need all detail report
if ($skipDetails) {
return $report;
}
/* Get the component info */
$query = array();
$components = array('header' => ts('Header'), 'footer' => ts('Footer'), 'reply' => ts('Reply'), 'unsubscribe' => ts('Unsubscribe'), 'optout' => ts('Opt-Out'));
foreach (array_keys($components) as $type) {
$query[] = "SELECT {$t['component']}.name as name,\n '{$type}' as type,\n {$t['component']}.id as id\n FROM {$t['component']}\n INNER JOIN {$t['mailing']}\n ON {$t['mailing']}.{$type}_id =\n {$t['component']}.id\n WHERE {$t['mailing']}.id = {$mailing_id}";
}
$q = '(' . implode(') UNION (', $query) . ')';
$mailing->query($q);
$report['component'] = array();
while ($mailing->fetch()) {
$report['component'][] = array('type' => $components[$mailing->type], 'name' => $mailing->name, 'link' => CRM_Utils_System::url('civicrm/mailing/component', "reset=1&action=update&id={$mailing->id}"));
}
/* Get the recipient group info */
$mailing->query("\n SELECT {$t['mailing_group']}.group_type as group_type,\n {$t['group']}.id as group_id,\n {$t['group']}.title as group_title,\n {$t['mailing']}.id as mailing_id,\n {$t['mailing']}.name as mailing_name\n FROM {$t['mailing_group']}\n LEFT JOIN {$t['group']}\n ON {$t['mailing_group']}.entity_id = {$t['group']}.id\n AND {$t['mailing_group']}.entity_table =\n '{$t['group']}'\n LEFT JOIN {$t['mailing']}\n ON {$t['mailing_group']}.entity_id =\n {$t['mailing']}.id\n AND {$t['mailing_group']}.entity_table =\n '{$t['mailing']}'\n\n WHERE {$t['mailing_group']}.mailing_id = {$mailing_id}\n ");
$report['group'] = array('include' => array(), 'exclude' => array());
while ($mailing->fetch()) {
$row = array();
if (isset($mailing->group_id)) {
$row['id'] = $mailing->group_id;
$row['name'] = $mailing->group_title;
$row['link'] = CRM_Utils_System::url('civicrm/group/search', "reset=1&force=1&context=smog&gid={$row['id']}");
} else {
$row['id'] = $mailing->mailing_id;
$row['name'] = $mailing->mailing_name;
$row['mailing'] = true;
$row['link'] = CRM_Utils_System::url('civicrm/mailing/report', "mid={$row['id']}");
}
if ($mailing->group_type == 'Include') {
$report['group']['include'][] = $row;
} else {
$report['group']['exclude'][] = $row;
}
}
/* Get the event totals, grouped by job (retries) */
$mailing->query("\n SELECT {$t['job']}.*,\n COUNT(DISTINCT {$t['queue']}.id) as queue,\n COUNT(DISTINCT {$t['delivered']}.id) as delivered,\n COUNT(DISTINCT {$t['reply']}.id) as reply,\n COUNT(DISTINCT {$t['forward']}.id) as forward,\n COUNT(DISTINCT {$t['bounce']}.id) as bounce,\n COUNT(DISTINCT {$t['urlopen']}.id) as url,\n COUNT(DISTINCT {$t['spool']}.id) as spool\n FROM {$t['job']}\n LEFT JOIN {$t['queue']}\n ON {$t['queue']}.job_id = {$t['job']}.id\n LEFT JOIN {$t['reply']}\n ON {$t['reply']}.event_queue_id = {$t['queue']}.id\n LEFT JOIN {$t['forward']}\n ON {$t['forward']}.event_queue_id = {$t['queue']}.id\n LEFT JOIN {$t['bounce']}\n ON {$t['bounce']}.event_queue_id = {$t['queue']}.id\n LEFT JOIN {$t['delivered']}\n ON {$t['delivered']}.event_queue_id = {$t['queue']}.id\n AND {$t['bounce']}.id IS null\n LEFT JOIN {$t['urlopen']}\n ON {$t['urlopen']}.event_queue_id = {$t['queue']}.id\n LEFT JOIN {$t['spool']}\n ON {$t['spool']}.job_id = {$t['job']}.id\n WHERE {$t['job']}.mailing_id = {$mailing_id}\n AND {$t['job']}.is_test = 0\n GROUP BY {$t['job']}.id");
$report['jobs'] = array();
$report['event_totals'] = array();
$elements = array('queue', 'delivered', 'url', 'forward', 'reply', 'unsubscribe', 'opened', 'bounce', 'spool');
// initialize various counters
foreach ($elements as $field) {
$report['event_totals'][$field] = 0;
}
while ($mailing->fetch()) {
$row = array();
foreach ($elements as $field) {
if (isset($mailing->{$field})) {
$row[$field] = $mailing->{$field};
$report['event_totals'][$field] += $mailing->{$field};
}
}
// compute open total separately to discount duplicates
// CRM-1258
$row['opened'] = CRM_Mailing_Event_BAO_Opened::getTotalCount($mailing_id, $mailing->id, true);
$report['event_totals']['opened'] += $row['opened'];
// compute unsub total separately to discount duplicates
// CRM-1783
$row['unsubscribe'] = CRM_Mailing_Event_BAO_Unsubscribe::getTotalCount($mailing_id, $mailing->id, true);
$report['event_totals']['unsubscribe'] += $row['unsubscribe'];
foreach (array_keys(CRM_Mailing_BAO_Job::fields()) as $field) {
$row[$field] = $mailing->{$field};
}
if ($mailing->queue) {
$row['delivered_rate'] = 100.0 * $mailing->delivered / $mailing->queue;
$row['bounce_rate'] = 100.0 * $mailing->bounce / $mailing->queue;
$row['unsubscribe_rate'] = 100.0 * $row['unsubscribe'] / $mailing->queue;
} else {
$row['delivered_rate'] = 0;
$row['bounce_rate'] = 0;
$row['unsubscribe_rate'] = 0;
}
$row['links'] = array('clicks' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=click&mid={$mailing_id}&jid={$mailing->id}"), 'queue' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=queue&mid={$mailing_id}&jid={$mailing->id}"), 'delivered' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=delivered&mid={$mailing_id}&jid={$mailing->id}"), 'bounce' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=bounce&mid={$mailing_id}&jid={$mailing->id}"), 'unsubscribe' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=unsubscribe&mid={$mailing_id}&jid={$mailing->id}"), 'forward' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=forward&mid={$mailing_id}&jid={$mailing->id}"), 'reply' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=reply&mid={$mailing_id}&jid={$mailing->id}"), 'opened' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=opened&mid={$mailing_id}&jid={$mailing->id}"));
foreach (array('scheduled_date', 'start_date', 'end_date') as $key) {
$row[$key] = CRM_Utils_Date::customFormat($row[$key]);
}
$report['jobs'][] = $row;
}
$report['event_totals']['queue'] = self::getRecipientsCount($mailing_id, false, $mailing_id);
if (CRM_Utils_Array::value('queue', $report['event_totals'])) {
$report['event_totals']['delivered_rate'] = 100.0 * $report['event_totals']['delivered'] / $report['event_totals']['queue'];
$report['event_totals']['bounce_rate'] = 100.0 * $report['event_totals']['bounce'] / $report['event_totals']['queue'];
$report['event_totals']['unsubscribe_rate'] = 100.0 * $report['event_totals']['unsubscribe'] / $report['event_totals']['queue'];
} else {
$report['event_totals']['delivered_rate'] = 0;
$report['event_totals']['bounce_rate'] = 0;
$report['event_totals']['unsubscribe_rate'] = 0;
}
/* Get the click-through totals, grouped by URL */
$mailing->query("\n SELECT {$t['url']}.url,\n {$t['url']}.id,\n COUNT({$t['urlopen']}.id) as clicks,\n COUNT(DISTINCT {$t['queue']}.id) as unique_clicks\n FROM {$t['url']}\n LEFT JOIN {$t['urlopen']}\n ON {$t['urlopen']}.trackable_url_id = {$t['url']}.id\n LEFT JOIN {$t['queue']}\n ON {$t['urlopen']}.event_queue_id = {$t['queue']}.id\n LEFT JOIN {$t['job']}\n ON {$t['queue']}.job_id = {$t['job']}.id\n WHERE {$t['url']}.mailing_id = {$mailing_id}\n AND {$t['job']}.is_test = 0\n GROUP BY {$t['url']}.id");
$report['click_through'] = array();
while ($mailing->fetch()) {
$report['click_through'][] = array('url' => $mailing->url, 'link' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=click&mid={$mailing_id}&uid={$mailing->id}"), 'link_unique' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=click&mid={$mailing_id}&uid={$mailing->id}&distinct=1"), 'clicks' => $mailing->clicks, 'unique' => $mailing->unique_clicks, 'rate' => CRM_Utils_Array::value('delivered', $report['event_totals']) ? 100.0 * $mailing->unique_clicks / $report['event_totals']['delivered'] : 0);
}
$report['event_totals']['links'] = array('clicks' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=click&mid={$mailing_id}"), 'clicks_unique' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=click&mid={$mailing_id}&distinct=1"), 'queue' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=queue&mid={$mailing_id}"), 'delivered' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=delivered&mid={$mailing_id}"), 'bounce' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=bounce&mid={$mailing_id}"), 'unsubscribe' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=unsubscribe&mid={$mailing_id}"), 'forward' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=forward&mid={$mailing_id}"), 'reply' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=reply&mid={$mailing_id}"), 'opened' => CRM_Utils_System::url('civicrm/mailing/report/event', "reset=1&event=opened&mid={$mailing_id}"));
return $report;
}
/**
* Implementation of hook_civicrm_postEmailSend
*/
function mte_civicrm_postEmailSend(&$params)
{
if (!empty($params['mandrillHeader'])) {
$header = explode(CRM_Core_Config::singleton()->verpSeparator, $params['mandrillHeader']);
$params = array('job_id' => $header[2], 'event_queue_id' => $header[3], 'hash' => $header[4]);
CRM_Mailing_Event_BAO_Delivered::create($params);
$mandrillActivtyParams = array('mailing_queue_id' => $header[3], 'activity_id' => $header[0]);
CRM_Mte_BAO_MandrillActivity::create($mandrillActivtyParams);
}
}
/**
* Create a new forward event, create a new contact if necessary
*/
function &forward($job_id, $queue_id, $hash, $forward_email)
{
$q =& CRM_Mailing_Event_BAO_Queue::verify($job_id, $queue_id, $hash);
if (!$q) {
return null;
}
/* Find the email address/contact, if it exists */
$contact = CRM_Contact_BAO_Contact::getTableName();
$location = CRM_Core_BAO_Location::getTableName();
$email = CRM_Core_BAO_Email::getTableName();
$queueTable = CRM_Mailing_Event_BAO_Queue::getTableName();
$job = CRM_Mailing_BAO_Job::getTableName();
$mailing = CRM_Mailing_BAO_Mailing::getTableName();
$forward = CRM_Mailing_Event_BAO_Forward::getTableName();
$domain =& CRM_Mailing_Event_BAO_Queue::getDomain($queue_id);
$dao =& new CRM_Core_Dao();
$dao->query("\n SELECT {$contact}.id as contact_id,\n {$email}.id as email_id,\n {$contact}.do_not_email as do_not_email,\n {$queueTable}.id as queue_id\n FROM {$email}, {$job} as temp_job\n INNER JOIN {$location}\n ON {$email}.location_id = {$location}.id\n INNER JOIN {$contact}\n ON {$location}.entity_table = '{$contact}'\n AND {$location}.entity_id = {$contact}.id\n LEFT JOIN {$queueTable}\n ON {$email}.id = {$queueTable}.email_id\n LEFT JOIN {$job}\n ON {$queueTable}.job_id = {$job}.id\n AND temp_job.mailing_id = {$job}.mailing_id\n WHERE temp_job.id = {$job_id}\n AND {$email}.email = '" . CRM_Utils_Type::escape($forward_email, 'String') . "'");
$dao->fetch();
CRM_Core_DAO::transaction('BEGIN');
if (isset($dao->queue_id) || $dao->do_not_email == 1) {
/* We already sent this mailing to $forward_email, or we should
* never email this contact. Give up. */
return false;
} elseif (empty($dao->contact_id)) {
/* No contact found, we'll have to create a new one */
$contact_params = array('email' => $forward_email);
$contact =& crm_create_contact($contact_params);
if (is_a($contact, 'CRM_Core_Error')) {
return false;
}
/* This is an ugly hack, but the API doesn't really support
* overriding the domain ID any other way */
$contact->domain_id = $domain->id;
$contact->save();
$contact_id = $contact->id;
$email_id = $contact->location[1]->email[1]->id;
} else {
$contact_id = $dao->contact_id;
$email_id = $dao->email_id;
}
/* Create a new queue event */
$queue_params = array('email_id' => $email_id, 'contact_id' => $contact_id, 'job_id' => $job_id);
$queue =& CRM_Mailing_Event_BAO_Queue::create($queue_params);
$forward =& new CRM_Mailing_Event_BAO_Forward();
$forward->time_stamp = date('YmdHis');
$forward->event_queue_id = $queue_id;
$forward->dest_queue_id = $queue->id;
$forward->save();
$dao->reset();
$dao->query(" SELECT {$job}.mailing_id as mailing_id \n FROM {$job}\n WHERE {$job}.id = " . CRM_Utils_Type::escape($job_id, 'Integer'));
$dao->fetch();
$mailing_obj =& new CRM_Mailing_BAO_Mailing();
$mailing_obj->id = $dao->mailing_id;
$mailing_obj->find(true);
$config =& CRM_Core_Config::singleton();
$mailer =& $config->getMailer();
$recipient = null;
$message =& $mailing_obj->compose($job_id, $queue->id, $queue->hash, $queue->contact_id, $forward_email, $recipient);
$body = $message->get();
$headers = $message->headers();
PEAR::setErrorHandling(PEAR_ERROR_CALLBACK, array('CRM_Mailing_BAO_Mailing', 'catchSMTP'));
$result = $mailer->send($recipient, $headers, $body);
CRM_Core_Error::setCallback();
$params = array('event_queue_id' => $queue->id, 'job_id' => $job_id, 'hash' => $queue->hash);
if (is_a($result, PEAR_Error)) {
/* Register the bounce event */
$params = array_merge($params, CRM_Mailing_BAO_BouncePattern::match($result->getMessage()));
CRM_Mailing_Event_BAO_Bounce::create($params);
} else {
/* Register the delivery event */
CRM_Mailing_Event_BAO_Delivered::create($params);
}
CRM_Core_DAO::transaction('COMMIT');
return true;
}
/**
* Get rows for the event browser
*
* @param int $mailing_id ID of the mailing
* @param int $job_id optional ID of the job
* @param boolean $is_distinct Group by queue id?
* @param int $offset Offset
* @param int $rowCount Number of rows
* @param array $sort sort array
* @return array Result set
* @access public
* @static
*/
function &getRows($mailing_id, $job_id = null, $is_distinct = false, $offset = null, $rowCount = null, $sort = null)
{
$dao =& new CRM_Core_Dao();
$delivered = CRM_Mailing_Event_BAO_Delivered::getTableName();
$bounce = CRM_Mailing_Event_BAO_Bounce::getTableName();
$queue = CRM_Mailing_Event_BAO_Queue::getTableName();
$mailing = CRM_Mailing_BAO_Mailing::getTableName();
$job = CRM_Mailing_BAO_Job::getTableName();
$contact = CRM_Contact_BAO_Contact::getTableName();
$email = CRM_Core_BAO_Email::getTableName();
$query = "\n SELECT {$contact}.display_name as display_name,\n {$contact}.id as contact_id,\n {$email}.email as email,\n {$delivered}.time_stamp as date\n FROM {$contact}\n INNER JOIN {$queue}\n ON {$queue}.contact_id = {$contact}.id\n INNER JOIN {$email}\n ON {$queue}.email_id = {$email}.id\n INNER JOIN {$delivered}\n ON {$delivered}.event_queue_id = {$queue}.id\n LEFT JOIN {$bounce}\n ON {$bounce}.event_queue_id = {$queue}.id\n INNER JOIN {$job}\n ON {$queue}.job_id = {$job}.id\n INNER JOIN {$mailing}\n ON {$job}.mailing_id = {$mailing}.id\n WHERE {$bounce}.id IS null\n AND {$mailing}.id = " . CRM_Utils_Type::escape($mailing_id, 'Integer');
if (!empty($job_id)) {
$query .= " AND {$job}.id = " . CRM_Utils_Type::escape($job_id, 'Integer');
}
if ($is_distinct) {
$query .= " GROUP BY {$queue}.id ";
}
$query .= " ORDER BY {$contact}.sort_name, {$delivered}.time_stamp ";
if ($offset) {
$query .= ' LIMIT ' . CRM_Utils_Type::escape($offset, 'Integer') . ', ' . CRM_Utils_Type::escape($rowCount, 'Integer');
}
$dao->query($query);
$results = array();
while ($dao->fetch()) {
$url = CRM_Utils_System::url('civicrm/contact/view', "reset=1&cid={$dao->contact_id}");
$results[] = array('name' => "<a href=\"{$url}\">{$dao->display_name}</a>", 'email' => $dao->email, 'date' => CRM_Utils_Date::customFormat($dao->date));
}
return $results;
}
