Filter cancelled and draft payments
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 05c8fb7..f4c72b4 100644
--- a/erpnext/accounts/report/sales_payment_summary/sales_payment_summary.py
+++ b/erpnext/accounts/report/sales_payment_summary/sales_payment_summary.py
@@ -108,33 +108,33 @@
def get_pos_invoice_data(filters):
conditions = get_conditions(filters)
result = frappe.db.sql(''
- 'SELECT '
- 'posting_date, owner, sum(net_total) as "net_total", sum(total_taxes) as "total_taxes", '
- 'sum(paid_amount) as "paid_amount", sum(outstanding_amount) as "outstanding_amount", '
- 'mode_of_payment, warehouse, cost_center '
- 'FROM ('
- 'SELECT '
- 'parent, item_code, sum(amount) as "base_total", warehouse, cost_center '
- 'from `tabSales Invoice Item` group by parent'
- ') t1 '
- 'left join '
- '(select parent, mode_of_payment from `tabSales Invoice Payment` group by parent) t3 '
- 'on (t3.parent = t1.parent) '
- 'JOIN ('
- 'SELECT '
- 'docstatus, company, is_pos, name, posting_date, owner, sum(base_total) as "base_total", '
- 'sum(net_total) as "net_total", sum(total_taxes_and_charges) as "total_taxes", '
- 'sum(base_paid_amount) as "paid_amount", sum(outstanding_amount) as "outstanding_amount" '
- 'FROM `tabSales Invoice` '
- 'GROUP BY name'
- ') a '
- 'ON ('
- 't1.parent = a.name and t1.base_total = a.base_total) '
- 'WHERE a.docstatus = 1'
- ' AND {conditions} '
- 'GROUP BY '
- 'owner, posting_date, warehouse'.format(conditions=conditions), filters, as_dict=1
- )
+ 'SELECT '
+ 'posting_date, owner, sum(net_total) as "net_total", sum(total_taxes) as "total_taxes", '
+ 'sum(paid_amount) as "paid_amount", sum(outstanding_amount) as "outstanding_amount", '
+ 'mode_of_payment, warehouse, cost_center '
+ 'FROM ('
+ 'SELECT '
+ 'parent, item_code, sum(amount) as "base_total", warehouse, cost_center '
+ 'from `tabSales Invoice Item` group by parent'
+ ') t1 '
+ 'left join '
+ '(select parent, mode_of_payment from `tabSales Invoice Payment` group by parent) t3 '
+ 'on (t3.parent = t1.parent) '
+ 'JOIN ('
+ 'SELECT '
+ 'docstatus, company, is_pos, name, posting_date, owner, sum(base_total) as "base_total", '
+ 'sum(net_total) as "net_total", sum(total_taxes_and_charges) as "total_taxes", '
+ 'sum(base_paid_amount) as "paid_amount", sum(outstanding_amount) as "outstanding_amount" '
+ 'FROM `tabSales Invoice` '
+ 'GROUP BY name'
+ ') a '
+ 'ON ('
+ 't1.parent = a.name and t1.base_total = a.base_total) '
+ 'WHERE a.docstatus = 1'
+ ' AND {conditions} '
+ 'GROUP BY '
+ 'owner, posting_date, warehouse'.format(conditions=conditions), filters, as_dict=1
+ )
return result
@@ -170,6 +170,7 @@
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})
union
select a.owner, a.posting_date,
@@ -211,6 +212,7 @@
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