protected $groupFilterNotOptimised = FALSE;
/**
- * Indicate that report is not fully FGB compliant.
+ * Use the generic (but flawed) handling to implement full group by.
+ *
+ * Note that because we are calling the parent group by function we set this to FALSE.
+ * The parent group by function adds things to the group by in order to make the mysql pass
+ * but can create incorrect results in the process.
*
* @var bool
*/
- public $optimisedForOnlyFullGroupBy;
+ public $optimisedForOnlyFullGroupBy = FALSE;
/**
* Class constructor.
'grouping' => 'contri-fields',
'filters' => [
'receive_date' => ['operatorType' => CRM_Report_Form::OP_DATE],
+ 'receipt_date' => ['operatorType' => CRM_Report_Form::OP_DATE],
'thankyou_date' => ['operatorType' => CRM_Report_Form::OP_DATE],
'contribution_status_id' => [
'title' => ts('Contribution Status'),
}
else {
$select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
- $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
- $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
+ $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = $field['type'] ?? NULL;
+ $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'] ?? NULL;
}
}
}
* Set group by clause.
*/
public function groupBy() {
- $this->_groupBy = "";
- $groupByColumns = [];
- $append = FALSE;
+ parent::groupBy();
+
+ $isGroupByFrequency = !empty($this->_params['group_bys_freq']);
+
if (!empty($this->_params['group_bys']) &&
is_array($this->_params['group_bys'])
) {
- foreach ($this->_columns as $tableName => $table) {
- if (array_key_exists('group_bys', $table)) {
- foreach ($table['group_bys'] as $fieldName => $field) {
- if (!empty($this->_params['group_bys'][$fieldName])) {
- if (!empty($field['chart'])) {
- $this->assign('chartSupported', TRUE);
- }
-
- if (!empty($table['group_bys'][$fieldName]['frequency']) &&
- !empty($this->_params['group_bys_freq'][$fieldName])
- ) {
-
- $append = "YEAR({$field['dbAlias']});;";
- if (in_array(strtolower($this->_params['group_bys_freq'][$fieldName]),
- ['year']
- )) {
- $append = '';
- }
- if ($this->_params['group_bys_freq'][$fieldName] == 'FISCALYEAR') {
- $groupByColumns[] = self::fiscalYearOffset($field['dbAlias']);
- }
- else {
- $groupByColumns[] = "$append {$this->_params['group_bys_freq'][$fieldName]}({$field['dbAlias']})";
- }
- $append = TRUE;
- }
- else {
- $groupByColumns[] = $field['dbAlias'];
- }
- }
- }
- }
- }
if (!empty($this->_statFields) &&
- (($append && count($groupByColumns) <= 1) || (!$append)) &&
+ (($isGroupByFrequency && count($this->_groupByArray) <= 1) || (!$isGroupByFrequency)) &&
!$this->_having
) {
$this->_rollup = " WITH ROLLUP";
}
$groupBy = [];
- foreach ($groupByColumns as $key => $val) {
+ foreach ($this->_groupByArray as $key => $val) {
if (strpos($val, ';;') !== FALSE) {
$groupBy = array_merge($groupBy, explode(';;', $val));
}
else {
- $groupBy[] = $groupByColumns[$key];
+ $groupBy[] = $this->_groupByArray[$key];
}
}
$this->_groupBy = "GROUP BY " . implode(', ', $groupBy);
* @param array $rows
*
* @return array
+ *
+ * @throws \CRM_Core_Exception
*/
public function statistics(&$rows) {
$statistics = parent::statistics($rows);
- $softCredit = CRM_Utils_Array::value('soft_amount', $this->_params['fields']);
+ $softCredit = $this->_params['fields']['soft_amount'] ?? NULL;
$onlySoftCredit = $softCredit && !CRM_Utils_Array::value('total_amount', $this->_params['fields']);
- if (empty($this->_groupBy)) {
- $group = "\nGROUP BY {$this->_aliases['civicrm_contribution']}.currency";
- }
- else {
- $group = "\n {$this->_groupBy}, {$this->_aliases['civicrm_contribution']}.currency";
+ if (!isset($this->_groupByArray['civicrm_contribution_currency'])) {
+ $this->_groupByArray['civicrm_contribution_currency'] = 'currency';
}
+ $group = ' GROUP BY ' . implode(', ', $this->_groupByArray);
$this->from('contribution');
if ($softCredit) {
CRM_Utils_Hook::alterReportVar('sql', $this, $this);
$contriQuery = "
-COUNT({$this->_aliases['civicrm_contribution']}.total_amount ) as civicrm_contribution_total_amount_count,
-SUM({$this->_aliases['civicrm_contribution']}.total_amount ) as civicrm_contribution_total_amount_sum,
-ROUND(AVG({$this->_aliases['civicrm_contribution']}.total_amount), 2) as civicrm_contribution_total_amount_avg,
-{$this->_aliases['civicrm_contribution']}.currency as currency
-{$this->_from} {$this->_where}";
+ COUNT({$this->_aliases['civicrm_contribution']}.total_amount ) as civicrm_contribution_total_amount_count,
+ SUM({$this->_aliases['civicrm_contribution']}.total_amount ) as civicrm_contribution_total_amount_sum,
+ ROUND(AVG({$this->_aliases['civicrm_contribution']}.total_amount), 2) as civicrm_contribution_total_amount_avg,
+ {$this->_aliases['civicrm_contribution']}.currency as currency
+ {$this->_from} {$this->_where}
+ ";
if ($softCredit) {
- $select = "
-COUNT({$this->_aliases['civicrm_contribution_soft']}.amount ) as civicrm_contribution_soft_soft_amount_count,
-SUM({$this->_aliases['civicrm_contribution_soft']}.amount ) as civicrm_contribution_soft_soft_amount_sum,
-ROUND(AVG({$this->_aliases['civicrm_contribution_soft']}.amount), 2) as civicrm_contribution_soft_soft_amount_avg";
- $contriQuery = "{$select}, {$contriQuery}";
- $softSQL = "SELECT {$select}, {$this->_aliases['civicrm_contribution']}.currency as currency
- {$this->_from} {$this->_where} {$group} {$this->_having}";
+ $selectOnlySoftCredit = "
+ COUNT({$this->_aliases['civicrm_contribution_soft']}.amount ) as civicrm_contribution_soft_soft_amount_count,
+ SUM({$this->_aliases['civicrm_contribution_soft']}.amount ) as civicrm_contribution_soft_soft_amount_sum,
+ ROUND(AVG({$this->_aliases['civicrm_contribution_soft']}.amount), 2) as civicrm_contribution_soft_soft_amount_avg,
+ ";
+
+ $selectWithSoftCredit = "
+ COUNT({$this->_aliases['civicrm_contribution_soft']}.amount ) as civicrm_contribution_soft_soft_amount_count,
+ SUM({$this->_aliases['civicrm_contribution_soft']}.amount ) as civicrm_contribution_soft_soft_amount_sum,
+ ROUND(AVG({$this->_aliases['civicrm_contribution_soft']}.amount), 2) as civicrm_contribution_soft_soft_amount_avg,
+ COUNT({$this->_aliases['civicrm_contribution']}.total_amount ) as civicrm_contribution_total_amount_count,
+ SUM({$this->_aliases['civicrm_contribution']}.total_amount ) as civicrm_contribution_total_amount_sum,
+ ROUND(AVG({$this->_aliases['civicrm_contribution']}.total_amount), 2) as civicrm_contribution_total_amount_avg,
+ ";
+
+ if ($softCredit && $onlySoftCredit) {
+ $contriQuery = "{$selectOnlySoftCredit} {$contriQuery}";
+ $softSQL = "SELECT {$selectOnlySoftCredit} {$this->_aliases['civicrm_contribution']}.currency as currency
+ {$this->_from} {$this->_where} {$group} {$this->_having}";
+ }
+ elseif ($softCredit && !$onlySoftCredit) {
+ $contriQuery = "{$selectOnlySoftCredit} {$contriQuery}";
+ $softSQL = "SELECT {$selectWithSoftCredit} {$this->_aliases['civicrm_contribution']}.currency as currency
+ {$this->_from} {$this->_where} {$group} {$this->_having}";
+ }
}
$contriSQL = "SELECT {$contriQuery} {$group} {$this->_having}";
return $statistics;
}
- /**
- * Post process function.
- */
- public function postProcess() {
- $this->buildACLClause($this->_aliases['civicrm_contact']);
- parent::postProcess();
- }
-
/**
* Build chart.
*
if (!empty($this->_params['charts'])) {
if (!empty($this->_params['group_bys']['receive_date'])) {
- $contrib = !empty($this->_params['fields']['total_amount']) ? TRUE : FALSE;
- $softContrib = !empty($this->_params['fields']['soft_amount']) ? TRUE : FALSE;
+ $contrib = !empty($this->_params['fields']['total_amount']);
+ $softContrib = !empty($this->_params['fields']['soft_amount']);
foreach ($rows as $key => $row) {
if ($row['civicrm_contribution_receive_date_subtotal']) {
$contributionPages = CRM_Contribute_PseudoConstant::contributionPage();
//CRM-16338 if both soft-credit and contribution are enabled then process the contribution's
//total amount's average, count and sum separately and add it to the respective result list
- $softCredit = (!empty($this->_params['fields']['soft_amount']) && !empty($this->_params['fields']['total_amount'])) ? TRUE : FALSE;
+ $softCredit = (!empty($this->_params['fields']['soft_amount']) && !empty($this->_params['fields']['total_amount']));
if ($softCredit) {
$this->from('contribution');
$this->customDataFrom();
projects / civicrm-core.git / blobdiff