3 +--------------------------------------------------------------------+
4 | Copyright CiviCRM LLC. All rights reserved. |
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 |
9 +--------------------------------------------------------------------+
15 * @copyright CiviCRM LLC https://civicrm.org/licensing
19 class CRM_Report_Form_Campaign_SurveyDetails
extends CRM_Report_Form
{
21 protected $_emailField = FALSE;
23 protected $_phoneField = FALSE;
25 protected $_locationBasedPhoneField = FALSE;
27 protected $_summary = NULL;
28 protected $_customGroupGroupBy = FALSE;
29 protected $_customGroupExtends = array(
36 public $_drilldownReport = array('contact/detail' => 'Link to Detail Report');
38 private static $_surveyRespondentStatus;
40 // Survey Question titles are overridden when in print or pdf mode to
42 * say Q1, Q2 instead of the full title - to save space.
45 private $_columnTitleOverrides = array();
52 public function __construct() {
53 //filter options for survey activity status.
54 $responseStatus = array('' => '- Any -');
55 self
::$_surveyRespondentStatus = array();
56 $activityStatus = CRM_Core_PseudoConstant
::activityStatus('name');
57 if ($statusId = array_search('Scheduled', $activityStatus)) {
58 $responseStatus[$statusId] = ts('Reserved');
59 self
::$_surveyRespondentStatus[$statusId] = 'Reserved';
61 if ($statusId = array_search('Completed', $activityStatus)) {
62 $responseStatus[$statusId] = ts('Interviewed');
63 self
::$_surveyRespondentStatus[$statusId] = 'Interviewed';
66 $optionGroups = CRM_Campaign_BAO_Survey
::getResultSets('name');
67 $resultOptions = array();
68 foreach ($optionGroups as $gid => $name) {
71 $value = CRM_Core_OptionGroup
::values($name);
73 $value = array_combine($value, $value);
75 $resultOptions = $resultOptions +
$value;
78 asort($resultOptions);
80 //get all interviewers.
81 $allSurveyInterviewers = CRM_Campaign_BAO_Survey
::getInterviewers();
83 $this->_columns
= array(
84 'civicrm_activity_contact' => array(
85 'dao' => 'CRM_Activity_DAO_ActivityContact',
86 'fields' => array('contact_id' => array('title' => ts('Interviewer Name'))),
88 'contact_id' => array(
89 'name' => 'contact_id',
90 'title' => ts('Interviewer Name'),
91 'type' => CRM_Utils_Type
::T_INT
,
92 'operatorType' => CRM_Report_Form
::OP_SELECT
,
94 '' => ts('- any interviewer -'),
95 ) +
$allSurveyInterviewers,
98 'grouping' => 'survey-interviewer-fields',
100 'civicrm_contact' => array(
101 'dao' => 'CRM_Contact_DAO_Contact',
104 'title' => ts('Contact ID'),
105 'no_display' => TRUE,
108 'sort_name' => array(
109 'title' => ts('Respondent Name'),
115 'sort_name' => array(
116 'title' => ts('Respondent Name'),
117 'operator' => 'like',
120 'grouping' => 'contact-fields',
121 'order_bys' => array(
122 'sort_name' => array(
123 'title' => ts('Respondent Name'),
128 'civicrm_phone' => array(
129 'dao' => 'CRM_Core_DAO_Phone',
133 'title' => ts('Phone'),
136 'grouping' => 'location-fields',
138 'civicrm_email' => array(
139 'dao' => 'CRM_Core_DAO_Email',
143 'title' => ts('Email'),
146 'grouping' => 'location-fields',
148 ) +
$this->getAddressColumns() +
150 'civicrm_activity' => array(
151 'dao' => 'CRM_Activity_DAO_Activity',
152 'alias' => 'survey_activity',
154 'survey_id' => array(
155 'name' => 'source_record_id',
156 'title' => ts('Survey'),
157 'type' => CRM_Utils_Type
::T_INT
,
158 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
159 'options' => CRM_Campaign_BAO_Survey
::getSurveys(),
161 'survey_response' => array(
162 'name' => 'survey_response',
163 'title' => ts('Survey Responses'),
167 'title' => ts('Note'),
173 'title' => ts('Survey Result'),
175 'activity_date_time' => array(
176 'name' => 'activity_date_time',
177 'title' => ts('Date'),
178 'type' => CRM_Utils_Type
::T_DATE + CRM_Utils_Type
::T_TIME
,
182 'survey_id' => array(
183 'name' => 'source_record_id',
184 'title' => ts('Survey'),
185 'type' => CRM_Utils_Type
::T_INT
,
186 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
187 'options' => CRM_Campaign_BAO_Survey
::getSurveys(),
189 'status_id' => array(
190 'name' => 'status_id',
191 'title' => ts('Respondent Status'),
192 'type' => CRM_Utils_Type
::T_INT
,
193 'operatorType' => CRM_Report_Form
::OP_SELECT
,
194 'options' => $responseStatus,
197 'title' => ts('Survey Result'),
198 'type' => CRM_Utils_Type
::T_STRING
,
199 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
200 'options' => $resultOptions,
202 'activity_date_time' => array(
203 'title' => ts('Date'),
204 'type' => CRM_Utils_Type
::T_DATE + CRM_Utils_Type
::T_TIME
,
205 'operatorType' => CRM_Report_Form
::OP_DATE
,
208 'grouping' => 'survey-activity-fields',
209 'order_bys' => array(
210 'activity_date_time' => array(
211 'title' => ts('Date'),
216 parent
::__construct();
219 public function preProcess() {
220 parent
::preProcess();
223 public function select() {
226 //add the survey response fields.
227 $this->_addSurveyResponseColumns();
229 $this->_columnHeaders
= array();
230 foreach ($this->_columns
as $tableName => $table) {
231 if (!isset($table['fields'])) {
234 foreach ($table['fields'] as $fieldName => $field) {
235 if (!empty($field['required']) ||
236 !empty($this->_params
['fields'][$fieldName]) ||
237 !empty($field['is_required'])
240 $fieldsName = CRM_Utils_Array
::value(1, explode('_', $tableName));
242 $this->{"_$fieldsName" . 'Field'} = TRUE;
245 //need to pickup custom data/survey response fields.
246 if ($fieldName == 'survey_response') {
250 $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
253 $title = $field['title'] ??
NULL;
254 // Check for an override.
255 if (!empty($this->_columnTitleOverrides
["{$tableName}_{$fieldName}"])) {
256 $title = $this->_columnTitleOverrides
["{$tableName}_{$fieldName}"];
258 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['title'] = $title;
260 $this->_selectAliases
[] = "{$tableName}_{$fieldName}";
265 $this->_select
= "SELECT " . implode(",\n", $select) . " ";
268 public function from() {
269 $this->_from
= " FROM civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom} ";
270 $activityContacts = CRM_Activity_BAO_ActivityContact
::buildOptions('record_type_id', 'validate');
271 $assigneeID = CRM_Utils_Array
::key('Activity Assignees', $activityContacts);
272 $targetID = CRM_Utils_Array
::key('Activity Targets', $activityContacts);
274 //get the activity table joins.
275 $this->_from
.= " INNER JOIN civicrm_activity_contact civicrm_activity_target ON
276 ( {$this->_aliases['civicrm_contact']}.id = civicrm_activity_target.contact_id AND civicrm_activity_target.record_type_id = {$targetID}) \n";
277 $this->_from
.= " INNER JOIN civicrm_activity {$this->_aliases['civicrm_activity']} ON
278 ( {$this->_aliases['civicrm_activity']}.id = civicrm_activity_target.activity_id )\n";
279 $this->_from
.= " INNER JOIN civicrm_activity_contact activity_contact_civireport ON
280 ( {$this->_aliases['civicrm_activity']}.id = activity_contact_civireport.activity_id AND activity_contact_civireport.record_type_id = {$assigneeID} )\n";
282 $this->joinAddressFromContact();
283 $this->joinPhoneFromContact();
284 $this->joinEmailFromContact();
286 if ($this->_locationBasedPhoneField
) {
287 foreach ($this->_surveyResponseFields
as $key => $value) {
288 if (substr($key, 0, 5) == 'phone' && !empty($value['location_type_id'])
290 $fName = str_replace('-', '_', $key);
291 $this->_from
.= "LEFT JOIN civicrm_phone " .
292 $this->_aliases
["civicrm_phone_{$fName}"] .
293 " ON {$this->_aliases['civicrm_contact']}.id = " .
294 $this->_aliases
["civicrm_phone_{$fName}"] . ".contact_id AND " .
295 $this->_aliases
["civicrm_phone_{$fName}"] .
296 ".location_type_id = {$value['location_type_id']} AND " .
297 $this->_aliases
["civicrm_phone_{$fName}"] .
298 ".phone_type_id = {$value['phone_type_id']}\n";
304 public function where() {
306 foreach ($this->_columns
as $tableName => $table) {
307 if (array_key_exists('filters', $table)) {
308 foreach ($table['filters'] as $fieldName => $field) {
311 if (CRM_Utils_Array
::value('type', $field) & CRM_Utils_Type
::T_DATE
) {
312 $relative = $this->_params
["{$fieldName}_relative"] ??
NULL;
313 $from = $this->_params
["{$fieldName}_from"] ??
NULL;
314 $to = $this->_params
["{$fieldName}_to"] ??
NULL;
316 $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
319 $op = $this->_params
["{$fieldName}_op"] ??
NULL;
321 $clause = $this->whereClause($field,
323 CRM_Utils_Array
::value("{$fieldName}_value", $this->_params
),
324 CRM_Utils_Array
::value("{$fieldName}_min", $this->_params
),
325 CRM_Utils_Array
::value("{$fieldName}_max", $this->_params
)
330 if (!empty($clause)) {
331 $clauses[] = $clause;
337 //apply survey activity types filter.
338 $surveyActivityTypes = CRM_Campaign_BAO_Survey
::getSurveyActivityType();
339 if (!empty($surveyActivityTypes)) {
340 $clauses[] = "( {$this->_aliases['civicrm_activity']}.activity_type_id IN ( " .
341 implode(' , ', array_keys($surveyActivityTypes)) . ' ) )';
344 // always filter out deleted activities (so contacts that have been released
345 // don't show up in the report).
346 $clauses[] = "( {$this->_aliases['civicrm_activity']}.is_deleted = 0 )";
348 if (empty($clauses)) {
349 $this->_where
= "WHERE ( 1 ) ";
352 $this->_where
= "WHERE " . implode(' AND ', $clauses);
355 if ($this->_aclWhere
) {
356 $this->_where
.= " AND {$this->_aclWhere} ";
360 public function compileContent() {
361 $coverSheet = $this->_surveyCoverSheet() .
362 "<div style=\"page-break-after: always\"></div>";
363 $templateFile = $this->getHookedTemplateFileName();
365 CRM_Core_Form
::$_template->fetch($templateFile) .
366 CRM_Utils_Array
::value('report_footer', $this->_formValues
);
370 * @return bool|mixed|null|string
372 private function _surveyCoverSheet() {
374 $surveyIds = $this->_params
['survey_id_value'] ??
NULL;
375 if (CRM_Utils_System
::isNull($surveyIds)) {
381 $surveyResponseFields = array();
382 foreach ($this->_columns
as $tableName => $values) {
383 if (!is_array($values['fields'])) {
386 foreach ($values['fields'] as $name => $field) {
387 if (!empty($field['isSurveyResponseField'])) {
388 $fldId = substr($name, 7);
389 $fieldIds[$fldId] = $fldId;
390 $title = CRM_Utils_Array
::value('label', $field, $field['title']);
391 $surveyResponseFields[$name] = array(
394 'name' => "{$tableName}_{$name}",
400 //now pickup all options.
401 if (!empty($fieldIds)) {
403 SELECT field.id as id,
406 FROM civicrm_custom_field field
407 INNER JOIN civicrm_option_value val ON ( val.option_group_id = field.option_group_id )
408 WHERE field.id IN (' . implode(' , ', $fieldIds) . ' )
409 Order By val.weight';
410 $field = CRM_Core_DAO
::executeQuery($query);
412 while ($field->fetch()) {
413 $name = "custom_{$field->id}";
414 $surveyResponseFields[$name]['options'][$field->value
] = $field->label
;
418 //get the result values.
420 SELECT survey.id as id,
421 survey.title as title,
424 FROM civicrm_survey survey
425 INNER JOIN civicrm_option_value val ON ( val.option_group_id = survey.result_id )
426 WHERE survey.id IN ( ' . implode(' , ', array_values($surveyIds)) . ' )
427 Order By val.weight';
428 $resultSet = CRM_Core_DAO
::executeQuery($query);
429 $surveyResultFields = array();
430 while ($resultSet->fetch()) {
431 $surveyResultFields[$resultSet->id
]['title'] = $resultSet->title
;
432 $surveyResultFields[$resultSet->id
]['options'][$resultSet->value
] = $resultSet->label
;
435 $this->assign('surveyResultFields', $surveyResultFields);
436 $this->assign('surveyResponseFields', $surveyResponseFields);
438 $templateFile = 'CRM/Report/Form/Campaign/SurveyCoverSheet.tpl';
439 $coverSheet = CRM_Core_Form
::$_template->fetch($templateFile);
445 * Alter display of rows.
447 * Iterate through the rows retrieved via SQL and make changes for display purposes,
448 * such as rendering contacts as links.
451 * Rows generated by SQL, with an array for each row.
453 public function alterDisplay(&$rows) {
455 $this->_formatSurveyResult($rows);
456 $this->_formatSurveyResponseData($rows);
459 foreach ($rows as $rowNum => $row) {
460 // convert display name to links
461 if (array_key_exists('civicrm_contact_sort_name', $row) &&
462 array_key_exists('civicrm_contact_id', $row)
464 $url = CRM_Report_Utils_Report
::getNextUrl('contact/detail',
465 'reset=1&force=1&id_op=eq&id_value=' .
466 $row['civicrm_contact_id'],
467 $this->_absoluteUrl
, $this->_id
, $this->_drilldownReport
469 $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
473 if (array_key_exists('civicrm_activity_contact_contact_id', $row)) {
474 $rows[$rowNum]['civicrm_activity_contact_contact_id'] = CRM_Utils_Array
::value($row['civicrm_activity_contact_contact_id'],
475 CRM_Campaign_BAO_Survey
::getInterviewers()
480 if (array_key_exists('civicrm_activity_survey_id', $row)) {
481 $rows[$rowNum]['civicrm_activity_survey_id'] = CRM_Utils_Array
::value($row['civicrm_activity_survey_id'],
482 CRM_Campaign_BAO_Survey
::getSurveys()
487 $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, NULL, NULL) ?
TRUE : $entryFound;
489 // skip looking further in rows, if first row itself doesn't
490 // have the column we need
500 private function _formatSurveyResult(&$rows) {
501 $surveyIds = $this->_params
['survey_id_value'] ??
NULL;
502 if (CRM_Utils_System
::isNull($surveyIds) ||
503 empty($this->_params
['fields']['result']) ||
504 !in_array($this->_outputMode
, array('print', 'pdf'))
509 //swap the survey result label w/ value.
511 SELECT survey.id as id,
514 FROM civicrm_option_value val
515 INNER JOIN civicrm_option_group grp ON ( grp.id = val.option_group_id )
516 INNER JOIN civicrm_survey survey ON ( survey.result_id = grp.id )
517 WHERE survey.id IN (' . implode(' , ', array_values($surveyIds)) . ' )
518 Order By val.weight';
520 $result = CRM_Core_DAO
::executeQuery($query);
521 $resultSet = array();
522 while ($result->fetch()) {
523 $resultSet[$result->id
][$result->value
] = $result->label
;
526 $statusId = $this->_params
['status_id_value'] ??
NULL;
527 $respondentStatus = self
::$_surveyRespondentStatus[$statusId] ??
NULL;
529 $surveyId = $surveyIds[0] ??
NULL;
530 foreach ($rows as & $row) {
531 if (!empty($row['civicrm_activity_survey_id'])) {
532 $surveyId = $row['civicrm_activity_survey_id'];
534 $result = CRM_Utils_Array
::value($surveyId, $resultSet, array());
535 $resultLabel = $row['civicrm_activity_result'] ??
NULL;
536 if ($respondentStatus == 'Reserved') {
537 $row['civicrm_activity_result'] = implode(' | ', array_keys($result));
539 elseif ($resultLabel) {
540 $resultValue = array_search($resultLabel, $result);
542 $row['civicrm_activity_result'] = $resultValue;
551 private function _formatSurveyResponseData(&$rows) {
552 $surveyIds = $this->_params
['survey_id_value'] ??
NULL;
553 if (CRM_Utils_System
::isNull($surveyIds) ||
554 empty($this->_params
['fields']['survey_response'])
559 $surveyResponseFields = array();
560 $surveyResponseFieldIds = array();
561 foreach ($this->_columns
as $tableName => $values) {
562 if (!is_array($values['fields'])) {
565 foreach ($values['fields'] as $name => $field) {
566 if (!empty($field['isSurveyResponseField'])) {
567 $fldId = substr($name, 7);
568 $surveyResponseFields[$name] = "{$tableName}_{$name}";
569 $surveyResponseFieldIds[$fldId] = $fldId;
574 if (empty($surveyResponseFieldIds)) {
578 $hasResponseData = FALSE;
579 foreach ($surveyResponseFields as $fldName) {
580 foreach ($rows as $row) {
581 if (!empty($row[$fldName])) {
582 $hasResponseData = TRUE;
588 //do check respondent status.
589 $statusId = $this->_params
['status_id_value'] ??
NULL;
590 $respondentStatus = self
::$_surveyRespondentStatus[$statusId] ??
NULL;
592 if (!$hasResponseData &&
593 ($respondentStatus != 'Reserved')
598 //start response data formatting.
607 FROM civicrm_custom_field cf
608 INNER JOIN civicrm_custom_group cg ON ( cg.id = cf.custom_group_id )
609 LEFT JOIN civicrm_option_value ov ON ( cf.option_group_id = ov.option_group_id )
610 WHERE cf.id IN ( ' . implode(' , ', $surveyResponseFieldIds) . ' )
613 $responseFields = array();
614 $fieldValueMap = array();
623 $responseField = CRM_Core_DAO
::executeQuery($query);
624 while ($responseField->fetch()) {
625 $reponseFldName = $responseField->table_name
. '_custom_' .
627 foreach ($properties as $prop) {
628 $responseFields[$reponseFldName][$prop] = $responseField->$prop;
630 if ($responseField->option_group_id
) {
631 //show value for print and pdf.
632 $value = $responseField->label
;
633 if (in_array($this->_outputMode
, array(
637 $value = $responseField->value
;
639 $fieldValueMap[$responseField->option_group_id
][$responseField->value
] = $value;
643 //actual data formatting.
645 foreach ($rows as & $row) {
646 if (!is_array($row)) {
650 foreach ($row as $name => & $value) {
651 if (!array_key_exists($name, $responseFields)) {
655 if ($respondentStatus == 'Reserved' &&
656 in_array($this->_outputMode
, array('print', 'pdf'))
658 $optGrpId = $responseFields[$name]['option_group_id'] ??
NULL;
659 $options = CRM_Utils_Array
::value($optGrpId, $fieldValueMap, array());
660 $value = implode(' | ', array_keys($options));
663 $value = CRM_Core_BAO_CustomField
::displayValue($value, $responseFields[$name]['id']);
673 private function _addSurveyResponseColumns() {
674 $surveyIds = $this->_params
['survey_id_value'] ??
NULL;
675 if (CRM_Utils_System
::isNull($surveyIds) ||
676 empty($this->_params
['fields']['survey_response'])
681 $responseFields = array();
682 foreach ($surveyIds as $surveyId) {
683 $responseFields +
= CRM_Campaign_BAO_Survey
::getSurveyResponseFields($surveyId);
684 $this->_surveyResponseFields
= $responseFields;
686 foreach ($responseFields as $key => $value) {
687 if (substr($key, 0, 5) == 'phone' && !empty($value['location_type_id'])) {
688 $fName = str_replace('-', '_', $key);
689 $this->_columns
["civicrm_{$fName}"] = array(
690 'dao' => 'CRM_Core_DAO_Phone',
691 'alias' => "phone_civireport_{$fName}",
693 $fName => array_merge($value, array(
694 'is_required' => '1',
695 'alias' => "phone_civireport_{$fName}",
696 'dbAlias' => "phone_civireport_{$fName}.phone",
697 'no_display' => TRUE,
701 $this->_aliases
["civicrm_phone_{$fName}"] = $this->_columns
["civicrm_{$fName}"]['alias'];
702 $this->_locationBasedPhoneField
= TRUE;
705 $responseFieldIds = array();
706 foreach (array_keys($responseFields) as $key) {
707 $cfId = CRM_Core_BAO_CustomField
::getKeyID($key);
709 $responseFieldIds[$cfId] = $cfId;
712 if (empty($responseFieldIds)) {
725 FROM civicrm_custom_group cg
726 INNER JOIN civicrm_custom_field cf ON ( cg.id = cf.custom_group_id )
727 WHERE cf.id IN ( ' . implode(' , ', $responseFieldIds) .
728 ' ) ORDER BY cf.weight';
729 $response = CRM_Core_DAO
::executeQuery($query);
731 while ($response->fetch()) {
732 $resTable = $response->table_name
;
733 $fieldName = "custom_{$response->cfId}";
735 //need to check does these custom data already included.
737 if (!array_key_exists($resTable, $this->_columns
)) {
738 $this->_columns
[$resTable]['dao'] = 'CRM_Contact_DAO_Contact';
739 $this->_columns
[$resTable]['extends'] = $response->extends;
741 if (empty($this->_columns
[$resTable]['alias'])) {
742 $this->_columns
[$resTable]['alias'] = "{$resTable}_survey_response";
744 if (empty($this->_columns
[$resTable]['fields'])) {
745 $this->_columns
[$resTable]['fields'] = [];
748 if (in_array($this->_outputMode
, ['print', 'pdf'])) {
749 $this->_columnTitleOverrides
["{$resTable}_{$fieldName}"] = 'Q' . $fieldCnt;
753 if (array_key_exists($fieldName, $this->_columns
[$resTable]['fields'])) {
754 $this->_columns
[$resTable]['fields'][$fieldName]['required'] = TRUE;
755 $this->_columns
[$resTable]['fields'][$fieldName]['isSurveyResponseField'] = TRUE;
759 $title = $responseFields[$fieldName]['title'];
760 $fldType = 'CRM_Utils_Type::T_STRING';
761 if ($response->time_format
) {
762 $fldType = CRM_Utils_Type
::T_TIMESTAMP
;
765 'name' => $response->column_name
,
768 'label' => $responseFields[$fieldName]['title'],
769 'dataType' => $response->data_type
,
770 'htmlType' => $response->html_type
,
772 'alias' => ($response->data_type
== 'ContactReference') ?
$this->_columns
[$resTable]['alias'] .
773 '_contact' : $this->_columns
[$resTable]['alias'],
774 'dbAlias' => $this->_columns
[$resTable]['alias'] . '.' .
775 $response->column_name
,
776 'no_display' => TRUE,
777 'isSurveyResponseField' => TRUE,
780 $this->_columns
[$resTable]['fields'][$fieldName] = $field;
781 $this->_aliases
[$resTable] = $this->_columns
[$resTable]['alias'];