From 10ab77cb03ede42404d1f64b8e5b72f09e54d25a Mon Sep 17 00:00:00 2001
From: Coleman Watts <coleman@civicrm.org>
Date: Thu, 17 Feb 2022 13:50:39 -0500
Subject: [PATCH] SearchKit - Support relative dates and datepicker with the
 DATE() sql function

---
 Civi/Api4/Query/Api4SelectQuery.php      |  7 +++++++
 Civi/Api4/Utils/FormattingUtil.php       | 12 ++++++------
 ext/search_kit/Civi/Search/Admin.php     |  3 +++
 tests/phpunit/api/v4/Action/DateTest.php |  3 ++-
 4 files changed, 18 insertions(+), 7 deletions(-)

diff --git a/Civi/Api4/Query/Api4SelectQuery.php b/Civi/Api4/Query/Api4SelectQuery.php
index f08bbbf6be..a6e0eaed08 100644
--- a/Civi/Api4/Query/Api4SelectQuery.php
+++ b/Civi/Api4/Query/Api4SelectQuery.php
@@ -458,6 +458,13 @@ class Api4SelectQuery {
         $field = $this->getField($fieldName, TRUE);
         FormattingUtil::formatInputValue($value, $fieldName, $field, $operator);
       }
+      elseif ($expr->getType() === 'SqlFunction') {
+        $fauxField = [
+          'name' => NULL,
+          'data_type' => $expr::getDataType(),
+        ];
+        FormattingUtil::formatInputValue($value, NULL, $fauxField, $operator);
+      }
       $fieldAlias = $expr->render($this->apiFieldSpec);
     }
     // For HAVING, expr must be an item in the SELECT clause
diff --git a/Civi/Api4/Utils/FormattingUtil.php b/Civi/Api4/Utils/FormattingUtil.php
index 140e149244..87ee2d7ae3 100644
--- a/Civi/Api4/Utils/FormattingUtil.php
+++ b/Civi/Api4/Utils/FormattingUtil.php
@@ -80,14 +80,14 @@ class FormattingUtil {
    * This is used by read AND write actions (Get, Create, Update, Replace)
    *
    * @param $value
-   * @param string $fieldName
+   * @param string|null $fieldName
    * @param array $fieldSpec
-   * @param string $operator (only for 'get' actions)
-   * @param int $index (for recursive loops)
+   * @param string|null $operator (only for 'get' actions)
+   * @param null $index (for recursive loops)
    * @throws \API_Exception
    * @throws \CRM_Core_Exception
    */
-  public static function formatInputValue(&$value, $fieldName, $fieldSpec, &$operator = NULL, $index = NULL) {
+  public static function formatInputValue(&$value, ?string $fieldName, array $fieldSpec, &$operator = NULL, $index = NULL) {
     // Evaluate pseudoconstant suffix
     $suffix = strpos($fieldName, ':');
     if ($suffix) {
@@ -126,7 +126,7 @@ class FormattingUtil {
     }
 
     $hic = \CRM_Utils_API_HTMLInputCoder::singleton();
-    if (is_string($value) && !$hic->isSkippedField($fieldSpec['name'])) {
+    if (is_string($value) && $fieldName && !$hic->isSkippedField($fieldSpec['name'])) {
       $value = $hic->encodeValue($value);
     }
   }
@@ -142,7 +142,7 @@ class FormattingUtil {
    * @param $index
    * @return array|string
    */
-  private static function formatDateValue($format, $value, &$operator = NULL, $index = NULL) {
+  public static function formatDateValue($format, $value, &$operator = NULL, $index = NULL) {
     // Non-relative dates (or if no search operator)
     if (!$operator || !array_key_exists($value, \CRM_Core_OptionGroup::values('relative_date_filters'))) {
       return date($format, strtotime($value));
diff --git a/ext/search_kit/Civi/Search/Admin.php b/ext/search_kit/Civi/Search/Admin.php
index 21f87cfff6..abb082079f 100644
--- a/ext/search_kit/Civi/Search/Admin.php
+++ b/ext/search_kit/Civi/Search/Admin.php
@@ -454,6 +454,9 @@ class Admin {
       // Normalize this property name to match fields data_type
       $function['data_type'] = $function['dataType'] ?? NULL;
       unset($function['dataType']);
+      if ($function['data_type'] === 'Date') {
+        $function['input_type'] = 'Date';
+      }
       // Filter out empty param properties (simplifies the javascript which treats empty arrays/objects as != null)
       foreach ($function['params'] as $i => $param) {
         $function['params'][$i] = array_filter($param);
diff --git a/tests/phpunit/api/v4/Action/DateTest.php b/tests/phpunit/api/v4/Action/DateTest.php
index 255534510c..1e58c5e772 100644
--- a/tests/phpunit/api/v4/Action/DateTest.php
+++ b/tests/phpunit/api/v4/Action/DateTest.php
@@ -107,8 +107,9 @@ class DateTest extends UnitTestCase {
     $this->assertContains($act[5], $result);
     $this->assertContains($act[6], $result);
 
+    // Ensure it also works if the DATE() function is used
     $result = Activity::get(FALSE)->addSelect('id')
-      ->addWhere('activity_date_time', '>=', 'this.year')
+      ->addWhere('DATE(activity_date_time)', '>=', 'this.year')
       ->execute()->column('id');
     $this->assertNotContains($act[0], $result);
     $this->assertNotContains($act[1], $result);
-- 
2.25.1