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