From 6b62f1bb3dcc2bdaa0b2588599794c0e90c9846c Mon Sep 17 00:00:00 2001
From: Dave Greenberg <dave@civicrm.org>
Date: Fri, 28 Feb 2014 17:16:53 -0800
Subject: [PATCH] CRM-14286 - Contact mailings tab should show opens and clicks
 for currently viewed contact. This commit cannot be backported to 4.3 without
 adjustment  since CRM_Mailing_BAO_Job has been renamed to
 CRM_Mailing_BAO_MailingJob as of 4.4.

----------------------------------------
* CRM-14286: Contact Mailings tab selector should show Open and Click activity specfic to the currently viewed contact
  http://issues.civicrm.org/jira/browse/CRM-14286
---
 CRM/Mailing/BAO/Mailing.php                | 10 +++---
 CRM/Mailing/Event/BAO/Opened.php           | 41 ++++++++++++++++++++++
 CRM/Mailing/Event/BAO/TrackableURLOpen.php | 41 ++++++++++++++++++++++
 templates/CRM/Mailing/Page/Tab.tpl         |  2 +-
 4 files changed, 89 insertions(+), 5 deletions(-)

diff --git a/CRM/Mailing/BAO/Mailing.php b/CRM/Mailing/BAO/Mailing.php
index 34ab8f3bc4..fd9f202ac7 100644
--- a/CRM/Mailing/BAO/Mailing.php
+++ b/CRM/Mailing/BAO/Mailing.php
@@ -2742,8 +2742,8 @@ AND        m.id = %1
 
     //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));
+      $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
@@ -2760,8 +2760,10 @@ AND        m.id = %1
         "reset=1&cid={$values['creator_id']}");
 
       //CRM-12814
-      $contactMailings[$mailingId]['openstats'] = ts('Opens') . ': ' . $openCounts[$values['mailing_id']] .
-        '<br />' . ts('Clicks') . ': ' . $clickCounts[$values['mailing_id']];
+      $contactMailings[$mailingId]['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(
diff --git a/CRM/Mailing/Event/BAO/Opened.php b/CRM/Mailing/Event/BAO/Opened.php
index 619cae0582..88dd87bc60 100644
--- a/CRM/Mailing/Event/BAO/Opened.php
+++ b/CRM/Mailing/Event/BAO/Opened.php
@@ -159,6 +159,47 @@ class CRM_Mailing_Event_BAO_Opened extends CRM_Mailing_Event_DAO_Opened {
     return $openedCount;
   }
 
+  /**
+   * Get opened count for each mailing for a given set of mailing IDs and a specific contact
+   *
+   * @param int $mailingIDs   IDs of the mailing (comma separated)
+   * @param int $contactID    ID of the contact
+   *
+   * @return array            Count per mailing ID
+   * @access public
+   * @static
+   */
+  public static function getMailingContactCount($mailingIDs, $contactID) {
+    $dao = new CRM_Core_DAO();
+    $openedCount = array();
+
+    $open = self::getTableName();
+    $queue = CRM_Mailing_Event_BAO_Queue::getTableName();
+    $job = CRM_Mailing_BAO_MailingJob::getTableName();
+    $mailingIDs = implode(',', $mailingIDs);
+
+    $query = "
+      SELECT $job.mailing_id as mailingID, COUNT($open.id) as opened
+      FROM $open
+      INNER JOIN $queue
+        ON  $open.event_queue_id = $queue.id
+        AND $queue.contact_id = $contactID
+      INNER JOIN $job
+        ON  $queue.job_id = $job.id
+        AND $job.is_test = 0
+      WHERE $job.mailing_id IN ({$mailingIDs})
+      GROUP BY civicrm_mailing_job.mailing_id
+    ";
+
+    $dao->query($query);
+
+    while ( $dao->fetch() ) {
+      $openedCount[$dao->mailingID] = $dao->opened;
+    }
+
+    return $openedCount;
+  }
+
   /**
    * Get rows for the event browser
    *
diff --git a/CRM/Mailing/Event/BAO/TrackableURLOpen.php b/CRM/Mailing/Event/BAO/TrackableURLOpen.php
index 19de7aac91..fdc92c3bda 100644
--- a/CRM/Mailing/Event/BAO/TrackableURLOpen.php
+++ b/CRM/Mailing/Event/BAO/TrackableURLOpen.php
@@ -190,6 +190,47 @@ class CRM_Mailing_Event_BAO_TrackableURLOpen extends CRM_Mailing_Event_DAO_Track
     return $clickCount;
   }
 
+  /**
+   * Get tracked url count for each mailing for a given set of mailing IDs
+   *
+   * @param int $mailingIDs    IDs of the mailing (comma separated)
+   * @param int $contactID     ID of the contact
+   *
+   * @return array             Count per mailing ID
+   * @access public
+   * @static
+   */
+  public static function getMailingContactCount($mailingIDs, $contactID) {
+    $dao = new CRM_Core_DAO();
+    $clickCount = array();
+
+    $click = self::getTableName();
+    $queue = CRM_Mailing_Event_BAO_Queue::getTableName();
+    $job = CRM_Mailing_BAO_MailingJob::getTableName();
+    $mailingIDs = implode(',', $mailingIDs);
+
+    $query = "
+      SELECT $job.mailing_id as mailingID, COUNT($click.id) as opened
+      FROM $click
+      INNER JOIN $queue
+        ON  $click.event_queue_id = $queue.id
+        AND $queue.contact_id = $contactID
+      INNER JOIN $job
+        ON  $queue.job_id = $job.id
+        AND $job.is_test = 0
+      WHERE $job.mailing_id IN ({$mailingIDs})
+      GROUP BY civicrm_mailing_job.mailing_id
+    ";
+
+    $dao->query($query);
+
+    while ( $dao->fetch() ) {
+      $clickCount[$dao->mailingID] = $dao->opened;
+    }
+
+    return $clickCount;
+  }
+
   /**
    * Get rows for the event browser
    *
diff --git a/templates/CRM/Mailing/Page/Tab.tpl b/templates/CRM/Mailing/Page/Tab.tpl
index 2b11fc06a8..f34a3d3b42 100644
--- a/templates/CRM/Mailing/Page/Tab.tpl
+++ b/templates/CRM/Mailing/Page/Tab.tpl
@@ -30,7 +30,7 @@
     <tr>
       <th class='crm-mailing-contact-subject'>{ts}Subject{/ts}</th>
       <th class='crm-mailing-contact_created'>{ts}Added By{/ts}</th>
-      <th class='crm-contact-activity_contact nosort'>{ts}With{/ts}</th>
+      <th class='crm-contact-activity_contact nosort'>{ts}Recipients{/ts}</th>
       <th class='crm-mailing-contact-date'>{ts}Date{/ts}</th>
       <th class='crm-mailing_openstats'>{ts}Opens/ Clicks{/ts}</th>
       <th class='crm-mailing-contact-links nosort'>&nbsp;</th>
-- 
2.25.1