AR report: filter based on customer group and credit days based on field. Fixed #8214
diff --git a/erpnext/accounts/report/accounts_receivable/accounts_receivable.js b/erpnext/accounts/report/accounts_receivable/accounts_receivable.js
index 9bc5c2f..f98d037 100644
--- a/erpnext/accounts/report/accounts_receivable/accounts_receivable.js
+++ b/erpnext/accounts/report/accounts_receivable/accounts_receivable.js
@@ -17,6 +17,21 @@
"options": "Customer"
},
{
+ "fieldname":"customer_group",
+ "label": __("Customer Group"),
+ "fieldtype": "Link",
+ "options": "Customer Group"
+ },
+ {
+ "fieldname":"credit_days_based_on",
+ "label": __("Credit Days Based On"),
+ "fieldtype": "Select",
+ "options": "" + NEWLINE + "Fixed Days" + NEWLINE + "Last Day of the Next Month"
+ },
+ {
+ "fieldtype": "Break",
+ },
+ {
"fieldname":"report_date",
"label": __("As on Date"),
"fieldtype": "Date",
@@ -30,9 +45,6 @@
"default": "Posting Date"
},
{
- "fieldtype": "Break",
- },
- {
"fieldname":"range1",
"label": __("Ageing Range 1"),
"fieldtype": "Int",
diff --git a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py
index 6d1e168..62d0d5a 100644
--- a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py
+++ b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py
@@ -71,7 +71,10 @@
"width": 100
})
if args.get("party_type") == "Customer":
- columns += [_("Territory") + ":Link/Territory:80"]
+ columns += [
+ _("Territory") + ":Link/Territory:80",
+ _("Customer Group") + ":Link/Customer Group:120"
+ ]
if args.get("party_type") == "Supplier":
columns += [_("Supplier Type") + ":Link/Supplier Type:80"]
@@ -139,7 +142,7 @@
# customer territory / supplier type
if args.get("party_type") == "Customer":
- row += [self.get_territory(gle.party)]
+ row += [self.get_territory(gle.party), self.get_customer_group(gle.party)]
if args.get("party_type") == "Supplier":
row += [self.get_supplier_type(gle.party)]
@@ -186,6 +189,9 @@
def get_territory(self, party_name):
return self.get_party_map("Customer").get(party_name, {}).get("territory") or ""
+
+ def get_customer_group(self, party_name):
+ return self.get_party_map("Customer").get(party_name, {}).get("customer_group") or ""
def get_supplier_type(self, party_name):
return self.get_party_map("Supplier").get(party_name, {}).get("supplier_type") or ""
@@ -193,12 +199,12 @@
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)))
-
+ select_fields = "name, customer_name, territory, customer_group"
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)))
+ select_fields = "name, supplier_name, supplier_type"
+
+ self.party_map = dict(((r.name, r) for r in frappe.db.sql("select {0} from `tab{1}`"
+ .format(select_fields, party_type), as_dict=True)))
return self.party_map
@@ -251,6 +257,19 @@
conditions.append("party=%s")
values.append(self.filters.get(party_type_field))
+ if party_type_field=="customer":
+ if self.filters.get("customer_group"):
+ lft, rgt = frappe.db.get_value("Customer Group",
+ self.filters.get("customer_group"), ["lft", "rgt"])
+
+ conditions.append("""party in (select name from tabCustomer
+ where exists(select name from `tabCustomer Group` where lft >= {0} and rgt <= {1}
+ and name=tabCustomer.customer_group))""".format(lft, rgt))
+
+ if self.filters.get("credit_days_based_on"):
+ conditions.append("party in (select name from tabCustomer where credit_days_based_on=%s)")
+ values.append(self.filters.get("credit_days_based_on"))
+
return " and ".join(conditions), values
def get_gl_entries_for(self, party, party_type, against_voucher_type, against_voucher):
diff --git a/erpnext/accounts/report/accounts_receivable_summary/accounts_receivable_summary.js b/erpnext/accounts/report/accounts_receivable_summary/accounts_receivable_summary.js
index 5cc0566..a18c1a7 100644
--- a/erpnext/accounts/report/accounts_receivable_summary/accounts_receivable_summary.js
+++ b/erpnext/accounts/report/accounts_receivable_summary/accounts_receivable_summary.js
@@ -17,6 +17,21 @@
"options": "Customer"
},
{
+ "fieldname":"customer_group",
+ "label": __("Customer Group"),
+ "fieldtype": "Link",
+ "options": "Customer Group"
+ },
+ {
+ "fieldname":"credit_days_based_on",
+ "label": __("Credit Days Based On"),
+ "fieldtype": "Select",
+ "options": "" + NEWLINE + "Fixed Days" + NEWLINE + "Last Day of the Next Month"
+ },
+ {
+ "fieldtype": "Break",
+ },
+ {
"fieldname":"report_date",
"label": __("Date"),
"fieldtype": "Date",
@@ -30,9 +45,6 @@
"default": "Posting Date"
},
{
- "fieldtype": "Break",
- },
- {
"fieldname":"range1",
"label": __("Ageing Range 1"),
"fieldtype": "Int",
diff --git a/erpnext/accounts/report/accounts_receivable_summary/accounts_receivable_summary.py b/erpnext/accounts/report/accounts_receivable_summary/accounts_receivable_summary.py
index 8c627b6..81bcccb 100644
--- a/erpnext/accounts/report/accounts_receivable_summary/accounts_receivable_summary.py
+++ b/erpnext/accounts/report/accounts_receivable_summary/accounts_receivable_summary.py
@@ -27,7 +27,10 @@
str(self.filters.range3) + _("-Above") + ":Currency/currency:100"]
if args.get("party_type") == "Customer":
- columns += [_("Territory") + ":Link/Territory:80"]
+ columns += [
+ _("Territory") + ":Link/Territory:80",
+ _("Customer Group") + ":Link/Customer Group:120"
+ ]
if args.get("party_type") == "Supplier":
columns += [_("Supplier Type") + ":Link/Supplier Type:80"]
@@ -58,7 +61,7 @@
]
if args.get("party_type") == "Customer":
- row += [self.get_territory(party)]
+ row += [self.get_territory(party), self.get_customer_group(party)]
if args.get("party_type") == "Supplier":
row += [self.get_supplier_type(party)]
@@ -107,7 +110,7 @@
if args.get("party_type") == "Supplier":
cols += ["supplier_type", "remarks"]
if args.get("party_type") == "Customer":
- cols += ["territory", "remarks"]
+ cols += ["territory", "customer_group", "remarks"]
return self.make_data_dict(cols, voucherwise_data)