From d795db31b9b84331e7555e998aa70c2aa6e7ea67 Mon Sep 17 00:00:00 2001
From: Andrew West <me@awest.uk>
Date: Thu, 13 Apr 2023 19:41:26 +0100
Subject: [PATCH] Add sql QUARTER function

---
 Civi/Api4/Query/SqlFunctionQUARTER.php        | 46 +++++++++++++++++++
 .../phpunit/api/v4/Action/SqlFunctionTest.php |  3 ++
 2 files changed, 49 insertions(+)
 create mode 100644 Civi/Api4/Query/SqlFunctionQUARTER.php

diff --git a/Civi/Api4/Query/SqlFunctionQUARTER.php b/Civi/Api4/Query/SqlFunctionQUARTER.php
new file mode 100644
index 0000000000..8977e39436
--- /dev/null
+++ b/Civi/Api4/Query/SqlFunctionQUARTER.php
@@ -0,0 +1,46 @@
+<?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 SqlFunctionQUARTER 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('Quarter only');
+  }
+
+  /**
+   * @return string
+   */
+  public static function getDescription(): string {
+    return ts('The numeric quarter (1-4) of a date.');
+  }
+
+}
diff --git a/tests/phpunit/api/v4/Action/SqlFunctionTest.php b/tests/phpunit/api/v4/Action/SqlFunctionTest.php
index 3304c92639..ec13dcaaff 100644
--- a/tests/phpunit/api/v4/Action/SqlFunctionTest.php
+++ b/tests/phpunit/api/v4/Action/SqlFunctionTest.php
@@ -231,6 +231,7 @@ class SqlFunctionTest extends Api4TestBase implements TransactionalInterface {
     $result = Contact::get(FALSE)
       ->addSelect('DATEDIFF("2010-01-01", birth_date) AS diff')
       ->addSelect('YEAR(birth_date) AS year')
+      ->addSelect('QUARTER(birth_date) AS quarter')
       ->addSelect('MONTH(birth_date) AS month')
       ->addSelect('EXTRACT(YEAR_MONTH FROM birth_date) AS year_month')
       ->addWhere('last_name', '=', $lastName)
@@ -239,11 +240,13 @@ class SqlFunctionTest extends Api4TestBase implements TransactionalInterface {
 
     $this->assertEquals(51, $result[0]['diff']);
     $this->assertEquals(2009, $result[0]['year']);
+    $this->assertEquals(4, $result[0]['quarter']);
     $this->assertEquals(11, $result[0]['month']);
     $this->assertEquals('200911', $result[0]['year_month']);
 
     $this->assertEquals(0, $result[1]['diff']);
     $this->assertEquals(2010, $result[1]['year']);
+    $this->assertEquals(1, $result[1]['quarter']);
     $this->assertEquals(1, $result[1]['month']);
     $this->assertEquals('201001', $result[1]['year_month']);
   }
-- 
2.25.1