[Website] Items list and stock (#11480)
* Add stock availability in items list and a parameter to set the number of products per page
* Substract reserved_qty from actual_qty for website stock
diff --git a/erpnext/portal/doctype/products_settings/products_settings.json b/erpnext/portal/doctype/products_settings/products_settings.json
index 90de96c..2d025cf 100644
--- a/erpnext/portal/doctype/products_settings/products_settings.json
+++ b/erpnext/portal/doctype/products_settings/products_settings.json
@@ -1,17 +1,23 @@
{
"allow_copy": 0,
+ "allow_guest_to_view": 0,
"allow_import": 0,
"allow_rename": 0,
+ "beta": 0,
"creation": "2016-04-22 09:11:55.272398",
"custom": 0,
"docstatus": 0,
"doctype": "DocType",
"document_type": "",
+ "editable_grid": 0,
+ "engine": "InnoDB",
"fields": [
{
+ "allow_bulk_edit": 0,
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
+ "columns": 0,
"description": "If checked, the Home page will be the default Item Group for the website",
"fieldname": "home_page_is_products",
"fieldtype": "Check",
@@ -19,7 +25,9 @@
"ignore_user_permissions": 0,
"ignore_xss_filter": 0,
"in_filter": 0,
+ "in_global_search": 0,
"in_list_view": 0,
+ "in_standard_filter": 0,
"label": "Home Page is Products",
"length": 0,
"no_copy": 0,
@@ -28,6 +36,7 @@
"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,
@@ -35,16 +44,20 @@
"unique": 0
},
{
+ "allow_bulk_edit": 0,
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
+ "columns": 0,
"fieldname": "products_as_list",
"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": "Show Products as a List",
"length": 0,
"no_copy": 0,
@@ -53,6 +66,39 @@
"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,
+ "bold": 0,
+ "collapsible": 0,
+ "columns": 0,
+ "default": "6",
+ "fieldname": "products_per_page",
+ "fieldtype": "Int",
+ "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": "Products per Page",
+ "length": 0,
+ "no_copy": 0,
+ "options": "",
+ "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,
@@ -60,16 +106,17 @@
"unique": 0
}
],
+ "has_web_view": 0,
"hide_heading": 0,
"hide_toolbar": 0,
"idx": 0,
+ "image_view": 0,
"in_create": 0,
- "in_dialog": 0,
"is_submittable": 0,
"issingle": 1,
"istable": 0,
"max_attachments": 0,
- "modified": "2016-04-22 09:11:59.537639",
+ "modified": "2017-11-07 19:34:33.055048",
"modified_by": "Administrator",
"module": "Portal",
"name": "Products Settings",
@@ -100,7 +147,9 @@
"quick_entry": 1,
"read_only": 0,
"read_only_onload": 0,
+ "show_name_in_global_search": 0,
"sort_field": "modified",
"sort_order": "DESC",
+ "track_changes": 1,
"track_seen": 0
}
\ No newline at end of file
diff --git a/erpnext/portal/doctype/products_settings/test_products_settings.js b/erpnext/portal/doctype/products_settings/test_products_settings.js
new file mode 100644
index 0000000..b7049b3
--- /dev/null
+++ b/erpnext/portal/doctype/products_settings/test_products_settings.js
@@ -0,0 +1,23 @@
+/* eslint-disable */
+// rename this file from _test_[name] to test_[name] to activate
+// and remove above this line
+
+QUnit.test("test: Products Settings", function (assert) {
+ let done = assert.async();
+
+ // number of asserts
+ assert.expect(1);
+
+ frappe.run_serially([
+ // insert a new Products Settings
+ () => frappe.tests.make('Products Settings', [
+ // values to be set
+ {key: 'value'}
+ ]),
+ () => {
+ assert.equal(cur_frm.doc.key, 'value');
+ },
+ () => done()
+ ]);
+
+});
diff --git a/erpnext/portal/doctype/products_settings/test_products_settings.py b/erpnext/portal/doctype/products_settings/test_products_settings.py
new file mode 100644
index 0000000..d04a009
--- /dev/null
+++ b/erpnext/portal/doctype/products_settings/test_products_settings.py
@@ -0,0 +1,10 @@
+# -*- coding: utf-8 -*-
+# Copyright (c) 2017, Frappe Technologies Pvt. Ltd. and Contributors
+# See license.txt
+from __future__ import unicode_literals
+
+import frappe
+import unittest
+
+class TestProductsSettings(unittest.TestCase):
+ pass
diff --git a/erpnext/setup/doctype/item_group/item_group.py b/erpnext/setup/doctype/item_group/item_group.py
index a4c377e..364f21a 100644
--- a/erpnext/setup/doctype/item_group/item_group.py
+++ b/erpnext/setup/doctype/item_group/item_group.py
@@ -57,7 +57,7 @@
def get_context(self, context):
context.show_search=True
- context.page_length = 6
+ context.page_length = cint(frappe.db.get_single_value('Products Settings', 'products_per_page')) or 6
context.search_link = '/product_search'
start = int(frappe.form_dict.start or 0)
@@ -81,24 +81,26 @@
child_groups = ", ".join(['"' + i[0] + '"' for i in get_child_groups(product_group)])
# base query
- query = """select name, item_name, item_code, route, image, website_image, thumbnail, item_group,
- description, web_long_description as website_description
- from `tabItem`
- where show_in_website = 1
- and disabled=0
- and (end_of_life is null or end_of_life='0000-00-00' or end_of_life > %(today)s)
- and (variant_of = '' or variant_of is null)
- and (item_group in ({child_groups})
- or name in (select parent from `tabWebsite Item Group` where item_group in ({child_groups})))
+ query = """select I.name, I.item_name, I.item_code, I.route, I.image, I.website_image, I.thumbnail, I.item_group,
+ I.description, I.web_long_description as website_description,
+ case when (S.actual_qty - S.reserved_qty) > 0 then 1 else 0 end as in_stock
+ from `tabItem` I
+ left join tabBin S on I.item_code = S.item_code and I.website_warehouse = S.warehouse
+ where I.show_in_website = 1
+ and I.disabled = 0
+ and (I.end_of_life is null or I.end_of_life='0000-00-00' or I.end_of_life > %(today)s)
+ and (I.variant_of = '' or I.variant_of is null)
+ and (I.item_group in ({child_groups})
+ or I.name in (select parent from `tabWebsite Item Group` where item_group in ({child_groups})))
""".format(child_groups=child_groups)
# search term condition
if search:
- query += """ and (web_long_description like %(search)s
- or item_name like %(search)s
- or name like %(search)s)"""
+ query += """ and (I.web_long_description like %(search)s
+ or I.item_name like %(search)s
+ or I.name like %(search)s)"""
search = "%" + cstr(search) + "%"
- query += """order by weightage desc, item_name, modified desc limit %s, %s""" % (start, limit)
+ query += """order by I.weightage desc, in_stock desc, I.item_name limit %s, %s""" % (start, limit)
data = frappe.db.sql(query, {"product_group": product_group,"search": search, "today": nowdate()}, as_dict=1)
diff --git a/erpnext/templates/includes/products_as_grid.html b/erpnext/templates/includes/products_as_grid.html
index 7a15c1f..b2437d3 100644
--- a/erpnext/templates/includes/products_as_grid.html
+++ b/erpnext/templates/includes/products_as_grid.html
@@ -3,8 +3,13 @@
<a class="product-link" href="{{ route|abs_url }}">
<div class="col-sm-4 col-xs-4 product-image-wrapper">
<div class="product-image-img">
- {{ product_image_square(thumbnail or website_image) }}
- <div class="product-text" itemprop="name">{{ item_name }}</div>
+ {{ product_image_square(thumbnail or website_image) }}
+ <div class="product-text" itemprop="name">{{ item_name }}</div>
+ {% if in_stock %}
+ <div style='color: green'> <i class='fa fa-check'></i> {{ _("In stock") }}</div>
+ {% else %}
+ <div style='color: red'> <i class='fa fa-close'></i> {{ _("Not in stock") }}</div>
+ {% endif %}
</div>
</div>
</a>
diff --git a/erpnext/templates/pages/product_search.py b/erpnext/templates/pages/product_search.py
index 49f321d..a872f19 100644
--- a/erpnext/templates/pages/product_search.py
+++ b/erpnext/templates/pages/product_search.py
@@ -17,23 +17,25 @@
# limit = 12 because we show 12 items in the grid view
# base query
- query = """select name, item_name, item_code, route, website_image, thumbnail, item_group,
- description, web_long_description as website_description
- from `tabItem`
- where (show_in_website = 1 or show_variant_in_website = 1)
- and disabled=0
- and (end_of_life is null or end_of_life='0000-00-00' or end_of_life > %(today)s)"""
+ query = """select I.name, I.item_name, I.item_code, I.route, I.website_image, I.thumbnail, I.item_group,
+ I.description, I.web_long_description as website_description,
+ case when (S.actual_qty - S.reserved_qty) > 0 then 1 else 0 end as in_stock
+ from `tabItem` I
+ left join tabBin S on I.item_code = S.item_code and I.website_warehouse = S.warehouse
+ where (I.show_in_website = 1 or I.show_variant_in_website = 1)
+ and I.disabled = 0
+ and (I.end_of_life is null or I.end_of_life='0000-00-00' or I.end_of_life > %(today)s)"""
# search term condition
if search:
- query += """ and (web_long_description like %(search)s
- or description like %(search)s
- or item_name like %(search)s
- or name like %(search)s)"""
+ query += """ and (I.web_long_description like %(search)s
+ or I.description like %(search)s
+ or I.item_name like %(search)s
+ or I.name like %(search)s)"""
search = "%" + cstr(search) + "%"
# order by
- query += """ order by weightage desc, idx desc, modified desc limit %s, %s""" % (cint(start), cint(limit))
+ query += """ order by I.weightage desc, in_stock desc, I.item_name limit %s, %s""" % (cint(start), cint(limit))
data = frappe.db.sql(query, {
"search": search,
diff --git a/erpnext/utilities/product.py b/erpnext/utilities/product.py
index 1ad8b6e..10366be 100644
--- a/erpnext/utilities/product.py
+++ b/erpnext/utilities/product.py
@@ -16,7 +16,7 @@
warehouse = frappe.db.get_value("Item", template_item_code, item_warehouse_field)
if warehouse:
- stock_qty = frappe.db.sql("""select actual_qty from tabBin where
+ stock_qty = frappe.db.sql("""select GREATEST(actual_qty - reserved_qty, 0) from tabBin where
item_code=%s and warehouse=%s""", (item_code, warehouse))
if stock_qty:
in_stock = stock_qty[0][0] > 0 and 1 or 0