From dbc32dc1014ec0751abdcb8052e66d4261ccc31c Mon Sep 17 00:00:00 2001
From: larssandergreen <lars@wildsight.ca>
Date: Wed, 17 May 2023 18:06:29 -0600
Subject: [PATCH] Export dates from SK Download Spreadsheet in SQL format

---
 .../Action/SearchDisplay/AbstractRunAction.php  | 17 ++++++++---------
 .../Civi/Api4/Action/SearchDisplay/Download.php | 17 +++++++++++++++++
 2 files changed, 25 insertions(+), 9 deletions(-)

diff --git a/ext/search_kit/Civi/Api4/Action/SearchDisplay/AbstractRunAction.php b/ext/search_kit/Civi/Api4/Action/SearchDisplay/AbstractRunAction.php
index b438c72b01..e61ae577f1 100644
--- a/ext/search_kit/Civi/Api4/Action/SearchDisplay/AbstractRunAction.php
+++ b/ext/search_kit/Civi/Api4/Action/SearchDisplay/AbstractRunAction.php
@@ -207,7 +207,8 @@ abstract class AbstractRunAction extends \Civi\Api4\Generic\AbstractAction {
           $out['val'] = $this->rewrite($column, $data);
         }
         else {
-          $out['val'] = $this->formatViewValue($column['key'], $rawValue, $data);
+          $dataType = $this->getSelectExpression($column['key'])['dataType'] ?? NULL;
+          $out['val'] = $this->formatViewValue($column['key'], $rawValue, $data, $dataType);
         }
         if ($this->hasValue($column['label']) && (!empty($column['forceLabel']) || $this->hasValue($out['val']))) {
           $out['label'] = $this->replaceTokens($column['label'], $data, 'view');
@@ -736,7 +737,8 @@ abstract class AbstractRunAction extends \Civi\Api4\Generic\AbstractAction {
       foreach ($this->getTokens($tokenExpr) as $token) {
         $val = $data[$token] ?? NULL;
         if (isset($val) && $format === 'view') {
-          $val = $this->formatViewValue($token, $val, $data);
+          $dataType = $this->getSelectExpression($token)['dataType'] ?? NULL;
+          $val = $this->formatViewValue($token, $val, $data, $dataType);
         }
         if (!(is_null($index))) {
           $replacement = is_array($val) ? $val[$index] ?? '' : $val;
@@ -759,17 +761,16 @@ abstract class AbstractRunAction extends \Civi\Api4\Generic\AbstractAction {
    * @param string $key
    * @param mixed $rawValue
    * @param array $data
+   * @param string $dataType
    * @return array|string
    */
-  protected function formatViewValue($key, $rawValue, $data) {
+  protected function formatViewValue($key, $rawValue, $data, $dataType) {
     if (is_array($rawValue)) {
       return array_map(function($val) use ($key, $data) {
         return $this->formatViewValue($key, $val, $data);
-      }, $rawValue);
+      }, $rawValue, $dataType);
     }
 
-    $dataType = $this->getSelectExpression($key)['dataType'] ?? NULL;
-
     $formatted = $rawValue;
 
     switch ($dataType) {
@@ -787,9 +788,7 @@ abstract class AbstractRunAction extends \Civi\Api4\Generic\AbstractAction {
 
       case 'Date':
       case 'Timestamp':
-        if (!(isset($this->format) && in_array($this->format, ['csv', 'xlsx', 'ods']))) {
-          $formatted = \CRM_Utils_Date::customFormat($rawValue);
-        }
+        $formatted = \CRM_Utils_Date::customFormat($rawValue);
     }
 
     return $formatted;
diff --git a/ext/search_kit/Civi/Api4/Action/SearchDisplay/Download.php b/ext/search_kit/Civi/Api4/Action/SearchDisplay/Download.php
index 9429a17604..6757e5d4d8 100644
--- a/ext/search_kit/Civi/Api4/Action/SearchDisplay/Download.php
+++ b/ext/search_kit/Civi/Api4/Action/SearchDisplay/Download.php
@@ -111,6 +111,23 @@ class Download extends AbstractRunAction {
     \CRM_Utils_System::civiExit();
   }
 
+  /**
+   * Return raw value if it is a single date, otherwise return parent
+   * {@inheritDoc}
+   */
+  protected function formatViewValue($key, $rawValue, $data, $dataType) {
+    if (is_array($rawValue)) {
+      return parent::formatViewValue($key, $rawValue, $data, $dataType);
+    }
+
+    if (($dataType === 'Date' || $dataType === 'Timestamp') && in_array($this->format, ['csv', 'xlsx', 'ods'])) {
+      return $rawValue;
+    }
+    else {
+      return parent::formatViewValue($key, $rawValue, $data, $dataType);
+    }
+  }
+
   /**
    * Outputs headers and CSV directly to browser for download
    * @param array $rows
-- 
2.25.1