CRM/Report/Form/Contribute/Summary.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  */
  33 class CRM_Report_Form_Contribute_Summary extends CRM_Report_Form {
  34   protected $_addressField = FALSE;
  35
  36   protected $_charts = array(
  37     '' => 'Tabular',
  38     'barChart' => 'Bar Chart',
  39     'pieChart' => 'Pie Chart',
  40   );
  41   protected $_customGroupExtends = array('Contribution');
  42   protected $_customGroupGroupBy = TRUE;
  43
  44   public $_drilldownReport = array('contribute/detail' => 'Link to Detail Report');
  45
  46   /**
  47    * To what frequency group-by a date column
  48    *
  49    * @var array
  50    */
  51   protected $_groupByDateFreq = array(
  52     'MONTH' => 'Month',
  53     'YEARWEEK' => 'Week',
  54     'DATE' => 'Day',
  55     'QUARTER' => 'Quarter',
  56     'YEAR' => 'Year',
  57     'FISCALYEAR' => 'Fiscal Year',
  58   );
  59
  60   /**
  61    * This report has been optimised for group filtering.
  62    *
  63    * CRM-19170
  64    *
  65    * @var bool
  66    */
  67   protected $groupFilterNotOptimised = FALSE;
  68
  69   /**
  70    * Class constructor.
  71    */
  72   public function __construct() {
  73
  74     // Check if CiviCampaign is a) enabled and b) has active campaigns
  75     $config = CRM_Core_Config::singleton();
  76     $campaignEnabled = in_array("CiviCampaign", $config->enableComponents);
  77     if ($campaignEnabled) {
  78       $getCampaigns = CRM_Campaign_BAO_Campaign::getPermissionedCampaigns(NULL, NULL, TRUE, FALSE, TRUE);
  79       $this->activeCampaigns = $getCampaigns['campaigns'];
  80       asort($this->activeCampaigns);
  81     }
  82
  83     $this->_columns = array(
  84       'civicrm_contact' => array(
  85         'dao' => 'CRM_Contact_DAO_Contact',
  86         'fields' => array_merge(
  87           $this->getBasicContactFields(),
  88           array(
  89             'sort_name' => array(
  90               'title' => ts('Contact Name'),
  91               'no_repeat' => TRUE,
  92             ),
  93           )
  94         ),
  95         'grouping' => 'contact-fields',
  96         'group_bys' => array(
  97           'id' => array('title' => ts('Contact ID')),
  98           'sort_name' => array(
  99             'title' => ts('Contact Name'),
 100           ),
 101         ),
 102       ),
 103       'civicrm_email' => array(
 104         'dao' => 'CRM_Core_DAO_Email',
 105         'fields' => array(
 106           'email' => array(
 107             'title' => ts('Email'),
 108             'no_repeat' => TRUE,
 109           ),
 110         ),
 111         'grouping' => 'contact-fields',
 112       ),
 113       'civicrm_line_item' => array(
 114         'dao' => 'CRM_Price_DAO_LineItem',
 115       ),
 116       'civicrm_phone' => array(
 117         'dao' => 'CRM_Core_DAO_Phone',
 118         'fields' => array(
 119           'phone' => array(
 120             'title' => ts('Phone'),
 121             'no_repeat' => TRUE,
 122           ),
 123         ),
 124         'grouping' => 'contact-fields',
 125       ),
 126       'civicrm_financial_type' => array(
 127         'dao' => 'CRM_Financial_DAO_FinancialType',
 128         'fields' => array('financial_type' => NULL),
 129         'grouping' => 'contri-fields',
 130         'group_bys' => array(
 131           'financial_type' => array('title' => ts('Financial Type')),
 132         ),
 133       ),
 134       'civicrm_contribution' => array(
 135         'dao' => 'CRM_Contribute_DAO_Contribution',
 136           //'bao'           => 'CRM_Contribute_BAO_Contribution',
 137         'fields' => array(
 138           'contribution_status_id' => array(
 139             'title' => ts('Contribution Status'),
 140           ),
 141           'contribution_source' => array('title' => ts('Source')),
 142           'currency' => array(
 143             'required' => TRUE,
 144             'no_display' => TRUE,
 145           ),
 146           'total_amount' => array(
 147             'title' => ts('Contribution Amount Stats'),
 148             'default' => TRUE,
 149             'statistics' => array(
 150               'count' => ts('Contributions'),
 151               'sum' => ts('Contribution Aggregate'),
 152               'avg' => ts('Contribution Avg'),
 153             ),
 154           ),
 155         ),
 156         'grouping' => 'contri-fields',
 157         'filters' => array(
 158           'receive_date' => array('operatorType' => CRM_Report_Form::OP_DATE),
 159           'contribution_status_id' => array(
 160             'title' => ts('Contribution Status'),
 161             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 162             'options' => CRM_Contribute_PseudoConstant::contributionStatus(),
 163             'default' => array(1),
 164             'type' => CRM_Utils_Type::T_INT,
 165           ),
 166           'currency' => array(
 167             'title' => ts('Currency'),
 168             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 169             'options' => CRM_Core_OptionGroup::values('currencies_enabled'),
 170             'default' => NULL,
 171             'type' => CRM_Utils_Type::T_STRING,
 172           ),
 173           'financial_type_id' => array(
 174             'title' => ts('Financial Type'),
 175             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 176             'options' => CRM_Financial_BAO_FinancialType::getAvailableFinancialTypes(),
 177             'type' => CRM_Utils_Type::T_INT,
 178           ),
 179           'contribution_page_id' => array(
 180             'title' => ts('Contribution Page'),
 181             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 182             'options' => CRM_Contribute_PseudoConstant::contributionPage(),
 183             'type' => CRM_Utils_Type::T_INT,
 184           ),
 185           'total_amount' => array(
 186             'title' => ts('Contribution Amount'),
 187           ),
 188           'total_sum' => array(
 189             'title' => ts('Contribution Aggregate'),
 190             'type' => CRM_Report_Form::OP_INT,
 191             'dbAlias' => 'civicrm_contribution_total_amount_sum',
 192             'having' => TRUE,
 193           ),
 194           'total_count' => array(
 195             'title' => ts('Contribution Count'),
 196             'type' => CRM_Report_Form::OP_INT,
 197             'dbAlias' => 'civicrm_contribution_total_amount_count',
 198             'having' => TRUE,
 199           ),
 200           'total_avg' => array(
 201             'title' => ts('Contribution Avg'),
 202             'type' => CRM_Report_Form::OP_INT,
 203             'dbAlias' => 'civicrm_contribution_total_amount_avg',
 204             'having' => TRUE,
 205           ),
 206         ),
 207         'group_bys' => array(
 208           'receive_date' => array(
 209             'frequency' => TRUE,
 210             'default' => TRUE,
 211             'chart' => TRUE,
 212           ),
 213           'contribution_source' => NULL,
 214           'contribution_status_id' => array(
 215             'title' => ts('Contribution Status'),
 216             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 217             'options' => CRM_Contribute_PseudoConstant::contributionStatus(),
 218             'default' => array(1),
 219             'type' => CRM_Utils_Type::T_INT,
 220           ),
 221         ),
 222       ),
 223       'civicrm_batch' => array(
 224         'dao' => 'CRM_Batch_DAO_EntityBatch',
 225         'grouping' => 'contri-fields',
 226         'filters' => array(
 227           'batch_id' => array(
 228             'title' => ts('Batch Title'),
 229             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 230             'options' => CRM_Batch_BAO_Batch::getBatches(),
 231             'type' => CRM_Utils_Type::T_INT,
 232           ),
 233         ),
 234       ),
 235       'civicrm_contribution_soft' => array(
 236         'dao' => 'CRM_Contribute_DAO_ContributionSoft',
 237         'fields' => array(
 238           'soft_amount' => array(
 239             'title' => ts('Soft Credit Amount Stats'),
 240             'name' => 'amount',
 241             'statistics' => array(
 242               'count' => ts('Soft Credits'),
 243               'sum' => ts('Soft Credit Aggregate'),
 244               'avg' => ts('Soft Credit Avg'),
 245             ),
 246           ),
 247         ),
 248         'grouping' => 'contri-fields',
 249         'filters' => array(
 250           'amount' => array(
 251             'title' => ts('Soft Credit Amount'),
 252           ),
 253           'soft_credit_type_id' => array(
 254             'title' => ts('Soft Credit Type'),
 255             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 256             'options' => CRM_Core_OptionGroup::values('soft_credit_type'),
 257             'default' => NULL,
 258             'type' => CRM_Utils_Type::T_STRING,
 259           ),
 260           'soft_sum' => array(
 261             'title' => ts('Soft Credit Aggregate'),
 262             'type' => CRM_Report_Form::OP_INT,
 263             'dbAlias' => 'civicrm_contribution_soft_soft_amount_sum',
 264             'having' => TRUE,
 265           ),
 266           'soft_count' => array(
 267             'title' => ts('Soft Credits Count'),
 268             'type' => CRM_Report_Form::OP_INT,
 269             'dbAlias' => 'civicrm_contribution_soft_soft_amount_count',
 270             'having' => TRUE,
 271           ),
 272           'soft_avg' => array(
 273             'title' => ts('Soft Credit Avg'),
 274             'type' => CRM_Report_Form::OP_INT,
 275             'dbAlias' => 'civicrm_contribution_soft_soft_amount_avg',
 276             'having' => TRUE,
 277           ),
 278         ),
 279       ),
 280     ) + $this->addAddressFields();
 281
 282     // If we have a campaign, build out the relevant elements
 283     if ($campaignEnabled && !empty($this->activeCampaigns)) {
 284       $this->_columns['civicrm_contribution']['fields']['campaign_id'] = array(
 285         'title' => ts('Campaign'),
 286         'default' => 'false',
 287       );
 288       $this->_columns['civicrm_contribution']['filters']['campaign_id'] = array(
 289         'title' => ts('Campaign'),
 290         'type' => CRM_Utils_Type::T_INT,
 291         'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 292         'options' => $this->activeCampaigns,
 293       );
 294       $this->_columns['civicrm_contribution']['group_bys']['campaign_id'] = array('title' => ts('Campaign'));
 295     }
 296
 297     $this->_tagFilter = TRUE;
 298     $this->_groupFilter = TRUE;
 299     $this->_currencyColumn = 'civicrm_contribution_currency';
 300     parent::__construct();
 301   }
 302
 303   /**
 304    * Set select clause.
 305    */
 306   public function select() {
 307     $select = array();
 308     $this->_columnHeaders = array();
 309     foreach ($this->_columns as $tableName => $table) {
 310       if (array_key_exists('group_bys', $table)) {
 311         foreach ($table['group_bys'] as $fieldName => $field) {
 312           if ($tableName == 'civicrm_address') {
 313             $this->_addressField = TRUE;
 314           }
 315           if (!empty($this->_params['group_bys'][$fieldName])) {
 316             switch (CRM_Utils_Array::value($fieldName, $this->_params['group_bys_freq'])) {
 317               case 'YEARWEEK':
 318                 $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL WEEKDAY({$field['dbAlias']}) DAY) AS {$tableName}_{$fieldName}_start";
 319                 $select[] = "YEARWEEK({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
 320                 $select[] = "WEEKOFYEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
 321                 $field['title'] = ts('Week Beginning');
 322                 break;
 323
 324               case 'YEAR':
 325                 $select[] = "MAKEDATE(YEAR({$field['dbAlias']}), 1)  AS {$tableName}_{$fieldName}_start";
 326                 $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
 327                 $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
 328                 $field['title'] = ts('Year Beginning');
 329                 break;
 330
 331               case 'FISCALYEAR':
 332                 $config = CRM_Core_Config::singleton();
 333                 $fy = $config->fiscalYearStart;
 334                 $fiscal = self::fiscalYearOffset($field['dbAlias']);
 335
 336                 $select[] = "DATE_ADD(MAKEDATE({$fiscal}, 1), INTERVAL ({$fy{'M'}})-1 MONTH) AS {$tableName}_{$fieldName}_start";
 337                 $select[] = "{$fiscal} AS {$tableName}_{$fieldName}_subtotal";
 338                 $select[] = "{$fiscal} AS {$tableName}_{$fieldName}_interval";
 339                 $field['title'] = ts('Fiscal Year Beginning');
 340                 break;
 341
 342               case 'MONTH':
 343                 $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL (DAYOFMONTH({$field['dbAlias']})-1) DAY) as {$tableName}_{$fieldName}_start";
 344                 $select[] = "MONTH({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
 345                 $select[] = "MONTHNAME({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
 346                 $field['title'] = ts('Month Beginning');
 347                 break;
 348
 349               case 'DATE':
 350                 $select[] = "DATE({$field['dbAlias']}) as {$tableName}_{$fieldName}_start";
 351                 $field['title'] = ts('Date');
 352                 break;
 353
 354               case 'QUARTER':
 355                 $select[] = "STR_TO_DATE(CONCAT( 3 * QUARTER( {$field['dbAlias']} ) -2 , '/', '1', '/', YEAR( {$field['dbAlias']} ) ), '%m/%d/%Y') AS {$tableName}_{$fieldName}_start";
 356                 $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
 357                 $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
 358                 $field['title'] = 'Quarter';
 359                 break;
 360             }
 361             if (!empty($this->_params['group_bys_freq'][$fieldName])) {
 362               $this->_interval = $this->_params['group_bys_freq'][$fieldName];
 363               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['title'] = $field['title'];
 364               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['type'] = $field['type'];
 365               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['group_by'] = $this->_params['group_bys_freq'][$fieldName];
 366
 367               // just to make sure these values are transferred to rows.
 368               // since we need that for calculation purpose,
 369               // e.g making subtotals look nicer or graphs
 370               $this->_columnHeaders["{$tableName}_{$fieldName}_interval"] = array('no_display' => TRUE);
 371               $this->_columnHeaders["{$tableName}_{$fieldName}_subtotal"] = array('no_display' => TRUE);
 372             }
 373           }
 374         }
 375       }
 376
 377       if (array_key_exists('fields', $table)) {
 378         foreach ($table['fields'] as $fieldName => $field) {
 379           if ($tableName == 'civicrm_address') {
 380             $this->_addressField = TRUE;
 381           }
 382           if (!empty($field['required']) ||
 383             !empty($this->_params['fields'][$fieldName])
 384           ) {
 385             // only include statistics columns if set
 386             if (!empty($field['statistics'])) {
 387               foreach ($field['statistics'] as $stat => $label) {
 388                 $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 389                 $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = $field['type'];
 390                 $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 391                 switch (strtolower($stat)) {
 392                   case 'sum':
 393                     $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
 394                     break;
 395
 396                   case 'count':
 397                     $select[] = "COUNT({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
 398                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_INT;
 399                     break;
 400
 401                   case 'avg':
 402                     $select[] = "ROUND(AVG({$field['dbAlias']}),2) as {$tableName}_{$fieldName}_{$stat}";
 403                     break;
 404                 }
 405               }
 406             }
 407             else {
 408               $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 409               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 410               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 411             }
 412           }
 413         }
 414       }
 415     }
 416
 417     $this->_selectClauses = $select;
 418     $this->_select = "SELECT " . implode(', ', $select) . " ";
 419   }
 420
 421   /**
 422    * Set form rules.
 423    *
 424    * @param array $fields
 425    * @param array $files
 426    * @param CRM_Report_Form_Contribute_Summary $self
 427    *
 428    * @return array
 429    */
 430   public static function formRule($fields, $files, $self) {
 431     // Check for searching combination of display columns and
 432     // grouping criteria
 433     $ignoreFields = array('total_amount', 'sort_name');
 434     $errors = $self->customDataFormRule($fields, $ignoreFields);
 435
 436     if (empty($fields['fields']['total_amount'])) {
 437       foreach (array(
 438                  'total_count_value',
 439                  'total_sum_value',
 440                  'total_avg_value',
 441                ) as $val) {
 442         if (!empty($fields[$val])) {
 443           $errors[$val] = ts("Please select the Amount Statistics");
 444         }
 445       }
 446     }
 447
 448     return $errors;
 449   }
 450
 451   /**
 452    * Set from clause.
 453    *
 454    * @param string $entity
 455    *
 456    * @todo fix function signature to match parent. Remove hacky passing of $entity
 457    * to acheive unclear results.
 458    */
 459   public function from($entity = NULL) {
 460     $softCreditJoinType = "LEFT";
 461     if (!empty($this->_params['fields']['soft_amount']) &&
 462       empty($this->_params['fields']['total_amount'])
 463     ) {
 464       // if its only soft credit stats, use inner join
 465       $softCreditJoinType = "INNER";
 466     }
 467
 468     $softCreditJoin = "{$softCreditJoinType} JOIN civicrm_contribution_soft {$this->_aliases['civicrm_contribution_soft']}
 469                        ON {$this->_aliases['civicrm_contribution_soft']}.contribution_id = {$this->_aliases['civicrm_contribution']}.id";
 470     if ($entity == 'contribution' || empty($this->_params['fields']['soft_amount'])) {
 471       $softCreditJoin .= " AND {$this->_aliases['civicrm_contribution_soft']}.id = (SELECT MIN(id) FROM civicrm_contribution_soft cs WHERE cs.contribution_id = {$this->_aliases['civicrm_contribution']}.id) ";
 472     }
 473
 474     $this->setFromBase('civicrm_contact');
 475
 476     $this->_from .= "
 477              INNER JOIN civicrm_contribution   {$this->_aliases['civicrm_contribution']}
 478                      ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id AND
 479                         {$this->_aliases['civicrm_contribution']}.is_test = 0
 480              {$softCreditJoin}
 481              LEFT  JOIN civicrm_financial_type  {$this->_aliases['civicrm_financial_type']}
 482                      ON {$this->_aliases['civicrm_contribution']}.financial_type_id ={$this->_aliases['civicrm_financial_type']}.id
 483              LEFT  JOIN civicrm_email {$this->_aliases['civicrm_email']}
 484                      ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND
 485                         {$this->_aliases['civicrm_email']}.is_primary = 1)
 486
 487              LEFT  JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
 488                      ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND
 489                         {$this->_aliases['civicrm_phone']}.is_primary = 1)";
 490
 491     $this->addAddressFromClause();
 492     if (!empty($this->_params['batch_id_value'])) {
 493       $this->_from .= "
 494                  LEFT JOIN civicrm_entity_financial_trxn eft
 495                         ON eft.entity_id = {$this->_aliases['civicrm_contribution']}.id AND
 496                            eft.entity_table = 'civicrm_contribution'
 497                  LEFT JOIN civicrm_entity_batch {$this->_aliases['civicrm_batch']}
 498                         ON {$this->_aliases['civicrm_batch']}.entity_id = eft.financial_trxn_id AND
 499                            {$this->_aliases['civicrm_batch']}.entity_table = 'civicrm_financial_trxn'\n";
 500     }
 501   }
 502
 503   /**
 504    * Set group by clause.
 505    */
 506   public function groupBy() {
 507     $this->_groupBy = "";
 508     $append = FALSE;
 509     if (!empty($this->_params['group_bys']) &&
 510       is_array($this->_params['group_bys'])
 511     ) {
 512       foreach ($this->_columns as $tableName => $table) {
 513         if (array_key_exists('group_bys', $table)) {
 514           foreach ($table['group_bys'] as $fieldName => $field) {
 515             if (!empty($this->_params['group_bys'][$fieldName])) {
 516               if (!empty($field['chart'])) {
 517                 $this->assign('chartSupported', TRUE);
 518               }
 519
 520               if (!empty($table['group_bys'][$fieldName]['frequency']) &&
 521                 !empty($this->_params['group_bys_freq'][$fieldName])
 522               ) {
 523
 524                 $append = "YEAR({$field['dbAlias']});;";
 525                 if (in_array(strtolower($this->_params['group_bys_freq'][$fieldName]),
 526                   array('year')
 527                 )) {
 528                   $append = '';
 529                 }
 530                 if ($this->_params['group_bys_freq'][$fieldName] == 'FISCALYEAR') {
 531                   $this->_groupBy[] = self::fiscalYearOffset($field['dbAlias']);
 532                 }
 533                 else {
 534                   $this->_groupBy[] = "$append {$this->_params['group_bys_freq'][$fieldName]}({$field['dbAlias']})";
 535                 }
 536                 $append = TRUE;
 537               }
 538               else {
 539                 $this->_groupBy[] = $field['dbAlias'];
 540               }
 541             }
 542           }
 543         }
 544       }
 545
 546       if (!empty($this->_statFields) &&
 547         (($append && count($this->_groupBy) <= 1) || (!$append)) &&
 548         !$this->_having
 549       ) {
 550         $this->_rollup = " WITH ROLLUP";
 551       }
 552       $groupBy = array();
 553       foreach ($this->_groupBy as $key => $val) {
 554         if (strpos($val, ';;') !== FALSE) {
 555           $groupBy = array_merge($groupBy, explode(';;', $val));
 556         }
 557         else {
 558           $groupBy[] = $this->_groupBy[$key];
 559         }
 560       }
 561       $this->_groupBy = "GROUP BY " . implode(', ', $groupBy);
 562     }
 563     else {
 564       $groupBy = "{$this->_aliases['civicrm_contact']}.id";
 565       $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_contact']}.id";
 566     }
 567     $this->_groupBy .= $this->_rollup;
 568     // append select with ANY_VALUE() keyword
 569     $this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($this->_selectClauses, $groupBy);
 570   }
 571
 572   /**
 573    * Store having clauses as an array.
 574    */
 575   public function storeWhereHavingClauseArray() {
 576     parent::storeWhereHavingClauseArray();
 577     if (empty($this->_params['fields']['soft_amount']) &&
 578       !empty($this->_havingClauses)
 579     ) {
 580       foreach ($this->_havingClauses as $key => $havingClause) {
 581         if (stristr($havingClause, 'soft_soft')) {
 582           unset($this->_havingClauses[$key]);
 583         }
 584       }
 585     }
 586   }
 587
 588   /**
 589    * Set statistics.
 590    *
 591    * @param array $rows
 592    *
 593    * @return array
 594    */
 595   public function statistics(&$rows) {
 596     $statistics = parent::statistics($rows);
 597
 598     $softCredit = CRM_Utils_Array::value('soft_amount', $this->_params['fields']);
 599     $onlySoftCredit = $softCredit && !CRM_Utils_Array::value('total_amount', $this->_params['fields']);
 600     $group = "\nGROUP BY {$this->_aliases['civicrm_contribution']}.currency";
 601
 602     $this->from('contribution');
 603     $this->customDataFrom();
 604
 605     $contriQuery = "
 606 COUNT({$this->_aliases['civicrm_contribution']}.total_amount )        as civicrm_contribution_total_amount_count,
 607 SUM({$this->_aliases['civicrm_contribution']}.total_amount )          as civicrm_contribution_total_amount_sum,
 608 ROUND(AVG({$this->_aliases['civicrm_contribution']}.total_amount), 2) as civicrm_contribution_total_amount_avg,
 609 {$this->_aliases['civicrm_contribution']}.currency                    as currency
 610 {$this->_from} {$this->_where}";
 611
 612     if ($softCredit) {
 613       $this->from();
 614       $select = "
 615 COUNT({$this->_aliases['civicrm_contribution_soft']}.amount )        as civicrm_contribution_soft_soft_amount_count,
 616 SUM({$this->_aliases['civicrm_contribution_soft']}.amount )          as civicrm_contribution_soft_soft_amount_sum,
 617 ROUND(AVG({$this->_aliases['civicrm_contribution_soft']}.amount), 2) as civicrm_contribution_soft_soft_amount_avg";
 618       $contriQuery = "{$select}, {$contriQuery}";
 619       $softSQL = "SELECT {$select}, {$this->_aliases['civicrm_contribution']}.currency as currency
 620       {$this->_from} {$this->_where} {$group} {$this->_having}";
 621     }
 622
 623     $contriSQL = "SELECT {$contriQuery} {$group} {$this->_having}";
 624     $contriDAO = CRM_Core_DAO::executeQuery($contriSQL);
 625
 626     $totalAmount = $average = $mode = $median = $softTotalAmount = $softAverage = array();
 627     $count = $softCount = 0;
 628     while ($contriDAO->fetch()) {
 629       $totalAmount[]
 630         = CRM_Utils_Money::format($contriDAO->civicrm_contribution_total_amount_sum, $contriDAO->currency) .
 631         " (" . $contriDAO->civicrm_contribution_total_amount_count . ")";
 632       $average[] = CRM_Utils_Money::format($contriDAO->civicrm_contribution_total_amount_avg, $contriDAO->currency);
 633       $count += $contriDAO->civicrm_contribution_total_amount_count;
 634     }
 635
 636     $groupBy = "\n{$group}, {$this->_aliases['civicrm_contribution']}.total_amount";
 637     $orderBy = "\nORDER BY civicrm_contribution_total_amount_count DESC";
 638     $modeSQL = "SELECT MAX(civicrm_contribution_total_amount_count) as civicrm_contribution_total_amount_count,
 639       SUBSTRING_INDEX(GROUP_CONCAT(amount ORDER BY mode.civicrm_contribution_total_amount_count DESC SEPARATOR ';'), ';', 1) as amount,
 640       currency
 641       FROM (SELECT {$this->_aliases['civicrm_contribution']}.total_amount as amount,
 642     {$contriQuery} {$groupBy} {$orderBy}) as mode GROUP BY currency";
 643
 644     $mode = CRM_Contribute_BAO_Contribution::computeStats('mode', $modeSQL);
 645
 646     $medianSQL = "{$this->_from} {$this->_where}";
 647     $median = CRM_Contribute_BAO_Contribution::computeStats('median', $medianSQL, $this->_aliases['civicrm_contribution']);
 648
 649     if ($softCredit) {
 650       $softDAO = CRM_Core_DAO::executeQuery($softSQL);
 651       while ($softDAO->fetch()) {
 652         $softTotalAmount[]
 653           = CRM_Utils_Money::format($softDAO->civicrm_contribution_soft_soft_amount_sum, $softDAO->currency) .
 654           " (" . $softDAO->civicrm_contribution_soft_soft_amount_count . ")";
 655         $softAverage[] = CRM_Utils_Money::format($softDAO->civicrm_contribution_soft_soft_amount_avg, $softDAO->currency);
 656         $softCount += $softDAO->civicrm_contribution_soft_soft_amount_count;
 657       }
 658     }
 659
 660     if (!$onlySoftCredit) {
 661       $statistics['counts']['amount'] = array(
 662         'title' => ts('Total Amount'),
 663         'value' => implode(',  ', $totalAmount),
 664         'type' => CRM_Utils_Type::T_STRING,
 665       );
 666       $statistics['counts']['count'] = array(
 667         'title' => ts('Total Contributions'),
 668         'value' => $count,
 669       );
 670       $statistics['counts']['avg'] = array(
 671         'title' => ts('Average'),
 672         'value' => implode(',  ', $average),
 673         'type' => CRM_Utils_Type::T_STRING,
 674       );
 675       $statistics['counts']['mode'] = array(
 676         'title' => ts('Mode'),
 677         'value' => implode(',  ', $mode),
 678         'type' => CRM_Utils_Type::T_STRING,
 679       );
 680       $statistics['counts']['median'] = array(
 681         'title' => ts('Median'),
 682         'value' => implode(',  ', $median),
 683         'type' => CRM_Utils_Type::T_STRING,
 684       );
 685     }
 686     if ($softCredit) {
 687       $statistics['counts']['soft_amount'] = array(
 688         'title' => ts('Total Soft Credit Amount'),
 689         'value' => implode(',  ', $softTotalAmount),
 690         'type' => CRM_Utils_Type::T_STRING,
 691       );
 692       $statistics['counts']['soft_count'] = array(
 693         'title' => ts('Total Soft Credits'),
 694         'value' => $softCount,
 695       );
 696       $statistics['counts']['soft_avg'] = array(
 697         'title' => ts('Average Soft Credit'),
 698         'value' => implode(',  ', $softAverage),
 699         'type' => CRM_Utils_Type::T_STRING,
 700       );
 701     }
 702     return $statistics;
 703   }
 704
 705   /**
 706    * Post process function.
 707    */
 708   public function postProcess() {
 709     $this->buildACLClause($this->_aliases['civicrm_contact']);
 710     parent::postProcess();
 711   }
 712
 713   /**
 714    * Build table rows for output.
 715    *
 716    * @param string $sql
 717    * @param array $rows
 718    */
 719   public function buildRows($sql, &$rows) {
 720     $dao = CRM_Core_DAO::executeQuery($sql);
 721     if (!is_array($rows)) {
 722       $rows = array();
 723     }
 724
 725     // use this method to modify $this->_columnHeaders
 726     $this->modifyColumnHeaders();
 727     $contriRows = array();
 728     $unselectedSectionColumns = $this->unselectedSectionColumns();
 729
 730     //CRM-16338 if both soft-credit and contribution are enabled then process the contribution's
 731     //total amount's average, count and sum separately and add it to the respective result list
 732     $softCredit = (!empty($this->_params['fields']['soft_amount']) && !empty($this->_params['fields']['total_amount'])) ? TRUE : FALSE;
 733     if ($softCredit) {
 734       $this->from('contribution');
 735       $contriSQL = "{$this->_select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy} {$this->_limit}";
 736       $contriDAO = CRM_Core_DAO::executeQuery($contriSQL);
 737       $contriFields = array(
 738         'civicrm_contribution_total_amount_sum',
 739         'civicrm_contribution_total_amount_avg',
 740         'civicrm_contribution_total_amount_count');
 741       $contriRows = array();
 742       while ($contriDAO->fetch()) {
 743         $contriRow = array();
 744         foreach ($contriFields as $column) {
 745           $contriRow[$column] = $contriDAO->$column;
 746         }
 747         $contriRows[] = $contriRow;
 748       }
 749     }
 750
 751     $count = 0;
 752     while ($dao->fetch()) {
 753       $row = array();
 754       foreach ($this->_columnHeaders as $key => $value) {
 755         if ($softCredit && array_key_exists($key, $contriRows[$count])) {
 756           $row[$key] = $contriRows[$count][$key];
 757         }
 758         elseif (property_exists($dao, $key)) {
 759           $row[$key] = $dao->$key;
 760         }
 761       }
 762
 763       // section headers not selected for display need to be added to row
 764       foreach ($unselectedSectionColumns as $key => $values) {
 765         if (property_exists($dao, $key)) {
 766           $row[$key] = $dao->$key;
 767         }
 768       }
 769
 770       $count++;
 771       $rows[] = $row;
 772     }
 773
 774   }
 775
 776   /**
 777    * Build chart.
 778    *
 779    * @param array $rows
 780    */
 781   public function buildChart(&$rows) {
 782     $graphRows = array();
 783
 784     if (!empty($this->_params['charts'])) {
 785       if (!empty($this->_params['group_bys']['receive_date'])) {
 786
 787         $contrib = !empty($this->_params['fields']['total_amount']) ? TRUE : FALSE;
 788         $softContrib = !empty($this->_params['fields']['soft_amount']) ? TRUE : FALSE;
 789
 790         foreach ($rows as $key => $row) {
 791           if ($row['civicrm_contribution_receive_date_subtotal']) {
 792             $graphRows['receive_date'][] = $row['civicrm_contribution_receive_date_start'];
 793             $graphRows[$this->_interval][] = $row['civicrm_contribution_receive_date_interval'];
 794             if ($softContrib && $contrib) {
 795               // both contri & soft contri stats are present
 796               $graphRows['multiValue'][0][] = $row['civicrm_contribution_total_amount_sum'];
 797               $graphRows['multiValue'][1][] = $row['civicrm_contribution_soft_soft_amount_sum'];
 798             }
 799             elseif ($softContrib) {
 800               // only soft contributions
 801               $graphRows['multiValue'][0][] = $row['civicrm_contribution_soft_soft_amount_sum'];
 802             }
 803             else {
 804               // only contributions
 805               $graphRows['multiValue'][0][] = $row['civicrm_contribution_total_amount_sum'];
 806             }
 807           }
 808         }
 809
 810         if ($softContrib && $contrib) {
 811           $graphRows['barKeys'][0] = ts('Contributions');
 812           $graphRows['barKeys'][1] = ts('Soft Credits');
 813           $graphRows['legend'] = ts('Contributions and Soft Credits');
 814         }
 815         elseif ($softContrib) {
 816           $graphRows['legend'] = ts('Soft Credits');
 817         }
 818
 819         // build the chart.
 820         $config = CRM_Core_Config::Singleton();
 821         $graphRows['xname'] = $this->_interval;
 822         $graphRows['yname'] = "Amount ({$config->defaultCurrency})";
 823         CRM_Utils_OpenFlashChart::chart($graphRows, $this->_params['charts'], $this->_interval);
 824         $this->assign('chartType', $this->_params['charts']);
 825       }
 826     }
 827   }
 828
 829   /**
 830    * Alter display of rows.
 831    *
 832    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 833    * such as rendering contacts as links.
 834    *
 835    * @param array $rows
 836    *   Rows generated by SQL, with an array for each row.
 837    */
 838   public function alterDisplay(&$rows) {
 839     $entryFound = FALSE;
 840     $contributionStatus = CRM_Contribute_PseudoConstant::contributionStatus();
 841
 842     foreach ($rows as $rowNum => $row) {
 843       // make count columns point to detail report
 844       if (!empty($this->_params['group_bys']['receive_date']) &&
 845         !empty($row['civicrm_contribution_receive_date_start']) &&
 846         CRM_Utils_Array::value('civicrm_contribution_receive_date_start', $row) &&
 847         !empty($row['civicrm_contribution_receive_date_subtotal'])
 848       ) {
 849
 850         $dateStart = CRM_Utils_Date::customFormat($row['civicrm_contribution_receive_date_start'], '%Y%m%d');
 851         $endDate = new DateTime($dateStart);
 852         $dateEnd = array();
 853
 854         list($dateEnd['Y'], $dateEnd['M'], $dateEnd['d']) = explode(':', $endDate->format('Y:m:d'));
 855
 856         switch (strtolower($this->_params['group_bys_freq']['receive_date'])) {
 857           case 'month':
 858             $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'] + 1,
 859               $dateEnd['d'] - 1, $dateEnd['Y']
 860             ));
 861             break;
 862
 863           case 'year':
 864             $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'],
 865               $dateEnd['d'] - 1, $dateEnd['Y'] + 1
 866             ));
 867             break;
 868
 869           case 'fiscalyear':
 870             $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'],
 871               $dateEnd['d'] - 1, $dateEnd['Y'] + 1
 872             ));
 873             break;
 874
 875           case 'yearweek':
 876             $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'],
 877               $dateEnd['d'] + 6, $dateEnd['Y']
 878             ));
 879             break;
 880
 881           case 'quarter':
 882             $dateEnd = date("Ymd", mktime(0, 0, 0, $dateEnd['M'] + 3,
 883               $dateEnd['d'] - 1, $dateEnd['Y']
 884             ));
 885             break;
 886         }
 887         $url = CRM_Report_Utils_Report::getNextUrl('contribute/detail',
 888           "reset=1&force=1&receive_date_from={$dateStart}&receive_date_to={$dateEnd}",
 889           $this->_absoluteUrl,
 890           $this->_id,
 891           $this->_drilldownReport
 892         );
 893         $rows[$rowNum]['civicrm_contribution_receive_date_start_link'] = $url;
 894         $rows[$rowNum]['civicrm_contribution_receive_date_start_hover'] = ts('List all contribution(s) for this date unit.');
 895         $entryFound = TRUE;
 896       }
 897
 898       // make subtotals look nicer
 899       if (array_key_exists('civicrm_contribution_receive_date_subtotal', $row) &&
 900         !$row['civicrm_contribution_receive_date_subtotal']
 901       ) {
 902         $this->fixSubTotalDisplay($rows[$rowNum], $this->_statFields);
 903         $entryFound = TRUE;
 904       }
 905
 906       // convert display name to links
 907       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 908         array_key_exists('civicrm_contact_id', $row)
 909       ) {
 910         $url = CRM_Report_Utils_Report::getNextUrl('contribute/detail',
 911           'reset=1&force=1&id_op=eq&id_value=' . $row['civicrm_contact_id'],
 912           $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 913         );
 914         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 915         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("Lists detailed contribution(s) for this record.");
 916         $entryFound = TRUE;
 917       }
 918
 919       // convert contribution status id to status name
 920       if ($value = CRM_Utils_Array::value('civicrm_contribution_contribution_status_id', $row)) {
 921         $rows[$rowNum]['civicrm_contribution_contribution_status_id'] = $contributionStatus[$value];
 922         $entryFound = TRUE;
 923       }
 924
 925       // If using campaigns, convert campaign_id to campaign title
 926       if (array_key_exists('civicrm_contribution_campaign_id', $row)) {
 927         if ($value = $row['civicrm_contribution_campaign_id']) {
 928           $rows[$rowNum]['civicrm_contribution_campaign_id'] = $this->activeCampaigns[$value];
 929         }
 930         $entryFound = TRUE;
 931       }
 932
 933       $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, 'contribute/detail', 'List all contribution(s) for this ') ? TRUE : $entryFound;
 934       $entryFound = $this->alterDisplayContactFields($row, $rows, $rowNum, 'contribute/detail', 'List all contribution(s) for this ') ? TRUE : $entryFound;
 935
 936       // skip looking further in rows, if first row itself doesn't
 937       // have the column we need
 938       if (!$entryFound) {
 939         break;
 940       }
 941     }
 942   }
 943
 944 }