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