From 5c9d1caa3d401e61d8aed92d98d0ad3534749cac Mon Sep 17 00:00:00 2001
From: Coleman Watts <coleman@civicrm.org>
Date: Mon, 6 Mar 2023 14:18:12 -0500
Subject: [PATCH] SearchKit - Add next_birthday calc field

---
 .../Spec/Provider/ContactGetSpecProvider.php  | 36 ++++++++++++++++++-
 .../phpunit/api/v4/Action/ContactGetTest.php  |  8 +++--
 2 files changed, 41 insertions(+), 3 deletions(-)

diff --git a/Civi/Api4/Service/Spec/Provider/ContactGetSpecProvider.php b/Civi/Api4/Service/Spec/Provider/ContactGetSpecProvider.php
index e6cb6c8e94..ffc8efa056 100644
--- a/Civi/Api4/Service/Spec/Provider/ContactGetSpecProvider.php
+++ b/Civi/Api4/Service/Spec/Provider/ContactGetSpecProvider.php
@@ -40,8 +40,9 @@ class ContactGetSpecProvider extends \Civi\Core\Service\AutoService implements G
       ->setOptionsCallback([__CLASS__, 'getGroupList']);
     $spec->addFieldSpec($field);
 
-    // Age field
+    // Fields specific to Individuals
     if (!$spec->getValue('contact_type') || $spec->getValue('contact_type') === 'Individual') {
+      // Age field
       $field = new FieldSpec('age_years', 'Contact', 'Integer');
       $field->setLabel(ts('Age (years)'))
         ->setTitle(ts('Age (years)'))
@@ -52,6 +53,20 @@ class ContactGetSpecProvider extends \Civi\Core\Service\AutoService implements G
         ->setReadonly(TRUE)
         ->setSqlRenderer([__CLASS__, 'calculateAge']);
       $spec->addFieldSpec($field);
+
+      // Birthday field
+      if (!$spec->getValue('contact_type') || $spec->getValue('contact_type') === 'Individual') {
+        $field = new FieldSpec('next_birthday', 'Contact', 'Integer');
+        $field->setLabel(ts('Next Birthday in (days)'))
+          ->setTitle(ts('Next Birthday in (days)'))
+          ->setColumnName('birth_date')
+          ->setInputType('Number')
+          ->setDescription(ts('Number of days until next birthday'))
+          ->setType('Extra')
+          ->setReadonly(TRUE)
+          ->setSqlRenderer([__CLASS__, 'calculateBirthday']);
+        $spec->addFieldSpec($field);
+      }
     }
 
     // Address, Email, Phone, IM primary/billing virtual fields
@@ -183,4 +198,23 @@ class ContactGetSpecProvider extends \Civi\Core\Service\AutoService implements G
     return "TIMESTAMPDIFF(YEAR, {$field['sql_name']}, CURDATE())";
   }
 
+  /**
+   * Generate SQL for upcoming birthday field
+   *
+   * Calculates the number of days until the next birthday
+   *
+   * @param array $field
+   * @return string
+   */
+  public static function calculateBirthday(array $field): string {
+    return "DATEDIFF(
+        IF(
+            DATE(CONCAT(YEAR(CURDATE()), '-', MONTH({$field['sql_name']}), '-', DAY({$field['sql_name']}))) < CURDATE(),
+            CONCAT(YEAR(CURDATE()) + 1, '-', MONTH({$field['sql_name']}), '-', DAY({$field['sql_name']})),
+            CONCAT(YEAR(CURDATE()), '-', MONTH({$field['sql_name']}), '-', DAY({$field['sql_name']}))
+        ),
+        CURDATE()
+    )";
+  }
+
 }
diff --git a/tests/phpunit/api/v4/Action/ContactGetTest.php b/tests/phpunit/api/v4/Action/ContactGetTest.php
index e2ada7b656..7c44a0b6d0 100644
--- a/tests/phpunit/api/v4/Action/ContactGetTest.php
+++ b/tests/phpunit/api/v4/Action/ContactGetTest.php
@@ -327,17 +327,21 @@ class ContactGetTest extends Api4TestBase implements TransactionalInterface {
   public function testAge(): void {
     $lastName = uniqid(__FUNCTION__);
     $sampleData = [
-      ['first_name' => 'abc', 'last_name' => $lastName, 'birth_date' => 'now - 1 year - 1 month'],
+      ['first_name' => 'abc', 'last_name' => $lastName, 'birth_date' => 'now - 2 year + 3 day'],
       ['first_name' => 'def', 'last_name' => $lastName, 'birth_date' => 'now - 21 year - 6 month'],
+      ['first_name' => 'ghi', 'last_name' => $lastName, 'birth_date' => 'now'],
     ];
     $this->saveTestRecords('Contact', ['records' => $sampleData]);
 
     $result = Contact::get(FALSE)
       ->addWhere('last_name', '=', $lastName)
-      ->addSelect('first_name', 'age_years')
+      ->addSelect('first_name', 'age_years', 'next_birthday')
       ->execute()->indexBy('first_name');
     $this->assertEquals(1, $result['abc']['age_years']);
+    $this->assertEquals(3, $result['abc']['next_birthday']);
     $this->assertEquals(21, $result['def']['age_years']);
+    $this->assertEquals(0, $result['ghi']['age_years']);
+    $this->assertEquals(0, $result['ghi']['next_birthday']);
 
     Contact::get(FALSE)
       ->addWhere('age_years', '=', 21)
-- 
2.25.1