fix: set customer and supplier details using sql (#21846)
* fix: set customer and supplier details using sql
instead of slowing down the query with get_doc and save()
we can just use sql to update the required values for
customer and supplier
Signed-off-by: Chinmay D. Pai <chinmaydpai@gmail.com>
* chore: remove extra quote
Co-authored-by: Himanshu <himanshuwarekar@yahoo.com>
* fix: update sql query to include tabPrice List
Signed-off-by: Chinmay D. Pai <chinmaydpai@gmail.com>
Co-authored-by: Himanshu <himanshuwarekar@yahoo.com>
diff --git a/erpnext/patches/v12_0/unset_customer_supplier_based_on_type_of_item_price.py b/erpnext/patches/v12_0/unset_customer_supplier_based_on_type_of_item_price.py
index 60aec05..b8efb21 100644
--- a/erpnext/patches/v12_0/unset_customer_supplier_based_on_type_of_item_price.py
+++ b/erpnext/patches/v12_0/unset_customer_supplier_based_on_type_of_item_price.py
@@ -1,15 +1,29 @@
from __future__ import unicode_literals
import frappe
+
def execute():
- invalid_selling_item_price = frappe.db.sql(
- """SELECT name FROM `tabItem Price` WHERE selling = 1 and buying = 0 and (supplier IS NOT NULL or supplier = '')"""
- )
- invalid_buying_item_price = frappe.db.sql(
- """SELECT name FROM `tabItem Price` WHERE selling = 0 and buying = 1 and (customer IS NOT NULL or customer = '')"""
- )
- docs_to_modify = invalid_buying_item_price + invalid_selling_item_price
- for d in docs_to_modify:
- # saving the doc will auto reset invalid customer/supplier field
- doc = frappe.get_doc("Item Price", d[0])
- doc.save()
\ No newline at end of file
+ """
+ set proper customer and supplier details for item price
+ based on selling and buying values
+ """
+
+ # update for selling
+ frappe.db.sql(
+ """UPDATE `tabItem Price` ip, `tabPrice List` pl
+ SET ip.`reference` = ip.`customer`, ip.`supplier` = NULL
+ WHERE ip.`selling` = 1
+ AND ip.`buying` = 0
+ AND (ip.`supplier` IS NOT NULL OR ip.`supplier` = '')
+ AND ip.`price_list` = pl.`name`
+ AND pl.`enabled` = 1""")
+
+ # update for buying
+ frappe.db.sql(
+ """UPDATE `tabItem Price` ip, `tabPrice List` pl
+ SET ip.`reference` = ip.`supplier`, ip.`customer` = NULL
+ WHERE ip.`selling` = 0
+ AND ip.`buying` = 1
+ AND (ip.`customer` IS NOT NULL OR ip.`customer` = '')
+ AND ip.`price_list` = pl.`name`
+ AND pl.`enabled` = 1""")