CRM/Report/Form/Campaign/SurveyDetails.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   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  +--------------------------------------------------------------------+
  10  */
  11
  12 /**
  13  *
  14  * @package CRM
  15  * @copyright CiviCRM LLC https://civicrm.org/licensing
  16  * $Id$
  17  *
  18  */
  19 class CRM_Report_Form_Campaign_SurveyDetails extends CRM_Report_Form {
  20
  21   protected $_emailField = FALSE;
  22
  23   protected $_phoneField = FALSE;
  24
  25   protected $_locationBasedPhoneField = FALSE;
  26
  27   protected $_summary = NULL;
  28   protected $_customGroupGroupBy = FALSE;
  29   protected $_customGroupExtends = array(
  30     'Contact',
  31     'Individual',
  32     'Household',
  33     'Organization',
  34     'Activity',
  35   );
  36   public $_drilldownReport = array('contact/detail' => 'Link to Detail Report');
  37
  38   private static $_surveyRespondentStatus;
  39
  40   // Survey Question titles are overridden when in print or pdf mode to
  41   /**
  42    * say Q1, Q2 instead of the full title - to save space.
  43    * @var array
  44    */
  45   private $_columnTitleOverrides = [];
  46
  47   /**
  48    */
  49
  50   /**
  51    */
  52   public function __construct() {
  53     //filter options for survey activity status.
  54     $responseStatus = array('' => '- Any -');
  55     self::$_surveyRespondentStatus = [];
  56     $activityStatus = CRM_Core_PseudoConstant::activityStatus('name');
  57     if ($statusId = array_search('Scheduled', $activityStatus)) {
  58       $responseStatus[$statusId] = ts('Reserved');
  59       self::$_surveyRespondentStatus[$statusId] = 'Reserved';
  60     }
  61     if ($statusId = array_search('Completed', $activityStatus)) {
  62       $responseStatus[$statusId] = ts('Interviewed');
  63       self::$_surveyRespondentStatus[$statusId] = 'Interviewed';
  64     }
  65
  66     $optionGroups = CRM_Campaign_BAO_Survey::getResultSets('name');
  67     $resultOptions = [];
  68     foreach ($optionGroups as $gid => $name) {
  69       if ($name) {
  70         $value = [];
  71         $value = CRM_Core_OptionGroup::values($name);
  72         if (!empty($value)) {
  73           $value = array_combine($value, $value);
  74         }
  75         $resultOptions = $resultOptions + $value;
  76       }
  77     }
  78     asort($resultOptions);
  79
  80     //get all interviewers.
  81     $allSurveyInterviewers = CRM_Campaign_BAO_Survey::getInterviewers();
  82
  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'))),
  87         'filters' => array(
  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,
  93             'options' => array(
  94               '' => ts('- any interviewer -'),
  95             ) + $allSurveyInterviewers,
  96           ),
  97         ),
  98         'grouping' => 'survey-interviewer-fields',
  99       ),
 100       'civicrm_contact' => array(
 101         'dao' => 'CRM_Contact_DAO_Contact',
 102         'fields' => array(
 103           'id' => array(
 104             'title' => ts('Contact ID'),
 105             'no_display' => TRUE,
 106             'required' => TRUE,
 107           ),
 108           'sort_name' => array(
 109             'title' => ts('Respondent Name'),
 110             'required' => TRUE,
 111             'no_repeat' => TRUE,
 112           ),
 113         ),
 114         'filters' => array(
 115           'sort_name' => array(
 116             'title' => ts('Respondent Name'),
 117             'operator' => 'like',
 118           ),
 119         ),
 120         'grouping' => 'contact-fields',
 121         'order_bys' => array(
 122           'sort_name' => array(
 123             'title' => ts('Respondent Name'),
 124             'required' => TRUE,
 125           ),
 126         ),
 127       ),
 128       'civicrm_phone' => array(
 129         'dao' => 'CRM_Core_DAO_Phone',
 130         'fields' => array(
 131           'phone' => array(
 132             'name' => 'phone',
 133             'title' => ts('Phone'),
 134           ),
 135         ),
 136         'grouping' => 'location-fields',
 137       ),
 138       'civicrm_email' => array(
 139         'dao' => 'CRM_Core_DAO_Email',
 140         'fields' => array(
 141           'email' => array(
 142             'name' => 'email',
 143             'title' => ts('Email'),
 144           ),
 145         ),
 146         'grouping' => 'location-fields',
 147       ),
 148     ) + $this->getAddressColumns() +
 149     array(
 150       'civicrm_activity' => array(
 151         'dao' => 'CRM_Activity_DAO_Activity',
 152         'alias' => 'survey_activity',
 153         'fields' => array(
 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(),
 160           ),
 161           'survey_response' => array(
 162             'name' => 'survey_response',
 163             'title' => ts('Survey Responses'),
 164           ),
 165           'details' => array(
 166             'name' => 'details',
 167             'title' => ts('Note'),
 168             'type' => 1,
 169           ),
 170           'result' => array(
 171             'name' => 'result',
 172             'required' => TRUE,
 173             'title' => ts('Survey Result'),
 174           ),
 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,
 179           ),
 180         ),
 181         'filters' => array(
 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(),
 188           ),
 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,
 195           ),
 196           'result' => array(
 197             'title' => ts('Survey Result'),
 198             'type' => CRM_Utils_Type::T_STRING,
 199             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 200             'options' => $resultOptions,
 201           ),
 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,
 206           ),
 207         ),
 208         'grouping' => 'survey-activity-fields',
 209         'order_bys' => array(
 210           'activity_date_time' => array(
 211             'title' => ts('Date'),
 212           ),
 213         ),
 214       ),
 215     );
 216     parent::__construct();
 217   }
 218
 219   public function preProcess() {
 220     parent::preProcess();
 221   }
 222
 223   public function select() {
 224     $select = [];
 225
 226     //add the survey response fields.
 227     $this->_addSurveyResponseColumns();
 228
 229     $this->_columnHeaders = [];
 230     foreach ($this->_columns as $tableName => $table) {
 231       if (!isset($table['fields'])) {
 232         continue;
 233       }
 234       foreach ($table['fields'] as $fieldName => $field) {
 235         if (!empty($field['required']) ||
 236           !empty($this->_params['fields'][$fieldName]) ||
 237           !empty($field['is_required'])
 238         ) {
 239
 240           $fieldsName = CRM_Utils_Array::value(1, explode('_', $tableName));
 241           if ($fieldsName) {
 242             $this->{"_$fieldsName" . 'Field'} = TRUE;
 243           }
 244
 245           //need to pickup custom data/survey response fields.
 246           if ($fieldName == 'survey_response') {
 247             continue;
 248           }
 249
 250           $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 251
 252           // Set default title
 253           $title = $field['title'] ?? NULL;
 254           // Check for an override.
 255           if (!empty($this->_columnTitleOverrides["{$tableName}_{$fieldName}"])) {
 256             $title = $this->_columnTitleOverrides["{$tableName}_{$fieldName}"];
 257           }
 258           $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $title;
 259
 260           $this->_selectAliases[] = "{$tableName}_{$fieldName}";
 261         }
 262       }
 263     }
 264
 265     $this->_select = "SELECT " . implode(",\n", $select) . " ";
 266   }
 267
 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);
 273
 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";
 281
 282     $this->joinAddressFromContact();
 283     $this->joinPhoneFromContact();
 284     $this->joinEmailFromContact();
 285
 286     if ($this->_locationBasedPhoneField) {
 287       foreach ($this->_surveyResponseFields as $key => $value) {
 288         if (substr($key, 0, 5) == 'phone' && !empty($value['location_type_id'])
 289         ) {
 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";
 299         }
 300       }
 301     }
 302   }
 303
 304   public function where() {
 305     $clauses = [];
 306     foreach ($this->_columns as $tableName => $table) {
 307       if (array_key_exists('filters', $table)) {
 308         foreach ($table['filters'] as $fieldName => $field) {
 309           $clause = NULL;
 310
 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;
 315
 316             $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
 317           }
 318           else {
 319             $op = $this->_params["{$fieldName}_op"] ?? NULL;
 320             if ($op) {
 321               $clause = $this->whereClause($field,
 322                 $op,
 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)
 326               );
 327             }
 328           }
 329
 330           if (!empty($clause)) {
 331             $clauses[] = $clause;
 332           }
 333         }
 334       }
 335     }
 336
 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)) . ' ) )';
 342     }
 343
 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 )";
 347
 348     if (empty($clauses)) {
 349       $this->_where = "WHERE ( 1 ) ";
 350     }
 351     else {
 352       $this->_where = "WHERE " . implode(' AND ', $clauses);
 353     }
 354
 355     if ($this->_aclWhere) {
 356       $this->_where .= " AND {$this->_aclWhere} ";
 357     }
 358   }
 359
 360   public function compileContent() {
 361     $coverSheet = $this->_surveyCoverSheet() .
 362         "<div style=\"page-break-after: always\"></div>";
 363     $templateFile = $this->getHookedTemplateFileName();
 364     return $coverSheet .
 365       CRM_Core_Form::$_template->fetch($templateFile) .
 366       CRM_Utils_Array::value('report_footer', $this->_formValues);
 367   }
 368
 369   /**
 370    * @return bool|mixed|null|string
 371    */
 372   private function _surveyCoverSheet() {
 373     $coverSheet = NULL;
 374     $surveyIds = $this->_params['survey_id_value'] ?? NULL;
 375     if (CRM_Utils_System::isNull($surveyIds)) {
 376       return $coverSheet;
 377     }
 378
 379     $fieldIds = [];
 380
 381     $surveyResponseFields = [];
 382     foreach ($this->_columns as $tableName => $values) {
 383       if (!is_array($values['fields'])) {
 384         continue;
 385       }
 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(
 392             'id' => $fldId,
 393             'title' => $title,
 394             'name' => "{$tableName}_{$name}",
 395           );
 396         }
 397       }
 398     }
 399
 400     //now pickup all options.
 401     if (!empty($fieldIds)) {
 402       $query = '
 403     SELECT  field.id as id,
 404             val.label as label,
 405             val.value as value
 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);
 411       $options = [];
 412       while ($field->fetch()) {
 413         $name = "custom_{$field->id}";
 414         $surveyResponseFields[$name]['options'][$field->value] = $field->label;
 415       }
 416     }
 417
 418     //get the result values.
 419     $query = '
 420     SELECT  survey.id as id,
 421             survey.title as title,
 422             val.label as label,
 423             val.value as value
 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 = [];
 430     while ($resultSet->fetch()) {
 431       $surveyResultFields[$resultSet->id]['title'] = $resultSet->title;
 432       $surveyResultFields[$resultSet->id]['options'][$resultSet->value] = $resultSet->label;
 433     }
 434
 435     $this->assign('surveyResultFields', $surveyResultFields);
 436     $this->assign('surveyResponseFields', $surveyResponseFields);
 437
 438     $templateFile = 'CRM/Report/Form/Campaign/SurveyCoverSheet.tpl';
 439     $coverSheet = CRM_Core_Form::$_template->fetch($templateFile);
 440
 441     return $coverSheet;
 442   }
 443
 444   /**
 445    * Alter display of rows.
 446    *
 447    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 448    * such as rendering contacts as links.
 449    *
 450    * @param array $rows
 451    *   Rows generated by SQL, with an array for each row.
 452    */
 453   public function alterDisplay(&$rows) {
 454
 455     $this->_formatSurveyResult($rows);
 456     $this->_formatSurveyResponseData($rows);
 457
 458     $entryFound = FALSE;
 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)
 463       ) {
 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
 468         );
 469         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 470         $entryFound = TRUE;
 471       }
 472
 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()
 476         );
 477         $entryFound = TRUE;
 478       }
 479
 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()
 483         );
 484         $entryFound = TRUE;
 485       }
 486
 487       $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, NULL, NULL) ? TRUE : $entryFound;
 488
 489       // skip looking further in rows, if first row itself doesn't
 490       // have the column we need
 491       if (!$entryFound) {
 492         break;
 493       }
 494     }
 495   }
 496
 497   /**
 498    * @param $rows
 499    */
 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'))
 505     ) {
 506       return;
 507     }
 508
 509     //swap the survey result label w/ value.
 510     $query = '
 511     SELECT  survey.id as id,
 512             val.label as label,
 513             val.value as value
 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';
 519
 520     $result = CRM_Core_DAO::executeQuery($query);
 521     $resultSet = [];
 522     while ($result->fetch()) {
 523       $resultSet[$result->id][$result->value] = $result->label;
 524     }
 525
 526     $statusId = $this->_params['status_id_value'] ?? NULL;
 527     $respondentStatus = self::$_surveyRespondentStatus[$statusId] ?? NULL;
 528
 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'];
 533       }
 534       $result = CRM_Utils_Array::value($surveyId, $resultSet, []);
 535       $resultLabel = $row['civicrm_activity_result'] ?? NULL;
 536       if ($respondentStatus == 'Reserved') {
 537         $row['civicrm_activity_result'] = implode(' | ', array_keys($result));
 538       }
 539       elseif ($resultLabel) {
 540         $resultValue = array_search($resultLabel, $result);
 541         if ($resultValue) {
 542           $row['civicrm_activity_result'] = $resultValue;
 543         }
 544       }
 545     }
 546   }
 547
 548   /**
 549    * @param $rows
 550    */
 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'])
 555     ) {
 556       return;
 557     }
 558
 559     $surveyResponseFields = [];
 560     $surveyResponseFieldIds = [];
 561     foreach ($this->_columns as $tableName => $values) {
 562       if (!is_array($values['fields'])) {
 563         continue;
 564       }
 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;
 570         }
 571       }
 572     }
 573
 574     if (empty($surveyResponseFieldIds)) {
 575       return;
 576     }
 577
 578     $hasResponseData = FALSE;
 579     foreach ($surveyResponseFields as $fldName) {
 580       foreach ($rows as $row) {
 581         if (!empty($row[$fldName])) {
 582           $hasResponseData = TRUE;
 583           break;
 584         }
 585       }
 586     }
 587
 588     //do check respondent status.
 589     $statusId = $this->_params['status_id_value'] ?? NULL;
 590     $respondentStatus = self::$_surveyRespondentStatus[$statusId] ?? NULL;
 591
 592     if (!$hasResponseData &&
 593       ($respondentStatus != 'Reserved')
 594     ) {
 595       return;
 596     }
 597
 598     //start response data formatting.
 599     $query = '
 600     SELECT  cf.id,
 601             cf.data_type,
 602             cf.html_type,
 603             cg.table_name,
 604             cf.column_name,
 605             ov.value, ov.label,
 606             cf.option_group_id
 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) . ' )
 611   Order By  ov.weight';
 612
 613     $responseFields = [];
 614     $fieldValueMap = [];
 615     $properties = array(
 616       'id',
 617       'data_type',
 618       'html_type',
 619       'column_name',
 620       'option_group_id',
 621     );
 622
 623     $responseField = CRM_Core_DAO::executeQuery($query);
 624     while ($responseField->fetch()) {
 625       $reponseFldName = $responseField->table_name . '_custom_' .
 626         $responseField->id;
 627       foreach ($properties as $prop) {
 628         $responseFields[$reponseFldName][$prop] = $responseField->$prop;
 629       }
 630       if ($responseField->option_group_id) {
 631         //show value for print and pdf.
 632         $value = $responseField->label;
 633         if (in_array($this->_outputMode, array(
 634           'print',
 635           'pdf',
 636         ))) {
 637           $value = $responseField->value;
 638         }
 639         $fieldValueMap[$responseField->option_group_id][$responseField->value] = $value;
 640       }
 641     }
 642
 643     //actual data formatting.
 644     $hasData = FALSE;
 645     foreach ($rows as & $row) {
 646       if (!is_array($row)) {
 647         continue;
 648       }
 649
 650       foreach ($row as $name => & $value) {
 651         if (!array_key_exists($name, $responseFields)) {
 652           continue;
 653         }
 654         $hasData = TRUE;
 655         if ($respondentStatus == 'Reserved' &&
 656           in_array($this->_outputMode, array('print', 'pdf'))
 657         ) {
 658           $optGrpId = $responseFields[$name]['option_group_id'] ?? NULL;
 659           $options = CRM_Utils_Array::value($optGrpId, $fieldValueMap, []);
 660           $value = implode(' | ', array_keys($options));
 661         }
 662         else {
 663           $value = CRM_Core_BAO_CustomField::displayValue($value, $responseFields[$name]['id']);
 664         }
 665       }
 666
 667       if (!$hasData) {
 668         break;
 669       }
 670     }
 671   }
 672
 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'])
 677     ) {
 678       return;
 679     }
 680
 681     $responseFields = [];
 682     foreach ($surveyIds as $surveyId) {
 683       $responseFields += CRM_Campaign_BAO_Survey::getSurveyResponseFields($surveyId);
 684       $this->_surveyResponseFields = $responseFields;
 685     }
 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}",
 692           'fields' => array(
 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,
 698             )),
 699           ),
 700         );
 701         $this->_aliases["civicrm_phone_{$fName}"] = $this->_columns["civicrm_{$fName}"]['alias'];
 702         $this->_locationBasedPhoneField = TRUE;
 703       }
 704     }
 705     $responseFieldIds = [];
 706     foreach (array_keys($responseFields) as $key) {
 707       $cfId = CRM_Core_BAO_CustomField::getKeyID($key);
 708       if ($cfId) {
 709         $responseFieldIds[$cfId] = $cfId;
 710       }
 711     }
 712     if (empty($responseFieldIds)) {
 713       return;
 714     }
 715
 716     $query = '
 717      SELECT  cg.extends,
 718              cf.data_type,
 719              cf.html_type,
 720              cg.table_name,
 721              cf.column_name,
 722              cf.time_format,
 723              cf.id as cfId,
 724              cf.option_group_id
 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);
 730     $fieldCnt = 1;
 731     while ($response->fetch()) {
 732       $resTable = $response->table_name;
 733       $fieldName = "custom_{$response->cfId}";
 734
 735       //need to check does these custom data already included.
 736
 737       if (!array_key_exists($resTable, $this->_columns)) {
 738         $this->_columns[$resTable]['dao'] = 'CRM_Contact_DAO_Contact';
 739         $this->_columns[$resTable]['extends'] = $response->extends;
 740       }
 741       if (empty($this->_columns[$resTable]['alias'])) {
 742         $this->_columns[$resTable]['alias'] = "{$resTable}_survey_response";
 743       }
 744       if (empty($this->_columns[$resTable]['fields'])) {
 745         $this->_columns[$resTable]['fields'] = [];
 746       }
 747
 748       if (in_array($this->_outputMode, ['print', 'pdf'])) {
 749         $this->_columnTitleOverrides["{$resTable}_{$fieldName}"] = 'Q' . $fieldCnt;
 750         $fieldCnt++;
 751       }
 752
 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;
 756         continue;
 757       }
 758
 759       $title = $responseFields[$fieldName]['title'];
 760       $fldType = 'CRM_Utils_Type::T_STRING';
 761       if ($response->time_format) {
 762         $fldType = CRM_Utils_Type::T_TIMESTAMP;
 763       }
 764       $field = array(
 765         'name' => $response->column_name,
 766         'type' => $fldType,
 767         'title' => $title,
 768         'label' => $responseFields[$fieldName]['title'],
 769         'dataType' => $response->data_type,
 770         'htmlType' => $response->html_type,
 771         'required' => TRUE,
 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,
 778       );
 779
 780       $this->_columns[$resTable]['fields'][$fieldName] = $field;
 781       $this->_aliases[$resTable] = $this->_columns[$resTable]['alias'];
 782     }
 783   }
 784
 785 }