#
#			ITSV GmbH
#	CCDB - COmmand and Control Database
#
#	FILE:			dquerymfile_SVCLGENUSPSTAMM.txt
#	DESCRIPTION:	DQUERY deinition for DQUERY SVCLGENUSPSTAMM
#					builds table SVCLUSPPERSZP by reading SVCLUSPPERS and 
#					enhancing by masterdata
#					from SVCLKURSSTAMM for Unternehmen (based on KUR)
#					from SVCLUSPPSTAMM for Personen (based on SVBPKVER)
#
@querytitle			SV-Clearing USP Stammdaten erzeugen
@querydescription	erzeugt die Tabelle SVCLUSPPERSZP aus SVCLUSPPERS mit ZPV-Anreicherung
					von Tabelle SVCLKURSSTAMM (für Unternehmen, identifiziert durch KUR)
					und Tabelle SVCLUSPPSTAMM (für Personen, identifiziert durch SV-bPK-V -> PARTNERID -> VSNR)
@group				SVCLCASETOOLS
@querytype			function
@function			seqtrans.seqtrans
@puth				1

~query.tsteps

#
# <<TSTEPNUM:0>>: prepare indices
#
sql					select count(*) from SVCLUSPPERS
result_varname		cntres
post_qexpression
	this.options.maxrecsteps = 10000000000;
	this.recstoprocess = this.cntres.getCellValue(0,0);
	this.curec = 0;
	this.recsprocessed = 0;
	this.ppush([this.phead("STARTED"),"RECSTOPROCESS="+this.recstoprocess,"",""]);

#
# <<TSTEPNUM:1>>: create table if not yet existing
#
sql					create table if not exists SVCLUSPPERSZP (KUR varchar(32), UPARID varchar(32), UNAME varchar(255), 
                                                             PPARID varchar(32), PVSNR varchar(32), PNAME varchar(255), PSVBPKVER varchar(255))
															 
#
# <<TSTEPNUM:2>>: 
#
nextrec:
if 		(this.curec>=this.recstoprocess)
goto	all_records_done

#
# <<TSTEPNUM:3>>: read one record
#
pre_qexpression
	this.progstat.recstoprocess = this.recstoprocess;
	this.progstat.recsprocessed = this.recsprocessed;
	if ((this.recsprocessed%20)==0) {
		seqtrans_progress_status(this,"GENUSPSTAMM_ROW");
	}
sql					select * from SVCLUSPPERS limit ::curec::,1
named_params		curec
result_varname		rres
post_qexpression
	proc: {
		if (this.rres.getRowCount()!=1) {
			this.errcoll.collect(null,"reading RRECORD # "+this.curec+" delivered wrong number of rows",this.rres);
			break proc;
		}
		this.rdata = this.rres.getRowObject(0);
		/* logger.debug(this.phead("RRECORD")+": "+aux.objTxt(this.rdata)); */
	}

#
# <<TSTEPNUM:4>>: read U master data
#
pre_qexpression
	this.KUR = this.rdata.KUR;
sql					select * from SVCLKURSSTAMM where KUR=::KUR:: LIMIT 0,1
named_params		KUR
result_varname		ures
post_qexpression
	proc: {
		if (this.ures.getRowCount==0) {
			this.udata = { KUR: this.KUR, PARTNERID: null, NAMENSZUSAMMENFASSUNG: null }
		} else if (this.ures.getRowCount()>1) {
			this.errcoll.collect(null,"UDATA for KUR "+this.KUR+" delivered wrong number of rows",this.ures);
			break proc;
		} else {
			this.udata = this.ures.getRowObject(0);
		}
		/* logger.debug(this.phead("UDATA")+": "+aux.objTxt(this.udata)); */
	}

#
# <<TSTEPNUM:5>>: read P master data
#
pre_qexpression
	this.SVBPKVER = this.rdata.SVBPKVER;
sql					select * from SVCLUSPPSTAMM where SV_BKR_VERSCHL=::SVBPKVER::
named_params		SVBPKVER
result_varname		pres
post_qexpression
	proc: {
		if (this.pres.getRowCount()==0) {
			this.pdata = { PARTNERID: null, NAMENSZUSAMMENFASSUNG: null, PARTNERID: null, VSNR: null, SV_BKR_VERSCHL: this.SVBPKVER };
		} else if (this.pres.getRowCount()>1) {
			this.errcoll.collect(null,"PDATA for SVBPKVER in RRECORD # "+this.curec+" delivered wrong number of rows",this.pres);
			break proc;
		} else {
			this.pdata = this.pres.getRowObject(0);
		}
		/* logger.debug(this.phead("PDATA")+": "+aux.objTxt(this.pdata)); */
	}

#
# <<TSTEPNUM:6>>: insert enhanced record into  SVCLUSPPERSZP
#
pre_qexpression
	this.UPARID = this.udata.PARTNERID;
	this.UNAME = this.udata.NAMENSZUSAMMENFASSUNG;
	this.PPARID = this.pdata.PARTNERID;
	this.PVSNR  = this.pdata.VSNR;
	this.PNAME = this.pdata.NAMENSZUSAMMENFASSUNG;
	this.PSVBPKVER = this.pdata.SV_BKR_VERSCHL;
sql		insert into SVCLUSPPERSZP(KUR,UPARID,UNAME,PSVBPKVER,PPARID,PVSNR,PNAME) 
				values(::KUR::,::UPARID::,::UNAME::,::PSVBPKVER::,::PPARID::,::PVSNR::,::PNAME::)
named_params	KUR,UPARID,UNAME,PSVBPKVER,PPARID,PVSNR,PNAME
post_qexpression
	this.curec++;
	this.recsprocessed++;

#
# <<TSTEPNUM:7>>: rinse and repeat
#
goto nextrec

#
# <<TSTEPNUM:8>>: all records done, close protocol
#
qexpression
	this.ppush(["this.phead("FINISHED"),"RECSPROCESSED="+this.recsprocessed,"",""]);
	
#
# <<TSTEPNUM:9>>: end
#
end