Merge pull request #7691 from bcornwellmott/stock_reports
Stock reports
diff --git a/erpnext/manufacturing/report/bom_stock_report/__init__.py b/erpnext/manufacturing/report/bom_stock_report/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/manufacturing/report/bom_stock_report/__init__.py
diff --git a/erpnext/manufacturing/report/bom_stock_report/bom_stock_report.js b/erpnext/manufacturing/report/bom_stock_report/bom_stock_report.js
new file mode 100644
index 0000000..8aed965
--- /dev/null
+++ b/erpnext/manufacturing/report/bom_stock_report/bom_stock_report.js
@@ -0,0 +1,17 @@
+frappe.query_reports["BOM Stock Report"] = {
+ "filters": [
+ {
+ "fieldname":"bom",
+ "label": __("BOM"),
+ "fieldtype": "Link",
+ "options": "BOM",
+ "reqd": 1
+ },{
+ "fieldname":"warehouse",
+ "label": __("Warehouse"),
+ "fieldtype": "Link",
+ "options": "Warehouse",
+ "reqd": 1
+ }
+ ]
+}
diff --git a/erpnext/manufacturing/report/bom_stock_report/bom_stock_report.json b/erpnext/manufacturing/report/bom_stock_report/bom_stock_report.json
new file mode 100644
index 0000000..64d0ebb
--- /dev/null
+++ b/erpnext/manufacturing/report/bom_stock_report/bom_stock_report.json
@@ -0,0 +1,20 @@
+{
+ "add_total_row": 0,
+ "apply_user_permissions": 1,
+ "creation": "2017-01-10 14:00:50.387244",
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "idx": 0,
+ "is_standard": "Yes",
+ "letter_head": "Standard",
+ "modified": "2017-02-09 23:19:32.050905",
+ "modified_by": "Administrator",
+ "module": "Manufacturing",
+ "name": "BOM Stock Report",
+ "owner": "Administrator",
+ "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"
+}
\ No newline at end of file
diff --git a/erpnext/manufacturing/report/production_order_stock_report/__init__.py b/erpnext/manufacturing/report/production_order_stock_report/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/manufacturing/report/production_order_stock_report/__init__.py
diff --git a/erpnext/manufacturing/report/production_order_stock_report/production_order_stock_report.js b/erpnext/manufacturing/report/production_order_stock_report/production_order_stock_report.js
new file mode 100644
index 0000000..917af5d
--- /dev/null
+++ b/erpnext/manufacturing/report/production_order_stock_report/production_order_stock_report.js
@@ -0,0 +1,14 @@
+// Copyright (c) 2016, Velometro Mobility Inc and contributors
+// For license information, please see license.txt
+
+frappe.query_reports["Production Order Stock Report"] = {
+ "filters": [
+ {
+ "fieldname":"warehouse",
+ "label": __("Warehouse"),
+ "fieldtype": "Link",
+ "options": "Warehouse"
+
+ }
+ ]
+}
diff --git a/erpnext/manufacturing/report/production_order_stock_report/production_order_stock_report.json b/erpnext/manufacturing/report/production_order_stock_report/production_order_stock_report.json
new file mode 100644
index 0000000..1b1357e
--- /dev/null
+++ b/erpnext/manufacturing/report/production_order_stock_report/production_order_stock_report.json
@@ -0,0 +1,19 @@
+{
+ "add_total_row": 0,
+ "apply_user_permissions": 1,
+ "creation": "2017-01-10 14:01:43.905861",
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "idx": 0,
+ "is_standard": "Yes",
+ "letter_head": "Standard",
+ "modified": "2017-01-10 14:01:43.905861",
+ "modified_by": "Administrator",
+ "module": "Manufacturing",
+ "name": "Production Order Stock Report",
+ "owner": "Administrator",
+ "ref_doctype": "Production Order",
+ "report_name": "Production Order Stock Report",
+ "report_type": "Script Report"
+}
\ No newline at end of file
diff --git a/erpnext/manufacturing/report/production_order_stock_report/production_order_stock_report.py b/erpnext/manufacturing/report/production_order_stock_report/production_order_stock_report.py
new file mode 100644
index 0000000..4bb00a2
--- /dev/null
+++ b/erpnext/manufacturing/report/production_order_stock_report/production_order_stock_report.py
@@ -0,0 +1,133 @@
+# Copyright (c) 2017, Velometro Mobility Inc and contributors
+# For license information, please see license.txt
+
+from __future__ import unicode_literals
+from frappe.utils import flt, cint
+import frappe
+
+def execute(filters=None):
+ prod_list = get_production_orders()
+ data = get_item_list(prod_list, filters)
+ columns = get_columns()
+ return columns, data
+
+def get_item_list(prod_list, filters):
+ out = []
+
+ low_price_data = []
+ low_supplier = []
+
+ #Add a row for each item/qty
+ for prod_order in prod_list:
+ bom = frappe.db.get_value("Production Order", prod_order.name, "bom_no")
+ warehouse = frappe.db.get_value("Production Order", prod_order.name, "source_warehouse")
+ desc = frappe.db.get_value("BOM", bom, "description")
+ qty = frappe.db.get_value("Production Order", prod_order.name, "qty")
+ produced_value = frappe.db.get_value("Production Order", prod_order.name, "produced_qty")
+ item_list = frappe.db.sql("""SELECT
+ bom_item.item_code as item_code,
+ ifnull(ledger.actual_qty/bom_item.qty,0) as build_qty
+ FROM
+ `tabBOM Item` AS bom_item
+ LEFT JOIN `tabBin` AS ledger
+ ON bom_item.item_code = ledger.item_code
+ AND ledger.warehouse = ifnull(%(warehouse)s,%(filterhouse)s)
+ WHERE
+ bom_item.parent = %(bom)s
+ GROUP BY
+ bom_item.item_code""", {"bom": bom, "warehouse": warehouse, "filterhouse": filters.warehouse}, as_dict=1)
+ stock_qty = 0
+ count = 0
+ buildable_qty = qty
+ for item in item_list:
+ count = count + 1
+ if item.build_qty >= (qty-produced_value):
+ stock_qty = stock_qty + 1
+ elif buildable_qty >= item.build_qty:
+ buildable_qty = item.build_qty
+
+
+ if count == stock_qty:
+ build = "Y"
+ else:
+ build = "N"
+
+ row = frappe._dict({
+ "production_order": prod_order.name,
+ "status": prod_order.status,
+ "req_items": cint(count),
+ "instock": stock_qty,
+ "description": desc,
+ "bom_no": bom,
+ "qty": qty,
+ "buildable_qty": buildable_qty,
+ "ready_to_build": build
+ })
+
+ out.append(row)
+
+ return out
+
+def get_production_orders():
+
+ out = frappe.get_all("Production Order", filters={"docstatus": 1, "status": ( "!=","Completed")}, fields=["name","status"], order_by='name')
+ return out
+
+def get_columns():
+ columns = [{
+ "fieldname": "production_order",
+ "label": "Production Order",
+ "fieldtype": "Link",
+ "options": "Production Order",
+ "width": 110
+ }, {
+ "fieldname": "bom_no",
+ "label": "BOM",
+ "fieldtype": "Link",
+ "options": "BOM",
+ "width": 120
+ }, {
+ "fieldname": "description",
+ "label": "Description",
+ "fieldtype": "Data",
+ "options": "",
+ "width": 230
+ }, {
+ "fieldname": "qty",
+ "label": "Qty to Build",
+ "fieldtype": "Data",
+ "options": "",
+ "width": 110
+ }, {
+ "fieldname": "status",
+ "label": "Status",
+ "fieldtype": "Data",
+ "options": "",
+ "width": 100
+ }, {
+ "fieldname": "req_items",
+ "label": "# Req'd Items",
+ "fieldtype": "Data",
+ "options": "",
+ "width": 105
+ }, {
+ "fieldname": "instock",
+ "label": "# In Stock",
+ "fieldtype": "Data",
+ "options": "",
+ "width": 105
+ }, {
+ "fieldname": "buildable_qty",
+ "label": "Buildable Qty",
+ "fieldtype": "Data",
+ "options": "",
+ "width": 100
+ }, {
+ "fieldname": "ready_to_build",
+ "label": "Build All?",
+ "fieldtype": "Data",
+ "options": "",
+ "width": 90
+ }]
+
+ return columns