From 5b794985dec26fd0aebe034f136fad6d89cf596d Mon Sep 17 00:00:00 2001
From: "deb.monish" <monish.deb@jmaconsulting.biz>
Date: Sun, 25 Jun 2017 21:49:34 +0530
Subject: [PATCH] CRM-20412: mailing report: unique opens detail view
 inaccurate

---
 CRM/Mailing/Event/BAO/Opened.php            | 26 +++++++++++++++------
 tests/phpunit/CRM/Mailing/BAO/QueryTest.php | 20 ++++++++++++++++
 2 files changed, 39 insertions(+), 7 deletions(-)

diff --git a/CRM/Mailing/Event/BAO/Opened.php b/CRM/Mailing/Event/BAO/Opened.php
index c882eaf096..11b99fadd5 100644
--- a/CRM/Mailing/Event/BAO/Opened.php
+++ b/CRM/Mailing/Event/BAO/Opened.php
@@ -241,11 +241,24 @@ class CRM_Mailing_Event_BAO_Opened extends CRM_Mailing_Event_DAO_Opened {
     $contact = CRM_Contact_BAO_Contact::getTableName();
     $email = CRM_Core_BAO_Email::getTableName();
 
+    $selectClauses = array(
+      "$contact.display_name as display_name",
+      "$contact.id as contact_id",
+      "$email.email as email",
+      ($is_distinct) ? "MIN({$open}.time_stamp) as date" : "{$open}.time_stamp as date",
+    );
+
+    if ($is_distinct) {
+      $groupBy = " GROUP BY $queue.id ";
+      $select = CRM_Contact_BAO_Query::appendAnyValueToSelect($selectClauses, "$queue.id");
+    }
+    else {
+      $groupBy = '';
+      $select = " SELECT " . implode(', ', $selectClauses);
+    }
+
     $query = "
-            SELECT      $contact.display_name as display_name,
-                        $contact.id as contact_id,
-                        $email.email as email,
-                        $open.time_stamp as date
+            $select
             FROM        $contact
             INNER JOIN  $queue
                     ON  $queue.contact_id = $contact.id
@@ -268,9 +281,7 @@ class CRM_Mailing_Event_BAO_Opened extends CRM_Mailing_Event_DAO_Opened {
       $query .= " AND $contact.id = " . CRM_Utils_Type::escape($contact_id, 'Integer');
     }
 
-    if ($is_distinct) {
-      $query .= " GROUP BY $queue.id, $open.time_stamp ";
-    }
+    $query .= $groupBy;
 
     $orderBy = "sort_name ASC, {$open}.time_stamp DESC";
     if ($sort) {
@@ -289,6 +300,7 @@ class CRM_Mailing_Event_BAO_Opened extends CRM_Mailing_Event_DAO_Opened {
       //Added "||$rowCount" to avoid displaying all records on first page
       $query .= ' LIMIT ' . CRM_Utils_Type::escape($offset, 'Integer') . ', ' . CRM_Utils_Type::escape($rowCount, 'Integer');
     }
+
     $dao->query($query);
 
     $results = array();
diff --git a/tests/phpunit/CRM/Mailing/BAO/QueryTest.php b/tests/phpunit/CRM/Mailing/BAO/QueryTest.php
index 3043acc397..020188337f 100644
--- a/tests/phpunit/CRM/Mailing/BAO/QueryTest.php
+++ b/tests/phpunit/CRM/Mailing/BAO/QueryTest.php
@@ -69,4 +69,24 @@ class CRM_Mailing_BAO_QueryTest extends CiviUnitTestCase {
     $this->assertEquals($ids, $contacts);
   }
 
+  /**
+   * CRM-20412: Test accurate count for unique open details
+   */
+  public function testOpenedMailingQuery() {
+    $op = new PHPUnit_Extensions_Database_Operation_Insert();
+    $op->execute($this->_dbconn,
+      $this->createFlatXMLDataSet(
+        dirname(__FILE__) . '/queryDataset.xml'
+      )
+    );
+
+    // ensure that total unique opened mail count is same while
+    //   fetching rows and row count for mailing_id = 14
+    $totalOpenedMailCount = CRM_Mailing_Event_BAO_Opened::getTotalCount(14, NULL, TRUE);
+    $totalOpenedMail = CRM_Mailing_Event_BAO_Opened::getRows(14, NULL, TRUE);
+
+    $this->assertEquals(4, $totalOpenedMailCount);
+    $this->assertEquals(4, count($totalOpenedMail));
+  }
+
 }
-- 
2.25.1