How is this scrip report possible? If the Stock Entry Type is “Repack” and in the Target Warehouse column in the Item table, if any warehouse type is Spoilage, then I want all the quantities of the Source Warehouse. If the column of Target Warehouse is not of type Spoilage, then the quantity of Source Warehouse should be taken as 0.
def get_spoilage_stock(item_code, warehouse, posting_date):
spoilage_st = frappe.db.sql("""
SELECT COALESCE(SUM(item.qty), 0)
FROM `tabStock Entry` AS se
JOIN `tabStock Entry Detail` AS item ON se.name = item.parent
JOIN `tabWarehouse` AS t_wh ON item.t_warehouse = t_wh.name
-- JOIN `tabWarehouse` AS s_wh ON item.s_warehouse = s_wh.name
WHERE item.item_code = %s
AND t_wh.warehouse_type = 'Spoilage'
AND se.stock_entry_type = 'Repack'
AND se.posting_date = %s
""", (item_code, warehouse, posting_date))
# Extract the first element of each tuple and then sum
return sum(entry[0] for entry in spoilage_st) or 0.0
Thank You!