GSTR1 for B2B, B2CL and B2CS (#12459)
diff --git a/erpnext/regional/india/utils.py b/erpnext/regional/india/utils.py
index 8088bfc..58df053 100644
--- a/erpnext/regional/india/utils.py
+++ b/erpnext/regional/india/utils.py
@@ -1,5 +1,6 @@
import frappe, re
from frappe import _
+from frappe.utils import cstr
from erpnext.regional.india import states, state_numbers
from erpnext.controllers.taxes_and_totals import get_itemised_tax, get_itemised_taxable_amount
@@ -61,12 +62,10 @@
return hsn_tax, hsn_taxable_amount
def set_place_of_supply(doc, method):
- if not hasattr(doc, 'customer_gstin'):
- return
-
address_name = doc.shipping_address_name or doc.customer_address
- address = frappe.db.get_value("Address", address_name, ["gst_state", "gst_state_number"], as_dict=1)
- doc.place_of_supply = str(address.gst_state_number) + "-" + address.gst_state
+ if address_name:
+ address = frappe.db.get_value("Address", address_name, ["gst_state", "gst_state_number"], as_dict=1)
+ doc.place_of_supply = cstr(address.gst_state_number) + "-" + address.gst_state
# don't remove this function it is used in tests
def test_method():
diff --git a/erpnext/regional/report/gstr_1/gstr_1.py b/erpnext/regional/report/gstr_1/gstr_1.py
index 982c409..65b1b89 100644
--- a/erpnext/regional/report/gstr_1/gstr_1.py
+++ b/erpnext/regional/report/gstr_1/gstr_1.py
@@ -6,167 +6,299 @@
from frappe import _
def execute(filters=None):
- columns, data = get_columns(filters), get_data(filters)
- return columns, data
+ return Gstr1Report(filters).run()
-def get_columns(filters):
- return [
- "GSTIN/UIN of Recipient::150",
- "Receiver Name::120",
- "Invoice Number:Link/Sales Invoice:120",
- "Invoice date:Date:120",
- "Invoice Value:Currency:120",
- "Place of Supply::120",
- "Reverse Charge::120",
- "Invoice Type::120",
- "E-Commerce GSTIN::120",
- "Rate:Int:80",
- "Taxable Value:Currency:120",
- "Cess Amount:Currency:120"
- ]
+class Gstr1Report(object):
+ def __init__(self, filters=None):
+ self.filters = frappe._dict(filters or {})
+ self.customer_type = "Company" if self.filters.get("type_of_business") == "B2B" else "Individual"
+
+ def run(self):
+ self.get_columns()
+ self.get_data()
+ return self.columns, self.data
-def get_data(filters):
- gst_accounts = get_gst_accounts(filters)
- invoices = get_invoice_data(filters)
- invoice_items = get_invoice_items(invoices)
- items_based_on_tax_rate, invoice_cess = get_items_based_on_tax_rate(invoices.keys(), gst_accounts)
+ def get_data(self):
+ self.data = []
+ self.get_gst_accounts()
+ self.get_invoice_data()
- data = []
- for inv, items_based_on_rate in items_based_on_tax_rate.items():
- invoice_details = invoices.get(inv)
- for rate, items in items_based_on_rate.items():
- row = [
- invoice_details.customer_gstin,
- invoice_details.customer_name,
- inv,
- invoice_details.posting_date,
- invoice_details.base_rounded_total or invoice_details.base_grand_total,
- invoice_details.place_of_supply,
- invoice_details.reverse_charge,
- invoice_details.invoice_type,
- invoice_details.ecommerce_gstin,
- rate,
- sum([net_amount for item_code, net_amount in invoice_items.get(inv).items()
- if item_code in items]),
- invoice_cess.get(inv)
- ]
- data.append(row)
+ if not self.invoices: return
- return data
+ self.get_invoice_items()
+ self.get_items_based_on_tax_rate()
+ invoice_fields = [d["fieldname"] for d in self.invoice_columns]
-def get_gst_accounts(filters):
- gst_accounts = frappe._dict()
- gst_settings_accounts = frappe.get_list("GST Account",
- filters={"parent": "GST Settings", "company": filters.company},
- fields=["cgst_account", "sgst_account", "igst_account", "cess_account"])
- if not gst_settings_accounts:
- frappe.throw(_("Please set GST Accounts in GST Settings"))
+ for inv, items_based_on_rate in self.items_based_on_tax_rate.items():
+ invoice_details = self.invoices.get(inv)
+ for rate, items in items_based_on_rate.items():
+ row = []
+ for fieldname in invoice_fields:
+ if fieldname == "invoice_value":
+ row.append(invoice_details.base_rounded_total or invoice_details.base_grand_total)
+ else:
+ row.append(invoice_details.get(fieldname))
- for d in gst_settings_accounts:
- for acc, val in d.items():
- gst_accounts.setdefault(acc, []).append(val)
+ row += [rate,
+ sum([net_amount for item_code, net_amount in self.invoice_items.get(inv).items()
+ if item_code in items]),
+ self.invoice_cess.get(inv)
+ ]
- return gst_accounts
+ if self.filters.get("type_of_business") == "B2C Small":
+ row.append("E" if invoice_details.ecommerce_gstin else "OE")
-def get_invoice_data(filters):
- invoices = frappe._dict()
- conditions = get_conditions(filters)
- match_conditions = frappe.build_match_conditions("Sales Invoice")
+ self.data.append(row)
- if match_conditions:
- match_conditions = " and {0} ".format(match_conditions)
+ def get_invoice_data(self):
+ self.invoices = frappe._dict()
+ conditions = self.get_conditions()
- invoice_data = frappe.db.sql("""
- select
- `tabSales Invoice`.name,
- `tabSales Invoice`.customer_name,
- `tabSales Invoice`.posting_date,
- `tabSales Invoice`.base_grand_total,
- `tabSales Invoice`.base_rounded_total,
- `tabSales Invoice`.customer_gstin,
- `tabSales Invoice`.place_of_supply,
- `tabSales Invoice`.ecommerce_gstin,
- `tabSales Invoice`.reverse_charge,
- `tabSales Invoice`.invoice_type
- from `tabSales Invoice`
- where `tabSales Invoice`.docstatus = 1 %s %s
- order by `tabSales Invoice`.posting_date desc
- """ % (conditions, match_conditions), filters, as_dict=1)
+ invoice_data = frappe.db.sql("""
+ select
+ name as invoice_number,
+ customer_name,
+ posting_date,
+ base_grand_total,
+ base_rounded_total,
+ customer_gstin,
+ place_of_supply,
+ ecommerce_gstin,
+ reverse_charge,
+ invoice_type
+ from `tabSales Invoice`
+ where docstatus = 1 %s
+ order by posting_date desc
+ """ % (conditions), self.filters, as_dict=1)
- for d in invoice_data:
- invoices.setdefault(d.name, d)
- return invoices
+ for d in invoice_data:
+ self.invoices.setdefault(d.invoice_number, d)
-def get_conditions(filters):
- conditions = ""
+ def get_conditions(self):
+ conditions = ""
- for opts in (("company", " and company=%(company)s"),
- ("from_date", " and `tabSales Invoice`.posting_date>=%(from_date)s"),
- ("to_date", " and `tabSales Invoice`.posting_date<=%(to_date)s")):
- if filters.get(opts[0]):
- conditions += opts[1]
+ for opts in (("company", " and company=%(company)s"),
+ ("from_date", " and posting_date>=%(from_date)s"),
+ ("to_date", " and posting_date<=%(to_date)s")):
+ if self.filters.get(opts[0]):
+ conditions += opts[1]
- return conditions
+ customers = frappe.get_all("Customer", filters={"customer_type": self.customer_type})
+ conditions += " and customer in ('{0}')".format("', '".join([frappe.db.escape(c.name)
+ for c in customers]))
-def get_invoice_items(invoices):
- invoice_items = frappe._dict()
- items = frappe.db.sql("""
- select item_code, parent, base_net_amount
- from `tabSales Invoice Item`
- where parent in (%s)
- """ % (', '.join(['%s']*len(invoices))), tuple(invoices), as_dict=1)
+ if self.filters.get("type_of_business") == "B2C Large":
+ conditions += """ and SUBSTR(place_of_supply, 1, 2) != SUBSTR(company_gstin, 1, 2)
+ and grand_total > 250000"""
+ elif self.filters.get("type_of_business") == "B2C Small":
+ conditions += """ and (
+ SUBSTR(place_of_supply, 1, 2) = SUBSTR(company_gstin, 1, 2)
+ or grand_total <= 250000
+ )"""
- for d in items:
- invoice_items.setdefault(d.parent, {}).setdefault(d.item_code, d.base_net_amount)
- return invoice_items
+ return conditions
-def get_items_based_on_tax_rate(invoices, gst_accounts):
- tax_details = frappe.db.sql("""
- select
- parent, account_head, item_wise_tax_detail, base_tax_amount_after_discount_amount
- from `tabSales Taxes and Charges`
- where
- parenttype = 'Sales Invoice' and docstatus = 1
- and parent in (%s)
- and tax_amount_after_discount_amount > 0
- order by account_head
- """ % (', '.join(['%s']*len(invoices))), tuple(invoices))
+ def get_invoice_items(self):
+ self.invoice_items = frappe._dict()
+ items = frappe.db.sql("""
+ select item_code, parent, base_net_amount
+ from `tabSales Invoice Item`
+ where parent in (%s)
+ """ % (', '.join(['%s']*len(self.invoices))), tuple(self.invoices), as_dict=1)
- items_based_on_tax_rate = {}
- invoice_cess = frappe._dict()
- unidentified_gst_accounts = []
+ for d in items:
+ self.invoice_items.setdefault(d.parent, {}).setdefault(d.item_code, d.base_net_amount)
- for parent, account, item_wise_tax_detail, tax_amount in tax_details:
- if account in gst_accounts.cess_account:
- invoice_cess.setdefault(parent, tax_amount)
- else:
- if item_wise_tax_detail:
- try:
- item_wise_tax_detail = json.loads(item_wise_tax_detail)
- cgst_or_sgst = False
- if account in gst_accounts.cgst_account or account in gst_accounts.sgst_account:
- cgst_or_sgst = True
+ def get_items_based_on_tax_rate(self):
+ tax_details = frappe.db.sql("""
+ select
+ parent, account_head, item_wise_tax_detail, base_tax_amount_after_discount_amount
+ from `tabSales Taxes and Charges`
+ where
+ parenttype = 'Sales Invoice' and docstatus = 1
+ and parent in (%s)
+ and tax_amount_after_discount_amount > 0
+ order by account_head
+ """ % (', '.join(['%s']*len(self.invoices.keys()))), tuple(self.invoices.keys()))
- if not (cgst_or_sgst or account in gst_accounts.igst_account):
- if "gst" in account.lower() and account not in unidentified_gst_accounts:
- unidentified_gst_accounts.append(account)
+ self.items_based_on_tax_rate = {}
+ self.invoice_cess = frappe._dict()
+ unidentified_gst_accounts = []
+
+ for parent, account, item_wise_tax_detail, tax_amount in tax_details:
+ if account in self.gst_accounts.cess_account:
+ self.invoice_cess.setdefault(parent, tax_amount)
+ else:
+ if item_wise_tax_detail:
+ try:
+ item_wise_tax_detail = json.loads(item_wise_tax_detail)
+ cgst_or_sgst = False
+ if account in self.gst_accounts.cgst_account \
+ or account in self.gst_accounts.sgst_account:
+ cgst_or_sgst = True
+
+ if not (cgst_or_sgst or account in self.gst_accounts.igst_account):
+ if "gst" in account.lower() and account not in unidentified_gst_accounts:
+ unidentified_gst_accounts.append(account)
+ continue
+
+ for item_code, tax_amounts in item_wise_tax_detail.items():
+ tax_rate = tax_amounts[0]
+ if cgst_or_sgst:
+ tax_rate *= 2
+
+ rate_based_dict = self.items_based_on_tax_rate.setdefault(parent, {})\
+ .setdefault(tax_rate, [])
+ if item_code not in rate_based_dict:
+ rate_based_dict.append(item_code)
+
+ except ValueError:
continue
+ if unidentified_gst_accounts:
+ frappe.msgprint(_("Following accounts might be selected in GST Settings:")
+ + "<br>" + "<br>".join(unidentified_gst_accounts), alert=True)
- for item_code, tax_amounts in item_wise_tax_detail.items():
- tax_rate = tax_amounts[0]
- if cgst_or_sgst:
- tax_rate *= 2
+ def get_gst_accounts(self):
+ self.gst_accounts = frappe._dict()
+ gst_settings_accounts = frappe.get_list("GST Account",
+ filters={"parent": "GST Settings", "company": self.filters.company},
+ fields=["cgst_account", "sgst_account", "igst_account", "cess_account"])
- rate_based_dict = items_based_on_tax_rate.setdefault(parent, {})\
- .setdefault(tax_rate, [])
- if item_code not in rate_based_dict:
- rate_based_dict.append(item_code)
+ if not gst_settings_accounts:
+ frappe.throw(_("Please set GST Accounts in GST Settings"))
- except ValueError:
- continue
- if unidentified_gst_accounts:
- frappe.msgprint(_("Following accounts might be selected in GST Settings:")
- + "<br>" + "<br>".join(unidentified_gst_accounts), alert=True)
+ for d in gst_settings_accounts:
+ for acc, val in d.items():
+ self.gst_accounts.setdefault(acc, []).append(val)
- return items_based_on_tax_rate, invoice_cess
+ def get_columns(self):
+ self.tax_columns = [
+ {
+ "fieldname": "rate",
+ "label": "Rate",
+ "fieldtype": "Int",
+ "width": 60
+ },
+ {
+ "fieldname": "taxable_value",
+ "label": "Taxable Value",
+ "fieldtype": "Currency",
+ "width": 100
+ },
+ {
+ "fieldname": "cess_amount",
+ "label": "Cess Amount",
+ "fieldtype": "Currency",
+ "width": 100
+ }
+ ]
+ self.other_columns = []
+
+ if self.filters.get("type_of_business") == "B2B":
+ self.invoice_columns = [
+ {
+ "fieldname": "customer_gstin",
+ "label": "GSTIN/UIN of Recipient",
+ "fieldtype": "Data"
+ },
+ {
+ "fieldname": "customer_name",
+ "label": "Receiver Name",
+ "fieldtype": "Data"
+ },
+ {
+ "fieldname": "invoice_number",
+ "label": "Invoice Number",
+ "fieldtype": "Link",
+ "options": "Sales Invoice"
+ },
+ {
+ "fieldname": "posting_date",
+ "label": "Invoice date",
+ "fieldtype": "Date"
+ },
+ {
+ "fieldname": "invoice_value",
+ "label": "Invoice Value",
+ "fieldtype": "Currency"
+ },
+ {
+ "fieldname": "place_of_supply",
+ "label": "Place of Supply",
+ "fieldtype": "Data"
+ },
+ {
+ "fieldname": "reverse_charge",
+ "label": "Reverse Charge",
+ "fieldtype": "Data"
+ },
+ {
+ "fieldname": "invoice_type",
+ "label": "Invoice Type",
+ "fieldtype": "Data"
+ },
+ {
+ "fieldname": "ecommerce_gstin",
+ "label": "E-Commerce GSTIN",
+ "fieldtype": "Data"
+ }
+ ]
+ elif self.filters.get("type_of_business") == "B2C Large":
+ self.invoice_columns = [
+ {
+ "fieldname": "invoice_number",
+ "label": "Invoice Number",
+ "fieldtype": "Link",
+ "options": "Sales Invoice",
+ "width": 120
+ },
+ {
+ "fieldname": "posting_date",
+ "label": "Invoice date",
+ "fieldtype": "Date",
+ "width": 100
+ },
+ {
+ "fieldname": "invoice_value",
+ "label": "Invoice Value",
+ "fieldtype": "Currency",
+ "width": 100
+ },
+ {
+ "fieldname": "place_of_supply",
+ "label": "Place of Supply",
+ "fieldtype": "Data",
+ "width": 120
+ },
+ {
+ "fieldname": "ecommerce_gstin",
+ "label": "E-Commerce GSTIN",
+ "fieldtype": "Data",
+ "width": 130
+ }
+ ]
+ elif self.filters.get("type_of_business") == "B2C Small":
+ self.invoice_columns = [
+ {
+ "fieldname": "place_of_supply",
+ "label": "Place of Supply",
+ "fieldtype": "Data",
+ "width": 120
+ },
+ {
+ "fieldname": "ecommerce_gstin",
+ "label": "E-Commerce GSTIN",
+ "fieldtype": "Data",
+ "width": 130
+ }
+ ]
+ self.other_columns = [
+ {
+ "fieldname": "type",
+ "label": "Type",
+ "fieldtype": "Data",
+ "width": 50
+ }
+ ]
+ self.columns = self.invoice_columns + self.tax_columns + self.other_columns
\ No newline at end of file
diff --git a/erpnext/regional/report/gstr_1/utils.py b/erpnext/regional/report/gstr_1/utils.py
deleted file mode 100644
index e69de29..0000000
--- a/erpnext/regional/report/gstr_1/utils.py
+++ /dev/null