CRM/Report/Form/Contribute/Detail.php

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