#
#		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 zeigt 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:
#		1) from BEICON export for months < 2019.11, 
#		2) from MVBRFAUSW-exOffo-Summary for months >= 2019.11
#
pre_qexpression
	this.cdata.THISMON = aux.nowstring().substring(0,6)+"00";
sql				select	TRAEGER,	CONCAT(BZRJAHR,'.',SUBSTR(CONCAT('00',BZRMONAT),-2,2)) as "BZR",			ANZAHLEXOFFO 
				  from	@@EOTABNAME@@
				 where	CONCAT(BZRJAHR,'.',SUBSTR(CONCAT('00',BZRMONAT),-2,2))<'2019.11'
				UNION
				select	TRAEGER,	CONCAT(SUBSTR(MELDUNGS_DATUM,1,4),'.',SUBSTR(MELDUNGS_DATUM,5,2)) as BZR,	SUM(ANZAHL_EXOFFO_MBGM) as ANZAHLEXOFFO 
				  from  @@EFTABNAME@@
				 where	MELDUNGS_DATUM>20191100 and MELDUNGS_DATUM<::THISMON::
				 group by TRAEGER,MELDUNGS_DATUM
named_params	THISMON
result_varname		eores
result_title
	Anzahl exOffo-mBGMs der ÖGK per Monat per Landesstelle und Total im Trend
result_description
	<ul>
	<li>Die Daten für diese Auswertung stammen 
		<ul>
		<li>für die Monate 01.2019 bis 10.2019 aus BEICON mit Summen-Auswertung aus STP-MVB-Meldungedaten:
			<ul>
			<li>Summiert nach Beitragszeitraum = Monat. Je Monat wurde summiert:</li>
			<li>Herkunft: ex-Offo</li>
			<li>Satzart:
				<ul>
				<li>G1 (BMBGMO - Monatliche Beitragsgrundlagenmeldung Original Selbstabrechner) oder </li>
				<li>G5 (BMBGOK - Monatliche Beitragsgrundlagenmeldung Original Selbstabrechner - kürzer als ein Monat vereinbarte Beschäftigung)</li>
				</ul>
			</ul></li>
		<li>für 11.2019 und die folgenden Monate aus Summendaten aus der <i>Auswertung_MeldungenClearingfaelle</i>:
			<ul>
			<li>Summe der Anzahl der Meldungen nach Beitragszeitraum=Meldungs-Eingangs-Monat:</li>
			<li>Anzahl aller Meldungen mit zumindest einer Rückfrage vom Typ <code>BW1916</code></li>
			</ul></li>
		</ul>
	<li>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</li>
	</ul>
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("description",this.tstep.result_description);
		ptlres.setResultAttribute("title",this.tstep.result_title);
		this.ppush({type: 'result', result: ptlres });
		this.exOffo_total_refcount = refcount;
	}

#
# <<TSTEPNUM:2>>: 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 });
	}