/** * Register an open event. * * @param int $queue_id * The Queue Event ID of the recipient. */ public static function open($queue_id) { // First make sure there's a matching queue event. $success = FALSE; $q = new CRM_Mailing_Event_BAO_Queue(); $q->id = $queue_id; if ($q->find(TRUE)) { $oe = new CRM_Mailing_Event_BAO_Opened(); $oe->event_queue_id = $queue_id; $oe->time_stamp = date('YmdHis'); $oe->save(); $success = TRUE; } return $success; }
/** * Register an open event * * @param int $queue_id The Queue Event ID of the recipient * @return void * @access public * @static */ public static function open($queue_id) { /* First make sure there's a matching queue event */ require_once 'CRM/Mailing/Event/BAO/Queue.php'; $success = false; $q = new CRM_Mailing_Event_BAO_Queue(); $q->id = $queue_id; if ($q->find(true)) { $oe = new CRM_Mailing_Event_BAO_Opened(); $oe->event_queue_id = $queue_id; $oe->time_stamp = date('YmdHis'); $oe->save(); $success = true; } return $success; }
/** * Function to set variables up before form is built * * @return void * @access public */ public function preProcess() { //get the activity values $activityId = CRM_Utils_Request::retrieve('id', 'Positive', $this); $context = CRM_Utils_Request::retrieve('context', 'String', $this); $cid = CRM_Utils_Request::retrieve('cid', 'Positive', $this); //check for required permissions, CRM-6264 if ($activityId && !CRM_Activity_BAO_Activity::checkPermission($activityId, CRM_Core_Action::VIEW)) { CRM_Core_Error::fatal(ts('You do not have permission to access this page.')); } $session = CRM_Core_Session::singleton(); if (!in_array($context, array('home', 'dashlet', 'dashletFullscreen'))) { $url = CRM_Utils_System::url('civicrm/contact/view', "reset=1&cid={$cid}&selectedChild=activity"); } else { $url = CRM_Utils_System::url('civicrm/dashboard', 'reset=1'); } $session->pushUserContext($url); $defaults = array(); $params = array('id' => $activityId); CRM_Activity_BAO_Activity::retrieve($params, $defaults); //set activity type name and description to template list($activityTypeName, $activityTypeDescription) = CRM_Core_BAO_OptionValue::getActivityTypeDetails($defaults['activity_type_id']); $this->assign('activityTypeName', $activityTypeName); $this->assign('activityTypeDescription', $activityTypeDescription); if (CRM_Utils_Array::value('mailingId', $defaults)) { $this->_mailing_id = CRM_Utils_Array::value('source_record_id', $defaults); $mailingReport = CRM_Mailing_BAO_Mailing::report($this->_mailing_id, TRUE); CRM_Mailing_BAO_Mailing::getMailingContent($mailingReport, $this); $this->assign('mailingReport', $mailingReport); $full_open_report = CRM_Mailing_Event_BAO_Opened::getRows($this->_mailing_id, NULL, FALSE, NULL, NULL, NULL, $cid); $this->assign('openreport', $full_open_report); $click_thru_report = CRM_Mailing_Event_BAO_TrackableURLOpen::getRows($this->_mailing_id, NULL, FALSE, NULL, NULL, NULL, NULL, $cid); $this->assign('clickreport', $click_thru_report); } foreach ($defaults as $key => $value) { if (substr($key, -3) != '_id') { $values[$key] = $value; } } //get the campaign if ($campaignId = CRM_Utils_Array::value('campaign_id', $defaults)) { $campaigns = CRM_Campaign_BAO_Campaign::getCampaigns($campaignId); $values['campaign'] = $campaigns[$campaignId]; } if ($engagementLevel = CRM_Utils_Array::value('engagement_level', $defaults)) { $engagementLevels = CRM_Campaign_PseudoConstant::engagementLevel(); $values['engagement_level'] = CRM_Utils_Array::value($engagementLevel, $engagementLevels, $engagementLevel); } $values['attachment'] = CRM_Core_BAO_File::attachmentInfo('civicrm_activity', $activityId); $this->assign('values', $values); }
/** * Send graph detail for A/B tests mail. * * @param array $params * * @return array * @throws API_Exception */ function civicrm_api3_mailing_a_b_graph_stats($params) { civicrm_api3_verify_mandatory($params, 'CRM_Mailing_DAO_MailingAB', array('id'), FALSE); $defaults = array('criteria' => 'Open', 'target_date' => CRM_Utils_Time::getTime('YmdHis'), 'split_count' => 6, 'split_count_select' => 1); $params = array_merge($defaults, $params); $mailingAB = civicrm_api3('MailingAB', 'getsingle', array('id' => $params['id'])); $graphStats = array(); $ABFormat = array('A' => 'mailing_id_a', 'B' => 'mailing_id_b'); foreach ($ABFormat as $name => $column) { switch (strtolower($params['criteria'])) { case 'open': $result = CRM_Mailing_Event_BAO_Opened::getRows($mailingAB['mailing_id_a'], NULL, TRUE, 0, 1, "civicrm_mailing_event_opened.time_stamp ASC"); $startDate = CRM_Utils_Date::processDate($result[0]['date']); $targetDate = CRM_Utils_Date::processDate($params['target_date']); $dateDuration = round(round(strtotime($targetDate) - strtotime($startDate)) / $params['split_count']); $toDate = strtotime($startDate) + $dateDuration * $params['split_count_select']; $toDate = date('YmdHis', $toDate); $graphStats[$name] = array($params['split_count_select'] => array('count' => CRM_Mailing_Event_BAO_Opened::getTotalCount($mailingAB[$column], NULL, TRUE, $toDate), 'time' => CRM_Utils_Date::customFormat($toDate))); break; case 'total unique clicks': $result = CRM_Mailing_Event_BAO_TrackableURLOpen::getRows($mailingAB['mailing_id_a'], NULL, TRUE, 0, 1, "civicrm_mailing_event_trackable_url_open.time_stamp ASC"); $startDate = CRM_Utils_Date::processDate($result[0]['date']); $targetDate = CRM_Utils_Date::processDate($params['target_date']); $dateDuration = round(abs(strtotime($targetDate) - strtotime($startDate)) / $params['split_count']); $toDate = strtotime($startDate) + $dateDuration * $params['split_count_select']; $toDate = date('YmdHis', $toDate); $graphStats[$name] = array($params['split_count_select'] => array('count' => CRM_Mailing_Event_BAO_TrackableURLOpen::getTotalCount($params['mailing_id'], NULL, FALSE, NULL, $toDate), 'time' => CRM_Utils_Date::customFormat($toDate))); break; case 'total clicks on a particular link': if (empty($params['target_url'])) { throw new API_Exception("Provide url to get stats result for total clicks on a particular link"); } // FIXME: doesn't make sense to get url_id mailing_id_(a|b) while getting start date in mailing_id_a $url_id = CRM_Mailing_BAO_TrackableURL::getTrackerURLId($mailingAB[$column], $params['target_url']); $result = CRM_Mailing_Event_BAO_TrackableURLOpen::getRows($mailingAB['mailing_id_a'], NULL, FALSE, $url_id, 0, 1, "civicrm_mailing_event_trackable_url_open.time_stamp ASC"); $startDate = CRM_Utils_Date::processDate($result[0]['date']); $targetDate = CRM_Utils_Date::processDate($params['target_date']); $dateDuration = round(abs(strtotime($targetDate) - strtotime($startDate)) / $params['split_count']); $toDate = strtotime($startDate) + $dateDuration * $params['split_count_select']; $toDate = CRM_Utils_Date::processDate($toDate); $graphStats[$name] = array($params['split_count_select'] => array('count' => CRM_Mailing_Event_BAO_TrackableURLOpen::getTotalCount($params['mailing_id'], NULL, FALSE, $url_id, $toDate), 'time' => CRM_Utils_Date::customFormat($toDate))); break; } } return civicrm_api3_create_success($graphStats); }
/** * 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; } }
/** * wrapper for ajax activity selector. * * @param array $params * Associated array for params record id. * * @return array * associated array of contact activities */ public static function getContactMailingSelector(&$params) { // format the params $params['offset'] = ($params['page'] - 1) * $params['rp']; $params['rowCount'] = $params['rp']; $params['sort'] = CRM_Utils_Array::value('sortBy', $params); $params['caseId'] = NULL; // get contact mailings $mailings = CRM_Mailing_BAO_Mailing::getContactMailings($params); // add total $params['total'] = CRM_Mailing_BAO_Mailing::getContactMailingsCount($params); //CRM-12814 if (!empty($mailings)) { $openCounts = CRM_Mailing_Event_BAO_Opened::getMailingContactCount(array_keys($mailings), $params['contact_id']); $clickCounts = CRM_Mailing_Event_BAO_TrackableURLOpen::getMailingContactCount(array_keys($mailings), $params['contact_id']); } // format params and add links $contactMailings = array(); foreach ($mailings as $mailingId => $values) { $mailing = array(); $mailing['subject'] = $values['subject']; $mailing['creator_name'] = CRM_Utils_System::href($values['creator_name'], 'civicrm/contact/view', "reset=1&cid={$values['creator_id']}"); $mailing['recipients'] = CRM_Utils_System::href(ts('(recipients)'), 'civicrm/mailing/report/event', "mid={$values['mailing_id']}&reset=1&cid={$params['contact_id']}&event=queue&context=mailing"); $mailing['start_date'] = CRM_Utils_Date::customFormat($values['start_date']); //CRM-12814 $mailing['openstats'] = "Opens: " . CRM_Utils_Array::value($values['mailing_id'], $openCounts, 0) . "<br />Clicks: " . CRM_Utils_Array::value($values['mailing_id'], $clickCounts, 0); $actionLinks = array(CRM_Core_Action::VIEW => array('name' => ts('View'), 'url' => 'civicrm/mailing/view', 'qs' => "reset=1&id=%%mkey%%", 'title' => ts('View Mailing'), 'class' => 'crm-popup'), CRM_Core_Action::BROWSE => array('name' => ts('Mailing Report'), 'url' => 'civicrm/mailing/report', 'qs' => "mid=%%mid%%&reset=1&cid=%%cid%%&context=mailing", 'title' => ts('View Mailing Report'))); $mailingKey = $values['mailing_id']; if ($hash = CRM_Mailing_BAO_Mailing::getMailingHash($mailingKey)) { $mailingKey = $hash; } $mailing['links'] = CRM_Core_Action::formLink($actionLinks, NULL, array('mid' => $values['mailing_id'], 'cid' => $params['contact_id'], 'mkey' => $mailingKey), ts('more'), FALSE, 'mailing.contact.action', 'Mailing', $values['mailing_id']); array_push($contactMailings, $mailing); } $contactMailingsDT = array(); $contactMailingsDT['data'] = $contactMailings; $contactMailingsDT['recordsTotal'] = $params['total']; $contactMailingsDT['recordsFiltered'] = $params['total']; return $contactMailingsDT; }
/** * Function which needs to be explained. * * @param array $params * * @return array * @throws \API_Exception */ function civicrm_api3_mailing_stats($params) { civicrm_api3_verify_mandatory($params, 'CRM_Mailing_DAO_MailingJob', array('mailing_id'), FALSE); if ($params['date'] == 'now') { $params['date'] = date('YmdHis'); } else { $params['date'] = CRM_Utils_Date::processDate($params['date'] . ' ' . $params['date_time']); } $stats[$params['mailing_id']] = array(); if (empty($params['job_id'])) { $params['job_id'] = NULL; } foreach (array('Delivered', 'Bounces', 'Unsubscribers', 'Unique Clicks', 'Opened') as $detail) { switch ($detail) { case 'Delivered': $stats[$params['mailing_id']] += array($detail => CRM_Mailing_Event_BAO_Delivered::getTotalCount($params['mailing_id'], $params['job_id'], FALSE, $params['date'])); break; case 'Bounces': $stats[$params['mailing_id']] += array($detail => CRM_Mailing_Event_BAO_Bounce::getTotalCount($params['mailing_id'], $params['job_id'], FALSE, $params['date'])); break; case 'Unsubscribers': $stats[$params['mailing_id']] += array($detail => CRM_Mailing_Event_BAO_Unsubscribe::getTotalCount($params['mailing_id'], $params['job_id'], FALSE, NULL, $params['date'])); break; case 'Unique Clicks': $stats[$params['mailing_id']] += array($detail => CRM_Mailing_Event_BAO_TrackableURLOpen::getTotalCount($params['mailing_id'], $params['job_id'], FALSE, NULL, $params['date'])); break; case 'Opened': $stats[$params['mailing_id']] += array($detail => CRM_Mailing_Event_BAO_Opened::getTotalCount($params['mailing_id'], $params['job_id'], FALSE, $params['date'])); break; } } return civicrm_api3_create_success($stats); }
/** * Handle an open event * * @param array $params * * @return array */ function civicrm_api3_mailing_event_open($params) { civicrm_api3_verify_mandatory($params, 'CRM_Mailing_Event_DAO_Opened', array('event_queue_id'), FALSE); $queue = $params['event_queue_id']; $success = CRM_Mailing_Event_BAO_Opened::open($queue); if (!$success) { return civicrm_api3_create_error('mailing open event failed'); } return civicrm_api3_create_success($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); } } } } }
/** * This function is a wrapper for ajax activity selector * * @param array $params associated array for params record id. * * @return array $contactActivities associated array of contact activities * @access public */ public static function getContactMailingSelector(&$params) { // format the params $params['offset'] = ($params['page'] - 1) * $params['rp']; $params['rowCount'] = $params['rp']; $params['sort'] = CRM_Utils_Array::value('sortBy', $params); $params['caseId'] = NULL; // get contact mailings $mailings = CRM_Mailing_BAO_Mailing::getContactMailings($params); // add total $params['total'] = CRM_Mailing_BAO_Mailing::getContactMailingsCount($params); //CRM-12814 if (!empty($mailings)) { $openCounts = CRM_Mailing_Event_BAO_Opened::getMailingTotalCount(array_keys($mailings)); $clickCounts = CRM_Mailing_Event_BAO_TrackableURLOpen::getMailingTotalCount(array_keys($mailings)); } // format params and add links $contactMailings = array(); foreach ($mailings as $mailingId => $values) { $contactMailings[$mailingId]['subject'] = $values['subject']; $contactMailings[$mailingId]['start_date'] = CRM_Utils_Date::customFormat($values['start_date']); $contactMailings[$mailingId]['recipients'] = CRM_Utils_System::href(ts('(recipients)'), 'civicrm/mailing/report/event', "mid={$values['mailing_id']}&reset=1&cid={$params['contact_id']}&event=queue&context=mailing"); $contactMailings[$mailingId]['mailing_creator'] = CRM_Utils_System::href($values['creator_name'], 'civicrm/contact/view', "reset=1&cid={$values['creator_id']}"); //CRM-12814 $contactMailings[$mailingId]['openstats'] = ts('Opens') . ': ' . $openCounts[$values['mailing_id']] . '<br />' . ts('Clicks') . ': ' . $clickCounts[$values['mailing_id']]; $actionLinks = array(CRM_Core_Action::VIEW => array('name' => ts('View'), 'url' => 'civicrm/mailing/view', 'qs' => "reset=1&id={$values['mailing_id']}", 'title' => ts('View Mailing'), 'class' => 'crm-mailing-view'), CRM_Core_Action::BROWSE => array('name' => ts('Mailing Report'), 'url' => 'civicrm/mailing/report', 'qs' => "mid={$values['mailing_id']}&reset=1&cid={$params['contact_id']}&context=mailing", 'title' => ts('View Mailing Report'))); $contactMailings[$mailingId]['links'] = CRM_Core_Action::formLink($actionLinks, NULL, array()); } return $contactMailings; }
<?php require_once '../civicrm.config.php'; require_once 'CRM/Core/Config.php'; require_once 'CRM/Core/Error.php'; require_once 'CRM/Utils/Array.php'; $config =& CRM_Core_Config::singleton(); $queue_id = CRM_Utils_Array::value('q', $_GET); if (!$queue_id) { echo "Missing input parameters\n"; exit; } require_once 'CRM/Mailing/Event/BAO/Opened.php'; CRM_Mailing_Event_BAO_Opened::open($queue_id); $filename = "../i/tracker.gif"; header('Cache-Control: must-revalidate, post-check=0, pre-check=0'); header('Content-Description: File Transfer'); header('Content-type: image/gif'); header('Content-Length: ' . filesize($filename)); header('Content-Disposition: inline; filename=tracker.gif'); readfile($filename); exit;
/** * 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; } }
/** * 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, $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_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(); $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_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; $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 (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']; $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={$mailing_id}"; 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()); } return $report; }
/** * Handle an open event * * @param array $params * * @return array */ function civicrm_mailer_event_open($params) { $errors = _civicrm_mailer_check_params($params, array('event_queue_id')); if (!empty($errors)) { return $errors; } $queue = $params['event_queue_id']; $success = CRM_Mailing_Event_BAO_Opened::open($queue); if (!$success) { return civicrm_create_error(ts('mailer open event failed')); } return civicrm_create_success(); }
/** * 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; }
/** * 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(); $open = CRM_Mailing_Event_BAO_Opened::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 {$open}.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 {$open}\n ON {$open}.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 {$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, {$open}.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; }