/**
  * 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;
 }