CRM/Report/Form/Campaign/SurveyDetails.php

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