CRM/Report/Form/Contribute/Summary.php

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