#
#			ITSV GmbH
#	CCDB - Command and Control Database
#
#	FILE:			dquerymfile_kfoparfromzpv.txt
#	DESCRIPTION:	DQUERY definition for DQUERY kfoparfromzpv
#
@querytitle			KFOPP-001: KFO-Partner aus ZPV-Abzug erzeugen
@querydescription	erzeugt die KFO-Partner-Master-Datenbank (Tabelle CCKFOLEPARTNER) in der CCDB aus einem ZPV-Abzug aus der Tabelle CCKFOPPZPVDUMP.
					Dabei werden aus einem Eintrag in der Abzugs-Tabelle potentiell mehrere Einträge in der KFO-Partner-Tabelle, 
					da in der Abzugs-Tabelle ein Eintrag mehrere VPNRs haben kann
@group				KFOQSDBPARPREFILL
@querytype			tseq
@sql_select			select * from CCKFOPPZPVDUMP
@PFLIST				LENR,VPNR,TITELVORNE,VORNAME,NACHNAME,TITELHINTEN,ORGANISATIONSNAME,STAAT_ISOA2,BUNDESLANDCODE,BUNDESLANDKURZ,BUNDESLAND,PLZ,GEMEINDECODE,POLITISCHERBEZIRK,ORT,STRASSE,HAUSNUMMER,ADRESSNUMMER,BEGINNDATNUM,ENDEDATNUM,ENDEGRUND

~query.tsteps

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

#
# 1: init - drop 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.candidatesprocessed = 0;
	this.parscreated = 0;
	this.differencesreported = 0;
	this.partnersnotstored = 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'});
	this.ppush([this.phead("STARTED"),"RECSTOPROCESS="+this.recstoprocess,"",""]);
sql					drop table if exists CCKFOLEPARTNER
result_varname		tdres
post_qexpression
	this.ppush([this.phead("TABLEDROPPED"),"table CCKFOLEPARTNER dropped","",this.tdres]);

#
# 2: init - create ZPV partner table
#
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))
result_varname	tcres
post_qexpression
	this.ppush([this.phead("TABLEPREPARED"),"table CCKFOLEPARTNER created","",this.tcres]);
	this.init_done = true;

#
# 3: setup loop for one record
#
record_proper:
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;	
	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;
	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";

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

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

#
# 6: if no record with the same LENR/VPNR/ADRN, this is unique => store it
#
if 		(this.parchkres.getRowCount()<1)
goto	store_partner

#
# 7: partner already exists with this LENR/VPNR/ADRN, check whats going on
#
qexpression
	let report = true;
	let crow = null;
	this.partnersnotstored++;
	cseq: {
		if (this.parchkres.getRowCount()>1) {
			report = true;
			break cseq;
		}
		crow = this.parchkres.getRowObject(0);
		if (crow.BUNDESLANDCODE    == this.cdata.BUNDESLANDCODE) {
			report = false;
			break cseq;
		}
		/* duplicate, different Bundesland but same ADRN, check for Übersiedlung */
		let ddiff = aux.checkTimeRanges([{beginn: crow.BEGINNDATNUM, ende: crow.ENDEDATNUM},{beginn: this.cdata.BEGINNDATNUM, ende: this.cdata.ENDEDATNUM}],{mode:"sequence"});
		if (ddiff==0) {	/* no proper sequence */
			this.cdata.SEQCHK = ddiff;
			report = true;
			break cseq;
		}
	}
	if (report) {
		this.ppush([this.phead("DUPLICATEPARTNER_DIFFERENT_ADDRESS"),
					"DUPLICATE_LENR/VPNR/ADRN="+this.cdata.LENR+"/"+this.VPNR+"/"+this.cdata.ADRESSNUMMER,
					((this.parchkres.getRowCount()>1)?"MULTIPLE_ALREADY_PRESENT":""),
					{ new_data: this.cdata, already_there: crow?crow:this.parchkres.rows}]);
		this.differencesreported++;
	}
then_goto	parloop_end

#
# 8: partner does not yet exist, insert it into CCKFOLEPARTNERS
#
store_partner:
sql				insert into CCKFOLEPARTNER(@@PFLIST@@) values(@@PPFLIST@@)
named_params	@@PFLIST@@
post_qexpression
	this.parscreated++;

#
# 9: partner loop iteration work done
#
parloop_end:
qexpression
	this.vpnrnr++;

#
# 10: repeat partner loop
#
goto parloop

#
# 11: loop iteration complete
#
parloop_done:
pre_qexpression
	this.recsprocessed++;
if (!this.is_last_record)
goto record_end

#
# 12: last record, finish up protocol
#
qexpression
	this.query.title = this.parscreated + " KFO-Partner-Datensätze angelegt";
	ppush(this,[this.phead("FINISHED"),
				"ZPV_RECORDS_TO_PROCESS="+this.recstoprocess,
				"ZPV_RECORDS_PROCESSED="+this.recsprocessed,
				"PARTNER_CANDIDATES_PROCESSED="+this.candidatesprocessed+
					", PARTNERS_CREATED="+this.parscreated+
					", DIFFERENCES_REPORTED="+this.differencesreported+
					", PARTNERS_NOT_STORED="+this.partnersnotstored]);
	
#
# 13: end of record
#
record_end:
end