#
#		ITSV GmbH
#	CCDB - COmmand and COntrol Database
#
#	FILE:			dquerymfile_loadbeiconexoffomoncount.txt
#	DESCRIPTION:	DQUERY definition for DQUERY loadbeiconexoffomoncount
#
@querytitle			BEICON-Auszug mit exOffo.-mBGM-Monats-Anzahlen laden
@querydescription	lädt eine XLSX-Datei mit der MFILETYPE beiconexoffomoncount in die Tabelle SVCLBEICONEXOFFOMONCOUNT
@group				SVCLEXOSTAT
@attributenames		mfileid:mfileid:{{mfileidoptions}}
@mfileidoptions		{"labeltext":"Ladedatei","typedesc":"managed file ID der Datei mit exOffo-Zahlen aus mBGM, die geladen werden soll"}
@querytype			function
@function			seqtrans.seqtrans
@FLIST				REFDATNUM,BZRJAHR,BZRMONAT,TRAEGER,ANZAHLEXOFFO
@FTYPES				DECIMAL(10),DECIMAL(4),DECIMAL(2),VARCHAR(4),DECIMAL(19)
@TLIST				11,12,13,14,15,16,17,18,19
@TABNAME			SVCLBEICONEXOFFOMONCOUNT
@input_mode							MFILE    
@input_options.startline			4
@input_options.headersInFirstLine	Y

~query.tsteps

#
# 0: init
#
if		(this.init_done)
goto	record_proper

#
# 1:
qexpression
	proc: {
		this.FLIST = this.query.FLIST;
		this.FTYPES = this.query.FTYPES;
		this.TABNAME = this.query.TABNAME;
		this.ppush([this.phead("INIT"),"RECORDSTOPROCESS="+this.inputresult.getRowCount(),"",""]);
		this.fieldnames = this.query.FLIST.split(",");
		this.fieldtypes = this.query.FTYPES.split(",");
		this.tcsql = "create table if not exists "+this.TABNAME+"(";
		for (let i=0; i<this.fieldnames.length; i++) {
			if (i>0) this.tcsql += ",";
			this.tcsql += this.fieldnames[i] + " " + this.fieldtypes[i];
		}
		this.svts = this.query.TLIST.split(",");
		this.numtraeger = this.svts.length;
		this.tcsql += ")";
		this.insql = "insert into "+this.TABNAME+"("+this.query.FLIST+") values(::"+this.fieldnames.join("::,::")+"::)";
		this.chksql = "select "+this.fieldnames.join(",")+" from "+this.TABNAME+" where BZRJAHR=::BZRJAHR:: and BZRMONAT=::BZRMONAT:: and TRAEGER=::TRAEGER::";
		this.recsprocessed = 0;
		this.recscreated = 0;
		this.recsupdated = 0;
		let mfparts = this.mfileinfo.mfilename.split("_");
		this.refdatnum = mfparts[0];
		let rdrex = /^[0-9]{8}$/;
		let m = rdrex.exec(this.refdatnum);
		if (!m) {
			this.errcoll.collect(null,"REFDATNUM part \""+this.refdatnum+"\" of MFILE name \""+this.mfileinfo.mfilename+"\" is not valid YYYYMMDD",{ mfparts: mfparts, mfileinfo: this.mfileinfo });
			break proc;
		}
	}
		
#
# 2: init - create table if not yet existing
#
sql				@@tcsql@@
result_varname	tcres
post_qexpression
	this.ppush([this.phead("TABLE_CREATED"),"","",this.tcres]);

#
# 3: init - done
#
qexpression
		this.init_done = true;

#
# 4: setup loop for one insert record per TRAEGER
#
record_proper:
qexpression
		this.findex = 0;

#
# 5: check if we're complete with one input record
#
next_insert:
if		(this.findex>=this.numtraeger)
goto	insert_done

#
# 6: check count for 1 TRAEGER
#
pre_qexpression
	proc: {
		this.cdata.REFDATNUM = this.refdatnum;
		let bzrt = typeof(this.cdata.Beitragszeitraum);
		if (this.cdata.Beitragszeitraum instanceof Date) {
			let bzr = new Date(this.cdata.Beitragszeitraum);
			bzr.setHours(bzr.getHours()+12);		/* DIRTY PATCH: move time to approx midday to fix TZ-Offset quirkiness, TODO: make right */
			this.cdata.BZRJAHR = bzr.getFullYear();
			this.cdata.BZRMONAT = bzr.getMonth()+1;
		} else if (bzrt=="string") {
			let bzrfs = this.cdata.Beitragszeitraum.split(".");
			if (bzrfs.length<2) {
				this.errcoll.collect(null,"Beitragszeitraum of record "+this.recnum+" has less than 2 parts",this.cdata);
				break proc;
			}
			this.cdata.BZRJAHR = bzrfs[0];
			this.cdata.BZRMONAT = bzrfs[1];
		} else {
			this.errcoll.collect(null,"Beitragszeitraum of record "+this.recnum+" is of invalid format",this.cdata);
			break proc;
		}
		this.cdata.TRAEGER = this.svts[this.findex];
		this.cdata.ANZAHLEXOFFO = this.cdata[this.cdata.TRAEGER];
	}
sql				@@chksql@@
named_params	@@FLIST@@
result_varname	chkres
post_qexpression
	proc: {
		this.todo = "record_error";
		if (this.chkres.getRowCount()>1) {
			this.errcoll.collect(null,"More than 1 already present record for BZR="+this.cdata.BZRJAHR+"."+this.cdata.BZRMONAT+" and TRAEGER="+this.cdata.TRAEGER,{ old_data: this.chkres, new_data: aux.deepCopy(this.cdata)});
			break proc;
		} else if (this.chkres.getRowCount()==1) {
			let oldata = this.chkres.getRowObject(0);
			if (oldata.REFDATNUM>this.cdata.REFDATNUM) {
				this.errcoll.collect(null,"Already existing record for BZR="+this.cdata.BZRJAHR+"."+this.cdata.BZRMONAT+" and TRAEGER="+this.cdata.TRAEGER+" has newer reference date",{ old_data: oldata, new_data: aux.deepCopy(this.cdata)});
				break proc;
			}
			if (oldata.ANZAHLEXOFFO!=this.cdata.ANZAHLEXOFFO) {
				this.errcoll.collect(null,"Already existing record for BZR="+this.cdata.BZRJAHR+"."+this.cdata.BZRMONAT+" and TRAEGER="+this.cdata.TRAEGER+" has different ANZAHLEXOFFO",{ old_data: oldata, new_data: aux.deepCopy(this.cdata)});
				break proc;
			}
			this.todo = "record_update";
		} else {
			this.todo = "record_insert";
		}
	}
then_goto	@@todo@@

#
# 7: update count for 1 TRAEGER
#
record_update:
sql				update @@TABNAME@@ set REFDATNUM=::REFDATNUM:: where BZRJAHR=::BZRJAHR:: and BZRMONAT=::BZRMONAT:: and TRAEGER=::TRAEGER::
named_params	@@FLIST@@
result_varname	updres
post_qexpression
		this.ppush([this.phead("COUNTUPDATED"),"BZR="+this.cdata.BZRJAHR+"."+this.cdata.BZRMONAT,"TRAEGER="+this.cdata.TRAEGER,{inres: aux.deepCopy(this.updres), cdata: aux.deepCopy(this.cdata)}]);
		this.findex++;
		this.recsupdated++;
then_goto		next_insert


#
# 8: insert count for 1 TRAEGER
#
record_insert:
sql				@@insql@@
named_params	@@FLIST@@
result_varname	inres
post_qexpression
		this.ppush([this.phead("COUNTINSERTED"),"BZR="+this.cdata.BZRJAHR+"."+this.cdata.BZRMONAT,"TRAEGER="+this.cdata.TRAEGER,{inres: aux.deepCopy(this.inres), cdata: aux.deepCopy(this.cdata)}]);
		this.findex++;
		this.recscreated++;
then_goto		next_insert

#
# 9: done one record
#
insert_done:
pre_qexpression
		this.recsprocessed++;
if		(!this.is_last_record)
goto	end

#
# 10: last record, put summary protocol record
#
qexpression
		this.ppush([this.phead("FINISHED"),"RECORDSTOPROCESS="+this.inputresult.getRowCount(),"RECORDSPROCESSED="+this.recsprocessed,"RECORDS_CREATED="+this.recscreated+", RECORDS_UPDATED="+this.recsupdated]);

#
# 11: record complete
#
end:
end