fix: item price query for postgres
postgres doesn't like bad type comparisons and doesn't have `isnull`
funciton
diff --git a/erpnext/stock/doctype/item_price/item_price.py b/erpnext/stock/doctype/item_price/item_price.py
index ab797cd..bcd31ad 100644
--- a/erpnext/stock/doctype/item_price/item_price.py
+++ b/erpnext/stock/doctype/item_price/item_price.py
@@ -5,6 +5,8 @@
import frappe
from frappe import _
from frappe.model.document import Document
+from frappe.query_builder import Criterion
+from frappe.query_builder.functions import Cast_
from frappe.utils import getdate
@@ -48,35 +50,57 @@
)
def check_duplicates(self):
- conditions = (
- """where item_code = %(item_code)s and price_list = %(price_list)s and name != %(name)s"""
- )
- for field in [
+ item_price = frappe.qb.DocType("Item Price")
+
+ query = (
+ frappe.qb.from_(item_price)
+ .select(item_price.price_list_rate)
+ .where(
+ (item_price.item_code == self.item_code)
+ & (item_price.price_list == self.price_list)
+ & (item_price.name != self.name)
+ )
+ )
+ data_fields = (
"uom",
"valid_from",
"valid_upto",
- "packing_unit",
"customer",
"supplier",
"batch_no",
- ]:
- if self.get(field):
- conditions += " and {0} = %({0})s ".format(field)
- else:
- conditions += "and ({0} is null or {0} = '')".format(field)
-
- price_list_rate = frappe.db.sql(
- """
- select price_list_rate
- from `tabItem Price`
- {conditions}
- """.format(
- conditions=conditions
- ),
- self.as_dict(),
)
+ number_fields = ["packing_unit"]
+
+ for field in data_fields:
+ if self.get(field):
+ query = query.where(item_price[field] == self.get(field))
+ else:
+ query = query.where(
+ Criterion.any(
+ [
+ item_price[field].isnull(),
+ Cast_(item_price[field], "varchar") == "",
+ ]
+ )
+ )
+
+ for field in number_fields:
+ if self.get(field):
+ query = query.where(item_price[field] == self.get(field))
+ else:
+ query = query.where(
+ Criterion.any(
+ [
+ item_price[field].isnull(),
+ item_price[field] == 0,
+ ]
+ )
+ )
+
+ price_list_rate = query.run(as_dict=True)
+
if price_list_rate:
frappe.throw(
_(