VenkataRaju/DynamicFormulaEvaluator.html

## DynamicFormulaEvaluator.html
<!DOCTYPE html>
<!-- Author: Venkata Raju -->
<!--
TODO: 1) Allow multi line in formula (mouse cursor up and down should work)
      2) Resizeable table header resize: horizontal
-->
<html>
<meta charset="UTF-8">
<head>
<title>Dynamic Formula Evaluator</title>
<style>
* {
  font-size: 1em;
}

#addRowBtn:first-letter {
  text-decoration: underline;
}

#data, th, td {
  border-style: solid;
  border-color: gray;
}

#data {
  border-collapse: separate;
  border-spacing: 0;
  border-width: 0 0 1px 1px;
}

thead {
  font-family: monospace;
  padding: 2px;
}

th {
  font-size: 1.2em;
  padding: 2px;
  background-color: rgb(200, 240, 200);
}

th, td
{
  border-width: 1px 1px 0 0;
  padding: 0px 3px;
}

#data td:nth-child(3) {
  text-align: center;
}

#data td:nth-child(2), #data td:nth-child(4) {
  text-align: right;
}

#data td:last-child {
  text-align: center;
}

#data button {
  width: 100%;
  font-size: small;
}

#errMsg {
  color: red;
}

.err {
  border: 2px solid red;
}

.noedit {
  background-color: rgb(240, 240, 240);
}
</style>
<script>
  var NAME_REG_EX = new RegExp("^[_a-zA-Z]\\w*$");

  var tbody, refTr, prevErrTd = null;

  function $(id) document.getElementById(id);
  function select(selector, el) (el || document).querySelector(selector);
  function selectAll(selector, el) Array.from((el || document).querySelectorAll(selector));

  function init()
  {
    var trs = selectAll("#data > tbody > tr");
    refTr = trs[0].cloneNode(true);
    tbody = trs[0].parentNode;

    tbody.firstElementChild.remove();

    /*
    var data = [
                ["a", 20, false, "", ""],
                ["b", 40, true, 1, "a + 20"]
               ];
    */

    /* Critical, Major, Minor, Warning */
    var data = [
                ["VAL", 20, false, "", ""],
                ["PV_VAL", 20, false, "", ""],
                ["", "", true, "1", "VAL < 0.4 * PV_VAL ? 'Critical' : VAL < 0.6 * PV_VAL ? 'Major' : VAL < 0.8 * PV_VAL ? 'Minor' : 'Warning'"],
               ];

    for(var i = 0, len = data.length; i < len; i++)
    {
      var row = tbody.appendChild(refTr.cloneNode(true));
      var tds = row.children;
      var rowData = data[i];
      rowData.forEach((value, i) => (i !== 2) ? (tds[i].textContent = value) : (tds[2].firstChild.checked = value));
      checkboxClicked(tds[2].firstChild)
    }
  }

  function addNewRow()
  {
    var cloneTr = refTr.cloneNode(true);
    var tds = cloneTr.children;
    [tds[3], tds[4]].forEach(el => el.classList.add("noedit"));
    tbody.appendChild(cloneTr);
    evaluate();
  }

  function checkboxClicked(cb)
  {
    var tr = cb.parentNode.parentNode;
    var tds = Array.from(tr.children);
    if (cb.checked && tds[3].textContent.length === 0)
    {
      var maxPriority = Array.from(tr.parentNode.children)
                          .filter(tr => tr.children[2].firstChild.checked && !isNaN(+tr.children[3].textContent))
                          .map(tr => +tr.children[3].textContent)
                          .reduce((a, b) => a < b ? b : a);
      tds[3].textContent = maxPriority + 1;
    }
    tds.forEach((td, i) => (i == 0 || i == 2) ? "nothing" : (cb.checked ^ i === 1) ? td.classList.remove("noedit") : td.classList.add("noedit"));
    evaluate();
  }

  function keyDown(event)
  {
    if (event.keyCode !== 13 /* Enter Key */ && event.keyCode !== 38 /* Up Key */ && event.keyCode !== 40 /* Down Key */)
      return;

    var td = nextRotationTd(event.target, event.keyCode !== 38 && !event.shiftKey);
    if (td !== null)
      td.focus();
    event.preventDefault();
  }

  function nextRotationTd(td, isDown)
  {
    var tr = td.parentNode;
    var sibTr = tr[(isDown ? "next" : "previous") + "ElementSibling"];
    if (sibTr === null)
      sibTr = tr.parentNode[(isDown ? "first" : "last") + "ElementChild"];
    return (sibTr !== null) ? sibTr.children[Array.from(tr.children).indexOf(td)] : null;
  }

  function keyDownOnEl(event)
  {
    if (event.keyCode !== 13 /* Enter Key */ && event.keyCode !== 38 /* Up Key */ && event.keyCode !== 40 /* Down Key */)
      return;

    var el = event.target;
    if (event.keyCode === 13 && el.nodeName === "BUTTON")
      return; // Not handling Enter on Button, default action will be triggered

    var td =  nextRotationTd(el.parentNode, event.keyCode !== 38 && !event.shiftKey);
    if (td !== null)
      td.firstElementChild.focus();
    event.preventDefault();
  }

  function inputTextChanged(event)
  {
    if (!event.target/* td */.classList.contains("noedit"))
      evaluate();
  }

  function evaluate()
  {
    clearError(); // Reset

    var trsTds = Array.from(tbody.children).map(tr => tr.children);

    // Checking for duplicate names and evaluation orders
    var names = {}, evOrders = {};
    for (var i = 0, len = trsTds.length; i < len; i++)
    {
      var trTds = trsTds[i];

      var nameEl = trTds[0];
      var name = nameEl.textContent;
      if (name.length !== 0)
      {
        if(!NAME_REG_EX.test(name))
          return showError(nameEl, "Invalid name: '" + name +"'. Only _, lower case, upper case and digits(not at the begining) are allowed.");
        if (names[name])
          return showError(nameEl, "Duplicate name: '" + name + "'");
        names[name] = true;
      }

      var evaluate = trTds[2].firstChild.checked;
      if (!evaluate)
      {
        if(name.length === 0)
        { // Skip this row
          trsTds.splice(i, 1);
          i--;
          len--;
        }
        else
        {
          var value = trTds[1].textContent;
          if (value.length === 0)
            return showError(trTds[1], "No valid 'Value' is provided");
        }
      }
      else
      {
        var evOrderEl = trTds[3];
        var evOrderStr = evOrderEl.textContent;
        if (evOrderStr.length === 0)
          return showError(evOrderEl, "No 'Evaluation Order' is provided");
        var evOrder = +evOrderStr;
        if (isNaN(evOrder) || evOrder < 1)
          return showError(evOrderEl, "Invalid 'Evaluation Order': " + evOrderStr);
        if (evOrders[evOrder])
          return showError(evOrderEl, "Duplicate 'Evaluation Order': " + evOrder);
        evOrders[evOrder] = true;

        if (trTds[4].textContent.length === 0)
          return showError(trTds[4], "No 'Formula' is provided");
      }
    }

    trsTds.sort((tds1, tds2) =>
    {
      var evaluate1 = tds1[2].firstChild.checked, evaluate2 = tds2[2].firstChild.checked;
      if (!evaluate1 || !evaluate2)
        return (!evaluate1 && !evaluate2) ? 0 : !evaluate1 ? -1 : 1;
      var evOrder1 = +tds1[3].textContent, evOrder2 = +tds2[3].textContent;
      return evOrder1 < evOrder2 ? -1 : evOrder1 > evOrder2 ? 1 : 0;
    });

    var valueByName = {};

    for (var trTds of trsTds)
    {
      var name = trTds[0].textContent;
      if (!trTds[2].firstChild.checked)
      {
        if (name.length !== 0)
        {
          var valStr = trTds[1].textContent, valNum = +valStr;
          valueByName[name] = (valStr.length === 0 || isNaN(valNum)) ? valStr : valNum;
        }
        continue;
      }

      var names = Object.keys(valueByName);

      // Long strings first
      names.sort((name1, name2) => (name1.length > name2.length) ? -1 : (name1.length < name2.length) ? 1 : 0);

      var formula = trTds[4].textContent;
      var valSubFormula = formula.replace(new RegExp("[_a-zA-Z]\\w*", "g"), name =>
      {
        if (valueByName.hasOwnProperty(name))
        {
          var val = valueByName[name];
          return (typeof val === "number") ? val : ("'" + val + "'" /* String */);
        }
        return name;
      });

      try
      {
        var result = eval(valSubFormula),
            resultStr = "" + result,
            resultNum = +resultStr;
        if (name.length !== 0)
          valueByName[name] = result;
        trTds[1].innerHTML =  (resultStr.length === 0 || isNaN(resultNum)) ? result : resultNum.toFixed(2);
      }
      catch (e)
      {
        return showError(trTds[4], "Unable to evaluate the formula: " + formula + ", " + e.message /* + ", " + formula */);
      }
    }
  }

  function showError(td, errMsg)
  {
    clearError();
    prevErrTd = td;
    prevErrTd.classList.add("err");
    $("errMsg").innerHTML = errMsg;
  }

  function clearError()
  {
    if (prevErrTd === null)
      return;
    prevErrTd.classList.remove("err");
    prevErrTd = null;
    $("errMsg").innerHTML = "";
  }

  function deleteRow(event)
  {
    var tr = event.target.parentNode.parentNode;
    var sib =  tr.nextElementSibling || tr.previousElementSibling; //TODO: event.shiftKey doesn't work
    tr.remove();
    if (sib !== null)
      sib.lastElementChild.firstElementChild.focus();
    evaluate();
  }
</script>
<style>
</style>
</head>
<body onload="init()">
  <button id="addRowBtn" accesskey="A" title="Alt + Shift + A" onclick="addNewRow()">Add Row</button>
  <label id="errMsg"></label>
  <br>
  <br>
  <table id="data" align="center" style="width: 100%" border="1">
    <thead>
      <tr>
        <th style="width: 15%">Name</th>
        <th rowspan="2" style="width: 15%">Value</th>
        <th rowspan="2" style="width: 5%">Evaluate</th>
        <th style="width: 15%">Evaluation Order</th>
        <th style="width: 40%">Formula</th>
        <th rowspan="3" style="width: 10%">Delete Row</th>
      </tr>
    </thead>
    <tbody>
      <tr>
        <td contenteditable="true" onkeydown="keyDown(event)" onkeyup="inputTextChanged(event)"></td>
        <td contenteditable="true" onkeydown="keyDown(event)" onkeyup="inputTextChanged(event)"></td>
        <td onclick="this.firstChild.click();"><input type="checkbox"	onkeydown="keyDownOnEl(event)" onclick="event.stopPropagation(); checkboxClicked(this)" /></td>
        <td contenteditable="true" onkeydown="keyDown(event)" onkeyup="inputTextChanged(event)"></td>
        <td contenteditable="true" onkeydown="keyDown(event)" onkeyup="inputTextChanged(event)"></td>
        <td><button onkeydown="keyDownOnEl(event)" onclick="deleteRow(event)">Delete</button></td>
      </tr>
    </tbody
  </table>
</body>
</html>
	<!DOCTYPE html>
	<!-- Author: Venkata Raju -->
	<!--
	TODO: 1) Allow multi line in formula (mouse cursor up and down should work)
	2) Resizeable table header resize: horizontal
	-->
	<html>
	<meta charset="UTF-8">
	<head>
	<title>Dynamic Formula Evaluator</title>
	<style>
	* {
	font-size: 1em;
	}

	#addRowBtn:first-letter {
	text-decoration: underline;
	}

	#data, th, td {
	border-style: solid;
	border-color: gray;
	}

	#data {
	border-collapse: separate;
	border-spacing: 0;
	border-width: 0 0 1px 1px;
	}

	thead {
	font-family: monospace;
	padding: 2px;
	}

	th {
	font-size: 1.2em;
	padding: 2px;
	background-color: rgb(200, 240, 200);
	}

	th, td
	{
	border-width: 1px 1px 0 0;
	padding: 0px 3px;
	}

	#data td:nth-child(3) {
	text-align: center;
	}

	#data td:nth-child(2), #data td:nth-child(4) {
	text-align: right;
	}

	#data td:last-child {
	text-align: center;
	}

	#data button {
	width: 100%;
	font-size: small;
	}

	#errMsg {
	color: red;
	}

	.err {
	border: 2px solid red;
	}

	.noedit {
	background-color: rgb(240, 240, 240);
	}
	</style>
	<script>
	var NAME_REG_EX = new RegExp("^[_a-zA-Z]\\w*$");

	var tbody, refTr, prevErrTd = null;

	function $(id) document.getElementById(id);
	function select(selector, el) (el \|\| document).querySelector(selector);
	function selectAll(selector, el) Array.from((el \|\| document).querySelectorAll(selector));

	function init()
	{
	var trs = selectAll("#data > tbody > tr");
	refTr = trs[0].cloneNode(true);
	tbody = trs[0].parentNode;

	tbody.firstElementChild.remove();

	/*
	var data = [
	["a", 20, false, "", ""],
	["b", 40, true, 1, "a + 20"]
	];
	*/

	/* Critical, Major, Minor, Warning */
	var data = [
	["VAL", 20, false, "", ""],
	["PV_VAL", 20, false, "", ""],
	["", "", true, "1", "VAL < 0.4 * PV_VAL ? 'Critical' : VAL < 0.6 * PV_VAL ? 'Major' : VAL < 0.8 * PV_VAL ? 'Minor' : 'Warning'"],
	];

	for(var i = 0, len = data.length; i < len; i++)
	{
	var row = tbody.appendChild(refTr.cloneNode(true));
	var tds = row.children;
	var rowData = data[i];
	rowData.forEach((value, i) => (i !== 2) ? (tds[i].textContent = value) : (tds[2].firstChild.checked = value));
	checkboxClicked(tds[2].firstChild)
	}
	}

	function addNewRow()
	{
	var cloneTr = refTr.cloneNode(true);
	var tds = cloneTr.children;
	[tds[3], tds[4]].forEach(el => el.classList.add("noedit"));
	tbody.appendChild(cloneTr);
	evaluate();
	}

	function checkboxClicked(cb)
	{
	var tr = cb.parentNode.parentNode;
	var tds = Array.from(tr.children);
	if (cb.checked && tds[3].textContent.length === 0)
	{
	var maxPriority = Array.from(tr.parentNode.children)
	.filter(tr => tr.children[2].firstChild.checked && !isNaN(+tr.children[3].textContent))
	.map(tr => +tr.children[3].textContent)
	.reduce((a, b) => a < b ? b : a);
	tds[3].textContent = maxPriority + 1;
	}
	tds.forEach((td, i) => (i == 0 \|\| i == 2) ? "nothing" : (cb.checked ^ i === 1) ? td.classList.remove("noedit") : td.classList.add("noedit"));
	evaluate();
	}

	function keyDown(event)
	{
	if (event.keyCode !== 13 /* Enter Key / && event.keyCode !== 38 / Up Key / && event.keyCode !== 40 / Down Key */)
	return;

	var td = nextRotationTd(event.target, event.keyCode !== 38 && !event.shiftKey);
	if (td !== null)
	td.focus();
	event.preventDefault();
	}

	function nextRotationTd(td, isDown)
	{
	var tr = td.parentNode;
	var sibTr = tr[(isDown ? "next" : "previous") + "ElementSibling"];
	if (sibTr === null)
	sibTr = tr.parentNode[(isDown ? "first" : "last") + "ElementChild"];
	return (sibTr !== null) ? sibTr.children[Array.from(tr.children).indexOf(td)] : null;
	}

	function keyDownOnEl(event)
	{
	if (event.keyCode !== 13 /* Enter Key / && event.keyCode !== 38 / Up Key / && event.keyCode !== 40 / Down Key */)
	return;

	var el = event.target;
	if (event.keyCode === 13 && el.nodeName === "BUTTON")
	return; // Not handling Enter on Button, default action will be triggered

	var td = nextRotationTd(el.parentNode, event.keyCode !== 38 && !event.shiftKey);
	if (td !== null)
	td.firstElementChild.focus();
	event.preventDefault();
	}

	function inputTextChanged(event)
	{
	if (!event.target/* td */.classList.contains("noedit"))
	evaluate();
	}

	function evaluate()
	{
	clearError(); // Reset

	var trsTds = Array.from(tbody.children).map(tr => tr.children);

	// Checking for duplicate names and evaluation orders
	var names = {}, evOrders = {};
	for (var i = 0, len = trsTds.length; i < len; i++)
	{
	var trTds = trsTds[i];

	var nameEl = trTds[0];
	var name = nameEl.textContent;
	if (name.length !== 0)
	{
	if(!NAME_REG_EX.test(name))
	return showError(nameEl, "Invalid name: '" + name +"'. Only _, lower case, upper case and digits(not at the begining) are allowed.");
	if (names[name])
	return showError(nameEl, "Duplicate name: '" + name + "'");
	names[name] = true;
	}

	var evaluate = trTds[2].firstChild.checked;
	if (!evaluate)
	{
	if(name.length === 0)
	{ // Skip this row
	trsTds.splice(i, 1);
	i--;
	len--;
	}
	else
	{
	var value = trTds[1].textContent;
	if (value.length === 0)
	return showError(trTds[1], "No valid 'Value' is provided");
	}
	}
	else
	{
	var evOrderEl = trTds[3];
	var evOrderStr = evOrderEl.textContent;
	if (evOrderStr.length === 0)
	return showError(evOrderEl, "No 'Evaluation Order' is provided");
	var evOrder = +evOrderStr;
	if (isNaN(evOrder) \|\| evOrder < 1)
	return showError(evOrderEl, "Invalid 'Evaluation Order': " + evOrderStr);
	if (evOrders[evOrder])
	return showError(evOrderEl, "Duplicate 'Evaluation Order': " + evOrder);
	evOrders[evOrder] = true;

	if (trTds[4].textContent.length === 0)
	return showError(trTds[4], "No 'Formula' is provided");
	}
	}

	trsTds.sort((tds1, tds2) =>
	{
	var evaluate1 = tds1[2].firstChild.checked, evaluate2 = tds2[2].firstChild.checked;
	if (!evaluate1 \|\| !evaluate2)
	return (!evaluate1 && !evaluate2) ? 0 : !evaluate1 ? -1 : 1;
	var evOrder1 = +tds1[3].textContent, evOrder2 = +tds2[3].textContent;
	return evOrder1 < evOrder2 ? -1 : evOrder1 > evOrder2 ? 1 : 0;
	});

	var valueByName = {};

	for (var trTds of trsTds)
	{
	var name = trTds[0].textContent;
	if (!trTds[2].firstChild.checked)
	{
	if (name.length !== 0)
	{
	var valStr = trTds[1].textContent, valNum = +valStr;
	valueByName[name] = (valStr.length === 0 \|\| isNaN(valNum)) ? valStr : valNum;
	}
	continue;
	}

	var names = Object.keys(valueByName);

	// Long strings first
	names.sort((name1, name2) => (name1.length > name2.length) ? -1 : (name1.length < name2.length) ? 1 : 0);

	var formula = trTds[4].textContent;
	var valSubFormula = formula.replace(new RegExp("[_a-zA-Z]\\w*", "g"), name =>
	{
	if (valueByName.hasOwnProperty(name))
	{
	var val = valueByName[name];
	return (typeof val === "number") ? val : ("'" + val + "'" /* String */);
	}
	return name;
	});

	try
	{
	var result = eval(valSubFormula),
	resultStr = "" + result,
	resultNum = +resultStr;
	if (name.length !== 0)
	valueByName[name] = result;
	trTds[1].innerHTML = (resultStr.length === 0 \|\| isNaN(resultNum)) ? result : resultNum.toFixed(2);
	}
	catch (e)
	{
	return showError(trTds[4], "Unable to evaluate the formula: " + formula + ", " + e.message /* + ", " + formula */);
	}
	}
	}

	function showError(td, errMsg)
	{
	clearError();
	prevErrTd = td;
	prevErrTd.classList.add("err");
	$("errMsg").innerHTML = errMsg;
	}

	function clearError()
	{
	if (prevErrTd === null)
	return;
	prevErrTd.classList.remove("err");
	prevErrTd = null;
	$("errMsg").innerHTML = "";
	}

	function deleteRow(event)
	{
	var tr = event.target.parentNode.parentNode;
	var sib = tr.nextElementSibling \|\| tr.previousElementSibling; //TODO: event.shiftKey doesn't work
	tr.remove();
	if (sib !== null)
	sib.lastElementChild.firstElementChild.focus();
	evaluate();
	}
	</script>
	<style>
	</style>
	</head>
	<body onload="init()">
	<button id="addRowBtn" accesskey="A" title="Alt + Shift + A" onclick="addNewRow()">Add Row</button>
	<label id="errMsg"></label>
	<br>
	<br>
	<table id="data" align="center" style="width: 100%" border="1">
	<thead>
	<tr>
	<th style="width: 15%">Name</th>
	<th rowspan="2" style="width: 15%">Value</th>
	<th rowspan="2" style="width: 5%">Evaluate</th>
	<th style="width: 15%">Evaluation Order</th>
	<th style="width: 40%">Formula</th>
	<th rowspan="3" style="width: 10%">Delete Row</th>
	</tr>
	</thead>
	<tbody>
	<tr>
	<td contenteditable="true" onkeydown="keyDown(event)" onkeyup="inputTextChanged(event)"></td>
	<td contenteditable="true" onkeydown="keyDown(event)" onkeyup="inputTextChanged(event)"></td>
	<td onclick="this.firstChild.click();"><input type="checkbox" onkeydown="keyDownOnEl(event)" onclick="event.stopPropagation(); checkboxClicked(this)" /></td>
	<td contenteditable="true" onkeydown="keyDown(event)" onkeyup="inputTextChanged(event)"></td>
	<td contenteditable="true" onkeydown="keyDown(event)" onkeyup="inputTextChanged(event)"></td>
	<td><button onkeydown="keyDownOnEl(event)" onclick="deleteRow(event)">Delete</button></td>
	</tr>
	</tbody
	</table>
	</body>
	</html>