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