crm.fsf.org/20131203/files/sites/all/modules-old/civicrm/CRM/Report/Form/Campaign/SurveyDetails.php

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