From 242220e23122d05860cf591eeac6521ea34dd5fc Mon Sep 17 00:00:00 2001
From: Coleman Watts <coleman@civicrm.org>
Date: Mon, 27 Mar 2017 15:46:08 -0400
Subject: [PATCH] CRM-20345 - Case API - Sort by client

---
 api/v3/Case.php                   | 29 +++++++++++++
 tests/phpunit/api/v3/CaseTest.php | 68 +++++++++++++++++++++++++++++++
 2 files changed, 97 insertions(+)

diff --git a/api/v3/Case.php b/api/v3/Case.php
index 0169c26ff8..d4c34c86da 100644
--- a/api/v3/Case.php
+++ b/api/v3/Case.php
@@ -240,6 +240,35 @@ function civicrm_api3_case_get($params) {
     $sql->where("a.id IN (SELECT case_id FROM civicrm_case_contact WHERE $clause)");
   }
 
+  // Order by case contact (primary client)
+  // Ex: "contact_id", "contact_id.display_name", "contact_id.sort_name DESC".
+  if (!empty($options['sort']) && strpos($options['sort'], 'contact_id') !== FALSE) {
+    $sort = explode(', ', $options['sort']);
+    $contactSort = NULL;
+    foreach ($sort as $index => $sortString) {
+      if (strpos($sortString, 'contact_id') === 0) {
+        $contactSort = $sortString;
+        unset($sort[$index]);
+        // Get sort field and direction
+        list($sortField, $dir) = array_pad(explode(' ', $contactSort), 2, 'ASC');
+        list(, $sortField) = array_pad(explode('.', $sortField), 2, 'id');
+        // Validate inputs
+        if (!array_key_exists($sortField, CRM_Contact_DAO_Contact::fieldKeys()) || ($dir != 'ASC' && $dir != 'DESC')) {
+          throw new API_Exception("Unknown field specified for sort. Cannot order by '$contactSort'");
+        }
+        $sql->orderBy("case_contact.$sortField $dir");
+      }
+    }
+    // Remove contact sort params so the basic_get function doesn't see them
+    $params['options']['sort'] = implode(', ', $sort);
+    unset($params['option_sort'], $params['option.sort'], $params['sort']);
+    // Add necessary joins to the first case client
+    if ($contactSort) {
+      $sql->join('ccc', 'LEFT JOIN (SELECT * FROM civicrm_case_contact WHERE id IN (SELECT MIN(id) FROM civicrm_case_contact GROUP BY case_id)) AS ccc ON ccc.case_id = a.id');
+      $sql->join('case_contact', 'LEFT JOIN civicrm_contact AS case_contact ON ccc.contact_id = case_contact.id');
+    }
+  }
+
   // Add clause to search by activity
   if (!empty($params['activity_id'])) {
     if (!CRM_Utils_Rule::positiveInteger($params['activity_id'])) {
diff --git a/tests/phpunit/api/v3/CaseTest.php b/tests/phpunit/api/v3/CaseTest.php
index cced2f112c..bde930a222 100644
--- a/tests/phpunit/api/v3/CaseTest.php
+++ b/tests/phpunit/api/v3/CaseTest.php
@@ -563,4 +563,72 @@ class api_v3_CaseTest extends CiviCaseTestCase {
     $this->assertEquals(array($case1['id'], $case2['id']), array_keys(CRM_Utils_Array::rekey($result['api.Case.get']['values'], 'id')));
   }
 
+  /**
+   * Test the ability to order by client using the join syntax.
+   *
+   * For multi-client cases, should order by the first client.
+   */
+  public function testCaseGetOrderByClient() {
+    $contact1 = $this->individualCreate(array('first_name' => 'Aa', 'last_name' => 'Zz'));
+    $contact2 = $this->individualCreate(array('first_name' => 'Bb', 'last_name' => 'Yy'));
+    $contact3 = $this->individualCreate(array('first_name' => 'Cc', 'last_name' => 'Xx'));
+
+    $case1 = $this->callAPISuccess('Case', 'create', array(
+      'contact_id' => $contact1,
+      'subject' => "Test case 1",
+      'case_type_id' => $this->caseTypeId,
+    ));
+    $case2 = $this->callAPISuccess('Case', 'create', array(
+      'contact_id' => $contact2,
+      'subject' => "Test case 2",
+      'case_type_id' => $this->caseTypeId,
+    ));
+    $case3 = $this->callAPISuccess('Case', 'create', array(
+      'contact_id' => array($contact3, $contact1),
+      'subject' => "Test case 3",
+      'case_type_id' => $this->caseTypeId,
+    ));
+
+    $result = $this->callAPISuccess('Case', 'get', array(
+      'contact_id' => array('IN' => array($contact1, $contact2, $contact3)),
+      'sequential' => 1,
+      'return' => 'id',
+      'options' => array('sort' => 'contact_id.first_name'),
+    ));
+    $this->assertEquals($case3['id'], $result['values'][2]['id']);
+    $this->assertEquals($case2['id'], $result['values'][1]['id']);
+    $this->assertEquals($case1['id'], $result['values'][0]['id']);
+
+    $result = $this->callAPISuccess('Case', 'get', array(
+      'contact_id' => array('IN' => array($contact1, $contact2, $contact3)),
+      'sequential' => 1,
+      'return' => 'id',
+      'options' => array('sort' => 'contact_id.last_name ASC'),
+    ));
+    $this->assertEquals($case1['id'], $result['values'][2]['id']);
+    $this->assertEquals($case2['id'], $result['values'][1]['id']);
+    $this->assertEquals($case3['id'], $result['values'][0]['id']);
+
+    $result = $this->callAPISuccess('Case', 'get', array(
+      'contact_id' => array('IN' => array($contact1, $contact2, $contact3)),
+      'sequential' => 1,
+      'return' => 'id',
+      'options' => array('sort' => 'contact_id.first_name DESC'),
+    ));
+    $this->assertEquals($case1['id'], $result['values'][2]['id']);
+    $this->assertEquals($case2['id'], $result['values'][1]['id']);
+    $this->assertEquals($case3['id'], $result['values'][0]['id']);
+
+    $result = $this->callAPISuccess('Case', 'get', array(
+      'contact_id' => array('IN' => array($contact1, $contact2, $contact3)),
+      'sequential' => 1,
+      'return' => 'id',
+      'options' => array('sort' => 'contact_id DESC'),
+    ));
+    $this->assertEquals($case1['id'], $result['values'][2]['id']);
+    $this->assertEquals($case2['id'], $result['values'][1]['id']);
+    $this->assertEquals($case3['id'], $result['values'][0]['id']);
+    $this->assertCount(3, $result['values']);
+  }
+
 }
-- 
2.25.1