From 867527199573864fd47cbf24dc2d5bfc9810a756 Mon Sep 17 00:00:00 2001 From: Pradeep Nayak Date: Wed, 26 Jul 2017 02:00:03 +0530 Subject: [PATCH] CRM-20967, use is_payment = TRUE to retrieve payments ---------------------------------------- * CRM-20967: Event Participants List shows Total paid and balance incorrect https://issues.civicrm.org/jira/browse/CRM-20967 --- CRM/Report/Form/Event/ParticipantListing.php | 11 +++-------- 1 file changed, 3 insertions(+), 8 deletions(-) diff --git a/CRM/Report/Form/Event/ParticipantListing.php b/CRM/Report/Form/Event/ParticipantListing.php index 3c980e7d7a..4a650cc4de 100644 --- a/CRM/Report/Form/Event/ParticipantListing.php +++ b/CRM/Report/Form/Event/ParticipantListing.php @@ -192,12 +192,12 @@ class CRM_Report_Form_Event_ParticipantListing extends CRM_Report_Form_Event { 'participant_register_date' => array('title' => ts('Registration Date')), 'total_paid' => array( 'title' => ts('Total Paid'), - 'dbAlias' => 'SUM(ft.total_amount)', + 'dbAlias' => 'IFNULL(SUM(ft.total_amount), 0)', 'type' => 1024, ), 'balance' => array( 'title' => ts('Balance'), - 'dbAlias' => 'participant_civireport.fee_amount - SUM(ft.total_amount)', + 'dbAlias' => 'participant_civireport.fee_amount - IFNULL(SUM(ft.total_amount), 0)', 'type' => 1024, ), ), @@ -563,14 +563,9 @@ ORDER BY cv.label $this->_from .= " LEFT JOIN civicrm_entity_financial_trxn eft ON (eft.entity_id = {$this->_aliases['civicrm_contribution']}.id) - LEFT JOIN civicrm_financial_account fa - ON (fa.account_type_code = 'AR') - LEFT JOIN civicrm_financial_account fae - ON (fae.account_type_code = 'EXP') LEFT JOIN civicrm_financial_trxn ft ON (ft.id = eft.financial_trxn_id AND eft.entity_table = 'civicrm_contribution') AND - (ft.to_financial_account_id != fa.id) AND - (ft.to_financial_account_id != fae.id) + (ft.is_payment = 1) "; } } -- 2.25.1