feat:filters on bank reconciliation
Added date filters on bank transactions, payment entries and journal entries and sorted list as per date in ascending order.
diff --git a/erpnext/accounts/doctype/bank_reconciliation_tool/bank_reconciliation_tool.js b/erpnext/accounts/doctype/bank_reconciliation_tool/bank_reconciliation_tool.js
index 28e79b5..3d2b54c 100644
--- a/erpnext/accounts/doctype/bank_reconciliation_tool/bank_reconciliation_tool.js
+++ b/erpnext/accounts/doctype/bank_reconciliation_tool/bank_reconciliation_tool.js
@@ -160,6 +160,9 @@
).$wrapper,
bank_statement_from_date: frm.doc.bank_statement_from_date,
bank_statement_to_date: frm.doc.bank_statement_to_date,
+ filtered_by_reference_date:frm.doc.filtered_by_reference_date,
+ from_reference_date:frm.doc.from_reference_date,
+ to_reference_date:frm.doc.to_reference_date,
bank_statement_closing_balance:
frm.doc.bank_statement_closing_balance,
cards_manager: frm.cards_manager,
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 30cc56b..44583c1 100644
--- a/erpnext/accounts/doctype/bank_reconciliation_tool/bank_reconciliation_tool.py
+++ b/erpnext/accounts/doctype/bank_reconciliation_tool/bank_reconciliation_tool.py
@@ -25,8 +25,6 @@
@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])
@@ -52,8 +50,8 @@
"party",
],
filters=filters,
+ order_by="date",
)
- transactions = sorted(transactions, key=lambda x: x["date"]) if transactions else []
return transactions
@@ -330,23 +328,58 @@
@frappe.whitelist()
-def get_linked_payments(bank_transaction_name, document_types=None):
+def get_linked_payments(
+ bank_transaction_name,
+ document_types=None,
+ from_date=None,
+ to_date=None,
+ filtered_by_reference_date=None,
+ from_reference_date=None,
+ to_reference_date=None,
+):
# get all matching payments for a bank transaction
transaction = frappe.get_doc("Bank Transaction", bank_transaction_name)
bank_account = frappe.db.get_values(
"Bank Account", transaction.bank_account, ["account", "company"], as_dict=True
)[0]
(account, company) = (bank_account.account, bank_account.company)
- matching = check_matching(account, company, transaction, document_types)
+ matching = check_matching(
+ account,
+ company,
+ transaction,
+ document_types,
+ from_date,
+ to_date,
+ filtered_by_reference_date,
+ from_reference_date,
+ to_reference_date,
+ )
return matching
-def check_matching(bank_account, company, transaction, document_types):
+def check_matching(
+ bank_account,
+ company,
+ transaction,
+ document_types,
+ from_date,
+ to_date,
+ filtered_by_reference_date,
+ from_reference_date,
+ to_reference_date,
+):
# 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,
+ from_date,
+ to_date,
+ filtered_by_reference_date,
+ from_reference_date,
+ to_reference_date,
)
- subquery = get_queries(bank_account, company, transaction, document_types)
filters = {
"amount": transaction.unallocated_amount,
"payment_type": "Receive" if transaction.deposit > 0 else "Pay",
@@ -367,19 +400,20 @@
filters,
)
)
- 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
+ return sorted(matching_vouchers, key=lambda x: x[0], reverse=True) if matching_vouchers else []
-def get_queries(bank_account, company, transaction, document_types):
+def get_queries(
+ bank_account,
+ company,
+ transaction,
+ document_types,
+ from_date,
+ to_date,
+ filtered_by_reference_date,
+ from_reference_date,
+ to_reference_date,
+):
# get queries to get matching vouchers
amount_condition = "=" if "exact_match" in document_types else "<="
account_from_to = "paid_to" if transaction.deposit > 0 else "paid_from"
@@ -395,6 +429,11 @@
document_types,
amount_condition,
account_from_to,
+ from_date,
+ to_date,
+ filtered_by_reference_date,
+ from_reference_date,
+ to_reference_date,
)
or []
)
@@ -403,15 +442,42 @@
def get_matching_queries(
- bank_account, company, transaction, document_types, amount_condition, account_from_to
+ bank_account,
+ company,
+ transaction,
+ document_types,
+ amount_condition,
+ account_from_to,
+ from_date,
+ to_date,
+ filtered_by_reference_date,
+ from_reference_date,
+ to_reference_date,
):
queries = []
if "payment_entry" in document_types:
- pe_amount_matching = get_pe_matching_query(amount_condition, account_from_to, transaction)
+ pe_amount_matching = get_pe_matching_query(
+ amount_condition,
+ account_from_to,
+ transaction,
+ from_date,
+ to_date,
+ filtered_by_reference_date,
+ from_reference_date,
+ to_reference_date,
+ )
queries.extend([pe_amount_matching])
if "journal_entry" in document_types:
- je_amount_matching = get_je_matching_query(amount_condition, transaction)
+ je_amount_matching = get_je_matching_query(
+ amount_condition,
+ transaction,
+ from_date,
+ to_date,
+ filtered_by_reference_date,
+ from_reference_date,
+ to_reference_date,
+ )
queries.extend([je_amount_matching])
if transaction.deposit > 0 and "sales_invoice" in document_types:
@@ -518,41 +584,27 @@
return vouchers
-def get_pe_matching_query(amount_condition, account_from_to, transaction):
+def get_pe_matching_query(
+ amount_condition,
+ account_from_to,
+ transaction,
+ from_date,
+ to_date,
+ filtered_by_reference_date,
+ from_reference_date,
+ to_reference_date,
+):
# 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"
- cond_filtered_from_ref_date = ""
- cond_filtered_to_ref_date = ""
- cond_filtered_from_posting_date = ""
- cond_filtered_to_posting_date = ""
- from_ref_date =""
- to_ref_date =""
- from_post_date = ""
- to_post_date = ""
- if(filtered_by_reference_date):
- cond_filtered_from_ref_date = " AND reference_date >="
- cond_filtered_to_ref_date = " AND reference_date <="
- from_ref_date = from_reference_date
- to_ref_date = to_reference_date
- elif(not filtered_by_reference_date):
- cond_filtered_from_posting_date = " AND posting_date >="
- cond_filtered_to_posting_date = " AND posting_date <="
- from_post_date = from_date
- to_post_date = to_date
-
- pe_data= f"""
+ filter_by_date = f"AND posting_date between '{from_date}' and '{to_date}'"
+ order_by = " posting_date"
+ if filtered_by_reference_date == "1":
+ filter_by_date = f"AND reference_date between '{from_reference_date}' and '{to_reference_date}'"
+ order_by = " reference_date"
+ 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
@@ -574,49 +626,33 @@
AND payment_type IN (%(payment_type)s, 'Internal Transfer')
AND ifnull(clearance_date, '') = ""
AND {account_from_to} = %(bank_account)s
- AND reference_no = '{transaction.reference_number}'
- {cond_filtered_from_ref_date} "{from_ref_date}"
- {cond_filtered_to_ref_date} "{to_ref_date}"
- {cond_filtered_from_posting_date} "{from_post_date}"
- {cond_filtered_to_posting_date} "{to_post_date}"
- """
- return pe_data
+ {filter_by_date}
+ order by{order_by}
+
+ """
-def get_je_matching_query(amount_condition, transaction):
+def get_je_matching_query(
+ amount_condition,
+ transaction,
+ from_date,
+ to_date,
+ filtered_by_reference_date,
+ from_reference_date,
+ to_reference_date,
+):
# get matching journal entry query
# 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"
- cond_filtered_from_ref_date = ""
- cond_filtered_to_ref_date = ""
- cond_filtered_from_posting_date = ""
- cond_filtered_to_posting_date = ""
- from_ref_date =""
- to_ref_date =""
- from_post_date = ""
- to_post_date = ""
- if(filtered_by_reference_date):
- cond_filtered_from_ref_date = " AND je.cheque_date >="
- cond_filtered_to_ref_date = " AND je.cheque_date <="
- from_ref_date = from_reference_date
- to_ref_date = to_reference_date
- elif(not filtered_by_reference_date):
- cond_filtered_from_posting_date = " AND je.posting_date>="
- cond_filtered_to_posting_date = " AND je.posting_date <="
- from_post_date = from_date
- to_post_date = to_date
- je_data = f"""
+ # filter_by_date = f"AND je.posting_date between '{from_date}' and '{to_date}'"
+ order_by = " je.posting_date"
+ if filtered_by_reference_date == "1":
+ filter_by_date = f"AND je.cheque_date between '{from_reference_date}' and '{to_reference_date}'"
+ order_by = " je.cheque_date"
+
+ return f"""
SELECT
(CASE WHEN je.cheque_no=%(reference_no)s THEN 1 ELSE 0 END
+ 1) AS rank ,
@@ -640,13 +676,10 @@
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_no = '{transaction.reference_number}'
- {cond_filtered_from_ref_date} "{from_ref_date}"
- {cond_filtered_to_ref_date} "{to_ref_date}"
- {cond_filtered_from_posting_date} "{from_post_date}"
- {cond_filtered_to_posting_date} "{to_post_date}"
- """
- return je_data
+ {filter_by_date}
+ order by {order_by}
+ """
+
def get_si_matching_query(amount_condition):
# get matchin sales invoice query
diff --git a/erpnext/hooks.py b/erpnext/hooks.py
index 1efc82e..92601b3 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/data_table_manager.js b/erpnext/public/js/bank_reconciliation_tool/data_table_manager.js
index 9ef8ce6..e1914b4 100644
--- a/erpnext/public/js/bank_reconciliation_tool/data_table_manager.js
+++ b/erpnext/public/js/bank_reconciliation_tool/data_table_manager.js
@@ -5,7 +5,12 @@
Object.assign(this, opts);
this.dialog_manager = new erpnext.accounts.bank_reconciliation.DialogManager(
this.company,
- this.bank_account
+ this.bank_account,
+ this.bank_statement_from_date,
+ this.bank_statement_to_date,
+ this.filtered_by_reference_date,
+ this.from_reference_date,
+ this.to_reference_date
);
this.make_dt();
}
@@ -17,6 +22,8 @@
"erpnext.accounts.doctype.bank_reconciliation_tool.bank_reconciliation_tool.get_bank_transactions",
args: {
bank_account: this.bank_account,
+ from_date:this.bank_statement_from_date,
+ to_date:this.bank_statement_to_date
},
callback: function (response) {
me.format_data(response.message);
diff --git a/erpnext/public/js/bank_reconciliation_tool/dialog_manager.js b/erpnext/public/js/bank_reconciliation_tool/dialog_manager.js
index d7c0e61..5d59497 100644
--- a/erpnext/public/js/bank_reconciliation_tool/dialog_manager.js
+++ b/erpnext/public/js/bank_reconciliation_tool/dialog_manager.js
@@ -5,6 +5,11 @@
this.bank_account = bank_account;
this.company = company;
this.make_dialog();
+ this.bank_statement_from_date = bank_statement_from_date;
+ this.bank_statement_to_date = bank_statement_to_date;
+ this.filtered_by_reference_date = filtered_by_reference_date;
+ this.from_reference_date = from_reference_date;
+ this.to_reference_date = to_reference_date;
}
show_dialog(bank_transaction_name, update_dt_cards) {
@@ -50,7 +55,7 @@
this.company = company;
this.make_dialog();
}
-
+
show_dialog(bank_transaction_name, update_dt_cards) {
this.bank_transaction_name = bank_transaction_name;
this.update_dt_cards = update_dt_cards;
@@ -86,7 +91,7 @@
},
});
}
-
+
get_linked_vouchers(document_types) {
frappe.call({
method:
@@ -94,12 +99,17 @@
args: {
bank_transaction_name: this.bank_transaction_name,
document_types: document_types,
+ from_date: this.bank_statement_from_date,
+ to_date: this.bank_statement_to_date,
+ filtered_by_reference_date: this.filtered_by_reference_date,
+ from_reference_date:this.from_reference_date,
+ to_reference_date:this.to_reference_date
},
-
+
callback: (result) => {
const data = result.message;
-
-
+
+
if (data && data.length > 0) {
const proposals_wrapper = this.dialog.fields_dict.payment_proposals.$wrapper;
proposals_wrapper.show();
@@ -123,13 +133,13 @@
const proposals_wrapper = this.dialog.fields_dict.payment_proposals.$wrapper;
proposals_wrapper.hide();
this.dialog.fields_dict.no_matching_vouchers.$wrapper.show();
-
+
}
this.dialog.show();
},
});
}
-
+
get_dt_columns() {
this.columns = [
{
@@ -162,7 +172,7 @@
editable: false,
width: 120,
},
-
+
{
name: __("Reference Number"),
editable: false,
@@ -170,7 +180,7 @@
},
];
}
-
+
get_datatable(proposals_wrapper) {
if (!this.datatable) {
const datatable_options = {
@@ -189,11 +199,11 @@
this.datatable.rowmanager.checkMap = [];
}
}
-
+
make_dialog() {
const me = this;
me.selected_payment = null;
-
+
const fields = [
{
label: __("Action"),
@@ -221,7 +231,7 @@
depends_on: "eval:doc.action=='Match Against Voucher'",
},
];
-
+
frappe.call({
method: "erpnext.accounts.doctype.bank_transaction.bank_transaction.get_doctypes_for_bank_reconciliation",
callback: (r) => {
@@ -238,9 +248,9 @@
onchange: () => this.update_options(),
});
});
-
+
fields.push(...this.get_voucher_fields());
-
+
me.dialog = new frappe.ui.Dialog({
title: __("Reconcile the Bank Transaction"),
fields: fields,
@@ -251,7 +261,7 @@
}
});
}
-
+
get_voucher_fields() {
return [
{
@@ -431,7 +441,7 @@
label: "Allocated Amount",
read_only: 1,
},
-
+
{
fieldname: "unallocated_amount",
fieldtype: "Currency",
@@ -440,7 +450,7 @@
},
];
}
-
+
get_selected_attributes() {
let selected_attributes = [];
this.dialog.$wrapper.find(".checkbox input").each((i, col) => {
@@ -448,15 +458,15 @@
selected_attributes.push($(col).attr("data-fieldname"));
}
});
-
+
return selected_attributes;
}
-
+
update_options() {
let selected_attributes = this.get_selected_attributes();
this.get_linked_vouchers(selected_attributes);
}
-
+
reconciliation_dialog_primary_action(values) {
if (values.action == "Match Against Voucher") this.match(values);
if (
@@ -472,7 +482,7 @@
else if (values.action == "Update Bank Transaction")
this.update_transaction(values);
}
-
+
match() {
var selected_map = this.datatable.rowmanager.checkMap;
let rows = [];
@@ -502,7 +512,7 @@
},
});
}
-
+
add_payment_entry(values) {
frappe.call({
method:
@@ -526,7 +536,7 @@
},
});
}
-
+
add_journal_entry(values) {
frappe.call({
method:
@@ -550,7 +560,7 @@
},
});
}
-
+
update_transaction(values) {
frappe.call({
method:
@@ -569,7 +579,7 @@
},
});
}
-
+
edit_in_full_page() {
const values = this.dialog.get_values(true);
if (values.document_type == "Payment Entry") {
@@ -616,9 +626,9 @@
});
}
}
-
+
};
-
+
get_linked_vouchers(document_types) {
frappe.call({
method: