Update bank_reconciliation_tool.py
Applying date filter on transactions and all the bank entries and also gives the filter the bank entries as per reference date. Sorted all transactions and entries as per date in ascending order.
Also added posting date columns in all bank entries and default checkbox tick of journal entry, hide the sales invoice and purchase invoice checkbox.
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 d353270..cd99d38 100644
--- a/erpnext/accounts/doctype/bank_reconciliation_tool/bank_reconciliation_tool.py
+++ b/erpnext/accounts/doctype/bank_reconciliation_tool/bank_reconciliation_tool.py
@@ -25,6 +25,8 @@
@frappe.whitelist()
def get_bank_transactions(bank_account, from_date=None, to_date=None):
# returns bank transactions for a bank account
+ from_date = frappe.db.get_single_value('Bank Reconciliation Tool','bank_statement_from_date')
+ to_date = frappe.db.get_single_value('Bank Reconciliation Tool','bank_statement_to_date')
filters = []
filters.append(["bank_account", "=", bank_account])
filters.append(["docstatus", "=", 1])
@@ -51,9 +53,11 @@
],
filters=filters,
)
+ transactions= sorted(transactions, key=lambda x: x['date']) if transactions else []
return transactions
+
@frappe.whitelist()
def get_account_balance(bank_account, till_date):
# returns account balance till the specified date
@@ -340,6 +344,7 @@
def check_matching(bank_account, company, transaction, document_types):
# combine all types of vouchers
+ filtered_by_reference_date = frappe.db.get_single_value('Bank Reconciliation Tool','filtered_by_reference_date')
subquery = get_queries(bank_account, company, transaction, document_types)
filters = {
"amount": transaction.unallocated_amount,
@@ -361,8 +366,12 @@
filters,
)
)
-
- return sorted(matching_vouchers, key=lambda x: x[0], reverse=True) if matching_vouchers else []
+ matching_vouchers_with_ref_no = tuple(ele for ele in matching_vouchers if frappe.as_json(ele[5]) != "null")
+ if filtered_by_reference_date:
+ matching_vouchers = sorted(matching_vouchers_with_ref_no , key=lambda x: x[5]) if matching_vouchers else []
+ else:
+ matching_vouchers = sorted(matching_vouchers, key=lambda x: x[8]) if matching_vouchers else []
+ return matching_vouchers
def get_queries(bank_account, company, transaction, document_types):
@@ -506,33 +515,72 @@
def get_pe_matching_query(amount_condition, account_from_to, transaction):
# get matching payment entries query
+ from_date = frappe.db.get_single_value('Bank Reconciliation Tool','bank_statement_from_date')
+ to_date = frappe.db.get_single_value('Bank Reconciliation Tool','bank_statement_to_date')
+ from_reference_date = frappe.db.get_single_value('Bank Reconciliation Tool','from_reference_date')
+ to_reference_date = frappe.db.get_single_value('Bank Reconciliation Tool','to_reference_date')
+ filtered_by_reference_date = frappe.db.get_single_value('Bank Reconciliation Tool','filtered_by_reference_date')
if transaction.deposit > 0:
currency_field = "paid_to_account_currency as currency"
else:
currency_field = "paid_from_account_currency as currency"
- return f"""
- SELECT
- (CASE WHEN reference_no=%(reference_no)s THEN 1 ELSE 0 END
- + CASE WHEN (party_type = %(party_type)s AND party = %(party)s ) THEN 1 ELSE 0 END
- + 1 ) AS rank,
- 'Payment Entry' as doctype,
- name,
- paid_amount,
- reference_no,
- reference_date,
- party,
- party_type,
- posting_date,
- {currency_field}
- FROM
- `tabPayment Entry`
- WHERE
- paid_amount {amount_condition} %(amount)s
- AND docstatus = 1
- AND payment_type IN (%(payment_type)s, 'Internal Transfer')
- AND ifnull(clearance_date, '') = ""
- AND {account_from_to} = %(bank_account)s
- """
+ if (filtered_by_reference_date):
+ pe_data= f"""
+ SELECT
+ (CASE WHEN reference_no=%(reference_no)s THEN 1 ELSE 0 END
+ + CASE WHEN (party_type = %(party_type)s AND party = %(party)s ) THEN 1 ELSE 0 END
+ + 1 ) AS rank,
+ 'Payment Entry' as doctype,
+ name,
+ paid_amount,
+ reference_no,
+ reference_date,
+ party,
+ party_type,
+ posting_date,
+ {currency_field}
+ FROM
+ `tabPayment Entry`
+ WHERE
+ paid_amount {amount_condition} %(amount)s
+ AND docstatus = 1
+ AND payment_type IN (%(payment_type)s, 'Internal Transfer')
+ AND ifnull(clearance_date, '') = ""
+ AND {account_from_to} = %(bank_account)s
+ AND reference_date >= '{from_reference_date}'
+ AND reference_date <= '{to_reference_date}'
+
+ """
+ else:
+ pe_data= f"""
+ SELECT
+ (CASE WHEN reference_no=%(reference_no)s THEN 1 ELSE 0 END
+ + CASE WHEN (party_type = %(party_type)s AND party = %(party)s ) THEN 1 ELSE 0 END
+ + 1 ) AS rank,
+ 'Payment Entry' as doctype,
+ name,
+ paid_amount,
+ reference_no,
+ reference_date,
+ party,
+ party_type,
+ posting_date,
+ {currency_field}
+ FROM
+ `tabPayment Entry`
+ WHERE
+ paid_amount {amount_condition} %(amount)s
+ AND docstatus = 1
+ AND payment_type IN (%(payment_type)s, 'Internal Transfer')
+ AND ifnull(clearance_date, '') = ""
+ AND {account_from_to} = %(bank_account)s
+ AND posting_date >= '{from_date}'
+ AND posting_date <= '{to_date}'
+
+ """
+ return pe_data
+
+
def get_je_matching_query(amount_condition, transaction):
@@ -541,35 +589,69 @@
# We have mapping at the bank level
# So one bank could have both types of bank accounts like asset and liability
# So cr_or_dr should be judged only on basis of withdrawal and deposit and not account type
+ from_date = frappe.db.get_single_value('Bank Reconciliation Tool','bank_statement_from_date')
+ to_date = frappe.db.get_single_value('Bank Reconciliation Tool','bank_statement_to_date')
+ from_reference_date = frappe.db.get_single_value('Bank Reconciliation Tool','from_reference_date')
+ to_reference_date = frappe.db.get_single_value('Bank Reconciliation Tool','to_reference_date')
+ filtered_by_reference_date = frappe.db.get_single_value('Bank Reconciliation Tool','filtered_by_reference_date')
cr_or_dr = "credit" if transaction.withdrawal > 0 else "debit"
-
- return f"""
-
- SELECT
- (CASE WHEN je.cheque_no=%(reference_no)s THEN 1 ELSE 0 END
- + 1) AS rank ,
- 'Journal Entry' as doctype,
- je.name,
- jea.{cr_or_dr}_in_account_currency as paid_amount,
- je.cheque_no as reference_no,
- je.cheque_date as reference_date,
- je.pay_to_recd_from as party,
- jea.party_type,
- je.posting_date,
- jea.account_currency as currency
- FROM
- `tabJournal Entry Account` as jea
- JOIN
- `tabJournal Entry` as je
- ON
- jea.parent = je.name
- WHERE
- (je.clearance_date is null or je.clearance_date='0000-00-00')
- AND jea.account = %(bank_account)s
- AND jea.{cr_or_dr}_in_account_currency {amount_condition} %(amount)s
- AND je.docstatus = 1
- """
-
+ if (filtered_by_reference_date==1):
+ je_data = f"""
+ SELECT
+ (CASE WHEN je.cheque_no=%(reference_no)s THEN 1 ELSE 0 END
+ + 1) AS rank ,
+ 'Journal Entry' as doctype,
+ je.name,
+ jea.{cr_or_dr}_in_account_currency as paid_amount,
+ je.cheque_no as reference_no,
+ je.cheque_date as reference_date,
+ je.pay_to_recd_from as party,
+ jea.party_type,
+ je.posting_date,
+ jea.account_currency as currency
+ FROM
+ `tabJournal Entry Account` as jea
+ JOIN
+ `tabJournal Entry` as je
+ ON
+ jea.parent = je.name
+ WHERE
+ (je.clearance_date is null or je.clearance_date='0000-00-00')
+ AND jea.account = %(bank_account)s
+ AND jea.{cr_or_dr}_in_account_currency {amount_condition} %(amount)s
+ AND je.docstatus = 1
+ AND je.cheque_date >= '{from_reference_date}'
+ AND je.cheque_date <= '{to_reference_date}'
+ """
+ else:
+ je_data = f"""
+ SELECT
+ (CASE WHEN je.cheque_no=%(reference_no)s THEN 1 ELSE 0 END
+ + 1) AS rank ,
+ 'Journal Entry' as doctype,
+ je.name,
+ jea.{cr_or_dr}_in_account_currency as paid_amount,
+ je.cheque_no as reference_no,
+ je.cheque_date as reference_date,
+ je.pay_to_recd_from as party,
+ jea.party_type,
+ je.posting_date,
+ jea.account_currency as currency
+ FROM
+ `tabJournal Entry Account` as jea
+ JOIN
+ `tabJournal Entry` as je
+ ON
+ jea.parent = je.name
+ WHERE
+ (je.clearance_date is null or je.clearance_date='0000-00-00')
+ AND jea.account = %(bank_account)s
+ AND jea.{cr_or_dr}_in_account_currency {amount_condition} %(amount)s
+ AND je.docstatus = 1
+ AND je.posting_date >= '{from_date}'
+ AND je.posting_date <= '{to_date}'
+ """
+ return je_data
def get_si_matching_query(amount_condition):
# get matchin sales invoice query
diff --git a/erpnext/hooks.py b/erpnext/hooks.py
index 92601b3..1efc82e 100644
--- a/erpnext/hooks.py
+++ b/erpnext/hooks.py
@@ -469,8 +469,8 @@
bank_reconciliation_doctypes = [
"Payment Entry",
"Journal Entry",
- "Purchase Invoice",
- "Sales Invoice",
+ # "Purchase Invoice",
+ # "Sales Invoice",
"Loan Repayment",
"Loan Disbursement",
]
diff --git a/erpnext/public/js/bank_reconciliation_tool/dialog_manager.js b/erpnext/public/js/bank_reconciliation_tool/dialog_manager.js
index ca01f68..bd00374 100644
--- a/erpnext/public/js/bank_reconciliation_tool/dialog_manager.js
+++ b/erpnext/public/js/bank_reconciliation_tool/dialog_manager.js
@@ -35,6 +35,7 @@
if (r.message) {
this.bank_transaction = r.message;
r.message.payment_entry = 1;
+ r.message.journal_entry = 1;
this.dialog.set_values(r.message);
this.dialog.show();
}
@@ -66,6 +67,7 @@
row[1],
row[2],
reference_date,
+ row[8],
format_currency(row[3], row[9]),
row[6],
row[4],
@@ -102,6 +104,11 @@
width: 120,
},
{
+ name: "Posting Date",
+ editable: false,
+ width: 120,
+ },
+ {
name: __("Amount"),
editable: false,
width: 100,