CRM/Report/Form/Campaign/SurveyDetails.php

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