CRM/Report/Form/Contribute/Detail.php

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