Merge pull request #28783 from nextchamp-saqib/hsn-wise-summary-report-fix
fix: hsn-wise summary is incorrect if an invoice has repeated item code
diff --git a/erpnext/regional/report/hsn_wise_summary_of_outward_supplies/hsn_wise_summary_of_outward_supplies.py b/erpnext/regional/report/hsn_wise_summary_of_outward_supplies/hsn_wise_summary_of_outward_supplies.py
index e03ad37..1c1335e 100644
--- a/erpnext/regional/report/hsn_wise_summary_of_outward_supplies/hsn_wise_summary_of_outward_supplies.py
+++ b/erpnext/regional/report/hsn_wise_summary_of_outward_supplies/hsn_wise_summary_of_outward_supplies.py
@@ -114,9 +114,11 @@
items = frappe.db.sql("""
select
- `tabSales Invoice Item`.name, `tabSales Invoice Item`.base_price_list_rate,
- `tabSales Invoice Item`.gst_hsn_code, `tabSales Invoice Item`.stock_qty,
- `tabSales Invoice Item`.stock_uom, `tabSales Invoice Item`.base_net_amount,
+ `tabSales Invoice Item`.gst_hsn_code,
+ `tabSales Invoice Item`.stock_uom,
+ sum(`tabSales Invoice Item`.stock_qty) as stock_qty,
+ sum(`tabSales Invoice Item`.base_net_amount) as base_net_amount,
+ sum(`tabSales Invoice Item`.base_price_list_rate) as base_price_list_rate,
`tabSales Invoice Item`.parent, `tabSales Invoice Item`.item_code,
`tabGST HSN Code`.description
from `tabSales Invoice`, `tabSales Invoice Item`, `tabGST HSN Code`
@@ -124,6 +126,8 @@
and `tabSales Invoice`.docstatus = 1
and `tabSales Invoice Item`.gst_hsn_code is not NULL
and `tabSales Invoice Item`.gst_hsn_code = `tabGST HSN Code`.name %s %s
+ group by
+ `tabSales Invoice Item`.parent, `tabSales Invoice Item`.item_code
""" % (conditions, match_conditions), filters, as_dict=1)
diff --git a/erpnext/regional/report/hsn_wise_summary_of_outward_supplies/test_hsn_wise_summary_of_outward_supplies.py b/erpnext/regional/report/hsn_wise_summary_of_outward_supplies/test_hsn_wise_summary_of_outward_supplies.py
new file mode 100644
index 0000000..86dc458
--- /dev/null
+++ b/erpnext/regional/report/hsn_wise_summary_of_outward_supplies/test_hsn_wise_summary_of_outward_supplies.py
@@ -0,0 +1,89 @@
+# Copyright (c) 2021, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+
+from unittest import TestCase
+
+import frappe
+
+from erpnext.accounts.doctype.sales_invoice.test_sales_invoice import create_sales_invoice
+from erpnext.regional.doctype.gstr_3b_report.test_gstr_3b_report import (
+ make_company as setup_company,
+)
+from erpnext.regional.doctype.gstr_3b_report.test_gstr_3b_report import (
+ make_customers as setup_customers,
+)
+from erpnext.regional.doctype.gstr_3b_report.test_gstr_3b_report import (
+ set_account_heads as setup_gst_settings,
+)
+from erpnext.regional.report.hsn_wise_summary_of_outward_supplies.hsn_wise_summary_of_outward_supplies import (
+ execute as run_report,
+)
+from erpnext.stock.doctype.item.test_item import make_item
+
+
+class TestHSNWiseSummaryReport(TestCase):
+ @classmethod
+ def setUpClass(cls):
+ setup_company()
+ setup_customers()
+ setup_gst_settings()
+ make_item("Golf Car", properties={ "gst_hsn_code": "999900" })
+
+ @classmethod
+ def tearDownClass(cls):
+ frappe.db.rollback()
+
+ def test_hsn_summary_for_invoice_with_duplicate_items(self):
+ si = create_sales_invoice(
+ company="_Test Company GST",
+ customer = "_Test GST Customer",
+ currency = "INR",
+ warehouse = "Finished Goods - _GST",
+ debit_to = "Debtors - _GST",
+ income_account = "Sales - _GST",
+ expense_account = "Cost of Goods Sold - _GST",
+ cost_center = "Main - _GST",
+ do_not_save=1
+ )
+
+ si.items = []
+ si.append("items", {
+ "item_code": "Golf Car",
+ "gst_hsn_code": "999900",
+ "qty": "1",
+ "rate": "120",
+ "cost_center": "Main - _GST"
+ })
+ si.append("items", {
+ "item_code": "Golf Car",
+ "gst_hsn_code": "999900",
+ "qty": "1",
+ "rate": "140",
+ "cost_center": "Main - _GST"
+ })
+ si.append("taxes", {
+ "charge_type": "On Net Total",
+ "account_head": "Output Tax IGST - _GST",
+ "cost_center": "Main - _GST",
+ "description": "IGST @ 18.0",
+ "rate": 18
+ })
+ si.posting_date = "2020-11-17"
+ si.submit()
+ si.reload()
+
+ [columns, data] = run_report(filters=frappe._dict({
+ "company": "_Test Company GST",
+ "gst_hsn_code": "999900",
+ "company_gstin": si.company_gstin,
+ "from_date": si.posting_date,
+ "to_date": si.posting_date
+ }))
+
+ filtered_rows = list(filter(lambda row: row['gst_hsn_code'] == "999900", data))
+ self.assertTrue(filtered_rows)
+
+ hsn_row = filtered_rows[0]
+ self.assertEquals(hsn_row['stock_qty'], 2.0)
+ self.assertEquals(hsn_row['total_amount'], 306.8)