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_Campaign_SurveyDetails extends CRM_Report_Form { | |
18 | ||
19 | protected $_emailField = FALSE; | |
20 | ||
21 | protected $_phoneField = FALSE; | |
22 | ||
23 | protected $_locationBasedPhoneField = FALSE; | |
24 | ||
25 | protected $_summary = NULL; | |
26 | protected $_customGroupGroupBy = FALSE; | |
9d72cede EM |
27 | protected $_customGroupExtends = array( |
28 | 'Contact', | |
29 | 'Individual', | |
30 | 'Household', | |
31 | 'Organization', | |
21dfd5f5 | 32 | 'Activity', |
9d72cede | 33 | ); |
6a488035 TO |
34 | public $_drilldownReport = array('contact/detail' => 'Link to Detail Report'); |
35 | ||
cba8728a | 36 | private static $_surveyRespondentStatus; |
6a488035 | 37 | |
b2a674e6 | 38 | // Survey Question titles are overridden when in print or pdf mode to |
c86d4e7c SL |
39 | /** |
40 | * say Q1, Q2 instead of the full title - to save space. | |
41 | * @var array | |
42 | */ | |
affcc9d2 | 43 | private $_columnTitleOverrides = []; |
b2a674e6 | 44 | |
74cf4551 | 45 | /** |
74cf4551 | 46 | */ |
c86d4e7c | 47 | |
74cf4551 | 48 | /** |
74cf4551 | 49 | */ |
00be9182 | 50 | public function __construct() { |
6a488035 | 51 | //filter options for survey activity status. |
fbaf3c7d | 52 | $responseStatus = array('' => ts('- Any -')); |
affcc9d2 | 53 | self::$_surveyRespondentStatus = []; |
6a488035 TO |
54 | $activityStatus = CRM_Core_PseudoConstant::activityStatus('name'); |
55 | if ($statusId = array_search('Scheduled', $activityStatus)) { | |
56 | $responseStatus[$statusId] = ts('Reserved'); | |
57 | self::$_surveyRespondentStatus[$statusId] = 'Reserved'; | |
58 | } | |
59 | if ($statusId = array_search('Completed', $activityStatus)) { | |
60 | $responseStatus[$statusId] = ts('Interviewed'); | |
61 | self::$_surveyRespondentStatus[$statusId] = 'Interviewed'; | |
62 | } | |
63 | ||
9d72cede | 64 | $optionGroups = CRM_Campaign_BAO_Survey::getResultSets('name'); |
affcc9d2 | 65 | $resultOptions = []; |
9d72cede EM |
66 | foreach ($optionGroups as $gid => $name) { |
67 | if ($name) { | |
affcc9d2 | 68 | $value = []; |
6a488035 | 69 | $value = CRM_Core_OptionGroup::values($name); |
9d72cede | 70 | if (!empty($value)) { |
6a488035 | 71 | $value = array_combine($value, $value); |
9d72cede EM |
72 | } |
73 | $resultOptions = $resultOptions + $value; | |
6a488035 TO |
74 | } |
75 | } | |
76 | asort($resultOptions); | |
77 | ||
78 | //get all interviewers. | |
79 | $allSurveyInterviewers = CRM_Campaign_BAO_Survey::getInterviewers(); | |
80 | ||
81 | $this->_columns = array( | |
9d72cede | 82 | 'civicrm_activity_contact' => array( |
4e3d3cfc | 83 | 'dao' => 'CRM_Activity_DAO_ActivityContact', |
84 | 'fields' => array('contact_id' => array('title' => ts('Interviewer Name'))), | |
6a488035 | 85 | 'filters' => array( |
9d72cede EM |
86 | 'contact_id' => array( |
87 | 'name' => 'contact_id', | |
6a488035 TO |
88 | 'title' => ts('Interviewer Name'), |
89 | 'type' => CRM_Utils_Type::T_INT, | |
9d72cede | 90 | 'operatorType' => CRM_Report_Form::OP_SELECT, |
6a488035 | 91 | 'options' => array( |
3bdca100 | 92 | '' => ts('- any interviewer -'), |
93 | ) + $allSurveyInterviewers, | |
21dfd5f5 | 94 | ), |
9d72cede | 95 | ), |
6a488035 TO |
96 | 'grouping' => 'survey-interviewer-fields', |
97 | ), | |
9d72cede | 98 | 'civicrm_contact' => array( |
6a488035 | 99 | 'dao' => 'CRM_Contact_DAO_Contact', |
9d72cede EM |
100 | 'fields' => array( |
101 | 'id' => array( | |
102 | 'title' => ts('Contact ID'), | |
6a488035 TO |
103 | 'no_display' => TRUE, |
104 | 'required' => TRUE, | |
105 | ), | |
9d72cede EM |
106 | 'sort_name' => array( |
107 | 'title' => ts('Respondent Name'), | |
6a488035 TO |
108 | 'required' => TRUE, |
109 | 'no_repeat' => TRUE, | |
110 | ), | |
111 | ), | |
9d72cede EM |
112 | 'filters' => array( |
113 | 'sort_name' => array( | |
114 | 'title' => ts('Respondent Name'), | |
6a488035 | 115 | 'operator' => 'like', |
21dfd5f5 | 116 | ), |
9d72cede | 117 | ), |
6a488035 | 118 | 'grouping' => 'contact-fields', |
9d72cede EM |
119 | 'order_bys' => array( |
120 | 'sort_name' => array( | |
121 | 'title' => ts('Respondent Name'), | |
6a488035 | 122 | 'required' => TRUE, |
21dfd5f5 | 123 | ), |
9d72cede | 124 | ), |
6a488035 | 125 | ), |
9d72cede | 126 | 'civicrm_phone' => array( |
6a488035 TO |
127 | 'dao' => 'CRM_Core_DAO_Phone', |
128 | 'fields' => array( | |
9d72cede EM |
129 | 'phone' => array( |
130 | 'name' => 'phone', | |
6a488035 | 131 | 'title' => ts('Phone'), |
21dfd5f5 | 132 | ), |
9d72cede | 133 | ), |
6a488035 TO |
134 | 'grouping' => 'location-fields', |
135 | ), | |
9d72cede | 136 | 'civicrm_email' => array( |
6a488035 TO |
137 | 'dao' => 'CRM_Core_DAO_Email', |
138 | 'fields' => array( | |
9d72cede EM |
139 | 'email' => array( |
140 | 'name' => 'email', | |
6a488035 | 141 | 'title' => ts('Email'), |
21dfd5f5 | 142 | ), |
9d72cede | 143 | ), |
6a488035 TO |
144 | 'grouping' => 'location-fields', |
145 | ), | |
10a6a659 | 146 | ) + $this->getAddressColumns() + |
ee8628be JM |
147 | array( |
148 | 'civicrm_activity' => array( | |
6a488035 TO |
149 | 'dao' => 'CRM_Activity_DAO_Activity', |
150 | 'alias' => 'survey_activity', | |
151 | 'fields' => array( | |
9d72cede EM |
152 | 'survey_id' => array( |
153 | 'name' => 'source_record_id', | |
6a488035 TO |
154 | 'title' => ts('Survey'), |
155 | 'type' => CRM_Utils_Type::T_INT, | |
9d72cede EM |
156 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
157 | 'options' => CRM_Campaign_BAO_Survey::getSurveys(), | |
6a488035 TO |
158 | ), |
159 | 'survey_response' => array( | |
160 | 'name' => 'survey_response', | |
161 | 'title' => ts('Survey Responses'), | |
162 | ), | |
163 | 'details' => array( | |
164 | 'name' => 'details', | |
165 | 'title' => ts('Note'), | |
166 | 'type' => 1, | |
167 | ), | |
168 | 'result' => array( | |
169 | 'name' => 'result', | |
170 | 'required' => TRUE, | |
171 | 'title' => ts('Survey Result'), | |
172 | ), | |
9d2dd2e3 | 173 | 'activity_date_time' => array( |
174 | 'name' => 'activity_date_time', | |
175 | 'title' => ts('Date'), | |
f049f621 | 176 | 'type' => CRM_Utils_Type::T_DATE + CRM_Utils_Type::T_TIME, |
9d2dd2e3 | 177 | ), |
6a488035 TO |
178 | ), |
179 | 'filters' => array( | |
9d72cede EM |
180 | 'survey_id' => array( |
181 | 'name' => 'source_record_id', | |
6a488035 TO |
182 | 'title' => ts('Survey'), |
183 | 'type' => CRM_Utils_Type::T_INT, | |
184 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
9d72cede | 185 | 'options' => CRM_Campaign_BAO_Survey::getSurveys(), |
6a488035 TO |
186 | ), |
187 | 'status_id' => array( | |
188 | 'name' => 'status_id', | |
189 | 'title' => ts('Respondent Status'), | |
190 | 'type' => CRM_Utils_Type::T_INT, | |
191 | 'operatorType' => CRM_Report_Form::OP_SELECT, | |
192 | 'options' => $responseStatus, | |
193 | ), | |
9d72cede EM |
194 | 'result' => array( |
195 | 'title' => ts('Survey Result'), | |
196 | 'type' => CRM_Utils_Type::T_STRING, | |
197 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
198 | 'options' => $resultOptions, | |
199 | ), | |
9d2dd2e3 | 200 | 'activity_date_time' => array( |
201 | 'title' => ts('Date'), | |
f049f621 | 202 | 'type' => CRM_Utils_Type::T_DATE + CRM_Utils_Type::T_TIME, |
9d2dd2e3 | 203 | 'operatorType' => CRM_Report_Form::OP_DATE, |
204 | ), | |
6a488035 TO |
205 | ), |
206 | 'grouping' => 'survey-activity-fields', | |
9d2dd2e3 | 207 | 'order_bys' => array( |
208 | 'activity_date_time' => array( | |
209 | 'title' => ts('Date'), | |
210 | ), | |
211 | ), | |
ee8628be | 212 | ), |
10a6a659 | 213 | ); |
6a488035 TO |
214 | parent::__construct(); |
215 | } | |
216 | ||
00be9182 | 217 | public function preProcess() { |
6a488035 TO |
218 | parent::preProcess(); |
219 | } | |
f813f78e | 220 | |
00be9182 | 221 | public function select() { |
affcc9d2 | 222 | $select = []; |
f813f78e | 223 | |
6a488035 TO |
224 | //add the survey response fields. |
225 | $this->_addSurveyResponseColumns(); | |
f813f78e | 226 | |
affcc9d2 | 227 | $this->_columnHeaders = []; |
6a488035 TO |
228 | foreach ($this->_columns as $tableName => $table) { |
229 | if (!isset($table['fields'])) { | |
230 | continue; | |
231 | } | |
232 | foreach ($table['fields'] as $fieldName => $field) { | |
9d72cede EM |
233 | if (!empty($field['required']) || |
234 | !empty($this->_params['fields'][$fieldName]) || | |
b99f3e96 | 235 | !empty($field['is_required']) |
6a488035 TO |
236 | ) { |
237 | ||
238 | $fieldsName = CRM_Utils_Array::value(1, explode('_', $tableName)); | |
239 | if ($fieldsName) { | |
240 | $this->{"_$fieldsName" . 'Field'} = TRUE; | |
241 | } | |
242 | ||
243 | //need to pickup custom data/survey response fields. | |
244 | if ($fieldName == 'survey_response') { | |
245 | continue; | |
246 | } | |
247 | ||
248 | $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}"; | |
b2a674e6 JM |
249 | |
250 | // Set default title | |
9c1bc317 | 251 | $title = $field['title'] ?? NULL; |
b2a674e6 JM |
252 | // Check for an override. |
253 | if (!empty($this->_columnTitleOverrides["{$tableName}_{$fieldName}"])) { | |
254 | $title = $this->_columnTitleOverrides["{$tableName}_{$fieldName}"]; | |
255 | } | |
256 | $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $title; | |
257 | ||
81272d80 | 258 | $this->_selectAliases[] = "{$tableName}_{$fieldName}"; |
6a488035 TO |
259 | } |
260 | } | |
261 | } | |
262 | ||
263 | $this->_select = "SELECT " . implode(",\n", $select) . " "; | |
264 | } | |
265 | ||
00be9182 | 266 | public function from() { |
6a488035 | 267 | $this->_from = " FROM civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom} "; |
44f817d4 | 268 | $activityContacts = CRM_Activity_BAO_ActivityContact::buildOptions('record_type_id', 'validate'); |
9e74e3ce | 269 | $assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts); |
270 | $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts); | |
6a488035 TO |
271 | |
272 | //get the activity table joins. | |
f813f78e | 273 | $this->_from .= " INNER JOIN civicrm_activity_contact civicrm_activity_target ON |
9e74e3ce | 274 | ( {$this->_aliases['civicrm_contact']}.id = civicrm_activity_target.contact_id AND civicrm_activity_target.record_type_id = {$targetID}) \n"; |
f813f78e | 275 | $this->_from .= " INNER JOIN civicrm_activity {$this->_aliases['civicrm_activity']} ON |
9e74e3ce | 276 | ( {$this->_aliases['civicrm_activity']}.id = civicrm_activity_target.activity_id )\n"; |
a418f518 KW |
277 | $this->_from .= " INNER JOIN civicrm_activity_contact activity_contact_civireport ON |
278 | ( {$this->_aliases['civicrm_activity']}.id = activity_contact_civireport.activity_id AND activity_contact_civireport.record_type_id = {$assigneeID} )\n"; | |
6a488035 | 279 | |
6d81d64a | 280 | $this->joinAddressFromContact(); |
281 | $this->joinPhoneFromContact(); | |
282 | $this->joinEmailFromContact(); | |
6a488035 | 283 | |
9d72cede EM |
284 | if ($this->_locationBasedPhoneField) { |
285 | foreach ($this->_surveyResponseFields as $key => $value) { | |
286 | if (substr($key, 0, 5) == 'phone' && !empty($value['location_type_id']) | |
287 | ) { | |
288 | $fName = str_replace('-', '_', $key); | |
289 | $this->_from .= "LEFT JOIN civicrm_phone " . | |
290 | $this->_aliases["civicrm_phone_{$fName}"] . | |
291 | " ON {$this->_aliases['civicrm_contact']}.id = " . | |
292 | $this->_aliases["civicrm_phone_{$fName}"] . ".contact_id AND " . | |
293 | $this->_aliases["civicrm_phone_{$fName}"] . | |
294 | ".location_type_id = {$value['location_type_id']} AND " . | |
295 | $this->_aliases["civicrm_phone_{$fName}"] . | |
296 | ".phone_type_id = {$value['phone_type_id']}\n"; | |
6a488035 TO |
297 | } |
298 | } | |
299 | } | |
300 | } | |
f813f78e | 301 | |
00be9182 | 302 | public function where() { |
affcc9d2 | 303 | $clauses = []; |
6a488035 TO |
304 | foreach ($this->_columns as $tableName => $table) { |
305 | if (array_key_exists('filters', $table)) { | |
306 | foreach ($table['filters'] as $fieldName => $field) { | |
307 | $clause = NULL; | |
308 | ||
309 | if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_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 TO |
313 | |
314 | $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']); | |
315 | } | |
316 | else { | |
9c1bc317 | 317 | $op = $this->_params["{$fieldName}_op"] ?? NULL; |
6a488035 TO |
318 | if ($op) { |
319 | $clause = $this->whereClause($field, | |
320 | $op, | |
321 | CRM_Utils_Array::value("{$fieldName}_value", $this->_params), | |
322 | CRM_Utils_Array::value("{$fieldName}_min", $this->_params), | |
323 | CRM_Utils_Array::value("{$fieldName}_max", $this->_params) | |
324 | ); | |
325 | } | |
326 | } | |
327 | ||
328 | if (!empty($clause)) { | |
329 | $clauses[] = $clause; | |
330 | } | |
331 | } | |
332 | } | |
333 | } | |
334 | ||
335 | //apply survey activity types filter. | |
336 | $surveyActivityTypes = CRM_Campaign_BAO_Survey::getSurveyActivityType(); | |
337 | if (!empty($surveyActivityTypes)) { | |
3bdca100 | 338 | $clauses[] = "( {$this->_aliases['civicrm_activity']}.activity_type_id IN ( " . |
9d72cede | 339 | implode(' , ', array_keys($surveyActivityTypes)) . ' ) )'; |
6a488035 TO |
340 | } |
341 | ||
342 | // always filter out deleted activities (so contacts that have been released | |
343 | // don't show up in the report). | |
344 | $clauses[] = "( {$this->_aliases['civicrm_activity']}.is_deleted = 0 )"; | |
345 | ||
346 | if (empty($clauses)) { | |
347 | $this->_where = "WHERE ( 1 ) "; | |
348 | } | |
349 | else { | |
350 | $this->_where = "WHERE " . implode(' AND ', $clauses); | |
351 | } | |
352 | ||
353 | if ($this->_aclWhere) { | |
354 | $this->_where .= " AND {$this->_aclWhere} "; | |
355 | } | |
356 | } | |
357 | ||
54aaa395 JM |
358 | public function compileContent() { |
359 | $coverSheet = $this->_surveyCoverSheet() . | |
360 | "<div style=\"page-break-after: always\"></div>"; | |
361 | $templateFile = $this->getHookedTemplateFileName(); | |
362 | return $coverSheet . | |
363 | CRM_Core_Form::$_template->fetch($templateFile) . | |
364 | CRM_Utils_Array::value('report_footer', $this->_formValues); | |
6a488035 TO |
365 | } |
366 | ||
74cf4551 EM |
367 | /** |
368 | * @return bool|mixed|null|string | |
369 | */ | |
6a488035 TO |
370 | private function _surveyCoverSheet() { |
371 | $coverSheet = NULL; | |
9c1bc317 | 372 | $surveyIds = $this->_params['survey_id_value'] ?? NULL; |
6a488035 TO |
373 | if (CRM_Utils_System::isNull($surveyIds)) { |
374 | return $coverSheet; | |
375 | } | |
376 | ||
affcc9d2 | 377 | $fieldIds = []; |
6a488035 | 378 | |
affcc9d2 | 379 | $surveyResponseFields = []; |
6a488035 TO |
380 | foreach ($this->_columns as $tableName => $values) { |
381 | if (!is_array($values['fields'])) { | |
382 | continue; | |
383 | } | |
384 | foreach ($values['fields'] as $name => $field) { | |
a7488080 | 385 | if (!empty($field['isSurveyResponseField'])) { |
6a488035 TO |
386 | $fldId = substr($name, 7); |
387 | $fieldIds[$fldId] = $fldId; | |
388 | $title = CRM_Utils_Array::value('label', $field, $field['title']); | |
389 | $surveyResponseFields[$name] = array( | |
390 | 'id' => $fldId, | |
391 | 'title' => $title, | |
392 | 'name' => "{$tableName}_{$name}", | |
393 | ); | |
394 | } | |
395 | } | |
396 | } | |
397 | ||
398 | //now pickup all options. | |
399 | if (!empty($fieldIds)) { | |
400 | $query = ' | |
401 | SELECT field.id as id, | |
402 | val.label as label, | |
403 | val.value as value | |
404 | FROM civicrm_custom_field field | |
405 | INNER JOIN civicrm_option_value val ON ( val.option_group_id = field.option_group_id ) | |
406 | WHERE field.id IN (' . implode(' , ', $fieldIds) . ' ) | |
407 | Order By val.weight'; | |
408 | $field = CRM_Core_DAO::executeQuery($query); | |
affcc9d2 | 409 | $options = []; |
6a488035 TO |
410 | while ($field->fetch()) { |
411 | $name = "custom_{$field->id}"; | |
412 | $surveyResponseFields[$name]['options'][$field->value] = $field->label; | |
413 | } | |
414 | } | |
415 | ||
416 | //get the result values. | |
417 | $query = ' | |
418 | SELECT survey.id as id, | |
419 | survey.title as title, | |
420 | val.label as label, | |
421 | val.value as value | |
422 | FROM civicrm_survey survey | |
f813f78e | 423 | INNER JOIN civicrm_option_value val ON ( val.option_group_id = survey.result_id ) |
6a488035 TO |
424 | WHERE survey.id IN ( ' . implode(' , ', array_values($surveyIds)) . ' ) |
425 | Order By val.weight'; | |
426 | $resultSet = CRM_Core_DAO::executeQuery($query); | |
affcc9d2 | 427 | $surveyResultFields = []; |
6a488035 TO |
428 | while ($resultSet->fetch()) { |
429 | $surveyResultFields[$resultSet->id]['title'] = $resultSet->title; | |
430 | $surveyResultFields[$resultSet->id]['options'][$resultSet->value] = $resultSet->label; | |
431 | } | |
432 | ||
433 | $this->assign('surveyResultFields', $surveyResultFields); | |
434 | $this->assign('surveyResponseFields', $surveyResponseFields); | |
435 | ||
436 | $templateFile = 'CRM/Report/Form/Campaign/SurveyCoverSheet.tpl'; | |
437 | $coverSheet = CRM_Core_Form::$_template->fetch($templateFile); | |
438 | ||
439 | return $coverSheet; | |
440 | } | |
441 | ||
74cf4551 | 442 | /** |
4b62bc4f EM |
443 | * Alter display of rows. |
444 | * | |
445 | * Iterate through the rows retrieved via SQL and make changes for display purposes, | |
446 | * such as rendering contacts as links. | |
447 | * | |
448 | * @param array $rows | |
449 | * Rows generated by SQL, with an array for each row. | |
74cf4551 | 450 | */ |
00be9182 | 451 | public function alterDisplay(&$rows) { |
6a488035 | 452 | |
6a488035 | 453 | $this->_formatSurveyResult($rows); |
6a488035 TO |
454 | $this->_formatSurveyResponseData($rows); |
455 | ||
6a488035 TO |
456 | $entryFound = FALSE; |
457 | foreach ($rows as $rowNum => $row) { | |
6a488035 TO |
458 | // convert display name to links |
459 | if (array_key_exists('civicrm_contact_sort_name', $row) && | |
460 | array_key_exists('civicrm_contact_id', $row) | |
461 | ) { | |
462 | $url = CRM_Report_Utils_Report::getNextUrl('contact/detail', | |
463 | 'reset=1&force=1&id_op=eq&id_value=' . | |
464 | $row['civicrm_contact_id'], | |
465 | $this->_absoluteUrl, $this->_id, $this->_drilldownReport | |
466 | ); | |
467 | $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url; | |
468 | $entryFound = TRUE; | |
469 | } | |
470 | ||
a418f518 KW |
471 | if (array_key_exists('civicrm_activity_contact_contact_id', $row)) { |
472 | $rows[$rowNum]['civicrm_activity_contact_contact_id'] = CRM_Utils_Array::value($row['civicrm_activity_contact_contact_id'], | |
6a488035 TO |
473 | CRM_Campaign_BAO_Survey::getInterviewers() |
474 | ); | |
475 | $entryFound = TRUE; | |
476 | } | |
477 | ||
6a488035 TO |
478 | if (array_key_exists('civicrm_activity_survey_id', $row)) { |
479 | $rows[$rowNum]['civicrm_activity_survey_id'] = CRM_Utils_Array::value($row['civicrm_activity_survey_id'], | |
480 | CRM_Campaign_BAO_Survey::getSurveys() | |
481 | ); | |
482 | $entryFound = TRUE; | |
483 | } | |
484 | ||
96d30001 | 485 | $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, NULL, NULL) ? TRUE : $entryFound; |
486 | ||
6a488035 TO |
487 | // skip looking further in rows, if first row itself doesn't |
488 | // have the column we need | |
489 | if (!$entryFound) { | |
490 | break; | |
491 | } | |
492 | } | |
493 | } | |
494 | ||
74cf4551 | 495 | /** |
fa3fdebc | 496 | * @param array $rows |
74cf4551 | 497 | */ |
6a488035 | 498 | private function _formatSurveyResult(&$rows) { |
9c1bc317 | 499 | $surveyIds = $this->_params['survey_id_value'] ?? NULL; |
9d72cede EM |
500 | if (CRM_Utils_System::isNull($surveyIds) || |
501 | empty($this->_params['fields']['result']) || | |
6a488035 TO |
502 | !in_array($this->_outputMode, array('print', 'pdf')) |
503 | ) { | |
504 | return; | |
505 | } | |
506 | ||
507 | //swap the survey result label w/ value. | |
508 | $query = ' | |
509 | SELECT survey.id as id, | |
f813f78e | 510 | val.label as label, |
6a488035 TO |
511 | val.value as value |
512 | FROM civicrm_option_value val | |
513 | INNER JOIN civicrm_option_group grp ON ( grp.id = val.option_group_id ) | |
f813f78e | 514 | INNER JOIN civicrm_survey survey ON ( survey.result_id = grp.id ) |
6a488035 TO |
515 | WHERE survey.id IN (' . implode(' , ', array_values($surveyIds)) . ' ) |
516 | Order By val.weight'; | |
517 | ||
518 | $result = CRM_Core_DAO::executeQuery($query); | |
affcc9d2 | 519 | $resultSet = []; |
6a488035 TO |
520 | while ($result->fetch()) { |
521 | $resultSet[$result->id][$result->value] = $result->label; | |
522 | } | |
523 | ||
9c1bc317 CW |
524 | $statusId = $this->_params['status_id_value'] ?? NULL; |
525 | $respondentStatus = self::$_surveyRespondentStatus[$statusId] ?? NULL; | |
6a488035 | 526 | |
9c1bc317 | 527 | $surveyId = $surveyIds[0] ?? NULL; |
6a488035 | 528 | foreach ($rows as & $row) { |
a7488080 | 529 | if (!empty($row['civicrm_activity_survey_id'])) { |
6a488035 TO |
530 | $surveyId = $row['civicrm_activity_survey_id']; |
531 | } | |
affcc9d2 | 532 | $result = CRM_Utils_Array::value($surveyId, $resultSet, []); |
9c1bc317 | 533 | $resultLabel = $row['civicrm_activity_result'] ?? NULL; |
6a488035 TO |
534 | if ($respondentStatus == 'Reserved') { |
535 | $row['civicrm_activity_result'] = implode(' | ', array_keys($result)); | |
536 | } | |
537 | elseif ($resultLabel) { | |
538 | $resultValue = array_search($resultLabel, $result); | |
539 | if ($resultValue) { | |
540 | $row['civicrm_activity_result'] = $resultValue; | |
541 | } | |
542 | } | |
543 | } | |
544 | } | |
545 | ||
74cf4551 | 546 | /** |
fa3fdebc | 547 | * @param array $rows |
74cf4551 | 548 | */ |
6a488035 | 549 | private function _formatSurveyResponseData(&$rows) { |
9c1bc317 | 550 | $surveyIds = $this->_params['survey_id_value'] ?? NULL; |
9d72cede EM |
551 | if (CRM_Utils_System::isNull($surveyIds) || |
552 | empty($this->_params['fields']['survey_response']) | |
553 | ) { | |
6a488035 TO |
554 | return; |
555 | } | |
556 | ||
affcc9d2 CW |
557 | $surveyResponseFields = []; |
558 | $surveyResponseFieldIds = []; | |
6a488035 TO |
559 | foreach ($this->_columns as $tableName => $values) { |
560 | if (!is_array($values['fields'])) { | |
561 | continue; | |
562 | } | |
563 | foreach ($values['fields'] as $name => $field) { | |
a7488080 | 564 | if (!empty($field['isSurveyResponseField'])) { |
6a488035 TO |
565 | $fldId = substr($name, 7); |
566 | $surveyResponseFields[$name] = "{$tableName}_{$name}"; | |
567 | $surveyResponseFieldIds[$fldId] = $fldId; | |
568 | } | |
569 | } | |
570 | } | |
571 | ||
572 | if (empty($surveyResponseFieldIds)) { | |
573 | return; | |
574 | } | |
575 | ||
576 | $hasResponseData = FALSE; | |
577 | foreach ($surveyResponseFields as $fldName) { | |
578 | foreach ($rows as $row) { | |
a7488080 | 579 | if (!empty($row[$fldName])) { |
6a488035 TO |
580 | $hasResponseData = TRUE; |
581 | break; | |
582 | } | |
583 | } | |
584 | } | |
585 | ||
586 | //do check respondent status. | |
9c1bc317 CW |
587 | $statusId = $this->_params['status_id_value'] ?? NULL; |
588 | $respondentStatus = self::$_surveyRespondentStatus[$statusId] ?? NULL; | |
6a488035 TO |
589 | |
590 | if (!$hasResponseData && | |
591 | ($respondentStatus != 'Reserved') | |
592 | ) { | |
593 | return; | |
594 | } | |
595 | ||
596 | //start response data formatting. | |
f813f78e | 597 | $query = ' |
6a488035 TO |
598 | SELECT cf.id, |
599 | cf.data_type, | |
600 | cf.html_type, | |
f813f78e | 601 | cg.table_name, |
6a488035 TO |
602 | cf.column_name, |
603 | ov.value, ov.label, | |
604 | cf.option_group_id | |
f813f78e | 605 | FROM civicrm_custom_field cf |
606 | INNER JOIN civicrm_custom_group cg ON ( cg.id = cf.custom_group_id ) | |
6a488035 TO |
607 | LEFT JOIN civicrm_option_value ov ON ( cf.option_group_id = ov.option_group_id ) |
608 | WHERE cf.id IN ( ' . implode(' , ', $surveyResponseFieldIds) . ' ) | |
609 | Order By ov.weight'; | |
610 | ||
affcc9d2 CW |
611 | $responseFields = []; |
612 | $fieldValueMap = []; | |
9d72cede | 613 | $properties = array( |
6a488035 TO |
614 | 'id', |
615 | 'data_type', | |
616 | 'html_type', | |
617 | 'column_name', | |
618 | 'option_group_id', | |
619 | ); | |
620 | ||
621 | $responseField = CRM_Core_DAO::executeQuery($query); | |
622 | while ($responseField->fetch()) { | |
9d72cede EM |
623 | $reponseFldName = $responseField->table_name . '_custom_' . |
624 | $responseField->id; | |
6a488035 TO |
625 | foreach ($properties as $prop) { |
626 | $responseFields[$reponseFldName][$prop] = $responseField->$prop; | |
627 | } | |
628 | if ($responseField->option_group_id) { | |
629 | //show value for print and pdf. | |
630 | $value = $responseField->label; | |
631 | if (in_array($this->_outputMode, array( | |
9d72cede | 632 | 'print', |
21dfd5f5 | 633 | 'pdf', |
9d72cede | 634 | ))) { |
6a488035 TO |
635 | $value = $responseField->value; |
636 | } | |
637 | $fieldValueMap[$responseField->option_group_id][$responseField->value] = $value; | |
638 | } | |
639 | } | |
6a488035 TO |
640 | |
641 | //actual data formatting. | |
642 | $hasData = FALSE; | |
643 | foreach ($rows as & $row) { | |
644 | if (!is_array($row)) { | |
645 | continue; | |
646 | } | |
647 | ||
648 | foreach ($row as $name => & $value) { | |
649 | if (!array_key_exists($name, $responseFields)) { | |
650 | continue; | |
651 | } | |
652 | $hasData = TRUE; | |
653 | if ($respondentStatus == 'Reserved' && | |
654 | in_array($this->_outputMode, array('print', 'pdf')) | |
655 | ) { | |
9c1bc317 | 656 | $optGrpId = $responseFields[$name]['option_group_id'] ?? NULL; |
affcc9d2 | 657 | $options = CRM_Utils_Array::value($optGrpId, $fieldValueMap, []); |
9d72cede | 658 | $value = implode(' | ', array_keys($options)); |
6a488035 TO |
659 | } |
660 | else { | |
28241a61 | 661 | $value = CRM_Core_BAO_CustomField::displayValue($value, $responseFields[$name]['id']); |
6a488035 TO |
662 | } |
663 | } | |
664 | ||
9d72cede EM |
665 | if (!$hasData) { |
666 | break; | |
667 | } | |
6a488035 TO |
668 | } |
669 | } | |
670 | ||
671 | private function _addSurveyResponseColumns() { | |
9c1bc317 | 672 | $surveyIds = $this->_params['survey_id_value'] ?? NULL; |
9d72cede EM |
673 | if (CRM_Utils_System::isNull($surveyIds) || |
674 | empty($this->_params['fields']['survey_response']) | |
675 | ) { | |
6a488035 TO |
676 | return; |
677 | } | |
678 | ||
affcc9d2 | 679 | $responseFields = []; |
6a488035 | 680 | foreach ($surveyIds as $surveyId) { |
f7ed6ab4 | 681 | $responseFields += CRM_Campaign_BAO_Survey::getSurveyResponseFields($surveyId); |
6a488035 TO |
682 | $this->_surveyResponseFields = $responseFields; |
683 | } | |
9d72cede EM |
684 | foreach ($responseFields as $key => $value) { |
685 | if (substr($key, 0, 5) == 'phone' && !empty($value['location_type_id'])) { | |
686 | $fName = str_replace('-', '_', $key); | |
3bdca100 | 687 | $this->_columns["civicrm_{$fName}"] = array( |
688 | 'dao' => 'CRM_Core_DAO_Phone', | |
689 | 'alias' => "phone_civireport_{$fName}", | |
690 | 'fields' => array( | |
691 | $fName => array_merge($value, array( | |
c86d4e7c SL |
692 | 'is_required' => '1', |
693 | 'alias' => "phone_civireport_{$fName}", | |
694 | 'dbAlias' => "phone_civireport_{$fName}.phone", | |
695 | 'no_display' => TRUE, | |
696 | )), | |
3bdca100 | 697 | ), |
698 | ); | |
6a488035 TO |
699 | $this->_aliases["civicrm_phone_{$fName}"] = $this->_columns["civicrm_{$fName}"]['alias']; |
700 | $this->_locationBasedPhoneField = TRUE; | |
701 | } | |
702 | } | |
affcc9d2 | 703 | $responseFieldIds = []; |
6a488035 TO |
704 | foreach (array_keys($responseFields) as $key) { |
705 | $cfId = CRM_Core_BAO_CustomField::getKeyID($key); | |
706 | if ($cfId) { | |
707 | $responseFieldIds[$cfId] = $cfId; | |
708 | } | |
709 | } | |
710 | if (empty($responseFieldIds)) { | |
711 | return; | |
712 | } | |
713 | ||
714 | $query = ' | |
f813f78e | 715 | SELECT cg.extends, |
716 | cf.data_type, | |
717 | cf.html_type, | |
718 | cg.table_name, | |
6a488035 TO |
719 | cf.column_name, |
720 | cf.time_format, | |
721 | cf.id as cfId, | |
722 | cf.option_group_id | |
f813f78e | 723 | FROM civicrm_custom_group cg |
6a488035 | 724 | INNER JOIN civicrm_custom_field cf ON ( cg.id = cf.custom_group_id ) |
9d72cede EM |
725 | WHERE cf.id IN ( ' . implode(' , ', $responseFieldIds) . |
726 | ' ) ORDER BY cf.weight'; | |
6a488035 | 727 | $response = CRM_Core_DAO::executeQuery($query); |
d06b0fea | 728 | $fieldCnt = 1; |
6a488035 TO |
729 | while ($response->fetch()) { |
730 | $resTable = $response->table_name; | |
731 | $fieldName = "custom_{$response->cfId}"; | |
f813f78e | 732 | |
6a488035 TO |
733 | //need to check does these custom data already included. |
734 | ||
735 | if (!array_key_exists($resTable, $this->_columns)) { | |
736 | $this->_columns[$resTable]['dao'] = 'CRM_Contact_DAO_Contact'; | |
737 | $this->_columns[$resTable]['extends'] = $response->extends; | |
738 | } | |
a7488080 | 739 | if (empty($this->_columns[$resTable]['alias'])) { |
6a488035 TO |
740 | $this->_columns[$resTable]['alias'] = "{$resTable}_survey_response"; |
741 | } | |
e01bf597 CW |
742 | if (empty($this->_columns[$resTable]['fields'])) { |
743 | $this->_columns[$resTable]['fields'] = []; | |
6a488035 | 744 | } |
6a488035 | 745 | |
e01bf597 | 746 | if (in_array($this->_outputMode, ['print', 'pdf'])) { |
d06b0fea JM |
747 | $this->_columnTitleOverrides["{$resTable}_{$fieldName}"] = 'Q' . $fieldCnt; |
748 | $fieldCnt++; | |
b2a674e6 JM |
749 | } |
750 | ||
751 | if (array_key_exists($fieldName, $this->_columns[$resTable]['fields'])) { | |
752 | $this->_columns[$resTable]['fields'][$fieldName]['required'] = TRUE; | |
753 | $this->_columns[$resTable]['fields'][$fieldName]['isSurveyResponseField'] = TRUE; | |
754 | continue; | |
6a488035 TO |
755 | } |
756 | ||
b2a674e6 | 757 | $title = $responseFields[$fieldName]['title']; |
6a488035 TO |
758 | $fldType = 'CRM_Utils_Type::T_STRING'; |
759 | if ($response->time_format) { | |
760 | $fldType = CRM_Utils_Type::T_TIMESTAMP; | |
761 | } | |
762 | $field = array( | |
763 | 'name' => $response->column_name, | |
764 | 'type' => $fldType, | |
765 | 'title' => $title, | |
766 | 'label' => $responseFields[$fieldName]['title'], | |
767 | 'dataType' => $response->data_type, | |
768 | 'htmlType' => $response->html_type, | |
769 | 'required' => TRUE, | |
84178120 | 770 | 'alias' => ($response->data_type == 'ContactReference') ? $this->_columns[$resTable]['alias'] . |
3bdca100 | 771 | '_contact' : $this->_columns[$resTable]['alias'], |
9d72cede | 772 | 'dbAlias' => $this->_columns[$resTable]['alias'] . '.' . |
3bdca100 | 773 | $response->column_name, |
6a488035 TO |
774 | 'no_display' => TRUE, |
775 | 'isSurveyResponseField' => TRUE, | |
776 | ); | |
777 | ||
778 | $this->_columns[$resTable]['fields'][$fieldName] = $field; | |
779 | $this->_aliases[$resTable] = $this->_columns[$resTable]['alias']; | |
780 | } | |
781 | } | |
96025800 | 782 | |
6a488035 | 783 | } |