ParentWarehous.py

parent_warehouse=""
if filters.get("parent_warehouse"):
    escaped_input = frappe.db.escape(filters.get("parent_warehouse"))
    parent_warehouse = f" AND `tabWarehouse`.parent_warehouse = {escaped_input}"
    
item_group=""
if filters.get("item_group"):
    escaped_input = frappe.db.escape(filters.get("item_group"))
    item_group = f" AND `tabItem`.item_group = {escaped_input}"
    
item_code=""
if filters.get("item_code"):
    escaped_input = frappe.db.escape(filters.get("item_code"))
    item_code = f" AND `tabBin`.item_code = {escaped_input}"
    
check_zero=""
if filters.get("check_zero"):
    check_zero = f" AND `tabBin`.actual_qty != 0"
    
res = frappe.db.sql(f"""SELECT
`tabWarehouse`.parent_warehouse,
`tabBin`.item_code,
`tabItem`.item_name,
`tabItem`.item_group,
sum(`tabBin`.actual_qty) as actual_qty
FROM `tabBin`
INNER JOIN `tabWarehouse`
on `tabWarehouse`.name = `tabBin`.warehouse
INNER JOIN `tabItem`
ON `tabBin`.item_code = `tabItem`.item_code
where 1=1
{check_zero}
{parent_warehouse}
{item_code}
{item_group}
group by
`tabBin`.item_code
   """, as_dict=True)
result = res