Initial commit of Tax Detail report and report builder
diff --git a/erpnext/accounts/report/tax_detail/__init__.py b/erpnext/accounts/report/tax_detail/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/accounts/report/tax_detail/__init__.py
diff --git a/erpnext/accounts/report/tax_detail/tax_detail.js b/erpnext/accounts/report/tax_detail/tax_detail.js
new file mode 100644
index 0000000..1ac1140
--- /dev/null
+++ b/erpnext/accounts/report/tax_detail/tax_detail.js
@@ -0,0 +1,165 @@
+// Copyright (c) 2021, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+// Contributed by Case Solved and sponsored by Nulight Studios
+/* eslint-disable */
+
+frappe.query_reports["Tax Detail"] = {
+ "filters": [
+ {
+ "fieldname":"company",
+ "label": __("Company"),
+ "fieldtype": "Link",
+ "options": "Company",
+ "default": frappe.defaults.get_user_default("company"),
+ "reqd": 1
+ },
+ {
+ "fieldname":"from_date",
+ "label": __("From Date"),
+ "fieldtype": "Date",
+ "default": frappe.datetime.month_start(frappe.datetime.get_today()),
+ "reqd": 1,
+ "width": "60px"
+ },
+ {
+ "fieldname":"to_date",
+ "label": __("To Date"),
+ "fieldtype": "Date",
+ "default": frappe.datetime.month_end(frappe.datetime.get_today()),
+ "reqd": 1,
+ "width": "60px"
+ },
+ ],
+ onload: function(report) {
+ report.page.add_inner_button(__("New Report"), () => new_report(), __("Custom Report"));
+ report.page.add_inner_button(__("Load Report"), () => load_report(), __("Custom Report"));
+ load_page_report();
+ }
+};
+
+class TaxReport {
+ constructor() {
+ this.report = frappe.query_reports["Tax Detail"]
+ this.qr = frappe.query_report
+ this.page = frappe.query_report.page
+ this.create_controls()
+ }
+ save_report() {
+ frappe.call({
+ method:'erpnext.accounts.report.tax_detail.tax_detail.new_custom_report',
+ args: {'name': values.report_name},
+ freeze: true
+ }).then((r) => {
+ frappe.set_route("query-report", values.report_name);
+ });
+ }
+ create_controls() {
+ this.section_name = this.page.add_field({
+ label: 'Section',
+ fieldtype: 'Select',
+ fieldname: 'section_name',
+ change() {
+ this.taxreport.set_section()
+ }
+ });
+ this.new_section = this.page.add_field({
+ label: 'New Section',
+ fieldtype: 'Button',
+ fieldname: 'new_section'
+ });
+ this.delete_section = this.page.add_field({
+ label: 'Delete Section',
+ fieldtype: 'Button',
+ fieldname: 'delete_section'
+ });
+ this.page.add_field({
+ label: 'Filter',
+ fieldtype: 'Select',
+ fieldname: 'filter_index'
+ });
+ this.page.add_field({
+ label: 'Add Filter',
+ fieldtype: 'Button',
+ fieldname: 'add_filter'
+ });
+ this.page.add_field({
+ label: 'Delete Filter',
+ fieldtype: 'Button',
+ fieldname: 'delete_filter'
+ });
+ this.page.add_field({
+ label: 'Value Column',
+ fieldtype: 'Select',
+ fieldname: 'value_field',
+ });
+ this.page.add_field({
+ label: 'Save',
+ fieldtype: 'Button',
+ fieldname: 'save'
+ });
+ }
+}
+
+function get_reports(cb) {
+ frappe.call({
+ method: 'erpnext.accounts.report.tax_detail.tax_detail.get_custom_reports',
+ freeze: true
+ }).then((r) => {
+ cb(r.message);
+ })
+}
+
+function new_report() {
+ const dialog = new frappe.ui.Dialog({
+ title: __("New Report"),
+ fields: [
+ {
+ fieldname: 'report_name',
+ label: 'Report Name',
+ fieldtype: 'Data',
+ default: 'VAT Return'
+ }
+ ],
+ primary_action_label: __('Create'),
+ primary_action: function new_report_pa(values) {
+ frappe.call({
+ method:'erpnext.accounts.report.tax_detail.tax_detail.new_custom_report',
+ args: {'name': values.report_name},
+ freeze: true
+ }).then((r) => {
+ frappe.set_route("query-report", values.report_name);
+ });
+ dialog.hide();
+ }
+ });
+ dialog.show();
+}
+
+function load_page_report() {
+ if (frappe.query_report.report_name === 'Tax Detail') {
+ return;
+ }
+ this.taxreport = new TaxReport();
+}
+
+function load_report() {
+ get_reports(function load_report_cb(reports) {
+ const dialog = new frappe.ui.Dialog({
+ title: __("Load Report"),
+ fields: [
+ {
+ fieldname: 'report_name',
+ label: 'Report Name',
+ fieldtype: 'Select',
+ options: Object.keys(reports)
+ }
+ ],
+ primary_action_label: __('Load'),
+ primary_action: function load_report_pa(values) {
+ dialog.hide();
+ frappe.set_route("query-report", values.report_name);
+ }
+ });
+ dialog.show();
+ });
+}
diff --git a/erpnext/accounts/report/tax_detail/tax_detail.json b/erpnext/accounts/report/tax_detail/tax_detail.json
new file mode 100644
index 0000000..d52ffd0
--- /dev/null
+++ b/erpnext/accounts/report/tax_detail/tax_detail.json
@@ -0,0 +1,32 @@
+{
+ "add_total_row": 0,
+ "columns": [],
+ "creation": "2021-02-19 16:44:21.175113",
+ "disable_prepared_report": 0,
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "filters": [],
+ "idx": 0,
+ "is_standard": "Yes",
+ "modified": "2021-02-19 16:44:21.175113",
+ "modified_by": "Administrator",
+ "module": "Accounts",
+ "name": "Tax Detail",
+ "owner": "Administrator",
+ "prepared_report": 0,
+ "ref_doctype": "GL Entry",
+ "report_name": "Tax Detail",
+ "report_type": "Script Report",
+ "roles": [
+ {
+ "role": "Accounts User"
+ },
+ {
+ "role": "Accounts Manager"
+ },
+ {
+ "role": "Auditor"
+ }
+ ]
+}
\ No newline at end of file
diff --git a/erpnext/accounts/report/tax_detail/tax_detail.py b/erpnext/accounts/report/tax_detail/tax_detail.py
new file mode 100644
index 0000000..46e7ae0
--- /dev/null
+++ b/erpnext/accounts/report/tax_detail/tax_detail.py
@@ -0,0 +1,169 @@
+# Copyright (c) 2021, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+# Contributed by Case Solved and sponsored by Nulight Studios
+
+from __future__ import unicode_literals
+import frappe
+from frappe import _
+
+# field lists in multiple doctypes will be coalesced
+required_sql_fields = {
+ "GL Entry": ["posting_date", "voucher_type", "voucher_no", "account", "account_currency", "debit", "credit"],
+ "Account": ["account_type"],
+ ("Purchase Invoice", "Sales Invoice"): ["taxes_and_charges", "tax_category"],
+ ("Purchase Invoice Item", "Sales Invoice Item"): ["item_tax_template", "item_name", "base_net_amount", "item_tax_rate"],
+# "Journal Entry": ["total_amount_currency"],
+# "Journal Entry Account": ["debit_in_account_currency", "credit_in_account_currency"]
+}
+
+@frappe.whitelist()
+def get_required_fieldlist():
+ """For overriding the fieldlist from the client"""
+ return required_sql_fields
+
+def execute(filters=None, fieldlist=required_sql_fields):
+ if not filters:
+ return [], []
+
+ fieldstr = get_fieldstr(fieldlist)
+
+ gl_entries = frappe.db.sql("""
+ select {fieldstr}
+ from `tabGL Entry` ge
+ inner join `tabAccount` a on
+ ge.account=a.name and ge.company=a.company
+ left join `tabSales Invoice` si on
+ a.account_type='Tax' and ge.company=si.company and ge.voucher_type='Sales Invoice' and ge.voucher_no=si.name
+ left join `tabSales Invoice Item` sii on
+ si.name=sii.parent
+ left join `tabPurchase Invoice` pi on
+ a.account_type='Tax' and ge.company=pi.company and ge.voucher_type='Purchase Invoice' and ge.voucher_no=pi.name
+ left join `tabPurchase Invoice Item` pii on
+ pi.name=pii.parent
+/* left outer join `tabJournal Entry` je on
+ ge.voucher_no=je.name and ge.company=je.company
+ left outer join `tabJournal Entry Account` jea on
+ je.name=jea.parent and a.account_type='Tax' */
+ where (ge.voucher_type, ge.voucher_no) in (
+ select ge.voucher_type, ge.voucher_no
+ from `tabGL Entry` ge
+ join `tabAccount` a on ge.account=a.name and ge.company=a.company
+ where
+ a.account_type='Tax' and
+ ge.company=%(company)s and
+ ge.posting_date>=%(from_date)s and
+ ge.posting_date<=%(to_date)s
+ )
+ order by ge.posting_date, ge.voucher_no
+ """.format(fieldstr=fieldstr), filters, as_dict=1)
+
+ gl_entries = modify_report_data(gl_entries)
+
+ return get_columns(fieldlist), gl_entries
+
+
+abbrev = lambda dt: ''.join(l[0].lower() for l in dt.split(' ')) + '.'
+doclist = lambda dt, dfs: [abbrev(dt) + f for f in dfs]
+coalesce = lambda dts, dfs: ['coalesce(' + ', '.join(abbrev(dt) + f for dt in dts) + ') ' + f for f in dfs]
+
+def get_fieldstr(fieldlist):
+ fields = []
+ for doctypes, docfields in fieldlist.items():
+ if isinstance(doctypes, str):
+ fields += doclist(doctypes, docfields)
+ if isinstance(doctypes, tuple):
+ fields += coalesce(doctypes, docfields)
+ return ', '.join(fields)
+
+def get_columns(fieldlist):
+ columns = {}
+ for doctypes, docfields in fieldlist.items():
+ if isinstance(doctypes, str):
+ doctypes = [doctypes]
+ for doctype in doctypes:
+ meta = frappe.get_meta(doctype)
+ # get column field metadata from the db
+ fieldmeta = {}
+ for field in meta.get('fields'):
+ if field.fieldname in docfields:
+ fieldmeta[field.fieldname] = {
+ "label": _(field.label),
+ "fieldname": field.fieldname,
+ "fieldtype": field.fieldtype,
+ "options": field.options
+ }
+ # edit the columns to match the modified data
+ for field in docfields:
+ col = modify_report_columns(doctype, field, fieldmeta[field])
+ if col:
+ columns[col["fieldname"]] = col
+ # use of a dict ensures duplicate columns are removed
+ return list(columns.values())
+
+def modify_report_columns(doctype, field, column):
+ "Because data is rearranged into other columns"
+ if doctype in ["Sales Invoice Item", "Purchase Invoice Item"] and field == "item_tax_rate":
+ return None
+ if doctype == "Sales Invoice Item" and field == "base_net_amount":
+ column.update({"label": _("Credit Net Amount"), "fieldname": "credit_net_amount"})
+ if doctype == "Purchase Invoice Item" and field == "base_net_amount":
+ column.update({"label": _("Debit Net Amount"), "fieldname": "debit_net_amount"})
+ if field == "taxes_and_charges":
+ column.update({"label": _("Taxes and Charges Template")})
+ return column
+
+def modify_report_data(data):
+ import json
+ for line in data:
+ if line.account_type == "Tax" and line.item_tax_rate:
+ tax_rates = json.loads(line.item_tax_rate)
+ for account, rate in tax_rates.items():
+ if account == line.account:
+ if line.voucher_type == "Sales Invoice":
+ line.credit = line.base_net_amount * (rate / 100)
+ line.credit_net_amount = line.base_net_amount
+ if line.voucher_type == "Purchase Invoice":
+ line.debit = line.base_net_amount * (rate / 100)
+ line.debit_net_amount = line.base_net_amount
+ return data
+
+####### JS client utilities
+
+custom_report_dict = {
+ 'ref_doctype': 'GL Entry',
+ 'report_type': 'Custom Report',
+ 'reference_report': 'Tax Detail'
+}
+
+@frappe.whitelist()
+def get_custom_reports():
+ reports = frappe.get_list('Report',
+ filters = custom_report_dict,
+ fields = ['name', 'json'],
+ as_list=False
+ )
+ reports_dict = {rep.pop('name'): rep for rep in reports}
+ # Prevent custom reports with the same name
+ reports_dict['Tax Detail'] = {'json': None}
+ return reports_dict
+
+@frappe.whitelist()
+def new_custom_report(name=None):
+ if name == 'Tax Detail':
+ frappe.throw("The parent report cannot be overwritten.")
+ if not name:
+ frappe.throw("The report name must be supplied.")
+ doc = {
+ 'doctype': 'Report',
+ 'report_name': name,
+ 'is_standard': 'No',
+ 'module': 'Accounts'
+ }
+ doc.update(custom_report_dict)
+ doc = frappe.get_doc(doc)
+ doc.insert()
+ return True
+
+@frappe.whitelist()
+def save_custom_report(data):
+ return None