refactor: pass dimension filters to query
diff --git a/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py
index 092cf45..cb7d5ea 100644
--- a/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py
+++ b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py
@@ -173,6 +173,15 @@
if self.payment_name:
condition.update({"name": self.payment_name})
+ # pass dynamic dimension filter values to query builder
+ dimensions = {}
+ dimensions_and_defaults = get_dimensions()
+ for x in dimensions_and_defaults[0]:
+ dimension = x.fieldname
+ if self.get(dimension):
+ dimensions.update({dimension: self.get(dimension)})
+ condition.update({"accounting_dimensions": dimensions})
+
payment_entries = get_advance_payment_entries_for_regional(
self.party_type,
self.party,
diff --git a/erpnext/controllers/accounts_controller.py b/erpnext/controllers/accounts_controller.py
index aef3d31..31ff799 100644
--- a/erpnext/controllers/accounts_controller.py
+++ b/erpnext/controllers/accounts_controller.py
@@ -7,6 +7,7 @@
import frappe
from frappe import _, bold, qb, throw
from frappe.model.workflow import get_workflow_name, is_transition_condition_satisfied
+from frappe.query_builder import Criterion
from frappe.query_builder.custom import ConstantColumn
from frappe.query_builder.functions import Abs, Sum
from frappe.utils import (
@@ -2695,47 +2696,37 @@
q = q.select((payment_entry.target_exchange_rate).as_("exchange_rate"))
if condition:
- if condition.get("name", None):
- q = q.where(payment_entry.name.like(f"%{condition.get('name')}%"))
+ # conditions should be built as an array and passed as Criterion
+ common_filter_conditions = []
- q = q.where(payment_entry.company == condition["company"])
- q = (
- q.where(payment_entry.posting_date >= condition["from_payment_date"])
- if condition.get("from_payment_date")
- else q
- )
- q = (
- q.where(payment_entry.posting_date <= condition["to_payment_date"])
- if condition.get("to_payment_date")
- else q
- )
+ common_filter_conditions.append(payment_entry.company == condition["company"])
+ if condition.get("name", None):
+ common_filter_conditions.append(payment_entry.name.like(f"%{condition.get('name')}%"))
+
+ if condition.get("from_payment_date"):
+ common_filter_conditions.append(payment_entry.posting_date.gte(condition["from_payment_date"]))
+
+ if condition.get("to_payment_date"):
+ common_filter_conditions.append(payment_entry.posting_date.lte(condition["to_payment_date"]))
+
if condition.get("get_payments") == True:
- q = (
- q.where(payment_entry.cost_center == condition["cost_center"])
- if condition.get("cost_center")
- else q
- )
- q = (
- q.where(payment_entry.unallocated_amount >= condition["minimum_payment_amount"])
- if condition.get("minimum_payment_amount")
- else q
- )
- q = (
- q.where(payment_entry.unallocated_amount <= condition["maximum_payment_amount"])
- if condition.get("maximum_payment_amount")
- else q
- )
- else:
- q = (
- q.where(payment_entry.total_debit >= condition["minimum_payment_amount"])
- if condition.get("minimum_payment_amount")
- else q
- )
- q = (
- q.where(payment_entry.total_debit <= condition["maximum_payment_amount"])
- if condition.get("maximum_payment_amount")
- else q
- )
+ if condition.get("cost_center"):
+ common_filter_conditions.append(payment_entry.cost_center == condition["cost_center"])
+
+ if condition.get("accounting_dimensions"):
+ for field, val in condition.get("accounting_dimensions").items():
+ common_filter_conditions.append(payment_entry[field] == val)
+
+ if condition.get("minimum_payment_amount"):
+ common_filter_conditions.append(
+ payment_entry.unallocated_amount.gte(condition["minimum_payment_amount"])
+ )
+
+ if condition.get("maximum_payment_amount"):
+ common_filter_conditions.append(
+ payment_entry.unallocated_amount.lte(condition["maximum_payment_amount"])
+ )
+ q = q.where(Criterion.all(common_filter_conditions))
q = q.orderby(payment_entry.posting_date)
q = q.limit(limit) if limit else q