#
#		ITSV GmbH
#	CCDB - Command and Control Database
#
#	FILE:			dquerymfile_LLOADCFENZ.txt
#	DESCRIPTION:	dquery definition for LOADCFENZ
#
#	HISTORY:		20200910	WSC		add "rueckfrageinfo"
# 
@group				SVCLCFSTATCFENZ
@querytitle 		DGD-Auswertung zu ELDA-nicht-zustellbar-Clearingfälle laden
@querydescription	lädt die monatliche Auswertung über ELDA-nicht-zustellbar-Clearingfälle in die Tabelle CCCFUNZ
@attributenames		mfileid:mfileid:{{mfileidoptions}}
@mfileidoptions		{"labeltext":"Lade-Managed-File ID","typedesc":"Managed File ID der Datei, die geladen werden soll, diese muss den MFILETYPE \"DGDCFENZ\" haben"}
@input_encoding		iso-8859-15
@querytype			function
@function			seqtrans.seqtrans
@FLIST				repdatnum,traeger,parparrolleid_partner,partnerrollenart_kurz,bknr,vsnr,status,referenzwert,einlange_dat,xpath		
@TLIST				repdatnum,traeger,parparrolleid_partner,partnerrollenart_kurz,bknr,vsnr,status,referenzwert,einlange_dat,rueckfrageinfo,zuordnungsinfo
@TYLIST				varchar(32),varchar(2),varchar(32),varchar(32),varchar(10),varchar(10),varchar(20),varchar(70),varchar(19),varchar(1024),varchar(4096)
@SVTLIST			05:BVAEB-EB,11:ÖGK-W,12:ÖGK-N,13:ÖGK-B,14:ÖGK-O,15:ÖGK-ST,16:ÖGK-K,17:ÖGK-S,18:ÖGK-T,19:ÖGK-V
@init.qexpression
	this.load_file = load_file;
	this.csv_rep_func = function(linetext,repres) {
		let rp = new Array();
		fldarr = this.load_file.splitCSVline(linetext,{linesep: ";", quotechar: "\""});
		rp.push("LOADCFENZ: Trying to repair fields: "+aux.objTxt(fldarr));
		if (fldarr.length<5) {
			rp.push("too few fields, no repair possible");
			if (repres) repres.info = rp.join("\n");
			return linetext;
		}
		if (fldarr.length>5) { 
			/* if #4 is no valid Datum, referenzwert may be split up by one or more ";" in it */
			let rex = /[0-9]{1,4}\-[0-9]{1,2}\-[0-9]{1,2}\s.+/;
			while ((fldarr.length>5) && (fldarr[4]!="") && (!rex.test(fldarr[4]))) {
				rp.push("record has still "+fldarr.length+" fields, fld#4 is no valid DATUM/ZEIT (and also not empty), try merging #3+#4");
				let rep = fldarr[3]+"%3B"+fldarr[4];
				fldarr.splice(4,1);
				fldarr[3] = rep;
				linetext = fldarr.join(";");
			}
		}
		if (repres) repres.info = rp.join("\n");
		return linetext;
	}.bind(this);

~query.tsteps

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

#
# <<TSTEPNUM:1>>: initialization: get MFILE info
#
qexpression
	proc: {
		this.progstat.current_mfileid = this.cdata.MFILEID;
		if (!this.init_done) {
			this.FLIST = this.query.FLIST;
			this.tfnames = this.FLIST.split(",");
			this.FVLIST = "::" + this.tfnames.join("::,::") + "::";
			this.TLIST = this.query.TLIST;
			this.ttnames = this.TLIST.split(",");
			this.TVLIST = "::" + this.ttnames.join("::,::") + "::";
			this.ttypes = this.query.TYLIST.split(",");
			this.svta = this.query.SVTLIST.split(",");
			this.svts = {};
			let fs;
			for (let ti=0; ti<this.svta.length; ti++) {
				fs = this.svta[ti].split(":");
				this.svts[fs[0]] = { name: fs[1] };
			}
			if (this.ttnames.length!=this.ttypes.length) {
				this.errcoll.collect(null,this.phead("TTYNAMENOTTTYPES"),"ttnmes and ttypes are different in length");
				break proc;
			}
			if (!this.mfileinfo) {
				this.errcoll.collect(null,"no MFILEINFO");
				break proc;
			}
			if (!this.mfileinfo.mfileid) {
				this.errcoll.collect(null,"MFILEINFO has no MFILEID",this.mfileinfo);
			}
			this.load_mfileid = this.mfileinfo.mfileid;
			if (!this.mfileinfo.mfilename) {
				this.errcoll.collect(null,"MFILEINFO has no MFILENAME attribute",this.mfileinfo);
				break proc;
			}
			this.load_mfileattribs = this.mfileinfo.mfileattribs;
			this.load_mfileattributes = aux.txtObjDecode(this.load_mfileattribs) || {};
			if (!this.load_mfileattributes.Berichtsdatum) {
				this.errcoll.collect(null,this.phead("NOBERICHTSDATUM_no_Berichtsdatum_in LOAD_file_attribute"),{ mfilinfo: this.mfileinfo, load_mfileattributes: this.load_mfileattributes});
				break proc;
			}
			if (this.load_mfileattributes.loadstatus && this.load_mfileattributes.loadstatus=="loaded") {
				this.errcoll.collect(null,"LOAD_MFILE "+this.load_mfileid+" is already loaded",{ mfilinfo: this.mfileinfo, load_mfileattributes: this.load_mfileattributes});
				break proc;
			}
			this.mfnparts = this.mfileinfo.mfilename.split(".");
			if (this.mfnparts.length<2) {
				this.errcoll.collect(null,"MFILENAME does not have at least 2 parts, separated by '.'",this.mfileinfo);
				break proc;
			}
			if ((this.mfnparts[1]!="csv") && (this.mfnparts[1]!="xlsx")) {
				this.errcoll.collect(null,"MFILENAME does not signify CSV or XLSX file",this.mfileinfo);
				break proc;
			}
			this.mfnfields = this.mfnparts[0].split("_");
			if (this.mfnfields.length!=5) {
				this.errcoll.collect(null,"MFILENAME does not have 5 fields, separated by '_'",this.mfileinfo);
				break proc;
			}
			if (this.mfnfields[0]!="DGD") {
				this.errcoll.collect(null,"MFILENAME field 1 is not 'DGD'",this.mfileinfo);
				break proc;
			}
			if (this.mfnfields[1]!="Auswertung") {
				this.errcoll.collect(null,"MFILENAME field 2 is not 'Auswertung'",this.mfileinfo);
				break proc;
			}
			if (this.mfnfields[2]!="CF") {
				this.errcoll.collect(null,"MFILENAME field 3 is not 'CF'",this.mfileinfo);
				break proc;
			}
			if (this.mfnfields[3]!="ELDAUNZUSTELLBAR") {
				this.errcoll.collect(null,"MFILENAME field 4 is not 'ELDAUNZUSTELLBAR'",this.mfileinfo);
				break proc;
			}
			let dnrex = /[0-9]{8}/;
			if (!dnrex.test(this.mfnfields[4])) {
				this.errcoll.collect(null,"MFILENAME field 5 is no valid 8-digit date number",{ mfileinfo: this.mfileinfo, field_5: this.mfnfields[4]});
				break proc;
			}
			this.traeger = this.mfnfields[0];
			this.repdatnum = this.mfnfields[4];
			this.ppfx = this.fname+"("+this.dataname+")";
			this.recstoprocess = this.inputresult.getRowCount();
			this.LOADSTARTED = aux.nowstring();
			ppush(this,[this.ppfx+"_started","START_TIME="+this.LOADSTARTED,"MFILEID_TO_LOAD="+this.mfileid,""]);
			this.recsprocessed = 0;
			this.progstat.FILEDATE = this.fdate;
			this.tablename = "CCCFUNZ";
			let flfn = this.query.FLIST.split(",");
			let ifi, rfi;
			for (let fi=0; fi<flfn.length; fi++) {							/* check if input result has all required columns (named in query parameter FLIST) */
				ifi = this.inputresult.findColumn(flfn[fi]);
				if (ifi<0) {												/* inputresult does not have this field, check for possible repairs */
					if (flfn[fi]=="einlange_dat") {							/* we don't have "einlange_dat", check if there is a replacement */
						rfi = this.inputresult.findColumn("erstell_ts");	/* erstell_ts is possible replacement */
						if (rfi>=0) {										/* we have erstell_ts, rename it to "enlange_dat" */
							this.inputresult.setMetaDataName(rfi,"einlange_dat");
							this.cdata = this.inputresult.getRowObject(this.recnum,"value");	/* reload CDATA, as names have changed, for first row */
						} else {
							this.errcoll.collect(null,"input dataset does not have column "+flfn[fi]+", but also no replacement column \"erstell_ts\", no repair possible");
						}
					} else if (flfn[fi]=="vsnr") {							/* we don't have "vsnr", check for possible replacement */
						rfi = this.inputresult.findColumn("VSNR");
						if (rfi>=0) {										/* we don't have "vsnr", but we do have "VSNR", rename it */
							this.inputresult.setMetaDataName(rfi,"vsnr");
							this.cdata = this.inputresult.getRowObject(this.recnum,"value");	/* reload CDATA, as names have changed, for first row */
						} else {											/* we don't have neither "vsnr" nor "VSNR", replace with empty string => add one last empty column */
							let vcolnum = this.inputresult.addMetaDataName("vsnr");
							for (let ri=0; ri<this.inputresult.getRowCount(); ri++) {
								this.inputresult.setCellValue(ri,vcolnum,"");
							}
						}
					} else if (flfn[fi]=="repdatnum") {								/* we don't have "repdatnum", fill in from execution context, taken from uploaded file name */
						let rcolnum = this.inputresult.addMetaDataName("repdatnum");
						for (let ri=0; ri<this.inputresult.getRowCount(); ri++) {
							this.inputresult.setCellValue(ri,rcolnum,this.repdatnum);
						}
					} else if (flfn[fi]=="parparrolleid_partner") {					/* we don't have "parparrolleid_partner", create it with empty value */
						let pidcolnum = this.inputresult.addMetaDataName("parparrolleid_partner");
						for (let ri=0; ri<this.inputresult.getRowCount(); ri++) {
							this.inputresult.setCellValue(ri,pidcolnum,"");
						}
					} else if (flfn[fi]=="partnerrollenart_kurz") {					/* we don't have "partnerrollenart_kurz", create it with empty value */
						let pracolnum = this.inputresult.addMetaDataName("partnerrollenart_kurz");
						for (let ri=0; ri<this.inputresult.getRowCount(); ri++) {
							this.inputresult.setCellValue(ri,pracolnum,"");
						}
					} else if (flfn[fi]=="xpath") {									/* 20200910: we don't have "xpath", create it with empty value */
						let pracolnum = this.inputresult.addMetaDataName("xpath");
						for (let ri=0; ri<this.inputresult.getRowCount(); ri++) {
							this.inputresult.setCellValue(ri,pracolnum,"");
						}
					} else {
						this.errcoll.collect(null,"input dataset does not have column \""+flfn[fi]+"\", no repair possible");
					}
				}
			}
		}
	}

#
# <<TSTEPNUM:2>>: initialization: check if table there (if not, the SQL step will fail and everything will be rolled back)
#
sql					select * from @@tablename@@ limit 1,1
result_varname		cres
catch_errors		true
post_qexpression
	proc: {
		if (this.cres.error) {
			this.errcoll.collect(null,"Error getting infomation about "+this.tablename,{ db_result: this.cres });
			break proc;
		}
		let ci;
		this.missCols = new Array();
		for (let ti=0; ti<this.ttnames.length; ti++) {
			ci = this.cres.findColumn(this.ttnames[ti]);
			if (ci<0) {
				logger.debug(this.phead("MISSCOL")+".MISSING_NAME="+this.ttnames[ti]);
				this.missCols.push({ name: this.ttnames[ti], tt_index: ti });
			}
		}
	}

#
# <<TSTEPNUM:3>>: if missing columns, add them
#
add_next_column:
if		(this.missCols.length<1)
goto	columns_ok

#
# <<TSTEPNUM:4>>: add next missing column
#
pre_qexpression
	let newcolind = this.missCols.shift();
	this.newcolname = newcolind.name;
	this.newcolindex = newcolind.tt_index;
	this.newcoltype = this.ttypes[this.newcolindex];
sql				alter table @@tablename@@ add column @@newcolname@@ @@newcoltype@@
result_varname	ares
post_qexpression
	this.ppush([this.phead("COLADDED"),"ADDED_COLUMN="+this.newcolname+".WITH_TYPE="+this.newcoltype,"TO_TABLE="+this.tablename,{ rawhtml: "<pre>aux.objTxt(this.ares)+"</pre>" }]);
then_goto		add_next_column

#
# <<TSTEPNUM:5>>: init done
#
columns_ok:
qexpression
	this.init_done = true;

#
#  <<TSTEPNUM:6>>: check and prepare record, look up Zuordnungen
#
record_proper:
pre_qexpression
	proc: {
		let tr = String("00" + this.cdata.traeger);
		this.cdata.traeger = tr.substring(tr.length-2);
		let pd = this.cdata.einlange_dat;
		if (!aux.isDate(pd)) {
			pd = aux.dateFromString(this.cdata.einlange_dat);
			if (pd.error) {
				this.cdata.einlange_dat = String(this.cdata.einlange_dat);
				/* 16.07.2021: do not stop on date error to pass data, BEICON corrupted erstell_ts for 2021.07 dataset
				this.errcoll.collect(pd.error,"einlange_dat of "+this.recnum+" is no valid Date() but: "+aux.objTxt(this.cdata.einlange_dat), this.cdata);
				if (pd.errcoll) this.errcoll.append(pd.errcoll);
				break proc;
				*/
			}
		} else {
			this.cdata.einlange_dat = pd;
			this.cdata.edt_old = this.cdata.einlange_dat;
			this.cdata.einlange_dat = aux.svDateTime(this.cdata.einlange_dat);
		}
		if (this.cdata.einlange_dat.length>19) { 
			this.cdata.einlange_dat = ("?" + this.cdata.einlange_dat).substring(0,19);
			this.ppush([this.phead("WRONG_EINLANGE_DAT"),"einlange_dat(corr)="+this.cdata.einlange_dat,"CDATA: "+aux.objTxt(this.cdata),"RECNUM="+this.recnum]);
		}
		this.cdata.rueckfrageinfo = this.cdata.xpath;				/* 20200910 - get "rueckfrageinfo" from "xpath" in input */
		if (!this.rfis) this.rfis = {};
		if (!this.rfis[this.cdata.rueckfrageinfo]) {
			this.rfis[this.cdata.rueckfrageinfo] = { ANZAHL: 0 };
		}
		this.rfis[this.cdata.rueckfrageinfo].ANZAHL++;
		this.traegerkurz = this.svts[this.cdata.traeger].name;
		/* this.tstep.sql = "select * from SVCLZUODUMP where traeger='"+this.traegerkurz+"' and bknr='"+this.cdata.bknr+"'"; */
	}
# sql		select 'NO_SQL_CALCULATED'
# named_params	traegerkurz,bknr
# result_varname	zres
#post_qexpression
#	let zuoa = new Array();
#	let zrow;
#	for (let ri=0; ri<this.zres.getRowCount(); ri++) {
#		zrow = this.zres.getRowObject(ri);
#		zuoa.push(zrow);
#	}
qexpression
	this.zuordnungsinfo = "" /* JSON.stringify(zuoa); */

#
# <<TSTEPNUM:7>>: insert the record
#
sql					insert into @@tablename@@(@@TLIST@@) values(@@TVLIST@@)
named_params		@@TLIST@@
post_qexpression
	this.recsprocessed++;

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

#
# <<TSTEPNUM:9>>: all records processed, mark MFILE as loaded
#
pre_qexpression
	this.LOADFINISHED = aux.nowstring();
	this.load_mfileattributes.loadstate = "loaded";
	delete this.load_mfileattributes.to_reconvert;
	delete this.load_mfileattributes.unloadtime;
	this.load_mfileattribs = JSON.stringify(this.load_mfileattributes);
sql				update CCMFILES SET MFILEATTRIBS=::load_mfileattribs:: WHERE MFILEID=::mfileid::
named_params	load_mfileattribs,mfileid
post_qexpression
	for (let rfi in this.rfis) {
		this.ppush([this.phead("rueckfrageinfoanzahl"),"ruckfrageinfo="+rfi,"ANZAHL="+this.rfis[rfi].ANZAHL,""]);
	}
	ppush(this,[this.ppfx+"_complete","MFILEID_TO_LOAD="+this.mfileid,"RECORDS_TO_PROCESS="+this.recstoprocess,"RECORDS_PROCESSED="+this.recsprocessed]);

#
# <<TSTEPNUM:10>>: end of record processing
#
record_done:
end