From 10ab0ceb2a2eb9944fc502bc15581821064e96f1 Mon Sep 17 00:00:00 2001
From: Aidan Saunders <aidan.saunders@squiffle.uk>
Date: Fri, 16 Jun 2023 15:39:14 +0100
Subject: [PATCH] Add LEFT, RIGHT & SUBSTRING SQL functions, with a test

---
 Civi/Api4/Query/SqlFunctionLEFT.php           | 52 +++++++++++++++++
 Civi/Api4/Query/SqlFunctionRIGHT.php          | 52 +++++++++++++++++
 Civi/Api4/Query/SqlFunctionSUBSTRING.php      | 57 +++++++++++++++++++
 .../phpunit/api/v4/Action/SqlFunctionTest.php |  6 ++
 4 files changed, 167 insertions(+)
 create mode 100644 Civi/Api4/Query/SqlFunctionLEFT.php
 create mode 100644 Civi/Api4/Query/SqlFunctionRIGHT.php
 create mode 100644 Civi/Api4/Query/SqlFunctionSUBSTRING.php

diff --git a/Civi/Api4/Query/SqlFunctionLEFT.php b/Civi/Api4/Query/SqlFunctionLEFT.php
new file mode 100644
index 0000000000..08a341a4e5
--- /dev/null
+++ b/Civi/Api4/Query/SqlFunctionLEFT.php
@@ -0,0 +1,52 @@
+<?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 SqlFunctionLEFT extends SqlFunction {
+
+  protected static $category = self::CATEGORY_STRING;
+
+  protected static $dataType = 'String';
+
+  protected static function params(): array {
+    return [
+      [
+        'optional' => FALSE,
+        'must_be' => ['SqlField', 'SqlString'],
+        'label' => ts('Source'),
+      ],
+      [
+        'optional' => FALSE,
+        'must_be' => ['SqlNumber'],
+        'label' => ts('Number of characters'),
+      ],
+    ];
+  }
+
+  /**
+   * @return string
+   */
+  public static function getTitle(): string {
+    return ts('Left part of text');
+  }
+
+  /**
+   * @return string
+   */
+  public static function getDescription(): string {
+    return ts('Extracts a number of characters from text starting from the left.');
+  }
+
+}
diff --git a/Civi/Api4/Query/SqlFunctionRIGHT.php b/Civi/Api4/Query/SqlFunctionRIGHT.php
new file mode 100644
index 0000000000..71015c0bec
--- /dev/null
+++ b/Civi/Api4/Query/SqlFunctionRIGHT.php
@@ -0,0 +1,52 @@
+<?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 SqlFunctionRIGHT extends SqlFunction {
+
+  protected static $category = self::CATEGORY_STRING;
+
+  protected static $dataType = 'String';
+
+  protected static function params(): array {
+    return [
+      [
+        'optional' => FALSE,
+        'must_be' => ['SqlField', 'SqlString'],
+        'label' => ts('Source'),
+      ],
+      [
+        'optional' => FALSE,
+        'must_be' => ['SqlNumber'],
+        'label' => ts('Number of characters'),
+      ],
+    ];
+  }
+
+  /**
+   * @return string
+   */
+  public static function getTitle(): string {
+    return ts('Right part of text');
+  }
+
+  /**
+   * @return string
+   */
+  public static function getDescription(): string {
+    return ts('Extracts a number of characters from text starting from the right.');
+  }
+
+}
diff --git a/Civi/Api4/Query/SqlFunctionSUBSTRING.php b/Civi/Api4/Query/SqlFunctionSUBSTRING.php
new file mode 100644
index 0000000000..b9e7332731
--- /dev/null
+++ b/Civi/Api4/Query/SqlFunctionSUBSTRING.php
@@ -0,0 +1,57 @@
+<?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 SqlFunctionSUBSTRING extends SqlFunction {
+
+  protected static $category = self::CATEGORY_STRING;
+
+  protected static $dataType = 'String';
+
+  protected static function params(): array {
+    return [
+      [
+        'optional' => FALSE,
+        'must_be' => ['SqlField', 'SqlString'],
+        'label' => ts('Source'),
+      ],
+      [
+        'optional' => FALSE,
+        'must_be' => ['SqlNumber'],
+        'label' => ts('Starting position in string.  Negative numbers count from the end of the string.'),
+      ],
+      [
+        'optional' => TRUE,
+        'must_be' => ['SqlNumber'],
+        'label' => ts('Number of characters'),
+      ],
+    ];
+  }
+
+  /**
+   * @return string
+   */
+  public static function getTitle(): string {
+    return ts('Extract part of text');
+  }
+
+  /**
+   * @return string
+   */
+  public static function getDescription(): string {
+    return ts('Extracts a number of characters from text starting from a given position.');
+  }
+
+}
diff --git a/tests/phpunit/api/v4/Action/SqlFunctionTest.php b/tests/phpunit/api/v4/Action/SqlFunctionTest.php
index 329c4fefc7..a538ed8052 100644
--- a/tests/phpunit/api/v4/Action/SqlFunctionTest.php
+++ b/tests/phpunit/api/v4/Action/SqlFunctionTest.php
@@ -226,12 +226,18 @@ class SqlFunctionTest extends Api4TestBase implements TransactionalInterface {
       ->addSelect('REPLACE(first_name, "c", "cdef") AS new_first')
       ->addSelect('UPPER(first_name)')
       ->addSelect('LOWER(middle_name)')
+      ->addSelect('LEFT(last_name, 3) AS left_last')
+      ->addSelect('RIGHT(last_name, 3) AS right_last')
+      ->addSelect('SUBSTRING(last_name, 2, 3) AS sub_last')
       ->execute()->first();
 
     $this->assertEquals('abc|Q|tester1', $result['concat_ws']);
     $this->assertEquals('abcdef', $result['new_first']);
     $this->assertEquals('ABC', $result['UPPER:first_name']);
     $this->assertEquals('q', $result['LOWER:middle_name']);
+    $this->assertEquals('tes', $result['left_last']);
+    $this->assertEquals('er1', $result['right_last']);
+    $this->assertEquals('est', $result['sub_last']);
   }
 
   public function testDateFunctions() {
-- 
2.25.1