From f8da3b9336a6dc74aafff1ab2954a6f62b18d407 Mon Sep 17 00:00:00 2001
From: "deb.monish" <monish.deb@webaccessglobal.com>
Date: Fri, 25 Mar 2016 16:20:15 +0530
Subject: [PATCH] CRM-18236 fix

---
 CRM/Utils/SQL/Select.php                      | 16 +++++-
 Civi/ActionSchedule/RecipientBuilder.php      | 52 ++++++++++++++-----
 .../CRM/Core/BAO/ActionScheduleTest.php       |  2 +-
 3 files changed, 55 insertions(+), 15 deletions(-)

diff --git a/CRM/Utils/SQL/Select.php b/CRM/Utils/SQL/Select.php
index c416a955e2..000042776d 100644
--- a/CRM/Utils/SQL/Select.php
+++ b/CRM/Utils/SQL/Select.php
@@ -125,6 +125,7 @@ class CRM_Utils_SQL_Select implements ArrayAccess {
   private $limit = NULL;
   private $offset = NULL;
   private $params = array();
+  private $distinct = NULL;
 
   // Public to work-around PHP 5.3 limit.
   public $strict = NULL;
@@ -256,6 +257,19 @@ class CRM_Utils_SQL_Select implements ArrayAccess {
     return $this;
   }
 
+  /**
+   * Return only distinct values
+   *
+   * @param bool $isDistinct allow DISTINCT select or not
+   * @return CRM_Utils_SQL_Select
+   */
+  public function distinct($isDistinct = TRUE) {
+    if ($isDistinct) {
+      $this->distinct = 'DISTINCT ';
+    }
+    return $this;
+  }
+
   /**
    * Limit results by adding extra condition(s) to the WHERE clause
    *
@@ -536,7 +550,7 @@ class CRM_Utils_SQL_Select implements ArrayAccess {
       $sql .= ")\n";
     }
     if ($this->selects) {
-      $sql .= 'SELECT ' . implode(', ', $this->selects) . "\n";
+      $sql .= 'SELECT ' . $this->distinct . implode(', ', $this->selects) . "\n";
     }
     else {
       $sql .= 'SELECT *' . "\n";
diff --git a/Civi/ActionSchedule/RecipientBuilder.php b/Civi/ActionSchedule/RecipientBuilder.php
index 450143f36a..450dcfe270 100644
--- a/Civi/ActionSchedule/RecipientBuilder.php
+++ b/Civi/ActionSchedule/RecipientBuilder.php
@@ -178,31 +178,57 @@ class RecipientBuilder {
 
     $startDateClauses = $this->prepareStartDateClauses();
 
-    $firstQuery = $query->copy()
-      ->merge($this->selectIntoActionLog(self::PHASE_RELATION_FIRST, $query))
-      ->merge($this->joinReminder('LEFT JOIN', 'rel', $query))
-      ->where("reminder.id IS NULL")
-      ->where($startDateClauses)
-      ->strict()
-      ->toSQL();
-    \CRM_Core_DAO::executeQuery($firstQuery);
-
     // In some cases reference_date got outdated due to many reason e.g. In Membership renewal end_date got extended
     // which means reference date mismatches with the end_date where end_date may be used as the start_action_date
     // criteria  for some schedule reminder so in order to send new reminder we INSERT new reminder with new reference_date
     // value via UNION operation
+    $referenceReminderIDs = array();
+    $referenceDate = NULL;
     if (!empty($query['casUseReferenceDate'])) {
+      // First retrieve all the action log's ids which are outdated or in other words reference_date now don't match with entity date.
+      // And the retrieve the updated entity date which will later used below to update all other outdated action log records
+      $sql = $query->copy()
+        ->select('reminder.id as id')
+        ->select($query['casDateField'] . ' as reference_date')
+        ->merge($this->joinReminder('INNER JOIN', 'rel', $query))
+        ->where("reminder.id IS NOT NULL AND reminder.reference_date IS NOT NULL AND reminder.reference_date <> !casDateField")
+        ->where($startDateClauses)
+        ->orderBy("reminder.id desc")
+        ->strict()
+        ->toSQL();
+      $dao = \CRM_Core_DAO::executeQuery($sql);
+
+      while ($dao->fetch()) {
+        $referenceReminderIDs[] = $dao->id;
+        $referenceDate = $dao->reference_date;
+      }
+    }
+
+    if (empty($referenceReminderIDs)) {
+      $firstQuery = $query->copy()
+        ->merge($this->selectIntoActionLog(self::PHASE_RELATION_FIRST, $query))
+        ->merge($this->joinReminder('LEFT JOIN', 'rel', $query))
+        ->where("reminder.id IS NULL")
+        ->where($startDateClauses)
+        ->strict()
+        ->toSQL();
+      \CRM_Core_DAO::executeQuery($firstQuery);
+    }
+    else {
+      // INSERT new log to send reminder as desired entity date got updated
       $referenceQuery = $query->copy()
         ->merge($this->selectIntoActionLog(self::PHASE_RELATION_FIRST, $query))
         ->merge($this->joinReminder('LEFT JOIN', 'rel', $query))
-        ->where("reminder.id IS NOT NULL")
+        ->where("reminder.id = !reminderID")
         ->where($startDateClauses)
-        ->where("reminder.action_date_time IS NOT NULL AND reminder.reference_date IS NOT NULL")
-        ->groupBy("reminder.id, reminder.reference_date")
-        ->having("reminder.id = MAX(reminder.id) AND reminder.reference_date <> !casDateField")
+        ->param('reminderID', $referenceReminderIDs[0])
         ->strict()
         ->toSQL();
       \CRM_Core_DAO::executeQuery($referenceQuery);
+
+      // Update all the previous outdated reference date valued, action_log rows to the latest changed entity date
+      $updateQuery = "UPDATE civicrm_action_log SET reference_date = '" . $referenceDate . "' WHERE id IN (" . implode(', ', $referenceReminderIDs) . ")";
+      \CRM_Core_DAO::executeQuery($updateQuery);
     }
   }
 
diff --git a/tests/phpunit/CRM/Core/BAO/ActionScheduleTest.php b/tests/phpunit/CRM/Core/BAO/ActionScheduleTest.php
index 22a6cc30c3..b4301f388e 100644
--- a/tests/phpunit/CRM/Core/BAO/ActionScheduleTest.php
+++ b/tests/phpunit/CRM/Core/BAO/ActionScheduleTest.php
@@ -1294,7 +1294,7 @@ class CRM_Core_BAO_ActionScheduleTest extends CiviUnitTestCase {
       array(
         // It should not re-send on the same day
         'time' => '2012-04-12 01:00:00',
-        'recipients' => array(array()),
+        'recipients' => array(),
       ),
     ));
   }
-- 
2.25.1