3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.3 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2013 |
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-2013
35 class CRM_Case_BAO_Query
{
37 static function &getFields() {
39 $fields = CRM_Case_BAO_Case
::exportableFields();
41 // add activity related fields
42 $fields = array_merge($fields, CRM_Activity_BAO_Activity
::exportableFields('Case'));
48 * build select for Case
53 static function select(&$query) {
54 if (($query->_mode
& CRM_Contact_BAO_Query
::MODE_CASE
) ||
55 CRM_Utils_Array
::value('case_id', $query->_returnProperties
)
57 $query->_select
['case_id'] = "civicrm_case.id as case_id";
58 $query->_element
['case_id'] = 1;
59 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
60 $query->_tables
['civicrm_case_contact'] = $query->_whereTables
['civicrm_case_contact'] = 1;
63 if (CRM_Utils_Array
::value('case_type_id', $query->_returnProperties
)) {
64 $query->_select
['case_type_id'] = "case_type.id as case_type_id";
65 $query->_element
['case_type_id'] = 1;
66 $query->_tables
['case_type'] = $query->_whereTables
['case_type'] = 1;
67 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
70 if (CRM_Utils_Array
::value('case_type', $query->_returnProperties
)) {
71 $query->_select
['case_type'] = "case_type.label as case_type";
72 $query->_element
['case_type'] = 1;
73 $query->_tables
['case_type'] = $query->_whereTables
['case_type'] = 1;
74 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
77 if (CRM_Utils_Array
::value('case_start_date', $query->_returnProperties
)) {
78 $query->_select
['case_start_date'] = "civicrm_case.start_date as case_start_date";
79 $query->_element
['case_start_date'] = 1;
80 $query->_tables
['civicrm_case'] = 1;
83 if (CRM_Utils_Array
::value('case_end_date', $query->_returnProperties
)) {
84 $query->_select
['case_end_date'] = "civicrm_case.end_date as case_end_date";
85 $query->_element
['case_end_date'] = 1;
86 $query->_tables
['civicrm_case'] = 1;
89 if (CRM_Utils_Array
::value('case_status_id', $query->_returnProperties
)) {
90 $query->_select
['case_status_id'] = "case_status.id as case_status_id";
91 $query->_element
['case_status_id'] = 1;
92 $query->_tables
['case_status_id'] = $query->_whereTables
['case_status_id'] = 1;
93 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
96 if (CRM_Utils_Array
::value('case_status', $query->_returnProperties
)) {
97 $query->_select
['case_status'] = "case_status.label as case_status";
98 $query->_element
['case_status'] = 1;
99 $query->_tables
['case_status_id'] = $query->_whereTables
['case_status_id'] = 1;
100 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
103 if (CRM_Utils_Array
::value('case_deleted', $query->_returnProperties
)) {
104 $query->_select
['case_deleted'] = "civicrm_case.is_deleted as case_deleted";
105 $query->_element
['case_deleted'] = 1;
106 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
109 if (CRM_Utils_Array
::value('case_role', $query->_returnProperties
)) {
110 $query->_select
['case_role'] = "case_relation_type.label_b_a as case_role";
111 $query->_element
['case_role'] = 1;
112 $query->_tables
['case_relationship'] = $query->_whereTables
['case_relationship'] = 1;
113 $query->_tables
['case_relation_type'] = $query->_whereTables
['case_relation_type'] = 1;
116 if (CRM_Utils_Array
::value('case_recent_activity_date', $query->_returnProperties
)) {
117 $query->_select
['case_recent_activity_date'] = "case_activity.activity_date_time as case_recent_activity_date";
118 $query->_element
['case_recent_activity_date'] = 1;
119 $query->_tables
['case_activity'] = $query->_whereTables
['case_activity'] = 1;
122 if (CRM_Utils_Array
::value('case_activity_subject', $query->_returnProperties
)) {
123 $query->_select
['case_activity_subject'] = "case_activity.subject as case_activity_subject";
124 $query->_element
['case_activity_subject'] = 1;
125 $query->_tables
['case_activity'] = 1;
126 $query->_tables
['civicrm_case_contact'] = 1;
127 $query->_tables
['civicrm_case'] = 1;
130 if (CRM_Utils_Array
::value('case_subject', $query->_returnProperties
)) {
131 $query->_select
['case_subject'] = "civicrm_case.subject as case_subject";
132 $query->_element
['case_subject'] = 1;
133 $query->_tables
['civicrm_case_contact'] = 1;
134 $query->_tables
['civicrm_case'] = 1;
137 if (CRM_Utils_Array
::value('case_source_contact_id', $query->_returnProperties
)) {
138 $query->_select
['case_source_contact_id'] = "civicrm_case_reporter.sort_name as case_source_contact_id";
139 $query->_element
['case_source_contact_id'] = 1;
140 $query->_tables
['civicrm_case_reporter'] = 1;
141 $query->_tables
['case_activity'] = 1;
142 $query->_tables
['civicrm_case_contact'] = 1;
143 $query->_tables
['civicrm_case'] = 1;
146 if (CRM_Utils_Array
::value('case_activity_status_id', $query->_returnProperties
)) {
147 $query->_select
['case_activity_status_id'] = "rec_activity_status.id as case_activity_status_id";
148 $query->_element
['case_activity_status_id'] = 1;
149 $query->_tables
['case_activity'] = 1;
150 $query->_tables
['recent_activity_status'] = 1;
151 $query->_tables
['civicrm_case_contact'] = 1;
152 $query->_tables
['civicrm_case'] = 1;
155 if (CRM_Utils_Array
::value('case_activity_status', $query->_returnProperties
)) {
156 $query->_select
['case_activity_status'] = "rec_activity_status.label as case_activity_status";
157 $query->_element
['case_activity_status'] = 1;
158 $query->_tables
['case_activity'] = 1;
159 $query->_tables
['recent_activity_status'] = 1;
160 $query->_tables
['civicrm_case_contact'] = 1;
161 $query->_tables
['civicrm_case'] = 1;
164 if (CRM_Utils_Array
::value('case_activity_duration', $query->_returnProperties
)) {
165 $query->_select
['case_activity_duration'] = "case_activity.duration as case_activity_duration";
166 $query->_element
['case_activity_duration'] = 1;
167 $query->_tables
['case_activity'] = 1;
168 $query->_tables
['civicrm_case_contact'] = 1;
169 $query->_tables
['civicrm_case'] = 1;
172 if (CRM_Utils_Array
::value('case_activity_medium_id', $query->_returnProperties
)) {
173 $query->_select
['case_activity_medium_id'] = "recent_activity_medium.label as case_activity_medium_id";
174 $query->_element
['case_activity_medium_id'] = 1;
175 $query->_tables
['case_activity'] = 1;
176 $query->_tables
['case_activity_medium'] = 1;
177 $query->_tables
['civicrm_case_contact'] = 1;
178 $query->_tables
['civicrm_case'] = 1;
181 if (CRM_Utils_Array
::value('case_activity_details', $query->_returnProperties
)) {
182 $query->_select
['case_activity_details'] = "case_activity.details as case_activity_details";
183 $query->_element
['case_activity_details'] = 1;
184 $query->_tables
['case_activity'] = 1;
185 $query->_tables
['civicrm_case_contact'] = 1;
186 $query->_tables
['civicrm_case'] = 1;
189 if (CRM_Utils_Array
::value('case_activity_is_auto', $query->_returnProperties
)) {
190 $query->_select
['case_activity_is_auto'] = "case_activity.is_auto as case_activity_is_auto";
191 $query->_element
['case_activity_is_auto'] = 1;
192 $query->_tables
['case_activity'] = 1;
193 $query->_tables
['civicrm_case_contact'] = 1;
194 $query->_tables
['civicrm_case'] = 1;
197 if (CRM_Utils_Array
::value('case_scheduled_activity_date', $query->_returnProperties
)) {
198 $query->_select
['case_scheduled_activity_date'] = "case_activity.activity_date_time as case_scheduled_activity_date";
199 $query->_element
['case_scheduled_activity_date'] = 1;
200 $query->_tables
['case_activity'] = 1;
201 $query->_tables
['civicrm_case_contact'] = 1;
202 $query->_tables
['civicrm_case'] = 1;
204 if (CRM_Utils_Array
::value('case_recent_activity_type', $query->_returnProperties
)) {
205 $query->_select
['case_recent_activity_type'] = "rec_activity_type.label as case_recent_activity_type";
206 $query->_element
['case_recent_activity_type'] = 1;
207 $query->_tables
['case_activity'] = 1;
208 $query->_tables
['case_activity_type'] = 1;
209 $query->_tables
['civicrm_case_contact'] = 1;
210 $query->_tables
['civicrm_case'] = 1;
215 * Given a list of conditions in query generate the required
221 static function where(&$query) {
222 foreach ($query->_params
as $id => $values) {
223 if (!is_array($values) ||
count($values) != 5) {
227 if (substr($query->_params
[$id][0], 0, 5) == 'case_') {
228 if ($query->_mode
== CRM_Contact_BAO_Query
::MODE_CONTACTS
) {
229 $query->_useDistinct
= TRUE;
231 $grouping = $query->_params
[$id][3];
232 self
::whereClauseSingle($query->_params
[$id], $query);
238 * where clause for a single field
243 static function whereClauseSingle(&$values, &$query) {
244 list($name, $op, $value, $grouping, $wildcard) = $values;
245 $val = $names = array();
248 case 'case_status_id':
249 $statuses = CRM_Case_PseudoConstant
::caseStatus();
250 // Standardize input from checkboxes or single value
251 if (is_array($value)) {
252 $value = array_keys($value, 1);
254 foreach ((array) $value as $k) {
255 if ($k && isset($statuses[$k])) {
257 $names[] = $statuses[$k];
259 elseif ($k && ($v = CRM_Utils_Array
::key($k, $statuses))) {
265 $query->_where
[$grouping][] = "civicrm_case.status_id IN (" . implode(',', $val) . ")";
266 $query->_qill
[$grouping][] = ts('Case Status is %1', array(1 => implode(' ' . ts('or') . ' ', $names)));
267 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
272 $caseTypes = CRM_Case_PseudoConstant
::caseType('label', FALSE);
274 if (is_array($value)) {
275 foreach ($value as $k => $v) {
278 $names[] = $caseTypes[$k];
282 elseif (is_numeric($value)) {
283 $val[$value] = $value;
286 elseif ($caseTypeId = CRM_Utils_Array
::key($value, $caseTypes)) {
287 $val[$caseTypeId] = $caseTypeId;
288 $names[] = $caseTypes[$caseTypeId];
291 $value = CRM_Core_DAO
::VALUE_SEPARATOR
. implode(CRM_Core_DAO
::VALUE_SEPARATOR
. "%' OR civicrm_case.case_type_id LIKE '%" .
292 CRM_Core_DAO
::VALUE_SEPARATOR
, $val
293 ) . CRM_Core_DAO
::VALUE_SEPARATOR
;
295 $query->_where
[$grouping][] = "(civicrm_case.case_type_id LIKE '%{$value}%')";
297 $query->_qill
[$grouping][] = ts('Case Type is %1', array(1 => implode(' ' . ts('or') . ' ', $names)));
298 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
302 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_case.id", $op, $value, 'Int');
303 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
308 if (!empty($value)) {
310 $session = CRM_Core_Session
::singleton();
311 $userID = $session->get('userID');
312 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("case_relationship.contact_id_b", $op, $userID, 'Int');
313 $query->_qill
[$grouping][] = ts('Case %1 My Cases', array(1 => $op));
314 $query->_tables
['case_relationship'] = $query->_whereTables
['case_relationship'] = 1;
316 elseif ($value == 1) {
317 $query->_qill
[$grouping][] = ts('Case %1 All Cases', array(1 => $op));
318 $query->_where
[$grouping][] = "civicrm_case_contact.contact_id = contact_a.id";
320 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
321 $query->_tables
['civicrm_case_contact'] = $query->_whereTables
['civicrm_case_contact'] = 1;
326 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_case.is_deleted", $op, $value, 'Boolean');
328 $query->_qill
[$grouping][] = ts("Find Deleted Cases");
330 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
333 case 'case_activity_subject':
334 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("case_activity.subject", $op, $value, 'String');
335 $query->_qill
[$grouping][] = ts("Activity Subject %1 '%2'", array(1 => $op, 2 => $value));
336 $query->_tables
['case_activity'] = $query->_whereTables
['case_activity'] = 1;
337 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
338 $query->_tables
['civicrm_case_contact'] = $query->_whereTables
['civicrm_case_contact'] = 1;
342 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_case.subject", $op, $value, 'String');
343 $query->_qill
[$grouping][] = ts("Case Subject %1 '%2'", array(1 => $op, 2 => $value));
344 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
345 $query->_tables
['civicrm_case_contact'] = $query->_whereTables
['civicrm_case_contact'] = 1;
348 case 'case_source_contact_id':
349 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_case_reporter.sort_name", $op, $value, 'String');
350 $query->_qill
[$grouping][] = ts("Activity Reporter %1 '%2'", array(1 => $op, 2 => $value));
351 $query->_tables
['case_activity'] = $query->_whereTables
['case_activity'] = 1;
352 $query->_tables
['civicrm_case_reporter'] = $query->_whereTables
['civicrm_case_reporter'] = 1;
353 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
354 $query->_tables
['civicrm_case_contact'] = $query->_whereTables
['civicrm_case_contact'] = 1;
357 case 'case_recent_activity_date':
358 $date = CRM_Utils_Date
::format($value);
359 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("case_activity.activity_date_time", $op, $date, 'Date');
361 $date = CRM_Utils_Date
::customFormat($date);
362 $query->_qill
[$grouping][] = ts("Activity Actual Date %1 %2", array(1 => $op, 2 => $date));
364 $query->_tables
['case_activity'] = $query->_whereTables
['case_activity'] = 1;
365 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
366 $query->_tables
['civicrm_case_contact'] = $query->_whereTables
['civicrm_case_contact'] = 1;
369 case 'case_scheduled_activity_date':
370 $date = CRM_Utils_Date
::format($value);
371 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("case_activity.activity_date_time", $op, $date, 'Date');
373 $date = CRM_Utils_Date
::customFormat($date);
374 $query->_qill
[$grouping][] = ts("Activity Schedule Date %1 %2", array(1 => $op, 2 => $date));
376 $query->_tables
['case_activity'] = $query->_whereTables
['case_activity'] = 1;
377 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
378 $query->_tables
['civicrm_case_contact'] = $query->_whereTables
['civicrm_case_contact'] = 1;
381 case 'case_recent_activity_type':
383 if ($activityType = CRM_Core_OptionGroup
::getLabel('activity_type', $value, 'value')) {
384 $names = $activityType;
387 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("case_activity.activity_type_id", $op, $value, 'Int');
388 $query->_qill
[$grouping][] = ts("Activity Type %1 %2", array(1 => $op, 2 => $names));
389 $query->_tables
['case_activity'] = $query->_whereTables
['case_activity'] = 1;
390 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
391 $query->_tables
['case_activity_type'] = 1;
392 $query->_tables
['civicrm_case_contact'] = $query->_whereTables
['civicrm_case_contact'] = 1;
395 case 'case_activity_status_id':
397 if ($activityStatus = CRM_Core_OptionGroup
::getLabel('activity_status', $value, 'value')) {
398 $names = $activityStatus;
401 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("case_activity.status_id", $op, $value, 'Int');
402 $query->_qill
[$grouping][] = ts("Activity Type %1 %2", array(1 => $op, 2 => $names));
403 $query->_tables
['case_activity'] = $query->_whereTables
['case_activity'] = 1;
404 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
405 $query->_tables
['case_activity_status'] = 1;
406 $query->_tables
['civicrm_case_contact'] = $query->_whereTables
['civicrm_case_contact'] = 1;
409 case 'case_activity_duration':
410 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("case_activity.duration", $op, $value, 'Int');
411 $query->_qill
[$grouping][] = ts("Activity Duration %1 %2", array(1 => $op, 2 => $value));
412 $query->_tables
['case_activity'] = $query->_whereTables
['case_activity'] = 1;
413 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
414 $query->_tables
['civicrm_case_contact'] = $query->_whereTables
['civicrm_case_contact'] = 1;
417 case 'case_activity_medium_id':
419 if ($activityMedium = CRM_Core_OptionGroup
::getLabel('encounter_medium', $value, 'value')) {
420 $names = $activityMedium;
423 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("case_activity.medium_id", $op, $value, 'Int');
424 $query->_qill
[$grouping][] = ts("Activity Medium %1 %2", array(1 => $op, 2 => $names));
425 $query->_tables
['case_activity'] = $query->_whereTables
['case_activity'] = 1;
426 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
427 $query->_tables
['case_activity_medium'] = 1;
428 $query->_tables
['civicrm_case_contact'] = $query->_whereTables
['civicrm_case_contact'] = 1;
431 case 'case_activity_details':
432 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("case_activity.details", $op, $value, 'String');
433 $query->_qill
[$grouping][] = ts("Activity Details %1 '%2'", array(1 => $op, 2 => $value));
434 $query->_tables
['case_activity'] = $query->_whereTables
['case_activity'] = 1;
435 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
436 $query->_tables
['civicrm_case_contact'] = $query->_whereTables
['civicrm_case_contact'] = 1;
439 case 'case_activity_is_auto':
440 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("case_activity.is_auto", $op, $value, 'Boolean');
441 $query->_qill
[$grouping][] = ts("Activity Auto Genrated %1 '%2'", array(1 => $op, 2 => $value));
442 $query->_tables
['case_activity'] = $query->_whereTables
['case_activity'] = 1;
443 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
444 $query->_tables
['civicrm_case_contact'] = $query->_whereTables
['civicrm_case_contact'] = 1;
447 // adding where clause for case_role
450 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("case_relation_type.name_b_a", $op, $value, 'String');
451 $query->_qill
[$grouping][] = ts("Role in Case %1 '%2'", array(1 => $op, 2 => $value));
452 $query->_tables
['case_relation_type'] = $query->_whereTables
['case_relationship_type'] = 1;
453 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
454 $query->_tables
['civicrm_case_contact'] = $query->_whereTables
['civicrm_case_contact'] = 1;
457 case 'case_from_start_date_low':
458 case 'case_from_start_date_high':
459 $query->dateQueryBuilder($values,
460 'civicrm_case', 'case_from_start_date', 'start_date', 'Start Date'
464 case 'case_to_end_date_low':
465 case 'case_to_end_date_high':
466 $query->dateQueryBuilder($values,
467 'civicrm_case', 'case_to_end_date', 'end_date', 'End Date'
471 case 'case_start_date':
472 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_case.start_date", $op, $value, 'Int');
473 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
476 case 'case_end_date':
477 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_case.end_date", $op, $value, 'Int');
478 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
484 foreach ($taglist as $val) {
486 $val = explode(',', $val);
487 foreach ($val as $tId) {
488 if (is_numeric($tId)) {
495 $tags = CRM_Core_PseudoConstant
::tag();
497 if (is_array($value)) {
498 foreach ($value as $k => $v) {
501 $names[] = $tags[$k];
506 $query->_where
[$grouping][] = " civicrm_case_tag.tag_id IN (" . implode(',', $val) . " )";
507 $query->_qill
[$grouping][] = ts('Case Tags %1', array(1 => $op)) . ' ' . implode(' ' . ts('or') . ' ', $names);
508 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
509 $query->_tables
['civicrm_case_contact'] = $query->_whereTables
['civicrm_case_contact'] = 1;
510 $query->_tables
['civicrm_case_tag'] = $query->_whereTables
['civicrm_case_tag'] = 1;
515 static function from($name, $mode, $side) {
519 case 'civicrm_case_contact':
520 $from .= " $side JOIN civicrm_case_contact ON civicrm_case_contact.contact_id = contact_a.id ";
523 case 'civicrm_case_reporter':
524 $from .= " $side JOIN civicrm_contact as civicrm_case_reporter ON case_activity.source_contact_id = civicrm_case_reporter.id ";
528 $from .= " INNER JOIN civicrm_case ON civicrm_case_contact.case_id = civicrm_case.id";
531 case 'case_status_id':
532 $from .= " $side JOIN civicrm_option_group option_group_case_status ON (option_group_case_status.name = 'case_status')";
533 $from .= " $side JOIN civicrm_option_value case_status ON (civicrm_case.status_id = case_status.value AND option_group_case_status.id = case_status.option_group_id ) ";
537 $from .= " $side JOIN civicrm_option_group option_group_case_type ON (option_group_case_type.name = 'case_type')";
538 $from .= " $side JOIN civicrm_option_value case_type ON (civicrm_case.case_type_id = case_type.value AND option_group_case_type.id = case_type.option_group_id ) ";
541 case 'case_activity_type':
542 $from .= " $side JOIN civicrm_option_group option_group_activity_type ON (option_group_activity_type.name = 'activity_type')";
543 $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 ) ";
546 case 'recent_activity_status':
547 $from .= " $side JOIN civicrm_option_group option_group_activity_status ON (option_group_activity_status.name = 'activity_status')";
548 $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 ) ";
551 case 'case_relationship':
552 $session = CRM_Core_Session
::singleton();
553 $userID = $session->get('userID');
554 $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 )";
557 case 'case_relation_type':
558 $from .= " $side JOIN civicrm_relationship_type case_relation_type ON ( case_relation_type.id = case_relationship.relationship_type_id AND
559 case_relation_type.id = case_relationship.relationship_type_id )";
562 case 'case_activity_medium':
563 $from .= " $side JOIN civicrm_option_group option_group_activity_medium ON (option_group_activity_medium.name = 'encounter_medium')";
564 $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 ) ";
567 case 'case_activity':
568 $from .= " INNER JOIN civicrm_case_activity ON civicrm_case_activity.case_id = civicrm_case.id ";
569 $from .= " INNER JOIN civicrm_activity case_activity ON ( civicrm_case_activity.activity_id = case_activity.id
570 AND case_activity.is_current_revision = 1 )";
573 case 'civicrm_case_tag':
574 $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 ) ";
581 * getter for the qill object
587 return (isset($this->_qill
)) ?
$this->_qill
: "";
590 static function defaultReturnProperties($mode,
591 $includeCustomFields = TRUE
596 if ($mode & CRM_Contact_BAO_Query
::MODE_CASE
) {
599 'contact_sub_type' => 1,
604 'case_activity_subject' => 1,
610 'case_recent_activity_date' => 1,
611 'case_recent_activity_type' => 1,
612 'case_scheduled_activity_date' => 1,
614 // 'case_scheduled_activity_type'=> 1
617 if ($includeCustomFields) {
618 // also get all the custom case properties
619 $fields = CRM_Core_BAO_CustomField
::getFieldsForImport('Case');
620 if (!empty($fields)) {
621 foreach ($fields as $name => $dontCare) {
622 $properties[$name] = 1;
632 * This includes any extra fields that might need for export etc
634 static function extraReturnProperties($mode) {
637 if ($mode & CRM_Contact_BAO_Query
::MODE_CASE
) {
639 'case_start_date' => 1,
640 'case_end_date' => 1,
642 'case_source_contact_id' => 1,
643 'case_activity_status' => 1,
644 'case_activity_duration' => 1,
645 'case_activity_medium_id' => 1,
646 'case_activity_details' => 1,
647 'case_activity_is_auto' => 1,
653 static function tableNames(&$tables) {
654 if (CRM_Utils_Array
::value('civicrm_case', $tables)) {
655 $tables = array_merge(array('civicrm_case_contact' => 1), $tables);
658 if (CRM_Utils_Array
::value('case_relation_type', $tables)) {
659 $tables = array_merge(array('case_relationship' => 1), $tables);
664 * add all the elements shared between case search and advanaced search
671 static function buildSearchForm(&$form) {
672 $config = CRM_Core_Config
::singleton();
674 //validate case configuration.
675 $configured = CRM_Case_BAO_Case
::isCaseConfigured();
676 $form->assign('notConfigured', !$configured['configured']);
678 $caseTypes = CRM_Case_PseudoConstant
::caseType('label', FALSE);
679 foreach ($caseTypes as $id => $name) {
680 $form->addElement('checkbox', "case_type_id[$id]", NULL, $name);
683 $statuses = CRM_Case_PseudoConstant
::caseStatus('label', FALSE);
684 foreach ($statuses as $id => $name) {
685 $form->addElement('checkbox', "case_status_id[$id]", NULL, $name);
688 CRM_Core_Form_Date
::buildDateRange($form, 'case_from', 1, '_start_date_low', '_start_date_high', ts('From'), FALSE, FALSE);
689 CRM_Core_Form_Date
::buildDateRange($form, 'case_to', 1, '_end_date_low', '_end_date_high', ts('From'), FALSE, FALSE);
691 $form->assign('validCiviCase', TRUE);
693 //give options when all cases are accessible.
694 $accessAllCases = FALSE;
695 if (CRM_Core_Permission
::check('access all cases and activities')) {
696 $accessAllCases = TRUE;
697 $caseOwner = array(1 => ts('Search All Cases'), 2 => ts('Only My Cases'));
698 $form->addRadio('case_owner', ts('Cases'), $caseOwner);
700 $form->assign('accessAllCases', $accessAllCases);
702 $caseTags = CRM_Core_BAO_Tag
::getTags('civicrm_case');
705 foreach ($caseTags as $tagID => $tagName) {
706 $form->_tagElement
= &$form->addElement('checkbox', "case_tags[$tagID]", NULL, $tagName);
710 $parentNames = CRM_Core_BAO_Tag
::getTagSet('civicrm_case');
711 CRM_Core_Form_Tag
::buildQuickForm($form, $parentNames, 'civicrm_case', NULL, TRUE, FALSE, TRUE);
713 if (CRM_Core_Permission
::check('administer CiviCRM')) {
714 $form->addElement('checkbox', 'case_deleted', ts('Deleted Cases'));
717 // add all the custom searchable fields
718 $extends = array('Case');
719 $groupDetails = CRM_Core_BAO_CustomGroup
::getGroupDetail(NULL, TRUE, $extends);
721 $form->assign('caseGroupTree', $groupDetails);
722 foreach ($groupDetails as $group) {
723 foreach ($group['fields'] as $field) {
724 $fieldId = $field['id'];
725 $elementName = 'custom_' . $fieldId;
726 CRM_Core_BAO_CustomField
::addQuickFormElement($form,
734 $form->setDefaults(array('case_owner' => 1));
737 static function searchAction(&$row, $id) {}
739 static function addShowHide(&$showHide) {
740 $showHide->addHide('caseForm');
741 $showHide->addShow('caseForm_show');