Commit | Line | Data |
---|---|---|
6a488035 | 1 | <?php |
6a488035 TO |
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 TO |
16 | */ |
17 | class CRM_Report_Form_Case_Demographics extends CRM_Report_Form { | |
18 | ||
19 | protected $_summary = NULL; | |
20 | ||
21 | protected $_emailField = FALSE; | |
22 | ||
23 | protected $_phoneField = FALSE; | |
74cf4551 | 24 | /** |
1728e9a0 | 25 | * This report has not been optimised for group filtering. |
26 | * | |
27 | * The functionality for group filtering has been improved but not | |
28 | * all reports have been adjusted to take care of it. This report has not | |
29 | * and will run an inefficient query until fixed. | |
30 | * | |
1728e9a0 | 31 | * @var bool |
0e480632 | 32 | * @see https://issues.civicrm.org/jira/browse/CRM-19170 |
74cf4551 | 33 | */ |
1728e9a0 | 34 | protected $groupFilterNotOptimised = TRUE; |
35 | ||
74cf4551 | 36 | /** |
1728e9a0 | 37 | * Class constructor. |
74cf4551 | 38 | */ |
00be9182 | 39 | public function __construct() { |
be2fb01f CW |
40 | $this->_columns = [ |
41 | 'civicrm_contact' => [ | |
6a488035 | 42 | 'dao' => 'CRM_Contact_DAO_Contact', |
be2fb01f CW |
43 | 'fields' => [ |
44 | 'sort_name' => [ | |
9d72cede | 45 | 'title' => ts('Contact Name'), |
6a488035 TO |
46 | 'required' => TRUE, |
47 | 'no_repeat' => TRUE, | |
be2fb01f CW |
48 | ], |
49 | 'gender_id' => [ | |
9d72cede | 50 | 'title' => ts('Gender'), |
6a488035 | 51 | 'default' => TRUE, |
be2fb01f CW |
52 | ], |
53 | 'birth_date' => [ | |
9d72cede | 54 | 'title' => ts('Birthdate'), |
6a488035 | 55 | 'default' => FALSE, |
be2fb01f CW |
56 | ], |
57 | 'id' => [ | |
6a488035 TO |
58 | 'no_display' => TRUE, |
59 | 'required' => TRUE, | |
be2fb01f CW |
60 | ], |
61 | ], | |
62 | 'filters' => [ | |
63 | 'sort_name' => [ | |
54071e11 EM |
64 | 'title' => ts('Contact Name'), |
65 | 'operatorType' => CRM_Report_Form::OP_STRING, | |
be2fb01f CW |
66 | ], |
67 | 'contact_type' => [ | |
9d72cede | 68 | 'title' => ts('Contact Type'), |
6a488035 | 69 | 'operatorType' => CRM_Report_Form::OP_SELECT, |
be2fb01f | 70 | 'options' => [ |
9d72cede | 71 | '' => ts('-select-'), |
6a488035 TO |
72 | 'Individual' => ts('Individual'), |
73 | 'Organization' => ts('Organization'), | |
74 | 'Household' => ts('Household'), | |
be2fb01f | 75 | ], |
6a488035 | 76 | 'default' => 'Individual', |
be2fb01f CW |
77 | ], |
78 | 'id' => [ | |
9d72cede | 79 | 'title' => ts('Contact ID'), |
6a488035 | 80 | 'no_display' => TRUE, |
be2fb01f CW |
81 | ], |
82 | ], | |
83 | 'order_bys' => [ | |
84 | 'sort_name' => [ | |
c79b3487 BS |
85 | 'title' => ts('Contact Name'), |
86 | 'default_weight' => '1', | |
87 | 'dbAlias' => 'civicrm_contact_sort_name', | |
be2fb01f CW |
88 | ], |
89 | ], | |
6a488035 | 90 | 'grouping' => 'contact-fields', |
be2fb01f CW |
91 | ], |
92 | 'civicrm_email' => [ | |
6a488035 | 93 | 'dao' => 'CRM_Core_DAO_Email', |
be2fb01f CW |
94 | 'fields' => [ |
95 | 'email' => [ | |
9d72cede | 96 | 'title' => ts('Email'), |
6a488035 | 97 | 'no_repeat' => TRUE, |
be2fb01f CW |
98 | ], |
99 | ], | |
6a488035 | 100 | 'grouping' => 'contact-fields', |
be2fb01f CW |
101 | ], |
102 | 'civicrm_address' => [ | |
6a488035 TO |
103 | 'dao' => 'CRM_Core_DAO_Address', |
104 | 'grouping' => 'contact-fields', | |
be2fb01f CW |
105 | 'fields' => [ |
106 | 'street_address' => ['default' => FALSE], | |
107 | 'city' => ['default' => TRUE], | |
6a488035 | 108 | 'postal_code' => NULL, |
be2fb01f | 109 | 'state_province_id' => [ |
9d72cede | 110 | 'title' => ts('State/Province'), |
be2fb01f CW |
111 | ], |
112 | 'country_id' => [ | |
9d72cede | 113 | 'title' => ts('Country'), |
6a488035 | 114 | 'default' => FALSE, |
be2fb01f CW |
115 | ], |
116 | ], | |
6a488035 | 117 | /* |
9d72cede EM |
118 | 'filters' => array( |
119 | 'country_id' => array( 'title' => ts( 'Country' ), | |
6a488035 TO |
120 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
121 | 'options' => CRM_Core_PseudoConstant::country( ), | |
2f4c2f5d | 122 | ), |
9d72cede | 123 | 'state_province_id' => array( 'title' => ts( 'State/Province' ), |
6a488035 | 124 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
2f4c2f5d | 125 | 'options' => CRM_Core_PseudoConstant::stateProvince( ), ), |
126 | ), | |
e70a7fc0 | 127 | */ |
be2fb01f CW |
128 | ], |
129 | 'civicrm_phone' => [ | |
6a488035 | 130 | 'dao' => 'CRM_Core_DAO_Phone', |
be2fb01f | 131 | 'fields' => ['phone' => NULL], |
6a488035 | 132 | 'grouping' => 'contact-fields', |
be2fb01f CW |
133 | ], |
134 | 'civicrm_activity' => [ | |
6a488035 | 135 | 'dao' => 'CRM_Activity_DAO_Activity', |
be2fb01f CW |
136 | 'fields' => [ |
137 | 'id' => [ | |
9d72cede | 138 | 'title' => ts('Activity ID'), |
6a488035 TO |
139 | 'no_display' => TRUE, |
140 | 'required' => TRUE, | |
be2fb01f CW |
141 | ], |
142 | ], | |
143 | ], | |
144 | 'civicrm_case' => [ | |
6a488035 | 145 | 'dao' => 'CRM_Case_DAO_Case', |
be2fb01f CW |
146 | 'fields' => [ |
147 | 'id' => [ | |
9d72cede | 148 | 'title' => ts('Case ID'), |
6a488035 | 149 | 'required' => TRUE, |
be2fb01f CW |
150 | ], |
151 | 'start_date' => [ | |
9d72cede | 152 | 'title' => ts('Case Start'), |
6a488035 | 153 | 'required' => TRUE, |
be2fb01f CW |
154 | ], |
155 | 'end_date' => [ | |
9d72cede | 156 | 'title' => ts('Case End'), |
6a488035 | 157 | 'required' => TRUE, |
be2fb01f CW |
158 | ], |
159 | ], | |
160 | 'filters' => [ | |
161 | 'case_id_filter' => [ | |
9d72cede | 162 | 'name' => 'id', |
6a488035 TO |
163 | 'title' => ts('Cases?'), |
164 | 'operatorType' => CRM_Report_Form::OP_SELECT, | |
be2fb01f | 165 | 'options' => [ |
9d72cede EM |
166 | 1 => ts('Exclude non-case'), |
167 | 2 => ts('Exclude cases'), | |
21dfd5f5 | 168 | 3 => ts('Include Both'), |
be2fb01f | 169 | ], |
6a488035 | 170 | 'default' => 3, |
be2fb01f CW |
171 | ], |
172 | 'start_date' => [ | |
9d72cede | 173 | 'title' => ts('Case Start'), |
6a488035 | 174 | 'operatorType' => CRM_Report_Form::OP_DATE, |
be2fb01f CW |
175 | ], |
176 | 'end_date' => [ | |
9d72cede | 177 | 'title' => ts('Case End'), |
6a488035 | 178 | 'operatorType' => CRM_Report_Form::OP_DATE, |
be2fb01f CW |
179 | ], |
180 | ], | |
181 | 'order_bys' => [ | |
182 | 'id' => [ | |
c79b3487 BS |
183 | 'title' => ts('Case ID'), |
184 | 'default_weight' => '2', | |
185 | 'dbAlias' => 'civicrm_case_id', | |
be2fb01f CW |
186 | ], |
187 | ], | |
188 | ], | |
189 | ]; | |
6a488035 | 190 | |
16e2e80c | 191 | $this->_groupFilter = TRUE; |
6a488035 TO |
192 | $this->_tagFilter = TRUE; |
193 | ||
d66c61b6 | 194 | $open_case_val = CRM_Core_PseudoConstant::getKey('CRM_Activity_BAO_Activity', 'activity_type_id', 'Open Case'); |
6a488035 TO |
195 | $crmDAO = &CRM_Core_DAO::executeQuery("SELECT cg.table_name, cg.extends AS ext, cf.label, cf.column_name FROM civicrm_custom_group cg INNER JOIN civicrm_custom_field cf ON cg.id = cf.custom_group_id |
196 | where (cg.extends='Contact' OR cg.extends='Individual' OR cg.extends_entity_column_value='$open_case_val') AND cg.is_active=1 AND cf.is_active=1 ORDER BY cg.table_name"); | |
9d72cede EM |
197 | $curTable = ''; |
198 | $curExt = ''; | |
be2fb01f | 199 | $curFields = []; |
6a488035 TO |
200 | while ($crmDAO->fetch()) { |
201 | if ($curTable == '') { | |
202 | $curTable = $crmDAO->table_name; | |
203 | $curExt = $crmDAO->ext; | |
204 | } | |
205 | elseif ($curTable != $crmDAO->table_name) { | |
206 | // dummy DAO | |
be2fb01f | 207 | $this->_columns[$curTable] = [ |
6a488035 TO |
208 | 'dao' => 'CRM_Contact_DAO_Contact', |
209 | 'fields' => $curFields, | |
210 | 'ext' => $curExt, | |
be2fb01f | 211 | ]; |
9d72cede EM |
212 | $curTable = $crmDAO->table_name; |
213 | $curExt = $crmDAO->ext; | |
be2fb01f | 214 | $curFields = []; |
6a488035 TO |
215 | } |
216 | ||
be2fb01f | 217 | $curFields[$crmDAO->column_name] = ['title' => $crmDAO->label]; |
6a488035 TO |
218 | } |
219 | if (!empty($curFields)) { | |
220 | // dummy DAO | |
be2fb01f | 221 | $this->_columns[$curTable] = [ |
6a488035 TO |
222 | 'dao' => 'CRM_Contact_DAO_Contact', |
223 | 'fields' => $curFields, | |
224 | 'ext' => $curExt, | |
be2fb01f | 225 | ]; |
6a488035 TO |
226 | } |
227 | ||
6a488035 TO |
228 | parent::__construct(); |
229 | } | |
230 | ||
00be9182 | 231 | public function preProcess() { |
6a488035 TO |
232 | parent::preProcess(); |
233 | } | |
234 | ||
00be9182 | 235 | public function select() { |
be2fb01f CW |
236 | $select = []; |
237 | $this->_columnHeaders = []; | |
6a488035 TO |
238 | foreach ($this->_columns as $tableName => $table) { |
239 | if (array_key_exists('fields', $table)) { | |
240 | foreach ($table['fields'] as $fieldName => $field) { | |
9d72cede EM |
241 | if (!empty($field['required']) || |
242 | !empty($this->_params['fields'][$fieldName]) | |
243 | ) { | |
6a488035 TO |
244 | if ($tableName == 'civicrm_email') { |
245 | $this->_emailField = TRUE; | |
246 | } | |
247 | elseif ($tableName == 'civicrm_phone') { | |
248 | $this->_phoneField = TRUE; | |
249 | } | |
250 | ||
251 | $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}"; | |
9c1bc317 | 252 | $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = $field['type'] ?? NULL; |
6a488035 TO |
253 | $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title']; |
254 | } | |
255 | } | |
256 | } | |
257 | } | |
d1641c51 | 258 | $this->_selectClauses = $select; |
6a488035 TO |
259 | |
260 | $this->_select = "SELECT " . implode(', ', $select) . " "; | |
261 | } | |
262 | ||
74cf4551 EM |
263 | /** |
264 | * @param $fields | |
265 | * @param $files | |
e8cf95b4 | 266 | * @param self $self |
74cf4551 EM |
267 | * |
268 | * @return array | |
269 | */ | |
00be9182 | 270 | public static function formRule($fields, $files, $self) { |
be2fb01f | 271 | $errors = $grouping = []; |
6a488035 TO |
272 | return $errors; |
273 | } | |
274 | ||
00be9182 | 275 | public function from() { |
6a488035 TO |
276 | $this->_from = " |
277 | FROM civicrm_contact {$this->_aliases['civicrm_contact']} | |
2f4c2f5d | 278 | LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']} |
279 | ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_address']}.contact_id AND | |
6a488035 TO |
280 | {$this->_aliases['civicrm_address']}.is_primary = 1 ) |
281 | LEFT JOIN civicrm_case_contact ccc ON ccc.contact_id = {$this->_aliases['civicrm_contact']}.id | |
282 | LEFT JOIN civicrm_case {$this->_aliases['civicrm_case']} ON {$this->_aliases['civicrm_case']}.id = ccc.case_id | |
283 | LEFT JOIN civicrm_case_activity cca ON cca.case_id = {$this->_aliases['civicrm_case']}.id | |
2f4c2f5d | 284 | LEFT JOIN civicrm_activity {$this->_aliases['civicrm_activity']} ON {$this->_aliases['civicrm_activity']}.id = cca.activity_id |
6a488035 TO |
285 | "; |
286 | ||
287 | foreach ($this->_columns as $t => $c) { | |
9d72cede EM |
288 | if (substr($t, 0, 13) == 'civicrm_value' || |
289 | substr($t, 0, 12) == 'custom_value' | |
290 | ) { | |
6a488035 | 291 | $this->_from .= " LEFT JOIN $t {$this->_aliases[$t]} ON {$this->_aliases[$t]}.entity_id = "; |
9d72cede EM |
292 | $this->_from .= ($c['ext'] == |
293 | 'Activity') ? "{$this->_aliases['civicrm_activity']}.id" : "{$this->_aliases['civicrm_contact']}.id"; | |
6a488035 TO |
294 | } |
295 | } | |
296 | ||
6d81d64a | 297 | $this->joinPhoneFromContact(); |
298 | $this->joinEmailFromContact(); | |
6a488035 | 299 | |
6a488035 TO |
300 | } |
301 | ||
00be9182 | 302 | public function where() { |
be2fb01f | 303 | $clauses = []; |
6a488035 TO |
304 | $this->_having = ''; |
305 | foreach ($this->_columns as $tableName => $table) { | |
306 | if (array_key_exists('filters', $table)) { | |
307 | foreach ($table['filters'] as $fieldName => $field) { | |
308 | $clause = NULL; | |
309 | if ($field['operatorType'] & CRM_Report_Form::OP_DATE) { | |
9c1bc317 CW |
310 | $relative = $this->_params["{$fieldName}_relative"] ?? NULL; |
311 | $from = $this->_params["{$fieldName}_from"] ?? NULL; | |
312 | $to = $this->_params["{$fieldName}_to"] ?? NULL; | |
6a488035 | 313 | |
54071e11 | 314 | $clause = $this->dateClause($field['dbAlias'], $relative, $from, $to, CRM_Utils_Type::T_DATE); |
6a488035 TO |
315 | } |
316 | else { | |
9c1bc317 | 317 | $op = $this->_params["{$fieldName}_op"] ?? NULL; |
6a488035 TO |
318 | if ($op) { |
319 | // handle special case | |
320 | if ($fieldName == 'case_id_filter') { | |
9c1bc317 | 321 | $choice = $this->_params["{$fieldName}_value"] ?? NULL; |
6a488035 TO |
322 | if ($choice == 1) { |
323 | $clause = "({$this->_aliases['civicrm_case']}.id Is Not Null)"; | |
324 | } | |
325 | elseif ($choice == 2) { | |
326 | $clause = "({$this->_aliases['civicrm_case']}.id Is Null)"; | |
327 | } | |
328 | } | |
329 | else { | |
330 | $clause = $this->whereClause($field, | |
331 | $op, | |
332 | CRM_Utils_Array::value("{$fieldName}_value", $this->_params), | |
333 | CRM_Utils_Array::value("{$fieldName}_min", $this->_params), | |
334 | CRM_Utils_Array::value("{$fieldName}_max", $this->_params) | |
335 | ); | |
336 | } | |
337 | } | |
338 | } | |
339 | ||
340 | if (!empty($clause)) { | |
341 | $clauses[] = $clause; | |
342 | } | |
343 | } | |
344 | } | |
345 | } | |
346 | ||
347 | $clauses[] = "(({$this->_aliases['civicrm_case']}.is_deleted = 0) OR ({$this->_aliases['civicrm_case']}.is_deleted Is Null))"; | |
348 | $clauses[] = "(({$this->_aliases['civicrm_activity']}.is_deleted = 0) OR ({$this->_aliases['civicrm_activity']}.is_deleted Is Null))"; | |
349 | $clauses[] = "(({$this->_aliases['civicrm_activity']}.is_current_revision = 1) OR ({$this->_aliases['civicrm_activity']}.is_deleted Is Null))"; | |
350 | ||
351 | $this->_where = "WHERE " . implode(' AND ', $clauses); | |
352 | } | |
353 | ||
00be9182 | 354 | public function groupBy() { |
be2fb01f | 355 | $groupBy = ["{$this->_aliases['civicrm_contact']}.id", "{$this->_aliases['civicrm_case']}.id"]; |
b708c08d | 356 | $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy); |
6a488035 TO |
357 | } |
358 | ||
00be9182 | 359 | public function postProcess() { |
6a488035 TO |
360 | |
361 | $this->beginPostProcess(); | |
362 | ||
363 | $sql = $this->buildQuery(TRUE); | |
be2fb01f | 364 | $rows = $graphRows = []; |
6a488035 TO |
365 | $this->buildRows($sql, $rows); |
366 | ||
367 | $this->formatDisplay($rows); | |
368 | $this->doTemplateAssignment($rows); | |
369 | $this->endPostProcess($rows); | |
370 | } | |
371 | ||
74cf4551 | 372 | /** |
4b62bc4f EM |
373 | * Alter display of rows. |
374 | * | |
375 | * Iterate through the rows retrieved via SQL and make changes for display purposes, | |
376 | * such as rendering contacts as links. | |
377 | * | |
378 | * @param array $rows | |
379 | * Rows generated by SQL, with an array for each row. | |
74cf4551 | 380 | */ |
00be9182 | 381 | public function alterDisplay(&$rows) { |
6a488035 TO |
382 | $entryFound = FALSE; |
383 | foreach ($rows as $rowNum => $row) { | |
384 | // make count columns point to detail report | |
385 | // convert display name to links | |
386 | if (array_key_exists('civicrm_contact_sort_name', $row) && | |
387 | array_key_exists('civicrm_contact_id', $row) | |
388 | ) { | |
389 | $url = CRM_Utils_System::url('civicrm/contact/view', | |
390 | 'reset=1&cid=' . $row['civicrm_contact_id'], | |
391 | $this->_absoluteUrl | |
392 | ); | |
393 | $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url; | |
394 | $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact details for this contact."); | |
395 | $entryFound = TRUE; | |
396 | } | |
397 | ||
6a488035 TO |
398 | // handle custom fields |
399 | foreach ($row as $k => $r) { | |
9d72cede EM |
400 | if (substr($k, 0, 13) == 'civicrm_value' || |
401 | substr($k, 0, 12) == 'custom_value' | |
402 | ) { | |
6a488035 TO |
403 | if ($r || $r == '0') { |
404 | if ($newval = $this->getCustomFieldLabel($k, $r)) { | |
405 | $rows[$rowNum][$k] = $newval; | |
406 | } | |
407 | } | |
408 | $entryFound = TRUE; | |
409 | } | |
410 | } | |
411 | ||
0e742891 | 412 | $entryFound = $this->alterDisplayContactFields($row, $rows, $rowNum, NULL, NULL) ? TRUE : $entryFound; |
e3665bb8 | 413 | $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, NULL, NULL) ? TRUE : $entryFound; |
414 | ||
6a488035 TO |
415 | // skip looking further in rows, if first row itself doesn't |
416 | // have the column we need | |
417 | if (!$entryFound) { | |
418 | break; | |
419 | } | |
420 | } | |
421 | } | |
422 | ||
74cf4551 | 423 | /** |
100fef9d | 424 | * @param string $fname |
74cf4551 EM |
425 | * @param $val |
426 | * | |
427 | * @return null|string | |
428 | */ | |
00be9182 | 429 | public function getCustomFieldLabel($fname, $val) { |
6a488035 TO |
430 | $query = " |
431 | SELECT v.label | |
432 | FROM civicrm_custom_group cg INNER JOIN civicrm_custom_field cf ON cg.id = cf.custom_group_id | |
433 | INNER JOIN civicrm_option_group g ON cf.option_group_id = g.id | |
434 | INNER JOIN civicrm_option_value v ON g.id = v.option_group_id | |
435 | WHERE CONCAT(cg.table_name, '_', cf.column_name) = %1 AND v.value = %2"; | |
be2fb01f CW |
436 | $params = [ |
437 | 1 => [$fname, 'String'], | |
438 | 2 => [$val, 'String'], | |
439 | ]; | |
6a488035 TO |
440 | return CRM_Core_DAO::singleValueQuery($query, $params); |
441 | } | |
96025800 | 442 | |
6a488035 | 443 | } |