CRM/Case/BAO/Query.php

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