[#1379] Add Accounts Receivable Summary Report
diff --git a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py
index d5bc872..d7b7458 100644
--- a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py
+++ b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py
@@ -50,14 +50,15 @@
from erpnext.accounts.utils import get_currency_precision
currency_precision = get_currency_precision() or 2
data = []
- dr_or_cr = args.get("dr_or_cr")
+ dr_or_cr = "debit" if args.get("party_type") == "Customer" else "credit"
+ voucher_details = self.get_voucher_details()
future_vouchers = self.get_entries_after(self.filters.report_date, args.get("party_type"))
for gle in self.get_entries_till(self.filters.report_date, args.get("party_type")):
- if self.is_receivable_or_payable(gle, args.get("dr_or_cr"), future_vouchers):
- outstanding_amount = self.get_outstanding_amount(gle, self.filters.report_date, args.get("dr_or_cr"))
+ if self.is_receivable_or_payable(gle, dr_or_cr, future_vouchers):
+ outstanding_amount = self.get_outstanding_amount(gle, self.filters.report_date, dr_or_cr)
if abs(outstanding_amount) > 0.1/10**currency_precision:
- due_date = self.get_due_date(args.get("party_type"), gle)
+ due_date = voucher_details.get("voucher_no", {}).get("due_date", "")
invoiced_amount = gle.get(dr_or_cr) if (gle.get(dr_or_cr) > 0) else 0
paid_amt = invoiced_amount - outstanding_amount
entry_date = due_date if self.filters.ageing_based_on == "Due Date" else gle.posting_date
@@ -69,7 +70,10 @@
row += [gle.voucher_type, gle.voucher_no, due_date]
if args.get("party_type") == "Supplier":
- row += self.get_supplier_bill_data(gle)
+ row += [
+ voucher_details.get("voucher_no", {}).get("bill_no", ""),
+ voucher_details.get("voucher_no", {}).get("bill_date", "")
+ ]
row += [invoiced_amount, paid_amt, outstanding_amount] + \
get_ageing_data(cint(self.filters.range1), cint(self.filters.range2), \
@@ -113,9 +117,9 @@
payment_amount = 0.0
for e in self.get_gl_entries_for(gle.party, gle.party_type, gle.voucher_type, gle.voucher_no):
if getdate(e.posting_date) <= report_date and e.name!=gle.name:
- payment_amount += (flt(e.credit if dr_or_cr == "debit" else e.debit) - flt(e.get(dr_or_cr)))
+ payment_amount += (flt(e.credit if gle.party_type == "Customer" else e.debit) - flt(e.get(dr_or_cr)))
- return flt(gle.get(dr_or_cr)) - flt(gle.credit if dr_or_cr == "debit" else gle.debit) - payment_amount
+ return flt(gle.get(dr_or_cr)) - flt(gle.credit if gle.party_type == "Customer" else gle.debit) - payment_amount
def get_party_name(self, party_type, party_name):
return self.get_party_map(party_type).get(party_name, {}).get("customer_name" if party_type == "Customer" else "supplier_name") or ""
@@ -138,35 +142,18 @@
return self.party_map
- def get_due_date(self, party_type, gle):
- self.get_voucher_details(gle)
- if party_type == "Customer":
- return self.voucher_detail_map.get(gle.voucher_no) if gle.voucher_type == "Sales Invoice" else ""
- elif party_type == "Supplier":
- return self.voucher_detail_map.get(gle.voucher_no).get("due_date") \
- if gle.voucher_type in ["Purchase Invoice", "Journal Voucher"] else ""
+ def get_voucher_details(self):
+ voucher_details = frappe._dict()
- def get_supplier_bill_data(self, gle):
- self.get_voucher_details(gle)
- return [self.voucher_detail_map.get(gle.voucher_no).get("bill_no"), \
- self.voucher_detail_map.get(gle.voucher_no).get("bill_date")] \
- if gle.voucher_type in ["Purchase Invoice", "Journal Voucher"] else ""
+ for si in frappe.db.sql("""select name, due_date
+ from `tabSales Invoice` where docstatus=1""", as_dict=1):
+ voucher_details.setdefault(si.name, si)
- def get_voucher_details(self, gle):
- # TODO can be restricted to posting date
- if not hasattr(self, "voucher_detail_map"):
- self.voucher_detail_map = dict(frappe.db.sql("""select name, due_date
- from `tabSales Invoice` where docstatus=1"""))
+ for pi in frappe.db.sql("""select name, due_date, bill_no, bill_date
+ from `tabPurchase Invoice` where docstatus=1""", as_dict=1):
+ voucher_details.setdefault(pi.name, pi)
- voucher_details = {}
- get_voucher_details = frappe.db.sql("""select name, due_date, bill_no, bill_date
- from `tabPurchase Invoice` where docstatus=1""")
-
- for voucher_name, due_date, bill_no, bill_date in get_voucher_details:
- voucher_details.setdefault(voucher_name, {}).update({"due_date": due_date,
- "bill_no": bill_no, "bill_date": bill_date})
-
- self.voucher_detail_map.update(voucher_details)
+ return voucher_details
def get_gl_entries(self, party_type):
if not hasattr(self, "gl_entries"):
@@ -212,7 +199,7 @@
def execute(filters=None):
args = {
"party_type": "Customer",
- "dr_or_cr": "debit",
+ # "dr_or_cr": "debit",
"naming_by": ["Selling Settings", "cust_master_name"],
}
return ReceivablePayableReport(filters).run(args)
diff --git a/erpnext/accounts/report/accounts_receivable_summary/__init__.py b/erpnext/accounts/report/accounts_receivable_summary/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/accounts/report/accounts_receivable_summary/__init__.py
diff --git a/erpnext/accounts/report/accounts_receivable_summary/accounts_receivable_summary.js b/erpnext/accounts/report/accounts_receivable_summary/accounts_receivable_summary.js
new file mode 100644
index 0000000..c60d916
--- /dev/null
+++ b/erpnext/accounts/report/accounts_receivable_summary/accounts_receivable_summary.js
@@ -0,0 +1,57 @@
+// Copyright (c) 2013, Web Notes Technologies Pvt. Ltd. and Contributors
+// License: GNU General Public License v3. See license.txt
+
+frappe.query_reports["Accounts Receivable Summary"] = {
+ "filters": [
+ {
+ "fieldname":"company",
+ "label": __("Company"),
+ "fieldtype": "Link",
+ "options": "Company",
+ "default": frappe.defaults.get_user_default("company")
+ },
+ {
+ "fieldname":"customer",
+ "label": __("Customer"),
+ "fieldtype": "Link",
+ "options": "Customer"
+ },
+ {
+ "fieldname":"report_date",
+ "label": __("Date"),
+ "fieldtype": "Date",
+ "default": get_today()
+ },
+ {
+ "fieldname":"ageing_based_on",
+ "label": __("Ageing Based On"),
+ "fieldtype": "Select",
+ "options": 'Posting Date' + NEWLINE + 'Due Date',
+ "default": "Posting Date"
+ },
+ {
+ "fieldtype": "Break",
+ },
+ {
+ "fieldname":"range1",
+ "label": __("Ageing Range 1"),
+ "fieldtype": "Int",
+ "default": "30",
+ "reqd": 1
+ },
+ {
+ "fieldname":"range2",
+ "label": __("Ageing Range 2"),
+ "fieldtype": "Int",
+ "default": "60",
+ "reqd": 1
+ },
+ {
+ "fieldname":"range3",
+ "label": __("Ageing Range 3"),
+ "fieldtype": "Int",
+ "default": "90",
+ "reqd": 1
+ }
+ ]
+}
diff --git a/erpnext/accounts/report/accounts_receivable_summary/accounts_receivable_summary.json b/erpnext/accounts/report/accounts_receivable_summary/accounts_receivable_summary.json
new file mode 100644
index 0000000..79e9e6e
--- /dev/null
+++ b/erpnext/accounts/report/accounts_receivable_summary/accounts_receivable_summary.json
@@ -0,0 +1,17 @@
+{
+ "add_total_row": 1,
+ "apply_user_permissions": 1,
+ "creation": "2014-10-17 15:45:00.694265",
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "is_standard": "Yes",
+ "modified": "2014-10-17 15:45:00.694265",
+ "modified_by": "Administrator",
+ "module": "Accounts",
+ "name": "Accounts Receivable Summary",
+ "owner": "Administrator",
+ "ref_doctype": "Sales Invoice",
+ "report_name": "Accounts Receivable Summary",
+ "report_type": "Script Report"
+}
\ No newline at end of file
diff --git a/erpnext/accounts/report/accounts_receivable_summary/accounts_receivable_summary.py b/erpnext/accounts/report/accounts_receivable_summary/accounts_receivable_summary.py
new file mode 100644
index 0000000..16bc6bc
--- /dev/null
+++ b/erpnext/accounts/report/accounts_receivable_summary/accounts_receivable_summary.py
@@ -0,0 +1,96 @@
+# Copyright (c) 2013, Web Notes Technologies Pvt. Ltd. and Contributors and contributors
+# For license information, please see license.txt
+
+from __future__ import unicode_literals
+import frappe
+from frappe import _
+from erpnext.accounts.report.accounts_receivable.accounts_receivable import ReceivablePayableReport
+
+class AccountsReceivableSummary(ReceivablePayableReport):
+ def run(self, args):
+ party_naming_by = frappe.db.get_value(args.get("naming_by")[0], None, args.get("naming_by")[1])
+ return self.get_columns(party_naming_by), self.get_data(party_naming_by, args)
+
+ def get_columns(self, party_naming_by):
+ columns = [_("Customer") + ":Link/Customer:200"]
+
+ if party_naming_by == "Naming Series":
+ columns += ["Customer Name::110"]
+
+ columns += [_("Total Invoiced Amount") + ":Currency:100",
+ _("Total Paid Amount") + ":Currency:100", _("Total Outstanding Amount") + ":Currency:100",
+ "0-" + self.filters.range1 + ":Currency:100",
+ self.filters.range1 + "-" + self.filters.range2 + ":Currency:100",
+ self.filters.range2 + "-" + self.filters.range3 + ":Currency:100",
+ self.filters.range3 + _("-Above") + ":Currency:100",
+ _("Territory") + ":Link/Territory:80"
+ ]
+
+ return columns
+
+ def get_data(self, party_naming_by, args):
+ data = []
+ prev_columns, prev_data = ReceivablePayableReport(self.filters).run(args)
+ total_amount_dict = frappe._dict()
+
+ key_list = ["posting_date", "customer"]
+
+ if party_naming_by == "Naming Series":
+ key_list += ["customer_name"]
+
+ key_list += ["voucher_type", "voucher_no", "due_date", "invoiced_amt", "paid_amt",
+ "outstanding_amt", "age", "range1", "range2", "range3", "range4", "territory", "remarks"]
+
+ data_dict = self.make_data_dict(key_list, prev_data)
+
+ for d in data_dict:
+ if d["customer"] in total_amount_dict:
+ customer_key = total_amount_dict[d.customer]
+ customer_key["total_invoiced_amt"] += d.get("invoiced_amt")
+ customer_key["total_paid_amt"] += d.get("paid_amt")
+ customer_key["total_outstanding_amt"]+= d.get("outstanding_amt")
+ customer_key["total_range1"] += d.get("range1")
+ customer_key["total_range2"] += d.get("range2")
+ customer_key["total_range3"] += d.get("range3")
+ customer_key["total_range4"] += d.get("range4")
+ else:
+ total_amount_dict.setdefault(d.get("customer"), {}).update({
+ "total_invoiced_amt": d.get("invoiced_amt"),
+ "total_paid_amt": d.get("paid_amt"),
+ "total_outstanding_amt": d.get("outstanding_amt"),
+ "total_range1": d.get("range1"),
+ "total_range2": d.get("range2"),
+ "total_range3": d.get("range3"),
+ "total_range4": d.get("range4")
+ })
+
+ for i in total_amount_dict:
+ row = [i]
+
+ if party_naming_by == "Naming Series":
+ row += [self.get_party_name("Customer", i)]
+
+ row += [total_amount_dict[i]["total_invoiced_amt"], total_amount_dict[i]["total_paid_amt"],
+ total_amount_dict[i]["total_outstanding_amt"], total_amount_dict[i]["total_range1"],
+ total_amount_dict[i]["total_range2"], total_amount_dict[i]["total_range3"],
+ total_amount_dict[i]["total_range4"], self.get_territory(i)]
+
+ data.append(row)
+
+ return data
+
+ def make_data_dict(self, key_list, data):
+ make_data_dict = []
+ for d in data:
+ make_data_dict.append(frappe._dict(zip(key_list, d)))
+
+ return make_data_dict
+
+def execute(filters=None):
+ args = {
+ "party_type": "Customer",
+ "dr_or_cr": "debit",
+ "naming_by": ["Selling Settings", "cust_master_name"],
+ }
+
+ return AccountsReceivableSummary(filters).run(args)