CRM/Report/Form/Contribute/Detail.php

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