/**
* Register a reply event.
*
* @param int $job_id
* The job ID of the reply.
* @param int $queue_id
* The queue event id.
* @param string $hash
* The hash.
*
* @param null $replyto
*
* @return object|null
* The mailing object, or null on failure
*/
public static function &reply($job_id, $queue_id, $hash, $replyto = NULL)
{
/* First make sure there's a matching queue event */
$q = CRM_Mailing_Event_BAO_Queue::verify($job_id, $queue_id, $hash);
$success = NULL;
if (!$q) {
return $success;
}
$mailing = new CRM_Mailing_BAO_Mailing();
$mailings = CRM_Mailing_BAO_Mailing::getTableName();
$jobs = CRM_Mailing_BAO_MailingJob::getTableName();
$mailing->query("SELECT * FROM {$mailings}\n INNER JOIN {$jobs}\n ON {$jobs}.mailing_id = {$mailings}.id\n WHERE {$jobs}.id = {$q->job_id}");
$mailing->fetch();
if ($mailing->auto_responder) {
self::autoRespond($mailing, $queue_id, $replyto);
}
$re = new CRM_Mailing_Event_BAO_Reply();
$re->event_queue_id = $queue_id;
$re->time_stamp = date('YmdHis');
$re->save();
if (!$mailing->forward_replies || empty($mailing->replyto_email)) {
return $success;
}
return $mailing;
}
/**
* @return array
*/
public function mailing_select()
{
$data = array();
$mailing = new CRM_Mailing_BAO_Mailing();
$query = "SELECT name FROM civicrm_mailing WHERE sms_provider_id IS NULL";
$mailing->query($query);
while ($mailing->fetch()) {
$data[mysql_real_escape_string($mailing->name)] = $mailing->name;
}
return $data;
}
function mailing_select()
{
require_once 'CRM/Mailing/BAO/Mailing.php';
$data = array();
$mailing = new CRM_Mailing_BAO_Mailing();
$query = "SELECT name FROM civicrm_mailing ";
$mailing->query($query);
while ($mailing->fetch()) {
$data[$mailing->name] = $mailing->name;
}
return $data;
}
/**
* 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;
}
/**
* @return array
*/
function mailingList()
{
$data = array();
$mailing = new CRM_Mailing_BAO_Mailing();
$query = "SELECT name FROM civicrm_mailing ";
$mailing->query($query);
while ($mailing->fetch()) {
$data[mysql_real_escape_string($mailing->name)] = $mailing->name;
}
return $data;
}
/**
* Send a response email informing the contact of the groups from which he.
* has been unsubscribed.
*
* @param string $queue_id
* The queue event ID.
* @param array $groups
* List of group IDs.
* @param bool $is_domain
* Is this domain-level?.
* @param int $job
* The job ID.
*/
public static function send_unsub_response($queue_id, $groups, $is_domain = FALSE, $job)
{
$config = CRM_Core_Config::singleton();
$domain = CRM_Core_BAO_Domain::getDomain();
$jobObject = new CRM_Mailing_BAO_MailingJob();
$jobTable = $jobObject->getTableName();
$mailingObject = new CRM_Mailing_DAO_Mailing();
$mailingTable = $mailingObject->getTableName();
$contactsObject = new CRM_Contact_DAO_Contact();
$contacts = $contactsObject->getTableName();
$emailObject = new CRM_Core_DAO_Email();
$email = $emailObject->getTableName();
$queueObject = new CRM_Mailing_Event_BAO_Queue();
$queue = $queueObject->getTableName();
//get the default domain email address.
list($domainEmailName, $domainEmailAddress) = CRM_Core_BAO_Domain::getNameAndEmail();
$dao = new CRM_Mailing_BAO_Mailing();
$dao->query(" SELECT * FROM {$mailingTable}\n INNER JOIN {$jobTable} ON\n {$jobTable}.mailing_id = {$mailingTable}.id\n WHERE {$jobTable}.id = {$job}");
$dao->fetch();
$component = new CRM_Mailing_BAO_Component();
if ($is_domain) {
$component->id = $dao->optout_id;
} else {
$component->id = $dao->unsubscribe_id;
}
$component->find(TRUE);
$html = $component->body_html;
if ($component->body_text) {
$text = $component->body_text;
} else {
$text = CRM_Utils_String::htmlToText($component->body_html);
}
$eq = new CRM_Core_DAO();
$eq->query("SELECT {$contacts}.preferred_mail_format as format,\n {$contacts}.id as contact_id,\n {$email}.email as email,\n {$queue}.hash as hash\n FROM {$contacts}\n INNER JOIN {$queue} ON {$queue}.contact_id = {$contacts}.id\n INNER JOIN {$email} ON {$queue}.email_id = {$email}.id\n WHERE {$queue}.id = " . CRM_Utils_Type::escape($queue_id, 'Integer'));
$eq->fetch();
if ($groups) {
foreach ($groups as $key => $value) {
if (!$value) {
unset($groups[$key]);
}
}
}
$message = new Mail_mime("\n");
list($addresses, $urls) = CRM_Mailing_BAO_Mailing::getVerpAndUrls($job, $queue_id, $eq->hash, $eq->email);
$bao = new CRM_Mailing_BAO_Mailing();
$bao->body_text = $text;
$bao->body_html = $html;
$tokens = $bao->getTokens();
if ($eq->format == 'HTML' || $eq->format == 'Both') {
$html = CRM_Utils_Token::replaceDomainTokens($html, $domain, TRUE, $tokens['html']);
$html = CRM_Utils_Token::replaceUnsubscribeTokens($html, $domain, $groups, TRUE, $eq->contact_id, $eq->hash);
$html = CRM_Utils_Token::replaceActionTokens($html, $addresses, $urls, TRUE, $tokens['html']);
$html = CRM_Utils_Token::replaceMailingTokens($html, $dao, NULL, $tokens['html']);
$message->setHTMLBody($html);
}
if (!$html || $eq->format == 'Text' || $eq->format == 'Both') {
$text = CRM_Utils_Token::replaceDomainTokens($text, $domain, FALSE, $tokens['text']);
$text = CRM_Utils_Token::replaceUnsubscribeTokens($text, $domain, $groups, FALSE, $eq->contact_id, $eq->hash);
$text = CRM_Utils_Token::replaceActionTokens($text, $addresses, $urls, FALSE, $tokens['text']);
$text = CRM_Utils_Token::replaceMailingTokens($text, $dao, NULL, $tokens['text']);
$message->setTxtBody($text);
}
$emailDomain = CRM_Core_BAO_MailSettings::defaultDomain();
$headers = array('Subject' => $component->subject, 'From' => "\"{$domainEmailName}\" <do-not-reply@{$emailDomain}>", 'To' => $eq->email, 'Reply-To' => "do-not-reply@{$emailDomain}", 'Return-Path' => "do-not-reply@{$emailDomain}");
CRM_Mailing_BAO_Mailing::addMessageIdHeader($headers, 'u', $job, $queue_id, $eq->hash);
$b = CRM_Utils_Mail::setMimeParams($message);
$h = $message->headers($headers);
$mailer = \Civi::service('pear_mail');
if (is_object($mailer)) {
$errorScope = CRM_Core_TemporaryErrorScope::ignoreException();
$mailer->send($eq->email, $h, $b);
unset($errorScope);
}
}
/**
* Send a reponse email informing the contact of the groups to which he/she
* has been resubscribed.
*
* @param string $queue_id The queue event ID
* @param array $groups List of group IDs
* @param bool $is_domain Is this domain-level?
* @param int $job The job ID
* @return void
* @access public
* @static
*/
public static function send_resub_response($queue_id, $groups, $is_domain = false, $job)
{
// param is_domain is not supported as of now.
$config = CRM_Core_Config::singleton();
$domain =& CRM_Core_BAO_Domain::getDomain();
$jobTable = CRM_Mailing_BAO_Job::getTableName();
$mailingTable = CRM_Mailing_DAO_Mailing::getTableName();
$contacts = CRM_Contact_DAO_Contact::getTableName();
$email = CRM_Core_DAO_Email::getTableName();
$queue = CRM_Mailing_Event_BAO_Queue::getTableName();
//get the default domain email address.
list($domainEmailName, $domainEmailAddress) = CRM_Core_BAO_Domain::getNameAndEmail();
$dao = new CRM_Mailing_BAO_Mailing();
$dao->query(" SELECT * FROM {$mailingTable} \n INNER JOIN {$jobTable} ON\n {$jobTable}.mailing_id = {$mailingTable}.id \n WHERE {$jobTable}.id = {$job}");
$dao->fetch();
$component = new CRM_Mailing_BAO_Component();
$component->id = $dao->resubscribe_id;
$component->find(true);
$html = $component->body_html;
if ($component->body_text) {
$text = $component->body_text;
} else {
$text = CRM_Utils_String::htmlToText($component->body_html);
}
$eq = new CRM_Core_DAO();
$eq->query("SELECT {$contacts}.preferred_mail_format as format,\n {$contacts}.id as contact_id,\n {$email}.email as email,\n {$queue}.hash as hash\n FROM {$contacts}\n INNER JOIN {$queue} ON {$queue}.contact_id = {$contacts}.id\n INNER JOIN {$email} ON {$queue}.email_id = {$email}.id\n WHERE {$queue}.id = " . CRM_Utils_Type::escape($queue_id, 'Integer'));
$eq->fetch();
foreach ($groups as $key => $value) {
if (!$value) {
unset($groups[$key]);
}
}
$message = new Mail_mime("\n");
list($addresses, $urls) = CRM_Mailing_BAO_Mailing::getVerpAndUrls($job, $queue_id, $eq->hash, $eq->email);
$bao = new CRM_Mailing_BAO_Mailing();
$bao->body_text = $text;
$bao->body_html = $html;
$tokens = $bao->getTokens();
require_once 'CRM/Utils/Token.php';
if ($eq->format == 'HTML' || $eq->format == 'Both') {
$html = CRM_Utils_Token::replaceDomainTokens($html, $domain, true, $tokens['html']);
$html = CRM_Utils_Token::replaceResubscribeTokens($html, $domain, $groups, true, $eq->contact_id, $eq->hash);
$html = CRM_Utils_Token::replaceActionTokens($html, $addresses, $urls, true, $tokens['html']);
$html = CRM_Utils_Token::replaceMailingTokens($html, $dao, null, $tokens['html']);
$message->setHTMLBody($html);
}
if (!$html || $eq->format == 'Text' || $eq->format == 'Both') {
$text = CRM_Utils_Token::replaceDomainTokens($html, $domain, true, $tokens['text']);
$text = CRM_Utils_Token::replaceResubscribeTokens($text, $domain, $groups, false, $eq->contact_id, $eq->hash);
$text = CRM_Utils_Token::replaceActionTokens($text, $addresses, $urls, false, $tokens['text']);
$text = CRM_Utils_Token::replaceMailingTokens($text, $dao, null, $tokens['text']);
$message->setTxtBody($text);
}
require_once 'CRM/Core/BAO/MailSettings.php';
$emailDomain = CRM_Core_BAO_MailSettings::defaultDomain();
$headers = array('Subject' => $component->subject, 'From' => "\"{$domainEmailName}\" <do-not-reply@{$emailDomain}>", 'To' => $eq->email, 'Reply-To' => "do-not-reply@{$emailDomain}", 'Return-Path' => "do-not-reply@{$emailDomain}");
$b =& CRM_Utils_Mail::setMimeParams($message);
$h =& $message->headers($headers);
$mailer =& $config->getMailer();
PEAR::setErrorHandling(PEAR_ERROR_CALLBACK, array('CRM_Core_Error', 'nullHandler'));
if (is_object($mailer)) {
$mailer->send($eq->email, $h, $b);
CRM_Core_Error::setCallback();
}
}
/**
* Get the mailing object for this queue event instance.
*
* @param
*
* @return object
* Mailing BAO
*/
public function &getMailing()
{
$mailing = new CRM_Mailing_BAO_Mailing();
$jobs = CRM_Mailing_BAO_MailingJob::getTableName();
$mailings = CRM_Mailing_BAO_Mailing::getTableName();
$queue = self::getTableName();
$mailing->query("\n SELECT {$mailings}.*\n FROM {$mailings}\n INNER JOIN {$jobs}\n ON {$jobs}.mailing_id = {$mailings}.id\n INNER JOIN {$queue}\n ON {$queue}.job_id = {$jobs}.id\n WHERE {$queue}.id = {$this->id}");
$mailing->fetch();
return $mailing;
}
/**
* 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;
}
/**
* @return array
*/
public function mailingList()
{
$data = array();
$mailing = new CRM_Mailing_BAO_Mailing();
$query = "SELECT name FROM civicrm_mailing ";
$mailing->query($query);
while ($mailing->fetch()) {
$data[CRM_Core_DAO::escapeString($mailing->name)] = $mailing->name;
}
return $data;
}
