[fix] #11427
diff --git a/erpnext/accounts/report/daily_sales_payment_summary/__init__.py b/erpnext/accounts/report/daily_sales_payment_summary/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/accounts/report/daily_sales_payment_summary/__init__.py
diff --git a/erpnext/accounts/report/daily_sales_payment_summary/daily_sales_payment_summary.js b/erpnext/accounts/report/daily_sales_payment_summary/daily_sales_payment_summary.js
new file mode 100644
index 0000000..0426e0a
--- /dev/null
+++ b/erpnext/accounts/report/daily_sales_payment_summary/daily_sales_payment_summary.js
@@ -0,0 +1,62 @@
+// Copyright (c) 2015, Frappe Technologies Pvt. Ltd. and Contributors
+// License: GNU General Public License v3. See license.txt
+
+frappe.query_reports["Daily Sales Payment Summary"] = {
+ "filters": [
+ {
+ "fieldname":"from_date",
+ "label": __("From Date"),
+ "fieldtype": "Date",
+ "default": frappe.datetime.get_today(),
+ "width": "80"
+ },
+ {
+ "fieldname":"to_date",
+ "label": __("To Date"),
+ "fieldtype": "Date",
+ "default": frappe.datetime.get_today()
+ },
+ {
+ "fieldname":"customer",
+ "label": __("Customer"),
+ "fieldtype": "Link",
+ "options": "Customer"
+ },
+ {
+ "fieldname":"company",
+ "label": __("Company"),
+ "fieldtype": "Link",
+ "options": "Company",
+ "default": frappe.defaults.get_user_default("Company")
+ },
+ {
+ "fieldname":"mode_of_payment",
+ "label": __("Mode of Payment"),
+ "fieldtype": "Link",
+ "options": "Mode of Payment"
+ },
+ {
+ "fieldname":"owner",
+ "label": __("Owner"),
+ "fieldtype": "Link",
+ "options": "User"
+ },
+ {
+ "fieldname":"cost_center",
+ "label": __("Cost Center"),
+ "fieldtype": "Link",
+ "options": "Cost Center"
+ },
+ {
+ "fieldname":"warehouse",
+ "label": __("Warehouse"),
+ "fieldtype": "Link",
+ "options": "Warehouse"
+ },
+ {
+ "fieldname":"is_pos",
+ "label": __("POS?"),
+ "fieldtype": "Check"
+ }
+ ]
+}
diff --git a/erpnext/accounts/report/daily_sales_payment_summary/daily_sales_payment_summary.json b/erpnext/accounts/report/daily_sales_payment_summary/daily_sales_payment_summary.json
new file mode 100644
index 0000000..0d43fd1
--- /dev/null
+++ b/erpnext/accounts/report/daily_sales_payment_summary/daily_sales_payment_summary.json
@@ -0,0 +1,26 @@
+{
+ "add_total_row": 0,
+ "apply_user_permissions": 1,
+ "creation": "2017-11-02 00:28:38.519057",
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "idx": 0,
+ "is_standard": "Yes",
+ "modified": "2017-11-02 10:39:56.984273",
+ "modified_by": "Administrator",
+ "module": "Accounts",
+ "name": "Daily Sales Payment Summary",
+ "owner": "Administrator",
+ "ref_doctype": "Sales Invoice",
+ "report_name": "Daily Sales Payment Summary",
+ "report_type": "Script Report",
+ "roles": [
+ {
+ "role": "Accounts Manager"
+ },
+ {
+ "role": "Accounts User"
+ }
+ ]
+}
\ No newline at end of file
diff --git a/erpnext/accounts/report/daily_sales_payment_summary/daily_sales_payment_summary.py b/erpnext/accounts/report/daily_sales_payment_summary/daily_sales_payment_summary.py
new file mode 100644
index 0000000..76d25d0
--- /dev/null
+++ b/erpnext/accounts/report/daily_sales_payment_summary/daily_sales_payment_summary.py
@@ -0,0 +1,233 @@
+# Copyright (c) 2015, Frappe Technologies Pvt. Ltd. and Contributors
+# License: GNU General Public License v3. See license.txt
+
+from __future__ import unicode_literals
+import frappe
+from frappe.utils import flt
+from frappe import msgprint, _
+
+def execute(filters=None):
+ return _execute(filters)
+
+def _execute(filters, additional_table_columns=None, additional_query_columns=None):
+ if not filters: filters = frappe._dict({})
+
+ invoice_list = get_invoices(filters, additional_query_columns)
+ columns, income_accounts, tax_accounts = get_columns(invoice_list, additional_table_columns)
+
+ if not invoice_list:
+ msgprint(_("No record found"))
+ return columns, invoice_list
+
+ invoice_income_map = get_invoice_income_map(invoice_list)
+ invoice_income_map, invoice_tax_map = get_invoice_tax_map(invoice_list,
+ invoice_income_map, income_accounts)
+ #Cost Center & Warehouse Map
+ invoice_cc_wh_map = get_invoice_cc_wh_map(invoice_list)
+ customers = list(set([inv.customer for inv in invoice_list]))
+ customer_map = get_customer_details(customers)
+ company_currency = frappe.db.get_value("Company", filters.get("company"), "default_currency")
+ mode_of_payments = get_mode_of_payments([inv.name for inv in invoice_list])
+
+ data = []
+ for inv in invoice_list:
+ # invoice details
+ cost_center = list(set(invoice_cc_wh_map.get(inv.name, {}).get("cost_center", [])))
+ warehouse = list(set(invoice_cc_wh_map.get(inv.name, {}).get("warehouse", [])))
+
+ customer_details = customer_map.get(inv.customer, {})
+ row = [
+ inv.name, inv.posting_date, inv.customer, inv.customer_name
+ ]
+
+ if additional_query_columns:
+ for col in additional_query_columns:
+ row.append(inv.get(col))
+
+ row +=[
+ ", ".join(mode_of_payments.get(inv.name, [])),
+ inv.owner,
+ ", ".join(cost_center), ", ".join(warehouse), company_currency
+ ]
+ # map income values
+ base_net_total = 0
+ for income_acc in income_accounts:
+ income_amount = flt(invoice_income_map.get(inv.name, {}).get(income_acc))
+ base_net_total += income_amount
+ row.append(income_amount)
+
+ # net total
+ row.append(base_net_total or inv.base_net_total)
+ # tax account
+ total_tax = 0
+ for tax_acc in tax_accounts:
+ if tax_acc not in income_accounts:
+ tax_amount = flt(invoice_tax_map.get(inv.name, {}).get(tax_acc))
+ total_tax += tax_amount
+ row.append(tax_amount)
+
+ # total tax, grand total, outstanding amount & rounded total
+ row += [total_tax, inv.base_grand_total,inv.paid_amount, inv.outstanding_amount]
+
+ data.append(row)
+
+ return columns, data
+
+def get_columns(invoice_list, additional_table_columns):
+ """return columns based on filters"""
+ columns = [
+ _("Invoice") + ":Link/Sales Invoice:120", _("Posting Date") + ":Date:80",
+ _("Customer") + ":Link/Customer:120", _("Customer Name") + "::120"
+ ]
+
+ if additional_table_columns:
+ columns += additional_table_columns
+
+ columns +=[
+ _("Mode of Payment") + "::120",
+ _("Owner") + "::150",
+ _("Cost Center") + ":Link/Cost Center:100", _("Warehouse") + ":Link/Warehouse:100",
+ {
+ "fieldname": "currency",
+ "label": _("Currency"),
+ "fieldtype": "Data",
+ "width": 80
+ }
+ ]
+
+ income_accounts = tax_accounts = income_columns = tax_columns = []
+
+ if invoice_list:
+ income_accounts = frappe.db.sql_list("""select distinct income_account
+ from `tabSales Invoice Item` where docstatus = 1 and parent in (%s)
+ order by income_account""" %
+ ', '.join(['%s']*len(invoice_list)), tuple([inv.name for inv in invoice_list]))
+
+ tax_accounts = frappe.db.sql_list("""select distinct account_head
+ from `tabSales Taxes and Charges` where parenttype = 'Sales Invoice'
+ and docstatus = 1 and base_tax_amount_after_discount_amount != 0
+ and parent in (%s) order by account_head""" %
+ ', '.join(['%s']*len(invoice_list)), tuple([inv.name for inv in invoice_list]))
+
+ income_columns = [(account + ":Currency/currency:120") for account in income_accounts]
+ for account in tax_accounts:
+ if account not in income_accounts:
+ tax_columns.append(account + ":Currency/currency:120")
+
+ columns = columns + income_columns + [_("Net Total") + ":Currency/currency:120"] + tax_columns + \
+ [_("Total Tax") + ":Currency/currency:120", _("Grand Total") + ":Currency/currency:120",
+ _("Paid Amount") + ":Currency/currency:120",
+ _("Outstanding Amount") + ":Currency/currency:120"]
+
+ return columns, income_accounts, tax_accounts
+
+def get_conditions(filters):
+ conditions = ""
+
+ if filters.get("company"): conditions += " and company=%(company)s"
+ if filters.get("customer"): conditions += " and customer = %(customer)s"
+ if filters.get("owner"): conditions += " and owner = %(owner)s"
+
+ if filters.get("from_date"): conditions += " and posting_date >= %(from_date)s"
+ if filters.get("to_date"): conditions += " and posting_date <= %(to_date)s"
+
+ if filters.get("mode_of_payment"):
+ conditions += """ and exists(select name from `tabSales Invoice Payment`
+ where parent=`tabSales Invoice`.name
+ and ifnull(`tabSales Invoice Payment`.mode_of_payment, '') = %(mode_of_payment)s)"""
+
+ if filters.get("cost_center"):
+ conditions += """ and exists(select name from `tabSales Invoice Item`
+ where parent=`tabSales Invoice`.name
+ and ifnull(`tabSales Invoice Item`.cost_center, '') = %(cost_center)s)"""
+
+ if filters.get("warehouse"):
+ conditions += """ and exists(select name from `tabSales Invoice Item`
+ where parent=`tabSales Invoice`.name
+ and ifnull(`tabSales Invoice Item`.warehouse, '') = %(warehouse)s)"""
+
+ if filters.get("is_pos"): conditions += " and is_pos = %(is_pos)s"
+
+ return conditions
+
+def get_invoices(filters, additional_query_columns):
+ if additional_query_columns:
+ additional_query_columns = ', ' + ', '.join(additional_query_columns)
+
+ conditions = get_conditions(filters)
+ return frappe.db.sql("""select name, posting_date, customer, customer_name, owner,
+ base_net_total, base_grand_total, paid_amount, outstanding_amount {0}
+ from `tabSales Invoice`
+ where docstatus = 1 %s order by posting_date desc, name desc""".format(additional_query_columns or '') %
+ conditions, filters, as_dict=1)
+
+def get_invoice_income_map(invoice_list):
+ income_details = frappe.db.sql("""select parent, income_account, sum(base_net_amount) as amount
+ from `tabSales Invoice Item` where parent in (%s) group by parent, income_account""" %
+ ', '.join(['%s']*len(invoice_list)), tuple([inv.name for inv in invoice_list]), as_dict=1)
+
+ invoice_income_map = {}
+ for d in income_details:
+ invoice_income_map.setdefault(d.parent, frappe._dict()).setdefault(d.income_account, [])
+ invoice_income_map[d.parent][d.income_account] = flt(d.amount)
+
+ return invoice_income_map
+
+def get_invoice_tax_map(invoice_list, invoice_income_map, income_accounts):
+ tax_details = frappe.db.sql("""select parent, account_head,
+ sum(base_tax_amount_after_discount_amount) as tax_amount
+ from `tabSales Taxes and Charges` where parent in (%s) group by parent, account_head""" %
+ ', '.join(['%s']*len(invoice_list)), tuple([inv.name for inv in invoice_list]), as_dict=1)
+
+ invoice_tax_map = {}
+ for d in tax_details:
+ if d.account_head in income_accounts:
+ if invoice_income_map[d.parent].has_key(d.account_head):
+ invoice_income_map[d.parent][d.account_head] += flt(d.tax_amount)
+ else:
+ invoice_income_map[d.parent][d.account_head] = flt(d.tax_amount)
+ else:
+ invoice_tax_map.setdefault(d.parent, frappe._dict()).setdefault(d.account_head, [])
+ invoice_tax_map[d.parent][d.account_head] = flt(d.tax_amount)
+
+ return invoice_income_map, invoice_tax_map
+
+def get_invoice_cc_wh_map(invoice_list):
+
+ si_items = frappe.db.sql("""select parent, cost_center, warehouse
+ from `tabSales Invoice Item` where parent in (%s)
+ and (ifnull(cost_center, '') != '' or ifnull(warehouse, '') != '')""" %
+ ', '.join(['%s']*len(invoice_list)), tuple([inv.name for inv in invoice_list]),as_dict=1)
+
+ invoice_cc_wh_map = {}
+ for d in si_items:
+ if d.cost_center:
+ invoice_cc_wh_map.setdefault(d.parent, frappe._dict()).setdefault(
+ "cost_center", []).append(d.cost_center)
+
+ if d.warehouse:
+ invoice_cc_wh_map.setdefault(d.parent, frappe._dict()).setdefault(
+ "warehouse", []).append(d.warehouse)
+
+ return invoice_cc_wh_map
+
+def get_customer_details(customers):
+ customer_map = {}
+ for cust in frappe.db.sql("""select name from `tabCustomer`
+ where name in (%s)""" % ", ".join(["%s"]*len(customers)), tuple(customers), as_dict=1):
+ customer_map.setdefault(cust.name, cust)
+
+ return customer_map
+
+
+def get_mode_of_payments(invoice_list):
+ mode_of_payments = {}
+ if invoice_list:
+ inv_mop = frappe.db.sql("""select parent, mode_of_payment
+ from `tabSales Invoice Payment` where parent in (%s) group by parent, mode_of_payment""" %
+ ', '.join(['%s']*len(invoice_list)), tuple(invoice_list), as_dict=1)
+
+ for d in inv_mop:
+ mode_of_payments.setdefault(d.parent, []).append(d.mode_of_payment)
+
+ return mode_of_payments
diff --git a/erpnext/config/accounts.py b/erpnext/config/accounts.py
index cdce13b..ef5350b 100644
--- a/erpnext/config/accounts.py
+++ b/erpnext/config/accounts.py
@@ -468,6 +468,12 @@
"name": "Customer Credit Balance",
"doctype": "Customer"
},
+ {
+ "type": "report",
+ "is_query_report": True,
+ "name": "Daily Sales Payment Summary",
+ "doctype": "Sales Invoice"
+ }
]
},
{