refactor: move fn to fetch advance taxes to utils & use qb
diff --git a/erpnext/accounts/report/purchase_register/purchase_register.py b/erpnext/accounts/report/purchase_register/purchase_register.py
index 6e35c9d..93dadc6 100644
--- a/erpnext/accounts/report/purchase_register/purchase_register.py
+++ b/erpnext/accounts/report/purchase_register/purchase_register.py
@@ -12,6 +12,7 @@
get_accounting_dimensions,
)
from erpnext.accounts.report.utils import (
+ get_advance_taxes_and_charges,
get_conditions,
get_journal_entries,
get_party_details,
@@ -235,7 +236,7 @@
if filters.get("supplier"):
query = query.where(pi.supplier == filters.supplier)
query = get_conditions(filters, query, [pi, invoice_item], accounting_dimensions)
- invoices = query.run(as_dict=True, debug=True)
+ invoices = query.run(as_dict=True)
return invoices
@@ -312,20 +313,7 @@
)
if include_payments:
- advance_tax_details = frappe.db.sql(
- """
- select parent, account_head, case add_deduct_tax when "Add" then sum(base_tax_amount)
- else sum(base_tax_amount) * -1 end as tax_amount
- from `tabAdvance Taxes and Charges`
- where parent in (%s) and charge_type in ('On Paid Amount', 'Actual')
- and base_tax_amount != 0
- group by parent, account_head, add_deduct_tax
- """
- % ", ".join(["%s"] * len(invoice_list)),
- tuple(inv.name for inv in invoice_list),
- as_dict=1,
- )
- tax_details += advance_tax_details
+ tax_details += get_advance_taxes_and_charges(invoice_list)
invoice_tax_map = {}
for d in tax_details:
diff --git a/erpnext/accounts/report/sales_register/sales_register.py b/erpnext/accounts/report/sales_register/sales_register.py
index 2460cd7..1daf524 100644
--- a/erpnext/accounts/report/sales_register/sales_register.py
+++ b/erpnext/accounts/report/sales_register/sales_register.py
@@ -13,6 +13,7 @@
get_accounting_dimensions,
)
from erpnext.accounts.report.utils import (
+ get_advance_taxes_and_charges,
get_conditions,
get_journal_entries,
get_party_details,
@@ -42,6 +43,7 @@
invoice_list, additional_table_columns, include_payments
)
+ print("Accounts", tax_accounts)
if not invoice_list:
msgprint(_("No record found"))
return columns, invoice_list
@@ -120,6 +122,7 @@
or 2
)
tax_amount = flt(invoice_tax_map.get(inv.name, {}).get(tax_acc), tax_amount_precision)
+ print(tax_amount)
total_tax += tax_amount
row.update({frappe.scrub(tax_acc): tax_amount})
@@ -399,7 +402,7 @@
if filters.get("customer"):
query = query.where(si.customer == filters.customer)
query = get_conditions(filters, query, [si, invoice_item, invoice_payment], accounting_dimensions)
- invoices = query.run(as_dict=True, debug=True)
+ invoices = query.run(as_dict=True)
return invoices
@@ -465,20 +468,7 @@
)
if include_payments:
- advance_tax_details = frappe.db.sql(
- """
- select parent, account_head, case add_deduct_tax when "Add" then sum(base_tax_amount)
- else sum(base_tax_amount) * -1 end as tax_amount
- from `tabAdvance Taxes and Charges`
- where parent in (%s) and charge_type in ('On Paid Amount', 'Actual')
- and base_tax_amount != 0
- group by parent, account_head, add_deduct_tax
- """
- % ", ".join(["%s"] * len(invoice_list)),
- tuple(inv.name for inv in invoice_list),
- as_dict=1,
- )
- tax_details += advance_tax_details
+ tax_details += get_advance_taxes_and_charges(invoice_list)
invoice_tax_map = {}
for d in tax_details:
diff --git a/erpnext/accounts/report/utils.py b/erpnext/accounts/report/utils.py
index 6617f9a..8b58a46 100644
--- a/erpnext/accounts/report/utils.py
+++ b/erpnext/accounts/report/utils.py
@@ -1,5 +1,6 @@
import frappe
from frappe.query_builder.custom import ConstantColumn
+from frappe.query_builder.functions import Sum
from frappe.utils import flt, formatdate, get_datetime_str, get_table_name
from pypika import Order
@@ -220,7 +221,7 @@
if doctype == "Purchase Taxes and Charges":
return query.where(taxes.category.isin(["Total", "Valuation and Total"]))
elif doctype == "Sales Taxes and Charges":
- return query.where(taxes.charge_type.isin(["Total", "Valuation and Total"]))
+ return query
return query.where(taxes.charge_type.isin(["On Paid Amount", "Actual"]))
@@ -250,7 +251,7 @@
.orderby(je.posting_date, je.name, order=Order.desc)
)
query = get_conditions(filters, query, [je], accounting_dimensions, payments=True)
- journal_entries = query.run(as_dict=True, debug=True)
+ journal_entries = query.run(as_dict=True)
return journal_entries
@@ -276,7 +277,7 @@
.orderby(pe.posting_date, pe.name, order=Order.desc)
)
query = get_conditions(filters, query, [pe], accounting_dimensions, payments=True)
- payment_entries = query.run(as_dict=True, debug=True)
+ payment_entries = query.run(as_dict=True)
return payment_entries
@@ -326,3 +327,25 @@
fieldname = dimension.fieldname
query = query.where(parent_doc.fieldname.isin(filters.fieldname))
return query
+
+
+def get_advance_taxes_and_charges(invoice_list):
+ adv_taxes = frappe.qb.DocType("Advance Taxes and Charges")
+ return (
+ frappe.qb.from_(adv_taxes)
+ .select(
+ adv_taxes.parent,
+ adv_taxes.account_head,
+ (
+ frappe.qb.terms.Case()
+ .when(adv_taxes.add_deduct_tax == "Add", Sum(adv_taxes.base_tax_amount))
+ .else_(Sum(adv_taxes.base_tax_amount) * -1)
+ ).as_("tax_amount"),
+ )
+ .where(
+ (adv_taxes.parent.isin([inv.name for inv in invoice_list]))
+ & (adv_taxes.charge_type.isin(["On Paid Amount", "Actual"]))
+ & (adv_taxes.base_tax_amount != 0)
+ )
+ .groupby(adv_taxes.parent, adv_taxes.account_head, adv_taxes.add_deduct_tax)
+ ).run(as_dict=True, debug=True)