#
#	ITSV GmbH
#	CCDB - Command and Control Database
#
#	FILE:			dquerymfile_KFOPP003.txt
#	DESCRIPTION:	DQUERY-Definition for CCDB-Query KFOPP003 (fka repKFOppPreAssess)
#					this query reports pre-Assessment Information before merging ZPV-Exported LE-Masterdata into KFOQSDB
#
@querytitle			KFOPP-003: Vor-Auswertung ZPV-Behandlerauszug vs. KFOQSDB-Partner-Stammdaten 
@querydescription	Berichtet über Unterschiede in KFO-Behandler-Stammdaten zwischen ZPV-Abzug und KFOQSDB-Snapshot
@group				KFOQSDBPARPREFILL
@querytype			tseq
@sql_select			select NOW() as REPORTTIME
@compattlist		SATZID,MAXFALLQST, KFO_VPNR, ZPV_VPNRS, ANZAHL_FAELLE, FEHLER_NICHT_ZPV, PROP_NICHT_ZPV, REP_NICHT_ZPV,
					KFO_PARTNERNAME, ZPV_PARTNERNAME, FEHLER_NAME, PROP_NAME, REP_NAME,
					KFO_STAAT, ZPV_STAATEN, FEHLER_STAAT, PROP_STAAT, REP_STAAT,
					KFO_BUNDESLAND, ZPV_BUNDESLAENDER, FEHLER_BUNDESLAND, PROP_BUNDESLAND, REP_BUNDESLAND,
					ZPV_ADRESSEN,
					ZPV_TITELVORNE, ZPV_VORNAME, ZPV_NACHNAME, ZPV_TITELHINTEN, ZPV_ORGANISATIONSNAME, FAELLE_PRO_QST

~query.tsteps

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

#
# <<TSTEPNUM:1>>: initialize: setup reporting text flow
#
qexpression
	proc: {
		this.options.maxrecsteps = 1000000;
		this.compattlist = this.query.compattlist;
		this.reporttime = this.cdata.REPORTTIME;
		this.timestamp = aux.serialDateTime(this.cdata.REPORTTIME);
		this.compattribs = this.compattlist.split(",");
		for (let i=0; i<this.compattribs.length; i++) {
			this.compattribs[i] = this.compattribs[i].trim();
		}
	}

#
# <<TSTEPNUM:2>>: change maximum GROUP_CONCAT result length for ZPV_ADDRESSEN
#
sql			set group_concat_max_len = 16384
result_varname	sgres
post_qexpression
	this.ppush([this.phead("PARZPVPAS_GROUCONCATMAXSET"),"","",this.sgres]);
	 
#
# <<TSTEPNUM:2>>: create view CCVKFOPARVSZPVSTO for KFO vs. ZPV without restriction to Bundesland
#
# NOTE: GROUP_CONCAT() does not include items which are NULL. Therefore ZPV_ADRESSEN includes an IFNULL() for gem.GEMEINDENAME, otherwise, Wien-Adressen would not show up
#
sql	create or replace view CCVKFOPARVSZPVSTO as
	select	SATZID,
			MAXFALLQST,
			dbp.ANZAHL_FAELLE as ANZAHL_FAELLE,
			dbp.VPNR as KFO_VPNR, 
			IFNULL(GROUP_CONCAT(distinct lep.VPNR),'') as ZPV_VPNRS,
			(CASE WHEN (CASE WHEN lep.NACHNAME='' then lep.ORGANISATIONSNAME
				ELSE CONCAT((CASE WHEN lep.TITELVORNE!='' THEN CONCAT(lep.TITELVORNE,' ') ELSE '' END),
				            lep.VORNAME,' ',lep.NACHNAME,
							(CASE WHEN lep.TITELHINTEN!='' THEN CONCAT(' ',lep.TITELHINTEN) ELSE '' END)) END)!=dbp.PARTNERNAME THEN 'X' ELSE '' END) as FEHLER_NAME,
			(CASE WHEN GROUP_CONCAT(distinct lep.BUNDESLANDKURZ)!=dbp.BUNDESLANDKURZ THEN 'X' ELSE '' END) as FEHLER_BUNDESLAND,
			(CASE WHEN lep.VPNR is null THEN 'X' ELSE '' END) as FEHLER_NICHT_ZPV,
			(CASE WHEN IFNULL(GROUP_CONCAT(distinct lep.STAAT_ISOA2),'')!=dbp.STAAT_ISOA2 THEN 'X' ELSE '' END) as FEHLER_STAAT,
			PROP_NICHT_ZPV, PROP_STAAT, PROP_BUNDESLAND, PROP_NAME,
			REP_NICHT_ZPV, REP_STAAT, REP_BUNDESLAND, REP_NAME,
			dbp.PARTNERNAME as KFO_PARTNERNAME,
			IFNULL(lep.TITELVORNE,'') as ZPV_TITELVORNE,
			IFNULL(lep.VORNAME,'') as ZPV_VORNAME,
			IFNULL(lep.NACHNAME,'') as ZPV_NACHNAME,
			IFNULL(lep.TITELHINTEN,'') as ZPV_TITELHINTEN,
			dbp.BUNDESLANDKURZ as KFO_BUNDESLAND,
			IFNULL(GROUP_CONCAT(distinct lep.BUNDESLANDKURZ),'') as ZPV_BUNDESLAENDER,
			IFNULL(dbp.STAAT_ISOA2,'') as KFO_STAAT,
			IFNULL(GROUP_CONCAT(distinct lep.STAAT_ISOA2),'') as ZPV_STAATEN,
			IFNULL((CASE	WHEN IFNULL(lep.NACHNAME,'')=''	THEN lep.ORGANISATIONSNAME
															ELSE CONCAT(lep.TITELVORNE,' ',lep.VORNAME,' ',lep.NACHNAME,' ',lep.TITELHINTEN) END),'') as ZPV_PARTNERNAME, 
			/* GROUP_CONCAT(CONCAT('ADRN=',lep.ADRESSNUMMER,'/ADR=',lep.STAAT_ISOA2,'-',lep.PLZ,'-(',gem.GEMEINDENAME,')-',lep.STRASSE)) as ZPV_ADRESSEN, */
			group_concat(concat(lep.ADRESSNUMMER,lep.STAAT_ISOA2,'-',lep.PLZ,
			                    '-(',IFNULL(gem.GEMEINDENAME,(CASE WHEN SUBSTR(lep.PLZ,1,1)='1' THEN 'Wien' ELSE '?Gemeinde?' END)),')-',lep.STRASSE)) as ZPV_ADRESSEN,
			IFNULL(GROUP_CONCAT(distinct lep.ORGANISATIONSNAME),'') as ZPV_ORGANISATIONSNAME,
			FAELLE_PRO_QST
	  from CCKFODBPARTNER dbp
	    left outer join CCKFOLEPARTNER lep on lep.VPNR=dbp.VPNR
		  left outer join CCRWGEMEINDE gem on gem.GEMEINDECODE=lep.GEMEINDECODE
	 group by 	dbp.SATZID, dbp.MAXFALLQST, dbp.VPNR, dbp.ANZAHL_FAELLE, dbp.PARTNERNAME, lep.VORNAME, lep.NACHNAME, lep.ORGANISATIONSNAME, lep.TITELVORNE, lep.TITELHINTEN, dbp.BUNDESLANDKURZ, dbp.STAAT_ISOA2, dbp.FAELLE_PRO_QST,
				PROP_NICHT_ZPV, PROP_STAAT, PROP_BUNDESLAND, PROP_NAME, REP_NICHT_ZPV, REP_STAAT, REP_BUNDESLAND, REP_NAME
	 order by dbp.VPNR,lep.VPNR asc

#
# <<TSTEPNUM:3>>: drop old status of CCKFOPPPARZPVPAS
#
sql					drop table if exists CCKFOPPPARZPVPAS
result_varname		cdres
post_qexpression
	this.ppush([this.phead("PARZPVPAS_TABLE_DROPPED"),"","",this.cdres]);
	
#
# <<TSTEPNUM:4>>: make table CCKFOPPPARZPVPAS a materialized copy of CCVKFOPARVSZPVSTO
#
sql 				create table CCKFOPPPARZPVPAS as select * from CCVKFOPARVSZPVSTO
result_varname		ccres
post_qexpression
	this.ppush([this.phead("PARZPVPAS_TABLE_CREATED"),"","",this.cdres]);

#
# <<TSTEPNUM:5>>: last initialization step
#
qexpression		this.init_done = true;

#
# <<TSTEPNUM:6>>: initialization complete
#
init_complete:
noop

#
# <<TSTEPNUM:7>>: deliver base numbers
#
sql		select 'Anzahl Partner in KFOQSDB' as "Name", count(*) as "Wert" from CCKFODBPARTNER 
		UNION
		select 'Anzahl Standort-Partner in ZPV' as "Name", count(*) as "Wert" from CCKFOLEPARTNER
		UNION
		select 'Anzahl Partner in ZPV, die (noch) nicht in KFOQSDB vorhanden sind' as "Name", 
				count(distinct lep.LENR) as "Wert"
		  from CCVKFOLEPARTNER lep
			left outer join CCKFODBPARTNER dbp on dbp.VPNR=lep.VPNR
		 where dbp.VPNR is null
result_varname	ovres
post_qexpression
	this.ovres.title = "Überblicks-Werte";
	this.ovres.rows.collapsible = true;
	this.ovres.rows.collapsed = false;
	this.ppush({type: 'result', result: this.ovres });
	
#
# <<TSTEPNUM:8>>: check present partners in CCKFODBPARTNER(KFOQSDB) against matching (by VPNR) Standort-Partner from CCKFOLEPARTNER(ZPV)
#
sql		select @@compattlist@@ from CCVKFOPARVSZPVSTO
result_varname	mpares
post_qexpression
	this.mpares.totalpartners = this.mpares.getRowCount();
	let crow, nrow, nparts, mnpn, tv_matches, vn_matches, nn_matches, th_matches;
	let filtres = new aux.Result('dbresult');
	filtres.addColumnNames(this.query.compattlist);
	filtres.setResultAttribute("tableclass","excelFilterTable");
	let lastvpnr = "DiesIstSicherKeineVPNR";
	let lastrow = -1;
	for (let ri=0; ri<this.mpares.getRowCount(); ri++) {
		crow = this.mpares.getRowObject(ri);
		nrow = {	SATZID:				crow.SATZID,
					MAXFALLQST:			crow.MAXFALLQST,
					KFO_VPNR: 			crow.KFO_VPNR,
					ZPV_VPNRS:			crow.ZPV_VPNRS,
					ANZAHL_FAELLE:		crow.ANZAHL_FAELLE,
					FEHLER_NICHT_ZPV:	crow.FEHLER_NICHT_ZPV,
					FEHLER_STAAT:		crow.FEHLER_STAAT,
					FEHLER_BUNDESLAND:  crow.FEHLER_BUNDESLAND,
					FEHLER_NAME:		crow.FEHLER_NAME,
					PROP_NICHT_ZPV:		"",
					PROP_STAAT:			"",
					PROP_BUNDESLAND:	"",
					PROP_NAME:			"",
					REP_NICHT_ZPV:		"",
					REP_STAAT:			"",
					REP_BUNDESLAND:		"",
					REP_NAME:			"",
					KFO_PARTNERNAME:	crow.KFO_PARTNERNAME,
					ZPV_PARTNERNAME:	crow.ZPV_PARTNERNAME,
					KFO_BUNDESLAND:		crow.KFO_BUNDESLAND,
					ZPV_BUNDESLAENDER:	crow.ZPV_BUNDESLAENDER,
					ZPV_ADRESSEN:		crow.ZPV_ADRESSEN?(crow.ZPV_ADRESSEN.split(",").join("|")):"",
					KFO_STAAT:			crow.KFO_STAAT,
					ZPV_STAATEN:		crow.ZPV_STAATEN,
					FAELLE_PRO_QST:		crow.FAELLE_PRO_QST
				};
		if (crow.FEHLER_NICHT_ZPV=='') {
			/* the Partner in KFOQSDB is also in ZPV with the same VPNR */
			nrow.PROP_NICHT_ZPV = "";
			nrow.FEHLER_NAME = crow.FEHLER_NAME;
			if (crow.FEHLER_NAME=='X') {
				/* provide more detail for name mismatches */
				nparts = crow.KFO_PARTNERNAME.split(" ");
				mnpn = 0;	/* count matching name parts */
				tv_matches = false;
				vn_matches = false;
				nn_matches = false;
				th_matches = false;
				for (let npi=0; npi<nparts.length; npi++) {
					if (nparts[npi].endsWith(',')) {
						nparts[npi] = nparts[npi].substring(0,nparts[npi].length-1);
					}
					if (nparts[npi]==crow.ZPV_TITELVORNE) {
						tv_matches = true;
						mnpn++;
					} else if (nparts[npi]==crow.ZPV_VORNAME) {
						vn_matches = true;
						mnpn++;
					} else if (nparts[npi]==crow.ZPV_NACHNAME) {
						nn_matches = true;
						mnpn++;
					} else if (nparts[npi]==crow.ZPV_TITELHINTEN) {
						th_matches = true;
						mnpn++;
					}
				}
				if (mnpn==nparts.length) {	
					/* all parts of KFOQSDB name match against ZPV name parts, assume name to be sufficiently similar */
					nrow.FEHLER_NAME = 'S';
					nrow.PROP_NAME = 'Z';
				} else if (vn_matches && nn_matches && (tv_matches || th_matches) && nparts.length>mnpn) {	
					/* at least 3 parts of KFOQSDB name parts match, KFOQSDB name has more parts, maybe similar enough, let user decide */
					nrow.FEHLER_NAME = 'Q';
					nrow.PROP_NAME = 'Z'
				} else {
					nrow.FEHLER_NAME = 'X';
					nrow.PROP_NAME = 'V';
				}
			}
			nrow.ZPV_TITELVORNE = crow.ZPV_TITELVORNE;
			nrow.ZPV_VORNAME = crow.ZPV_VORNAME;
			nrow.ZPV_NACHNAME = crow.ZPV_NACHNAME;
			nrow.ZPV_TITELHINTEN = crow.ZPV_TITELHINTEN;
			nrow.ZPV_ORGANISATIONSNAME = crow.ZPV_ORGANISATIONSNAME;
			nrow.ZPV_PARTNERNAME = crow.ZPV_PARTNERNAME;
			nrow.ZPV_ADRESSNUMMERN = crow.ZPV_ADRESSNUMMERN;
			nrow.ZPV_BUNDESLAENDER = crow.ZPV_BUNDESLAENDER;
			nrow.ZPV_PLZS = crow.ZPV_PLZS;
			nrow.ZPV_GEMEINDECODES = crow.ZPV_GEMEINDECODES;
			nrow.ZPV_STRASSEN = crow.ZPV_STRASSEN;
			nrow.FEHLER_BUNDESLAND = crow.FEHLER_BUNDESLAND;
			if (nrow.FEHLER_STAAT!='') {
				nrow.PROP_STAAT = "V";
			}
			if (nrow.FEHLER_BUNDESLAND!='') {
				nrow.PROP_BUNDESLAND = "V";
			}
		} else {
			/* the Partner in KFOQSDB has no corresponding entry delivered from ZPV */
			nrow.ZPV_VPNRS = "";
			nrow.FEHLER_NICHT_ZPV = crow.FEHLER_NICHT_ZPV;
			nrow.ZPV_TITELVORNE = "";
			nrow.ZPV_VORNAME = "";
			nrow.ZPV_NACHNAME = "";
			nrow.ZPV_TITELHINTEN = "";
			nrow.ZPV_ORGANISATIONSNAME = "";
			nrow.ZPV_PARTNERNAME = "";
			nrow.ZPV_ADRESSNUMMERN = "";
			nrow.ZPV_BUNDESLAENDER = "";
			nrow.ZPV_GEMEINDECODES = "";
			nrow.ZPV_PLZS = "";
			nrow.ZPV_STRASSEN = "";
			nrow.FEHLER_BUNDESLAND = "";
			nrow.FEHLER_NAME = "";
			if (crow.ANZAHL_FAELLE>0) {
				nrow.PROP_NICHT_ZPV = "V";
			} else {
				nrow.PROP_NICHT_ZPV = "L";
			}
		}
		filtres.addRowObject(nrow);
	}
	filtres.setResultAttribute("title","Partner in KFOQSDB im Vergleich mit ZPV");
	filtres.setResultAttribute("toptext",	
		 "<ul><li>Auswertung von "+aux.svDateTime(this.reporttime)+"</li>\r\n"
		+"<li>Diese Aufstellung beinhaltet ausschließlich LE-Partner, die in der KFOQSDB bekannt sind<br/>\r\n"
		+"    (LE-Partner, die nicht in der KFOQSDB, aber in ZPV vorhanden sind, sind in der Aufstellung <a href=\"#REPZPVNOTKFO\">\"KFO-Leistungserbringer aus ZPV, die in KFOQSDB unbekannt sind\"</a> enthalten)</li>\r\n"
		+"<li>Anzahl gesamte Partner-Einträge in der KFOQSDB: "+this.mpares.totalpartners+"</li>\r\n"
		+"<li>Anzahl geprüfte Einträge: "+filtres.getRowCount()+"</li>\r\n"
		+"</ul>\r\n"
		);
	filtres.rows.collapsible = true;
	filtres.rows.collapsed = true;
	this.filtres = new aux.Result(filtres);
	ppush(this,{type: 'result', result: filtres });

#
# <<TSTEPNUM:9>>: write back all FEHLER_xxx fields into the basic CCKFODBPARTNER tableclass, set up loop
#
qexpression
	this.wbindex = 0;
	
#
# <<TSTEPNUM:10>>: write one row back
#
wbloop:
if		(this.wbindex>=this.filtres.getRowCount())
goto	wbloop_ended

#
# <<TSTEPNUM:11>>: write one row back (the fields that have been modified by above steps)
#
pre_qexpression
	this.cdata = this.filtres.getRowObject(this.wbindex);
	for (let i=0; i<this.compattribs.length; i++) {
		if ((!this.cdata.hasOwnProperty(this.compattribs[i])) || (this.cdata[this.compattribs[i]]==null)) {
			this.cdata[this.compattribs[i]] = "";
		}
	}
sql		UPDATE CCKFOPPPARZPVPAS set	FEHLER_NICHT_ZPV=::FEHLER_NICHT_ZPV::,	FEHLER_STAAT=::FEHLER_STAAT::,	FEHLER_BUNDESLAND=::FEHLER_BUNDESLAND::,	FEHLER_NAME=::FEHLER_NAME::, 
									PROP_NICHT_ZPV=::PROP_NICHT_ZPV::, 		PROP_STAAT=::PROP_STAAT::,		PROP_BUNDESLAND=::PROP_BUNDESLAND::,		PROP_NAME=::PROP_NAME::,
									REP_NICHT_ZPV=::REP_NICHT_ZPV::,		REP_STAAT=::REP_STAAT::,		REP_BUNDESLAND=::REP_BUNDESLAND::,			REP_NAME=::REP_NAME::,
									ZPV_ADRESSEN=::ZPV_ADRESSEN::
		 where KFO_VPNR=::KFO_VPNR::
named_params	KFO_VPNR,FEHLER_NICHT_ZPV,FEHLER_NAME,FEHLER_BUNDESLAND,FEHLER_STAAT,PROP_NICHT_ZPV,PROP_STAAT,PROP_BUNDESLAND,PROP_NAME,REP_NICHT_ZPV,REP_STAAT,REP_BUNDESLAND,REP_NAME,ZPV_ADRESSEN

#
# <<TSTEPNUM:12>>: repeat writeback loop
#
pre_qexpression
	this.wbindex++;
goto	wbloop

#
# <<TSTEPNUM:13>>: end of writeback loop
#
wbloop_ended:
noop

#
# <<TSTEPNUM:14>>: check partners in KFOQSDB without matches in ZPV
#
sql				select dbp.VPNR as KFO_VPNR,dbp.PARTNERNAME as KFO_PARTNERNAME, dbp.BUNDESLANDKURZ as KFO_BUNDESLANDKURZ
				 from CCKFODBPARTNER dbp
				    left outer join CCKFOLEPARTNER lep on lep.VPNR=dbp.VPNR and lep.BUNDESLANDKURZ=dbp.BUNDESLANDKURZ
				where lep.VPNR is null
result_varname	mpnres
post_qexpression
	this.mpnres.title = "KFOQSDB-Partner OHNE passende ZPV-Partner";
	this.mpnres.toptext =	 "<ul>"
							+"<li>Anzahl Partner ohne Entsprechung in ZPV: "+this.mpnres.getRowCount()+"</li>"
							+"</ul>";
	this.mpnres.rows.collapsible = true;
	this.mpnres.rows.collapsed = true;
	ppush(this,{type: 'result', result: this.mpnres });
	

#
# <<TSTEPNUM:15>>: create convenience view on CCKFOLEPARTNER
#
sql	create or replace view CCVKFOLEPARTNER as
	select	lep.LENR,lep.VPNR,
			(CASE WHEN lep.ORGANISATIONSNAME is not null and lep.ORGANISATIONSNAME!='' THEN lep.ORGANISATIONSNAME
			 ELSE CONCAT(IFNULL(lep.TITELVORNE,''),' ',lep.VORNAME,' ',lep.NACHNAME,' ',lep.TITELHINTEN) END) as PARTNERNAME,
			lep.BUNDESLANDKURZ,lep.STAAT_ISOA2,lep.ADRESSNUMMER
	  from CCKFOLEPARTNER lep
	
#
# <<TSTEPNUM:16>>: show ZPV-Partners not in KFOQSDB
#
sql		select lep.LENR, GROUP_CONCAT(distinct lep.VPNR) as ZPV_VPNRS, GROUP_CONCAT(distinct lep.PARTNERNAME) as ZPV_PARTNERNAMEN,
				GROUP_CONCAT(distinct lep.STAAT_ISOA2) as ZPV_STAATEN,
				GROUP_CONCAT(distinct lep.BUNDESLANDKURZ) as ZPV_BUNDESLAENDER,
				GROUP_CONCAT(distinct lep.ADRESSNUMMER) as ZPV_ADRESSNUMMERN
		  from CCVKFOLEPARTNER lep
		    left outer join CCKFODBPARTNER dbp on dbp.VPNR=lep.VPNR
		 where dbp.VPNR is null
		 group by lep.LENR
result_varname	lenres
post_qexpression
	this.lenres.title = "KFO-Leistungserbringer aus ZPV, die in KFOQSDB unbekannt sind";
	let crow;
	let vpnrcn = this.lenres.findColumn("ZPV_VPNRS");
	for (let ri=0; ri<this.lenres.getRowCount(); ri++) {
		if (this.lenres.rows[ri][vpnrcn].length>40) this.lenres.rows[ri][vpnrcn] = this.lenres.rows[ri][vpnrcn].substring(0,40)+"...";
	}	
	this.lenres.toptext = "<ul>"
						 +"  <li>Es gibt "+this.lenres.getRowCount()+" Leistungserbringer mit KFO-Fachgebieten, die in ZPV, aber nicht der KFOQSDB enthalten sind</li>\r\n"
						 +"  <li>In der folgenden Aufstellung sind die Partner nach LE-Nummer gruppiert. Zu einer LENR können mehrere VPNR und Adressen/Standorte gehören</li>\r\n"
						 +"</ul>";
	this.lenres.rows.collapsible = true;
	this.lenres.rows.collapsed = true;
	ppush(this,{type: 'result', result: this.lenres });
	
#
# <<TSTEPNUM:17>>: title the protocol, seqtrans will produce a result from it
#
qexpression
	this.protocol.title = 'Partner-Abgleich KFOQSDB mit ZPV';