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