Details
-
Type: Bug
-
Status: Done/Fixed
-
Priority: Minor
-
Resolution: Fixed/Completed
-
Affects Version/s: 4.2.8
-
Fix Version/s: 4.3.4
-
Component/s: CiviReport
-
Labels:None
Description
I noticed the sql produced by this report had two SELECT fields with the same name "civicrm_contribution_total_amount":
first instance: SUM(contribution_civireport.total_amount) as civicrm_contribution_total_amount,
second instance: contribution_civireport.total_amount as civicrm_contribution_total_amount,
Copy of full SQL:
SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_sort_name,
contact_civireport.id as civicrm_contact_id,
SUM(contribution_civireport.total_amount) as civicrm_contribution_total_amount,
contribution_civireport.total_amount as civicrm_contribution_total_amount,
YEAR(contribution_civireport.receive_date) as civicrm_contribution_receive_date
FROM civicrm_contact contact_civireport
INNER JOIN civicrm_contribution contribution_civireport
ON contact_civireport.id = contribution_civireport.contact_id
AND contribution_civireport.is_test = 0
WHERE contact_civireport.id IN (1,2,3)
AND contribution_civireport.is_test = 0
AND ( contribution_civireport.contribution_status_id IN (1) )
GROUP BY contribution_civireport.contact_id, YEAR(contribution_civireport.receive_date)
I ran the sql directly in the db to view the raw results. I think that the report is using the second instance (which would only capture a single contribution per row), but think it should be using the SUM value in the first instance (which would sum all contributions by contact by year).
I edited the function select() code for the total_amount field (moving it to within the if/else statement below), which resulted in only the SUM value (first instance) being included in the query result set, and this sum value being used by the report.
Attached is a patch with these edits for v4.2.8