CRM/Case/BAO/Query.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.5                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2014                                |
   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-2014
  32  * $Id$
  33  *
  34  */
  35 class CRM_Case_BAO_Query {
  36
  37   /**
  38    * @param bool $excludeActivityFields
  39    *
  40    * @return array
  41    */
  42   static function &getFields($excludeActivityFields = FALSE) {
  43     $fields = array();
  44     $fields = CRM_Case_BAO_Case::exportableFields();
  45
  46     // add activity related fields
  47     if (!$excludeActivityFields) {
  48       $fields = array_merge($fields, CRM_Activity_BAO_Activity::exportableFields('Case'));
  49     }
  50
  51     return $fields;
  52   }
  53
  54   /**
  55    * Build select for Case
  56    *
  57    * @param $query
  58    *
  59    * @return void
  60    * @access public
  61    */
  62   static function select(&$query) {
  63     if (($query->_mode & CRM_Contact_BAO_Query::MODE_CASE) || !empty($query->_returnProperties['case_id'])) {
  64       $query->_select['case_id'] = "civicrm_case.id as case_id";
  65       $query->_element['case_id'] = 1;
  66       $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
  67       $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
  68     }
  69
  70     if (!empty($query->_returnProperties['case_type_id'])) {
  71       $query->_select['case_type_id'] = "civicrm_case_type.id as case_type_id";
  72       $query->_element['case_type_id'] = 1;
  73       $query->_tables['case_type'] = $query->_whereTables['case_type'] = 1;
  74       $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
  75     }
  76
  77     if (!empty($query->_returnProperties['case_type'])) {
  78       $query->_select['case_type'] = "civicrm_case_type.title as case_type";
  79       $query->_element['case_type'] = 1;
  80       $query->_tables['case_type'] = $query->_whereTables['case_type'] = 1;
  81       $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
  82     }
  83
  84     if (!empty($query->_returnProperties['case_start_date'])) {
  85       $query->_select['case_start_date'] = "civicrm_case.start_date as case_start_date";
  86       $query->_element['case_start_date'] = 1;
  87       $query->_tables['civicrm_case'] = 1;
  88     }
  89
  90     if (!empty($query->_returnProperties['case_end_date'])) {
  91       $query->_select['case_end_date'] = "civicrm_case.end_date as case_end_date";
  92       $query->_element['case_end_date'] = 1;
  93       $query->_tables['civicrm_case'] = 1;
  94     }
  95
  96     if (!empty($query->_returnProperties['case_status_id'])) {
  97       $query->_select['case_status_id'] = "case_status.id as case_status_id";
  98       $query->_element['case_status_id'] = 1;
  99       $query->_tables['case_status_id'] = $query->_whereTables['case_status_id'] = 1;
 100       $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 101     }
 102
 103     if (!empty($query->_returnProperties['case_status'])) {
 104       $query->_select['case_status'] = "case_status.label as case_status";
 105       $query->_element['case_status'] = 1;
 106       $query->_tables['case_status_id'] = $query->_whereTables['case_status_id'] = 1;
 107       $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 108     }
 109
 110     if (!empty($query->_returnProperties['case_deleted'])) {
 111       $query->_select['case_deleted'] = "civicrm_case.is_deleted as case_deleted";
 112       $query->_element['case_deleted'] = 1;
 113       $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 114     }
 115
 116     if (!empty($query->_returnProperties['case_role'])) {
 117       $query->_select['case_role'] = "case_relation_type.label_b_a as case_role";
 118       $query->_element['case_role'] = 1;
 119       $query->_tables['case_relationship'] = $query->_whereTables['case_relationship'] = 1;
 120       $query->_tables['case_relation_type'] = $query->_whereTables['case_relation_type'] = 1;
 121     }
 122
 123     if (!empty($query->_returnProperties['case_recent_activity_date'])) {
 124       $query->_select['case_recent_activity_date'] = "case_activity.activity_date_time as case_recent_activity_date";
 125       $query->_element['case_recent_activity_date'] = 1;
 126       $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1;
 127     }
 128
 129     if (!empty($query->_returnProperties['case_activity_subject'])) {
 130       $query->_select['case_activity_subject'] = "case_activity.subject as case_activity_subject";
 131       $query->_element['case_activity_subject'] = 1;
 132       $query->_tables['case_activity'] = 1;
 133       $query->_tables['civicrm_case_contact'] = 1;
 134       $query->_tables['civicrm_case'] = 1;
 135     }
 136
 137     if (!empty($query->_returnProperties['case_subject'])) {
 138       $query->_select['case_subject'] = "civicrm_case.subject as case_subject";
 139       $query->_element['case_subject'] = 1;
 140       $query->_tables['civicrm_case_contact'] = 1;
 141       $query->_tables['civicrm_case'] = 1;
 142     }
 143
 144     if (!empty($query->_returnProperties['case_source_contact_id'])) {
 145       $query->_select['case_source_contact_id'] = "civicrm_case_reporter.sort_name as case_source_contact_id";
 146       $query->_element['case_source_contact_id'] = 1;
 147       $query->_tables['civicrm_case_reporter'] = 1;
 148       $query->_tables['case_activity'] = 1;
 149       $query->_tables['civicrm_case_contact'] = 1;
 150       $query->_tables['civicrm_case'] = 1;
 151     }
 152
 153     if (!empty($query->_returnProperties['case_activity_status_id'])) {
 154       $query->_select['case_activity_status_id'] = "rec_activity_status.id as case_activity_status_id";
 155       $query->_element['case_activity_status_id'] = 1;
 156       $query->_tables['case_activity'] = 1;
 157       $query->_tables['recent_activity_status'] = 1;
 158       $query->_tables['civicrm_case_contact'] = 1;
 159       $query->_tables['civicrm_case'] = 1;
 160     }
 161
 162     if (!empty($query->_returnProperties['case_activity_status'])) {
 163       $query->_select['case_activity_status'] = "rec_activity_status.label as case_activity_status";
 164       $query->_element['case_activity_status'] = 1;
 165       $query->_tables['case_activity'] = 1;
 166       $query->_tables['recent_activity_status'] = 1;
 167       $query->_tables['civicrm_case_contact'] = 1;
 168       $query->_tables['civicrm_case'] = 1;
 169     }
 170
 171     if (!empty($query->_returnProperties['case_activity_duration'])) {
 172       $query->_select['case_activity_duration'] = "case_activity.duration as case_activity_duration";
 173       $query->_element['case_activity_duration'] = 1;
 174       $query->_tables['case_activity'] = 1;
 175       $query->_tables['civicrm_case_contact'] = 1;
 176       $query->_tables['civicrm_case'] = 1;
 177     }
 178
 179     if (!empty($query->_returnProperties['case_activity_medium_id'])) {
 180       $query->_select['case_activity_medium_id'] = "recent_activity_medium.label as case_activity_medium_id";
 181       $query->_element['case_activity_medium_id'] = 1;
 182       $query->_tables['case_activity'] = 1;
 183       $query->_tables['case_activity_medium'] = 1;
 184       $query->_tables['civicrm_case_contact'] = 1;
 185       $query->_tables['civicrm_case'] = 1;
 186     }
 187
 188     if (!empty($query->_returnProperties['case_activity_details'])) {
 189       $query->_select['case_activity_details'] = "case_activity.details as case_activity_details";
 190       $query->_element['case_activity_details'] = 1;
 191       $query->_tables['case_activity'] = 1;
 192       $query->_tables['civicrm_case_contact'] = 1;
 193       $query->_tables['civicrm_case'] = 1;
 194     }
 195
 196     if (!empty($query->_returnProperties['case_activity_is_auto'])) {
 197       $query->_select['case_activity_is_auto'] = "case_activity.is_auto as case_activity_is_auto";
 198       $query->_element['case_activity_is_auto'] = 1;
 199       $query->_tables['case_activity'] = 1;
 200       $query->_tables['civicrm_case_contact'] = 1;
 201       $query->_tables['civicrm_case'] = 1;
 202     }
 203
 204     if (!empty($query->_returnProperties['case_scheduled_activity_date'])) {
 205       $query->_select['case_scheduled_activity_date'] = "case_activity.activity_date_time as case_scheduled_activity_date";
 206       $query->_element['case_scheduled_activity_date'] = 1;
 207       $query->_tables['case_activity'] = 1;
 208       $query->_tables['civicrm_case_contact'] = 1;
 209       $query->_tables['civicrm_case'] = 1;
 210     }
 211     if (!empty($query->_returnProperties['case_recent_activity_type'])) {
 212       $query->_select['case_recent_activity_type'] = "rec_activity_type.label as case_recent_activity_type";
 213       $query->_element['case_recent_activity_type'] = 1;
 214       $query->_tables['case_activity'] = 1;
 215       $query->_tables['case_activity_type'] = 1;
 216       $query->_tables['civicrm_case_contact'] = 1;
 217       $query->_tables['civicrm_case'] = 1;
 218     }
 219   }
 220
 221   /**
 222    * Given a list of conditions in query generate the required
 223    * where clause
 224    *
 225    * @param $query
 226    *
 227    * @return void
 228    * @access public
 229    */
 230   static function where(&$query) {
 231     foreach ($query->_params as $id => $values) {
 232       if (!is_array($values) || count($values) != 5) {
 233         continue;
 234       }
 235
 236       if (substr($query->_params[$id][0], 0, 5) == 'case_') {
 237         if ($query->_mode == CRM_Contact_BAO_Query::MODE_CONTACTS) {
 238           $query->_useDistinct = TRUE;
 239         }
 240         $grouping = $query->_params[$id][3];
 241         self::whereClauseSingle($query->_params[$id], $query);
 242       }
 243     }
 244   }
 245
 246   /**
 247    * Where clause for a single field
 248    *
 249    * @param $values
 250    * @param $query
 251    *
 252    * @return void
 253    * @access public
 254    */
 255   static function whereClauseSingle(&$values, &$query) {
 256     list($name, $op, $value, $grouping, $wildcard) = $values;
 257     $val = $names = array();
 258     switch ($name) {
 259       case 'case_status':
 260       case 'case_status_id':
 261         $statuses = CRM_Case_PseudoConstant::caseStatus();
 262         // Standardize input from checkboxes or single value
 263         if (is_array($value) && $query->_mode == CRM_Contact_BAO_Query::MODE_CASE) {
 264           $value = array_keys($value, 1);
 265         }
 266         foreach ((array) $value as $k) {
 267           if ($k && isset($statuses[$k])) {
 268             $val[$k] = $k;
 269             $names[] = $statuses[$k];
 270           }
 271           elseif ($k && ($v = CRM_Utils_Array::key($k, $statuses))) {
 272             $val[$v] = $v;
 273             $names[] = $k;
 274           }
 275         }
 276         if ($val) {
 277           $query->_where[$grouping][] = "civicrm_case.status_id IN (" . implode(',', $val) . ")";
 278         }
 279         else {
 280           $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_case.status_id', $op, $value, "Integer");
 281         }
 282         $query->_qill[$grouping][] = ts('Case Status is %1', array(1 => implode(' ' . ts('or') . ' ', $names)));
 283         $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 284         return;
 285
 286       case 'case_type_id':
 287       case 'case_type':
 288         $caseTypes = CRM_Case_PseudoConstant::caseType('title', FALSE);
 289
 290         if (is_array($value)) {
 291           foreach ($value as $k => $v) {
 292             if ($v) {
 293               $val[$k] = $k;
 294               $names[] = $caseTypes[$k];
 295             }
 296           }
 297         }
 298         elseif (is_numeric($value)) {
 299           $val[$value] = $value;
 300           $names[] = $value;
 301         }
 302         elseif ($caseTypeId = CRM_Utils_Array::key($value, $caseTypes)) {
 303           $val[$caseTypeId] = $caseTypeId;
 304           $names[] = $caseTypes[$caseTypeId];
 305         }
 306
 307         if ($val) {
 308           $query->_where[$grouping][] = "(civicrm_case.case_type_id IN (" . implode(',', $val) . "))";
 309         }
 310         else {
 311           $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_case.case_type_id', $op, $value, "Integer");
 312         }
 313
 314         $query->_qill[$grouping][] = ts('Case Type is %1', array(1 => implode(' ' . ts('or') . ' ', $names)));
 315         $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 316         return;
 317
 318       case 'case_id':
 319         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_case.id", $op, $value, 'Int');
 320         $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 321         return;
 322
 323       case 'case_owner':
 324       case 'case_mycases':
 325         if (!empty($value)) {
 326           if ($value == 2) {
 327             $session = CRM_Core_Session::singleton();
 328             $userID = $session->get('userID');
 329             $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_relationship.contact_id_b", $op, $userID, 'Int');
 330             $query->_qill[$grouping][] = ts('Case %1 My Cases', array(1 => $op));
 331             $query->_tables['case_relationship'] = $query->_whereTables['case_relationship'] = 1;
 332           }
 333           elseif ($value == 1) {
 334             $query->_qill[$grouping][] = ts('Case %1 All Cases', array(1 => $op));
 335             $query->_where[$grouping][] = "civicrm_case_contact.contact_id = contact_a.id";
 336           }
 337           $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 338           $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
 339         }
 340         return;
 341
 342       case 'case_deleted':
 343         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_case.is_deleted", $op, $value, 'Boolean');
 344         if ($value) {
 345           $query->_qill[$grouping][] = ts("Find Deleted Cases");
 346         }
 347         $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 348         return;
 349
 350       case 'case_activity_subject':
 351         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.subject", $op, $value, 'String');
 352         $query->_qill[$grouping][] = ts("Activity Subject %1 '%2'", array(1 => $op, 2 => $value));
 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_subject':
 359         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_case.subject", $op, $value, 'String');
 360         $query->_qill[$grouping][] = ts("Case Subject %1 '%2'", array(1 => $op, 2 => $value));
 361         $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 362         $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
 363         return;
 364
 365       case 'case_source_contact_id':
 366         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_case_reporter.sort_name", $op, $value, 'String');
 367         $query->_qill[$grouping][] = ts("Activity Reporter %1 '%2'", array(1 => $op, 2 => $value));
 368         $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1;
 369         $query->_tables['civicrm_case_reporter'] = $query->_whereTables['civicrm_case_reporter'] = 1;
 370         $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 371         $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
 372         return;
 373
 374       case 'case_recent_activity_date':
 375         $date = CRM_Utils_Date::format($value);
 376         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.activity_date_time", $op, $date, 'Date');
 377         if ($date) {
 378           $date = CRM_Utils_Date::customFormat($date);
 379           $query->_qill[$grouping][] = ts("Activity Actual Date %1 %2", array(1 => $op, 2 => $date));
 380         }
 381         $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1;
 382         $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 383         $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
 384         return;
 385
 386       case 'case_scheduled_activity_date':
 387         $date = CRM_Utils_Date::format($value);
 388         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.activity_date_time", $op, $date, 'Date');
 389         if ($date) {
 390           $date = CRM_Utils_Date::customFormat($date);
 391           $query->_qill[$grouping][] = ts("Activity Schedule Date %1 %2", array(1 => $op, 2 => $date));
 392         }
 393         $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1;
 394         $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 395         $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
 396         return;
 397
 398       case 'case_recent_activity_type':
 399         $names = $value;
 400         if ($activityType = CRM_Core_OptionGroup::getLabel('activity_type', $value, 'value')) {
 401           $names = $activityType;
 402         }
 403
 404         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.activity_type_id", $op, $value, 'Int');
 405         $query->_qill[$grouping][] = ts("Activity Type %1 %2", array(1 => $op, 2 => $names));
 406         $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1;
 407         $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 408         $query->_tables['case_activity_type'] = 1;
 409         $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
 410         return;
 411
 412       case 'case_activity_status_id':
 413         $names = $value;
 414         if ($activityStatus = CRM_Core_OptionGroup::getLabel('activity_status', $value, 'value')) {
 415           $names = $activityStatus;
 416         }
 417
 418         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.status_id", $op, $value, 'Int');
 419         $query->_qill[$grouping][] = ts("Activity Type %1 %2", array(1 => $op, 2 => $names));
 420         $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1;
 421         $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 422         $query->_tables['case_activity_status'] = 1;
 423         $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
 424         return;
 425
 426       case 'case_activity_duration':
 427         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.duration", $op, $value, 'Int');
 428         $query->_qill[$grouping][] = ts("Activity Duration %1 %2", array(1 => $op, 2 => $value));
 429         $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1;
 430         $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 431         $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
 432         return;
 433
 434       case 'case_activity_medium_id':
 435         $names = $value;
 436         if ($activityMedium = CRM_Core_OptionGroup::getLabel('encounter_medium', $value, 'value')) {
 437           $names = $activityMedium;
 438         }
 439
 440         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.medium_id", $op, $value, 'Int');
 441         $query->_qill[$grouping][] = ts("Activity Medium %1 %2", array(1 => $op, 2 => $names));
 442         $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1;
 443         $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 444         $query->_tables['case_activity_medium'] = 1;
 445         $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
 446         return;
 447
 448       case 'case_activity_details':
 449         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.details", $op, $value, 'String');
 450         $query->_qill[$grouping][] = ts("Activity Details %1 '%2'", array(1 => $op, 2 => $value));
 451         $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1;
 452         $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 453         $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
 454         return;
 455
 456       case 'case_activity_is_auto':
 457         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.is_auto", $op, $value, 'Boolean');
 458         $query->_qill[$grouping][] = ts("Activity Auto Genrated %1 '%2'", array(1 => $op, 2 => $value));
 459         $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1;
 460         $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 461         $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
 462         return;
 463
 464       // adding where clause for case_role
 465
 466       case 'case_role':
 467         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_relation_type.name_b_a", $op, $value, 'String');
 468         $query->_qill[$grouping][] = ts("Role in Case  %1 '%2'", array(1 => $op, 2 => $value));
 469         $query->_tables['case_relation_type'] = $query->_whereTables['case_relationship_type'] = 1;
 470         $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 471         $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
 472         return;
 473
 474       case 'case_from_start_date_low':
 475       case 'case_from_start_date_high':
 476         $query->dateQueryBuilder($values,
 477           'civicrm_case', 'case_from_start_date', 'start_date', 'Start Date'
 478         );
 479         return;
 480
 481       case 'case_to_end_date_low':
 482       case 'case_to_end_date_high':
 483         $query->dateQueryBuilder($values,
 484           'civicrm_case', 'case_to_end_date', 'end_date', 'End Date'
 485         );
 486         return;
 487
 488       case 'case_start_date':
 489         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_case.start_date", $op, $value, 'Int');
 490         $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 491         return;
 492
 493       case 'case_end_date':
 494         $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_case.end_date", $op, $value, 'Int');
 495         $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 496         return;
 497
 498       case 'case_taglist':
 499         $taglist = $value;
 500         $value = array();
 501         foreach ($taglist as $val) {
 502           if ($val) {
 503             $val = explode(',', $val);
 504             foreach ($val as $tId) {
 505               if (is_numeric($tId)) {
 506                 $value[$tId] = 1;
 507               }
 508             }
 509           }
 510         }
 511       case 'case_tags':
 512         $tags = CRM_Core_PseudoConstant::get('CRM_Core_DAO_EntityTag', 'tag_id', array('onlyActive' => FALSE));
 513
 514         if (is_array($value)) {
 515           foreach ($value as $k => $v) {
 516             if ($v) {
 517               $val[$k] = $k;
 518               $names[] = $tags[$k];
 519             }
 520           }
 521         }
 522
 523         $query->_where[$grouping][] = " civicrm_case_tag.tag_id IN (" . implode(',', $val) . " )";
 524         $query->_qill[$grouping][] = ts('Case Tags %1', array(1 => $op)) . ' ' . implode(' ' . ts('or') . ' ', $names);
 525         $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1;
 526         $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1;
 527         $query->_tables['civicrm_case_tag'] = $query->_whereTables['civicrm_case_tag'] = 1;
 528         return;
 529     }
 530   }
 531
 532   /**
 533    * @param string $name
 534    * @param $mode
 535    * @param $side
 536    *
 537    * @return string
 538    */
 539   static function from($name, $mode, $side) {
 540     $from = "";
 541
 542     switch ($name) {
 543       case 'civicrm_case_contact':
 544         $from .= " $side JOIN civicrm_case_contact ON civicrm_case_contact.contact_id = contact_a.id ";
 545         break;
 546
 547       case 'civicrm_case_reporter':
 548         $activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
 549         $sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts);
 550         $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} ) ";
 551         $from .= " $side JOIN civicrm_contact as civicrm_case_reporter ON case_activity_contact.contact_id = civicrm_case_reporter.id ";
 552         break;
 553
 554       case 'civicrm_case':
 555         $from .= " INNER JOIN civicrm_case ON civicrm_case_contact.case_id = civicrm_case.id";
 556         break;
 557
 558       case 'case_status_id':
 559         $from .= " $side JOIN civicrm_option_group option_group_case_status ON (option_group_case_status.name = 'case_status')";
 560         $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 ) ";
 561         break;
 562
 563       case 'case_type':
 564         $from .= " $side JOIN civicrm_case_type ON civicrm_case.case_type_id = civicrm_case_type.id ";
 565         break;
 566
 567       case 'case_activity_type':
 568         $from .= " $side JOIN civicrm_option_group option_group_activity_type ON (option_group_activity_type.name = 'activity_type')";
 569         $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 ) ";
 570         break;
 571
 572       case 'recent_activity_status':
 573         $from .= " $side JOIN civicrm_option_group option_group_activity_status ON (option_group_activity_status.name = 'activity_status')";
 574         $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 ) ";
 575         break;
 576
 577       case 'case_relationship':
 578         $session = CRM_Core_Session::singleton();
 579         $userID  = $session->get('userID');
 580         $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 )";
 581         break;
 582
 583       case 'case_relation_type':
 584         $from .= " $side JOIN civicrm_relationship_type case_relation_type ON ( case_relation_type.id = case_relationship.relationship_type_id AND
 585 case_relation_type.id = case_relationship.relationship_type_id )";
 586         break;
 587
 588       case 'case_activity_medium':
 589         $from .= " $side JOIN civicrm_option_group option_group_activity_medium ON (option_group_activity_medium.name = 'encounter_medium')";
 590         $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 ) ";
 591         break;
 592
 593       case 'case_activity':
 594         $from .= " INNER JOIN civicrm_case_activity ON civicrm_case_activity.case_id = civicrm_case.id ";
 595         $from .= " INNER JOIN civicrm_activity case_activity ON ( civicrm_case_activity.activity_id = case_activity.id
 596                                                                 AND case_activity.is_current_revision = 1 )";
 597         break;
 598
 599       case 'civicrm_case_tag':
 600         $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 ) ";
 601         break;
 602     }
 603     return $from;
 604   }
 605
 606   /**
 607    * Getter for the qill object
 608    *
 609    * @return string
 610    * @access public
 611    */
 612   function qill() {
 613     return (isset($this->_qill)) ? $this->_qill : "";
 614   }
 615
 616   /**
 617    * @param $mode
 618    * @param bool $includeCustomFields
 619    *
 620    * @return array|null
 621    */
 622   static function defaultReturnProperties($mode,
 623     $includeCustomFields = TRUE
 624   ) {
 625
 626     $properties = NULL;
 627
 628     if ($mode & CRM_Contact_BAO_Query::MODE_CASE) {
 629       $properties = array(
 630         'contact_type' => 1,
 631         'contact_sub_type' => 1,
 632         'contact_id' => 1,
 633         'sort_name' => 1,
 634         'display_name' => 1,
 635         'case_id' => 1,
 636         'case_activity_subject' => 1,
 637         'case_subject' => 1,
 638         'case_status' => 1,
 639         'case_type' => 1,
 640         'case_role' => 1,
 641         'case_deleted' => 1,
 642         'case_recent_activity_date' => 1,
 643         'case_recent_activity_type' => 1,
 644         'case_scheduled_activity_date' => 1,
 645         'phone' => 1,
 646         // 'case_scheduled_activity_type'=>      1
 647       );
 648
 649       if ($includeCustomFields) {
 650         // also get all the custom case properties
 651         $fields = CRM_Core_BAO_CustomField::getFieldsForImport('Case');
 652         if (!empty($fields)) {
 653           foreach ($fields as $name => $dontCare) {
 654             $properties[$name] = 1;
 655           }
 656         }
 657       }
 658     }
 659
 660     return $properties;
 661   }
 662
 663   /**
 664    * This includes any extra fields that might need for export etc
 665    */
 666   static function extraReturnProperties($mode) {
 667     $properties = NULL;
 668
 669     if ($mode & CRM_Contact_BAO_Query::MODE_CASE) {
 670       $properties = array(
 671         'case_start_date' => 1,
 672         'case_end_date' => 1,
 673         'case_subject' => 1,
 674         'case_source_contact_id' => 1,
 675         'case_activity_status' => 1,
 676         'case_activity_duration' => 1,
 677         'case_activity_medium_id' => 1,
 678         'case_activity_details' => 1,
 679         'case_activity_is_auto' => 1,
 680       );
 681     }
 682     return $properties;
 683   }
 684
 685   /**
 686    * @param $tables
 687    */
 688   static function tableNames(&$tables) {
 689     if (!empty($tables['civicrm_case'])) {
 690       $tables = array_merge(array('civicrm_case_contact' => 1), $tables);
 691     }
 692
 693     if (!empty($tables['case_relation_type'])) {
 694       $tables = array_merge(array('case_relationship' => 1), $tables);
 695     }
 696   }
 697
 698   /**
 699    * Add all the elements shared between case search and advanaced search
 700    *
 701    * @access public
 702    *
 703    * @param CRM_Core_Form $form
 704    *
 705    * @return void
 706    * @static
 707    */
 708   static function buildSearchForm(&$form) {
 709     $config = CRM_Core_Config::singleton();
 710
 711     //validate case configuration.
 712     $configured = CRM_Case_BAO_Case::isCaseConfigured();
 713     $form->assign('notConfigured', !$configured['configured']);
 714
 715     $caseTypes = CRM_Case_PseudoConstant::caseType('title', FALSE);
 716     foreach ($caseTypes as $id => $name) {
 717       $form->addElement('checkbox', "case_type_id[$id]", NULL, $name);
 718     }
 719
 720     $statuses = CRM_Case_PseudoConstant::caseStatus('label', FALSE);
 721     foreach ($statuses as $id => $name) {
 722       $form->addElement('checkbox', "case_status_id[$id]", NULL, $name);
 723     }
 724
 725     CRM_Core_Form_Date::buildDateRange($form, 'case_from', 1, '_start_date_low', '_start_date_high', ts('From'), FALSE);
 726     CRM_Core_Form_Date::buildDateRange($form, 'case_to',   1, '_end_date_low',   '_end_date_high',   ts('From'), FALSE);
 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 = array(1 => ts('Search All Cases'), 2 => ts('Only My Cases'));
 735       $form->addRadio('case_owner', ts('Cases'), $caseOwner);
 736     }
 737     $form->assign('accessAllCases', $accessAllCases);
 738
 739     $caseTags = CRM_Core_BAO_Tag::getTags('civicrm_case');
 740
 741     if ($caseTags) {
 742       foreach ($caseTags as $tagID => $tagName) {
 743         $form->_tagElement = &$form->addElement('checkbox', "case_tags[$tagID]", NULL, $tagName);
 744       }
 745     }
 746
 747     $parentNames = CRM_Core_BAO_Tag::getTagSet('civicrm_case');
 748     CRM_Core_Form_Tag::buildQuickForm($form, $parentNames, 'civicrm_case', NULL, TRUE, FALSE);
 749
 750     if (CRM_Core_Permission::check('administer CiviCRM')) {
 751       $form->addElement('checkbox', 'case_deleted', ts('Deleted Cases'));
 752     }
 753
 754     // add all the custom  searchable fields
 755     $extends = array('Case');
 756     $groupDetails = CRM_Core_BAO_CustomGroup::getGroupDetail(NULL, TRUE, $extends);
 757     if ($groupDetails) {
 758       $form->assign('caseGroupTree', $groupDetails);
 759       foreach ($groupDetails as $group) {
 760         foreach ($group['fields'] as $field) {
 761           $fieldId = $field['id'];
 762           $elementName = 'custom_' . $fieldId;
 763           CRM_Core_BAO_CustomField::addQuickFormElement($form,
 764             $elementName,
 765             $fieldId,
 766             FALSE, FALSE, TRUE
 767           );
 768         }
 769       }
 770     }
 771     $form->setDefaults(array('case_owner' => 1));
 772   }
 773
 774   /**
 775    * @param $row
 776    * @param int $id
 777    */
 778   static function searchAction(&$row, $id) {}
 779 }
 780