#
#		ITSV GmbH
#	CCDB - Command and Control Database
#
#	FILE:			dquerymfile_KFOPP000.txt
#	DESCRIPTION:	DQUERY definition file of DQUERY KFOPP000
#					this query prepares the table CCKFOPPZPVDUMP
#
@querytitle			KFOPP-000: Tabelle CCKFOPPZPVDUMP für ZPV-Leistungserbringer-Abzug anlegen und laden
@querydescription	Legt die Tabelle CCKFOPPZPVDUMP an, damit ein ZPV-Abzug aus einem Managed File eingelesen werden kann, danach wird ein Abzug geladen
@group				KFOQSDBPARPREFILL
@attributenames		loadfileid:mfileid:{{loadfileidoptions}}
@loadfileidoptions {
	labeltext: 		"Ladedatei",
	typedesc:		"ID der managed-file Datei, aus der der ZPV-Datenabzug geladen werden soll" }
@querytype			function
@function			seqtrans.seqtrans

~query.tsteps

#
# <<TSTEPNUM:0>>: setup general and then drop table if yet exists to be created afresh
#
pre_qexpression
	this.copyqvars("loadfileid");
sql				drop table if exists CCKFOPPZPVDUMP
result_varname	tdres
post_qexpression
	this.ppush([this.phead("TABLEDROPPED"),"","",this.tdres]);

#
# <<TSTEPNUM:1>>: create table
#
sql		create table CCKFOPPZPVDUMP (LENR varchar(10), 
                     VPNR varchar(200),
					 VERSICHERUNGSNUMMER varchar(32),
                     TITELVORNE varchar(32),
                     NAME varchar(100),
                     NACHNAME varchar(100),
                     TITELHINTEN varchar(32),
                     ORGANISATIONSNAME varchar(100),
                     BUNDESLAND varchar(32),
                     PLZL varchar(10),
                     ORT varchar(100),
                     STRASSE varchar(255),
                     HAUSNUMMER varchar(32),
                     ADRESSNUMMER varchar(32),
                     BUNDESLANDCODE varchar(10),
                     GEMEINDECODE varchar(10),
                     POLITISCHERBEZIRK varchar(70),
                     BEGINN varchar(16),
                     ENDE varchar(16),
                     ENDEGRUND varchar(100),
                     VPNR2 varchar(200))
result_varname		tcres
post_qexpression
	logger.debug(this.phead("TCRES")+": "+aux.objTxt(this.tcres));
	this.ppush([this.phead("KFOPPDUMPTABLECREATED"),"table CCKFOPPZPVDUMP created, is empty",
	                       "ATTN: load the table by loading a ZPV-Dump by setting the \"table\"-attribute of the managed file containing the dump to \"CCKFOPPZPVDUMP\"",this.tcres]);

#
# <<TSTEPNUM:2>>: dump the loadfile and check how many rows it has
#
query.dataname		VIEWMFILE
query.mfileid		@@loadfileid@@
result_varname		vlres
post_qexpression
	this.expecteddumprows = this.vlres.getRowCount()-2;
	this.ppush([this.phead("KFOPP000LOADFILE"),"ROWSINLOADFILE="+this.expecteddumprows,"",""]);

#
# <<TSTEPNUM:3>>: load the loadfile into CCKFOPPZPVDUMP
#
query.dataname		LOADBIGFILETABLE
query.mfid			@@loadfileid@@
query.tabname		CCKFOPPZPVDUMP

#
# <<TSTEPNUM:4>>: check how many rows have been loaded
#
sql					select count(*) from CCKFOPPZPVDUMP
result_varname		lcres
post_qexpression	
	this.gotdumprows = this.lcres.getCellValue(0,0);
	this.ppush([this.phead("KFOPP000DUMPLOADED"),"ROWLOADED="+this.gotdumprows,"",""]);