CRM/Report/Form/Contribute/Summary.php

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