CRM/Report/Form/Contribute/Detail.php

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