#
#			ITSV GmbH
#
#	CCDB - Command and Control Database
#
#	FILE:			dquerymfile_SVCLRFSTATDatAvail.txt
#	DESCRIPTION:	DQUERY definition for DQUERY SVCLRFSTATDatAvail
#					this query shows available data for statistics based on Auswertung_MeldungenClearingfaelle
#
@querytitle			Verfügbare Daten aus Auswertung_MeldungenClearingfaelle
@querydescription	zeigt Tage und Träger an, für die Daten aus Auswertung_MeldungenCleaeringfaelle vorhanden sind
@group				SVCLCFSTATMVBRFAUSW
@querytype			dbselect
@viewdefinitionsql
	--
	--
	-- view svclvmafiles enhances SVCLMAFILES:
	-- - GENDATUM - Date of TEV-run processing the Meldungen
	--              this is derived from the FTIMESTAMP in SVCLMAFILES
	--			 FTIMESTAMP is the point in time when the Auswertung_MeldungenClearingfaelle was started
	--              as the TEV runs over night, this may be on the original day (but then after noon) or on the following day (then being before noon)
	--			 so, GENDATUM is caluclated by:
	--				if FTIMESTAMP is before noon (hour part <12) => GENDATUM is the PREVIOUS day from FTIMESTAMP
	--				if FTIMESTAMP is after noon (hour part >=12) => GENDATUM is the actual day from FTIMESTAMP
	--
	CREATE OR REPLACE VIEW "svclvmafiles" AS 
	select	maf.MFILEID AS "MFILEID","maf"."TRAEGER" AS "TRAEGER",maf."FTIMESTAMP" AS "FTIMESTAMP",
			maf.LOADSTARTED AS "LOADSTARTED","maf"."LOADFINISHED" AS "LOADFINISHED",
			(case	when JSON_EXTRACT(mf.MFILEATTRIBS,'$.GENDATUM') is not null 
						then JSON_EXTRACT(mf.MFILEATTRIBS,'$.GENDATUM')
					when (hour(str_to_date("maf"."FTIMESTAMP",'%Y%m%d%H%i%S')) < 16) 
						then (str_to_date(substr("maf"."FTIMESTAMP",1,8),'%Y%m%d') + interval '-1' day) 
					else  str_to_date(substr("maf"."FTIMESTAMP",1,8),'%Y%m%d') end) AS "GENDATUM" 
	  from SVCLMAFILES maf
	    left outer join CCMFILES mf on mf.MFILEID=maf.MFILEID
	--
	-- view SVCLVMAFILESINFO gives a full calendar (starting fom 01-AUG-2019) with information about available Auswertung_MeldungsnClearingfaelle - Files
	-- - DATUM:			Date in Calendar
	-- - DATNUM:		Date in Calendar, formatted as decimal sequence number like yyyymmdd
	-- - BETRIEBSTAG:	1 if the day is an Arbeitstag (TEV-run), 0 otherwise
	-- - SVT:			Number of Träger with expected data
	-- - TRAEGER:		Number of Träger when data is available for this day, NULL otherwise
	-- - MFILEID:		managed file ID of Auswertungs-File if data is available for this day, NULL otherwise
	-- - FTIMESTAMP:	file timestamp from the data file if available (time when the Auswertungs-part in the TEV was started)
	--
	create or replace view SVCLVMAFILESINFO as
	select cal.DATUM, cal.DATNUM, cal.LEISTUNGSFAKTOR as BETRIEBSTAG, svtnum.TRAEGER as SVT, maf.TRAEGER as TRAEGER, maf.MFILEID, FTIMESTAMP
	  from CCVCALD cal
		left join (select num as TRAEGER from CCNUM where num between 11 and 19) svtnum on 1=1
		left join SVCLVMAFILES maf on maf.GENDATUM=cal.DATUM and maf.TRAEGER=svtnum.TRAEGER
	 where cal.DATNUM>=20190801
	 order by cal.DATUM, svtnum.TRAEGER asc
#
@sql				SELECT DATNUM,SVT,TRAEGER,MFILEID from SVCLVMAFILESINFO where BETRIEBSTAG=1
@post_query
	try {
		that.pres = aux.pivotizeResult(that.result,
								{	pivotize:	{	vcolumns:		[ 'DATNUM' ],
													hcolumns:		[ 'SVT' ],
													value_column:	'MFILEID',
													vkeyformat:		"CONCAT",
													hkeyformat:		"CONCAT",
													vkeycolname:	"DATNUM"
												}
							});
	} catch (e) {
		logger.debug(that.dataname+".ERROR_CAUGHT: "+e.message);
		logger.debug("... callstack: "+e.stack);
	}
	let aranges = new Array();
	let ar = { start: null, end: null };
	let cdatnum;
	let crow;
	let fc, ci, lastact;
	let ri;
	let desc = "";
	for (ri=0; ri<that.pres.getRowCount(); ri++) {
		crow = that.pres.getRowObject(ri);
		cdatnum = crow.DATNUM;
		for (ci=11, fc=0; ci<=19; ci++) {
			if (crow[ci]) {
				if (typeof(crow[ci])=="string") {
					let mfids = crow[ci].split(",");
					if (mfids.length>1) {
						if (!desc) desc = "Issues: ";
						desc += "<br/>MULTIFILE.DATNUM="+crow.DATNUM+".SVT="+ci+".MFILEIDS="+crow[ci];
					}
				}
				fc++;
			}
		}
		if (fc>=9) {
			lastact = cdatnum;
			if (!ar.start) {
				ar.start = cdatnum;
			}
		} else {
			if (ar.start) {
				ar.end = lastact;
				aranges.push(ar);
				ar = { start: null, end: null };
			}
		}
	}
	if ((ar.start) && (!ar.end)) {
		ar.end = cdatnum;
		aranges.push(ar);
	}
	let rows = new Array();
	let row;
	for (let ri=0; ri<aranges.length; ri++) {
		row = new Array();
		row.push(aranges[ri].start);
		row.push(aranges[ri].end);
		rows.push(row);
	}
	that.result = new aux.Result({resulttype: 'dbresult', metaData: [{name: 'START_DATNUM'},{name: 'END_DATNUM'}], rows: rows});
	if (desc) that.result.setResultAttribute("description",desc);