#
#			ITSV GmbH
#	CCDB - Command and Control Database
#
#	FILE:			dquerymfile_kfocopyklepartner.txt
#	DESCRIPTION:	DQUERY definition for kfocopyklepartner
#
@querytitle			KFOPP-002: Kopiere KFO-LE-Partner aus KFOQSDB
@querydescription	Liest Partner aus einer KFOQSDB und speichert sie in Tabelle CCKFODBPARTNER
@group				KFOQSDBPARPREFILL
@attributenames		mfileid:mfileid:{{mfileidoptions}},cfgname:kfodbconfigname
@mfileidoptions {
	labeltext:		"Ladedatei",
	typedesc:		"Name einer managed Datei, aus der die Partnerdaten geladen werden sollen, wenn nicht direkt aus der Datenbank gelesen wird",
	is_optional:	true }
@cfgnameoptions {
	labeltext:		"Ladedatenbank",
	typedesc:		"KFOQSDB-Datenbank-Instanz, aus der die Partnerdaten geladen werden sollen, wenn nicht aus der Ladedatei",
	is_optional:	true }
@querytype			function
@function			seqtrans.seqtrans
@sql_select			select p."PartnerBundesland",p."PartnerName",p."VpNr",p."countryCode", count(qse."ID") as "AnzahlFaelle",
					( select array_agg(distinct "QSTraeger") from QSV_VPQSTCNT where FALLZAHL=(select MAX(FALLZAHL) from QSV_VPQSTCNT where "VpNr"=p."VpNr") and "VpNr"=p."VpNr")[1] as "MAXFALLQST"
					  from "Partner" p
					    left outer join "QsEintrag" qse on qse."VpNr"=p."VpNr"
					  group by p."VpNr"
@DFLIST				BUNDESLANDKURZ,PARTNERNAME,VPNR,STAAT_ISOA2,ANZAHL_FAELLE,MAXFALLQST,FAELLE_PRO_QST,FEHLER_NICHT_ZPV,FEHLER_STAAT,FEHLER_BUNDESLAND,FEHLER_NAME,PROP_NICHT_ZPV,PROP_STAAT,PROP_BUNDESLAND,PROP_NAME,REP_NICHT_ZPV,REP_STAAT,REP_BUNDESLAND,REP_NAME
@PLIST				PartnerBundesland,PartnerName,VpNr,countryCode
@PTABNAME			CCKFODBPARTNER
@init.aexpression
	if (this.query.mfileid) {
		if (this.query.cfgname) {
			delete this.query.cfgname;
			delete this.query.sql_select;
		}
		this();		/* push on and let the built in seqtrans load the data from the managed file gotten from the <mfileid> query attribute */
	} else {
		if (!this.query.cfgname) {
			this.errcoll.collect(null,"Neither cfgname nor mfileid given");
			this();
		} else {
			db.getDbData({	sql: 	 "create or replace view QSV_VPQSTCNT as"
											+" select '' as \"SATZID\",qse.\"VpNr\",qse.\"QSTraeger\",count(qse.*) as FALLZAHL"
											+"   from \"QsEintrag\" qse"
											+"  group by qse.\"VpNr\",qse.\"QSTraeger\""
											+"  order by qse.\"VpNr\" asc, count(qse.*) desc",
									callback:	this,	/* after completion of creating the view the built in seqtrans mechanism loads */ 
														/* the data using the <sql_select> query attribute */
									config_name:	this.query.cfgname });
		}
	}

~query.tsteps

#
# 0: check init
#
if		(this.init_done)
goto	record_proper

#
# <<TSTEPNUM:1>>: do init
#
qexpression
	this.debug = true;
	this.DFLIST = this.query.DFLIST;
	this.PTABNAME = this.query.PTABNAME;
	this.data_cfgname = this.query.cfgname;
	logger.debug("DFLIST: "+this.DFLIST);
	this.DFields = this.DFLIST.split(",");
	this.PDFLIST = "::" + this.DFields.join("::,::") + "::";
	this.recstoprocess = this.inputresult.getRowCount();
	this.recsprocessed = 0;
	this.recscreated = 0;
	this.duplicatevpnrs = 0;
	this.ppush([this.phead("STARTED"),"RECSTOPROCESS="+this.recstoprocess,"",""]);

#
# <<TSTEPNUM:2>>: truncate target table before loading
#
sql		drop table if exists CCKFODBPARTNER
result_varname	tdres
post_qexpression
	this.ppush([this.phead("TABLEDROPPED"),"table CCKFODBPARTNER dropped","",this.tdres]);

#
# <<TSTEPNUM:3>>: create table afresh
#
sql		create table @@PTABNAME@@ (
			SATZID				varchar(32),
			VPNR				varchar(16), 
			PARTNERNAME			varchar(100), 
			BUNDESLANDKURZ		varchar(4), 
			STAAT_ISOA2			varchar(2), 
			ANZAHL_FAELLE		decimal(19), 
			MAXFALLQST			varchar(2),
			FAELLE_PRO_QST		varchar(2048),
			FEHLER_NICHT_ZPV	varchar(32),
			FEHLER_STAAT		varchar(32),
			FEHLER_BUNDESLAND	varchar(32),
			FEHLER_NAME			varchar(32),
			PROP_NICHT_ZPV		varchar(32), 
			PROP_STAAT			varchar(32), 
			PROP_BUNDESLAND		varchar(32), 
			PROP_NAME			varchar(32),
			REP_NICHT_ZPV		varchar(32), 
			REP_STAAT			varchar(32), 
			REP_BUNDESLAND		varchar(32), 
			REP_NAME			varchar(32))

#
# <<TSTEPNUM:3>>: init complete
#
qexpression
	this.init_done = true;

#
# <<TSTEPNUM:4>>: check if we have this VPNR already
#
record_proper:
sql				select @@DFLIST@@ from @@PTABNAME@@ where VPNR=::VpNr::
named_params	VpNr
result_varname	chkres

#
# <<TSTEPNUM:5>>: skip this record if we have it already
#
if 		(this.chkres.getRowCount()>0)
goto	duplicate_vpnr

#
# <<TSTEPNUM:6>>: no duplicate, assign unique SATZID and get case counts broken down by QS-TRAEGER for the LE-Partner
#
pre_qexpression
	this.cdata.SATZID = aux.syncUniqueNowID();
sql		select "QSTraeger" as "QST", count("QSTraeger") as "TCC" 
		  from "QsEintrag" qse
		 where "VpNr"=::VpNr::
		 group by "QSTraeger"
named_params	VpNr
cfgname	@@data_cfgname@@
result_varname	tccres
post_qexpression
	if (this.tccres.getRowCount()>0) {
		let tcc = {};
		let crow, qst, ccnt;
		for (let ri=0; ri<this.tccres.getRowCount(); ri++) {
			crow = this.tccres.getRowObject(ri);
			tcc[crow.QST] = crow.TCC;
		}
		this.cdata.FAELLE_PRO_QST = JSON.stringify(tcc);
	} else {
		this.cdata.FAELLE_PRO_QST = "{}";
	}

#
# <<TSTEPNUM:7>>: insert into table
#
pre_qexpression
	for (let di=0; di<this.DFields.length; di++) {
		if (!this.cdata.hasOwnProperty(this.DFields[di])) {
			this.cdata[this.DFields[di]] = "";
		}
	}
	this.cdata.VPNR = this.cdata.VpNr;
	this.cdata.PARTNERNAME = this.cdata.PartnerName;
	this.cdata.BUNDESLANDKURZ = this.cdata.PartnerBundesland;
	if (this.cdata.BUNDESLANDKURZ==null) {
		this.cdata.BUNDESLANDKURZ="UNBK";
	}
	this.cdata.STAAT_ISOA2 = this.cdata.countryCode;
	this.cdata.ANZAHL_FAELLE = this.cdata.AnzahlFaelle;
	if (!this.cdata.MAXFALLQST) this.cdata.MAXFALLQST = '99';
sql		insert into @@PTABNAME@@(@@DFLIST@@) values(@@PDFLIST@@)
named_params	@@DFLIST@@
post_qexpression
	this.recscreated++;

#
# <<TSTEPNUM:8>>: insert complete
#
goto record_complete

#
# <<TSTEPNUM:9>>: log duplicate VPNR in protocol
#
duplicate_vpnr:
qexpression
	this.ppush([this.phead("DUPLICATE_VPNR"),
				"VVPNR="+this.cdata.VpNr,
				"PRESENT_DATA="+aux.objTxt(this.chkres.getRowObject(0)),
				"NEW_NAME="+this.cdata.PartnerName]);
	this.duplicatevpnrs++;
	
#
# <<TSTEPNUM:10>>: record complete
#
record_complete:
qexpression
	this.recsprocessed++;

#
# <<TSTEPNUM:11>>: check if last
#
if 		(!this.is_last_record)
goto	record_end

#
# <<TSTEPNUM:12>>: last record, finish protocol
#
qexpression
	ppush(this,[this.ppfx+"_FINISHED","RECSPROCESSED="+this.recsprocessed,"DUPLICATEVPNRS="+this.duplicatevpnrs,"RECSCREATED="+this.recscreated]);

#
# <<TSTEPNUM:13>>: record end
#
record_end:
end