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