Update report with Valuation rate, stock values, move to Main report
diff --git a/erpnext/config/stock.py b/erpnext/config/stock.py
index bfb4b7f..957abec 100644
--- a/erpnext/config/stock.py
+++ b/erpnext/config/stock.py
@@ -171,6 +171,12 @@
"label": _("Stock Analytics"),
"icon": "icon-bar-chart"
},
+ {
+ "type": "report",
+ "is_query_report": True,
+ "name": "Warehouse-Wise Stock Balance",
+ "doctype": "Warehouse"
+ },
]
},
{
@@ -225,12 +231,6 @@
{
"type": "report",
"is_query_report": True,
- "name": "Warehouse-Wise Stock Balance",
- "doctype": "Warehouse"
- },
- {
- "type": "report",
- "is_query_report": True,
"name": "Item Prices",
"doctype": "Price List"
},
diff --git a/erpnext/stock/report/warehouse_wise_stock_balance/warehouse_wise_stock_balance.py b/erpnext/stock/report/warehouse_wise_stock_balance/warehouse_wise_stock_balance.py
index 16fe3be..775f6f1 100644
--- a/erpnext/stock/report/warehouse_wise_stock_balance/warehouse_wise_stock_balance.py
+++ b/erpnext/stock/report/warehouse_wise_stock_balance/warehouse_wise_stock_balance.py
@@ -19,9 +19,15 @@
for wh in sorted(iwb_map[company][item]):
qty_dict = iwb_map[company][item][wh]
data.append([item, item_map[item]["item_name"],
+ item_map[item]["item_group"],
+ item_map[item]["brand"],
item_map[item]["description"], wh,
- qty_dict.opening_qty, qty_dict.in_qty,
- qty_dict.out_qty, qty_dict.bal_qty, company
+ qty_dict.uom, qty_dict.opening_qty,
+ qty_dict.opening_val, qty_dict.in_qty,
+ qty_dict.in_val, qty_dict.out_qty,
+ qty_dict.out_val, qty_dict.bal_qty,
+ qty_dict.bal_val, qty_dict.val_rate,
+ company
])
return columns, data
@@ -29,9 +35,11 @@
def get_columns(filters):
"""return columns based on filters"""
- columns = ["Item:Link/Item:100", "Item Name::150", "Description::150", \
- "Warehouse:Link/Warehouse:100", "Opening Qty:Float:90", \
- "In Qty:Float:80", "Out Qty:Float:80", "Balance Qty:Float:90", "Company:Link/Company:100"]
+ columns = ["Item:Link/Item:100", "Item Name::150", "Item Group::100", "Brand::90", \
+ "Description::140", "Warehouse:Link/Warehouse:100", "Stock UOM::90", "Opening Qty:Float:100", \
+ "Opening Value:Float:110", "In Qty:Float:80", "In Value:Float:80", "Out Qty:Float:80", \
+ "Out Value:Float:80", "Balance Qty:Float:100", "Balance Value:Float:100", \
+ "Valuation Rate:Float:90", "Company:Link/Company:100"]
return columns
@@ -50,8 +58,8 @@
#get all details
def get_stock_ledger_entries(filters):
conditions = get_conditions(filters)
- return frappe.db.sql("""select item_code, warehouse,
- posting_date, actual_qty, company
+ return frappe.db.sql("""select item_code, warehouse, posting_date,
+ actual_qty, valuation_rate, stock_uom, company
from `tabStock Ledger Entry`
where docstatus < 2 %s order by item_code, warehouse""" %
conditions, as_dict=1)
@@ -63,24 +71,37 @@
for d in sle:
iwb_map.setdefault(d.company, {}).setdefault(d.item_code, {}).\
setdefault(d.warehouse, frappe._dict({\
- "opening_qty": 0.0, "in_qty": 0.0, "out_qty": 0.0, "bal_qty": 0.0
+ "opening_qty": 0.0, "opening_val": 0.0,
+ "in_qty": 0.0, "in_val": 0.0,
+ "out_qty": 0.0, "out_val": 0.0,
+ "bal_qty": 0.0, "bal_val": 0.0,
+ "val_rate": 0.0, "uom": None
}))
qty_dict = iwb_map[d.company][d.item_code][d.warehouse]
+ qty_dict.uom = d.stock_uom
+
if d.posting_date < filters["from_date"]:
qty_dict.opening_qty += flt(d.actual_qty)
+ qty_dict.opening_val += flt(d.actual_qty * d.valuation_rate)
elif d.posting_date >= filters["from_date"] and d.posting_date <= filters["to_date"]:
+ qty_dict.val_rate = d.valuation_rate
+
if flt(d.actual_qty) > 0:
qty_dict.in_qty += flt(d.actual_qty)
+ qty_dict.in_val += flt(d.actual_qty * d.valuation_rate)
else:
qty_dict.out_qty += abs(flt(d.actual_qty))
+ qty_dict.out_val += flt(abs(flt(d.actual_qty)) * d.valuation_rate)
qty_dict.bal_qty += flt(d.actual_qty)
+ qty_dict.bal_val += flt(d.actual_qty * d.valuation_rate)
return iwb_map
def get_item_details(filters):
item_map = {}
- for d in frappe.db.sql("select name, item_name, description from tabItem", as_dict=1):
+ for d in frappe.db.sql("select name, item_name, item_group, brand, \
+ description from tabItem", as_dict=1):
item_map.setdefault(d.name, d)
return item_map