CRM/Report/Form/Event/ParticipantListing.php

   1 <?php
   2 /*
   3   +--------------------------------------------------------------------+
   4   | CiviCRM version 4.7                                                |
   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_contribution' => array(
 298         'dao' => 'CRM_Contribute_DAO_Contribution',
 299         'fields' => array(
 300           'contribution_id' => array(
 301             'name' => 'id',
 302             'no_display' => TRUE,
 303             'required' => TRUE,
 304             'csv_display' => TRUE,
 305             'title' => ts('Contribution ID'),
 306           ),
 307           'financial_type_id' => array('title' => ts('Financial Type')),
 308           'receive_date' => array('title' => ts('Payment Date')),
 309           'contribution_status_id' => array('title' => ts('Contribution Status')),
 310           'payment_instrument_id' => array('title' => ts('Payment Type')),
 311           'contribution_source' => array(
 312             'name' => 'source',
 313             'title' => ts('Contribution Source'),
 314           ),
 315           'currency' => array(
 316             'required' => TRUE,
 317             'no_display' => TRUE,
 318           ),
 319           'trxn_id' => NULL,
 320           'fee_amount' => array('title' => ts('Transaction Fee')),
 321           'net_amount' => NULL,
 322         ),
 323         'grouping' => 'contrib-fields',
 324         'filters' => array(
 325           'receive_date' => array(
 326             'title' => ts('Payment Date'),
 327             'operatorType' => CRM_Report_Form::OP_DATE,
 328           ),
 329           'financial_type_id' => array(
 330             'title' => ts('Financial Type'),
 331             'type' => CRM_Utils_Type::T_INT,
 332             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 333             'options' => CRM_Contribute_PseudoConstant::financialType(),
 334           ),
 335           'currency' => array(
 336             'title' => ts('Contribution Currency'),
 337             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 338             'options' => CRM_Core_OptionGroup::values('currencies_enabled'),
 339             'default' => NULL,
 340             'type' => CRM_Utils_Type::T_STRING,
 341           ),
 342           'payment_instrument_id' => array(
 343             'title' => ts('Payment Type'),
 344             'type' => CRM_Utils_Type::T_INT,
 345             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 346             'options' => CRM_Contribute_PseudoConstant::paymentInstrument(),
 347           ),
 348           'contribution_status_id' => array(
 349             'title' => ts('Contribution Status'),
 350             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 351             'options' => CRM_Contribute_PseudoConstant::contributionStatus(),
 352             'default' => NULL,
 353           ),
 354         ),
 355       ),
 356       'civicrm_line_item' => array(
 357         'dao' => 'CRM_Price_DAO_LineItem',
 358         'grouping' => 'priceset-fields',
 359         'filters' => array(
 360           'price_field_value_id' => array(
 361             'name' => 'price_field_value_id',
 362             'title' => ts('Fee Level'),
 363             'type' => CRM_Utils_Type::T_INT,
 364             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 365             'options' => $this->getPriceLevels(),
 366           ),
 367         ),
 368       ),
 369     );
 370
 371     $this->_options = array(
 372       'blank_column_begin' => array(
 373         'title' => ts('Blank column at the Begining'),
 374         'type' => 'checkbox',
 375       ),
 376       'blank_column_end' => array(
 377         'title' => ts('Blank column at the End'),
 378         'type' => 'select',
 379         'options' => array(
 380           '' => ts('-select-'),
 381           1 => ts('One'),
 382           2 => ts('Two'),
 383           3 => ts('Three'),
 384         ),
 385       ),
 386     );
 387
 388     // CRM-17115 avoid duplication of sort_name - would be better to standardise name
 389     // & behaviour across reports but trying for no change at this point.
 390     $this->_columns['civicrm_contact']['fields']['sort_name']['no_display'] = TRUE;
 391
 392     // If we have active campaigns add those elements to both the fields and filters
 393     if ($campaignEnabled && !empty($this->activeCampaigns)) {
 394       $this->_columns['civicrm_participant']['fields']['campaign_id'] = array(
 395         'title' => ts('Campaign'),
 396         'default' => 'false',
 397       );
 398       $this->_columns['civicrm_participant']['filters']['campaign_id'] = array(
 399         'title' => ts('Campaign'),
 400         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 401         'options' => $this->activeCampaigns,
 402         'type' => CRM_Utils_Type::T_INT,
 403       );
 404       $this->_columns['civicrm_participant']['order_bys']['campaign_id'] = array(
 405         'title' => ts('Campaign'),
 406       );
 407     }
 408
 409     $this->_currencyColumn = 'civicrm_participant_fee_currency';
 410     parent::__construct();
 411   }
 412
 413   /**
 414    * Searches database for priceset values.
 415    *
 416    * @return array
 417    */
 418   public function getPriceLevels() {
 419     $query = "
 420 SELECT CONCAT(cv.label, ' (', ps.title, ' - ', cf.label , ')') label, cv.id
 421 FROM civicrm_price_field_value cv
 422 LEFT JOIN civicrm_price_field cf
 423   ON cv.price_field_id = cf.id
 424 LEFT JOIN civicrm_price_set_entity ce
 425   ON ce.price_set_id = cf.price_set_id
 426 LEFT JOIN civicrm_price_set ps
 427   ON ce.price_set_id = ps.id
 428 WHERE ce.entity_table = 'civicrm_event'
 429 ORDER BY  cv.label
 430 ";
 431     $dao = CRM_Core_DAO::executeQuery($query);
 432     $elements = array();
 433     while ($dao->fetch()) {
 434       $elements[$dao->id] = "$dao->label\n";
 435     }
 436
 437     return $elements;
 438   }
 439
 440   public function preProcess() {
 441     parent::preProcess();
 442   }
 443
 444   public function select() {
 445     $select = array();
 446     $this->_columnHeaders = array();
 447
 448     //add blank column at the Start
 449     if (array_key_exists('options', $this->_params) &&
 450       !empty($this->_params['options']['blank_column_begin'])
 451     ) {
 452       $select[] = " '' as blankColumnBegin";
 453       $this->_columnHeaders['blankColumnBegin']['title'] = '_ _ _ _';
 454     }
 455     foreach ($this->_columns as $tableName => $table) {
 456       if (array_key_exists('fields', $table)) {
 457         foreach ($table['fields'] as $fieldName => $field) {
 458           if (!empty($field['required']) ||
 459             !empty($this->_params['fields'][$fieldName])
 460           ) {
 461             if ($tableName == 'civicrm_contribution') {
 462               $this->_contribField = TRUE;
 463             }
 464             if ($fieldName == 'total_paid' || $fieldName == 'balance') {
 465               $this->_balance = TRUE;
 466               // modify the select if filtered by fee_level as the from clause
 467               // already selects the total_amount from civicrm_contribution table
 468               if (!empty($this->_params['price_field_value_id_value'])) {
 469                 $field['dbAlias'] = str_replace('SUM(ft.total_amount)', 'ft.total_amount', $field['dbAlias']);
 470               }
 471             }
 472             $alias = "{$tableName}_{$fieldName}";
 473             $select[] = "{$field['dbAlias']} as $alias";
 474             $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 475             $this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = CRM_Utils_Array::value('no_display', $field);
 476             $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 477             $this->_selectAliases[] = $alias;
 478           }
 479         }
 480       }
 481     }
 482     //add blank column at the end
 483     $blankcols = CRM_Utils_Array::value('blank_column_end', $this->_params);
 484     if ($blankcols) {
 485       for ($i = 1; $i <= $blankcols; $i++) {
 486         $select[] = " '' as blankColumnEnd_{$i}";
 487         $this->_columnHeaders["blank_{$i}"]['title'] = "_ _ _ _";
 488       }
 489     }
 490
 491     $this->_selectClauses = $select;
 492     $this->_select = "SELECT " . implode(', ', $select) . " ";
 493   }
 494
 495   /**
 496    * @param $fields
 497    * @param $files
 498    * @param $self
 499    *
 500    * @return array
 501    */
 502   public static function formRule($fields, $files, $self) {
 503     $errors = $grouping = array();
 504     return $errors;
 505   }
 506
 507   public function from() {
 508     $this->_from = "
 509         FROM civicrm_participant {$this->_aliases['civicrm_participant']}
 510              LEFT JOIN civicrm_event {$this->_aliases['civicrm_event']}
 511                     ON ({$this->_aliases['civicrm_event']}.id = {$this->_aliases['civicrm_participant']}.event_id ) AND
 512                         {$this->_aliases['civicrm_event']}.is_template = 0
 513              LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
 514                     ON ({$this->_aliases['civicrm_participant']}.contact_id  = {$this->_aliases['civicrm_contact']}.id  )
 515              {$this->_aclFrom}
 516              LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
 517                     ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_address']}.contact_id AND
 518                        {$this->_aliases['civicrm_address']}.is_primary = 1
 519              LEFT JOIN  civicrm_email {$this->_aliases['civicrm_email']}
 520                     ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND
 521                        {$this->_aliases['civicrm_email']}.is_primary = 1)
 522              LEFT  JOIN civicrm_phone  {$this->_aliases['civicrm_phone']}
 523                      ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND
 524                          {$this->_aliases['civicrm_phone']}.is_primary = 1
 525       ";
 526     if ($this->_contribField) {
 527       $this->_from .= "
 528              LEFT JOIN civicrm_participant_payment pp
 529                     ON ({$this->_aliases['civicrm_participant']}.id  = pp.participant_id)
 530              LEFT JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
 531                     ON (pp.contribution_id  = {$this->_aliases['civicrm_contribution']}.id)
 532       ";
 533     }
 534     if (!empty($this->_params['price_field_value_id_value'])) {
 535       $this->_from .= "
 536             LEFT JOIN civicrm_line_item line_item_civireport
 537                   ON line_item_civireport.entity_table = 'civicrm_participant' AND
 538                      line_item_civireport.entity_id = {$this->_aliases['civicrm_participant']}.id AND
 539                      line_item_civireport.qty > 0
 540       ";
 541     }
 542     if ($this->_balance) {
 543       $this->_from .= "
 544             LEFT JOIN civicrm_entity_financial_trxn eft
 545                   ON (eft.entity_id = {$this->_aliases['civicrm_contribution']}.id)
 546             LEFT JOIN civicrm_financial_trxn ft
 547                   ON (ft.id = eft.financial_trxn_id AND eft.entity_table = 'civicrm_contribution') AND
 548                      (ft.is_payment = 1)
 549       ";
 550     }
 551   }
 552
 553   public function where() {
 554     $clauses = array();
 555     foreach ($this->_columns as $tableName => $table) {
 556       if (array_key_exists('filters', $table)) {
 557         foreach ($table['filters'] as $fieldName => $field) {
 558           $clause = NULL;
 559
 560           if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
 561             $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
 562             $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
 563             $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
 564
 565             if ($relative || $from || $to) {
 566               $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
 567             }
 568           }
 569           else {
 570             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 571
 572             if ($fieldName == 'rid') {
 573               $value = CRM_Utils_Array::value("{$fieldName}_value", $this->_params);
 574               if (!empty($value)) {
 575                 $operator = '';
 576                 if ($op == 'notin') {
 577                   $operator = 'NOT';
 578                 }
 579
 580                 $regexp = "[[:cntrl:]]*" . implode('[[:>:]]*|[[:<:]]*', $value) . "[[:cntrl:]]*";
 581                 $clause = "{$field['dbAlias']} {$operator} REGEXP '{$regexp}'";
 582               }
 583               $op = NULL;
 584             }
 585
 586             if ($op) {
 587               $clause = $this->whereClause($field,
 588                 $op,
 589                 CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 590                 CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 591                 CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 592               );
 593             }
 594           }
 595
 596           if (!empty($clause)) {
 597             $clauses[] = $clause;
 598           }
 599         }
 600       }
 601     }
 602     if (empty($clauses)) {
 603       $this->_where = "WHERE {$this->_aliases['civicrm_participant']}.is_test = 0 ";
 604     }
 605     else {
 606       $this->_where = "WHERE {$this->_aliases['civicrm_participant']}.is_test = 0 AND " .
 607         implode(' AND ', $clauses);
 608     }
 609     if ($this->_aclWhere) {
 610       $this->_where .= " AND {$this->_aclWhere} ";
 611     }
 612   }
 613
 614   public function groupBy() {
 615     $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, "{$this->_aliases['civicrm_participant']}.id");
 616   }
 617
 618   public function postProcess() {
 619     // get the acl clauses built before we assemble the query
 620     $this->buildACLClause($this->_aliases['civicrm_contact']);
 621     parent::postProcess();
 622   }
 623
 624   /**
 625    * @param $rows
 626    * @param $entryFound
 627    * @param $row
 628    * @param int $rowId
 629    * @param $rowNum
 630    * @param $types
 631    *
 632    * @return bool
 633    */
 634   private function _initBasicRow(&$rows, &$entryFound, $row, $rowId, $rowNum, $types) {
 635     if (!array_key_exists($rowId, $row)) {
 636       return FALSE;
 637     }
 638
 639     $value = $row[$rowId];
 640     if ($value) {
 641       $rows[$rowNum][$rowId] = $types[$value];
 642     }
 643     $entryFound = TRUE;
 644   }
 645
 646   /**
 647    * Alter display of rows.
 648    *
 649    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 650    * such as rendering contacts as links.
 651    *
 652    * @param array $rows
 653    *   Rows generated by SQL, with an array for each row.
 654    */
 655   public function alterDisplay(&$rows) {
 656     $entryFound = FALSE;
 657     $eventType = CRM_Core_OptionGroup::values('event_type');
 658
 659     $financialTypes = CRM_Contribute_PseudoConstant::financialType();
 660     $contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus();
 661     $paymentInstruments = CRM_Contribute_PseudoConstant::paymentInstrument();
 662
 663     foreach ($rows as $rowNum => $row) {
 664       // make count columns point to detail report
 665       // convert display name to links
 666       if (array_key_exists('civicrm_participant_event_id', $row)) {
 667         $eventId = $row['civicrm_participant_event_id'];
 668         if ($eventId) {
 669           $rows[$rowNum]['civicrm_participant_event_id'] = CRM_Event_PseudoConstant::event($eventId, FALSE);
 670
 671           $url = CRM_Report_Utils_Report::getNextUrl('event/income',
 672             'reset=1&force=1&id_op=in&id_value=' . $eventId,
 673             $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 674           );
 675           $rows[$rowNum]['civicrm_participant_event_id_link'] = $url;
 676           $rows[$rowNum]['civicrm_participant_event_id_hover'] = ts("View Event Income Details for this Event");
 677         }
 678         $entryFound = TRUE;
 679       }
 680
 681       // handle event type id
 682       $this->_initBasicRow($rows, $entryFound, $row, 'civicrm_event_event_type_id', $rowNum, $eventType);
 683
 684       // handle participant status id
 685       if (array_key_exists('civicrm_participant_status_id', $row)) {
 686         $statusId = $row['civicrm_participant_status_id'];
 687         if ($statusId) {
 688           $rows[$rowNum]['civicrm_participant_status_id'] = CRM_Event_PseudoConstant::participantStatus($statusId, FALSE, 'label');
 689         }
 690         $entryFound = TRUE;
 691       }
 692
 693       // handle participant role id
 694       if (array_key_exists('civicrm_participant_role_id', $row)) {
 695         $roleId = $row['civicrm_participant_role_id'];
 696         if ($roleId) {
 697           $roles = explode(CRM_Core_DAO::VALUE_SEPARATOR, $roleId);
 698           $roleId = array();
 699           foreach ($roles as $role) {
 700             $roleId[$role] = CRM_Event_PseudoConstant::participantRole($role, FALSE);
 701           }
 702           $rows[$rowNum]['civicrm_participant_role_id'] = implode(', ', $roleId);
 703         }
 704         $entryFound = TRUE;
 705       }
 706
 707       // Handel value seperator in Fee Level
 708       if (array_key_exists('civicrm_participant_participant_fee_level', $row)) {
 709         $feeLevel = $row['civicrm_participant_participant_fee_level'];
 710         if ($feeLevel) {
 711           CRM_Event_BAO_Participant::fixEventLevel($feeLevel);
 712           $rows[$rowNum]['civicrm_participant_participant_fee_level'] = $feeLevel;
 713         }
 714         $entryFound = TRUE;
 715       }
 716
 717       // Convert display name to link
 718       $displayName = CRM_Utils_Array::value('civicrm_contact_sort_name_linked', $row);
 719       $cid = CRM_Utils_Array::value('civicrm_contact_id', $row);
 720       $id = CRM_Utils_Array::value('civicrm_participant_participant_record', $row);
 721
 722       if ($displayName && $cid && $id) {
 723         $url = CRM_Report_Utils_Report::getNextUrl('contact/detail',
 724           "reset=1&force=1&id_op=eq&id_value=$cid",
 725           $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 726         );
 727
 728         $viewUrl = CRM_Utils_System::url("civicrm/contact/view/participant",
 729           "reset=1&id=$id&cid=$cid&action=view&context=participant"
 730         );
 731
 732         $contactTitle = ts('View Contact Details');
 733         $participantTitle = ts('View Participant Record');
 734
 735         $rows[$rowNum]['civicrm_contact_sort_name_linked'] = "<a title='$contactTitle' href=$url>$displayName</a>";
 736         // Add a "View" link to the participant record if this isn't a CSV/PDF/printed document.
 737         if ($this->_outputMode !== 'csv' && $this->_outputMode !== 'pdf' && $this->_outputMode !== 'print') {
 738           $rows[$rowNum]['civicrm_contact_sort_name_linked'] .=
 739             "<span style='float: right;'><a title='$participantTitle' href=$viewUrl>" .
 740             ts('View') . "</a></span>";
 741         }
 742         $entryFound = TRUE;
 743       }
 744
 745       // Handle employer id
 746       if (array_key_exists('civicrm_contact_employer_id', $row)) {
 747         $employerId = $row['civicrm_contact_employer_id'];
 748         if ($employerId) {
 749           $rows[$rowNum]['civicrm_contact_employer_id'] = CRM_Contact_BAO_Contact::displayName($employerId);
 750           $url = CRM_Utils_System::url('civicrm/contact/view',
 751             'reset=1&cid=' . $employerId, $this->_absoluteUrl
 752           );
 753           $rows[$rowNum]['civicrm_contact_employer_id_link'] = $url;
 754           $rows[$rowNum]['civicrm_contact_employer_id_hover'] = ts('View Contact Summary for this Contact.');
 755         }
 756       }
 757
 758       // Convert campaign_id to campaign title
 759       $this->_initBasicRow($rows, $entryFound, $row, 'civicrm_participant_campaign_id', $rowNum, $this->activeCampaigns);
 760
 761       // handle contribution status
 762       $this->_initBasicRow($rows, $entryFound, $row, 'civicrm_contribution_contribution_status_id', $rowNum, $contributionStatus);
 763
 764       // handle payment instrument
 765       $this->_initBasicRow($rows, $entryFound, $row, 'civicrm_contribution_payment_instrument_id', $rowNum, $paymentInstruments);
 766
 767       // handle financial type
 768       $this->_initBasicRow($rows, $entryFound, $row, 'civicrm_contribution_financial_type_id', $rowNum, $financialTypes);
 769
 770       $entryFound = $this->alterDisplayContactFields($row, $rows, $rowNum, 'event/participantListing', 'View Event Income Details') ? TRUE : $entryFound;
 771
 772       // display birthday in the configured custom format
 773       if (array_key_exists('civicrm_contact_birth_date', $row)) {
 774         $birthDate = $row['civicrm_contact_birth_date'];
 775         if ($birthDate) {
 776           $rows[$rowNum]['civicrm_contact_birth_date'] = CRM_Utils_Date::customFormat($birthDate, '%Y%m%d');
 777         }
 778         $entryFound = TRUE;
 779       }
 780       $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'event/ParticipantListing', 'List all participant(s) for this ') ? TRUE : $entryFound;
 781
 782       // skip looking further in rows, if first row itself doesn't
 783       // have the column we need
 784       if (!$entryFound) {
 785         break;
 786       }
 787     }
 788   }
 789
 790 }