#
#			ITSV GmbH
#	CCDB - Command and Control Database
#
#	FILE:			dquerymfile_KFOPP006001.txt
#	DESCRIPTION:	DQUERY Definition for DQUERY KFOPP006001
#					this query loads one respone form to KFOPP004/KFOPP005 from one QS-Träger
#					into reponse consolidation table CCKFOPPRESP
#					The response forms have the same structure as the table CCKFOPPPARZPVPAS (from this the forms are generated in the first place)
#
@querytitle				KFOPP-006.001 - Lade Rückantwort eines QS-Trägers auf KFOPP-005 Problem-Auswertungen
@querydescription		lädt die Rückantwort eines QS-Trägers auf KFOPP-005 in die Tabelle CCKFOPPRESP
						wenn nötig, wird die Tabelle neu angelegt
@group					KFOQSDBPARPREFILL
@attributenames			mfileid:mfileid:{{mfileidoptions}}
@mfileidoptions	{
	labeltext:	"Ladedatei",
	typedesc:	"Managed File ID der Rückmeldedatei des QS-Trägers" }
@querytype				tseq
#
@TABNAME			CCKFOPPRESP
@FLIST				SATZID,MAXFALLQST, KFO_VPNR, ZPV_VPNRS, ANZAHL_FAELLE, FEHLER_NICHT_ZPV, PROP_NICHT_ZPV, REP_NICHT_ZPV,
					KFO_PARTNERNAME, ZPV_PARTNERNAME, FEHLER_NAME, PROP_NAME, REP_NAME,
					KFO_STAAT, ZPV_STAATEN, FEHLER_STAAT, PROP_STAAT, REP_STAAT,
					KFO_BUNDESLAND, ZPV_BUNDESLAENDER, FEHLER_BUNDESLAND, PROP_BUNDESLAND, REP_BUNDESLAND,
					ZPV_ADRESSEN,
					ZPV_TITELVORNE, ZPV_VORNAME, ZPV_NACHNAME, ZPV_TITELHINTEN, ZPV_ORGANISATIONSNAME, FAELLE_PRO_QST

~query.tsteps

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

#
#	<<TSTEPNUM:1>>: setup, determine presence of table and if its format is valid
# 
pre_qexpression
	this.copyqvars("TABNAME,FLIST,mfileid");
	this.tabfields = this.FLIST.split(",");
	for (let fi=0; fi<this.tabfields.length; fi++) this.tabfields[fi] = this.tabfields[fi].trim();
	this.TFLIST = "::" + this.tabfields.join("::,::") + "::";
sql				select * from @@TABNAME@@ limit 1,1
result_varname	cpres
catch_errors	true
post_qexpression
	proc: {
		let fci;
		logger.debug(this.phead("cpres")+": "+aux.objTxt(this.cpres));
		if (this.cpres && this.cpres.error) {
			this.tab_is_there = false;
			break proc;
		} else {
			this.tab_is_there = true;
		}
		for (let ci=0; ci<this.tabfields.length; ci++) {
			if (ci>=this.cpres.getColumnCount()) {
				this.errcoll.collect(null,"Table " + this.TABNAME + " exists but has too few columns",this.cpres);
			}
			fci = this.cpres.findColumn(this.tabfields[ci]);
			if (fci<0) {
				this.errcoll.collect(null,"Table " + this.TABNAME + " exists, however column \""+this.tabfields[ci]+"\" is missing",this.cpres);
			}
		}
		if (this.errcoll.hasErrors()) {
			break proc;
		}
	}

#
# <<TSTEPNUM:2>>: check state of managed file
#
sql				select * from CCMFILES where MFILEID=::mfileid::
named_params	mfileid
result_varname	mfres
post_qexpression
	proc: {
		if (this.mfres.getRowCount()!=1) {
			this.errcoll.collect(null,"mfres has wrong count of rows",this.mfres);
			break proc;
		}
		this.mfrow = this.mfres.getRowObject(0);
		this.mfatt = aux.txtObjDecode(this.mfrow.MFILEATTRIBS);
		if (!this.mfatt) {
			this.errcoll.collect(null,"could not read MFILEATTRIBS",{ row: this.mfrow });
			break proc;
		}
		if (!this.mfatt.loadstate) {
			this.errcoll.collect(null,"mfile has no loadstate attribute",{ attributes: this.mfatt });
			break proc;
		}
	}

#
# <<TSTEPNUM:2>>: check if table to be created
#
if		(this.tab_is_there)
goto	table_there

#
# <<TSTEPNUM:3>>: create table as copy from KFOPP003-result
#
sql		create table @@TABNAME@@ as select * from CCKFOPPPARZPVPAS limit 1,0

#
# <<TSTEPNUM:4>>: table is there, determine QS-Träger, setup counters
#
table_there:
qexpression
	this.recstoprocess = this.records_to_process;
	this.recsprocessed = 0;
	this.ppush([this.phead("STARTED"),"recstoprocess="+this.recstoprocess,"",""]);
	this.init_done = true;

#
# <<TSTEPNUM:5>>: record processing: check if this record already in destination table
#
record_proper:
pre_qexpression
	if (!this.cdata.hasOwnProperty("SATZID")) {
		this.cdata.SATZID = String(this.cdata.MAXFALLQST)+String(this.cdata.KFO_VPNR);
	}
sql 			select * from @@TABNAME@@ where SATZID=::SATZID::
named_params	SATZID
result_varname	rcres
post_qexpression
	proc: {
		if (this.rcres.getRowCount()>0) {
			this.errcoll.collect(null,"SATZID aus Datei "+this.mfileid+" existiert bereits in "+this.TABNAME,{ cdata: this.cdata });
			break proc;
		}
	}

#
# <<TSTEPNUM:6>>: store record in table
#
sql				insert into @@TABNAME@@(@@FLIST@@) values(@@TFLIST@@)
named_params	@@FLIST@@
post_qexpression
	this.recsprocessed++;

#
# <<TSTEPNUM:7>>: check if this was last record
#
unless		(this.is_last_record)
goto		record_end

#
# <<TSTEPNUM:8>>: last record, mark file as processed and terminate protocol
#
pre_qexpression
	this.mfatt.loadstate = "loaded";
	this.mfa = JSON.stringify(this.mfatt);
sql				update CCMFILES set MFILEATTRIBS=::mfa::
named_params	mfa
post_qexpression
	this.query.title = this.recsprocessed + "Datensätze geladen aus "+this.mfileid;
	this.ppush([this.phead("FINISHED"),
				"recstoprocess=" + this.recstoprocess,
				"recsprocessed=" + this.recsprocessed,
				{ rawhtml: "from_mfile=<a href=\"/dquery?dataname=SHOWFILE&mfileid="+this.mfileid+"\">"+this.mfileid+"</a>" }]);
	
#
# <<TSTEPNUM:9>>: end of record
#
record_end:
end