#
#				ITSV GmbH
#	CCDB _ COmmand and Control Database
#
#	FILE:				dquerymfile_REFMVBRFAUSWSUM.txt
#	DESCRIPTION:		DQUERY definition for DQUERY REFMVBRFAUSWSUM
#						refreshes (=re-creates) the summary tables for MVB_Auswertung_Clearingfälle
#						this was earlier part of LOADMVBRFAUSWMULTI, bu can be removed there as 
#						each individual LOADMVBRFAUSW updates the summary counters without the need 
#						to re-create the complete summary tables
#						This Query is only needed when a complete rebuild of the summary tables is desired
#
@querytitle				Auswertung_MeldungenClearingfaelle-Summen neu erstellen
@querydescription		Löscht die Summen-Tabellen und erstellt sie neu von Grund auf
@group					SVCLMVBRFAUSWDATALOAD
@querytype				function
@function				seqtrans.seqtrans

~query.tsteps

#
# <<TSTEPNUM:0>>: get performance data from last summary table recreation
#
pre_qexpression
	this.cdata.dataname = this.query.dataname;
sql
	select LTIME,LCLASS,LCODE,LDESC,LUNIT,LVALUE from CCPLOG 
	 where LCLASS='CPERF' and LCODE=::dataname:: 
       and LTIME=(select MAX(LTIME) from CCPLOG WHERE LCLASS='CPERF' and LCODE=::dataname::)
named_params	dataname
result_varname	lpres
post_qexpression
	this.last_timing = {};
	let lt = this.last_timing;
	if (this.lpres.getRowCount()>0) {
		let flag = 0;
		lt.desc = this.lpres.getCellValue(0,3);
		lt.unit = this.lpres.getCellValue(0,4);
		lt.count = this.lpres.getCellValue(0,5);
		lt.descobj = aux.txtObjDecode(lt.desc);
		if (lt.descobj.RECORD_COUNT) {
			lt.records_processed = lt.descobj.RECORD_COUNT;
			flag += 1;
		} else {
			lt.records_processed = 'NO_RECORDS_COUNT_IN_CCPLOG_DESC_FOR_'+this.cdata.dataname;
		} 
		if (lt.descobj.ACTION) {
			lt.action = lt.descobj.ACTION;
			flag += 1;
		} else {
			lt.action = 'NO_ACTION_IN_CCPLOG_DESC_FOR_'+this.cdata.dataname;
		}
		if (flag>=2) {
			delete lt.desc;
			delete lt.descobj;
		}
	} else {
		lt.desc = 'No data got for last ' + this.query.dataname + ' in CCPLOG';
	}
	this.hint = "Last time it took "+lt.count+lt.unit;
	this.ppush([this.phead("GETLASTPLOG"),this.hint,(lt.desc?(".DESC=\""+lt.desc+"\""):""),"LAST_TIMING: "+aux.objTxt(this.last_timing)]);
	this.rcstartts = 0+aux.nowstring();
	this.ppush([this.phead("willcreatesummary"),"TIME="+aux.nowsvtime(),"",""]);

#
# <<TSTEPNUM:1>>: drop Meldungen summary table
#
sql					drop table if exists SVCLMVBRFASVTTAG
result_varname		mcsres
post_qexpression
	this.ppush([this.phead("MELDSUMDROPPED"),"TIME="+aux.nowsvtime(),"dropped Meldungen-summary table SVCLMVBRFASVTTAG",this.mcsres]);
	

#
# <<TSTEPNUM:2>>: re-create Meldungen-summary table
#
sql		create table SVCLMVBRFASVTTAG as
		select distinct rfa.traeger as TRAEGER, rfa.DOKUMENT_DAT, rfa.DOKDAT_DATNUM as MELDUNGS_DATUM, count(distinct rfa.DOKUMENT_ID) as ANZAHL_MELDUNGEN
		  from CCMVBRFAUSW rfa
		 group by rfa.TRAEGER,rfa.DOKUMENT_DAT, rfa.DOKDAT_DATNUM
		 order by rfa.TRAEGER,rfa.DOKDAT_DATNUM asc
pinfo	Recreating Meldungen-summary, Expected time for total summary re-creation from last run: @@hint@@
post_qexpression
		this.ppush([this.phead("MELDSUMMARYRECREATED"),"TIME="+aux.nowsvtime(),"re-created Clearingfall-Summary table svclmvbclfsvttag",this.cfsres]);

#
# <<TSTEPNUM:3>>: drop old Clearingfall-summary table
#
sql				drop table if exists svclmvbclfsvttag
result_varname	dcsres
post_qexpression
		this.ppush([this.phead("CFSUMMARYDROPPED"),"TIME="+aux.nowsvtime(),"dropped Clearingfall-Summary table svclmvbclfsvttag",this.dcsres]);
		
#
# <<TSTEPNUM:4>>: re-create Clearingfall-summary table
#
sql		create table svclmvbclfsvttag as
		select distinct rfa.traeger as TRAEGER, rfa.DOKUMENT_DAT, rfa.DOKDAT_DATNUM as MELDUNGS_DATUM, count(distinct rfa.DOKUMENT_ID) as ANZAHL_MELDUNGEN_MIT_CLEARINGFAELLEN
		  from CCMVBRFAUSW rfa
		where rfa.RUECKFRAGETYPCODE!=''
		group by rfa.TRAEGER,rfa.DOKUMENT_DAT, rfa.DOKDAT_DATNUM
		order by rfa.TRAEGER,rfa.DOKDAT_DATNUM asc
pinfo	Recreating Clearingfall-summary, Expected time for total summary re-creation from last run: @@hint@@
result_varname	cfsres
post_qexpression
		this.ppush([this.phead("CFSUMMARYRECREATED"),"TIME="+aux.nowsvtime(),"re-created Clearingfall-Summary table svclmvbclfsvttag",this.cfsres]);

#
# <<TSTEPNUM:5>>: drop exOffo-summary table
#
sql				drop table if exists svclexoffosvttag
result_varname	deores
post_qexpression
		this.ppush([this.phead("EXOFFOSUMMARYDROPPED"),"TIME="+aux.nowsvtime(),"dropped exOffo-Summary table svclexoffosvttag",this.deores]);

#
# <<TSTEPNUM:6>>: re-create exOffo-summary table
#
sql	create table svclexoffosvttag as (
	select distinct rfa.traeger as TRAEGER, rfa.DOKUMENT_DAT, rfa.DOKDAT_DATNUM as MELDUNGS_DATUM, count(distinct rfa.DOKUMENT_ID) as ANZAHL_EXOFFO_MBGM
	  from CCMVBRFAUSW rfa
	where rfa.RUECKFRAGETYPCODE in ('BW1916')
	 group by rfa.TRAEGER,rfa.DOKUMENT_DAT, rfa.DOKDAT_DATNUM
	UNION
	select distinct rfat.TRAEGER, rfat.DOKUMENT_DAT, rfat.DOKDAT_DATNUM as MELDUNGS_DATUM, 0 as ANZAHL_EXOFFO_MBGM
	  from CCMVBRFAUSW rfat
      group by rfat.TRAEGER,rfat.DOKUMENT_DAT, rfat.DOKDAT_DATNUM
	  order by TRAEGER,MELDUNGS_DATUM asc
	)
pinfo	Recreating exOffo-summary, Expected time for total summary re-creation from last run: @@hint@@
result_varname	ceores
post_qexpression
		this.ppush([this.phead("EXOFFOSUMMARYRECREATED"),"TIME="+aux.nowsvtime(),"re-created exOffo-Summary table svclexoffosvttag",this.ceores]);

#
# <<TSTEPNUM:7>>: protocol end of summary creation
#
qexpression
	this.rcendts = 0+aux.nowstring();
	this.ppush([this.phead("sumrecreated"),"TIME="+aux.nowsvtime(),"Summary re-creation complete",""]);

#
# <<TSTEPNUM:8>>: gather data about input for creating performance log
#
sql				select count(*) from CCMVBRFAUSW
result_varname	pcres
post_qexpression
	this.rfarecs = this.pcres.getCellValue(0,0);

#
# <<TSTEPNUM:9>>: gather data about output for creating performance log
#
sql				select count(*) from SVCLMVBRFASVTTAG
result_varname	pcres
post_qexpression
	this.nsrecs = this.pcres.getCellValue(0,0)+0;
	this.nsmillis = this.rcendts - this.rcstartts;
	
#
# <<TSTEPNUM:10>>: create performance log for re-creating summary tables
#
pre_qexpression
	this.cdata.ltime = aux.noweutime();
	this.cdata.dataname = this.query.dataname;
	this.cdata.ldesc = JSON.stringify({ACTION: this.cdata.dataname, RAW_RFAUSW_RECORDS: this.rfarecs, SUMMARY_RECORDS_CREATED: this.nsrecs});
	this.cdata.nsmillis = this.nsmillis;
sql				insert into CCPLOG(LTIME,LCLASS,LCODE,LDESC,LUNIT,LVALUE) values(::ltime::,'CPERF',::dataname::,::ldesc::,'ms',::nsmillis::)
named_params	ltime,dataname,ldesc,nsmillis