TDS Monthly report & TDS Computation Summary report (#15204)
* tds computation report added
* tds computation logic added
* minor changes to commonify code
* tds payable monthly report added
* initial logic for monthly tds calculation added
* added field for supplier type and pan
* changes related to pan and entity type
* date filter fix and cleanup
diff --git a/erpnext/accounts/doctype/tax_withholding_category/tax_withholding_category.py b/erpnext/accounts/doctype/tax_withholding_category/tax_withholding_category.py
index 0dc4ecf..9a2c095 100644
--- a/erpnext/accounts/doctype/tax_withholding_category/tax_withholding_category.py
+++ b/erpnext/accounts/doctype/tax_withholding_category/tax_withholding_category.py
@@ -102,17 +102,25 @@
return tds_amount
-def get_advance_vouchers(supplier, fiscal_year):
+def get_advance_vouchers(supplier, fiscal_year=None, company=None, from_date=None, to_date=None):
+ condition = "fiscal_year=%s" % fiscal_year
+ if from_date and to_date:
+ condition = "company=%s and posting_date between %s and %s" % (company, from_date, to_date)
+
return frappe.db.sql_list("""
select distinct voucher_no
from `tabGL Entry`
- where party=%s and fiscal_year=%s and debit > 0
- """, (supplier, fiscal_year))
+ where party=%s and %s and debit > 0
+ """, (supplier, condition))
-def get_debit_note_amount(supplier, year_start_date, year_end_date):
+def get_debit_note_amount(supplier, year_start_date, year_end_date, company=None):
+ condition = ""
+ if company:
+ condition = " and company=%s " % company
+
return flt(frappe.db.sql("""
select abs(sum(net_total))
from `tabPurchase Invoice`
- where supplier=%s and is_return=1 and docstatus=1
+ where supplier=%s %s and is_return=1 and docstatus=1
and posting_date between %s and %s
- """, (supplier, year_start_date, year_end_date)))
\ No newline at end of file
+ """, (supplier, condition, year_start_date, year_end_date)))
diff --git a/erpnext/accounts/report/tds_computation_summary/__init__.py b/erpnext/accounts/report/tds_computation_summary/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/accounts/report/tds_computation_summary/__init__.py
diff --git a/erpnext/accounts/report/tds_computation_summary/tds_computation_summary.js b/erpnext/accounts/report/tds_computation_summary/tds_computation_summary.js
new file mode 100644
index 0000000..74669c4
--- /dev/null
+++ b/erpnext/accounts/report/tds_computation_summary/tds_computation_summary.js
@@ -0,0 +1,43 @@
+// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+/* eslint-disable */
+
+frappe.query_reports["TDS Computation Summary"] = {
+ "filters": [
+ {
+ "fieldname":"company",
+ "label": __("Company"),
+ "fieldtype": "Link",
+ "default": frappe.defaults.get_default('company')
+ },
+ {
+ "fieldname":"supplier",
+ "label": __("Supplier"),
+ "fieldtype": "Link",
+ "options": "Supplier",
+ "get_query": function() {
+ return {
+ "filters": {
+ "tax_withholding_category": ["!=",""],
+ }
+ }
+ }
+ },
+ {
+ "fieldname":"from_date",
+ "label": __("From Date"),
+ "fieldtype": "Date",
+ "default": frappe.datetime.add_months(frappe.datetime.get_today(), -1),
+ "reqd": 1,
+ "width": "60px"
+ },
+ {
+ "fieldname":"to_date",
+ "label": __("To Date"),
+ "fieldtype": "Date",
+ "default": frappe.datetime.get_today(),
+ "reqd": 1,
+ "width": "60px"
+ }
+ ]
+}
diff --git a/erpnext/accounts/report/tds_computation_summary/tds_computation_summary.json b/erpnext/accounts/report/tds_computation_summary/tds_computation_summary.json
new file mode 100644
index 0000000..6082ed2
--- /dev/null
+++ b/erpnext/accounts/report/tds_computation_summary/tds_computation_summary.json
@@ -0,0 +1,33 @@
+{
+ "add_total_row": 0,
+ "creation": "2018-08-21 11:25:00.551823",
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "idx": 0,
+ "is_standard": "Yes",
+ "letter_head": "Gadgets International",
+ "modified": "2018-08-21 11:25:00.551823",
+ "modified_by": "Administrator",
+ "module": "Accounts",
+ "name": "TDS Computation Summary",
+ "owner": "Administrator",
+ "prepared_report": 0,
+ "ref_doctype": "Purchase Invoice",
+ "report_name": "TDS Computation Summary",
+ "report_type": "Script Report",
+ "roles": [
+ {
+ "role": "Purchase User"
+ },
+ {
+ "role": "Accounts Manager"
+ },
+ {
+ "role": "Accounts User"
+ },
+ {
+ "role": "Auditor"
+ }
+ ]
+}
\ No newline at end of file
diff --git a/erpnext/accounts/report/tds_computation_summary/tds_computation_summary.py b/erpnext/accounts/report/tds_computation_summary/tds_computation_summary.py
new file mode 100644
index 0000000..391287b
--- /dev/null
+++ b/erpnext/accounts/report/tds_computation_summary/tds_computation_summary.py
@@ -0,0 +1,135 @@
+import frappe
+from frappe import _
+from frappe.utils import flt
+from erpnext.accounts.utils import get_fiscal_year
+from erpnext.accounts.doctype.tax_withholding_category.tax_withholding_category \
+ import get_advance_vouchers, get_debit_note_amount
+
+def execute(filters=None):
+ validate_filters(filters)
+
+ columns = get_columns()
+ res = get_result(filters)
+
+ return columns, res
+
+def validate_filters(filters):
+ ''' Validate if dates are properly set and lie in the same fiscal year'''
+ if filters.from_date > filters.to_date:
+ frappe.throw(_("From Date must be before To Date"))
+
+ from_year = get_fiscal_year(filters.from_date)[0]
+ to_year = get_fiscal_year(filters.to_date)[0]
+ if from_year != to_year:
+ frappe.throw(_("From Date and To Date lie in different Fiscal Year"))
+
+ filters["fiscal_year"] = from_year
+
+def get_result(filters):
+ # if no supplier selected, fetch data for all tds applicable supplier
+ # else fetch relevant data for selected supplier
+ pan = "pan" if frappe.db.has_column("Supplier", "pan") else "tax_id"
+ fields = ["name", pan+" as pan", "tax_withholding_category", "supplier_type"]
+ if filters.supplier:
+ filters.supplier = frappe.db.get_list('Supplier',
+ {"name": filters.supplier}, fields)
+ else:
+ filters.supplier = frappe.db.get_list('Supplier',
+ {"tax_withholding_category": ["!=", ""]}, fields)
+
+ out = []
+ for supplier in filters.supplier:
+ tds = frappe.get_doc("Tax Withholding Category", supplier.tax_withholding_category)
+ rate = [d.tax_withholding_rate for d in tds.rates if d.fiscal_year == filters.fiscal_year][0]
+ account = [d.account for d in tds.accounts if d.company == filters.company][0]
+
+ total_invoiced_amount, tds_deducted = get_invoice_and_tds_amount(supplier.name, account,
+ filters.company, filters.from_date, filters.to_date)
+
+ if total_invoiced_amount or tds_deducted:
+ out.append([supplier.pan, supplier.name, tds.name, supplier.supplier_type,
+ rate, total_invoiced_amount, tds_deducted])
+
+ return out
+
+def get_invoice_and_tds_amount(supplier, account, company, from_date, to_date):
+ ''' calculate total invoice amount and total tds deducted for given supplier '''
+
+ entries = frappe.db.sql("""
+ select voucher_no, credit
+ from `tabGL Entry`
+ where party in (%s) and credit > 0
+ and company=%s and posting_date between %s and %s
+ """, (supplier, company, from_date, to_date), as_dict=1)
+
+ supplier_credit_amount = flt(sum([d.credit for d in entries]))
+
+ vouchers = [d.voucher_no for d in entries]
+ vouchers += get_advance_vouchers(supplier, company=company,
+ from_date=from_date, to_date=to_date)
+
+ tds_deducted = 0
+ if vouchers:
+ tds_deducted = flt(frappe.db.sql("""
+ select sum(credit)
+ from `tabGL Entry`
+ where account=%s and posting_date between %s and %s
+ and company=%s and credit > 0 and voucher_no in ({0})
+ """.format(', '.join(["'%s'" % d for d in vouchers])),
+ (account, from_date, to_date, company))[0][0])
+
+ debit_note_amount = get_debit_note_amount(supplier, from_date, to_date, company=company)
+
+ total_invoiced_amount = supplier_credit_amount + tds_deducted - debit_note_amount
+
+ return total_invoiced_amount, tds_deducted
+
+def get_columns():
+ columns = [
+ {
+ "label": _("PAN"),
+ "fieldname": "pan",
+ "fieldtype": "Data",
+ "width": 90
+ },
+ {
+ "label": _("Supplier"),
+ "options": "Supplier",
+ "fieldname": "supplier",
+ "fieldtype": "Link",
+ "width": 180
+ },
+ {
+ "label": _("Section Code"),
+ "options": "Tax Withholding Category",
+ "fieldname": "section_code",
+ "fieldtype": "Link",
+ "width": 180
+ },
+ {
+ "label": _("Entity Type"),
+ "fieldname": "entity_type",
+ "fieldtype": "Data",
+ "width": 180
+ },
+ {
+ "label": _("TDS Rate %"),
+ "fieldname": "tds_rate",
+ "fieldtype": "Float",
+ "width": 90
+ },
+ {
+ "label": _("Total Amount Credited"),
+ "fieldname": "total_amount_credited",
+ "fieldtype": "Float",
+ "width": 90
+ },
+ {
+ "label": _("Amount of TDS Deducted"),
+ "fieldname": "tds_deducted",
+ "fieldtype": "Float",
+ "width": 90
+ }
+ ]
+
+ return columns
diff --git a/erpnext/accounts/report/tds_payable_monthly/__init__.py b/erpnext/accounts/report/tds_payable_monthly/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/accounts/report/tds_payable_monthly/__init__.py
diff --git a/erpnext/accounts/report/tds_payable_monthly/tds_payable_monthly.js b/erpnext/accounts/report/tds_payable_monthly/tds_payable_monthly.js
new file mode 100644
index 0000000..232d053
--- /dev/null
+++ b/erpnext/accounts/report/tds_payable_monthly/tds_payable_monthly.js
@@ -0,0 +1,89 @@
+// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+/* eslint-disable */
+
+frappe.query_reports["TDS Payable Monthly"] = {
+ "filters": [
+ {
+ "fieldname":"company",
+ "label": __("Company"),
+ "fieldtype": "Link",
+ "default": frappe.defaults.get_default('company')
+ },
+ {
+ "fieldname":"supplier",
+ "label": __("Supplier"),
+ "fieldtype": "Link",
+ "options": "Supplier",
+ "get_query": function() {
+ return {
+ "filters": {
+ "tax_withholding_category": ["!=", ""],
+ }
+ }
+ },
+ on_change: function() {
+ frappe.query_report.set_filter_value("purchase_invoice", "");
+ frappe.query_report.refresh();
+ }
+ },
+ {
+ "fieldname":"purchase_invoice",
+ "label": __("Purchase Invoice"),
+ "fieldtype": "Link",
+ "options": "Purchase Invoice",
+ "get_query": function() {
+ return {
+ "filters": {
+ "name": ["in", frappe.query_report.invoices]
+ }
+ }
+ },
+ on_change: function() {
+ let supplier = frappe.query_report.get_filter_value('supplier');
+ if(!supplier) return; // return if no supplier selected
+
+ // filter invoices based on selected supplier
+ let invoices = [];
+ frappe.query_report.invoice_data.map(d => {
+ if(d.supplier==supplier)
+ invoices.push(d.name)
+ });
+ frappe.query_report.invoices = invoices;
+ frappe.query_report.refresh();
+ }
+ },
+ {
+ "fieldname":"from_date",
+ "label": __("From Date"),
+ "fieldtype": "Date",
+ "default": frappe.datetime.add_months(frappe.datetime.get_today(), -1),
+ "reqd": 1,
+ "width": "60px"
+ },
+ {
+ "fieldname":"to_date",
+ "label": __("To Date"),
+ "fieldtype": "Date",
+ "default": frappe.datetime.get_today(),
+ "reqd": 1,
+ "width": "60px"
+ }
+ ],
+
+ onload: function(report) {
+ // fetch all tds applied invoices
+ frappe.call({
+ "method": "erpnext.accounts.report.tds_payable_monthly.tds_payable_monthly.get_tds_invoices",
+ callback: function(r) {
+ let invoices = [];
+ r.message.map(d => {
+ invoices.push(d.name);
+ });
+
+ report["invoice_data"] = r.message;
+ report["invoices"] = invoices;
+ }
+ });
+ }
+}
diff --git a/erpnext/accounts/report/tds_payable_monthly/tds_payable_monthly.json b/erpnext/accounts/report/tds_payable_monthly/tds_payable_monthly.json
new file mode 100644
index 0000000..6a83272
--- /dev/null
+++ b/erpnext/accounts/report/tds_payable_monthly/tds_payable_monthly.json
@@ -0,0 +1,33 @@
+{
+ "add_total_row": 0,
+ "creation": "2018-08-21 11:32:30.874923",
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "idx": 0,
+ "is_standard": "Yes",
+ "letter_head": "Gadgets International",
+ "modified": "2018-08-21 11:33:40.804532",
+ "modified_by": "Administrator",
+ "module": "Accounts",
+ "name": "TDS Payable Monthly",
+ "owner": "Administrator",
+ "prepared_report": 0,
+ "ref_doctype": "Purchase Invoice",
+ "report_name": "TDS Payable Monthly",
+ "report_type": "Script Report",
+ "roles": [
+ {
+ "role": "Purchase User"
+ },
+ {
+ "role": "Accounts Manager"
+ },
+ {
+ "role": "Accounts User"
+ },
+ {
+ "role": "Auditor"
+ }
+ ]
+}
\ No newline at end of file
diff --git a/erpnext/accounts/report/tds_payable_monthly/tds_payable_monthly.py b/erpnext/accounts/report/tds_payable_monthly/tds_payable_monthly.py
new file mode 100644
index 0000000..0e6f0a2
--- /dev/null
+++ b/erpnext/accounts/report/tds_payable_monthly/tds_payable_monthly.py
@@ -0,0 +1,190 @@
+# Copyright (c) 2013, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+from __future__ import unicode_literals
+import frappe
+from frappe import _
+from frappe.utils import getdate
+
+def execute(filters=None):
+ filters["invoices"] = frappe.cache().hget("invoices", frappe.session.user)
+ validate_filters(filters)
+ set_filters(filters)
+
+ columns = get_columns()
+ res = get_result(filters)
+
+ return columns, res
+
+def validate_filters(filters):
+ ''' Validate if dates are properly set '''
+ if filters.from_date > filters.to_date:
+ frappe.throw(_("From Date must be before To Date"))
+
+def set_filters(filters):
+ invoices = []
+
+ if not filters["invoices"]:
+ filters["invoices"] = get_tds_invoices()
+ if filters.supplier and filters.purchase_invoice:
+ for d in filters["invoices"]:
+ if d.name == filters.purchase_invoice and d.supplier == filters.supplier:
+ invoices.append(d)
+ elif filters.supplier and not filters.purchase_invoice:
+ for d in filters["invoices"]:
+ if d.supplier == filters.supplier:
+ invoices.append(d)
+ elif filters.purchase_invoice and not filters.supplier:
+ for d in filters["invoices"]:
+ if d.name == filters.purchase_invoice:
+ invoices.append(d)
+
+ filters["invoices"] = invoices if invoices else filters["invoices"]
+
+def get_result(filters):
+ supplier_map, tds_docs = get_supplier_map(filters)
+ gle_map = get_gle_map(filters)
+
+ out = []
+ for d in gle_map:
+ tds_deducted, total_amount_credited = 0, 0
+ supplier = supplier_map[d]
+
+ tds_doc = tds_docs[supplier.tax_withholding_category]
+ account = [i.account for i in tds_doc.accounts if i.company == filters.company][0]
+
+ for k in gle_map[d]:
+ if k.party == supplier_map[d] and k.credit > 0:
+ total_amount_credited += k.credit
+ elif k.account == account and k.credit > 0:
+ tds_deducted = k.credit
+ total_amount_credited += k.credit
+
+ rate = [i.tax_withholding_rate for i in tds_doc.rates
+ if i.fiscal_year == gle_map[d][0].fiscal_year][0]
+
+ if getdate(filters.from_date) <= gle_map[d][0].posting_date \
+ and getdate(filters.to_date) >= gle_map[d][0].posting_date:
+ out.append([supplier.pan, supplier.name, tds_doc.name,
+ supplier.supplier_type, rate, total_amount_credited, tds_deducted,
+ gle_map[d][0].posting_date, "Purchase Invoice", d])
+
+ return out
+
+def get_supplier_map(filters):
+ # create a supplier_map of the form {"purchase_invoice": {supplier_name, pan, tds_name}}
+ # pre-fetch all distinct applicable tds docs
+ supplier_map, tds_docs = {}, {}
+ pan = "pan" if frappe.db.has_column("Supplier", "pan") else "tax_id"
+ supplier_detail = frappe.db.get_all('Supplier',
+ {"name": ["in", [d.supplier for d in filters["invoices"]]]},
+ ["tax_withholding_category", "name", pan+" as pan", "supplier_type"])
+
+ for d in filters["invoices"]:
+ supplier_map[d.get("name")] = [k for k in supplier_detail
+ if k.name == d.get("supplier")][0]
+
+ for d in supplier_detail:
+ if d.get("tax_withholding_category") not in tds_docs:
+ tds_docs[d.get("tax_withholding_category")] = \
+ frappe.get_doc("Tax Withholding Category", d.get("tax_withholding_category"))
+
+ return supplier_map, tds_docs
+
+def get_gle_map(filters):
+ # create gle_map of the form
+ # {"purchase_invoice": list of dict of all gle created for this invoice}
+ gle_map = {}
+ gle = frappe.db.get_all('GL Entry',\
+ {"voucher_no": ["in", [d.get("name") for d in filters["invoices"]]]},
+ ["fiscal_year", "credit", "debit", "account", "voucher_no", "posting_date"])
+
+ for d in gle:
+ if not d.voucher_no in gle_map:
+ gle_map[d.voucher_no] = [d]
+ else:
+ gle_map[d.voucher_no].append(d)
+
+ return gle_map
+
+def get_columns():
+ pan = "pan" if frappe.db.has_column("Supplier", "pan") else "tax_id"
+ columns = [
+ {
+ "label": _(frappe.unscrub(pan)),
+ "fieldname": pan,
+ "fieldtype": "Data",
+ "width": 90
+ },
+ {
+ "label": _("Supplier"),
+ "options": "Supplier",
+ "fieldname": "supplier",
+ "fieldtype": "Link",
+ "width": 180
+ },
+ {
+ "label": _("Section Code"),
+ "options": "Tax Withholding Category",
+ "fieldname": "section_code",
+ "fieldtype": "Link",
+ "width": 180
+ },
+ {
+ "label": _("Entity Type"),
+ "fieldname": "entity_type",
+ "fieldtype": "Data",
+ "width": 180
+ },
+ {
+ "label": _("TDS Rate %"),
+ "fieldname": "tds_rate",
+ "fieldtype": "Float",
+ "width": 90
+ },
+ {
+ "label": _("Total Amount Credited"),
+ "fieldname": "total_amount_credited",
+ "fieldtype": "Float",
+ "width": 90
+ },
+ {
+ "label": _("Amount of TDS Deducted"),
+ "fieldname": "tds_deducted",
+ "fieldtype": "Float",
+ "width": 90
+ },
+ {
+ "label": _("Date of Transaction"),
+ "fieldname": "transaction_date",
+ "fieldtype": "Date",
+ "width": 90
+ },
+ {
+ "label": _("Transaction Type"),
+ "fieldname": "transaction_type",
+ "width": 90
+ },
+ {
+ "label": _("Reference No."),
+ "fieldname": "ref_no",
+ "fieldtype": "Dynamic Link",
+ "options": "transaction_type",
+ "width": 90
+ }
+ ]
+
+ return columns
+
+@frappe.whitelist()
+def get_tds_invoices():
+ # fetch tds applicable supplier and fetch invoices for these suppliers
+ suppliers = [d.name for d in frappe.db.get_list("Supplier",
+ {"tax_withholding_category": ["!=", ""]}, ["name"])]
+
+ invoices = frappe.db.get_list("Purchase Invoice",
+ {"supplier": ["in", suppliers]}, ["name", "supplier"])
+
+ frappe.cache().hset("invoices", frappe.session.user, invoices)
+
+ return invoices
diff --git a/erpnext/buying/doctype/supplier/supplier.json b/erpnext/buying/doctype/supplier/supplier.json
index 0cadc34..5b095b0 100644
--- a/erpnext/buying/doctype/supplier/supplier.json
+++ b/erpnext/buying/doctype/supplier/supplier.json
@@ -384,6 +384,72 @@
"bold": 0,
"collapsible": 0,
"columns": 0,
+ "default": "Company",
+ "fieldname": "supplier_type",
+ "fieldtype": "Select",
+ "hidden": 0,
+ "ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
+ "in_filter": 0,
+ "in_global_search": 0,
+ "in_list_view": 0,
+ "in_standard_filter": 0,
+ "label": "Supplier Type",
+ "length": 0,
+ "no_copy": 0,
+ "options": "Company\nIndividual",
+ "permlevel": 0,
+ "precision": "",
+ "print_hide": 0,
+ "print_hide_if_no_value": 0,
+ "read_only": 0,
+ "remember_last_selected_value": 0,
+ "report_hide": 0,
+ "reqd": 1,
+ "search_index": 0,
+ "set_only_once": 0,
+ "translatable": 0,
+ "unique": 0
+ },
+ {
+ "allow_bulk_edit": 0,
+ "allow_in_quick_entry": 0,
+ "allow_on_submit": 0,
+ "bold": 0,
+ "collapsible": 0,
+ "columns": 0,
+ "fieldname": "pan",
+ "fieldtype": "Data",
+ "hidden": 0,
+ "ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
+ "in_filter": 0,
+ "in_global_search": 0,
+ "in_list_view": 0,
+ "in_standard_filter": 0,
+ "label": "PAN",
+ "length": 0,
+ "no_copy": 0,
+ "permlevel": 0,
+ "precision": "",
+ "print_hide": 0,
+ "print_hide_if_no_value": 0,
+ "read_only": 0,
+ "remember_last_selected_value": 0,
+ "report_hide": 0,
+ "reqd": 0,
+ "search_index": 0,
+ "set_only_once": 0,
+ "translatable": 0,
+ "unique": 0
+ },
+ {
+ "allow_bulk_edit": 0,
+ "allow_in_quick_entry": 0,
+ "allow_on_submit": 0,
+ "bold": 0,
+ "collapsible": 0,
+ "columns": 0,
"fieldname": "language",
"fieldtype": "Link",
"hidden": 0,
@@ -1364,7 +1430,7 @@
"issingle": 0,
"istable": 0,
"max_attachments": 0,
- "modified": "2018-08-29 06:25:52.313864",
+ "modified": "2018-09-07 08:48:57.719713",
"modified_by": "Administrator",
"module": "Buying",
"name": "Supplier",