From 0f73dba3813aa5bb2d2e135ae47fb4c6fbdd08fa Mon Sep 17 00:00:00 2001
From: Matthew Wire <mjw@mjwconsult.co.uk>
Date: Fri, 17 Sep 2021 12:32:48 +0100
Subject: [PATCH] Fix issues with case sorting/searching from case dashboard

---
 CRM/Case/BAO/Case.php        | 7 +++++++
 CRM/Case/Selector/Search.php | 6 +++---
 2 files changed, 10 insertions(+), 3 deletions(-)

diff --git a/CRM/Case/BAO/Case.php b/CRM/Case/BAO/Case.php
index 7dc6181318..8b0c90d5ad 100644
--- a/CRM/Case/BAO/Case.php
+++ b/CRM/Case/BAO/Case.php
@@ -416,6 +416,8 @@ WHERE cc.contact_id = %1 AND civicrm_case_type.name = '{$caseType}'";
       "GROUP_CONCAT(DISTINCT IF(case_relationship.contact_id_b = $userID, case_relation_type.label_a_b, case_relation_type.label_b_a) SEPARATOR ', ') as case_role",
       't_act.activity_date_time as activity_date_time',
       't_act.id as activity_id',
+      'case_status.label AS case_status',
+      'civicrm_case_type.title AS case_type',
     ];
 
     $query = CRM_Contact_BAO_Query::appendAnyValueToSelect($selectClauses, 'case_id');
@@ -424,6 +426,11 @@ WHERE cc.contact_id = %1 AND civicrm_case_type.name = '{$caseType}'";
       FROM civicrm_case
         INNER JOIN civicrm_case_contact ON civicrm_case.id = civicrm_case_contact.case_id
         INNER JOIN civicrm_contact ON civicrm_case_contact.contact_id = civicrm_contact.id
+        LEFT JOIN civicrm_case_type ON civicrm_case.case_type_id = civicrm_case_type.id
+        LEFT JOIN civicrm_option_group option_group_case_status ON ( option_group_case_status.name = 'case_status' )
+        LEFT JOIN civicrm_option_value case_status ON ( civicrm_case.status_id = case_status.value
+          AND option_group_case_status.id = case_status.option_group_id )
+
 HERESQL;
 
     // 'upcoming' and 'recent' show the next scheduled and most recent
diff --git a/CRM/Case/Selector/Search.php b/CRM/Case/Selector/Search.php
index a0aabae81c..0d70d73967 100644
--- a/CRM/Case/Selector/Search.php
+++ b/CRM/Case/Selector/Search.php
@@ -354,7 +354,7 @@ class CRM_Case_Selector_Search extends CRM_Core_Selector_Base {
       $rows[$result->case_id] = $row;
     }
 
-    //retrive the scheduled & recent Activity type and date for selector
+    //retrieve the scheduled & recent Activity type and date for selector
     if (!empty($scheduledInfo)) {
       $schdeduledActivity = CRM_Case_BAO_Case::getNextScheduledActivity($scheduledInfo, 'upcoming');
       foreach ($schdeduledActivity as $key => $value) {
@@ -417,12 +417,12 @@ class CRM_Case_Selector_Search extends CRM_Core_Selector_Base {
         ],
         [
           'name' => ts('Most Recent'),
-          'sort' => 'case_recent_activity_date',
+          // @fixme: Triggers DB error field not found on "Find Cases": 'sort' => 'case_recent_activity_date',
           'direction' => CRM_Utils_Sort::DONTCARE,
         ],
         [
           'name' => ts('Next Sched.'),
-          'sort' => 'case_scheduled_activity_date',
+          // @fixme: Triggers DB error field not found on "Find Cases": 'sort' => 'case_scheduled_activity_date',
           'direction' => CRM_Utils_Sort::DONTCARE,
         ],
         ['name' => ts('Actions')],
-- 
2.25.1