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