CRM/Report/Form/Event/ParticipantListing.php

   1 <?php
   2 /*
   3   +--------------------------------------------------------------------+
   4   | CiviCRM version 5                                                  |
   5   +--------------------------------------------------------------------+
   6   | Copyright CiviCRM LLC (c) 2004-2018                                |
   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-2018
  32  */
  33 class CRM_Report_Form_Event_ParticipantListing extends CRM_Report_Form_Event {
  34
  35   protected $_summary = NULL;
  36
  37   protected $_contribField = FALSE;
  38   protected $_groupFilter = TRUE;
  39   protected $_tagFilter = TRUE;
  40   protected $_balance = FALSE;
  41   protected $activeCampaigns;
  42
  43   protected $_customGroupExtends = array(
  44     'Participant',
  45     'Contact',
  46     'Individual',
  47     'Event',
  48   );
  49
  50   public $_drilldownReport = array('event/income' => 'Link to Detail Report');
  51
  52   /**
  53    * Class constructor.
  54    */
  55   public function __construct() {
  56     $this->_autoIncludeIndexedFieldsAsOrderBys = 1;
  57
  58     // Check if CiviCampaign is a) enabled and b) has active campaigns
  59     $config = CRM_Core_Config::singleton();
  60     $campaignEnabled = in_array("CiviCampaign", $config->enableComponents);
  61     if ($campaignEnabled) {
  62       $getCampaigns = CRM_Campaign_BAO_Campaign::getPermissionedCampaigns(NULL, NULL, TRUE, FALSE, TRUE);
  63       $this->activeCampaigns = $getCampaigns['campaigns'];
  64       asort($this->activeCampaigns);
  65     }
  66
  67     $this->_columns = array(
  68       'civicrm_contact' => array(
  69         'dao' => 'CRM_Contact_DAO_Contact',
  70         'fields' => array_merge(array(
  71           // CRM-17115 - to avoid changing report output at this stage re-instate
  72           // old field name for sort name
  73           'sort_name_linked' => array(
  74             'title' => ts('Participant Name'),
  75             'required' => TRUE,
  76             'no_repeat' => TRUE,
  77             'dbAlias' => 'contact_civireport.sort_name',
  78           )),
  79           $this->getBasicContactFields(),
  80           array(
  81             'age_at_event' => array(
  82               'title' => ts('Age at Event'),
  83               'dbAlias' => 'TIMESTAMPDIFF(YEAR, contact_civireport.birth_date, event_civireport.start_date)',
  84             ),
  85           )
  86         ),
  87         'grouping' => 'contact-fields',
  88         'order_bys' => array(
  89           'sort_name' => array(
  90             'title' => ts('Last Name, First Name'),
  91             'default' => '1',
  92             'default_weight' => '0',
  93             'default_order' => 'ASC',
  94           ),
  95           'first_name' => array(
  96             'name' => 'first_name',
  97             'title' => ts('First Name'),
  98           ),
  99           'gender_id' => array(
 100             'name' => 'gender_id',
 101             'title' => ts('Gender'),
 102           ),
 103           'birth_date' => array(
 104             'name' => 'birth_date',
 105             'title' => ts('Birth Date'),
 106           ),
 107           'age_at_event' => array(
 108             'name' => 'age_at_event',
 109             'title' => ts('Age at Event'),
 110           ),
 111           'contact_type' => array(
 112             'title' => ts('Contact Type'),
 113           ),
 114           'contact_sub_type' => array(
 115             'title' => ts('Contact Subtype'),
 116           ),
 117         ),
 118         'filters' => CRM_Report_Form::getBasicContactFilters(),
 119       ),
 120       'civicrm_email' => array(
 121         'dao' => 'CRM_Core_DAO_Email',
 122         'fields' => array(
 123           'email' => array(
 124             'title' => ts('Email'),
 125             'no_repeat' => TRUE,
 126           ),
 127         ),
 128         'grouping' => 'contact-fields',
 129         'filters' => array(
 130           'email' => array(
 131             'title' => ts('Participant E-mail'),
 132             'operator' => 'like',
 133           ),
 134         ),
 135       ),
 136     );
 137     $this->_columns += $this->getAddressColumns();
 138     $this->_columns += array(
 139       'civicrm_participant' => array(
 140         'dao' => 'CRM_Event_DAO_Participant',
 141         'fields' => array(
 142           'participant_id' => array('title' => ts('Participant ID')),
 143           'participant_record' => array(
 144             'name' => 'id',
 145             'no_display' => TRUE,
 146             'required' => TRUE,
 147           ),
 148           'event_id' => array(
 149             'default' => TRUE,
 150             'type' => CRM_Utils_Type::T_STRING,
 151           ),
 152           'status_id' => array(
 153             'title' => ts('Status'),
 154             'default' => TRUE,
 155           ),
 156           'role_id' => array(
 157             'title' => ts('Role'),
 158             'default' => TRUE,
 159           ),
 160           'fee_currency' => array(
 161             'required' => TRUE,
 162             'no_display' => TRUE,
 163           ),
 164           'registered_by_id' => array(
 165             'title' => ts('Registered by Participant ID'),
 166           ),
 167           'source' => array(
 168             'title' => ts('Source'),
 169           ),
 170           'participant_fee_level' => NULL,
 171           'participant_fee_amount' => array('title' => ts('Participant Fee')),
 172           'participant_register_date' => array('title' => ts('Registration Date')),
 173           'total_paid' => array(
 174             'title' => ts('Total Paid'),
 175             'dbAlias' => 'IFNULL(SUM(ft.total_amount), 0)',
 176             'type' => 1024,
 177           ),
 178           'balance' => array(
 179             'title' => ts('Balance'),
 180             'dbAlias' => 'participant_civireport.fee_amount - IFNULL(SUM(ft.total_amount), 0)',
 181             'type' => 1024,
 182           ),
 183         ),
 184         'grouping' => 'event-fields',
 185         'filters' => array(
 186           'event_id' => array(
 187             'name' => 'event_id',
 188             'title' => ts('Event'),
 189             'operatorType' => CRM_Report_Form::OP_ENTITYREF,
 190             'type' => CRM_Utils_Type::T_INT,
 191             'attributes' => array(
 192               'entity' => 'event',
 193               'select' => array('minimumInputLength' => 0),
 194             ),
 195           ),
 196           'sid' => array(
 197             'name' => 'status_id',
 198             'title' => ts('Participant Status'),
 199             'type' => CRM_Utils_Type::T_INT,
 200             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 201             'options' => CRM_Event_PseudoConstant::participantStatus(NULL, NULL, 'label'),
 202           ),
 203           'rid' => array(
 204             'name' => 'role_id',
 205             'title' => ts('Participant Role'),
 206             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 207             'options' => CRM_Event_PseudoConstant::participantRole(),
 208           ),
 209           'participant_register_date' => array(
 210             'title' => ts('Registration Date'),
 211             'operatorType' => CRM_Report_Form::OP_DATE,
 212           ),
 213           'fee_currency' => array(
 214             'title' => ts('Fee Currency'),
 215             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 216             'options' => CRM_Core_OptionGroup::values('currencies_enabled'),
 217             'default' => NULL,
 218             'type' => CRM_Utils_Type::T_STRING,
 219           ),
 220           'registered_by_id' => array(
 221             'title' => ts('Registered by Participant ID'),
 222             'type' => CRM_Utils_Type::T_STRING,
 223             'operator' => 'like',
 224           ),
 225           'source' => array(
 226             'title' => ts('Source'),
 227             'type' => CRM_Utils_Type::T_STRING,
 228             'operator' => 'like',
 229           ),
 230         ),
 231         'order_bys' => array(
 232           'participant_register_date' => array(
 233             'title' => ts('Registration Date'),
 234             'default_weight' => '1',
 235             'default_order' => 'ASC',
 236           ),
 237           'event_id' => array(
 238             'title' => ts('Event'),
 239             'default_weight' => '1',
 240             'default_order' => 'ASC',
 241           ),
 242         ),
 243       ),
 244       'civicrm_phone' => array(
 245         'dao' => 'CRM_Core_DAO_Phone',
 246         'fields' => array(
 247           'phone' => array(
 248             'title' => ts('Phone'),
 249             'default' => TRUE,
 250             'no_repeat' => TRUE,
 251           ),
 252         ),
 253         'grouping' => 'contact-fields',
 254       ),
 255       'civicrm_event' => array(
 256         'dao' => 'CRM_Event_DAO_Event',
 257         'fields' => array(
 258           'event_type_id' => array(
 259             'title' => ts('Event Type'),
 260           ),
 261           'event_start_date' => array(
 262             'title' => ts('Event Start Date'),
 263           ),
 264           'event_end_date' => array(
 265             'title' => ts('Event End Date'),
 266           ),
 267         ),
 268         'grouping' => 'event-fields',
 269         'filters' => array(
 270           'eid' => array(
 271             'name' => 'event_type_id',
 272             'title' => ts('Event Type'),
 273             'type' => CRM_Utils_Type::T_INT,
 274             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 275             'options' => CRM_Core_OptionGroup::values('event_type'),
 276           ),
 277           'event_start_date' => array(
 278             'title' => ts('Event Start Date'),
 279             'operatorType' => CRM_Report_Form::OP_DATE,
 280           ),
 281           'event_end_date' => array(
 282             'title' => ts('Event End Date'),
 283             'operatorType' => CRM_Report_Form::OP_DATE,
 284           ),
 285         ),
 286         'order_bys' => array(
 287           'event_type_id' => array(
 288             'title' => ts('Event Type'),
 289             'default_weight' => '2',
 290             'default_order' => 'ASC',
 291           ),
 292           'event_start_date' => array(
 293             'title' => ts('Event Start Date'),
 294           ),
 295         ),
 296       ),
 297       'civicrm_note' => array(
 298         'dao' => 'CRM_Core_DAO_Note',
 299         'fields' => array(
 300           'participant_note' => array(
 301             'name' => 'note',
 302             'title' => ts('Participant Note'),
 303           ),
 304         ),
 305       ),
 306       'civicrm_contribution' => array(
 307         'dao' => 'CRM_Contribute_DAO_Contribution',
 308         'fields' => array(
 309           'contribution_id' => array(
 310             'name' => 'id',
 311             'no_display' => TRUE,
 312             'required' => TRUE,
 313             'csv_display' => TRUE,
 314             'title' => ts('Contribution ID'),
 315           ),
 316           'financial_type_id' => array('title' => ts('Financial Type')),
 317           'receive_date' => array('title' => ts('Payment Date')),
 318           'contribution_status_id' => array('title' => ts('Contribution Status')),
 319           'payment_instrument_id' => array('title' => ts('Payment Type')),
 320           'contribution_source' => array(
 321             'name' => 'source',
 322             'title' => ts('Contribution Source'),
 323           ),
 324           'currency' => array(
 325             'required' => TRUE,
 326             'no_display' => TRUE,
 327           ),
 328           'trxn_id' => NULL,
 329           'fee_amount' => array('title' => ts('Transaction Fee')),
 330           'net_amount' => NULL,
 331         ),
 332         'grouping' => 'contrib-fields',
 333         'filters' => array(
 334           'receive_date' => array(
 335             'title' => ts('Payment Date'),
 336             'operatorType' => CRM_Report_Form::OP_DATE,
 337           ),
 338           'financial_type_id' => array(
 339             'title' => ts('Financial Type'),
 340             'type' => CRM_Utils_Type::T_INT,
 341             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 342             'options' => CRM_Contribute_PseudoConstant::financialType(),
 343           ),
 344           'currency' => array(
 345             'title' => ts('Contribution Currency'),
 346             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 347             'options' => CRM_Core_OptionGroup::values('currencies_enabled'),
 348             'default' => NULL,
 349             'type' => CRM_Utils_Type::T_STRING,
 350           ),
 351           'payment_instrument_id' => array(
 352             'title' => ts('Payment Type'),
 353             'type' => CRM_Utils_Type::T_INT,
 354             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 355             'options' => CRM_Contribute_PseudoConstant::paymentInstrument(),
 356           ),
 357           'contribution_status_id' => array(
 358             'title' => ts('Contribution Status'),
 359             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 360             'options' => CRM_Contribute_PseudoConstant::contributionStatus(),
 361             'default' => NULL,
 362           ),
 363         ),
 364       ),
 365       'civicrm_line_item' => array(
 366         'dao' => 'CRM_Price_DAO_LineItem',
 367         'grouping' => 'priceset-fields',
 368         'filters' => array(
 369           'price_field_value_id' => array(
 370             'name' => 'price_field_value_id',
 371             'title' => ts('Fee Level'),
 372             'type' => CRM_Utils_Type::T_INT,
 373             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 374             'options' => $this->getPriceLevels(),
 375           ),
 376         ),
 377       ),
 378     );
 379
 380     $this->_options = array(
 381       'blank_column_begin' => array(
 382         'title' => ts('Blank column at the Begining'),
 383         'type' => 'checkbox',
 384       ),
 385       'blank_column_end' => array(
 386         'title' => ts('Blank column at the End'),
 387         'type' => 'select',
 388         'options' => array(
 389           '' => ts('-select-'),
 390           1 => ts('One'),
 391           2 => ts('Two'),
 392           3 => ts('Three'),
 393         ),
 394       ),
 395     );
 396
 397     // CRM-17115 avoid duplication of sort_name - would be better to standardise name
 398     // & behaviour across reports but trying for no change at this point.
 399     $this->_columns['civicrm_contact']['fields']['sort_name']['no_display'] = TRUE;
 400
 401     // If we have active campaigns add those elements to both the fields and filters
 402     if ($campaignEnabled && !empty($this->activeCampaigns)) {
 403       $this->_columns['civicrm_participant']['fields']['campaign_id'] = array(
 404         'title' => ts('Campaign'),
 405         'default' => 'false',
 406       );
 407       $this->_columns['civicrm_participant']['filters']['campaign_id'] = array(
 408         'title' => ts('Campaign'),
 409         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 410         'options' => $this->activeCampaigns,
 411         'type' => CRM_Utils_Type::T_INT,
 412       );
 413       $this->_columns['civicrm_participant']['order_bys']['campaign_id'] = array(
 414         'title' => ts('Campaign'),
 415       );
 416     }
 417
 418     $this->_currencyColumn = 'civicrm_participant_fee_currency';
 419     parent::__construct();
 420   }
 421
 422   /**
 423    * Searches database for priceset values.
 424    *
 425    * @return array
 426    */
 427   public function getPriceLevels() {
 428     $query = "
 429 SELECT CONCAT(cv.label, ' (', ps.title, ' - ', cf.label , ')') label, cv.id
 430 FROM civicrm_price_field_value cv
 431 LEFT JOIN civicrm_price_field cf
 432   ON cv.price_field_id = cf.id
 433 LEFT JOIN civicrm_price_set_entity ce
 434   ON ce.price_set_id = cf.price_set_id
 435 LEFT JOIN civicrm_price_set ps
 436   ON ce.price_set_id = ps.id
 437 WHERE ce.entity_table = 'civicrm_event'
 438 ORDER BY  cv.label
 439 ";
 440     $dao = CRM_Core_DAO::executeQuery($query);
 441     $elements = array();
 442     while ($dao->fetch()) {
 443       $elements[$dao->id] = "$dao->label\n";
 444     }
 445
 446     return $elements;
 447   }
 448
 449   public function preProcess() {
 450     parent::preProcess();
 451   }
 452
 453   public function select() {
 454     $select = array();
 455     $this->_columnHeaders = array();
 456
 457     //add blank column at the Start
 458     if (array_key_exists('options', $this->_params) &&
 459       !empty($this->_params['options']['blank_column_begin'])
 460     ) {
 461       $select[] = " '' as blankColumnBegin";
 462       $this->_columnHeaders['blankColumnBegin']['title'] = '_ _ _ _';
 463     }
 464     foreach ($this->_columns as $tableName => $table) {
 465       if (array_key_exists('fields', $table)) {
 466         foreach ($table['fields'] as $fieldName => $field) {
 467           if (!empty($field['required']) ||
 468             !empty($this->_params['fields'][$fieldName])
 469           ) {
 470             if ($tableName == 'civicrm_contribution') {
 471               $this->_contribField = TRUE;
 472             }
 473             if ($fieldName == 'total_paid' || $fieldName == 'balance') {
 474               $this->_balance = TRUE;
 475               // modify the select if filtered by fee_level as the from clause
 476               // already selects the total_amount from civicrm_contribution table
 477               if (!empty($this->_params['price_field_value_id_value'])) {
 478                 $field['dbAlias'] = str_replace('SUM(ft.total_amount)', 'ft.total_amount', $field['dbAlias']);
 479               }
 480             }
 481             $alias = "{$tableName}_{$fieldName}";
 482             $select[] = "{$field['dbAlias']} as $alias";
 483             $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 484             $this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = CRM_Utils_Array::value('no_display', $field);
 485             $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 486             $this->_selectAliases[] = $alias;
 487           }
 488         }
 489       }
 490     }
 491     //add blank column at the end
 492     $blankcols = CRM_Utils_Array::value('blank_column_end', $this->_params);
 493     if ($blankcols) {
 494       for ($i = 1; $i <= $blankcols; $i++) {
 495         $select[] = " '' as blankColumnEnd_{$i}";
 496         $this->_columnHeaders["blank_{$i}"]['title'] = "_ _ _ _";
 497       }
 498     }
 499
 500     $this->_selectClauses = $select;
 501     $this->_select = "SELECT " . implode(', ', $select) . " ";
 502   }
 503
 504   /**
 505    * @param $fields
 506    * @param $files
 507    * @param $self
 508    *
 509    * @return array
 510    */
 511   public static function formRule($fields, $files, $self) {
 512     $errors = $grouping = array();
 513     return $errors;
 514   }
 515
 516   public function from() {
 517     $this->_from = "
 518         FROM civicrm_participant {$this->_aliases['civicrm_participant']}
 519              LEFT JOIN civicrm_event {$this->_aliases['civicrm_event']}
 520                     ON ({$this->_aliases['civicrm_event']}.id = {$this->_aliases['civicrm_participant']}.event_id ) AND
 521                         {$this->_aliases['civicrm_event']}.is_template = 0
 522              LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
 523                     ON ({$this->_aliases['civicrm_participant']}.contact_id  = {$this->_aliases['civicrm_contact']}.id  )
 524              {$this->_aclFrom}
 525       ";
 526
 527     $this->joinAddressFromContact();
 528     $this->joinPhoneFromContact();
 529     $this->joinEmailFromContact();
 530
 531     // Include participant note.
 532     if ($this->isTableSelected('civicrm_note')) {
 533       $this->_from .= "
 534             LEFT JOIN civicrm_note {$this->_aliases['civicrm_note']}
 535                    ON ( {$this->_aliases['civicrm_note']}.entity_table = 'civicrm_participant' AND
 536                    {$this->_aliases['civicrm_participant']}.id = {$this->_aliases['civicrm_note']}.entity_id )";
 537     }
 538
 539     if ($this->_contribField) {
 540       $this->_from .= "
 541              LEFT JOIN civicrm_participant_payment pp
 542                     ON ({$this->_aliases['civicrm_participant']}.id  = pp.participant_id)
 543              LEFT JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
 544                     ON (pp.contribution_id  = {$this->_aliases['civicrm_contribution']}.id)
 545       ";
 546     }
 547     if (!empty($this->_params['price_field_value_id_value'])) {
 548       $this->_from .= "
 549             LEFT JOIN civicrm_line_item line_item_civireport
 550                   ON line_item_civireport.entity_table = 'civicrm_participant' AND
 551                      line_item_civireport.entity_id = {$this->_aliases['civicrm_participant']}.id AND
 552                      line_item_civireport.qty > 0
 553       ";
 554     }
 555     if ($this->_balance) {
 556       $this->_from .= "
 557             LEFT JOIN civicrm_entity_financial_trxn eft
 558                   ON (eft.entity_id = {$this->_aliases['civicrm_contribution']}.id)
 559             LEFT JOIN civicrm_financial_trxn ft
 560                   ON (ft.id = eft.financial_trxn_id AND eft.entity_table = 'civicrm_contribution') AND
 561                      (ft.is_payment = 1)
 562       ";
 563     }
 564   }
 565
 566   public function where() {
 567     $clauses = array();
 568     foreach ($this->_columns as $tableName => $table) {
 569       if (array_key_exists('filters', $table)) {
 570         foreach ($table['filters'] as $fieldName => $field) {
 571           $clause = NULL;
 572
 573           if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
 574             $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
 575             $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
 576             $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
 577
 578             if ($relative || $from || $to) {
 579               $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
 580             }
 581           }
 582           else {
 583             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 584
 585             if ($fieldName == 'rid') {
 586               $value = CRM_Utils_Array::value("{$fieldName}_value", $this->_params);
 587               if (!empty($value)) {
 588                 $operator = '';
 589                 if ($op == 'notin') {
 590                   $operator = 'NOT';
 591                 }
 592
 593                 $regexp = "[[:cntrl:]]*" . implode('[[:>:]]*|[[:<:]]*', $value) . "[[:cntrl:]]*";
 594                 $clause = "{$field['dbAlias']} {$operator} REGEXP '{$regexp}'";
 595               }
 596               $op = NULL;
 597             }
 598
 599             if ($op) {
 600               $clause = $this->whereClause($field,
 601                 $op,
 602                 CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 603                 CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 604                 CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 605               );
 606             }
 607           }
 608
 609           if (!empty($clause)) {
 610             $clauses[] = $clause;
 611           }
 612         }
 613       }
 614     }
 615     if (empty($clauses)) {
 616       $this->_where = "WHERE {$this->_aliases['civicrm_participant']}.is_test = 0 ";
 617     }
 618     else {
 619       $this->_where = "WHERE {$this->_aliases['civicrm_participant']}.is_test = 0 AND " .
 620         implode(' AND ', $clauses);
 621     }
 622     if ($this->_aclWhere) {
 623       $this->_where .= " AND {$this->_aclWhere} ";
 624     }
 625   }
 626
 627   public function groupBy() {
 628     $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, "{$this->_aliases['civicrm_participant']}.id");
 629   }
 630
 631   public function postProcess() {
 632     // get the acl clauses built before we assemble the query
 633     $this->buildACLClause($this->_aliases['civicrm_contact']);
 634     parent::postProcess();
 635   }
 636
 637   /**
 638    * @param $rows
 639    * @param $entryFound
 640    * @param $row
 641    * @param int $rowId
 642    * @param $rowNum
 643    * @param $types
 644    *
 645    * @return bool
 646    */
 647   private function _initBasicRow(&$rows, &$entryFound, $row, $rowId, $rowNum, $types) {
 648     if (!array_key_exists($rowId, $row)) {
 649       return FALSE;
 650     }
 651
 652     $value = $row[$rowId];
 653     if ($value) {
 654       $rows[$rowNum][$rowId] = $types[$value];
 655     }
 656     $entryFound = TRUE;
 657   }
 658
 659   /**
 660    * Alter display of rows.
 661    *
 662    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 663    * such as rendering contacts as links.
 664    *
 665    * @param array $rows
 666    *   Rows generated by SQL, with an array for each row.
 667    */
 668   public function alterDisplay(&$rows) {
 669     $entryFound = FALSE;
 670     $eventType = CRM_Core_OptionGroup::values('event_type');
 671
 672     $financialTypes = CRM_Contribute_PseudoConstant::financialType();
 673     $contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus();
 674     $paymentInstruments = CRM_Contribute_PseudoConstant::paymentInstrument();
 675
 676     foreach ($rows as $rowNum => $row) {
 677       // make count columns point to detail report
 678       // convert display name to links
 679       if (array_key_exists('civicrm_participant_event_id', $row)) {
 680         $eventId = $row['civicrm_participant_event_id'];
 681         if ($eventId) {
 682           $rows[$rowNum]['civicrm_participant_event_id'] = CRM_Event_PseudoConstant::event($eventId, FALSE);
 683
 684           $url = CRM_Report_Utils_Report::getNextUrl('event/income',
 685             'reset=1&force=1&id_op=in&id_value=' . $eventId,
 686             $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 687           );
 688           $rows[$rowNum]['civicrm_participant_event_id_link'] = $url;
 689           $rows[$rowNum]['civicrm_participant_event_id_hover'] = ts("View Event Income Details for this Event");
 690         }
 691         $entryFound = TRUE;
 692       }
 693
 694       // handle event type id
 695       $this->_initBasicRow($rows, $entryFound, $row, 'civicrm_event_event_type_id', $rowNum, $eventType);
 696
 697       // handle participant status id
 698       if (array_key_exists('civicrm_participant_status_id', $row)) {
 699         $statusId = $row['civicrm_participant_status_id'];
 700         if ($statusId) {
 701           $rows[$rowNum]['civicrm_participant_status_id'] = CRM_Event_PseudoConstant::participantStatus($statusId, FALSE, 'label');
 702         }
 703         $entryFound = TRUE;
 704       }
 705
 706       // handle participant role id
 707       if (array_key_exists('civicrm_participant_role_id', $row)) {
 708         $roleId = $row['civicrm_participant_role_id'];
 709         if ($roleId) {
 710           $roles = explode(CRM_Core_DAO::VALUE_SEPARATOR, $roleId);
 711           $roleId = array();
 712           foreach ($roles as $role) {
 713             $roleId[$role] = CRM_Event_PseudoConstant::participantRole($role, FALSE);
 714           }
 715           $rows[$rowNum]['civicrm_participant_role_id'] = implode(', ', $roleId);
 716         }
 717         $entryFound = TRUE;
 718       }
 719
 720       // Handel value seperator in Fee Level
 721       if (array_key_exists('civicrm_participant_participant_fee_level', $row)) {
 722         $feeLevel = $row['civicrm_participant_participant_fee_level'];
 723         if ($feeLevel) {
 724           CRM_Event_BAO_Participant::fixEventLevel($feeLevel);
 725           $rows[$rowNum]['civicrm_participant_participant_fee_level'] = $feeLevel;
 726         }
 727         $entryFound = TRUE;
 728       }
 729
 730       // Convert display name to link
 731       $displayName = CRM_Utils_Array::value('civicrm_contact_sort_name_linked', $row);
 732       $cid = CRM_Utils_Array::value('civicrm_contact_id', $row);
 733       $id = CRM_Utils_Array::value('civicrm_participant_participant_record', $row);
 734
 735       if ($displayName && $cid && $id) {
 736         $url = CRM_Report_Utils_Report::getNextUrl('contact/detail',
 737           "reset=1&force=1&id_op=eq&id_value=$cid",
 738           $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 739         );
 740
 741         $viewUrl = CRM_Utils_System::url("civicrm/contact/view/participant",
 742           "reset=1&id=$id&cid=$cid&action=view&context=participant"
 743         );
 744
 745         $contactTitle = ts('View Contact Details');
 746         $participantTitle = ts('View Participant Record');
 747
 748         $rows[$rowNum]['civicrm_contact_sort_name_linked'] = "<a title='$contactTitle' href=$url>$displayName</a>";
 749         // Add a "View" link to the participant record if this isn't a CSV/PDF/printed document.
 750         if ($this->_outputMode !== 'csv' && $this->_outputMode !== 'pdf' && $this->_outputMode !== 'print') {
 751           $rows[$rowNum]['civicrm_contact_sort_name_linked'] .=
 752             "<span style='float: right;'><a title='$participantTitle' href=$viewUrl>" .
 753             ts('View') . "</a></span>";
 754         }
 755         $entryFound = TRUE;
 756       }
 757
 758       // Handle employer id
 759       if (array_key_exists('civicrm_contact_employer_id', $row)) {
 760         $employerId = $row['civicrm_contact_employer_id'];
 761         if ($employerId) {
 762           $rows[$rowNum]['civicrm_contact_employer_id'] = CRM_Contact_BAO_Contact::displayName($employerId);
 763           $url = CRM_Utils_System::url('civicrm/contact/view',
 764             'reset=1&cid=' . $employerId, $this->_absoluteUrl
 765           );
 766           $rows[$rowNum]['civicrm_contact_employer_id_link'] = $url;
 767           $rows[$rowNum]['civicrm_contact_employer_id_hover'] = ts('View Contact Summary for this Contact.');
 768         }
 769       }
 770
 771       // Convert campaign_id to campaign title
 772       $this->_initBasicRow($rows, $entryFound, $row, 'civicrm_participant_campaign_id', $rowNum, $this->activeCampaigns);
 773
 774       // handle contribution status
 775       $this->_initBasicRow($rows, $entryFound, $row, 'civicrm_contribution_contribution_status_id', $rowNum, $contributionStatus);
 776
 777       // handle payment instrument
 778       $this->_initBasicRow($rows, $entryFound, $row, 'civicrm_contribution_payment_instrument_id', $rowNum, $paymentInstruments);
 779
 780       // handle financial type
 781       $this->_initBasicRow($rows, $entryFound, $row, 'civicrm_contribution_financial_type_id', $rowNum, $financialTypes);
 782
 783       $entryFound = $this->alterDisplayContactFields($row, $rows, $rowNum, 'event/participantListing', 'View Event Income Details') ? TRUE : $entryFound;
 784
 785       // display birthday in the configured custom format
 786       if (array_key_exists('civicrm_contact_birth_date', $row)) {
 787         $birthDate = $row['civicrm_contact_birth_date'];
 788         if ($birthDate) {
 789           $rows[$rowNum]['civicrm_contact_birth_date'] = CRM_Utils_Date::customFormat($birthDate, '%Y%m%d');
 790         }
 791         $entryFound = TRUE;
 792       }
 793       $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'event/ParticipantListing', 'List all participant(s) for this ') ? TRUE : $entryFound;
 794
 795       // skip looking further in rows, if first row itself doesn't
 796       // have the column we need
 797       if (!$entryFound) {
 798         break;
 799       }
 800     }
 801   }
 802
 803 }