1var ruleJS = function (root) { 2 'use strict'; 3 4 /** 5 * object instance 6 */ 7 var instance = this; 8 9 /** 10 * root element 11 */ 12 var rootElement = document.getElementById(root) || null; 13 14 /** 15 * current version 16 * @type {string} 17 */ 18 var version = '0.0.3'; 19 20 /** 21 * parser object delivered by jison library 22 * @type {Parser|*|{}} 23 */ 24 var parser = {}; 25 26 var FormulaParser = function (handler) { 27 var formulaLexer = function () {}; 28 formulaLexer.prototype = Parser.lexer; 29 30 var formulaParser = function () { 31 this.lexer = new formulaLexer(); 32 this.yy = {}; 33 }; 34 35 formulaParser.prototype = Parser; 36 var newParser = new formulaParser(); 37 newParser.setObj = function (obj) { 38 newParser.yy.obj = obj; 39 }; 40 41 newParser.yy.parseError = function (str, hash) { 42 // if (!((hash.expected && hash.expected.indexOf("';'") >= 0) && 43 // (hash.token === "}" || hash.token === "EOF" || 44 // parser.newLine || parser.wasNewLine))) 45 // { 46 // throw new SyntaxError(hash); 47 // } 48 throw { 49 name: 'Parser error', 50 message: str, 51 prop: hash, 52 }; 53 }; 54 newParser.yy.handler = handler; 55 56 return newParser; 57 }; 58 59 /** 60 * Exception object 61 * @type {{errors: {type: string, output: string}[], get: get}} 62 */ 63 var Exception = { 64 /** 65 * error types 66 */ 67 errors: [ 68 { type: 'NULL', output: '#NULL' }, 69 { type: 'DIV_ZERO', output: '#DIV/0!' }, 70 { type: 'VALUE', output: '#VALUE!' }, 71 { type: 'REF', output: '#REF!' }, 72 { type: 'NAME', output: '#NAME?' }, 73 { type: 'NUM', output: '#NUM!' }, 74 { type: 'NOT_AVAILABLE', output: '#N/A!' }, 75 { type: 'ERROR', output: '#ERROR' }, 76 { type: 'NEED_UPDATE', output: '#NEED_UPDATE' }, 77 ], 78 /** 79 * get error by type 80 * @param {String} type 81 * @returns {*} 82 */ 83 get: function (type) { 84 var error = Exception.errors.filter(function (item) { 85 return item.type === type || item.output === type; 86 })[0]; 87 88 return error ? error.output : null; 89 }, 90 }; 91 92 /** 93 * matrix collection for each form, contains cache of all form element 94 */ 95 var Matrix = function () { 96 /** 97 * single item (cell) object 98 * @type {{id: string, formula: string, value: string, error: string, deps: Array, formulaEdit: boolean}} 99 */ 100 var item = { 101 id: '', 102 formula: '', 103 value: '', 104 error: '', 105 deps: [], 106 formulaEdit: false, 107 }; 108 109 /** 110 * array of items 111 * @type {Array} 112 */ 113 this.data = []; 114 115 /** 116 * form elements, which can be parsed 117 * @type {string[]} 118 */ 119 var formElements = ['input[type=text]', '[data-formula]']; 120 121 var listen = function () { 122 if (document.activeElement && document.activeElement !== document.body) { 123 document.activeElement.blur(); 124 } else if (!document.activeElement) { 125 //IE 126 document.body.focus(); 127 } 128 }; 129 130 /** 131 * get item from data array 132 * @param {String} id 133 * @returns {*} 134 */ 135 this.getItem = function (id) { 136 return instance.matrix.data.filter(function (item) { 137 return item.id === id; 138 })[0]; 139 }; 140 141 this.needRecalc = function () { 142 var need = false; 143 jQuery.each(instance.matrix.data, function (i, item) { 144 if (item.needUpdate || item.error === '#NEED_UPDATE') { 145 need = true; 146 return false; 147 } 148 }); 149 return need; 150 }; 151 152 /** 153 * remove item from data array 154 * @param {String} id 155 */ 156 this.removeItem = function (id) { 157 instance.matrix.data = instance.matrix.data.filter(function (item) { 158 return item.id !== id; 159 }); 160 }; 161 162 /** 163 * remove items from data array in col 164 * @param {Number} col 165 */ 166 this.removeItemsInCol = function (col) { 167 instance.matrix.data = instance.matrix.data.filter(function (item) { 168 return item.col !== col; 169 }); 170 }; 171 172 /** 173 * remove items from data array in row 174 * @param {Number} row 175 */ 176 this.removeItemsInRow = function (row) { 177 instance.matrix.data = instance.matrix.data.filter(function (item) { 178 return item.row !== row; 179 }); 180 }; 181 182 /** 183 * remove items from data array below col 184 * @param col 185 */ 186 this.removeItemsBelowCol = function (col) { 187 instance.matrix.data = instance.matrix.data.filter(function (item) { 188 return item.col < col; 189 }); 190 }; 191 192 /** 193 * remove items from data array below row 194 * @param row 195 */ 196 this.removeItemsBelowRow = function (row) { 197 instance.matrix.data = instance.matrix.data.filter(function (item) { 198 return item.row < row; 199 }); 200 }; 201 202 /** 203 * update item properties 204 * @param {Object|String} item or id 205 * @param {Object} props 206 */ 207 this.updateItem = function (item, props) { 208 if (instance.utils.isString(item)) { 209 item = instance.matrix.getItem(item); 210 } 211 212 if (item && props) { 213 for (var p in props) { 214 if (item[p] && instance.utils.isArray(item[p])) { 215 if (instance.utils.isArray(props[p])) {
216 props[p].forEach(function (i) { 217 if (item[p].indexOf(i) === -1) { 218 item[p].push(i); 219 } 220 }); 221 } else { 222 if (item[p].indexOf(props[p]) === -1) { 223 item[p].push(props[p]); 224 } 225 } 226 } else { 227 item[p] = props[p]; 228 } 229 } 230 } 231 }; 232 233 /** 234 * add item to data array 235 * @param {Object} item 236 */ 237 this.addItem = function (item) { 238 var cellId = item.id, 239 coords = instance.utils.cellCoords(cellId); 240 241 item.row = coords.row; 242 item.col = coords.col; 243 244 var cellExist = instance.matrix.data.filter(function (cell) { 245 return cell.id === cellId; 246 })[0]; 247 248 if (!cellExist) { 249 instance.matrix.data.push(item); 250 } else { 251 instance.matrix.updateItem(cellExist, item); 252 } 253 254 return instance.matrix.getItem(cellId); 255 }; 256 257 /** 258 * get references items to column 259 * @param {Number} col 260 * @returns {Array} 261 */ 262 this.getRefItemsToColumn = function (col) { 263 var result = []; 264 265 if (!instance.matrix.data.length) { 266 return result; 267 } 268 269 instance.matrix.data.forEach(function (item) { 270 if (item.deps) { 271 var deps = item.deps.filter(function (cell) { 272 var alpha = instance.utils.getCellAlphaNum(cell).alpha, 273 num = instance.utils.toNum(alpha); 274 275 return num >= col; 276 }); 277 278 if (deps.length > 0 && result.indexOf(item.id) === -1) { 279 result.push(item.id); 280 } 281 } 282 }); 283 284 return result; 285 }; 286 287 this.getRefItemsToRow = function (row) { 288 var result = []; 289 290 if (!instance.matrix.data.length) { 291 return result; 292 } 293 294 instance.matrix.data.forEach(function (item) { 295 if (item.deps) { 296 var deps = item.deps.filter(function (cell) { 297 var num = instance.utils.getCellAlphaNum(cell).num; 298 return num > row; 299 }); 300 301 if (deps.length > 0 && result.indexOf(item.id) === -1) { 302 result.push(item.id); 303 } 304 } 305 }); 306 307 return result; 308 }; 309 310 /** 311 * update element item properties in data array 312 * @param {Element} element 313 * @param {Object} props 314 */ 315 this.updateElementItem = function (element, props) { 316 var id = element.getAttribute('data-cell-id'), 317 item = instance.matrix.getItem(id); 318 instance.matrix.updateItem(item, props); 319 }; 320 321 /** 322 * get cell dependencies 323 * @param {String} id 324 * @returns {Array} 325 */ 326 this.getDependencies = function (id) { 327 /** 328 * get dependencies by element 329 * @param {String} id 330 * @returns {Array} 331 */ 332 var getDependencies = function (id) { 333 var filtered = instance.matrix.data.filter(function (cell) { 334 if (cell.deps) { 335 return cell.deps.indexOf(id) > -1; 336 } 337 }); 338 339 var deps = []; 340 filtered.forEach(function (cell) { 341 if (deps.indexOf(cell.id) === -1) { 342 deps.push(cell.id); 343 } 344 }); 345 346 return deps; 347 }; 348 349 var allDependencies = []; 350 351 /** 352 * get total dependencies 353 * @param {String} id 354 */ 355 var getTotalDependencies = function (id) { 356 var deps = getDependencies(id); 357 358 if (deps.length) { 359 deps.forEach(function (refId) { 360 if (allDependencies.indexOf(refId) === -1) { 361 allDependencies.push(refId); 362 363 var item = instance.matrix.getItem(refId); 364 if (item.deps.length) { 365 getTotalDependencies(refId); 366 } 367 } 368 }); 369 } 370 }; 371 372 getTotalDependencies(id); 373 374 return allDependencies; 375 }; 376 377 /** 378 * get total element cell dependencies 379 * @param {Element} element 380 * @returns {Array} 381 */ 382 this.getElementDependencies = function (element) { 383 return instance.matrix.getDependencies(element.getAttribute('id')); 384 }; 385 386 /** 387 * recalculate refs cell 388 * @param {Element} element 389 */ 390 var recalculateElementDependencies = function (element) { 391 var allDependencies = instance.matrix.getElementDependencies(element), 392 id = element.getAttribute('id'); 393
394 allDependencies.forEach(function (refId) { 395 var item = instance.matrix.getItem(refId); 396 if (item && item.formula) { 397 var refElement = document.getElementById(refId); 398 calculateElementFormula(item.formula, refElement); 399 } 400 }); 401 }; 402 403 /** 404 * calculate element formula 405 * @param {String} formula 406 * @param {Element} element 407 * @returns {Object} 408 */ 409 var calculateElementFormula = function (formula, element) { 410 // to avoid double translate formulas, update item data in parser 411 var parsed = parse(formula, element), 412 value = parsed.result, 413 error = parsed.error, 414 nodeName = element.nodeName.toUpperCase(); 415 416 if (parsed.error && formula.indexOf('IFERROR') > -1) { 417 var matches = formula.match(/\((\(*.*\)*),(\(*.*\)*)\)$/); 418 if (matches) { 419 var secondParse = parse(matches[2], element); 420 if (!secondParse.error) { 421 error = null; 422 value = secondParse.result; 423 } 424 } 425 } 426 427 instance.matrix.updateElementItem(element, { value: value, error: error }); 428 429 if (error === '#VALUE!') { 430 error = 0; 431 } 432 433 var renderedValue = value; 434 435 if (['INPUT'].indexOf(nodeName) === -1) { 436 /*SUPSYSTIC Originaly here was element.innerText and this didn't work in FF browser */ 437 438 if (value !== '0' && value !== 0 && value % 1 !== 0) { 439 // round float 440 var floatValue = parseFloat(value); 441 if (floatValue.toString().indexOf('.') !== -1) { 442 var afterPointSybolsLength = floatValue.toString().split('.')[1].length; 443 if (afterPointSybolsLength > 4) { 444 renderedValue = floatValue.toFixed(4); 445 } 446 } 447 } 448 var renderedResult = renderedValue || error, 449 isHyperlinkFormula = formula && formula.toUpperCase().indexOf('HYPERLINK(') === 0; 450 451 if (isHyperlinkFormula) { 452 if (renderedResult != element.innerHTML) instance.isEdited = true; 453 element.innerHTML = renderedResult; 454 } else if (element.innerText) { 455 if (renderedResult != element.innerText) instance.isEdited = true; 456 element.innerText = renderedResult; 457 } else { 458 if (renderedResult != element.textContent) instance.isEdited = true; 459 element.textContent = renderedResult; 460 } 461 /* */ 462 } 463 464 var parseResult = value || error; 465 if (parseResult != element.value) instance.isEdited = true; 466 element.value = parseResult; 467 468 return parsed; 469 }; 470 471 /** 472 * register new found element to matrix 473 * @param {Element} element 474 * @returns {Object} 475 */ 476 var registerElementInMatrix = function (element, skipCalculation) { 477 var id = element.getAttribute('data-cell-id'), 478 formula = element.getAttribute('data-formula'); 479 480 if (formula) { 481 // add item with basic properties to data array 482 instance.matrix.addItem({ 483 id: id, 484 formula: formula, 485 }); 486 487 if (!skipCalculation) { 488 calculateElementFormula(formula, element); 489 } 490 } 491 }; 492 493 /** 494 * register events for elements 495 * @param element 496 */ 497 var registerElementEvents = function (element) { 498 var id = element.getAttribute('id'); 499 500 // on db click show formula 501 element.addEventListener('dblclick', function () { 502 var item = instance.matrix.getItem(id); 503 504 if (item && item.formula) { 505 item.formulaEdit = true; 506 element.value = '=' + item.formula; 507 } 508 }); 509 510 element.addEventListener('blur', function () { 511 var item = instance.matrix.getItem(id); 512 513 if (item) { 514 if (item.formulaEdit) { 515 element.value = item.value || item.error; 516 } 517 518 item.formulaEdit = false; 519 } 520 }); 521 522 // if pressed ESC restore original value 523 element.addEventListener('keyup', function (event) { 524 switch (event.keyCode) { 525 case 13: // ENTER 526 case 27: // ESC 527 // leave cell 528 listen(); 529 break; 530 } 531 }); 532
533 // re-calculate formula if ref cells value changed 534 element.addEventListener('change', function () { 535 // reset and remove item 536 instance.matrix.removeItem(id); 537 538 // check if inserted text could be the formula 539 var value = element.value; 540 541 if (value && value[0] === '=') { 542 element.setAttribute('data-formula', value.substr(1)); 543 registerElementInMatrix(element); 544 } 545 546 // get ref cells and re-calculate formulas 547 recalculateElementDependencies(element); 548 }); 549 }; 550 551 this.depsInFormula = function (item) { 552 var formula = item.formula, 553 deps = item.deps; 554 555 if (deps) { 556 deps = deps.filter(function (id) { 557 return formula.indexOf(id) !== -1; 558 }); 559 560 return deps.length > 0; 561 } 562 563 return false; 564 }; 565 566 /** 567 * scan the form and build the calculation matrix 568 */ 569 this.scan = function () { 570 var $totalElements = instance.instanceTable ? instance.instanceTable.api().cells().nodes() : rootElement.querySelectorAll(formElements); 571 //var $totalElements = rootElement.querySelectorAll(formElements); 572 var formulaElements = []; 573 574 // iterate through elements contains specified attributes 575 [].slice.call($totalElements).forEach(function ($item) { 576 registerElementInMatrix($item, true); 577 registerElementEvents($item); 578 579 if ($item.getAttribute('data-formula')) { 580 formulaElements.push($item); 581 } 582 }); 583 584 formulaElements.forEach(function ($item) { 585 calculateElementFormula($item.getAttribute('data-formula'), $item); 586 }); 587 }; 588 }; 589 590 /** 591 * utils methods 592 * @type {{isArray: isArray, toNum: toNum, toChar: toChar, cellCoords: cellCoords}} 593 */ 594 var utils = { 595 /** 596 * check if value is array 597 * @param value 598 * @returns {boolean} 599 */ 600 isArray: function (value) { 601 return Object.prototype.toString.call(value) === '[object Array]'; 602 }, 603 604 /** 605 * check if value is number 606 * @param value 607 * @returns {boolean} 608 */ 609 isNumber: function (value) { 610 return Object.prototype.toString.call(value) === '[object Number]'; 611 }, 612 613 /** 614 * check if value is string 615 * @param value 616 * @returns {boolean} 617 */ 618 isString: function (value) { 619 return Object.prototype.toString.call(value) === '[object String]'; 620 }, 621 622 /** 623 * check if value is function 624 * @param value 625 * @returns {boolean} 626 */ 627 isFunction: function (value) { 628 return Object.prototype.toString.call(value) === '[object Function]'; 629 }, 630 631 /** 632 * check if value is undefined 633 * @param value 634 * @returns {boolean} 635 */ 636 isUndefined: function (value) { 637 return Object.prototype.toString.call(value) === '[object Undefined]'; 638 }, 639 640 /** 641 * check if value is null 642 * @param value 643 * @returns {boolean} 644 */ 645 isNull: function (value) { 646 return Object.prototype.toString.call(value) === '[object Null]'; 647 }, 648 649 /** 650 * check if value is set 651 * @param value 652 * @returns {boolean} 653 */ 654 isSet: function (value) { 655 return !instance.utils.isUndefined(value) && !instance.utils.isNull(value); 656 }, 657 658 /** 659 * check if value is cell 660 * @param {String} value 661 * @returns {Boolean} 662 */ 663 isCell: function (value) { 664 return value.match(/^[A-Za-z]+[0-9]+/) ? true : false; 665 }, 666 667 /** 668 * get row name and column number 669 * @param cell 670 * @returns {{alpha: string, num: number}} 671 */ 672 getCellAlphaNum: function (cell) { 673 var num = cell.match(/\d+$/), 674 alpha = cell.replace(num, ''); 675 676 return { 677 alpha: alpha, 678 num: parseInt(num[0], 10), 679 }; 680 }, 681 682 /** 683 * change row cell index A1 -> A2
684 * @param {String} cell 685 * @param {Number} counter 686 * @returns {String} 687 */ 688 changeRowIndex: function (cell, counter) { 689 var alphaNum = instance.utils.getCellAlphaNum(cell), 690 alpha = alphaNum.alpha, 691 col = alpha, 692 row = parseInt(alphaNum.num + counter, 10); 693 694 if (row < 1) { 695 row = 1; 696 } 697 698 return col + '' + row; 699 }, 700 701 /** 702 * change col cell index A1 -> B1 Z1 -> AA1 703 * @param {String} cell 704 * @param {Number} counter 705 * @returns {String} 706 */ 707 changeColIndex: function (cell, counter) { 708 var alphaNum = instance.utils.getCellAlphaNum(cell), 709 alpha = alphaNum.alpha, 710 col = instance.utils.toChar(parseInt(instance.utils.toNum(alpha) + counter, 10)), 711 row = alphaNum.num; 712 713 if (!col || col.length === 0) { 714 col = 'A'; 715 } 716 717 var fixedCol = alpha[0] === '$' || false, 718 fixedRow = alpha[alpha.length - 1] === '$' || false; 719 720 col = (fixedCol ? '$' : '') + col; 721 row = (fixedRow ? '$' : '') + row; 722 723 return col + '' + row; 724 }, 725 726 changeFormula: function (formula, delta, change) { 727 if (!delta) { 728 delta = 1; 729 } 730 731 return formula.replace(/(\$?[A-Za-z]+\$?[0-9]+)/g, function (match) { 732 var alphaNum = instance.utils.getCellAlphaNum(match), 733 alpha = alphaNum.alpha, 734 num = alphaNum.num; 735 736 if (instance.utils.isNumber(change.col)) { 737 num = instance.utils.toNum(alpha); 738 739 if (change.col <= num) { 740 return instance.utils.changeColIndex(match, delta); 741 } 742 } 743 744 if (instance.utils.isNumber(change.row)) { 745 if (change.row < num) { 746 return instance.utils.changeRowIndex(match, delta); 747 } 748 } 749 750 return match; 751 }); 752 }, 753 754 /** 755 * update formula cells 756 * @param {String} formula 757 * @param {String} direction 758 * @param {Number} delta 759 * @returns {String} 760 */ 761 updateFormula: function (formula, direction, delta) { 762 var type, counter; 763 764 // left, right -> col 765 if (['left', 'right'].indexOf(direction) !== -1) { 766 type = 'col'; 767 } else if (['up', 'down'].indexOf(direction) !== -1) { 768 type = 'row'; 769 } 770 771 // down, up -> row 772 if (['down', 'right'].indexOf(direction) !== -1) { 773 counter = delta * 1; 774 } else if (['up', 'left'].indexOf(direction) !== -1) { 775 counter = delta * -1; 776 } 777 778 if (type && counter) { 779 return formula.replace(/(\$?[A-Za-z]+\$?[0-9]+)/g, function (match) { 780 var alpha = instance.utils.getCellAlphaNum(match).alpha; 781 782 var fixedCol = alpha[0] === '$' || false, 783 fixedRow = alpha[alpha.length - 1] === '$' || false; 784 785 if (type === 'row' && fixedRow) { 786 return match; 787 } 788 789 if (type === 'col' && fixedCol) { 790 return match; 791 } 792 793 return type === 'row' ? instance.utils.changeRowIndex(match, counter) : instance.utils.changeColIndex(match, counter); 794 }); 795 } 796 797 return formula; 798 }, 799 800 /** 801 * convert string char to number e.g A => 0, Z => 25, AA => 27 802 * @param {String} chr 803 * @returns {Number} 804 */ 805 toNum: function (chr) { 806 // chr = instance.utils.clearFormula(chr).split(''); 807 // 808 // var base = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"], 809 // i, j, result = 0; 810 // 811 // for (i = 0, j = chr.length - 1; i < chr.length; i += 1, j -= 1) { 812 // result += Math.pow(base.length, j) * (base.indexOf(chr[i])); 813 // } 814 // 815 // return result; 816 817 chr = instance.utils.clearFormula(chr); 818 var baseUppercase = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 819 baseLowercase = baseUppercase.toLowerCase(), 820 base, 821 i, 822 j, 823 result = 0; 824 825 for (i = 0, j = chr.length - 1; i < chr.length; i += 1, j -= 1) { 826 // Fix for using lovercase cell coords in formulas - supsystic 827 if (baseLowercase.indexOf(chr[i]) === -1) { 828 base = baseUppercase; 829 } else {
830 base = baseLowercase; 831 } 832 result += Math.pow(base.length, j) * (base.indexOf(chr[i]) + 1); 833 } 834 835 if (result) { 836 --result; 837 } 838 839 return result; 840 }, 841 842 /** 843 * convert number to string char, e.g 0 => A, 25 => Z, 26 => AA 844 * @param {Number} num 845 * @returns {String} 846 */ 847 toChar: function (num) { 848 var s = ''; 849 850 while (num >= 0) { 851 s = String.fromCharCode((num % 26) + 97) + s; 852 num = Math.floor(num / 26) - 1; 853 } 854 855 return s.toUpperCase(); 856 }, 857 858 /** 859 * get cell coordinates 860 * @param {String} cell A1 861 * @returns {{row: Number, col: number}} 862 */ 863 cellCoords: function (cell) { 864 var num = cell.match(/\d+$/), 865 alpha = cell.replace(num, ''); 866 867 return { 868 row: parseInt(num[0], 10) - 1, 869 col: instance.utils.toNum(alpha), 870 }; 871 }, 872 873 /** 874 * remove $ from formula 875 * @param {String} formula 876 * @returns {String|void} 877 */ 878 clearFormula: function (formula) { 879 return formula.replace(/\$/g, ''); 880 }, 881 882 /** 883 * translate cell coordinates to merged form {row:0, col:0} -> A1 884 * @param coords 885 * @returns {string} 886 */ 887 translateCellCoords: function (coords) { 888 return instance.utils.toChar(coords.col) + '' + parseInt(coords.row + 1, 10); 889 }, 890 891 /** 892 * iterate cell range and get theirs indexes and values 893 * @param {Object} startCell ex.: {row:1, col: 1} 894 * @param {Object} endCell ex.: {row:10, col: 1} 895 * @param {Function=} callback 896 * @returns {{index: Array, value: Array}} 897 */ 898 iterateCells: function (startCell, endCell, callback) { 899 var result = { 900 index: [], // list of cell index: A1, A2, A3 901 value: [], // list of cell value 902 }; 903 904 var cols = { 905 start: 0, 906 end: 0, 907 }; 908 909 if (endCell.col >= startCell.col) { 910 cols = { 911 start: startCell.col, 912 end: endCell.col, 913 }; 914 } else { 915 cols = { 916 start: endCell.col, 917 end: startCell.col, 918 }; 919 } 920 921 var rows = { 922 start: 0, 923 end: 0, 924 }; 925 926 if (endCell.row >= startCell.row) { 927 rows = { 928 start: startCell.row, 929 end: endCell.row, 930 }; 931 } else { 932 rows = { 933 start: endCell.row, 934 end: startCell.row, 935 }; 936 } 937 938 for (var column = cols.start; column <= cols.end; column++) { 939 for (var row = rows.start; row <= rows.end; row++) { 940 var cellIndex = instance.utils.toChar(column) + (row + 1), 941 cellValue = instance.helper.cellValue.call(this, cellIndex); 942 943 result.index.push(cellIndex); 944 result.value.push(cellValue); 945 } 946 } 947 948 if (instance.utils.isFunction(callback)) { 949 return callback.apply(callback, [result]); 950 } else { 951 return result; 952 } 953 }, 954 955 sort: function (rev) { 956 return function (a, b) { 957 return (a < b ? -1 : a > b ? 1 : 0) * (rev ? -1 : 1); 958 }; 959 }, 960 }; 961 962 /** 963 * helper with methods using by parser 964 * @type {{number: number, numberInverted: numberInverted, mathMatch: mathMatch, callFunction: callFunction}} 965 */ 966 var helper = { 967 /** 968 * list of supported formulas 969 */ 970 SUPPORTED_FORMULAS: [ 971 'UNIQUE', 972 'FLATTEN', 973 'FUNCTION', 974 'MOMENT', 975 'MOMENTADD', 976 'MOMENTDIFF', 977 'MOMENTSUB', 978 'MOMENTUTC', 979 'MOMENTUTCADD', 980 'MOMENTUTCDIFF', 981 'MOMENTUTCSUB', 982 'MOMENTUNIX', 983 'MOMENTFORMAT', 984 'MOMENTISLEAPYEAR', 985 'MOMENTISDST', 986 'MOMENTSTARTOF', 987 'MOMENTENDOF', 988 'MOMENTISAFTER', 989 'MOMENTISBEFORE', 990 'INDEX', 991 'INTERVAL', 992 'ARGSCONCAT', 993 'ARGSTOARRAY', 994 'CLEANFLOAT', 995 'COUNTIN', 996 'FINDFIELD', 997 'FINDRESULTINDEX', 998 'DAVERAGE', 999 'DCOUNT', 1000 'DCOUNTA', 1001 'DGET', 1002 'DMAX', 1003 'DMIN', 1004 'DPRODUCT', 1005 'DSTDEV', 1006 'DSTDEVP', 1007 'DSUM', 1008 'DVAR', 1009 'DVARP', 1010 'GETJSON', 1011 'DATE', 1012 'DATEVALUE', 1013 'DAY', 1014 'DAYS', 1015 'DAYS360', 1016 'EDATE', 1017 'EOMONTH', 1018 'FROMNOW', 1019 'HOUR', 1020 'MINUTE', 1021 'MATCH', 1022 'ISOWEEKNUM', 1023 'MONTH', 1024 'NETWORKDAYS', 1025 'NETWORKDAYSINTL', 1026 'NOW', 1027 'SECOND', 1028 'TIME', 1029 'TIMEVALUE', 1030 'TODAY', 1031 'WEEKDAY', 1032 'WEEKNUM', 1033 'WORKDAY', 1034 'WORKDAYINTL', 1035 'YEAR', 1036 'YEARFRAC', 1037 'BESSELI', 1038 'BESSELJ', 1039 'BESSELK', 1040 'BESSELY', 1041 'VALIDBIN', 1042 'BIN2DEC', 1043 'BIN2HEX', 1044 'BIN2OCT', 1045 'BITAND', 1046 'BITLSHIFT', 1047 'BITOR', 1048 'BITRSHIFT', 1049 'BITXOR', 1050 'COMPLEX', 1051 'CONVERT', 1052 'DEC2BIN', 1053 'DEC2HEX', 1054 'DEC2OCT', 1055 'DELTA', 1056 'ERF', 1057 'ERFC' /*"ERFCPRECISE",*/ /*"ERFPRECISE",*/, 1058 'GESTEP', 1059 'HEX2BIN', 1060 'HEX2DEC', 1061 'HEX2OCT', 1062 'IMABS', 1063 'IMAGINARY', 1064 'IMARGUMENT', 1065 'IMCONJUGATE', 1066 'IMCOS', 1067 'IMCOSH', 1068 'IMCOT',
1069 'IMCSC', 1070 'IMCSCH', 1071 'IMDIV', 1072 'IMEXP', 1073 'IMLN', 1074 'IMLOG10', 1075 'IMLOG2', 1076 'IMPOWER', 1077 'IMPRODUCT', 1078 'IMREAL', 1079 'IMSEC', 1080 'IMSECH', 1081 'IMSIN', 1082 'IMSINH', 1083 'IMSQRT', 1084 'IMSUB', 1085 'IMSUM', 1086 'IMTAN', 1087 'OCT2BIN', 1088 'OCT2DEC', 1089 'OCT2HEX', 1090 'ACCRINT', 1091 /*"ACCRINTM", "AMORDEGRC", "AMORLINC", 1092 "COUPDAYBS", "COUPDAYS", "COUPDAYSNC", "COUPNCD", "COUPNUM", "COUPPCD",*/ 'CUMIPMT', 1093 'CUMPRINC', 1094 'DB', 1095 'DDB', 1096 /*"DISC",*/ 'DOLLARDE', 1097 'DOLLARFR' /*"DURATION",*/, 1098 'EFFECT', 1099 'FV', 1100 'FVSCHEDULE', 1101 /*"INTRATE",*/ 'IPMT', 1102 'IRR', 1103 'ISPMT', 1104 /*"MDURATION",*/ 'MIRR', 1105 'NOMINAL', 1106 'NPER', 1107 'NPV', 1108 /*"ODDFPRICE", "ODDFYIELD", "ODDLPRICE", "ODDLYIELD",*/ 1109 'PDURATION', 1110 'PMT', 1111 'PPMT', 1112 /*"PRICE", "PRICEDISC", "PRICEMAT",*/ 'PV', 1113 'RATE', 1114 /*"RECEIVED",*/ 'RRI', 1115 'SLN', 1116 'SYD', 1117 'TBILLEQ', 1118 'TBILLPRICE', 1119 'TBILLYIELD', 1120 /*"VDB",*/ 1121 'XIRR', 1122 'XNPV', 1123 /*"YIELD", "YIELDDISC", "YIELDMAT",*/ 1124 'ISNUMBER', 1125 'AND', 1126 'FALSE', 1127 'SWITCH', 1128 'IF', 1129 'IFNA', 1130 'NOT', 1131 'OR', 1132 'TRUE', 1133 'XOR', 1134 'REFERENCE', 1135 'ABS', 1136 'ACOS', 1137 'ACOSH', 1138 'ACOT', 1139 'ACOTH', 1140 'AGGREGATE', 1141 'ARABIC', 1142 'ASIN', 1143 'ASINH', 1144 'ATAN', 1145 'ATAN2', 1146 'ATANH', 1147 'BASE', 1148 'CEILING', 1149 'CEILINGMATH', 1150 'CEILINGPRECISE', 1151 'COMBIN', 1152 'COMBINA', 1153 'COS', 1154 'COSH', 1155 'COT', 1156 'COTH', 1157 'CSC', 1158 'CSCH', 1159 'DECIMAL', 1160 'DEGREES', 1161 'EVEN', 1162 'EXP', 1163 'FACT', 1164 'FACTDOUBLE', 1165 'FLOOR', 1166 'FLOORMATH', 1167 'FLOORPRECISE', 1168 'GCD', 1169 'INT', 1170 'ISEVEN', 1171 'ISOCEILING', 1172 'ISODD', 1173 'LCM', 1174 'LN', 1175 'LOG', 1176 'LOG10', 1177 'MOD', 1178 'MROUND', 1179 'MULTINOMIAL', 1180 'ODD', 1181 'E', 1182 'PI', 1183 'POWER', 1184 'PRODUCT', 1185 'QUOTIENT', 1186 'RADIANS', 1187 'RAND', 1188 'RANDBETWEEN', 1189 'ROUND', 1190 'ROUNDDOWN', 1191 'ROUNDUP', 1192 'SERIESSUM', 1193 'SEC', 1194 'SECH', 1195 'SIGN', 1196 'SIN', 1197 'SINH', 1198 'SQRT', 1199 'SQRTPI', 1200 'SUBTOTAL', 1201 'SUM', 1202 'SUMIF', 1203 'SUMIFS', 1204 'SUMPRODUCT', 1205 'SUMSQ', 1206 'SUMX2MY2', 1207 'SUMX2PY2', 1208 'SUMXMY2', 1209 'TAN', 1210 'TANH', 1211 'TRUNC', 1212 'AVEDEV', 1213 'AVERAGE', 1214 'AVERAGEA', 1215 'AVERAGEIF', 1216 'AVERAGEIFS', 1217 'BETADIST', 1218 'BETAINV', 1219 'BINOMDIST', 1220 'BINOMDISTRANGE', 1221 'BINOMINV', 1222 'CHISQDIST', 1223 /*"CHISQDISTRT", "CHISQINV", "CHISQINVRT", "CHISQTEST",*/ 'CONFIDENCENORM', 1224 'CONFIDENCET', 1225 'CORREL', 1226 'COUNT', 1227 'COUNTA', 1228 'COUNTBLANK', 1229 'COUNTIF', 1230 'COUNTIFS', 1231 'COUNTUNIQUE', 1232 'COVARIANCEP', 1233 'COVARIANCES', 1234 'DEVSQ', 1235 'EXPONDIST', 1236 'FDIST', 1237 /*"FDISTRT",*/ 'FINV', 1238 /*"FINVRT", "FTEST",*/ 'FISHER', 1239 'FISHERINV', 1240 'FORECAST', 1241 'FREQUENCY', 1242 'GAMMA', 1243 /*"GAMMADIST", "GAMMAINV",*/ 'GAMMALN', 1244 /*"GAMMALNPRECISE",*/ 'GAUSS', 1245 'GEOMEAN', 1246 'GROWTH', 1247 'HARMEAN', 1248 'HYPGEOMDIST', 1249 'INTERCEPT', 1250 'KURT', 1251 'LARGE', 1252 'LINEST', 1253 /*"LOGEST",*/ 'LOGNORMDIST', 1254 'LOGNORMINV', 1255 'MAX', 1256 'MAXA', 1257 'MEDIAN', 1258 'MIN', 1259 'MINA', 1260 'MODEMULT', 1261 'MODESNGL', 1262 'NEGBINOMDIST', 1263 'NORMDIST', 1264 'NORMINV', 1265 'NORMSDIST', 1266 'NORMSINV', 1267 'PEARSON', 1268 'PERCENTILEEXC', 1269 'PERCENTILEINC', 1270 'PERCENTRANKEXC', 1271 'PERCENTRANKINC', 1272 'PERMUT', 1273 'PERMUTATIONA', 1274 'PHI', 1275 'POISSONDIST', 1276 'PROB', 1277 'QUARTILEEXC', 1278 'QUARTILEINC', 1279 'RANK', 1280 'RANKAVG', 1281 'RANKEQ', 1282 'RSQ', 1283 'SKEW',
1284 'SKEWP', 1285 'SLOPE', 1286 'SMALL', 1287 'STANDARDIZE', 1288 'STDEVA', 1289 'STDEVP', 1290 'STDEVPA', 1291 'STDEVS', 1292 'STEYX', 1293 'TDIST', 1294 /*"TDIST2T", "TDISTRT",*/ 'TINV', 1295 /*"TINV2T", "TTEST", "TREND",*/ 'TRIMMEAN', 1296 'VARA', 1297 'VARP', 1298 'VARPA', 1299 'VARS', 1300 'WEIBULLDIST', 1301 'ZTEST', 1302 'CHAR', 1303 'CLEAN', 1304 'CODE', 1305 'CONCATENATE', 1306 'DOLLAR', 1307 'EXACT', 1308 'FIND', 1309 'FIXED', 1310 'HTML2TEXT', 1311 'HUMANIZE', 1312 'HYPERLINK', 1313 'JOIN', 1314 'LEFT', 1315 'LEN', 1316 'LOWER', 1317 'MID', 1318 'NUMBERVALUE', 1319 'NUMBERS', 1320 'PROPER', 1321 'REGEXEXTRACT', 1322 'REGEXMATCH', 1323 'REGEXREPLACE', 1324 'REPLACE', 1325 'REPT', 1326 'RIGHT', 1327 'ROMAN', 1328 'SEARCH', 1329 'SPLIT', 1330 'SUBSTITUTE', 1331 'T', 1332 'TEXT', 1333 'TRIM', 1334 'UNICHAR', 1335 'UNICODE', 1336 'UPPER', 1337 'VALUE', 1338 'MD5', 1339 'NUMERAL', 1340 'ISERR', 1341 'ISERROR', 1342 'IFERROR', 1343 'POISSON', 1344 'LOOKUP', 1345 'VLOOKUP', 1346 ], 1347 1348 /** 1349 * get number 1350 * @param {Number|String} num 1351 * @returns {Number} 1352 */ 1353 number: function (num) { 1354 switch (typeof num) { 1355 case 'number': 1356 return num; 1357 case 'string': 1358 if (!isNaN(num)) { 1359 return num.indexOf('.') > -1 ? parseFloat(num) : parseInt(num, 10); 1360 } 1361 if (!num) { 1362 return 0; 1363 } 1364 } 1365 1366 return num; 1367 }, 1368 1369 /** 1370 * get string 1371 * @param {Number|String} str 1372 * @returns {string} 1373 */ 1374 string: function (str) { 1375 return str.substring(1, str.length - 1); 1376 }, 1377 1378 /** 1379 * invert number 1380 * @param num 1381 * @returns {Number} 1382 */ 1383 numberInverted: function (num) { 1384 return this.number(num) * -1; 1385 }, 1386 1387 /** 1388 * match special operation 1389 * @param {String} type 1390 * @param {String} exp1 1391 * @param {String} exp2 1392 * @returns {*} 1393 */ 1394 specialMatch: function (type, exp1, exp2) { 1395 var result; 1396 1397 switch (type) { 1398 case '&': 1399 result = exp1.toString() + exp2.toString(); 1400 break; 1401 } 1402 return result; 1403 }, 1404 1405 /** 1406 * match logic operation 1407 * @param {String} type 1408 * @param {String|Number} exp1 1409 * @param {String|Number} exp2 1410 * @returns {Boolean} result 1411 */ 1412 logicMatch: function (type, exp1, exp2) { 1413 var result; 1414 1415 switch (type) { 1416 case '=': 1417 result = exp1 === exp2; 1418 break; 1419 1420 case '@MORE@': 1421 result = exp1 > exp2; 1422 break; 1423 1424 case '@LESS@': 1425 result = exp1 < exp2; 1426 break; 1427 1428 case '@MORE@=': 1429 result = exp1 >= exp2; 1430 break; 1431 1432 case '@LESS@=': 1433 result = exp1 <= exp2; 1434 break; 1435 1436 case '@LESS@@MORE@': 1437 result = exp1 != exp2; 1438 break; 1439 1440 case 'NOT': 1441 result = exp1 != exp2; 1442 break; 1443 } 1444 1445 return result; 1446 }, 1447 1448 /** 1449 * match math operation 1450 * @param {String} type 1451 * @param {Number} number1 1452 * @param {Number} number2 1453 * @returns {*} 1454 */ 1455 mathMatch: function (type, number1, number2) { 1456 var result; 1457 var moment1 = null; 1458 var moment2 = null; 1459 1460 number1 = helper.number(number1); 1461 number2 = helper.number(number2); 1462 if (isNaN(number1) || isNaN(number2)) { 1463 if (number1[0] === '=' || number2[0] === '=') { 1464 throw Error('NEED_UPDATE'); 1465 } 1466 1467 if (isNaN(number1)) { 1468 moment1 = moment(number1, window.supsystic.Tables._dateFormat); 1469 if (moment1 && moment1._pf && moment1._pf.empty) { 1470 moment1 = null; 1471 number1 = 0; 1472 } 1473 } 1474 if (isNaN(number2)) { 1475 moment2 = moment(number2, window.supsystic.Tables._dateFormat); 1476 if (moment2 && moment2._pf && moment2._pf.empty) { 1477 moment2 = null; 1478 number2 = 0; 1479 } 1480 } 1481 } 1482 switch (type) { 1483 case '+': 1484 if (moment1 || moment2) { 1485 if (moment1 && moment2) { 1486 throw Error('VALUE'); 1487 } else if (moment1 && !moment2) { 1488 result = moment1.add(number2, 'days').format(window.supsystic.Tables._dateFormat); 1489 } else if (!moment1 && moment2) { 1490 result = moment2.add(number1, 'days').format(window.supsystic.Tables._dateFormat); 1491 } 1492 } else { 1493 result = number1 + number2; 1494 } 1495 break; 1496 case '-': 1497 if (moment1 || moment2) { 1498 if (moment1 && moment2) { 1499 result = moment1.diff(moment2, 'days'); 1500 } else if (moment1 && !moment2) { 1501 result = moment1.add(-number2, 'days').format(window.supsystic.Tables._dateFormat); 1502 } else if (!moment1 && moment2) { 1503 throw Error('VALUE'); 1504 } 1505 } else { 1506 result = number1 - number2; 1507 } 1508 break; 1509 case '/': 1510 if (moment1 || moment2) { 1511 throw Error('VALUE'); 1512 } else { 1513 result = number1 / number2; 1514 if (result == Infinity) { 1515 throw Error('DIV_ZERO'); 1516 } else if (isNaN(result)) { 1517 throw Error('VALUE'); 1518 } 1519 } 1520 break; 1521 case '*': 1522 if (moment1 || moment2) { 1523 throw Error('VALUE'); 1524 } else { 1525 result = number1 * number2; 1526 } 1527 break; 1528 case '^': 1529 if (moment1 || moment2) { 1530 throw Error('VALUE'); 1531 } else { 1532 result = Math.pow(number1, number2); 1533 } 1534 break; 1535 } 1536 1537 return result; 1538 }, 1539 1540 /** 1541 * call function from formula 1542 * @param {String} fn 1543 * @param {Array} args 1544 * @returns {*} 1545 */ 1546 callFunction: function (fn, args) { 1547 fn = fn.toUpperCase(); 1548 args = args || []; 1549 1550 if (instance.helper.SUPPORTED_FORMULAS.indexOf(fn) > -1) { 1551 if (instance.formulas[fn]) { 1552 return instance.formulas[fn].apply(this, args); 1553 } 1554 } 1555 1556 throw Error('NAME'); 1557 }, 1558 1559 /** 1560 * get variable from formula 1561 * @param {Array} args 1562 * @returns {*} 1563 */ 1564 callVariable: function (args) { 1565 args = args || []; 1566 var str = args[0]; 1567 1568 if (str) { 1569 str = str.toUpperCase(); 1570 if (instance.formulas[str]) { 1571 return typeof instance.formulas[str] === 'function' ? instance.formulas[str].apply(this, args) : instance.formulas[str]; 1572 } 1573 } 1574 1575 throw Error('NAME'); 1576 }, 1577 1578 /** 1579 * Get cell value 1580 * @param {String} cell => A1 AA1 1581 * @returns {*} 1582 */
1583 cellValue: function (cell) { 1584 var isEditorPagination = typeof g_stbPagination != 'undefined' && g_stbPagination && !(this instanceof HTMLElement), 1585 value, 1586 fnCellValue = instance.custom.cellValue, 1587 element = this, 1588 cellCoords = instance.utils.cellCoords(cell), 1589 item = instance.matrix.getItem(isEditorPagination ? instance.utils.translateCellCoords({ row: cellCoords.row - window.editor.pageStart, col: cellCoords.col }) : cell); 1590 1591 // check if custom cellValue fn exists 1592 if (instance.utils.isFunction(fnCellValue)) { 1593 var cellId = instance.utils.translateCellCoords({ row: element.row, col: element.col }); 1594 1595 // get value 1596 value = item ? item.value : isEditorPagination ? window.editor.getCellValuePagination(cellCoords.row, cellCoords.col) : fnCellValue(cellCoords.row, cellCoords.col); 1597 1598 if (instance.utils.isNull(value)) { 1599 value = 0; 1600 } 1601 1602 if (cellId) { 1603 //update dependencies 1604 instance.matrix.updateItem(cellId, { deps: [cell] }); 1605 } 1606 } else { 1607 // get value 1608 if (item) { 1609 value = item.value; 1610 } else { 1611 element = document.getElementById(cell); 1612 if (element) { 1613 value = element.value; 1614 } 1615 } 1616 1617 // Get custom value 1618 if (!value) { 1619 element = rootElement.querySelector('[data-cell-id=' + cell + ']'); 1620 if (!element && instance.instanceTable) { 1621 element = instance.instanceTable 1622 .api() 1623 .cell('[data-cell-id="' + cell + '"]') 1624 .node(); 1625 } 1626 1627 var originalValue = element.getAttribute('data-original-value'); 1628 if (originalValue !== undefined) { 1629 value = originalValue; 1630 } else { 1631 value = element.innerText || element.textContent; 1632 } 1633 } 1634 1635 //update dependencies 1636 instance.matrix.updateElementItem(element, { deps: [cell] }); 1637 } 1638 1639 // check references error 1640 if (item && item.deps) { 1641 if (item.deps.indexOf(cellId) !== -1) { 1642 throw Error('REF'); 1643 } 1644 } 1645 1646 // check if any error occurs 1647 if (item && item.error) { 1648 throw Error(item.error); 1649 } 1650 1651 // return value if is set 1652 if (instance.utils.isSet(value)) { 1653 var result = instance.helper.number(value); 1654 1655 return !isNaN(result) ? result : value; 1656 } 1657 1658 // cell is not available 1659 throw Error('NOT_AVAILABLE'); 1660 }, 1661 1662 /** 1663 * Get cell range values 1664 * @param {String} start cell A1 1665 * @param {String} end cell B3 1666 * @returns {Array} 1667 */ 1668 cellRangeValue: function (start, end) { 1669 var fnCellValue = instance.custom.cellValue, 1670 coordsStart = instance.utils.cellCoords(start), 1671 coordsEnd = instance.utils.cellCoords(end), 1672 element = this; 1673 1674 // iterate cells to get values and indexes 1675 var cells = instance.utils.iterateCells.call(this, coordsStart, coordsEnd), 1676 result = []; 1677 1678 // check if custom cellValue fn exists 1679 if (instance.utils.isFunction(fnCellValue)) { 1680 var cellId = instance.utils.translateCellCoords({ row: element.row, col: element.col }); 1681 1682 //update dependencies 1683 instance.matrix.updateItem(cellId, { deps: cells.index }); 1684 } else { 1685 //update dependencies 1686 instance.matrix.updateElementItem(element, { deps: cells.index }); 1687 } 1688 1689 result.push(cells.value); 1690 return result; 1691 }, 1692 1693 /** 1694 * Get fixed cell value 1695 * @param {String} id 1696 * @returns {*} 1697 */ 1698 fixedCellValue: function (id) { 1699 id = id.replace(/\$/g, ''); 1700 return instance.helper.cellValue.call(this, id); 1701 }, 1702 1703 /** 1704 * Get fixed cell range values 1705 * @param {String} start 1706 * @param {String} end 1707 * @returns {Array} 1708 */ 1709 fixedCellRangeValue: function (start, end) { 1710 start = start.replace(/\$/g, ''); 1711 end = end.replace(/\$/g, ''); 1712 1713 return instance.helper.cellRangeValue.call(this, start, end); 1714 }, 1715 1716 /** 1717 * Unescape HTML in the selectsd cells for formulas result. 1718 * @param $table 1719 */ 1720 unescapeHTML: function ($table) { 1721 $table = $table instanceof jQuery ? $table : jQuery($table); 1722 $table.find('.unescapeHTML').each(function () { 1723 var cell = jQuery(this), 1724 cellContent = cell 1725 .html() 1726 .replace(/</g, '<') 1727 .replace(/>/g, '>') 1728 .replace(/"/g, '"') 1729 .replace(/&/g, '&'); 1730 1731 cell.html(cellContent); 1732 }); 1733 }, 1734 }; 1735 1736 /** 1737 * parse input string using parser 1738 * @returns {Object} {{error: *, result: *}} 1739 * @param formula 1740 * @param element 1741 */ 1742 var parse = function (formula, element) { 1743 formula = window.supsystic.Tables.prepareFormulaToParse(formula); // fix of case sensitive for cells' indexes and formulas' names 1744 window.supsystic.Tables._currentFormula = formula; // to calculate custom supsystic formulas 1745 window.supsystic.Tables._dateFormat = ''; 1746 window.supsystic.Tables._timeFormat = ''; 1747 1748 if (SDT_DATA && SDT_DATA.isAdmin && typeof element.row != 'undefined' && typeof element.col != 'undefined') { 1749 var cellMeta = window.editor.getCellMeta(element.row, element.col); 1750 1751 window.supsystic.Tables._dateFormat = cellMeta && cellMeta.dateFormat ? cellMeta.dateFormat : jQuery('#editor-set-date-format').val(); 1752 window.supsystic.Tables._timeFormat = cellMeta && cellMeta.formatType == 'time_duration' ? cellMeta.format : jQuery('#editor-set-time-duration-format').val(); 1753 } else { 1754 var elem = jQuery(element); 1755 1756 window.supsystic.Tables._dateFormat = elem.data('cell-format') || elem.parents('.supsystic-table:first').data('date-format'); 1757 window.supsystic.Tables._timeFormat = elem.data('cell-format') || elem.parents('.supsystic-table:first').data('time-format'); 1758 } 1759 1760 var result = null, 1761 error = null; 1762 1763 try { 1764 if (element instanceof HTMLElement) { 1765 id = element.getAttribute('data-cell-id'); 1766 }
1766 else if (element && element.id) { 1767 id = element.id; 1768 } 1769 1770 var curItem = instance.matrix.getItem(id); 1771 1772 parser.setObj(element); 1773 result = parser.parse(formula); 1774 var id; 1775 1776 var deps = instance.matrix.getDependencies(id), 1777 isFrontend = typeof instance.isFrontend != 'undefined' || instance.isFrontend; 1778 1779 if (!isFrontend && deps.indexOf(id) !== -1) { 1780 result = null; 1781 1782 deps.forEach(function (id) { 1783 instance.matrix.updateItem(id, { value: null, error: Exception.get('REF') }); 1784 }); 1785 1786 throw Error('REF'); 1787 } 1788 1789 if (!error && result === 0) { 1790 result = '0'; 1791 } 1792 1793 if (!isFrontend && curItem && curItem.value != result) { 1794 deps.forEach(function (id) { 1795 instance.matrix.updateItem(id, { value: null, needUpdate: true }); 1796 }); 1797 } 1798 } catch (ex) { 1799 // Errors debugging 1800 //console.log(ex); 1801 var message = Exception.get(ex.message); 1802 1803 if (message) { 1804 error = message; 1805 } else { 1806 error = Exception.get('ERROR'); 1807 } 1808 1809 //console.debug(ex.prop); 1810 //debugger; 1811 //error = ex.message; 1812 //error = Exception.get('ERROR'); 1813 } 1814 1815 return { 1816 error: error, 1817 result: result, 1818 }; 1819 }; 1820 1821 /** 1822 * initial method, create formulas, parser and matrix objects 1823 */ 1824 var init = function () { 1825 instance = this; 1826 parser = new FormulaParser(instance); 1827 1828 instance.formulas = Formula; 1829 instance.formulas.POISSON = function (x, mean, cumulative) { 1830 return instance.formulas.POISSONDIST(x, mean, cumulative); 1831 }; 1832 instance.formulas.LOOKUP = function (lookup_value, lookup_vector, result_vector) { 1833 var lookupRows, lookupColumns, l, r, k, resultRows, resultColumns, key1, key2, dataValue1, dataValue2; 1834 1835 result_vector = (typeof result_vector[0] == 'object' ? result_vector[0] : result_vector) || null; 1836 lookup_vector = typeof lookup_vector[0] == 'object' ? lookup_vector[0] : lookup_vector; 1837 lookup_value = supsysticFlattenSingleValue(lookup_value); 1838 1839 if (typeof lookup_vector != 'object') { 1840 return Exception.get('NOT_AVAILABLE'); 1841 } 1842 1843 lookupRows = lookup_vector.length; 1844 l = Object.keys(lookup_vector); 1845 l = l.shift(); 1846 lookupColumns = lookup_vector[l].length; 1847 1848 if ((lookupRows == 1 && lookupColumns > 1) || (lookupRows == 2 && lookupColumns != 2)) { 1849 lookup_vector = supsysticTranspose(lookup_vector); 1850 lookupRows = lookup_vector.length; 1851 l = Object.keys(lookup_vector); 1852 lookupColumns = lookup_vector[l.shift()].length; 1853 } 1854 if (result_vector == null) { 1855 result_vector = lookup_vector; 1856 } 1857 1858 resultRows = result_vector.length; 1859 l = Object.keys(result_vector); 1860 l = l.shift(); 1861 resultColumns = result_vector[l].length; 1862 1863 if ((resultRows == 1 && resultColumns > 1) || (resultRows == 2 && resultColumns != 2)) { 1864 result_vector = supsysticTranspose(result_vector); 1865 resultRows = result_vector.length; 1866 r = Object.keys(result_vector); 1867 resultColumns = result_vector[r.shift()].length; 1868 } 1869 if (lookupRows == 2) { 1870 result_vector = lookup_vector.pop(); 1871 lookup_vector = lookup_vector.shift(); 1872 } 1873 if (lookupColumns != 2) { 1874 for (var v in lookup_vector) { 1875 if (typeof lookup_vector[v] == 'object') { 1876 k = Object.keys(lookup_vector[v]); 1877 key1 = key2 = k.shift(); 1878 key2++; 1879 dataValue1 = lookup_vector[v][key1]; 1880 } else { 1881 key1 = 0; 1882 key2 = 1; 1883 dataValue1 = lookup_vector[v]; 1884 } 1885 dataValue2 = result_vector.shift(); 1886 1887 if (typeof dataValue2 == 'object') { 1888 dataValue2 = dataValue2.shift(); 1889 } 1890 lookup_vector[v] = {}; 1891 lookup_vector[v][key1] = dataValue1; 1892 lookup_vector[v][key2] = dataValue2; 1893 } 1894 } 1895 1896 return instance.formulas.VLOOKUP(lookup_value, lookup_vector, 2, false); 1897 }; 1898 instance.formulas.VLOOKUP = function (lookup_value, lookup_array, index_number, not_exact_match) { 1899 var f, 1900 firstRow, 1901 firstColumn, 1902 returnColumn, 1903 columnKeys, 1904 rowNumber = false, 1905 rowValue = false; 1906 1907 not_exact_match = typeof not_exact_match != 'undefined' ? not_exact_match : true; 1908 lookup_value = supsysticFlattenSingleValue(lookup_value); 1909 index_number = supsysticFlattenSingleValue(index_number); 1910 not_exact_match = supsysticFlattenSingleValue(not_exact_match); 1911 1912 // index_number must be greater than or equal to 1 1913 if (index_number < 1) { 1914 return Exception.get('VALUE'); 1915 } 1916 1917 // index_number must be less than or equal to the number of columns in lookup_array 1918 if (typeof lookup_array != 'object' || !lookup_array.length) { 1919 return Exception.get('REF'); 1920 } else { 1921 f = Object.keys(lookup_array); 1922 firstRow = f.pop();
1923 1924 if (typeof lookup_array[firstRow] != 'object' || index_number > lookup_array[firstRow].length) { 1925 return Exception.get('REF'); 1926 } else { 1927 columnKeys = Object.keys(lookup_array[firstRow]); 1928 returnColumn = columnKeys[--index_number]; 1929 firstColumn = columnKeys.shift(); 1930 } 1931 } 1932 if (!not_exact_match) { 1933 lookup_array.sort(supsysticVlookupSort); 1934 } 1935 for (var i in lookup_array) { 1936 if ((!isNaN(lookup_value) && !isNaN(lookup_array[i][firstColumn]) && lookup_array[i][firstColumn] > lookup_value) || (isNaN(lookup_value) && isNaN(lookup_array[i][firstColumn]) && lookup_array[i][firstColumn].toLowerCase() > lookup_value.toLowerCase())) { 1937 break; 1938 } 1939 rowNumber = i; 1940 rowValue = lookup_array[i][firstColumn]; 1941 } 1942 if (rowNumber !== false) { 1943 if (!not_exact_match && rowValue != lookup_value) { 1944 // if an exact match is required, we have what we need to return an appropriate response 1945 return Exception.get('NOT_AVAILABLE'); 1946 } else { 1947 // otherwise return the appropriate value 1948 return lookup_array[rowNumber][returnColumn]; 1949 } 1950 } 1951 1952 return Exception.get('NOT_AVAILABLE'); 1953 }; 1954 instance.matrix = new Matrix(); 1955 1956 instance.custom = {}; 1957 1958 if (rootElement) { 1959 var recalcLimit = 5; 1960 instance.isFrontend = true; 1961 do { 1962 instance.isEdited = false; 1963 instance.matrix.scan(); 1964 recalcLimit--; 1965 } while (instance.isEdited && recalcLimit > 0); 1966 1967 //instance.matrix.scan(); 1968 helper.unescapeHTML(rootElement); 1969 } 1970 }; 1971 1972 return { 1973 init: init, 1974 version: version, 1975 utils: utils, 1976 helper: helper, 1977 parse: parse, 1978 }; 1979}; 1980 1981// Transpose the matrix 1982function supsysticTranspose(matrixData) { 1983 var returnMatrix = [], 1984 column = 0; 1985 1986 if (typeof matrixData != 'object') { 1987 matrixData = [[matrixData]]; 1988 } 1989 1990 for (var i in matrixData) { 1991 var row = 0; 1992 1993 for (var j in matrixData[i]) { 1994 returnMatrix[row] = returnMatrix[row] ? returnMatrix[row] : []; 1995 returnMatrix[row][column] = matrixData[i][j]; 1996 ++row; 1997 } 1998 ++column; 1999 } 2000 return returnMatrix; 2001} 2002 2003function supsysticFlattenSingleValue(value) { 2004 value = value ? value : ''; 2005 2006 while (typeof value == 'object') { 2007 value = value.pop(); 2008 } 2009 2010 return value; 2011} 2012 2013function supsysticVlookupSort(a, b) { 2014 var aLower, bLower; 2015 2016 if ((aLower = a[0].toString().toLowerCase()) == (bLower = b[0].toString().toLowerCase())) { 2017 return 0; 2018 } 2019 return aLower < bLower ? -1 : 1; 2020}
Line numbers count LF bytes from the start of the resource, as the search results do. Vendor segments are library code the classifier recognised; they are stored but not indexed. Bytes are shown as Latin1 characters, one per byte.