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