fix: Update queries in Bank Reconciliation Tool
diff --git a/erpnext/accounts/doctype/bank_reconciliation_tool/bank_reconciliation_tool.py b/erpnext/accounts/doctype/bank_reconciliation_tool/bank_reconciliation_tool.py
index 26078d6..f3351dd 100644
--- a/erpnext/accounts/doctype/bank_reconciliation_tool/bank_reconciliation_tool.py
+++ b/erpnext/accounts/doctype/bank_reconciliation_tool/bank_reconciliation_tool.py
@@ -7,6 +7,7 @@
import frappe
from frappe import _
from frappe.model.document import Document
+from frappe.query_builder.custom import ConstantColumn
from frappe.utils import flt
from erpnext import get_company_currency
@@ -320,14 +321,34 @@
amount_condition = True if "exact_match" in document_types else False
if transaction.withdrawal > 0 and "loan_disbursement" in document_types:
- vouchers.append(get_ld_matching_query(bank_account, amount_condition, filters))
+ vouchers.extend(get_ld_matching_query(bank_account, amount_condition, filters))
if transaction.deposit > 0 and "loan_repayment" in document_types:
- vouchers.append(get_lr_matching_query(bank_account, amount_condition, filters))
+ vouchers.extend(get_lr_matching_query(bank_account, amount_condition, filters))
+
+ return vouchers
def get_ld_matching_query(bank_account, amount_condition, filters):
loan_disbursement = frappe.qb.DocType("Loan Disbursement")
+ matching_reference = loan_disbursement.reference_number == filters.get("reference_number")
+ matching_party = loan_disbursement.applicant_type == filters.get("party_type") and \
+ loan_disbursement.applicant == filters.get("party")
+
+ rank = (
+ frappe.qb.terms.Case()
+ .when(matching_reference, 1)
+ .else_(0)
+ )
+
+ rank1 = (
+ frappe.qb.terms.Case()
+ .when(matching_party, 1)
+ .else_(0)
+ )
+
query = frappe.qb.from_(loan_disbursement).select(
+ rank + rank1 + 1,
+ ConstantColumn("Loan Disbursement").as_("doctype"),
loan_disbursement.name,
loan_disbursement.disbursed_amount,
loan_disbursement.reference_number,
@@ -351,14 +372,33 @@
loan_disbursement.disbursed_amount <= filters.get('amount')
)
- vouchers = query.run(as_dict=1)
+ vouchers = query.run(as_list=True)
+
return vouchers
def get_lr_matching_query(bank_account, amount_condition, filters):
loan_repayment = frappe.qb.DocType("Loan Repayment")
+ matching_reference = loan_repayment.reference_number == filters.get("reference_number")
+ matching_party = loan_repayment.applicant_type == filters.get("party_type") and \
+ loan_repayment.applicant == filters.get("party")
+
+ rank = (
+ frappe.qb.terms.Case()
+ .when(matching_reference, 1)
+ .else_(0)
+ )
+
+ rank1 = (
+ frappe.qb.terms.Case()
+ .when(matching_party, 1)
+ .else_(0)
+ )
+
query = frappe.qb.from_(loan_repayment).select(
+ rank + rank1 + 1,
+ ConstantColumn("Loan Repayment").as_("doctype"),
loan_repayment.name,
- loan_repayment.paid_amount,
+ loan_repayment.amount_paid,
loan_repayment.reference_number,
loan_repayment.reference_date,
loan_repayment.applicant_type,
@@ -368,19 +408,20 @@
).where(
loan_repayment.clearance_date.isnull()
).where(
- loan_repayment.disbursement_account == bank_account
+ loan_repayment.payment_account == bank_account
)
if amount_condition:
query.where(
- loan_repayment.paid_amount == filters.get('amount')
+ loan_repayment.amount_paid == filters.get('amount')
)
else:
query.where(
- loan_repayment.paid_amount <= filters.get('amount')
+ loan_repayment.amount_paid <= filters.get('amount')
)
- vouchers = query.run(as_dict=1)
+ vouchers = query.run()
+
return vouchers
def get_pe_matching_query(amount_condition, account_from_to, transaction):
diff --git a/erpnext/accounts/doctype/bank_transaction/bank_transaction.py b/erpnext/accounts/doctype/bank_transaction/bank_transaction.py
index 51e1d6e..da944fa 100644
--- a/erpnext/accounts/doctype/bank_transaction/bank_transaction.py
+++ b/erpnext/accounts/doctype/bank_transaction/bank_transaction.py
@@ -49,7 +49,8 @@
def clear_linked_payment_entries(self, for_cancel=False):
for payment_entry in self.payment_entries:
- if payment_entry.payment_document in ["Payment Entry", "Journal Entry", "Purchase Invoice", "Expense Claim"]:
+ if payment_entry.payment_document in ["Payment Entry", "Journal Entry", "Purchase Invoice", "Expense Claim", "Loan Repayment",
+ "Loan Disbursement"]:
self.clear_simple_entry(payment_entry, for_cancel=for_cancel)
elif payment_entry.payment_document == "Sales Invoice":
@@ -104,6 +105,7 @@
bt.docstatus = 1""", (payment_entry.payment_document, payment_entry.payment_entry), as_dict=True)
def get_paid_amount(payment_entry, currency, bank_account):
+ print(payment_entry.payment_document, "#@#@#@")
if payment_entry.payment_document in ["Payment Entry", "Sales Invoice", "Purchase Invoice"]:
paid_amount_field = "paid_amount"
@@ -116,11 +118,18 @@
payment_entry.payment_entry, paid_amount_field)
elif payment_entry.payment_document == "Journal Entry":
- return frappe.db.get_value('Journal Entry Account', {'parent': payment_entry.payment_entry, 'account': bank_account}, "sum(credit_in_account_currency)")
+ return frappe.db.get_value('Journal Entry Account', {'parent': payment_entry.payment_entry, 'account': bank_account},
+ "sum(credit_in_account_currency)")
elif payment_entry.payment_document == "Expense Claim":
return frappe.db.get_value(payment_entry.payment_document, payment_entry.payment_entry, "total_amount_reimbursed")
+ elif payment_entry.payment_document == "Loan Disbursement":
+ return frappe.db.get_value(payment_entry.payment_document, payment_entry.payment_entry, "disbursed_amount")
+
+ elif payment_entry.payment_document == "Loan Repayment":
+ return frappe.db.get_value(payment_entry.payment_document, payment_entry.payment_entry, "amount_paid")
+
else:
frappe.throw("Please reconcile {0}: {1} manually".format(payment_entry.payment_document, payment_entry.payment_entry))
diff --git a/erpnext/loan_management/doctype/loan_repayment/loan_repayment.json b/erpnext/loan_management/doctype/loan_repayment/loan_repayment.json
index 766602d..480e010 100644
--- a/erpnext/loan_management/doctype/loan_repayment/loan_repayment.json
+++ b/erpnext/loan_management/doctype/loan_repayment/loan_repayment.json
@@ -40,7 +40,7 @@
"repayment_details",
"amended_from",
"accounting_details_section",
- "repayment_account",
+ "payment_account",
"penalty_income_account",
"column_break_36",
"loan_account"
@@ -281,7 +281,7 @@
},
{
"fetch_from": "against_loan.payment_account",
- "fieldname": "repayment_account",
+ "fieldname": "payment_account",
"fieldtype": "Link",
"label": "Repayment Account",
"options": "Account",
@@ -311,7 +311,7 @@
"index_web_pages_for_search": 1,
"is_submittable": 1,
"links": [],
- "modified": "2022-02-17 19:10:07.742298",
+ "modified": "2022-02-18 19:10:07.742298",
"modified_by": "Administrator",
"module": "Loan Management",
"name": "Loan Repayment",