#
#		ITSV GmbH
#	CCDB - Command and Control Database
#
#	FILE:				dquerymfile_SVCL52CABPK.txt
#	DESCRIPTION:	DQUERY definition for DQUERY SVCL52CABPK
#					this query creates a new table SVCL52CASSOC with a SVBPKVER to the USP-Person-VSNR
#
@querytitle			Ergänze SVCL52CASSOC um das SV-bPK der USP-Person-VSNR
@querydescription	erzeugt eine neue SVCL52CASSOC mit dem SV-bPK der USP-Person aus der Tabelle SVCLUSPPERSZP
@group				SVCLCASETOOLS
@oldtable			SVCL52CASSOC
@newtable			SVCL52CASSOCT
~query.tsteps

#
# <<TSTEPNUM:0>>: determine records to process
#
pre_qexpression
	this.options.maxrecsteps = 10000000;
	this.oldtable = this.query.oldtable;
	this.newtable = this.query.newtable;
	this.psize = 100;
sql					select count(*) from @@oldtable@@
result_varname		rcres
post_qexpression
	this.recstoprocess = Number(this.rcres.getCellValue(0,0));
	this.ppush([this.phead("START"),"recstoprocess="+this.recstoprocess,"time="+aux.nowsvtime(),""]);
	
#
# <<TSTEPNUM:1>>: cache USP-Person VSNR and SVBPKVER in memory
#
sql				select PVSNR,PSVBPKVER from SVCLUSPPERSZP
result_varname	upres
post_qexpression
	this.ups = {};
	let ro;
	this.upcnt = 0;
	for (let ri=0; ri<this.upres.getRowCount(); ri++) {
		ro = this.upres.getRowObject(ri);
		this.ups[ro.PVSNR] = { PSVBPKVER: ro.PSVBPKVER };
		this.upcnt++;
	}
	this.ppush([this.phead("UPCACHED"),"USP-Persons-Cached="+this.upcnt,"",""]);

#
# <<TSTEPNUM:2>>: drop new table if present
#
sql					drop table if exists @@newtable@@

#
# <<TSTEPNUM:3>>: create new table by selecting one record from old plus additional column SVBPKVER
#
sql		create table @@newtable@@ as select old.* from @@oldtable@@ old limit 1,1

#
# <<TSTEPNUM:4>>: add new SVBPKVER column
#
sql		alter table @@newtable@@ add SVBPKVER varchar(255) NOT NULL

#
# <<TSTEPNUM:5>>: leave only structure in new table, will be filled later in rloop
#
sql		truncate @@newtable@@

#
# <<TSTEPNUM:6>>: setup loop
#
qexpression
	this.recsprocessed = 0;
	this.curpart = 0;
	this.cpname = aux.DEC(this.curpart,6,6);

#
# <<TSTEPNUM:7>>: check if complete
#
rloop:
if		(this.recsprocessed>=this.recstoprocess)
goto	parts_done

#
# <<TSTEPNUM:8>>: process next part, drop existing old part table
#
pre_qexpression
	this.nextrecord = this.recsprocessed+1;
sql		drop table if exists @@oldtable@@_@@cpname@@

#
# <<TSTEPNUM:9>>: create next old part table
#
sql		create table @@oldtable@@_@@cpname@@ as select * from @@oldtable@@ limit @@nextrecord@@,@@psize@@;

#
# <<TSTEPNUM:10>>: remove existing new part table 
#
sql			drop table if exists @@newtable@@_@@cpname@@

#
# <<TSTEPNUM:11>>: transvert part table
#
sql				select * from @@oldtable@@_@@cpname@@
result_varname	pres
post_qexpression
	this.precs = this.pres.getRowCount();
	this.tcns = new Array();
	for (let ci=0; ci<this.pres.getColumnCount(); ci++) {
		this.tcns.push(this.pres.getMetaDataName(ci));
	}
	this.tcns.push("SVBPKVER");
	this.TNLIST = this.tcns.join(",");
	this.TFLIST = "::"+this.tcns.join("::,::")+"::";
	this.prnum = 0;

#
# <<TSTEPNUM:12>>: check part record loop
#
prloop:
if 		(this.prnum>=this.precs)
goto	pr_done
 
#
# <<TSTEPNUM:13>>: enhance one part record
#
pre_qexpression
	this.ro = this.pres.getRowObject(this.prnum);
	for (let cnam in this.ro) this[cnam] = this.ro[cnam];
	this.SVBPKVER = this.ups[this.VSNR].PSVBPKVER || '';
sql				insert into @@newtable@@(@@TNLIST@@) values(@@TFLIST@@)
named_params	@@TNLIST@@

#
# <<TSTEPNUM:14>>: repeat for next part record
#
pre_qexpression
	this.prnum++;
goto	prloop

#
# ------------------------------------------------------------------------------
#
# <<TSTEPNUM:15>>: drop old part table 
#
pr_done:
sql			drop table @@oldtable@@_@@cpname@@

#
# <<TSTEPNUM:16>>: one part done
#
qexpression
	this.recsprocessed += this.psize
	logger.debug(this.phead("PART"+this.cpname)+".recsprocessed="+this.recsprocessed);

#
# <<TSTEPNUM:17>>: rinse and repeat
#
pre_qexpression
	this.curpart++;
	this.cpname = aux.DEC(this.curpart,6,6);
goto	rloop

#
# ------------------------------------------------------------------------------
#
# <<TSTEPNUM:18>>: complete, rename old table 
#
parts_done:
pre_qexpression
	this.tt = aux.uniqueNowID();
	this.savetable = this.oldtable+"_"+this.tt;
sql		rename table @@oldtable@@ to @@savetable@@


#
# <<TSTEPNUM:19>>: complete, rename new table
#
sql		rename table @@newtable@@ to @@oldtable@@
post_qexpression
	this.ppush([this.phead("COMPLETE"),"recsprocessed="+this.recsprocessed,"saved_table="+this.savetable,"time="+aux.nowsvtime(),""]);

#
# <<TSTEPNUM:20>>: end of process
#
end