fix: AP and AR summary
diff --git a/erpnext/accounts/party.py b/erpnext/accounts/party.py
index 4996203..d5f8634 100644
--- a/erpnext/accounts/party.py
+++ b/erpnext/accounts/party.py
@@ -14,6 +14,7 @@
from frappe.contacts.doctype.contact.contact import get_contact_details
from frappe.core.doctype.user_permission.user_permission import get_permitted_documents
from frappe.model.utils import get_fetch_values
+from frappe.query_builder.functions import Date, Sum
from frappe.utils import (
add_days,
add_months,
@@ -920,32 +921,35 @@
def get_partywise_advanced_payment_amount(
- party_type, posting_date=None, future_payment=0, company=None, party=None
+ party_type, posting_date=None, future_payment=0, company=None, party=None, account_type=None
):
- cond = "1=1"
+ gle = frappe.qb.DocType("GL Entry")
+ query = (
+ frappe.qb.from_(gle)
+ .select(gle.party)
+ .where(
+ (gle.party_type.isin(party_type)) & (gle.against_voucher == None) & (gle.is_cancelled == 0)
+ )
+ .groupby(gle.party)
+ )
+ if account_type == "Receivable":
+ query = query.select(Sum(gle.credit).as_("amount"))
+ else:
+ query = query.select(Sum(gle.debit).as_("amount"))
+
if posting_date:
if future_payment:
- cond = "(posting_date <= '{0}' OR DATE(creation) <= '{0}')" "".format(posting_date)
+ query = query.where((gle.posting_date <= posting_date) | (Date(gle.creation) <= posting_date))
else:
- cond = "posting_date <= '{0}'".format(posting_date)
+ query = query.where(gle.posting_date <= posting_date)
if company:
- cond += "and company = {0}".format(frappe.db.escape(company))
+ query = query.where(gle.company == company)
if party:
- cond += "and party = {0}".format(frappe.db.escape(party))
+ query = query.where(gle.party == party)
- data = frappe.db.sql(
- """ SELECT party, sum({0}) as amount
- FROM `tabGL Entry`
- WHERE
- party_type = %s and against_voucher is null
- and is_cancelled = 0
- and {1} GROUP BY party""".format(
- ("credit") if party_type == "Customer" else "debit", cond
- ),
- party_type,
- )
+ data = query.run(as_dict=True)
if data:
return frappe._dict(data)
diff --git a/erpnext/accounts/report/accounts_payable_summary/accounts_payable_summary.py b/erpnext/accounts/report/accounts_payable_summary/accounts_payable_summary.py
index 65fe1de..834c83c 100644
--- a/erpnext/accounts/report/accounts_payable_summary/accounts_payable_summary.py
+++ b/erpnext/accounts/report/accounts_payable_summary/accounts_payable_summary.py
@@ -9,7 +9,7 @@
def execute(filters=None):
args = {
- "party_type": "Supplier",
+ "account_type": "Payable",
"naming_by": ["Buying Settings", "supp_master_name"],
}
return AccountsReceivableSummary(filters).run(args)
diff --git a/erpnext/accounts/report/accounts_receivable_summary/accounts_receivable_summary.py b/erpnext/accounts/report/accounts_receivable_summary/accounts_receivable_summary.py
index 9c01b1a..3aa1ae7 100644
--- a/erpnext/accounts/report/accounts_receivable_summary/accounts_receivable_summary.py
+++ b/erpnext/accounts/report/accounts_receivable_summary/accounts_receivable_summary.py
@@ -12,7 +12,7 @@
def execute(filters=None):
args = {
- "party_type": "Customer",
+ "account_type": "Receivable",
"naming_by": ["Selling Settings", "cust_master_name"],
}
@@ -21,7 +21,10 @@
class AccountsReceivableSummary(ReceivablePayableReport):
def run(self, args):
- self.party_type = args.get("party_type")
+ self.account_type = args.get("account_type")
+ self.party_type = frappe.db.get_all(
+ "Party Type", {"account_type": self.account_type}, pluck="name"
+ )
self.party_naming_by = frappe.db.get_value(
args.get("naming_by")[0], None, args.get("naming_by")[1]
)
@@ -35,13 +38,19 @@
self.get_party_total(args)
+ party = None
+ for party_type in self.party_type:
+ if self.filters.get(scrub(party_type)):
+ party = self.filters.get(scrub(party_type))
+
party_advance_amount = (
get_partywise_advanced_payment_amount(
self.party_type,
self.filters.report_date,
self.filters.show_future_payments,
self.filters.company,
- party=self.filters.get(scrub(self.party_type)),
+ party=party,
+ account_type=self.account_type,
)
or {}
)
@@ -57,9 +66,13 @@
row.party = party
if self.party_naming_by == "Naming Series":
- row.party_name = frappe.get_cached_value(
- self.party_type, party, scrub(self.party_type) + "_name"
- )
+ if self.account_type == "Payable":
+ doctype = "Supplier"
+ fieldname = "supplier_name"
+ else:
+ doctype = "Customer"
+ fieldname = "customer_name"
+ row.party_name = frappe.get_cached_value(doctype, party, fieldname)
row.update(party_dict)
@@ -93,6 +106,7 @@
# set territory, customer_group, sales person etc
self.set_party_details(d)
+ self.party_total[d.party].update({"party_type": d.party_type})
def init_party_total(self, row):
self.party_total.setdefault(
@@ -131,17 +145,27 @@
def get_columns(self):
self.columns = []
self.add_column(
- label=_(self.party_type),
+ label="Party Type",
+ fieldname="party_type",
+ fieldtype="Data",
+ width=100,
+ )
+ self.add_column(
+ label="Party",
fieldname="party",
- fieldtype="Link",
- options=self.party_type,
+ fieldtype="Dynamic Link",
+ options="party_type",
width=180,
)
if self.party_naming_by == "Naming Series":
- self.add_column(_("{0} Name").format(self.party_type), fieldname="party_name", fieldtype="Data")
+ self.add_column(
+ label="Supplier Name" if self.account_type == "Payable" else "Customer Name",
+ fieldname="party_name",
+ fieldtype="Data",
+ )
- credit_debit_label = "Credit Note" if self.party_type == "Customer" else "Debit Note"
+ credit_debit_label = "Credit Note" if self.account_type == "Receivable" else "Debit Note"
self.add_column(_("Advance Amount"), fieldname="advance")
self.add_column(_("Invoiced Amount"), fieldname="invoiced")
@@ -159,7 +183,7 @@
self.add_column(label=_("Future Payment Amount"), fieldname="future_amount")
self.add_column(label=_("Remaining Balance"), fieldname="remaining_balance")
- if self.party_type == "Customer":
+ if self.account_type == "Receivable":
self.add_column(
label=_("Territory"), fieldname="territory", fieldtype="Link", options="Territory"
)