CRM/Report/Form/Contribute/Detail.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.6                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2014                                |
   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-2014
  32  * $Id$
  33  *
  34  */
  35 class CRM_Report_Form_Contribute_Detail 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 $_softFrom = NULL;
  44
  45   protected $_customGroupExtends = array('Contribution');
  46
  47   /**
  48    */
  49   /**
  50    */
  51   public function __construct() {
  52
  53     // Check if CiviCampaign is a) enabled and b) has active campaigns
  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' => array(
  63           'dao' => 'CRM_Contact_DAO_Contact',
  64           'fields' => array(
  65             'sort_name' => array(
  66               'title' => ts('Donor Name'),
  67               'required' => TRUE,
  68             ),
  69             'first_name' => array(
  70               'title' => ts('First Name'),
  71             ),
  72             'last_name' => array(
  73               'title' => ts('Last Name'),
  74             ),
  75             'id' => array(
  76               'no_display' => TRUE,
  77               'required' => TRUE,
  78             ),
  79             'contact_type' => array(
  80               'title' => ts('Contact Type'),
  81             ),
  82             'contact_sub_type' => array(
  83               'title' => ts('Contact Subtype'),
  84             ),
  85           ),
  86           'filters' => array(
  87             'sort_name' => array(
  88               'title' => ts('Donor Name'),
  89               'operator' => 'like',
  90             ),
  91             'id' => array(
  92               'title' => ts('Contact ID'),
  93               'no_display' => TRUE,
  94               'type' => CRM_Utils_Type::T_INT,
  95             ),
  96           ),
  97           'order_bys' => array(
  98             'sort_name' => array(
  99               'title' => ts('Last Name, First Name'),
 100               'default' => '1',
 101               'default_weight' => '0',
 102               'default_order' => 'ASC',
 103             ),
 104           ),
 105           'grouping' => 'contact-fields',
 106         ),
 107         'civicrm_email' => array(
 108           'dao' => 'CRM_Core_DAO_Email',
 109           'fields' => array(
 110             'email' => array(
 111               'title' => ts('Donor Email'),
 112               'default' => TRUE,
 113             ),
 114           ),
 115           'grouping' => 'contact-fields',
 116         ),
 117         'civicrm_phone' => array(
 118           'dao' => 'CRM_Core_DAO_Phone',
 119           'fields' => array(
 120             'phone' => array(
 121               'title' => ts('Donor Phone'),
 122               'default' => TRUE,
 123               'no_repeat' => TRUE,
 124             ),
 125           ),
 126           'grouping' => 'contact-fields',
 127         ),
 128         'civicrm_contribution' => array(
 129           'dao' => 'CRM_Contribute_DAO_Contribution',
 130           'fields' => array(
 131             'contribution_id' => array(
 132               'name' => 'id',
 133               'no_display' => TRUE,
 134               'required' => TRUE,
 135             ),
 136             'list_contri_id' => array(
 137               'name' => 'id',
 138               'title' => ts('Contribution ID'),
 139             ),
 140             'financial_type_id' => array(
 141               'title' => ts('Financial Type'),
 142               'default' => TRUE,
 143             ),
 144             'contribution_status_id' => array(
 145               'title' => ts('Contribution Status'),
 146             ),
 147             'contribution_page_id' => array(
 148               'title' => ts('Contribution Page'),
 149             ),
 150             'source' => array(
 151               'title' => ts('Source'),
 152             ),
 153             'payment_instrument_id' => array(
 154               'title' => ts('Payment Type'),
 155             ),
 156             'check_number' => array(
 157               'title' => ts('Check Number'),
 158             ),
 159             'currency' => array(
 160               'required' => TRUE,
 161               'no_display' => TRUE,
 162             ),
 163             'trxn_id' => NULL,
 164             'receive_date' => array('default' => TRUE),
 165             'receipt_date' => NULL,
 166             'total_amount' => array(
 167               'title' => ts('Amount'),
 168               'required' => TRUE,
 169               'statistics' => array('sum' => ts('Amount')),
 170             ),
 171             'fee_amount' => NULL,
 172             'net_amount' => NULL,
 173             'contribution_or_soft' => array(
 174               'title' => ts('Contribution OR Soft Credit?'),
 175               'dbAlias' => "'Contribution'",
 176             ),
 177             'soft_credits' => array(
 178               'title' => ts('Soft Credits'),
 179               'dbAlias' => "NULL",
 180             ),
 181             'soft_credit_for' => array(
 182               'title' => ts('Soft Credit For'),
 183               'dbAlias' => "NULL",
 184             ),
 185           ),
 186           'filters' => array(
 187             'contribution_or_soft' => array(
 188               'title' => ts('Contribution OR Soft Credit?'),
 189               'clause' => "(1)",
 190               'operatorType' => CRM_Report_Form::OP_SELECT,
 191               'type' => CRM_Utils_Type::T_STRING,
 192               'options' => array(
 193                 'both' => ts('Both'),
 194                 'contributions_only' => ts('Contributions Only'),
 195                 'soft_credits_only' => ts('Soft Credits Only'),
 196               ),
 197             ),
 198             'receive_date' => array('operatorType' => CRM_Report_Form::OP_DATE),
 199             'currency' => array(
 200               'title' => 'Currency',
 201               'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 202               'options' => CRM_Core_OptionGroup::values('currencies_enabled'),
 203               'default' => NULL,
 204               'type' => CRM_Utils_Type::T_STRING,
 205             ),
 206             'financial_type_id' => array(
 207               'title' => ts('Financial Type'),
 208               'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 209               'options' => CRM_Contribute_PseudoConstant::financialType(),
 210               'type' => CRM_Utils_Type::T_INT,
 211             ),
 212             'contribution_page_id' => array(
 213               'title' => ts('Contribution Page'),
 214               'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 215               'options' => CRM_Contribute_PseudoConstant::contributionPage(),
 216               'type' => CRM_Utils_Type::T_INT,
 217             ),
 218             'payment_instrument_id' => array(
 219               'title' => ts('Payment Type'),
 220               'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 221               'options' => CRM_Contribute_PseudoConstant::paymentInstrument(),
 222               'type' => CRM_Utils_Type::T_INT,
 223             ),
 224             'contribution_status_id' => array(
 225               'title' => ts('Contribution Status'),
 226               'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 227               'options' => CRM_Contribute_PseudoConstant::contributionStatus(),
 228               'default' => array(1),
 229               'type' => CRM_Utils_Type::T_INT,
 230             ),
 231             'total_amount' => array('title' => ts('Contribution Amount')),
 232           ),
 233           'order_bys' => array(
 234             'financial_type_id' => array('title' => ts('Financial Type')),
 235             'contribution_status_id' => array('title' => ts('Contribution Status')),
 236             'payment_instrument_id' => array('title' => ts('Payment Instrument')),
 237             'receive_date' => array('title' => ts('Receive Date')),
 238           ),
 239           'grouping' => 'contri-fields',
 240         ),
 241         'civicrm_contribution_soft' => array(
 242           'dao' => 'CRM_Contribute_DAO_ContributionSoft',
 243           'fields' => array(
 244             'soft_credit_type_id' => array('title' => ts('Soft Credit Type')),
 245           ),
 246           'filters' => array(
 247             'soft_credit_type_id' => array(
 248               'title' => 'Soft Credit Type',
 249               'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 250               'options' => CRM_Core_OptionGroup::values('soft_credit_type'),
 251               'default' => NULL,
 252               'type' => CRM_Utils_Type::T_STRING,
 253             ),
 254           ),
 255         ),
 256         'civicrm_contribution_ordinality' => array(
 257           'dao' => 'CRM_Contribute_DAO_Contribution',
 258           'alias' => 'cordinality',
 259           'filters' => array(
 260             'ordinality' => array(
 261               'title' => ts('Contribution Ordinality'),
 262               'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 263               'options' => array(
 264                 0 => 'First by Contributor',
 265                 1 => 'Second or Later by Contributor',
 266               ),
 267               'type' => CRM_Utils_Type::T_INT,
 268             ),
 269           ),
 270         ),
 271         'civicrm_note' => array(
 272           'dao' => 'CRM_Core_DAO_Note',
 273           'fields' => array(
 274             'contribution_note' => array(
 275               'name' => 'note',
 276               'title' => ts('Contribution Note'),
 277             ),
 278           ),
 279           'filters' => array(
 280             'note' => array(
 281               'name' => 'note',
 282               'title' => ts('Contribution Note'),
 283               'operator' => 'like',
 284               'type' => CRM_Utils_Type::T_STRING,
 285             ),
 286           ),
 287         ),
 288       ) + $this->addAddressFields(FALSE);
 289
 290     $this->_groupFilter = TRUE;
 291     $this->_tagFilter = TRUE;
 292
 293     // Don't show Batch display column and filter unless batches are being used
 294     $this->_allBatches = CRM_Batch_BAO_Batch::getBatches();
 295     if (!empty($this->_allBatches)) {
 296       $this->_columns['civicrm_batch']['dao'] = 'CRM_Batch_DAO_Batch';
 297       $this->_columns['civicrm_batch']['fields']['batch_id'] = array(
 298         'name' => 'id',
 299         'title' => ts('Batch Name'),
 300       );
 301       $this->_columns['civicrm_batch']['filters']['bid'] = array(
 302         'name' => 'id',
 303         'title' => ts('Batch Name'),
 304         'type' => CRM_Utils_Type::T_INT,
 305         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 306         'options' => $this->_allBatches,
 307       );
 308       $this->_columns['civicrm_entity_batch']['dao'] = 'CRM_Batch_DAO_EntityBatch';
 309       $this->_columns['civicrm_entity_batch']['fields']['entity_batch_id'] = array(
 310         'name' => 'batch_id',
 311         'default' => TRUE,
 312         'no_display' => TRUE,
 313       );
 314     }
 315
 316     // If we have active campaigns add those elements to both the fields and filters
 317     if ($campaignEnabled && !empty($this->activeCampaigns)) {
 318       $this->_columns['civicrm_contribution']['fields']['campaign_id'] = array(
 319         'title' => ts('Campaign'),
 320         'default' => 'false',
 321       );
 322       $this->_columns['civicrm_contribution']['filters']['campaign_id'] = array(
 323         'title' => ts('Campaign'),
 324         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 325         'options' => $this->activeCampaigns,
 326       );
 327       $this->_columns['civicrm_contribution']['order_bys']['campaign_id'] = array('title' => ts('Campaign'));
 328     }
 329
 330     $this->_currencyColumn = 'civicrm_contribution_currency';
 331     parent::__construct();
 332   }
 333
 334   public function preProcess() {
 335     parent::preProcess();
 336   }
 337
 338   public function select() {
 339     $this->_columnHeaders = array();
 340
 341     parent::select();
 342     //total_amount was affected by sum as it is considered as one of the stat field
 343     //so it is been replaced with correct alias, CRM-13833
 344     $this->_select = str_replace("sum({$this->_aliases['civicrm_contribution']}.total_amount)", "{$this->_aliases['civicrm_contribution']}.total_amount", $this->_select);
 345   }
 346
 347   public function orderBy() {
 348     parent::orderBy();
 349
 350     // please note this will just add the order-by columns to select query, and not display in column-headers.
 351     // This is a solution to not throw fatal errors when there is a column in order-by, not present in select/display columns.
 352     foreach ($this->_orderByFields as $orderBy) {
 353       if (!array_key_exists($orderBy['name'], $this->_params['fields']) &&
 354         empty($orderBy['section'])
 355       ) {
 356         $this->_select .= ", {$orderBy['dbAlias']} as {$orderBy['tplField']}";
 357       }
 358     }
 359   }
 360
 361   /**
 362    * @param bool $softcredit
 363    */
 364   public function from($softcredit = FALSE) {
 365     $this->_from = "
 366         FROM  civicrm_contact      {$this->_aliases['civicrm_contact']} {$this->_aclFrom}
 367               INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
 368                       ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id AND {$this->_aliases['civicrm_contribution']}.is_test = 0";
 369
 370     if (CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) ==
 371       'both'
 372     ) {
 373       $this->_from .= "\n LEFT JOIN civicrm_contribution_soft contribution_soft_civireport
 374                          ON contribution_soft_civireport.contribution_id = {$this->_aliases['civicrm_contribution']}.id";
 375     }
 376     elseif (CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) ==
 377       'soft_credits_only'
 378     ) {
 379       $this->_from .= "\n INNER JOIN civicrm_contribution_soft contribution_soft_civireport
 380                          ON contribution_soft_civireport.contribution_id = {$this->_aliases['civicrm_contribution']}.id";
 381     }
 382
 383     if ($softcredit) {
 384       $this->_from = "
 385         FROM  civireport_contribution_detail_temp1 temp1_civireport
 386                INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
 387                        ON temp1_civireport.civicrm_contribution_contribution_id = {$this->_aliases['civicrm_contribution']}.id
 388                INNER JOIN civicrm_contribution_soft contribution_soft_civireport
 389                        ON contribution_soft_civireport.contribution_id = {$this->_aliases['civicrm_contribution']}.id
 390                INNER JOIN civicrm_contact      {$this->_aliases['civicrm_contact']}
 391                        ON {$this->_aliases['civicrm_contact']}.id = contribution_soft_civireport.contact_id
 392                {$this->_aclFrom}";
 393     }
 394
 395     if (!empty($this->_params['ordinality_value'])) {
 396       $this->_from .= "
 397               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']}
 398                       ON {$this->_aliases['civicrm_contribution_ordinality']}.id = {$this->_aliases['civicrm_contribution']}.id";
 399     }
 400
 401     $this->addPhoneFromClause();
 402
 403     if ($this->_addressField OR
 404       (!empty($this->_params['state_province_id_value']) OR
 405         !empty($this->_params['country_id_value']))
 406     ) {
 407       $this->_from .= "
 408             LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
 409                    ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_address']}.contact_id AND
 410                       {$this->_aliases['civicrm_address']}.is_primary = 1\n";
 411     }
 412
 413     if ($this->_emailField) {
 414       $this->_from .= "
 415             LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']}
 416                    ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND
 417                       {$this->_aliases['civicrm_email']}.is_primary = 1\n";
 418     }
 419     // include contribution note
 420     if (!empty($this->_params['fields']['contribution_note']) ||
 421       !empty($this->_params['note_value'])
 422     ) {
 423       $this->_from .= "
 424             LEFT JOIN civicrm_note {$this->_aliases['civicrm_note']}
 425                       ON ( {$this->_aliases['civicrm_note']}.entity_table = 'civicrm_contribution' AND
 426                            {$this->_aliases['civicrm_contribution']}.id = {$this->_aliases['civicrm_note']}.entity_id )";
 427     }
 428     //for contribution batches
 429     if ($this->_allBatches &&
 430       (!empty($this->_params['fields']['batch_id']) ||
 431         !empty($this->_params['bid_value']))
 432     ) {
 433       $this->_from .= "
 434                 LEFT JOIN civicrm_entity_financial_trxn tx ON (tx.entity_id = {$this->_aliases['civicrm_contribution']}.id AND
 435                    tx.entity_table = 'civicrm_contribution')
 436                  LEFT JOIN  civicrm_entity_batch {$this->_aliases['civicrm_entity_batch']}
 437                         ON ({$this->_aliases['civicrm_entity_batch']}.entity_id = tx.financial_trxn_id AND
 438                         {$this->_aliases['civicrm_entity_batch']}.entity_table = 'civicrm_financial_trxn')
 439                  LEFT JOIN civicrm_batch {$this->_aliases['civicrm_batch']}
 440                         ON {$this->_aliases['civicrm_batch']}.id = {$this->_aliases['civicrm_entity_batch']}.batch_id";
 441     }
 442
 443   }
 444
 445   public function groupBy() {
 446     $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_contact']}.id, {$this->_aliases['civicrm_contribution']}.id ";
 447   }
 448
 449   /**
 450    * @param $rows
 451    *
 452    * @return array
 453    */
 454   public function statistics(&$rows) {
 455     $statistics = parent::statistics($rows);
 456
 457     $totalAmount = $average = array();
 458     $count = 0;
 459     $select = "
 460         SELECT COUNT({$this->_aliases['civicrm_contribution']}.total_amount ) as count,
 461                SUM( {$this->_aliases['civicrm_contribution']}.total_amount ) as amount,
 462                ROUND(AVG({$this->_aliases['civicrm_contribution']}.total_amount), 2) as avg,
 463                {$this->_aliases['civicrm_contribution']}.currency as currency
 464         ";
 465
 466     $group = "\nGROUP BY {$this->_aliases['civicrm_contribution']}.currency";
 467     $sql = "{$select} {$this->_from} {$this->_where} {$group}";
 468     $dao = CRM_Core_DAO::executeQuery($sql);
 469
 470     while ($dao->fetch()) {
 471       $totalAmount[] =
 472         CRM_Utils_Money::format($dao->amount, $dao->currency) . " (" .
 473         $dao->count . ")";
 474       $average[] = CRM_Utils_Money::format($dao->avg, $dao->currency);
 475       $count += $dao->count;
 476     }
 477     $statistics['counts']['amount'] = array(
 478       'title' => ts('Total Amount (Contributions)'),
 479       'value' => implode(',  ', $totalAmount),
 480       'type' => CRM_Utils_Type::T_STRING,
 481     );
 482     $statistics['counts']['count'] = array(
 483       'title' => ts('Total Contributions'),
 484       'value' => $count,
 485     );
 486     $statistics['counts']['avg'] = array(
 487       'title' => ts('Average'),
 488       'value' => implode(',  ', $average),
 489       'type' => CRM_Utils_Type::T_STRING,
 490     );
 491
 492     // Stats for soft credits
 493     if ($this->_softFrom &&
 494       CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) !=
 495       'contributions_only'
 496     ) {
 497       $totalAmount = $average = array();
 498       $count = 0;
 499       $select = "
 500 SELECT COUNT(contribution_soft_civireport.amount ) as count,
 501        SUM(contribution_soft_civireport.amount ) as amount,
 502        ROUND(AVG(contribution_soft_civireport.amount), 2) as avg,
 503        {$this->_aliases['civicrm_contribution']}.currency as currency";
 504       $sql = "
 505 {$select}
 506 {$this->_softFrom}
 507 GROUP BY {$this->_aliases['civicrm_contribution']}.currency";
 508       $dao = CRM_Core_DAO::executeQuery($sql);
 509       while ($dao->fetch()) {
 510         $totalAmount[] =
 511           CRM_Utils_Money::format($dao->amount, $dao->currency) . " (" .
 512           $dao->count . ")";
 513         $average[] = CRM_Utils_Money::format($dao->avg, $dao->currency);
 514         $count += $dao->count;
 515       }
 516       $statistics['counts']['softamount'] = array(
 517         'title' => ts('Total Amount (Soft Credits)'),
 518         'value' => implode(',  ', $totalAmount),
 519         'type' => CRM_Utils_Type::T_STRING,
 520       );
 521       $statistics['counts']['softcount'] = array(
 522         'title' => ts('Total Soft Credits'),
 523         'value' => $count,
 524       );
 525       $statistics['counts']['softavg'] = array(
 526         'title' => ts('Average (Soft Credits)'),
 527         'value' => implode(',  ', $average),
 528         'type' => CRM_Utils_Type::T_STRING,
 529       );
 530     }
 531
 532     return $statistics;
 533   }
 534
 535   public function postProcess() {
 536     // get the acl clauses built before we assemble the query
 537     $this->buildACLClause($this->_aliases['civicrm_contact']);
 538
 539     $this->beginPostProcess();
 540
 541     if (CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) ==
 542       'contributions_only' &&
 543       !empty($this->_params['fields']['soft_credit_type_id'])
 544     ) {
 545       unset($this->_params['fields']['soft_credit_type_id']);
 546       if (!empty($this->_params['soft_credit_type_id_value'])) {
 547         $this->_params['soft_credit_type_id_value'] = array();
 548       }
 549     }
 550
 551     // 1. use main contribution query to build temp table 1
 552     $sql = $this->buildQuery();
 553     $tempQuery =
 554       'CREATE TEMPORARY TABLE civireport_contribution_detail_temp1 AS ' . $sql;
 555     CRM_Core_DAO::executeQuery($tempQuery);
 556     $this->setPager();
 557
 558     // 2. customize main contribution query for soft credit, and build temp table 2 with soft credit contributions only
 559     $this->from(TRUE);
 560     // also include custom group from if included
 561     // since this might be included in select
 562     $this->customDataFrom();
 563
 564     $select = str_ireplace('contribution_civireport.total_amount', 'contribution_soft_civireport.amount', $this->_select);
 565     $select = str_ireplace("'Contribution' as", "'Soft Credit' as", $select);
 566     // we inner join with temp1 to restrict soft contributions to those in temp1 table
 567     $sql = "{$select} {$this->_from} {$this->_where} {$this->_groupBy}";
 568     $tempQuery =
 569       'CREATE TEMPORARY TABLE civireport_contribution_detail_temp2 AS ' . $sql;
 570     CRM_Core_DAO::executeQuery($tempQuery);
 571     if (CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) ==
 572       'soft_credits_only'
 573     ) {
 574       // revise pager : prev, next based on soft-credits only
 575       $this->setPager();
 576     }
 577
 578     // copy _from for later use of stats calculation for soft credits, and reset $this->_from to main query
 579     $this->_softFrom = $this->_from;
 580
 581     // simple reset of ->_from
 582     $this->from();
 583
 584     // also include custom group from if included
 585     // since this might be included in select
 586     $this->customDataFrom();
 587
 588     // 3. Decide where to populate temp3 table from
 589     if (CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) ==
 590       'contributions_only'
 591     ) {
 592       $tempQuery = "(SELECT * FROM civireport_contribution_detail_temp1)";
 593     }
 594     elseif (CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) ==
 595       'soft_credits_only'
 596     ) {
 597       $tempQuery = "(SELECT * FROM civireport_contribution_detail_temp2)";
 598     }
 599     else {
 600       $tempQuery = "
 601 (SELECT * FROM civireport_contribution_detail_temp1)
 602 UNION ALL
 603 (SELECT * FROM civireport_contribution_detail_temp2)";
 604     }
 605
 606     // 4. build temp table 3
 607     $sql = "CREATE TEMPORARY TABLE civireport_contribution_detail_temp3 AS {$tempQuery}";
 608     CRM_Core_DAO::executeQuery($sql);
 609
 610     // 5. Re-construct order-by to make sense for final query on temp3 table
 611     $orderBy = '';
 612     if (!empty($this->_orderByArray)) {
 613       $aliases = array_flip($this->_aliases);
 614       $orderClause = array();
 615       foreach ($this->_orderByArray as $clause) {
 616         list($alias, $rest) = explode('.', $clause);
 617         $orderClause[] = $aliases[$alias] . "_" . $rest;
 618       }
 619       $orderBy = (!empty($orderClause)) ? "ORDER BY " . implode(', ', $orderClause) : '';
 620     }
 621
 622     // 6. show result set from temp table 3
 623     $rows = array();
 624     $sql = "SELECT * FROM civireport_contribution_detail_temp3 {$orderBy}";
 625     $this->buildRows($sql, $rows);
 626
 627     // format result set.
 628     $this->formatDisplay($rows, FALSE);
 629
 630     // assign variables to templates
 631     $this->doTemplateAssignment($rows);
 632
 633     // do print / pdf / instance stuff if needed
 634     $this->endPostProcess($rows);
 635   }
 636
 637   /**
 638    * @param $rows
 639    */
 640   public function alterDisplay(&$rows) {
 641     // custom code to alter rows
 642     $checkList = array();
 643     $entryFound = FALSE;
 644     $display_flag = $prev_cid = $cid = 0;
 645     $contributionTypes = CRM_Contribute_PseudoConstant::financialType();
 646     $contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus();
 647     $paymentInstruments = CRM_Contribute_PseudoConstant::paymentInstrument();
 648     $contributionPages = CRM_Contribute_PseudoConstant::contributionPage();
 649
 650     foreach ($rows as $rowNum => $row) {
 651       if (!empty($this->_noRepeats) && $this->_outputMode != 'csv') {
 652         // don't repeat contact details if its same as the previous row
 653         if (array_key_exists('civicrm_contact_id', $row)) {
 654           if ($cid = $row['civicrm_contact_id']) {
 655             if ($rowNum == 0) {
 656               $prev_cid = $cid;
 657             }
 658             else {
 659               if ($prev_cid == $cid) {
 660                 $display_flag = 1;
 661                 $prev_cid = $cid;
 662               }
 663               else {
 664                 $display_flag = 0;
 665                 $prev_cid = $cid;
 666               }
 667             }
 668
 669             if ($display_flag) {
 670               foreach ($row as $colName => $colVal) {
 671                 // Hide repeats in no-repeat columns, but not if the field's a section header
 672                 if (in_array($colName, $this->_noRepeats) &&
 673                   !array_key_exists($colName, $this->_sections)
 674                 ) {
 675                   unset($rows[$rowNum][$colName]);
 676                 }
 677               }
 678             }
 679             $entryFound = TRUE;
 680           }
 681         }
 682       }
 683
 684       if (CRM_Utils_Array::value('civicrm_contribution_contribution_or_soft', $rows[$rowNum]) ==
 685         'Contribution'
 686       ) {
 687         unset($rows[$rowNum]['civicrm_contribution_soft_soft_credit_type_id']);
 688       }
 689
 690       // convert donor sort name to link
 691       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 692         !empty($rows[$rowNum]['civicrm_contact_sort_name']) &&
 693         array_key_exists('civicrm_contact_id', $row)
 694       ) {
 695         $url = CRM_Utils_System::url("civicrm/contact/view",
 696           'reset=1&cid=' . $row['civicrm_contact_id'],
 697           $this->_absoluteUrl
 698         );
 699         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 700         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact Summary for this Contact.");
 701       }
 702
 703       if ($value = CRM_Utils_Array::value('civicrm_contribution_financial_type_id', $row)) {
 704         $rows[$rowNum]['civicrm_contribution_financial_type_id'] = $contributionTypes[$value];
 705         $entryFound = TRUE;
 706       }
 707       if ($value = CRM_Utils_Array::value('civicrm_contribution_contribution_status_id', $row)) {
 708         $rows[$rowNum]['civicrm_contribution_contribution_status_id'] = $contributionStatus[$value];
 709         $entryFound = TRUE;
 710       }
 711       if ($value = CRM_Utils_Array::value('civicrm_contribution_contribution_page_id', $row)) {
 712         $rows[$rowNum]['civicrm_contribution_contribution_page_id'] = $contributionPages[$value];
 713         $entryFound = TRUE;
 714       }
 715       if ($value = CRM_Utils_Array::value('civicrm_contribution_payment_instrument_id', $row)) {
 716         $rows[$rowNum]['civicrm_contribution_payment_instrument_id'] = $paymentInstruments[$value];
 717         $entryFound = TRUE;
 718       }
 719       if (array_key_exists('civicrm_batch_batch_id', $row)) {
 720         if ($value = $row['civicrm_batch_batch_id']) {
 721           $rows[$rowNum]['civicrm_batch_batch_id'] = CRM_Core_DAO::getFieldValue('CRM_Batch_DAO_Batch', $value, 'title');
 722         }
 723         $entryFound = TRUE;
 724       }
 725
 726       // Contribution amount links to viewing contribution
 727       if (($value = CRM_Utils_Array::value('civicrm_contribution_total_amount_sum', $row)) &&
 728         CRM_Core_Permission::check('access CiviContribute')
 729       ) {
 730         $url = CRM_Utils_System::url("civicrm/contact/view/contribution",
 731           "reset=1&id=" . $row['civicrm_contribution_contribution_id'] .
 732           "&cid=" . $row['civicrm_contact_id'] .
 733           "&action=view&context=contribution&selectedChild=contribute",
 734           $this->_absoluteUrl
 735         );
 736         $rows[$rowNum]['civicrm_contribution_total_amount_sum_link'] = $url;
 737         $rows[$rowNum]['civicrm_contribution_total_amount_sum_hover'] = ts("View Details of this Contribution.");
 738         $entryFound = TRUE;
 739       }
 740
 741       // convert campaign_id to campaign title
 742       if (array_key_exists('civicrm_contribution_campaign_id', $row)) {
 743         if ($value = $row['civicrm_contribution_campaign_id']) {
 744           $rows[$rowNum]['civicrm_contribution_campaign_id'] = $this->activeCampaigns[$value];
 745           $entryFound = TRUE;
 746         }
 747       }
 748
 749       // soft credits
 750       if (array_key_exists('civicrm_contribution_soft_credits', $row) &&
 751         'Contribution' ==
 752         CRM_Utils_Array::value('civicrm_contribution_contribution_or_soft', $rows[$rowNum]) &&
 753         array_key_exists('civicrm_contribution_contribution_id', $row)
 754       ) {
 755         $query = "
 756 SELECT civicrm_contact_id, civicrm_contact_sort_name, civicrm_contribution_total_amount_sum, civicrm_contribution_currency
 757 FROM   civireport_contribution_detail_temp2
 758 WHERE  civicrm_contribution_contribution_id={$row['civicrm_contribution_contribution_id']}";
 759         $dao = CRM_Core_DAO::executeQuery($query);
 760         $string = '';
 761         $separator = ($this->_outputMode !== 'csv') ? "<br/>" : ' ';
 762         while ($dao->fetch()) {
 763           $url = CRM_Utils_System::url("civicrm/contact/view", 'reset=1&cid=' .
 764             $dao->civicrm_contact_id);
 765           $string = $string . ($string ? $separator : '') .
 766             "<a href='{$url}'>{$dao->civicrm_contact_sort_name}</a> " .
 767             CRM_Utils_Money::format($dao->civicrm_contribution_total_amount_sum, $dao->civicrm_contribution_currency);
 768         }
 769         $rows[$rowNum]['civicrm_contribution_soft_credits'] = $string;
 770       }
 771
 772       if (array_key_exists('civicrm_contribution_soft_credit_for', $row) &&
 773         'Soft Credit' ==
 774         CRM_Utils_Array::value('civicrm_contribution_contribution_or_soft', $rows[$rowNum]) &&
 775         array_key_exists('civicrm_contribution_contribution_id', $row)
 776       ) {
 777         $query = "
 778 SELECT civicrm_contact_id, civicrm_contact_sort_name
 779 FROM   civireport_contribution_detail_temp1
 780 WHERE  civicrm_contribution_contribution_id={$row['civicrm_contribution_contribution_id']}";
 781         $dao = CRM_Core_DAO::executeQuery($query);
 782         $string = '';
 783         while ($dao->fetch()) {
 784           $url = CRM_Utils_System::url("civicrm/contact/view", 'reset=1&cid=' .
 785             $dao->civicrm_contact_id);
 786           $string = $string .
 787             "\n<a href='{$url}'>{$dao->civicrm_contact_sort_name}</a>";
 788         }
 789         $rows[$rowNum]['civicrm_contribution_soft_credit_for'] = $string;
 790       }
 791
 792       //convert soft_credit_type_id into label
 793       if (array_key_exists('civicrm_contribution_soft_soft_credit_type_id', $rows[$rowNum])) {
 794         $rows[$rowNum]['civicrm_contribution_soft_soft_credit_type_id'] = CRM_Core_OptionGroup::getLabel('soft_credit_type',
 795           $row['civicrm_contribution_soft_soft_credit_type_id']);
 796       }
 797
 798       $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'contribute/detail', 'List all contribution(s) for this ') ? TRUE : $entryFound;
 799
 800       // skip looking further in rows, if first row itself doesn't
 801       // have the column we need
 802       if (!$entryFound) {
 803         break;
 804       }
 805       $lastKey = $rowNum;
 806     }
 807   }
 808
 809   public function sectionTotals() {
 810
 811     // Reports using order_bys with sections must populate $this->_selectAliases in select() method.
 812     if (empty($this->_selectAliases)) {
 813       return;
 814     }
 815
 816     if (!empty($this->_sections)) {
 817       // build the query with no LIMIT clause
 818       $select = str_ireplace('SELECT SQL_CALC_FOUND_ROWS ', 'SELECT ', $this->_select);
 819       $sql = "{$select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy}";
 820
 821       // pull section aliases out of $this->_sections
 822       $sectionAliases = array_keys($this->_sections);
 823
 824       $ifnulls = array();
 825       foreach (array_merge($sectionAliases, $this->_selectAliases) as $alias) {
 826         $ifnulls[] = "ifnull($alias, '') as $alias";
 827       }
 828
 829       /* Group (un-limited) report by all aliases and get counts. This might
 830       * be done more efficiently when the contents of $sql are known, ie. by
 831       * overriding this method in the report class.
 832       */
 833
 834       $addtotals = '';
 835
 836       if (array_search("civicrm_contribution_total_amount_sum", $this->_selectAliases) !==
 837         FALSE
 838       ) {
 839         $addtotals = ", sum(civicrm_contribution_total_amount_sum) as sumcontribs";
 840         $showsumcontribs = TRUE;
 841       }
 842
 843       $query = "select "
 844         . implode(", ", $ifnulls)
 845         .
 846         "$addtotals, count(*) as ct from civireport_contribution_detail_temp3 group by " .
 847         implode(", ", $sectionAliases);
 848       // initialize array of total counts
 849       $sumcontribs = $totals = array();
 850       $dao = CRM_Core_DAO::executeQuery($query);
 851       while ($dao->fetch()) {
 852
 853         // let $this->_alterDisplay translate any integer ids to human-readable values.
 854         $rows[0] = $dao->toArray();
 855         $this->alterDisplay($rows);
 856         $row = $rows[0];
 857
 858         // add totals for all permutations of section values
 859         $values = array();
 860         $i = 1;
 861         $aliasCount = count($sectionAliases);
 862         foreach ($sectionAliases as $alias) {
 863           $values[] = $row[$alias];
 864           $key = implode(CRM_Core_DAO::VALUE_SEPARATOR, $values);
 865           if ($i == $aliasCount) {
 866             // the last alias is the lowest-level section header; use count as-is
 867             $totals[$key] = $dao->ct;
 868             if ($showsumcontribs) {
 869               $sumcontribs[$key] = $dao->sumcontribs;
 870             }
 871           }
 872           else {
 873             // other aliases are higher level; roll count into their total
 874             $totals[$key] = (array_key_exists($key, $totals)) ? $totals[$key] + $dao->ct : $dao->ct;
 875             if ($showsumcontribs) {
 876               $sumcontribs[$key] = array_key_exists($key, $sumcontribs) ? $sumcontribs[$key] + $dao->sumcontribs : $dao->sumcontribs;
 877             }
 878           }
 879         }
 880       }
 881       if ($showsumcontribs) {
 882         $totalandsum = array();
 883         // ts exception to avoid having ts("%1 %2: %3")
 884         $title = '%1 contributions / soft-credits: %2';
 885
 886         if (CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) ==
 887           'contributions_only'
 888         ) {
 889           $title = '%1 contributions: %2';
 890         }
 891         elseif (CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) ==
 892           'soft_credits_only'
 893         ) {
 894           $title = '%1 soft-credits: %2';
 895         }
 896         foreach ($totals as $key => $total) {
 897           $totalandsum[$key] = ts($title, array(
 898             1 => $total,
 899             2 => CRM_Utils_Money::format($sumcontribs[$key]),
 900           ));
 901         }
 902         $this->assign('sectionTotals', $totalandsum);
 903       }
 904       else {
 905         $this->assign('sectionTotals', $totals);
 906       }
 907     }
 908   }
 909 }