Merge pull request #30499 from ruthra-kumar/payment_terms_status_filter
feat: 'customer' column and more filter to Payment terms status report
diff --git a/erpnext/selling/report/payment_terms_status_for_sales_order/payment_terms_status_for_sales_order.js b/erpnext/selling/report/payment_terms_status_for_sales_order/payment_terms_status_for_sales_order.js
index 0e36b3f..c068ae3 100644
--- a/erpnext/selling/report/payment_terms_status_for_sales_order/payment_terms_status_for_sales_order.js
+++ b/erpnext/selling/report/payment_terms_status_for_sales_order/payment_terms_status_for_sales_order.js
@@ -27,28 +27,55 @@
"default": frappe.datetime.get_today()
},
{
- "fieldname":"sales_order",
- "label": __("Sales Order"),
- "fieldtype": "MultiSelectList",
+ "fieldname":"customer_group",
+ "label": __("Customer Group"),
+ "fieldtype": "Link",
"width": 100,
- "options": "Sales Order",
- "get_data": function(txt) {
- return frappe.db.get_link_options("Sales Order", txt, this.filters());
- },
- "filters": () => {
- return {
- docstatus: 1,
- payment_terms_template: ['not in', ['']],
- company: frappe.query_report.get_filter_value("company"),
- transaction_date: ['between', [frappe.query_report.get_filter_value("period_start_date"), frappe.query_report.get_filter_value("period_end_date")]]
+ "options": "Customer Group",
+ },
+ {
+ "fieldname":"customer",
+ "label": __("Customer"),
+ "fieldtype": "Link",
+ "width": 100,
+ "options": "Customer",
+ "get_query": () => {
+ var customer_group = frappe.query_report.get_filter_value('customer_group');
+ return{
+ "query": "erpnext.selling.report.payment_terms_status_for_sales_order.payment_terms_status_for_sales_order.get_customers_or_items",
+ "filters": [
+ ['Customer', 'disabled', '=', '0'],
+ ['Customer Group','name', '=', customer_group]
+ ]
}
- },
- on_change: function(){
- frappe.query_report.refresh();
+ }
+ },
+ {
+ "fieldname":"item_group",
+ "label": __("Item Group"),
+ "fieldtype": "Link",
+ "width": 100,
+ "options": "Item Group",
+
+ },
+ {
+ "fieldname":"item",
+ "label": __("Item"),
+ "fieldtype": "Link",
+ "width": 100,
+ "options": "Item",
+ "get_query": () => {
+ var item_group = frappe.query_report.get_filter_value('item_group');
+ return{
+ "query": "erpnext.selling.report.payment_terms_status_for_sales_order.payment_terms_status_for_sales_order.get_customers_or_items",
+ "filters": [
+ ['Item', 'disabled', '=', '0'],
+ ['Item Group','name', '=', item_group]
+ ]
+ }
}
}
]
-
return filters;
}
diff --git a/erpnext/selling/report/payment_terms_status_for_sales_order/payment_terms_status_for_sales_order.py b/erpnext/selling/report/payment_terms_status_for_sales_order/payment_terms_status_for_sales_order.py
index 7f797f6..cb22fb6 100644
--- a/erpnext/selling/report/payment_terms_status_for_sales_order/payment_terms_status_for_sales_order.py
+++ b/erpnext/selling/report/payment_terms_status_for_sales_order/payment_terms_status_for_sales_order.py
@@ -3,7 +3,7 @@
import frappe
from frappe import _, qb, query_builder
-from frappe.query_builder import functions
+from frappe.query_builder import Criterion, functions
def get_columns():
@@ -15,6 +15,12 @@
"options": "Sales Order",
},
{
+ "label": _("Customer"),
+ "fieldname": "customer",
+ "fieldtype": "Link",
+ "options": "Customer",
+ },
+ {
"label": _("Posting Date"),
"fieldname": "submitted",
"fieldtype": "Date",
@@ -67,6 +73,55 @@
return columns
+def get_descendants_of(doctype, group_name):
+ group_doc = qb.DocType(doctype)
+ # get lft and rgt of group node
+ lft, rgt = (
+ qb.from_(group_doc).select(group_doc.lft, group_doc.rgt).where(group_doc.name == group_name)
+ ).run()[0]
+
+ # get all children of group node
+ query = (
+ qb.from_(group_doc).select(group_doc.name).where((group_doc.lft >= lft) & (group_doc.rgt <= rgt))
+ )
+
+ child_nodes = []
+ for x in query.run():
+ child_nodes.append(x[0])
+
+ return child_nodes
+
+
+@frappe.whitelist()
+@frappe.validate_and_sanitize_search_inputs
+def get_customers_or_items(doctype, txt, searchfield, start, page_len, filters):
+ filter_list = []
+ if isinstance(filters, list):
+ for item in filters:
+ if item[0] == doctype:
+ filter_list.append(item)
+ elif item[0] == "Customer Group":
+ if item[3] != "":
+ filter_list.append(
+ [doctype, "customer_group", "in", get_descendants_of("Customer Group", item[3])]
+ )
+ elif item[0] == "Item Group":
+ if item[3] != "":
+ filter_list.append([doctype, "item_group", "in", get_descendants_of("Item Group", item[3])])
+
+ if searchfield and txt:
+ filter_list.append([doctype, searchfield, "like", "%%%s%%" % txt])
+
+ return frappe.desk.reportview.execute(
+ doctype,
+ filters=filter_list,
+ fields=["name", "customer_group"] if doctype == "Customer" else ["name", "item_group"],
+ limit_start=start,
+ limit_page_length=page_len,
+ as_list=True,
+ )
+
+
def get_conditions(filters):
"""
Convert filter options to conditions used in query
@@ -79,11 +134,37 @@
conditions.start_date = filters.period_start_date or frappe.utils.add_months(
conditions.end_date, -1
)
- conditions.sales_order = filters.sales_order or []
return conditions
+def build_filter_criterions(filters):
+ filters = frappe._dict(filters) if filters else frappe._dict({})
+ qb_criterions = []
+
+ if filters.customer_group:
+ qb_criterions.append(
+ qb.DocType("Sales Order").customer_group.isin(
+ get_descendants_of("Customer Group", filters.customer_group)
+ )
+ )
+
+ if filters.customer:
+ qb_criterions.append(qb.DocType("Sales Order").customer == filters.customer)
+
+ if filters.item_group:
+ qb_criterions.append(
+ qb.DocType("Sales Order Item").item_group.isin(
+ get_descendants_of("Item Group", filters.item_group)
+ )
+ )
+
+ if filters.item:
+ qb_criterions.append(qb.DocType("Sales Order Item").item_code == filters.item)
+
+ return qb_criterions
+
+
def get_so_with_invoices(filters):
"""
Get Sales Order with payment terms template with their associated Invoices
@@ -92,16 +173,23 @@
so = qb.DocType("Sales Order")
ps = qb.DocType("Payment Schedule")
+ soi = qb.DocType("Sales Order Item")
+
+ conditions = get_conditions(filters)
+ filter_criterions = build_filter_criterions(filters)
+
datediff = query_builder.CustomFunction("DATEDIFF", ["cur_date", "due_date"])
ifelse = query_builder.CustomFunction("IF", ["condition", "then", "else"])
- conditions = get_conditions(filters)
query_so = (
qb.from_(so)
+ .join(soi)
+ .on(soi.parent == so.name)
.join(ps)
.on(ps.parent == so.name)
.select(
so.name,
+ so.customer,
so.transaction_date.as_("submitted"),
ifelse(datediff(ps.due_date, functions.CurDate()) < 0, "Overdue", "Unpaid").as_("status"),
ps.payment_term,
@@ -117,12 +205,10 @@
& (so.company == conditions.company)
& (so.transaction_date[conditions.start_date : conditions.end_date])
)
+ .where(Criterion.all(filter_criterions))
.orderby(so.name, so.transaction_date, ps.due_date)
)
- if conditions.sales_order != []:
- query_so = query_so.where(so.name.isin(conditions.sales_order))
-
sorders = query_so.run(as_dict=True)
invoices = []
diff --git a/erpnext/selling/report/payment_terms_status_for_sales_order/test_payment_terms_status_for_sales_order.py b/erpnext/selling/report/payment_terms_status_for_sales_order/test_payment_terms_status_for_sales_order.py
index 89940a6..9d542f5 100644
--- a/erpnext/selling/report/payment_terms_status_for_sales_order/test_payment_terms_status_for_sales_order.py
+++ b/erpnext/selling/report/payment_terms_status_for_sales_order/test_payment_terms_status_for_sales_order.py
@@ -11,10 +11,13 @@
)
from erpnext.stock.doctype.item.test_item import create_item
-test_dependencies = ["Sales Order", "Item", "Sales Invoice", "Payment Terms Template"]
+test_dependencies = ["Sales Order", "Item", "Sales Invoice", "Payment Terms Template", "Customer"]
class TestPaymentTermsStatusForSalesOrder(FrappeTestCase):
+ def tearDown(self):
+ frappe.db.rollback()
+
def create_payment_terms_template(self):
# create template for 50-50 payments
template = None
@@ -48,9 +51,9 @@
template.insert()
self.template = template
- def test_payment_terms_status(self):
+ def test_01_payment_terms_status(self):
self.create_payment_terms_template()
- item = create_item(item_code="_Test Excavator", is_stock_item=0)
+ item = create_item(item_code="_Test Excavator 1", is_stock_item=0)
so = make_sales_order(
transaction_date="2021-06-15",
delivery_date=add_days("2021-06-15", -30),
@@ -78,13 +81,14 @@
"company": "_Test Company",
"period_start_date": "2021-06-01",
"period_end_date": "2021-06-30",
- "sales_order": [so.name],
+ "item": item.item_code,
}
)
expected_value = [
{
"name": so.name,
+ "customer": so.customer,
"submitted": datetime.date(2021, 6, 15),
"status": "Completed",
"payment_term": None,
@@ -98,6 +102,7 @@
},
{
"name": so.name,
+ "customer": so.customer,
"submitted": datetime.date(2021, 6, 15),
"status": "Partly Paid",
"payment_term": None,
@@ -132,11 +137,11 @@
)
doc.insert()
- def test_alternate_currency(self):
+ def test_02_alternate_currency(self):
transaction_date = "2021-06-15"
self.create_payment_terms_template()
self.create_exchange_rate(transaction_date)
- item = create_item(item_code="_Test Excavator", is_stock_item=0)
+ item = create_item(item_code="_Test Excavator 2", is_stock_item=0)
so = make_sales_order(
transaction_date=transaction_date,
currency="USD",
@@ -166,7 +171,7 @@
"company": "_Test Company",
"period_start_date": "2021-06-01",
"period_end_date": "2021-06-30",
- "sales_order": [so.name],
+ "item": item.item_code,
}
)
@@ -174,6 +179,7 @@
expected_value = [
{
"name": so.name,
+ "customer": so.customer,
"submitted": datetime.date(2021, 6, 15),
"status": "Completed",
"payment_term": None,
@@ -187,6 +193,7 @@
},
{
"name": so.name,
+ "customer": so.customer,
"submitted": datetime.date(2021, 6, 15),
"status": "Partly Paid",
"payment_term": None,
@@ -200,3 +207,134 @@
},
]
self.assertEqual(data, expected_value)
+
+ def test_03_group_filters(self):
+ transaction_date = "2021-06-15"
+ self.create_payment_terms_template()
+ item1 = create_item(item_code="_Test Excavator 1", is_stock_item=0)
+ item1.item_group = "Products"
+ item1.save()
+
+ so1 = make_sales_order(
+ transaction_date=transaction_date,
+ delivery_date=add_days(transaction_date, -30),
+ item=item1.item_code,
+ qty=1,
+ rate=1000000,
+ do_not_save=True,
+ )
+ so1.po_no = ""
+ so1.taxes_and_charges = ""
+ so1.taxes = ""
+ so1.payment_terms_template = self.template.name
+ so1.save()
+ so1.submit()
+
+ item2 = create_item(item_code="_Test Steel", is_stock_item=0)
+ item2.item_group = "Raw Material"
+ item2.save()
+
+ so2 = make_sales_order(
+ customer="_Test Customer 1",
+ transaction_date=transaction_date,
+ delivery_date=add_days(transaction_date, -30),
+ item=item2.item_code,
+ qty=100,
+ rate=1000,
+ do_not_save=True,
+ )
+ so2.po_no = ""
+ so2.taxes_and_charges = ""
+ so2.taxes = ""
+ so2.payment_terms_template = self.template.name
+ so2.save()
+ so2.submit()
+
+ base_filters = {
+ "company": "_Test Company",
+ "period_start_date": "2021-06-01",
+ "period_end_date": "2021-06-30",
+ }
+
+ expected_value_so1 = [
+ {
+ "name": so1.name,
+ "customer": so1.customer,
+ "submitted": datetime.date(2021, 6, 15),
+ "status": "Overdue",
+ "payment_term": None,
+ "description": "_Test 50-50",
+ "due_date": datetime.date(2021, 6, 30),
+ "invoice_portion": 50.0,
+ "currency": "INR",
+ "base_payment_amount": 500000.0,
+ "paid_amount": 0.0,
+ "invoices": "",
+ },
+ {
+ "name": so1.name,
+ "customer": so1.customer,
+ "submitted": datetime.date(2021, 6, 15),
+ "status": "Overdue",
+ "payment_term": None,
+ "description": "_Test 50-50",
+ "due_date": datetime.date(2021, 7, 15),
+ "invoice_portion": 50.0,
+ "currency": "INR",
+ "base_payment_amount": 500000.0,
+ "paid_amount": 0.0,
+ "invoices": "",
+ },
+ ]
+
+ expected_value_so2 = [
+ {
+ "name": so2.name,
+ "customer": so2.customer,
+ "submitted": datetime.date(2021, 6, 15),
+ "status": "Overdue",
+ "payment_term": None,
+ "description": "_Test 50-50",
+ "due_date": datetime.date(2021, 6, 30),
+ "invoice_portion": 50.0,
+ "currency": "INR",
+ "base_payment_amount": 50000.0,
+ "paid_amount": 0.0,
+ "invoices": "",
+ },
+ {
+ "name": so2.name,
+ "customer": so2.customer,
+ "submitted": datetime.date(2021, 6, 15),
+ "status": "Overdue",
+ "payment_term": None,
+ "description": "_Test 50-50",
+ "due_date": datetime.date(2021, 7, 15),
+ "invoice_portion": 50.0,
+ "currency": "INR",
+ "base_payment_amount": 50000.0,
+ "paid_amount": 0.0,
+ "invoices": "",
+ },
+ ]
+
+ group_filters = [
+ {"customer_group": "All Customer Groups"},
+ {"item_group": "All Item Groups"},
+ {"item_group": "Products"},
+ {"item_group": "Raw Material"},
+ ]
+
+ expected_values_for_group_filters = [
+ expected_value_so1 + expected_value_so2,
+ expected_value_so1 + expected_value_so2,
+ expected_value_so1,
+ expected_value_so2,
+ ]
+
+ for idx, g in enumerate(group_filters, 0):
+ # build filter
+ filters = frappe._dict({}).update(base_filters).update(g)
+ with self.subTest(filters=filters):
+ columns, data, message, chart = execute(filters)
+ self.assertEqual(data, expected_values_for_group_filters[idx])