3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.6 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2015 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
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. |
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. |
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 +--------------------------------------------------------------------+
31 * @copyright CiviCRM LLC (c) 2004-2015
35 class CRM_Case_BAO_Query
{
38 * @param bool $excludeActivityFields
42 public static function &getFields($excludeActivityFields = FALSE) {
44 $fields = CRM_Case_BAO_Case
::exportableFields();
46 // add activity related fields
47 if (!$excludeActivityFields) {
48 $fields = array_merge($fields, CRM_Activity_BAO_Activity
::exportableFields('Case'));
55 * Build select for Case.
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
221 * Given a list of conditions in query generate the required
228 public static function where(&$query) {
229 foreach ($query->_params
as $id => $values) {
230 if (!is_array($values) ||
count($values) != 5) {
234 if (substr($query->_params
[$id][0], 0, 5) == 'case_') {
235 if ($query->_mode
== CRM_Contact_BAO_Query
::MODE_CONTACTS
) {
236 $query->_useDistinct
= TRUE;
238 $grouping = $query->_params
[$id][3];
239 self
::whereClauseSingle($query->_params
[$id], $query);
245 * Where clause for a single field.
252 public static function whereClauseSingle(&$values, &$query) {
253 list($name, $op, $value, $grouping, $wildcard) = $values;
254 $val = $names = array();
260 case 'case_status_id':
263 if (strpos($name, 'type')) {
264 $name = 'case_type_id';
265 $label = 'Case Type(s)';
267 elseif (strpos($name, 'status')) {
269 $label = 'Case Status(s)';
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);
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;
285 if (!empty($value)) {
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;
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";
297 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
298 $query->_tables
['civicrm_case_contact'] = $query->_whereTables
['civicrm_case_contact'] = 1;
303 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_case.is_deleted", $op, $value, 'Boolean');
305 $query->_qill
[$grouping][] = ts("Find Deleted Cases");
307 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
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;
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;
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;
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');
338 $date = CRM_Utils_Date
::customFormat($date);
339 $query->_qill
[$grouping][] = ts("Activity Actual Date %1 %2", array(1 => $op, 2 => $date));
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;
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');
350 $date = CRM_Utils_Date
::customFormat($date);
351 $query->_qill
[$grouping][] = ts("Activity Schedule Date %1 %2", array(1 => $op, 2 => $date));
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;
358 case 'case_recent_activity_type':
360 if ($activityType = CRM_Core_OptionGroup
::getLabel('activity_type', $value, 'value')) {
361 $names = $activityType;
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;
372 case 'case_activity_status_id':
374 if ($activityStatus = CRM_Core_OptionGroup
::getLabel('activity_status', $value, 'value')) {
375 $names = $activityStatus;
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;
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;
394 case 'case_activity_medium_id':
396 if ($activityMedium = CRM_Core_OptionGroup
::getLabel('encounter_medium', $value, 'value')) {
397 $names = $activityMedium;
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;
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;
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;
424 // adding where clause for 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;
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'
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'
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;
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;
461 foreach ($taglist as $val) {
463 $val = explode(',', $val);
464 foreach ($val as $tId) {
465 if (is_numeric($tId)) {
472 $tags = CRM_Core_PseudoConstant
::get('CRM_Core_DAO_EntityTag', 'tag_id', array('onlyActive' => FALSE));
474 if (is_array($value)) {
475 foreach ($value as $k => $v) {
478 $names[] = $tags[$k];
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;
493 * @param string $name
499 public static function from($name, $mode, $side) {
503 case 'civicrm_case_contact':
504 $from .= " $side JOIN civicrm_case_contact ON civicrm_case_contact.contact_id = contact_a.id ";
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 ";
515 $from .= " INNER JOIN civicrm_case ON civicrm_case_contact.case_id = civicrm_case.id";
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 ) ";
524 $from .= " $side JOIN civicrm_case_type ON civicrm_case.case_type_id = civicrm_case_type.id ";
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 ) ";
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 ) ";
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 )";
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 )";
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 ) ";
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 )";
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 ) ";
567 * Getter for the qill object.
571 public function qill() {
572 return (isset($this->_qill
)) ?
$this->_qill
: "";
577 * @param bool $includeCustomFields
581 public static function defaultReturnProperties(
583 $includeCustomFields = TRUE
588 if ($mode & CRM_Contact_BAO_Query
::MODE_CASE
) {
591 'contact_sub_type' => 1,
596 'case_activity_subject' => 1,
602 'case_recent_activity_date' => 1,
603 'case_recent_activity_type' => 1,
604 'case_scheduled_activity_date' => 1,
606 // 'case_scheduled_activity_type'=> 1
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;
624 * This includes any extra fields that might need for export etc.
626 public static function extraReturnProperties($mode) {
629 if ($mode & CRM_Contact_BAO_Query
::MODE_CASE
) {
631 'case_start_date' => 1,
632 'case_end_date' => 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,
648 public static function tableNames(&$tables) {
649 if (!empty($tables['civicrm_case'])) {
650 $tables = array_merge(array('civicrm_case_contact' => 1), $tables);
653 if (!empty($tables['case_relation_type'])) {
654 $tables = array_merge(array('case_relationship' => 1), $tables);
659 * Add all the elements shared between case search and advanaced search.
662 * @param CRM_Core_Form $form
666 public static function buildSearchForm(&$form) {
667 $config = CRM_Core_Config
::singleton();
669 //validate case configuration.
670 $configured = CRM_Case_BAO_Case
::isCaseConfigured();
671 $form->assign('notConfigured', !$configured['configured']);
673 $form->add('select', 'case_type_id',
675 CRM_Case_PseudoConstant
::caseType('title', FALSE),
676 FALSE, array('class' => 'crm-select2', 'multiple' => 'multiple')
679 $form->add('select', 'case_status_id',
681 CRM_Case_PseudoConstant
::caseStatus('label', FALSE),
682 FALSE, array('class' => 'crm-select2', 'multiple' => 'multiple')
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);
688 $form->assign('validCiviCase', TRUE);
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);
697 $form->assign('accessAllCases', $accessAllCases);
699 $caseTags = CRM_Core_BAO_Tag
::getTags('civicrm_case');
702 foreach ($caseTags as $tagID => $tagName) {
703 $form->_tagElement
= &$form->addElement('checkbox', "case_tags[$tagID]", NULL, $tagName);
707 $parentNames = CRM_Core_BAO_Tag
::getTagSet('civicrm_case');
708 CRM_Core_Form_Tag
::buildQuickForm($form, $parentNames, 'civicrm_case', NULL, TRUE, FALSE);
710 if (CRM_Core_Permission
::check('administer CiviCRM')) {
711 $form->addElement('checkbox', 'case_deleted', ts('Deleted Cases'));
714 // add all the custom searchable fields
715 $extends = array('Case');
716 $groupDetails = CRM_Core_BAO_CustomGroup
::getGroupDetail(NULL, TRUE, $extends);
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,
731 $form->setDefaults(array('case_owner' => 1));
738 public static function searchAction(&$row, $id) {