From 57623a4ceeb1f8f8b03a01b7c8b4954dc9defd09 Mon Sep 17 00:00:00 2001
From: Sunil Pawar <sunil@cividesk.com>
Date: Mon, 2 Jul 2018 21:10:02 +0530
Subject: [PATCH] dev/report/#5 - Fix mailing report unique count issue

---
 CRM/Mailing/BAO/Mailing.php           |  7 +++++++
 CRM/Report/Form/Mailing/Opened.php    | 18 ++++++++++++------
 templates/CRM/Mailing/Page/Report.tpl |  2 +-
 3 files changed, 20 insertions(+), 7 deletions(-)

diff --git a/CRM/Mailing/BAO/Mailing.php b/CRM/Mailing/BAO/Mailing.php
index b0583b56eb..03f6afb4d0 100644
--- a/CRM/Mailing/BAO/Mailing.php
+++ b/CRM/Mailing/BAO/Mailing.php
@@ -2198,6 +2198,7 @@ ORDER BY   civicrm_email.is_bulkmail DESC
                'forward',
                'reply',
                'opened',
+               'opened_unique',
                'optout',
              ) as $key) {
       $url = 'mailing/detail';
@@ -2235,6 +2236,12 @@ ORDER BY   civicrm_email.is_bulkmail DESC
           break;
 
         case 'opened':
+          $url = "mailing/opened";
+          $searchFilter .= "&mailing_open_status=Y";
+          $reportFilter .= "&distinct=0"; // do not use group by clause in report, because same report used for total and unique open
+          break;
+
+        case 'opened_unique':
           $url = "mailing/opened";
           $searchFilter .= "&mailing_open_status=Y";
           break;
diff --git a/CRM/Report/Form/Mailing/Opened.php b/CRM/Report/Form/Mailing/Opened.php
index e893b75fc8..98ba274137 100644
--- a/CRM/Report/Form/Mailing/Opened.php
+++ b/CRM/Report/Form/Mailing/Opened.php
@@ -289,13 +289,19 @@ class CRM_Report_Form_Mailing_Opened extends CRM_Report_Form {
   }
 
   public function groupBy() {
-    $groupBys = empty($this->_params['charts']) ? array("civicrm_mailing_event_queue.email_id") : array("{$this->_aliases['civicrm_mailing']}.id");
-
-    if (!empty($this->_params['unique_opens_value'])) {
-      $groupBys[] = "civicrm_mailing_event_queue.id";
+    $groupBys = array();
+    // Do not use group by clause if distinct = 0 mentioned in url params. flag is used in mailing report screen, default value is TRUE
+    // this report is used to show total opened and unique opened
+    if (CRM_Utils_Request::retrieve('distinct', 'Boolean', CRM_Core_DAO::$_nullObject, FALSE, TRUE)) {
+      $groupBys = empty($this->_params['charts']) ? array("civicrm_mailing_event_queue.email_id") : array("{$this->_aliases['civicrm_mailing']}.id");
+      if (!empty($this->_params['unique_opens_value'])) {
+        $groupBys[] = "civicrm_mailing_event_queue.id";
+      }
+    }
+    if (!empty($groupBys)) {
+      $this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($this->_selectClauses, $groupBys);
+      $this->_groupBy = "GROUP BY " . implode(', ', $groupBys);
     }
-    $this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($this->_selectClauses, $groupBys);
-    $this->_groupBy = "GROUP BY " . implode(', ', $groupBys);
   }
 
   public function postProcess() {
diff --git a/templates/CRM/Mailing/Page/Report.tpl b/templates/CRM/Mailing/Page/Report.tpl
index 0767df8c74..9c95b36046 100644
--- a/templates/CRM/Mailing/Page/Report.tpl
+++ b/templates/CRM/Mailing/Page/Report.tpl
@@ -37,7 +37,7 @@
   {if $report.mailing.open_tracking}
     <tr><td class="label"><a href="{$report.event_totals.links.opened}&distinct=1">{ts}Unique Opens{/ts}</a></td>
         <td>{$report.event_totals.opened} ({$report.event_totals.opened_rate|string_format:"%0.2f"}%)</td>
-        <td>{$report.event_totals.actionlinks.opened}</td></tr>
+        <td>{$report.event_totals.actionlinks.opened_unique}</td></tr>
     <tr><td class="label"><a href="{$report.event_totals.links.opened}">{ts}Total Opens{/ts}</a></td>
         <td>{$report.event_totals.total_opened}</td>
         <td>{$report.event_totals.actionlinks.opened}</td></tr>
-- 
2.25.1