refactor: run qb directly instead of converting to sql
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 9e6b51d..7e70a7d 100644
--- a/erpnext/accounts/doctype/bank_reconciliation_tool/bank_reconciliation_tool.py
+++ b/erpnext/accounts/doctype/bank_reconciliation_tool/bank_reconciliation_tool.py
@@ -509,6 +509,18 @@
to_reference_date,
):
exact_match = True if "exact_match" in document_types else False
+
+ common_filters = frappe._dict(
+ {
+ "amount": transaction.unallocated_amount,
+ "payment_type": "Receive" if transaction.deposit > 0.0 else "Pay",
+ "reference_no": transaction.reference_number,
+ "party_type": transaction.party_type,
+ "party": transaction.party,
+ "bank_account": bank_account,
+ }
+ )
+
queries = get_queries(
bank_account,
company,
@@ -520,20 +532,12 @@
from_reference_date,
to_reference_date,
exact_match,
+ common_filters,
)
- filters = {
- "amount": transaction.unallocated_amount,
- "payment_type": "Receive" if transaction.deposit > 0.0 else "Pay",
- "reference_no": transaction.reference_number,
- "party_type": transaction.party_type,
- "party": transaction.party,
- "bank_account": bank_account,
- }
-
matching_vouchers = []
for query in queries:
- matching_vouchers.extend(frappe.db.sql(query, filters, as_dict=True))
+ matching_vouchers.extend(query.run(as_dict=True))
return (
sorted(matching_vouchers, key=lambda x: x["rank"], reverse=True) if matching_vouchers else []
@@ -551,6 +555,7 @@
from_reference_date,
to_reference_date,
exact_match,
+ common_filters,
):
# get queries to get matching vouchers
account_from_to = "paid_to" if transaction.deposit > 0.0 else "paid_from"
@@ -571,6 +576,7 @@
filter_by_reference_date,
from_reference_date,
to_reference_date,
+ common_filters,
)
or []
)
@@ -590,6 +596,7 @@
filter_by_reference_date,
from_reference_date,
to_reference_date,
+ common_filters,
):
queries = []
currency = get_account_currency(bank_account)
@@ -604,6 +611,7 @@
filter_by_reference_date,
from_reference_date,
to_reference_date,
+ common_filters,
)
queries.append(query)
@@ -616,16 +624,17 @@
filter_by_reference_date,
from_reference_date,
to_reference_date,
+ common_filters,
)
queries.append(query)
if transaction.deposit > 0.0 and "sales_invoice" in document_types:
- query = get_si_matching_query(exact_match, currency)
+ query = get_si_matching_query(exact_match, currency, common_filters)
queries.append(query)
if transaction.withdrawal > 0.0:
if "purchase_invoice" in document_types:
- query = get_pi_matching_query(exact_match, currency)
+ query = get_pi_matching_query(exact_match, currency, common_filters)
queries.append(query)
if "bank_transaction" in document_types:
@@ -680,7 +689,7 @@
.where(amount_condition)
.where(bt.docstatus == 1)
)
- return str(query)
+ return query
def get_pe_matching_query(
@@ -692,6 +701,7 @@
filter_by_reference_date,
from_reference_date,
to_reference_date,
+ common_filters,
):
# get matching payment entries query
to_from = "to" if transaction.deposit > 0.0 else "from"
@@ -734,7 +744,7 @@
.where(pe.docstatus == 1)
.where(pe.payment_type.isin([payment_type, "Internal Transfer"]))
.where(pe.clearance_date.isnull())
- .where(getattr(pe, account_from_to) == Parameter("%(bank_account)s"))
+ .where(getattr(pe, account_from_to) == common_filters.bank_account)
.where(amount_condition)
.where(filter_by_date)
.orderby(pe.reference_date if cint(filter_by_reference_date) else pe.posting_date)
@@ -743,7 +753,7 @@
if frappe.flags.auto_reconcile_vouchers == True:
query = query.where(ref_condition)
- return str(query)
+ return query
def get_je_matching_query(
@@ -754,6 +764,7 @@
filter_by_reference_date,
from_reference_date,
to_reference_date,
+ common_filters,
):
# get matching journal entry query
# We have mapping at the bank level
@@ -793,7 +804,7 @@
.where(je.docstatus == 1)
.where(je.voucher_type != "Opening Entry")
.where(je.clearance_date.isnull())
- .where(jea.account == Parameter("%(bank_account)s"))
+ .where(jea.account == common_filters.bank_account)
.where(amount_equality if exact_match else getattr(jea, amount_field) > 0.0)
.where(je.docstatus == 1)
.where(filter_by_date)
@@ -803,10 +814,10 @@
if frappe.flags.auto_reconcile_vouchers == True:
query = query.where(ref_condition)
- return str(query)
+ return query
-def get_si_matching_query(exact_match, currency):
+def get_si_matching_query(exact_match, currency, common_filters):
# get matching sales invoice query
si = frappe.qb.DocType("Sales Invoice")
sip = frappe.qb.DocType("Sales Invoice Payment")
@@ -836,15 +847,15 @@
)
.where(si.docstatus == 1)
.where(sip.clearance_date.isnull())
- .where(sip.account == Parameter("%(bank_account)s"))
+ .where(sip.account == common_filters.bank_account)
.where(amount_condition)
.where(si.currency == currency)
)
- return str(query)
+ return query
-def get_pi_matching_query(exact_match, currency):
+def get_pi_matching_query(exact_match, currency, common_filters):
# get matching purchase invoice query when they are also used as payment entries (is_paid)
purchase_invoice = frappe.qb.DocType("Purchase Invoice")
@@ -872,9 +883,9 @@
.where(purchase_invoice.docstatus == 1)
.where(purchase_invoice.is_paid == 1)
.where(purchase_invoice.clearance_date.isnull())
- .where(purchase_invoice.cash_bank_account == Parameter("%(bank_account)s"))
+ .where(purchase_invoice.cash_bank_account == common_filters.bank_account)
.where(amount_condition)
.where(purchase_invoice.currency == currency)
)
- return str(query)
+ return query