#
#		ITSV GmbH
#	CCDB - Command and Control DAtabase
#
#	FILE:			dquerymfile_loadgemeinderw.txt
#	DESCRIPTION:	DQUERY definition for DQUERY loadgemeinderw
#					this DQUERY loads a managed file into the table CCRWGEMEINDE
#
@querytitle			Managed file in Regelwerkstabelle CCRWGEMEINDE laden
@querydescription	lädt den inhalt einer managed Datei in die Regelwerks-Tabelle CCRWGEMEINDE
@group				ADMIN
@attributenames		mfileid:mfileid:{{mfileidoptions}}
#
# NOTE: input data for the transaction sequence is taken from the managed file named in query attribute <mfileid>
#
@mfileidoptions		{"labeltext":"managed Datei","typedesc":"ID der managed Datei, die in die Tabelle CCRWGEMEINDE geladen werden soll"}
@querytype			function
@function			seqtrans.seqtrans
@FLIST				GEMEINDEKENNZIFFER,	GEMEINDENAME,		GEMEINDECODE,	STATUS,			PLZ_GEM_AMT,	WEITERE_PLZ
@FSIZELIST			5,					255,				5,				3,				4,				255
@input_options.startline			3
@input_options.headersInFirstLine	0
@input_encoding		ISO8859-1
@init.qexpression
	this.REXLIST = [ /^[0-9]{5}$/,		/^.+$/,				/^[0-9]{5}$/,	/^[A-Z]{0,2}$/,	/^[0-9]{4}$/,	/^([0-9]{4}\s*)*$/ ];
	this.load_file = load_file;	
	this.query.input_options.colnames = new Array();
	this.fieldsizes = this.query.FSIZELIST.split(",");
	for (let i=0; i<this.fieldsizes.length; i++) {
		this.fieldsizes[i] = 0 + this.fieldsizes[i].trim();
	}
	this.fieldnames = this.query.FLIST.split(",");
	for (let i=0; i<this.fieldnames.length; i++) {
		this.fieldnames[i] = this.fieldnames[i].trim();
		this.query.input_options.colnames.push(this.fieldnames[i]);
	}
	this.fieldcount = this.fieldnames.length;
	this.FLIST = this.fieldnames.join(",");
	this.FTLIST = "::"+this.fieldnames.join("::,::") + "::";
	this.recordsprocessed = 0;
	this.recordsinserted = 0;
	this.recordsskipped = 0;
	this.csv_rep_func = function(linetext,repres) {
		let rp = new Array();
		/* let fldarr = this.load_file.splitCSVline(linetext,{fieldsep: ";", quotechar: "\""}); */
		let fldarr = this.load_file.splitCSVline(linetext,{ fieldsep: ";" });
		if (aux.res_is_error(fldarr)) {
			rp.push("Error splitting input line WITHOUT quoting: "+aux.objTxt(fldarr));
			fldarr = linetext.split(";");
			rp.push("stopgap by simply String.split()ting it along \";\"");
		}
		rp.push("Trying to repair fields: "+aux.objTxt(fldarr));
		if (fldarr.length==this.fieldcount) {
			rp.push("line already has exactly "+this.fieldcount+" fields (as expected), we should not have come here!");
			if (repres) repres.info = rp.join("\n");
			return linetext;
		}
		rp.push("no repair implemented: \""+linetext+"\"");
		if (repres) repres.info = rp.join("\n");
		return linetext;
	}.bind(this);
	
~query.tsteps

#
# <<TSTEPNUM:0>>: check for initialize
#
if		(this.init_ok)
goto	init_done

#
# <<TSTEPNUM:1>>: init: drop table
#
sql		drop table if exists CCRWGEMEINDE
result_varname	dropres
post_qexpression
	this.ppush([this.phead("RWTABDROPPED"),"","",this.dropres]);

#
# <<TSTEPNUM:2>>: init: create table
#
pre_qexpression
	this.cclause = "";
	for (let i=0; i<this.fieldcount; i++) {
		if (i>0) this.cclause += ",";
		this.cclause += this.fieldnames[i] + " varchar("+this.fieldsizes[i] + ")";
	}
sql		create table CCRWGEMEINDE(@@cclause@@)
result_varname	creres
post_qexpression
	this.ppush([this.phead("RWTABCREATED"),"","",this.creres]);
	
#
# <<TSTEPNUM:3>>: init complete
#
qexpression
	this.init_ok = true;

#
# <<TSTEPNUM:4>>: init done
#
init_done:
noop

#
# <<TSTEPNUM:5>>: check record structure and skip inserting if not good
# 
pre_qexpression
	this.ok = true;
	let rp = new Array();
	proc: {
		let m;
		let fn;
		for (let ci=0; ci<this.fieldcount; ci++) {
			fn = this.fieldnames[ci];
			m = this.REXLIST[ci].exec(this.cdata[fn]);
			if (!m) {
				rp.push("Field "+fn+" is not well formed: \""+this.cdata[fn]+"\"");
				this.ok = false;
			}
		}
	}
	if (!this.ok) {
		this.ppush([this.phead("MALFORMEDRECORD"),"RECORD_NUMBER="+this.recnum,"will be skipped",rp]);
		this.recordsskipped++;
	}
if 		(!this.ok)
goto	record_skipped

#
# <<TSTEPNUM:6>>: insert record
#
sql				insert into CCRWGEMEINDE(@@FLIST@@) values(@@FTLIST@@)
named_params	@@FLIST@@
result_varname	insres
post_qexpression
	this.recordsinserted++;

#
# <<TSTEPNUM:7>>: check if last recordsinserted
#
record_skipped:
pre_qexpression
	this.recordsprocessed++;
if (!this.is_last_record)
goto rec_end

#
# <<TSTEPNUM:8>>: close protocol
#
qexpression
	this.ppush([this.phead("FINISHED"),"RECORDS_PROCESSED="+this.recordsprocessed,"RECORDSINSERTED="+this.recordsinserted,"RECORDS_SKIPPED="+this.recordsskipped]);

#
# <<TSTEPNUM:9>>: end of record
#
rec_end:
end