X-Git-Url: https://vcs.fsf.org/?a=blobdiff_plain;f=tests%2Fphpunit%2Fapi%2Fv4%2FAction%2FSqlFunctionTest.php;h=3fb5d18dc2d2b3a9a9bf6f24457294d9111de6e8;hb=46f571dd014906bdc43b4311e2ad74de2d899ea0;hp=59300c3692059b637dbca0d5d6ae720fbfaaf778;hpb=e6e3eea65e4505d58bed705bcae99f9a4c36d106;p=civicrm-core.git

diff --git a/tests/phpunit/api/v4/Action/SqlFunctionTest.php b/tests/phpunit/api/v4/Action/SqlFunctionTest.php
index 59300c3692..3fb5d18dc2 100644
--- a/tests/phpunit/api/v4/Action/SqlFunctionTest.php
+++ b/tests/phpunit/api/v4/Action/SqlFunctionTest.php
@@ -19,15 +19,16 @@
 
 namespace api\v4\Action;
 
-use api\v4\UnitTestCase;
+use api\v4\Api4TestBase;
 use Civi\Api4\Activity;
 use Civi\Api4\Contact;
 use Civi\Api4\Contribution;
+use Civi\Test\TransactionalInterface;
 
 /**
  * @group headless
  */
-class SqlFunctionTest extends UnitTestCase {
+class SqlFunctionTest extends Api4TestBase implements TransactionalInterface {
 
   public function testGetFunctions() {
     $functions = array_column(\CRM_Api4_Page_Api4Explorer::getSqlFunctions(), NULL, 'name');
@@ -245,21 +246,86 @@ class SqlFunctionTest extends UnitTestCase {
     }
   }
 
+  public function testCurrentDate() {
+    $lastName = uniqid(__FUNCTION__);
+    $sampleData = [
+      ['first_name' => 'abc', 'last_name' => $lastName, 'birth_date' => 'now'],
+      ['first_name' => 'def', 'last_name' => $lastName, 'birth_date' => 'now - 1 year'],
+      ['first_name' => 'def', 'last_name' => $lastName, 'birth_date' => 'now - 10 year'],
+    ];
+    Contact::save(FALSE)
+      ->setRecords($sampleData)
+      ->execute();
+
+    $result = Contact::get(FALSE)
+      ->addWhere('last_name', '=', $lastName)
+      ->addWhere('birth_date', '=', 'CURDATE()', TRUE)
+      ->selectRowCount()
+      ->execute();
+    $this->assertCount(1, $result);
+
+    $result = Contact::get(FALSE)
+      ->addWhere('last_name', '=', $lastName)
+      ->addWhere('birth_date', '<', 'DATE(NOW())', TRUE)
+      ->selectRowCount()
+      ->execute();
+    $this->assertCount(2, $result);
+  }
+
   public function testRandFunction() {
-    $cid = Contact::create(FALSE)
-      ->addValue('first_name', 'hello')
-      ->execute()->first()['id'];
+    Contact::save(FALSE)
+      ->setRecords(array_fill(0, 6, []))
+      ->execute();
 
     $result = Contact::get(FALSE)
       ->addSelect('RAND() AS rand')
       ->addOrderBy('RAND()')
       ->setDebug(TRUE)
-      ->setLimit(1)
+      ->setLimit(6)
       ->execute();
 
-    $this->assertStringContainsString('ORDER BY RAND()', $result->debug['sql'][0]);
-    $this->assertGreaterThanOrEqual(0, $result[0]['rand']);
-    $this->assertLessThan(1, $result[0]['rand']);
+    // Random numbers should have been ordered from least to greatest
+    $this->assertGreaterThanOrEqual($result[0]['rand'], $result[1]['rand']);
+    $this->assertGreaterThanOrEqual($result[1]['rand'], $result[2]['rand']);
+    $this->assertGreaterThanOrEqual($result[2]['rand'], $result[3]['rand']);
+    $this->assertGreaterThanOrEqual($result[3]['rand'], $result[4]['rand']);
+    $this->assertGreaterThanOrEqual($result[4]['rand'], $result[5]['rand']);
+  }
+
+  public function testDateInWhereClause() {
+    $lastName = uniqid(__FUNCTION__);
+    $sampleData = [
+      ['first_name' => 'abc', 'last_name' => $lastName, 'birth_date' => '2009-11-11'],
+      ['first_name' => 'def', 'last_name' => $lastName, 'birth_date' => '2009-01-01'],
+      ['first_name' => 'def', 'last_name' => $lastName, 'birth_date' => '2010-01-01'],
+    ];
+    Contact::save(FALSE)
+      ->setRecords($sampleData)
+      ->execute();
+
+    // Should work with isExpression=FALSE
+    $result = Contact::get(FALSE)
+      ->addWhere('last_name', '=', $lastName)
+      ->addWhere('YEAR(birth_date)', '=', 2009)
+      ->selectRowCount()
+      ->execute();
+    $this->assertCount(2, $result);
+
+    // Should work with isExpression=TRUE
+    $result = Contact::get(FALSE)
+      ->addWhere('last_name', '=', $lastName)
+      ->addWhere('YEAR(birth_date)', '=', 2009, TRUE)
+      ->selectRowCount()
+      ->execute();
+    $this->assertCount(2, $result);
+
+    // Try an expression in the value
+    $result = Contact::get(FALSE)
+      ->addWhere('last_name', '=', $lastName)
+      ->addWhere('MONTH(birth_date)', '=', 'MONTH("2030-11-12")', TRUE)
+      ->addSelect('birth_date')
+      ->execute()->single();
+    $this->assertEquals('2009-11-11', $result['birth_date']);
   }
 
 }