#
#		ITSV GmbH
#	CCDB - Command and Control Database
#
#	FILE:			dquerymfile_flexloadtable
#	DESCRIPTION:	DQUERY definition for query flexloadtable
#
@querytitle			Lade Tabelle flexibel aus managed file		
@querydescription	Flexibles Laden einer Tabelle aus einem Managed File
@group				DATAFILE
@attributenames		mfileid:mfileid:{{mfileidoptions}},tablename:string:{{tablenameoptions}}
@mfileidoptions		{"labeltext":"Ladedatei","typedesc":"ID des managed file, das in die Tabelle geladen werden soll"}
@tablenameoptions	{"labeltext":"Tabelle","typedesc":"Name der Tabelle, in die der Inhalt der Datei geladen werden soll"}
@querytype			function
@function			seqtrans.seqtrans

~query.tsteps

#
# <<TSTEPNUM:0>>: initialize
#
if		(this.init_done)
goto	record_proper

#
# <<TSTEPNUM:1>>: initialize file info from inputresult
#
qexpression
	proc: {
		copycvars(this,"MFILEID,MFILETYPE,MFILEATTRIBS");
		this.recstoprocess = this.inputresult.getRowCount();
		this.ppfx = this.fname+"("+this.dataname+")";
		this.tablename = this.query.tablename;
		let cc, cnam;
		this.fcols = {};
		this.fnames = new Array();
		for (let hi=0; hi<this.inputresult.metaData.length; hi++) {
			cc = this.inputresult.metaData[hi];
			if (cc.name) {
				cnam = cc.name;
			} else {
				cnam = "Column"+hi;
			}
			this.fcols[cnam] = { column: hi };
			this.fnames.push(cnam);
		}
		this.FLIST = this.fnames.join(",");
		this.FFLIST = "::" + this.fnames.join("::,::") + "::";
		this.loadcontrols = {};
		this.loadcontrol = {};
	}

#
# <<TSTEPNUM:2>>: 	get information about table to load
#
aexpression
	logger.debug("DB: "+aux.objTxt(db));
	db.analyzeCCDBtable(this.tablename,{},this);
result_varname		descres
post_qexpression
	proc: {
		if (this.descres.resulttype!='tabdesc') {
			this.errcoll.collect(null,"analyze table for "+this.query.tablename+" did not produce tabdesc",this.descres);
			break proc;
		}
		this.tcols = {};
		this.tnames = new Array();
		let cc, cnam;
		for (let ci=0; ci<this.descres.columns.length; ci++) {
			cc = this.descres.columns[ci];
			this.tcols[cc.name] = { name: cc.name, column: ci, type: cc.type, width: cc.width, decimals: cc.decimals };
			this.tnames.push(cc.name);
		}
		this.TLIST  = this.tnames.join(",");
		this.TFLIST = "::" + this.tnames.join("::,::") + "::";
		ppush(this,[this.ppfx+"_STARTED","RECSTOPROCESS="+this.recstoprocess,"FILE_COLUMNS: "+this.FLIST, "TABLE_COLUMNS: "+this.TLIST]);
		this.recsprocessed = 0;
		this.recsskipped = 0;
	}

#
# <<TSTEPNUM:3>>:	find table-specific or general MFILE with control information
#
pre_qexpression
	this.gcmfid = 'rwobj_loadflex';
	this.scmfid = 'rwobj_loadflex'+this.tablename;
sql				select MFILEID,MFILEATTRIBS from CCMFILES where MFILETYPE='rwobj' and MFILEID in (::scmfid::,::gcmfid::)
named_params	gcmfid,scmfid
result_varname	cmfres
post_qexpression
	this.cmfid = null;
	this.cmfattribs = null;
	for (let ri=0; ri<this.cmfres.getRowCount(); ri++) {
		if (this.cmfres.getCellValue(ri,0)==this.scmfid) {
			this.cmfid = this.cmfres.getCellValue(ri,0);
			this.cmfattributes = aux.txtObjDecode(this.cmfres.getCellValue(ri,1));
		}
	}
	if (!this.cmfid) {
		for (let ri=0; ri<this.cmfres.getRowCount(); ri++) {
			if (this.cmfres.getCellValue(ri,0)==this.gcmfid) {
				this.cmfid = this.cmfres.getCellValue(ri,0);
				this.cmfattributes = aux.txtObjDecode(this.cmfres.getCellValue(ri,1));
			}
		}
	}

#
# <<TSTEPNUM:4>>:	check if control MFILE found
#
if		(!this.cmfid)
goto	skip_cmf

#
# <<TSTEPNUM:5>>:	load and compile control MFILE
#
aexpression
	objcomp.compileMfile(this.cmfid,this.loadcontrols,{},this);
post_qexpression
	logger.debug("flexloadtable.UDBG.tablename="+this.tablename+".loadcontrols(from_mfile): "+aux.objTxt(this.loadcontrols));
	if (this.loadcontrols[this.tablename]) {
		this.loadcontrol = this.loadcontrols[this.tablename];
	}

#
# <<TSTEPNUM:6>>:	init done
#
skip_cmf:
qexpression
	this.init_done = true;

#
# <<TSTEPNUM:7>>: check if record shall be inserted
#
record_proper:
pre_qexpression
	this.skip_record = false;
	if (this.loadcontrol._skip_condition) {
		if (this.loadcontrol._skip_condition.qexpression) {
			this.skip_record = eval(this.loadcontrol._skip_condition.qexpression);
		}
	}
	if (this.skip_record) this.recsskipped++;
if 		(this.skip_record) 
goto	record_done

#
# <<TSTEPNUM:8>>: determine if update or insert
#
pre_qexpression
	proc: {
		this.db_oper = "INSERT";
		if (this.loadcontrol._keep_unique) {
			if (this.loadcontrol._keep_unique.check_sql) {
				this.check_sql = this.loadcontrol._keep_unique.check_sql;
				if (this.loadcontrol._keep_unique.pre_check) {
					eval(this.loadcontrol._keep_unique.pre_check);
				}
			}
			if (this.check_sql) {
				this.db_oper = "CHECK";
			} else {
				this.errcoll.collect(null,"no check_sql specified in _keep_unique of loadcontrol for table "+this.tablename,this.loadcontrol);
				break proc;
			}
		}
	}
goto do_@@db_oper@@

#
# <<TSTEPNUM:9>>: check if record already there: execute check sql statement
#
do_CHECK:
sql				@@check_sql@@
named_params	@@TLIST@@
result_varname	checkres

#
# <<TSTEPNUM:10>>: check if record already there: check checkselect result
#
pre_qexpression
	this.db_oper = "INSERT";
	if (this.loadcontrol._keep_unique.post_check) {
		eval(this.localcontrol._keep_unique.post_check);
	} else {
		if (this.checkres.getRowCount()>0) {
			this.db_oper = "UPDATE";
		}
	}
goto do_@@db_oper@@

#
# <<TSTEPNUM:11>>: update existing record
#
do_UPDATE:
pre_qexpression
	proc: {
		if (this.loadcontrol._keep_unique.update_sql) {
			this.update_sql = this.loadcontrol._keep_unique.update_sql;
		} else {
			this.errcoll.collect(null,"no update_sql specified in _keep_unique of loadcontrol",this.loadcontrol);
			break proc;
		}
	}
sql				@@update_sql@@
named_params	@@TLIST@@
post_qexpression
	this.recsprocessed++;

#
# <<TSTEPNUM:12>>: update complete
#
goto	record_done
		
#
# <<TSTEPNUM:13>>:	insert one record
#
do_INSERT:
pre_qexpression
	proc: {
		let cname, ctc, clc;
		for (let ci=0; ci<this.tnames.length; ci++) {
			cname = this.tnames[ci];
			ctc = this.tcols[cname];
			if (this.loadcontrol[cname]) {
				clc = this.loadcontrol[cname];
				if (clc.fromcolname) {
					this.cdata[cname] = this.cdata[clc.fromcolname];
				} else if (clc.hasOwnProperty("constvalue")) {
					this.cdata[cname] = clc.constvalue;
				} else if (clc.hasOwnProperty("qexpression")) {
					this.cdata[cname] = eval(clc.qexpression);
				} else {
					this.errcoll.collect(null,"loadcontrol for column \""+cname+"\" in record "+this.recnum+" for table \""+this.tablename+"\" is there, but no valid mechanism to load",
										{ fcols: this.fcols, tcols: this.tcols, loadcontrol: this.loadcontrol });
					break proc;
				}
			} else if (this.fcols[cname]) {
				this.cdata[cname] = getcvar(this,cname);
			} else {
				this.errcoll.collect(null,"No way to get data for column \""+cname+"\" in record "+this.recnum+" for table \""+this.tablename+"\"",
										{ fcols: this.fcols, tcols: this.tcols, loadcontrol: this.loadcontrol });
				break proc;
			}
		}
	}
sql				INSERT into  @@tablename@@(@@TLIST@@) VALUES(@@TFLIST@@)
named_params	@@TLIST@@
post_qexpression
	this.recsprocessed++;

#
# <<TSTEPNUM:14>>: insert done
#
goto	record_done

#
# <<TSTEPNUM:15>>: skip record, nothing changed
#
do_SKIP:
pre_qexpression
	this.recsskipped++;
goto	record_done
	
#
# <<TSTEPNUM:14>>: record done, check if last record
#
record_done:
if		(!this.is_last_record)
goto	done

#
# <<TSTEPNUM:15>>: finish up protocol
#
qexpression
	ppush(this,[this.ppfx+"_FINISHED","RECSTOPROCESS="+this.recstoprocess,"RECSPROCESSED="+this.recsprocessed,"RECS_SKIPPED="+this.recsskipped]);

#
# <<TSTEPNUM:16>>: end of record
#
done:
end