Merge pull request #30763 from deepeshgarg007/loan_reconciliation
fix: Add loan doctypes in bank clearance
diff --git a/erpnext/accounts/doctype/bank_clearance/bank_clearance.py b/erpnext/accounts/doctype/bank_clearance/bank_clearance.py
index 96779d7..0f617b5 100644
--- a/erpnext/accounts/doctype/bank_clearance/bank_clearance.py
+++ b/erpnext/accounts/doctype/bank_clearance/bank_clearance.py
@@ -5,7 +5,10 @@
import frappe
from frappe import _, msgprint
from frappe.model.document import Document
-from frappe.utils import flt, fmt_money, getdate, nowdate
+from frappe.query_builder.custom import ConstantColumn
+from frappe.utils import flt, fmt_money, getdate
+
+import erpnext
form_grid_templates = {"journal_entries": "templates/form_grid/bank_reconciliation_grid.html"}
@@ -76,6 +79,52 @@
as_dict=1,
)
+ loan_disbursement = frappe.qb.DocType("Loan Disbursement")
+
+ loan_disbursements = (
+ frappe.qb.from_(loan_disbursement)
+ .select(
+ ConstantColumn("Loan Disbursement").as_("payment_document"),
+ loan_disbursement.name.as_("payment_entry"),
+ loan_disbursement.disbursed_amount.as_("credit"),
+ ConstantColumn(0).as_("debit"),
+ loan_disbursement.reference_number.as_("cheque_number"),
+ loan_disbursement.reference_date.as_("cheque_date"),
+ loan_disbursement.disbursement_date.as_("posting_date"),
+ loan_disbursement.applicant.as_("against_account"),
+ )
+ .where(loan_disbursement.docstatus == 1)
+ .where(loan_disbursement.disbursement_date >= self.from_date)
+ .where(loan_disbursement.disbursement_date <= self.to_date)
+ .where(loan_disbursement.clearance_date.isnull())
+ .where(loan_disbursement.disbursement_account.isin([self.bank_account, self.account]))
+ .orderby(loan_disbursement.disbursement_date)
+ .orderby(loan_disbursement.name, frappe.qb.desc)
+ ).run(as_dict=1)
+
+ loan_repayment = frappe.qb.DocType("Loan Repayment")
+
+ loan_repayments = (
+ frappe.qb.from_(loan_repayment)
+ .select(
+ ConstantColumn("Loan Repayment").as_("payment_document"),
+ loan_repayment.name.as_("payment_entry"),
+ loan_repayment.amount_paid.as_("debit"),
+ ConstantColumn(0).as_("credit"),
+ loan_repayment.reference_number.as_("cheque_number"),
+ loan_repayment.reference_date.as_("cheque_date"),
+ loan_repayment.applicant.as_("against_account"),
+ loan_repayment.posting_date,
+ )
+ .where(loan_repayment.docstatus == 1)
+ .where(loan_repayment.clearance_date.isnull())
+ .where(loan_repayment.posting_date >= self.from_date)
+ .where(loan_repayment.posting_date <= self.to_date)
+ .where(loan_repayment.payment_account.isin([self.bank_account, self.account]))
+ .orderby(loan_repayment.posting_date)
+ .orderby(loan_repayment.name, frappe.qb.desc)
+ ).run(as_dict=1)
+
pos_sales_invoices, pos_purchase_invoices = [], []
if self.include_pos_transactions:
pos_sales_invoices = frappe.db.sql(
@@ -114,20 +163,29 @@
entries = sorted(
list(payment_entries)
- + list(journal_entries + list(pos_sales_invoices) + list(pos_purchase_invoices)),
- key=lambda k: k["posting_date"] or getdate(nowdate()),
+ + list(journal_entries)
+ + list(pos_sales_invoices)
+ + list(pos_purchase_invoices)
+ + list(loan_disbursements)
+ + list(loan_repayments),
+ key=lambda k: getdate(k["posting_date"]),
)
self.set("payment_entries", [])
self.total_amount = 0.0
+ default_currency = erpnext.get_default_currency()
for d in entries:
row = self.append("payment_entries", {})
amount = flt(d.get("debit", 0)) - flt(d.get("credit", 0))
+ if not d.get("account_currency"):
+ d.account_currency = default_currency
+
formatted_amount = fmt_money(abs(amount), 2, d.account_currency)
d.amount = formatted_amount + " " + (_("Dr") if amount > 0 else _("Cr"))
+ d.posting_date = getdate(d.posting_date)
d.pop("credit")
d.pop("debit")
diff --git a/erpnext/accounts/doctype/bank_clearance/test_bank_clearance.py b/erpnext/accounts/doctype/bank_clearance/test_bank_clearance.py
index 706fbbe..c1e55f6 100644
--- a/erpnext/accounts/doctype/bank_clearance/test_bank_clearance.py
+++ b/erpnext/accounts/doctype/bank_clearance/test_bank_clearance.py
@@ -1,9 +1,96 @@
# Copyright (c) 2020, Frappe Technologies Pvt. Ltd. and Contributors
# See license.txt
-# import frappe
import unittest
+import frappe
+from frappe.utils import add_months, getdate
+
+from erpnext.accounts.doctype.payment_entry.test_payment_entry import get_payment_entry
+from erpnext.accounts.doctype.purchase_invoice.test_purchase_invoice import make_purchase_invoice
+from erpnext.loan_management.doctype.loan.test_loan import (
+ create_loan,
+ create_loan_accounts,
+ create_loan_type,
+ create_repayment_entry,
+ make_loan_disbursement_entry,
+)
+
class TestBankClearance(unittest.TestCase):
- pass
+ @classmethod
+ def setUpClass(cls):
+ make_bank_account()
+ create_loan_accounts()
+ create_loan_masters()
+ add_transactions()
+
+ # Basic test case to test if bank clearance tool doesn't break
+ # Detailed test can be added later
+ def test_bank_clearance(self):
+ bank_clearance = frappe.get_doc("Bank Clearance")
+ bank_clearance.account = "_Test Bank Clearance - _TC"
+ bank_clearance.from_date = add_months(getdate(), -1)
+ bank_clearance.to_date = getdate()
+ bank_clearance.get_payment_entries()
+ self.assertEqual(len(bank_clearance.payment_entries), 3)
+
+
+def make_bank_account():
+ if not frappe.db.get_value("Account", "_Test Bank Clearance - _TC"):
+ frappe.get_doc(
+ {
+ "doctype": "Account",
+ "account_type": "Bank",
+ "account_name": "_Test Bank Clearance",
+ "company": "_Test Company",
+ "parent_account": "Bank Accounts - _TC",
+ }
+ ).insert()
+
+
+def create_loan_masters():
+ create_loan_type(
+ "Clearance Loan",
+ 2000000,
+ 13.5,
+ 25,
+ 0,
+ 5,
+ "Cash",
+ "_Test Bank Clearance - _TC",
+ "_Test Bank Clearance - _TC",
+ "Loan Account - _TC",
+ "Interest Income Account - _TC",
+ "Penalty Income Account - _TC",
+ )
+
+
+def add_transactions():
+ make_payment_entry()
+ make_loan()
+
+
+def make_loan():
+ loan = create_loan(
+ "_Test Customer",
+ "Clearance Loan",
+ 280000,
+ "Repay Over Number of Periods",
+ 20,
+ applicant_type="Customer",
+ )
+ loan.submit()
+ make_loan_disbursement_entry(loan.name, loan.loan_amount, disbursement_date=getdate())
+ repayment_entry = create_repayment_entry(loan.name, "_Test Customer", getdate(), loan.loan_amount)
+ repayment_entry.save()
+ repayment_entry.submit()
+
+
+def make_payment_entry():
+ pi = make_purchase_invoice(supplier="_Test Supplier", qty=1, rate=690)
+ pe = get_payment_entry("Purchase Invoice", pi.name, bank_account="_Test Bank Clearance - _TC")
+ pe.reference_no = "Conrad Oct 18"
+ pe.reference_date = "2018-10-24"
+ pe.insert()
+ pe.submit()