Merge pull request #9442 from creamdory/develop
BOM Stock Report to allow Multi Level Warehouse Setup
diff --git a/erpnext/manufacturing/report/bom_stock_report/bom_stock_report.json b/erpnext/manufacturing/report/bom_stock_report/bom_stock_report.json
index f2090b7..c563b87 100644
--- a/erpnext/manufacturing/report/bom_stock_report/bom_stock_report.json
+++ b/erpnext/manufacturing/report/bom_stock_report/bom_stock_report.json
@@ -8,7 +8,7 @@
"idx": 0,
"is_standard": "Yes",
"letter_head": "",
- "modified": "2017-02-24 19:56:30.215599",
+ "modified": "2017-06-23 04:46:43.209008",
"modified_by": "Administrator",
"module": "Manufacturing",
"name": "BOM Stock Report",
@@ -16,7 +16,7 @@
"query": "SELECT \n\tbom_item.item_code as \"Item:Link/Item:200\",\n\tbom_item.description as \"Description:Data:300\",\n\tbom_item.qty as \"Required Qty:Float:100\",\n\tledger.actual_qty as \"In Stock Qty:Float:100\",\n\tFLOOR(ledger.actual_qty /bom_item.qty) as \"Enough Parts to Build:Int:100\"\nFROM\n\t`tabBOM Item` AS bom_item \n\tLEFT JOIN `tabBin` AS ledger\t\n\t\tON bom_item.item_code = ledger.item_code \n\t\tAND ledger.warehouse = %(warehouse)s\nWHERE\n\tbom_item.parent=%(bom)s\n\nGROUP BY bom_item.item_code",
"ref_doctype": "BOM",
"report_name": "BOM Stock Report",
- "report_type": "Query Report",
+ "report_type": "Script Report",
"roles": [
{
"role": "Manufacturing Manager"
diff --git a/erpnext/manufacturing/report/bom_stock_report/bom_stock_report.py b/erpnext/manufacturing/report/bom_stock_report/bom_stock_report.py
new file mode 100644
index 0000000..343cfe9
--- /dev/null
+++ b/erpnext/manufacturing/report/bom_stock_report/bom_stock_report.py
@@ -0,0 +1,57 @@
+# Copyright (c) 2013, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+from __future__ import unicode_literals
+import frappe
+from frappe import _
+
+def execute(filters=None):
+ if not filters: filters = {}
+ columns = get_columns()
+ data = get_bom_stock(filters)
+ return columns, data
+
+def get_columns():
+ """return columns"""
+ columns = [
+ _("Item") + ":Link/Item:150",
+ _("Description") + "::500",
+ _("Required Qty") + ":Float:100",
+ _("In Stock Qty") + ":Float:100",
+ _("Enough Parts to Build") + ":Float:200",
+ ]
+
+ return columns
+
+def get_bom_stock(filters):
+ conditions = ""
+ bom = filters.get("bom")
+
+ 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)
+ else:
+ conditions += " and ledger.warehouse = '%s'" % frappe.db.escape(filters.get("warehouse"))
+
+ else:
+ conditions += ""
+
+ return frappe.db.sql("""
+ SELECT
+ bom_item.item_code ,
+ bom_item.description ,
+ bom_item.qty,
+ sum(ledger.actual_qty) as actual_qty,
+ sum(FLOOR(ledger.actual_qty /bom_item.qty))as to_build
+ FROM
+ `tabBOM Item` AS bom_item
+ LEFT JOIN `tabBin` AS ledger
+ ON bom_item.item_code = ledger.item_code
+ %s
+ WHERE
+ bom_item.parent = '%s'
+
+ GROUP BY bom_item.item_code""" % (conditions, bom))