CRM/Case/BAO/Query.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   6  | This work is published under the GNU AGPLv3 license with some      |
   7  | permitted exceptions and without any warranty. For full license    |
   8  | and copyright information, see https://civicrm.org/licensing       |
   9  +--------------------------------------------------------------------+
  10  */
  11
  12 /**
  13  *
  14  * @package CRM
  15  * @copyright CiviCRM LLC https://civicrm.org/licensing
  16  */
  17 class CRM_Case_BAO_Query extends CRM_Core_BAO_Query {
  18
  19   /**
  20    * Get fields.
  21    *
  22    * @param bool $excludeActivityFields
  23    *
  24    * @return array
  25    */
  26   public static function &getFields($excludeActivityFields = FALSE) {
  27     $fields = CRM_Case_BAO_Case::exportableFields();
  28
  29     // add activity related fields
  30     if (!$excludeActivityFields) {
  31       $fields = array_merge($fields, CRM_Activity_BAO_Activity::exportableFields('Case'));
  32     }
  33
  34     return $fields;
  35   }
  36
  37   /**
  38    * Build select for Case.
  39    *
  40    * @param CRM_Contact_BAO_Query $query
  41    */
  42   public static function select(&$query) {
  43     if (($query->_mode & CRM_Contact_BAO_Query::MODE_CASE) || !empty($query->_returnProperties['case_id'])) {
  44       $query->_select['case_id'] = "civicrm_case.id as case_id";
  45       $query->_element['case_id'] = 1;
  46       $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
  47       $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
  48     }
  49
  50     if (!empty($query->_returnProperties['case_type_id'])) {
  51       $query->_select['case_type_id'] = "civicrm_case_type.id as case_type_id";
  52       $query->_element['case_type_id'] = 1;
  53       $query->_tables['case_type'] = $query->_whereTables['case_type'] = 1;
  54       $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
  55     }
  56
  57     if (!empty($query->_returnProperties['case_type'])) {
  58       $query->_select['case_type'] = "civicrm_case_type.title as case_type";
  59       $query->_element['case_type'] = 1;
  60       $query->_tables['case_type'] = $query->_whereTables['case_type'] = 1;
  61       $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
  62     }
  63
  64     if (!empty($query->_returnProperties['case_start_date'])) {
  65       $query->_select['case_start_date'] = "civicrm_case.start_date as case_start_date";
  66       $query->_element['case_start_date'] = 1;
  67       $query->_tables['civicrm_case'] = 1;
  68     }
  69
  70     if (!empty($query->_returnProperties['case_end_date'])) {
  71       $query->_select['case_end_date'] = "civicrm_case.end_date as case_end_date";
  72       $query->_element['case_end_date'] = 1;
  73       $query->_tables['civicrm_case'] = 1;
  74     }
  75
  76     if (!empty($query->_returnProperties['case_status_id'])) {
  77       $query->_select['case_status_id'] = "case_status.id as case_status_id";
  78       $query->_element['case_status_id'] = 1;
  79       $query->_tables['case_status_id'] = $query->_whereTables['case_status_id'] = 1;
  80       $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
  81     }
  82
  83     if (!empty($query->_returnProperties['case_status'])) {
  84       $query->_select['case_status'] = "case_status.label as case_status";
  85       $query->_element['case_status'] = 1;
  86       $query->_tables['case_status_id'] = $query->_whereTables['case_status_id'] = 1;
  87       $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
  88     }
  89
  90     if (!empty($query->_returnProperties['case_deleted'])) {
  91       $query->_select['case_deleted'] = "civicrm_case.is_deleted as case_deleted";
  92       $query->_element['case_deleted'] = 1;
  93       $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
  94     }
  95
  96     if (!empty($query->_returnProperties['case_role'])) {
  97       $query->_select['case_role'] = "IF(case_relationship.contact_id_b = contact_a.id, case_relation_type.label_b_a, case_relation_type.label_a_b) as case_role";
  98       $query->_element['case_role'] = 1;
  99       $query->_tables['case_relationship'] = $query->_whereTables['case_relationship'] = 1;
 100       $query->_tables['case_relation_type'] = $query->_whereTables['case_relation_type'] = 1;
 101     }
 102
 103     if (!empty($query->_returnProperties['case_recent_activity_date'])) {
 104       $query->_select['case_recent_activity_date'] = "case_activity.activity_date_time as case_recent_activity_date";
 105       $query->_element['case_recent_activity_date'] = 1;
 106       $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1;
 107     }
 108
 109     if (!empty($query->_returnProperties['case_activity_subject'])) {
 110       $query->_select['case_activity_subject'] = "case_activity.subject as case_activity_subject";
 111       $query->_element['case_activity_subject'] = 1;
 112       $query->_tables['case_activity'] = 1;
 113       $query->_tables['civicrm_case_contact'] = 1;
 114       $query->_tables['civicrm_case'] = 1;
 115     }
 116
 117     if (!empty($query->_returnProperties['case_subject'])) {
 118       $query->_select['case_subject'] = "civicrm_case.subject as case_subject";
 119       $query->_element['case_subject'] = 1;
 120       $query->_tables['civicrm_case_contact'] = 1;
 121       $query->_tables['civicrm_case'] = 1;
 122     }
 123
 124     // @todo switch to a more standard case_source_contact as the key where we want the name not the id.
 125     if (!empty($query->_returnProperties['case_source_contact_id'])) {
 126       $query->_select['case_source_contact_id'] = "civicrm_case_reporter.sort_name as case_source_contact_id";
 127       $query->_element['case_source_contact_id'] = 1;
 128       $query->_tables['civicrm_case_reporter'] = 1;
 129       $query->_tables['case_activity'] = 1;
 130       $query->_tables['civicrm_case_contact'] = 1;
 131       $query->_tables['civicrm_case'] = 1;
 132     }
 133
 134     if (!empty($query->_returnProperties['case_activity_status_id'])) {
 135       $query->_select['case_activity_status_id'] = "rec_activity_status.id as case_activity_status_id";
 136       $query->_element['case_activity_status_id'] = 1;
 137       $query->_tables['case_activity'] = 1;
 138       $query->_tables['recent_activity_status'] = 1;
 139       $query->_tables['civicrm_case_contact'] = 1;
 140       $query->_tables['civicrm_case'] = 1;
 141     }
 142
 143     if (!empty($query->_returnProperties['case_activity_status'])) {
 144       $query->_select['case_activity_status'] = "rec_activity_status.label as case_activity_status";
 145       $query->_element['case_activity_status'] = 1;
 146       $query->_tables['case_activity'] = 1;
 147       $query->_tables['recent_activity_status'] = 1;
 148       $query->_tables['civicrm_case_contact'] = 1;
 149       $query->_tables['civicrm_case'] = 1;
 150     }
 151
 152     if (!empty($query->_returnProperties['case_activity_duration'])) {
 153       $query->_select['case_activity_duration'] = "case_activity.duration as case_activity_duration";
 154       $query->_element['case_activity_duration'] = 1;
 155       $query->_tables['case_activity'] = 1;
 156       $query->_tables['civicrm_case_contact'] = 1;
 157       $query->_tables['civicrm_case'] = 1;
 158     }
 159
 160     if (!empty($query->_returnProperties['case_activity_medium_id'])) {
 161       $query->_select['case_activity_medium_id'] = "recent_activity_medium.label as case_activity_medium_id";
 162       $query->_element['case_activity_medium_id'] = 1;
 163       $query->_tables['case_activity'] = 1;
 164       $query->_tables['case_activity_medium'] = 1;
 165       $query->_tables['civicrm_case_contact'] = 1;
 166       $query->_tables['civicrm_case'] = 1;
 167     }
 168
 169     if (!empty($query->_returnProperties['case_activity_details'])) {
 170       $query->_select['case_activity_details'] = "case_activity.details as case_activity_details";
 171       $query->_element['case_activity_details'] = 1;
 172       $query->_tables['case_activity'] = 1;
 173       $query->_tables['civicrm_case_contact'] = 1;
 174       $query->_tables['civicrm_case'] = 1;
 175     }
 176
 177     if (!empty($query->_returnProperties['case_activity_is_auto'])) {
 178       $query->_select['case_activity_is_auto'] = "case_activity.is_auto as case_activity_is_auto";
 179       $query->_element['case_activity_is_auto'] = 1;
 180       $query->_tables['case_activity'] = 1;
 181       $query->_tables['civicrm_case_contact'] = 1;
 182       $query->_tables['civicrm_case'] = 1;
 183     }
 184
 185     if (!empty($query->_returnProperties['case_scheduled_activity_date'])) {
 186       $query->_select['case_scheduled_activity_date'] = "case_activity.activity_date_time as case_scheduled_activity_date";
 187       $query->_element['case_scheduled_activity_date'] = 1;
 188       $query->_tables['case_activity'] = 1;
 189       $query->_tables['civicrm_case_contact'] = 1;
 190       $query->_tables['civicrm_case'] = 1;
 191     }
 192     if (!empty($query->_returnProperties['case_recent_activity_type'])) {
 193       $query->_select['case_recent_activity_type'] = "rec_activity_type.label as case_recent_activity_type";
 194       $query->_element['case_recent_activity_type'] = 1;
 195       $query->_tables['case_activity'] = 1;
 196       $query->_tables['case_activity_type'] = 1;
 197       $query->_tables['civicrm_case_contact'] = 1;
 198       $query->_tables['civicrm_case'] = 1;
 199     }
 200   }
 201
 202   /**
 203    * Given a list of conditions in query generate the required where clause.
 204    *
 205    * @param CRM_Contact_BAO_Query $query
 206    */
 207   public static function where(&$query) {
 208     foreach ($query->_params as $id => $values) {
 209       if (!is_array($values) || count($values) != 5) {
 210         continue;
 211       }
 212
 213       if (substr($query->_params[$id][0], 0, 5) == 'case_') {
 214         if ($query->_mode == CRM_Contact_BAO_Query::MODE_CONTACTS) {
 215           $query->_useDistinct = TRUE;
 216         }
 217         self::whereClauseSingle($query->_params[$id], $query);
 218       }
 219     }
 220     // Add acl clause
 221     // This is new and so far only for cases - it would be good to find a more abstract
 222     // way to auto-apply this for all search components rather than copy-pasting this code to others
 223     if (isset($query->_tables['civicrm_case'])) {
 224       $aclClauses = array_filter(CRM_Case_BAO_Case::getSelectWhereClause());
 225       foreach ($aclClauses as $clause) {
 226         $query->_where[0][] = $clause;
 227       }
 228     }
 229   }
 230
 231   /**
 232    * Where clause for a single field.
 233    *
 234    * CRM-17120 adds a test that checks the Qill on some of these parameters.
 235    * However, I couldn't find a way, other than via test, to access the
 236    * case_activity options in the code below and invalid sql was returned.
 237    * Perhaps the options are just legacy?
 238    *
 239    * Also, CRM-17120 locks in the Qill - but it probably is not quite right as I
 240    * see 'Activity Type = Scheduled' (rather than activity status).
 241    *
 242    * See CRM_Case_BAO_QueryTest for more.
 243    *
 244    * @param array $values
 245    * @param CRM_Contact_BAO_Query $query
 246    *
 247    * @throws \CRM_Core_Exception
 248    */
 249   public static function whereClauseSingle(&$values, &$query) {
 250     if ($query->buildDateRangeQuery($values)) {
 251       // @todo - move this to Contact_Query in or near the call to
 252       // $this->buildRelativeDateQuery($values);
 253       return;
 254     }
 255     list($name, $op, $value, $grouping, $wildcard) = $values;
 256     $fields = CRM_Case_BAO_Case::fields();
 257     $fieldSpec = $fields[$values[0]] ?? [];
 258     $val = $names = [];
 259     switch ($name) {
 260
 261       case 'case_type_id':
 262       case 'case_type':
 263       case 'case_status':
 264       case 'case_status_id':
 265       case 'case_id':
 266
 267         if (strpos($name, 'type')) {
 268           $name = 'case_type_id';
 269           $label = 'Case Type(s)';
 270         }
 271         elseif (strpos($name, 'status')) {
 272           $name = 'status_id';
 273           $label = 'Case Status(s)';
 274         }
 275         else {
 276           $name = 'id';
 277           $label = 'Case ID';
 278         }
 279
 280         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_case.{$name}", $op, $value, "Integer");
 281         $query->_qill[$grouping][] = CRM_Contact_BAO_Query::getQillValue('CRM_Case_DAO_Case', $name, $value, $op, $label);
 282         $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 283         return;
 284
 285       case 'case_owner':
 286       case 'case_mycases':
 287         if (!empty($value)) {
 288           if ($value == 2) {
 289             $session = CRM_Core_Session::singleton();
 290             $userID = $session->get('userID');
 291             $query->_where[$grouping][] = ' (( ' . CRM_Contact_BAO_Query::buildClause("case_relationship.contact_id_b", $op, $userID, 'Int') . ' AND ' . CRM_Contact_BAO_Query::buildClause("case_relationship.is_active", '<>', 0, 'Int') . ' ) OR ( ' . CRM_Contact_BAO_Query::buildClause("case_relationship.contact_id_a", $op, $userID, 'Int') . ' AND ' . CRM_Contact_BAO_Query::buildClause("case_relationship.is_active", '<>', 0, 'Int') . ' ))';
 292             $query->_qill[$grouping][] = ts('Case %1 My Cases', [1 => $op]);
 293             $query->_tables['case_relationship'] = $query->_whereTables['case_relationship'] = 1;
 294           }
 295           elseif ($value == 1) {
 296             $query->_qill[$grouping][] = ts('Case %1 All Cases', [1 => $op]);
 297             $query->_where[$grouping][] = "civicrm_case_contact.contact_id = contact_a.id";
 298           }
 299           $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 300           $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
 301         }
 302         return;
 303
 304       case 'case_deleted':
 305         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_case.is_deleted", $op, $value, 'Boolean');
 306         if ($value) {
 307           $query->_qill[$grouping][] = ts("Find Deleted Cases");
 308         }
 309         $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 310         return;
 311
 312       case 'case_activity_subject':
 313         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.subject", $op, $value, 'String');
 314         $query->_qill[$grouping][] = ts("Activity Subject %1 '%2'", [1 => $op, 2 => $value]);
 315         $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1;
 316         $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 317         $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
 318         return;
 319
 320       case 'case_subject':
 321         $query->handleWhereFromMetadata($fieldSpec, $name, $value, $op);
 322         return;
 323
 324       // @todo switch to a more standard case_source_contact as the key where we want the name not the id.
 325       case 'case_source_contact_id':
 326         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_case_reporter.sort_name", $op, $value, 'String');
 327         $query->_qill[$grouping][] = ts("Activity Reporter %1 '%2'", [1 => $op, 2 => $value]);
 328         $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1;
 329         $query->_tables['civicrm_case_reporter'] = $query->_whereTables['civicrm_case_reporter'] = 1;
 330         $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 331         $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
 332         return;
 333
 334       case 'case_recent_activity_date':
 335         $date = CRM_Utils_Date::format($value);
 336         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.activity_date_time", $op, $date, 'Date');
 337         if ($date) {
 338           $date = CRM_Utils_Date::customFormat($date);
 339           $query->_qill[$grouping][] = ts("Activity Actual Date %1 %2", [1 => $op, 2 => $date]);
 340         }
 341         $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1;
 342         $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 343         $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
 344         return;
 345
 346       case 'case_scheduled_activity_date':
 347         $date = CRM_Utils_Date::format($value);
 348         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.activity_date_time", $op, $date, 'Date');
 349         if ($date) {
 350           $date = CRM_Utils_Date::customFormat($date);
 351           $query->_qill[$grouping][] = ts("Activity Schedule Date %1 %2", [1 => $op, 2 => $date]);
 352         }
 353         $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1;
 354         $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 355         $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
 356         return;
 357
 358       case 'case_recent_activity_type':
 359         $names = $value;
 360         if (($activityType = CRM_Core_PseudoConstant::getLabel('CRM_Activity_BAO_Activity', 'activity_type_id', $value)) != FALSE) {
 361           $names = $activityType;
 362         }
 363
 364         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.activity_type_id", $op, $value, 'Int');
 365         $query->_qill[$grouping][] = ts("Activity Type %1 %2", [1 => $op, 2 => $names]);
 366         $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1;
 367         $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 368         $query->_tables['case_activity_type'] = 1;
 369         $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
 370         return;
 371
 372       case 'case_activity_status_id':
 373         $names = $value;
 374         if (($activityStatus = CRM_Core_PseudoConstant::getLabel('CRM_Activity_BAO_Activity', 'status_id', $value)) != FALSE) {
 375           $names = $activityStatus;
 376         }
 377
 378         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.status_id", $op, $value, 'Int');
 379         $query->_qill[$grouping][] = ts("Activity Type %1 %2", [1 => $op, 2 => $names]);
 380         $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1;
 381         $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 382         $query->_tables['case_activity_status'] = 1;
 383         $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
 384         return;
 385
 386       case 'case_activity_duration':
 387         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.duration", $op, $value, 'Int');
 388         $query->_qill[$grouping][] = ts("Activity Duration %1 %2", [1 => $op, 2 => $value]);
 389         $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1;
 390         $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 391         $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
 392         return;
 393
 394       case 'case_activity_medium_id':
 395         $names = $value;
 396         if (($activityMedium = CRM_Core_PseudoConstant::getLabel('CRM_Activity_BAO_Activity', 'medium_id', $value)) != FALSE) {
 397           $names = $activityMedium;
 398         }
 399
 400         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.medium_id", $op, $value, 'Int');
 401         $query->_qill[$grouping][] = ts("Activity Medium %1 %2", [1 => $op, 2 => $names]);
 402         $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1;
 403         $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 404         $query->_tables['case_activity_medium'] = 1;
 405         $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
 406         return;
 407
 408       case 'case_activity_details':
 409         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.details", $op, $value, 'String');
 410         $query->_qill[$grouping][] = ts("Activity Details %1 '%2'", [1 => $op, 2 => $value]);
 411         $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1;
 412         $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 413         $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
 414         return;
 415
 416       case 'case_activity_is_auto':
 417         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.is_auto", $op, $value, 'Boolean');
 418         $query->_qill[$grouping][] = ts("Activity Auto Generated %1 '%2'", [1 => $op, 2 => $value]);
 419         $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1;
 420         $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 421         $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
 422         return;
 423
 424       // adding where clause for case_role
 425
 426       case 'case_role':
 427         $query->_qill[$grouping][] = ts("Role in Case  %1 '%2'", [1 => $op, 2 => $value]);
 428         $query->_tables['case_relation_type'] = $query->_whereTables['case_relationship_type'] = 1;
 429         $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 430         $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
 431         return;
 432
 433       case 'case_from_start_date_low':
 434       case 'case_from_start_date_high':
 435         CRM_Core_Error::deprecatedFunctionWarning('case_from is deprecated');
 436         $query->dateQueryBuilder($values,
 437           'civicrm_case', 'case_from_start_date', 'start_date', 'Start Date'
 438         );
 439         return;
 440
 441       case 'case_to_end_date_low':
 442       case 'case_to_end_date_high':
 443         CRM_Core_Error::deprecatedFunctionWarning('case_to is deprecated');
 444         $query->dateQueryBuilder($values,
 445           'civicrm_case', 'case_to_end_date', 'end_date', 'End Date'
 446         );
 447         return;
 448
 449       case 'case_start_date':
 450         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_case.start_date", $op, $value, 'Int');
 451         $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 452         return;
 453
 454       case 'case_end_date':
 455         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_case.end_date", $op, $value, 'Int');
 456         $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 457         return;
 458
 459       case 'case_taglist':
 460         $taglist = $value;
 461         $value = [];
 462         foreach ($taglist as $val) {
 463           if ($val) {
 464             $val = explode(',', $val);
 465             foreach ($val as $tId) {
 466               if (is_numeric($tId)) {
 467                 $value[$tId] = 1;
 468               }
 469             }
 470           }
 471         }
 472       case 'case_tags':
 473         $tags = CRM_Core_PseudoConstant::get('CRM_Core_DAO_EntityTag', 'tag_id', ['onlyActive' => FALSE]);
 474
 475         if (!empty($value)) {
 476           if (is_array($value)) {
 477             // Search tag(s) are part of a tag set
 478             $val = array_keys($value);
 479           }
 480           else {
 481             // Search tag(s) are part of the tag tree
 482             $val = explode(',', $value);
 483           }
 484           foreach ($val as $v) {
 485             if ($v) {
 486               $names[] = $tags[$v];
 487             }
 488           }
 489         }
 490
 491         $query->_where[$grouping][] = " civicrm_case_tag.tag_id IN (" . implode(',', $val) . " )";
 492         $query->_qill[$grouping][] = ts('Case Tags %1', [1 => $op]) . ' ' . implode(' ' . ts('or') . ' ', $names);
 493         $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 494         $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
 495         $query->_tables['civicrm_case_tag'] = $query->_whereTables['civicrm_case_tag'] = 1;
 496         return;
 497     }
 498   }
 499
 500   /**
 501    * Build from clause.
 502    *
 503    * @param string $name
 504    * @param string $mode
 505    * @param string $side
 506    *
 507    * @return string
 508    */
 509   public static function from($name, $mode, $side) {
 510     $from = "";
 511
 512     switch ($name) {
 513       case 'civicrm_case_contact':
 514         $from .= " $side JOIN civicrm_case_contact ON civicrm_case_contact.contact_id = contact_a.id ";
 515         break;
 516
 517       case 'civicrm_case_reporter':
 518         $activityContacts = CRM_Activity_BAO_ActivityContact::buildOptions('record_type_id', 'validate');
 519         $sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts);
 520         $from .= " $side JOIN civicrm_activity_contact as case_activity_contact ON (case_activity.id = case_activity_contact.activity_id AND  case_activity_contact.record_type_id = {$sourceID} ) ";
 521         $from .= " $side JOIN civicrm_contact as civicrm_case_reporter ON case_activity_contact.contact_id = civicrm_case_reporter.id ";
 522         break;
 523
 524       case 'civicrm_case':
 525         $from .= " INNER JOIN civicrm_case ON civicrm_case_contact.case_id = civicrm_case.id";
 526         break;
 527
 528       case 'case_status_id':
 529         $from .= " $side JOIN civicrm_option_group option_group_case_status ON (option_group_case_status.name = 'case_status')";
 530         $from .= " $side 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 ) ";
 531         break;
 532
 533       case 'case_type':
 534         $from .= " $side JOIN civicrm_case_type ON civicrm_case.case_type_id = civicrm_case_type.id ";
 535         break;
 536
 537       case 'case_activity_type':
 538         $from .= " $side JOIN civicrm_option_group option_group_activity_type ON (option_group_activity_type.name = 'activity_type')";
 539         $from .= " $side JOIN civicrm_option_value rec_activity_type ON (case_activity.activity_type_id = rec_activity_type.value AND option_group_activity_type.id = rec_activity_type.option_group_id ) ";
 540         break;
 541
 542       case 'recent_activity_status':
 543         $from .= " $side JOIN civicrm_option_group option_group_activity_status ON (option_group_activity_status.name = 'activity_status')";
 544         $from .= " $side JOIN civicrm_option_value rec_activity_status ON (case_activity.status_id = rec_activity_status.value AND option_group_activity_status.id = rec_activity_status.option_group_id ) ";
 545         break;
 546
 547       case 'case_relationship':
 548         $session = CRM_Core_Session::singleton();
 549         $userID = $session->get('userID');
 550         $from .= " $side JOIN civicrm_relationship case_relationship ON ( case_relationship.contact_id_a = civicrm_case_contact.contact_id AND case_relationship.contact_id_b = {$userID} AND case_relationship.case_id = civicrm_case.id OR case_relationship.contact_id_b = civicrm_case_contact.contact_id AND case_relationship.contact_id_a = {$userID} AND case_relationship.case_id = civicrm_case.id )";
 551         break;
 552
 553       case 'case_relation_type':
 554         $from .= " $side JOIN civicrm_relationship_type case_relation_type ON ( case_relation_type.id = case_relationship.relationship_type_id AND
 555 case_relation_type.id = case_relationship.relationship_type_id )";
 556         break;
 557
 558       case 'case_activity_medium':
 559         $from .= " $side JOIN civicrm_option_group option_group_activity_medium ON (option_group_activity_medium.name = 'encounter_medium')";
 560         $from .= " $side JOIN civicrm_option_value recent_activity_medium ON (case_activity.medium_id = recent_activity_medium.value AND option_group_activity_medium.id = recent_activity_medium.option_group_id ) ";
 561         break;
 562
 563       case 'case_activity':
 564         $from .= " INNER JOIN civicrm_case_activity ON civicrm_case_activity.case_id = civicrm_case.id ";
 565         $from .= " INNER JOIN civicrm_activity case_activity ON ( civicrm_case_activity.activity_id = case_activity.id
 566                                                                 AND case_activity.is_current_revision = 1 )";
 567         break;
 568
 569       case 'civicrm_case_tag':
 570         $from .= " $side JOIN civicrm_entity_tag as civicrm_case_tag ON ( civicrm_case_tag.entity_table = 'civicrm_case' AND civicrm_case_tag.entity_id = civicrm_case.id ) ";
 571         break;
 572     }
 573     return $from;
 574   }
 575
 576   /**
 577    * Getter for the qill object.
 578    *
 579    * @return string
 580    */
 581   public function qill() {
 582     return (isset($this->_qill)) ? $this->_qill : "";
 583   }
 584
 585   /**
 586    * @param $mode
 587    * @param bool $includeCustomFields
 588    *
 589    * @return array|null
 590    */
 591   public static function defaultReturnProperties(
 592     $mode,
 593     $includeCustomFields = TRUE
 594   ) {
 595
 596     $properties = NULL;
 597
 598     if ($mode & CRM_Contact_BAO_Query::MODE_CASE) {
 599       $properties = [
 600         'contact_type' => 1,
 601         'contact_sub_type' => 1,
 602         'contact_id' => 1,
 603         'sort_name' => 1,
 604         'display_name' => 1,
 605         'case_id' => 1,
 606         'case_activity_subject' => 1,
 607         'case_subject' => 1,
 608         'case_status' => 1,
 609         'case_type' => 1,
 610         'case_role' => 1,
 611         'case_deleted' => 1,
 612         'case_recent_activity_date' => 1,
 613         'case_recent_activity_type' => 1,
 614         'case_scheduled_activity_date' => 1,
 615         'phone' => 1,
 616         // 'case_scheduled_activity_type'=>      1
 617       ];
 618
 619       if ($includeCustomFields) {
 620         // also get all the custom case properties
 621         $fields = CRM_Core_BAO_CustomField::getFieldsForImport('Case');
 622         if (!empty($fields)) {
 623           foreach ($fields as $name => $dontCare) {
 624             $properties[$name] = 1;
 625           }
 626         }
 627       }
 628     }
 629
 630     return $properties;
 631   }
 632
 633   /**
 634    * This includes any extra fields that might need for export etc.
 635    *
 636    * @param string $mode
 637    *
 638    * @return array|null
 639    */
 640   public static function extraReturnProperties($mode) {
 641     $properties = NULL;
 642
 643     if ($mode & CRM_Contact_BAO_Query::MODE_CASE) {
 644       $properties = [
 645         'case_start_date' => 1,
 646         'case_end_date' => 1,
 647         'case_subject' => 1,
 648         // @todo switch to a more standard case_source_contact as the key where we want the name not the id.
 649         'case_source_contact_id' => 1,
 650         'case_activity_status' => 1,
 651         'case_activity_duration' => 1,
 652         'case_activity_medium_id' => 1,
 653         'case_activity_details' => 1,
 654         'case_activity_is_auto' => 1,
 655       ];
 656     }
 657     return $properties;
 658   }
 659
 660   /**
 661    * @param $tables
 662    */
 663   public static function tableNames(&$tables) {
 664     if (!empty($tables['civicrm_case'])) {
 665       $tables = array_merge(['civicrm_case_contact' => 1], $tables);
 666     }
 667
 668     if (!empty($tables['case_relation_type'])) {
 669       $tables = array_merge(['case_relationship' => 1], $tables);
 670     }
 671   }
 672
 673   /**
 674    * Get the metadata for fields to be included on the case search form.
 675    *
 676    * @todo ideally this would be a trait included on the case search & advanced search
 677    * rather than a static function.
 678    */
 679   public static function getSearchFieldMetadata() {
 680     $fields = ['case_type_id', 'case_status_id', 'case_start_date', 'case_end_date', 'case_subject', 'case_id', 'case_deleted'];
 681     $metadata = civicrm_api3('Case', 'getfields', [])['values'];
 682     $metadata['case_id'] = $metadata['id'];
 683     $metadata = array_intersect_key($metadata, array_flip($fields));
 684     $metadata['case_tags'] = [
 685       'title' => ts('Case Tag(s)'),
 686       'type' => CRM_Utils_Type::T_INT,
 687       'is_pseudofield' => TRUE,
 688       'html' => ['type' => 'Select2'],
 689     ];
 690     if (CRM_Core_Permission::check('access all cases and activities')) {
 691       $metadata['case_owner'] = [
 692         'title' => ts('Cases'),
 693         'type' => CRM_Utils_Type::T_INT,
 694         'is_pseudofield' => TRUE,
 695         'html' => ['type' => 'Radio'],
 696       ];
 697     }
 698     if (!CRM_Core_Permission::check('administer CiviCase')) {
 699       unset($metadata['case_deleted']);
 700     }
 701     return $metadata;
 702   }
 703
 704   /**
 705    * Add all the elements shared between case search and advanced search.
 706    *
 707    * @param CRM_Case_Form_Search $form
 708    */
 709   public static function buildSearchForm(&$form) {
 710     //validate case configuration.
 711     $configured = CRM_Case_BAO_Case::isCaseConfigured();
 712     $form->assign('notConfigured', !$configured['configured']);
 713
 714     $form->addSearchFieldMetadata(['Case' => self::getSearchFieldMetadata()]);
 715     $form->addFormFieldsFromMetadata();
 716
 717     $form->assign('validCiviCase', TRUE);
 718
 719     //give options when all cases are accessible.
 720     $accessAllCases = FALSE;
 721     if (CRM_Core_Permission::check('access all cases and activities')) {
 722       $accessAllCases = TRUE;
 723       $caseOwner = [1 => ts('Search All Cases'), 2 => ts('Only My Cases')];
 724       $form->addRadio('case_owner', ts('Cases'), $caseOwner);
 725       if ($form->get('context') != 'dashboard') {
 726         $form->add('checkbox', 'upcoming', ts('Search Cases with Upcoming Activities'));
 727       }
 728     }
 729     $form->assign('accessAllCases', $accessAllCases);
 730
 731     $caseTags = CRM_Core_BAO_Tag::getColorTags('civicrm_case');
 732
 733     if ($caseTags) {
 734       $form->add('select2', 'case_tags', ts('Case Tag(s)'), $caseTags, FALSE, ['class' => 'big', 'placeholder' => ts('- select -'), 'multiple' => TRUE]);
 735     }
 736
 737     $parentNames = CRM_Core_BAO_Tag::getTagSet('civicrm_case');
 738     CRM_Core_Form_Tag::buildQuickForm($form, $parentNames, 'civicrm_case', NULL, TRUE, FALSE);
 739
 740     self::addCustomFormFields($form, ['Case']);
 741
 742     $form->setDefaults(['case_owner' => 1]);
 743   }
 744
 745 }