def get_product_list(search=None, start=0, limit=12): # limit = 12 because we show 12 items in the grid view # base query 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, I.is_stock_item, case when (S.actual_qty - S.reserved_qty) > 0 then 1 else 0 end as in_stock, I.website_warehouse, I.has_batch_no 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)""" # search term condition if search: 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 I.weightage desc, in_stock desc, I.modified desc limit %s, %s""" % (cint(start), cint(limit)) data = frappe.db.sql(query, { "search": search, "today": nowdate() }, as_dict=1) for item in data: set_product_info_for_website(item) return [get_item_for_list_in_html(r) for r in data]
def get_product_list(search=None, start=0, limit=12): # limit = 12 because we show 12 items in the grid view # base query 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, I.is_stock_item, case when (S.actual_qty - S.reserved_qty) > 0 then 1 else 0 end as in_stock, I.website_warehouse, I.has_batch_no 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)""" # search term condition if search: 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 I.weightage desc, in_stock desc, I.modified desc limit %s, %s""" % ( cint(start), cint(limit)) data = frappe.db.sql(query, { "search": search, "today": nowdate() }, as_dict=1) for item in data: set_product_info_for_website(item) return [get_item_for_list_in_html(r) for r in data]
def get_product_list_for_group(product_group=None, start=0, limit=10, search=None): if product_group: item_group = frappe.get_cached_doc('Item Group', product_group) if item_group.is_group: # return child item groups if the type is of "Is Group" return get_child_groups_for_list_in_html(item_group, start, limit, search) child_groups = ", ".join([ '"' + frappe.db.escape(i[0]) + '"' for i in get_child_groups(product_group) ]) # base query 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, I.is_stock_item, case when (S.actual_qty - S.reserved_qty) > 0 then 1 else 0 end as in_stock, I.website_warehouse, I.has_batch_no 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 (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 I.weightage desc, in_stock desc, I.modified desc limit %s, %s""" % ( start, limit) data = frappe.db.sql(query, { "product_group": product_group, "search": search, "today": nowdate() }, as_dict=1) data = adjust_qty_for_expired_items(data) if cint(frappe.db.get_single_value("Shopping Cart Settings", "enabled")): for item in data: set_product_info_for_website(item) return [get_item_for_list_in_html(r) for r in data]
def get_product_list_for_group(product_group=None, start=0, limit=10, search=None): if product_group: item_group = frappe.get_cached_doc('Item Group', product_group) if item_group.is_group: # return child item groups if the type is of "Is Group" return get_child_groups_for_list_in_html(item_group, start, limit, search) child_groups = ", ".join(['"' + frappe.db.escape(i[0]) + '"' for i in get_child_groups(product_group)]) # base query 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, I.is_stock_item, case when (S.actual_qty - S.reserved_qty) > 0 then 1 else 0 end as in_stock, I.website_warehouse, I.has_batch_no 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 (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 I.weightage desc, in_stock desc, I.modified desc limit %s, %s""" % (start, limit) data = frappe.db.sql(query, {"product_group": product_group,"search": search, "today": nowdate()}, as_dict=1) data = adjust_qty_for_expired_items(data) for item in data: set_product_info_for_website(item) return [get_item_for_list_in_html(r) for r in data]