CRM/Report/Form/Member/ContributionDetail.php

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