fix: currency formatting in item-wise sales history (#33903)
* fix(item-sales-history): currency formatting
* chore: linting issues
* fix: convert raw sql to qb
diff --git a/erpnext/selling/report/item_wise_sales_history/item_wise_sales_history.py b/erpnext/selling/report/item_wise_sales_history/item_wise_sales_history.py
index e10df2a..44c4d54 100644
--- a/erpnext/selling/report/item_wise_sales_history/item_wise_sales_history.py
+++ b/erpnext/selling/report/item_wise_sales_history/item_wise_sales_history.py
@@ -41,8 +41,20 @@
{"label": _("Description"), "fieldtype": "Data", "fieldname": "description", "width": 150},
{"label": _("Quantity"), "fieldtype": "Float", "fieldname": "quantity", "width": 150},
{"label": _("UOM"), "fieldtype": "Link", "fieldname": "uom", "options": "UOM", "width": 100},
- {"label": _("Rate"), "fieldname": "rate", "options": "Currency", "width": 120},
- {"label": _("Amount"), "fieldname": "amount", "options": "Currency", "width": 120},
+ {
+ "label": _("Rate"),
+ "fieldname": "rate",
+ "fieldtype": "Currency",
+ "options": "currency",
+ "width": 120,
+ },
+ {
+ "label": _("Amount"),
+ "fieldname": "amount",
+ "fieldtype": "Currency",
+ "options": "currency",
+ "width": 120,
+ },
{
"label": _("Sales Order"),
"fieldtype": "Link",
@@ -93,8 +105,9 @@
},
{
"label": _("Billed Amount"),
- "fieldtype": "currency",
+ "fieldtype": "Currency",
"fieldname": "billed_amount",
+ "options": "currency",
"width": 120,
},
{
@@ -104,6 +117,13 @@
"options": "Company",
"width": 100,
},
+ {
+ "label": _("Currency"),
+ "fieldtype": "Link",
+ "fieldname": "currency",
+ "options": "Currency",
+ "hidden": 1,
+ },
]
@@ -141,31 +161,12 @@
"billed_amount": flt(record.get("billed_amt")),
"company": record.get("company"),
}
+ row["currency"] = frappe.get_cached_value("Company", row["company"], "default_currency")
data.append(row)
return data
-def get_conditions(filters):
- conditions = ""
- if filters.get("item_group"):
- conditions += "AND so_item.item_group = %s" % frappe.db.escape(filters.item_group)
-
- if filters.get("from_date"):
- conditions += "AND so.transaction_date >= '%s'" % filters.from_date
-
- if filters.get("to_date"):
- conditions += "AND so.transaction_date <= '%s'" % filters.to_date
-
- if filters.get("item_code"):
- conditions += "AND so_item.item_code = %s" % frappe.db.escape(filters.item_code)
-
- if filters.get("customer"):
- conditions += "AND so.customer = %s" % frappe.db.escape(filters.customer)
-
- return conditions
-
-
def get_customer_details():
details = frappe.get_all("Customer", fields=["name", "customer_name", "customer_group"])
customer_details = {}
@@ -187,29 +188,50 @@
def get_sales_order_details(company_list, filters):
- conditions = get_conditions(filters)
+ db_so = frappe.qb.DocType("Sales Order")
+ db_so_item = frappe.qb.DocType("Sales Order Item")
- return frappe.db.sql(
- """
- SELECT
- so_item.item_code, so_item.description, so_item.qty,
- so_item.uom, so_item.base_rate, so_item.base_amount,
- so.name, so.transaction_date, so.customer,so.territory,
- so.project, so_item.delivered_qty,
- so_item.billed_amt, so.company
- FROM
- `tabSales Order` so, `tabSales Order Item` so_item
- WHERE
- so.name = so_item.parent
- AND so.company in ({0})
- AND so.docstatus = 1 {1}
- """.format(
- ",".join(["%s"] * len(company_list)), conditions
- ),
- tuple(company_list),
- as_dict=1,
+ query = (
+ frappe.qb.from_(db_so)
+ .inner_join(db_so_item)
+ .on(db_so_item.parent == db_so.name)
+ .select(
+ db_so.name,
+ db_so.customer,
+ db_so.transaction_date,
+ db_so.territory,
+ db_so.project,
+ db_so.company,
+ db_so_item.item_code,
+ db_so_item.description,
+ db_so_item.qty,
+ db_so_item.uom,
+ db_so_item.base_rate,
+ db_so_item.base_amount,
+ db_so_item.delivered_qty,
+ (db_so_item.billed_amt * db_so.conversion_rate).as_("billed_amt"),
+ )
+ .where(db_so.docstatus == 1)
+ .where(db_so.company.isin(tuple(company_list)))
)
+ if filters.get("item_group"):
+ query = query.where(db_so_item.item_group == frappe.db.escape(filters.item_group))
+
+ if filters.get("from_date"):
+ query = query.where(db_so.transaction_date >= filters.from_date)
+
+ if filters.get("to_date"):
+ query = query.where(db_so.transaction_date <= filters.to_date)
+
+ if filters.get("item_code"):
+ query = query.where(db_so_item.item_group == frappe.db.escape(filters.item_code))
+
+ if filters.get("customer"):
+ query = query.where(db_so.customer == filters.customer)
+
+ return query.run(as_dict=1)
+
def get_chart_data(data):
item_wise_sales_map = {}