Warehouse Tree view currently shows Stock Value against each warehouse by default as follows -
While we understand the feature and need for the same, fetching stock balances of each child warehouse on click on ‘Expand All’ button results into calculation of stock value for all the child warehouses using following function -
def get_stock_value_on(warehouse=None, posting_date=None, item_code=None):
if not posting_date: posting_date = nowdate()
values, condition = [posting_date], ""
if warehouse:
lft, rgt, is_group = frappe.db.get_value("Warehouse", warehouse, ["lft", "rgt", "is_group"])
if is_group:
values.extend([lft, rgt])
condition += "and exists (\
select name from `tabWarehouse` wh where wh.name = sle.warehouse\
and wh.lft >= %s and wh.rgt <= %s)"
else:
values.append(warehouse)
condition += " AND warehouse = %s"
if item_code:
values.append(item_code)
condition.append(" AND item_code = %s")
stock_ledger_entries = frappe.db.sql("""
SELECT item_code, stock_value, name, warehouse
FROM `tabStock Ledger Entry` sle
WHERE posting_date <= %s {0}
ORDER BY timestamp(posting_date, posting_time) DESC, name DESC
""".format(condition), values, as_dict=1)
sle_map = {}
for sle in stock_ledger_entries:
if not sle_map.has_key((sle.item_code, sle.warehouse)):
sle_map[(sle.item_code, sle.warehouse)] = flt(sle.stock_value)
return sum(sle_map.values())
While we can tune above function to make it directly select sum(stock_value) directly in the query, we had a question if the stock_value can be retrieve directly from tabBin by multiplying actual_qty by valuation_rate?
This way we can avoid larger table scan on tabStock Ledger Entry