Merge pull request #25797 from marination/item-variants-report
fix: Item Variant Details Report
diff --git a/erpnext/stock/report/item_variant_details/item_variant_details.py b/erpnext/stock/report/item_variant_details/item_variant_details.py
index e8449cc..d8563d7 100644
--- a/erpnext/stock/report/item_variant_details/item_variant_details.py
+++ b/erpnext/stock/report/item_variant_details/item_variant_details.py
@@ -14,47 +14,58 @@
if not item:
return []
item_dicts = []
- variants = None
- variant_results = frappe.db.sql("""select name from `tabItem`
- where variant_of = %s""", item, as_dict=1)
+ variant_results = frappe.db.get_all(
+ "Item",
+ fields=["name"],
+ filters={
+ "variant_of": ["=", item],
+ "disabled": 0
+ }
+ )
+
if not variant_results:
- frappe.msgprint(_("There isn't any item variant for the selected item"))
+ frappe.msgprint(_("There aren't any item variants for the selected item"))
return []
else:
- variants = ", ".join([frappe.db.escape(variant['name']) for variant in variant_results])
+ variant_list = [variant['name'] for variant in variant_results]
- order_count_map = get_open_sales_orders_map(variants)
- stock_details_map = get_stock_details_map(variants)
- buying_price_map = get_buying_price_map(variants)
- selling_price_map = get_selling_price_map(variants)
- attr_val_map = get_attribute_values_map(variants)
+ order_count_map = get_open_sales_orders_count(variant_list)
+ stock_details_map = get_stock_details_map(variant_list)
+ buying_price_map = get_buying_price_map(variant_list)
+ selling_price_map = get_selling_price_map(variant_list)
+ attr_val_map = get_attribute_values_map(variant_list)
- attribute_list = [d[0] for d in frappe.db.sql("""select attribute
- from `tabItem Variant Attribute`
- where parent in ({variants}) group by attribute""".format(variants=variants))]
+ attributes = frappe.db.get_all(
+ "Item Variant Attribute",
+ fields=["attribute"],
+ filters={
+ "parent": ["in", variant_list]
+ },
+ group_by="attribute"
+ )
+ attribute_list = [row.get("attribute") for row in attributes]
# Prepare dicts
variant_dicts = [{"variant_name": d['name']} for d in variant_results]
for item_dict in variant_dicts:
- name = item_dict["variant_name"]
+ name = item_dict.get("variant_name")
- for d in attribute_list:
- attr_dict = attr_val_map[name]
- if attr_dict and attr_dict.get(d):
- item_dict[d] = attr_val_map[name][d]
+ for attribute in attribute_list:
+ attr_dict = attr_val_map.get(name)
+ if attr_dict and attr_dict.get(attribute):
+ item_dict[frappe.scrub(attribute)] = attr_val_map.get(name).get(attribute)
- item_dict["Open Orders"] = order_count_map.get(name) or 0
+ item_dict["open_orders"] = order_count_map.get(name) or 0
if stock_details_map.get(name):
- item_dict["Inventory"] = stock_details_map.get(name)["Inventory"] or 0
- item_dict["In Production"] = stock_details_map.get(name)["In Production"] or 0
- item_dict["Available Selling"] = stock_details_map.get(name)["Available Selling"] or 0
+ item_dict["current_stock"] = stock_details_map.get(name)["Inventory"] or 0
+ item_dict["in_production"] = stock_details_map.get(name)["In Production"] or 0
else:
- item_dict["Inventory"] = item_dict["In Production"] = item_dict["Available Selling"] = 0
+ item_dict["current_stock"] = item_dict["in_production"] = 0
- item_dict["Avg. Buying Price List Rate"] = buying_price_map.get(name) or 0
- item_dict["Avg. Selling Price List Rate"] = selling_price_map.get(name) or 0
+ item_dict["avg_buying_price_list_rate"] = buying_price_map.get(name) or 0
+ item_dict["avg_selling_price_list_rate"] = selling_price_map.get(name) or 0
item_dicts.append(item_dict)
@@ -71,117 +82,158 @@
item_doc = frappe.get_doc("Item", item)
- for d in item_doc.attributes:
- columns.append(d.attribute + ":Data:100")
+ for entry in item_doc.attributes:
+ columns.append({
+ "fieldname": frappe.scrub(entry.attribute),
+ "label": entry.attribute,
+ "fieldtype": "Data",
+ "width": 100
+ })
- columns += [_("Avg. Buying Price List Rate") + ":Currency:110", _("Avg. Selling Price List Rate") + ":Currency:110",
- _("Inventory") + ":Float:100", _("In Production") + ":Float:100",
- _("Open Orders") + ":Float:100", _("Available Selling") + ":Float:100"
+ additional_columns = [
+ {
+ "fieldname": "avg_buying_price_list_rate",
+ "label": _("Avg. Buying Price List Rate"),
+ "fieldtype": "Currency",
+ "width": 150
+ },
+ {
+ "fieldname": "avg_selling_price_list_rate",
+ "label": _("Avg. Selling Price List Rate"),
+ "fieldtype": "Currency",
+ "width": 150
+ },
+ {
+ "fieldname": "current_stock",
+ "label": _("Current Stock"),
+ "fieldtype": "Float",
+ "width": 120
+ },
+ {
+ "fieldname": "in_production",
+ "label": _("In Production"),
+ "fieldtype": "Float",
+ "width": 150
+ },
+ {
+ "fieldname": "open_orders",
+ "label": _("Open Sales Orders"),
+ "fieldtype": "Float",
+ "width": 150
+ }
]
+ columns.extend(additional_columns)
return columns
-def get_open_sales_orders_map(variants):
- open_sales_orders = frappe.db.sql("""
- select
- count(*) as count,
- item_code
- from
- `tabSales Order Item`
- where
- docstatus = 1 and
- qty > ifnull(delivered_qty, 0) and
- item_code in ({variants})
- group by
- item_code
- """.format(variants=variants), as_dict=1)
+def get_open_sales_orders_count(variants_list):
+ open_sales_orders = frappe.db.get_list(
+ "Sales Order",
+ fields=[
+ "name",
+ "`tabSales Order Item`.item_code"
+ ],
+ filters=[
+ ["Sales Order", "docstatus", "=", 1],
+ ["Sales Order Item", "item_code", "in", variants_list]
+ ],
+ distinct=1
+ )
order_count_map = {}
- for d in open_sales_orders:
- order_count_map[d["item_code"]] = d["count"]
+ for row in open_sales_orders:
+ item_code = row.get("item_code")
+ if order_count_map.get(item_code) is None:
+ order_count_map[item_code] = 1
+ else:
+ order_count_map[item_code] += 1
return order_count_map
-def get_stock_details_map(variants):
- stock_details = frappe.db.sql("""
- select
- sum(planned_qty) as planned_qty,
- sum(actual_qty) as actual_qty,
- sum(projected_qty) as projected_qty,
- item_code
- from
- `tabBin`
- where
- item_code in ({variants})
- group by
- item_code
- """.format(variants=variants), as_dict=1)
+def get_stock_details_map(variant_list):
+ stock_details = frappe.db.get_all(
+ "Bin",
+ fields=[
+ "sum(planned_qty) as planned_qty",
+ "sum(actual_qty) as actual_qty",
+ "sum(projected_qty) as projected_qty",
+ "item_code",
+ ],
+ filters={
+ "item_code": ["in", variant_list]
+ },
+ group_by="item_code"
+ )
stock_details_map = {}
- for d in stock_details:
- name = d["item_code"]
+ for row in stock_details:
+ name = row.get("item_code")
stock_details_map[name] = {
- "Inventory" :d["actual_qty"],
- "In Production" :d["planned_qty"],
- "Available Selling" :d["projected_qty"]
+ "Inventory": row.get("actual_qty"),
+ "In Production": row.get("planned_qty")
}
return stock_details_map
-def get_buying_price_map(variants):
- buying = frappe.db.sql("""
- select
- avg(price_list_rate) as avg_rate,
- item_code
- from
- `tabItem Price`
- where
- item_code in ({variants}) and buying=1
- group by
- item_code
- """.format(variants=variants), as_dict=1)
+def get_buying_price_map(variant_list):
+ buying = frappe.db.get_all(
+ "Item Price",
+ fields=[
+ "avg(price_list_rate) as avg_rate",
+ "item_code",
+ ],
+ filters={
+ "item_code": ["in", variant_list],
+ "buying": 1
+ },
+ group_by="item_code"
+ )
buying_price_map = {}
- for d in buying:
- buying_price_map[d["item_code"]] = d["avg_rate"]
+ for row in buying:
+ buying_price_map[row.get("item_code")] = row.get("avg_rate")
return buying_price_map
-def get_selling_price_map(variants):
- selling = frappe.db.sql("""
- select
- avg(price_list_rate) as avg_rate,
- item_code
- from
- `tabItem Price`
- where
- item_code in ({variants}) and selling=1
- group by
- item_code
- """.format(variants=variants), as_dict=1)
+def get_selling_price_map(variant_list):
+ selling = frappe.db.get_all(
+ "Item Price",
+ fields=[
+ "avg(price_list_rate) as avg_rate",
+ "item_code",
+ ],
+ filters={
+ "item_code": ["in", variant_list],
+ "selling": 1
+ },
+ group_by="item_code"
+ )
selling_price_map = {}
- for d in selling:
- selling_price_map[d["item_code"]] = d["avg_rate"]
+ for row in selling:
+ selling_price_map[row.get("item_code")] = row.get("avg_rate")
return selling_price_map
-def get_attribute_values_map(variants):
- list_attr = frappe.db.sql("""
- select
- attribute, attribute_value, parent
- from
- `tabItem Variant Attribute`
- where
- parent in ({variants})
- """.format(variants=variants), as_dict=1)
+def get_attribute_values_map(variant_list):
+ attribute_list = frappe.db.get_all(
+ "Item Variant Attribute",
+ fields=[
+ "attribute",
+ "attribute_value",
+ "parent"
+ ],
+ filters={
+ "parent": ["in", variant_list]
+ }
+ )
attr_val_map = {}
- for d in list_attr:
- name = d["parent"]
+ for row in attribute_list:
+ name = row.get("parent")
if not attr_val_map.get(name):
attr_val_map[name] = {}
- attr_val_map[name][d["attribute"]] = d["attribute_value"]
+ attr_val_map[name][row.get("attribute")] = row.get("attribute_value")
return attr_val_map