#
#			ITSV GmbH
#	CCDB - Command and Control Database
#
#	FILE:			dquerymfile_kfopp.txt
#	DESCRIPTION:	DQUERY definition for kfopp
#					this DQUERY contains all actions for KFOPP (KFOQSDB-146 - LE-Partner-Prefill from ZPV)
#
@querytitle			KFOPP - Werkzeuge für KFO Leistungserbringer-Partner-Vorbefüllung
@querydescription	
@group				KFOQSDBPARPREFILL
@attributenames		mode:integer:{{modeoptions}},cfgname:kfodbconfigname
@modeoptions		{"labeltext":Modus","typedesc":"Betriebsart: 0..5, 0 zeigt Hilfe an","is_optional":"true"}
@modeoptions		
@querytype			function
@function			seqtrans.seqtrans
@init.aexpression
	if (this.query.mode) {
		this.query.mode = "0";
	}
	if (this.query.mode==1) {
		db.getDbData({	sql:	"select * from CCKFOPPZPVDUMP",
						callback:	this,
						config_name:	this.query.cfgname });
	} else {
		this();
	}
@PFLIST				LENR,VPNR,TITELVORNE,VORNAME,NACHNAME,TITELHINTEN,ORGANISATIONSNAME,STAAT_ISOA2,BUNDESLANDCODE,BUNDESLANDKURZ,BUNDESLAND,PLZ,GEMEINDECODE,POLITISCHERBEZIRK,ORT,STRASSE,HAUSNUMMER,ADRESSNUMMER,BEGINNDATNUM,ENDEDATNUM,ENDEGRUND
@DOC
	<h2>KFOPP Werkzeuge</h2>
	<p>Modes:</p>
	<ul>
	<li> 0 - Usage: zeigt diese Usage-Information an
	<li> 1 - KFOPP-001: KFO-Partner aus ZPV-Abzug erzeugen<br/>
			erzeugt die KFO-Partner-Master-Datenbank in Tabelle CCKFOLEPARTNER in der CCDB aus einem ZPV-Abzug aus der Tabelle CCKFOPPZPVDUMP</li>
	<li> 2 - <extern></li>
	<li> 3 - <extern></li>
	<li> 4 - KFOPP-04:	erzeugt die Tabelle CCKFOLEPARTNER</li>
	<li> 5 - KFOPP-05:	erzeugt die Tabelle CCKFODBPARTNER</li>
	</ol>
			
~query.tsteps

#
# <<TSTEPNUM:0>>: determine mode and branch to it
#
pre_qexpression
	proc: {
		if (!this.query.mode) {
			this.errcoll.collect(null,"mode parameter not given",this.query);
			break proc;
		}
		this.kfopp_modelabel = 'mode_'+this.query.mode;
		if (!this.labels[this.kfopp_modelabel]) {
			this.errcoll.collect(null,"Cannot process Mode \""+this.query.mode+"\", label \""+this.kfopp_modelabel+"\" does not exist",this.query);
			break proc;
		}
	}
goto	@@kfopp_modelabel@@

#
# ------------------------------------------------------------------------------
#
#	MODE 0:	show documentation
#
#	<<TSTEPNUM:1>>: present documentation attribute as <pre> HTML text in the protocol
#
mode_0:
qexpression
	this.ppush([this.phead("usage"),"<pre>"+this.query.DOC+"</pre>","",""]);
then_goto single_done

#
# ------------------------------------------------------------------------------
#
#	MODE 1: KFOPP-001: KFO-Partner (CCKFOLEPARTNER) aus ZPV-Abzug (Tabelle CCKFOPPZPVDUMP) erzeugen
#

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

#
# <<TSTEPNUM:1>>: init + truncate partner table before being built afresh
#
pre_qexpression
	this.ppfx = this.fname+"("+this.query.dataname+")";
	this.PFLIST = this.query.PFLIST;
	this.tfields = this.PFLIST.split(",");
	this.PPFLIST = "::" + this.tfields.join("::,::") + "::";
	this.recsprocessed = 0;
	this.recstoprocess = this.inputresult.getRowCount();
	this.parscreated = 0;
	this.query.bundeslaender = new Array();
	this.query.bundeslaender.push({code: 0, kurz: 'N/A',	name: 'keinBundesland'});
	this.query.bundeslaender.push({code: 1, kurz: 'BGLD',	name: 'Burgenland'});
	this.query.bundeslaender.push({code: 2,	kurz: 'KTN',	name: 'Kärnten'});
	this.query.bundeslaender.push({code: 3, kurz: 'NOE',	name: 'Niederösterreich'});
	this.query.bundeslaender.push({code: 4, kurz: 'OOE',	name: 'Oberösterreich'});
	this.query.bundeslaender.push({code: 5, kurz: 'SBG',	name: 'Salzburg'});
	this.query.bundeslaender.push({code: 6, kurz: 'STMK',	name: 'Steiermark'});
	this.query.bundeslaender.push({code: 7, kurz: 'T',		name: 'Tirol'});
	this.query.bundeslaender.push({code: 8, kurz: 'VBG',	name: 'Vorarlberg'});
	this.query.bundeslaender.push({code: 9, kurz: 'W',		name: 'Wien'});
	ppush(this,[this.phead("M1_STARTED"),"RECSTOPROCESS="+this.recstoprocess,"",""]);
sql					truncate table CCKFOLEPARTNER
post_qexpression
	this.init_done = true;

#
# <<TSTEPNUM:2>>: setup loop for one record
#		(one record from CCKFOPPZPVDUMP may result in more records in CCKFOLEPARTNER
#
record_proper:
qexpression
	this.vpnrs = new Array();
	if (this.cdata.VPNR) {
		this.vpnrs = this.cdata.VPNR.split(",");
	}
	if (this.cdata.VPNR2) {
		this.vpnr2s = this.cdata.VPNR2.split(",");
		this.vpnrs = this.vpnrs.concat(this.vpnr2s);
	}
	delete this.cdata.VPNR;
	delete this.cdata.VPNR2;
	this.vpnrnr = 0;

#
# <<TSTEPNUM:3>>: check at start of partner loop
#
parloop:
if 		(this.vpnrnr>=this.vpnrs.length)
goto	parloop_done

#
# <<TSTEPNUM:4>>: check if current partner already existant
#	partner is assumed to be unique by LENR/VPNR/ADRN
#
pre_qexpression	
	this.VPNR = this.vpnrs[this.vpnrnr];
sql				select * from CCKFOLEPARTNER where LENR=::LENR:: and VPNR=::VPNR:: and ADRESSNUMMER=::ADRESSNUMMER::
named_params	LENR,VPNR,ADRESSNUMMER
result_varname	parchkres

#
# <<TSTEPNUM:5>>: if record already exists, do nothing more for this partner
#
if 		(this.parchkres.getRowCount()>0)
goto	parloop_end

#
# <<TSTEPNUM:6>>: partner does not yet exist, insert it into CCKFOLEPARTNERS
#
pre_qexpression
	this.cdata.VORNAME = this.cdata.NAME;
	this.cdata.PLZ = this.cdata.PLZL;
	this.cdata.BEGINNDATNUM = this.cdata.BEGINN;
	this.cdata.ENDEDATNUM = this.cdata.ENDE;
	if (this.cdata.BUNDESLAND=="" && this.cdata.BUNDESLANDCODE=="") {
		this.cdata.BUNDESLANDKURZ="";
	} else {
		this.cdata.BUNDESLANDKURZ = this.query.bundeslaender[Number(this.cdata.BUNDESLANDCODE)].kurz;
		if (this.cdata.BUNDESLAND!=this.query.bundeslaender[Number(this.cdata.BUNDESLANDCODE)].name) {
			this.errcoll.collect(null,"Bundeslandcode "+this.cdata.BUNDESLANDCODE+" does not match BUNDESLAND "+this.cdata.BUNDESLAND+" at record "+this.recnum);
		}
	}
	this.cdata.STAAT_ISOA2 = "AT";
sql				insert into CCKFOLEPARTNER(@@PFLIST@@) values(@@PPFLIST@@)
named_params	@@PFLIST@@
post_qexpression
	this.parscreated++;

#
# <<TSTEPNUM:7>>: partner loop iteration work done
#
parloop_end:
qexpression
	this.vpnrnr++;

#
# <<TSTEPNUM:8>>: repeat partner loop
#
goto parloop

#
# <<TSTEPNUM:9>>: loop iteration complete
#
parloop_done:
pre_qexpression
	this.recsprocessed++;
if (!this.is_last_record)
goto record_end

#
# <<TSTEPNUM:10>>: last record, finish up protocol
#
qexpression
	ppush(this,[this.phead("M1_FINISHED"),"RECSTOPROCESS="+this.recstoprocess,"RECSPROCESSED="+this.recsprocessed,"PARTNERS_CREATED="+this.parscreated]);
	
#
# <<TSTEPNUM:11>>: end of record
#
record_end:
end

#
# ------------------------------------------------------------------------------
#
#	MODE 4:	create table CCKFOLEPARTNER
#

#
# <<TSTEPNUM:12>>:	drop table if yet exists
#
mode_4:
sql 	drop table if exists CCKFOLEPARTNER

sql
	create table CCKFOLEPARTNER (	
						LENR varchar(10),
						VPNR	varchar(6),
						TITELVORNE varchar(32),
						VORNAME varchar(100),
						NACHNAME varchar(100),
						TITELHINTEN varchar(32),
						ORGANISATIONSNAME varchar(100),
						STAAT_ISOA2 varchar(2),
						BUNDESLANDKURZ varchar(4),
						BUNDESLANDCODE varchar(1),
						BUNDESLAND varchar(32),
						PLZ varchar(10),
						GEMEINDECODE varchar(6),
						POLITISCHERBEZIRK varchar(70),
						ORT varchar(100),
						STRASSE varchar(100),
						HAUSNUMMER varchar(32),
						ADRESSNUMMER varchar(3),
						BEGINNDATNUM varchar(8),
						ENDEDATNUM varchar(8),
						ENDEGRUND varchar(100))
post_qexpression
	this.ppush([this.phead("M4_FINISHED"),"table CCKFOLEPARTNER created","",""]);
then_goto	single_done

#
# ------------------------------------------------------------------------------
#
#	MODE 5:	create table CCKFODBPARTNER
#

#
#	14: 	drop the table if yet exists
#
mode_5:
sql		drop table if exist CCKFODBPARTNER

#
#	15:		create table from scratch
#
sql
	create table CCKFODBPARTNER (
		BUNDESLANDKURZ varchar(4), 
		PARTNERNAME varchar(100), 
		VPNR varchar(16), 
		STAAT_ISOA2 varchar(2), 
		ANZAHL_FAELLE decimal(19), 
		MAXFALLQST varchar(2))
post_qexpression
	this.ppush([this.phead("M5_FINISHED"),"table CCKFODBPARTNER created, is empty","",""]);
then_goto	single_done


#
# ------------------------------------------------------------------------------
#
#	MODE 6: create table CCKFOPPZPVDUMP
#

#
#	16:		drop the table if yet exists
#
mode_6:
sql		drop table if exists CCKFOPPZPVDUMP

#	
#	17:		create table from scratch
#
sql
	create table CCKFOPPZPVDUMP (LENR varchar(10), 
                             VPNR varchar(200),
                             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))
post_qexpression
	this.ppush([this.phead("M6_FINISHED"),"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\"",""]);
then_goto	single_done

#
# ------------------------------------------------------------------------------
#
single_done:
	end