CRM/Report/Form/Member/ContributionDetail.php

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