Details
-
Type:
Bug
-
Status: Done/Fixed
-
Priority:
Trivial
-
Resolution: Fixed/Completed
-
Affects Version/s: 4.6.9
-
Fix Version/s: 4.7
-
Component/s: CiviContribute, CiviReport
-
Labels:
-
Documentation Required?:None
-
Funding Source:Contributed Code
Description
If you run the contribution detail report and include contributions that have a double entry, the contribution total will be double the actual total.
We ran into this when doing batch processing with credit card transactions that have a revenue transaction and an expense transaction for the fee. What seems to be happening is that the query pulls the transaction line items, connects them to the contribution, and then totals on the contribution value (thus duplicating everything).
See the attached screenshot. Although there are only 3 rows in the result set, the contribution count is 6 and the total is double what it should be.
Attachments
Issue Links
Activity
Brian Shaughnessy It sounds like you're saying that if any of the contributions in the report data set had a transaction fee, that would cause this miscalc on totals. This seems like a pretty big discrepancy so surprised it hadn't been reported previously. I did a quick test on my 4.7 sandbox and I'm not seeing the problem ...
- Online contribution using test processor which is hard-coded to do assess a 1.50 fee
- Ran the report - totals look correct (see attached).
it only appears if you are displaying/filtering a batch. that's where the problem join exists.
in my testing:
- created a contribution that would create a fee
- batched the contribution (two line items – the full amount and the fee line)
- closed the batch
- ran the contrib detail and filtered by that batch
Yep - seeing that now (in 4.7). Can you push out a PR for the fix please (for master)?
Good / bad news is I discovered that the batch listing page is broken in 4.7 by doing this test. Thanks, I think 
(Since I have your 'attention' - can you also see about finishing up this PR so we can merge - https://github.com/civicrm/civicrm-core/pull/6421)
https://github.com/civicrm/civicrm-core/pull/7094
I'll try to look at that other one this weekend.
I think what we want to do here is change the table join pertaining to batches. That's where were seeing the issue.
https://github.com/civicrm/civicrm-core/blob/master/CRM/Report/Form/Contribute/Detail.php#L486
proposed change:
$this->_from .= "
LEFT JOIN (
SELECT entity_id, financial_trxn_id
FROM civicrm_entity_financial_trxn
WHERE entity_table = 'civicrm_contribution'
GROUP BY entity_id
) tx ON tx.entity_id = {$this->_aliases['civicrm_contribution']}.id
LEFT JOIN civicrm_entity_batch {$this->_aliases['civicrm_entity_batch']}
ON ({$this->_aliases['civicrm_entity_batch']}.entity_id = tx.financial_trxn_id AND
{$this->_aliases['civicrm_entity_batch']}.entity_table = 'civicrm_financial_trxn')
LEFT JOIN civicrm_batch {$this->_aliases['civicrm_batch']}
ON {$this->_aliases['civicrm_batch']}.id = {$this->_aliases['civicrm_entity_batch']}.batch_id";