CRM/Report/Form/Contribute/Summary.php

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