Adding "Include UOM" in Reports with Qty and Rates (#15541)
* Added Include UOM field for Stock Balane, Stock Ledger and Stock Projected Qty
* Add columns in result list-of-lists instead of converting reports to list-of-dicts
* For requested changes
-Merged conversion factor query with item detail queries
-Ensuring snail_case
-Made columns consistently list-of-dicts
diff --git a/erpnext/stock/report/stock_balance/stock_balance.js b/erpnext/stock/report/stock_balance/stock_balance.js
index a563564..839ed7a 100644
--- a/erpnext/stock/report/stock_balance/stock_balance.js
+++ b/erpnext/stock/report/stock_balance/stock_balance.js
@@ -52,6 +52,12 @@
"options": "Warehouse"
},
{
+ "fieldname":"include_uom",
+ "label": __("Include UOM"),
+ "fieldtype": "Link",
+ "options": "UOM"
+ },
+ {
"fieldname": "show_variant_attributes",
"label": __("Show Variant Attributes"),
"fieldtype": "Check"
diff --git a/erpnext/stock/report/stock_balance/stock_balance.py b/erpnext/stock/report/stock_balance/stock_balance.py
index e6ca5c2..e72e94b 100644
--- a/erpnext/stock/report/stock_balance/stock_balance.py
+++ b/erpnext/stock/report/stock_balance/stock_balance.py
@@ -5,6 +5,7 @@
import frappe
from frappe import _
from frappe.utils import flt, cint, getdate, now
+from erpnext.stock.utils import update_included_uom_in_report
from erpnext.stock.report.stock_ledger.stock_ledger import get_item_group_condition
from six import iteritems
@@ -14,6 +15,7 @@
validate_filters(filters)
+ include_uom = filters.get("include_uom")
columns = get_columns()
items = get_items(filters)
sle = get_stock_ledger_entries(filters, items)
@@ -27,6 +29,7 @@
item_reorder_detail_map = get_item_reorder_details(item_map.keys())
data = []
+ conversion_factors = []
for (company, item, warehouse) in sorted(iwb_map):
if item_map.get(item):
qty_dict = iwb_map[(company, item, warehouse)]
@@ -54,36 +57,40 @@
variants_attributes = get_variants_attributes()
report_data += [item_map[item].get(i) for i in variants_attributes]
+ if include_uom:
+ conversion_factors.append(item_map[item].conversion_factor)
+
data.append(report_data)
if filters.get('show_variant_attributes', 0) == 1:
columns += ["{}:Data:100".format(i) for i in get_variants_attributes()]
+ update_included_uom_in_report(columns, data, include_uom, conversion_factors)
return columns, data
def get_columns():
"""return columns"""
columns = [
- _("Item")+":Link/Item:100",
- _("Item Name")+"::150",
- _("Item Group")+":Link/Item Group:100",
- _("Brand")+":Link/Brand:90",
- _("Description")+"::140",
- _("Warehouse")+":Link/Warehouse:100",
- _("Stock UOM")+":Link/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",
- _("Reorder Level")+":Float:80",
- _("Reorder Qty")+":Float:80",
- _("Company")+":Link/Company:100"
+ {"label": _("Item"), "fieldname": "item_code", "fieldtype": "Link", "options": "Item", "width": 100},
+ {"label": _("Item Name"), "fieldname": "item_name", "width": 150},
+ {"label": _("Item Group"), "fieldname": "item_group", "fieldtype": "Link", "options": "Item Group", "width": 100},
+ {"label": _("Brand"), "fieldname": "brand", "fieldtype": "Link", "options": "Brand", "width": 90},
+ {"label": _("Description"), "fieldname": "description", "width": 140},
+ {"label": _("Warehouse"), "fieldname": "warehouse", "fieldtype": "Link", "options": "Warehouse", "width": 100},
+ {"label": _("Stock UOM"), "fieldname": "stock_uom", "fieldtype": "Link", "options": "UOM", "width": 90},
+ {"label": _("Opening Qty"), "fieldname": "opening_qty", "fieldtype": "Float", "width": 100, "convertible": "qty"},
+ {"label": _("Opening Value"), "fieldname": "opening_val", "fieldtype": "Float", "width": 110},
+ {"label": _("In Qty"), "fieldname": "in_qty", "fieldtype": "Float", "width": 80, "convertible": "qty"},
+ {"label": _("In Value"), "fieldname": "in_val", "fieldtype": "Float", "width": 80},
+ {"label": _("Out Qty"), "fieldname": "out_qty", "fieldtype": "Float", "width": 80, "convertible": "qty"},
+ {"label": _("Out Value"), "fieldname": "out_val", "fieldtype": "Float", "width": 80},
+ {"label": _("Balance Qty"), "fieldname": "bal_qty", "fieldtype": "Float", "width": 100, "convertible": "qty"},
+ {"label": _("Balance Value"), "fieldname": "bal_val", "fieldtype": "Currency", "width": 100},
+ {"label": _("Valuation Rate"), "fieldname": "val_rate", "fieldtype": "Currency", "width": 90, "convertible": "rate"},
+ {"label": _("Reorder Level"), "fieldname": "reorder_level", "fieldtype": "Float", "width": 80, "convertible": "qty"},
+ {"label": _("Reorder Qty"), "fieldname": "reorder_qty", "fieldtype": "Float", "width": 80, "convertible": "qty"},
+ {"label": _("Company"), "fieldname": "company", "fieldtype": "Link", "options": "Company", "width": 100}
]
return columns
@@ -210,11 +217,18 @@
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"
+
for item in frappe.db.sql("""
- select name, item_name, description, item_group, brand, stock_uom
- from `tabItem`
- where name in ({0}) and ifnull(disabled, 0) = 0
- """.format(', '.join(['"' + frappe.db.escape(i, percent=False) + '"' for i in items])), as_dict=1):
+ 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)
if filters.get('show_variant_attributes', 0) == 1:
diff --git a/erpnext/stock/report/stock_ledger/stock_ledger.js b/erpnext/stock/report/stock_ledger/stock_ledger.js
index 660357c..3fab327 100644
--- a/erpnext/stock/report/stock_ledger/stock_ledger.js
+++ b/erpnext/stock/report/stock_ledger/stock_ledger.js
@@ -70,6 +70,12 @@
"label": __("Project"),
"fieldtype": "Link",
"options": "Project"
+ },
+ {
+ "fieldname":"include_uom",
+ "label": __("Include UOM"),
+ "fieldtype": "Link",
+ "options": "UOM"
}
]
}
diff --git a/erpnext/stock/report/stock_ledger/stock_ledger.py b/erpnext/stock/report/stock_ledger/stock_ledger.py
index 9237cfd..578000b 100644
--- a/erpnext/stock/report/stock_ledger/stock_ledger.py
+++ b/erpnext/stock/report/stock_ledger/stock_ledger.py
@@ -4,15 +4,18 @@
from __future__ import unicode_literals
import frappe
from frappe import _
+from erpnext.stock.utils import update_included_uom_in_report
def execute(filters=None):
+ include_uom = filters.get("include_uom")
columns = get_columns()
items = get_items(filters)
sl_entries = get_stock_ledger_entries(filters, items)
- item_details = get_item_details(items, sl_entries)
+ item_details = get_item_details(items, sl_entries, include_uom)
opening_row = get_opening_balance(filters, columns)
data = []
+ conversion_factors = []
if opening_row:
data.append(opening_row)
@@ -26,28 +29,36 @@
sle.valuation_rate, sle.stock_value, sle.voucher_type, sle.voucher_no,
sle.batch_no, sle.serial_no, sle.project, sle.company])
+ if include_uom:
+ conversion_factors.append(item_detail.conversion_factor)
+
+ update_included_uom_in_report(columns, data, include_uom, conversion_factors)
return columns, data
def get_columns():
columns = [
- _("Date") + ":Datetime:95", _("Item") + ":Link/Item:130",
- _("Item Name") + "::100", _("Item Group") + ":Link/Item Group:100",
- _("Brand") + ":Link/Brand:100", _("Description") + "::200",
- _("Warehouse") + ":Link/Warehouse:100", _("Stock UOM") + ":Link/UOM:100",
- _("Qty") + ":Float:50", _("Balance Qty") + ":Float:100",
+ {"label": _("Date"), "fieldname": "date", "fieldtype": "Datetime", "width": 95},
+ {"label": _("Item"), "fieldname": "item_code", "fieldtype": "Link", "options": "Item", "width": 130},
+ {"label": _("Item Name"), "fieldname": "item_name", "width": 100},
+ {"label": _("Item Group"), "fieldname": "item_group", "fieldtype": "Link", "options": "Item Group", "width": 100},
+ {"label": _("Brand"), "fieldname": "brand", "fieldtype": "Link", "options": "Brand", "width": 100},
+ {"label": _("Description"), "fieldname": "description", "width": 200},
+ {"label": _("Warehouse"), "fieldname": "warehouse", "fieldtype": "Link", "options": "Warehouse", "width": 100},
+ {"label": _("Stock UOM"), "fieldname": "stock_uom", "fieldtype": "Link", "options": "UOM", "width": 100},
+ {"label": _("Qty"), "fieldname": "actual_qty", "fieldtype": "Float", "width": 50, "convertible": "qty"},
+ {"label": _("Balance Qty"), "fieldname": "qty_after_transaction", "fieldtype": "Float", "width": 100, "convertible": "qty"},
{"label": _("Incoming Rate"), "fieldname": "incoming_rate", "fieldtype": "Currency", "width": 110,
- "options": "Company:company:default_currency"},
+ "options": "Company:company:default_currency", "convertible": "rate"},
{"label": _("Valuation Rate"), "fieldname": "valuation_rate", "fieldtype": "Currency", "width": 110,
- "options": "Company:company:default_currency"},
+ "options": "Company:company:default_currency", "convertible": "rate"},
{"label": _("Balance Value"), "fieldname": "stock_value", "fieldtype": "Currency", "width": 110,
"options": "Company:company:default_currency"},
- _("Voucher Type") + "::110",
- _("Voucher #") + ":Dynamic Link/" + _("Voucher Type") + ":100",
- _("Batch") + ":Link/Batch:100",
- _("Serial #") + ":Link/Serial No:100",
- _("Project") + ":Link/Project:100",
- {"label": _("Company"), "fieldtype": "Link", "width": 110,
- "options": "company", "fieldname": "company"}
+ {"label": _("Voucher Type"), "fieldname": "voucher_type", "width": 110},
+ {"label": _("Voucher #"), "fieldname": "voucher_no", "fieldtype": "Dynamic Link", "options": "voucher_type", "width": 100},
+ {"label": _("Batch"), "fieldname": "batch_no", "fieldtype": "Link", "options": "Batch", "width": 100},
+ {"label": _("Serial #"), "fieldname": "serial_no", "fieldtype": "Link", "options": "Serial No", "width": 100},
+ {"label": _("Project"), "fieldname": "project", "fieldtype": "Link", "options": "Project", "width": 100},
+ {"label": _("Company"), "fieldname": "company", "fieldtype": "Link", "options": "Company", "width": 110}
]
return columns
@@ -88,7 +99,7 @@
.format(" and ".join(conditions)), filters)
return items
-def get_item_details(items, sl_entries):
+def get_item_details(items, sl_entries, include_uom):
item_details = {}
if not items:
items = list(set([d.item_code for d in sl_entries]))
@@ -96,11 +107,18 @@
if not items:
return item_details
+ cf_field = cf_join = ""
+ if 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"
+
for item in frappe.db.sql("""
- select name, item_name, description, item_group, brand, stock_uom
- from `tabItem`
- where name in ({0})
- """.format(', '.join(['"' + frappe.db.escape(i,percent=False) + '"' for i in items])), as_dict=1):
+ 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})
+ """.format(cf_field=cf_field, cf_join=cf_join, names=', '.join(['"' + frappe.db.escape(i, percent=False) + '"' for i in items])),
+ {"include_uom": include_uom}, as_dict=1):
item_details.setdefault(item.name, item)
return item_details
diff --git a/erpnext/stock/report/stock_projected_qty/stock_projected_qty.js b/erpnext/stock/report/stock_projected_qty/stock_projected_qty.js
index 51b9b0c..6589688 100644
--- a/erpnext/stock/report/stock_projected_qty/stock_projected_qty.js
+++ b/erpnext/stock/report/stock_projected_qty/stock_projected_qty.js
@@ -37,6 +37,12 @@
"label": __("Brand"),
"fieldtype": "Link",
"options": "Brand"
+ },
+ {
+ "fieldname":"include_uom",
+ "label": __("Include UOM"),
+ "fieldtype": "Link",
+ "options": "UOM"
}
]
}
diff --git a/erpnext/stock/report/stock_projected_qty/stock_projected_qty.py b/erpnext/stock/report/stock_projected_qty/stock_projected_qty.py
index 3e6e5a5..d6be6c08 100644
--- a/erpnext/stock/report/stock_projected_qty/stock_projected_qty.py
+++ b/erpnext/stock/report/stock_projected_qty/stock_projected_qty.py
@@ -5,27 +5,18 @@
import frappe
from frappe import _
from frappe.utils import flt, today
+from erpnext.stock.utils import update_included_uom_in_report
def execute(filters=None):
filters = frappe._dict(filters or {})
- return get_columns(), get_data(filters)
-
-def get_columns():
- return [_("Item Code") + ":Link/Item:140", _("Item Name") + "::100", _("Description") + "::200",
- _("Item Group") + ":Link/Item Group:100", _("Brand") + ":Link/Brand:100", _("Warehouse") + ":Link/Warehouse:120",
- _("UOM") + ":Link/UOM:100", _("Actual Qty") + ":Float:100", _("Planned Qty") + ":Float:100",
- _("Requested Qty") + ":Float:110", _("Ordered Qty") + ":Float:100",
- _("Reserved Qty") + ":Float:100", _("Reserved Qty for Production") + ":Float:100",
- _("Reserved for sub contracting") + ":Float:100",
- _("Projected Qty") + ":Float:100", _("Reorder Level") + ":Float:100", _("Reorder Qty") + ":Float:100",
- _("Shortage Qty") + ":Float:100"]
-
-def get_data(filters):
+ include_uom = filters.get("include_uom")
+ columns = get_columns()
bin_list = get_bin_list(filters)
- item_map = get_item_map(filters.get("item_code"))
+ item_map = get_item_map(filters.get("item_code"), include_uom)
+
warehouse_company = {}
data = []
-
+ conversion_factors = []
for bin in bin_list:
item = item_map.get(bin.item_code)
@@ -60,7 +51,35 @@
bin.reserved_qty, bin.reserved_qty_for_production, bin.reserved_qty_for_sub_contract,
bin.projected_qty, re_order_level, re_order_qty, shortage_qty])
- return data
+ if include_uom:
+ conversion_factors.append(item.conversion_factor)
+
+ update_included_uom_in_report(columns, data, include_uom, conversion_factors)
+ return columns, data
+
+def get_columns():
+ return [
+ {"label": _("Item Code"), "fieldname": "item_code", "fieldtype": "Link", "options": "Item", "width": 140},
+ {"label": _("Item Name"), "fieldname": "item_name", "width": 100},
+ {"label": _("Description"), "fieldname": "description", "width": 200},
+ {"label": _("Item Group"), "fieldname": "item_group", "fieldtype": "Link", "options": "Item Group", "width": 100},
+ {"label": _("Brand"), "fieldname": "brand", "fieldtype": "Link", "options": "Brand", "width": 100},
+ {"label": _("Warehouse"), "fieldname": "warehouse", "fieldtype": "Link", "options": "Warehouse", "width": 120},
+ {"label": _("UOM"), "fieldname": "stock_uom", "fieldtype": "Link", "options": "UOM", "width": 100},
+ {"label": _("Actual Qty"), "fieldname": "actual_qty", "fieldtype": "Float", "width": 100, "convertible": "qty"},
+ {"label": _("Planned Qty"), "fieldname": "planned_qty", "fieldtype": "Float", "width": 100, "convertible": "qty"},
+ {"label": _("Requested Qty"), "fieldname": "indented_qty", "fieldtype": "Float", "width": 110, "convertible": "qty"},
+ {"label": _("Ordered Qty"), "fieldname": "ordered_qty", "fieldtype": "Float", "width": 100, "convertible": "qty"},
+ {"label": _("Reserved Qty"), "fieldname": "reserved_qty", "fieldtype": "Float", "width": 100, "convertible": "qty"},
+ {"label": _("Reserved Qty for Production"), "fieldname": "reserved_qty_for_production", "fieldtype": "Float",
+ "width": 100, "convertible": "qty"},
+ {"label": _("Reserved for sub contracting"), "fieldname": "reserved_qty_for_sub_contract", "fieldtype": "Float",
+ "width": 100, "convertible": "qty"},
+ {"label": _("Projected Qty"), "fieldname": "projected_qty", "fieldtype": "Float", "width": 100, "convertible": "qty"},
+ {"label": _("Reorder Level"), "fieldname": "re_order_level", "fieldtype": "Float", "width": 100, "convertible": "qty"},
+ {"label": _("Reorder Qty"), "fieldname": "re_order_qty", "fieldtype": "Float", "width": 100, "convertible": "qty"},
+ {"label": _("Shortage Qty"), "fieldname": "shortage_qty", "fieldtype": "Float", "width": 100, "convertible": "qty"}
+ ]
def get_bin_list(filters):
conditions = []
@@ -83,20 +102,29 @@
return bin_list
-def get_item_map(item_code):
+def get_item_map(item_code, include_uom):
"""Optimization: get only the item doc and re_order_levels table"""
condition = ""
if item_code:
condition = 'and item_code = "{0}"'.format(frappe.db.escape(item_code, percent=False))
- items = frappe.db.sql("""select * from `tabItem` item
- where is_stock_item = 1
- and disabled=0
+ cf_field = cf_join = ""
+ if 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"
+
+ items = 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.is_stock_item = 1
+ and item.disabled=0
{condition}
- and (end_of_life > %(today)s or end_of_life is null or end_of_life='0000-00-00')
+ and (item.end_of_life > %(today)s or item.end_of_life is null or item.end_of_life='0000-00-00')
and exists (select name from `tabBin` bin where bin.item_code=item.name)"""\
- .format(condition=condition), {"today": today()}, as_dict=True)
+ .format(cf_field=cf_field, cf_join=cf_join, condition=condition),
+ {"today": today(), "include_uom": include_uom}, as_dict=True)
condition = ""
if item_code:
diff --git a/erpnext/stock/utils.py b/erpnext/stock/utils.py
index 55078a5..de31c54 100644
--- a/erpnext/stock/utils.py
+++ b/erpnext/stock/utils.py
@@ -246,3 +246,34 @@
def is_group_warehouse(warehouse):
if frappe.db.get_value("Warehouse", warehouse, "is_group"):
frappe.throw(_("Group node warehouse is not allowed to select for transactions"))
+
+def update_included_uom_in_report(columns, result, include_uom, conversion_factors):
+ if not include_uom or not conversion_factors:
+ return
+
+ convertible_cols = {}
+ for col_idx in reversed(range(0, len(columns))):
+ col = columns[col_idx]
+ if isinstance(col, dict) and col.get("convertible") in ['rate', 'qty']:
+ convertible_cols[col_idx] = col['convertible']
+ columns.insert(col_idx+1, col.copy())
+ columns[col_idx+1]['fieldname'] += "_alt"
+ if convertible_cols[col_idx] == 'rate':
+ columns[col_idx+1]['label'] += " (per {})".format(include_uom)
+ else:
+ columns[col_idx+1]['label'] += " ({})".format(include_uom)
+
+ for row_idx, row in enumerate(result):
+ new_row = []
+ for col_idx, d in enumerate(row):
+ new_row.append(d)
+ if col_idx in convertible_cols:
+ if conversion_factors[row_idx]:
+ if convertible_cols[col_idx] == 'rate':
+ new_row.append(flt(d) * conversion_factors[row_idx])
+ else:
+ new_row.append(flt(d) / conversion_factors[row_idx])
+ else:
+ new_row.append(None)
+
+ result[row_idx] = new_row