CRM/Report/Form/Campaign/SurveyDetails.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.7                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2016                                |
   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-2016
  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   /**
  57    */
  58   /**
  59    */
  60   public function __construct() {
  61     //filter options for survey activity status.
  62     $responseStatus = array('' => '- Any -');
  63     self::$_surveyRespondentStatus = array();
  64     $activityStatus = CRM_Core_PseudoConstant::activityStatus('name');
  65     if ($statusId = array_search('Scheduled', $activityStatus)) {
  66       $responseStatus[$statusId] = ts('Reserved');
  67       self::$_surveyRespondentStatus[$statusId] = 'Reserved';
  68     }
  69     if ($statusId = array_search('Completed', $activityStatus)) {
  70       $responseStatus[$statusId] = ts('Interviewed');
  71       self::$_surveyRespondentStatus[$statusId] = 'Interviewed';
  72     }
  73
  74     $optionGroups = CRM_Campaign_BAO_Survey::getResultSets('name');
  75     $resultOptions = array();
  76     foreach ($optionGroups as $gid => $name) {
  77       if ($name) {
  78         $value = array();
  79         $value = CRM_Core_OptionGroup::values($name);
  80         if (!empty($value)) {
  81           $value = array_combine($value, $value);
  82         }
  83         $resultOptions = $resultOptions + $value;
  84       }
  85     }
  86     asort($resultOptions);
  87
  88     //get all interviewers.
  89     $allSurveyInterviewers = CRM_Campaign_BAO_Survey::getInterviewers();
  90
  91     $this->_columns = array(
  92       'civicrm_activity_contact' => array(
  93         'dao' => 'CRM_Activity_DAO_ActivityContact',
  94         'fields' => array('contact_id' => array('title' => ts('Interviewer Name'))),
  95         'filters' => array(
  96           'contact_id' => array(
  97             'name' => 'contact_id',
  98             'title' => ts('Interviewer Name'),
  99             'type' => CRM_Utils_Type::T_INT,
 100             'operatorType' => CRM_Report_Form::OP_SELECT,
 101             'options' => array(
 102               '' => ts('- any interviewer -'),
 103             ) + $allSurveyInterviewers,
 104           ),
 105         ),
 106         'grouping' => 'survey-interviewer-fields',
 107       ),
 108       'civicrm_contact' => array(
 109         'dao' => 'CRM_Contact_DAO_Contact',
 110         'fields' => array(
 111           'id' => array(
 112             'title' => ts('Contact ID'),
 113             'no_display' => TRUE,
 114             'required' => TRUE,
 115           ),
 116           'sort_name' => array(
 117             'title' => ts('Respondent Name'),
 118             'required' => TRUE,
 119             'no_repeat' => TRUE,
 120           ),
 121         ),
 122         'filters' => array(
 123           'sort_name' => array(
 124             'title' => ts('Respondent Name'),
 125             'operator' => 'like',
 126           ),
 127         ),
 128         'grouping' => 'contact-fields',
 129         'order_bys' => array(
 130           'sort_name' => array(
 131             'title' => ts('Respondent Name'),
 132             'required' => TRUE,
 133           ),
 134         ),
 135       ),
 136       'civicrm_phone' => array(
 137         'dao' => 'CRM_Core_DAO_Phone',
 138         'fields' => array(
 139           'phone' => array(
 140             'name' => 'phone',
 141             'title' => ts('Phone'),
 142           ),
 143         ),
 144         'grouping' => 'location-fields',
 145       ),
 146       'civicrm_address' => array(
 147         'dao' => 'CRM_Core_DAO_Address',
 148         'fields' => array(
 149           'street_number' => array(
 150             'name' => 'street_number',
 151             'title' => ts('Street Number'),
 152             'type' => 1,
 153           ),
 154           'street_name' => array(
 155             'name' => 'street_name',
 156             'title' => ts('Street Name'),
 157             'type' => 1,
 158           ),
 159           'street_unit' => array(
 160             'name' => 'street_unit',
 161             'title' => ts('Street Unit'),
 162             'type' => 1,
 163           ),
 164           'postal_code' => array(
 165             'name' => 'postal_code',
 166             'title' => ts('Postal Code'),
 167             'type' => 1,
 168           ),
 169           'city' => array(
 170             'name' => 'city',
 171             'title' => ts('City'),
 172             'type' => 1,
 173           ),
 174           'state_province_id' => array(
 175             'name' => 'state_province_id',
 176             'title' => ts('State/Province'),
 177           ),
 178           'country_id' => array(
 179             'name' => 'country_id',
 180             'title' => ts('Country'),
 181           ),
 182         ),
 183         'filters' => array(
 184           'street_number' => array(
 185             'title' => ts('Street Number'),
 186             'type' => 1,
 187             'name' => 'street_number',
 188           ),
 189           'street_name' => array(
 190             'title' => ts('Street Name'),
 191             'name' => 'street_name',
 192             'operator' => 'like',
 193           ),
 194           'postal_code' => array(
 195             'title' => ts('Postal Code'),
 196             'type' => 1,
 197             'name' => 'postal_code',
 198           ),
 199           'city' => array(
 200             'title' => ts('City'),
 201             'operator' => 'like',
 202             'name' => 'city',
 203           ),
 204           'state_province_id' => array(
 205             'name' => 'state_province_id',
 206             'title' => ts('State/Province'),
 207             'type' => CRM_Utils_Type::T_INT,
 208             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 209             'options' => CRM_Core_PseudoConstant::stateProvince(),
 210           ),
 211           'country_id' => array(
 212             'name' => 'country_id',
 213             'title' => ts('Country'),
 214             'type' => CRM_Utils_Type::T_INT,
 215             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 216             'options' => CRM_Core_PseudoConstant::country(),
 217           ),
 218         ),
 219         'order_bys' => array(
 220           'street_name' => array('title' => ts('Street Name')),
 221           'street_number_odd_even' => array(
 222             'title' => ts('Odd / Even Street Number'),
 223             'name' => 'street_number',
 224             'dbAlias' => 'address_civireport.street_number%2',
 225           ),
 226           'street_number' => array('title' => ts('Street Number')),
 227         ),
 228         'grouping' => 'location-fields',
 229       ),
 230       'civicrm_email' => array(
 231         'dao' => 'CRM_Core_DAO_Email',
 232         'fields' => array(
 233           'email' => array(
 234             'name' => 'email',
 235             'title' => ts('Email'),
 236           ),
 237         ),
 238         'grouping' => 'location-fields',
 239       ),
 240       'civicrm_activity' => array(
 241         'dao' => 'CRM_Activity_DAO_Activity',
 242         'alias' => 'survey_activity',
 243         'fields' => array(
 244           'survey_id' => array(
 245             'name' => 'source_record_id',
 246             'title' => ts('Survey'),
 247             'type' => CRM_Utils_Type::T_INT,
 248             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 249             'options' => CRM_Campaign_BAO_Survey::getSurveys(),
 250           ),
 251           'survey_response' => array(
 252             'name' => 'survey_response',
 253             'title' => ts('Survey Responses'),
 254           ),
 255           'details' => array(
 256             'name' => 'details',
 257             'title' => ts('Note'),
 258             'type' => 1,
 259           ),
 260           'result' => array(
 261             'name' => 'result',
 262             'required' => TRUE,
 263             'title' => ts('Survey Result'),
 264           ),
 265         ),
 266         'filters' => array(
 267           'survey_id' => array(
 268             'name' => 'source_record_id',
 269             'title' => ts('Survey'),
 270             'type' => CRM_Utils_Type::T_INT,
 271             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 272             'options' => CRM_Campaign_BAO_Survey::getSurveys(),
 273           ),
 274           'status_id' => array(
 275             'name' => 'status_id',
 276             'title' => ts('Respondent Status'),
 277             'type' => CRM_Utils_Type::T_INT,
 278             'operatorType' => CRM_Report_Form::OP_SELECT,
 279             'options' => $responseStatus,
 280           ),
 281           'result' => array(
 282             'title' => ts('Survey Result'),
 283             'type' => CRM_Utils_Type::T_STRING,
 284             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 285             'options' => $resultOptions,
 286           ),
 287         ),
 288         'grouping' => 'survey-activity-fields',
 289       ),
 290     );
 291     parent::__construct();
 292   }
 293
 294   public function preProcess() {
 295     parent::preProcess();
 296   }
 297
 298   public function select() {
 299     $select = array();
 300
 301     //add the survey response fields.
 302     $this->_addSurveyResponseColumns();
 303
 304     $this->_columnHeaders = array();
 305     foreach ($this->_columns as $tableName => $table) {
 306       if (!isset($table['fields'])) {
 307         continue;
 308       }
 309       foreach ($table['fields'] as $fieldName => $field) {
 310         if (!empty($field['required']) ||
 311           !empty($this->_params['fields'][$fieldName]) ||
 312           CRM_Utils_Array::value('is_required', $field)
 313         ) {
 314
 315           $fieldsName = CRM_Utils_Array::value(1, explode('_', $tableName));
 316           if ($fieldsName) {
 317             $this->{"_$fieldsName" . 'Field'} = TRUE;
 318           }
 319
 320           //need to pickup custom data/survey response fields.
 321           if ($fieldName == 'survey_response') {
 322             continue;
 323           }
 324
 325           $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 326           $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 327           $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 328         }
 329       }
 330     }
 331
 332     $this->_select = "SELECT " . implode(",\n", $select) . " ";
 333   }
 334
 335   public function from() {
 336     $this->_from = " FROM civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom} ";
 337     $activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
 338     $assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
 339     $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
 340
 341     //get the activity table joins.
 342     $this->_from .= " INNER JOIN civicrm_activity_contact civicrm_activity_target ON
 343                       ( {$this->_aliases['civicrm_contact']}.id = civicrm_activity_target.contact_id AND civicrm_activity_target.record_type_id = {$targetID}) \n";
 344     $this->_from .= " INNER JOIN civicrm_activity {$this->_aliases['civicrm_activity']} ON
 345                       ( {$this->_aliases['civicrm_activity']}.id = civicrm_activity_target.activity_id )\n";
 346     $this->_from .= " INNER JOIN civicrm_activity_contact activity_contact_civireport ON
 347                       ( {$this->_aliases['civicrm_activity']}.id = activity_contact_civireport.activity_id  AND activity_contact_civireport.record_type_id = {$assigneeID} )\n";
 348
 349     //get the address table.
 350     $this->_from .= " LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']} ON
 351                       {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_address']}.contact_id AND {$this->_aliases['civicrm_address']}.is_primary = 1\n";
 352
 353     if ($this->_emailField) {
 354       $this->_from .= "LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']} ON
 355                        {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND {$this->_aliases['civicrm_email']}.is_primary = 1\n";
 356     }
 357
 358     if ($this->_phoneField) {
 359       $this->_from .= "LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']} ON
 360                        {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND {$this->_aliases['civicrm_phone']}.is_primary = 1\n";
 361     }
 362
 363     if ($this->_locationBasedPhoneField) {
 364       foreach ($this->_surveyResponseFields as $key => $value) {
 365         if (substr($key, 0, 5) == 'phone' && !empty($value['location_type_id'])
 366         ) {
 367           $fName = str_replace('-', '_', $key);
 368           $this->_from .= "LEFT JOIN civicrm_phone " .
 369             $this->_aliases["civicrm_phone_{$fName}"] .
 370             " ON {$this->_aliases['civicrm_contact']}.id = " .
 371             $this->_aliases["civicrm_phone_{$fName}"] . ".contact_id AND " .
 372             $this->_aliases["civicrm_phone_{$fName}"] .
 373             ".location_type_id = {$value['location_type_id']} AND " .
 374             $this->_aliases["civicrm_phone_{$fName}"] .
 375             ".phone_type_id = {$value['phone_type_id']}\n";
 376         }
 377       }
 378     }
 379   }
 380
 381   public function where() {
 382     $clauses = array();
 383     foreach ($this->_columns as $tableName => $table) {
 384       if (array_key_exists('filters', $table)) {
 385         foreach ($table['filters'] as $fieldName => $field) {
 386           $clause = NULL;
 387
 388           if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
 389             $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
 390             $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
 391             $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
 392
 393             $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
 394           }
 395           else {
 396             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 397             if ($op) {
 398               $clause = $this->whereClause($field,
 399                 $op,
 400                 CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 401                 CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 402                 CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 403               );
 404             }
 405           }
 406
 407           if (!empty($clause)) {
 408             $clauses[] = $clause;
 409           }
 410         }
 411       }
 412     }
 413
 414     //apply survey activity types filter.
 415     $surveyActivityTypes = CRM_Campaign_BAO_Survey::getSurveyActivityType();
 416     if (!empty($surveyActivityTypes)) {
 417       $clauses[] = "( {$this->_aliases['civicrm_activity']}.activity_type_id IN ( " .
 418         implode(' , ', array_keys($surveyActivityTypes)) . ' ) )';
 419     }
 420
 421     // always filter out deleted activities (so contacts that have been released
 422     // don't show up in the report).
 423     $clauses[] = "( {$this->_aliases['civicrm_activity']}.is_deleted = 0 )";
 424
 425     if (empty($clauses)) {
 426       $this->_where = "WHERE ( 1 ) ";
 427     }
 428     else {
 429       $this->_where = "WHERE " . implode(' AND ', $clauses);
 430     }
 431
 432     if ($this->_aclWhere) {
 433       $this->_where .= " AND {$this->_aclWhere} ";
 434     }
 435   }
 436
 437   public function postProcess() {
 438     // get the acl clauses built before we assemble the query
 439     $this->buildACLClause($this->_aliases['civicrm_contact']);
 440
 441     // get ready with post process params
 442     $this->beginPostProcess();
 443
 444     // build query
 445     $sql = $this->buildQuery();
 446
 447     // build array of result based on column headers. This method also allows
 448     // modifying column headers before using it to build result set i.e $rows.
 449     $rows = array();
 450     $this->buildRows($sql, $rows);
 451
 452     // format result set.
 453     $this->formatDisplay($rows);
 454
 455     //call local post process for only print and pdf.
 456     //we do need special formatted o/p only when we do have grouping
 457     $orderBys = CRM_Utils_Array::value('order_bys', $this->_params, array());
 458     if (in_array($this->_outputMode, array(
 459       'print',
 460       'pdf',
 461     ))) {
 462
 463       $outPut = array();
 464       $templateFile = parent::getTemplateFileName();
 465       if (array_key_exists('street_name', $orderBys) ||
 466         array_key_exists('street_number', $orderBys)
 467       ) {
 468         $orderByStreetName = CRM_Utils_Array::value('street_name', $orderBys);
 469         $orderByStreetNum = CRM_Utils_Array::value('street_number', $orderBys);
 470
 471         $pageCnt = 0;
 472         $dataPerPage = array();
 473         $lastStreetName = $lastStreetNum = NULL;
 474         foreach ($rows as $row) {
 475           //do we need to take new page.
 476           if ($orderByStreetName &&
 477             ($lastStreetName !=
 478               CRM_Utils_Array::value('civicrm_address_street_name', $row))
 479           ) {
 480             $pageCnt++;
 481           }
 482           elseif ($orderByStreetNum &&
 483             ($lastStreetNum !=
 484               CRM_Utils_Array::value('civicrm_address_street_number', $row) % 2
 485             )
 486           ) {
 487             $pageCnt++;
 488           }
 489
 490           //get the data per page.
 491           $dataPerPage[$pageCnt][] = $row;
 492           $lastStreetName = CRM_Utils_Array::value('civicrm_address_street_name', $row);
 493           $lastStreetNum = CRM_Utils_Array::value('civicrm_address_street_number', $row) % 2;
 494         }
 495
 496         foreach ($dataPerPage as $page) {
 497           // assign variables to templates
 498           $this->doTemplateAssignment($page);
 499           $outPut[] = CRM_Core_Form::$_template->fetch($templateFile);
 500         }
 501       }
 502       else {
 503         $this->doTemplateAssignment($rows);
 504         $outPut[] = CRM_Core_Form::$_template->fetch($templateFile);
 505       }
 506
 507       $header = $this->_formValues['report_header'];
 508       $footer = $this->_formValues['report_footer'];
 509
 510       //get the cover sheet.
 511       $coverSheet = $this->_surveyCoverSheet();
 512       $footerImage = preg_replace('/<\/html>|<\/body>|<\/div>/i', '', $footer);
 513
 514       $outPut = $header . $coverSheet .
 515         "<div style=\"page-break-after: always\"></div>" .
 516         implode($footerImage .
 517           "<div style=\"page-break-after: always\"></div>",
 518           $outPut
 519         ) . $footer;
 520
 521       if ($this->_outputMode == 'print') {
 522         echo $outPut;
 523       }
 524       else {
 525         CRM_Utils_PDF_Utils::html2pdf($outPut, "CiviReport.pdf");
 526       }
 527
 528       CRM_Utils_System::civiExit();
 529     }
 530     else {
 531       $this->doTemplateAssignment($rows);
 532       $this->endPostProcess($rows);
 533     }
 534
 535   }
 536
 537   /**
 538    * @return bool|mixed|null|string
 539    */
 540   private function _surveyCoverSheet() {
 541     $coverSheet = NULL;
 542     $surveyIds = CRM_Utils_Array::value('survey_id_value', $this->_params);
 543     if (CRM_Utils_System::isNull($surveyIds)) {
 544       return $coverSheet;
 545     }
 546
 547     $fieldIds = array();
 548
 549     $surveyResponseFields = array();
 550     foreach ($this->_columns as $tableName => $values) {
 551       if (!is_array($values['fields'])) {
 552         continue;
 553       }
 554       foreach ($values['fields'] as $name => $field) {
 555         if (!empty($field['isSurveyResponseField'])) {
 556           $fldId = substr($name, 7);
 557           $fieldIds[$fldId] = $fldId;
 558           $title = CRM_Utils_Array::value('label', $field, $field['title']);
 559           $surveyResponseFields[$name] = array(
 560             'id' => $fldId,
 561             'title' => $title,
 562             'name' => "{$tableName}_{$name}",
 563           );
 564         }
 565       }
 566     }
 567
 568     //now pickup all options.
 569     if (!empty($fieldIds)) {
 570       $query = '
 571     SELECT  field.id as id,
 572             val.label as label,
 573             val.value as value
 574       FROM  civicrm_custom_field field
 575 INNER JOIN  civicrm_option_value val ON ( val.option_group_id = field.option_group_id )
 576      WHERE  field.id IN (' . implode(' , ', $fieldIds) . ' )
 577   Order By  val.weight';
 578       $field = CRM_Core_DAO::executeQuery($query);
 579       $options = array();
 580       while ($field->fetch()) {
 581         $name = "custom_{$field->id}";
 582         $surveyResponseFields[$name]['options'][$field->value] = $field->label;
 583       }
 584     }
 585
 586     //get the result values.
 587     $query = '
 588     SELECT  survey.id as id,
 589             survey.title as title,
 590             val.label as label,
 591             val.value as value
 592       FROM  civicrm_survey survey
 593 INNER JOIN  civicrm_option_value val ON ( val.option_group_id = survey.result_id )
 594      WHERE  survey.id IN ( ' . implode(' , ', array_values($surveyIds)) . ' )
 595   Order By  val.weight';
 596     $resultSet = CRM_Core_DAO::executeQuery($query);
 597     $surveyResultFields = array();
 598     while ($resultSet->fetch()) {
 599       $surveyResultFields[$resultSet->id]['title'] = $resultSet->title;
 600       $surveyResultFields[$resultSet->id]['options'][$resultSet->value] = $resultSet->label;
 601     }
 602
 603     $this->assign('surveyResultFields', $surveyResultFields);
 604     $this->assign('surveyResponseFields', $surveyResponseFields);
 605
 606     $templateFile = 'CRM/Report/Form/Campaign/SurveyCoverSheet.tpl';
 607     $coverSheet = CRM_Core_Form::$_template->fetch($templateFile);
 608
 609     return $coverSheet;
 610   }
 611
 612   /**
 613    * Alter display of rows.
 614    *
 615    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 616    * such as rendering contacts as links.
 617    *
 618    * @param array $rows
 619    *   Rows generated by SQL, with an array for each row.
 620    */
 621   public function alterDisplay(&$rows) {
 622
 623     $this->_formatSurveyResult($rows);
 624     $this->_formatSurveyResponseData($rows);
 625
 626     $entryFound = FALSE;
 627     foreach ($rows as $rowNum => $row) {
 628       // handle state province
 629       if (array_key_exists('civicrm_address_state_province_id', $row)) {
 630         if ($value = $row['civicrm_address_state_province_id']) {
 631           $rows[$rowNum]['civicrm_address_state_province_id'] = CRM_Core_PseudoConstant::stateProvince($value);
 632         }
 633         $entryFound = TRUE;
 634       }
 635
 636       // handle country
 637       if (array_key_exists('civicrm_address_country_id', $row)) {
 638         if ($value = $row['civicrm_address_country_id']) {
 639           $rows[$rowNum]['civicrm_address_country_id'] = CRM_Core_PseudoConstant::country($value);
 640         }
 641         $entryFound = TRUE;
 642       }
 643
 644       // convert display name to links
 645       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 646         array_key_exists('civicrm_contact_id', $row)
 647       ) {
 648         $url = CRM_Report_Utils_Report::getNextUrl('contact/detail',
 649           'reset=1&force=1&id_op=eq&id_value=' .
 650           $row['civicrm_contact_id'],
 651           $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 652         );
 653         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 654         $entryFound = TRUE;
 655       }
 656
 657       if (array_key_exists('civicrm_activity_contact_contact_id', $row)) {
 658         $rows[$rowNum]['civicrm_activity_contact_contact_id'] = CRM_Utils_Array::value($row['civicrm_activity_contact_contact_id'],
 659           CRM_Campaign_BAO_Survey::getInterviewers()
 660         );
 661         $entryFound = TRUE;
 662       }
 663
 664       if (array_key_exists('civicrm_activity_survey_id', $row)) {
 665         $rows[$rowNum]['civicrm_activity_survey_id'] = CRM_Utils_Array::value($row['civicrm_activity_survey_id'],
 666           CRM_Campaign_BAO_Survey::getSurveys()
 667         );
 668         $entryFound = TRUE;
 669       }
 670
 671       // skip looking further in rows, if first row itself doesn't
 672       // have the column we need
 673       if (!$entryFound) {
 674         break;
 675       }
 676     }
 677   }
 678
 679   /**
 680    * @param $rows
 681    */
 682   private function _formatSurveyResult(&$rows) {
 683     $surveyIds = CRM_Utils_Array::value('survey_id_value', $this->_params);
 684     if (CRM_Utils_System::isNull($surveyIds) ||
 685       empty($this->_params['fields']['result']) ||
 686       !in_array($this->_outputMode, array('print', 'pdf'))
 687     ) {
 688       return;
 689     }
 690
 691     //swap the survey result label w/ value.
 692     $query = '
 693     SELECT  survey.id as id,
 694             val.label as label,
 695             val.value as value
 696       FROM  civicrm_option_value val
 697 INNER JOIN  civicrm_option_group grp ON ( grp.id = val.option_group_id )
 698 INNER JOIN  civicrm_survey survey ON ( survey.result_id = grp.id )
 699      WHERE  survey.id IN (' . implode(' , ', array_values($surveyIds)) . ' )
 700   Order By  val.weight';
 701
 702     $result = CRM_Core_DAO::executeQuery($query);
 703     $resultSet = array();
 704     while ($result->fetch()) {
 705       $resultSet[$result->id][$result->value] = $result->label;
 706     }
 707
 708     $statusId = CRM_Utils_Array::value('status_id_value', $this->_params);
 709     $respondentStatus = CRM_Utils_Array::value($statusId, self::$_surveyRespondentStatus);
 710
 711     $surveyId = CRM_Utils_Array::value(0, $surveyIds);
 712     foreach ($rows as & $row) {
 713       if (!empty($row['civicrm_activity_survey_id'])) {
 714         $surveyId = $row['civicrm_activity_survey_id'];
 715       }
 716       $result = CRM_Utils_Array::value($surveyId, $resultSet, array());
 717       $resultLabel = CRM_Utils_Array::value('civicrm_activity_result', $row);
 718       if ($respondentStatus == 'Reserved') {
 719         $row['civicrm_activity_result'] = implode(' | ', array_keys($result));
 720       }
 721       elseif ($resultLabel) {
 722         $resultValue = array_search($resultLabel, $result);
 723         if ($resultValue) {
 724           $row['civicrm_activity_result'] = $resultValue;
 725         }
 726       }
 727     }
 728   }
 729
 730   /**
 731    * @param $rows
 732    */
 733   private function _formatSurveyResponseData(&$rows) {
 734     $surveyIds = CRM_Utils_Array::value('survey_id_value', $this->_params);
 735     if (CRM_Utils_System::isNull($surveyIds) ||
 736       empty($this->_params['fields']['survey_response'])
 737     ) {
 738       return;
 739     }
 740
 741     $surveyResponseFields = array();
 742     $surveyResponseFieldIds = array();
 743     foreach ($this->_columns as $tableName => $values) {
 744       if (!is_array($values['fields'])) {
 745         continue;
 746       }
 747       foreach ($values['fields'] as $name => $field) {
 748         if (!empty($field['isSurveyResponseField'])) {
 749           $fldId = substr($name, 7);
 750           $surveyResponseFields[$name] = "{$tableName}_{$name}";
 751           $surveyResponseFieldIds[$fldId] = $fldId;
 752         }
 753       }
 754     }
 755
 756     if (empty($surveyResponseFieldIds)) {
 757       return;
 758     }
 759
 760     $hasResponseData = FALSE;
 761     foreach ($surveyResponseFields as $fldName) {
 762       foreach ($rows as $row) {
 763         if (!empty($row[$fldName])) {
 764           $hasResponseData = TRUE;
 765           break;
 766         }
 767       }
 768     }
 769
 770     //do check respondent status.
 771     $statusId = CRM_Utils_Array::value('status_id_value', $this->_params);
 772     $respondentStatus = CRM_Utils_Array::value($statusId, self::$_surveyRespondentStatus);
 773
 774     if (!$hasResponseData &&
 775       ($respondentStatus != 'Reserved')
 776     ) {
 777       return;
 778     }
 779
 780     //start response data formatting.
 781     $query = '
 782     SELECT  cf.id,
 783             cf.data_type,
 784             cf.html_type,
 785             cg.table_name,
 786             cf.column_name,
 787             ov.value, ov.label,
 788             cf.option_group_id
 789       FROM  civicrm_custom_field cf
 790 INNER JOIN  civicrm_custom_group cg ON ( cg.id = cf.custom_group_id )
 791  LEFT JOIN  civicrm_option_value ov ON ( cf.option_group_id = ov.option_group_id )
 792      WHERE  cf.id IN ( ' . implode(' , ', $surveyResponseFieldIds) . ' )
 793   Order By  ov.weight';
 794
 795     $responseFields = array();
 796     $fieldValueMap = array();
 797     $properties = array(
 798       'id',
 799       'data_type',
 800       'html_type',
 801       'column_name',
 802       'option_group_id',
 803     );
 804
 805     $responseField = CRM_Core_DAO::executeQuery($query);
 806     while ($responseField->fetch()) {
 807       $reponseFldName = $responseField->table_name . '_custom_' .
 808         $responseField->id;
 809       foreach ($properties as $prop) {
 810         $responseFields[$reponseFldName][$prop] = $responseField->$prop;
 811       }
 812       if ($responseField->option_group_id) {
 813         //show value for print and pdf.
 814         $value = $responseField->label;
 815         if (in_array($this->_outputMode, array(
 816           'print',
 817           'pdf',
 818         ))) {
 819           $value = $responseField->value;
 820         }
 821         $fieldValueMap[$responseField->option_group_id][$responseField->value] = $value;
 822       }
 823     }
 824     $responseField->free();
 825
 826     //actual data formatting.
 827     $hasData = FALSE;
 828     foreach ($rows as & $row) {
 829       if (!is_array($row)) {
 830         continue;
 831       }
 832
 833       foreach ($row as $name => & $value) {
 834         if (!array_key_exists($name, $responseFields)) {
 835           continue;
 836         }
 837         $hasData = TRUE;
 838         if ($respondentStatus == 'Reserved' &&
 839           in_array($this->_outputMode, array('print', 'pdf'))
 840         ) {
 841           $optGrpId = CRM_Utils_Array::value('option_group_id', $responseFields[$name]);
 842           $options = CRM_Utils_Array::value($optGrpId, $fieldValueMap, array());
 843           $value = implode(' | ', array_keys($options));
 844         }
 845         else {
 846           $value = CRM_Core_BAO_CustomField::displayValue($value, $responseFields[$name]['id']);
 847         }
 848       }
 849
 850       if (!$hasData) {
 851         break;
 852       }
 853     }
 854   }
 855
 856   private function _addSurveyResponseColumns() {
 857     $surveyIds = CRM_Utils_Array::value('survey_id_value', $this->_params);
 858     if (CRM_Utils_System::isNull($surveyIds) ||
 859       empty($this->_params['fields']['survey_response'])
 860     ) {
 861       return;
 862     }
 863
 864     $responseFields = array();
 865     foreach ($surveyIds as $surveyId) {
 866       $responseFields += CRM_Campaign_BAO_Survey::getSurveyResponseFields($surveyId);
 867       $this->_surveyResponseFields = $responseFields;
 868     }
 869     foreach ($responseFields as $key => $value) {
 870       if (substr($key, 0, 5) == 'phone' && !empty($value['location_type_id'])) {
 871         $fName = str_replace('-', '_', $key);
 872         $this->_columns["civicrm_{$fName}"] = array(
 873           'dao' => 'CRM_Core_DAO_Phone',
 874           'alias' => "phone_civireport_{$fName}",
 875           'fields' => array(
 876             $fName => array_merge($value, array(
 877                 'is_required' => '1',
 878                 'alias' => "phone_civireport_{$fName}",
 879                 'dbAlias' => "phone_civireport_{$fName}.phone",
 880                 'no_display' => TRUE,
 881               )
 882             ),
 883           ),
 884         );
 885         $this->_aliases["civicrm_phone_{$fName}"] = $this->_columns["civicrm_{$fName}"]['alias'];
 886         $this->_locationBasedPhoneField = TRUE;
 887       }
 888     }
 889     $responseFieldIds = array();
 890     foreach (array_keys($responseFields) as $key) {
 891       $cfId = CRM_Core_BAO_CustomField::getKeyID($key);
 892       if ($cfId) {
 893         $responseFieldIds[$cfId] = $cfId;
 894       }
 895     }
 896     if (empty($responseFieldIds)) {
 897       return;
 898     }
 899
 900     $query = '
 901      SELECT  cg.extends,
 902              cf.data_type,
 903              cf.html_type,
 904              cg.table_name,
 905              cf.column_name,
 906              cf.time_format,
 907              cf.id as cfId,
 908              cf.option_group_id
 909        FROM  civicrm_custom_group cg
 910 INNER  JOIN  civicrm_custom_field cf ON ( cg.id = cf.custom_group_id )
 911       WHERE  cf.id IN ( ' . implode(' , ', $responseFieldIds) .
 912       ' )  ORDER BY cf.weight';
 913     $response = CRM_Core_DAO::executeQuery($query);
 914     $fildCnt = 1;
 915     while ($response->fetch()) {
 916       $resTable = $response->table_name;
 917       $fieldName = "custom_{$response->cfId}";
 918
 919       //need to check does these custom data already included.
 920
 921       if (!array_key_exists($resTable, $this->_columns)) {
 922         $this->_columns[$resTable]['dao'] = 'CRM_Contact_DAO_Contact';
 923         $this->_columns[$resTable]['extends'] = $response->extends;
 924       }
 925       if (empty($this->_columns[$resTable]['alias'])) {
 926         $this->_columns[$resTable]['alias'] = "{$resTable}_survey_response";
 927       }
 928       if (!is_array(CRM_Utils_Array::value('fields', $this->_columns[$resTable]))) {
 929         $this->_columns[$resTable]['fields'] = array();
 930       }
 931       if (array_key_exists($fieldName, $this->_columns[$resTable]['fields'])) {
 932         $this->_columns[$resTable]['fields'][$fieldName]['required'] = TRUE;
 933         $this->_columns[$resTable]['fields'][$fieldName]['isSurveyResponseField'] = TRUE;
 934         continue;
 935       }
 936
 937       $title = $responseFields[$fieldName]['title'];
 938       if (in_array($this->_outputMode, array(
 939         'print',
 940         'pdf',
 941       ))) {
 942         $title = 'Q' . $fildCnt++;
 943       }
 944
 945       $fldType = 'CRM_Utils_Type::T_STRING';
 946       if ($response->time_format) {
 947         $fldType = CRM_Utils_Type::T_TIMESTAMP;
 948       }
 949       $field = array(
 950         'name' => $response->column_name,
 951         'type' => $fldType,
 952         'title' => $title,
 953         'label' => $responseFields[$fieldName]['title'],
 954         'dataType' => $response->data_type,
 955         'htmlType' => $response->html_type,
 956         'required' => TRUE,
 957         'alias' => ($response->data_type == 'ContactReference') ? $this->_columns[$resTable]['alias'] .
 958         '_contact' : $this->_columns[$resTable]['alias'],
 959         'dbAlias' => $this->_columns[$resTable]['alias'] . '.' .
 960         $response->column_name,
 961         'no_display' => TRUE,
 962         'isSurveyResponseField' => TRUE,
 963       );
 964
 965       $this->_columns[$resTable]['fields'][$fieldName] = $field;
 966       $this->_aliases[$resTable] = $this->_columns[$resTable]['alias'];
 967     }
 968   }
 969
 970 }