feat: add invoice totals in tax withholding report (#36567)
* fix: add invoice totals in tax withholding report
* fix: naming series col in tax withholding report
* fix: tds computation summary cols
diff --git a/erpnext/accounts/report/tax_withholding_details/tax_withholding_details.js b/erpnext/accounts/report/tax_withholding_details/tax_withholding_details.js
index b66a555..8808165 100644
--- a/erpnext/accounts/report/tax_withholding_details/tax_withholding_details.js
+++ b/erpnext/accounts/report/tax_withholding_details/tax_withholding_details.js
@@ -33,7 +33,14 @@
frappe.throw(__("Please select Party Type first"));
}
return party_type;
- }
+ },
+ "get_query": function() {
+ return {
+ "filters": {
+ "tax_withholding_category": ["!=",""],
+ }
+ }
+ },
},
{
"fieldname":"from_date",
diff --git a/erpnext/accounts/report/tax_withholding_details/tax_withholding_details.py b/erpnext/accounts/report/tax_withholding_details/tax_withholding_details.py
index ddd049a..7d16661 100644
--- a/erpnext/accounts/report/tax_withholding_details/tax_withholding_details.py
+++ b/erpnext/accounts/report/tax_withholding_details/tax_withholding_details.py
@@ -7,19 +7,26 @@
def execute(filters=None):
+ if filters.get("party_type") == "Customer":
+ party_naming_by = frappe.db.get_single_value("Selling Settings", "cust_master_name")
+ else:
+ party_naming_by = frappe.db.get_single_value("Buying Settings", "supp_master_name")
+
+ filters.update({"naming_series": party_naming_by})
+
validate_filters(filters)
(
tds_docs,
tds_accounts,
tax_category_map,
journal_entry_party_map,
- invoice_net_total_map,
+ net_total_map,
) = get_tds_docs(filters)
columns = get_columns(filters)
res = get_result(
- filters, tds_docs, tds_accounts, tax_category_map, journal_entry_party_map, invoice_net_total_map
+ filters, tds_docs, tds_accounts, tax_category_map, journal_entry_party_map, net_total_map
)
return columns, res
@@ -31,7 +38,7 @@
def get_result(
- filters, tds_docs, tds_accounts, tax_category_map, journal_entry_party_map, invoice_net_total_map
+ filters, tds_docs, tds_accounts, tax_category_map, journal_entry_party_map, net_total_map
):
party_map = get_party_pan_map(filters.get("party_type"))
tax_rate_map = get_tax_rate_map(filters)
@@ -39,7 +46,7 @@
out = []
for name, details in gle_map.items():
- tax_amount, total_amount = 0, 0
+ tax_amount, total_amount, grand_total, base_total = 0, 0, 0, 0
tax_withholding_category = tax_category_map.get(name)
rate = tax_rate_map.get(tax_withholding_category)
@@ -60,8 +67,8 @@
if entry.account in tds_accounts:
tax_amount += entry.credit - entry.debit
- if invoice_net_total_map.get(name):
- total_amount = invoice_net_total_map.get(name)
+ if net_total_map.get(name):
+ total_amount, grand_total, base_total = net_total_map.get(name)
else:
total_amount += entry.credit
@@ -69,15 +76,13 @@
if party_map.get(party, {}).get("party_type") == "Supplier":
party_name = "supplier_name"
party_type = "supplier_type"
- table_name = "Supplier"
else:
party_name = "customer_name"
party_type = "customer_type"
- table_name = "Customer"
row = {
"pan"
- if frappe.db.has_column(table_name, "pan")
+ if frappe.db.has_column(filters.party_type, "pan")
else "tax_id": party_map.get(party, {}).get("pan"),
"party": party_map.get(party, {}).get("name"),
}
@@ -91,6 +96,8 @@
"entity_type": party_map.get(party, {}).get(party_type),
"rate": rate,
"total_amount": total_amount,
+ "grand_total": grand_total,
+ "base_total": base_total,
"tax_amount": tax_amount,
"transaction_date": posting_date,
"transaction_type": voucher_type,
@@ -144,9 +151,9 @@
def get_columns(filters):
- pan = "pan" if frappe.db.has_column("Supplier", "pan") else "tax_id"
+ pan = "pan" if frappe.db.has_column(filters.party_type, "pan") else "tax_id"
columns = [
- {"label": _(frappe.unscrub(pan)), "fieldname": pan, "fieldtype": "Data", "width": 90},
+ {"label": _(frappe.unscrub(pan)), "fieldname": pan, "fieldtype": "Data", "width": 60},
{
"label": _(filters.get("party_type")),
"fieldname": "party",
@@ -158,25 +165,30 @@
if filters.naming_series == "Naming Series":
columns.append(
- {"label": _("Party Name"), "fieldname": "party_name", "fieldtype": "Data", "width": 180}
+ {
+ "label": _(filters.party_type + " Name"),
+ "fieldname": "party_name",
+ "fieldtype": "Data",
+ "width": 180,
+ }
)
columns.extend(
[
{
+ "label": _("Date of Transaction"),
+ "fieldname": "transaction_date",
+ "fieldtype": "Date",
+ "width": 100,
+ },
+ {
"label": _("Section Code"),
"options": "Tax Withholding Category",
"fieldname": "section_code",
"fieldtype": "Link",
- "width": 180,
- },
- {"label": _("Entity Type"), "fieldname": "entity_type", "fieldtype": "Data", "width": 120},
- {
- "label": _("TDS Rate %") if filters.get("party_type") == "Supplier" else _("TCS Rate %"),
- "fieldname": "rate",
- "fieldtype": "Percent",
"width": 90,
},
+ {"label": _("Entity Type"), "fieldname": "entity_type", "fieldtype": "Data", "width": 100},
{
"label": _("Total Amount"),
"fieldname": "total_amount",
@@ -184,15 +196,27 @@
"width": 90,
},
{
- "label": _("TDS Amount") if filters.get("party_type") == "Supplier" else _("TCS Amount"),
+ "label": _("TDS Rate %") if filters.get("party_type") == "Supplier" else _("TCS Rate %"),
+ "fieldname": "rate",
+ "fieldtype": "Percent",
+ "width": 90,
+ },
+ {
+ "label": _("Tax Amount"),
"fieldname": "tax_amount",
"fieldtype": "Float",
"width": 90,
},
{
- "label": _("Date of Transaction"),
- "fieldname": "transaction_date",
- "fieldtype": "Date",
+ "label": _("Grand Total"),
+ "fieldname": "grand_total",
+ "fieldtype": "Float",
+ "width": 90,
+ },
+ {
+ "label": _("Base Total"),
+ "fieldname": "base_total",
+ "fieldtype": "Float",
"width": 90,
},
{"label": _("Transaction Type"), "fieldname": "transaction_type", "width": 100},
@@ -216,7 +240,7 @@
payment_entries = []
journal_entries = []
tax_category_map = frappe._dict()
- invoice_net_total_map = frappe._dict()
+ net_total_map = frappe._dict()
or_filters = frappe._dict()
journal_entry_party_map = frappe._dict()
bank_accounts = frappe.get_all("Account", {"is_group": 0, "account_type": "Bank"}, pluck="name")
@@ -260,13 +284,13 @@
tds_documents.append(d.voucher_no)
if purchase_invoices:
- get_doc_info(purchase_invoices, "Purchase Invoice", tax_category_map, invoice_net_total_map)
+ get_doc_info(purchase_invoices, "Purchase Invoice", tax_category_map, net_total_map)
if sales_invoices:
- get_doc_info(sales_invoices, "Sales Invoice", tax_category_map, invoice_net_total_map)
+ get_doc_info(sales_invoices, "Sales Invoice", tax_category_map, net_total_map)
if payment_entries:
- get_doc_info(payment_entries, "Payment Entry", tax_category_map)
+ get_doc_info(payment_entries, "Payment Entry", tax_category_map, net_total_map)
if journal_entries:
journal_entry_party_map = get_journal_entry_party_map(journal_entries)
@@ -277,7 +301,7 @@
tds_accounts,
tax_category_map,
journal_entry_party_map,
- invoice_net_total_map,
+ net_total_map,
)
@@ -295,11 +319,25 @@
return journal_entry_party_map
-def get_doc_info(vouchers, doctype, tax_category_map, invoice_net_total_map=None):
+def get_doc_info(vouchers, doctype, tax_category_map, net_total_map=None):
if doctype == "Purchase Invoice":
- fields = ["name", "tax_withholding_category", "base_tax_withholding_net_total"]
- if doctype == "Sales Invoice":
- fields = ["name", "base_net_total"]
+ fields = [
+ "name",
+ "tax_withholding_category",
+ "base_tax_withholding_net_total",
+ "grand_total",
+ "base_total",
+ ]
+ elif doctype == "Sales Invoice":
+ fields = ["name", "base_net_total", "grand_total", "base_total"]
+ elif doctype == "Payment Entry":
+ fields = [
+ "name",
+ "tax_withholding_category",
+ "paid_amount",
+ "paid_amount_after_tax",
+ "base_paid_amount",
+ ]
else:
fields = ["name", "tax_withholding_category"]
@@ -308,9 +346,15 @@
for entry in entries:
tax_category_map.update({entry.name: entry.tax_withholding_category})
if doctype == "Purchase Invoice":
- invoice_net_total_map.update({entry.name: entry.base_tax_withholding_net_total})
- if doctype == "Sales Invoice":
- invoice_net_total_map.update({entry.name: entry.base_net_total})
+ net_total_map.update(
+ {entry.name: [entry.base_tax_withholding_net_total, entry.grand_total, entry.base_total]}
+ )
+ elif doctype == "Sales Invoice":
+ net_total_map.update({entry.name: [entry.base_net_total, entry.grand_total, entry.base_total]})
+ elif doctype == "Payment Entry":
+ net_total_map.update(
+ {entry.name: [entry.paid_amount, entry.paid_amount_after_tax, entry.base_paid_amount]}
+ )
def get_tax_rate_map(filters):
diff --git a/erpnext/accounts/report/tds_computation_summary/tds_computation_summary.js b/erpnext/accounts/report/tds_computation_summary/tds_computation_summary.js
index d334846..a0be1b5 100644
--- a/erpnext/accounts/report/tds_computation_summary/tds_computation_summary.js
+++ b/erpnext/accounts/report/tds_computation_summary/tds_computation_summary.js
@@ -12,17 +12,35 @@
"default": frappe.defaults.get_default('company')
},
{
- "fieldname":"supplier",
- "label": __("Supplier"),
- "fieldtype": "Link",
- "options": "Supplier",
+ "fieldname":"party_type",
+ "label": __("Party Type"),
+ "fieldtype": "Select",
+ "options": ["Supplier", "Customer"],
+ "reqd": 1,
+ "default": "Supplier",
+ "on_change": function(){
+ frappe.query_report.set_filter_value("party", "");
+ }
+ },
+ {
+ "fieldname":"party",
+ "label": __("Party"),
+ "fieldtype": "Dynamic Link",
+ "get_options": function() {
+ var party_type = frappe.query_report.get_filter_value('party_type');
+ var party = frappe.query_report.get_filter_value('party');
+ if(party && !party_type) {
+ frappe.throw(__("Please select Party Type first"));
+ }
+ return party_type;
+ },
"get_query": function() {
return {
"filters": {
"tax_withholding_category": ["!=",""],
}
}
- }
+ },
},
{
"fieldname":"from_date",
diff --git a/erpnext/accounts/report/tds_computation_summary/tds_computation_summary.py b/erpnext/accounts/report/tds_computation_summary/tds_computation_summary.py
index c6aa21c..82f97f1 100644
--- a/erpnext/accounts/report/tds_computation_summary/tds_computation_summary.py
+++ b/erpnext/accounts/report/tds_computation_summary/tds_computation_summary.py
@@ -9,9 +9,14 @@
def execute(filters=None):
- validate_filters(filters)
+ if filters.get("party_type") == "Customer":
+ party_naming_by = frappe.db.get_single_value("Selling Settings", "cust_master_name")
+ else:
+ party_naming_by = frappe.db.get_single_value("Buying Settings", "supp_master_name")
- filters.naming_series = frappe.db.get_single_value("Buying Settings", "supp_master_name")
+ filters.update({"naming_series": party_naming_by})
+
+ validate_filters(filters)
columns = get_columns(filters)
(
@@ -25,7 +30,7 @@
res = get_result(
filters, tds_docs, tds_accounts, tax_category_map, journal_entry_party_map, invoice_total_map
)
- final_result = group_by_supplier_and_category(res)
+ final_result = group_by_party_and_category(res, filters)
return columns, final_result
@@ -43,60 +48,67 @@
filters["fiscal_year"] = from_year
-def group_by_supplier_and_category(data):
- supplier_category_wise_map = {}
+def group_by_party_and_category(data, filters):
+ party_category_wise_map = {}
for row in data:
- supplier_category_wise_map.setdefault(
- (row.get("supplier"), row.get("section_code")),
+ party_category_wise_map.setdefault(
+ (row.get("party"), row.get("section_code")),
{
"pan": row.get("pan"),
- "supplier": row.get("supplier"),
- "supplier_name": row.get("supplier_name"),
+ "tax_id": row.get("tax_id"),
+ "party": row.get("party"),
+ "party_name": row.get("party_name"),
"section_code": row.get("section_code"),
"entity_type": row.get("entity_type"),
- "tds_rate": row.get("tds_rate"),
- "total_amount_credited": 0.0,
- "tds_deducted": 0.0,
+ "rate": row.get("rate"),
+ "total_amount": 0.0,
+ "tax_amount": 0.0,
},
)
- supplier_category_wise_map.get((row.get("supplier"), row.get("section_code")))[
- "total_amount_credited"
- ] += row.get("total_amount_credited", 0.0)
+ party_category_wise_map.get((row.get("party"), row.get("section_code")))[
+ "total_amount"
+ ] += row.get("total_amount", 0.0)
- supplier_category_wise_map.get((row.get("supplier"), row.get("section_code")))[
- "tds_deducted"
- ] += row.get("tds_deducted", 0.0)
+ party_category_wise_map.get((row.get("party"), row.get("section_code")))[
+ "tax_amount"
+ ] += row.get("tax_amount", 0.0)
- final_result = get_final_result(supplier_category_wise_map)
+ final_result = get_final_result(party_category_wise_map)
return final_result
-def get_final_result(supplier_category_wise_map):
+def get_final_result(party_category_wise_map):
out = []
- for key, value in supplier_category_wise_map.items():
+ for key, value in party_category_wise_map.items():
out.append(value)
return out
def get_columns(filters):
+ pan = "pan" if frappe.db.has_column(filters.party_type, "pan") else "tax_id"
columns = [
- {"label": _("PAN"), "fieldname": "pan", "fieldtype": "Data", "width": 90},
+ {"label": _(frappe.unscrub(pan)), "fieldname": pan, "fieldtype": "Data", "width": 90},
{
- "label": _("Supplier"),
- "options": "Supplier",
- "fieldname": "supplier",
- "fieldtype": "Link",
+ "label": _(filters.get("party_type")),
+ "fieldname": "party",
+ "fieldtype": "Dynamic Link",
+ "options": "party_type",
"width": 180,
},
]
if filters.naming_series == "Naming Series":
columns.append(
- {"label": _("Supplier Name"), "fieldname": "supplier_name", "fieldtype": "Data", "width": 180}
+ {
+ "label": _(filters.party_type + " Name"),
+ "fieldname": "party_name",
+ "fieldtype": "Data",
+ "width": 180,
+ }
)
columns.extend(
@@ -109,18 +121,23 @@
"width": 180,
},
{"label": _("Entity Type"), "fieldname": "entity_type", "fieldtype": "Data", "width": 180},
- {"label": _("TDS Rate %"), "fieldname": "tds_rate", "fieldtype": "Percent", "width": 90},
{
- "label": _("Total Amount Credited"),
- "fieldname": "total_amount_credited",
- "fieldtype": "Float",
- "width": 90,
+ "label": _("TDS Rate %") if filters.get("party_type") == "Supplier" else _("TCS Rate %"),
+ "fieldname": "rate",
+ "fieldtype": "Percent",
+ "width": 120,
},
{
- "label": _("Amount of TDS Deducted"),
- "fieldname": "tds_deducted",
+ "label": _("Total Amount"),
+ "fieldname": "total_amount",
"fieldtype": "Float",
- "width": 90,
+ "width": 120,
+ },
+ {
+ "label": _("Tax Amount"),
+ "fieldname": "tax_amount",
+ "fieldtype": "Float",
+ "width": 120,
},
]
)