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