CRM/Report/Form/Member/ContributionDetail.php

   1 <?php
   2 /*
   3   +--------------------------------------------------------------------+
   4   | CiviCRM version 5                                                  |
   5   +--------------------------------------------------------------------+
   6   | Copyright CiviCRM LLC (c) 2004-2019                                |
   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-2019
  32  */
  33 class CRM_Report_Form_Member_ContributionDetail extends CRM_Report_Form {
  34
  35   protected $_summary = NULL;
  36
  37   protected $_customGroupExtends = array(
  38     'Contribution',
  39     'Membership',
  40     'Contact',
  41   );
  42
  43   /**
  44    * This report has not been optimised for group filtering.
  45    *
  46    * The functionality for group filtering has been improved but not
  47    * all reports have been adjusted to take care of it. This report has not
  48    * and will run an inefficient query until fixed.
  49    *
  50    * CRM-19170
  51    *
  52    * @var bool
  53    */
  54   protected $groupFilterNotOptimised = TRUE;
  55
  56   /**
  57    * Class constructor.
  58    */
  59   public function __construct() {
  60     $config = CRM_Core_Config::singleton();
  61     $campaignEnabled = in_array('CiviCampaign', $config->enableComponents);
  62     if ($campaignEnabled) {
  63       $getCampaigns = CRM_Campaign_BAO_Campaign::getPermissionedCampaigns(NULL, NULL, TRUE, FALSE, TRUE);
  64       $this->activeCampaigns = $getCampaigns['campaigns'];
  65       asort($this->activeCampaigns);
  66     }
  67     $this->_columns = array(
  68       'civicrm_contact' => array(
  69         'dao' => 'CRM_Contact_DAO_Contact',
  70         'fields' => array(
  71           'sort_name' => array(
  72             'title' => ts('Donor Name'),
  73             'required' => TRUE,
  74             'no_repeat' => TRUE,
  75           ),
  76           'first_name' => array(
  77             'title' => ts('First Name'),
  78             'no_repeat' => TRUE,
  79           ),
  80           'last_name' => array(
  81             'title' => ts('Last Name'),
  82             'no_repeat' => TRUE,
  83           ),
  84           'nick_name' => array(
  85             'title' => ts('Nickname'),
  86             'no_repeat' => TRUE,
  87           ),
  88           'contact_type' => array(
  89             'title' => ts('Contact Type'),
  90             'no_repeat' => TRUE,
  91           ),
  92           'contact_sub_type' => array(
  93             'title' => ts('Contact Subtype'),
  94             'no_repeat' => TRUE,
  95           ),
  96           'do_not_email' => array(
  97             'title' => ts('Do Not Email'),
  98             'no_repeat' => TRUE,
  99           ),
 100           'is_opt_out' => array(
 101             'title' => ts('No Bulk Email(Is Opt Out)'),
 102             'no_repeat' => TRUE,
 103           ),
 104           'id' => array(
 105             'no_display' => TRUE,
 106             'required' => TRUE,
 107             'csv_display' => TRUE,
 108             'title' => ts('Contact ID'),
 109           ),
 110         ),
 111         'filters' => array(
 112           'sort_name' => array(
 113             'title' => ts('Donor Name'),
 114             'operator' => 'like',
 115           ),
 116           'id' => array(
 117             'title' => ts('Contact ID'),
 118             'no_display' => TRUE,
 119           ),
 120         ),
 121         'grouping' => 'contact-fields',
 122       ),
 123       'civicrm_email' => array(
 124         'dao' => 'CRM_Core_DAO_Email',
 125         'fields' => array(
 126           'email' => array(
 127             'title' => ts('Donor Email'),
 128             'default' => TRUE,
 129             'no_repeat' => TRUE,
 130           ),
 131         ),
 132         'grouping' => 'contact-fields',
 133       ),
 134       'civicrm_phone' => array(
 135         'dao' => 'CRM_Core_DAO_Phone',
 136         'fields' => array(
 137           'phone' => array(
 138             'title' => ts('Donor Phone'),
 139             'default' => TRUE,
 140             'no_repeat' => TRUE,
 141           ),
 142         ),
 143         'grouping' => 'contact-fields',
 144       ),
 145       'first_donation' => array(
 146         'dao' => 'CRM_Contribute_DAO_Contribution',
 147         'fields' => array(
 148           'first_donation_date' => array(
 149             'title' => ts('First Contribution Date'),
 150             'base_field' => 'receive_date',
 151             'no_repeat' => TRUE,
 152           ),
 153           'first_donation_amount' => array(
 154             'title' => ts('First Contribution Amount'),
 155             'base_field' => 'total_amount',
 156             'no_repeat' => TRUE,
 157           ),
 158         ),
 159       ),
 160       'civicrm_contribution' => array(
 161         'dao' => 'CRM_Contribute_DAO_Contribution',
 162         'fields' => array(
 163           'contribution_id' => array(
 164             'name' => 'id',
 165             'no_display' => TRUE,
 166             'required' => TRUE,
 167             'csv_display' => TRUE,
 168             'title' => ts('Contribution ID'),
 169           ),
 170           'financial_type_id' => array(
 171             'title' => ts('Financial Type'),
 172             'default' => TRUE,
 173           ),
 174           'contribution_recur_id' => array(
 175             'title' => ts('Recurring Contribution Id'),
 176             'name' => 'contribution_recur_id',
 177             'required' => TRUE,
 178             'no_display' => TRUE,
 179             'csv_display' => TRUE,
 180           ),
 181           'contribution_status_id' => array(
 182             'title' => ts('Contribution Status'),
 183           ),
 184           'payment_instrument_id' => array(
 185             'title' => ts('Payment Type'),
 186           ),
 187           'contribution_source' => array(
 188             'name' => 'source',
 189             'title' => ts('Contribution Source'),
 190           ),
 191           'currency' => array(
 192             'required' => TRUE,
 193             'no_display' => TRUE,
 194           ),
 195           'trxn_id' => NULL,
 196           'receive_date' => array('default' => TRUE),
 197           'receipt_date' => NULL,
 198           'fee_amount' => NULL,
 199           'net_amount' => NULL,
 200           'total_amount' => array(
 201             'title' => ts('Amount'),
 202             'required' => TRUE,
 203           ),
 204         ),
 205         'filters' => array(
 206           'receive_date' => array('operatorType' => CRM_Report_Form::OP_DATE),
 207           'financial_type_id' => array(
 208             'title' => ts('Financial Type'),
 209             'type' => CRM_Utils_Type::T_INT,
 210             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 211             'options' => CRM_Contribute_PseudoConstant::financialType(),
 212           ),
 213           'currency' => array(
 214             'title' => ts('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           'payment_instrument_id' => array(
 221             'title' => ts('Payment Type'),
 222             'type' => CRM_Utils_Type::T_INT,
 223             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 224             'options' => CRM_Contribute_PseudoConstant::paymentInstrument(),
 225           ),
 226           'contribution_status_id' => array(
 227             'title' => ts('Contribution Status'),
 228             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 229             'options' => CRM_Contribute_PseudoConstant::contributionStatus(),
 230             'default' => array(1),
 231           ),
 232           'total_amount' => array('title' => ts('Contribution Amount')),
 233         ),
 234         'grouping' => 'contri-fields',
 235       ),
 236       'civicrm_product' => array(
 237         'dao' => 'CRM_Contribute_DAO_Product',
 238         'fields' => array(
 239           'product_name' => array(
 240             'name' => 'name',
 241             'title' => ts('Premium'),
 242           ),
 243         ),
 244       ),
 245       'civicrm_batch' => array(
 246         'dao' => 'CRM_Batch_DAO_EntityBatch',
 247         'grouping' => 'contri-fields',
 248         'fields' => array(
 249           'batch_id' => array(
 250             'name' => 'batch_id',
 251             'title' => ts('Batch Name'),
 252           ),
 253         ),
 254         'filters' => array(
 255           'bid' => array(
 256             'title' => ts('Batch Name'),
 257             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 258             'options' => CRM_Batch_BAO_Batch::getBatches(),
 259             'type' => CRM_Utils_Type::T_INT,
 260             'dbAlias' => 'batch_civireport.batch_id',
 261           ),
 262         ),
 263       ),
 264       'civicrm_contribution_product' => array(
 265         'dao' => 'CRM_Contribute_DAO_ContributionProduct',
 266         'fields' => array(
 267           'product_id' => array(
 268             'no_display' => TRUE,
 269           ),
 270           'product_option' => array(
 271             'title' => ts('Premium Option'),
 272           ),
 273           'fulfilled_date' => array(
 274             'title' => ts('Premium Fulfilled Date'),
 275           ),
 276           'contribution_id' => array(
 277             'no_display' => TRUE,
 278           ),
 279         ),
 280       ),
 281       'civicrm_contribution_ordinality' => array(
 282         'dao' => 'CRM_Contribute_DAO_Contribution',
 283         'alias' => 'cordinality',
 284         'filters' => array(
 285           'ordinality' => array(
 286             'title' => ts('Contribution Ordinality'),
 287             'type' => CRM_Utils_Type::T_INT,
 288             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 289             'options' => array(
 290               0 => 'First by Contributor',
 291               1 => 'Second or Later by Contributor',
 292             ),
 293           ),
 294         ),
 295       ),
 296       'civicrm_membership' => array(
 297         'dao' => 'CRM_Member_DAO_Membership',
 298         'fields' => array(
 299           'membership_type_id' => array(
 300             'title' => ts('Membership Type'),
 301             'required' => TRUE,
 302             'no_repeat' => TRUE,
 303           ),
 304           'membership_start_date' => array(
 305             'title' => ts('Start Date'),
 306             'default' => TRUE,
 307           ),
 308           'membership_end_date' => array(
 309             'title' => ts('End Date'),
 310             'default' => TRUE,
 311           ),
 312           'join_date' => array(
 313             'title' => ts('Join Date'),
 314             'default' => TRUE,
 315           ),
 316           'source' => array('title' => ts('Membership Source')),
 317         ),
 318         'filters' => array(
 319           'join_date' => array('operatorType' => CRM_Report_Form::OP_DATE),
 320           'membership_start_date' => array('operatorType' => CRM_Report_Form::OP_DATE),
 321           'membership_end_date' => array('operatorType' => CRM_Report_Form::OP_DATE),
 322           'owner_membership_id' => array(
 323             'title' => ts('Membership Owner ID'),
 324             'operatorType' => CRM_Report_Form::OP_INT,
 325           ),
 326           'tid' => array(
 327             'name' => 'membership_type_id',
 328             'title' => ts('Membership Types'),
 329             'type' => CRM_Utils_Type::T_INT,
 330             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 331             'options' => CRM_Member_PseudoConstant::membershipType(),
 332           ),
 333         ),
 334         'grouping' => 'member-fields',
 335       ),
 336       'civicrm_membership_status' => array(
 337         'dao' => 'CRM_Member_DAO_MembershipStatus',
 338         'alias' => 'mem_status',
 339         'fields' => array(
 340           'membership_status_name' => array(
 341             'name' => 'name',
 342             'title' => ts('Membership Status'),
 343             'default' => TRUE,
 344           ),
 345         ),
 346         'filters' => array(
 347           'sid' => array(
 348             'name' => 'id',
 349             'title' => ts('Membership Status'),
 350             'type' => CRM_Utils_Type::T_INT,
 351             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 352             'options' => CRM_Member_PseudoConstant::membershipStatus(NULL, NULL, 'label'),
 353           ),
 354         ),
 355         'grouping' => 'member-fields',
 356       ),
 357       'civicrm_note' => array(
 358         'dao' => 'CRM_Core_DAO_Note',
 359         'fields' => array(
 360           'contribution_note' => array(
 361             'name' => 'note',
 362             'title' => ts('Contribution Note'),
 363           ),
 364         ),
 365         'filters' => array(
 366           'note' => array(
 367             'name' => 'note',
 368             'title' => ts('Contribution Note'),
 369             'operator' => 'like',
 370             'type' => CRM_Utils_Type::T_STRING,
 371           ),
 372         ),
 373       ),
 374     ) + $this->addAddressFields(FALSE);
 375
 376     $this->_groupFilter = TRUE;
 377     $this->_tagFilter = TRUE;
 378
 379     if ($campaignEnabled && !empty($this->activeCampaigns)) {
 380       $this->_columns['civicrm_contribution']['fields']['campaign_id'] = array(
 381         'title' => ts('Campaign'),
 382         'default' => 'false',
 383       );
 384       $this->_columns['civicrm_contribution']['filters']['campaign_id'] = array(
 385         'title' => ts('Campaign'),
 386         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 387         'options' => $this->activeCampaigns,
 388         'type' => CRM_Utils_Type::T_INT,
 389       );
 390       $this->_columns['civicrm_contribution']['order_bys']['campaign_id'] = array('title' => ts('Campaign'));
 391     }
 392
 393     $this->_currencyColumn = 'civicrm_contribution_currency';
 394     parent::__construct();
 395   }
 396
 397   public function preProcess() {
 398     parent::preProcess();
 399   }
 400
 401   public function select() {
 402     $select = array();
 403
 404     $this->_columnHeaders = array();
 405     foreach ($this->_columns as $tableName => $table) {
 406       if (array_key_exists('fields', $table)) {
 407         foreach ($table['fields'] as $fieldName => $field) {
 408           if (!empty($field['required']) ||
 409             !empty($this->_params['fields'][$fieldName])
 410           ) {
 411
 412             // only include statistics columns if set
 413             if (!empty($field['statistics'])) {
 414               foreach ($field['statistics'] as $stat => $label) {
 415                 switch (strtolower($stat)) {
 416                   case 'sum':
 417                     $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
 418                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 419                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
 420                     $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 421                     break;
 422
 423                   case 'count':
 424                     $select[] = "COUNT({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
 425                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 426                     $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 427                     break;
 428
 429                   case 'avg':
 430                     $select[] = "ROUND(AVG({$field['dbAlias']}),2) as {$tableName}_{$fieldName}_{$stat}";
 431                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
 432                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 433                     $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 434                     break;
 435                 }
 436               }
 437             }
 438             elseif ($fieldName == 'first_donation_date' ||
 439               $fieldName == 'first_donation_amount'
 440             ) {
 441               $baseField = CRM_Utils_Array::value('base_field', $field);
 442               $select[] = "{$this->_aliases['civicrm_contribution']}.{$baseField} as {$tableName}_{$fieldName}";
 443               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 444               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 445             }
 446             else {
 447               $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 448               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 449               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 450             }
 451           }
 452         }
 453       }
 454     }
 455
 456     $this->_selectClauses = $select;
 457     $this->_select = 'SELECT ' . implode(', ', $select) . ' ';
 458   }
 459
 460   public function from() {
 461     $this->_from = "
 462               FROM civireport_membership_contribution_detail
 463               INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
 464                       ON (civireport_membership_contribution_detail.contribution_id = {$this->_aliases['civicrm_contribution']}.id)
 465               LEFT JOIN civicrm_membership {$this->_aliases['civicrm_membership']}
 466                       ON (civireport_membership_contribution_detail.membership_id = {$this->_aliases['civicrm_membership']}.id)
 467               INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
 468                       ON (civireport_membership_contribution_detail.contact_id = {$this->_aliases['civicrm_contact']}.id)
 469               LEFT  JOIN civicrm_membership_status {$this->_aliases['civicrm_membership_status']}
 470                           ON {$this->_aliases['civicrm_membership_status']}.id =
 471                              {$this->_aliases['civicrm_membership']}.status_id
 472                              {$this->_aclFrom}
 473 ";
 474
 475     //for premiums
 476     if (!empty($this->_params['fields']['product_name']) ||
 477       !empty($this->_params['fields']['product_option']) ||
 478       !empty($this->_params['fields']['fulfilled_date'])
 479     ) {
 480       $this->_from .= "
 481                  LEFT JOIN  civicrm_contribution_product {$this->_aliases['civicrm_contribution_product']}
 482                         ON ({$this->_aliases['civicrm_contribution_product']}.contribution_id = {$this->_aliases['civicrm_contribution']}.id)
 483                  LEFT JOIN  civicrm_product {$this->_aliases['civicrm_product']} ON ({$this->_aliases['civicrm_product']}.id = {$this->_aliases['civicrm_contribution_product']}.product_id)";
 484     }
 485
 486     if (!empty($this->_params['ordinality_value'])) {
 487       $this->_from .= "
 488               INNER JOIN (SELECT c.id, IF(COUNT(oc.id) = 0, 0, 1) AS ordinality FROM civicrm_contribution c LEFT JOIN civicrm_contribution oc ON c.contact_id = oc.contact_id AND oc.receive_date < c.receive_date GROUP BY c.id) {$this->_aliases['civicrm_contribution_ordinality']}
 489                       ON {$this->_aliases['civicrm_contribution_ordinality']}.id = {$this->_aliases['civicrm_contribution']}.id";
 490     }
 491
 492     // include contribution note
 493     if (!empty($this->_params['fields']['contribution_note']) ||
 494       !empty($this->_params['note_value'])
 495     ) {
 496       $this->_from .= "
 497             LEFT JOIN civicrm_note {$this->_aliases['civicrm_note']}
 498                       ON ( {$this->_aliases['civicrm_note']}.entity_table = 'civicrm_contribution' AND
 499                            {$this->_aliases['civicrm_contribution']}.id = {$this->_aliases['civicrm_note']}.entity_id )";
 500
 501     }
 502
 503     //for contribution batches
 504     if (!empty($this->_params['fields']['batch_id']) ||
 505         !empty($this->_params['bid_value'])
 506     ) {
 507       $this->_from .= "
 508         LEFT JOIN civicrm_entity_financial_trxn eft
 509           ON eft.entity_id = {$this->_aliases['civicrm_contribution']}.id AND
 510             eft.entity_table = 'civicrm_contribution'
 511         LEFT JOIN civicrm_entity_batch {$this->_aliases['civicrm_batch']}
 512           ON ({$this->_aliases['civicrm_batch']}.entity_id = eft.financial_trxn_id
 513           AND {$this->_aliases['civicrm_batch']}.entity_table = 'civicrm_financial_trxn')";
 514     }
 515
 516     $this->joinAddressFromContact();
 517     $this->joinPhoneFromContact();
 518     $this->joinEmailFromContact();
 519   }
 520
 521   /**
 522    * @param bool $applyLimit
 523    */
 524   public function tempTable($applyLimit = TRUE) {
 525     // create temp table with contact ids,contribtuion id,membership id
 526     $dropTempTable = 'DROP TEMPORARY TABLE IF EXISTS civireport_membership_contribution_detail';
 527     CRM_Core_DAO::executeQuery($dropTempTable);
 528
 529     $sql = 'CREATE TEMPORARY TABLE civireport_membership_contribution_detail
 530             (contribution_id int, INDEX USING HASH(contribution_id), contact_id int, INDEX USING HASH(contact_id),
 531             membership_id int, INDEX USING HASH(membership_id), payment_id int, INDEX USING HASH(payment_id)) ENGINE=MEMORY' . $this->_databaseAttributes;
 532     CRM_Core_DAO::executeQuery($sql);
 533
 534     $fillTemp = "
 535           INSERT INTO civireport_membership_contribution_detail (contribution_id, contact_id, membership_id)
 536           SELECT contribution.id, {$this->_aliases['civicrm_contact']}.id, m.id
 537           FROM civicrm_contribution contribution
 538           INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
 539                 ON {$this->_aliases['civicrm_contact']}.id = contribution.contact_id AND contribution.is_test = 0
 540           {$this->_aclFrom}
 541           LEFT JOIN civicrm_membership_payment mp
 542                 ON contribution.id = mp.contribution_id
 543           LEFT JOIN civicrm_membership m
 544                 ON mp.membership_id = m.id AND m.is_test = 0 ";
 545
 546     CRM_Core_DAO::executeQuery($fillTemp);
 547   }
 548
 549   /**
 550    * @param bool $applyLimit
 551    *
 552    * @return string
 553    */
 554   public function buildQuery($applyLimit = TRUE) {
 555     $this->select();
 556     //create temp table to be used as base table
 557     $this->tempTable();
 558     $this->from();
 559     $this->customDataFrom();
 560     $this->buildPermissionClause();
 561     $this->where();
 562     $this->groupBy();
 563     $this->orderBy();
 564
 565     // order_by columns not selected for display need to be included in SELECT
 566     $unselectedSectionColumns = $this->unselectedSectionColumns();
 567     foreach ($unselectedSectionColumns as $alias => $section) {
 568       $this->_select .= ", {$section['dbAlias']} as {$alias}";
 569     }
 570
 571     if ($applyLimit && empty($this->_params['charts'])) {
 572       $this->limit();
 573     }
 574
 575     $sql = "{$this->_select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy} {$this->_limit}";
 576     $this->addToDeveloperTab($sql);
 577     return $sql;
 578   }
 579
 580   public function groupBy() {
 581     $groupBy = array(
 582       "{$this->_aliases['civicrm_contact']}.id",
 583       "{$this->_aliases['civicrm_contribution']}.id",
 584     );
 585     $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy);
 586   }
 587
 588   public function orderBy() {
 589     $this->_orderBy = " ORDER BY {$this->_aliases['civicrm_contact']}.sort_name, {$this->_aliases['civicrm_contact']}.id ";
 590     if (!empty($this->_params['fields']['first_donation_date']) ||
 591       !empty($this->_params['fields']['first_donation_amount'])
 592     ) {
 593       $this->_orderBy .= ", {$this->_aliases['civicrm_contribution']}.receive_date";
 594     }
 595   }
 596
 597   /**
 598    * @param $rows
 599    *
 600    * @return array
 601    */
 602   public function statistics(&$rows) {
 603     $statistics = parent::statistics($rows);
 604
 605     $select = "SELECT DISTINCT {$this->_aliases['civicrm_contribution']}.id";
 606
 607     $sql = "SELECT COUNT(cc.id) as count, SUM(cc.total_amount) as amount, ROUND(AVG(cc.total_amount), 2) as avg, cc.currency as currency
 608             FROM civicrm_contribution cc
 609             WHERE cc.id IN ({$select} {$this->_from} {$this->_where})
 610             GROUP BY cc.currency";
 611
 612     $dao = CRM_Core_DAO::executeQuery($sql);
 613     $totalAmount = $average = array();
 614     while ($dao->fetch()) {
 615       $totalAmount[]
 616         = CRM_Utils_Money::format($dao->amount, $dao->currency) . "(" .
 617         $dao->count . ")";
 618       $average[] = CRM_Utils_Money::format($dao->avg, $dao->currency);
 619     }
 620     $statistics['counts']['amount'] = array(
 621       'title' => ts('Total Amount'),
 622       'value' => implode(',  ', $totalAmount),
 623       'type' => CRM_Utils_Type::T_STRING,
 624     );
 625
 626     $statistics['counts']['avg'] = array(
 627       'title' => ts('Average'),
 628       'value' => implode(',  ', $average),
 629       'type' => CRM_Utils_Type::T_STRING,
 630     );
 631
 632     return $statistics;
 633   }
 634
 635   public function postProcess() {
 636     // get the acl clauses built before we assemble the query
 637     $this->buildACLClause($this->_aliases['civicrm_contact']);
 638     parent::postProcess();
 639   }
 640
 641   /**
 642    * @param $rows
 643    */
 644   public function alterDisplay(&$rows) {
 645     // custom code to alter rows
 646     $checkList = array();
 647
 648     $entryFound = FALSE;
 649     $contributionTypes = CRM_Contribute_PseudoConstant::financialType();
 650     $contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus();
 651     $paymentInstruments = CRM_Contribute_PseudoConstant::paymentInstrument();
 652     $batches = CRM_Batch_BAO_Batch::getBatches();
 653
 654     //altering the csv display adding additional fields
 655     if ($this->_outputMode == 'csv') {
 656       foreach ($this->_columns as $tableName => $table) {
 657         if (array_key_exists('fields', $table)) {
 658           foreach ($table['fields'] as $fieldName => $field) {
 659             if (!empty($field['csv_display']) && !empty($field['no_display'])) {
 660               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 661               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 662             }
 663           }
 664         }
 665       }
 666     }
 667
 668     // allow repeat for first donation amount and date in csv
 669     $fAmt = '';
 670     $fDate = '';
 671     foreach ($rows as $rowNum => $row) {
 672       if ($this->_outputMode == 'csv') {
 673         if (array_key_exists('civicrm_contact_id', $row)) {
 674           if ($contactId = $row['civicrm_contact_id']) {
 675             if ($rowNum == 0) {
 676               $pcid = $contactId;
 677               $fAmt = $row['first_donation_first_donation_amount'];
 678               $fDate = $row['first_donation_first_donation_date'];
 679             }
 680             else {
 681               if ($pcid == $contactId) {
 682                 $rows[$rowNum]['first_donation_first_donation_amount'] = $fAmt;
 683                 $rows[$rowNum]['first_donation_first_donation_date'] = $fDate;
 684                 $pcid = $contactId;
 685               }
 686               else {
 687                 $fAmt = $row['first_donation_first_donation_amount'];
 688                 $fDate = $row['first_donation_first_donation_date'];
 689                 $pcid = $contactId;
 690               }
 691             }
 692           }
 693         }
 694       }
 695
 696       if (!empty($this->_noRepeats) && $this->_outputMode != 'csv') {
 697         $repeatFound = FALSE;
 698
 699         $display_flag = NULL;
 700         if (array_key_exists('civicrm_contact_id', $row)) {
 701           if ($cid = $row['civicrm_contact_id']) {
 702             if ($rowNum == 0) {
 703               $prev_cid = $cid;
 704             }
 705             else {
 706               if ($prev_cid == $cid) {
 707                 $display_flag = 1;
 708                 $prev_cid = $cid;
 709               }
 710               else {
 711                 $display_flag = 0;
 712                 $prev_cid = $cid;
 713               }
 714             }
 715
 716             if ($display_flag) {
 717               foreach ($row as $colName => $colVal) {
 718                 if (in_array($colName, $this->_noRepeats)) {
 719                   unset($rows[$rowNum][$colName]);
 720                 }
 721               }
 722             }
 723             $entryFound = TRUE;
 724           }
 725         }
 726       }
 727
 728       if (array_key_exists('civicrm_membership_membership_type_id', $row)) {
 729         if ($value = $row['civicrm_membership_membership_type_id']) {
 730           $rows[$rowNum]['civicrm_membership_membership_type_id'] = CRM_Member_PseudoConstant::membershipType($value, FALSE);
 731         }
 732         $entryFound = TRUE;
 733       }
 734
 735       if (!empty($row['civicrm_batch_batch_id'])) {
 736         $rows[$rowNum]['civicrm_batch_batch_id'] = CRM_Utils_Array::value($row['civicrm_batch_batch_id'], $batches);
 737         $entryFound = TRUE;
 738       }
 739
 740       // convert donor sort name to link
 741       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 742         !empty($rows[$rowNum]['civicrm_contact_sort_name']) &&
 743         array_key_exists('civicrm_contact_id', $row)
 744       ) {
 745         $url = CRM_Utils_System::url('civicrm/contact/view',
 746           'reset=1&cid=' . $row['civicrm_contact_id'],
 747           $this->_absoluteUrl
 748         );
 749
 750         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 751         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts('View Contact Summary for this Contact.');
 752       }
 753
 754       if ($value = CRM_Utils_Array::value('civicrm_contribution_financial_type_id', $row)) {
 755         $rows[$rowNum]['civicrm_contribution_financial_type_id'] = $contributionTypes[$value];
 756         $entryFound = TRUE;
 757       }
 758       if ($value = CRM_Utils_Array::value('civicrm_contribution_contribution_status_id', $row)) {
 759         $rows[$rowNum]['civicrm_contribution_contribution_status_id'] = $contributionStatus[$value];
 760         $entryFound = TRUE;
 761       }
 762       if ($value = CRM_Utils_Array::value('civicrm_contribution_payment_instrument_id', $row)) {
 763         $rows[$rowNum]['civicrm_contribution_payment_instrument_id'] = $paymentInstruments[$value];
 764         $entryFound = TRUE;
 765       }
 766       if (($value = CRM_Utils_Array::value('civicrm_contribution_total_amount_sum', $row)) &&
 767         CRM_Core_Permission::check('access CiviContribute')
 768       ) {
 769         $url = CRM_Utils_System::url('civicrm/contact/view/contribution',
 770           'reset=1&id=' . $row['civicrm_contribution_contribution_id'] .
 771           '&cid=' . $row['civicrm_contact_id'] .
 772           '&action=view&context=contribution&selectedChild=contribute',
 773           $this->_absoluteUrl
 774         );
 775         $rows[$rowNum]['civicrm_contribution_total_amount_sum_link'] = $url;
 776         $rows[$rowNum]['civicrm_contribution_total_amount_sum_hover'] = ts('View Details of this Contribution.');
 777         $entryFound = TRUE;
 778       }
 779
 780       // convert campaign_id to campaign title
 781       if (array_key_exists('civicrm_contribution_campaign_id', $row)) {
 782         if ($value = $row['civicrm_contribution_campaign_id']) {
 783           $rows[$rowNum]['civicrm_contribution_campaign_id'] = $this->activeCampaigns[$value];
 784           $entryFound = TRUE;
 785         }
 786       }
 787
 788       $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'member/contributionDetail', 'List all contribution(s) for this ') ? TRUE : $entryFound;
 789
 790       // skip looking further in rows, if first row itself doesn't
 791       // have the column we need
 792       if (!$entryFound) {
 793         break;
 794       }
 795       $lastKey = $rowNum;
 796     }
 797   }
 798
 799 }