javascript - Jquery excel type dynamic formulas calculation -
i generating financial analysis , generate dynamic formula chart of accounts.
example
- cash -- input able field
- short term investments -- input able field
- liquid assets -- formula field , generate formula
@1~cº + @2~cº - notes receivable -- input able field
- accounts receivable -- input able field
- provision debts -- input able field
- net accounts receivable -- formula field , generate formula
@4~cº + @5~cº+@6~cº
and on...
i calculating on text box focusout calculate_year.
function calculate_year(thisobj) { var obj; //intiate vaiables var year_formulatxt = "",resultexp ="",lastpos ="",sequencetypeposc="",sequencetypeposp="",sequencetypeposf="",coacode="",coavalue="",ratio_formulatxt = ""; var ratio_formulatxt = "",coacode_p="",coavalue_pyear_formula = "",ratio_formula = "",year_formulaobj = "",ratio_formulaobj = "",year_formulaarr = "",ratio_formulaarr = "",formularesult=""; //match paterm var matchpattern = /[^\s()*/%+-/]+/g; var tableid = $(thisobj).closest('table').attr('id'); var cuurentdiv = $(thisobj).closest('.program-column').index(); if (cuurentdiv < 2){ // check if previous month added cuurentdiv = cuurentdiv + 1 ; } var tableidnext = $('.mcsb_container .program-column:eq('+cuurentdiv+') table').attr('id'); //get textbox inside div $('#'+tableid+' .financial_txt').each(function () { //º obj = $(this); year_formulaobj = obj.find('input[type=text]'); ratio_formulaobj= obj.find('input[type=text]:eq(1)'); //calcualtion formula fields if ($(year_formulaobj).attr('data-fieldtype') == "f") { //get formula custom field year_formula = $(year_formulaobj).attr('year_formula'); if($.trim(year_formula) !=""){ //match formula math's operator(binary operator) year_formulaarr = year_formula.match(matchpattern); //break string @ º : working single experssion using loop // ----------------------for year --------------------------- for( var i=0; i< year_formulaarr.length; i++ ){ //sub string '@' 'º' lastpos = year_formulaarr[i].substring(1, year_formulaarr[i].length - 1); //all sequence type sequencetypeposc = lastpos.indexof("c"); sequencetypeposp = lastpos.indexof("p"); sequencetypeposf = lastpos.indexof("f");// if(sequencetypeposc >= 0){ //console.log(lastpos); //getting value of coacode formula coacode = lastpos.substring(0, sequencetypeposc - 1); //getting value of coacode text box id coavalue = $.trim($('#'+tableid+' #txt_year_formula'+coacode).val()) == "" ? 0 : $.trim($('#'+tableid+' #txt_year_formula'+coacode).val()); $('#'+tableid+' #txt_year_formula'+coacode).val(coavalue); //work field value var tempres = year_formula.substring(year_formula.indexof("@"), year_formula.indexof("º")+1); year_formula = year_formula.replace(tempres,coavalue); tempres = year_formula; //replace rest of @ ,º 0 tempres = tempres.replace(/\s*@[^º]+º\s*/g,parsefloat(0)); var result = parsefloat(matheval(tempres)).tofixed(3)|| 0; $('#'+tableid+' #txt_year_formula'+$(this).attr('id')).val(matheval(result) == "nan" ?"0":matheval(result)); } } } } }); } function matheval (exp) { var reg = /(?:[a-z$_][a-z0-9$_]*)|(?:[;={}\[\]"'!&<>^\\?:])/ig, valid = true; // detect valid js identifier names , replace them exp = exp.replace(reg, function ($0) { // if name direct member of math, allow if (math.hasownproperty($0)) return "math."+$0; // otherwise expression invalid else valid = false; }); // don't eval if our replace function flagged invalid if (!valid){ //console.log("invalid arithmetic expression"); } else{ try { return (eval(exp) == "infinity" ? "0":eval(exp)); } catch (e) { }; } } the string break , generate calculation best way on function calculate_year ?
as seen calculating amount dynamic formulas,the problem there formulas return me infinity because of tempres.replace(/\s*@[^º]+º\s*/g,parsefloat(0));
@8~cº / @27~cº how can handle ?sorry english
if inspect see formula.
Comments
Post a Comment