-
Notifications
You must be signed in to change notification settings - Fork 6.7k
/
bom_stock_report.py
79 lines (64 loc) · 2.08 KB
/
bom_stock_report.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
# 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",
_("Qty per BOM Line") + ":Float:100",
_("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")
table = "`tabBOM Item`"
qty_field = "qty"
qty_to_produce = filters.get("qty_to_produce", 1)
if int(qty_to_produce) <= 0:
frappe.throw(_("Quantity to Produce can not be less than Zero"))
if filters.get("show_exploded_view"):
table = "`tabBOM Explosion Item`"
qty_field = "stock_qty"
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_field},
bom_item.{qty_field} * {qty_to_produce},
sum(ledger.actual_qty) as actual_qty,
sum(FLOOR(ledger.actual_qty / (bom_item.{qty_field} * {qty_to_produce})))
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,
qty_to_produce=qty_to_produce or 1)
)