From 6245de60a716da2d16f124233789d57c48e49a45 Mon Sep 17 00:00:00 2001
From: Jon Goldberg <jon@megaphonetech.com>
Date: Mon, 13 Jan 2020 17:52:22 -0500
Subject: [PATCH] fix created_date/modified_date relative date filter searches

---
 CRM/Contact/BAO/Query.php                   | 19 ++++++++------
 tests/phpunit/CRM/Contact/BAO/QueryTest.php | 29 +++++++++++++++++++++
 2 files changed, 40 insertions(+), 8 deletions(-)

diff --git a/CRM/Contact/BAO/Query.php b/CRM/Contact/BAO/Query.php
index f0eaf25884..a5c0b434d3 100644
--- a/CRM/Contact/BAO/Query.php
+++ b/CRM/Contact/BAO/Query.php
@@ -7066,6 +7066,14 @@ AND   displayRelType.is_active = 1
     $dates = CRM_Utils_Date::getFromTo($value, NULL, NULL);
     // Where end would be populated only if we are handling one of the weird ones with different from & to fields.
     $secondWhere = $fieldSpec['where_end'] ?? $fieldSpec['where'];
+
+    $where = $fieldSpec['where'];
+    if ($fieldSpec['table_name'] === 'civicrm_contact') {
+      // Special handling for contact table as it has a known alias in advanced search.
+      $where = str_replace('civicrm_contact.', 'contact_a.', $where);
+      $secondWhere = str_replace('civicrm_contact.', 'contact_a.', $secondWhere);
+    }
+
     if (empty($dates[0])) {
       // ie. no start date we only have end date
       $this->_where[$grouping][] = $secondWhere . " <= '{$dates[1]}'";
@@ -7077,7 +7085,7 @@ AND   displayRelType.is_active = 1
     elseif (empty($dates[1])) {
 
       // ie. no end date we only have start date
-      $this->_where[$grouping][] = $fieldSpec['where'] . " >= '{$dates[1]}'";
+      $this->_where[$grouping][] = $where . " >= '{$dates[0]}'";
 
       $this->_qill[$grouping][] = ts('%1 is ', [$fieldSpec['title']]) . $filters[$value] . ' (' . ts("from %1", [
         CRM_Utils_Date::customFormat($dates[0]),
@@ -7085,13 +7093,8 @@ AND   displayRelType.is_active = 1
     }
     else {
       // we have start and end dates.
-      $where = $fieldSpec['where'];
-      if ($fieldSpec['table_name'] === 'civicrm_contact') {
-        // Special handling for contact table as it has a known alias in advanced search.
-        $where = str_replace('civicrm_contact.', 'contact_a.', $where);
-      }
-      if ($secondWhere !== $fieldSpec['where']) {
-        $this->_where[$grouping][] = $fieldSpec['where'] . ">=  '{$dates[0]}' AND $secondWhere <='{$dates[1]}'";
+      if ($secondWhere !== $where) {
+        $this->_where[$grouping][] = $where . ">=  '{$dates[0]}' AND $secondWhere <='{$dates[1]}'";
       }
       else {
         $this->_where[$grouping][] = $where . " BETWEEN '{$dates[0]}' AND '{$dates[1]}'";
diff --git a/tests/phpunit/CRM/Contact/BAO/QueryTest.php b/tests/phpunit/CRM/Contact/BAO/QueryTest.php
index f70937de06..00b2c6c708 100644
--- a/tests/phpunit/CRM/Contact/BAO/QueryTest.php
+++ b/tests/phpunit/CRM/Contact/BAO/QueryTest.php
@@ -1067,6 +1067,35 @@ civicrm_relationship.is_active = 1 AND
     $this->disableFinancialACLs();
   }
 
+  /**
+   * Test relative date filters to ensure they generate correct SQL.
+   *
+   * @dataProvider relativeDateFilters
+   */
+  public function testRelativeDateFilters($filter, $expectedWhere) {
+    $params = [['created_date_relative', '=', $filter, 0, 0]];
+
+    $dates = CRM_Utils_Date::getFromTo($filter, NULL, NULL);
+    $expectedWhere = str_replace(['date0', 'date1'], [$dates[0], $dates[1]], $expectedWhere);
+
+    $query = new CRM_Contact_BAO_Query(
+      $params, [],
+      NULL, TRUE, FALSE, 1,
+      TRUE,
+      TRUE, FALSE
+    );
+
+    list($select, $from, $where, $having) = $query->query();
+    $this->assertEquals($expectedWhere, $where);
+  }
+
+  public function relativeDateFilters() {
+    $dataProvider[] = ['this.year', "WHERE  ( contact_a.created_date BETWEEN 'date0' AND 'date1' )  AND (contact_a.is_deleted = 0)"];
+    $dataProvider[] = ['greater.day', "WHERE  ( contact_a.created_date >= 'date0' )  AND (contact_a.is_deleted = 0)"];
+    $dataProvider[] = ['earlier.week', "WHERE  ( contact_a.created_date <= 'date1' )  AND (contact_a.is_deleted = 0)"];
+    return $dataProvider;
+  }
+
   /**
    * Create contributions to test summary calculations.
    *
-- 
2.25.1