Point of sale will list only items that are in stock based on option "Display Items in stock" in POS Profle. (#14895)
* Update point_of_sale.py
* Update point_of_sale.py
Add custom field(Display Items In Stock) to POS Profile Doctype of type Check.
* New field " Display Items in Stock" added
* Update point_of_sale.py
* Update point_of_sale.py
ORM used instead of SQL
used Bin to find actual quantity balances.
* Update point_of_sale.py
* Update point_of_sale.py
* Update point_of_sale.py
* Update point_of_sale.py
* Update point_of_sale.py
diff --git a/erpnext/accounts/doctype/pos_profile/pos_profile.json b/erpnext/accounts/doctype/pos_profile/pos_profile.json
index fd77ff5..cc0b932 100644
--- a/erpnext/accounts/doctype/pos_profile/pos_profile.json
+++ b/erpnext/accounts/doctype/pos_profile/pos_profile.json
@@ -323,7 +323,37 @@
"set_only_once": 0,
"translatable": 0,
"unique": 0
- },
+ },
+ {
+ "allow_bulk_edit": 0,
+ "allow_on_submit": 0,
+ "bold": 0,
+ "collapsible": 0,
+ "columns": 0,
+ "fieldname": "display_items_in_stock",
+ "fieldtype": "Check",
+ "hidden": 0,
+ "ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
+ "in_filter": 0,
+ "in_global_search": 0,
+ "in_list_view": 0,
+ "in_standard_filter": 0,
+ "label": "Display Items In Stock",
+ "length": 0,
+ "no_copy": 0,
+ "permlevel": 0,
+ "precision": "",
+ "print_hide": 0,
+ "print_hide_if_no_value": 0,
+ "read_only": 0,
+ "remember_last_selected_value": 0,
+ "report_hide": 0,
+ "reqd": 0,
+ "search_index": 0,
+ "set_only_once": 0,
+ "unique": 0
+ },
{
"allow_bulk_edit": 0,
"allow_on_submit": 0,
@@ -1610,4 +1640,4 @@
"title_field": "pos_profile_name",
"track_changes": 0,
"track_seen": 0
-}
\ No newline at end of file
+}
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 c25bcd3..ed28204 100644
--- a/erpnext/selling/page/point_of_sale/point_of_sale.py
+++ b/erpnext/selling/page/point_of_sale/point_of_sale.py
@@ -14,8 +14,13 @@
serial_no = ""
batch_no = ""
barcode = ""
-
+ warehouse = ""
+ display_items_in_stock = 0
item_code = search_value
+
+ if pos_profile:
+ warehouse, display_items_in_stock = frappe.db.get_value('POS Profile', pos_profile, ['warehouse', 'display_items_in_stock'])
+
if not frappe.db.exists('Item Group', item_group):
item_group = get_root_of('Item Group')
@@ -42,28 +47,60 @@
lft, rgt = frappe.db.get_value('Item Group', item_group, ['lft', 'rgt'])
# locate function is used to sort by closest match from the beginning of the value
- res = frappe.db.sql("""select i.name as item_code, i.item_name, i.image as item_image,
- 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 ifnull(i.is_fixed_asset, 0) = 0
- and i.item_group in (select name from `tabItem Group` where lft >= {lft} and rgt <= {rgt})
- and ifnull(i.end_of_life, curdate()) >= curdate()
- and {condition}
- limit {start}, {page_length}""".format(start=start,
- page_length=page_length, lft=lft, rgt=rgt, condition=condition),
- {
- 'item_code': item_code,
- 'price_list': price_list
- } , as_dict=1)
- res = {
+
+ 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.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} limit {start}, {page_length}""".format(start=start,page_length=page_length,lft=lft, rgt=rgt, condition=condition),
+ {
+ 'item_code': item_code,
+ 'price_list': price_list
+ } , as_dict=1)
+
+ res = {
'items': res
- }
+ }
+
+ elif display_items_in_stock == 1:
+ query = """select i.name as item_code, i.item_name, i.image as item_image,
+ 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 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"""
+
+ 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} limit {start}, {page_length}""".format
+ (start=start,page_length=page_length,lft=lft, rgt=rgt, condition=condition),
+ {
+ 'item_code': item_code,
+ 'price_list': price_list,
+ 'warehouse': warehouse
+ } , as_dict=1)
+
+ res = {
+ 'items': res
+ }
if serial_no:
res.update({
@@ -114,4 +151,4 @@
return frappe.db.sql(""" select distinct name from `tabItem Group`
where {condition} and (name like %(txt)s) limit {start}, {page_len}"""
.format(condition = cond, start=start, page_len= page_len),
- {'txt': '%%%s%%' % txt})
\ No newline at end of file
+ {'txt': '%%%s%%' % txt})