#
#		ITSV GmbH
#	CCDB - Command and Control Database
#
#	FILE:			dquerymfile_PivotTest.txt
#	DESCRIPTION:	DQUERY definition for DQUERY PivotTest
#					This DQUERY tests the aux.pivotizeResult() function
#
@querytitle			Pivot-Test
@querydescription	Testet die aux.pivotizeResult() Funktion
@group				TEST
@querytype			function
@function			seqtrans.seqtrans
@title				Ergebnis von PivotTest

~query.tsteps

#
# <<TSTEPNUM:0>>:	prepare a result-object for the tests
#
qexpression
	let ptr = {	resulttype:		'dbresult' };
	let nr,rc,ri,ci;
	ptr.metaData = new Array();
	for (ci=0; ci<26; ci++) {
		ptr.metaData.push({ name: "Column"+String.fromCodePoint("A".codePointAt(0)+ci) });
	}
	ptr.rows = new Array();
	for (let ri=0; ri<100; ri++) {
		nr = new Array();
		for (let ci=0; ci<26; ci++) {
			rc = String.fromCodePoint("A".codePointAt(0)+ci)+ri;
			nr.push(rc);
		}
		ptr.rows.push(nr);
	}
	this.ptres = new aux.Result(ptr);
	this.ptres.title = "Testdaten für PivotTest";
	/* ppush(this,{ type: 'result', result: this.ptres }); */
	
#
# <<TSTEPNUM:1>>:	test sample from CCDB
#
sql				select * from CCILVD where JAHR=2019
result_varname	ptdres
post_qexpression
	this.ptdres.title = "Testdaten aus CCILVD für PivotTest";
	this.ptdresd = new aux.Result(this.ptdres);
	let cc = 0;
	while (this.ptdresd.getRowCount()>20) {
		this.ptdresd.rows.pop();
		cc++;
	}
	this.ptdresd.title = "Testdaten aus CCILVD für PivotTest (um "+cc+" Einträge gekürzt)";
	ppush(this,{ type: 'result', result: this.ptdresd })

#
# <<TSTEPNUM:2>>:	test "select distinct"
#
qexpression
	this.sdres = aux.select_result_data(this.ptdres,	{	select:	{	cmd:		"select distinct",
																		columns:	["JAHR","MONAT"]	}});
	this.sdres.setResultAttribute("title","select_distinct über CCILVD-Testdaten");
	ppush(this,{type: "result", result: this.sdres});
	
#
# <<TSTEPNUM:3>>:	pivotize along 2+2+1 columns
#
qexpression
	this.pt1res = aux.pivotizeResult(this.ptdres,	{	pivotize:	{	vcolumns: [ 'JAHR', 'MONAT' ],
																		hcolumns: [ 'PSP', 'PSPDETAIL', 'USKURZZ' ],
																		value_column: 'STUNDEN'
																	}
													}
									);
	this.pt1res.title = "Pivottest #1: 2+2+1 Spalten";
	ppush(this,{ type: 'result', result: this.pt1res });