CRM/Report/Form/Member/ContributionDetail.php

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