1 /* ***************************************************************************** 2 * ITSV GmbH 3 * 4 * Software Operations 5 * Zentrale Daten und Services 6 * 7 * Product: CCDB 8 * Module: functions 9 * Description: This module contains business functions for CCDB 10 * History: 11 * Date | Author | Version | DESCRIPTION 12 * -----------+-----------+------------+------------------------------------------- 13 * 04.11.2015 | WSC | 0.6 | extracted from ccdb.js 14 * | | | 15 * | | | 16 * | | | 17 * | | | 18 * | | | 19 */ 20 21 var util = require('util'); 22 var fs = require('fs'); 23 var aux = require('./auxiliary.js'); 24 var flow = require('flow'); 25 var db = require('./db.js'); 26 var XLSX = require('xlsx'); 27 // var oracledb = require("oracledb"); 28 var oracledb = null; 29 30 var logger = null; 31 var mastername = "functions.mastername not set"; 32 var prefs = {"default" : "functions.prefs not set"} 33 34 /* ***************************************************************************** 35 * 36 * THIS IS A CLONE FROM THE ORIGINAL IN .AUXILIARY 37 * in order to be able to access db/... from the executed expressions 38 * 39 * FUNCTION: f_process_context_expression 40 * INPUT: that - query execution context 41 * expattnam - name of extension hook to be invoked 42 * resattnam - OPTIONAL: name of attribute of <that> to which the 43 * result of the expression is stored (if any) 44 * OUTPUT: that - possible changes by executed expression 45 * DESCRIPTION: processes query extension hook javascript expression 46 * it is assumed that <that> has an attribute named <expattnam> 47 * this attribute shall contain a javascript-expression 48 * this expression is executed 49 * if the expression produces a return value, this value 50 * is logged but not further used or kept 51 * before execution (with javascript eval()-function), placeholders 52 * delimited by {{..}} are replaced (using populate()-function) 53 * If the expression returns a result and <resattnam> is specified, 54 * the result is stored to the attribute <resattnam> of <that> 55 */ 56 57 function f_process_context_expression(that,procparname,resattnam) { 58 var procexpr = ''; 59 if (that[procparname]) { 60 procexpr = aux.populate(''+that[procparname],that); 61 } else if ((that.query) && (that.query[procparname])) { 62 procexpr = aux.populate(''+that.query[procparname],that.query,that); 63 } else { 64 procexpr = ''; 65 // logger.debug('f_process_context_expression: no JS-expression '+procparname+' defined'); 66 return; 67 } 68 // logger.debug("F_PROCESS_CONTEXT_EXPRESSION.EXP["+procparname+"(populated)]=\""+cutString(procexpr)+"\""); 69 var procres; 70 try { 71 procres = eval(procexpr); 72 } 73 catch (e) { 74 var nerr = new Error("Error in F_PROCESS_CONTEXT_EXPRESSION.EXP["+procparname+"(populated)]=\""+procexpr+"\": "+e.message); 75 logger.error(nerr.message); 76 procres = {error: nerr, expression: procexpr}; 77 } 78 if (resattnam) { 79 try { 80 that[resattnam] = procres; 81 } catch (e) { 82 logger.error("F_PROCESS_CONTEXT_EXPRESSION: Error assigning result to attribute "+resattnam+": "+e.message); 83 logPretty(that,"Context: "); 84 } 85 } 86 /* 87 if (procres) { 88 logger.debug("F_PROCESS_CONTEXT_EXPRESSION.RESULT[\""+procparname+"\"]: ",procres); 89 } else { 90 logger.debug("F_PROCESS_CONTEXT_EXPRESSION.RESULT[\""+procparname+"\"] is falsy"); 91 } 92 */ 93 // logger.debug("F_PROCESS_CONTEXT_EXPRESSION.END.RES: ",procres); 94 } 95 96 97 /* ***************************************************************************** 98 * FUNCTION: sessionHasRight 99 * INPUT: sess - session object 100 * rname - right name 101 * RESULT: true if session has the right <rname> 102 * DESCRIPTION: checks if the rightslist sub-object of the session object 103 * contains an entry with name <rname> 104 */ 105 function sessionHasRight(sess, rname) { 106 if ((sess) && (sess.rightslist)) { 107 // logger.debug("sessionHasRight.right=\""+rname+"\".rightslist=\""+sess.rightslist.join(",")+"\""); 108 for (var i = 0; i<sess.rightslist.length; i++) { 109 if (rname==sess.rightslist[i]) { 110 // logger.debug("sessionHasRight.TRUE"); 111 return true; 112 } 113 } 114 } 115 // logger.debug("sessionHasRight.FALSE"); 116 return false; 117 } 118 119 /* ***************************************************************************** 120 * FLOW: check_authorization 121 * INPUT: sess - session object to check authorization credentials 122 * actname - name of the action to be performed 123 * ctx - context objects further specifying action 124 * rfunc - callback function to be called upon completion of check 125 * PARAMETERS: 126 * err - error object, null if everything OK 127 * ares - result object optionally detailing credentials 128 * DESCRIPTION: checks if the current session (sess) is authorized to perform 129 * the action named in <actname>, further detailed by <ctx> 130 * delivers <err> if session is not allowed to perform action 131 * If the session has attribute allauthoverride="YES", then 132 * authorization is granted immediately 133 * ( this is needed by the local internal admin-session ) 134 * 135 */ 136 var check_authorization = flow.define( 137 // step 1: check session and initiate reading of authorization information 138 function(sess,actname,ctx,rfunc) { 139 this.context = ctx; 140 this.callback = rfunc; 141 this.session = sess; 142 this.actname = actname; 143 if (this.session && this.session.allauthoverride && this.session.allauthoverride=="YES") { 144 this.callback(null,null); 145 return; 146 } 147 if (!(this.session)) { 148 var nerr = new Error("Cannot determine authorization: No Session available"); 149 logger.error(nerr.message); 150 this.callback(nerr); 151 return; 152 } 153 if (!(this.session.rightslist)) { 154 var nerr = new Error("Berechtigung fehlgeschlagen: Sitzung hat keine CCDB-Rechte oder ist nicht angemeldet (<a href=\"/login\">Anmelden</a>)"); 155 logger.error(nerr.message); 156 this.callback(nerr); 157 return; 158 } 159 // logger.debug("CHECK_AUTHORIZATION.SESS_RIGHTSLIST=\""+this.session.rightslist.join(",")+"\""); 160 this.adbr = new db.Request({ info: "GETAUTHINFO.ACTNAME="+this.actname, 161 reqid: 'RQID_GETAUTHINFO', 162 sql: "SELECT RWNAME,RWVALUE,RWDESC from CCRW where RWTYP='ACTAUTHRULE' and RWPARENT='AUTH' and RWNAME IN (:ACTNAME,'GENERAL')", 163 params: [this.actname]}); 164 db.select(this.adbr,this); 165 }, 166 // step 2: process authorization expressions 167 function (err,ares) { 168 if (err) { 169 var nerr = new Error("Error getting authorization rules for actname=\""+this.actname+"\": "+err.message); 170 logger.error(nerr.message); 171 logger.error(err); 172 this.callback(nerr,ares); 173 return; 174 } 175 this.specific_authrule = "sessionHasRight(this.session,\""+this.actname+"\")"; 176 this.general_authrule = "sessionHasRight(this.session,\"allactions\")"; 177 if (ares.rows) { 178 for (var rn=0; rn<ares.rows.length; rn++) { 179 if ((ares.rows[rn][0]=='GENERAL') && (ares.rows[rn][1])) this.general_authrule = ares.rows[rn][1]; 180 if ((ares.rows[rn][0]==this.actname) && (ares.rows[rn][1])) this.specific_authrule = ares.rows[rn][1]; 181 } 182 } 183 try { 184 // logger.debug("SPECIFIC_AUTHRULE=\""+this.specific_authrule+"\""); 185 this.authorized = eval(this.specific_authrule); 186 // logger.debug("SPECIFIC_AUTHRULE_RESULT="+this.authorized); 187 } 188 catch (e) { 189 var nerr = new Error("Error processing specific authorization rule \""+this.specific_authrule+": "+e.message); 190 logger.error(nerr.message); 191 logger.error(e); 192 this.callback(nerr); 193 return; 194 } 195 if (!(this.authorized)) { 196 try { 197 // logger.debug("GENERAL_AUTHRULE=\""+this.general_authrule+"\""); 198 this.authorized = eval(this.general_authrule); 199 // logger.debug("GENERAL_AUTHRULE_RESULT="+this.authorized); 200 } 201 catch (e) { 202 var nerr = new Error("Error processing general authorization rule \""+this.general_authrule+": "+e.message); 203 logger.error(nerr.message); 204 this.callback(nerr); 205 return; 206 } 207 } 208 if (!this.authorized) { 209 var nerr = new Error("Session not authorized to perform action \""+this.actname+"\""); 210 this.callback(nerr); 211 return; 212 } 213 // if we came until here, authorization is granted 214 this.callback(undefined); 215 } 216 ); 217 218 /* ***************************************************************************** 219 * FLOW: save_to_mfile 220 * INPUT: mfiletype - type for managed file to create 221 * fdata - file data to write 222 * options - option object 223 * filename - filename, DEFAULT: <mfileid>.<extension> 224 * extension - file extension, DEFAULT: 'dat' 225 * donotwrite - if true, does not write to file only creates MFILE entry and name,path 226 * CALLBACK: rfunc - called upon completion 227 * PARAMETERS: 228 * err - error object 229 * result - result object: 230 * status - localized user-readyble status text 231 * mfileid - managed file id created 232 * mfilename - managed file name 233 * mfilepath - managed file physical path 234 * mfileattribs - managed file attributes 235 * DESCRIPTION: creates a new managed file of type <mfiletype> and 236 * writes <fdata> to it. All <options> are written to MFILEATTRIBS 237 * in the MFILE entry for the managed file 238 */ 239 var save_to_mfile = flow.define( 240 // step 1: create and write file 241 function(mfiletype,fdata,options,rfunc) { 242 this.mfiletype = mfiletype; 243 this.fdata = fdata; 244 this.options = options; 245 this.callback = rfunc; 246 this.uploadDir = __dirname + "/uploads/"; 247 this.pathprefix = "uploads/"; 248 aux.default_param(this.options,'extension','dat'); 249 this.mfileid = mfiletype+'_'+aux.nowstring(); 250 aux.default_param(this.options,'filename',this.mfileid); 251 this.mfilename = this.options.filename+'.'+this.options.extension; 252 this.mfilepath = this.uploadDir+this.mfileid+'.'+this.options.extension; 253 this.mfilerelpath = this.pathprefix+this.mfileid+'.'+this.options.extension; 254 if (this.options.donotwrite) { 255 this(); // proceed to next step, not written 256 } else { 257 fs.writeFile(this.mfilepath,this.fdata,this.options,this); // write physical file 258 } 259 }, 260 // step 2: create MFILE database entry 261 function(err) { 262 if (err) { 263 aux.error_callback(this.callback,err,'Error saving managed file "'+this.mfileid+'"'); 264 265 return; 266 } 267 delete this.options.donotwrite; // remove control options before writing to MFILE entry 268 delete this.options.filename; 269 delete this.options.extension; 270 this.mfileattribs = aux.objectIsEmpty(this.options)?"":JSON.stringify(this.options); 271 this.dbr = new db.Request({ info: "Creating managed file "+this.mfileid, 272 reqid: 'RQID_CREMFILE', 273 sql: "INSERT INTO ccmfiles(MFILEID,MFILETYPE,MFILENAME,MFILEPATH,MFILEATTRIBS) values(:mfileid,:mfiletype,:mfilename,:mfilepath,:mfileattribs)", 274 params: [this.mfileid,this.mfiletype,this.mfilename,this.mfilerelpath,this.mfileattribs]}); 275 db.select(this.dbr,this); 276 }, 277 // step 3: processing complete 278 function(err,result) { 279 if (err) { 280 aux.error_callback(this.callback,err,'Error creating managed file entry for MFILEID="'+this.mfileid,result+'"'); 281 return; 282 } 283 result = {resulttype: 'object', resultobject: {}}; 284 result.resultobject.status = 'Managed File '+this.mfileid+' erzeugt.'; 285 result.resultobject.mfileid=this.mfileid; 286 result.resultobject.mfilename = this.mfilename; 287 result.resultobject.mfilepath = this.mfilepath; 288 result.resultobject.mfileattribs = this.mfileattribs; 289 this.callback(null,result); 290 }); 291 // end of flow save_to_mfile 292 293 294 /* dummy workbook constructor */ 295 function Workbook() { 296 if(!(this instanceof Workbook)) return new Workbook(); 297 this.SheetNames = new Array(); 298 this.Sheets = {}; 299 } 300 301 /* TODO: date1904 logic */ 302 function datenum(v, date1904) { 303 if(date1904) v+=1462; 304 var epoch = Date.parse(v); 305 return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000); 306 } 307 308 /* ***************************************************************************** 309 * FUNCTION: sheet_from_array_of_arrays 310 * INPUT: data - array of arrays (2D-array) to create XLS-sheet from 311 * assumption is that data be an Array of rows 312 * each row consisting of an array of column-cells 313 * opts - option object controlling sheet creation 314 * RETURNS: ws - a worksheet object for XLSX: 315 * has one attribute for each non-empty cell 316 * these cell-attributes are named in A1-Excel-nomenclature 317 * the name-encoding encapsulated in the XLSX-library 318 * each cell-attribute is an XLSX-cell-object and has attributes: 319 * t - the type: 'n' / 's' / 'b' 320 * v - the value 321 * z - only set if date cell 322 * plus 2 special attributes: 323 * '!ref' - the sheet range encoded 324 * '!range' - the sheet range as structured object 325 * DESCRIPTION: creates a worksheet object for XLSX 326 */ 327 function sheet_from_array_of_arrays(data, opts) { 328 var ws = {}; 329 var range = {s: {c:10000000, r:10000000}, e: {c:0, r:0 }}; // create a impossible "negative-size" (start > end) range object which is expanded afterwards on demand 330 for(var R = 0; R != data.length; ++R) { 331 for(var C = 0; C != data[R].length; ++C) { 332 if(range.s.r > R) range.s.r = R; // expand range to the minimum and maximum of index values 333 if(range.s.c > C) range.s.c = C; 334 if(range.e.r < R) range.e.r = R; 335 if(range.e.c < C) range.e.c = C; 336 var cell = {v: data[R][C] }; 337 if(cell.v == null) continue; 338 var cell_ref = XLSX.utils.encode_cell({c:C,r:R}); 339 /* TEST: proper cell types and value handling */ 340 if(typeof cell.v === 'number') cell.t = 'n'; 341 else if(typeof cell.v === 'boolean') cell.t = 'b'; 342 else if(cell.v instanceof Date) { 343 cell.t = 'n'; cell.z = XLSX.SSF._table[14]; 344 cell.v = datenum(cell.v); 345 } 346 else cell.t = 's'; 347 ws[cell_ref] = cell; 348 } 349 } 350 /* TEST: proper range */ 351 if (range.s.c < 10000000) { 352 ws['!ref'] = XLSX.utils.encode_range(range); 353 ws['!range'] = range; 354 } 355 return ws; 356 } 357 358 /* ***************************************************************************** 359 * FLOW: save_db_xls 360 * INPUT: query - query object with parameters 361 * tablename name of table to be saved, if not given, saves complete database 362 * rfunc - callback upon completion 363 * PARAMETERS: 364 * err - error object, NULL if OK 365 * result - result object from save_to_mfile(): 366 * status - localized user-readable status text 367 * mfileid - managed file id created 368 * mfilename - managed file name 369 * mfilepath - managed file physical path 370 * mfileattribs - managed file attributes 371 * mfileid - managed file id 372 * pfunc - OPTIONAL: progress reporting callback 373 * 374 */ 375 376 377 var xlpcd = 0; 378 379 function save_db_xls_progstat(that,pdat) { 380 if (that.pcallback) { 381 if (!that.progstat) { 382 that.progstat = {action: 'save_db_xls', stage: 'unknown'}; 383 } 384 var pstat = that.progstat; 385 if (pdat) { 386 pstat.subaction = pdat; 387 } 388 that.pcallback(pstat); 389 } 390 } 391 392 var save_db_xls = flow.define( 393 // step 1: determine list of tables 394 function(query,rfunc,pfunc) { 395 this.flowname = "save_db_xls"; 396 this.query = query; 397 this.callback = rfunc; 398 this.pcallback = pfunc; 399 this.progstat = {action: 'save_db_xls', stage: 'initializing'}; 400 this.result = {resulttype: 'object', reporttype: "save_db_xls_report"}; 401 this.curdbtype = db.getCurrentConfigDBtype(); 402 aux.default_param(this.query,'tabnamsel',"(TABLE_NAME like 'CC%' or TABLE_NAME like 'BO%' or TABLE_NAME like 'CA%')"); 403 aux.default_param(this.query,'tabseloradbwhere'," where " + this.query.tabnamsel); 404 aux.default_param(this.query,'tabselmysqlwhere'," where TABLE_SCHEMA='CCDB' and TABLE_TYPE='BASE TABLE' and " + this.query.tabnamsel); 405 aux.default_param(this.query,'deftabselsql',"SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES and " + this.query.tabnamsel); 406 if (this.query.tablename) { // name of table given, only save simgle table 407 this.query.singletable = true; 408 this.mftype = "ccdbtable"; 409 this.mfbasename = this.query.tablename; 410 this.mfext = "xlsx"; 411 logger.debug("SAVE_DB_XLS.SINGLE_TABLE=\""+this.query.tablename+"\""); 412 switch (this.curdbtype) { 413 case 'oracledb': 414 this.query.deftabselsql = "select TABLE_NAME from USER_TABLES where TABLE_NAME='"+this.query.tablename+"' order by TABLE_NAME asc"; 415 break; 416 case 'mysql': 417 this.query.deftabselsql = "select DISTINCT TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME='"+this.query.tablename+"' order by TABLE_NAME asc"; 418 break; 419 } 420 } else { // no table name given, save complete CCDB database 421 logger.debug("SAVE_DB_XLS.MULTI_TABLE.SELECTOR=\""+this.query.tabnamsel+"\""); 422 this.query.singletable = false; 423 this.mftype = "dbdump"; 424 this.mfbasename = "ccdbsave"; 425 this.mfext = "xlsx"; 426 switch (this.curdbtype) { 427 case 'oracledb': 428 this.query.deftabselsql = "select TABLE_NAME from USER_TABLES " + this.query.tabseloradbwhere; 429 break; 430 case 'mysql': 431 this.query.deftabselsql = "select DISTINCT TABLE_NAME from INFORMATION_SCHEMA.TABLES " + this.query.tabselmysqlwhere; 432 } 433 } 434 aux.default_param(this.query,'tabselsql',this.query.deftabselsql); 435 this.dbr = new db.Request({ info: 'get list of CCDB tables for saving database', 436 reqid: 'RQID_SAVLSTTAB', 437 sql: this.query.tabselsql, 438 options: {maxrowstoget: 1000000000, notranslate: true} 439 }); 440 this.progstat.stage = 'getting list of tables to dump: "'+this.query.tabsql+'"'; 441 db.select(this.dbr,this, 442 function(pdat) { 443 save_db_xls_progstat(this,pdat); 444 }.bind(this)); 445 }, 446 // step 2: set up saving loop 447 function(err,result) { 448 if (err) { 449 aux.error_callback(this.callback,err,'Error getting list of CCDB tables for save',result); 450 return; 451 } 452 this.listresult = result; // listresult has one row in .rows for each table to be dumped to the workbook 453 this.curtab = -1; // index for current processed table, will be bumped up to 0 in loop 454 this.sdbr = new db.Request({ info: 'dump table {{tablename}} from CCDB', 455 reqid: 'RQID_SAVDMPTAB', 456 sql: 'select * from {{tablename}}', 457 options: {maxrowstoget: 1000000000, notranslate: true} 458 }); 459 this.tsql = this.sdbr.sql; // keep the SQL template for subsequent resolution 460 this.tinfo = this.sdbr.info; // same with info template 461 this.xlswb = new Workbook(); // open new empty excel workbook 462 this.tablename = 'NO_TABLE'; // setup to special value to be able to detect errors 463 this(); // jump into loop, NULL result will lead to initiating reading in next step 464 }, 465 // step 3: save the next table or, when all saved, create the workbook and MFILE entry 466 function(err,result) { 467 if (err) { 468 aux.error_callback(this.callback,err,'Error saving CCDB table # '+this.curtab+' - '+this.listresult.rows[this.curtab][0],result); 469 return; 470 } 471 if (result) { // we came from dumping the table, table content is in result.rows 472 if ((result.rowsgot!=result.rowsinresult)) { // did we get too few rows? 473 aux.error_callback(this.callback,null,"Did not get all rows from table \""+this.tablename+"\": Selected: "+result.rowsinresult+", Delivered: "+result.rowsgot,result); 474 return; 475 } 476 var tdata = new Array(); // buffer for collecting all table rows 477 var hdata = new Array(); // buffer for collecting header fields == column names 478 for (var hi=0; hi<result.metaData.length; hi++) { // make header row with column names 479 hdata.push(result.metaData[hi].name); 480 } 481 tdata.push(hdata); // make header row 1st row in worksheet data array 482 for (var ri = 0; ri<result.rows.length; ri++) { // join header row with data rows 483 tdata.push(result.rows[ri]); 484 } 485 this.ws = sheet_from_array_of_arrays(tdata); // make a worksheet 486 this.xlswb.SheetNames.push(this.tablename); 487 this.xlswb.Sheets[this.tablename] = this.ws; // put worksheet in workbook by its name, which is the name of the table 488 } 489 this.curtab++; // move on to next table in list 490 if (this.curtab>=this.listresult.rows.length) { // we are ready, no more tables to save .. 491 this(); // .. so proceed to next state 492 } else { 493 this.tablename = this.listresult.rows[this.curtab][0]; 494 this.progstat.stage = 'reading table '+(this.curtab+1)+' of '+this.listresult.rows.length+' - '+this.tablename; 495 logger.debug("SAVE_DB_XLS: "+this.progstat.stage); 496 this.sdbr.info = aux.populate(this.tinfo,this.query,this); 497 if (this.query['SQL_'+this.tablename+'_'+this.curdbtype]) { // did the caller specify a specific SQL for this table in the current DB type ? 498 this.sdbr.sql = aux.populate(this.query['SQL_'+this.tablename+'_'+this.curdbtype],this.query); // yes, use it 499 } else { // no DB-specific SQL 500 if (this.query['SQL_'+this.tablename]) { // did the caller specify a specific SQL for this table? 501 this.sdbr.sql = aux.populate(this.query['SQL_'+this.tablename],this.query); // yes, use this 502 } else { 503 this.sdbr.sql = aux.populate(this.tsql,this.query,this); // no, use the default table dump SQL to produce the current dump SQL statement 504 } 505 } 506 this.REWIND(); // stay at this flow step 507 db.select(this.sdbr,this, // dump the next table and repeat 508 function(pdat) { 509 save_db_xls_progstat(this,pdat); 510 }.bind(this)); 511 } 512 }, 513 // step 4: dump table definitions 514 function() { 515 if (this.query.singletable) { // if only a single table was requested to be dumped .. 516 logger.debug("SAVE_DB_XLS.SINGLE.SKIP_TABLEDEF"); 517 this(null,null); // .. skip this step 518 return; 519 } 520 aux.default_param(this.query,'tablistsql',this.query.tabselsql); 521 this.sdbr = new db.Request({ info: "dump table definitions from CCDB", 522 reqid: "RQID_SAVDMPTDEF", 523 sql: this.query.tablistsql, 524 options: {maxrowstoget: 1000000000, notranslate: true} 525 }); 526 this.progstat.stage = 'dumping table definitions'; 527 logger.debug("SAVE_DB_XLS: "+this.progstat.stage); 528 db.select(this.sdbr,this, // execute dump 529 function(pdat) { 530 save_db_xls_progstat(this,pdat); 531 }.bind(this)); 532 }, 533 // step 5: process table definitions, dump column definitions 534 function(err,result) { 535 if (err) { 536 aux.error_callback(this.callback,err,"Error dumping table definitions for saving database",result); 537 return; 538 } 539 if (this.query.singletable) { // if only single table requested .. 540 logger.debug("SAVE_DB_XLS.SINGLE_SKIP_COL_DEF"); 541 this(null,null); // .. skip this step 542 return; 543 } 544 if (result) { // we came from dumping the table, table content is in result.rows 545 if ((result.rowsgot!=result.rowsinresult)) { // did we get too few rows? 546 aux.error_callback(this.callback,null,"Did not get all rows from table definitions: Selected: "+result.rowsinresult+", Delivered: "+result.rowsgot,result); 547 return; 548 } 549 var tdata = new Array(); 550 var hdata = new Array(); 551 for (var hi=0; hi<result.metaData.length; hi++) { // make header row with column names 552 hdata.push(result.metaData[hi].name); 553 } 554 tdata.push(hdata); // make header row 1st row in worksheet data array 555 for (var ri = 0; ri<result.rows.length; ri++) { // join header row with data rows 556 tdata.push(result.rows[ri]); 557 } 558 this.ws = sheet_from_array_of_arrays(tdata); // make a worksheet 559 this.xlswb.SheetNames.push("TABLES"); 560 this.xlswb.Sheets["TABLES"] = this.ws; // put worksheet in workbook 561 } 562 this.query.colselsql = "select TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH from ALL_TAB_COLUMNS where " + this.query.tabnamsel; 563 switch (this.curdbtype) { 564 case 'oracledb': 565 this.query.colselsql = "select TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH from ALL_TAB_COLUMNS where " + this.query.tabnamsel; 566 break; 567 case 'mysql': 568 this.query.colselsql = "select TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH as DATA_LENGTH from INFORMATION_SCHEMA.COLUMNS where " + this.query.tabnamsel; 569 break; 570 } 571 this.sdbr = new db.Request({ info: "dump column definitions from CCDB", 572 reqid: "RQID_SAVDMPTDEF", 573 sql: this.query.colselsql, 574 options: {maxrowstoget: 1000000000, notranslate: true} 575 }); 576 this.progstat.stage = 'dumping column definitions'; 577 logger.debug("SAVE_DB_XLS: "+this.progstat.stage); 578 db.select(this.sdbr,this, // execute dump 579 function(pdat) { 580 save_db_xls_progstat(this,pdat); 581 }.bind(this)); 582 }, 583 // step 6: process column definitions, prepare dump view definitions - drop temporary table 584 function(err,result) { 585 if (err) { 586 aux.error_callback(this.callback,err,"Error dumping column definitions for saving database",result); 587 return; 588 } 589 if (this.query.singletable) { // if only a single table was requested to be dumped .. 590 logger.debug("SAVE_DB_XLS.SINGLE_SKIP_VIEWDEF"); 591 this(null,null); // .. skip this step 592 return; 593 } 594 if (result) { // we came from dumping the table, table content is in result.rows 595 if ((result.rowsgot!=result.rowsinresult)) { // did we get too few rows? 596 aux.error_callback(this.callback,null,"Did not get all rows from column definitions: Selected: "+result.rowsinresult+", Delivered: "+result.rowsgot,result); 597 return; 598 } 599 var tdata = new Array(); 600 var hdata = new Array(); 601 for (var hi=0; hi<result.metaData.length; hi++) { // make header row with column names 602 hdata.push(result.metaData[hi].name); 603 } 604 tdata.push(hdata); // make header row 1st row in worksheet data array 605 for (var ri = 0; ri<result.rows.length; ri++) { // join header row with data rows 606 tdata.push(result.rows[ri]); 607 } 608 this.ws = sheet_from_array_of_arrays(tdata); // make a worksheet 609 this.xlswb.SheetNames.push("COLUMNS"); 610 this.xlswb.Sheets["COLUMNS"] = this.ws; // put worksheet in workbook 611 } 612 // now drop temp table 613 this.sdbr = new db.Request({ info: "prepare view definition copy - drop temp table", 614 reqid: "RQID_SAVDMPVDT", 615 sql: "DROP TABLE IF EXISTS CXTEMP", 616 options: {notranslate: true} 617 }); 618 this.progstat.stage = 'dumping column definitions'; 619 logger.debug("SAVE_DB_XLS: "+this.progstat.stage); 620 db.select(this.sdbr,this, // execute dump 621 function(pdat) { 622 save_db_xls_progstat(this,pdat); 623 }.bind(this)); 624 }, 625 // step 7: check drop of temp table - prepare dump view definitions - create temporary table 626 function(err,result) { 627 if (err) { 628 // log error but continue, as table may have not existed before 629 logger.debug("Result dropping temp table: "+err.message); 630 } 631 if (this.query.singletable) { // if only a single table was requested to be dumped .. 632 logger.debug("SAVE_DB_XLS.SINGLE_SKIP_VEWDUMP_CRETEMP"); 633 this(null,null); // .. skip this step 634 return; 635 } 636 // now create temp table with view definitions 637 this.query.viewdumpsql = "create table CXTEMP as select VIEW_NAME, TEXT_LENGTH, TO_LOB(TEXT) as TEXT from ALL_VIEWS where VIEW_NAME like 'CC%' or VIEW_NAME like 'BS%'" 638 switch (this.curdbtype) { 639 case 'oracledb': 640 this.query.viewdumpsql = "create table CXTEMP as select VIEW_NAME, TEXT_LENGTH, TO_LOB(TEXT) as TEXT from ALL_VIEWS where VIEW_NAME like 'CC%' or VIEW_NAME like 'BS%'" 641 break; 642 case 'mysql': 643 this.query.viewdumpsql = "create table CXTEMP as select TABLE_NAME as VIEW_NAME, VIEW_DEFINITION as TEXT from INFORMATION_SCHEMA.VIEWS " + 644 " where TABLE_SCHEMA='CCDB' and " + this.query.tabnamsel; 645 break; 646 } 647 this.sdbr = new db.Request({ info: "prepare dumping view definition - create temp table", 648 reqid: "RQID_SAVDMPVCT", 649 sql: this.query.viewdumpsql, 650 options: {maxrowstoget: 1000000000, notranslate: true} 651 }); 652 this.progstat.stage = 'prepare dumping view definitions - create temp table'; 653 logger.debug("SAVE_DB_XLS: "+this.progstat.stage); 654 db.select(this.sdbr,this, // execute dump 655 function(pdat) { 656 save_db_xls_progstat(this,pdat); 657 }.bind(this)); 658 }, 659 // step 8: check create temp table - read view definitions from temp table 660 function(err,result) { 661 if (err) { 662 aux.error_callback(this.callback,err,"Error creating temporary table for dumping view definitions for saving database",result); 663 return; 664 } 665 if (this.query.singletable) { // if only a single table was requested to be dumped .. 666 logger.debug("SAVE_DB_XLS.SINGLE.SKIP_CRETAB_VIEWDEFDUMP"); 667 this(null,null); // .. skip this step 668 return; 669 } 670 // now read view definitions from temp table 671 this.query.viewreadsql = "select VIEW_NAME, TEXT_LENGTH, to_char(substr(TEXT,1,3000)) as TEXT_1, to_char(substr(TEXT,3001,3000)) as TEXT_2, to_char(substr(TEXT,6001,3000)) as TEXT_3 " + 672 " from CXTEMP"; 673 switch (this.curdbtype) { 674 case 'oracledb': 675 this.query.viewreadsql = "select VIEW_NAME, TEXT_LENGTH, to_char(substr(TEXT,1,3000)) as TEXT_1, to_char(substr(TEXT,3001,3000)) as TEXT_2, to_char(substr(TEXT,6001,3000)) as TEXT_3 " + 676 " from CXTEMP"; 677 break; 678 case 'mysql': 679 this.query.viewreadsql = "select VIEW_NAME, LENGTH(TEXT) as TEXT_LENGTH, substr(TEXT,1,3000) as TEXT_1, substr(TEXT,3001,3000) as TEXT_2, " + 680 " substr(TEXT,6001,3000) as TEXT_3 from CXTEMP"; 681 break; 682 } 683 this.sdbr = new db.Request({ info: "reading view definitions from temp table", 684 reqid: "RQID_SAVDMPVRT", 685 sql: this.query.viewreadsql, 686 options: {maxrowstoget: 1000000000, resultSet: false, notranslate: true} 687 }); 688 this.progstat.stage = 'reading view definitions from temp table CXTEMP'; 689 logger.debug("SAVE_DB_XLS: "+this.progstat.stage); 690 db.select(this.sdbr,this, // execute read 691 function(pdat) { 692 save_db_xls_progstat(this,pdat); 693 }.bind(this)); 694 }, 695 // step 9: check read view dump result, process view definitions 696 function(err,result) { 697 if (err) { 698 aux.error_callback(this.callback,err,"Error reading view definitions from temp table for saving database",result); 699 return; 700 } 701 if (this.query.singletable) { // if only a single table was requested to be dumped .. 702 logger.debug("SAVE_DB_XLS.SINGLE.SKIP_PROC_VIEWDEFTEMPTAB"); 703 this(null,null); // .. skip this step 704 return; 705 } 706 if (result) { // we came from dumping view definitions, table content is in result.rows 707 if ((result.rowsgot!=result.rowsinresult)) { // did we get too few rows? 708 aux.error_callback(this.callback,null,"Did not get all rows from view definitions: Selected: "+result.rowsinresult+", Delivered: "+result.rowsgot,result); 709 return; 710 } 711 var vdlerrtxt = ''; 712 for (var di=0; di<result.rows.length; di++) { 713 if (result.rows[di][1]>result.rows[di][2].length) { // concatenate TEXT_1 and TEXT_2 and TEXT_3, as one column may only have 1000 chars in oracledb driver 714 result.rows[di][2] += result.rows[di][3] 715 } 716 if (result.rows[di][1]>result.rows[di][2].length) { 717 result.rows[di][2] += result.rows[di][4]; 718 } 719 if (result.rows[di][1]!=result.rows[di][2].length) { 720 vdlerrtxt += " Definition of view "+result.rows[di][0]+": shall have "+result.rows[di][1]+" chars, but has "+result.rows[di][2].length+"."; 721 } 722 } 723 if (vdlerrtxt) { 724 this.result.viewerrors = "Error(s) in view definition lengths: "+vdlerrtxt; 725 } 726 var tdata = new Array(); 727 var hdata = new Array(); 728 for (var hi=0; hi<result.metaData.length; hi++) { // make header row with column names 729 hdata.push(result.metaData[hi].name); 730 } 731 tdata.push(hdata); // make header row 1st row in worksheet data array 732 for (var ri = 0; ri<result.rows.length; ri++) { // join header row with data rows 733 tdata.push(result.rows[ri]); 734 } 735 this.ws = sheet_from_array_of_arrays(tdata); // make a worksheet 736 this.xlswb.SheetNames.push("VIEWS"); 737 this.xlswb.Sheets["VIEWS"] = this.ws; // put worksheet in workbook 738 } 739 // now drop temp table again 740 this.sdbr = new db.Request({ info: "prepare view definition copy - drop temp table again", 741 reqid: "RQID_SAVDMPVDTA", 742 sql: "DROP TABLE IF EXISTS CXTEMP", 743 options: {notranslate: true} 744 }); 745 this.progstat.stage = 'post-process view definitions - drop temp table again'; 746 logger.debug("SAVE_DB_XLS: "+this.progstat.stage); 747 db.select(this.sdbr,this, // execute drop temp table again 748 function(pdat) { 749 save_db_xls_progstat(this,pdat); 750 }.bind(this)); 751 }, 752 // step 10: check drop temp table again 753 function(err,result) { 754 if (err) { 755 aux.error_callback(this.callback,err,"Error dropping temp table CXTEMP again in dumping view definitions to save database",result); 756 return; 757 } 758 logger.debug("SAVE_DB_XLS.STEP10.GO_ON"); 759 this(); 760 }, 761 // step 11: save the workbook 762 function() { 763 this.mfatt = {filename: this.mfbasename, extension: this.mfext, donotwrite: true}; 764 if (this.query.singletable) { 765 this.mfatt.table = this.query.tablename; 766 } 767 save_to_mfile(this.mftype,null,this.mfatt,this); // creates the entry in the CCMFILES table, but does not write file 768 }, 769 // step 12: write physical file 770 function(err,result) { 771 if (err) { 772 aux.error_callback(this.callback,err,'Error creating MFILE entry for saving database',result); 773 return; 774 } 775 result.resultobject.preresult = this.result; 776 logger.debug("SAVE_DB_XLS.MFILEPATH=\""+result.resultobject.mfilepath+"\""); 777 try { 778 XLSX.writeFile(this.xlswb, result.resultobject.mfilepath); 779 } 780 catch (e) { 781 logger.error('Error writing physical file to save database:'); 782 logger.error(e.stack); 783 aux.error_callback(this.callback,e,'Error writing physical file to save database', result); 784 return; 785 } 786 // if we're here, everything has been successful 787 logger.debug("SAVE_DB_XLS.COMPLETED." + (this.query.singletable?"SINGLE_TABLE":"MULTIPLE_TABLE")); 788 logger.debug(result); 789 this.callback(null,result); 790 } 791 // end of flow save_db_xls 792 ); 793 794 /* ***************************************************************************** 795 * 796 * COMPONENT: generic RW-creator 797 * DESCRIPTION: creates multi-format RW-structures: 798 * a created RW-structure may consist from a combination of: 799 * 1) single record (ectype='single') entries. For a single record entry it can be specified: 800 * what to go into RWTYP, RWPARENT, RWNAME, RWVALUE, RWDESC fields 801 * this can be: 802 * ctyo='literal' a fixed literal value 803 * ctyp='qdatt' the content of a querydata field 804 * 2) multi-record (ectype='array') entities. For these it can be specified: 805 * a querydata (<arrayobject>) field, which must be an array, over which to iterate 806 * each row in this array will lead to one RW entry 807 * what to go into RWTYP, RWPARENT, RWNAME, RWVALUE, RWDESC fields 808 * this can be: 809 * ctyp='literal' a fixed literal value 810 * ctyp='qdaratt' the content of a field inside the respective <arrayobject>-row 811 * 812 * 813 */ 814 815 /* 816 * control data structure for generic RW-creator 817 * 818 * consists of one controlling object entry per CRERW-action: 819 * each of these objects has the following attributes 820 * cname: the short name of the action 821 * completiontext: the displayed result text when the action has completed successfully 822 * steps: an array of steps to be performed one after the other, starting from 0: 823 * each step has the following attributes: 824 * name: the short name of the step 825 * arrayobject: name of the attribute of <baseobject> used as array to take content from for ectype='array' 826 * baseobject: name of the GRW-context-attribute used as base object to take data content from 827 * ectype: the type of action performed in the step. Dependent on the action type, additional step attributes are used: 828 * 'chkpres': check the presence of the requested (RW-)entry to be created 829 * associated step attributes: 830 * baseobject: 831 * reqid: 832 * sql_<dbtype>, 833 * sql: the SQL statement to be executed for the presence check 834 * if there is already data present for the structure to be created, this query shall return some rows. In this case, 835 * the action will FAIL and be terminated 836 * bindvars_<dbtype>, 837 * bindvars: an array of bind-variable-definitions to pass to the SQL statement 838 * 'single': a single SQL statement adding data to the table CCRW 839 * associated step attributes: 840 * sql_<dbtype>, 841 * sql: the SQL statement to be executed for adding the data 842 * placeholders are replaced before the execution from <baseobject> an <querydata> 843 * pnames_<dbtype>, 844 * pnames: an array of strings naming the bind-variable-parameters to be passed to the SQL statement 845 * defaults to ['typcont','parcont','namcont','valcont','dsccont'] 846 * these are the names of step attributes containing field content control objects 847 * typcont: content control for the RWTYPE field 848 * parcont: content control for the RWPARENT field 849 * namcont: content control for the RWNAME field 850 * valcont: content control for the RWVALUE field 851 * dsccont: content control for the RWDESC field 852 * all these contents are objects with following attributes: 853 * ctyp: type of the content: 854 * 'literal': content is literal value taken from ccont 855 * 'qdatt: content is taken from context attribute <ccont> 856 * 'qdaratt': field <ccont> in currently processed row of current array-object 857 * ( content is taken from context attribute "arrayobject" at index "attindex", sub-attribute <ccont> ) 858 * 'resrowatt': field <ccont> in currently processed row of last result 859 * ( content is taken from context attribute "lastresult" at index "attindex", sub-attribute <ccont> ) 860 * ccont: content data or name of attribute (indirect data) to get content from 861 * 'array': like 'single', but inserting an array of data 862 * placeholders in <sql> or <sql_dbtype>> are replaced before the first execution from <baseobject> AND <querydata> 863 * 'fromresult': like 'array', but from the result of a previously executed query (a 'dbselect' step) 864 * 'dbselect': perform a database-query and store result in GRW-context attribute "lastresult" 865 */ 866 var crerw_control = 867 { "CREQRY": { 868 cname: "CREQRY", 869 completiontext: "Neue Aktion {{qryname}} angelegt, {{linesadded}} Einträge zum Regelwerk hinzugefügt", 870 steps: [ { name: 'qrychk', ectype: 'chkpres', reqid: 'RQID_CHKQRYPRES', 871 sql: "select * from CCRW where (RWTYP='DQUERY' and RWPARENT='QUERIES' and RWNAME=:qryname) OR (RWTYP='DQUERYPARAM' and RWPARENT=:qryname)", 872 sql_mysql: "select * from CCRW where (RWTYP='DQUERY' and RWPARENT='QUERIES' and RWNAME=?) OR (RWTYP='DQUERYPARAM' and RWPARENT=?)", 873 bindvars: [{name: 'qryname', qryatt: 'qryname'}], 874 bindvars_mysql: [{name: 'qryname', qryatt: 'qryname'},{name: 'qryname', qryatt: 'qryname'}] }, 875 { name: 'qryrec', ectype: 'single', baseobject: 'querydata', 876 typcont: { ctyp: 'literal', ccont: 'DQUERY'}, 877 parcont: { ctyp: 'literal', ccont: 'QUERIES'}, 878 namcont: { ctyp: 'qdatt', ccont: 'qryname'}, 879 valcont: { ctyp: 'qdatt', ccont: 'qrytext'}, 880 dsccont: { ctyp: 'qdatt', ccont: 'qrydesc'} }, 881 { name: 'qrygrp', ectype: 'single', baseobject: 'querydata', 882 typcont: { ctyp: 'literal', ccont: 'DQUERYPARAM'}, 883 parcont: { ctyp: 'qdatt', ccont: 'qryname'}, 884 namcont: { ctyp: 'literal', ccont: 'group'}, 885 valcont: { ctyp: 'qdatt', ccont: 'qrygrp'}, 886 dsccont: { ctyp: 'literal', ccont: ''} }, 887 { name: 'qryatt', ectype: 'array', baseobject: 'querydata', arrayobject: 'qryattribs', 888 typcont: { ctyp: 'literal', ccont: 'DQUERYPARAM'}, 889 parcont: { ctyp: 'qdatt', ccont: 'qryname'}, 890 namcont: { ctyp: 'qdaratt', ccont: 'qryattname'}, 891 valcont: { ctyp: 'qdaratt', ccont: 'qryattvalue'}, 892 dsccont: { ctyp: 'qdaratt', ccont: 'qryattdesc'} } ] }, 893 "CRETYP": { 894 cname: "CRETYP", 895 completiontext: "Neuer Typ {{typname}} angelegt, {{linesadded}} Einträge zum Regelwerk hinzugefügt", 896 steps: [ // step 0: check if type already present in CCRW 897 { name: 'typchk', ectype: 'chkpres', reqid: 'RQID_CHKTYPRES', baseobject: 'querydata', 898 sql: "select * from CCRW where (RWTYP='TYPE' and RWPARENT='TYPES' and RWNAME='{{typname}}') OR (RWTYP='TYPEATTRIB' and RWPARENT='{{typname}}')" }, 899 // step 1: create TYPE entry 900 { name: 'typrec', ectype: 'single', baseobject: 'querydata', 901 typcont: { ctyp: 'literal', ccont: 'TYPE'}, 902 parcont: { ctyp: 'literal', ccont: 'TYPES'}, 903 namcont: { ctyp: 'qdatt', ccont: 'typname'}, 904 valcont: { ctyp: 'qdatt', ccont: 'typtext'}, 905 dsccont: { ctyp: 'qdatt', ccont: 'typdesc'} }, 906 // step 2: create TYPEATTRIB entries 907 { name: 'typatt', ectype: 'array', baseobject: 'querydata', arrayobject: 'typattribs', 908 typcont: { ctyp: 'literal', ccont: 'TYPEATTRIB'}, 909 parcont: { ctyp: 'qdatt', ccont: 'typname'}, 910 namcont: { ctyp: 'qdaratt', ccont: 'typattname'}, 911 valcont: { ctyp: 'qdaratt', ccont: 'typattvalue'}, 912 dsccont: { ctyp: 'qdaratt', ccont: 'typattdesc'}} ] } 913 } 914 915 /* ***************************************************************************** 916 * FUNCTION: prepare_grw_param 917 * INPUT: cont - control object 918 * that - cregrw flow context 919 * RESULT: value for the RW parameter controlled by <cont> 920 * DESCRIPTION: prepares a RW parameter (e.g. one data field for a record to be written to RW 921 * dependent on the value of cont.ctyp: 922 * cont.ctyp='literal' - parameter shall have a literal value from cont.ccont 923 * cont.ctyp='qdatt' - parameter shall contain the value of the querydata-attribute <cont.ccont> 924 * the querydata-object is in that.baseobject 925 * cont.ctyp='qdaratt' - parameter shall contain the value of the array-object-attribute <cont.ccont> 926 * the array-object is in the attribute <that.arrayobject> 927 * the row-index in the array object is in <that.attindex> 928 * 929 */ 930 function prepare_grw_param(cont,that) { 931 switch (cont.ctyp) { 932 case 'literal': 933 return cont.ccont; 934 break; 935 case 'qdatt': 936 return that.baseobject[cont.ccont]; 937 break; 938 case 'qdaratt': 939 return that.arrayobject[that.attindex][cont.ccont]; 940 break; 941 case 'resrowatt': 942 return that.lastresult.rows[that.attindex][cont.ccont]; 943 default: 944 return 'Error: prepare_grw_param: illegal ctyp '+cont.ctyp; 945 } 946 } 947 948 function prepare_grw_bindvar(grwctx,qdata,bvco) { 949 if (bvco.qryatt && qdata[bvco.qryatt]) { 950 return qdata[bvco.qryatt]; 951 } else { 952 if (!bvco.qratt) { 953 logger.error("Error preparing GRW bind variable: bind-var control "+(bvco.name?"\""+bvco.name+"\"":"<no-name>")+"does not specify query attribute name"); 954 } else { 955 if (!qdata[bvco.qryatt]) { 956 logger.error("Error preparing GRW bind variable: query-data does not have required attribute \""+bvo.qryatt+"\""); 957 } 958 } 959 } 960 } 961 962 function prepare_grw_bindvars(grwctx,qdata,bvca) { 963 bvarray = new Array(); 964 if (bvca) { 965 for (var bvi=0; bvi<bvca.length; bvi++) { 966 bvarray.push(prepare_grw_bindvar(grwctx,qdata,bvca[bvi])); 967 } 968 } 969 return bvarray; 970 } 971 972 /* ***************************************************************************** 973 * FLOW: cregrw 974 * INPUT: dataname - the name of the action to be executed 975 * querydata - query object carrying the parameters for the query to be created 976 * rfunc - callback to be called upon completion 977 * err - [error] object in case of errors, otherwise null 978 * result - [result] object with information for the user 979 */ 980 var cregrw = flow.define( 981 // step 1: initiate GRW engine 982 function(dataname,querydata,rfunc) { 983 this.dataname = dataname; 984 this.querydata = querydata; 985 this.callback = rfunc; 986 logger.debug("CREGRW.BEGIN.DATANAME=\""+this.dataname+"\".QUERYDATA: ",querydata); 987 if (!crerw_control[this.dataname]) { 988 this.querydata.resulttype = 'object'; 989 aux.error_callback(this.callback,null,"No GRW configuration found for DATANAME=\""+this.dataname+"\"",querydata); 990 return; 991 } 992 this.control = crerw_control[this.dataname]; 993 this.step = 0; 994 this.linesadded = 0; 995 this.attindex = 0; 996 this.sqlparams = new Array(); // initialize SQL parameters 997 for (var i = 0; i<5; i++) { 998 this.sqlparams.push('***_NOVALUE_'+i+'_***'); 999 } 1000 this.dbr = new db.Request({ info: 'Add data for '+this.dataname, 1001 reqid: 'RQID_CREQRY', 1002 sql: "INSERT INTO CCRW(RWTYP,RWPARENT,RWNAME,RWVALUE,RWDESC) values(:RWTYP,:RWPARENT,:RWNAME,:RWVALUE,:RWDESC)", 1003 sql_mysql: "INSERT INTO CCRW(RWTYP,RWPARENT,RWNAME,RWVALUE,RWDESC) values(?,?,?,?,?)", 1004 params: this.sqlparams}); 1005 this.defsql = this.control.defsql || this.dbr.sql; 1006 this.defsql_mysql = this.control.defsql_mysql || this.dbr.sql_mysql; 1007 this.dbrc = new db.Request({ info: 'check if data for '+this.control.cname+'/'+this.querydata.qryname+' already there', 1008 reqid: 'RQID_CKQRYERR', 1009 sql: "SELECT 'NO_SQL_GIVEN_IN_CREGRW' as ERROR from DUAL", 1010 sql_mysql: "SELECT 'NO_SQL_GIVEN_IN_CREGRW' as ERROR", 1011 params: [] 1012 }); 1013 this.curstep = null; 1014 this(); 1015 }, 1016 // step 2: check previous result (if any) and initiate next step 1017 function(err,result) { 1018 if (err) { 1019 aux.error_callback(this.callback,err,"Error in CREGRW dataname=\""+this.dataname+"\", step="+this.step+"/"+this.attindex,result); 1020 return; 1021 } 1022 logger.debug("CREGRW.DATANAME=\""+this.dataname+"\".STEP="+this.step+"/"+this.attindex); 1023 if (result) { 1024 this.lastresult = result; 1025 logger.debug("CRERGW.STEPRESULT.STEP="+this.step+"/"+this.attindex); 1026 } 1027 if (this.curstep) { // we have initiated a step, check result 1028 switch (this.curstep.ectype) { 1029 case 'chkpres': // we ordered a check, this check shall fail if there are any rows in the result 1030 if (result.rows.length>0) { 1031 aux.error_callback(this.callback,null,"Error: data already there",result); 1032 return; 1033 } 1034 this.step++; 1035 this.attindex = 0; 1036 break; 1037 case 'dbselect': // a DB-read transaction is complete, move on to next step 1038 this.step++; 1039 this.attindex = 0; 1040 break; 1041 case 'single': // a single DB transaction is complete, move on to next step 1042 this.linesadded++; 1043 this.step++; 1044 this.attindex = 0; 1045 break; 1046 case 'array': // one of a multiple DB transaction from arrayobject is complete, check if more entries in array 1047 case 'fromresult': // one of a multiple DB transaction from lastresult is complete, check if more entries in result 1048 this.linesadded++; 1049 this.attindex++; 1050 var len = (this.curstep.ectype=='array')?this.arrayobject.length:this.lastresult.rows.length; 1051 if (this.attindex>(len-1)) { // no more rows, next step 1052 this.step++ 1053 this.attindex = 0; 1054 } 1055 break; 1056 default: 1057 aux.error_callback(this.callback,null,"Illegal ectype "+this.curstep.ectype,result); 1058 return; 1059 } 1060 } 1061 if (this.step>=this.control.steps.length) { 1062 this.callback(null,{resulttype: 'string', body: aux.populate(this.control.completiontext,this.querydata,this)}); 1063 return; 1064 } 1065 this.curstep = this.control.steps[this.step]; 1066 // logger.debug("CREGRW.CURSTEP:\n"+JSON.stringify(this.curstep,null,2)); 1067 switch (this.curstep.ectype) { 1068 case 'chkpres': // initiate a presence check 1069 this.baseobject = this[this.curstep.baseobject]; 1070 this.dbrc.reqid = this.curstep.reqid || "RQID_CHKQRYPRES_DEF"; 1071 this.dbrc.sql = aux.populate(this.curstep.sql,this.baseobject,this.querydata); 1072 if (this.curstep['sql_'+db.getCurrentConfigDBtype()]) { 1073 this.dbrc['sql_'+db.getCurrentConfigDBtype()] = aux.populate(this.curstep['sql_'+db.getCurrentConfigDBtype()],this.baseobject,this.querydata) 1074 } 1075 this.dbrc.info = "check if data already there for RW-control "+this.dataname+" at step "+this.curstep.name; 1076 if (this.curstep['bindvars_'+db.getCurrentConfigDBtype()]) { 1077 this.dbrc.params = prepare_grw_bindvars(this,this.querydata,this.curstep['bindvars_'+db.getCurrentConfigDBtype()]); 1078 } else { 1079 this.dbrc.params = prepare_grw_bindvars(this,this.querydata,this.curstep.bindvars); 1080 } 1081 this.REWIND(); 1082 db.select(this.dbrc,this); 1083 break; 1084 case 'dbselect': 1085 this.dbrc.sql = aux.populate(this.curstep.sql,this.baseoject,this.querydata); 1086 if (this.curstep['sql_'+db.getCurrentConfigDBtype()]) { 1087 this.dbrc['sql_'+db.getCurrentConfigDBtype()] = aux.populate(this.curstep['sql_'+db.getCurrentConfigDBtype()],this.baseobject,this.querydata) 1088 } 1089 this.dbrc.info = "Select data from DB for RW-control "+this.dataname+" at step "+this.curstep.name; 1090 if (this.curstep['bindvars_'+db.getCurrentConfigDBtype()]) { 1091 this.dbrc.params = prepare_grw_bindvars(this,this.querydata,this.curstep['bindvars_'+db.getCurrentConfigDBtype()]); 1092 } else { 1093 this.dbrc.params = prepare_grw_bindvars(this,this.querydata,this.curstep.bindvars); 1094 } 1095 this.REWIND(); 1096 db.select(this.dbrc,this); 1097 break; 1098 case 'single': // initiate a single DB transaction 1099 case 'array': // initiate a array DB transaction 1100 case 'fromresult': // initiate a multiple DB transaction from last db result 1101 this.baseobject = this[this.curstep.baseobject]; 1102 if (this.curstep.arrayobject) this.arrayobject = this.baseobject[this.curstep.arrayobject]; 1103 if (this.curstep['pnames_'+db.getCurrentConfigDBtype()]) { 1104 this.pnames = this.curstep['pnames_'+db.getCurrentConfigDBtype()]; 1105 } else { 1106 if (this.curstep.pnames) { 1107 this.pnames = this.curstep.pnames; 1108 } else { 1109 this.pnames = ['typcont','parcont','namcont','valcont','dsccont']; 1110 } 1111 } 1112 this.sqlparams = new Array(); 1113 for (var i = 0; i<this.pnames.length; i++) { 1114 this.sqlparams.push('***_NOVALUE_'+i+'_***'); 1115 } 1116 for (var pi = 0; pi<this.pnames.length; pi++) { 1117 this.sqlparams[pi] = prepare_grw_param(this.curstep[this.pnames[pi]],this); 1118 } 1119 if (this.curstep['sql_'+db.getCurrentConfigDBtype()]) { 1120 this.dbr.sql = aux.populate(this.curstep['sql_'+db.getCurrentConfigDBtype()],this.baseobject,this.querydata); 1121 } else { 1122 if (this.curstep.sql) { 1123 this.dbr.sql = aux.populate(this.curstep.sql,this.baseobject,this.querydata); 1124 } 1125 } 1126 this.REWIND(); 1127 // logger.debug("CREGRW.DBR:\n"+JSON.stringify(this.dbr,null,2)); 1128 db.select(this.dbr,this); 1129 break; 1130 default: 1131 aux.error_callback(this.callback,null,"Illegal ectype "+this.curstep.ectype); 1132 return 1133 } 1134 } 1135 // end of flow CREGRW 1136 ); 1137 1138 var object_persistance_control = 1139 { "EDIT_type": { 1140 objtype: "TYPE", 1141 paraminfotemplate: { name: 'type_edit_document', type: 'type_rw_data', value: {}, 1142 paramdata: [ 1143 { name: 'typname', type: 'queryname', value: '' }, 1144 { name: 'typtext', type: 'string', value: '' }, 1145 { name: 'typydesc', type: 'string', value: '' }, 1146 { name: 'typattribs', type: 'array', arraytype: 'typeattribute', 1147 paramdata: [ 1148 { name: 'typattname', type: 'string', value: '' }, 1149 { name: 'typattvalue', type: 'string', value: '' }, 1150 { name: 'typattdesc', type: 'string', value: '' } 1151 ] 1152 } 1153 ] 1154 }, 1155 steps: [ { name: "PETYPEOBJ", eptype: 'getmobject' } 1156 ] 1157 }, 1158 "EDIT_query": { 1159 objtype: "QUERY", 1160 paraminfotemplate: { name: 'query_edit_document', 1161 type: 'query_rw_data', 1162 value: {}, 1163 paramdata: [ 1164 { name: 'qryname', type: 'queryname', value: '' }, 1165 { name: 'qrytext', type: 'string', value: '' }, 1166 { name: 'qrydesc', type: 'string', value: '' }, 1167 { name: 'qrygrp', type: 'querygroup', value: '' }, 1168 { name: 'qryattribs', type: 'array', arraytype: 'queryattribute', 1169 paramdata: [ 1170 { name: 'qryattname', type: 'string', value: '' }, 1171 { name: 'qryattvalue', type: 'string', value: '' }, 1172 { name: 'qryattdesc', type: 'string', value: '' } 1173 ] 1174 } 1175 ] 1176 }, 1177 steps: [ { name: "PEQRYREC", eptype: "dbgetsingle", 1178 sql: "select RWTYP,RWPARENT,RWNAME,RWVALUE,RWDESC FROM CCRW where (RWTYP='TYPE' and RWPARENT='QUERIES' and RWNAME=:qryname)", 1179 bindvars: [{name: 'qryname', qryatt: 'qryname'}], 1180 fieldcontrols: [ 1181 { name: 'qryname', epatype: 'gcra', pinfopath: ['qryname'], colindex: 2}, 1182 { name: 'qrytext', epatype: 'gcra', pinfopath: ['qrytext'], colindex: 3}, 1183 { name: 'qrydsc', epatype: 'gcra', pinfopath: ['qrydesc'], colindex: 4} 1184 ], 1185 }, 1186 { name: 'PEQRYGRP', eptype: 'dbgetsingle', 1187 sql: "select RWTYP,RWPARENT,RWNAME,RWVALUE,RWDESC FROM CCRW where (RWTYP='DQUERYPARAM' and RWPARENT=:typname and RWNAME='group')", 1188 fieldcontrols: [ 1189 { name: 'qrygrp', epatype: 'gcra', pinfopath: ['qrygrp'], colindex: 3} 1190 ], 1191 }, 1192 { name: 'PEQRYATT', eptype: 'dbgetarray', 1193 sql: "select RWTYP,RWPARENT,RWNAME,RWVALUE,RWDESC FROM CCRW where(RWTYP='DQUERYPARAM' and RWPARENT=:qryname and RWNAME!='group')", 1194 bindvars: [{name: 'qryname', qryatt: 'qryname'}], 1195 pinfobasepath: ['qryattribs'], 1196 fieldcontrols: [ 1197 { name: 'qryattname', epatype: 'gcra', pinfopath: ['qryattname'], colindex: 2}, 1198 { name: 'qryattvalue', epatype: 'gcra', pinfopath: ['qryattvalue'], colindex: 3}, 1199 { name: 'qryattdesc', epatype: 'gcra', pinfopath: ['qryattdesc'], colindex: 4} 1200 ], 1201 } 1202 ] 1203 } 1204 } 1205 1206 /* 1207 * FUNCTION: follow_paraminfo_path 1208 * DESCRIPTION: follows the path in <patharray> (list of branch names in a [paraminfo] structure) starting from <rootobject> 1209 * path segment names in <patharray> may contain placeholders which are replace by values in 1210 * the context object <that> 1211 */ 1212 function follow_paraminfo_path(that,rootobject,valuerootobject,patharray) { 1213 var bo = rootobject; 1214 var vbo = valuerootobject; 1215 var psname, pio, plo, lbn; 1216 for (pi=0; pi<patharray.length; pi++) { 1217 cbo = bo; // memorize current bo as we need it for value tree 1218 psname = aux.populate(patharray[pi],that); // get the next branch's name 1219 // first, climb on in the paraminfo tree 1220 if (bo.type=='array') { // an 'array' paraminfo consumes 2 path segments: 1) the array index 2) the attribute in the array row object 1221 logger.debug("Edit-prepare: "+bo.name+" is an array, skipping path segment \""+psname+"\""); // ... so ignore the 1st, being the index in the array 1222 } else { 1223 if (!bo.paramdata) { 1224 bo.paramdata = new Array(); // if base object does not yet have a paramdata array for further branches, create it 1225 } 1226 if (!(pio=aux.find_in_paramdata(bo.paramdata,psname))) { // create branch if not yet existing 1227 logger.debug("Edit-Prepare: "+bo.name+" does not yet have attribute "+psname+", will create it"); 1228 pio = {name: psname, type: 'unknown', paramdata: new Array(), value: null}; 1229 bo.paramdata.push(pio); 1230 } 1231 bo = pio; // climb to the next branch in paraminfo tree 1232 // second, climb synchronously in the value tree 1233 if (cbo.type='array') 1234 if (!(util.isArray(vbo))) { 1235 logger.error("Edit-Prepare-Error: "+cbo.name+" is marked as array, but corresponding value tree node is not!!"); 1236 vbo = new Array(); 1237 } 1238 } 1239 if (!vbo[psname]) { 1240 vbo[psname] = {}; // create new branch or array entry if not yet existing 1241 } 1242 plo = vbo; // save next-to-last value object for returning it later 1243 vbo = vbo[psname]; // .. and climb to that 1244 lbn = psname; 1245 } 1246 return { paraminfo_object: bo, value_object: vbo, previous_value_object: plo, last_branch_name: lbn}; 1247 } 1248 1249 1250 /* 1251 * FUNCTION: prepare_ep_step 1252 */ 1253 function prepare_ep_step(that) { 1254 that.stepcontrol = that.control.steps[that.curstep]; 1255 switch (that.stepcontrol.eptype) { 1256 case 'dbgetsingle': 1257 case 'dbgetarray': 1258 that.dbr.info = "DB-Get for Edit-Prepare datatype "+that.datatype+" step "+that.stepcontrol.name; 1259 that.dbr.sql = aux.populate(that.stepcontrol.sql,that.query,that); 1260 that.dbr.params = prepare_grw_bindvars(that,that.query,that.stepcontrol.bindvars); 1261 that.REWIND(); 1262 that.state = 'wait_db'; 1263 db.Select(that.dbr,that); 1264 break; 1265 case 'getmobject': 1266 that.mobject = new Mobject({moclass: that.datatype}); 1267 that.REWIND(); 1268 that.state = 'wait_fromstore'; 1269 that.mobject.fromStore(that.query.moid,that); 1270 break; 1271 default: 1272 logger.error("EDIT-prepare-Error: illegal eptype "+that.stepcontrol.eptype+" to prepare in step ("+that.curstep+")"+that.stepcontrol.name); 1273 that.state = "error"; 1274 return; 1275 } 1276 } 1277 1278 /* 1279 * FUNCTION: process_ep_dbresult 1280 */ 1281 function process_ep_dbresult(that) { 1282 var fc; 1283 var val,obj; 1284 obj = follow_paraminfo_path(that,that.paraminfo,that.value,that.stepcontrol.pinfobasepath||[]); 1285 that.baseobject = obj.paraminfo_object; 1286 that.valuebaseobject = obj.value_object; 1287 for (that.arrindex=0; that.arrindex<that.result.rows.length; that.arrindex++) { 1288 for (var fi=0; fi<that.stepcontrol.fieldcontrols.length; fi++) { 1289 fc = that.stepcontrol.fieldcontrols[fi]; 1290 switch (fc.epatype) { 1291 case 'gcra': // GCRA = Get Current ResultAttribute, from row number in ARRINDEX 1292 val = that.result.rows[that.arrindex][fc.colindex]; 1293 obj = follow_paraminfo_path(that,that.baseobject,that.valuebaseobject,fc.pinfopath); 1294 obj.previous_value_object[obj.last_branch_name] = val; 1295 default: 1296 logger.error("EDIT-prepare-Error: illegal epatype "+fc.epatype+" to process result in step ("+that.curstep+")"+that.stepcontrol.name+" field control ("+fi+")"+fc.name); 1297 that.state = "error"; 1298 return; 1299 } 1300 } 1301 } 1302 that.curstep++; 1303 if (that.curstep>=that.control.steps.length) { 1304 that.state = "complete"; 1305 } else { 1306 that.state = "prep" 1307 } 1308 } 1309 /* 1310 * FUNCTION: init_ep_fromobject 1311 * DESCRIPTION: we have a managed object in <that.mobject> 1312 * now start preparing [paraminfo] in <that.paraminfo> for editing it 1313 * <that.paraminfo> is already populated from the control structure 1314 */ 1315 function init_ep_fromobject(that) { 1316 that.state = 'wait_attlist'; 1317 that.REWIND(); 1318 that.attributeinfo = {}; 1319 that.mobject.enumerateAttributes(that); 1320 } 1321 1322 function request_next_attinfo(that) { 1323 if (that.arrayindex>=that.attributelist.length) { 1324 that.state = 'prep_mo_editform'; 1325 that.REWIND(); 1326 that(); 1327 } else { 1328 that.state = 'wait_attinfo'; 1329 that.REWIND(); 1330 that.attname = that.attributelist[that.arrayindex]; 1331 that.mobject.getAttributeInfo(that.attname,that); 1332 } 1333 } 1334 1335 function init_ep_attinfo(that) { 1336 if (that.result.resulttype && that.result.resulttype=='attributelist') { 1337 that.attributelist = result.attributenames; 1338 that.arrayindex = 0; 1339 request_next_ep_attinfo(that); 1340 } else { 1341 aux.error_callback(that.callback,null,"Attribute list result invalid",that.result); 1342 that.exit = true; 1343 that.state = 'error'; 1344 return; 1345 } 1346 } 1347 1348 function next_ep_attinfo(that) { 1349 if (that.result.resulttype && that.result.resulttype=='attributeinfo') { 1350 that.attributeinfo[that.attname] = that.result.attributeinfo; 1351 that.arrayindex++; 1352 request_next_ep_attinfo(that); 1353 } else { 1354 aux.error_callback(that.callback,null,"Attribute info result invalid",that.result); 1355 that.exit = true; 1356 that.state = 'error'; 1357 return; 1358 } 1359 } 1360 1361 /* 1362 * FUNCTION: prep_ep_mo_editform 1363 * DESCRIPTION: we have a MO description in attributelist and attributeinfo, now build an edit-form control structure 1364 */ 1365 function prep_ep_mo_editform(that) { 1366 } 1367 1368 /* ****************************************************************************************************************************** 1369 * 1370 * FLOW: edit_prepare 1371 * DESCRIPTION: prepare a [Formdata] object to present editing form 1372 */ 1373 var old_edit_prepare = flow.define( 1374 function(sess,query,rfunc,pfunc) { 1375 this.session = sess; 1376 this.query = query; 1377 this.callback = rfunc; 1378 this.pcallback = pfunc; 1379 this.value = {}; 1380 this.value.meta = {}; 1381 logger.debug("EDIT_PREPARE.QUERY:",this.query); 1382 this.control = object_persistance_control["EDIT_"+this.query.datatype]; 1383 this.datatype = this.query.datatype; 1384 if (this.control && this.datatype) { 1385 this.value.meta.datatype = this.datatype; 1386 this.paraminfo = {}; 1387 aux.copyObject(this.paraminfo,this.control.paraminfotemplate); 1388 this.curstep = 0; 1389 this.stepcontrol = this.control.steps[this.curstep]; 1390 this.state = 'prep'; 1391 this.dbr = new db.Request({info: "DB-Get for Edit-Prepare datatype "+this.datatype, 1392 reqid: 'RQID_EPNOSQL', 1393 sql: "select 'NO_SQL_GIVEN_IN_EDIT_PREPARE' as ERROR from DUAL", 1394 sql_mysql: "select 'NO_SQL_GIVEN_IN_EDIT_PREPARE' as ERROR", 1395 params: null}); 1396 this(); 1397 } else { 1398 aux.error_callback(this.callback,null,"EDIT-Prepare-Error: "+(!this.datatype?"no datatype given":"")+(!this.control?" control structure "+(this.datatype?" for "+this.datatype:"")+" not found":"")); 1399 } 1400 }, 1401 // step 2: execute current state 1402 function(err,result) { 1403 if (err) { 1404 aux.error_callback(this.callback,err,"EDIT-prepare: Error in state "+this.state+" step ("+this.curstep+")"+this.stepcontrol.name,result); 1405 return; 1406 } 1407 if (result) { 1408 this.result = result; 1409 switch (this.state) { 1410 case 'wait_fromstore': // got managed object populated 1411 init_ep_fromobject(this); 1412 break; 1413 case 'wait_db': 1414 process_ep_dbresult(this); 1415 break; 1416 case 'wait_attlist': 1417 init_ep_attinfo(this); 1418 break; 1419 case 'wait_attinfo': 1420 next_ep_attinfo(this); 1421 break; 1422 default: 1423 aux.error_callback(this.callback,null,"EDIT-prepare-Error: illegal state "+this.state+" to process dbresult in step ("+this.curstep+")"+this.stepcontrol.name,result); 1424 return; 1425 } 1426 } 1427 switch (this.state) { 1428 case 'prep': 1429 prepare_ep_step(this); 1430 break; 1431 case 'wait_db': 1432 break; 1433 case 'complete': 1434 this.callback(null,{resulttype: 'editdata', paraminfo: this.paraminfo, value: this.value}); 1435 return; 1436 break; 1437 case 'wait_attlist': 1438 break; 1439 case 'wait_attinfo': 1440 break; 1441 case 'prep_mo_editform': 1442 prep_ep_mo_editform(that); 1443 break; 1444 default: 1445 aux.error_callback(this.callback,null,"EDIT-prepare-Error: illegal state "+this.state+" in step ("+this.curstep+")"+this.stepcontrol.name,result); 1446 return; 1447 } 1448 } 1449 // end of flow edit_prepare 1450 ); 1451 1452 var edit_prepare = flow.define( 1453 // step 1: instantiate managed object 1454 function(sess,query,rfunc,pfunc) { 1455 this.session = sess; 1456 this.query = query; 1457 this.callback = rfunc; 1458 this.pcallback = pfunc; 1459 if (!this.query.moid) { 1460 aux.error_callback(this.callback,null,"no MOID given for edit_prepare",this.query); 1461 return; 1462 } 1463 this.moid = this.query.moid; 1464 this.mobject = new Mobject(); 1465 this.mobject.fromStore(this.moid,this); 1466 }, 1467 // step 2: got managed object 1468 function(err,result) { 1469 if (err) { 1470 aux.error_callback(this.callback,err,"Error getting managed object "+this.moid,result); 1471 return; 1472 } 1473 if (!result) { 1474 aux.error_callback(this.callback,null,"Did not get result for managed object ID "+this.moid+" in edit_prepare",this.query); 1475 return; 1476 } 1477 aux.error_callback(this.callback,null,"Not Yet implemented",this.query); 1478 } 1479 // end of flow edit_prepare 1480 ); 1481 1482 1483 var edit_save = flow.define( 1484 function(sess,query,rfunc,pfunc) { 1485 this.session = sess; 1486 this.query = query; 1487 this.callback = rfunc; 1488 this.pcallback = pfunc; 1489 aux.error_callback(this.callback,null,"EDIT_save not yet implemented"); 1490 return; 1491 } 1492 // end of flow edit_save 1493 ); 1494 1495 /* ***************************************************************************** 1496 * FLOW: edit 1497 * INPUT: editobject - unique ID of object to edit 1498 * DESCRIPTION: building block for edit workflows: 1499 * mode='prepare': prepare a [paraminfo] object to present editing form 1500 * mode='save': commit the edited data 1501 */ 1502 var edit = flow.define( 1503 function(sess,query,rfunc,pfunc) { 1504 this.session = sess; 1505 this.query = query; 1506 this.callback = rfunc; 1507 this.pcallback = pfunc; 1508 if (!query.mode) { 1509 aux.error_callback(this.callback,null,"EDIT-Error: no mode given"); 1510 return; 1511 } 1512 switch (this.query.mode) { 1513 case 'prepare': 1514 edit_prepare(this.session,this.query,this.callback,this.pcallback); 1515 break; 1516 case 'save': 1517 edit_save(this.session,this.query,this.callback,this.pcallback); 1518 break; 1519 default: 1520 aux.error_callback(this.callback,null,"EDIT-Error: illegal edit-mode \""+this.query.mode+"\""); 1521 return; 1522 } 1523 return; 1524 }); 1525 1526 /* ***************************************************************************** 1527 * FLOW: flowsequence 1528 * INPUT: sess - session object 1529 * query - query object 1530 * controls the processing of the sequence 1531 * see "<query>-attributes" below 1532 * rfunc - callback function upon completion 1533 * parameters: err - error object on error, NULL otherwise 1534 * result - result object of sequence 1535 * pfunc - callback function to be called upon progress notification 1536 * DESCRIPTION: processes a sequence of steps 1537 * behaviour is controlled by attributes of [query] object 1538 * query-attributes being javascript expressions: 1539 * - may access variables in the context of the flow sequence via "that.<varname>", in the following referred to as "context variables" 1540 * context variables stay around until the end of the flowsequence 1541 * notable and useful context variables: 1542 * Name meaning 1543 * curstep current <stepname> 1544 * stepnum number of current step, starting from 0 1545 * fsteps array of unique <stepname>s, the <curstep> is the one at index <stepnum> 1546 * sql current SQL statement used in steptypes 'dbselect' and 'dbselect_loop' 1547 * state state of the flowsequence processing engine 1548 * substate sub-state of the flowstate processing engine 1549 * defined state/substate values and their meaning ("*" means "do not care") 1550 * state substate meaning 1551 * "start" current step is starting 1552 * "jsexpr" "active" JSEXPR is executing 1553 * "dbselect" dbselect is being processed 1554 * "init" dbselect is initializing 1555 * "wait_db" SQL statement is being executed 1556 * "dbselect_loop" dbselect_loop is being processed 1557 * "init" dbselect_loop is being initialized 1558 * "loopstart" at start of loop body 1559 * "prep_sql" preparing SQL statement 1560 * "exec_sql" SQL statement is executing 1561 * "sql_skipped" SQL statement is skipped 1562 * "loopcomplete" loop has been exited 1563 * "ready" step is ready, next step can be started (if any) 1564 * results array of [result] objects 1565 * - may return result values which are stored in context values 1566 * these return values are, depending on the expression, used to control the flow of the sequence or inside the respective step 1567 * if the return value is an object and has an attribute named "error", processing of the flowsequence is terminated and the "error" variable is 1568 * signalled as the error terminating the sequence 1569 * 1570 * <query>-attribute | meaning 1571 * -------------------------------------+------------------------------------------------------------------------------------------------------------------- 1572 * flowsequence | comma-separated list of unique <stepname>s 1573 * finalexpression | javascript-expression executed after last step is completed, return value kept in context variable "finalexpres" 1574 * <stepname>_steptype | type of step to be executed: 1575 * 'jsexpr' | process javascript, additional attributes define details: 1576 * <stepname>_aexpression | contains javascript expression to be executed, return value stored in context variable "jsexpres" 1577 * | aexpression is executed asynchronously, upon completion "that();" must be called to continue the flowsequence 1578 * | (for synchronous execution see <stepname>_qexpression below !!) 1579 * | during execution, before the callback is invoked, flowstate state/substate is 'jsexpr'/'active' 1580 * <stepname>_qexpression | contains javascript expression to be executed, return value stored in context variable "jsexpres" 1581 * | qexpresssion is executed synchronously, immediately after completion the flowsequence continues 1582 * | ( for asynchronous execution, see <stepname>_aexpression above !!) 1583 * | if jsexpres has a non-null "error" attribute, flowsequence is terminated 1584 * | if both aexpression and qexpression are present, aexpression has precedence over qexpression 1585 * 'dbselect' | process an DB-SQL-statement, additional attributes control more details: 1586 * <stepname>_presqlexpression | javascript expression processed before SQL 1587 * <stepname>_sql | SQL statement to be executed 1588 * | before executing the SQL placeholders ({{...}}) are replaced from context 1589 * <stepname>_bindvars | javascript expression returning array of variable names for BIND-variables passed to DB-SQL-statement 1590 * <stepname>_postsqlexpression | javascript expression executed after successful completion of SQL-statement 1591 * 'dbselect_loop' | process an DB-SQL-statement iteratively until a certain condition is reached, additional attributes: 1592 * <stepname>_initexpression | javascript expression executed at start of iterative loop 1593 * | return value kept in context variable "initexpres" 1594 * <stepname>_checkexpression | javascript expression executed at begin of each loop, must return boolean value, stored in context variable "checkexpres" 1595 * | if TRUE, loop iteration is continued 1596 * | otherwise 1597 * <stepname>_presqlexpression | javascript expression processed before SQL 1598 * | may return an object, which is kept in context variable "presqlexpres" 1599 * | following attributes of this object are recognized: 1600 * | presqlexpres.error - if it is defined, terminates flowsequence with this as an error object 1601 * | presqlexpres.skip - if it is defined, the execution of the SQL for this iteration is skipped 1602 * <stepname>_bindvars | javascript expression returning array of variable names for BIND-variables passed to DB-SQL-statement 1603 * | return value is stored in context variable "sqlparams" 1604 * <stepname>_sql_<dbtype>, | 1605 * <stepname>_sql | SQL statement to be executed 1606 * | where <dbtype> is the database type of the current CCDB configuration 1607 * | if <stepname>_sql_<dbtype> is present, it takes precedence over <stepname>_sql 1608 * | before executing the SQL placeholders ({{...}}) are replaced from context 1609 * <stepname>_postsqlexpression | javascript expression executed after successful completion of SQL-statement 1610 * | ATTENTION: this is not executed if the SQL is skipped 1611 * <stepname>_nextexpression | javascript expression executed at end of loop body, return value stored in context variable "nextexpres" 1612 * | if nextexpres has a non-null-"error" attribute, flowsequence is terminated 1613 * <stepname>_finalexpression | javascript expression executed once at end of completed loops, return value stored in context variable "finalexpres" 1614 * 'macro' | process a macro, which must be defined in the 'macros' branch of the global preferences 1615 * <stepname>_macro | name of the macro in the global prefs 1616 * | this must be the name of a branch in the 'macros' subtree in the global preferences 1617 * | this macro must have either a <aexpression> or a <qexpression> attribute. These are prcoessed 1618 * | according to steptype 'jsexpr' above 1619 * | 1620 * 1621 * 1622 * 1623 */ 1624 1625 function flowsequence_progress(that,pdat) { 1626 var pstat = { action: 'flowsequence.dataname='+that.query.dataname, 1627 stage: that.stepnum+" - "+that.curstep}; 1628 if (that.progressdata) { pstat.progressdata = that.progressdata; }; 1629 if (pdat) { pstat.subaction = pdat }; 1630 if (that.pcallback) { 1631 that.pcallback(pstat); 1632 } 1633 } 1634 1635 function flowsequence_do_branch(stepname) { 1636 // logger.debug("DO_BRANCH("+stepname+").BEGIN"); 1637 this.branch = {}; 1638 switch (stepname) { 1639 case "__STAY": 1640 this.branch.stepnum = this.stepnum; 1641 break; 1642 case "__PREV": 1643 if (this.stepnum>0) { 1644 this.branch.stepnum = this.stepnum - 1; 1645 } 1646 break; 1647 default: 1648 var ni = {}; 1649 // logger.debug("-- default: aux.find_in_array = ",aux.find_in_array); 1650 if (aux.find_in_array(this.fsteps,stepname,ni)) { 1651 this.branch.stepnum = ni.loc; 1652 } else { 1653 throw new Error("Flowsequence Branch tag "+stepname+" not found"); 1654 } 1655 } 1656 } 1657 1658 var flowsequence = flow.define( 1659 // step 1: setup 1660 function(sess,query,rfunc,pfunc) { 1661 this.do_branch = flowsequence_do_branch.bind(this); 1662 this.session = sess; 1663 this.query = query; 1664 this.callback = rfunc; 1665 this.pcallback = pfunc; 1666 if (!this.query.flowsequence) { 1667 aux.error_callback(this.callback,null,"flowsequence not defined",this.query); 1668 return; 1669 } 1670 logger.debug("flowsequence/1.FLOWSEQUENCE["+this.query.dataname+"]="+this.query.flowsequence); 1671 this.fsteps = this.query.flowsequence.split(','); 1672 this.stepnum = 0; 1673 this.curstep = this.fsteps[this.stepnum]; 1674 this.results = new Array(); 1675 this.state = 'start'; 1676 this.substate = 'any'; 1677 this(); 1678 }, 1679 // step 2: process result of step <stepnum>, initiate step <stepnum+1> 1680 function(err,result) { 1681 if (err) { 1682 aux.error_callback(this.callback,err,"Error in flowsequence "+this.query.dataname+" at step "+this.stepnum+" - "+this.curstep+" state "+this.state+'/'+this.substate,this.result); 1683 return; 1684 } 1685 this.result = result; 1686 this.progressdata = "flowsequence/2.FLOWSEQUENCE["+this.query.dataname+"].STEP="+this.stepnum+"-"+this.curstep+".STATE="+this.state+"/"+this.substate; 1687 // logger.debug("FLOWSEQUENCE.PROGRESS: "+this.progressdata); 1688 flowsequence_progress(this); 1689 switch (this.state) { 1690 case 'start': 1691 // prepare new step 1692 this.curstep = this.fsteps[this.stepnum]; 1693 this.branch = null; 1694 if (!(this.steptype = this.query[this.curstep+"_steptype"])) { 1695 aux.error_callback(this.callback,null,"steptype for flowsequence "+this.query.dataname+" step "+this.stepnum+" - "+this.curstep+" is not defined",this.query); 1696 return; 1697 } 1698 switch (this.steptype) { 1699 case 'dbselect': 1700 initiate_fstep_dbselect(this); 1701 break; 1702 case 'dbselect_loop': 1703 initiate_fstep_dbselect_loop(this); 1704 break; 1705 case 'jsexpr': 1706 initiate_fstep_jsexpr(this); 1707 break; 1708 case 'macro': 1709 initiate_fstep_macro(this); 1710 break; 1711 default: 1712 aux.error_callback(this.callback,null,"illegal steptype "+this.steptype+" in flowsequence "+this.query.dataname+" step "+this.stepnum+" - "+this.curstep,this.query); 1713 this.exit = true; 1714 return; 1715 } 1716 break; 1717 case 'dbselect_loop': 1718 next_fstep_dbselect_loop(this); 1719 break; 1720 case 'dbselect': 1721 next_fstep_dbselect(this); 1722 break; 1723 case 'jsexpr': 1724 next_fstep_jsexpr(this); 1725 break; 1726 case 'macro': 1727 next_fstep_macro(this); 1728 break; 1729 case 'ready': // current step is complete, move on to next step 1730 if (this.branch) { // shall we branch ? 1731 this.stepnum = this.branch.stepnum; // branch, take next step from branch target 1732 } else { 1733 this.stepnum++; // no branch, simply move to following step 1734 } 1735 this.branch = null; 1736 if (this.stepnum<this.fsteps.length) { // at least one more step in this flowsequence 1737 this.state = 'start'; 1738 this.REWIND(); 1739 this(); 1740 } else { // all steps of this flowsequence executed 1741 f_process_context_expression(this,"finalexpression","finalexpres"); 1742 if (this.finalexpres && this.finalexpres.error) { 1743 aux.error_callback(this.callback,this.finalexpres.error,"Error processing finalexpression of flowsequence "+this.query.dataname,this.finalexpres); 1744 this.exit = true; 1745 return; 1746 } 1747 if (this.result) { // the result of the flowsequence is either this.result ... 1748 this.callback(null,this.result); 1749 } else { // ... or the most recent of the collected results ... 1750 if (this.results && this.results[this.results.length-1]) { 1751 this.callback(null,this.results[this.results.length-1]); 1752 } else { // ... or a default text if no results available 1753 this.callback(null,{resulttype: 'string', body: 'flowsequence '+this.query.dataname+' successfully completed, no specific result object'}); 1754 } 1755 } 1756 return; 1757 } 1758 break; 1759 default: 1760 aux.error_callback(this.callback,null,"illegal state "+this.state+" in flowsequence "+this.query.dataname+" at step "+this.stepnum,result); 1761 return; 1762 } 1763 } 1764 // end of flow flowsequence 1765 ); 1766 1767 function initiate_fstep_dbselect(that) { 1768 if (!(that.sql = (that.query[that.curstep+"_sql_"+db.getCurrentConfigDBtype()]) || (that.query[that.curstep+"_sql"]))) { 1769 aux.error_callback(that.callback,null,"dbselect SQL statement for flowsequence "+that.query.dataname+" step "+that.stepnum+" - "+that.curstep+" is not defined",that.query); 1770 that.exit = true; 1771 return; 1772 } 1773 that.presqlexpression = that.query[that.curstep+"_presqlexpression"]; 1774 if (that.presqlexpression) { 1775 f_process_context_expression(that,'presqlexpression','presqlexpres'); 1776 if (that.presqlexpres && that.presqlexpres.error) { 1777 aux.error_callback(that.callback,presqlexpres.error,"Error processing presqlexpression",presqlexpres); 1778 that.exit = true; 1779 return; 1780 } 1781 } 1782 that.bindvars_expression = that.query[that.curstep+"_bindvars"]; 1783 that.sqlparams = new Array(); 1784 if (that.bindvars_expression) { 1785 // logger.debug("...process_bindvars_expression.that: ",that); 1786 f_process_context_expression(that,'bindvars_expression','sqlparams'); 1787 // logger.debug("...process_bindvars_expression.sqlparams: ",that.sqlparams); 1788 if (that.sqlparams && that.sqlparams.error) { 1789 aux.error_callback(that.callback,sqlparams.error,"Error processing bindvars_expression",sqlparams); 1790 that.exit = true; 1791 return; 1792 } 1793 } 1794 that.dbr = new db.Request({info: "flowsequence "+that.query.dataname+" step "+that.stepnum+" - "+that.curstep, 1795 reqid: "RQID_FSQ_"+that.query_dataname+"_"+that.curstep, 1796 sql: that.sql, 1797 params: that.sqlparams}); 1798 that.dbr.sql = aux.populate(that.sql,that,that.query); 1799 that.state = 'dbselect'; 1800 that.substate = 'wait_db'; 1801 that.REWIND(); 1802 db.select(that.dbr,that, 1803 function(pdat) { 1804 flowsequence_progress(that,pdat); 1805 }); 1806 } 1807 1808 function next_fstep_dbselect(that) { 1809 switch (that.substate) { 1810 case 'wait_db': 1811 if (that.result && that.result.resulttype && that.result.resulttype=='dbresult') { 1812 that.results.push(that.result); 1813 that.postsqlexpression = that.query[that.curstep+"_postsqlexpression"]; 1814 if (that.postsqlexpression) { 1815 f_process_context_expression(that,'postsqlexpression','postsqlexpres'); 1816 if (that.postsqlexpres && that.postsqlexpres.error) { 1817 aux.error_callback(that.callback,that.postsqlexpres.error,"Error processing postsqlexpression",that.postsqlexpres); 1818 that.exit = true; 1819 return; 1820 } 1821 } 1822 that.state = 'ready'; 1823 that.REWIND(); 1824 that(); 1825 } else { 1826 aux.error_callback(that.callback,null,"no or wrong result from database in flowsequence "+that.query.dataname+" step "+that.stepnum+" - "+that.curstep,that.result); 1827 that.exit = true; 1828 } 1829 break; 1830 default: 1831 aux.error_callback(that.callback,null,"illegal state "+that.state+'/'+that.substate+" in flowsequence "+that.query.dataname+" step "+that.stepnum+" - "+that.curstep,that.result); 1832 that.exit = true; 1833 } 1834 } 1835 1836 function initiate_fstep_dbselect_loop(that) { 1837 if (!(that.sql = (that.query[that.curstep+"_sql_"+db.getCurrentConfigDBtype()]) || (that.query[that.curstep+"_sql"]))) { 1838 aux.error_callback(that.callback,null,"dbselect_loop SQL statement for flowsequence "+that.query.dataname+" step "+that.stepnum+" - "+that.curstep+" is not defined",that.query); 1839 that.exit = true; 1840 return; 1841 } 1842 that.state = 'dbselect_loop'; 1843 that.substate = 'init'; 1844 that.dbr = new db.Request({info: "fsequence "+that.query.dataname+" step "+that.stepnum+" - "+that.curstep, 1845 reqid: "RQID_FSQDBSLOOP_"+that.query_dataname+"_"+that.curstep, 1846 sql: that.sql, 1847 params: new Array()}); 1848 that.REWIND(); 1849 that(); 1850 } 1851 1852 function next_fstep_dbselect_loop(that) { 1853 switch (that.substate) { 1854 case 'init': 1855 f_process_context_expression(that,that.curstep+"_initexpression","initexpres"); 1856 if (that.initexpres.error) { 1857 aux.error_callback(that.callback,that.initexpres.error,"Error processing initexpression",that.initexpres); 1858 that.exit = true; 1859 return; 1860 } 1861 that.REWIND(); 1862 that.substate = 'loopstart'; 1863 that(); 1864 break; 1865 case 'loopstart': 1866 f_process_context_expression(that,that.curstep+"_checkexpression","checkexpres"); 1867 // logger.debug("...CHECKEXPRES: ",that.checkexpres); 1868 if (that.checkexpres) { 1869 if (that.checkexpres.error) { 1870 aux.error_callback(that.callback,that.checkexpres.error,"Error processing checkexpression",that.checkexpres); 1871 that.exit = true; 1872 return; 1873 } 1874 that.substate = 'prep_sql'; 1875 that.REWIND(); 1876 that(); 1877 } else { 1878 that.substate = 'loopcomplete'; 1879 that.REWIND(); 1880 that(); 1881 } 1882 break; 1883 case 'prep_sql': 1884 f_process_context_expression(that,that.curstep+"_presqlexpression","presqlexpres"); 1885 if (that.presqlexpres && that.presqlexpres.error) { 1886 aux.error_callback(that.callback,that.presqlexpres.error,"Error processing presqlexpression",that.presqlexpres); 1887 that.exit = true; 1888 return; 1889 } 1890 if (that.presqlexpres && that.presqlexpres.skip) { // pre-sql-expression decided to skip this array record 1891 that.substate = 'sql_skipped' 1892 that.REWIND(); 1893 that(); 1894 } else { 1895 that.sqlparams = new Array(); 1896 f_process_context_expression(that,that.curstep+"_bindvars","sqlparams"); 1897 if (that.sqlparams && that.sqlparams.error) { 1898 aux.error_callback(that.callback,that.sqlparams.error,"Error processing bindvars expression",that.sqlparams); 1899 that.exit = true; 1900 return; 1901 } 1902 that.dbr.sql = aux.populate(that.sql,that,that.query); 1903 that.dbr.params = that.sqlparams; 1904 that.substate = 'exec_sql'; 1905 that.REWIND(); 1906 db.select(that.dbr,that,that.pcallback); 1907 } 1908 break; 1909 case 'exec_sql': 1910 if (that.result && that.result.resulttype && that.result.resulttype=='dbresult') { 1911 if (!(that.ignoredbresult)) { 1912 that.results.push(that.result); 1913 } 1914 f_process_context_expression(that,that.curstep+"_postsqlexpression","postsqlexpres"); 1915 if (that.postsqlexpres && that.postsqlexpres.error) { 1916 aux.error_callback(that.callback,that.postsqlexpres.error,"Error processing postsqlexpression",that.postsqlexpres); 1917 that.exit = true; 1918 return; 1919 } 1920 } else { 1921 aux.error_callback(that.callback,null,"no or wrong result from database in flowsequence "+that.query.dataname+" step "+that.stepnum+" - "+that.curstep,that.result); 1922 that.exit = true; 1923 return; 1924 } 1925 // ATTENTION: intentionally fall into next case !! 1926 case 'sql_skipped': 1927 // ATTENTION: this is an entry in the above case !! 1928 that.substate = 'sql_done'; 1929 f_process_context_expression(that,that.curstep+"_nextexpression","nextexpres"); 1930 if (that.nextexpres && that.nextexpres.error) { 1931 aux.error_callback(that.callback,that.nextexpres.error,"Error processing nextexpression",that.nextexpres); 1932 that.exit = true; 1933 return; 1934 } 1935 that.substate = 'loopstart'; 1936 that.REWIND(); 1937 that(); 1938 break; 1939 case 'loopcomplete': 1940 f_process_context_expression(that,that.curstep+"_finalexpression","finalexpres"); 1941 if (that.finalexpres && that.finalexpres.error) { 1942 aux.error_callback(that.callback,that.finalexpres.error,"Error processing finalexpression",that.finalexpres); 1943 that.exit = true; 1944 return; 1945 } 1946 that.state = 'ready'; 1947 that.REWIND(); 1948 that(); 1949 break; 1950 default: 1951 aux.error_callback(that.callback,null,"illegal state "+that.state+'/'+that.substate+" in flowsequence "+that.query.dataname+" step "+that.stepnum+" - "+that.curstep,that.result); 1952 that.exit = true; 1953 } 1954 } 1955 1956 function initiate_fstep_jsexpr(that) { 1957 that.async = false; 1958 that.jsexpression = that.query[that.curstep+"_aexpression"]; 1959 if (that.jsexpression) { 1960 that.async = true; 1961 } else { 1962 that.jsexpression = that.query[that.curstep+"_qexpression"]; 1963 that.async = false; 1964 } 1965 that.state = 'jsexpr'; 1966 that.substate = 'active'; 1967 if (that.jsexpression) { 1968 f_process_context_expression(that,"jsexpression","qexpres"); 1969 if (that.qexpres && that.qexpres.error) { 1970 aux.error_callback(that.callback,that.qexpres.error,"Error processing qexpression",that.qexpres); 1971 that.exit = true; 1972 return; 1973 } 1974 that.REWIND(); 1975 // logger.debug("INITIATE_FSTEP_JSEXPR.EXPRCOMPL.ASYNC="+(that.async?"YES":"NO")); 1976 if (!that.async) { // if synchronous(QEXPRESSION), kick myself forward, else wait for callback from AEXPRESSION 1977 that(null,that.qexpres); 1978 } 1979 } else { 1980 that({resulttype: 'object', body: "neither aexpression nor qexpression for flowsequence "+that.query.dataname+" step "+that.stepnum+" - "+that.curstep}); 1981 } 1982 } 1983 1984 1985 function next_fstep_jsexpr(that) { 1986 // logger.debug("NEXT_FSTEP_JSEXPR.SUBSTATE="+that.substate); for (var ta in that) { logger.debug(".THAT["+ta+"]: ",that[ta]); } 1987 switch (that.substate) { 1988 case 'active': 1989 that.jsresult = that.result; // save result of jsexpr step in jsresult 1990 that.state = 'ready'; 1991 that.REWIND(); 1992 that(); 1993 break; 1994 default: 1995 aux.error_callback(that.callback,null,"illegal state "+that.state+'/'+that.substate+" in flowsequence "+that.query.dataname+" step "+that.stepnum+" - "+that.curstep,that.result); 1996 that.exit = true; 1997 } 1998 } 1999 2000 function initiate_fstep_macro(that) { 2001 that.async = false; 2002 that.macname = that.query[that.curstep+"_macro"]; 2003 if (!that.macname) { 2004 aux.error_callback(that.callback,null,"Macro name not defined for flowsequence step "+that.curstep,that.query); 2005 that.exit = true; 2006 return; 2007 } 2008 var prefs = aux.getGlobalNamedObject("PREFS"); 2009 if (!(that.macro = prefs.macros[that.macname])) { 2010 aux.error_callback(that.callback,null,"Macro "+that.macname+" is not defined",that.query); 2011 that.exit = true; 2012 return; 2013 } 2014 that.jsexpression = that.macro["aexpression"]; 2015 if (that.jsexpression) { 2016 that.async = true; 2017 } else { 2018 that.jsexpression = that.macro["qexpression"]; 2019 that.async = false; 2020 } 2021 that.state = 'macro'; 2022 that.substate = 'active'; 2023 if (that.jsexpression) { 2024 f_process_context_expression(that,"jsexpression","jsexpres"); 2025 if (that.jsexpres && that.jsexpres.error) { 2026 aux.error_callback(that.callback,that.jsexpres.error,"Error processing jsexpression in macro "+that.macname,that.jsexpres); 2027 that.exit = true; 2028 return; 2029 } 2030 that.REWIND(); 2031 // logger.debug("INITIATE_FSTEP_MACRO.EXPRCOMPL.ASYNC="+(that.async?"YES":"NO")); 2032 if (!that.async) { // if synchronous(QEXPRESSION), kick myself forward, else wait for callback from AEXPRESSION 2033 that(null,that.jsexpres); 2034 } 2035 } else { 2036 that({resulttype: 'object', body: "neither aexpression nor qexpression for macro "+that.macname+" in flowsequence "+that.query.dataname+" step "+that.stepnum+" - "+that.curstep}); 2037 } 2038 } 2039 2040 function next_fstep_macro(that) { 2041 // logger.debug("NEXT_FSTEP_MACRO.SUBSTATE="+that.substate); for (var ta in that) { logger.debug(".THAT["+ta+"]: ",that[ta]); } 2042 switch (that.substate) { 2043 case 'active': 2044 that.jsresult = that.result; // save result of jsexpr step in jsresult 2045 that.state = 'ready'; 2046 that.REWIND(); 2047 that(); 2048 break; 2049 default: 2050 aux.error_callback(that.callback,null,"illegal state "+that.state+'/'+that.substate+" macro "+that.macname+" in flowsequence "+that.query.dataname+" step "+that.stepnum+" - "+that.curstep,that.result); 2051 that.exit = true; 2052 } 2053 } 2054 2055 /* ***************************************************************************** 2056 * 2057 * The Managed Objects 2058 * 2059 * *****************************************************************************/ 2060 2061 /* **************************************************************************** 2062 * FLOW: genericPersist 2063 * INPUT: sess - session object 2064 * instance - object reference to be processed 2065 * this is either being read from 2066 * or also being written to 2067 * action - name of the action to be performed 2068 * there must be a =>FLOWSEQUENCE with this name 2069 * callback - function to be called upon completion 2070 * parameters: 2071 * err - error object if something went wrong 2072 * result - result object 2073 * if no error, this is the instance 2074 * pcallback - progress status callback 2075 * DESCRIPTION: generic persistance processor: 2076 * performs the flowsequence with dataname=<action> 2077 * supplies a [query] object with: 2078 * dataname - <action> 2079 * moid - <moid> 2080 * instance - <instance> 2081 * <xxxx> - data from the FLOWSEQUENCE definition (RWVALUE field with RWTYP='FQATTRIB', RWPARENT=<action>, RWNAME=xxxx ) 2082 * <xxxx>_DESC - descriptive information for <xxxx> (RWDESC field from RWTYP='FQATTRIB', RWPARENT=<action>, RWNAME=xxxx ) 2083 * genericPersist is the central interface between 2084 * the in-memory representation of managed objects and their 2085 * persistent storage 2086 * genericPersist does neither assume nor know anything about the objects 2087 * 2088 */ 2089 var genericPersist = flow.define( 2090 function(sess,instance,moid,action,callback,pcallback) { 2091 this.session = sess; 2092 this.instance = instance; 2093 this.moid = moid; 2094 this.action = action; 2095 this.callback = callback; 2096 this.pcallback = pcallback; 2097 /* 2098 logger.debug("...genericPersist.start.SESS: ",sess); 2099 logger.debug("...genericPersist.start.INSTANCE: ",instance); 2100 logger.debug("...genericPersist.start.MOID: ",moid); 2101 logger.debug("...genericPersist.start.ACTION: ",action); 2102 logger.debug("...genericPersist.start.CALLBACK: ",callback); 2103 logger.debug("...genericPersist.start.PCALLBACK: ",pcallback); 2104 */ 2105 this.query = { dataname: this.action, 2106 moid: this.moid, 2107 instance: this.instance 2108 }; 2109 // logger.debug("...genericPersist.before_dbselect.this.query: ",this.query); 2110 this.qdbr = new db.Request({info: "get persistance processing flowsequence descriptor for "+this.action, 2111 reqid: "RQID_FQDESC", 2112 sql: "select RWTYP,RWPARENT,RWNAME,RWVALUE,RWDESC from CCRW where RWTYP='FQATTRIB' and RWPARENT=:fqname", 2113 sql_mysql: "select RWTYP,RWPARENT,RWNAME,RWVALUE,RWDESC from CCRW where RWTYP='FQATTRIB' and RWPARENT=?", 2114 params: [this.action] 2115 }); 2116 db.select(this.qdbr,this); 2117 }, 2118 // process flowsequence descriptor data from RW and initiate flowsequence 2119 function(err,result) { 2120 if (err) { 2121 aux.error_callback(this.callback,err,"genericPersist: Error getting flowsequence descriptor for "+this.action,result); 2122 return; 2123 } 2124 if (result.rows.length<1) { 2125 aux.error_callback(this.callback,null,"genericPersist: no flowsequence data for "+this.action,result); 2126 return; 2127 } 2128 for (var ri=0; ri<result.rows.length; ri++) { // each row in the result defines one query attribute to be used by the flowsequence 2129 this.query[result.rows[ri][2]] = result.rows[ri][3]; // RWNAME is the name of the attribute, RWVALUE is the contents 2130 this.query[result.rows[ri][2]+"_DESC"] = result.rows[ri][4]; // RWDESC my contain a description which is kept in <name>_DESC 2131 } 2132 // logger.debug("...genericPersist.before_flowsequence.this.query: ",this.query); 2133 flowsequence(this.session,this.query,this,this.pcallback); 2134 }, 2135 // flowsequence complete 2136 function(err,result) { 2137 if (err) { 2138 aux.error_callback(this.callback,err,"Error processing flowsequence for "+this.action,result); 2139 return; 2140 } 2141 this.callback(err,result); 2142 } 2143 // end of flow genericPersist 2144 ); 2145 2146 2147 /* ***************************************************************************** 2148 * CLASS: SubItem 2149 * DESCRIPTION: links a DataItem as a sub-item into a parent DataItem 2150 */ 2151 function SubItem(ditem,nam) { 2152 this.classname = "SubItem"; 2153 this.item = ditem; 2154 this.name = nam; 2155 } 2156 2157 var setupDataItem = flow.define( 2158 function(item,callback) { 2159 this.item = item; 2160 callback(null,{resulttype: 'dataitem', dataitem: this.item}); 2161 } 2162 ); 2163 2164 /* ***************************************************************************** 2165 * CLASS: DataItem 2166 * DESCRIPTION: contains one primitive or complex data item 2167 * a DataItem has 2168 * - a name 2169 * - a type 2170 * - a value 2171 * may be a primitive value, an object or an array 2172 * - zero or more SubItems 2173 * - a descriptor containing further attributes 2174 */ 2175 function DataItem(nam,typ,val) { 2176 this.name = nam; 2177 this.type = typ; 2178 this.value = val; 2179 this.subitems = new Array(); 2180 this.desc = {}; 2181 this.enumerateSubItems = function() { 2182 return this.subitems; 2183 } 2184 this.addSubItem = function(si) { 2185 this.subitems.push(si); 2186 } 2187 // populate all info 2188 this.setup = function(callback) { 2189 setupDataItem(this,callback); 2190 } 2191 } 2192 2193 var addMOAttFormItem = flow.define( 2194 function(fdata,obj,attnam,rfunc) { 2195 this.formdata = fdata; 2196 this.callback = rfunc; 2197 this.mobject = obj; 2198 this.attnam = attnam; 2199 obj.getAttributeInfo(attnam,this); 2200 }, 2201 function(err,result) { 2202 if (err) { 2203 aux.error_callback(this.callback,err,"Error getting attribute info for attribute "+this.attnam,result); 2204 return; 2205 } 2206 this.attvalue = this.mobject.getAttribute(attnam); 2207 this.formitem = new Formdata(this.attnam,result.attributeinfo.type,this.attvalue,this.attributeinfo); 2208 fdata.addFormItem(this.formitem); 2209 this.callback(null,null); 2210 } 2211 ); 2212 2213 /* ***************************************************************************** 2214 * FORMS Management 2215 * *****************************************************************************/ 2216 2217 /* 2218 INTRODUCTION: 2219 2220 a FORM is a displayable mechanism to show and/or edit the contents of a data object 2221 2222 In CCDB, there is a multi-layer architecture employed in displaying/editing data objects: 2223 2224 - a [ManagedObject] represents the data object 2225 - a [ManagedObject] is transferred into a [FormData] object 2226 - the [FormData] object contains all necessar structural information that is needed 2227 to present the Data Object on a display device 2228 Thus, the [FormData] object is the device-neutral interface between the data world and 2229 the display/edit-device world 2230 - CCDB is a Web-application, therefore, the display-world is HTML5 2231 - the [FormData] is JSON-serialized and transferred to a HTML-page 2232 - Javascript on the page uses the [FormData] to build an HTML-form with the necessary controls 2233 2234 */ 2235 // add attributes from <attnamlist> of managed object <obj> to [Formdata] object <fdata> 2236 var addMOAttFormItems = flow.define( 2237 function(fdata,obj,attnamlist,rfunc) { 2238 this.formdata = fdata; 2239 this.mobject = obj; 2240 this.attnamlist = attnamlist; 2241 this.callback = rfunc; 2242 this.index = -1; 2243 this(); 2244 }, 2245 function(err,result) { 2246 if (err) { 2247 aux.error_callback(this.callback,err,"Error adding Form Item to Form Data",result); 2248 return; 2249 } 2250 if (this.index<this.attnamlist.length) { 2251 this.index++; 2252 this.REWIND(); 2253 attMOAttFromItem(this.formdata,this.mobject,this.attnamlist[this.index],this); 2254 } else { 2255 this.callback(); 2256 } 2257 } 2258 ); 2259 2260 /* ***************************************************************************** 2261 * CLASS: Formdata 2262 * DESCRIPTION: a [Formdata] object contains all data to present an object as an editable 2263 * web form. 2264 * May contain further [Formdata]-Items 2265 */ 2266 function Formdata(nam,typ,val,desc) { 2267 this.classname = "Formdata"; 2268 this.url = "/dquery"; 2269 this.req = "Aktion"; 2270 this.name = nam; 2271 this.type = typ; 2272 this.value = val; 2273 this.desc = desc; 2274 this.items = new Array(); 2275 this.inputString = false; 2276 this.inputNumber = false; 2277 this.inputEnum = false; 2278 this.inputArray = false; 2279 this.inputObject = false; 2280 this.value = null; 2281 this.parent = null; 2282 // mark parent Formdata 2283 this.setParent = function(theParent) { 2284 this.parent = theParent; 2285 } 2286 // add a Sub-[Formdata]-Item to the form 2287 this.addFormItem = function(fitem) { 2288 this.items.push(fitem); 2289 fitem.setParent(this); 2290 } 2291 // get a list of all (sub-)items 2292 this.enumerateItems = function() { 2293 return this.items; 2294 } 2295 // get the root of the Formdata this item belongs to 2296 this.getFormRoot = function() { 2297 var rfd = this; 2298 while (rfd.parent) { 2299 rfd = rfd.parent; 2300 } 2301 return rfd; 2302 } 2303 } 2304 2305 /* ***************************************************************************** 2306 * MAMANGED Objects 2307 * *****************************************************************************/ 2308 /* 2309 * INTRODUCTION: 2310 * 2311 * Managed Objects, represented as objects of class [Mobject], have 2312 * following properties: 2313 * - have methods to be persisted 2314 * - are accessible only via well defined methods 2315 * - can, therefore, be tracked 2316 * - can, therefore, be access controlled 2317 * 2318 */ 2319 2320 /* **************************************************************************** 2321 * MOCLASS: query 2322 */ 2323 2324 /* **************************************************************************** 2325 */ 2326 function moQueryfromStore(instance,moid,callback) { 2327 genericPersist(instance.session,instance,moid,'queryfromStore',callback); 2328 } 2329 2330 /* **************************************************************************** 2331 */ 2332 function moQuerytoStore(instance,moid,callback) { 2333 genericPersist(instance.session,instance,moid,'querytoStore',callback); 2334 } 2335 2336 /* **************************************************************************** 2337 * FUNCTION: moQueryConstructor 2338 */ 2339 function moQueryConstructor(instance, initializer) { 2340 instance.fromStoreImpl = moQueryfromStore; 2341 instance.toStoreImpl = moQuerytoStore; 2342 } 2343 2344 /* **************************************************************************** 2345 */ 2346 function moTypefromStore(instance,moid,callback) { 2347 // logger.debug("...moTypefromStore.moid: ",moid); 2348 genericPersist(instance.session,instance,moid,'typefromStore',callback); 2349 } 2350 2351 /* **************************************************************************** 2352 */ 2353 function moTypetoStore(instance,moid,callback) { 2354 genericPersist(instance.session,instance,moid,'typetoStore',callback); 2355 } 2356 2357 /* **************************************************************************** 2358 * FUNCTION: moTypeConstructor 2359 */ 2360 function moTypeConstructor(instance, initializer) { 2361 instance.fromStoreImpl = moTypefromStore; 2362 instance.toStoreImpl = moTypetoStore; 2363 } 2364 2365 /* **************************************************************************** 2366 */ 2367 function moMfilefromStore(instance,moid,callback) { 2368 genericPersist(instance.session,instance,moid,'mfilefromStore',callback); 2369 } 2370 2371 /* **************************************************************************** 2372 */ 2373 function moMfiletoStore(instance,moid,callback) { 2374 genericPersist(instance.session,instance,moid,'mfiletoStore',callback); 2375 } 2376 2377 /* **************************************************************************** 2378 * FUNCTION: moMfileConstructor 2379 */ 2380 function moMfileConstructor(instance, initializer) { 2381 instance.fromStoreImpl = moMfilefromStore; 2382 insatnce.toStoreImpl = moMfiletoStore; 2383 } 2384 2385 /* **************************************************************************** 2386 * BOBJ-managed-object permanent storage retrieval asynchronous function 2387 * DESCRIPTION: implemented as a configuration of 'genericPersist' 2388 */ 2389 function moBobjfromStore(instance,moid,callback) { 2390 genericPersist(instance.session,instance,moid,'bobjfromStore',callback); 2391 } 2392 2393 /* **************************************************************************** 2394 * BOBJ-managed-object permanent storage writing asynchronous function 2395 * DESCRIPTION: implemented as a configuration of 'genericPersist' 2396 */ 2397 function moBobjtoStore(instance,moid,callback) { 2398 genericPersist(instance.session,instance,moid,'bobjtoStore',callback); 2399 } 2400 2401 /* **************************************************************************** 2402 * FUNCTION: moBobjConstructor 2403 */ 2404 function moBobjConstructor(instance, initializer) { 2405 instance.fromStoreImpl = moBobjfromStore; 2406 instance.toStoreImpl = moBobjtoStore; 2407 } 2408 2409 2410 // managed object class descriptors 2411 2412 var moclasses = { 2413 query: { 2414 moclass: 'query', 2415 constructor: moQueryConstructor, 2416 fromStoreImpl: moQueryfromStore, 2417 toStoreImpl: moQuerytoStore, 2418 attributes: [ 2419 { name: 'queryname', 2420 type: 'queryname' 2421 }, 2422 { name: 'querytext', 2423 type: 'string' 2424 }, 2425 { name: 'group', 2426 type: 'querygroup' 2427 }, 2428 { name: '*', 2429 type: 'queryattribute', 2430 cardinality: '*' 2431 } ] 2432 }, 2433 type: { 2434 moclass: 'type', 2435 constructor: moTypeConstructor, 2436 fromStoreImpl: moTypefromStore, 2437 toStoreImpl: moTypetoStore 2438 }, 2439 mfile: { 2440 moclass: 'mfile', 2441 constructor: moMfileConstructor, 2442 fromStoreImpl: moMfilefromStore, 2443 toStoreImpl: moMfiletoStore 2444 }, 2445 bobj: { 2446 moclass: 'bobj', 2447 constructor: moBobjConstructor, 2448 fromStoreImpl: moBobjfromStore, 2449 toStoreImpl: moBobjtoStore 2450 } 2451 }; 2452 2453 /* ***************************************************************************** 2454 * CLASS: Mobject - managed Object 2455 */ 2456 function Mobject(initializer) { 2457 this.desc = {}; // object descriptor 2458 initializer = initializer || {}; 2459 this.moclass = initializer.moclass || 'mobject'; // the class name, default is 'mobject' 2460 this.status = 'unstable'; 2461 if (this.moclass!='mobject') { 2462 if (moclasses[this.moclass]) { 2463 this.desc = moclasses[this.moclass]; 2464 if (this.desc.constructor) { 2465 this.desc.constructor(this,initializer); 2466 } else if (this.desc.initializer) { 2467 aux.copyObject(this,this.desc.initializer); 2468 } 2469 } else { 2470 this.error = new Error("unknown managed object class "+this.moclass); 2471 } 2472 } 2473 // enumerateAttributes delivers an attributelist result object with a list array of attributes 2474 this.enumerateAttributes = function(callback) { 2475 var alarr = new Array(); 2476 for (var an in this.desc.attributes) { 2477 alarr.push(an); 2478 } 2479 callback(null,{ resulttype: 'attributelist', attributenames: alarr}); 2480 } 2481 this.getAttributeInfo = function(nam,callback) { 2482 var attinfo = aux.findInObjectArray(this.desc.attributes,'name',nam); 2483 callback(null,{ resulttype: 'attributeinfo', attributeinfo: attinfo } ); 2484 } 2485 this.getAttribute = function(nam) { 2486 return this[nam]; 2487 } 2488 this.setAttribute = function(nam,val) { 2489 this[nam] = val; 2490 } 2491 this.save = function(callback) { 2492 callback({error: new Error("mobject.save not yet implemented")},null); 2493 } 2494 // get this managed object from permanent store 2495 this.fromStore = function(moid,callback) { 2496 if (this.desc && this.desc.fromStoreImpl) { // is there an object-specific fromStore-Implementation ? 2497 this.desc.fromStoreImpl(this,moid,callback); 2498 } else if (this.fromStoreImpl) { // is there a class-specific fromStore-Implementation ? 2499 this.fromStoreImpl(this,moid,callback); 2500 } else { 2501 aux.error_callback(callback,null,"fromStore not (yet) implemented for MOID="+moid); 2502 return; 2503 } 2504 } 2505 // store this managed object in permanent store 2506 this.toStore = function(moid,callback) { 2507 if (this.desc && this.desc.toStoreImpl) { // is there a object-specific toStore-Implementation ? 2508 this.desc.toStoreImpl(this,moid,callback); 2509 } else if (this.toStoreImpl) { // is there a class-specific toStore-Implementation ? 2510 this.toStoreImpl(this,moid,callback); 2511 } else { 2512 aux.error_callback(callback,null,"toStore not (yet) implemented for MOID="+moid); 2513 return; 2514 } 2515 } 2516 } 2517 2518 function Action(desc) { 2519 this.descriptor = desc; 2520 this.parseDesc = function(target,desc) { 2521 var to = {}; 2522 if (desc.slice(0,1)=="{") { // starts with {, it's JSON 2523 try { 2524 to = JSON.parse(desc); 2525 } 2526 catch (e) { 2527 logger.error("Error parsing Action descriptor \""+desc+"\": ",e); 2528 throw e; 2529 } 2530 } else { // not JSON, look it up in prefs.actions 2531 // logger.debug("AUX: ",aux); 2532 var prefs = aux.getGlobalNamedObject("PREFS"); 2533 if (prefs.actions[desc]) { 2534 to = prefs.actions[desc]; 2535 } 2536 } 2537 for (var oan in to) { // copy initializer's attributes 2538 target[oan] = to[oan]; 2539 } 2540 } 2541 this.parseDesc(this,desc); 2542 this.cmd = this.cmd || "NO_COMMAND_GIVEN"; 2543 this.query = this.query || {status: "empty_query_object"}; 2544 this.session = this.session || {status: "empty_session_object"}; 2545 } 2546 2547 2548 /* ***************************************************************************** 2549 * 2550 * FLOW: load_dcresweekly 2551 * INPUT: SESS - session object to check authorization 2552 * MFILEID - managed file id of DCRESWEEKLY file to load 2553 * CALLBACK: rfunc - called upon completion: 2554 * PARAMETERS: 2555 * err - error object if an error occured, otherwise undefined 2556 * result - a DBSelect result, if applicable 2557 * DESCRIPTION: loads managed file <FILEID> into CCDCRES after checking 2558 * 2559 */ 2560 var load_dcresweekly = flow.define( 2561 // step 1: check authorization 2562 function(sess,mfileid,rfunc) { 2563 this.callback = rfunc; 2564 logger.debug("LOAD_DCRESWEEKLY.MFILEID=\""+mfileid+"\""); 2565 this.mfileid = mfileid; 2566 this.session = sess; 2567 check_authorization(this.session,'load_dcresweekly',this,this); 2568 }, 2569 // step 1a: truncate CCDCRESL 2570 function(err,result) { 2571 if (err) { 2572 aux.error_callback(this.callback,err,"Authorization Error in LOAD_DCRESWEEKLY(MFILEID=\""+this.mfileid+"\")",result); 2573 return; 2574 } 2575 this.dbr = new db.Request({ info: "truncate load table CCDCRESL", 2576 reqid: 'RQID_CDRTRURESL', 2577 sql: "truncate table CCDCRESL"}); 2578 db.select(this.dbr,this); 2579 }, 2580 // step 2: get data from MFILES 2581 function(err,result) { 2582 if (err) { 2583 aux.error_callback(this.callback,err,"Error in LOAD_DCERSWEEKLY/TRUNCATE",result); 2584 return; 2585 } 2586 logger.debug("LOAD_DCRESWEEKLY.TRUNCATE.RESULT:") 2587 logger.debug(result); 2588 get_mfile_data(this.mfileid,this); 2589 }, 2590 // step 3: check if data already in CCDCRES 2591 function(err,result) { 2592 if (err) { 2593 aux.error_callback(this.callback,err,"Error in LOAD_DCERSWEEKLY/2",result); 2594 return; 2595 } 2596 this.mfileinfo = result; 2597 this.filename = result.mfilename; 2598 this.filepath = result.mfilepath; 2599 logger.debug("LOAD_DCRESWEEKLY.WILLREAD.FILEPATH=\""+this.filepath+"\".ORIGINAL_NAME=\""+this.filename+"\""); 2600 var regex = /^zpv-report\.([0-9]+)\.csv$/ 2601 var matches = regex.exec(this.filename); 2602 // logger.debug("MATCHES="+matches); 2603 this.loadname = matches[1]; 2604 logger.debug("LOAD_DCRESWEEKLY.LOADNAME=\""+this.loadname); 2605 logger.debug("LOAD_DCRESWEEKLY.CHECK_FOR_LOADNAME_IN_CCDCRES=\""+this.loadname+"\""); 2606 this.dbr = new db.Request({ info: "get data for loadname "+this.loadname+" already there", 2607 reqid: 'RQID_CDRGDTLODNAM', 2608 sql: "select count(*) from CCDCRES where LOADNAME=:loadname", 2609 params: [this.loadname]}); 2610 db.select(this.dbr,this); 2611 }, 2612 // step 4: load data with LOAD_FILE into CCDCRESL 2613 function(err,result) { 2614 if (err) { 2615 aux.error_callback(this.callback,err,"Error in LOAD_DCRESWEEKLY/3",result); 2616 return; 2617 } 2618 this.calcJahr = function(self,filcolvalues) { 2619 // calculate the year for the record 2620 // based on WEEK_OF_YEAR in input field 0-9 2621 // and date of data-generation in LOADNAME: 2622 // if the week number of LOADNAME is less than the WEEK_OF_YEAR, the record must be from last year 2623 // in all other cases the present year, i.e. the year in LOADNAME 2624 // ... aux.logObject(self,"calcJahr: self:"); 2625 var loaddate = aux.dateFromSerialString(self.options.loadname); 2626 var loadyear = loaddate.getFullYear(); 2627 var loadweek = aux.getWeekNr(loaddate); 2628 if (filcolvalues[0]>=loadweek) { 2629 return loadyear-1; 2630 } else { 2631 return loadyear; 2632 } 2633 } 2634 if (result.rows.length>0 && result.rows[0][0]>0) { 2635 aux.error_callback(this.callback,null,"LOAD_DCRESWEEKLY: already "+result.rows[0][0]+" records present for LOADNAME="+this.loadname+" nothing loaded",result); 2636 return; 2637 } 2638 this.loadspec = { LOADNAME: {typ: 'fixopt', att: 'loadname'}, 2639 JAHR: {typ: 'exec', proc: this.calcJahr.bind(this)}, 2640 _all: {typ: 'copyall', att: 'loadfields'} 2641 }; 2642 load_file(this.session,this.mfileid,this,{tablename: "CCDCRESL", linesep: "\r\n", fieldsep: ";", loadspec: this.loadspec, loadname: this.loadname}); 2643 }, 2644 // step 5: check for unknown resource types 2645 function(err,result) { 2646 if (err) { 2647 aux.error_callback(this.callback,err,"Error in LOAD_DCRESWEEKLY/4/LOAD_FILE",result); 2648 return; 2649 } 2650 logger.debug("LOAD_DCRESWEEKLY.CHECK_FOR_UNKNOWN_RESOURCE_TYPES"); 2651 this.dbr = new db.Request({ info: "check for unknown resource types", 2652 reqid: 'RQID_CDRCHKUNKRTY', 2653 sql: "select distinct rl.RESOURCE_NAME, rt.RESTYPE "+ 2654 " from CCDCRESL rl "+ 2655 " left outer join CCVDCRTYP rt on rt.RESNAME=rl.RESOURCE_NAME "+ 2656 " where rt.RESTYPE is null"}); 2657 db.select(this.dbr,this); 2658 }, 2659 // step 6: check for unknown nodes 2660 function(err,result) { 2661 if (err) { 2662 aux.error_callback(this.callback,err,"Error in LOAD_DCRESWEEKLY/5",result); 2663 return; 2664 } 2665 if (result.rows.length>0) { 2666 aux.error_callback(this.callback,null,"unknown resource types detected, nothing loaded",result); 2667 return; 2668 } 2669 logger.debug("LOAD_DCRESWEEKLY.CHECK_FOR_UNKNOWN_NODES"); 2670 this.dbr = new db.Request({ info: "check for unknown nodes", 2671 reqid: 'RQID_CDRCHKUNKNOD', 2672 sql: "select distinct NODE_NAME, h.ALIAS "+ 2673 " from CCDCRESL rl "+ 2674 " left outer join CCDCHOST h on h.HOST=rl.NODE_NAME "+ 2675 " where h.ALIAS is null"}); 2676 db.select(this.dbr,this); 2677 }, 2678 // step 7: transfer from CCDCRESL to CCDCRES 2679 function(err,result) { 2680 if (err) { 2681 aux.error_callback(this.callback,err,"Error in LOAD_DCRESWEEKLY/6",result); 2682 return; 2683 } 2684 if (result.rows.length>0) { 2685 aux.error_callback(this.callback,null,"unknown nodes detected, nothing loaded",result); 2686 return; 2687 } 2688 logger.debug("LOAD_DCRESWEEKLY.TRANSFER_FROM_TABLE_CCDCRESL_TO_TABLE_CCDCRES"); 2689 this.dbr = new db.Request({ info: "transfer from CCDCRESL to CCDCRES", 2690 reqid: 'RQID_CDRXFLRES', 2691 sql: " insert into CCDCRES (RZ_NODE_NAME,LOADNAME,JAHR,KW,NODE,RESTYPE,AMOUNT) "+ 2692 " select rl.NODE_NAME as RZ_NODE_NAME, rl.LOADNAME as LOADNAME, rl.JAHR as JAHR, rl.WEEK_OF_YEAR as KW, h.ALIAS as NODE, "+ 2693 " resn.RWNAME as RESTYPE, rl.VALUE as amount "+ 2694 " from CCDCRESL rl "+ 2695 " left outer join CCDCHOST h on h.HOST=rl.NODE_NAME "+ 2696 " left outer join CCRW resn on resn.RWTYP='DCRTYP' and resn.RWVALUE=rl.RESOURCE_NAME" 2697 }); 2698 db.select(this.dbr,this); 2699 }, 2700 // step 8: check loaded CCDCRES against CCDCRESL 2701 function(err,result) { 2702 if (err) { 2703 aux.error_callback(this.callback,err,"Error in LOAD_DCRESWEEKLY/7",result); 2704 return; 2705 } 2706 logger.debug("LOAD_DCRESWEEKLY.CHECK_NUM_RECORDS_LOADED_INTO_CCDCRES_FROM_CCDCRESL_FOR_EACH_LOADTYPE"); 2707 this.dbr = new db.Request({ info: "check loaded CCDCRES against CCDCRESL", 2708 reqid: 'RQID_CDRCHKRESL', 2709 sql: 'select LOADNAME as "Ladebestandsname", '+ 2710 ' count(*) as "Anzahl geladener Datensätze" '+ 2711 ' from CCDCRES '+ 2712 ' where LOADNAME in (select distinct LOADNAME from CCDCRESL) group by LOADNAME'}); 2713 db.select(this.dbr,this) 2714 }, 2715 // step 9: return result of last check 2716 function(err,result) { 2717 if (err) { 2718 aux.error_callback(this.callback,err,"Error in LOAD_DCERSWEEKLY/8",result); 2719 return; 2720 } 2721 result.title = "data transferred from table CCDCRESL into table CCDCRES"; 2722 logger.debug("LOAD_DCRESWEEKLY: "+result.title); 2723 this.callback(null,result); 2724 } 2725 ); 2726 2727 2728 /* ***************************************************************************** 2729 * FUNCTION: prepare_colnames 2730 * INPUT: that - working context 2731 * filcolnames - array of column names from input file (usually first line) 2732 * RETURNS: array of column names 2733 * DESCRIPTION: prepares an array of column names based on options in query context THAT 2734 */ 2735 function prepare_colnames(that,filcolnames) { 2736 var cna = new Array(); 2737 var cfg; 2738 // logger.debug("LOADSPEC:"); 2739 // logger.debug(that.options.loadspec); 2740 for (var fn in that.options.loadspec) { 2741 cfg = that.options.loadspec[fn]; 2742 if (cfg.typ=='fixatt') { // fixatt: take name from <att> 2743 cna.push(cfg.att); 2744 } else if (cfg.typ=='fixopt') { // fixopt: take name from <att> 2745 cna.push(cfg.att); 2746 } else if (cfg.typ=='exec') { // exec: take name from loadspecname 2747 cna.push(fn); 2748 } else if (cfg.typ=='copyall') { // copyall: take list of names from <filcolnames> 2749 for (var fi = 0; fi<filcolnames.length; fi++) { 2750 cna.push(filcolnames[fi]); 2751 } 2752 } else if (cfg.typ=='copyref') { // copyref: take list of name from <tabcolnames> 2753 var colnamlist = that[cfg.tabcolnames]; 2754 for (var fi = 0; fi<colnamlist.length; fi++) { 2755 cna.push(colnamlist[fi]); 2756 } 2757 } 2758 } 2759 return cna; 2760 } 2761 2762 /* ***************************************************************************** 2763 * FUNCTION: prepare_colvalues 2764 * INPUT: that - working context 2765 * filcolvalues - array of column values from input data source (usually current record got from input file) 2766 * RETURNS: array of prepared column values, enriched or modified by <that.options.loadspec> 2767 * DESCRIPTION: Prepares column values before inserting them into a CCDB-table 2768 * <that.options.loadspec> is expected to be an array of load specifiers. 2769 * A load specifier is assumed to have the following attributes: 2770 * .typ - type of load specifier: 2771 * 'fixatt' - field value is content of context attribute <.att> 2772 * 'fixopt' - field value is content of option <that.options<.att>> 2773 * 'exec' - <.proc> must be a javascript-function. This is executed. 2774 * the function is given the parameters <that> and <filcolvalues> 2775 * The return value of the function is the value for the field 2776 * 'copyall' - copy all values from <filcolvalues> 2777 * .att - attribute name or value, dependent on <.typ> 2778 * .proc - (optional) javascript-function-object for .typ=='exec' 2779 */ 2780 function prepare_colvalues(that,filcolvalues) { 2781 var cva = new Array(); 2782 var cfg; 2783 // logger.debug("LOADSPEC:"); 2784 // logger.debug(that.options.loadspec); 2785 for (var fn in that.options.loadspec) { 2786 cfg = that.options.loadspec[fn]; 2787 if (cfg.typ=='fixatt') { // fixatt: field value is content of context attribute <att> 2788 cva.push(that[cfg.att]); 2789 } else if (cfg.typ=='fixopt') { // fixopt: field value is content of option <att> 2790 cva.push(that.options[cfg.att]); 2791 } else if (cfg.typ=='exec') { // exec: execute function .proc, result is field value 2792 cva.push(cfg.proc(that,filcolvalues)); 2793 } else if (cfg.typ=='copyall') { // copyall: add fields for all values in file record 2794 for (var fi = 0; fi<filcolvalues.length; fi++) { 2795 cva.push(filcolvalues[fi]); 2796 } 2797 } else if (cfg.typ=='copyref') { // copyref: add fields for all values named in <tabcolnames> 2798 var filcolnamlist = that[cfg.filcolnames]; // list of names in source (e.g. read from file) 2799 var tabcolnamlist = that[cfg.tabcolnames]; // list of names in target (e.g. database table) 2800 // logger.debug("COPYREF/FILCOLNAMES:"); 2801 // logger.debug(filcolnamlist); 2802 // logger.debug("COPYREF/TABCOLNAMES:"); 2803 // logger.debug(tabcolnamlist); 2804 for (var tni = 0; tni<tabcolnamlist.length; tni++) { // iterate over target column names 2805 var dvi = -1; 2806 for (var fni = 0; fni<filcolnamlist.length; fni++) { // look up target column name in source name list 2807 if (filcolnamlist[fni]==tabcolnamlist[tni]) { 2808 dvi = fni; // found, index in source name list is index in source data list 2809 break; 2810 } 2811 } 2812 if (dvi<0) { 2813 logger.error("Error in PREPARE_COLVALUES: column "+tabcolnamlist[tni]+" not present in file data"); 2814 cva.push(''); 2815 } else { 2816 if (!filcolvalues) { 2817 logger.error("PREPARE_COLVALUES: filcolvalues undefined, fn="+fn); 2818 /* 2819 for (var toa in that) { 2820 logger.debug("---THAT."+toa+": ",that[toa]); 2821 } 2822 logger.error("--.cfg:",cfg); 2823 */ 2824 cva.push('<undef:"filcolvalues undefined">'); 2825 } else { 2826 cva.push(filcolvalues[dvi]); 2827 } 2828 } 2829 } 2830 } 2831 } 2832 return cva; 2833 } 2834 2835 /* ***************************************************************************** 2836 * FLOW: determineDataContainerTypeFromMfileInfo 2837 * INPUT: mfileinfo - mfileinfo-object 2838 * options - options-object for possible further refinement 2839 * CALLBACK: rfunc - called upon completion 2840 * DESCRIPTION: determines the type of data container a MFILE is by: 2841 * 1) checking FTYPE in <mfileinfo> 2842 * 2) <future expansion> 2843 */ 2844 var determineDataContainerTypeFromMfileInfo = flow.define( 2845 // step 1: look into FTYPE 2846 function(mfileinfo,options,rfunc) { 2847 this.mfileinfo = mfileinfo; 2848 this.options = options; 2849 this.callback = rfunc; 2850 this.ctyp = 'UNKNOWN_NO_FTYPE'; 2851 if (this.mfileinfo.ftype) { 2852 switch (mfileinfo.ftype.ext.toUpperCase()) { 2853 case 'XLS': 2854 this.ctyp = 'XLS'; 2855 break; 2856 case 'XLSX': 2857 this.ctyp = 'XLSX'; 2858 break; 2859 case 'CSV': 2860 this.ctyp = 'CSV'; 2861 break; 2862 default: 2863 // no usable FTYPE extension, try file name extension 2864 switch (mfileinfo.ftype.namext.toUpperCase()) { 2865 case 'XLS': 2866 case 'XLSX': 2867 case 'CSV': 2868 this.ctyp = mfileinfo.ftype.namext.toUpperCase(); 2869 break; 2870 default: 2871 this.ctyp = 'UNKNOWN_'+mfileinfo.ftype.ext.toUpperCase(); 2872 } 2873 } 2874 } 2875 this.callback(null,{container_type: this.ctyp}); 2876 } 2877 ); 2878 2879 2880 /* ***************************************************************************** 2881 * FLOW: readCSVFileToMemory 2882 * INPUT: mfileinfo - mfileinfo-object about file to be read 2883 * options - options-object controlling processing: 2884 * sheetname - name of sheet/partial-table etc. to be read if file has such a structure (e.g. Excel workbook) 2885 * CALLBACK: rfunc - called upon completion 2886 * PARAMETERS: 2887 * err - error object, null if no error 2888 * result - result object with data from file/part 2889 */ 2890 var readCSVFileToMemory = flow.define( 2891 function(mfileinfo,options,rfunc) { 2892 this.mfileinfo = mfileinfo; 2893 this.options = options; 2894 this.callback = rfunc; 2895 aux.default_param(this,'options',{}); 2896 aux.default_param(this.options,'fieldsep','\t'); 2897 aux.default_param(this.options,'linesep','\n'); 2898 aux.default_param(this.options,'headersInFirstLine','Y'); 2899 aux.default_param(this.options,'csv_readoptions',{encoding: 'utf8'}); 2900 try { 2901 this.filedata = fs.readFileSync(this.mfileinfo.mfilepath,this.options.csv_readoptions); // TODO: make async !! 2902 } 2903 catch (e) { 2904 aux.error_callback(this.callback,e,"Fehler beim Lesen von "+this.xlsfilepath+" als CSV-Datei"); 2905 return; 2906 } 2907 this.filelines = this.filedata.split(this.options.linesep); 2908 logger.debug("readCSVFileToMemory("+this.mfileinfo.mfilepath+").LINES_GOT="+this.filelines.length); 2909 while (this.filelines[this.filelines.length-1].length==0) { 2910 this.filelines.length--; 2911 logger.debug("readCSVFileToMemory.LAST_LINE_IS_EMPTY.TRUNCATED_LINE_COUNT="+this.filelines.length); 2912 } 2913 this.result = new db.Result(); 2914 this.curline = 0; 2915 var flfa; 2916 // produce metaData 2917 if (this.options.headersInFirstLine) { // headers in first line determine input data structure 2918 flfa = this.filelines[0].split(this.options.fieldsep); 2919 for (var fi=0; fi<flfa.length; fi++) { 2920 this.result.metaData.push({name: flfa[fi]}); 2921 } 2922 this.curline++; 2923 } else { // no header information to be gotten from file, produce dummy metaData 2924 flfa = this.filelines[0].split(this.options.fieldsep); // use first line to determine count of columns 2925 for (var fi=0; fi<flfa.length; fi++) { 2926 this.result.metaData.push({name: 'Column'+fi}); 2927 } 2928 } 2929 // now produce data 2930 for (var li=this.curline; li<this.filelines.length; li++) { 2931 var crow = new Array(); 2932 flfa = this.filelines[li].split(this.options.fieldsep); 2933 for (var fi=0; fi<flfa.length; fi++) { 2934 crow.push(flfa[fi]); 2935 } 2936 this.result.rows.push(crow); 2937 } 2938 delete this.filelines; 2939 delete this.filedata; 2940 this.result.resulttype = 'dbresult'; 2941 this.callback(null,this.result); 2942 } 2943 // end of flow readCSVFileToMemory 2944 ); 2945 2946 /* ***************************************************************************** 2947 * FLOW: readExcelFileToMemory 2948 * INPUT: mfileinfo - mfileinfo-object about file to be read 2949 * options - options-object controlling processing: 2950 * sheetname - name of sheet/partial-table etc. to be read if file has such a structure (e.g. Excel workbook) 2951 * CALLBACK: rfunc - called upon completion 2952 * PARAMETERS: 2953 * err - error object, null if no error 2954 * result - result object with data from file/part 2955 */ 2956 var readExcelFileToMemory = flow.define( 2957 function(mfileinfo,options,rfunc) { 2958 this.mfileinfo = mfileinfo; 2959 this.options = options; 2960 this.callback = rfunc; 2961 aux.default_param(this.options,'xlsx_readoptions',{ cellDates: true, cellNF: true, cellStyles: true }); 2962 try { 2963 this.workbook = XLSX.readFile(this.mfileinfo.mfilepath,this.options.xlsx_readoptions); 2964 } 2965 catch (e) { 2966 var nerr = new Error("Fehler beim Lesen von "+this.xlsfilepath+" als Excel-Datei: "+e.message); 2967 logger.error(nerr.message); 2968 this.callback(nerr); 2969 return; 2970 } 2971 if (!(this.options.sheetname)) { 2972 if (this.workbook.SheetNames.length>0) { 2973 this.options.sheetname = this.workbook.SheetNames[0]; 2974 } 2975 } 2976 this.callback(null,{resulttype: 'XLSWorkbook', xlsworkbook: this.workbook}); 2977 return; 2978 } 2979 // end of flow readExcelFileToMemory 2980 ); 2981 2982 2983 /* ***************************************************************************** 2984 * FLOW: readFileDataToMemory 2985 * INPUT: mfileinfo - mfileinfo-object about file to be read 2986 * options - options-object controlling processing: 2987 * sheetname - name of sheet/partial-table etc. to be read if file has such a structure (e.g. Excel workbook) 2988 * CALLBACK: rfunc - called upon completion 2989 * PARAMETERS: 2990 * err - error object, null if no error 2991 * result - result object with data from file/part 2992 */ 2993 var readFileDataToMemory = flow.define( 2994 // step 1: setup context and determine data container type 2995 function(mfileinfo,options,rfunc) { 2996 this.options = options; 2997 this.mfileinfo = mfileinfo; 2998 this.callback = rfunc; 2999 determineDataContainerTypeFromMfileInfo(this.mfileinfo,this.options,this); 3000 }, 3001 // step 2: invoke container type specific loading flow 3002 function (err,result) { 3003 if (err) { 3004 aux.error_callback(this.callback,err,"ReadArrayTableFromFile: error determining container type",result); 3005 return; 3006 } 3007 this.container_type = result.container_type; 3008 switch (this.container_type) { 3009 case 'XLS': 3010 case 'XLSX': 3011 readExcelFileToMemory(this.mfileinfo,this.options,this.callback); 3012 break; 3013 case 'CSV': 3014 readCSVFileToMemory(this.mfileinfo,this.options,this.callback); 3015 break; 3016 /* 3017 case 'ZIP': 3018 readZipTableFromFile(this.mfileinfo,this.options,this.callback); 3019 break; 3020 */ 3021 default: 3022 aux.error_callback(this.callback,null,"readArrayTableFromFile: Unknown file container type \""+this.container_type+"\" in MFILE \""+this.mfileinfo.mfileid+"\"",null); 3023 } 3024 } 3025 ); 3026 3027 3028 /* ***************************************************************************** 3029 * FLOW: readArrayTableFromFile 3030 * INPUT: mfileinfo - mfileinfo-object about file to be read 3031 * options - options-object controlling processing: 3032 * sheetname - name of sheet/partial-table etc. to be read if file has such a structure (e.g. Excel workbook) 3033 * CALLBACK: rfunc - called upon completion 3034 * PARAMETERS: 3035 * err - error object, null if no error 3036 * result - result object with data from file/part 3037 */ 3038 var readArrayTableFromFile = flow.define( 3039 // step 1: setup context and determine data container type 3040 function(mfileinfo,options,rfunc) { 3041 this.options = options; 3042 this.mfileinfo = mfileinfo; 3043 this.callback = rfunc; 3044 if (this.options.memoryfiledata) { 3045 if (this.options.memoryfiledata.resulttype) { 3046 switch (this.options.memoryfiledata.resulttype) { 3047 case 'XLSWorkbook': 3048 this(null,{container_type: 'XLS'}); 3049 break; 3050 case 'dbresult': 3051 this(null,{container_type: 'CSV'}); 3052 break; 3053 default: 3054 aux.error_callback(this.callback,null,"illegal resulttype in option memoryfiledata",this.options.memoryfiledata); 3055 return; 3056 } 3057 } else { 3058 aux.error_callback(this.callback,null,"no resulttype in option memoryfiledata",this.options.memoryfiledata); 3059 return; 3060 } 3061 } else { 3062 determineDataContainerTypeFromMfileInfo(this.mfileinfo,this.options,this); 3063 } 3064 }, 3065 // step 2: invoke container type specific loading flow 3066 function (err,result) { 3067 if (err) { 3068 aux.error_callback(this.callback,err,"ReadArrayTableFromFile: error determining container type",result); 3069 return; 3070 } 3071 this.container_type = result.container_type; 3072 switch (this.container_type) { 3073 case 'XLS': 3074 case 'XLSX': 3075 readExcelTableFromFile(this.mfileinfo,this.options,this.callback); 3076 break; 3077 case 'CSV': 3078 readCSVTableFromFile(this.mfileinfo,this.options,this.callback); 3079 break; 3080 /* 3081 case 'ZIP': 3082 readZipTableFromFile(this.mfileinfo,this.options,this.callback); 3083 break; 3084 */ 3085 default: 3086 aux.error_callback(this.callback,null,"readArrayTableFromFile: Unknown file container type \""+this.container_type+"\" in MFILE \""+this.mfileinfo.mfileid+"\"",null); 3087 } 3088 } 3089 ); 3090 3091 /* ***************************************************************************** 3092 * FLOW: readCSVTableFromFile 3093 * INPUT: mfileinfo - mfileinfo-object about file to be read 3094 * options - options-object controlling processing: 3095 * sheetname - name of sheet/partial-table etc. to be read if file has such a structure (e.g. Excel workbook) 3096 * CALLBACK: rfunc - called upon completion 3097 * PARAMETERS: 3098 * err - error object, null if no error 3099 * result - result object with data from file/part 3100 */ 3101 var readCSVTableFromFile = flow.define( 3102 function(mfileinfo,options,rfunc) { 3103 this.mfileinfo = mfileinfo; 3104 this.options = options; 3105 this.callback = rfunc; 3106 aux.default_param(this,'options',{}); 3107 aux.default_param(this.options,'fieldsep','\t'); 3108 aux.default_param(this.options,'linesep','\n'); 3109 aux.default_param(this.options,'headersInFirstLine','Y'); 3110 aux.default_param(this.options,'csv_readoptions',{encoding: 'utf8'}); 3111 if (this.options.memoryfiledata) { // raw file data already in memory as workbook object 3112 if (this.options.memoryfiledata.resulttype && this.options.memoryfiledata.resulttype=='dbresult') { 3113 this.callback(null,this.options.memoryfiledata); 3114 return; 3115 } else { 3116 aux.error_callback(this.callback,null,"Illegal format of option memoryfiledata for readCSVTableFromFile",this.options.memoryfiledata); 3117 return; 3118 } 3119 } else { 3120 try { 3121 this.filedata = fs.readFileSync(this.mfileinfo.mfilepath,this.options.csv_readoptions); // TODO: make async !! 3122 } 3123 catch (e) { 3124 aux.error_callback(this.callback,e,"Fehler beim Lesen von "+this.xlsfilepath+" als CSV-Datei"); 3125 return; 3126 } 3127 } 3128 this.filelines = this.filedata.split(this.options.linesep); 3129 logger.debug("readCSVTableFromFile("+this.mfileinfo.mfilepath+").LINES_GOT="+this.filelines.length); 3130 while (this.filelines[this.filelines.length-1].length==0) { 3131 this.filelines.length--; 3132 logger.debug("readCSVTableFromFile.LAST_LINE_IS_EMPTY.TRUNCATED_LINE_COUNT="+this.filelines.length); 3133 } 3134 this.result = new db.Result(); 3135 this.curline = 0; 3136 var flfa; 3137 // produce metaData 3138 if (this.options.headersInFirstLine) { // headers in first line determine input data structure 3139 flfa = this.filelines[0].split(this.options.fieldsep); 3140 for (var fi=0; fi<flfa.length; fi++) { 3141 this.result.metaData.push({name: flfa[fi]}); 3142 } 3143 this.curline++; 3144 } else { // no header information to be gotten from file, produce dummy metaData 3145 flfa = this.filelines[0].split(this.options.fieldsep); // use first line to determine count of columns 3146 for (var fi=0; fi<flfa.length; fi++) { 3147 this.result.metaData.push({name: 'Column'+fi}); 3148 } 3149 } 3150 // now produce data 3151 for (var li=this.curline; li<this.filelines.length; li++) { 3152 var crow = new Array(); 3153 flfa = this.filelines[li].split(this.options.fieldsep); 3154 for (var fi=0; fi<flfa.length; fi++) { 3155 crow.push(flfa[fi]); 3156 } 3157 this.result.rows.push(crow); 3158 } 3159 delete this.filelines; 3160 delete this.filedata; 3161 this.callback(null,this.result); 3162 } 3163 // end of flow readCSVTableFromFile 3164 ); 3165 3166 /* ***************************************************************************** 3167 * FLOW: readExcelTableFromFile 3168 * INPUT: mfileinfo - mfileinfo-object about file to be read 3169 * options - options-object controlling processing: 3170 * sheetname - name of sheet/partial-table etc. to be read if file has such a structure (e.g. Excel workbook) 3171 * CALLBACK: rfunc - called upon completion 3172 * PARAMETERS: 3173 * err - error object, null if no error 3174 * result - result object with data from file/part 3175 */ 3176 var readExcelTableFromFile = flow.define( 3177 function(mfileinfo,options,rfunc) { 3178 this.mfileinfo = mfileinfo; 3179 this.options = options; 3180 this.callback = rfunc; 3181 if (this.options.memoryfiledata) { // raw file data already in memory as workbook object 3182 if (this.options.memoryfiledata.resulttype && this.options.memoryfiledata.resulttype=='XLSWorkbook') { 3183 this.workbook = this.options.memoryfiledata.xlsworkbook; 3184 } else { 3185 // logger.debug("--MEMORYFILEDATA: ",this.options.memoryfiledata); 3186 aux.error_callback(this.callback,null,"Illegal format of option memoryfiledata for readExcelTableFromFile",this.options.memoryfiledata); 3187 return; 3188 } 3189 } else { 3190 aux.default_param(this.options,'xlsx_readoptions',{ cellDates: true, cellNF: true, cellStyles: true }); 3191 try { 3192 this.workbook = XLSX.readFile(this.mfileinfo.mfilepath,this.options.xlsx_readoptions); 3193 } 3194 catch (e) { 3195 var nerr = new Error("Fehler beim Lesen von "+this.xlsfilepath+" als Excel-Datei: "+e.message); 3196 logger.error(nerr.message); 3197 this.callback(nerr); 3198 return; 3199 } 3200 } 3201 if (!(this.options.sheetname)) { 3202 if (this.workbook.SheetNames.length>0) { 3203 this.options.sheetname = this.workbook.SheetNames[0]; 3204 } 3205 } 3206 if (this.options.disposition!='sheets') { 3207 // logger.debug("readExcelTableFromFile.DISPOSITION="+this.options.disposition+"-SHEETNAMES: ",this.workbook.SheetNames); 3208 // for (var sni in this.workbook.Sheets) logger.debug("-SHEETS.",sni); 3209 this.sheetindex = -1; 3210 for (var wbni = 0; wbni<this.workbook.SheetNames.length; wbni++) { 3211 // if sheetname not specified then use first sheet 3212 // logger.debug("FIND_SHEET.COMPARE=\""+this.workbook.SheetNames[wbni]+"\".AGAINST=\""+this.options.sheetname+"\""); 3213 if (((this.options.sheetname) && (this.options.sheetname==this.workbook.SheetNames[wbni])) || // EITHER sheetname was specified and current sheet is it OR ... 3214 ((!(this.options.sheetname)) && (wbni==0))) { // ... sheetname not specified and this is first sheet 3215 // logger.debug("XLSX.SHEET["+wbni+"]="+this.workbook.SheetNames[wbni]); 3216 this.sheetindex = wbni; 3217 if (!(this.sheetname)) { 3218 this.sheetname = this.workbook.SheetNames[wbni]; 3219 } 3220 break; 3221 } 3222 } 3223 if (this.sheetindex<0) { 3224 if (this.options.sheetname) { 3225 aux.error_callback(this.callback,null,"ReadExcelTableFromFile: worksheet named "+this.options.sheetname+" not found in workbook "+this.mfileinfo.mfilepath); 3226 // logger.debug("--Valid Worksheet names are:"); 3227 // for (var ti=0; ti<this.workbook.SheetNames.length; ti++) logger.debug('SheetNames['+ti+']="'+this.workbook.SheetNames[ti]+'"'); 3228 } else { 3229 aux.error_callback(this.callback,null,"ReadExcelTableFromFile: no sheets in workbook, cannot use the first one in "+this.mfileinfo.mfilepath); 3230 } 3231 return; 3232 } 3233 } else { // list of sheetnames requested 3234 this.result = {resulttype: 'dbresult', metaData: [{name: 'Arbeitsblattnummer'},{name: 'Arbeitsblattname'}]}; 3235 this.result.rows = new Array; 3236 for (var wsi = 0; wsi<this.workbook.SheetNames.length; wsi++) { 3237 this.result.rows.push([wsi,this.workbook.SheetNames[wsi]]); 3238 } 3239 this.callback(null,this.result); 3240 return; 3241 } 3242 this.worksheet = this.workbook.Sheets[this.options.sheetname]; 3243 if (!this.worksheet) { 3244 aux.error_callback(this.callback,null,"Error getting worksheet \""+this.options.sheetname+"\", worksheet is undefined"); 3245 logger.debug("-SHEETNAMES: ",this.workbook.SheetNames); 3246 for (var sni in this.workbook.Sheets) logger.debug("-SHEETS.",sni); 3247 return; 3248 } 3249 this.sheetrange = aux.excelSheetRange(this.worksheet); 3250 // logger.debug("ReadExcelTableFromFile.SHEETRANGE:"); 3251 // logger.debug(this.sheetrange); 3252 if (!this.sheetrange) { 3253 var xta; 3254 var lim; 3255 // logger.error("ReadExcelTableFromFile: range object in worksheet "+this.options.sheetname); 3256 aux.default_param(this.options,'startcol',0); 3257 aux.default_param(this.options,'endcol',999999); 3258 aux.default_param(this.options,'startline',0); 3259 aux.default_param(this.options,'endline',999999); 3260 // try to find the extents of the worksheet 3261 lim = this.options.startcol; 3262 for (var ci = this.options.startcol; ci<=this.options.endcol; ci++) { 3263 xta = XLSX.utils.encode_cell({c: ci, r: this.options.startline}); 3264 if (this.worksheet[xta]) { 3265 lim = ci; 3266 continue; 3267 } else { 3268 break; 3269 } 3270 } 3271 this.options.endcol = lim; 3272 lim = this.options.startline; 3273 for (var ri = this.options.startline; ri<=this.options.endline; ri++) { 3274 xta = XLSX.utils.encode_cell({c: this.options.startcol, r: ri}); 3275 if (this.worksheet[xta]) { 3276 lim = ri; 3277 continue; 3278 } else { 3279 break; 3280 } 3281 } 3282 this.options.endline = lim; 3283 // logger.debug("ReadExcelTableFromFile: sheet range detected as R="+this.options.startline+"/C="+this.options.startcol+" to R="+this.options.endline+"/C="+this.options.endcol); 3284 } else { 3285 // logger.debug(this.sheetrange); 3286 aux.default_param(this.options,'startcol',this.sheetrange.s.c); 3287 aux.default_param(this.options,'endcol',this.sheetrange.e.c); 3288 aux.default_param(this.options,'startline',this.sheetrange.s.r); 3289 aux.default_param(this.options,'endline',this.sheetrange.e.r); 3290 } 3291 // logger.debug("ReadExcelTableFromFile: sheet range R="+this.options.startline+"/C="+this.options.startcol+" to R="+this.options.endline+"/C="+this.options.endcol); 3292 this.result = new db.Result(); 3293 this.result.excelrangeinfo = "ReadExcelTableFromFile: sheet range R="+this.options.startline+"/C="+this.options.startcol+" to R="+this.options.endline+"/C="+this.options.endcol 3294 // get header / metaData information 3295 if (this.options.headersInFirstLine) { // headers in first line determine input data structure 3296 var ht; 3297 var hta; 3298 for (var ci=this.options.startcol; ci<=this.options.endcol; ci++) { 3299 hta = XLSX.utils.encode_cell({c: ci, r: this.options.startline}); 3300 // logger.debug("ReadExcelTableFromFile.GETHDR1LINE.CI="+ci+".STARTLINE="+this.options.startline+".HTA=\""+hta+"\""); 3301 if (this.worksheet[hta]) { 3302 ht = this.worksheet[hta].v; 3303 } else { 3304 logger.debug("ReadExcelTableFromFile.EMPTYHDRCELL.HTA="+hta+".CI="+ci+".ROW="+this.options.startline); 3305 ht = "Column"+ci; 3306 } 3307 this.result.metaData.push({name: ht}); 3308 } 3309 this.options.startline++; 3310 } else { 3311 if (this.options.colnames) { 3312 var cn = this.options.colnames.split(','); 3313 for (var ci = 0; ci<cn.length; ci++) { 3314 this.result.metaData.push({name: cn[ci]}); 3315 } 3316 } else { 3317 // if neither headersInFirstLine nor colnames specified, create a temporary array of column names ["Column0", "Column1", ....] 3318 for (var ci=this.options.startcol; ci<=this.options.endcol; ci++) { 3319 this.result.metaData.push({name: 'Column'+ci}); 3320 } 3321 } 3322 } 3323 // now get data from worksheet 3324 var lba; 3325 var ca; 3326 this.options.emptycellcount = 0; 3327 for (var ri = this.options.startline; ri<=this.options.endline; ri++) { 3328 lba = new Array(); // buffer one line herein 3329 for (var ci = this.options.startcol; ci<=this.options.endcol; ci++) { 3330 ca = XLSX.utils.encode_cell({c: ci, r: ri}); 3331 if (this.worksheet[ca]) { // cell is there, use its data 3332 lba.push(this.worksheet[ca].v); 3333 } else { // cell is empty, substitute with empty string 3334 this.options.emptycellcount++; 3335 lba.push(''); 3336 } 3337 } 3338 this.result.rows.push(lba); // add new row to result 3339 } 3340 this.result.options = this.options; // pass (changed) options with result 3341 delete this.workbook; // dispose of workbook in memory 3342 this.callback(null,this.result); 3343 } 3344 // end of flow readExcelTableFromFile 3345 ); 3346 3347 /* ***************************************************************************** 3348 * 3349 * FLOW: get_mfile_data 3350 * INPUT: mfileid - managed file ID 3351 * CALLBACK: rfunc - called upon completion 3352 * PARAMETERS: 3353 * err - error object in case of error 3354 * result - [mfileinfo] object with MFILE attributes 3355 * mfileid - unique managed file ID as kept in MFILES database 3356 * mfiletype - managed file type ("select RWNAME, RWVALUE,RWDESC from CCRW where RWTYP='MFTYP' and RWPARENT='MFILES'") 3357 * mfilename - the physical name of the file (mostly before uploading to managed file database) 3358 * mfilepath - the file system path to the managed file 3359 * usually relative. In this case it must be relative to the "appbase" global preference 3360 * mfileattribs - option text as in managed file database 3361 * in most cases this is a JSON-serialized object, but is given back as serialized string 3362 * ftype - file type object (as returned by aux.getFileType()) : 3363 * mime - MIME type, if available, null otherwise 3364 * ext - file name extension, if available, null otherwise 3365 * namext - file extension from fpath; 3366 * the extension is the last part of the pathname from the last '.' to the end 3367 * if path has no '.' => 'undef' 3368 * fexists - true if file really exists and is accessible, false otherwise 3369 * stats - file stats as returned by the fs.stat() system call ( https://nodejs.org/api/fs.html#fs_fs_stat_path_callback ) 3370 * pfunc - called upon progress report 3371 * PARAMETERS: 3372 * action - an action state object describing the state of affairs 3373 * options - optional options object 3374 * available options: 3375 * rawfile - if present, tells that this is a free file on disk and not a managed file 3376 * DESCRIPTION: gets MFILE data from database (and possibly additional information in the future) 3377 */ 3378 var get_mfile_data = flow.define( 3379 // step 1: request MFILE data form database 3380 function(mfileid,rfunc,pfunc,options) { 3381 this.mfileid = mfileid; 3382 this.callback = rfunc; 3383 this.options = options || {}; 3384 this.pcallback = pfunc; 3385 if (!this.options.rawfile) { 3386 this.dbr = new db.Request({ info: "Get MFILE data for MFILEID=\""+this.mfileid+"\"", 3387 reqid: 'RQID_GETMFILDAT', 3388 sql: "select MFILEID,MFILETYPE,MFILENAME,MFILEPATH,MFILEATTRIBS "+ 3389 " from CCMFILES where MFILEID=:mfileid", 3390 params: [this.mfileid]}); 3391 db.select(this.dbr,this,this.pcallback); 3392 } else { // raw file, attributes must be passed in options 3393 this.result = {}; 3394 this.result.rows = new Array(); 3395 this.mfrow = new Array(); 3396 this.mfrow.push(this.options.mfileid); 3397 this.mfrow.push(this.options.mfiletype); 3398 this.mfrow.push(this.options.mfilename); 3399 this.options.mfilepath = aux.absolutize_filepath(this.options.mfilepath); 3400 this.mfrow.push(this.options.mfilepath); 3401 this.mfrow.push(this.options.mfileattribs); 3402 this.result.rows.push(this.mfrow); 3403 this(null,this.result); // pretend same result as from database 3404 } 3405 }, 3406 // step 2: got MFILE-info from database 3407 function(err,result) { 3408 if (err) { 3409 aux.error_callback(this.callback,err,"Error getting MFILE-Info for MFILEID=\""+this.mfileid+"\"",result); 3410 return; 3411 } 3412 if (!(result.rows) || (result.rows.length<1) || !(result.rows[0]) || (result.rows[0].length<5)) { 3413 aux.error_callback(this.callback,null,"Invalid MFILE-Info data for MFILEID=\""+this.mfileid+"\"",result); 3414 return; 3415 } 3416 this.mfinfo = {mfileid: result.rows[0][0], 3417 mfiletype: result.rows[0][1], 3418 mfilename: result.rows[0][2], 3419 mfilepath: aux.absolutize_filepath(result.rows[0][3]), 3420 mfileattribs: result.rows[0][4]}; 3421 aux.getFileType(this.mfinfo.mfilepath,this); // get file type info 3422 }, 3423 // step 3: got FILETYPE-info, present everything to requester as an mfileinfo-object 3424 function(err,result) { 3425 if (err) { 3426 aux.error_callback(this.callback,err,"GET_MFILE_DATA: Error getting file type",result); 3427 return; 3428 } 3429 this.mfinfo.ftype = result; 3430 this.callback(undefined,this.mfinfo); 3431 } 3432 ); 3433 3434 /* ***************************************************************************** 3435 * 3436 * FLOW: load_file 3437 * INPUT: sess - session object to check authorization credentials 3438 * mfileid - managed file id to load data from 3439 * rfunc - callback function to be called when complete 3440 * options - optional control options: 3441 * sheetname - name of data-table in <mfileid> if file is multi-data/table to get data from 3442 * DEFAULT: dependent on file type: 3443 * Excel-workbook: first worksheet 3444 * tablename - name of table in CCDB-database to load file data into if <disposition> is 'load', ignored otherwise 3445 * disposition - controls what is done with the data 3446 * 'load' - load into table <tablename> 3447 * 'view' - deliver to caller in result 3448 * 'sheets' - deliver list of sheets in file to caller in excel file 3449 * 'memorize' - read the whole raw file into memory, no further processing 3450 * in this case, the result object has: 3451 * if the load file is an Excel-Spreadsheet: 3452 * <resulttype>='XLSWorkbook' and <xlsworkbook> points to the workbook 3453 * headersInFirstLine - if true, first line contains field names 3454 * DEFAULT: true 3455 * truncate - if true, truncate table before loading, i.e. 3456 * loaded content replaces already present content 3457 * if false, loaded content is added to already present content 3458 * DEFAULT: true 3459 * startcol - first column to start with 3460 * DEFAULT: first column in data 3461 * endcol - last column to use 3462 * DEFAULT: last column in data, as determined by the number of columns to process 3463 * startline - number of first line to use 3464 * DEFAULT: first line in data 3465 * endline - number of last line to use 3466 * DEFAULT: last line in data 3467 * colnames - comma-separated list of column names 3468 * DEFAULT: if <headersInFirstLine>: 3469 * contents of first line in sheet 3470 * if not: 3471 * column names from database 3472 * usememorydata - if not-null: file data shall be read from memory object in option <memoryfiledata> 3473 * memoryfiledata - a result-object with structure: 3474 * resulttype - 'XLSWorkbbook': 3475 * xlsworkbook - contains an XLSX.workbook object 3476 * loadspec - a specification what fields to load and how: 3477 * contains a set of objects named for the fields they affect, each object has attributes: 3478 * typ - type name of the load specification for the relevant field: 3479 * 'fixatt' - field shall contain a fixed value, taken from attribute with name from 'att' 3480 * 'fixopt' - field shall contain a fixed value, taken form option with name in 'att' 3481 * 'exec' - execute function .proc, result is field value 3482 * 'copyall' - copy all fields from filedata, names are in first line of filedata 3483 * in this case the object name is irrelevant 3484 * 'copyref' - copy all fields as in .tabcolnames, find column number from .filcolnames 3485 * att - name of an attribute or option 3486 * proc - only valid of typ='exec': 3487 * javascript function to be executed, its return value is used a field value 3488 * DEFAULT: 3489 * if <disposition> is 'view': {_all: {typ: 'copyall', att: 'loadfields'}} - delivers all fields from <mfileid> 3490 * if <disposition> is 'load': {_all: {typ: 'copyref', tabcolnames: 'tcn', filcolnames: 'cn'}} - loads all fields from data that have the same name as a corresponding column in <options.tablename> 3491 * pfunc - optional progress callback 3492 * DESCRIPTION: loads data from worksheet <sheetname> in 3493 * the managed file <mfileid> into the table <tablename> 3494 * controlled by <options> 3495 */ 3496 var load_file = flow.define( 3497 // step 1: check authorization 3498 function(sess,mfileid,rfunc,options,pfunc) { 3499 this.session = sess; 3500 this.mfileid = mfileid; 3501 this.callback = rfunc; 3502 this.options = options || {}; 3503 this.pcallback = pfunc; 3504 // check_authorization(this.session,'load_file',this,this); 3505 this(); 3506 }, 3507 // step 2: prepare context and get managed file 3508 function(err,result) { 3509 if (err) { 3510 var nerr = new Error("Authorization error in LOAD_FILE: "+err.message); 3511 logger.error(nerr.message); 3512 this.callback(nerr,result); 3513 return; 3514 } 3515 get_mfile_data(this.mfileid,this,this.pcallback,this.options); 3516 }, 3517 // step 2: get raw data from file 3518 function(err,mfileinfo) { 3519 if (err) { 3520 aux.error_callback(this.callback,err,"Error in LOAD_FILE/MFILEINFO(MFILEID=\""+this.mfileid+"\")",mfileinfo); 3521 return; 3522 } 3523 aux.default_param(this,'options',{}); 3524 this.mfileinfo = mfileinfo; 3525 if (this.mfileinfo.mfileattribs) { 3526 // logger.debug("LOAD_FILE.MFILEATTRIBS(string)=<"+this.mfileinfo.mfileattribs+">"); 3527 try { 3528 this.mfileattribs = JSON.parse(this.mfileinfo.mfileattribs); 3529 } 3530 catch (e) { 3531 aux.error_callback(this.callback,e,"LOAD_FILE/2: Error parsing MFILE-attributes(MFILEID=\""+this.mfileid+"\".ATTRIBS=\""+this.mfileinfo.mfileattribs+"\"",mfileinfo) 3532 return; 3533 } 3534 } else { 3535 // logger.debug("LOAD_FILE.MFILEATTRIBS_NOT_DEFINED"); 3536 } 3537 if ((this.options.loadcontrol) && (this.options.loadcontrol=='useloadcontrols')) { 3538 logger.debug("LOAD_FILE.USING_loadcontrols.TABLENAME=\""+this.options.tablename+"\""); 3539 if ((this.mfileattribs) && (this.mfileattribs.loadcontrols) && (this.mfileattribs.loadcontrols[this.options.tablename])) { 3540 if (this.mfileattribs.loadcontrols[this.options.tablename].tablename) this.options.tablename = this.mfileattribs.loadcontrols[this.options.tablename].tablename; 3541 if (this.mfileattribs.loadcontrols[this.options.tablename].sheetname) this.sheetname = this.mfileattribs.loadcontrols[this.options.tablename].sheetname; 3542 if (this.mfileattribs.loadcontrols[this.options.tablename].options) { // merge options, options from file overwrite options from above 3543 for (var lon in this.mfileattribs.loadcontrols[this.options.tablename].options) { 3544 this.options[lon] = this.mfileattribs.loadcontrols[this.options.tablename].options[lon]; 3545 } 3546 } 3547 } else { // no applicable loadcontrols present 3548 if ((this.mfileattribs) && (this.mfileattribs.table) && (this.mfileattribs.table==this.options.tablename)) { // fallback to table attribute 3549 if (this.mfileattribs.sheetname) { 3550 this.sheetname = this.mfileattribs.sheetname; 3551 } 3552 } else { // no fallback possible 3553 aux.error_callback(this.callback,null,"LOAD_FILE: loadcontrols."+this.options.tablename+" requested but not present with file, no fallback to table attribute possible",mfileinfo); 3554 return; 3555 } 3556 } 3557 } 3558 aux.default_param(this.options,'headersInFirstLine',true); 3559 aux.default_param(this.options,'truncate',true); 3560 aux.default_param(this.options,'disposition','load'); 3561 this.loadfilepath = this.mfileinfo.mfilepath; 3562 if (this.options.disposition=='memorize') { 3563 readFileDataToMemory(this.mfileinfo,this.options,this); 3564 } else { 3565 if (this.options.usememorydata) { // file data already in memory 3566 readArrayTableFromMemory(this.options.memoryfiledata,this.options,this); 3567 } else { 3568 readArrayTableFromFile(this.mfileinfo,this.options,this); 3569 } 3570 } 3571 }, 3572 // step 3: get table column names from database (but only if intended to load into table) 3573 function(err,result) { 3574 if (err) { 3575 aux.error_callback(this.callback,err,"LOAD_FILE: error reading file data from \""+this.mfileinfo.mfilepath+"\"",result); 3576 return; 3577 } 3578 this.filedata = result; 3579 if (this.options.disposition=='load') { 3580 this.cndbr = new db.Request({ info: 'LOAD_FILE: get column names for table '+this.options.tablename, 3581 reqid: 'RQID_GETTABCOLNAM', 3582 sql: "SELECT COLUMN_NAME,"+ 3583 "(CASE "+ 3584 " WHEN DATA_TYPE='NUMBER' THEN DATA_PRECISION "+ 3585 " ELSE CHAR_LENGTH END) as COL_WIDTH from USER_TAB_COLUMNS where table_name=:TABLE_NAME", 3586 params: [this.options.tablename] 3587 }); 3588 db.select(this.cndbr,this); 3589 } else { // no loading requested, just return read data to caller 3590 this.callback(null,this.filedata); 3591 return; 3592 } 3593 }, 3594 // step 4: for loading: having read column names from database, prepare control structures and truncate table if requested 3595 function(err,result) { 3596 if (err) { 3597 aux.error_callback(this.callback,err,"LOAD_FILE: error getting database table metadata",result); 3598 return; 3599 } 3600 this.cn = new Array(); // array of column names in file data 3601 for (var cni=0; cni<this.filedata.metaData.length; cni++) { 3602 this.cn.push(this.filedata.metaData[cni].name); 3603 } 3604 this.tcn = new Array(); // array of column names in table 3605 this.tcw = new Array(); // array of allowed column widths by database 3606 for (var tcni=0; tcni<result.rows.length; tcni++) { 3607 this.tcn.push(result.rows[tcni][0]); 3608 if (result.rows[tcni][1]>2048) result.rows[tcni][1]=2048; // TEMPFIX: truncate all columns to 2048 chars, TODO: make regular, adapted to oracledb evolution 3609 this.tcw.push(0+result.rows[tcni][1]); 3610 } 3611 aux.default_param(this.options,'loadspec',{_all: {typ: 'copyref', tabcolnames: 'tcn', filcolnames: 'cn'}}); 3612 this.colnames = prepare_colnames(this,this.cn); 3613 // now initiate truncation, if requested (default=true) 3614 if ((this.options.disposition=='load') && (this.options.truncate)) { 3615 this.trudbr = new db.Request({ info: 'LOAD_FILE: truncate table '+this.options.tablename, 3616 reqid: '__NOXLATE_RQID_LOADXLSTRUNC', 3617 sql: 'TRUNCATE TABLE '+this.options.tablename, 3618 params: [], 3619 options: {notranslate: true} 3620 }); 3621 db.select(this.trudbr,this); 3622 } else { 3623 // skip truncating, go on to next step 3624 this(); 3625 } 3626 }, 3627 // step 5: connect to database 3628 function(err,result) { 3629 if (err) { 3630 aux.error_callback(this.callback,err,"LOAD_FILE: Error in truncating target table",result); 3631 return; 3632 } 3633 this.dbconnection = new db.Connection(); 3634 this.dbtype = this.dbconnection.getConfig().getType(); 3635 this.dbconnection.connect(this); 3636 }, 3637 // step 6: connection got, prepare for insert 3638 function(err) { 3639 if (err) { 3640 aux.error_callback(this.callback,err,"LOAD_FILE: error in connecting to "+this.dbtype+"-type-database "+ 3641 this.dbconnection.getConfig().getName()+" with user "+this.dbconnection.getConfig().getUser(),null); 3642 return; 3643 } 3644 // set up DBR for inserting each single row 3645 this.dbr = new db.Request({ info: "LOAD_FILE", 3646 reqid: "__NOXLATE_RQID_LOADXLSINSERT", 3647 sql: " select 'NO_SQL_SET_FOR_LOAD_FILE' as ERROR from DUAL", 3648 params: [], 3649 options: {docommit: false, notranslate: true} 3650 }); 3651 switch (this.dbtype) { 3652 case "oracledb": 3653 this.sql = "insert into "+this.options.tablename+"("+this.colnames.join(",")+") values (:"+this.colnames.join(",:")+")"; 3654 break; 3655 case "mysql": 3656 this.sql = "insert into "+this.options.tablename+"("+this.colnames.join(",")+") values (?"+aux.repeatString(",?",this.colnames.length-1)+")"; 3657 break; 3658 default: 3659 aux.error_callback(this.callback,null,"Database Type "+this.dbtype+" not supported for LOAD_FILE/INSERT"); 3660 return; 3661 } 3662 this.dbr.setSQL(this.sql); 3663 this.linecount = 0; 3664 this.emptycellcount = 0; 3665 this.trunccellcount = 0; 3666 this(); 3667 }, 3668 // step 7: get next row from file data and process, this step is repeated as many times as there are records in worksheet 3669 function(err, result) { 3670 if (err) { 3671 if (!result) result = {}; 3672 result.params = this.params; 3673 result.sql = this.dbr.getSQL(); 3674 aux.error_callback(this.callback,err,"LOAD_FILE/5: error in inserting record "+this.linecount+ 3675 ", statement: \""+this.sql+"\", params["+this.params.length+"]",result); 3676 return; 3677 } 3678 // logger.debug("LOAD_FILE.ROW_PREPARED.FIELDCOUNT="+rv.length); 3679 if (this.filedata.rows.length>2000 && ((this.linecount % 1000)==0)) { process.stdout.write(this.linecount+" of "+this.filedata.rows.length+" rows inserted\x1b[0G"); } 3680 if (this.linecount>=this.filedata.rows.length) { 3681 this(); // fall thru to next step, no (more) data 3682 return; 3683 } 3684 if (!this.filedata.rows[this.linecount]) { 3685 logger.error("Empty Row Data in LOAD_FILE LINECOUNT="+this.linecount+", TABLENAME="+this.options.tablename); 3686 logger.debug("--ROWSINDATA="+this.filedata.rows.length); 3687 } 3688 this.params = prepare_colvalues(this,this.filedata.rows[this.linecount]); // pre-process values according to options 3689 // logger.debug("LOAD_FILE.INSERT_PREPARED.FIELDCOUNT="+this.dbr.params.length); 3690 // logger.debug(this.dbr.params); 3691 // logger.debug("LOAD_FILE: CHECK.TABLE_FIELDS="+this.dbr.params.length); 3692 var ci = -1; 3693 var csi = 0; 3694 for (var tci=0; tci<this.params.length; tci++) { // iterate over all database table column names 3695 // logger.debug("LOAD_FILE: table column "+tci+", named "+this.tcn[tci]+" has max. length "+this.tcw[tci]+ 3696 // ", data to be loaded having length of "+this.dbr.params[tci].length); 3697 if (this.params[tci].length>this.tcw[tci]) { // have to truncate data length to capacity of database table column 3698 logger.error("LOAD_FILE: column "+tci+" in row "+this.linecount+" has length of "+this.params[tci].length+ 3699 ", which is too large for allowed length of "+this.tcw[tci]+", field will be truncated"); 3700 this.params[tci] = this.params[tci].substr(0,this.tcw[tci]); 3701 logger.debug("LOAD_FILE.TRUNCATED_CELL.COLUMN="+tci+".ROW="+this.linecount+".TRUNCATED_LENGTH="+this.params[tci].length); 3702 this.trunccellcount++; 3703 } 3704 } 3705 // logger.debug("LOAD_FILE.CHECK.COMPLETE.NAMECOUNT="+this.tcn.length+".DATACOUNT="+this.dbr.params.length); 3706 this.parobj = {}; 3707 switch (this.dbtype) { 3708 case 'oracledb': 3709 if (!oracledb) { 3710 oracledb = require("oracledb"); 3711 } 3712 for (var tci=0; tci<this.tcn.length; tci++) { 3713 this.parobj[this.tcn[tci]] = {}; 3714 this.parobj[this.tcn[tci]].val = ''+this.params[tci]; // force string, TODO: check correct mapping procedure 3715 this.parobj[this.tcn[tci]].dir = oracledb.BIND_IN; 3716 this.parobj[this.tcn[tci]].type= oracledb.STRING; 3717 } 3718 break; 3719 case "mysql": 3720 this.parobj = this.params; 3721 break; 3722 default: 3723 aux.error_callback(this.callback,null,"Database Type "+this.dbtype+" not supported for LOAD_FILE/INSERT/Prepare_Params"); 3724 return; 3725 } 3726 this.dbr.setParams(this.parobj); 3727 // logger.debug("LOAD_FILE: parameter object created"); 3728 // logger.debug("LOAD_FILE: Execute["+this.linecount+"].DISPOSITION="+this.options.disposition+".INSOBJCNT="+this.tcn.length+":"); 3729 // logger.debug(this.dbr.parobj); 3730 this.dbconnection.execute(this.dbr, 3731 function(err,result) { 3732 this.linecount++; 3733 // logger.debug("LOAD_FILE: line "+this.linecount+" inserted"); 3734 if (this.linecount<this.filedata.rows.length) { // if there are more lines in the spreadsheet ... 3735 this.REWIND(); // ... repeat this step for the next row 3736 } 3737 this(err,result); 3738 }.bind(this)); 3739 }, 3740 // step 8: commit inserts 3741 function(err,result) { 3742 if (err) { 3743 if (!result) result = {}; 3744 result.params = this.params; 3745 result.sql = this.dbr.getSQL(); 3746 aux.error_callback(this.callback,"LOAD_FILE: error in inserting last record "+this.linecount+", statement: \""+result.sql+"\", params["+result.params.length+"]",result); 3747 return; 3748 } 3749 this.dbconnection.commit(this); // ... perform commit 3750 }, 3751 // step 8: commit complete, release connection to database 3752 function(err) { 3753 if (err) { 3754 aux.error_callback(this.callback,err,"LOAD_FILE: Error in commit",null); 3755 return; 3756 } 3757 // logger.debug("LOAD_FILE.INSERT_COMMITED.LINECOUNT="+this.linecount); 3758 this.dbconnection.release(this); // ... release the database connection 3759 }, 3760 // step 9: release complete, return result to callback 3761 function(err) { 3762 if (err) { 3763 aux.error_callback(this.callback,err,"LOAD_FILE/8: Error in release",null); 3764 return; 3765 } 3766 // put information in the result object to be passed to caller 3767 this.result = {resulttype: 'string', body: "LOAD_FILE: "+this.linecount+" records loaded into table "+this.options.tablename}; 3768 if (this.emptycellcount>0) this.result.title+=", "+this.emptycellcount+" empty cells filled with empty string"; 3769 if (this.trunccellcount>0) this.result.title+=", "+this.trunccellcount+" cells being too long truncated"; 3770 this.callback(undefined,this.result); 3771 } 3772 // end of flow LOAD_FILE 3773 ); 3774 3775 /* ***************************************************************************** 3776 * FLOW: create_table 3777 * INPUT: tabnam - name of table to be created 3778 * columns - [rows] object with column informations 3779 * must have columns: 3780 * index 0 - TABLE_NAME 3781 * index 1 - COLUMN_NAME 3782 * index 2 - DATA_TYPE 3783 * index 3 - DATA_LENGTH 3784 * rfunc - callback to be called upon completion 3785 * PARAMETERS: 3786 * err - [error] object, null if no error 3787 * result - [result] object in case of error 3788 * DESCRIPTION: creates table by executing following SQL command: 3789 * "CREATE TABLE <tabnam> (<coldef>{,<coldef>})" 3790 * <coldef> ::= <column_name> " " <data_type>(<data_length>) 3791 */ 3792 var create_table = flow.define( 3793 // step 1: prepare and issue create statement 3794 function(tabnam,columns,rfunc) { 3795 this.tabnam = tabnam; 3796 this.columns = columns; 3797 this.callback = rfunc; 3798 this.cresql = "CREATE TABLE "+tabnam+" ("; 3799 var first = true; 3800 var typnam; 3801 var addwitdh; 3802 var dbtype = db.getCurrentConfig().getType(); 3803 for (var ci = 0; ci<columns.length; ci++) { 3804 addwidth = true; 3805 if (columns[ci][0]==tabnam) { 3806 if (!first) this.cresql += ", "; 3807 first = false; 3808 typnam = columns[ci][2]; 3809 colwidth = columns[ci][3]; 3810 switch (typnam) { 3811 case "NUMBER": 3812 switch (dbtype) { 3813 case "oracledb": 3814 typnam = "NUMBER"; 3815 break; 3816 case "mysql": 3817 typnam = "NUMERIC"; 3818 break; 3819 } 3820 break; 3821 case "TIMESTAMP(6)": 3822 case "TIMESTAMP": 3823 switch (dbtype) { 3824 case "oracledb": 3825 typnam = "TIMESTAMP"; 3826 break; 3827 case "mysql": 3828 typnam = "TIMESTAMP"; 3829 addwidth = false; 3830 break; 3831 } 3832 break; 3833 case "varchar2": 3834 case "VARCHAR2": 3835 case "varchar": 3836 case "VARCHAR": 3837 switch (dbtype) { 3838 case "oracledb": 3839 typnam = "VARCHAR2"; 3840 break; 3841 case "mysql": 3842 typnam = "VARCHAR"; 3843 if (colwidth && colwidth>255) { 3844 if (colwidth<65535) { // 2**16-1 i.e. 2-byte length field 3845 typnam = "TEXT"; 3846 addwidth = false; 3847 } else if (colwidth<1048575) { // 2**24-1 i.e. 3-byte length field 3848 typnam = "MEDIUMTEXT"; 3849 addwidth = false; 3850 } else { 3851 typnam = "LONGTEXT"; 3852 addwidth = false; 3853 } 3854 } 3855 break; 3856 } 3857 break; 3858 } 3859 this.cresql += (columns[ci][1] + " " + typnam) 3860 if (addwidth) this.cresql += "(" + columns[ci][3] + ")"; 3861 } 3862 } 3863 this.cresql += ")"; 3864 this.credbr = new db.Request({ 3865 info: "creating table "+this.tabnam, 3866 reqid: "RQID_CRETAB_"+this.tabnam, 3867 sql: this.cresql, 3868 params: [], 3869 options: {notranslate: true} 3870 }); 3871 db.select(this.credbr,this); 3872 }, 3873 // step 2: check result of create statement 3874 function(err,result) { 3875 if (err) { 3876 result = result || {}; 3877 result.sql = this.cresql; 3878 aux.error_callback(this.callback,err,"Error creating table "+this.tabnam,result); 3879 return; 3880 } 3881 this.callback(null,result); 3882 } 3883 // end of flow create_table 3884 ); 3885 3886 3887 /* ***************************************************************************** 3888 * 3889 * FLOW: load_ilv_yearfile 3890 * INPUT: sess - session object to check authorization credentials 3891 * mfileid - managed file id to load data from 3892 * rfunc - callback function to be called when complete 3893 * pfunc - callback function to be called on progress report 3894 * DESCRIPTION: loads all ILV data from mfile <mfileid> into table CCILVD 3895 * <mfileid> file is assumed to be an ITSV accounting Excel workbook, 3896 * containing work accounting (ILV) data for one person for one year 3897 * before loading, all already existing data for this person and this year is purged 3898 * the workbook is assumed to be of the following structure 3899 * - data for each month is to be in one sheet, named accoring to the month (german, "Jänner" thru "Dezember") 3900 * - there is one row for each day, starting at row "7" 3901 * - there is one column for each work package, starting at column "D" thru "S" 3902 * - cell at row "5" in each column contains work detail description 3903 * - cell at row "6" in each column contains the PSP-Element for the work package 3904 * - cells "D7" thru "S37" contain work effort in Ph for the respective day and work package 3905 * if the work effort is not zero, an entry in CCILVD for the respective year,person,work package is created 3906 */ 3907 var load_ilv_yearfile = flow.define( 3908 // 3909 // step 1: collect all info about the managed file 3910 // 3911 function(sess,mfileid,rfunc,pfunc) { 3912 this.sess = sess; 3913 this.mfileid = mfileid; 3914 this.callback = rfunc; 3915 this.pcallback = pfunc; 3916 get_mfile_data(this.mfileid,this,this.pcallback); 3917 }, 3918 // 3919 // step 2: load whole XLSX file into memory 3920 // 3921 function(err,result) { 3922 if (err) { 3923 aux.error_callback(this.callback,err,'error getting MFILE info for MFILE ID='+this.mfileid); 3924 return; 3925 } 3926 this.mfileinfo = result; 3927 this.mfileinfo.mfileattributes = {}; 3928 if (this.mfileinfo.mfileattribs.charAt(0)=='{') { 3929 try { 3930 this.mfileinfo.mfileattributes = JSON.parse(this.mfileinfo.mfileattribs); 3931 } catch (e) { 3932 aux.error_callback(this.callback,e, 3933 "Error JSON-parsing MFILE-Attributes(\""+this.mfileinfo.mfileattribs+"\") of MFILE ID="+this.mfileid); 3934 return; 3935 } 3936 } 3937 load_file(this.sess,this.mfileid,this,{ disposition: 'memorize'}); 3938 }, 3939 // 3940 // step 2: check result of loading and check consistence of workbook 3941 // 3942 function(err,result) { 3943 if (err) { 3944 aux.error_callback(this.callback,err,'error in loading XLSX MFILE with ID='+this.mfileid+' into memory',result); 3945 return; 3946 } 3947 if (result.resulttype!='XLSWorkbook') { 3948 aux.error_callback(this.callback,null,"result of reading MFILE ID=\""+this.mfileid+"\" shall be \"XLSWorkbook\" but is \""+result.resulttype+"\"",result); 3949 return; 3950 } 3951 if (!result.xlsworkbook) { 3952 aux.error_callback(this.callback,null,"result of reading MFILE ID="+this.mfileid+" shall contain [xlsworkbook] attribute but doesn't",result); 3953 return; 3954 } 3955 this.workbook = result.xlsworkbook; 3956 // check if all month sheets are there and where they are 3957 var mslist=['Jänner','Februar','März','April','Mai','Juni','Juli','August','September','Oktober','November','Dezember']; 3958 var msidx = {}; 3959 for (var mi=0; mi<mslist.length; mi++) { 3960 msidx[mslist[mi]] = { sheetnumber: -1 }; 3961 } 3962 for (var sni = 0; sni<mslist.length; sni++) { 3963 var si = -1; 3964 var ssi = 0; 3965 while ((si<0) && (ssi<this.workbook.SheetNames.length)) { 3966 if (this.workbook.SheetNames[ssi]==mslist[sni]) { 3967 si = ssi; 3968 msidx[mslist[sni]].sheetnumber = ssi; 3969 break; 3970 } else { 3971 ssi++; 3972 } 3973 } 3974 if (si<0) { 3975 aux.error_callback(this.callback,null,"Month-Sheet \""+mslist[sni]+"\" is missing from MFILE ID=\""+this.mfileid+"\"",result); 3976 return; 3977 } 3978 } 3979 // check and read each month sheet 3980 // first, determine USKURZZ and JAHR, either from attributes or from original file name 3981 this.uskurzz = '??Mitarbeiter??'; 3982 this.jahr = '??Jahr??'; 3983 var extdotpos = this.mfileinfo.mfilename.indexOf(".",-1); 3984 if (extdotpos>0) { 3985 this.mfilenamebase = this.mfileinfo.mfilename.substring(0,extdotpos); 3986 } else { 3987 this.mfilenamebase = this.mfileinfo.mfilename; 3988 } 3989 // USKURZZ is taken either from mfileattribute 'uskurzz' or from first part of file name 3990 this.mfileinfo.mfilenamesegs = this.mfilenamebase.split('_'); 3991 logger.debug("..MFILENAMESEGS:"); 3992 logger.debug(this.mfileinfo.mfilenamesegs); 3993 if (this.mfileinfo.mfileattributes.uskurzz) { 3994 this.uskurzz = this.mfileinfo.mfileattributes.uskurzz; 3995 } else { 3996 if (this.mfileinfo.mfilenamesegs.length>0) { 3997 this.uskurzz = this.mfileinfo.mfilenamesegs[0]; 3998 } 3999 } 4000 // JAHR is taken from either mfileattribute 'jahr' or from last part of file name 4001 if (this.mfileinfo.mfileattributes.jahr) { 4002 this.jahr = this.mfileinfo.mfileattributes.jahr; 4003 } else { 4004 if (this.mfileinfo.mfilenamesegs.length>1) { 4005 this.jahr = this.mfileinfo.mfilenamesegs[this.mfileinfo.mfilenamesegs.length-1]; 4006 } 4007 } 4008 // deliver result as dbresult with columns: JAHR,MONAT,TAG,PSP,PSPDETAIL,USKURZZ,STUNDEN 4009 this.result = {resulttype: 'dbresult'}; 4010 this.result.metaData = new Array(); 4011 this.result.rows = new Array(); 4012 var colnames = ['JAHR','MONAT','TAG','PSP','PSPDETAIL','USKURZZ','STUNDEN']; 4013 for (var ci=0; ci<colnames.length; ci++) { 4014 this.result.metaData.push({name:colnames[ci]}); 4015 } 4016 for (var si=0; si<mslist.length; si++) { // loop level 1: MONAT, <si> iterates from 0..11, representing from "Jänner" thru "Dezember" in <mslist> 4017 this.worksheet = this.workbook.Sheets[mslist[si]]; // pick the sheet named like the month in <mslist> 4018 var xta = XLSX.utils.encode_cell({c: 0, r: 4}); 4019 if (!((this.worksheet[xta]) && (this.worksheet[xta].v=='Tag'))) { 4020 aux.error_callback(this.callback,null,'Cell '+xta+' in sheet '+mslist[si]+' of MFILE ID='+this.mfileid+' shall be "Tag", but is not',result); 4021 return; 4022 } 4023 xta = XLSX.utils.encode_cell({c: 0, r: 37}); 4024 if (!((this.worksheet[xta]) && (this.worksheet[xta].v=='Summe'))) { 4025 aux.error_callback(this.callback,null,'Cell '+xta+' in sheet '+mslist[si]+' of MFILE ID='+this.mfileid+' shall be "Summe", but is not',result); 4026 return; 4027 } 4028 var psps = new Array(); 4029 var pta; 4030 var pda; 4031 var dca; 4032 var drow; 4033 var psp; 4034 var pspd; 4035 var std; 4036 for (pspi=3; pspi<17; pspi++) { // loop level 2: Work-Packages, described by PSP-Element+Tätigkeit 4037 pta = XLSX.utils.encode_cell({c: pspi, r: 5}); // address of cell containing PSP-Element 4038 pda = XLSX.utils.encode_cell({c: pspi, r: 4}); // address of cell containing Tätigkeit 4039 if (this.worksheet[pta] && this.worksheet[pda]) { // consider this column only if BOTH PSP-Element and Tätigkeit are present 4040 psp = this.worksheet[pta].v; // get PSP-Element (PSP) for all days for this work package 4041 if (typeof(psp)==="string") { psp = psp.trim(); } 4042 pspd = this.worksheet[pda].v; // get Tätigkeit (PSPDETAIL) for all days for this work package 4043 if (typeof(pspd)==="string") { pspd = pspd.trim(); } 4044 for (var tag = 1; tag<32; tag++) { // loop level 3: days in month 4045 dca = XLSX.utils.encode_cell({c: pspi, r: 5+tag}); 4046 if (this.worksheet[dca]) { // consider the entry for this day if there is content in the day's cell for this PSP-Element/Tätigkeit 4047 std = this.worksheet[dca].v + 0.0; 4048 if (std>0.0) { // create entry only if any time accounted 4049 drow = new Array(); 4050 drow.push(this.jahr); // JAHR 4051 drow.push(si+1); // MONAT 4052 drow.push(tag); // TAG 4053 drow.push(psp); // PSP 4054 drow.push(pspd); // PSPDETAIL 4055 drow.push(this.uskurzz); // USKURZZ 4056 drow.push(std); // STUNDEN 4057 this.result.rows.push(drow); 4058 } 4059 } 4060 } 4061 } 4062 } 4063 } 4064 // initiate purging old data before inserting new data 4065 this.cdbr = new db.Request({ info: "PURGE_OLD_YEARDATA", 4066 reqid: "__NOXLATE_RQID_PURGE_OLD_YEARDATA", 4067 sql: "delete from CCILVD where JAHR=:JAHR and USKURZZ=:URKURZZ", 4068 sql_mysql: "delete from CCILVD where JAHR=? and USKURZZ=?", 4069 params: [this.jahr,this.uskurzz], 4070 options: {} 4071 }); 4072 db.select(this.cdbr,this); 4073 }, 4074 function(err,result) { 4075 if (err) { 4076 aux.error_callback(this.callback,err,'Error purging old yeardata for JAHR='+this.jahr+' and USKURZZ='+this.uskurzz,result); 4077 return; 4078 } 4079 // prepare for inserting all rows in result to CCILVD 4080 this.idbr = new db.Request({ info: "LOAD_YEARFILE", 4081 reqid: "__NOXLATE_RQID_LOAD_YEARFILE", 4082 sql: "insert into CCILVD(JAHR,MONAT,TAG,PSP,PSPDETAIL,USKURZZ,STUNDEN) values(:JAHR,:MONAT,:TAG,:PSP,:PSPDETAIL,:USKURZZ,:STUNDEN)", 4083 sql_mysql: "insert into CCILVD(JAHR,MONAT,TAG,PSP,PSPDETAIL,USKURZZ,STUNDEN) values(?,?,?,?,?,?,?)", 4084 params: [], 4085 options: {} 4086 }); 4087 this.irow = 0; 4088 // now enter loop for inserting new year data 4089 this(); 4090 }, 4091 function(err,result) { 4092 if (err) { 4093 logger.error("Errordata: row "+this.irow+" of "+this.result.rows.length+" total"); 4094 logger.error(this.result.rows[this.irow-1]); 4095 aux.error_callback(this.callback,err,'Error inserting row '+this.irow+' into table CCILVD',result); 4096 return; 4097 } 4098 if (this.irow<this.result.rows.length) { // more rows to insert 4099 this.idbr.setParams(this.result.rows[this.irow]); 4100 this.irow++; 4101 this.REWIND(); 4102 db.select(this.idbr,this); 4103 } else { 4104 this.callback(null,{resulttype:'plain',body:this.irow+' Zeilen in Tabelle CCILVD eingefügt'}); 4105 } 4106 }); 4107 4108 /* **************************************************************************** 4109 * 4110 * FUNCTION: doInAdminSession 4111 * INPUT: action - action to be performed in ADMIN session 4112 * DESCRIPTION: enqueues <action> in the ADMIN sessions' action queue to be performed A.S.A.P. 4113 */ 4114 4115 function doInAdminSession(action) { 4116 aux.getQueue("ADMIN").put(action); 4117 } 4118 4119 /* **************************************************************************** 4120 * The module interface 4121 */ 4122 4123 var localexportobjects = { 4124 save_to_mfile : save_to_mfile, 4125 save_db_xls : save_db_xls, 4126 cregrw : cregrw, 4127 edit : edit, 4128 flowsequence : flowsequence, 4129 Mobject : Mobject, 4130 doInAdminSession : doInAdminSession, 4131 Action : Action, 4132 load_file : load_file, 4133 load_dcresweekly : load_dcresweekly, 4134 get_mfile_data : get_mfile_data, 4135 create_table : create_table, 4136 check_authorization : check_authorization, 4137 load_ilv_yearfile : load_ilv_yearfile 4138 } 4139 4140 4141 module.exports = function(theLogger,theMasterName,thePrefs) { 4142 logger = theLogger; 4143 mastername = theMasterName; 4144 prefs = thePrefs; 4145 /* 4146 logger.debug("this is the instantiation followup of "+__filename+", the master is "+mastername+"/"+theMasterName); 4147 var c = 0; 4148 var oal = ""; 4149 for (var oa in module.exports) { 4150 oal += ((c<1)?"":",")+oa; 4151 c++; 4152 } 4153 logger.debug("module.exports has "+c+" attributes: "+oal); 4154 */ 4155 return module.exports; 4156 } 4157 4158 for (var oa in localexportobjects) { 4159 module.exports[oa] = localexportobjects[oa]; 4160 }