3 +--------------------------------------------------------------------+
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2019 |
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-2019
33 class CRM_Case_BAO_Query
extends CRM_Core_BAO_Query
{
38 * @param bool $excludeActivityFields
42 public static function &getFields($excludeActivityFields = FALSE) {
43 $fields = CRM_Case_BAO_Case
::exportableFields();
45 // add activity related fields
46 if (!$excludeActivityFields) {
47 $fields = array_merge($fields, CRM_Activity_BAO_Activity
::exportableFields('Case'));
54 * Build select for Case.
56 * @param CRM_Contact_BAO_Query $query
58 public static function select(&$query) {
59 if (($query->_mode
& CRM_Contact_BAO_Query
::MODE_CASE
) ||
!empty($query->_returnProperties
['case_id'])) {
60 $query->_select
['case_id'] = "civicrm_case.id as case_id";
61 $query->_element
['case_id'] = 1;
62 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
63 $query->_tables
['civicrm_case_contact'] = $query->_whereTables
['civicrm_case_contact'] = 1;
66 if (!empty($query->_returnProperties
['case_type_id'])) {
67 $query->_select
['case_type_id'] = "civicrm_case_type.id as case_type_id";
68 $query->_element
['case_type_id'] = 1;
69 $query->_tables
['case_type'] = $query->_whereTables
['case_type'] = 1;
70 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
73 if (!empty($query->_returnProperties
['case_type'])) {
74 $query->_select
['case_type'] = "civicrm_case_type.title as case_type";
75 $query->_element
['case_type'] = 1;
76 $query->_tables
['case_type'] = $query->_whereTables
['case_type'] = 1;
77 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
80 if (!empty($query->_returnProperties
['case_start_date'])) {
81 $query->_select
['case_start_date'] = "civicrm_case.start_date as case_start_date";
82 $query->_element
['case_start_date'] = 1;
83 $query->_tables
['civicrm_case'] = 1;
86 if (!empty($query->_returnProperties
['case_end_date'])) {
87 $query->_select
['case_end_date'] = "civicrm_case.end_date as case_end_date";
88 $query->_element
['case_end_date'] = 1;
89 $query->_tables
['civicrm_case'] = 1;
92 if (!empty($query->_returnProperties
['case_status_id'])) {
93 $query->_select
['case_status_id'] = "case_status.id as case_status_id";
94 $query->_element
['case_status_id'] = 1;
95 $query->_tables
['case_status_id'] = $query->_whereTables
['case_status_id'] = 1;
96 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
99 if (!empty($query->_returnProperties
['case_status'])) {
100 $query->_select
['case_status'] = "case_status.label as case_status";
101 $query->_element
['case_status'] = 1;
102 $query->_tables
['case_status_id'] = $query->_whereTables
['case_status_id'] = 1;
103 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
106 if (!empty($query->_returnProperties
['case_deleted'])) {
107 $query->_select
['case_deleted'] = "civicrm_case.is_deleted as case_deleted";
108 $query->_element
['case_deleted'] = 1;
109 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
112 if (!empty($query->_returnProperties
['case_role'])) {
113 $query->_select
['case_role'] = "case_relation_type.label_b_a as case_role";
114 $query->_element
['case_role'] = 1;
115 $query->_tables
['case_relationship'] = $query->_whereTables
['case_relationship'] = 1;
116 $query->_tables
['case_relation_type'] = $query->_whereTables
['case_relation_type'] = 1;
119 if (!empty($query->_returnProperties
['case_recent_activity_date'])) {
120 $query->_select
['case_recent_activity_date'] = "case_activity.activity_date_time as case_recent_activity_date";
121 $query->_element
['case_recent_activity_date'] = 1;
122 $query->_tables
['case_activity'] = $query->_whereTables
['case_activity'] = 1;
125 if (!empty($query->_returnProperties
['case_activity_subject'])) {
126 $query->_select
['case_activity_subject'] = "case_activity.subject as case_activity_subject";
127 $query->_element
['case_activity_subject'] = 1;
128 $query->_tables
['case_activity'] = 1;
129 $query->_tables
['civicrm_case_contact'] = 1;
130 $query->_tables
['civicrm_case'] = 1;
133 if (!empty($query->_returnProperties
['case_subject'])) {
134 $query->_select
['case_subject'] = "civicrm_case.subject as case_subject";
135 $query->_element
['case_subject'] = 1;
136 $query->_tables
['civicrm_case_contact'] = 1;
137 $query->_tables
['civicrm_case'] = 1;
140 if (!empty($query->_returnProperties
['case_source_contact_id'])) {
141 $query->_select
['case_source_contact_id'] = "civicrm_case_reporter.sort_name as case_source_contact_id";
142 $query->_element
['case_source_contact_id'] = 1;
143 $query->_tables
['civicrm_case_reporter'] = 1;
144 $query->_tables
['case_activity'] = 1;
145 $query->_tables
['civicrm_case_contact'] = 1;
146 $query->_tables
['civicrm_case'] = 1;
149 if (!empty($query->_returnProperties
['case_activity_status_id'])) {
150 $query->_select
['case_activity_status_id'] = "rec_activity_status.id as case_activity_status_id";
151 $query->_element
['case_activity_status_id'] = 1;
152 $query->_tables
['case_activity'] = 1;
153 $query->_tables
['recent_activity_status'] = 1;
154 $query->_tables
['civicrm_case_contact'] = 1;
155 $query->_tables
['civicrm_case'] = 1;
158 if (!empty($query->_returnProperties
['case_activity_status'])) {
159 $query->_select
['case_activity_status'] = "rec_activity_status.label as case_activity_status";
160 $query->_element
['case_activity_status'] = 1;
161 $query->_tables
['case_activity'] = 1;
162 $query->_tables
['recent_activity_status'] = 1;
163 $query->_tables
['civicrm_case_contact'] = 1;
164 $query->_tables
['civicrm_case'] = 1;
167 if (!empty($query->_returnProperties
['case_activity_duration'])) {
168 $query->_select
['case_activity_duration'] = "case_activity.duration as case_activity_duration";
169 $query->_element
['case_activity_duration'] = 1;
170 $query->_tables
['case_activity'] = 1;
171 $query->_tables
['civicrm_case_contact'] = 1;
172 $query->_tables
['civicrm_case'] = 1;
175 if (!empty($query->_returnProperties
['case_activity_medium_id'])) {
176 $query->_select
['case_activity_medium_id'] = "recent_activity_medium.label as case_activity_medium_id";
177 $query->_element
['case_activity_medium_id'] = 1;
178 $query->_tables
['case_activity'] = 1;
179 $query->_tables
['case_activity_medium'] = 1;
180 $query->_tables
['civicrm_case_contact'] = 1;
181 $query->_tables
['civicrm_case'] = 1;
184 if (!empty($query->_returnProperties
['case_activity_details'])) {
185 $query->_select
['case_activity_details'] = "case_activity.details as case_activity_details";
186 $query->_element
['case_activity_details'] = 1;
187 $query->_tables
['case_activity'] = 1;
188 $query->_tables
['civicrm_case_contact'] = 1;
189 $query->_tables
['civicrm_case'] = 1;
192 if (!empty($query->_returnProperties
['case_activity_is_auto'])) {
193 $query->_select
['case_activity_is_auto'] = "case_activity.is_auto as case_activity_is_auto";
194 $query->_element
['case_activity_is_auto'] = 1;
195 $query->_tables
['case_activity'] = 1;
196 $query->_tables
['civicrm_case_contact'] = 1;
197 $query->_tables
['civicrm_case'] = 1;
200 if (!empty($query->_returnProperties
['case_scheduled_activity_date'])) {
201 $query->_select
['case_scheduled_activity_date'] = "case_activity.activity_date_time as case_scheduled_activity_date";
202 $query->_element
['case_scheduled_activity_date'] = 1;
203 $query->_tables
['case_activity'] = 1;
204 $query->_tables
['civicrm_case_contact'] = 1;
205 $query->_tables
['civicrm_case'] = 1;
207 if (!empty($query->_returnProperties
['case_recent_activity_type'])) {
208 $query->_select
['case_recent_activity_type'] = "rec_activity_type.label as case_recent_activity_type";
209 $query->_element
['case_recent_activity_type'] = 1;
210 $query->_tables
['case_activity'] = 1;
211 $query->_tables
['case_activity_type'] = 1;
212 $query->_tables
['civicrm_case_contact'] = 1;
213 $query->_tables
['civicrm_case'] = 1;
218 * Given a list of conditions in query generate the required where clause.
220 * @param CRM_Contact_BAO_Query $query
222 public static function where(&$query) {
223 foreach ($query->_params
as $id => $values) {
224 if (!is_array($values) ||
count($values) != 5) {
228 if (substr($query->_params
[$id][0], 0, 5) == 'case_') {
229 if ($query->_mode
== CRM_Contact_BAO_Query
::MODE_CONTACTS
) {
230 $query->_useDistinct
= TRUE;
232 self
::whereClauseSingle($query->_params
[$id], $query);
236 // This is new and so far only for cases - it would be good to find a more abstract
237 // way to auto-apply this for all search components rather than copy-pasting this code to others
238 if (isset($query->_tables
['civicrm_case'])) {
239 $aclClauses = array_filter(CRM_Case_BAO_Case
::getSelectWhereClause());
240 foreach ($aclClauses as $clause) {
241 $query->_where
[0][] = $clause;
247 * Where clause for a single field.
249 * CRM-17120 adds a test that checks the Qill on some of these parameters.
250 * However, I couldn't find a way, other than via test, to access the
251 * case_activity options in the code below and invalid sql was returned.
252 * Perhaps the options are just legacy?
254 * Also, CRM-17120 locks in the Qill - but it probably is not quite right as I
255 * see 'Activity Type = Scheduled' (rather than activity status).
257 * See CRM_Case_BAO_QueryTest for more.
259 * @param array $values
260 * @param CRM_Contact_BAO_Query $query
262 public static function whereClauseSingle(&$values, &$query) {
263 list($name, $op, $value, $grouping, $wildcard) = $values;
270 case 'case_status_id':
273 if (strpos($name, 'type')) {
274 $name = 'case_type_id';
275 $label = 'Case Type(s)';
277 elseif (strpos($name, 'status')) {
279 $label = 'Case Status(s)';
286 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_case.{$name}", $op, $value, "Integer");
287 list($op, $value) = CRM_Contact_BAO_Query
::buildQillForFieldValue('CRM_Case_DAO_Case', $name, $value, $op);
289 $query->_qill
[$grouping][] = ts('%1 %2 %3', [1 => $label, 2 => $op, 3 => $value]);
290 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
295 if (!empty($value)) {
297 $session = CRM_Core_Session
::singleton();
298 $userID = $session->get('userID');
299 $query->_where
[$grouping][] = ' ( ' . CRM_Contact_BAO_Query
::buildClause("case_relationship.contact_id_b", $op, $userID, 'Int') . ' AND ' . CRM_Contact_BAO_Query
::buildClause("case_relationship.is_active", '<>', 0, 'Int') . ' ) ';
300 $query->_qill
[$grouping][] = ts('Case %1 My Cases', [1 => $op]);
301 $query->_tables
['case_relationship'] = $query->_whereTables
['case_relationship'] = 1;
303 elseif ($value == 1) {
304 $query->_qill
[$grouping][] = ts('Case %1 All Cases', [1 => $op]);
305 $query->_where
[$grouping][] = "civicrm_case_contact.contact_id = contact_a.id";
307 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
308 $query->_tables
['civicrm_case_contact'] = $query->_whereTables
['civicrm_case_contact'] = 1;
313 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_case.is_deleted", $op, $value, 'Boolean');
315 $query->_qill
[$grouping][] = ts("Find Deleted Cases");
317 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
320 case 'case_activity_subject':
321 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("case_activity.subject", $op, $value, 'String');
322 $query->_qill
[$grouping][] = ts("Activity Subject %1 '%2'", [1 => $op, 2 => $value]);
323 $query->_tables
['case_activity'] = $query->_whereTables
['case_activity'] = 1;
324 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
325 $query->_tables
['civicrm_case_contact'] = $query->_whereTables
['civicrm_case_contact'] = 1;
329 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_case.subject", $op, $value, 'String');
330 $query->_qill
[$grouping][] = ts("Case Subject %1 '%2'", [1 => $op, 2 => $value]);
331 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
332 $query->_tables
['civicrm_case_contact'] = $query->_whereTables
['civicrm_case_contact'] = 1;
335 case 'case_source_contact_id':
336 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_case_reporter.sort_name", $op, $value, 'String');
337 $query->_qill
[$grouping][] = ts("Activity Reporter %1 '%2'", [1 => $op, 2 => $value]);
338 $query->_tables
['case_activity'] = $query->_whereTables
['case_activity'] = 1;
339 $query->_tables
['civicrm_case_reporter'] = $query->_whereTables
['civicrm_case_reporter'] = 1;
340 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
341 $query->_tables
['civicrm_case_contact'] = $query->_whereTables
['civicrm_case_contact'] = 1;
344 case 'case_recent_activity_date':
345 $date = CRM_Utils_Date
::format($value);
346 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("case_activity.activity_date_time", $op, $date, 'Date');
348 $date = CRM_Utils_Date
::customFormat($date);
349 $query->_qill
[$grouping][] = ts("Activity Actual Date %1 %2", [1 => $op, 2 => $date]);
351 $query->_tables
['case_activity'] = $query->_whereTables
['case_activity'] = 1;
352 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
353 $query->_tables
['civicrm_case_contact'] = $query->_whereTables
['civicrm_case_contact'] = 1;
356 case 'case_scheduled_activity_date':
357 $date = CRM_Utils_Date
::format($value);
358 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("case_activity.activity_date_time", $op, $date, 'Date');
360 $date = CRM_Utils_Date
::customFormat($date);
361 $query->_qill
[$grouping][] = ts("Activity Schedule Date %1 %2", [1 => $op, 2 => $date]);
363 $query->_tables
['case_activity'] = $query->_whereTables
['case_activity'] = 1;
364 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
365 $query->_tables
['civicrm_case_contact'] = $query->_whereTables
['civicrm_case_contact'] = 1;
368 case 'case_recent_activity_type':
370 if (($activityType = CRM_Core_PseudoConstant
::getLabel('CRM_Activity_BAO_Activity', 'activity_type_id', $value)) != FALSE) {
371 $names = $activityType;
374 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("case_activity.activity_type_id", $op, $value, 'Int');
375 $query->_qill
[$grouping][] = ts("Activity Type %1 %2", [1 => $op, 2 => $names]);
376 $query->_tables
['case_activity'] = $query->_whereTables
['case_activity'] = 1;
377 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
378 $query->_tables
['case_activity_type'] = 1;
379 $query->_tables
['civicrm_case_contact'] = $query->_whereTables
['civicrm_case_contact'] = 1;
382 case 'case_activity_status_id':
384 if (($activityStatus = CRM_Core_PseudoConstant
::getLabel('CRM_Activity_BAO_Activity', 'status_id', $value)) != FALSE) {
385 $names = $activityStatus;
388 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("case_activity.status_id", $op, $value, 'Int');
389 $query->_qill
[$grouping][] = ts("Activity Type %1 %2", [1 => $op, 2 => $names]);
390 $query->_tables
['case_activity'] = $query->_whereTables
['case_activity'] = 1;
391 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
392 $query->_tables
['case_activity_status'] = 1;
393 $query->_tables
['civicrm_case_contact'] = $query->_whereTables
['civicrm_case_contact'] = 1;
396 case 'case_activity_duration':
397 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("case_activity.duration", $op, $value, 'Int');
398 $query->_qill
[$grouping][] = ts("Activity Duration %1 %2", [1 => $op, 2 => $value]);
399 $query->_tables
['case_activity'] = $query->_whereTables
['case_activity'] = 1;
400 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
401 $query->_tables
['civicrm_case_contact'] = $query->_whereTables
['civicrm_case_contact'] = 1;
404 case 'case_activity_medium_id':
406 if (($activityMedium = CRM_Core_PseudoConstant
::getLabel('CRM_Activity_BAO_Activity', 'medium_id', $value)) != FALSE) {
407 $names = $activityMedium;
410 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("case_activity.medium_id", $op, $value, 'Int');
411 $query->_qill
[$grouping][] = ts("Activity Medium %1 %2", [1 => $op, 2 => $names]);
412 $query->_tables
['case_activity'] = $query->_whereTables
['case_activity'] = 1;
413 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
414 $query->_tables
['case_activity_medium'] = 1;
415 $query->_tables
['civicrm_case_contact'] = $query->_whereTables
['civicrm_case_contact'] = 1;
418 case 'case_activity_details':
419 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("case_activity.details", $op, $value, 'String');
420 $query->_qill
[$grouping][] = ts("Activity Details %1 '%2'", [1 => $op, 2 => $value]);
421 $query->_tables
['case_activity'] = $query->_whereTables
['case_activity'] = 1;
422 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
423 $query->_tables
['civicrm_case_contact'] = $query->_whereTables
['civicrm_case_contact'] = 1;
426 case 'case_activity_is_auto':
427 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("case_activity.is_auto", $op, $value, 'Boolean');
428 $query->_qill
[$grouping][] = ts("Activity Auto Genrated %1 '%2'", [1 => $op, 2 => $value]);
429 $query->_tables
['case_activity'] = $query->_whereTables
['case_activity'] = 1;
430 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
431 $query->_tables
['civicrm_case_contact'] = $query->_whereTables
['civicrm_case_contact'] = 1;
434 // adding where clause for case_role
437 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("case_relation_type.name_b_a", $op, $value, 'String');
438 $query->_qill
[$grouping][] = ts("Role in Case %1 '%2'", [1 => $op, 2 => $value]);
439 $query->_tables
['case_relation_type'] = $query->_whereTables
['case_relationship_type'] = 1;
440 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
441 $query->_tables
['civicrm_case_contact'] = $query->_whereTables
['civicrm_case_contact'] = 1;
444 case 'case_from_start_date_low':
445 case 'case_from_start_date_high':
446 $query->dateQueryBuilder($values,
447 'civicrm_case', 'case_from_start_date', 'start_date', 'Start Date'
451 case 'case_to_end_date_low':
452 case 'case_to_end_date_high':
453 $query->dateQueryBuilder($values,
454 'civicrm_case', 'case_to_end_date', 'end_date', 'End Date'
458 case 'case_start_date':
459 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_case.start_date", $op, $value, 'Int');
460 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
463 case 'case_end_date':
464 $query->_where
[$grouping][] = CRM_Contact_BAO_Query
::buildClause("civicrm_case.end_date", $op, $value, 'Int');
465 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
471 foreach ($taglist as $val) {
473 $val = explode(',', $val);
474 foreach ($val as $tId) {
475 if (is_numeric($tId)) {
482 $tags = CRM_Core_PseudoConstant
::get('CRM_Core_DAO_EntityTag', 'tag_id', ['onlyActive' => FALSE]);
484 if (!empty($value)) {
485 $val = explode(',', $value);
486 foreach ($val as $v) {
488 $names[] = $tags[$v];
493 $query->_where
[$grouping][] = " civicrm_case_tag.tag_id IN (" . implode(',', $val) . " )";
494 $query->_qill
[$grouping][] = ts('Case Tags %1', [1 => $op]) . ' ' . implode(' ' . ts('or') . ' ', $names);
495 $query->_tables
['civicrm_case'] = $query->_whereTables
['civicrm_case'] = 1;
496 $query->_tables
['civicrm_case_contact'] = $query->_whereTables
['civicrm_case_contact'] = 1;
497 $query->_tables
['civicrm_case_tag'] = $query->_whereTables
['civicrm_case_tag'] = 1;
505 * @param string $name
506 * @param string $mode
507 * @param string $side
511 public static function from($name, $mode, $side) {
515 case 'civicrm_case_contact':
516 $from .= " $side JOIN civicrm_case_contact ON civicrm_case_contact.contact_id = contact_a.id ";
519 case 'civicrm_case_reporter':
520 $activityContacts = CRM_Activity_BAO_ActivityContact
::buildOptions('record_type_id', 'validate');
521 $sourceID = CRM_Utils_Array
::key('Activity Source', $activityContacts);
522 $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} ) ";
523 $from .= " $side JOIN civicrm_contact as civicrm_case_reporter ON case_activity_contact.contact_id = civicrm_case_reporter.id ";
527 $from .= " INNER JOIN civicrm_case ON civicrm_case_contact.case_id = civicrm_case.id";
530 case 'case_status_id':
531 $from .= " $side JOIN civicrm_option_group option_group_case_status ON (option_group_case_status.name = 'case_status')";
532 $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 ) ";
536 $from .= " $side JOIN civicrm_case_type ON civicrm_case.case_type_id = civicrm_case_type.id ";
539 case 'case_activity_type':
540 $from .= " $side JOIN civicrm_option_group option_group_activity_type ON (option_group_activity_type.name = 'activity_type')";
541 $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 ) ";
544 case 'recent_activity_status':
545 $from .= " $side JOIN civicrm_option_group option_group_activity_status ON (option_group_activity_status.name = 'activity_status')";
546 $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 ) ";
549 case 'case_relationship':
550 $session = CRM_Core_Session
::singleton();
551 $userID = $session->get('userID');
552 $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 )";
555 case 'case_relation_type':
556 $from .= " $side JOIN civicrm_relationship_type case_relation_type ON ( case_relation_type.id = case_relationship.relationship_type_id AND
557 case_relation_type.id = case_relationship.relationship_type_id )";
560 case 'case_activity_medium':
561 $from .= " $side JOIN civicrm_option_group option_group_activity_medium ON (option_group_activity_medium.name = 'encounter_medium')";
562 $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 ) ";
565 case 'case_activity':
566 $from .= " INNER JOIN civicrm_case_activity ON civicrm_case_activity.case_id = civicrm_case.id ";
567 $from .= " INNER JOIN civicrm_activity case_activity ON ( civicrm_case_activity.activity_id = case_activity.id
568 AND case_activity.is_current_revision = 1 )";
571 case 'civicrm_case_tag':
572 $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 ) ";
579 * Getter for the qill object.
583 public function qill() {
584 return (isset($this->_qill
)) ?
$this->_qill
: "";
589 * @param bool $includeCustomFields
593 public static function defaultReturnProperties(
595 $includeCustomFields = TRUE
600 if ($mode & CRM_Contact_BAO_Query
::MODE_CASE
) {
603 'contact_sub_type' => 1,
608 'case_activity_subject' => 1,
614 'case_recent_activity_date' => 1,
615 'case_recent_activity_type' => 1,
616 'case_scheduled_activity_date' => 1,
618 // 'case_scheduled_activity_type'=> 1
621 if ($includeCustomFields) {
622 // also get all the custom case properties
623 $fields = CRM_Core_BAO_CustomField
::getFieldsForImport('Case');
624 if (!empty($fields)) {
625 foreach ($fields as $name => $dontCare) {
626 $properties[$name] = 1;
636 * This includes any extra fields that might need for export etc.
638 * @param string $mode
642 public static function extraReturnProperties($mode) {
645 if ($mode & CRM_Contact_BAO_Query
::MODE_CASE
) {
647 'case_start_date' => 1,
648 'case_end_date' => 1,
650 'case_source_contact_id' => 1,
651 'case_activity_status' => 1,
652 'case_activity_duration' => 1,
653 'case_activity_medium_id' => 1,
654 'case_activity_details' => 1,
655 'case_activity_is_auto' => 1,
664 public static function tableNames(&$tables) {
665 if (!empty($tables['civicrm_case'])) {
666 $tables = array_merge(['civicrm_case_contact' => 1], $tables);
669 if (!empty($tables['case_relation_type'])) {
670 $tables = array_merge(['case_relationship' => 1], $tables);
675 * Add all the elements shared between case search and advanced search.
677 * @param CRM_Core_Form $form
679 public static function buildSearchForm(&$form) {
680 //validate case configuration.
681 $configured = CRM_Case_BAO_Case
::isCaseConfigured();
682 $form->assign('notConfigured', !$configured['configured']);
684 $form->addField('case_type_id', ['context' => 'search', 'entity' => 'Case']);
685 $form->addField('case_status_id', ['context' => 'search', 'entity' => 'Case']);
687 CRM_Core_Form_Date
::buildDateRange($form, 'case_from', 1, '_start_date_low', '_start_date_high', ts('From'), FALSE);
688 CRM_Core_Form_Date
::buildDateRange($form, 'case_to', 1, '_end_date_low', '_end_date_high', ts('From'), FALSE);
689 $form->addElement('hidden', 'case_from_start_date_range_error');
690 $form->addElement('hidden', 'case_to_end_date_range_error');
691 $form->addFormRule(['CRM_Case_BAO_Query', 'formRule'], $form);
693 $form->assign('validCiviCase', TRUE);
695 //give options when all cases are accessible.
696 $accessAllCases = FALSE;
697 if (CRM_Core_Permission
::check('access all cases and activities')) {
698 $accessAllCases = TRUE;
699 $caseOwner = [1 => ts('Search All Cases'), 2 => ts('Only My Cases')];
700 $form->addRadio('case_owner', ts('Cases'), $caseOwner);
701 if ($form->get('context') != 'dashboard') {
702 $form->add('checkbox', 'upcoming', ts('Search Cases with Upcoming Activities'));
705 $form->assign('accessAllCases', $accessAllCases);
707 $caseTags = CRM_Core_BAO_Tag
::getColorTags('civicrm_case');
710 $form->add('select2', 'case_tags', ts('Case Tag(s)'), $caseTags, FALSE, ['class' => 'big', 'placeholder' => ts('- select -'), 'multiple' => TRUE]);
713 $parentNames = CRM_Core_BAO_Tag
::getTagSet('civicrm_case');
714 CRM_Core_Form_Tag
::buildQuickForm($form, $parentNames, 'civicrm_case', NULL, TRUE, FALSE);
716 if (CRM_Core_Permission
::check('administer CiviCase')) {
717 $form->addElement('checkbox', 'case_deleted', ts('Deleted Cases'));
720 $form->addElement('text',
725 $form->addElement('text',
730 self
::addCustomFormFields($form, ['Case']);
732 $form->setDefaults(['case_owner' => 1]);
738 * @param array $fields
739 * @param array $files
740 * @param CRM_Core_Form $form
744 public static function formRule($fields, $files, $form) {
747 if ((empty($fields['case_from_start_date_low']) ||
empty($fields['case_from_start_date_high'])) && (empty($fields['case_to_end_date_low']) ||
empty($fields['case_to_end_date_high']))) {
751 CRM_Utils_Rule
::validDateRange($fields, 'case_from_start_date', $errors, ts('Case Start Date'));
752 CRM_Utils_Rule
::validDateRange($fields, 'case_to_end_date', $errors, ts('Case End Date'));
754 return empty($errors) ?
TRUE : $errors;