1/*var tablecalc_table; 2var tablecalc_crow; 3var tablecalc_ccol; 4var tablecalc_labels; 5var tablecalc_defer;*/ 6 7function tablecalcXY(st) { 8 var r=st.match(/r\d+/); 9 y=r[0].substr(1)*1; 10 var c=st.match(/c\d+/); 11 x=c[0].substr(1)*1; 12 return new Array(x,y); 13} 14function tablecalcVal(x,y,table,tostring) { 15 if (typeof tostring == 'undefined') { 16 tostring=0; 17 } 18 var v='notset'; 19 if ((x>=0) && (y>=0)) { 20 if (typeof table.rows != 'undefined') { 21 if (typeof table.rows[y] != 'undefined') { 22 if (typeof table.rows[y].cells[x] != 'undefined') { 23 var mr=stripHTML(table.rows[y].cells[x].innerHTML); 24 mr=mr.trim(); 25 m=parseFloat(mr); 26 if (!isNaN(m)) { 27 v=m; 28 } else { 29 if (!tostring) { 30 v="notnum"; 31 } else { 32 v=String(mr); 33 } 34 } 35 } 36 } 37 } 38 } 39 return v; 40} 41 42 43function tablecalcToArray(a) { 44 if (!Array.isArray(a)) { 45 return [a]; 46 } else { 47 return a; 48 } 49} 50 51function tablecalcToNumArray(a) { 52 if (!Array.isArray(a)) { 53 a=[a]; 54 } 55 var b=[]; 56 for (var i=0;i<a.length;i++) { 57 if (!isNaN(a[i]*1)) { 58 b.push(a[i]); 59 } 60 } 61 return b; 62} 63 64function correctFloat(a) { 65 var x=10000000000000; 66 return Math.round(a*x)/x; 67} 68 69function sum(a) { 70 a=tablecalcToNumArray(a); 71 var s=0; 72 for (var i=0;i<a.length;i++) { 73 tablecalc_log(a[i]*1); 74 s+=a[i]*1; 75 s=correctFloat(s); 76 } 77 return s; 78} 79 80function average(a) { 81 a=tablecalcToNumArray(a); 82 return correctFloat(sum(a)/a.length); 83} 84 85function min(a) { 86 a=tablecalcToNumArray(a); 87 var s=1*a[0]; 88 for (var i=1;i<a.length;i++) { 89 if (1*a[i]<s) { 90 s=1*a[i]; 91 } 92 } 93 return s; 94} 95 96 97function max(a) { 98 a=tablecalcToNumArray(a); 99 var s=1*a[0]; 100 for (var i=1;i<a.length;i++) { 101 if (1*a[i]>s) { 102 s=1*a[i]; 103 } 104 } 105 return s; 106} 107 108function label(st) { 109 if (typeof tablecalc_labels[st] == 'undefined') { 110 tablecalc_labels[st]=tablecalc_table; 111 } 112 return ""; 113} 114 115function col() { 116 return tablecalc_ccol; 117} 118 119function row() { 120 return tablecalc_crow; 121} 122 123function cell(x,y) { 124 var tmp=tablecalcVal(x,y,tablecalc_table); 125 if ( (tmp=='notset') || (tmp=='notnum')) { 126 return ''; 127 } else { 128 return tmp; 129 } 130} 131 132function range(x1,y1,x2,y2) { 133 var members=new Array(); 134 for (var x=x1;x<=x2;x++) { 135 for (var y=y1;y<=y2;y++) { 136 var tmp=cell(x,y); 137 if (tmp!='') { 138 members[members.length]=tmp; 139 } 140 } 141 } 142 var result=""; 143 if (members.length>0) { 144 result="new Array("; 145 for (var k=0;k<members.length;k++) { 146 if (k) { 147 result+=','; 148 } 149 result+="'"+members[k]+"'"; 150 } 151 result+=")" 152 } else if (!tablecalc_checkfinal()) { 153 throw "norange"; 154 } 155 return eval(result); 156} 157 158function count(a) { 159 a=tablecalcToArray(a); 160 return a.length; 161} 162 163function calc() { 164 return nop(); 165} 166 167function round(num,digits) { 168 var d=1; 169 for (var i=0;i<digits;i++) { 170 d*=10; 171 } 172 var n=Math.round(num*d)/d; 173 return n.toFixed(digits); 174} 175 176 177function nop() { 178 return ""; 179} 180 181function check(condition,whenTrue,whenFalse) { 182 if (typeof condition == 'undefined') { 183 condition=0; 184 } 185 if (typeof whenTrue == 'undefined') { 186 whenTrue=""; 187 } 188 if (typeof whenFalse == 'undefined') { 189 whenFalse=""; 190 } 191 if (condition) { 192 return whenTrue; 193 } else { 194 return whenFalse; 195 } 196} 197 198function countif(range,check,operation) { 199 a=tablecalcToArray(range); 200 var cnt=0; 201 for (var i=0;i<a.length;i++) { 202 cnt+=compare(a[i],check,operation); 203 } 204 return cnt; 205} 206 207function compare(a,b,operation) { 208 if (typeof operation == 'undefined') { 209 operation='='; 210 } 211 if (typeof a == 'undefined') { 212 a=0; 213 } 214 if (typeof b == 'undefined') { 215 b=0; 216 } 217 switch (operation) { 218 case ">": 219 if (a>b) {return 1;} else {return 0;} 220 break; 221 case "<": 222 if (a<b) {return 1;} else {return 0;} 223 break; 224 case ">=": 225 if (a>=b) {return 1;} else {return 0;} 226 break; 227 case "<=": 228 if (a>=b) {return 1;} else {return 0;} 229 break; 230 case "<>": 231 case "!=": 232 if (a!=b) {return 1;} else {return 0;} 233 break; 234 case "=": 235 default: 236 if (a==b) {return 1;} else {return 0;} 237 } 238 return 0; 239} 240 241function tablecalc_log(st) { 242 if (!tablecalc_debug) {return;} 243 if (tablecalc_debug==1) { 244 alert(st); 245 } else { 246 console.log(st); 247 } 248} 249 250function tablecalc_checkfinal() { 251 if (typeof tablecalc_isfinal === "undefined") { 252 return 0; 253 } else { 254 return tablecalc_isfinal; 255 } 256} 257 258function tablecalc(divID, formula, final) { 259 260 if (isNaN(final)) {final=0;} 261 262 if (typeof tablecalc_debug === "undefined") { 263 window.tablecalc_debug=0; 264 } 265 if (typeof tablecalc_labels === "undefined") { 266 window.tablecalc_labels=[]; 267 } 268 if (typeof tablecalc_defer === "undefined") { 269 window.tablecalc_defer=[]; 270 } 271 if (typeof tablecalc_crow === "undefined") { 272 window.tablecalc_crow=null; 273 } 274 if (typeof tablecalc_ccol === "undefined") { 275 window.tablecalc_ccol=null; 276 } 277 if (typeof tablecalc_table === "undefined") { 278 window.tablecalc_table=null; 279 } 280 if (typeof tablecalc_isfinal === "undefined") { 281 window.tablecalc_isfinal=final; 282 } 283 if (typeof tablecalc_setfinal === "undefined") { 284 window.tablecalc_setfinal=1; 285 setTimeout(tablecalc_final,0) 286 } 287 288 var oFormula=formula; 289 290 tablecalc_log("Entering: "+divID+"=>"+formula+"; is final: "+final); 291 var div = document.getElementById(divID); 292 //getting parent TD 293 var table=0; 294 var cCol=0; 295 var cRow=0; 296 var pNode=findParentNodeByName(div,"TD"); 297 if (!pNode) { 298 pNode=findParentNodeByName(div,"TH"); 299 } 300 if (pNode) { 301 cCol = pNode.cellIndex; 302 pNode=findParentNodeByName(pNode,"TR"); 303 if (pNode) { 304 cRow = pNode.rowIndex; 305 table=findParentNodeByName(pNode,"TABLE"); 306 } 307 } 308 tablecalc_crow=cRow; 309 tablecalc_ccol=cCol; 310 tablecalc_table=table; 311 312 var matchA=formula.match(/([a-z0-9_]+\.)?(r|c)\d+(r|c)\d+(\:(r|c)\d+(r|c)\d+)?(\,([a-z0-9]+\.)?(r|c)\d+(r|c)\d+(\:(r|c)\d+(r|c)\d+)?){0,99}/g); 313 if (matchA != null) { 314 for (var i = 0; i<matchA.length; i++) { 315 var members=new Array(); 316 317 var matchL=matchA[i].split(','); 318 for (var j=0;j<matchL.length;j++) { 319 var tmp_table=table; 320 var matchB=matchL[j].split('.',2); 321 if (matchB.length<2) { 322 matchB[1]=matchB[0]; 323 } else { 324 if (typeof tablecalc_labels[matchB[0]] != 'undefined') { 325 tmp_table=tablecalc_labels[matchB[0]]; 326 } else { 327 if (final) { 328 tmp_table="notable"; 329 } else { 330 tablecalcAddDefer(divID,oFormula); 331 //tablecalcProcessDefer(); 332 return false; 333 } 334 } 335 } 336 if (tmp_table!="notable") { 337 var matchC=matchB[1].split(':',2); 338 if (matchC.length<2) { 339 matchC[1]=matchC[0]; 340 } 341 from=tablecalcXY(matchC[0]); 342 to=tablecalcXY(matchC[1]); 343 if (from[0]>to[0]) { 344 var tmp=to[0]; 345 to[0]=from[0]; 346 from[0]=tmp; 347 } 348 if (from[1]>to[1]) { 349 var tmp=to[1]; 350 to[1]=from[1]; 351 from[1]=tmp; 352 } 353 for (var fx=from[0];fx<=to[0];fx++) { 354 for (var fy=from[1];fy<=to[1];fy++) { 355 if ((fx==cCol) && (fy==cRow) && (tmp_table==table)) {continue;} 356 var tmp=tablecalcVal(fx,fy,tmp_table); 357 tablecalc_log("member["+fx+","+fy+"]="+tmp); 358 if ( (tmp == 'notnum') || (tmp == 'notset') ) { 359 tablecalcAddDefer(divID,oFormula); 360 if (!final) { 361 //tablecalcProcessDefer(); 362 return false; 363 } else { 364 members[members.length]=tablecalcVal(fx,fy,tmp_table,1); 365 } 366 } else {/*if (tmp!='notset') {*/ 367 members[members.length]=tmp; 368 } 369 } 370 } 371 } else { 372 tablecalc_log("table not found by label: "+matchB[0]); 373 } 374 } 375 var result=""; 376 if (members.length>0) { 377 if (members.length==1) { 378 var tmp=parseFloat(members[0]); 379 if (isNaN(tmp)) { 380 result="'"+members[0]+"'"; 381 } else { 382 result=members[0]*1; 383 } 384 } else { 385 result="new Array("; 386 for (var k=0;k<members.length;k++) { 387 if (k) { 388 result+=','; 389 } 390 result+="'"+members[k]+"'"; 391 } 392 result+=")" 393 } 394 } 395 formula=formula.replace(matchA[i],result); 396 } 397 } 398 399 formula=formula.replace(/;/g,","); 400 tablecalc_log("Evaluating: "+formula); 401 var rc; 402 try { 403 eval('calcresult = '+formula); 404 //if (!isNaN(calcresult)) { 405 tablecalc_log("Got result: "+calcresult+" ("+(typeof calcresult)+")"); 406 if ((typeof calcresult === "number") && (isNaN(calcresult))) { 407 tablecalcAddDefer(divID,oFormula); 408 rc=false; 409 } else { 410 div.innerHTML=calcresult; 411 rc=true; 412 } 413 } catch (e) { 414 rc=false; 415 tablecalc_log("Exception: "+e); 416 tablecalcAddDefer(divID,oFormula); 417 } 418 if (!final) { 419 tablecalcProcessDefer(); 420 } 421 return rc; 422} 423 424function tablecalcAddDefer(divID,formula) { 425 if (typeof tablecalc_defer[divID] == 'undefined') { 426 tablecalc_defer[divID]=formula; 427 tablecalc_log("Added defer: "+divID+"=>"+tablecalc_defer[divID]); 428 } 429} 430 431function tablecalcProcessDefer() { 432 var exit; 433 var steps=0; 434 do { 435 steps++; 436 exit=1; 437 for (var divID in tablecalc_defer) { 438 if (tablecalc_defer[divID].length) { 439 tablecalc_log("calling defer: "+divID+"=>"+tablecalc_defer[divID]); 440 var tmp=tablecalc_defer[divID]; 441 tablecalc_defer[divID]=""; 442 if (!tablecalc(divID,tmp,0)) { 443 tablecalc_defer[divID]=tmp; 444 } else { 445 exit=0; 446 } 447 } 448 } 449 } while ( (!exit) && (steps<99) ); 450 if (steps>=99) { 451 tablecalc_log("max steps reached!"); 452 } 453} 454 455function tablecalc_final() { 456 tablecalc_log("entering final"); 457 tablecalc_isfinal=1; 458 tablecalcProcessDefer(); 459 for (var divID in tablecalc_defer) { 460 if (tablecalc_defer[divID].length) { 461 tablecalc_log("calling final defer: "+divID+"=>"+tablecalc_defer[divID]); 462 if (tablecalc(divID,tablecalc_defer[divID],1,99)) { 463 tablecalc_defer[divID]=""; 464 } 465 } 466 } 467} 468 469function findParentNodeByName(pNode,st) { 470 while ( ( pNode.nodeName!=st ) && (pNode.parentNode != null) ) { 471 pNode=pNode.parentNode; 472 } 473 if (pNode.nodeName!=st) { 474 pNode=0; 475 } 476 return pNode; 477} 478 479 480function stripHTML(oldString) { 481 return oldString.replace(/<[^>]*>/g, ""); 482} 483