feat: Formula based Quality Inspection (#23916)

* feat: Formula based Quality Inspection

* chore: Added Test for Formula Based QI reading
diff --git a/erpnext/stock/doctype/item_quality_inspection_parameter/item_quality_inspection_parameter.json b/erpnext/stock/doctype/item_quality_inspection_parameter/item_quality_inspection_parameter.json
index f1e1fd3..888bc2d 100644
--- a/erpnext/stock/doctype/item_quality_inspection_parameter/item_quality_inspection_parameter.json
+++ b/erpnext/stock/doctype/item_quality_inspection_parameter/item_quality_inspection_parameter.json
@@ -1,88 +1,57 @@
 {
- "allow_copy": 0, 
- "allow_import": 0, 
- "allow_rename": 0, 
- "autoname": "hash", 
- "beta": 0, 
- "creation": "2013-02-22 01:28:01", 
- "custom": 0, 
- "docstatus": 0, 
- "doctype": "DocType", 
- "editable_grid": 1, 
+ "actions": [],
+ "autoname": "hash",
+ "creation": "2013-02-22 01:28:01",
+ "doctype": "DocType",
+ "editable_grid": 1,
+ "engine": "InnoDB",
+ "field_order": [
+  "specification",
+  "value",
+  "column_break_3",
+  "acceptance_formula"
+ ],
  "fields": [
   {
-   "allow_on_submit": 0, 
-   "bold": 0, 
-   "collapsible": 0, 
-   "fieldname": "specification", 
-   "fieldtype": "Data", 
-   "hidden": 0, 
-   "ignore_user_permissions": 0, 
-   "ignore_xss_filter": 0, 
-   "in_filter": 0, 
-   "in_list_view": 1, 
-   "label": "Parameter", 
-   "length": 0, 
-   "no_copy": 0, 
-   "oldfieldname": "specification", 
-   "oldfieldtype": "Data", 
-   "permlevel": 0, 
-   "print_hide": 0, 
-   "print_hide_if_no_value": 0, 
-   "print_width": "200px", 
-   "read_only": 0, 
-   "report_hide": 0, 
-   "reqd": 1, 
-   "search_index": 0, 
-   "set_only_once": 0, 
-   "unique": 0, 
+   "fieldname": "specification",
+   "fieldtype": "Data",
+   "in_list_view": 1,
+   "label": "Parameter",
+   "oldfieldname": "specification",
+   "oldfieldtype": "Data",
+   "print_width": "200px",
+   "reqd": 1,
    "width": "200px"
-  }, 
+  },
   {
-   "allow_on_submit": 0, 
-   "bold": 0, 
-   "collapsible": 0, 
-   "fieldname": "value", 
-   "fieldtype": "Data", 
-   "hidden": 0, 
-   "ignore_user_permissions": 0, 
-   "ignore_xss_filter": 0, 
-   "in_filter": 0, 
-   "in_list_view": 1, 
-   "label": "Acceptance Criteria", 
-   "length": 0, 
-   "no_copy": 0, 
-   "oldfieldname": "value", 
-   "oldfieldtype": "Data", 
-   "permlevel": 0, 
-   "print_hide": 0, 
-   "print_hide_if_no_value": 0, 
-   "read_only": 0, 
-   "report_hide": 0, 
-   "reqd": 0, 
-   "search_index": 0, 
-   "set_only_once": 0, 
-   "unique": 0
+   "fieldname": "value",
+   "fieldtype": "Data",
+   "in_list_view": 1,
+   "label": "Acceptance Criteria",
+   "oldfieldname": "value",
+   "oldfieldtype": "Data"
+  },
+  {
+   "fieldname": "column_break_3",
+   "fieldtype": "Column Break"
+  },
+  {
+   "description": "Simple Python formula based on numeric Readings.<br> Example 1: <b>reading_1 &gt; 0.2 and reading_1 &lt; 0.5</b><br>\nExample 2: <b>(reading_1 + reading_2) / 2 &lt; 10</b>",
+   "fieldname": "acceptance_formula",
+   "fieldtype": "Code",
+   "in_list_view": 1,
+   "label": "Acceptance Criteria Formula"
   }
- ], 
- "hide_heading": 0, 
- "hide_toolbar": 0, 
- "idx": 1, 
- "image_view": 0, 
- "in_create": 0, 
-
- "is_submittable": 0, 
- "issingle": 0, 
- "istable": 1, 
- "max_attachments": 0, 
- "modified": "2016-07-11 03:28:01.074316", 
- "modified_by": "Administrator", 
- "module": "Stock", 
- "name": "Item Quality Inspection Parameter", 
- "owner": "Administrator", 
- "permissions": [], 
- "quick_entry": 0, 
- "read_only": 0, 
- "read_only_onload": 0, 
- "track_seen": 0
+ ],
+ "idx": 1,
+ "istable": 1,
+ "links": [],
+ "modified": "2020-11-16 16:33:42.421842",
+ "modified_by": "Administrator",
+ "module": "Stock",
+ "name": "Item Quality Inspection Parameter",
+ "owner": "Administrator",
+ "permissions": [],
+ "sort_field": "modified",
+ "sort_order": "DESC"
 }
\ No newline at end of file
diff --git a/erpnext/stock/doctype/quality_inspection/quality_inspection.py b/erpnext/stock/doctype/quality_inspection/quality_inspection.py
index c3bb514..399a63a 100644
--- a/erpnext/stock/doctype/quality_inspection/quality_inspection.py
+++ b/erpnext/stock/doctype/quality_inspection/quality_inspection.py
@@ -4,15 +4,20 @@
 from __future__ import unicode_literals
 import frappe
 from frappe.model.document import Document
+from frappe.model.mapper import get_mapped_doc
+from frappe import _
+from frappe.utils import flt
 from erpnext.stock.doctype.quality_inspection_template.quality_inspection_template \
 	import get_template_details
-from frappe.model.mapper import get_mapped_doc
 
 class QualityInspection(Document):
 	def validate(self):
 		if not self.readings and self.item_code:
 			self.get_item_specification_details()
 
+		if self.readings:
+			self.set_status_based_on_acceptance_formula()
+
 	def get_item_specification_details(self):
 		if not self.quality_inspection_template:
 			self.quality_inspection_template = frappe.db.get_value('Item',
@@ -26,6 +31,7 @@
 			child = self.append('readings', {})
 			child.specification = d.specification
 			child.value = d.value
+			child.acceptance_formula = d.acceptance_formula
 			child.status = "Accepted"
 
 	def get_quality_inspection_template(self):
@@ -58,6 +64,29 @@
 				.format(parent_doc=self.reference_type, child_doc=doctype),
 				(quality_inspection, self.modified, self.reference_name, self.item_code))
 
+	def set_status_based_on_acceptance_formula(self):
+		for reading in self.readings:
+			if not reading.acceptance_formula: continue
+
+			condition = reading.acceptance_formula
+			data = {}
+			for i in range(1, 11):
+				field = "reading_" + str(i)
+				data[field] = flt(reading.get(field)) or 0
+
+			try:
+				result = frappe.safe_eval(condition, None, data)
+				reading.status = "Accepted" if result else "Rejected"
+			except SyntaxError:
+				frappe.throw(_("Row #{0}: Acceptance Criteria Formula is incorrect.").format(reading.idx),
+					title=_("Invalid Formula"))
+			except NameError as e:
+				field = frappe.bold(e.args[0].split()[1])
+				frappe.throw(_("Row #{0}: {1} is not a valid reading field. Please refer to the field description.")
+					.format(reading.idx, field),
+					title=_("Invalid Formula"))
+
+
 @frappe.whitelist()
 @frappe.validate_and_sanitize_search_inputs
 def item_query(doctype, txt, searchfield, start, page_len, filters):
diff --git a/erpnext/stock/doctype/quality_inspection/test_quality_inspection.py b/erpnext/stock/doctype/quality_inspection/test_quality_inspection.py
index bb535c1..2c40009 100644
--- a/erpnext/stock/doctype/quality_inspection/test_quality_inspection.py
+++ b/erpnext/stock/doctype/quality_inspection/test_quality_inspection.py
@@ -7,6 +7,7 @@
 from frappe.utils import nowdate
 from erpnext.stock.doctype.item.test_item import create_item
 from erpnext.stock.doctype.delivery_note.test_delivery_note import create_delivery_note
+from erpnext.stock.doctype.stock_entry.test_stock_entry import make_stock_entry
 from erpnext.controllers.stock_controller import QualityInspectionRejectedError, QualityInspectionRequiredError, QualityInspectionNotSubmittedError
 
 # test_records = frappe.get_test_records('Quality Inspection')
@@ -17,10 +18,12 @@
 		frappe.db.set_value("Item", "_Test Item with QA", "inspection_required_before_delivery", 1)
 
 	def test_qa_for_delivery(self):
+		make_stock_entry(item_code="_Test Item with QA", target="_Test Warehouse - _TC", qty=1, basic_rate=100)
 		dn = create_delivery_note(item_code="_Test Item with QA", do_not_submit=True)
+
 		self.assertRaises(QualityInspectionRequiredError, dn.submit)
 
-		qa = create_quality_inspection(reference_type="Delivery Note", reference_name=dn.name, status="Rejected", submit=True)
+		qa = create_quality_inspection(reference_type="Delivery Note", reference_name=dn.name, status="Rejected")
 		dn.reload()
 		self.assertRaises(QualityInspectionRejectedError, dn.submit)
 
@@ -28,12 +31,51 @@
 		dn.reload()
 		dn.submit()
 
+		qa.cancel()
+		dn.reload()
+		dn.cancel()
+
 	def test_qa_not_submit(self):
 		dn = create_delivery_note(item_code="_Test Item with QA", do_not_submit=True)
-		qa = create_quality_inspection(reference_type="Delivery Note", reference_name=dn.name, submit = False)
+		qa = create_quality_inspection(reference_type="Delivery Note", reference_name=dn.name, do_not_submit=True)
 		dn.items[0].quality_inspection = qa.name
 		self.assertRaises(QualityInspectionNotSubmittedError, dn.submit)
 
+		qa.delete()
+		dn.delete()
+
+	def test_formula_based_qi_readings(self):
+		dn = create_delivery_note(item_code="_Test Item with QA", do_not_submit=True)
+		readings = [{
+			"specification": "Iron Content",
+			"acceptance_formula": "reading_1 > 0.35 and reading_1 < 0.50",
+			"reading_1": 0.4
+		},
+		{
+			"specification": "Calcium Content",
+			"acceptance_formula": "reading_1 > 0.20 and reading_1 < 0.50",
+			"reading_1": 0.7
+		},
+		{
+			"specification": "Mg Content",
+			"acceptance_formula": "(reading_1 + reading_2 + reading_3) / 3 < 0.9",
+			"reading_1": 0.5,
+			"reading_2": 0.7,
+			"reading_3": "random text" # check if random string input causes issues
+		}]
+
+		qa = create_quality_inspection(reference_type="Delivery Note", reference_name=dn.name,
+			readings=readings, do_not_save=True)
+		qa.save()
+
+		# status must be auto set as per formula
+		self.assertEqual(qa.readings[0].status, "Accepted")
+		self.assertEqual(qa.readings[1].status, "Rejected")
+		self.assertEqual(qa.readings[2].status, "Accepted")
+
+		qa.delete()
+		dn.delete()
+
 def create_quality_inspection(**args):
 	args = frappe._dict(args)
 	qa = frappe.new_doc("Quality Inspection")
@@ -44,12 +86,18 @@
 	qa.item_code = args.item_code or "_Test Item with QA"
 	qa.sample_size = 1
 	qa.inspected_by = frappe.session.user
-	qa.append("readings", {
-		"specification": "Size",
-		"status": args.status
-	})
-	qa.save()
-	if args.submit:
-		qa.submit()
+
+	readings = args.readings or {"specification": "Size", "status": args.status}
+
+	if isinstance(readings, list):
+		for entry in readings:
+			qa.append("readings", entry)
+	else:
+		qa.append("readings", readings)
+
+	if not args.do_not_save:
+		qa.save()
+		if not args.do_not_submit:
+			qa.submit()
 
 	return qa
diff --git a/erpnext/stock/doctype/quality_inspection_reading/quality_inspection_reading.json b/erpnext/stock/doctype/quality_inspection_reading/quality_inspection_reading.json
index f9f8a71..c1976dd 100644
--- a/erpnext/stock/doctype/quality_inspection_reading/quality_inspection_reading.json
+++ b/erpnext/stock/doctype/quality_inspection_reading/quality_inspection_reading.json
@@ -1,22 +1,29 @@
 {
+ "actions": [],
  "autoname": "hash",
  "creation": "2013-02-22 01:27:43",
  "doctype": "DocType",
  "editable_grid": 1,
+ "engine": "InnoDB",
  "field_order": [
   "specification",
   "value",
+  "status",
+  "column_break_4",
+  "acceptance_formula",
+  "section_break_3",
   "reading_1",
   "reading_2",
   "reading_3",
+  "column_break_10",
   "reading_4",
   "reading_5",
   "reading_6",
+  "column_break_14",
   "reading_7",
   "reading_8",
   "reading_9",
-  "reading_10",
-  "status"
+  "reading_10"
  ],
  "fields": [
   {
@@ -124,15 +131,40 @@
    "oldfieldname": "status",
    "oldfieldtype": "Select",
    "options": "Accepted\nRejected"
+  },
+  {
+   "fieldname": "section_break_3",
+   "fieldtype": "Section Break"
+  },
+  {
+   "fieldname": "column_break_4",
+   "fieldtype": "Column Break"
+  },
+  {
+   "description": "Simple Python formula based on numeric Readings.<br> Example 1: <b>reading_1 &gt; 0.2 and reading_1 &lt; 0.5</b><br>\nExample 2: <b>(reading_1 + reading_2) / 2 &lt; 10</b>",
+   "fieldname": "acceptance_formula",
+   "fieldtype": "Code",
+   "label": "Acceptance Criteria Formula"
+  },
+  {
+   "fieldname": "column_break_10",
+   "fieldtype": "Column Break"
+  },
+  {
+   "fieldname": "column_break_14",
+   "fieldtype": "Column Break"
   }
  ],
  "idx": 1,
  "istable": 1,
- "modified": "2019-07-11 18:48:12.667404",
+ "links": [],
+ "modified": "2020-11-16 16:34:29.947856",
  "modified_by": "Administrator",
  "module": "Stock",
  "name": "Quality Inspection Reading",
  "owner": "Administrator",
  "permissions": [],
+ "sort_field": "modified",
+ "sort_order": "DESC",
  "track_changes": 1
 }
\ No newline at end of file
diff --git a/erpnext/stock/doctype/quality_inspection_template/quality_inspection_template.py b/erpnext/stock/doctype/quality_inspection_template/quality_inspection_template.py
index 0d9a903..e284846 100644
--- a/erpnext/stock/doctype/quality_inspection_template/quality_inspection_template.py
+++ b/erpnext/stock/doctype/quality_inspection_template/quality_inspection_template.py
@@ -12,5 +12,7 @@
 def get_template_details(template):
 	if not template: return []
 
-	return frappe.get_all('Item Quality Inspection Parameter', fields=["specification", "value"],
-		filters={'parenttype': 'Quality Inspection Template', 'parent': template}, order_by="idx")
\ No newline at end of file
+	return frappe.get_all('Item Quality Inspection Parameter',
+		fields=["specification", "value", "acceptance_formula"],
+		filters={'parenttype': 'Quality Inspection Template', 'parent': template},
+		order_by="idx")
\ No newline at end of file