def get_item_group_condition(pos_profile):
cond = "and 1=1"
item_groups = get_item_groups(pos_profile)
if item_groups:
cond = "and item_group in (%s)" % (", ".join(["%s"] * len(item_groups)))
return cond % tuple(item_groups)
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)))
return cond % tuple(item_groups)
def _get_conditions(args_dict):
args = frappe._dict(args_dict)
join_clauses = compose(lambda x: " AND ".join(x), concatv)
lft, rgt = frappe.db.get_value("Item Group", args.item_group,
["lft", "rgt"])
warehouse, display_items_in_stock = (frappe.db.get_value(
"POS Profile", args.pos_profile,
["warehouse", "display_items_in_stock"]) if args.pos_profile else
("", 0))
profile_item_groups = get_item_groups(args.pos_profile)
def make_stock_clause():
if display_items_in_stock:
sub_query = ("""
SELECT
item_code,
actual_qty
FROM `tabBin`
WHERE
warehouse = %(warehouse)s AND
actual_qty > 0
""" if warehouse else """
SELECT
item_code,
SUM(actual_qty) AS actual_qty
FROM `tabBin`
GROUP BY item_code
""")
return """
INNER JOIN ({sub_query}) AS b ON
b.item_code = i.name AND
b.actual_qty > 0
""".format(sub_query=sub_query)
return ""
clauses = join_clauses(
[
"i.disabled = 0",
"i.has_variants = 0",
"i.is_sales_item = 1",
"ig.lft >= {lft} AND ig.rgt <= {rgt}".format(lft=lft, rgt=rgt),
],
["i.name = %(item_code)s"] if args.item_code else [],
["(i.name LIKE %(free_text)s OR i.item_name LIKE %(free_text)s)"]
if not args.item_code and args.search_value else [],
["i.item_group IN %(profile_item_groups)s"]
if profile_item_groups else [],
)
values = merge(
args,
{
"warehouse": warehouse,
"profile_item_groups": profile_item_groups,
"free_text": "%{}%".format(args.search_value),
},
)
return {"clauses": clauses, "stock_clause": make_stock_clause()}, values
def item_group_query(doctype, txt, searchfield, start, page_len, filters):
item_groups = []
cond = "1=1"
pos_profile= filters.get('pos_profile')
if pos_profile:
item_groups = get_item_groups(pos_profile)
if item_groups:
cond = "name in (%s)"%(', '.join(['%s']*len(item_groups)))
cond = cond % tuple(item_groups)
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})
def item_group_query(doctype, txt, searchfield, start, page_len, filters):
item_groups = []
cond = "1=1"
pos_profile= filters.get('pos_profile')
if pos_profile:
item_groups = get_item_groups(pos_profile)
if item_groups:
cond = "name in (%s)"%(', '.join(['%s']*len(item_groups)))
cond = cond % tuple(item_groups)
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})
