#
#	ITSV GmbH
#	CCDB - Command and Control Database
#
#	FILE:			dquerymfile_repLSWtopCF.txt
#	DESCRIPTION:	DQUERY-Definition for CCDB-Query repLSWtopCF
#					this query reports the top Clearingfall-Rückfragecodes for one specific Lohnsoftware
#	VERSION:		1.4
#	HISTORY:
#	Date     | Version | Author | Description
# -----------+---------+--------+-----------------------------------------------
# 2019-XX-XX |  1.0    | WSC    | Created
# -----------+---------+--------+-----------------------------------------------
#            |  1.2    | WSC    | restrict CF to "EXTERN"
# -----------+---------+--------+-----------------------------------------------
# 2020-10-22 |  1.3    | WSC    | suppress MW2022,MW0120 as these are not sent to LSW
# -----------+---------+--------+-----------------------------------------------
# 2020-10-30 |  1.4    | WSC    | options vondat, bisdat to narrow time range
# -----------+---------+--------+-----------------------------------------------
# 2022-01-07 |  1.5    | WSC    | use SVCLLSWIDRFDATCOUNT to optimize performance
# -----------+---------+--------+-----------------------------------------------
#            |         |        |
# -----------+---------+--------+-----------------------------------------------
#            |         |        |
# -----------+---------+--------+-----------------------------------------------
#
@querytitle			Top-Clearingfall-Rückfragecodes für eine Lohnsoftware im STP-MVB
@querydescription	Listet die Clearingfall-Rückfragecodes aus CCMVBRFAUSW für eine Lohnsoftware (LSWID) in absteigender Häufigkeit
@group				SVCLMVBRFAUSWREPSTAT
@attributenames		LSWID:LSWID,showtechdetail:noyes:{{stdoptions}},vondat:string:{{vondatoptions}},bisdat:string:{{bisdatoptions}}
@stdoptions {
	labeltext:		"Technische Detailerklärung",
	typedesc:		"Bei Ja werden technische Erklärungen zur Ermittlung der Daten mit ausgegeben. Solche Berichte dürfen NUR INNERHALB DER SV verwendet werden!",
	is_optional:	true,
	default:		"N" }
@vondatoptions {
	labeltext:		"Beginndatum",
	typedesc:		"Frühestes Datum, von dem Meldungen in der Auswertung berücksichtigt werden, in der Form YYYYMMDD",
	is_optional:	true,
	default:		"20180101" }
@bisdatoptions {
	labeltext:		"Endedatum",
	typedesc:		"Spätestes Datum, von dem Meldungen in der Auswertung berücksichtigt werden, in der Form YYYYMMDD",
	is_optional:	true,
	default:		"29991231" }
@querytype			function
@function			seqtrans.seqtrans
@sql_select			select LSWID,LSWHID,LSWATTRIBS from SVCLRWLSW where LSWID=::LSWID::
@formpagetitle		Lohnsoftware für Top-Clearingfall-Auswertung auswählen
@init.qexpression
	if (this.query.hasOwnProperty("showtechdetail")) {
		this.query.showtechdetail = aux.boolFromString(this.query.showtechdetail);
	} else {
		this.query.showtechdetail = false;
	}

~query.tsteps

#
# <<TSTEPNUM:0>>: initialize
#
if 		(this.init_done)
goto	init_complete

#
# 1: initialize: setup reporting text flow
#
qexpression
	proc: {
		copycvars(this,"LSWID,LSWHID,LSWATTRIBS");
		if (this.query.vondat) {
			this.vondat = this.query.vondat;
		} else {
			this.vondat = "20180101";
		}
		if (this.query.bisdat) {
			this.bisdat = this.query.bisdat;
		} else {
			this.bisdat = "29991231";
		}
		this.ftd = function(htext) { return "<div style=\"color: #0000C0\">\r\n<h4>Technische Details</h4>\r\n<p>\r\n"+htext+"\r\n</p></div>" };
		this.init_done = true;
	}

#
# <<TSTEPNUM:2>>: check if LSWID is known
#
init_complete:
pre_qexpression
	this.lswattributes = aux.txtObjDecode(this.LSWATTRIBS);
sql				select * from SVCLRWLSW where LSWID=::LSWID::
named_params	LSWID
result_varname	lswres
post_qexpression
	if (this.lswres.getRowCount()<1) {
		this.errcoll.collect(null,"LSWID "+this.LSWID+" is not known in RW SVCLRWLSW");
	}

#
# <<TSTEPNUM:3>>: get records for the LSW in a working table
#
pre_qexpression
	this.temptabname = "TOPCF_TEMP_"+aux.syncUniqueNowID();
	if (this.lswattributes.mvbrfausw_select) {
		this.tempselsql = "create table " + this.temptabname + " as "+this.lswattributes.mvbrfausw_select;
	} else {
		this.tempselsql = 	"create table " + this.temptabname + " as select * from SVCLLSWIDRFDATCOUNT where "+
							"LSWID='"+this.LSWID+"' and RUECKFRAGETYPCODE!='MW2022' and RUECKFRAGETYPCODE!='MW0120' and "+
							"DOKDATNUM between '"+this.vondat+"' and '"+this.bisdat+"'"; 
	}
sql		@@tempselsql@@
introtext
	<p>Diese Auswertung beinhaltet Daten eines begrenzten Zeitraums. Dieser ist untenstehend aufgeführt</p>
	<p>Es sind in dieser Auswertung noch nicht alle Texte zu den Rückfrage-Codes formalisiert aufgelöst. Wir arbeiten daran, alle formalisiert auflösen zu können. In der Zwischenzeit
	werden in der Spalte für den Text Beispiele angegeben.</p>
techdetail
	<h4>Software-Informationen</h4>
	<table border="1">
	<tr><td>Version</td><td>1.5</td></tr>
	<tr><td>Ausgabedatum</td><td>07.01.2022</td></tr>
	<tr><td colspan="2">Historie</td></tr>
	<tr><td>28.10.2019</td><td>Einschränkung auf <code>EXTERN_JN='J'</code> bei Rückfragen</td></tr>
	<tr><td>22.10.2020</td><td>Ausfilterung der Rückfragecodes MW2022 und MW0120, da diese nicht als externe Clearingfälle erzeugt werden</td></tr>
	<tr><td>07.01.2022</td><td>Performance-Optimierung für große Anzahl von Datensätzen in CCMVBRFAUSW</td></tr>
	</table>
	<p>Alle Basisdaten werden in der Tabelle CCMVBRFAUSW gespeichert und von dort aus verwendet.</p>
	<p>Die Basisdaten werden täglich aus den am Ende der STP-MVB TEV laufenden Auswertungen_MeldungenClearingfaelle entnommen. Diese erzeugen eine Datei pro Tag und pro Träger. Eine solche Datei hat einen 
	   Zeitstempel, der den Durchführungszeitpunkt angibt. Dieser liegt im Zeitraum der TEV-Durchführung und kann daher am Abend eines Tages oder dem Morgen des Folgetages liegen.</p>
	<p>Die Einträge werden mit 
		<ul>
		<li> dem MST-Code des Trägers,</li>
		<li>dem Durchführungszeitstempel und</li> 
		<li>der ID der Lohnsoftware, die den Eingangs-Meldungsbestand geliefert hat</li>
	    </ul>
		ergänzt.</p>
	<p>Die ID der Lohnsoftware (LSWID) wird auf Basis des Datenfelds <code>SOID</code> im Auswertungs-Datensatz folgendermaßen ermittelt:</p>
		<ol>
		<li>existiert im Regelwerk SVCLRWSOID ein Eintrag mit der SOID des Datensatzes, dann wird die dort gespeicherte LSWID herangezogen:
		    <ol>
			<li>Die so ermittelte LSWID wird als LSWID im Datensatz aufgenommen</li
			</ol>
		</li>
		<li>existiert keine passende SOID, dann werden alle Einträge im Regelwerk SVCLRWLSW durchgegangen, die ein Attribut <code>soidrex</code> haben:
		    <ol>
			<li>Das Attribut <code>soidrex</code> enthält eine <i>Regular Expression</i>, gegen die die <code>SOID</code> geprüft wird</li>
			</ol>
		<li>Ergibt die <code>soidrex</code> eine Übereinstimmung, so wird angenommen, dass die Lohnsoftware des SVCLRWLSW-Eintrags gemeint war (eventuell eine andere Version)<br/>
		    <ol>
			<li>Die <code>SOID</code> des Datensatzes wird mit der ermittelten LSWID in das Regelwerk SVCLRWSOID aufgenommen.</li>
			<li>Die ermittelte LSWID wird als LSWID im Datensatz aufgenommen</li>
			</ol>
			</li>
		<li>Ergibt die <code>soidrex</code> keine Übereinstimmung, so wird angenommen, dass eine neue, noch nicht bekannte Lohnsoftware vorliegt:
		    <ol>
			<li>Es wird eine neue, eindeutige LSWID <code>LSW_&lt;uniqueID&gt:</code> erzeugt und als neuer Eintrag im Regelwerk SVCLRWLSW angelegt</li>
			<li>Die <code>SOID</code> des Datensatzes wird mit der ermittelten LSWID in das Regelwerk SVCLRWSOID aufgenommen.</li>
			<li>Die ermittelte <code>LSWID</code> wird als LSWID im Datensatz aufgenommen</li>
			</ol>
		</li>
		</ol>
	<p>Beim Verarbeiten der MVB-Auswertungen werden auch in der Tabelle SVCLLSWIDRFDATCOUNT Zähler zu Rückfragen pro LSWID, Erzeugungsdatum und Rückfragetyp-Code geführt</p>
	<p>Diese Zähler werden in dieser Auswertung zur Ermittlung der Zahl der Rückfragen pro Rückfragetyp-Code im betrachteten Zeitraum herangezogen</p>
post_qexpression
	ppush(this,{type: 'html', htext: this.tstep.introtext});
	if (this.tstep.techdetail && this.query.showtechdetail) {
		ppush(this,{type: 'html', htext: this.ftd(this.tstep.techdetail)});
	}

#
# <<TSTEPNUM:4>>: get range
#
sql		select 'Datumsbereich Meldungen von' as "Bereich",MIN(DOKDATNUM) as "Wert" from @@temptabname@@ 
		UNION
		select 'Datumsbereich Meldungen bis', MAX(DOKDATNUM) from @@temptabname@@
		UNION
		select 'Anzahl Rückfragen im Zeitraum', SUM(RFCOUNT) from @@temptabname@@ where RUECKFRAGETYPCODE!=''
result_varname	rangeres
description
	<ul>
	<li>Datumsbereich Meldungen: dies ist der Zeitraum, aus dem Meldungen in die Auswertung herangezogen wurden.
	    Dieser kann auch groß sein, da Rückfragen zu weit zurückliegenden Meldungen entstehen können.</li>
	<li>Anzahl Rückfragen im Zeitraum: Das ist die Anzahl der Rückfragen in den Clearingfällen. Ein Clearingfall kann eine oder mehrere Rückfragen enthalten</li>
	</ul>
techdetail
	<ul>
	<li>Der Datumbereich der Meldungen wird aus den <code>DOKDATNUM</code> Eigenschaften der Datensätze ermittelt.</li>
	<li>Anzahl der Rückfragen sind die Anzahl der Rückfragen aus  mit nicht-leerem <code>RUECKFRAGETYPCODE</code> aus dem Betrachtungszeitraum</li>
	<li>Rückfragen werden nur berücksichtigt, wenn sie extern sind (<code>EXTERN_JN</code>=<code>J</code>)</li>
	</ul>
post_qexpression
	this.rangeres.title = "Betrachtete Bereiche";
	this.rangeres.description = this.tstep.description;
	if (this.tstep.techdetail && this.query.showtechdetail) this.rangeres.description += this.ftd(this.tstep.techdetail);
	ppush(this,{ type: 'result',	result: this.rangeres });

#
# <<TSTEPNUM:5>>: list SOIDs used
#
sql_old_014				select distinct SOID from @@temptabname@@ where SOID is not null and SOID!=''
sql			select distinct rfc.LSWID, lsw.*, soid.*
			  from @@temptabname@@ rfc
			  join svclrwlsw lsw on lsw.LSWID=rfc.LSWID
			  join svclrwsoid soid on soid.LSWID = lsw.LSWID
			  order by lsw.LSWID asc 
result_varname	soidres
post_qexpression
	if (this.soidres.getRowCount()>0) {
		this.soidres.title = "erkannte Software-ID(SOID)-Werte zur Berücksichtigung";
		this.soidres.description =	"In dieser Liste sind alle Werte aufgeführt, die in Meldungen im Datenfeld \"SOID\" übermittelt wurden, nicht leer sind "+
									"und als zur Lohnsoftware \""+this.LSWID+"\" gehörig erkannt wurden";
		if (this.lswattributes.mvbrfausw_select) {
			this.soidres.description += '<p>Die Auswahl der Rückfragen ist für diese Lohnsoftware besonders und beinhaltet nicht nur <code>SOID</code></p>';
		}
		if (this.tstep.techdetail && this.query.showtechdetail) this.soidres.description += this.ftd(this.tstep.techdetail);
		ppush(this,{type: 'result', result: this.soidres });
	} else {
		ppush(this,"Die Auswahl der Daten für diese Auswertung wurde nicht auf Grund der <code>SOID</code> vorgenommen");
		if (this.lswattributes.ident_qexpression) {
			ppush(this,"Die logische Bedingung für die Auswahl der Datensätze für die Lohnsoftware \""+this.LSWID+"\" ist: \"<code>"+this.lswattributes.ident_qexpression+"</code>\"");
		}
		if (this.lswattributes.mvbrfausw_select) {
			ppush(this,"Die Auswahl der Datensätze ist für diese Lohnsoftware besonders und beinhaltet nicht nur <code>SOID</code>");
		}
	}

#
# <<TSTEPNUM:6>>: get TOP RF along with descriptive data from SVCLRWRFCMX - for all types with more than 10 occurences
#
gosub				gentopres
params.minrfcount	10

#
# <<TSTEPNUM:7>>: if not much data, report ALL counts
#
if (this.topres.getRowCount()>5)
goto got_top

#
# <<TSTEPNUM:8>>: get TOP RF along with descriptive data from SVCLRWRFCMX - FOR ALL counts 
#
gosub				gentopres
params.minrfcount	0

#
# <<TSTEPNUM:9>>: drop temporary table
#
got_top:
sql		drop table @@temptabname@@
	
#
# <<TSTEPNUM:10>>: title the protocol, seqtrans will produce a result from it
#
qexpression
	this.protocol.title = 'Top-Clearingfälle für Lohnsoftware "'+this.LSWID+'"';

#
# <<TSTEPNUM:11>>: end of processing sequence
#
end

#
# ==============================================================================
#			SUBROUTINES
# ==============================================================================
#
# ------------------------------------------------------------------------------
#
#		SUBROUTINE:		gentopres
#		INPUT:			minrfcount	-	minimum count of occurences for a RUCKFRAGETYPCODE to be included in report
#		DESCRIPTION:	generate a report result about the top RF counts for the LSWID 
#
# <<TSTEPNUM:12>>:
#
gentopres:
pre_qexpression
	this.local.minrfcount = this.local.params.minrfcount;
sql_old_014		select RUECKFRAGETYPCODE,COUNT(RUECKFRAGETYPCODE) as ANZAHL_RUECKFRAGEN, 
		(CASE WHEN rfc.RUECKFRAGETEXT is not null THEN rfc.RUECKFRAGETEXT
		 ELSE CONCAT('Beispiel: ',(select RUECKFRAGETEXTGESAMTSABE from CCMVBRFAUSW where RUECKFRAGETYPCODE=aw.RUECKFRAGETYPCODE LIMIT 1))
		 END) as TEXT_DER_RUECKFRAGE, 
		 (CASE WHEN rfc.VARIABLEFELDER is null THEN '-----'
		       WHEN rfc.VARIABLEFELDER='' THEN '-----'
		   ELSE rfc.VARIABLEFELDER END) as VARIABLE_FELDER
		  from @@temptabname@@ aw
		  left outer join SVCLRWRFC rfc on rfc.CODE = aw.RUECKFRAGETYPCODE
		where (RUECKFRAGETYPCODE is not null and RUECKFRAGETYPCODE!='') and EXTERN_JN='J'
		group by RUECKFRAGETYPCODE, rfc.RUECKFRAGETEXT, rfc.VARIABLEFELDER 
		having COUNT(RUECKFRAGETYPCODE)>@@minrfcount@@
		order by count(RUECKFRAGETYPCODE) desc
		-- ----------------------------------------------------------
sql		select	LSWID,RUECKFRAGETYPCODE,SUM(RFCOUNT) as ANZAHL_RUECKFRAGEN, 
				(CASE WHEN rfc.RUECKFRAGETEXT is not null and rfc.RUECKFRAGETEXT!='' THEN rfc.RUECKFRAGETEXT 
				      WHEN rfc.BEISPIELTEXT is not null and rfc.BEISPIELTEXT!='' THEN CONCAT('Beispiel: ',rfc.BEISPIELTEXT) 
					  ELSE '-----' END) as TEXT_DER_RUECKFRAGE, 
				(CASE WHEN rfc.VARIABLEFELDER is null THEN '-----'
					WHEN rfc.VARIABLEFELDER='' THEN '-----'
					ELSE rfc.VARIABLEFELDER END) as VARIABLE_FELDER		
		  from @@temptabname@@ aw
		  left outer join SVCLRWRFCMX rfc on rfc.CODE = aw.RUECKFRAGETYPCODE
		 where (RUECKFRAGETYPCODE is not null and ((RUECKFRAGETYPCODE!='') and (RUECKFRAGETYPCODE!='MW2022') and (RUECKFRAGETYPCODE!='MW0120')))
		 group by LSWID, RUECKFRAGETYPCODE, TEXT_DER_RUECKFRAGE, VARIABLE_FELDER
		 having ANZAHL_RUECKFRAGEN>@@minrfcount@@
		 order by ANZAHL_RUECKFRAGEN desc
named_params	LSWID
result_varname	topres
toptext			Diese Aufstellung beinhaltet alle SV-externen Rückfragetypen, die in Clearingfällen im Betrachtungszeitraum vorkommen
techdetail
	<ul>
	<li>Die Abfrage zählt Rückfragen (=Zähler in SVCLLSWIDRFDATCOUNT) zu einer bestimmten Lohnsoftware, ungeachtet ihrer Zugehörigkeit zu Meldungen.</li>
	<li>Gezählt wird über den gesamten Datenbestand von SVCLLSWIDRFDATCOUNT, eingeschränkt auf den ausgewählten Zeitraum.</li>
	<li>Es werden nur Ergebnisse aufgenommen, die mehr als @@minrfcount@@ Rückfragen des Typs haben</li>
	<li>Gezählt in SVCLLSWIDRFDATCOUNT werden nur externe Rückfragen (<code>EXTERN_JN='J'</code>)</li>
	<li>Rückfragen mit Typcodes MW2022 und MW0120 sind ausgenommen, da sie ungeachtet von <code>EXTERN_JN</code>nicht als externe Clearingfälle erzeugt werden</li>
	<li>Die Ergebnisse werden absteigend nach Anzahl der vorgekommenen Rückfragen des Typs sortiert.</li>
	</ul>
post_qexpression
	proc: {
		let vfcn = this.topres.findColumn("VARIABLE_FELDER");
		let rtcn = this.topres.findColumn("TEXT_DER_RUECKFRAGE");
		if ( /* (vfcn<0) || */ (rtcn<0)) {
			this.errcoll.collect(null,"topres does not have the correct columns",{topres: topres, vfcn: vfcn, rtcn: rtcn});
			break proc;
		}
		if (vfcn>=0) {
			let vf;
			for (let ri=0; ri<this.topres.getRowCount(); ri++) {
				/* VARIABLE_FELDER may contain line breaks, make the HTML-conformant */
				vf = this.topres.getCellValue(ri,vfcn);
				vf = vf.replace(/\r\n/g,"\n");	/* first, convert CRLF to LF   */
				vf = vf.replace(/\n\r/g,"\n");	/* second, convert LFCR to LF  */
				vf = vf.replace(/\r/g,"\n");	/* finally, convert CR to LF, now there should be only LF left */
				vf = vf.replace(/\n/g,"<br/>"); /* convert newlines to HTML line breaks */
				this.topres.setCellValue(ri,vfcn,vf);
			}
		}
		this.topres.title = "Häufigste Rückfragecodes";
		if (this.tstep.toptext) this.topres.toptext = this.tstep.toptext;
		if (this.tstep.techdetail && this.query.showtechdetail) this.topres.toptext += this.ftd(this.tstep.techdetail);
		if (this.topres.getRowCount()>5) {
			this.ppush({ type: 'result', result: this.topres });
		} else {
			this.ppush('Zu wenige Rückfragetypen (<=5), die im Betrachtungszeitraum mehr als '+this.local.minrfcount+' Mal vorkommen');
		}
	}
then_return