CRM/Case/BAO/Query.php

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