Commonify AR and AP reports
diff --git a/erpnext/accounts/report/accounts_payable/accounts_payable.py b/erpnext/accounts/report/accounts_payable/accounts_payable.py
index 3972421..c72564c 100644
--- a/erpnext/accounts/report/accounts_payable/accounts_payable.py
+++ b/erpnext/accounts/report/accounts_payable/accounts_payable.py
@@ -3,132 +3,12 @@
from __future__ import unicode_literals
import frappe
-from frappe.utils import getdate, nowdate, flt, cstr, cint
-from frappe import msgprint, _
-from erpnext.accounts.report.accounts_receivable.accounts_receivable import get_ageing_data
+from erpnext.accounts.report.accounts_receivable.accounts_receivable import ReceivablePayableReport
def execute(filters=None):
- if not filters: filters = {}
- supplier_naming_by = frappe.db.get_value("Buying Settings", None, "supp_master_name")
- columns = get_columns(filters, supplier_naming_by)
- entries = get_gl_entries(filters)
-
- entries_after_report_date = [[gle.voucher_type, gle.voucher_no]
- for gle in get_gl_entries(filters, before_report_date=False)]
-
- supplier_details = get_supplier_details()
- voucher_detail_map = get_voucher_details()
-
- # Age of the invoice on this date
- age_on = getdate(filters.get("report_date")) > getdate(nowdate()) \
- and nowdate() or filters.get("report_date")
-
- data = []
- for gle in entries:
- if cstr(gle.against_voucher) == gle.voucher_no or not gle.against_voucher \
- or [gle.against_voucher_type, gle.against_voucher] in entries_after_report_date \
- or (gle.against_voucher_type == "Purchase Order"):
- voucher_details = voucher_detail_map.get(gle.voucher_type, {}).get(gle.voucher_no, {})
-
- invoiced_amount = gle.credit > 0 and gle.credit or 0
- outstanding_amount = get_outstanding_amount(gle,
- filters.get("report_date") or nowdate())
-
- if abs(flt(outstanding_amount)) > 0.01:
- paid_amount = invoiced_amount - outstanding_amount
- row = [gle.posting_date, gle.party]
-
- if supplier_naming_by == "Naming Series":
- row += [supplier_details.get(gle.party, {}).supplier_name or ""]
-
- row += [gle.voucher_type, gle.voucher_no, voucher_details.get("due_date", ""),
- voucher_details.get("bill_no", ""), voucher_details.get("bill_date", ""),
- invoiced_amount, paid_amount, outstanding_amount]
-
- # Ageing
- if filters.get("ageing_based_on") == "Due Date":
- ageing_based_on_date = voucher_details.get("due_date", "")
- else:
- ageing_based_on_date = gle.posting_date
-
- row += get_ageing_data(cint(filters.get("range1")), cint(filters.get("range2")), \
- cint(filters.get("range3")), age_on, ageing_based_on_date, outstanding_amount) + \
- [supplier_details.get(gle.party).supplier_type, gle.remarks]
-
- data.append(row)
-
- # for i in range(0, len(data)):
- # data[i].insert(4, """<a href="%s"><i class="icon icon-share" style="cursor: pointer;"></i></a>""" \
- # % ("/".join(["#Form", data[i][2], data[i][3]]),))
-
- return columns, data
-
-def get_columns(supplier_naming_by):
- columns = [_("Posting Date") + ":Date:80", _("Supplier") + ":Link/Supplier:150"]
-
- if supplier_naming_by == "Naming Series":
- columns += ["Supplier Name::110"]
-
- columns +=[_("Voucher Type") + "::110", _("Voucher No") + ":Dynamic Link/Voucher Type:120",
- _("Due Date") + ":Date:80", _("Bill No") + "::80", _("Bill Date") + ":Date:80",
- _("Invoiced Amount") + ":Currency:100", _("Paid Amount") + ":Currency:100",
- _("Outstanding Amount") + ":Currency:100", _("Age") + ":Int:50",
- "0-" + filters.get("range1") + ":Currency:100",
- filters.get("range1") + "-" + filters.get("range2") + ":Currency:100",
- filters.get("range2") + "-" + filters.get("range3") + ":Currency:100",
- filters.get("range3") + _("-Above") + ":Currency:100",
- _("Supplier Type") + ":Link/Supplier Type:150", _("Remarks") + "::150"
- ]
-
- return columns
-
-def get_gl_entries(filters, before_report_date=True):
- conditions = get_conditions(filters, before_report_date)
- gl_entries = []
- gl_entries = frappe.db.sql("""select * from `tabGL Entry`
- where docstatus < 2 and party_type='Supplier' %s
- order by posting_date, party""" % conditions, as_dict=1)
- return gl_entries
-
-def get_conditions(filters, before_report_date=True):
- conditions = ""
- if filters.get("company"):
- conditions += " and company='%s'" % filters["company"].replace("'", "\'")
-
- if filters.get("supplier"):
- conditions += " and party='%s'" % filters["supplier"].replace("'", "\'")
-
- if filters.get("report_date"):
- if before_report_date:
- conditions += " and posting_date<='%s'" % filters["report_date"]
- else:
- conditions += " and posting_date>'%s'" % filters["report_date"]
-
- return conditions
-
-def get_supplier_details():
- supplier_details = {}
- for d in frappe.db.sql("""select name, supplier_type, supplier_name from `tabSupplier`""", as_dict=1):
- supplier_details.setdefault(d.name, d)
-
- return supplier_details
-
-def get_voucher_details():
- voucher_details = {}
- for dt in ["Purchase Invoice", "Journal Voucher"]:
- voucher_details.setdefault(dt, frappe._dict())
- for t in frappe.db.sql("""select name, due_date, bill_no, bill_date from `tab%s`""" % dt, as_dict=1):
- voucher_details[dt].setdefault(t.name, t)
-
- return voucher_details
-
-def get_outstanding_amount(gle, report_date):
- payment_amount = frappe.db.sql("""
- select sum(ifnull(debit, 0)) - sum(ifnull(credit, 0))
- from `tabGL Entry`
- where party_type='Supplier' and party = %s and posting_date <= %s and against_voucher_type = %s
- and against_voucher = %s and name != %s""",
- (gle.party, report_date, gle.voucher_type, gle.voucher_no, gle.name))[0][0]
-
- outstanding_amount = flt(gle.credit) - flt(gle.debit) - flt(payment_amount)
- return outstanding_amount
+ args = {
+ "party_type": "Supplier",
+ "dr_or_cr": "credit",
+ "naming_by": ["Buying Settings", "supp_master_name"],
+ }
+ return ReceivablePayableReport(filters).run(args)
diff --git a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py
index a0ae70e..d5bc872 100644
--- a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py
+++ b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py
@@ -3,10 +3,10 @@
from __future__ import unicode_literals
import frappe
-from frappe import _
+from frappe import _, scrub
from frappe.utils import getdate, nowdate, flt, cint
-class AccountsReceivableReport(object):
+class ReceivablePayableReport(object):
def __init__(self, filters=None):
self.filters = frappe._dict(filters or {})
self.filters.report_date = getdate(self.filters.report_date or nowdate())
@@ -14,140 +14,191 @@
if self.filters.report_date > getdate(nowdate()) \
else self.filters.report_date
- def run(self):
- customer_naming_by = frappe.db.get_value("Selling Settings", None, "cust_master_name")
- return self.get_columns(customer_naming_by), self.get_data(customer_naming_by)
+ 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, args), self.get_data(party_naming_by, args)
- def get_columns(self, customer_naming_by):
- columns = [_("Posting Date") + ":Date:80", _("Customer") + ":Link/Customer:200"]
+ def get_columns(self, party_naming_by, args):
+ columns = [_("Posting Date") + ":Date:80", _(args.get("party_type")) + ":Link/" + args.get("party_type") + ":200"]
- if customer_naming_by == "Naming Series":
- columns += ["Customer Name::110"]
+ if party_naming_by == "Naming Series":
+ columns += [args.get("party_type") + " Name::110"]
columns += [_("Voucher Type") + "::110", _("Voucher No") + ":Dynamic Link/Voucher Type:120",
- _("Due Date") + ":Date:80", _("Invoiced Amount") + ":Currency:100",
- _("Payment Received") + ":Currency:100", _("Outstanding Amount") + ":Currency:100",
- _("Age") + ":Int:50", "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", _("Remarks") + "::200"
+ _("Due Date") + ":Date:80"]
+
+ if args.get("party_type") == "Supplier":
+ columns += [_("Bill No") + "::80", _("Bill Date") + ":Date:80"]
+
+ columns += [_("Invoiced Amount") + ":Currency:100", _("Paid Amount") + ":Currency:100",
+ _("Outstanding Amount") + ":Currency:100", _("Age") + ":Int:50",
+ "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"
]
+ if args.get("party_type") == "Customer":
+ columns += [_("Territory") + ":Link/Territory:80"]
+ if args.get("party_type") == "Supplier":
+ columns += [_("Supplier Type") + ":Link/Supplier Type:80"]
+ columns += [_("Remarks") + "::200"]
+
return columns
- def get_data(self, customer_naming_by):
+ def get_data(self, party_naming_by, args):
from erpnext.accounts.utils import get_currency_precision
currency_precision = get_currency_precision() or 2
-
data = []
- future_vouchers = self.get_entries_after(self.filters.report_date)
- for gle in self.get_entries_till(self.filters.report_date):
- if self.is_receivable(gle, future_vouchers):
- outstanding_amount = self.get_outstanding_amount(gle, self.filters.report_date)
+ dr_or_cr = args.get("dr_or_cr")
+ 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 abs(outstanding_amount) > 0.1/10**currency_precision:
- due_date = self.get_due_date(gle)
- invoiced_amount = gle.debit if (gle.debit > 0) else 0
- payment_received = invoiced_amount - outstanding_amount
-
- row = [gle.posting_date, gle.party]
- if customer_naming_by == "Naming Series":
- row += [self.get_customer_name(gle.party)]
-
- row += [gle.voucher_type, gle.voucher_no, due_date, invoiced_amount,
- payment_received, outstanding_amount]
-
+ due_date = self.get_due_date(args.get("party_type"), gle)
+ 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
- row += get_ageing_data(cint(self.filters.range1), cint(self.filters.range2), \
- cint(self.filters.range3), self.age_as_on, entry_date, outstanding_amount) + \
- [self.get_territory(gle.account), gle.remarks]
+ row = [gle.posting_date, gle.party]
+
+ if party_naming_by == "Naming Series":
+ row += [self.get_party_name(gle.party_type, gle.party)]
+
+ row += [gle.voucher_type, gle.voucher_no, due_date]
+
+ if args.get("party_type") == "Supplier":
+ row += self.get_supplier_bill_data(gle)
+
+ row += [invoiced_amount, paid_amt, outstanding_amount] + \
+ get_ageing_data(cint(self.filters.range1), cint(self.filters.range2), \
+ cint(self.filters.range3), self.age_as_on, entry_date, outstanding_amount)
+
+ if args.get("party_type") == "Customer":
+ row += [self.get_territory(gle.party), gle.remarks]
+ if args.get("party_type") == "Supplier":
+ row += [self.get_supplier_type(gle.party), gle.remarks]
data.append(row)
return data
- def get_entries_after(self, report_date):
+ def get_entries_after(self, report_date, party_type):
# returns a distinct list
- return list(set([(e.voucher_type, e.voucher_no) for e in self.get_gl_entries()
+ return list(set([(e.voucher_type, e.voucher_no) for e in self.get_gl_entries(party_type)
if getdate(e.posting_date) > report_date]))
- def get_entries_till(self, report_date):
+ def get_entries_till(self, report_date, party_type):
# returns a generator
- return (e for e in self.get_gl_entries()
+ return (e for e in self.get_gl_entries(party_type)
if getdate(e.posting_date) <= report_date)
- def is_receivable(self, gle, future_vouchers):
+ def is_receivable_or_payable(self, gle, dr_or_cr, future_vouchers):
return (
# advance
(not gle.against_voucher) or
- # against sales order
- (gle.against_voucher_type == "Sales Order") or
+ # against sales order/purchase order
+ (gle.against_voucher_type in ["Sales Order", "Purchase Order"]) or
- # sales invoice
- (gle.against_voucher==gle.voucher_no and gle.debit > 0) or
+ # sales invoice/purchase invoice
+ (gle.against_voucher==gle.voucher_no and gle.get(dr_or_cr) > 0) or
# entries adjusted with future vouchers
((gle.against_voucher_type, gle.against_voucher) in future_vouchers)
)
- def get_outstanding_amount(self, gle, report_date):
- payment_received = 0.0
- for e in self.get_gl_entries_for(gle.party, gle.voucher_type, gle.voucher_no):
+ def get_outstanding_amount(self, gle, report_date, dr_or_cr):
+ 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_received += (flt(e.credit) - flt(e.debit))
+ payment_amount += (flt(e.credit if dr_or_cr == "debit" else e.debit) - flt(e.get(dr_or_cr)))
- return flt(gle.debit) - flt(gle.credit) - payment_received
+ return flt(gle.get(dr_or_cr)) - flt(gle.credit if dr_or_cr == "debit" else gle.debit) - payment_amount
- def get_customer_name(self, customer):
- return self.get_customer_map().get(customer, {}).get("customer_name") or ""
+ 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 ""
- def get_territory(self, customer):
- return self.get_customer_map().get(customer, {}).get("territory") or ""
+ def get_territory(self, party_name):
+ return self.get_party_map("Customer").get(party_name, {}).get("territory") or ""
- def get_customer_map(self):
- if not hasattr(self, "customer_map"):
- self.customer_map = dict(((r.name, r) for r in frappe.db.sql("""select
- name, customer_name, territory from `tabCustomer`""", as_dict=True)))
+ def get_supplier_type(self, party_name):
+ return self.get_party_map("Supplier").get(party_name, {}).get("supplier_type") or ""
- return self.customer_map
+ def get_party_map(self, party_type):
+ if not hasattr(self, "party_map"):
+ if party_type == "Customer":
+ self.party_map = dict(((r.name, r) for r in frappe.db.sql("""select {0}, {1}, {2} from `tab{3}`"""
+ .format("name", "customer_name", "territory", party_type), as_dict=True)))
- def get_due_date(self, gle):
- if not hasattr(self, "invoice_due_date_map"):
- # TODO can be restricted to posting date
- self.invoice_due_date_map = dict(frappe.db.sql("""select name, due_date
+ elif party_type == "Supplier":
+ self.party_map = dict(((r.name, r) for r in frappe.db.sql("""select {0}, {1}, {2} from `tab{3}`"""
+ .format("name", "supplier_name", "supplier_type", party_type), as_dict=True)))
+
+ 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_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 ""
+
+ 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"""))
- return gle.voucher_type == "Sales Invoice" \
- and self.invoice_due_date_map.get(gle.voucher_no) or ""
+ voucher_details = {}
+ get_voucher_details = frappe.db.sql("""select name, due_date, bill_no, bill_date
+ from `tabPurchase Invoice` where docstatus=1""")
- def get_gl_entries(self):
+ 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)
+
+ def get_gl_entries(self, party_type):
if not hasattr(self, "gl_entries"):
- conditions, values = self.prepare_conditions()
+ conditions, values = self.prepare_conditions(party_type)
self.gl_entries = frappe.db.sql("""select * from `tabGL Entry`
- where docstatus < 2 and party_type='Customer' {0}
+ where docstatus < 2 and party_type=%(party_type)s {0}
order by posting_date, party""".format(conditions), values, as_dict=True)
return self.gl_entries
- def prepare_conditions(self):
+ def prepare_conditions(self, party_type):
conditions = [""]
values = {}
+ party_type_field = scrub(party_type)
+
+ if party_type:
+ values["party_type"] = party_type
if self.filters.company:
conditions.append("company=%(company)s")
values["company"] = self.filters.company
- if self.filters.customer:
- conditions.append("party=%(customer)s")
- values["customer"] = self.filters.customer
+ if self.filters.get(party_type_field):
+ conditions.append("party=%(party)s")
+ values["party"] = self.filters.get(party_type_field)
return " and ".join(conditions), values
- def get_gl_entries_for(self, party, against_voucher_type, against_voucher):
+ def get_gl_entries_for(self, party, party_type, against_voucher_type, against_voucher):
if not hasattr(self, "gl_entries_map"):
self.gl_entries_map = {}
- for gle in self.get_gl_entries():
+ for gle in self.get_gl_entries(party_type):
if gle.against_voucher_type and gle.against_voucher:
self.gl_entries_map.setdefault(gle.party, {})\
.setdefault(gle.against_voucher_type, {})\
@@ -159,7 +210,12 @@
.get(against_voucher, [])
def execute(filters=None):
- return AccountsReceivableReport(filters).run()
+ args = {
+ "party_type": "Customer",
+ "dr_or_cr": "debit",
+ "naming_by": ["Selling Settings", "cust_master_name"],
+ }
+ return ReceivablePayableReport(filters).run(args)
def get_ageing_data(first_range, second_range, third_range, age_as_on, entry_date, outstanding_amount):
# [0-30, 30-60, 60-90, 90-above]