Commit | Line | Data |
---|---|---|
6a488035 TO |
1 | <?php |
2 | /* | |
3 | +--------------------------------------------------------------------+ | |
bc77d7c0 | 4 | | Copyright CiviCRM LLC. All rights reserved. | |
6a488035 | 5 | | | |
bc77d7c0 TO |
6 | | This work is published under the GNU AGPLv3 license with some | |
7 | | permitted exceptions and without any warranty. For full license | | |
8 | | and copyright information, see https://civicrm.org/licensing | | |
6a488035 | 9 | +--------------------------------------------------------------------+ |
d25dd0ee | 10 | */ |
6a488035 TO |
11 | |
12 | /** | |
13 | * | |
14 | * @package CRM | |
ca5cec67 | 15 | * @copyright CiviCRM LLC https://civicrm.org/licensing |
6a488035 | 16 | */ |
86a0d21e | 17 | class CRM_Case_BAO_Query extends CRM_Core_BAO_Query { |
6a488035 | 18 | |
4c6ce474 | 19 | /** |
3819f101 | 20 | * Get fields. |
21 | * | |
4c6ce474 EM |
22 | * @param bool $excludeActivityFields |
23 | * | |
24 | * @return array | |
25 | */ | |
00be9182 | 26 | public static function &getFields($excludeActivityFields = FALSE) { |
6a488035 TO |
27 | $fields = CRM_Case_BAO_Case::exportableFields(); |
28 | ||
29 | // add activity related fields | |
1cb28d5d | 30 | if (!$excludeActivityFields) { |
31 | $fields = array_merge($fields, CRM_Activity_BAO_Activity::exportableFields('Case')); | |
32 | } | |
6a488035 TO |
33 | |
34 | return $fields; | |
35 | } | |
36 | ||
37 | /** | |
fe482240 | 38 | * Build select for Case. |
6a488035 | 39 | * |
d76e5b23 | 40 | * @param CRM_Contact_BAO_Query $query |
6a488035 | 41 | */ |
00be9182 | 42 | public static function select(&$query) { |
8cc574cf | 43 | if (($query->_mode & CRM_Contact_BAO_Query::MODE_CASE) || !empty($query->_returnProperties['case_id'])) { |
6a488035 TO |
44 | $query->_select['case_id'] = "civicrm_case.id as case_id"; |
45 | $query->_element['case_id'] = 1; | |
46 | $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1; | |
47 | $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1; | |
48 | } | |
49 | ||
a7488080 | 50 | if (!empty($query->_returnProperties['case_type_id'])) { |
8ffdec17 | 51 | $query->_select['case_type_id'] = "civicrm_case_type.id as case_type_id"; |
6a488035 TO |
52 | $query->_element['case_type_id'] = 1; |
53 | $query->_tables['case_type'] = $query->_whereTables['case_type'] = 1; | |
54 | $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1; | |
55 | } | |
56 | ||
e2751e44 | 57 | if (!empty($query->_returnProperties['case_type'])) { |
8ffdec17 | 58 | $query->_select['case_type'] = "civicrm_case_type.title as case_type"; |
6a488035 | 59 | $query->_element['case_type'] = 1; |
e2751e44 | 60 | $query->_tables['case_type'] = $query->_whereTables['case_type'] = 1; |
6a488035 TO |
61 | $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1; |
62 | } | |
63 | ||
a7488080 | 64 | if (!empty($query->_returnProperties['case_start_date'])) { |
6a488035 TO |
65 | $query->_select['case_start_date'] = "civicrm_case.start_date as case_start_date"; |
66 | $query->_element['case_start_date'] = 1; | |
67 | $query->_tables['civicrm_case'] = 1; | |
68 | } | |
69 | ||
a7488080 | 70 | if (!empty($query->_returnProperties['case_end_date'])) { |
6a488035 TO |
71 | $query->_select['case_end_date'] = "civicrm_case.end_date as case_end_date"; |
72 | $query->_element['case_end_date'] = 1; | |
73 | $query->_tables['civicrm_case'] = 1; | |
74 | } | |
75 | ||
a7488080 | 76 | if (!empty($query->_returnProperties['case_status_id'])) { |
6a488035 TO |
77 | $query->_select['case_status_id'] = "case_status.id as case_status_id"; |
78 | $query->_element['case_status_id'] = 1; | |
79 | $query->_tables['case_status_id'] = $query->_whereTables['case_status_id'] = 1; | |
80 | $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1; | |
81 | } | |
82 | ||
a7488080 | 83 | if (!empty($query->_returnProperties['case_status'])) { |
6a488035 TO |
84 | $query->_select['case_status'] = "case_status.label as case_status"; |
85 | $query->_element['case_status'] = 1; | |
86 | $query->_tables['case_status_id'] = $query->_whereTables['case_status_id'] = 1; | |
87 | $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1; | |
88 | } | |
89 | ||
a7488080 | 90 | if (!empty($query->_returnProperties['case_deleted'])) { |
6a488035 TO |
91 | $query->_select['case_deleted'] = "civicrm_case.is_deleted as case_deleted"; |
92 | $query->_element['case_deleted'] = 1; | |
93 | $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1; | |
94 | } | |
95 | ||
a7488080 | 96 | if (!empty($query->_returnProperties['case_role'])) { |
41cf58d3 | 97 | $query->_select['case_role'] = "IF(case_relationship.contact_id_b = contact_a.id, case_relation_type.label_b_a, case_relation_type.label_a_b) as case_role"; |
6a488035 TO |
98 | $query->_element['case_role'] = 1; |
99 | $query->_tables['case_relationship'] = $query->_whereTables['case_relationship'] = 1; | |
100 | $query->_tables['case_relation_type'] = $query->_whereTables['case_relation_type'] = 1; | |
101 | } | |
102 | ||
f046623b | 103 | if (!empty($query->_returnProperties['case_activity_date_time'])) { |
104 | $query->_select['case_activity_date_time'] = "case_activity.activity_date_time as case_activity_date_time"; | |
105 | $query->_element['case_activity_date_time'] = 1; | |
6a488035 TO |
106 | $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1; |
107 | } | |
108 | ||
a7488080 | 109 | if (!empty($query->_returnProperties['case_activity_subject'])) { |
6a488035 TO |
110 | $query->_select['case_activity_subject'] = "case_activity.subject as case_activity_subject"; |
111 | $query->_element['case_activity_subject'] = 1; | |
112 | $query->_tables['case_activity'] = 1; | |
113 | $query->_tables['civicrm_case_contact'] = 1; | |
114 | $query->_tables['civicrm_case'] = 1; | |
115 | } | |
116 | ||
a7488080 | 117 | if (!empty($query->_returnProperties['case_subject'])) { |
6a488035 TO |
118 | $query->_select['case_subject'] = "civicrm_case.subject as case_subject"; |
119 | $query->_element['case_subject'] = 1; | |
120 | $query->_tables['civicrm_case_contact'] = 1; | |
121 | $query->_tables['civicrm_case'] = 1; | |
122 | } | |
123 | ||
cc81d063 | 124 | // @todo switch to a more standard case_source_contact as the key where we want the name not the id. |
a7488080 | 125 | if (!empty($query->_returnProperties['case_source_contact_id'])) { |
6a488035 TO |
126 | $query->_select['case_source_contact_id'] = "civicrm_case_reporter.sort_name as case_source_contact_id"; |
127 | $query->_element['case_source_contact_id'] = 1; | |
128 | $query->_tables['civicrm_case_reporter'] = 1; | |
129 | $query->_tables['case_activity'] = 1; | |
130 | $query->_tables['civicrm_case_contact'] = 1; | |
131 | $query->_tables['civicrm_case'] = 1; | |
132 | } | |
133 | ||
a7488080 | 134 | if (!empty($query->_returnProperties['case_activity_status_id'])) { |
6a488035 TO |
135 | $query->_select['case_activity_status_id'] = "rec_activity_status.id as case_activity_status_id"; |
136 | $query->_element['case_activity_status_id'] = 1; | |
137 | $query->_tables['case_activity'] = 1; | |
f046623b | 138 | $query->_tables['case_activity_status'] = 1; |
6a488035 TO |
139 | $query->_tables['civicrm_case_contact'] = 1; |
140 | $query->_tables['civicrm_case'] = 1; | |
141 | } | |
142 | ||
a7488080 | 143 | if (!empty($query->_returnProperties['case_activity_status'])) { |
6a488035 TO |
144 | $query->_select['case_activity_status'] = "rec_activity_status.label as case_activity_status"; |
145 | $query->_element['case_activity_status'] = 1; | |
146 | $query->_tables['case_activity'] = 1; | |
f046623b | 147 | $query->_tables['case_activity_status'] = 1; |
6a488035 TO |
148 | $query->_tables['civicrm_case_contact'] = 1; |
149 | $query->_tables['civicrm_case'] = 1; | |
150 | } | |
151 | ||
a7488080 | 152 | if (!empty($query->_returnProperties['case_activity_duration'])) { |
6a488035 TO |
153 | $query->_select['case_activity_duration'] = "case_activity.duration as case_activity_duration"; |
154 | $query->_element['case_activity_duration'] = 1; | |
155 | $query->_tables['case_activity'] = 1; | |
156 | $query->_tables['civicrm_case_contact'] = 1; | |
157 | $query->_tables['civicrm_case'] = 1; | |
158 | } | |
159 | ||
a7488080 | 160 | if (!empty($query->_returnProperties['case_activity_medium_id'])) { |
f046623b | 161 | $query->_select['case_activity_medium_id'] = "case_activity_medium.label as case_activity_medium_id"; |
6a488035 TO |
162 | $query->_element['case_activity_medium_id'] = 1; |
163 | $query->_tables['case_activity'] = 1; | |
164 | $query->_tables['case_activity_medium'] = 1; | |
165 | $query->_tables['civicrm_case_contact'] = 1; | |
166 | $query->_tables['civicrm_case'] = 1; | |
167 | } | |
168 | ||
a7488080 | 169 | if (!empty($query->_returnProperties['case_activity_details'])) { |
6a488035 TO |
170 | $query->_select['case_activity_details'] = "case_activity.details as case_activity_details"; |
171 | $query->_element['case_activity_details'] = 1; | |
172 | $query->_tables['case_activity'] = 1; | |
173 | $query->_tables['civicrm_case_contact'] = 1; | |
174 | $query->_tables['civicrm_case'] = 1; | |
175 | } | |
176 | ||
a7488080 | 177 | if (!empty($query->_returnProperties['case_activity_is_auto'])) { |
6a488035 TO |
178 | $query->_select['case_activity_is_auto'] = "case_activity.is_auto as case_activity_is_auto"; |
179 | $query->_element['case_activity_is_auto'] = 1; | |
180 | $query->_tables['case_activity'] = 1; | |
181 | $query->_tables['civicrm_case_contact'] = 1; | |
182 | $query->_tables['civicrm_case'] = 1; | |
183 | } | |
184 | ||
f046623b | 185 | if (!empty($query->_returnProperties['case_activity_date_time'])) { |
186 | $query->_select['case_activity_date_time'] = "case_activity.activity_date_time as case_activity_date_time"; | |
187 | $query->_element['case_activity_date_time'] = 1; | |
6a488035 TO |
188 | $query->_tables['case_activity'] = 1; |
189 | $query->_tables['civicrm_case_contact'] = 1; | |
190 | $query->_tables['civicrm_case'] = 1; | |
191 | } | |
f046623b | 192 | if (!empty($query->_returnProperties['case_activity_type'])) { |
193 | $query->_select['case_activity_type'] = "rec_activity_type.label as case_activity_type"; | |
194 | $query->_element['case_activity_type'] = 1; | |
6a488035 TO |
195 | $query->_tables['case_activity'] = 1; |
196 | $query->_tables['case_activity_type'] = 1; | |
197 | $query->_tables['civicrm_case_contact'] = 1; | |
198 | $query->_tables['civicrm_case'] = 1; | |
199 | } | |
200 | } | |
201 | ||
202 | /** | |
3819f101 | 203 | * Given a list of conditions in query generate the required where clause. |
77b97be7 | 204 | * |
d76e5b23 | 205 | * @param CRM_Contact_BAO_Query $query |
6a488035 | 206 | */ |
00be9182 | 207 | public static function where(&$query) { |
6a488035 TO |
208 | foreach ($query->_params as $id => $values) { |
209 | if (!is_array($values) || count($values) != 5) { | |
210 | continue; | |
211 | } | |
212 | ||
213 | if (substr($query->_params[$id][0], 0, 5) == 'case_') { | |
214 | if ($query->_mode == CRM_Contact_BAO_Query::MODE_CONTACTS) { | |
215 | $query->_useDistinct = TRUE; | |
216 | } | |
6a488035 TO |
217 | self::whereClauseSingle($query->_params[$id], $query); |
218 | } | |
219 | } | |
6c051493 | 220 | // Add acl clause |
96454cad CW |
221 | // This is new and so far only for cases - it would be good to find a more abstract |
222 | // way to auto-apply this for all search components rather than copy-pasting this code to others | |
223 | if (isset($query->_tables['civicrm_case'])) { | |
224 | $aclClauses = array_filter(CRM_Case_BAO_Case::getSelectWhereClause()); | |
225 | foreach ($aclClauses as $clause) { | |
226 | $query->_where[0][] = $clause; | |
227 | } | |
6c051493 | 228 | } |
6a488035 TO |
229 | } |
230 | ||
231 | /** | |
fe482240 | 232 | * Where clause for a single field. |
6a488035 | 233 | * |
96dc1ce0 | 234 | * CRM-17120 adds a test that checks the Qill on some of these parameters. |
235 | * However, I couldn't find a way, other than via test, to access the | |
236 | * case_activity options in the code below and invalid sql was returned. | |
237 | * Perhaps the options are just legacy? | |
238 | * | |
239 | * Also, CRM-17120 locks in the Qill - but it probably is not quite right as I | |
240 | * see 'Activity Type = Scheduled' (rather than activity status). | |
241 | * | |
242 | * See CRM_Case_BAO_QueryTest for more. | |
243 | * | |
3819f101 | 244 | * @param array $values |
d76e5b23 | 245 | * @param CRM_Contact_BAO_Query $query |
bf993266 | 246 | * |
247 | * @throws \CRM_Core_Exception | |
6a488035 | 248 | */ |
00be9182 | 249 | public static function whereClauseSingle(&$values, &$query) { |
7d832c0c SL |
250 | if ($query->buildDateRangeQuery($values)) { |
251 | // @todo - move this to Contact_Query in or near the call to | |
252 | // $this->buildRelativeDateQuery($values); | |
253 | return; | |
254 | } | |
6a488035 | 255 | list($name, $op, $value, $grouping, $wildcard) = $values; |
11ac6a2b SL |
256 | $fields = CRM_Case_BAO_Case::fields(); |
257 | $fieldSpec = $fields[$values[0]] ?? []; | |
be2fb01f | 258 | $val = $names = []; |
6a488035 | 259 | switch ($name) { |
6a488035 TO |
260 | |
261 | case 'case_type_id': | |
8ae90f85 | 262 | case 'case_type': |
37653fa0 | 263 | case 'case_status': |
264 | case 'case_status_id': | |
265 | case 'case_id': | |
6a488035 | 266 | |
37653fa0 | 267 | if (strpos($name, 'type')) { |
268 | $name = 'case_type_id'; | |
269 | $label = 'Case Type(s)'; | |
6a488035 | 270 | } |
37653fa0 | 271 | elseif (strpos($name, 'status')) { |
272 | $name = 'status_id'; | |
273 | $label = 'Case Status(s)'; | |
8ae90f85 | 274 | } |
275 | else { | |
37653fa0 | 276 | $name = 'id'; |
277 | $label = 'Case ID'; | |
8ae90f85 | 278 | } |
6a488035 | 279 | |
37653fa0 | 280 | $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_case.{$name}", $op, $value, "Integer"); |
1a7029b8 | 281 | $query->_qill[$grouping][] = CRM_Contact_BAO_Query::getQillValue('CRM_Case_DAO_Case', $name, $value, $op, $label); |
6a488035 TO |
282 | $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1; |
283 | return; | |
284 | ||
285 | case 'case_owner': | |
286 | case 'case_mycases': | |
287 | if (!empty($value)) { | |
288 | if ($value == 2) { | |
289 | $session = CRM_Core_Session::singleton(); | |
290 | $userID = $session->get('userID'); | |
41cf58d3 | 291 | $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') . ' ) OR ( ' . CRM_Contact_BAO_Query::buildClause("case_relationship.contact_id_a", $op, $userID, 'Int') . ' AND ' . CRM_Contact_BAO_Query::buildClause("case_relationship.is_active", '<>', 0, 'Int') . ' ))'; |
be2fb01f | 292 | $query->_qill[$grouping][] = ts('Case %1 My Cases', [1 => $op]); |
6a488035 TO |
293 | $query->_tables['case_relationship'] = $query->_whereTables['case_relationship'] = 1; |
294 | } | |
295 | elseif ($value == 1) { | |
be2fb01f | 296 | $query->_qill[$grouping][] = ts('Case %1 All Cases', [1 => $op]); |
6a488035 TO |
297 | $query->_where[$grouping][] = "civicrm_case_contact.contact_id = contact_a.id"; |
298 | } | |
299 | $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1; | |
300 | $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1; | |
301 | } | |
302 | return; | |
303 | ||
304 | case 'case_deleted': | |
305 | $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_case.is_deleted", $op, $value, 'Boolean'); | |
306 | if ($value) { | |
307 | $query->_qill[$grouping][] = ts("Find Deleted Cases"); | |
308 | } | |
309 | $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1; | |
310 | return; | |
311 | ||
312 | case 'case_activity_subject': | |
313 | $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.subject", $op, $value, 'String'); | |
be2fb01f | 314 | $query->_qill[$grouping][] = ts("Activity Subject %1 '%2'", [1 => $op, 2 => $value]); |
6a488035 TO |
315 | $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1; |
316 | $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1; | |
317 | $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1; | |
318 | return; | |
319 | ||
320 | case 'case_subject': | |
11ac6a2b | 321 | $query->handleWhereFromMetadata($fieldSpec, $name, $value, $op); |
6a488035 TO |
322 | return; |
323 | ||
cc81d063 | 324 | // @todo switch to a more standard case_source_contact as the key where we want the name not the id. |
6a488035 TO |
325 | case 'case_source_contact_id': |
326 | $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_case_reporter.sort_name", $op, $value, 'String'); | |
be2fb01f | 327 | $query->_qill[$grouping][] = ts("Activity Reporter %1 '%2'", [1 => $op, 2 => $value]); |
6a488035 TO |
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 | ||
f046623b | 334 | case 'case_activity_date_time': |
6a488035 TO |
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); | |
f046623b | 339 | $query->_qill[$grouping][] = ts("Activity Date %1 %2", [1 => $op, 2 => $date]); |
6a488035 TO |
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 | ||
f046623b | 346 | case 'case_activity_type': |
6a488035 | 347 | $names = $value; |
96dc1ce0 | 348 | if (($activityType = CRM_Core_PseudoConstant::getLabel('CRM_Activity_BAO_Activity', 'activity_type_id', $value)) != FALSE) { |
6a488035 TO |
349 | $names = $activityType; |
350 | } | |
351 | ||
352 | $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.activity_type_id", $op, $value, 'Int'); | |
be2fb01f | 353 | $query->_qill[$grouping][] = ts("Activity Type %1 %2", [1 => $op, 2 => $names]); |
6a488035 TO |
354 | $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1; |
355 | $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1; | |
356 | $query->_tables['case_activity_type'] = 1; | |
357 | $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1; | |
358 | return; | |
359 | ||
360 | case 'case_activity_status_id': | |
361 | $names = $value; | |
96dc1ce0 | 362 | if (($activityStatus = CRM_Core_PseudoConstant::getLabel('CRM_Activity_BAO_Activity', 'status_id', $value)) != FALSE) { |
6a488035 TO |
363 | $names = $activityStatus; |
364 | } | |
365 | ||
366 | $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.status_id", $op, $value, 'Int'); | |
d13ce5f9 | 367 | $query->_qill[$grouping][] = ts("Activity Status %1 %2", [1 => $op, 2 => $names]); |
6a488035 TO |
368 | $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1; |
369 | $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1; | |
370 | $query->_tables['case_activity_status'] = 1; | |
371 | $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1; | |
372 | return; | |
373 | ||
374 | case 'case_activity_duration': | |
375 | $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.duration", $op, $value, 'Int'); | |
be2fb01f | 376 | $query->_qill[$grouping][] = ts("Activity Duration %1 %2", [1 => $op, 2 => $value]); |
6a488035 TO |
377 | $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1; |
378 | $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1; | |
379 | $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1; | |
380 | return; | |
381 | ||
382 | case 'case_activity_medium_id': | |
383 | $names = $value; | |
96dc1ce0 | 384 | if (($activityMedium = CRM_Core_PseudoConstant::getLabel('CRM_Activity_BAO_Activity', 'medium_id', $value)) != FALSE) { |
6a488035 TO |
385 | $names = $activityMedium; |
386 | } | |
387 | ||
388 | $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.medium_id", $op, $value, 'Int'); | |
be2fb01f | 389 | $query->_qill[$grouping][] = ts("Activity Medium %1 %2", [1 => $op, 2 => $names]); |
6a488035 TO |
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_medium'] = 1; | |
393 | $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1; | |
394 | return; | |
395 | ||
396 | case 'case_activity_details': | |
397 | $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.details", $op, $value, 'String'); | |
be2fb01f | 398 | $query->_qill[$grouping][] = ts("Activity Details %1 '%2'", [1 => $op, 2 => $value]); |
6a488035 TO |
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; | |
402 | return; | |
403 | ||
404 | case 'case_activity_is_auto': | |
405 | $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("case_activity.is_auto", $op, $value, 'Boolean'); | |
1292fe02 | 406 | $query->_qill[$grouping][] = ts("Activity Auto Generated %1 '%2'", [1 => $op, 2 => $value]); |
6a488035 TO |
407 | $query->_tables['case_activity'] = $query->_whereTables['case_activity'] = 1; |
408 | $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1; | |
409 | $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1; | |
410 | return; | |
411 | ||
412 | // adding where clause for case_role | |
413 | ||
414 | case 'case_role': | |
be2fb01f | 415 | $query->_qill[$grouping][] = ts("Role in Case %1 '%2'", [1 => $op, 2 => $value]); |
6a488035 TO |
416 | $query->_tables['case_relation_type'] = $query->_whereTables['case_relationship_type'] = 1; |
417 | $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1; | |
418 | $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1; | |
419 | return; | |
420 | ||
6a488035 TO |
421 | case 'case_start_date': |
422 | $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_case.start_date", $op, $value, 'Int'); | |
423 | $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1; | |
424 | return; | |
425 | ||
426 | case 'case_end_date': | |
427 | $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("civicrm_case.end_date", $op, $value, 'Int'); | |
428 | $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1; | |
429 | return; | |
430 | ||
431 | case 'case_taglist': | |
432 | $taglist = $value; | |
be2fb01f | 433 | $value = []; |
6a488035 TO |
434 | foreach ($taglist as $val) { |
435 | if ($val) { | |
436 | $val = explode(',', $val); | |
437 | foreach ($val as $tId) { | |
438 | if (is_numeric($tId)) { | |
439 | $value[$tId] = 1; | |
440 | } | |
441 | } | |
442 | } | |
443 | } | |
444 | case 'case_tags': | |
be2fb01f | 445 | $tags = CRM_Core_PseudoConstant::get('CRM_Core_DAO_EntityTag', 'tag_id', ['onlyActive' => FALSE]); |
6a488035 | 446 | |
dd33678f | 447 | if (!empty($value)) { |
0e80e389 | 448 | if (is_array($value)) { |
449 | // Search tag(s) are part of a tag set | |
450 | $val = array_keys($value); | |
451 | } | |
452 | else { | |
453 | // Search tag(s) are part of the tag tree | |
454 | $val = explode(',', $value); | |
455 | } | |
dd33678f | 456 | foreach ($val as $v) { |
6a488035 | 457 | if ($v) { |
dd33678f | 458 | $names[] = $tags[$v]; |
6a488035 TO |
459 | } |
460 | } | |
461 | } | |
462 | ||
463 | $query->_where[$grouping][] = " civicrm_case_tag.tag_id IN (" . implode(',', $val) . " )"; | |
be2fb01f | 464 | $query->_qill[$grouping][] = ts('Case Tags %1', [1 => $op]) . ' ' . implode(' ' . ts('or') . ' ', $names); |
6a488035 TO |
465 | $query->_tables['civicrm_case'] = $query->_whereTables['civicrm_case'] = 1; |
466 | $query->_tables['civicrm_case_contact'] = $query->_whereTables['civicrm_case_contact'] = 1; | |
467 | $query->_tables['civicrm_case_tag'] = $query->_whereTables['civicrm_case_tag'] = 1; | |
468 | return; | |
469 | } | |
470 | } | |
471 | ||
4c6ce474 | 472 | /** |
d76e5b23 | 473 | * Build from clause. |
474 | * | |
100fef9d | 475 | * @param string $name |
d76e5b23 | 476 | * @param string $mode |
477 | * @param string $side | |
4c6ce474 EM |
478 | * |
479 | * @return string | |
480 | */ | |
00be9182 | 481 | public static function from($name, $mode, $side) { |
6a488035 TO |
482 | $from = ""; |
483 | ||
484 | switch ($name) { | |
485 | case 'civicrm_case_contact': | |
486 | $from .= " $side JOIN civicrm_case_contact ON civicrm_case_contact.contact_id = contact_a.id "; | |
487 | break; | |
488 | ||
489 | case 'civicrm_case_reporter': | |
44f817d4 | 490 | $activityContacts = CRM_Activity_BAO_ActivityContact::buildOptions('record_type_id', 'validate'); |
962c0eda | 491 | $sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts); |
492 | $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} ) "; | |
09a5df84 | 493 | $from .= " $side JOIN civicrm_contact as civicrm_case_reporter ON case_activity_contact.contact_id = civicrm_case_reporter.id "; |
6a488035 TO |
494 | break; |
495 | ||
496 | case 'civicrm_case': | |
497 | $from .= " INNER JOIN civicrm_case ON civicrm_case_contact.case_id = civicrm_case.id"; | |
498 | break; | |
499 | ||
500 | case 'case_status_id': | |
501 | $from .= " $side JOIN civicrm_option_group option_group_case_status ON (option_group_case_status.name = 'case_status')"; | |
502 | $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 ) "; | |
503 | break; | |
504 | ||
505 | case 'case_type': | |
8ffdec17 | 506 | $from .= " $side JOIN civicrm_case_type ON civicrm_case.case_type_id = civicrm_case_type.id "; |
6a488035 TO |
507 | break; |
508 | ||
509 | case 'case_activity_type': | |
510 | $from .= " $side JOIN civicrm_option_group option_group_activity_type ON (option_group_activity_type.name = 'activity_type')"; | |
511 | $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 ) "; | |
512 | break; | |
513 | ||
f046623b | 514 | case 'case_activity_status': |
6a488035 TO |
515 | $from .= " $side JOIN civicrm_option_group option_group_activity_status ON (option_group_activity_status.name = 'activity_status')"; |
516 | $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 ) "; | |
517 | break; | |
518 | ||
519 | case 'case_relationship': | |
520 | $session = CRM_Core_Session::singleton(); | |
353ffa53 | 521 | $userID = $session->get('userID'); |
41cf58d3 | 522 | $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 OR case_relationship.contact_id_b = civicrm_case_contact.contact_id AND case_relationship.contact_id_a = {$userID} AND case_relationship.case_id = civicrm_case.id )"; |
6a488035 TO |
523 | break; |
524 | ||
525 | case 'case_relation_type': | |
526 | $from .= " $side JOIN civicrm_relationship_type case_relation_type ON ( case_relation_type.id = case_relationship.relationship_type_id AND | |
527 | case_relation_type.id = case_relationship.relationship_type_id )"; | |
528 | break; | |
529 | ||
530 | case 'case_activity_medium': | |
531 | $from .= " $side JOIN civicrm_option_group option_group_activity_medium ON (option_group_activity_medium.name = 'encounter_medium')"; | |
f046623b | 532 | $from .= " $side JOIN civicrm_option_value case_activity_medium ON (case_activity.medium_id = case_activity_medium.value AND option_group_activity_medium.id = case_activity_medium.option_group_id ) "; |
6a488035 TO |
533 | break; |
534 | ||
535 | case 'case_activity': | |
536 | $from .= " INNER JOIN civicrm_case_activity ON civicrm_case_activity.case_id = civicrm_case.id "; | |
537 | $from .= " INNER JOIN civicrm_activity case_activity ON ( civicrm_case_activity.activity_id = case_activity.id | |
538 | AND case_activity.is_current_revision = 1 )"; | |
539 | break; | |
540 | ||
541 | case 'civicrm_case_tag': | |
542 | $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 ) "; | |
543 | break; | |
544 | } | |
545 | return $from; | |
546 | } | |
547 | ||
548 | /** | |
fe482240 | 549 | * Getter for the qill object. |
6a488035 TO |
550 | * |
551 | * @return string | |
6a488035 | 552 | */ |
00be9182 | 553 | public function qill() { |
6a488035 TO |
554 | return (isset($this->_qill)) ? $this->_qill : ""; |
555 | } | |
556 | ||
4c6ce474 EM |
557 | /** |
558 | * @param $mode | |
559 | * @param bool $includeCustomFields | |
560 | * | |
561 | * @return array|null | |
562 | */ | |
e7483cbe | 563 | public static function defaultReturnProperties( |
28d4d481 | 564 | $mode, |
6a488035 TO |
565 | $includeCustomFields = TRUE |
566 | ) { | |
567 | ||
568 | $properties = NULL; | |
569 | ||
570 | if ($mode & CRM_Contact_BAO_Query::MODE_CASE) { | |
be2fb01f | 571 | $properties = [ |
6a488035 TO |
572 | 'contact_type' => 1, |
573 | 'contact_sub_type' => 1, | |
574 | 'contact_id' => 1, | |
575 | 'sort_name' => 1, | |
576 | 'display_name' => 1, | |
577 | 'case_id' => 1, | |
578 | 'case_activity_subject' => 1, | |
579 | 'case_subject' => 1, | |
580 | 'case_status' => 1, | |
581 | 'case_type' => 1, | |
582 | 'case_role' => 1, | |
583 | 'case_deleted' => 1, | |
f046623b | 584 | 'case_activity_date_time' => 1, |
585 | 'case_activity_type' => 1, | |
6a488035 | 586 | 'phone' => 1, |
be2fb01f | 587 | ]; |
6a488035 TO |
588 | |
589 | if ($includeCustomFields) { | |
590 | // also get all the custom case properties | |
591 | $fields = CRM_Core_BAO_CustomField::getFieldsForImport('Case'); | |
592 | if (!empty($fields)) { | |
593 | foreach ($fields as $name => $dontCare) { | |
594 | $properties[$name] = 1; | |
595 | } | |
596 | } | |
597 | } | |
598 | } | |
599 | ||
600 | return $properties; | |
601 | } | |
602 | ||
603 | /** | |
fe482240 | 604 | * This includes any extra fields that might need for export etc. |
ad37ac8e | 605 | * |
606 | * @param string $mode | |
607 | * | |
608 | * @return array|null | |
6a488035 | 609 | */ |
00be9182 | 610 | public static function extraReturnProperties($mode) { |
6a488035 TO |
611 | $properties = NULL; |
612 | ||
613 | if ($mode & CRM_Contact_BAO_Query::MODE_CASE) { | |
be2fb01f | 614 | $properties = [ |
6a488035 TO |
615 | 'case_start_date' => 1, |
616 | 'case_end_date' => 1, | |
617 | 'case_subject' => 1, | |
cc81d063 | 618 | // @todo switch to a more standard case_source_contact as the key where we want the name not the id. |
6a488035 TO |
619 | 'case_source_contact_id' => 1, |
620 | 'case_activity_status' => 1, | |
621 | 'case_activity_duration' => 1, | |
622 | 'case_activity_medium_id' => 1, | |
623 | 'case_activity_details' => 1, | |
624 | 'case_activity_is_auto' => 1, | |
be2fb01f | 625 | ]; |
6a488035 TO |
626 | } |
627 | return $properties; | |
628 | } | |
629 | ||
4c6ce474 EM |
630 | /** |
631 | * @param $tables | |
632 | */ | |
00be9182 | 633 | public static function tableNames(&$tables) { |
a7488080 | 634 | if (!empty($tables['civicrm_case'])) { |
be2fb01f | 635 | $tables = array_merge(['civicrm_case_contact' => 1], $tables); |
6a488035 TO |
636 | } |
637 | ||
a7488080 | 638 | if (!empty($tables['case_relation_type'])) { |
be2fb01f | 639 | $tables = array_merge(['case_relationship' => 1], $tables); |
6a488035 TO |
640 | } |
641 | } | |
642 | ||
b62aa188 MD |
643 | /** |
644 | * Get the metadata for fields to be included on the case search form. | |
645 | * | |
646 | * @todo ideally this would be a trait included on the case search & advanced search | |
647 | * rather than a static function. | |
648 | */ | |
649 | public static function getSearchFieldMetadata() { | |
650 | $fields = ['case_type_id', 'case_status_id', 'case_start_date', 'case_end_date', 'case_subject', 'case_id', 'case_deleted']; | |
651 | $metadata = civicrm_api3('Case', 'getfields', [])['values']; | |
020457ad | 652 | $metadata['case_id'] = $metadata['id']; |
b62aa188 MD |
653 | $metadata = array_intersect_key($metadata, array_flip($fields)); |
654 | $metadata['case_tags'] = [ | |
655 | 'title' => ts('Case Tag(s)'), | |
656 | 'type' => CRM_Utils_Type::T_INT, | |
657 | 'is_pseudofield' => TRUE, | |
774c73d9 | 658 | 'html' => ['type' => 'Select2'], |
b62aa188 MD |
659 | ]; |
660 | if (CRM_Core_Permission::check('access all cases and activities')) { | |
661 | $metadata['case_owner'] = [ | |
662 | 'title' => ts('Cases'), | |
663 | 'type' => CRM_Utils_Type::T_INT, | |
664 | 'is_pseudofield' => TRUE, | |
774c73d9 | 665 | 'html' => ['type' => 'Radio'], |
b62aa188 MD |
666 | ]; |
667 | } | |
668 | if (!CRM_Core_Permission::check('administer CiviCase')) { | |
669 | unset($metadata['case_deleted']); | |
670 | } | |
671 | return $metadata; | |
672 | } | |
673 | ||
6a488035 | 674 | /** |
3819f101 | 675 | * Add all the elements shared between case search and advanced search. |
6a488035 | 676 | * |
bf993266 | 677 | * @param CRM_Case_Form_Search $form |
6a488035 | 678 | */ |
3c8c56f0 EM |
679 | public static function buildSearchForm(&$form): void { |
680 | $form->addOptionalQuickFormElement('upcoming'); | |
6a488035 TO |
681 | //validate case configuration. |
682 | $configured = CRM_Case_BAO_Case::isCaseConfigured(); | |
683 | $form->assign('notConfigured', !$configured['configured']); | |
684 | ||
b62aa188 MD |
685 | $form->addSearchFieldMetadata(['Case' => self::getSearchFieldMetadata()]); |
686 | $form->addFormFieldsFromMetadata(); | |
6a488035 | 687 | |
6a488035 TO |
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; | |
be2fb01f | 694 | $caseOwner = [1 => ts('Search All Cases'), 2 => ts('Only My Cases')]; |
6a488035 | 695 | $form->addRadio('case_owner', ts('Cases'), $caseOwner); |
5f1c8c57 | 696 | if ($form->get('context') != 'dashboard') { |
697 | $form->add('checkbox', 'upcoming', ts('Search Cases with Upcoming Activities')); | |
698 | } | |
6a488035 TO |
699 | } |
700 | $form->assign('accessAllCases', $accessAllCases); | |
701 | ||
dd33678f | 702 | $caseTags = CRM_Core_BAO_Tag::getColorTags('civicrm_case'); |
6a488035 TO |
703 | |
704 | if ($caseTags) { | |
be2fb01f | 705 | $form->add('select2', 'case_tags', ts('Case Tag(s)'), $caseTags, FALSE, ['class' => 'big', 'placeholder' => ts('- select -'), 'multiple' => TRUE]); |
6a488035 TO |
706 | } |
707 | ||
708 | $parentNames = CRM_Core_BAO_Tag::getTagSet('civicrm_case'); | |
4dff5e17 | 709 | CRM_Core_Form_Tag::buildQuickForm($form, $parentNames, 'civicrm_case', NULL, TRUE, FALSE); |
6a488035 | 710 | |
be2fb01f | 711 | self::addCustomFormFields($form, ['Case']); |
6a488035 | 712 | |
be2fb01f | 713 | $form->setDefaults(['case_owner' => 1]); |
e547f744 | 714 | } |
96025800 | 715 | |
6a488035 | 716 | } |