CRM/Report/Form/Member/ContributionDetail.php

   1 <?php
   2 /*
   3   +--------------------------------------------------------------------+
   4   | CiviCRM version 4.7                                                |
   5   +--------------------------------------------------------------------+
   6   | Copyright CiviCRM LLC (c) 2004-2015                                |
   7   +--------------------------------------------------------------------+
   8   | This file is a part of CiviCRM.                                    |
   9   |                                                                    |
  10   | CiviCRM is free software; you can copy, modify, and distribute it  |
  11   | under the terms of the GNU Affero General Public License           |
  12   | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  13   |                                                                    |
  14   | CiviCRM is distributed in the hope that it will be useful, but     |
  15   | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  16   | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  17   | See the GNU Affero General Public License for more details.        |
  18   |                                                                    |
  19   | You should have received a copy of the GNU Affero General Public   |
  20   | License and the CiviCRM Licensing Exception along                  |
  21   | with this program; if not, contact CiviCRM LLC                     |
  22   | at info[AT]civicrm[DOT]org. If you have questions about the        |
  23   | GNU Affero General Public License or the licensing of CiviCRM,     |
  24   | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  25   +--------------------------------------------------------------------+
  26  */
  27
  28 /**
  29  *
  30  * @package CRM
  31  * @copyright CiviCRM LLC (c) 2004-2015
  32  * $Id$
  33  *
  34  */
  35 class CRM_Report_Form_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             'type' => CRM_Utils_Type::T_INT,
 199             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 200             'options' => CRM_Contribute_PseudoConstant::financialType(),
 201           ),
 202           'currency' => array(
 203             'title' => 'Currency',
 204             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 205             'options' => CRM_Core_OptionGroup::values('currencies_enabled'),
 206             'default' => NULL,
 207             'type' => CRM_Utils_Type::T_STRING,
 208           ),
 209           'payment_instrument_id' => array(
 210             'title' => ts('Payment Type'),
 211             'type' => CRM_Utils_Type::T_INT,
 212             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 213             'options' => CRM_Contribute_PseudoConstant::paymentInstrument(),
 214           ),
 215           'contribution_status_id' => array(
 216             'title' => ts('Contribution Status'),
 217             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 218             'options' => CRM_Contribute_PseudoConstant::contributionStatus(),
 219             'default' => array(1),
 220           ),
 221           'total_amount' => array('title' => ts('Contribution Amount')),
 222         ),
 223         'grouping' => 'contri-fields',
 224       ),
 225       'civicrm_product' => array(
 226         'dao' => 'CRM_Contribute_DAO_Product',
 227         'fields' => array(
 228           'product_name' => array(
 229             'name' => 'name',
 230             'title' => ts('Premium'),
 231           ),
 232         ),
 233       ),
 234       'civicrm_contribution_product' => array(
 235         'dao' => 'CRM_Contribute_DAO_ContributionProduct',
 236         'fields' => array(
 237           'product_id' => array(
 238             'no_display' => TRUE,
 239           ),
 240           'product_option' => array(
 241             'title' => ts('Premium Option'),
 242           ),
 243           'fulfilled_date' => array(
 244             'title' => ts('Premium Fulfilled Date'),
 245           ),
 246           'contribution_id' => array(
 247             'no_display' => TRUE,
 248           ),
 249         ),
 250       ),
 251       'civicrm_contribution_ordinality' => array(
 252         'dao' => 'CRM_Contribute_DAO_Contribution',
 253         'alias' => 'cordinality',
 254         'filters' => array(
 255           'ordinality' => array(
 256             'title' => ts('Contribution Ordinality'),
 257             'type' => CRM_Utils_Type::T_INT,
 258             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 259             'options' => array(
 260               0 => 'First by Contributor',
 261               1 => 'Second or Later by Contributor',
 262             ),
 263           ),
 264         ),
 265       ),
 266       'civicrm_membership' => array(
 267         'dao' => 'CRM_Member_DAO_Membership',
 268         'fields' => array(
 269           'membership_type_id' => array(
 270             'title' => ts('Membership Type'),
 271             'required' => TRUE,
 272             'no_repeat' => TRUE,
 273           ),
 274           'membership_start_date' => array(
 275             'title' => ts('Start Date'),
 276             'default' => TRUE,
 277           ),
 278           'membership_end_date' => array(
 279             'title' => ts('End Date'),
 280             'default' => TRUE,
 281           ),
 282           'join_date' => array(
 283             'title' => ts('Join Date'),
 284             'default' => TRUE,
 285           ),
 286           'source' => array('title' => ts('Membership Source')),
 287         ),
 288         'filters' => array(
 289           'join_date' => array('operatorType' => CRM_Report_Form::OP_DATE),
 290           'membership_start_date' => array('operatorType' => CRM_Report_Form::OP_DATE),
 291           'membership_end_date' => array('operatorType' => CRM_Report_Form::OP_DATE),
 292           'owner_membership_id' => array(
 293             'title' => ts('Membership Owner ID'),
 294             'operatorType' => CRM_Report_Form::OP_INT,
 295           ),
 296           'tid' => array(
 297             'name' => 'membership_type_id',
 298             'title' => ts('Membership Types'),
 299             'type' => CRM_Utils_Type::T_INT,
 300             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 301             'options' => CRM_Member_PseudoConstant::membershipType(),
 302           ),
 303         ),
 304         'grouping' => 'member-fields',
 305       ),
 306       'civicrm_membership_status' => array(
 307         'dao' => 'CRM_Member_DAO_MembershipStatus',
 308         'alias' => 'mem_status',
 309         'fields' => array(
 310           'membership_status_name' => array(
 311             'name' => 'name',
 312             'title' => ts('Membership Status'),
 313             'default' => TRUE,
 314           ),
 315         ),
 316         'filters' => array(
 317           'sid' => array(
 318             'name' => 'id',
 319             'title' => ts('Membership Status'),
 320             'type' => CRM_Utils_Type::T_INT,
 321             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 322             'options' => CRM_Member_PseudoConstant::membershipStatus(NULL, NULL, 'label'),
 323           ),
 324         ),
 325         'grouping' => 'member-fields',
 326       ),
 327       'civicrm_note' => array(
 328         'dao' => 'CRM_Core_DAO_Note',
 329         'fields' => array(
 330           'contribution_note' => array(
 331             'name' => 'note',
 332             'title' => ts('Contribution Note'),
 333           ),
 334         ),
 335         'filters' => array(
 336           'note' => array(
 337             'name' => 'note',
 338             'title' => ts('Contribution Note'),
 339             'operator' => 'like',
 340             'type' => CRM_Utils_Type::T_STRING,
 341           ),
 342         ),
 343       ),
 344     ) + $this->addAddressFields(FALSE);
 345
 346     $this->_groupFilter = TRUE;
 347     $this->_tagFilter = TRUE;
 348
 349     // Don't show Batch display column and filter unless batches are being used
 350     $this->_allBatches = CRM_Batch_BAO_Batch::getBatches();
 351     if (!empty($this->_allBatches)) {
 352       $this->_columns['civicrm_batch']['dao'] = 'CRM_Batch_DAO_Batch';
 353       $this->_columns['civicrm_batch']['fields']['batch_id'] = array(
 354         'name' => 'id',
 355         'title' => ts('Batch Name'),
 356       );
 357       $this->_columns['civicrm_batch']['filters']['bid'] = array(
 358         'name' => 'id',
 359         'title' => ts('Batch Name'),
 360         'type' => CRM_Utils_Type::T_INT,
 361         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 362         'options' => $this->_allBatches,
 363       );
 364       $this->_columns['civicrm_entity_batch']['dao'] = 'CRM_Batch_DAO_EntityBatch';
 365       $this->_columns['civicrm_entity_batch']['fields']['entity_batch_id'] = array(
 366         'name' => 'batch_id',
 367         'default' => TRUE,
 368         'no_display' => TRUE,
 369       );
 370     }
 371
 372     if ($campaignEnabled && !empty($this->activeCampaigns)) {
 373       $this->_columns['civicrm_contribution']['fields']['campaign_id'] = array(
 374         'title' => ts('Campaign'),
 375         'default' => 'false',
 376       );
 377       $this->_columns['civicrm_contribution']['filters']['campaign_id'] = array(
 378         'title' => ts('Campaign'),
 379         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 380         'options' => $this->activeCampaigns,
 381       );
 382       $this->_columns['civicrm_contribution']['order_bys']['campaign_id'] = array('title' => ts('Campaign'));
 383     }
 384
 385     $this->_currencyColumn = 'civicrm_contribution_currency';
 386     parent::__construct();
 387   }
 388
 389   public function preProcess() {
 390     parent::preProcess();
 391   }
 392
 393   public function select() {
 394     $select = array();
 395
 396     $this->_columnHeaders = array();
 397     foreach ($this->_columns as $tableName => $table) {
 398       if (array_key_exists('fields', $table)) {
 399         foreach ($table['fields'] as $fieldName => $field) {
 400           if (!empty($field['required']) ||
 401             !empty($this->_params['fields'][$fieldName])
 402           ) {
 403             if ($tableName == 'civicrm_address') {
 404               $this->_addressField = TRUE;
 405             }
 406             if ($tableName == 'civicrm_email') {
 407               $this->_emailField = TRUE;
 408             }
 409
 410             // only include statistics columns if set
 411             if (!empty($field['statistics'])) {
 412               foreach ($field['statistics'] as $stat => $label) {
 413                 switch (strtolower($stat)) {
 414                   case 'sum':
 415                     $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
 416                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 417                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
 418                     $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 419                     break;
 420
 421                   case 'count':
 422                     $select[] = "COUNT({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
 423                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 424                     $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 425                     break;
 426
 427                   case 'avg':
 428                     $select[] = "ROUND(AVG({$field['dbAlias']}),2) as {$tableName}_{$fieldName}_{$stat}";
 429                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
 430                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 431                     $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 432                     break;
 433                 }
 434               }
 435             }
 436             elseif ($fieldName == 'first_donation_date' ||
 437               $fieldName == 'first_donation_amount'
 438             ) {
 439               $baseField = CRM_Utils_Array::value('base_field', $field);
 440               $select[] = "{$this->_aliases['civicrm_contribution']}.{$baseField} as {$tableName}_{$fieldName}";
 441               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 442               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 443             }
 444             else {
 445               $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 446               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 447               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 448             }
 449           }
 450         }
 451       }
 452     }
 453
 454     $this->_select = 'SELECT ' . implode(', ', $select) . ' ';
 455   }
 456
 457   public function from() {
 458     $this->_from = "
 459               FROM civireport_membership_contribution_detail
 460               INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
 461                       ON (civireport_membership_contribution_detail.contribution_id = {$this->_aliases['civicrm_contribution']}.id)
 462               LEFT JOIN civicrm_membership {$this->_aliases['civicrm_membership']}
 463                       ON (civireport_membership_contribution_detail.membership_id = {$this->_aliases['civicrm_membership']}.id)
 464               INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
 465                       ON (civireport_membership_contribution_detail.contact_id = {$this->_aliases['civicrm_contact']}.id)
 466               LEFT  JOIN civicrm_membership_status {$this->_aliases['civicrm_membership_status']}
 467                           ON {$this->_aliases['civicrm_membership_status']}.id =
 468                              {$this->_aliases['civicrm_membership']}.status_id
 469                              {$this->_aclFrom}
 470 ";
 471
 472     //for premiums
 473     if (!empty($this->_params['fields']['product_name']) ||
 474       !empty($this->_params['fields']['product_option']) ||
 475       !empty($this->_params['fields']['fulfilled_date'])
 476     ) {
 477       $this->_from .= "
 478                  LEFT JOIN  civicrm_contribution_product {$this->_aliases['civicrm_contribution_product']}
 479                         ON ({$this->_aliases['civicrm_contribution_product']}.contribution_id = {$this->_aliases['civicrm_contribution']}.id)
 480                  LEFT JOIN  civicrm_product {$this->_aliases['civicrm_product']} ON ({$this->_aliases['civicrm_product']}.id = {$this->_aliases['civicrm_contribution_product']}.product_id)";
 481     }
 482
 483     if (!empty($this->_params['ordinality_value'])) {
 484       $this->_from .= "
 485               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']}
 486                       ON {$this->_aliases['civicrm_contribution_ordinality']}.id = {$this->_aliases['civicrm_contribution']}.id";
 487     }
 488
 489     // include contribution note
 490     if (!empty($this->_params['fields']['contribution_note']) ||
 491       !empty($this->_params['note_value'])
 492     ) {
 493       $this->_from .= "
 494             LEFT JOIN civicrm_note {$this->_aliases['civicrm_note']}
 495                       ON ( {$this->_aliases['civicrm_note']}.entity_table = 'civicrm_contribution' AND
 496                            {$this->_aliases['civicrm_contribution']}.id = {$this->_aliases['civicrm_note']}.entity_id )";
 497
 498     }
 499
 500     if (!empty($this->_params['fields']['phone'])) {
 501       $this->_from .= "
 502                LEFT JOIN  civicrm_phone {$this->_aliases['civicrm_phone']}
 503                       ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND
 504                          {$this->_aliases['civicrm_phone']}.is_primary = 1)";
 505     }
 506     //for contribution batches
 507     if ($this->_allBatches &&
 508       (!empty($this->_params['fields']['batch_id']) ||
 509         !empty($this->_params['bid_value']))
 510     ) {
 511       $this->_from .= "
 512                 LEFT JOIN civicrm_entity_financial_trxn tx ON (tx.entity_id = {$this->_aliases['civicrm_contribution']}.id AND
 513                    tx.entity_table = 'civicrm_contribution')
 514                  LEFT JOIN  civicrm_entity_batch {$this->_aliases['civicrm_entity_batch']}
 515                         ON ({$this->_aliases['civicrm_entity_batch']}.entity_id = tx.financial_trxn_id AND
 516                         {$this->_aliases['civicrm_entity_batch']}.entity_table = 'civicrm_financial_trxn')
 517                  LEFT JOIN civicrm_batch {$this->_aliases['civicrm_batch']}
 518                         ON {$this->_aliases['civicrm_batch']}.id = {$this->_aliases['civicrm_entity_batch']}.batch_id";
 519     }
 520
 521     if ($this->_addressField OR
 522       (!empty($this->_params['state_province_id_value']) OR
 523         !empty($this->_params['country_id_value']))
 524     ) {
 525       $this->_from .= "
 526             LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
 527                    ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_address']}.contact_id AND
 528                       {$this->_aliases['civicrm_address']}.is_primary = 1\n";
 529     }
 530
 531     if ($this->_emailField) {
 532       $this->_from .= "
 533             LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']}
 534                    ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND
 535                       {$this->_aliases['civicrm_email']}.is_primary = 1\n";
 536     }
 537   }
 538
 539   /**
 540    * @param bool $applyLimit
 541    */
 542   public function tempTable($applyLimit = TRUE) {
 543     // create temp table with contact ids,contribtuion id,membership id
 544     $dropTempTable = 'DROP TEMPORARY TABLE IF EXISTS civireport_membership_contribution_detail';
 545     CRM_Core_DAO::executeQuery($dropTempTable);
 546
 547     $sql = 'CREATE TEMPORARY TABLE civireport_membership_contribution_detail
 548             (contribution_id int, INDEX USING HASH(contribution_id), contact_id int, INDEX USING HASH(contact_id),
 549             membership_id int, INDEX USING HASH(membership_id), payment_id int, INDEX USING HASH(payment_id)) ENGINE=MEMORY';
 550     CRM_Core_DAO::executeQuery($sql);
 551
 552     $fillTemp = "
 553           INSERT INTO civireport_membership_contribution_detail (contribution_id, contact_id, membership_id)
 554           SELECT contribution.id, {$this->_aliases['civicrm_contact']}.id, m.id
 555           FROM civicrm_contribution contribution
 556           INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
 557                 ON {$this->_aliases['civicrm_contact']}.id = contribution.contact_id AND contribution.is_test = 0
 558           {$this->_aclFrom}
 559           LEFT JOIN civicrm_membership_payment mp
 560                 ON contribution.id = mp.contribution_id
 561           LEFT JOIN civicrm_membership m
 562                 ON mp.membership_id = m.id AND m.is_test = 0 ";
 563
 564     CRM_Core_DAO::executeQuery($fillTemp);
 565   }
 566
 567   /**
 568    * @param bool $applyLimit
 569    *
 570    * @return string
 571    */
 572   public function buildQuery($applyLimit = TRUE) {
 573     $this->select();
 574     //create temp table to be used as base table
 575     $this->tempTable();
 576     $this->from();
 577     $this->customDataFrom();
 578     $this->where();
 579     $this->groupBy();
 580     $this->orderBy();
 581
 582     // order_by columns not selected for display need to be included in SELECT
 583     $unselectedSectionColumns = $this->unselectedSectionColumns();
 584     foreach ($unselectedSectionColumns as $alias => $section) {
 585       $this->_select .= ", {$section['dbAlias']} as {$alias}";
 586     }
 587
 588     if ($applyLimit && empty($this->_params['charts'])) {
 589       $this->limit();
 590     }
 591
 592     $sql = "{$this->_select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy} {$this->_limit}";
 593     $this->addToDeveloperTab($sql);
 594     return $sql;
 595   }
 596
 597   public function groupBy() {
 598     $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_contact']}.id, {$this->_aliases['civicrm_contribution']}.id ";
 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
 666     //altering the csv display adding additional fields
 667     if ($this->_outputMode == 'csv') {
 668       foreach ($this->_columns as $tableName => $table) {
 669         if (array_key_exists('fields', $table)) {
 670           foreach ($table['fields'] as $fieldName => $field) {
 671             if (!empty($field['csv_display']) && !empty($field['no_display'])) {
 672               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 673               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 674             }
 675           }
 676         }
 677       }
 678     }
 679
 680     // allow repeat for first donation amount and date in csv
 681     $fAmt = '';
 682     $fDate = '';
 683     foreach ($rows as $rowNum => $row) {
 684       if ($this->_outputMode == 'csv') {
 685         if (array_key_exists('civicrm_contact_id', $row)) {
 686           if ($contactId = $row['civicrm_contact_id']) {
 687             if ($rowNum == 0) {
 688               $pcid = $contactId;
 689               $fAmt = $row['first_donation_first_donation_amount'];
 690               $fDate = $row['first_donation_first_donation_date'];
 691             }
 692             else {
 693               if ($pcid == $contactId) {
 694                 $rows[$rowNum]['first_donation_first_donation_amount'] = $fAmt;
 695                 $rows[$rowNum]['first_donation_first_donation_date'] = $fDate;
 696                 $pcid = $contactId;
 697               }
 698               else {
 699                 $fAmt = $row['first_donation_first_donation_amount'];
 700                 $fDate = $row['first_donation_first_donation_date'];
 701                 $pcid = $contactId;
 702               }
 703             }
 704           }
 705         }
 706       }
 707
 708       if (!empty($this->_noRepeats) && $this->_outputMode != 'csv') {
 709         $repeatFound = FALSE;
 710
 711         $display_flag = NULL;
 712         if (array_key_exists('civicrm_contact_id', $row)) {
 713           if ($cid = $row['civicrm_contact_id']) {
 714             if ($rowNum == 0) {
 715               $prev_cid = $cid;
 716             }
 717             else {
 718               if ($prev_cid == $cid) {
 719                 $display_flag = 1;
 720                 $prev_cid = $cid;
 721               }
 722               else {
 723                 $display_flag = 0;
 724                 $prev_cid = $cid;
 725               }
 726             }
 727
 728             if ($display_flag) {
 729               foreach ($row as $colName => $colVal) {
 730                 if (in_array($colName, $this->_noRepeats)) {
 731                   unset($rows[$rowNum][$colName]);
 732                 }
 733               }
 734             }
 735             $entryFound = TRUE;
 736           }
 737         }
 738       }
 739
 740       if (array_key_exists('civicrm_membership_membership_type_id', $row)) {
 741         if ($value = $row['civicrm_membership_membership_type_id']) {
 742           $rows[$rowNum]['civicrm_membership_membership_type_id'] = CRM_Member_PseudoConstant::membershipType($value, FALSE);
 743         }
 744         $entryFound = TRUE;
 745       }
 746
 747       if (array_key_exists('civicrm_batch_batch_id', $row)) {
 748         if ($value = $row['civicrm_batch_batch_id']) {
 749           $rows[$rowNum]['civicrm_batch_batch_id'] = CRM_Core_DAO::getFieldValue('CRM_Batch_DAO_Batch', $value, 'title');
 750         }
 751         $entryFound = TRUE;
 752       }
 753
 754       if (array_key_exists('civicrm_address_state_province_id', $row)) {
 755         if ($value = $row['civicrm_address_state_province_id']) {
 756           $rows[$rowNum]['civicrm_address_state_province_id'] = CRM_Core_PseudoConstant::stateProvince($value, FALSE);
 757         }
 758         $entryFound = TRUE;
 759       }
 760
 761       if (array_key_exists('civicrm_address_country_id', $row)) {
 762         if ($value = $row['civicrm_address_country_id']) {
 763           $rows[$rowNum]['civicrm_address_country_id'] = CRM_Core_PseudoConstant::country($value, FALSE);
 764         }
 765         $entryFound = TRUE;
 766       }
 767
 768       // convert donor sort name to link
 769       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 770         !empty($rows[$rowNum]['civicrm_contact_sort_name']) &&
 771         array_key_exists('civicrm_contact_id', $row)
 772       ) {
 773         $url = CRM_Utils_System::url('civicrm/contact/view',
 774           'reset=1&cid=' . $row['civicrm_contact_id'],
 775           $this->_absoluteUrl
 776         );
 777
 778         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 779         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts('View Contact Summary for this Contact.');
 780       }
 781
 782       if ($value = CRM_Utils_Array::value('civicrm_contribution_financial_type_id', $row)) {
 783         $rows[$rowNum]['civicrm_contribution_financial_type_id'] = $contributionTypes[$value];
 784         $entryFound = TRUE;
 785       }
 786       if ($value = CRM_Utils_Array::value('civicrm_contribution_contribution_status_id', $row)) {
 787         $rows[$rowNum]['civicrm_contribution_contribution_status_id'] = $contributionStatus[$value];
 788         $entryFound = TRUE;
 789       }
 790       if ($value = CRM_Utils_Array::value('civicrm_contribution_payment_instrument_id', $row)) {
 791         $rows[$rowNum]['civicrm_contribution_payment_instrument_id'] = $paymentInstruments[$value];
 792         $entryFound = TRUE;
 793       }
 794       if (($value = CRM_Utils_Array::value('civicrm_contribution_total_amount_sum', $row)) &&
 795         CRM_Core_Permission::check('access CiviContribute')
 796       ) {
 797         $url = CRM_Utils_System::url('civicrm/contact/view/contribution',
 798           'reset=1&id=' . $row['civicrm_contribution_contribution_id'] .
 799           '&cid=' . $row['civicrm_contact_id'] .
 800           '&action=view&context=contribution&selectedChild=contribute',
 801           $this->_absoluteUrl
 802         );
 803         $rows[$rowNum]['civicrm_contribution_total_amount_sum_link'] = $url;
 804         $rows[$rowNum]['civicrm_contribution_total_amount_sum_hover'] = ts('View Details of this Contribution.');
 805         $entryFound = TRUE;
 806       }
 807
 808       // convert campaign_id to campaign title
 809       if (array_key_exists('civicrm_contribution_campaign_id', $row)) {
 810         if ($value = $row['civicrm_contribution_campaign_id']) {
 811           $rows[$rowNum]['civicrm_contribution_campaign_id'] = $this->activeCampaigns[$value];
 812           $entryFound = TRUE;
 813         }
 814       }
 815
 816       $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'member/contributionDetail', 'List all contribution(s) for this ') ? TRUE : $entryFound;
 817
 818       // skip looking further in rows, if first row itself doesn't
 819       // have the column we need
 820       if (!$entryFound) {
 821         break;
 822       }
 823       $lastKey = $rowNum;
 824     }
 825   }
 826
 827 }