Sales Payment Summart Report Improvement (#14243)
* fix iteritems() takes exactly 1 argument (2 given)
* adds new helper functions
* use new functions to make POS invoices filterable on warehouse and cost center
* more PEP8 fixes
diff --git a/erpnext/accounts/doctype/sales_invoice/sales_invoice.py b/erpnext/accounts/doctype/sales_invoice/sales_invoice.py
index 3750c2d..d0994eb 100644
--- a/erpnext/accounts/doctype/sales_invoice/sales_invoice.py
+++ b/erpnext/accounts/doctype/sales_invoice/sales_invoice.py
@@ -341,9 +341,8 @@
# set pos values in items
for item in self.get("items"):
if item.get('item_code'):
- for fname, val in get_pos_profile_item_details(pos,
- iteritems(frappe._dict(item.as_dict()), pos)):
-
+ profile_details = get_pos_profile_item_details(pos, frappe._dict(item.as_dict()), pos)
+ for fname, val in iteritems(profile_details):
if (not for_validate) or (for_validate and not item.get(fname)):
item.set(fname, val)
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 4fcc05b..0c11d52 100644
--- a/erpnext/accounts/report/sales_payment_summary/sales_payment_summary.py
+++ b/erpnext/accounts/report/sales_payment_summary/sales_payment_summary.py
@@ -5,22 +5,59 @@
from frappe import _
from frappe.utils import cstr
+
def execute(filters=None):
columns, data = [], []
- columns=get_columns()
- data=get_sales_payment_data(filters, columns)
+ columns = get_columns(filters)
+ data = get_pos_sales_payment_data(filters) if filters.get('is_pos') else get_sales_payment_data(filters, columns)
return columns, data
-def get_columns():
+
+def get_pos_columns():
return [
_("Date") + ":Date:80",
_("Owner") + ":Data:200",
_("Payment Mode") + ":Data:240",
_("Sales and Returns") + ":Currency/currency:120",
_("Taxes") + ":Currency/currency:120",
- _("Payments") + ":Currency/currency:120"
+ _("Payments") + ":Currency/currency:120",
+ _("Warehouse") + ":Data:200",
+ _("Cost Center") + ":Data:200"
]
+
+def get_columns(filters):
+ if filters.get('is_pos'):
+ return get_pos_columns()
+ else:
+ return [
+ _("Date") + ":Date:80",
+ _("Owner") + ":Data:200",
+ _("Payment Mode") + ":Data:240",
+ _("Sales and Returns") + ":Currency/currency:120",
+ _("Taxes") + ":Currency/currency:120",
+ _("Payments") + ":Currency/currency:120",
+ _("Warehouse") + ":Data:200"
+ ]
+
+
+def get_pos_sales_payment_data(filters):
+ sales_invoice_data = get_pos_invoice_data(filters)
+ data = [
+ [
+ row['posting_date'],
+ row['owner'],
+ row['mode_of_payment'],
+ row['net_total'],
+ row['total_taxes'],
+ row['paid_amount'],
+ row['warehouse'],
+ row['cost_center']
+ ] for row in sales_invoice_data]
+
+ return data
+
+
def get_sales_payment_data(filters, columns):
data = []
show_payment_detail = False
@@ -51,16 +88,57 @@
data.append(row)
return data
+
def get_conditions(filters):
conditions = "1=1"
- if filters.get("from_date"): conditions += " and a.posting_date >= %(from_date)s"
- if filters.get("to_date"): conditions += " and a.posting_date <= %(to_date)s"
- if filters.get("company"): conditions += " and a.company=%(company)s"
- if filters.get("customer"): conditions += " and a.customer = %(customer)s"
- if filters.get("owner"): conditions += " and a.owner = %(owner)s"
- if filters.get("is_pos"): conditions += " and a.is_pos = %(is_pos)s"
+ if filters.get("from_date"):
+ conditions += " and a.posting_date >= %(from_date)s"
+ if filters.get("to_date"):
+ conditions += " and a.posting_date <= %(to_date)s"
+ if filters.get("company"):
+ conditions += " and a.company=%(company)s"
+ if filters.get("customer"):
+ conditions += " and a.customer = %(customer)s"
+ if filters.get("owner"):
+ conditions += " and a.owner = %(owner)s"
+ if filters.get("is_pos"):
+ conditions += " and a.is_pos = %(is_pos)s"
return conditions
+
+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
+ )
+ return result
+
+
def get_sales_invoice_data(filters):
conditions = get_conditions(filters)
return frappe.db.sql("""
@@ -77,6 +155,7 @@
a.owner, a.posting_date
""".format(conditions=conditions), filters, as_dict=1)
+
def get_mode_of_payments(filters):
mode_of_payments = {}
invoice_list = get_invoices(filters)
@@ -105,6 +184,7 @@
mode_of_payments.setdefault(d["owner"]+cstr(d["posting_date"]), []).append(d.mode_of_payment)
return mode_of_payments
+
def get_invoices(filters):
conditions = get_conditions(filters)
return frappe.db.sql("""select a.name
@@ -112,6 +192,7 @@
where a.docstatus = 1 and {conditions}""".format(conditions=conditions),
filters, as_dict=1)
+
def get_mode_of_payment_details(filters):
mode_of_payment_details = {}
invoice_list = get_invoices(filters)
@@ -160,4 +241,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
\ No newline at end of file
+ return mode_of_payment_details