Merge pull request #32150 from s-aga-r/refactor/report/bom-stock-calculated
refactor: BOM Stock Calculated report, fix required-qty
diff --git a/erpnext/manufacturing/report/bom_stock_calculated/bom_stock_calculated.js b/erpnext/manufacturing/report/bom_stock_calculated/bom_stock_calculated.js
index 0d5bfcb..a0fd91e 100644
--- a/erpnext/manufacturing/report/bom_stock_calculated/bom_stock_calculated.js
+++ b/erpnext/manufacturing/report/bom_stock_calculated/bom_stock_calculated.js
@@ -11,17 +11,24 @@
"options": "BOM",
"reqd": 1
},
- {
- "fieldname": "qty_to_make",
- "label": __("Quantity to Make"),
- "fieldtype": "Int",
- "default": "1"
- },
-
- {
+ {
+ "fieldname": "warehouse",
+ "label": __("Warehouse"),
+ "fieldtype": "Link",
+ "options": "Warehouse",
+ },
+ {
+ "fieldname": "qty_to_make",
+ "label": __("Quantity to Make"),
+ "fieldtype": "Float",
+ "default": "1.0",
+ "reqd": 1
+ },
+ {
"fieldname": "show_exploded_view",
"label": __("Show exploded view"),
- "fieldtype": "Check"
+ "fieldtype": "Check",
+ "default": false,
}
]
}
diff --git a/erpnext/manufacturing/report/bom_stock_calculated/bom_stock_calculated.py b/erpnext/manufacturing/report/bom_stock_calculated/bom_stock_calculated.py
index 933be3e..ec4b25c 100644
--- a/erpnext/manufacturing/report/bom_stock_calculated/bom_stock_calculated.py
+++ b/erpnext/manufacturing/report/bom_stock_calculated/bom_stock_calculated.py
@@ -4,29 +4,31 @@
import frappe
from frappe import _
+from frappe.query_builder.functions import IfNull, Sum
from frappe.utils.data import comma_and
+from pypika.terms import ExistsCriterion
def execute(filters=None):
- # if not filters: filters = {}
columns = get_columns()
- summ_data = []
+ data = []
- data = get_bom_stock(filters)
+ bom_data = get_bom_data(filters)
qty_to_make = filters.get("qty_to_make")
-
manufacture_details = get_manufacturer_records()
- for row in data:
- reqd_qty = qty_to_make * row.actual_qty
- last_pur_price = frappe.db.get_value("Item", row.item_code, "last_purchase_rate")
- summ_data.append(get_report_data(last_pur_price, reqd_qty, row, manufacture_details))
- return columns, summ_data
+ for row in bom_data:
+ required_qty = qty_to_make * row.qty_per_unit
+ last_purchase_rate = frappe.db.get_value("Item", row.item_code, "last_purchase_rate")
+
+ data.append(get_report_data(last_purchase_rate, required_qty, row, manufacture_details))
+
+ return columns, data
-def get_report_data(last_pur_price, reqd_qty, row, manufacture_details):
- to_build = row.to_build if row.to_build > 0 else 0
- diff_qty = to_build - reqd_qty
+def get_report_data(last_purchase_rate, required_qty, row, manufacture_details):
+ qty_per_unit = row.qty_per_unit if row.qty_per_unit > 0 else 0
+ difference_qty = row.actual_qty - required_qty
return [
row.item_code,
row.description,
@@ -34,85 +36,126 @@
comma_and(
manufacture_details.get(row.item_code, {}).get("manufacturer_part", []), add_quotes=False
),
+ qty_per_unit,
row.actual_qty,
- str(to_build),
- reqd_qty,
- diff_qty,
- last_pur_price,
+ required_qty,
+ difference_qty,
+ last_purchase_rate,
]
def get_columns():
- """return columns"""
- columns = [
- _("Item") + ":Link/Item:100",
- _("Description") + "::150",
- _("Manufacturer") + "::250",
- _("Manufacturer Part Number") + "::250",
- _("Qty") + ":Float:50",
- _("Stock Qty") + ":Float:100",
- _("Reqd Qty") + ":Float:100",
- _("Diff Qty") + ":Float:100",
- _("Last Purchase Price") + ":Float:100",
+ return [
+ {
+ "fieldname": "item",
+ "label": _("Item"),
+ "fieldtype": "Link",
+ "options": "Item",
+ "width": 120,
+ },
+ {
+ "fieldname": "description",
+ "label": _("Description"),
+ "fieldtype": "Data",
+ "width": 150,
+ },
+ {
+ "fieldname": "manufacturer",
+ "label": _("Manufacturer"),
+ "fieldtype": "Data",
+ "width": 120,
+ },
+ {
+ "fieldname": "manufacturer_part_number",
+ "label": _("Manufacturer Part Number"),
+ "fieldtype": "Data",
+ "width": 150,
+ },
+ {
+ "fieldname": "qty_per_unit",
+ "label": _("Qty Per Unit"),
+ "fieldtype": "Float",
+ "width": 110,
+ },
+ {
+ "fieldname": "available_qty",
+ "label": _("Available Qty"),
+ "fieldtype": "Float",
+ "width": 120,
+ },
+ {
+ "fieldname": "required_qty",
+ "label": _("Required Qty"),
+ "fieldtype": "Float",
+ "width": 120,
+ },
+ {
+ "fieldname": "difference_qty",
+ "label": _("Difference Qty"),
+ "fieldtype": "Float",
+ "width": 130,
+ },
+ {
+ "fieldname": "last_purchase_rate",
+ "label": _("Last Purchase Rate"),
+ "fieldtype": "Float",
+ "width": 160,
+ },
]
- return columns
-def get_bom_stock(filters):
- conditions = ""
- bom = filters.get("bom")
-
- table = "`tabBOM Item`"
- qty_field = "qty"
-
+def get_bom_data(filters):
if filters.get("show_exploded_view"):
- table = "`tabBOM Explosion Item`"
- qty_field = "stock_qty"
+ bom_item_table = "BOM Explosion Item"
+ else:
+ bom_item_table = "BOM Item"
+
+ bom_item = frappe.qb.DocType(bom_item_table)
+ bin = frappe.qb.DocType("Bin")
+
+ query = (
+ frappe.qb.from_(bom_item)
+ .left_join(bin)
+ .on(bom_item.item_code == bin.item_code)
+ .select(
+ bom_item.item_code,
+ bom_item.description,
+ bom_item.qty_consumed_per_unit.as_("qty_per_unit"),
+ IfNull(Sum(bin.actual_qty), 0).as_("actual_qty"),
+ )
+ .where((bom_item.parent == filters.get("bom")) & (bom_item.parenttype == "BOM"))
+ .groupby(bom_item.item_code)
+ )
if filters.get("warehouse"):
warehouse_details = frappe.db.get_value(
"Warehouse", filters.get("warehouse"), ["lft", "rgt"], as_dict=1
)
+
if warehouse_details:
- conditions += (
- " and exists (select name from `tabWarehouse` wh \
- where wh.lft >= %s and wh.rgt <= %s and ledger.warehouse = wh.name)"
- % (warehouse_details.lft, warehouse_details.rgt)
+ 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)
+ )
+ )
)
else:
- conditions += " and ledger.warehouse = %s" % frappe.db.escape(filters.get("warehouse"))
+ query = query.where(bin.warehouse == frappe.db.escape(filters.get("warehouse")))
- else:
- conditions += ""
-
- return frappe.db.sql(
- """
- SELECT
- bom_item.item_code,
- bom_item.description,
- bom_item.{qty_field},
- ifnull(sum(ledger.actual_qty), 0) as actual_qty,
- ifnull(sum(FLOOR(ledger.actual_qty / bom_item.{qty_field})), 0) as to_build
- FROM
- {table} AS bom_item
- LEFT JOIN `tabBin` AS ledger
- ON bom_item.item_code = ledger.item_code
- {conditions}
-
- WHERE
- bom_item.parent = '{bom}' and bom_item.parenttype='BOM'
-
- GROUP BY bom_item.item_code""".format(
- qty_field=qty_field, table=table, conditions=conditions, bom=bom
- ),
- as_dict=1,
- )
+ return query.run(as_dict=True)
def get_manufacturer_records():
details = frappe.get_all(
"Item Manufacturer", fields=["manufacturer", "manufacturer_part_no", "item_code"]
)
+
manufacture_details = frappe._dict()
for detail in details:
dic = manufacture_details.setdefault(detail.get("item_code"), {})
diff --git a/erpnext/manufacturing/report/bom_stock_calculated/test_bom_stock_calculated.py b/erpnext/manufacturing/report/bom_stock_calculated/test_bom_stock_calculated.py
new file mode 100644
index 0000000..8ad980f
--- /dev/null
+++ b/erpnext/manufacturing/report/bom_stock_calculated/test_bom_stock_calculated.py
@@ -0,0 +1,115 @@
+# Copyright (c) 2022, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+from frappe.tests.utils import FrappeTestCase
+
+from erpnext.manufacturing.doctype.production_plan.test_production_plan import make_bom
+from erpnext.manufacturing.report.bom_stock_calculated.bom_stock_calculated import (
+ execute as bom_stock_calculated_report,
+)
+from erpnext.stock.doctype.item.test_item import make_item
+
+
+class TestBOMStockCalculated(FrappeTestCase):
+ def setUp(self):
+ self.fg_item, self.rm_items = create_items()
+ self.boms = create_boms(self.fg_item, self.rm_items)
+
+ def test_bom_stock_calculated(self):
+ qty_to_make = 10
+
+ # Case 1: When Item(s) Qty and Stock Qty are equal.
+ data = bom_stock_calculated_report(
+ filters={
+ "qty_to_make": qty_to_make,
+ "bom": self.boms[0].name,
+ }
+ )[1]
+ expected_data = get_expected_data(self.boms[0], qty_to_make)
+ self.assertSetEqual(set(tuple(x) for x in data), set(tuple(x) for x in expected_data))
+
+ # Case 2: When Item(s) Qty and Stock Qty are different and BOM Qty is 1.
+ data = bom_stock_calculated_report(
+ filters={
+ "qty_to_make": qty_to_make,
+ "bom": self.boms[1].name,
+ }
+ )[1]
+ expected_data = get_expected_data(self.boms[1], qty_to_make)
+ self.assertSetEqual(set(tuple(x) for x in data), set(tuple(x) for x in expected_data))
+
+ # Case 3: When Item(s) Qty and Stock Qty are different and BOM Qty is greater than 1.
+ data = bom_stock_calculated_report(
+ filters={
+ "qty_to_make": qty_to_make,
+ "bom": self.boms[2].name,
+ }
+ )[1]
+ expected_data = get_expected_data(self.boms[2], qty_to_make)
+ 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 create_boms(fg_item, rm_items):
+ def update_bom_items(bom, uom, conversion_factor):
+ for item in bom.items:
+ item.uom = uom
+ item.conversion_factor = conversion_factor
+
+ return bom
+
+ bom1 = make_bom(item=fg_item, quantity=1, raw_materials=rm_items, rm_qty=10)
+
+ bom2 = make_bom(item=fg_item, quantity=1, raw_materials=rm_items, rm_qty=10, do_not_submit=True)
+ bom2 = update_bom_items(bom2, "Box", 10)
+ bom2.save()
+ bom2.submit()
+
+ bom3 = make_bom(item=fg_item, quantity=2, raw_materials=rm_items, rm_qty=10, do_not_submit=True)
+ bom3 = update_bom_items(bom3, "Box", 10)
+ bom3.save()
+ bom3.submit()
+
+ return [bom1, bom2, bom3]
+
+
+def get_expected_data(bom, qty_to_make):
+ expected_data = []
+
+ for idx in range(len(bom.items)):
+ expected_data.append(
+ [
+ bom.items[idx].item_code,
+ bom.items[idx].item_code,
+ "",
+ "",
+ float(bom.items[idx].stock_qty / bom.quantity),
+ float(100 * (idx + 1)),
+ float(qty_to_make * (bom.items[idx].stock_qty / bom.quantity)),
+ float((100 * (idx + 1)) - (qty_to_make * (bom.items[idx].stock_qty / bom.quantity))),
+ float(100 * (idx + 1)),
+ ]
+ )
+
+ return expected_data