From 2e44c7ca624abf859612eb18825a4e7d5f69473a Mon Sep 17 00:00:00 2001
From: Aidan Saunders <aidan.saunders@squiffle.uk>
Date: Fri, 14 Apr 2023 16:28:21 +0100
Subject: [PATCH] Add DAYSTOANNIV function

---
 Civi/Api4/Query/SqlFunction.php               | 12 +++-
 Civi/Api4/Query/SqlFunctionDAYSTOANNIV.php    | 60 +++++++++++++++++++
 .../phpunit/api/v4/Action/ContactGetTest.php  |  4 +-
 3 files changed, 74 insertions(+), 2 deletions(-)
 create mode 100644 Civi/Api4/Query/SqlFunctionDAYSTOANNIV.php

diff --git a/Civi/Api4/Query/SqlFunction.php b/Civi/Api4/Query/SqlFunction.php
index 7b00b6f2c9..74d17cb97f 100644
--- a/Civi/Api4/Query/SqlFunction.php
+++ b/Civi/Api4/Query/SqlFunction.php
@@ -17,7 +17,7 @@ namespace Civi\Api4\Query;
  * SqlFunction classes don't actually process data, SQL itself does the real work.
  * The role of each SqlFunction class is to:
  *
- * 1. Whitelist the SQL function for use by APIv4 (it doesn't allow any that don't have a SQLFunction class).
+ * 1. Whitelist a standard SQL function, or define a custom one, for use by APIv4 (it doesn't allow any that don't have a SQLFunction class).
  * 2. Document what the function does and what arguments it accepts.
  * 3. Tell APIv4 how to treat the inputs and how to format the outputs.
  *
@@ -124,6 +124,16 @@ abstract class SqlFunction extends SqlExpression {
         $output .= (strlen($output) ? ' ' : '') . $rendered;
       }
     }
+    return $this->renderExpression($output);
+  }
+
+  /**
+   * Render the final expression
+   *
+   * @param string $output
+   * @return string
+   */
+  protected function renderExpression($output): string {
     return $this->getName() . '(' . $output . ')';
   }
 
diff --git a/Civi/Api4/Query/SqlFunctionDAYSTOANNIV.php b/Civi/Api4/Query/SqlFunctionDAYSTOANNIV.php
new file mode 100644
index 0000000000..b47a4a1b31
--- /dev/null
+++ b/Civi/Api4/Query/SqlFunctionDAYSTOANNIV.php
@@ -0,0 +1,60 @@
+<?php
+/*
+ +--------------------------------------------------------------------+
+ | Copyright CiviCRM LLC. All rights reserved.                        |
+ |                                                                    |
+ | This work is published under the GNU AGPLv3 license with some      |
+ | permitted exceptions and without any warranty. For full license    |
+ | and copyright information, see https://civicrm.org/licensing       |
+ +--------------------------------------------------------------------+
+ */
+
+namespace Civi\Api4\Query;
+
+/**
+ * Sql function
+ */
+class SqlFunctionDAYSTOANNIV extends SqlFunction {
+
+  protected static $category = self::CATEGORY_DATE;
+
+  protected static $dataType = 'Integer';
+
+  protected static function params(): array {
+    return [
+      [
+        'max_expr' => 1,
+        'optional' => FALSE,
+      ],
+    ];
+  }
+
+  /**
+   * @return string
+   */
+  public static function getTitle(): string {
+    return ts('Days to Anniversary');
+  }
+
+  /**
+   * @return string
+   */
+  public static function getDescription(): string {
+    return ts('Number of days until the next anniversary of this date.');
+  }
+
+  /**
+   * @inheritDoc
+   */
+  protected function renderExpression($output): string {
+    return "DATEDIFF(
+      IF(
+          DATE(CONCAT(YEAR(CURDATE()), '-', MONTH({$output}), '-', DAY({$output}))) < CURDATE(),
+          CONCAT(YEAR(CURDATE()) + 1, '-', MONTH({$output}), '-', DAY({$output})),
+          CONCAT(YEAR(CURDATE()), '-', MONTH({$output}), '-', DAY({$output}))
+      ),
+      CURDATE()
+    )";
+  }
+
+}
diff --git a/tests/phpunit/api/v4/Action/ContactGetTest.php b/tests/phpunit/api/v4/Action/ContactGetTest.php
index 7d3d82ee42..93d9eb9314 100644
--- a/tests/phpunit/api/v4/Action/ContactGetTest.php
+++ b/tests/phpunit/api/v4/Action/ContactGetTest.php
@@ -335,13 +335,15 @@ class ContactGetTest extends Api4TestBase implements TransactionalInterface {
 
     $result = Contact::get(FALSE)
       ->addWhere('last_name', '=', $lastName)
-      ->addSelect('first_name', 'age_years', 'next_birthday')
+      ->addSelect('first_name', 'age_years', 'next_birthday', 'DAYSTOANNIV(birth_date)')
       ->execute()->indexBy('first_name');
     $this->assertEquals(1, $result['abc']['age_years']);
     $this->assertEquals(3, $result['abc']['next_birthday']);
+    $this->assertEquals(3, $result['abc']['DAYSTOANNIV:birth_date']);
     $this->assertEquals(21, $result['def']['age_years']);
     $this->assertEquals(0, $result['ghi']['age_years']);
     $this->assertEquals(0, $result['ghi']['next_birthday']);
+    $this->assertEquals(0, $result['ghi']['DAYSTOANNIV:birth_date']);
 
     Contact::get(FALSE)
       ->addWhere('age_years', '=', 21)
-- 
2.25.1