fix: added delivery date filters to get sales orders in production plan (#27367)
diff --git a/erpnext/manufacturing/doctype/production_plan/production_plan.json b/erpnext/manufacturing/doctype/production_plan/production_plan.json
index b5ed288..56cf2b4 100644
--- a/erpnext/manufacturing/doctype/production_plan/production_plan.json
+++ b/erpnext/manufacturing/doctype/production_plan/production_plan.json
@@ -16,10 +16,12 @@
"customer",
"warehouse",
"project",
+ "sales_order_status",
"column_break2",
"from_date",
"to_date",
- "sales_order_status",
+ "from_delivery_date",
+ "to_delivery_date",
"sales_orders_detail",
"get_sales_orders",
"sales_orders",
@@ -358,13 +360,23 @@
"fieldname": "get_sub_assembly_items",
"fieldtype": "Button",
"label": "Get Sub Assembly Items"
+ },
+ {
+ "fieldname": "from_delivery_date",
+ "fieldtype": "Date",
+ "label": "From Delivery Date"
+ },
+ {
+ "fieldname": "to_delivery_date",
+ "fieldtype": "Date",
+ "label": "To Delivery Date"
}
],
"icon": "fa fa-calendar",
"index_web_pages_for_search": 1,
"is_submittable": 1,
"links": [],
- "modified": "2021-08-23 17:26:03.799876",
+ "modified": "2021-09-06 18:35:59.642232",
"modified_by": "Administrator",
"module": "Manufacturing",
"name": "Production Plan",
diff --git a/erpnext/manufacturing/doctype/production_plan/production_plan.py b/erpnext/manufacturing/doctype/production_plan/production_plan.py
index 73db290..a28fc7a 100644
--- a/erpnext/manufacturing/doctype/production_plan/production_plan.py
+++ b/erpnext/manufacturing/doctype/production_plan/production_plan.py
@@ -735,43 +735,42 @@
def get_sales_orders(self):
so_filter = item_filter = ""
bom_item = "bom.item = so_item.item_code"
- if self.from_date:
- so_filter += " and so.transaction_date >= %(from_date)s"
- if self.to_date:
- so_filter += " and so.transaction_date <= %(to_date)s"
- if self.customer:
- so_filter += " and so.customer = %(customer)s"
- if self.project:
- so_filter += " and so.project = %(project)s"
- if self.sales_order_status:
- so_filter += "and so.status = %(sales_order_status)s"
+
+ date_field_mapper = {
+ 'from_date': ('>=', 'so.transaction_date'),
+ 'to_date': ('<=', 'so.transaction_date'),
+ 'from_delivery_date': ('>=', 'so_item.delivery_date'),
+ 'to_delivery_date': ('<=', 'so_item.delivery_date')
+ }
+
+ for field, value in date_field_mapper.items():
+ if self.get(field):
+ so_filter += f" and {value[1]} {value[0]} %({field})s"
+
+ for field in ['customer', 'project', 'sales_order_status']:
+ if self.get(field):
+ so_field = 'status' if field == 'sales_order_status' else field
+ so_filter += f" and so.{so_field} = %({field})s"
if self.item_code and frappe.db.exists('Item', self.item_code):
bom_item = self.get_bom_item() or bom_item
- item_filter += " and so_item.item_code = %(item)s"
+ item_filter += " and so_item.item_code = %(item_code)s"
- open_so = frappe.db.sql("""
+ open_so = frappe.db.sql(f"""
select distinct so.name, so.transaction_date, so.customer, so.base_grand_total
from `tabSales Order` so, `tabSales Order Item` so_item
where so_item.parent = so.name
and so.docstatus = 1 and so.status not in ("Stopped", "Closed")
and so.company = %(company)s
- and so_item.qty > so_item.work_order_qty {0} {1}
- and (exists (select name from `tabBOM` bom where {2}
+ and so_item.qty > so_item.work_order_qty {so_filter} {item_filter}
+ and (exists (select name from `tabBOM` bom where {bom_item}
and bom.is_active = 1)
or exists (select name from `tabPacked Item` pi
where pi.parent = so.name and pi.parent_item = so_item.item_code
and exists (select name from `tabBOM` bom where bom.item=pi.item_code
and bom.is_active = 1)))
- """.format(so_filter, item_filter, bom_item), {
- "from_date": self.from_date,
- "to_date": self.to_date,
- "customer": self.customer,
- "project": self.project,
- "item": self.item_code,
- "company": self.company,
- "sales_order_status": self.sales_order_status
- }, as_dict=1)
+ """, self.as_dict(), as_dict=1)
+
return open_so
@frappe.whitelist()