Merge pull request #16537 from nabinhait/stock-balance-fix
fix: Fixed error on stock balance report
diff --git a/erpnext/stock/report/stock_balance/stock_balance.py b/erpnext/stock/report/stock_balance/stock_balance.py
index e72e94b..0ece78f 100644
--- a/erpnext/stock/report/stock_balance/stock_balance.py
+++ b/erpnext/stock/report/stock_balance/stock_balance.py
@@ -173,15 +173,15 @@
qty_dict.val_rate = d.valuation_rate
qty_dict.bal_qty += qty_diff
qty_dict.bal_val += value_diff
-
+
iwb_map = filter_items_with_no_transactions(iwb_map)
return iwb_map
-
+
def filter_items_with_no_transactions(iwb_map):
for (company, item, warehouse) in sorted(iwb_map):
qty_dict = iwb_map[(company, item, warehouse)]
-
+
no_transactions = True
float_precision = cint(frappe.db.get_default("float_precision")) or 3
for key, val in iteritems(qty_dict):
@@ -189,7 +189,7 @@
qty_dict[key] = val
if key != "val_rate" and val:
no_transactions = False
-
+
if no_transactions:
iwb_map.pop((company, item, warehouse))
@@ -216,20 +216,28 @@
if not items:
items = list(set([d.item_code for d in sle]))
- if items:
- cf_field = cf_join = ""
- if filters.get("include_uom"):
- cf_field = ", ucd.conversion_factor"
- cf_join = "left join `tabUOM Conversion Detail` ucd on ucd.parent=item.name and ucd.uom=%(include_uom)s"
+ if not items:
+ return item_details
- for item in frappe.db.sql("""
- select item.name, item.item_name, item.description, item.item_group, item.brand, item.stock_uom{cf_field}
- from `tabItem` item
+ cf_field = cf_join = ""
+ if filters.get("include_uom"):
+ cf_field = ", ucd.conversion_factor"
+ cf_join = "left join `tabUOM Conversion Detail` ucd on ucd.parent=item.name and ucd.uom='%s'" \
+ % frappe.db.escape(filters.get("include_uom"))
+
+ item_codes = ', '.join(['"' + frappe.db.escape(i, percent=False) + '"' for i in items])
+ res = frappe.db.sql("""
+ select
+ item.name, item.item_name, item.description, item.item_group, item.brand, item.stock_uom {cf_field}
+ from
+ `tabItem` item
{cf_join}
- where item.name in ({names}) and ifnull(item.disabled, 0) = 0
- """.format(cf_field=cf_field, cf_join=cf_join, names=', '.join(['"' + frappe.db.escape(i, percent=False) + '"' for i in items])),
- {"include_uom": filters.get("include_uom")}, as_dict=1):
- item_details.setdefault(item.name, item)
+ where
+ item.name in ({item_codes}) and ifnull(item.disabled, 0) = 0
+ """.format(cf_field=cf_field, cf_join=cf_join, item_codes=item_codes), as_dict=1)
+
+ for item in res:
+ item_details.setdefault(item.name, item)
if filters.get('show_variant_attributes', 0) == 1:
variant_values = get_variant_values_for(list(item_details))