Merge pull request #17867 from shipmnts/develop_add_gstin_filters
fix: GSTIN filters in hsn wise summary and itemised reports
diff --git a/erpnext/accounts/report/item_wise_sales_register/item_wise_sales_register.py b/erpnext/accounts/report/item_wise_sales_register/item_wise_sales_register.py
index e8b19b4..5d3253a 100644
--- a/erpnext/accounts/report/item_wise_sales_register/item_wise_sales_register.py
+++ b/erpnext/accounts/report/item_wise_sales_register/item_wise_sales_register.py
@@ -102,7 +102,9 @@
("customer", " and `tabSales Invoice`.customer = %(customer)s"),
("item_code", " and `tabSales Invoice Item`.item_code = %(item_code)s"),
("from_date", " and `tabSales Invoice`.posting_date>=%(from_date)s"),
- ("to_date", " and `tabSales Invoice`.posting_date<=%(to_date)s")):
+ ("to_date", " and `tabSales Invoice`.posting_date<=%(to_date)s"),
+ ("company_gstin", " and `tabSales Invoice`.company_gstin = %(company_gstin)s"),
+ ("invoice_type", " and `tabSales Invoice`.invoice_type = %(invoice_type)s")):
if filters.get(opts[0]):
conditions += opts[1]
diff --git a/erpnext/regional/india/utils.py b/erpnext/regional/india/utils.py
index fc2fc96..8893cab 100644
--- a/erpnext/regional/india/utils.py
+++ b/erpnext/regional/india/utils.py
@@ -261,3 +261,19 @@
exemptions["monthly_house_rent"] = monthly_rent
exemptions["total_eligible_hra_exemption"] = eligible_hra
return exemptions
+
+@frappe.whitelist()
+def get_gstins_for_company(company):
+ company_gstins =[]
+ if company:
+ company_gstins = frappe.db.sql("""select
+ distinct `tabAddress`.gstin
+ from
+ `tabAddress`, `tabDynamic Link`
+ where
+ `tabDynamic Link`.parent = `tabAddress`.name and
+ `tabDynamic Link`.parenttype = 'Address' and
+ `tabDynamic Link`.link_doctype = 'Company' and
+ `tabDynamic Link`.link_name = '{0}'""".format(company))
+ return company_gstins
+
diff --git a/erpnext/regional/report/gst_itemised_sales_register/gst_itemised_sales_register.js b/erpnext/regional/report/gst_itemised_sales_register/gst_itemised_sales_register.js
index 1ece14b..3a0f0c9 100644
--- a/erpnext/regional/report/gst_itemised_sales_register/gst_itemised_sales_register.js
+++ b/erpnext/regional/report/gst_itemised_sales_register/gst_itemised_sales_register.js
@@ -3,5 +3,31 @@
/* eslint-disable */
{% include "erpnext/accounts/report/item_wise_sales_register/item_wise_sales_register.js" %}
+{% include "erpnext/regional/report/india_gst_common/india_gst_common.js" %}
-frappe.query_reports["GST Itemised Sales Register"] = frappe.query_reports["Item-wise Sales Register"]
\ No newline at end of file
+let filters = frappe.query_reports["Item-wise Sales Register"]["filters"];
+
+// Add GSTIN filter
+filters = filters.concat({
+ "fieldname":"company_gstin",
+ "label": __("Company GSTIN"),
+ "fieldtype": "Select",
+ "placeholder":"Company GSTIN",
+ "options": [""],
+ "width": "80"
+}, {
+ "fieldname":"invoice_type",
+ "label": __("Invoice Type"),
+ "fieldtype": "Select",
+ "placeholder":"Invoice Type",
+ "options": ["", "Regular", "SEZ", "Export", "Deemed Export"]
+});
+
+// Handle company on change
+for (var i = 0; i < filters.length; ++i) {
+ if (filters[i].fieldname === 'company') {
+ filters[i].on_change = fetch_gstins;
+ }
+}
+
+frappe.query_reports["GST Itemised Sales Register"] = { "filters": filters, "onload": fetch_gstins };
diff --git a/erpnext/regional/report/hsn_wise_summary_of_outward_supplies/hsn_wise_summary_of_outward_supplies.js b/erpnext/regional/report/hsn_wise_summary_of_outward_supplies/hsn_wise_summary_of_outward_supplies.js
index df0ef25..dcb81cb 100644
--- a/erpnext/regional/report/hsn_wise_summary_of_outward_supplies/hsn_wise_summary_of_outward_supplies.js
+++ b/erpnext/regional/report/hsn_wise_summary_of_outward_supplies/hsn_wise_summary_of_outward_supplies.js
@@ -2,6 +2,8 @@
// For license information, please see license.txt
/* eslint-disable */
+{% include "erpnext/regional/report/india_gst_common/india_gst_common.js" %}
+
frappe.query_reports["HSN-wise-summary of outward supplies"] = {
"filters": [
{
@@ -10,7 +12,8 @@
"fieldtype": "Link",
"options": "Company",
"reqd": 1,
- "default": frappe.defaults.get_user_default("Company")
+ "default": frappe.defaults.get_user_default("Company"),
+ "on_change": fetch_gstins
},
{
"fieldname":"gst_hsn_code",
@@ -18,6 +21,17 @@
"fieldtype": "Link",
"options": "GST HSN Code",
"width": "80"
+ },
+ {
+ "fieldname":"company_gstin",
+ "label": __("Company GSTIN"),
+ "fieldtype": "Select",
+ "placeholder":"Company GSTIN",
+ "options": [""],
+ "width": "80"
}
- ]
-}
+ ],
+ onload: (report) => {
+ fetch_gstins(report);
+ }
+};
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 165458c..e938e29 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
@@ -87,7 +87,8 @@
conditions = ""
for opts in (("company", " and company=%(company)s"),
- ("gst_hsn_code", " and gst_hsn_code=%(gst_hsn_code)s")):
+ ("gst_hsn_code", " and gst_hsn_code=%(gst_hsn_code)s"),
+ ("company_gstin", " and company_gstin=%(company_gstin)s")):
if filters.get(opts[0]):
conditions += opts[1]
@@ -193,7 +194,7 @@
add_column_index.append(i)
for row in data:
- if merged_hsn_dict.has_key(row[0]):
+ if row[0] in merged_hsn_dict:
to_add_row = merged_hsn_dict.get(row[0])
# add columns from the add_column_index table
diff --git a/erpnext/regional/report/india_gst_common/india_gst_common.js b/erpnext/regional/report/india_gst_common/india_gst_common.js
new file mode 100644
index 0000000..4960601
--- /dev/null
+++ b/erpnext/regional/report/india_gst_common/india_gst_common.js
@@ -0,0 +1,21 @@
+function fetch_gstins(report) {
+ var company_gstins = report.get_filter('company_gstin');
+ var company = report.get_filter_value('company');
+ if (company) {
+ frappe.call({
+ method:'erpnext.regional.india.utils.get_gstins_for_company',
+ async: false,
+ args: {
+ company: company
+ },
+ callback: function(r) {
+ r.message.unshift("");
+ company_gstins.df.options = r.message;
+ company_gstins.refresh();
+ }
+ });
+ } else {
+ company_gstins.df.options = [""];
+ company_gstins.refresh();
+ }
+}
\ No newline at end of file