From 5e8514166ad230dabec5b54b137b5e3b322bf29c Mon Sep 17 00:00:00 2001
From: Brian Shaughnessy <brian@lcdservices.biz>
Date: Sat, 1 Apr 2017 13:58:52 -0400
Subject: [PATCH] CRM-20368 case detail report

---
 CRM/Report/Form/Case/Detail.php | 38 ++++++++++++++++++++++++++++++++-
 1 file changed, 37 insertions(+), 1 deletion(-)

diff --git a/CRM/Report/Form/Case/Detail.php b/CRM/Report/Form/Case/Detail.php
index bae31bfc87..bb5f1a115b 100644
--- a/CRM/Report/Form/Case/Detail.php
+++ b/CRM/Report/Form/Case/Detail.php
@@ -54,6 +54,8 @@ class CRM_Report_Form_Case_Detail extends CRM_Report_Form {
 
   protected $_customGroupExtends = array('Case');
 
+  protected $_caseTypeNameOrderBy = FALSE;
+
   /**
    */
   public function __construct() {
@@ -135,6 +137,18 @@ class CRM_Report_Form_Case_Detail extends CRM_Report_Form {
             'default' => 0,
           ),
         ),
+        'order_bys'  => array(
+          'start_date' => array(
+            'title' => ts('Start Date'),
+            'default_weight' => 1,
+          ),
+          'end_date' => array(
+            'title' => ts('End Date'),
+          ),
+          'status_id' => array(
+            'title' => ts('Status'),
+          ),
+        ),
       ),
       'civicrm_contact' => array(
         'dao' => 'CRM_Contact_DAO_Contact',
@@ -336,6 +350,16 @@ class CRM_Report_Form_Case_Detail extends CRM_Report_Form {
         }
       }
     }
+
+    if ($orderBys = $this->_params['order_bys']) {
+      foreach ($orderBys as $orderBy) {
+        if ($orderBy['column'] == 'case_type_name') {
+          $select[] = "civireport_case_types.title as case_type_name";
+          $this->_caseTypeNameOrderBy = TRUE;
+        }
+      }
+    }
+
     $this->_selectClauses = $select;
 
     $this->_select = 'SELECT ' . implode(', ', $select) . ' ';
@@ -392,6 +416,14 @@ class CRM_Report_Form_Case_Detail extends CRM_Report_Form {
     if ($this->_activityLastCompleted) {
       $this->_from .= " LEFT JOIN civicrm_activity {$this->_aliases['civicrm_activity_last_completed']} ON ( {$this->_aliases['civicrm_activity_last_completed']}.id = ( SELECT max(activity_id) FROM civicrm_case_activity cca, civicrm_activity ca WHERE ca.id = cca.activity_id AND cca.case_id = {$case}.id AND ca.status_id = 2 ) )";
     }
+
+    //include case type name
+    if ($this->_caseTypeNameOrderBy) {
+      $this->_from .= "
+        LEFT JOIN civicrm_case_type civireport_case_types
+          ON {$case}.case_type_id = civireport_case_types.id
+      ";
+    }
   }
 
   public function where() {
@@ -480,7 +512,11 @@ class CRM_Report_Form_Case_Detail extends CRM_Report_Form {
   }
 
   public function orderBy() {
-    $this->_orderBy = " ORDER BY {$this->_aliases['civicrm_case']}.start_date DESC ";
+    parent::orderBy();
+
+    if ($this->_caseTypeNameOrderBy) {
+      $this->_orderBy = str_replace('case_civireport.case_type_name', 'civireport_case_types.title', $this->_orderBy);
+    }
   }
 
   public function caseDetailSpecialColumnProcess() {
-- 
2.25.1