Merge pull request #17213 from rohitwaghchaure/optimized_code_to_get_items_for_pos
fix: POS takes lot of time to load the items data
diff --git a/erpnext/selling/page/point_of_sale/point_of_sale.py b/erpnext/selling/page/point_of_sale/point_of_sale.py
index cf2cd6f..a9d2be5 100644
--- a/erpnext/selling/page/point_of_sale/point_of_sale.py
+++ b/erpnext/selling/page/point_of_sale/point_of_sale.py
@@ -38,61 +38,60 @@
# locate function is used to sort by closest match from the beginning of the value
- if display_items_in_stock == 0:
- res = frappe.db.sql("""select i.name as item_code, i.item_name, i.image as item_image, i.idx as idx,
- i.is_stock_item, item_det.price_list_rate, item_det.currency
- from `tabItem` i LEFT JOIN
- (select item_code, price_list_rate, currency from
- `tabItem Price` where price_list=%(price_list)s) item_det
- ON
- (item_det.item_code=i.name or item_det.item_code=i.variant_of)
- where
- i.disabled = 0 and i.has_variants = 0 and i.is_sales_item = 1
- and i.item_group in (select name from `tabItem Group` where lft >= {lft} and rgt <= {rgt})
- and {condition} order by idx desc limit {start}, {page_length}""".format(
- start=start,
- page_length=page_length,
- lft=lft,
- rgt=rgt,
- condition=condition
- ), {
- 'price_list': price_list
- }, as_dict=1)
+ result = []
- res = {
- 'items': res
- }
+ items_data = frappe.db.sql(""" SELECT name as item_code,
+ item_name, image as item_image, idx as idx,is_stock_item
+ FROM
+ `tabItem`
+ WHERE
+ disabled = 0 and has_variants = 0 and is_sales_item = 1
+ and item_group in (select name from `tabItem Group` where lft >= {lft} and rgt <= {rgt})
+ and {condition} order by idx desc limit {start}, {page_length}"""
+ .format(
+ start=start, page_length=page_length,
+ lft=lft, rgt=rgt,
+ condition=condition
+ ), as_dict=1)
- elif display_items_in_stock == 1:
- query = """select i.name as item_code, i.item_name, i.image as item_image, i.idx as idx,
- i.is_stock_item, item_det.price_list_rate, item_det.currency
- from `tabItem` i LEFT JOIN
- (select item_code, price_list_rate, currency from
- `tabItem Price` where price_list=%(price_list)s) item_det
- ON
- (item_det.item_code=i.name or item_det.item_code=i.variant_of) INNER JOIN"""
+ if items_data:
+ items = [d.item_code for d in items_data]
+ item_prices_data = frappe.get_all("Item Price",
+ fields = ["item_code", "price_list_rate", "currency"],
+ filters = {'price_list': price_list, 'item_code': ['in', items]})
- if warehouse is not None:
- query = query + """ (select item_code,actual_qty from `tabBin` where warehouse=%(warehouse)s and actual_qty > 0 group by item_code) item_se"""
- else:
- query = query + """ (select item_code,sum(actual_qty) as actual_qty from `tabBin` group by item_code) item_se"""
+ item_prices, bin_data = {}, {}
+ for d in item_prices_data:
+ item_prices[d.item_code] = d
- res = frappe.db.sql(query + """
- ON
- ((item_se.item_code=i.name or item_det.item_code=i.variant_of) and item_se.actual_qty>0)
- where
- i.disabled = 0 and i.has_variants = 0 and i.is_sales_item = 1
- and i.item_group in (select name from `tabItem Group` where lft >= {lft} and rgt <= {rgt})
- and {condition} order by idx desc limit {start}, {page_length} """.format
- (start=start,page_length=page_length,lft=lft, rgt=rgt, condition=condition),
- {
- 'price_list': price_list,
- 'warehouse': warehouse
- } , as_dict=1)
- res = {
- 'items': res
- }
+ if display_items_in_stock:
+ filters = {'actual_qty': [">", 0], 'item_code': ['in', items]}
+
+ if warehouse:
+ filters['warehouse'] = warehouse
+
+ bin_data = frappe._dict(
+ frappe.get_all("Bin", fields = ["item_code", "sum(actual_qty) as actual_qty"],
+ filters = filters, group_by = "item_code")
+ )
+
+ for item in items_data:
+ row = {}
+
+ row.update(item)
+ item_price = item_prices.get(item.item_code) or {}
+ row.update({
+ 'price_list_rate': item_price.get('price_list_rate'),
+ 'currency': item_price.get('currency'),
+ 'actual_qty': bin_data.get('actual_qty')
+ })
+
+ result.append(row)
+
+ res = {
+ 'items': result
+ }
if serial_no:
res.update({
@@ -132,16 +131,16 @@
def get_conditions(item_code, serial_no, batch_no, barcode):
if serial_no or batch_no or barcode:
- return "i.name = {0}".format(frappe.db.escape(item_code))
+ return "name = {0}".format(frappe.db.escape(item_code))
- return """(i.name like {item_code}
- or i.item_name like {item_code})""".format(item_code = frappe.db.escape('%' + item_code + '%'))
+ return """(name like {item_code}
+ or item_name like {item_code})""".format(item_code = frappe.db.escape('%' + item_code + '%'))
def get_item_group_condition(pos_profile):
cond = "and 1=1"
item_groups = get_item_groups(pos_profile)
if item_groups:
- cond = "and i.item_group in (%s)"%(', '.join(['%s']*len(item_groups)))
+ cond = "and item_group in (%s)"%(', '.join(['%s']*len(item_groups)))
return cond % tuple(item_groups)