Merge pull request #26480 from Anuja-pawar/sa-vat-report
feat(regional): South Africa VAT Audit Report
diff --git a/erpnext/accounts/doctype/south_africa_vat_account/__init__.py b/erpnext/accounts/doctype/south_africa_vat_account/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/accounts/doctype/south_africa_vat_account/__init__.py
diff --git a/erpnext/accounts/doctype/south_africa_vat_account/south_africa_vat_account.json b/erpnext/accounts/doctype/south_africa_vat_account/south_africa_vat_account.json
new file mode 100644
index 0000000..fa1aa7d
--- /dev/null
+++ b/erpnext/accounts/doctype/south_africa_vat_account/south_africa_vat_account.json
@@ -0,0 +1,34 @@
+{
+ "actions": [],
+ "autoname": "account",
+ "creation": "2021-07-08 22:04:24.634967",
+ "doctype": "DocType",
+ "editable_grid": 1,
+ "engine": "InnoDB",
+ "field_order": [
+ "account"
+ ],
+ "fields": [
+ {
+ "allow_in_quick_entry": 1,
+ "fieldname": "account",
+ "fieldtype": "Link",
+ "in_list_view": 1,
+ "in_preview": 1,
+ "label": "Account",
+ "options": "Account"
+ }
+ ],
+ "index_web_pages_for_search": 1,
+ "istable": 1,
+ "links": [],
+ "modified": "2021-07-08 22:35:33.202911",
+ "modified_by": "Administrator",
+ "module": "Accounts",
+ "name": "South Africa VAT Account",
+ "owner": "Administrator",
+ "permissions": [],
+ "sort_field": "modified",
+ "sort_order": "DESC",
+ "track_changes": 1
+}
\ No newline at end of file
diff --git a/erpnext/accounts/doctype/south_africa_vat_account/south_africa_vat_account.py b/erpnext/accounts/doctype/south_africa_vat_account/south_africa_vat_account.py
new file mode 100644
index 0000000..4bd8c65
--- /dev/null
+++ b/erpnext/accounts/doctype/south_africa_vat_account/south_africa_vat_account.py
@@ -0,0 +1,8 @@
+# Copyright (c) 2021, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+# import frappe
+from frappe.model.document import Document
+
+class SouthAfricaVATAccount(Document):
+ pass
diff --git a/erpnext/patches.txt b/erpnext/patches.txt
index da96d95..04f05ed 100644
--- a/erpnext/patches.txt
+++ b/erpnext/patches.txt
@@ -296,4 +296,5 @@
erpnext.patches.v13_0.update_amt_in_work_order_required_items
erpnext.patches.v13_0.update_export_type_for_gst
erpnext.patches.v13_0.update_tds_check_field #3
-erpnext.patches.v13_0.shopify_deprecation_warning
\ No newline at end of file
+erpnext.patches.v13_0.add_custom_field_for_south_africa
+erpnext.patches.v13_0.shopify_deprecation_warning
diff --git a/erpnext/patches/v13_0/add_custom_field_for_south_africa.py b/erpnext/patches/v13_0/add_custom_field_for_south_africa.py
new file mode 100644
index 0000000..f882fde
--- /dev/null
+++ b/erpnext/patches/v13_0/add_custom_field_for_south_africa.py
@@ -0,0 +1,13 @@
+# Copyright (c) 2020, Frappe and Contributors
+# License: GNU General Public License v3. See license.txt
+
+from __future__ import unicode_literals
+import frappe
+from erpnext.regional.south_africa.setup import make_custom_fields
+
+def execute():
+ company = frappe.get_all('Company', filters = {'country': 'South Africa'})
+ if not company:
+ return
+
+ make_custom_fields()
diff --git a/erpnext/regional/doctype/south_africa_vat_settings/__init__.py b/erpnext/regional/doctype/south_africa_vat_settings/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/regional/doctype/south_africa_vat_settings/__init__.py
diff --git a/erpnext/regional/doctype/south_africa_vat_settings/south_africa_vat_settings.js b/erpnext/regional/doctype/south_africa_vat_settings/south_africa_vat_settings.js
new file mode 100644
index 0000000..e37a61a
--- /dev/null
+++ b/erpnext/regional/doctype/south_africa_vat_settings/south_africa_vat_settings.js
@@ -0,0 +1,23 @@
+// Copyright (c) 2021, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+
+frappe.ui.form.on('South Africa VAT Settings', {
+ refresh: function(frm) {
+ frm.set_query("company", function() {
+ return {
+ filters: {
+ country: "South Africa",
+ }
+ };
+ });
+ frm.set_query("account", "vat_accounts", function() {
+ return {
+ filters: {
+ company: frm.doc.company,
+ account_type: "Tax",
+ is_group: 0
+ }
+ };
+ });
+ }
+});
diff --git a/erpnext/regional/doctype/south_africa_vat_settings/south_africa_vat_settings.json b/erpnext/regional/doctype/south_africa_vat_settings/south_africa_vat_settings.json
new file mode 100644
index 0000000..8a51829
--- /dev/null
+++ b/erpnext/regional/doctype/south_africa_vat_settings/south_africa_vat_settings.json
@@ -0,0 +1,76 @@
+{
+ "actions": [],
+ "autoname": "field:company",
+ "creation": "2021-07-08 22:34:33.668015",
+ "doctype": "DocType",
+ "editable_grid": 1,
+ "engine": "InnoDB",
+ "field_order": [
+ "company",
+ "vat_accounts"
+ ],
+ "fields": [
+ {
+ "fieldname": "company",
+ "fieldtype": "Link",
+ "in_list_view": 1,
+ "label": "Company",
+ "options": "Company",
+ "reqd": 1,
+ "unique": 1
+ },
+ {
+ "fieldname": "vat_accounts",
+ "fieldtype": "Table",
+ "label": "VAT Accounts",
+ "options": "South Africa VAT Account",
+ "reqd": 1
+ }
+ ],
+ "index_web_pages_for_search": 1,
+ "links": [],
+ "modified": "2021-07-14 02:17:52.476762",
+ "modified_by": "Administrator",
+ "module": "Regional",
+ "name": "South Africa VAT Settings",
+ "owner": "Administrator",
+ "permissions": [
+ {
+ "create": 1,
+ "delete": 1,
+ "email": 1,
+ "export": 1,
+ "print": 1,
+ "read": 1,
+ "report": 1,
+ "role": "Accounts Manager",
+ "share": 1,
+ "write": 1
+ },
+ {
+ "create": 1,
+ "delete": 1,
+ "email": 1,
+ "export": 1,
+ "print": 1,
+ "read": 1,
+ "report": 1,
+ "role": "Accounts User",
+ "share": 1,
+ "write": 1
+ },
+ {
+ "email": 1,
+ "export": 1,
+ "print": 1,
+ "read": 1,
+ "report": 1,
+ "role": "Auditor",
+ "share": 1
+ }
+ ],
+ "quick_entry": 1,
+ "sort_field": "modified",
+ "sort_order": "DESC",
+ "track_changes": 1
+}
\ No newline at end of file
diff --git a/erpnext/regional/doctype/south_africa_vat_settings/south_africa_vat_settings.py b/erpnext/regional/doctype/south_africa_vat_settings/south_africa_vat_settings.py
new file mode 100644
index 0000000..d74154b
--- /dev/null
+++ b/erpnext/regional/doctype/south_africa_vat_settings/south_africa_vat_settings.py
@@ -0,0 +1,8 @@
+# Copyright (c) 2021, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+# import frappe
+from frappe.model.document import Document
+
+class SouthAfricaVATSettings(Document):
+ pass
diff --git a/erpnext/regional/doctype/south_africa_vat_settings/test_south_africa_vat_settings.py b/erpnext/regional/doctype/south_africa_vat_settings/test_south_africa_vat_settings.py
new file mode 100644
index 0000000..1c36652
--- /dev/null
+++ b/erpnext/regional/doctype/south_africa_vat_settings/test_south_africa_vat_settings.py
@@ -0,0 +1,8 @@
+# Copyright (c) 2021, Frappe Technologies Pvt. Ltd. and Contributors
+# See license.txt
+
+# import frappe
+import unittest
+
+class TestSouthAfricaVATSettings(unittest.TestCase):
+ pass
diff --git a/erpnext/regional/report/vat_audit_report/__init__.py b/erpnext/regional/report/vat_audit_report/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/regional/report/vat_audit_report/__init__.py
diff --git a/erpnext/regional/report/vat_audit_report/vat_audit_report.js b/erpnext/regional/report/vat_audit_report/vat_audit_report.js
new file mode 100644
index 0000000..39ef9b5
--- /dev/null
+++ b/erpnext/regional/report/vat_audit_report/vat_audit_report.js
@@ -0,0 +1,31 @@
+// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+/* eslint-disable */
+
+frappe.query_reports["VAT Audit Report"] = {
+ "filters": [
+ {
+ "fieldname": "company",
+ "label": __("Company"),
+ "fieldtype": "Link",
+ "options": "Company",
+ "reqd": 1,
+ "default": frappe.defaults.get_user_default("Company")
+ },
+ {
+ "fieldname": "from_date",
+ "label": __("From Date"),
+ "fieldtype": "Date",
+ "reqd": 1,
+ "default": frappe.datetime.add_months(frappe.datetime.get_today(), -2),
+ "width": "80"
+ },
+ {
+ "fieldname": "to_date",
+ "label": __("To Date"),
+ "fieldtype": "Date",
+ "reqd": 1,
+ "default": frappe.datetime.get_today()
+ }
+ ]
+};
diff --git a/erpnext/regional/report/vat_audit_report/vat_audit_report.json b/erpnext/regional/report/vat_audit_report/vat_audit_report.json
new file mode 100644
index 0000000..8917e8f
--- /dev/null
+++ b/erpnext/regional/report/vat_audit_report/vat_audit_report.json
@@ -0,0 +1,32 @@
+{
+ "add_total_row": 0,
+ "columns": [],
+ "creation": "2021-07-09 11:07:43.473518",
+ "disable_prepared_report": 0,
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "filters": [],
+ "idx": 0,
+ "is_standard": "Yes",
+ "modified": "2021-07-09 11:07:43.473518",
+ "modified_by": "Administrator",
+ "module": "Regional",
+ "name": "VAT Audit Report",
+ "owner": "Administrator",
+ "prepared_report": 0,
+ "ref_doctype": "GL Entry",
+ "report_name": "VAT Audit Report",
+ "report_type": "Script Report",
+ "roles": [
+ {
+ "role": "Accounts User"
+ },
+ {
+ "role": "Accounts Manager"
+ },
+ {
+ "role": "Auditor"
+ }
+ ]
+}
\ No newline at end of file
diff --git a/erpnext/regional/report/vat_audit_report/vat_audit_report.py b/erpnext/regional/report/vat_audit_report/vat_audit_report.py
new file mode 100644
index 0000000..f45ba01
--- /dev/null
+++ b/erpnext/regional/report/vat_audit_report/vat_audit_report.py
@@ -0,0 +1,253 @@
+# Copyright (c) 2013, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+from __future__ import unicode_literals
+import frappe
+import json
+from frappe import _
+from frappe.utils import formatdate
+
+def execute(filters=None):
+ return VATAuditReport(filters).run()
+
+class VATAuditReport(object):
+
+ def __init__(self, filters=None):
+ self.filters = frappe._dict(filters or {})
+ self.columns = []
+ self.data = []
+ self.doctypes = ["Purchase Invoice", "Sales Invoice"]
+
+ def run(self):
+ self.get_sa_vat_accounts()
+ self.get_columns()
+ for doctype in self.doctypes:
+ self.select_columns = """
+ name as voucher_no,
+ posting_date, remarks"""
+ columns = ", supplier as party, credit_to as account" if doctype=="Purchase Invoice" \
+ else ", customer as party, debit_to as account"
+ self.select_columns += columns
+
+ self.get_invoice_data(doctype)
+
+ if self.invoices:
+ self.get_invoice_items(doctype)
+ self.get_items_based_on_tax_rate(doctype)
+ self.get_data(doctype)
+
+ return self.columns, self.data
+
+ def get_sa_vat_accounts(self):
+ self.sa_vat_accounts = frappe.get_list("South Africa VAT Account",
+ filters = {"parent": self.filters.company}, pluck="account")
+ if not self.sa_vat_accounts and not frappe.flags.in_test and not frappe.flags.in_migrate:
+ frappe.throw(_("Please set VAT Accounts in South Africa VAT Settings"))
+
+ def get_invoice_data(self, doctype):
+ conditions = self.get_conditions()
+ self.invoices = frappe._dict()
+
+ invoice_data = frappe.db.sql("""
+ SELECT
+ {select_columns}
+ FROM
+ `tab{doctype}`
+ WHERE
+ docstatus = 1 {where_conditions}
+ and is_opening = "No"
+ ORDER BY
+ posting_date DESC
+ """.format(select_columns=self.select_columns, doctype=doctype,
+ where_conditions=conditions), self.filters, as_dict=1)
+
+ for d in invoice_data:
+ self.invoices.setdefault(d.voucher_no, d)
+
+ def get_invoice_items(self, doctype):
+ self.invoice_items = frappe._dict()
+
+ items = frappe.db.sql("""
+ SELECT
+ item_code, parent, taxable_value, base_net_amount, is_zero_rated
+ FROM
+ `tab%s Item`
+ WHERE
+ parent in (%s)
+ """ % (doctype, ", ".join(["%s"]*len(self.invoices))), tuple(self.invoices), as_dict=1)
+ for d in items:
+ if d.item_code not in self.invoice_items.get(d.parent, {}):
+ self.invoice_items.setdefault(d.parent, {}).setdefault(d.item_code, {
+ 'net_amount': 0.0})
+ self.invoice_items[d.parent][d.item_code]['net_amount'] += d.get('taxable_value', 0) or d.get('base_net_amount', 0)
+ self.invoice_items[d.parent][d.item_code]['is_zero_rated'] = d.is_zero_rated
+
+ def get_items_based_on_tax_rate(self, doctype):
+ self.items_based_on_tax_rate = frappe._dict()
+ self.item_tax_rate = frappe._dict()
+ self.tax_doctype = "Purchase Taxes and Charges" if doctype=="Purchase Invoice" \
+ else "Sales Taxes and Charges"
+
+ self.tax_details = frappe.db.sql("""
+ SELECT
+ parent, account_head, item_wise_tax_detail, base_tax_amount_after_discount_amount
+ FROM
+ `tab%s`
+ WHERE
+ parenttype = %s and docstatus = 1
+ and parent in (%s)
+ ORDER BY
+ account_head
+ """ % (self.tax_doctype, "%s", ", ".join(["%s"]*len(self.invoices.keys()))),
+ tuple([doctype] + list(self.invoices.keys())))
+
+ for parent, account, item_wise_tax_detail, tax_amount in self.tax_details:
+ if item_wise_tax_detail:
+ try:
+ if account in self.sa_vat_accounts:
+ item_wise_tax_detail = json.loads(item_wise_tax_detail)
+ else:
+ continue
+ for item_code, taxes in item_wise_tax_detail.items():
+ is_zero_rated = self.invoice_items.get(parent).get(item_code).get("is_zero_rated")
+ #to skip items with non-zero tax rate in multiple rows
+ if taxes[0] == 0 and not is_zero_rated:
+ continue
+ tax_rate, item_amount_map = self.get_item_amount_map(parent, item_code, taxes)
+
+ if tax_rate is not None:
+ 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
+
+ def get_item_amount_map(self, parent, item_code, taxes):
+ net_amount = self.invoice_items.get(parent).get(item_code).get("net_amount")
+ tax_rate = taxes[0]
+ tax_amount = taxes[1]
+ gross_amount = net_amount + tax_amount
+ item_amount_map = self.item_tax_rate.setdefault(parent, {}) \
+ .setdefault(item_code, [])
+ amount_dict = {
+ "tax_rate": tax_rate,
+ "gross_amount": gross_amount,
+ "tax_amount": tax_amount,
+ "net_amount": net_amount
+ }
+ item_amount_map.append(amount_dict)
+
+ return tax_rate, item_amount_map
+
+ def get_conditions(self):
+ conditions = ""
+ 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
+
+ def get_data(self, doctype):
+ consolidated_data = self.get_consolidated_data(doctype)
+ section_name = _("Purchases") if doctype == "Purchase Invoice" else _("Sales")
+
+ for rate, section in consolidated_data.items():
+ rate = int(rate)
+ label = frappe.bold(section_name + "- " + "Rate" + " " + str(rate) + "%")
+ section_head = {"posting_date": label}
+ total_gross = total_tax = total_net = 0
+ self.data.append(section_head)
+ for row in section.get("data"):
+ self.data.append(row)
+ total_gross += row["gross_amount"]
+ total_tax += row["tax_amount"]
+ total_net += row["net_amount"]
+
+ total = {
+ "posting_date": frappe.bold(_("Total")),
+ "gross_amount": total_gross,
+ "tax_amount": total_tax,
+ "net_amount": total_net,
+ "bold":1
+ }
+ self.data.append(total)
+ self.data.append({})
+
+ def get_consolidated_data(self, doctype):
+ consolidated_data_map={}
+ for inv, inv_data in self.invoices.items():
+ if self.items_based_on_tax_rate.get(inv):
+ for rate, items in self.items_based_on_tax_rate.get(inv).items():
+ consolidated_data_map.setdefault(rate, {"data": []})
+ for item in items:
+ row = {}
+ item_details = self.item_tax_rate.get(inv).get(item)
+ row["account"] = inv_data.get("account")
+ row["posting_date"] = formatdate(inv_data.get("posting_date"), "dd-mm-yyyy")
+ row["voucher_type"] = doctype
+ row["voucher_no"] = inv
+ row["remarks"] = inv_data.get("remarks")
+ row["gross_amount"]= item_details[0].get("gross_amount")
+ row["tax_amount"]= item_details[0].get("tax_amount")
+ row["net_amount"]= item_details[0].get("net_amount")
+ consolidated_data_map[rate]["data"].append(row)
+
+ return consolidated_data_map
+
+ def get_columns(self):
+ self.columns = [
+ {
+ "fieldname": "posting_date",
+ "label": "Posting Date",
+ "fieldtype": "Data",
+ "width": 200
+ },
+ {
+ "fieldname": "account",
+ "label": "Account",
+ "fieldtype": "Link",
+ "options": "Account",
+ "width": 150
+ },
+ {
+ "fieldname": "voucher_type",
+ "label": "Voucher Type",
+ "fieldtype": "Data",
+ "width": 140,
+ "hidden": 1
+ },
+ {
+ "fieldname": "voucher_no",
+ "label": "Reference",
+ "fieldtype": "Dynamic Link",
+ "options": "voucher_type",
+ "width": 150
+ },
+ {
+ "fieldname": "remarks",
+ "label": "Details",
+ "fieldtype": "Data",
+ "width": 150
+ },
+ {
+ "fieldname": "net_amount",
+ "label": "Net Amount",
+ "fieldtype": "Currency",
+ "width": 150
+ },
+ {
+ "fieldname": "tax_amount",
+ "label": "Tax Amount",
+ "fieldtype": "Currency",
+ "width": 150
+ },
+ {
+ "fieldname": "gross_amount",
+ "label": "Gross Amount",
+ "fieldtype": "Currency",
+ "width": 150
+ },
+ ]
diff --git a/erpnext/regional/south_africa/__init__.py b/erpnext/regional/south_africa/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/regional/south_africa/__init__.py
diff --git a/erpnext/regional/south_africa/setup.py b/erpnext/regional/south_africa/setup.py
new file mode 100644
index 0000000..ac783b8
--- /dev/null
+++ b/erpnext/regional/south_africa/setup.py
@@ -0,0 +1,36 @@
+# Copyright (c) 2015, Frappe Technologies Pvt. Ltd. and Contributors
+# License: GNU General Public License v3. See license.txt
+
+from __future__ import unicode_literals
+
+# import frappe, os, json
+from frappe.custom.doctype.custom_field.custom_field import create_custom_fields
+from frappe.permissions import add_permission, update_permission_property
+
+def setup(company=None, patch=True):
+ add_permissions()
+
+def make_custom_fields(update=True):
+ is_zero_rated = dict(fieldname='is_zero_rated', label='Is Zero Rated',
+ fieldtype='Check', fetch_from='item_code.is_zero_rated',
+ insert_after='description', print_hide=1)
+ custom_fields = {
+ 'Item': [
+ dict(fieldname='is_zero_rated', label='Is Zero Rated',
+ fieldtype='Check', insert_after='item_group',
+ print_hide=1)
+ ],
+ 'Sales Invoice Item': is_zero_rated,
+ 'Purchase Invoice Item': is_zero_rated
+ }
+
+ create_custom_fields(custom_fields, update=update)
+
+def add_permissions():
+ """Add Permissions for South Africa VAT Settings and South Africa VAT Account"""
+ for doctype in ('South Africa VAT Settings', 'South Africa VAT Account'):
+ add_permission(doctype, 'All', 0)
+ for role in ('Accounts Manager', 'Accounts User', 'System Manager'):
+ add_permission(doctype, role, 0)
+ update_permission_property(doctype, role, 0, 'write', 1)
+ update_permission_property(doctype, role, 0, 'create', 1)
\ No newline at end of file