#
#	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
# -----------+---------+--------+-----------------------------------------------
#
@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 = "20180101";
		}
		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 CCMVBRFAUSW where "+
							"LSWID='"+this.LSWID+"' and RUECKFRAGETYPCODE!='MW2022' and RUECKFRAGETYPCODE!='MW0120' and "+
							"DOKDAT_DATNUM 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.01</td></tr>
	<tr><td>Ausgabedatum</td><td>28.10.2019</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>
	</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>
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(DOKDAT_DATNUM) as "Wert" from @@temptabname@@ 
		UNION
		select 'Datumsbereich Meldungen bis', MAX(DOKDAT_DATNUM) from @@temptabname@@
		UNION
		select 'Datumsbereich Rückfragen von', MIN(FTIMESTAMP) from @@temptabname@@ where EXTERN_JN='J'
		UNION
		select 'Datumsbereich Rückfragen bis', MAX(FTIMESTAMP) from @@temptabname@@ where EXTERN_JN='J'
		UNION
		select 'Anzahl Meldungen im Zeitraum', count(distinct DOKUMENT_ID) from @@temptabname@@
		UNION
		select 'Anzahl Meldungen mit Clearingfällen im Zeitraum', count(distinct DOKUMENT_ID) from @@temptabname@@ where RUECKFRAGETYPCODE!='' and EXTERN_JN='J'
		UNION
		select 'Anzahl Rückfragen im Zeitraum', count(*) from @@temptabname@@ where RUECKFRAGETYPCODE!='' and EXTERN_JN='J'
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>Datumsbereich Rückfragen: dies ist der Zeitraum, in dem die Rückfragen entstanden sind, die in dieser Auswertung berücksichtigt wurden.</li>
	<li>Anzahl Meldungen im Zeitraum: Das ist die Anzahl der von Dienstgebern im Zeitraum erstellten Meldungen. Zu jeder Meldung können kein oder ein Clearingfall enstehen.</li>
	<li>Anzahl Meldungen mit Clearingfällen: Das ist der Anteil der Gesamt-Anzahl von Meldungen, zu denen Clearingfälle entstanden sind</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>DOKUMENT_DAT</code> Eigenschaften der Datensätze ermittelt.</li>
	<li>Der Datumsbereich der Rückfragen wird aus den <code>FTIMESTAMP</code> Eigenschaften der Datensätze ermittelt (Verarbeitungsdatum der Auswertung)</li>
	<li>Anzahl der Meldungen im Zeitraum wird über die Anzahl der eindeutigen <code>DOKUMENT_DAT</code> ermittelt</li>
	<li>Anzahl der Meldungen mit Clearngfällen wir über die Anzahl der eindeutigen <code>DOKUMENT_DAT</code>, zu denen es Datensätze mit nicht-leerem <code>RUECKFRAGETYPCODE</code> gibt, ermittelt</li>
	<li>Anzahl der Rückfragen sind die Anzahl der Datensätze mit nicht-leerem <code>RUECKFRAGETYPCODE</code></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				select distinct SOID from @@temptabname@@ where SOID is not null and SOID!=''
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 Meldungs-Datensätze 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 SVCLRWRFC - for all types with more than 10 occurences
#
sql		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)>10
		order by count(RUECKFRAGETYPCODE) desc
named_params	LSWID
result_varname	topres
toptext			Diese Aufstellung beinhaltet Rückfragetypen, die in mehr als 10 Rückfragen vorkommen.
techdetail
	<ul>
	<li>Die Abfrage zählt Rückfragen (=Einträge in CCMVBRFAUSW) zu einer bestimmten Lohnsoftware, ungeachtet ihrer Zugehörigkeit zu Meldungen.</li>
	<li>Gezählt wird über den gesamten Datenbestand von CCMVBRFAUSW.</li>
	<li>Es werden nur Ergebnisse aufgenommen, die mehr als 10 Rückfragen des Typs haben</li>
	<li>Gezählt werden nur externe Rückfragen (<code>EXTERN_JN='J'</code>)</li>
	<li>Rückfragen mit Typcodes MW2022 und MW0120 sind ausgenommen, da sie 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;
		}
		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 10 Mal vorkommen');
		}
	}
	
#
# <<TSTEPNUM:7>>: if not much data, report ALL counts
#
if (this.topres.getRowCount()>5)
goto got_top

#
# 8: get TOP RF along with descriptive data from SVCLRWRFC - FOR ALL counts 
#
sql		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)>0
		order by count(RUECKFRAGETYPCODE) 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 (=Einträge in <code>CCMVBRFAUSW</code>) zu einer bestimmten Lohnsoftware, ungeachtet ihrer Zugehörigkeit zu Meldungen (DOKUMENT_ID).</li>
	<li>Gezählt wird über den alle Datensätze in <code>CCMVBRFAUSW</code>, die von der ausgewählten Lohnsoftware (<code>LSWID</code>) übermittelt wurden.</li>
	<li>Gezählt werden nur externe Rückfragen (<code>EXTERN_JN='J'</code>)</li>
	<li>Zu jedem <code>RUECKFRAGETYPCODE</code> wird die Anzahl der Datensätze, in denen er vorkommt, ermittelt.
	<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;
		}
		let vf;
		for (let ri=0; ri<this.topres.getRowCount(); ri++) {
			/* VARIABLE_FELDER may contain line breaks, make them 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 = "Alle SV-externenRückfragecodes, häufigere zuerst";
		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()>0) {
			ppush(this,{ type: 'result', result: this.topres });
		} else {
			ppush(this,'Keine Rückfragetypen, die im Betrachtungszeitraum vorkommen');
		}
	}
		
#
# <<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+'"';