fix: report sales payments summary (#30264)
diff --git a/erpnext/accounts/report/sales_payment_summary/sales_payment_summary.py b/erpnext/accounts/report/sales_payment_summary/sales_payment_summary.py
index 3b73628..d5483b5 100644
--- a/erpnext/accounts/report/sales_payment_summary/sales_payment_summary.py
+++ b/erpnext/accounts/report/sales_payment_summary/sales_payment_summary.py
@@ -199,31 +199,39 @@
invoice_list = get_invoices(filters)
invoice_list_names = ",".join('"' + invoice['name'] + '"' for invoice in invoice_list)
if invoice_list:
- inv_mop_detail = frappe.db.sql("""select a.owner, a.posting_date,
- ifnull(b.mode_of_payment, '') as mode_of_payment, sum(b.base_amount) as paid_amount
- from `tabSales Invoice` a, `tabSales Invoice Payment` b
- where a.name = b.parent
- and a.docstatus = 1
- and a.name in ({invoice_list_names})
- group by a.owner, a.posting_date, mode_of_payment
- union
- select a.owner,a.posting_date,
- ifnull(b.mode_of_payment, '') as mode_of_payment, sum(b.base_paid_amount) as paid_amount
- from `tabSales Invoice` a, `tabPayment Entry` b,`tabPayment Entry Reference` c
- where a.name = c.reference_name
- and b.name = c.parent
- and b.docstatus = 1
- and a.name in ({invoice_list_names})
- group by a.owner, a.posting_date, mode_of_payment
- union
- select a.owner, a.posting_date,
- ifnull(a.voucher_type,'') as mode_of_payment, sum(b.credit)
- from `tabJournal Entry` a, `tabJournal Entry Account` b
- where a.name = b.parent
- and a.docstatus = 1
- and b.reference_type = "Sales Invoice"
- and b.reference_name in ({invoice_list_names})
- group by a.owner, a.posting_date, mode_of_payment
+ inv_mop_detail = frappe.db.sql("""
+ select t.owner,
+ t.posting_date,
+ t.mode_of_payment,
+ sum(t.paid_amount) as paid_amount
+ from (
+ select a.owner, a.posting_date,
+ ifnull(b.mode_of_payment, '') as mode_of_payment, sum(b.base_amount) as paid_amount
+ from `tabSales Invoice` a, `tabSales Invoice Payment` b
+ where a.name = b.parent
+ and a.docstatus = 1
+ and a.name in ({invoice_list_names})
+ group by a.owner, a.posting_date, mode_of_payment
+ union
+ select a.owner,a.posting_date,
+ ifnull(b.mode_of_payment, '') as mode_of_payment, sum(c.allocated_amount) as paid_amount
+ from `tabSales Invoice` a, `tabPayment Entry` b,`tabPayment Entry Reference` c
+ where a.name = c.reference_name
+ and b.name = c.parent
+ and b.docstatus = 1
+ and a.name in ({invoice_list_names})
+ group by a.owner, a.posting_date, mode_of_payment
+ union
+ select a.owner, a.posting_date,
+ ifnull(a.voucher_type,'') as mode_of_payment, sum(b.credit)
+ from `tabJournal Entry` a, `tabJournal Entry Account` b
+ where a.name = b.parent
+ and a.docstatus = 1
+ and b.reference_type = "Sales Invoice"
+ and b.reference_name in ({invoice_list_names})
+ group by a.owner, a.posting_date, mode_of_payment
+ ) t
+ group by t.owner, t.posting_date, t.mode_of_payment
""".format(invoice_list_names=invoice_list_names), as_dict=1)
inv_change_amount = frappe.db.sql("""select a.owner, a.posting_date,
@@ -231,7 +239,7 @@
from `tabSales Invoice` a, `tabSales Invoice Payment` b
where a.name = b.parent
and a.name in ({invoice_list_names})
- and b.mode_of_payment = 'Cash'
+ and b.type = 'Cash'
and a.base_change_amount > 0
group by a.owner, a.posting_date, mode_of_payment""".format(invoice_list_names=invoice_list_names), as_dict=1)
@@ -244,4 +252,4 @@
for d in inv_mop_detail:
mode_of_payment_details.setdefault(d["owner"]+cstr(d["posting_date"]), []).append((d.mode_of_payment,d.paid_amount))
- return mode_of_payment_details
+ return mode_of_payment_details
\ No newline at end of file