From 89bfb100cc41f5dae1c30280fd793fcee2800a6f Mon Sep 17 00:00:00 2001
From: Monish Deb <deb.monish@gmail.com>
Date: Fri, 30 Nov 2018 15:09:40 +0530
Subject: [PATCH] Optimize CRM_Core_BAO_FinancialTrxn::getTotalPayment

---
 CRM/Contribute/BAO/Contribution.php | 22 +++++-----------------
 CRM/Core/BAO/FinancialTrxn.php      | 23 +++++++++++++----------
 2 files changed, 18 insertions(+), 27 deletions(-)

diff --git a/CRM/Contribute/BAO/Contribution.php b/CRM/Contribute/BAO/Contribution.php
index 21d40d568a..f9a21180db 100644
--- a/CRM/Contribute/BAO/Contribution.php
+++ b/CRM/Contribute/BAO/Contribution.php
@@ -4169,27 +4169,15 @@ WHERE eft.financial_trxn_id IN ({$trxnId}, {$baseTrxnId['financialTrxnId']})
    * @return float
    */
   public static function getContributionBalance($contributionId, $contributionTotal = NULL) {
-
     if ($contributionTotal === NULL) {
       $contributionTotal = CRM_Price_BAO_LineItem::getLineTotal($contributionId);
     }
-    $statusId = CRM_Core_PseudoConstant::getKey('CRM_Contribute_BAO_Contribution', 'contribution_status_id', 'Completed');
-    $refundStatusId = CRM_Core_PseudoConstant::getKey('CRM_Contribute_BAO_Contribution', 'contribution_status_id', 'Refunded');
-
-    $sqlFtTotalAmt = "
-SELECT SUM(ft.total_amount)
-FROM civicrm_financial_trxn ft
-  INNER JOIN civicrm_entity_financial_trxn eft ON (ft.id = eft.financial_trxn_id AND eft.entity_table = 'civicrm_contribution' AND eft.entity_id = {$contributionId})
-WHERE ft.is_payment = 1
-  AND ft.status_id IN ({$statusId}, {$refundStatusId})
-";
 
-    $ftTotalAmt = CRM_Core_DAO::singleValueQuery($sqlFtTotalAmt);
-    if (!$ftTotalAmt) {
-      $ftTotalAmt = 0;
-    }
-    $currency = CRM_Core_DAO::getFieldValue('CRM_Contribute_DAO_Contribution', $contributionId, 'currency');
-    return CRM_Utils_Money::subtractCurrencies($contributionTotal, $ftTotalAmt, $currency);
+    return CRM_Utils_Money::subtractCurrencies(
+      $contributionTotal,
+      CRM_Core_BAO_FinancialTrxn::getTotalPayments($contributionId, TRUE) ?: 0,
+      CRM_Core_DAO::getFieldValue('CRM_Contribute_DAO_Contribution', $contributionId, 'currency')
+    );
   }
 
   /**
diff --git a/CRM/Core/BAO/FinancialTrxn.php b/CRM/Core/BAO/FinancialTrxn.php
index 3eb7aecadc..e6070ebfb1 100644
--- a/CRM/Core/BAO/FinancialTrxn.php
+++ b/CRM/Core/BAO/FinancialTrxn.php
@@ -484,23 +484,26 @@ WHERE ceft.entity_id = %1";
   }
 
   /**
-   * @param int $contributionId
+   * @param int $contributionID
+   * @param bool $includeRefund
    *
    * @return string
    */
-  public static function getTotalPayments($contributionId) {
-    $statusId = CRM_Core_PseudoConstant::getKey('CRM_Contribute_BAO_Contribution', 'contribution_status_id', 'Completed');
+  public static function getTotalPayments($contributionID, $includeRefund = FALSE) {
+    $statusIDs = [CRM_Core_PseudoConstant::getKey('CRM_Contribute_BAO_Contribution', 'contribution_status_id', 'Completed')];
+
+    if ($includeRefund) {
+      $statusIDs[] = CRM_Core_PseudoConstant::getKey('CRM_Contribute_BAO_Contribution', 'contribution_status_id', 'Refunded');
+    }
 
     $sql = "SELECT SUM(ft.total_amount) FROM civicrm_financial_trxn ft
       INNER JOIN civicrm_entity_financial_trxn eft ON (eft.financial_trxn_id = ft.id AND eft.entity_table = 'civicrm_contribution')
-      WHERE eft.entity_id = %1 AND ft.is_payment = 1 AND ft.status_id = %2";
-
-    $params = array(
-      1 => array($contributionId, 'Integer'),
-      2 => array($statusId, 'Integer'),
-    );
+      WHERE eft.entity_id = %1 AND ft.is_payment = 1 AND ft.status_id IN (%2) ";
 
-    return CRM_Core_DAO::singleValueQuery($sql, $params);
+    return CRM_Core_DAO::singleValueQuery($sql, [
+      1 => [$contributionID, 'Integer'],
+      2 => [implode(',', $statusIDs), 'CommaSeparatedIntegers'],
+    ]);
   }
 
   /**
-- 
2.25.1