#
#			ITSV GmbH
#	CCDB - Command and Control Database
#
#	FILE:			dquersmfile_SVCL52BUILDASSOC.txt
#	DESCRIPTION:	DQUERY definition for DQUERY SVCL52BUILDASSOC
#					builds all possible associations of access to ZUORDnungen
#					from all available USP Personen/Unternehmen-Combinations
#
@querytitle				SVCLEAR-52-0002 - erstelle mögliche Kombinationen - EXTREM LANGE LAUFZEIT
@querydescription		erstellt alle möglichen Zuordnungs-Zugriffs-Kombinationen basierende auf Personen/Unternehmen-Kombinationen<br/>
						ACHTUNG: diese Abfrage läuft bis zu 100 Stunden!!
						aus der Tabelle SVCLUSPPERS
@group					SVCLCASETOOLS
@querytype				function
@function				seqtrans.seqtrans
@FLIST					KUR,UPARID,UNAME,VSNR,PPARID,PNAME,TRAEGER,BKNR,VUMFANG,VOLLMACHTVON,VOLLMACHTBIS,ELDASNR,ZUOID,ZUOINHPARID,ZUOINHROLLE,ZUOVON,ZUOBIS
@puth					1

~query.tsteps

#
# <<TSTEPNUM:0>>: drop table if yet exists
#
sql		drop table if exists SVCL52CASSOC

#
# <<TSTEPNUM:1>>: create table afresh
#
sql create table SVCL52CASSOC(KUR varchar(32), UPARID varchar(32), UNAME varchar(255), 
                              VSNR varchar(32), PPARID varchar(32), PNAME varchar(255), TRAEGER varchar(8),
							  BKNR varchar(32), VUMFANG varchar(32), VOLLMACHTVON varchar(32), VOLLMACHTBIS varchar(32), ELDASNR varchar(32), 
							  ZUOID varchar(32), ZUOINHPARID varchar(32), ZUOINHROLLE varchar(32), ZUOVON varchar(32), ZUOBIS varchar(32))

#
# <<TSTEPNUM:2>>: get base data from SVCLUSPPERS
#
pre_qexpression
	this.options.maxrecsteps = 10000000000;
	this.FLIST = this.query.FLIST;
	this.FTLIST = "::"+this.FLIST.split(",").join("::,::")+"::";
	this.recscreated = 0;
sql			select	ub.KUR,ub.VERFAHRENSRECHT,ub.SVBPKVER, 
					up.PARTNERID as PPARID, up.VSNR, up.NAMENSZUSAMMENFASSUNG as PNAME, 
					uu.PARTNERID as UPARID, uu.NAMENSZUSAMMENFASSUNG as UNAME
			from SVCLUSPPERS ub
				left join SVCLUSPPSTAMM up on up.SV_BKR_VERSCHL=ub.SVBPKVER
				left join SVCLKURSSTAMM uu on uu.KUR = ub.KUR
result_varname		ub
post_qexpression
	this.recstoprocess = this.ub.getRowCount();
	this.recsprocessed = 0;
	this.curecnum = 0;
	this.ppush([this.phead("STARTED"),"RECSTOPROCESS="+this.recstoprocess,"",""]);

#
# <<TSTEPNUM:3>>: check if UB loop complete
#
ub_loop:
if 		(this.curecnum>=this.recstoprocess)
goto	ub_done

#
# <<TSTEPNUM:4>>: process one UB record
#
pre_qexpression
	this.curubrec = this.ub.getRowObject(this.curecnum);
	this.UPARID = this.curubrec.UPARID;
	this.VSNR = this.curubrec.VSNR;
	this.PNAME = this.curubrec.PNAME;
	this.UNAME = this.curubrec.UNAME;
	this.progstat.recstoprocess = this.recstoprocess;
	this.progstat.recsprocessed = this.recsprocessed;
	this.progstat.recscreated = this.recscreated;
	if (this.pcallback && ((this.recsprocessed%100)==0)) this.pcallback(this.progstat);
	logger.debug(this.phead("UBREC.RECSTOPROCESS="+this.recstoprocess+".CURECNUM="+this.curecnum)+".PNAME="+this.curubrec.PNAME+".UNAME="+this.curubrec.UNAME);
gosub			ub_proc_record
params.UPARID	@@UPARID@@
params.VSNR		@@VSNR@@
params.PNAME	@@PNAME@@
params.UNAME	@@UNAME@@
post_qexpression
	this.recsprocessed++;
	this.curecnum++;
then_goto	ub_loop

#
# <<TSTEPNUM:5>>: all UB records done
#
ub_done:
pre_qexpression
	this.ppush([this.phead("FINISHED"),"RECSTOPROCESS="+this.recstoprocess,"RECSPROCESSED="+this.recsprocessed,"RECSCREATED="+this.recscreated]);
end

#
# ------------------------------------------------------------------------------
#	SUBROUTINE:			ub_proc_record
#	INPUT:				UPARID		-	Unternehmens-Partner-ID
#						UNAME		-	Namenszusammenfassung of Unternehmen
#						VSNR		-	VSNR of Person 
#						PNAME		-	Namenszusammenfassung of Person
#
# <<TSTEPNUM:6>>:	get all ELDA-SNRs current UB user has access to
#
ub_proc_record:
pre_qexpression
	this.local.UPARID = this.local.params.UPARID;
	this.local.UNAME  = this.local.params.UNAME;
	this.local.VSNR = this.local.params.VSNR;
	this.local.PNAME = this.local.params.PNAME;
sql				select SERIENNUMMER from SVCLESNRPERS where VSNR=::VSNR::
named_params	VSNR
result_varname	es
post_qexpression
	this.local.estoprocess = this.es.getRowCount();

#
# <<TSTEPNUM:7>>: get all own and bevollmächtigte BKNRs
#
sql				select '' as VUMFANG, '' as VOLLMACHTVON, '' as VOLLMACHTBIS, SVT, BKNR from SVCLBKNRSSTAMM where PARTNERID=::UPARID::
				UNION
				select vo.VUMFANG, vo.BEGINNDAT as VOLLMACHTVON, vo.ENDEDAT as VOLLMACHTBIS, vobk.SVT, vobk.BKNR
					from SVCLZPVOLLMACHTEN vo
						left join SVCLBKNRSSTAMM vobk on vobk.PARTNERID=vo.PARTNERID
				 where vo.PARTNERBEZUGID=::UPARID::
named_params	UPARID
result_varname	bk
post_qexpression
	this.local.bkstoprocess = this.bk.getRowCount();
	this.local.curbknum = 0;
	logger.debug(this.phead("PROC_BK_ES")+".UPARID="+this.local.UPARID+".VSNR="+this.local.VSNR+
	             ".BK_TO_PROCESS="+this.local.bkstoprocess+".ES_TO_PROCESS="+this.local.estoprocess);

#
# <<TSTEPNUM:8>>: process only if at least one ELDA-SNR AND one BKNR
#
if		(this.local.bkstoprocess<1 || this.local.estoprocess<1)
return

#
# <<TSTEPNUM:9>>: process all BKNRs
#
bk_loop:
if 		(this.local.curbknum>=this.local.bkstoprocess)
return

#
# <<TSTEPNUM:10>>: setup processing loop for ELDA-SNRs
#
qexpression
	this.local.curesnum = 0;

#
# <<TSTEPNUM:11>>: process current BKNR against all ELDA-SNRs
#
es_loop:
pre_qexpression
	logger.debug(this.phead("ES_LOOP")+".ES="+this.local.curesnum+"/"+this.local.estoprocess+".BK="+this.local.curbknum+"/"+this.local.bkstoprocess);
if		(this.local.curesnum>=this.local.estoprocess) 
goto	bk_es_done

#
# <<TSTEPNUM:12>>: process current BKNR against current ELDA-SNR
#
pre_qexpression
	this.curbkrec = this.bk.getRowObject(this.local.curbknum);
	this.local.SVT = this.curbkrec.SVT;
	this.local.BKNR = this.curbkrec.BKNR;
	this.local.curesrec	= this.es.getRowObject(this.local.curesnum);
	this.local.SERIENNUMMER = this.local.curesrec.SERIENNUMMER;
	logger.debug(this.phead("BK-ES")+".SVT="+this.local.SVT+".BKNR="+this.local.BKNR+".SERIENNUMMER="+this.local.SERIENNUMMER);
gosub	bkes_proc_record
params.SVT			@@SVT@@
params.BKNR			@@BKNR@@
params.SERIENNUMMER @@SERIENNUMMER@@
post_qexpression
	this.local.curesnum++;

#
# <<TSTEPNUM:13>>:
#	
goto	es_loop

#
# <<TSTEPNUM:14>>: all ELDA-SNR done against current BKNR, move up
#
bk_es_done:
qexpression
	this.local.curbknum++;
then_goto	bk_loop

#
# ------------------------------------------------------------------------------
#	SUBROUTINE:		bkes_proc_record
#	INPUT:			SVT
#					BKNR
#					SERIENNUMMER
#
#	<<TSTEPNUM:15>>:		get all applicable ELDA-SNR to check associations to
#
bkes_proc_record:
pre_qexpression
	this.local.SVT = this.local.params.SVT;
	this.local.BKNR = this.local.params.BKNR;
	this.local.SERIENNUMMER = this.local.params.SERIENNUMMER;
	logger.debug(this.phead("bkes_proc_record")+".recstepcount="+this.recstepcount+".SVT="+this.local.SVT+".BKNR="+this.local.BKNR+".SERIENNUMMER="+this.local.SERIENNUMMER);
sql				select * from SVCLZUORD where TRAEGER=::SVT:: and BKNR=::BKNR:: and SERIENNUMMER=::SERIENNUMMER::
named_params	SVT,BKNR,SERIENNUMMER
result_varname	zuo
post_qexpression
	logger.debug(this.phead("ZUO")+".SVT="+this.local.SVT+".BKNR="+this.local.BKNR+".SNR="+this.local.SERIENNUMMER+".COUNT="+this.zuo.getRowCount());
	this.local.zuostoprocess = this.zuo.getRowCount();
	this.local.curzuonum = 0;
	
#
# <<TSTEPNUM:16>>: ZUO processing loop
#
zuo_proc_loop:
if (this.local.curzuonum>=this.local.zuostoprocess)
return

#
# <<TSTEPNUM:17>>: process one ZUO
#
pre_qexpression
	this.local.curzuorec	= this.zuo.getRowObject(this.local.curzuonum);
	this.local.KUR			= this.curubrec.KUR;
	this.local.UPARID		= this.curubrec.UPARID;
	this.local.UNAME		= this.curubrec.UNAME;
	this.local.VSNR			= this.curubrec.VSNR;
	this.local.PPARID		= this.curubrec.PPARID;
	this.local.PNAME		= this.curubrec.PNAME;
	this.local.TRAEGER		= this.local.SVT;
	this.local.BKNR			= this.local.BKNR;
	this.local.VUMFANG		= this.curbkrec.VUMFANG;
	this.local.VOLLMACHTVON	= this.curbkrec.VOLLMACHTVON;
	this.local.VOLLMACHTBIS	= this.curbkrec.VOLLMACHTBIS;
	this.local.ELDASNR		= this.local.SERIENNUMMER;
	this.local.ZUOID		= this.local.curzuorec.ID;
	this.local.ZUOINHPARID	= this.local.curzuorec.INHPARID;
	this.local.ZUOINHROLLE	= this.local.curzuorec.INHROLLE;
	this.local.ZUOVON		= this.local.curzuorec.AKTIVVONDATNUM;
	this.local.ZUOBIS		= this.local.curzuorec.AKTIVBISDATNUM;
sql				insert into SVCL52CASSOC(@@FLIST@@) values(@@FTLIST@@)
named_params	@@FLIST@@
post_qexpression
	this.recscreated++;
	this.local.curzuonum++;
then_goto	zuo_proc_loop