Merge branch 'develop' into FIX-ISS-22-23-05722
diff --git a/erpnext/manufacturing/report/bom_stock_report/bom_stock_report.py b/erpnext/manufacturing/report/bom_stock_report/bom_stock_report.py
index cdf1541..3573a3a 100644
--- a/erpnext/manufacturing/report/bom_stock_report/bom_stock_report.py
+++ b/erpnext/manufacturing/report/bom_stock_report/bom_stock_report.py
@@ -4,7 +4,8 @@
import frappe
from frappe import _
-from frappe.query_builder.functions import Sum
+from frappe.query_builder.functions import Floor, Sum
+from frappe.utils import cint
from pypika.terms import ExistsCriterion
@@ -34,57 +35,55 @@
def get_bom_stock(filters):
- qty_to_produce = filters.get("qty_to_produce") or 1
- if int(qty_to_produce) < 0:
- frappe.throw(_("Quantity to Produce can not be less than Zero"))
+ qty_to_produce = filters.get("qty_to_produce")
+ if cint(qty_to_produce) <= 0:
+ frappe.throw(_("Quantity to Produce should be greater than zero."))
if filters.get("show_exploded_view"):
bom_item_table = "BOM Explosion Item"
else:
bom_item_table = "BOM Item"
- bin = frappe.qb.DocType("Bin")
- bom = frappe.qb.DocType("BOM")
- bom_item = frappe.qb.DocType(bom_item_table)
-
- query = (
- frappe.qb.from_(bom)
- .inner_join(bom_item)
- .on(bom.name == bom_item.parent)
- .left_join(bin)
- .on(bom_item.item_code == bin.item_code)
- .select(
- bom_item.item_code,
- bom_item.description,
- bom_item.stock_qty,
- bom_item.stock_uom,
- (bom_item.stock_qty / bom.quantity) * qty_to_produce,
- Sum(bin.actual_qty),
- Sum(bin.actual_qty) / (bom_item.stock_qty / bom.quantity),
- )
- .where((bom_item.parent == filters.get("bom")) & (bom_item.parenttype == "BOM"))
- .groupby(bom_item.item_code)
+ warehouse_details = frappe.db.get_value(
+ "Warehouse", filters.get("warehouse"), ["lft", "rgt"], as_dict=1
)
- if filters.get("warehouse"):
- warehouse_details = frappe.db.get_value(
- "Warehouse", filters.get("warehouse"), ["lft", "rgt"], as_dict=1
- )
+ BOM = frappe.qb.DocType("BOM")
+ BOM_ITEM = frappe.qb.DocType(bom_item_table)
+ BIN = frappe.qb.DocType("Bin")
+ WH = frappe.qb.DocType("Warehouse")
+ CONDITIONS = ()
- if warehouse_details:
- wh = frappe.qb.DocType("Warehouse")
- query = query.where(
- ExistsCriterion(
- frappe.qb.from_(wh)
- .select(wh.name)
- .where(
- (wh.lft >= warehouse_details.lft)
- & (wh.rgt <= warehouse_details.rgt)
- & (bin.warehouse == wh.name)
- )
- )
+ if warehouse_details:
+ CONDITIONS = ExistsCriterion(
+ frappe.qb.from_(WH)
+ .select(WH.name)
+ .where(
+ (WH.lft >= warehouse_details.lft)
+ & (WH.rgt <= warehouse_details.rgt)
+ & (BIN.warehouse == WH.name)
)
- else:
- query = query.where(bin.warehouse == filters.get("warehouse"))
+ )
+ else:
+ CONDITIONS = BIN.warehouse == filters.get("warehouse")
- return query.run()
+ QUERY = (
+ frappe.qb.from_(BOM)
+ .inner_join(BOM_ITEM)
+ .on(BOM.name == BOM_ITEM.parent)
+ .left_join(BIN)
+ .on((BOM_ITEM.item_code == BIN.item_code) & (CONDITIONS))
+ .select(
+ BOM_ITEM.item_code,
+ BOM_ITEM.description,
+ BOM_ITEM.stock_qty,
+ BOM_ITEM.stock_uom,
+ BOM_ITEM.stock_qty * qty_to_produce / BOM.quantity,
+ Sum(BIN.actual_qty).as_("actual_qty"),
+ Sum(Floor(BIN.actual_qty / (BOM_ITEM.stock_qty * qty_to_produce / BOM.quantity))),
+ )
+ .where((BOM_ITEM.parent == filters.get("bom")) & (BOM_ITEM.parenttype == "BOM"))
+ .groupby(BOM_ITEM.item_code)
+ )
+
+ return QUERY.run()
diff --git a/erpnext/manufacturing/report/bom_stock_report/test_bom_stock_report.py b/erpnext/manufacturing/report/bom_stock_report/test_bom_stock_report.py
new file mode 100644
index 0000000..1c56ebe
--- /dev/null
+++ b/erpnext/manufacturing/report/bom_stock_report/test_bom_stock_report.py
@@ -0,0 +1,108 @@
+# Copyright (c) 2022, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+
+import frappe
+from frappe.exceptions import ValidationError
+from frappe.tests.utils import FrappeTestCase
+from frappe.utils import floor
+
+from erpnext.manufacturing.doctype.production_plan.test_production_plan import make_bom
+from erpnext.manufacturing.report.bom_stock_report.bom_stock_report import (
+ get_bom_stock as bom_stock_report,
+)
+from erpnext.stock.doctype.item.test_item import make_item
+from erpnext.stock.doctype.stock_entry.test_stock_entry import make_stock_entry
+
+
+class TestBomStockReport(FrappeTestCase):
+ def setUp(self):
+ self.warehouse = "_Test Warehouse - _TC"
+ self.fg_item, self.rm_items = create_items()
+ make_stock_entry(target=self.warehouse, item_code=self.rm_items[0], qty=20, basic_rate=100)
+ make_stock_entry(target=self.warehouse, item_code=self.rm_items[1], qty=40, basic_rate=200)
+ self.bom = make_bom(item=self.fg_item, quantity=1, raw_materials=self.rm_items, rm_qty=10)
+
+ def test_bom_stock_report(self):
+ # Test 1: When `qty_to_produce` is 0.
+ filters = frappe._dict(
+ {
+ "bom": self.bom.name,
+ "warehouse": "Stores - _TC",
+ "qty_to_produce": 0,
+ }
+ )
+ self.assertRaises(ValidationError, bom_stock_report, filters)
+
+ # Test 2: When stock is not available.
+ data = bom_stock_report(
+ frappe._dict(
+ {
+ "bom": self.bom.name,
+ "warehouse": "Stores - _TC",
+ "qty_to_produce": 1,
+ }
+ )
+ )
+ expected_data = get_expected_data(self.bom, "Stores - _TC", 1)
+ self.assertSetEqual(set(tuple(x) for x in data), set(tuple(x) for x in expected_data))
+
+ # Test 3: When stock is available.
+ data = bom_stock_report(
+ frappe._dict(
+ {
+ "bom": self.bom.name,
+ "warehouse": self.warehouse,
+ "qty_to_produce": 1,
+ }
+ )
+ )
+ expected_data = get_expected_data(self.bom, self.warehouse, 1)
+ self.assertSetEqual(set(tuple(x) for x in data), set(tuple(x) for x in expected_data))
+
+
+def create_items():
+ fg_item = make_item(properties={"is_stock_item": 1}).name
+ rm_item1 = make_item(
+ properties={
+ "is_stock_item": 1,
+ "standard_rate": 100,
+ "opening_stock": 100,
+ "last_purchase_rate": 100,
+ }
+ ).name
+ rm_item2 = make_item(
+ properties={
+ "is_stock_item": 1,
+ "standard_rate": 200,
+ "opening_stock": 200,
+ "last_purchase_rate": 200,
+ }
+ ).name
+
+ return fg_item, [rm_item1, rm_item2]
+
+
+def get_expected_data(bom, warehouse, qty_to_produce, show_exploded_view=False):
+ expected_data = []
+
+ for item in bom.get("exploded_items") if show_exploded_view else bom.get("items"):
+ in_stock_qty = frappe.get_cached_value(
+ "Bin", {"item_code": item.item_code, "warehouse": warehouse}, "actual_qty"
+ )
+
+ expected_data.append(
+ [
+ item.item_code,
+ item.description,
+ item.stock_qty,
+ item.stock_uom,
+ item.stock_qty * qty_to_produce / bom.quantity,
+ in_stock_qty,
+ floor(in_stock_qty / (item.stock_qty * qty_to_produce / bom.quantity))
+ if in_stock_qty
+ else None,
+ ]
+ )
+
+ return expected_data