CRM/Report/Form/Event/ParticipantListing.php

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