[report][enhancement] Payment period based on invoice date: show party columns and filter based on party
diff --git a/erpnext/accounts/doctype/journal_entry/journal_entry.py b/erpnext/accounts/doctype/journal_entry/journal_entry.py
index efbd1e9..3ba0596 100644
--- a/erpnext/accounts/doctype/journal_entry/journal_entry.py
+++ b/erpnext/accounts/doctype/journal_entry/journal_entry.py
@@ -458,11 +458,11 @@
if voucher_type=="Bank Entry":
account = frappe.db.get_value("Company", company, "default_bank_account")
if not account:
- account = frappe.db.get_value("Account", {"company": company, "account_type": "Bank"})
+ account = frappe.db.get_value("Account", {"company": company, "account_type": "Bank", "is_group": 0})
elif voucher_type=="Cash Entry":
account = frappe.db.get_value("Company", company, "default_cash_account")
if not account:
- account = frappe.db.get_value("Account", {"company": company, "account_type": "Cash"})
+ account = frappe.db.get_value("Account", {"company": company, "account_type": "Cash", "is_group": 0})
if account:
return {
diff --git a/erpnext/accounts/report/payment_period_based_on_invoice_date/payment_period_based_on_invoice_date.js b/erpnext/accounts/report/payment_period_based_on_invoice_date/payment_period_based_on_invoice_date.js
index fd03cc3..952540c 100644
--- a/erpnext/accounts/report/payment_period_based_on_invoice_date/payment_period_based_on_invoice_date.js
+++ b/erpnext/accounts/report/payment_period_based_on_invoice_date/payment_period_based_on_invoice_date.js
@@ -4,6 +4,14 @@
frappe.query_reports["Payment Period Based On Invoice Date"] = {
"filters": [
{
+ fieldname:"company",
+ label: __("Company"),
+ fieldtype: "Link",
+ options: "Company",
+ reqd: 1,
+ default: frappe.defaults.get_user_default("company")
+ },
+ {
fieldname: "from_date",
label: __("From Date"),
fieldtype: "Date",
@@ -23,27 +31,28 @@
default: "Incoming"
},
{
- fieldname:"account",
- label: __("Account"),
- fieldtype: "Link",
- options: "Account",
- get_query: function() {
+ "fieldname":"party_type",
+ "label": __("Party Type"),
+ "fieldtype": "Link",
+ "options": "DocType",
+ "get_query": function() {
return {
- query: "erpnext.controllers.queries.get_account_list",
- filters: {
- "report_type": "Balance Sheet",
- company: frappe.query_report.filters_by_name.company.get_value()
- }
+ filters: {"name": ["in", ["Customer", "Supplier"]]}
}
}
},
{
- fieldname:"company",
- label: __("Company"),
- fieldtype: "Link",
- options: "Company",
- reqd: 1,
- default: frappe.defaults.get_user_default("company")
+ "fieldname":"party",
+ "label": __("Party"),
+ "fieldtype": "Dynamic Link",
+ "get_options": function() {
+ var party_type = frappe.query_report.filters_by_name.party_type.get_value();
+ var party = frappe.query_report.filters_by_name.party.get_value();
+ if(party && !party_type) {
+ frappe.throw(__("Please select Party Type first"));
+ }
+ return party_type;
+ }
},
]
}
diff --git a/erpnext/accounts/report/payment_period_based_on_invoice_date/payment_period_based_on_invoice_date.py b/erpnext/accounts/report/payment_period_based_on_invoice_date/payment_period_based_on_invoice_date.py
index fdd6e1a..bf5c4e5 100644
--- a/erpnext/accounts/report/payment_period_based_on_invoice_date/payment_period_based_on_invoice_date.py
+++ b/erpnext/accounts/report/payment_period_based_on_invoice_date/payment_period_based_on_invoice_date.py
@@ -20,16 +20,16 @@
for d in entries:
if d.against_voucher:
against_date = d.against_voucher and invoice_posting_date_map[d.against_voucher] or ""
- outstanding_amount = flt(d.debit) or -1 * flt(d.credit)
+ payment_amount = flt(d.debit) or -1 * flt(d.credit)
else:
against_date = d.against_invoice and invoice_posting_date_map[d.against_invoice] or ""
- outstanding_amount = flt(d.credit) or -1 * flt(d.debit)
+ payment_amount = flt(d.credit) or -1 * flt(d.debit)
- row = [d.name, d.account, d.posting_date, d.against_voucher or d.against_invoice,
+ row = [d.name, d.party_type, d.party, d.posting_date, d.against_voucher or d.against_invoice,
against_date, d.debit, d.credit, d.cheque_no, d.cheque_date, d.remark]
if d.against_voucher or d.against_invoice:
- row += get_ageing_data(30, 60, 90, d.posting_date, against_date, outstanding_amount)
+ row += get_ageing_data(30, 60, 90, d.posting_date, against_date, payment_amount)
else:
row += ["", "", "", "", ""]
@@ -38,7 +38,8 @@
return columns, data
def get_columns():
- return [_("Journal Entry") + ":Link/Journal Entry:140", _("Account") + ":Link/Account:140",
+ return [_("Journal Entry") + ":Link/Journal Entry:140",
+ _("Party Type") + ":Link/DocType:100", _("Party") + ":Dynamic Link/party_type:140",
_("Posting Date") + ":Date:100", _("Against Invoice") + ":Link/Purchase Invoice:130",
_("Against Invoice Posting Date") + ":Date:130", _("Debit") + ":Currency:120", _("Credit") + ":Currency:120",
_("Reference No") + "::100", _("Reference Date") + ":Date:100", _("Remarks") + "::150", _("Age") +":Int:40",
@@ -46,41 +47,38 @@
]
def get_conditions(filters):
- conditions = ""
- party = None
+ conditions = []
- if filters.get("account"):
- party = filters["account"]
- else:
- conditions += " and company = '%s'" % frappe.db.escape(filters["company"])
+ if not filters.get("party_type"):
+ if filters.get("payment_type") == "Outgoing":
+ filters["party_type"] = "Supplier"
+ else:
+ filters["party_type"] = "Customer"
+
+ if filters.get("party_type"):
+ conditions.append("jvd.party_type=%(party_type)s")
- account_type = "Receivable" if filters.get("payment_type") == "Incoming" else "Payable"
+ if filters.get("party"):
+ conditions.append("jvd.party=%(party)s")
- conditions += """ and account in
- (select name from tabAccount
- where account_type = '{0}'
- and company='{1}')""".format(account_type, frappe.db.escape(filters["company"]))
-
- if party:
- conditions += " and jvd.party = '%s'" % frappe.db.escape(party)
- else:
- conditions += " and ifnull(jvd.party, '') != ''"
+ if filters.get("company"):
+ conditions.append("jv.company=%(company)s")
if filters.get("from_date"):
- conditions += " and jv.posting_date >= '%s'" % filters["from_date"]
+ conditions.append("jv.posting_date >= %(from_date)s")
if filters.get("to_date"):
- conditions += " and jv.posting_date <= '%s'" % filters["to_date"]
+ conditions.append("jv.posting_date <= %(to_date)s")
- return conditions
+ return "and {}".format(" and ".join(conditions)) if conditions else ""
def get_entries(filters):
conditions = get_conditions(filters)
- entries = frappe.db.sql("""select jv.name, jvd.account, jv.posting_date,
+ entries = frappe.db.sql("""select jv.name, jvd.party_type, jvd.party, jv.posting_date,
jvd.against_voucher, jvd.against_invoice, jvd.debit, jvd.credit,
jv.cheque_no, jv.cheque_date, jv.remark
from `tabJournal Entry Account` jvd, `tabJournal Entry` jv
where jvd.parent = jv.name and jv.docstatus=1 %s order by jv.name DESC""" %
- conditions, as_dict=1, debug=1)
+ conditions, filters, as_dict=1)
return entries