#
#		ITSV GmbH
#	CCDB - Command and Control Database
#
#	FILE:			dquerymfile_LOADSVCLZUOE.txt
#	DESCRIPTION:	DQUERY definition for DQUERY LOADSVCLZUOE
#
@querytitle			Datei mit Clearing-Zuordnungs-Endedaten laden
@querydescription	lädt eine managed Datei mit Clearing-Zuordnungs-Endedaten in die Tabelle SVCLZUOE
@group				SVCLCASETOOLS
@attributenames		mfileid:mfileid:{{mfileidoptions}}
@mfileidoptions		{"labeltext":"Managed File","typedesc":"Managed File, das in die Tabelle geladen werden soll"}
@querytype			function
@function			seqtrans.seqtrans
@FLIST				ELDASNR,BKNR,TRAEGER,INHPARID,INHROLLE,ANMERKUNG,AKTIVBISDATNUM,INPUTLINE
@TLIST				11:WGKK,12:NÖGKK,13:BGKK,14:OÖGKK,15:STGKK,16:KGKK,17:SGKK,18:TGKK,19:VGKK,5:VAEB

~query.tsteps

#
# <<TSTEPNUM:0>>: check if already set up
#
if			(this.set_up)
goto		record_proper

#
# <<TSTEPNUM:1>>: prepare global vars
#
qexpression
	proc: {
		this.FLIST = this.query.FLIST;
		this.FTLIST = "::"+this.query.FLIST.split(",").join("::,::")+"::";
		this.datrexs = new Array();
		this.datrexs.push({ rex: /([0-9]{1,2})\.([0-9]{1,2})\.([0-9]{1,4})\s([0-9]{1,2})\:([0-9]{1,2})/, yindex: 3, mindex: 2, dindex: 1, hhindex: 4, mmindex: 5, minlen: 4 }); /* DD.MM.YYYY hh:mm ... */
		this.datrexs.push({ rex: /([0-9]{1,4})\-([0-9]{1,2})\-([0-9]{1,2})\s([0-9]{1,2})\:([0-9]{1,2})/, yindex: 1, mindex: 2, dindex: 3, hhindex: 4, mmindex: 5, minlen: 4 }); /* YYYY-MM-DD hh:mm ... */
		let tas, ct;
		let tds = this.query.TLIST.split(",");
		this.ts = {};
		for (let ti=0; ti<tds.length; ti++) {
			tas = tds[ti].split(":");
			if (tas.length<2) {
				this.errcoll.collect("Error in TLIST: record "+ti+" has too few parts");
				break proc;
			}
			ct = tas[0];
			this.ts[ct] = { traeger: ct, aliases: new Array() };
			this.ts[ct].aliases = tas[1].split(";");
		}
		this.findTraeger = function(kurzz) {
			for (tnam in this.ts) {
				for (let ai=0; ai<this.ts[tnam].aliases.length; ai++) {
					if (kurzz==this.ts[tnam].aliases[ai]) return this.ts[tnam];
				}
			}
			return null;
		}
	}

#
# <<TSTEPNUM:2>>: create table if not yet exists
#
sql			create table if not exists SVCLZUOE (ELDASNR varchar(32), BKNR varchar(32), TRAEGER varchar(2), INHPARID varchar(32), INHROLLE varchar(1), ANMERKUNG varchar(255), AKTIVBISDATNUM varchar(32), INPUTLINE varchar(1024))

#
# <<TSTEPNUM:3>>: log start
#
qexpression
	this.recstoprocess = this.inputresult.getRowCount();
	this.recsprocessed = 0;
	this.ppush([this.phead("STARTED"),"RECSTOPROCESS="+this.recstoprocess,"",""]);
	this.set_up = true;

#
# <<TSTEPNUM:4>>: prepare field contents
#
record_proper:
qexpression
	proc: {
		this.cdata.ELDASNR 	= this.cdata.seriennummer;
		this.cdata.BKNR		= this.cdata.bknr;
		let traeger = this.findTraeger(this.cdata.traegercode);
		if (!traeger) {
			this.errcoll.collect("Traegercode "+this.cdata.traegercode+" not recognized");
			break proc;
		}
		let datmatch, cmc, bisdatnum;
		matchloop: {
			for (let ri=0; ri<this.datrexs.length; ri++) {
				cmc = this.datrexs[ri];
				datmatch = cmc.rex.exec(this.cdata.aktiv_bis);
				if (datmatch && (datmatch.length>=cmc.minlen)) {
					if (!this.datmatchcounters) this.datmatchcounters = new Array();
					while (this.datmatchcounters.length<=ri) this.datmatchcounters.push(0);
					this.datmatchcounters[ri]++;
					bisdatnum = aux.DEC(datmatch[cmc.yindex],4,4) + aux.DEC(datmatch[cmc.mindex],2,2) + aux.DEC(datmatch[cmc.dindex],2,2);
					break matchloop;
				}
			}
			this.errcoll.collect("Error in record "+this.recnum+": aktiv_bis is malformed: no rex matched: "+aux.objTxt(datmatch), {cdata: this.cdata, recnum: this.recnum});
			break proc;
		}
		this.cdata.TRAEGER			=	traeger.traeger;
		this.cdata.INHPARID			=	this.cdata.inhaber_partnerid;
		this.cdata.INHROLLE			=	this.cdata.inhaber_rolle_id;
		this.cdata.ANMERKUNG		=	this.cdata.anmerkung;
		this.cdata.AKTIVBISDATNUM	=	Number(bisdatnum);
		this.cdata.INPUTLINE		=	this.inputresult.getRow(this.recnum).join(";");
	}

#
# <<TSTEPNUM:5>>: insert one record into database table
#
sql				insert into SVCLZUOE(@@FLIST@@) values (@@FTLIST@@)
named_params	@@FLIST@@
post_qexpression
	this.recsprocessed++;

#
# <<TSTEPNUM:6>>: check if this was the last record
#
if 		(!this.is_last_record)
goto 	record_end

#
# <<TSTEPNUM:7>>: log finish
#
qexpression
	this.ppush([this.phead("FINISHED"),"RECSPROCESSED="+this.recsprocessed,"DATMATCHCOUNTERS="+aux.objTxt(this.datmatchcounters),""]);
	
#
# <<TSTEPNUM:8>>: end of prcessing
#
record_end:
end