/** * Generate a report. Fetch event count information, mailing data, and job * status. * * @param int $id The mailing id to report * @return array Associative array of reporting data * @access public * @static */ function &report($id) { $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'; $t = array('mailing' => CRM_Mailing_BAO_Mailing::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()); $report = array(); /* FIXME: put some permissioning in here */ /* 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(CRM_Mailing_BAO_Mailing::fields()) as $field) { $report['mailing'][$field] = $mailing->{$field}; } /* 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']}.name as group_name,\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_name; $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['opened']}.id) as opened,\n COUNT(DISTINCT {$t['reply']}.id) as reply,\n COUNT(DISTINCT {$t['unsubscribe']}.id) as unsubscribe,\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 FROM {$t['job']}\n LEFT JOIN {$t['queue']}\n ON {$t['queue']}.job_id = {$t['job']}.id\n LEFT JOIN {$t['opened']}\n ON {$t['opened']}.event_queue_id = {$t['queue']}.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['unsubscribe']}\n ON {$t['unsubscribe']}.event_queue_id = \n {$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 \n WHERE {$t['job']}.mailing_id = {$mailing_id}\n GROUP BY {$t['job']}.id"); $report['jobs'] = array(); $report['event_totals'] = array(); while ($mailing->fetch()) { $row = array(); foreach (array('queue', 'delivered', 'opened', 'url', 'forward', 'reply', 'unsubscribe', 'bounce') as $field) { $row[$field] = $mailing->{$field}; $report['event_totals'][$field] += $mailing->{$field}; } 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 * $mailing->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/event', "reset=1&event=click&mid={$mailing_id}&jid={$mailing->id}"), 'queue' => CRM_Utils_System::url('civicrm/mailing/event', "reset=1&event=queue&mid={$mailing_id}&jid={$mailing->id}"), 'delivered' => CRM_Utils_System::url('civicrm/mailing/event', "reset=1&event=delivered&mid={$mailing_id}&jid={$mailing->id}"), 'bounce' => CRM_Utils_System::url('civicrm/mailing/event', "reset=1&event=bounce&mid={$mailing_id}&jid={$mailing->id}"), 'unsubscribe' => CRM_Utils_System::url('civicrm/mailing/event', "reset=1&event=unsubscribe&mid={$mailing_id}&jid={$mailing->id}"), 'forward' => CRM_Utils_System::url('civicrm/mailing/event', "reset=1&event=forward&mid={$mailing_id}&jid={$mailing->id}"), 'reply' => CRM_Utils_System::url('civicrm/mailing/event', "reset=1&event=reply&mid={$mailing_id}&jid={$mailing->id}"), 'opened' => CRM_Utils_System::url('civicrm/mailing/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; } if ($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']; } 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 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/event', "reset=1&event=click&mid={$mailing_id}&uid={$mailing->id}"), 'link_unique' => CRM_Utils_System::url('civicrm/mailing/event', "reset=1&event=click&mid={$mailing_id}&uid={$mailing->id}&distinct=1"), 'clicks' => $mailing->clicks, 'unique' => $mailing->unique_clicks, 'rate' => $report['event_totals']['delivered'] ? 100.0 * $mailing->unique_clicks / $report['event_totals']['delivered'] : 0); } $report['event_totals']['links'] = array('clicks' => CRM_Utils_System::url('civicrm/mailing/event', "reset=1&event=click&mid={$mailing_id}"), 'clicks_unique' => CRM_Utils_System::url('civicrm/mailing/event', "reset=1&event=click&mid={$mailing_id}&distinct=1"), 'queue' => CRM_Utils_System::url('civicrm/mailing/event', "reset=1&event=queue&mid={$mailing_id}"), 'delivered' => CRM_Utils_System::url('civicrm/mailing/event', "reset=1&event=delivered&mid={$mailing_id}"), 'bounce' => CRM_Utils_System::url('civicrm/mailing/event', "reset=1&event=bounce&mid={$mailing_id}"), 'unsubscribe' => CRM_Utils_System::url('civicrm/mailing/event', "reset=1&event=unsubscribe&mid={$mailing_id}"), 'forward' => CRM_Utils_System::url('civicrm/mailing/event', "reset=1&event=forward&mid={$mailing_id}"), 'reply' => CRM_Utils_System::url('civicrm/mailing/event', "reset=1&event=reply&mid={$mailing_id}"), 'opened' => CRM_Utils_System::url('civicrm/mailing/event', "reset=1&event=opened&mid={$mailing_id}")); $report['retry'] = CRM_Utils_System::url('civicrm/mailing/retry', "reset=1&mid={$mailing_id}"); return $report; }