#
#		ITSV GmbH
#	CCDB - Command and Control Database
#
#	FILE:			dquerymfile_SVCLEXOFFMONTREND.txt
#	DESCRIPTION:	DQUERY definition for DQUERY SVCLEXOFFOMONTREND
#					this query lists monthly values for exOffo-mBGM per ÖGK-LST and in total
#
@querytitle			mBGM Cleaingfall- und ex-Offo Zahlen monatlich
@querydescription	Diese Abfrage zeit die monatlichen Anzahlen von Clearingfällen und exOffo-mBGM aufgeteilt nach ÖGK-Landesstellen und in Summe
@group				SVCLEXOSTAT
@querytype			function
@function			seqtrans.seqtrans
@title				mBGM-Clearingfall-exOffo-Übersicht
@FLIST				REFDATNUM,BZRJAHR,BZRMONAT,TRAEGER,ANZAHLEXOFFO
@FTYPES				DECIMAL(10),DECIMAL(4),DECIMAL(2),VARCHAR(4),DECIMAL(19)
@TLIST				11,12,13,14,15,16,17,18,19
@EOTABNAME			SVCLBEICONEXOFFOMONCOUNT
@CFTABNAME			svclmvbclfsvttag
@EFTABNAME			svclexoffosvttag

~query.tsteps

#
# <<TSTEPNUM:0>>:
#
qexpression
	proc: {
		this.EOTABNAME = this.query.EOTABNAME;
		this.CFTABNAME = this.query.CFTABNAME;
		this.EFTABNAME = this.query.EFTABNAME;
	}


#
# <<TSTEPNUM:1>>: query exOffo data from BEICON export
#
sql					select REFDATNUM,CONCAT(BZRJAHR,'.',SUBSTR(CONCAT('00',BZRMONAT),-2,2)) as "BZR",TRAEGER,ANZAHLEXOFFO from @@EOTABNAME@@
result_varname		eores
post_qexpression
	proc: {
		let ptlres = aux.pivotizeResult(this.eores,		{	pivotize:	{	vcolumns:		[ 'BZR' ],
																			hcolumns:		[ 'TRAEGER' ],
																			value_column:	'ANZAHLEXOFFO',
																			vkeyformat:		"CONCAT",
																			hkeyformat:		"CONCAT",
																			vkeycolname:	"Beitragszeitraum->Landesstelle"
																		}
														}
										);
		let tccn = ptlres.addMetaData({name: 'Total', class: 'vertical'});
		let prcn = ptlres.addMetaData({name: 'ProzentAnteilig', class: 'vertical' });
		let tis = this.query.TLIST.split(",");
		let tcns = new Array();
		let ti, tcn, totalcount, refcount, sc;
		for (ti=0; ti<tis.length; ti++) {
			tcn = ptlres.findColumn(tis[ti]);
			if (tcn<0) {
				this.errcoll.collect(null,"no column in result for TRAEGER \""+tis[ti]+"\"",ptlres);
				break proc;
			}
			tcns.push(tcn);
		}
		for (let ri=0; ri<ptlres.getRowCount(); ri++) {
			totalcount = 0;
			for (ti=0; ti<tcns.length; ti++) {
				totalcount += ptlres.getCellValue(ri,tcns[ti]);
			}
			ptlres.setCellValue(ri,tccn,totalcount);
			if (ri==0) refcount = totalcount;
			if (refcount<1) refcount=0.1;
			ptlres.setCellValue(ri,prcn,Math.floor((totalcount*100)/refcount)+" %");
		}
		ptlres.setResultAttribute("title","Anzahl exOffo-mBGMs der ÖGK per Monat per Landesstelle und Total im Trend aus BEICON Meldungs-Export");
		this.ppush({type: 'result', result: ptlres });
		this.exOffo_total_refcount = refcount;
	}

#
# <<TSTEPNUM:2>>: query exOffo data from exOffo-summary
#
pre_qexpression
	this.cdata.THISMON = aux.nowstring().substring(0,6)+"00";
sql			select TRAEGER,
				   SUBSTR(MELDUNGS_DATUM,1,6) as BZR, 
			       SUM(ANZAHL_EXOFFO_MBGM) as ANZAHL_EXOFFO 
              from  @@EFTABNAME@@
			where MELDUNGS_DATUM>20191000 and MELDUNGS_DATUM<::THISMON::
			 group by TRAEGER,SUBSTR(MELDUNGS_DATUM,1,6)
named_params	THISMON
result_varname	cfres
result_description
	Der niedrige Wert in 2020.02 für Träger 11 und damit total resultiert aus fehlenden Daten der Auswertung_MeldungenClearingfaelle für den Träger 11 aus der TEV am 26.02.2020
result_title
	Anzahl exOffo-mBGM der ÖGK per Monat per Landesstelle und Total im Trend aus Rückfrage-Auswertung
post_qexpression
	proc: {
		let ptlres = aux.pivotizeResult(this.cfres,		{	pivotize:	{	vcolumns:		[ 'BZR' ],
																			hcolumns:		[ 'TRAEGER' ],
																			value_column:	'ANZAHL_EXOFFO',
																			vkeyformat:		"CONCAT",
																			hkeyformat:		"CONCAT",
																			vkeycolname:	"Beitragszeitraum->Landesstelle"
																		}
														}
										);
		let tccn = ptlres.addMetaData({name: 'Total', class: 'vertical'});
		let prcn = ptlres.addMetaData({name: 'ProzentAnteilig', class: 'vertical' });
		let tis = this.query.TLIST.split(",");
		let tcns = new Array();
		let ti, tcn, totalcount, refcount, sc;
		if (this.exOffo_total_refcount) {
			refcount = this.exOffo_total_refcount;
		} else {
			refcount = 0;
		}
		for (ti=0; ti<tis.length; ti++) {
			tcn = ptlres.findColumn(tis[ti]);
			if (tcn<0) {
				this.errcoll.collect(null,"no column in result for TRAEGER \""+tis[ti]+"\"",ptlres);
				break proc;
			}
			tcns.push(tcn);
		}
		for (let ri=0; ri<ptlres.getRowCount(); ri++) {
			totalcount = 0;
			for (ti=0; ti<tcns.length; ti++) {
				totalcount += ptlres.getCellValue(ri,tcns[ti]);
			}
			ptlres.setCellValue(ri,tccn,totalcount);
			if (!refcount) {
				if (ri==0) refcount = totalcount;
				if (refcount<1) refcount=0.1;
			}
			ptlres.setCellValue(ri,prcn,Math.floor((totalcount*100)/refcount)+" %");
		}
		ptlres.setResultAttribute("description",this.tstep.result_description);
		ptlres.setResultAttribute("title",this.tstep.result_title);
		this.ppush({type: 'result', result: ptlres });
	}


#
# <<TSTEPNUM:3>>: query CF data
#
pre_qexpression
	this.cdata.THISMON = aux.nowstring().substring(0,6)+"00";
sql			select TRAEGER,
				   SUBSTR(MELDUNGS_DATUM,1,6) as BZR, 
			       SUM(ANZAHL_MELDUNGEN_MIT_CLEARINGFAELLEN) as ANZAHLCF 
              from  @@CFTABNAME@@
			where MELDUNGS_DATUM>20191000 and MELDUNGS_DATUM<::THISMON::
			 group by TRAEGER,SUBSTR(MELDUNGS_DATUM,1,6)
named_params	THISMON
result_varname	cfres
post_qexpression
	proc: {
		let ptlres = aux.pivotizeResult(this.cfres,		{	pivotize:	{	vcolumns:		[ 'BZR' ],
																			hcolumns:		[ 'TRAEGER' ],
																			value_column:	'ANZAHLCF',
																			vkeyformat:		"CONCAT",
																			hkeyformat:		"CONCAT",
																			vkeycolname:	"Beitragszeitraum->Landesstelle"
																		}
														}
										);
		let tccn = ptlres.addMetaData({name: 'Total', class: 'vertical'});
		let prcn = ptlres.addMetaData({name: 'ProzentAnteilig', class: 'vertical' });
		let tis = this.query.TLIST.split(",");
		let tcns = new Array();
		let ti, tcn, totalcount, refcount, sc;
		refcount = 414800;				/* reference count for total CF in APR-2019 extrapolated from SVT 14 in APR-2019 */
		for (ti=0; ti<tis.length; ti++) {
			tcn = ptlres.findColumn(tis[ti]);
			if (tcn<0) {
				this.errcoll.collect(null,"no column in result for TRAEGER \""+tis[ti]+"\"",ptlres);
				break proc;
			}
			tcns.push(tcn);
		}
		for (let ri=0; ri<ptlres.getRowCount(); ri++) {
			totalcount = 0;
			for (ti=0; ti<tcns.length; ti++) {
				totalcount += ptlres.getCellValue(ri,tcns[ti]);
			}
			ptlres.setCellValue(ri,tccn,totalcount);
			if ((ri==0) && (refcount==0)) {
				refcount = totalcount;
			}
			if (refcount<1) refcount=0.1;
			ptlres.setCellValue(ri,prcn,Math.floor((totalcount*100)/refcount)+" %");
		}
		ptlres.setResultAttribute("title","Anzahl Clearingfälle der ÖGK per Monat per Landesstelle und Total im Trend");
		ptlres.setResultAttribute("description","100% Referenz ist die extrapoliert geschätzte Anzahl von Clearingfällen im April 2019 von 414.800");
		this.ppush({type: 'result', result: ptlres });
	}