refactor: replace sql with query builder for Jourals query
diff --git a/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py
index f382434..26bf1c0 100644
--- a/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py
+++ b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py
@@ -195,66 +195,67 @@
return payment_entries
def get_jv_entries(self):
- condition = self.get_conditions()
+ je = qb.DocType("Journal Entry")
+ jea = qb.DocType("Journal Entry Account")
+ conditions = self.get_journal_filter_conditions()
+
+ # Dimension filters
+ for x in self.dimensions:
+ dimension = x.fieldname
+ if self.get(dimension):
+ conditions.append(jea[dimension] == self.get(dimension))
if self.payment_name:
- condition += f" and t1.name like '%%{self.payment_name}%%'"
+ conditions.append(je.name.like(f"%%{self.payent_name}%%"))
if self.get("cost_center"):
- condition += f" and t2.cost_center = '{self.cost_center}' "
+ conditions.append(jea.cost_center == self.cost_center)
dr_or_cr = (
"credit_in_account_currency"
if erpnext.get_party_account_type(self.party_type) == "Receivable"
else "debit_in_account_currency"
)
+ conditions.append(jea[dr_or_cr].gt(0))
- bank_account_condition = (
- "t2.against_account like %(bank_cash_account)s" if self.bank_cash_account else "1=1"
+ if self.bank_cash_account:
+ conditions.append(jea.against_account.like(f"%%{self.bank_cash_account}%%"))
+
+ journal_query = (
+ qb.from_(je)
+ .inner_join(jea)
+ .on(jea.parent == je.name)
+ .select(
+ ConstantColumn("Journal Entry").as_("reference_type"),
+ je.name.as_("reference_name"),
+ je.posting_date,
+ je.remark.as_("remarks"),
+ jea.name.as_("reference_row"),
+ jea[dr_or_cr].as_("amount"),
+ jea.is_advance,
+ jea.exchange_rate,
+ jea.account_currency.as_("currency"),
+ jea.cost_center.as_("cost_center"),
+ )
+ .where(
+ (je.docstatus == 1)
+ & (jea.party_type == self.party_type)
+ & (jea.party == self.party)
+ & (jea.account == self.receivable_payable_account)
+ & (
+ (jea.reference_type == "")
+ | (jea.reference_type.isnull())
+ | (jea.reference_type.isin(("Sales Order", "Purchase Order")))
+ )
+ )
+ .where(Criterion.all(conditions))
+ .orderby(je.posting_date)
)
- limit = f"limit {self.payment_limit}" if self.payment_limit else " "
+ if self.payment_limit:
+ journal_query = journal_query.limit(self.payment_limit)
- # nosemgrep
- journal_entries = frappe.db.sql(
- """
- select
- "Journal Entry" as reference_type, t1.name as reference_name,
- t1.posting_date, t1.remark as remarks, t2.name as reference_row,
- {dr_or_cr} as amount, t2.is_advance, t2.exchange_rate,
- t2.account_currency as currency, t2.cost_center as cost_center
- from
- `tabJournal Entry` t1, `tabJournal Entry Account` t2
- where
- t1.name = t2.parent and t1.docstatus = 1 and t2.docstatus = 1
- and t2.party_type = %(party_type)s and t2.party = %(party)s
- and t2.account = %(account)s and {dr_or_cr} > 0 {condition}
- and (t2.reference_type is null or t2.reference_type = '' or
- (t2.reference_type in ('Sales Order', 'Purchase Order')
- and t2.reference_name is not null and t2.reference_name != ''))
- and (CASE
- WHEN t1.voucher_type in ('Debit Note', 'Credit Note')
- THEN 1=1
- ELSE {bank_account_condition}
- END)
- order by t1.posting_date
- {limit}
- """.format(
- **{
- "dr_or_cr": dr_or_cr,
- "bank_account_condition": bank_account_condition,
- "condition": condition,
- "limit": limit,
- }
- ),
- {
- "party_type": self.party_type,
- "party": self.party,
- "account": self.receivable_payable_account,
- "bank_cash_account": "%%%s%%" % self.bank_cash_account,
- },
- as_dict=1,
- )
+ journal_entries = journal_query.run(as_dict=True)
return list(journal_entries)
@@ -698,37 +699,25 @@
self.build_dimensions_filter_conditions()
- def get_conditions(self, get_payments=False):
- condition = " and company = '{0}' ".format(self.company)
+ def get_journal_filter_conditions(self):
+ conditions = []
+ je = qb.DocType("Journal Entry")
+ jea = qb.DocType("Journal Entry Account")
+ conditions.append(je.company == self.company)
- if self.get("cost_center") and get_payments:
- condition = " and cost_center = '{0}' ".format(self.cost_center)
+ if self.from_payment_date:
+ conditions.append(je.posting_date.gte(self.from_payment_date))
- condition += (
- " and posting_date >= {0}".format(frappe.db.escape(self.from_payment_date))
- if self.from_payment_date
- else ""
- )
- condition += (
- " and posting_date <= {0}".format(frappe.db.escape(self.to_payment_date))
- if self.to_payment_date
- else ""
- )
+ if self.to_payment_date:
+ conditions.append(je.posting_date.lte(self.to_payment_date))
if self.minimum_payment_amount:
- condition += (
- " and unallocated_amount >= {0}".format(flt(self.minimum_payment_amount))
- if get_payments
- else " and total_debit >= {0}".format(flt(self.minimum_payment_amount))
- )
- if self.maximum_payment_amount:
- condition += (
- " and unallocated_amount <= {0}".format(flt(self.maximum_payment_amount))
- if get_payments
- else " and total_debit <= {0}".format(flt(self.maximum_payment_amount))
- )
+ conditions.append(je.total_debit.gte(self.minimum_payment_amount))
- return condition
+ if self.maximum_payment_amount:
+ conditions.append(je.total_debit.lte(self.maximumb_payment_amount))
+
+ return conditions
def reconcile_dr_cr_note(dr_cr_notes, company):