Merge pull request #16253 from chdecultot/sales_summary_correction
[Fix] Filter cancelled and draft payments in sales payment summary
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..c234da0 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
@@ -156,7 +156,6 @@
def get_mode_of_payments(filters):
- frappe.log_error(filters, 'filters')
mode_of_payments = {}
invoice_list = get_invoices(filters)
invoice_list_names = ",".join(['"' + invoice['name'] + '"' for invoice in invoice_list])
@@ -164,12 +163,14 @@
inv_mop = frappe.db.sql("""select a.owner,a.posting_date, ifnull(b.mode_of_payment, '') as mode_of_payment
from `tabSales Invoice` a, `tabSales Invoice Payment` b
where a.name = b.parent
+ and a.docstatus = 1
and a.name in ({invoice_list_names})
union
select a.owner,a.posting_date, ifnull(b.mode_of_payment, '') as mode_of_payment
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,
@@ -196,13 +197,13 @@
def get_mode_of_payment_details(filters):
mode_of_payment_details = {}
invoice_list = get_invoices(filters)
- frappe.log_error(invoice_list, 'invoice_list')
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
@@ -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
diff --git a/erpnext/accounts/report/sales_payment_summary/test_sales_payment_summary.py b/erpnext/accounts/report/sales_payment_summary/test_sales_payment_summary.py
new file mode 100644
index 0000000..62843e7
--- /dev/null
+++ b/erpnext/accounts/report/sales_payment_summary/test_sales_payment_summary.py
@@ -0,0 +1,165 @@
+# Copyright (c) 2018, Frappe Technologies Pvt. Ltd. and Contributors
+# License: GNU General Public License v3. See license.txt
+
+from __future__ import unicode_literals
+import unittest
+import frappe
+from erpnext.accounts.report.sales_payment_summary.sales_payment_summary import get_mode_of_payments, get_mode_of_payment_details
+from frappe.utils import today
+from erpnext.accounts.doctype.payment_entry.payment_entry import get_payment_entry
+
+test_dependencies = ["Sales Invoice"]
+
+class TestSalesPaymentSummary(unittest.TestCase):
+ @classmethod
+ def setUpClass(self):
+ create_records()
+ pes = frappe.get_all("Payment Entry")
+ jes = frappe.get_all("Journal Entry")
+ sis = frappe.get_all("Sales Invoice")
+ for pe in pes:
+ frappe.db.set_value("Payment Entry", pe.name, "docstatus", 2)
+ for je in jes:
+ frappe.db.set_value("Journal Entry", je.name, "docstatus", 2)
+ for si in sis:
+ frappe.db.set_value("Sales Invoice", si.name, "docstatus", 2)
+
+ def test_get_mode_of_payments(self):
+ filters = get_filters()
+
+ for dummy in range(2):
+ si = create_sales_invoice_record()
+ si.insert()
+ si.submit()
+
+ if int(si.name[-3:])%2 == 0:
+ bank_account = "_Test Cash - _TC"
+ mode_of_payment = "Cash"
+ else:
+ bank_account = "_Test Bank - _TC"
+ mode_of_payment = "Credit Card"
+
+ pe = get_payment_entry("Sales Invoice", si.name, bank_account=bank_account)
+ pe.reference_no = "_Test"
+ pe.reference_date = today()
+ pe.mode_of_payment = mode_of_payment
+ pe.insert()
+ pe.submit()
+
+ mop = get_mode_of_payments(filters)
+ self.assertTrue('Credit Card' in mop.values()[0])
+ self.assertTrue('Cash' in mop.values()[0])
+
+ # Cancel all Cash payment entry and check if this mode of payment is still fetched.
+ payment_entries = frappe.get_all("Payment Entry", filters={"mode_of_payment": "Cash", "docstatus": 1}, fields=["name", "docstatus"])
+ for payment_entry in payment_entries:
+ pe = frappe.get_doc("Payment Entry", payment_entry.name)
+ pe.cancel()
+
+ mop = get_mode_of_payments(filters)
+ self.assertTrue('Credit Card' in mop.values()[0])
+ self.assertTrue('Cash' not in mop.values()[0])
+
+ def test_get_mode_of_payments_details(self):
+ filters = get_filters()
+
+ for dummy in range(2):
+ si = create_sales_invoice_record()
+ si.insert()
+ si.submit()
+
+ if int(si.name[-3:])%2 == 0:
+ bank_account = "_Test Cash - _TC"
+ mode_of_payment = "Cash"
+ else:
+ bank_account = "_Test Bank - _TC"
+ mode_of_payment = "Credit Card"
+
+ pe = get_payment_entry("Sales Invoice", si.name, bank_account=bank_account)
+ pe.reference_no = "_Test"
+ pe.reference_date = today()
+ pe.mode_of_payment = mode_of_payment
+ pe.insert()
+ pe.submit()
+
+ mopd = get_mode_of_payment_details(filters)
+
+ mopd_values = mopd.values()[0]
+ for mopd_value in mopd_values:
+ if mopd_value[0] == "Credit Card":
+ cc_init_amount = mopd_value[1]
+
+ # Cancel one Credit Card Payment Entry and check that it is not fetched in mode of payment details.
+ payment_entries = frappe.get_all("Payment Entry", filters={"mode_of_payment": "Credit Card", "docstatus": 1}, fields=["name", "docstatus"])
+ for payment_entry in payment_entries[:1]:
+ pe = frappe.get_doc("Payment Entry", payment_entry.name)
+ pe.cancel()
+
+ mopd = get_mode_of_payment_details(filters)
+ mopd_values = mopd.values()[0]
+ for mopd_value in mopd_values:
+ if mopd_value[0] == "Credit Card":
+ cc_final_amount = mopd_value[1]
+
+ self.assertTrue(cc_init_amount > cc_final_amount)
+
+def get_filters():
+ return {
+ "from_date": "1900-01-01",
+ "to_date": today(),
+ "company": "_Test Company"
+ }
+
+def create_sales_invoice_record(qty=1):
+ # return sales invoice doc object
+ return frappe.get_doc({
+ "doctype": "Sales Invoice",
+ "customer": frappe.get_doc('Customer', {"customer_name": "Prestiga-Biz"}).name,
+ "company": '_Test Company',
+ "due_date": today(),
+ "posting_date": today(),
+ "currency": "INR",
+ "taxes_and_charges": "",
+ "debit_to": "Debtors - _TC",
+ "taxes": [],
+ "items": [{
+ 'doctype': 'Sales Invoice Item',
+ 'item_code': frappe.get_doc('Item', {'item_name': 'Consulting'}).name,
+ 'qty': qty,
+ "rate": 10000,
+ 'income_account': 'Sales - _TC',
+ 'cost_center': 'Main - _TC',
+ 'expense_account': 'Cost of Goods Sold - _TC'
+ }]
+ })
+
+def create_records():
+ if frappe.db.exists("Customer", "Prestiga-Biz"):
+ return
+
+ #customer
+ frappe.get_doc({
+ "customer_group": "_Test Customer Group",
+ "customer_name": "Prestiga-Biz",
+ "customer_type": "Company",
+ "doctype": "Customer",
+ "territory": "_Test Territory"
+ }).insert()
+
+ # item
+ item = frappe.get_doc({
+ "doctype": "Item",
+ "item_code": "Consulting",
+ "item_name": "Consulting",
+ "item_group": "All Item Groups",
+ "company": "_Test Company",
+ "is_stock_item": 0
+ }).insert()
+
+ # item price
+ frappe.get_doc({
+ "doctype": "Item Price",
+ "price_list": "Standard Selling",
+ "item_code": item.item_code,
+ "price_list_rate": 10000
+ }).insert()
\ No newline at end of file