﻿OPEN DATABASE TARMED;

--! Transform XML- in TXT-Dateien

TRANSFORM XML GetFile(c:\temp\IMPORT\KVG\XML\KAPITEL.xml) DELIMITER @@ HEADER;

TRANSFORM XML GetFile(c:\temp\IMPORT\KVG\XML\KAPITELTXT.xml) DELIMITER @@ HEADER;
 
TRANSFORM XML GetFile(c:\temp\IMPORT\KVG\XML\LEISTBAUM1.xml) DELIMITER @@ HEADER;
 
TRANSFORM XML GetFile(c:\temp\IMPORT\KVG\XML\LEISTBAUM2.xml) DELIMITER @@ HEADER;
 
TRANSFORM XML GetFile(c:\temp\IMPORT\KVG\XML\LEISTUNG.xml) DELIMITER @@ HEADER;
 
TRANSFORM XML GetFile(c:\temp\IMPORT\KVG\XML\LEISTUNGTXT.xml) DELIMITER @@ HEADER;

TRANSFORM XML GetFile(c:\temp\IMPORT\KVG\XML\ALTER.xml) DELIMITER @@ HEADER; 

TRANSFORM XML GetFile(c:\temp\IMPORT\KVG\XML\MENGE.xml) DELIMITER @@ HEADER; 

TRANSFORM XML GetFile(c:\temp\IMPORT\KVG\XML\ZRE.xml) DELIMITER @@ HEADER; 

TRANSFORM XML GetFile(c:\temp\IMPORT\KVG\XML\ZRETXT.xml) DELIMITER @@ HEADER; 

TRANSFORM XML GetFile(c:\temp\IMPORT\KVG\XML\LEISTUNGQTD.xml) DELIMITER @@ HEADER; 

--! TXT-Dateien um Spalte BEREICH erweitern

SETCOL GetFile(c:\temp\IMPORT\KVG\XML\KAPITEL.txt) DELIMITER @@ COLUMN BEREICH SET 2;

SETCOL GetFile(c:\temp\IMPORT\KVG\XML\KAPITELTXT.txt) DELIMITER @@ COLUMN BEREICH SET 2;
 
SETCOL GetFile(c:\temp\IMPORT\KVG\XML\LEISTBAUM1.txt) DELIMITER @@ COLUMN BEREICH SET 2;
 
SETCOL GetFile(c:\temp\IMPORT\KVG\XML\LEISTBAUM2.txt) DELIMITER @@ COLUMN BEREICH SET 2;
 
SETCOL GetFile(c:\temp\IMPORT\KVG\XML\LEISTUNG.txt) DELIMITER @@ COLUMN BEREICH SET 2;
 
SETCOL GetFile(c:\temp\IMPORT\KVG\XML\LEISTUNGTXT.txt) DELIMITER @@ COLUMN BEREICH SET 2;

SETCOL GetFile(c:\temp\IMPORT\KVG\XML\ALTER.txt) DELIMITER @@ COLUMN BEREICH SET 2; 

SETCOL GetFile(c:\temp\IMPORT\KVG\XML\MENGE.txt) DELIMITER @@ COLUMN BEREICH SET 2; 

SETCOL GetFile(c:\temp\IMPORT\KVG\XML\ZRE.txt) DELIMITER @@ COLUMN BEREICH SET 2; 

SETCOL GetFile(c:\temp\IMPORT\KVG\XML\ZRETXT.txt) DELIMITER @@ COLUMN BEREICH SET 2; 

SETCOL GetFile(c:\temp\IMPORT\KVG\XML\LEISTUNGQTD.txt) DELIMITER @@ COLUMN BEREICH SET 2; 

--! Tabellen leeren

DELETE FROM T_KAPITEL WHERE BEREICH=2;
DELETE FROM T_KAPITELGRP WHERE BEREICH=2;
DELETE FROM T_KAPITELTXT WHERE BEREICH=2;
DELETE FROM T_LEISTBAUM WHERE BEREICH=2;
DELETE FROM T_LEISTUNG WHERE BEREICH=2;
DELETE FROM T_LEISTUNGTXT WHERE BEREICH=2;
DELETE FROM T_ALTER WHERE BEREICH=2;
DELETE FROM T_MENGE WHERE BEREICH=2;
DELETE FROM T_ZRE WHERE BEREICH=2;
DELETE FROM T_ZRETXT WHERE BEREICH=2;
DELETE FROM T_LEISTUNGQTD WHERE BEREICH=2;

--! Tabellen laden

LOAD DATA FROM GetFile(c:\temp\IMPORT\KVG\XML\KAPITEL.txt) INTO T_KAPITEL(KNR, GVON, GBIS, BEREICH) DELIMITER @@ HEADER;

LOAD DATA FROM GetFile(c:\temp\IMPORT\KVG\XML\KAPITELTXT.txt) INTO T_KAPITELTXT(KNR, SPR, BEZ, GVON, GBIS, BEREICH) DELIMITER @@ HEADER;

LOAD DATA FROM GetFile(c:\temp\IMPORT\KVG\XML\LEISTUNG.txt) INTO T_LEISTUNG(LNR, KNR, SEX, LMIN, TPARL, TPASL, TPTEL, GVON, GBIS, BEREICH) DELIMITER @@ HEADER;

LOAD DATA FROM GetFile(c:\temp\IMPORT\KVG\XML\LEISTUNGTXT.txt) INTO T_LEISTUNGTXT(LNR, SPR, BEZ, GVON, GBIS, BEREICH) DELIMITER @@ HEADER;

LOAD DATA FROM GetFile(c:\temp\IMPORT\KVG\XML\LEISTBAUM1.txt) INTO T_LEISTBAUM (MLNR, SLNR, GVON, GBIS, BEREICH) DELIMITER @@ HEADER;

LOAD DATA FROM GetFile(c:\temp\IMPORT\KVG\XML\LEISTBAUM2.txt) INTO T_LEISTBAUM (MLNR, SLNR, GVON, GBIS, BEREICH) DELIMITER @@ HEADER;

LOAD DATA FROM GetFile(c:\temp\IMPORT\KVG\XML\ALTER.txt) INTO T_ALTER (LNR, AVON, TVON, ABIS, TBIS, ZRE, GVON, GBIS, BEREICH) DELIMITER @@ HEADER;

LOAD DATA FROM GetFile(c:\temp\IMPORT\KVG\XML\MENGE.txt) INTO T_MENGE (LNR, OPER, MENGE, ZRE, GVON, GBIS, BEREICH) DELIMITER @@ HEADER;

LOAD DATA FROM GetFile(c:\temp\IMPORT\KVG\XML\ZRE.txt) INTO T_ZRE(ZRE, BEZG, GVON, GBIS, BEREICH) DELIMITER @@ HEADER;

LOAD DATA FROM GetFile(c:\temp\IMPORT\KVG\XML\ZRETXT.txt) INTO T_ZRETXT (ZRE, SPR, BEZ, GVON, GBIS, BEREICH) DELIMITER @@ HEADER;

LOAD DATA FROM GetFile(c:\temp\IMPORT\KVG\XML\LEISTUNGQTD.txt) INTO T_LEISTUNGQTD(LNR, QTD, GVON, GBIS, BEREICH) DELIMITER @@ HEADER;

--! Texte in T_KAPITEL laden

UPDATE T_KAPITEL SET BEZE=BEZ FROM T_KAPITEL INNER JOIN   
T_KAPITELTXT ON T_KAPITEL.BEREICH = T_KAPITELTXT.BEREICH AND T_KAPITEL.KNR = T_KAPITELTXT.KNR AND T_KAPITEL.GVON <= T_KAPITELTXT.GBIS AND 
T_KAPITEL.GBIS >= T_KAPITELTXT.GVON WHERE  T_KAPITEL.BEREICH=2 AND UPPER(T_KAPITELTXT.SPR)='E';

UPDATE T_KAPITEL SET BEZF=BEZ FROM T_KAPITEL INNER JOIN   
T_KAPITELTXT ON T_KAPITEL.BEREICH = T_KAPITELTXT.BEREICH AND T_KAPITEL.KNR = T_KAPITELTXT.KNR AND T_KAPITEL.GVON <= T_KAPITELTXT.GBIS AND 
T_KAPITEL.GBIS >= T_KAPITELTXT.GVON WHERE T_KAPITEL.BEREICH=2 AND UPPER(T_KAPITELTXT.SPR)='F';

UPDATE T_KAPITEL SET BEZG=BEZ FROM T_KAPITEL INNER JOIN   
T_KAPITELTXT ON T_KAPITEL.BEREICH = T_KAPITELTXT.BEREICH AND T_KAPITEL.KNR = T_KAPITELTXT.KNR AND T_KAPITEL.GVON <= T_KAPITELTXT.GBIS AND 
T_KAPITEL.GBIS >= T_KAPITELTXT.GVON WHERE T_KAPITEL.BEREICH=2 AND UPPER(T_KAPITELTXT.SPR)='D';

UPDATE T_KAPITEL SET BEZI=BEZ FROM T_KAPITEL INNER JOIN   
T_KAPITELTXT ON T_KAPITEL.BEREICH = T_KAPITELTXT.BEREICH AND T_KAPITEL.KNR = T_KAPITELTXT.KNR AND T_KAPITEL.GVON <= T_KAPITELTXT.GBIS AND 
T_KAPITEL.GBIS >= T_KAPITELTXT.GVON WHERE T_KAPITEL.BEREICH=2 AND UPPER(T_KAPITELTXT.SPR)='I';

--! Texte in T_LEISTUNG laden

UPDATE T_LEISTUNG SET BEZE=BEZ FROM T_LEISTUNG INNER JOIN   
T_LEISTUNGTXT ON T_LEISTUNG.BEREICH = T_LEISTUNGTXT.BEREICH AND T_LEISTUNG.LNR = T_LEISTUNGTXT.LNR AND T_LEISTUNG.GVON <= T_LEISTUNGTXT.GBIS AND 
T_LEISTUNG.GBIS >= T_LEISTUNGTXT.GVON WHERE T_LEISTUNG.BEREICH=2 AND UPPER(T_LEISTUNGTXT.SPR)='E';

UPDATE T_LEISTUNG SET BEZF=BEZ FROM T_LEISTUNG INNER JOIN   
T_LEISTUNGTXT ON T_LEISTUNG.BEREICH = T_LEISTUNGTXT.BEREICH AND T_LEISTUNG.LNR = T_LEISTUNGTXT.LNR AND T_LEISTUNG.GVON <= T_LEISTUNGTXT.GBIS AND 
T_LEISTUNG.GBIS >= T_LEISTUNGTXT.GVON WHERE T_LEISTUNG.BEREICH=2 AND UPPER(T_LEISTUNGTXT.SPR)='F';

UPDATE T_LEISTUNG SET BEZG=BEZ FROM T_LEISTUNG INNER JOIN   
T_LEISTUNGTXT ON T_LEISTUNG.BEREICH = T_LEISTUNGTXT.BEREICH AND T_LEISTUNG.LNR = T_LEISTUNGTXT.LNR AND T_LEISTUNG.GVON <= T_LEISTUNGTXT.GBIS AND 
T_LEISTUNG.GBIS >= T_LEISTUNGTXT.GVON WHERE T_LEISTUNG.BEREICH=2 AND UPPER(T_LEISTUNGTXT.SPR)='D';

UPDATE T_LEISTUNG SET BEZI=BEZ FROM T_LEISTUNG INNER JOIN   
T_LEISTUNGTXT ON T_LEISTUNG.BEREICH = T_LEISTUNGTXT.BEREICH AND T_LEISTUNG.LNR = T_LEISTUNGTXT.LNR AND T_LEISTUNG.GVON <= T_LEISTUNGTXT.GBIS AND 
T_LEISTUNG.GBIS >= T_LEISTUNGTXT.GVON WHERE T_LEISTUNG.BEREICH=2 AND UPPER(T_LEISTUNGTXT.SPR)='I';

--! Texte in T_ZRE laden

UPDATE T_ZRE SET BEZE=BEZ FROM T_ZRE INNER JOIN   
T_ZRETXT ON T_ZRE.BEREICH = T_ZRETXT.BEREICH AND T_ZRE.ZRE = T_ZRETXT.ZRE AND T_ZRE.GVON <= T_ZRETXT.GBIS AND 
T_ZRE.GBIS >= T_ZRETXT.GVON WHERE T_ZRE.BEREICH=2 AND UPPER(T_ZRETXT.SPR)='E';

UPDATE T_ZRE SET BEZF=BEZ FROM T_ZRE INNER JOIN   
T_ZRETXT ON T_ZRE.BEREICH = T_ZRETXT.BEREICH AND  T_ZRE.ZRE = T_ZRETXT.ZRE AND T_ZRE.GVON <= T_ZRETXT.GBIS AND 
T_ZRE.GBIS >= T_ZRETXT.GVON WHERE  T_ZRE.BEREICH=2 AND UPPER(T_ZRETXT.SPR)='F';

UPDATE T_ZRE SET BEZI=BEZ FROM T_ZRE INNER JOIN   
T_ZRETXT ON T_ZRE.BEREICH = T_ZRETXT.BEREICH AND  T_ZRE.ZRE = T_ZRETXT.ZRE AND T_ZRE.GVON <= T_ZRETXT.GBIS AND 
T_ZRE.GBIS >= T_ZRETXT.GVON WHERE  T_ZRE.BEREICH=2 AND UPPER(T_ZRETXT.SPR)='I';

--! Tabellen von Historie befreien (Gültig-Bis < Aktuelles-Jahr - 5)

DELETE FROM T_KAPITEL WHERE BEREICH=2 AND YEAR(GBIS)<YEAR(GETDATE())-5;
DELETE FROM T_KAPITELTXT WHERE BEREICH=2 AND YEAR(GBIS)<YEAR(GETDATE())-5;
DELETE FROM T_LEISTBAUM WHERE BEREICH=2 AND YEAR(GBIS)<YEAR(GETDATE())-5;
DELETE FROM T_LEISTUNG WHERE BEREICH=2 AND YEAR(GBIS)<YEAR(GETDATE())-5;
DELETE FROM T_LEISTUNGTXT WHERE BEREICH=2 AND YEAR(GBIS)<YEAR(GETDATE())-5;
DELETE FROM T_ALTER WHERE BEREICH=2 AND YEAR(GBIS)<YEAR(GETDATE())-5;
DELETE FROM T_MENGE WHERE BEREICH=2 AND YEAR(GBIS)<YEAR(GETDATE())-5;
DELETE FROM T_ZRE WHERE BEREICH=2 AND YEAR(GBIS)<YEAR(GETDATE())-5;
DELETE FROM T_LEISTUNGQTD WHERE BEREICH=2 AND YEAR(GBIS)<YEAR(GETDATE())-5;
DELETE FROM T_CODIERUNG WHERE BEREICH=2 AND YEAR(Datum)<YEAR(GETDATE())-5;

--! Tabelle T_KAPITELGRP füllen

INSERT T_KAPITELGRP SELECT BEREICH, dbo.uf_GRPNR(KNR) AS KNR, BEZE, BEZF, BEZG, BEZI, GVON, GBIS FROM T_KAPITEL WHERE BEREICH=2 AND NOT EXISTS (SELECT T_LEISTUNG.KNR FROM T_LEISTUNG WHERE T_LEISTUNG.BEREICH = T_KAPITEL.BEREICH AND T_LEISTUNG.KNR = T_KAPITEL.KNR);

DELETE FROM T_KAPITEL WHERE BEREICH=2 AND EXISTS (SELECT BEREICH, KNR FROM T_KAPITELGRP WHERE T_KAPITEL.BEREICH = T_KAPITELGRP.BEREICH AND T_KAPITEL.KNR = T_KAPITELGRP.KNR);

--! TARMED-Sex: 1=Frau 0=Mann werden zu MEDFASOP-Sex: 1=Frau 2=Mann

UPDATE T_LEISTUNG SET SEX=2 WHERE BEREICH=2 AND ISNULL(SEX,3)=0;

--! TARMED-KAPITEL werden für MEDFASOP aktualisiert NULL=keine Angabe, 1=mit, 2=ohne, 3=beides

UPDATE T_KAPITEL SET LMIN=NULL WHERE BEREICH=2; 

UPDATE T_KAPITEL SET LMIN=ISNULL(LMIN,0)+1 WHERE T_KAPITEL.BEREICH=2 AND EXISTS (SELECT T_LEISTUNG.BEREICH, T_LEISTUNG.KNR FROM T_LEISTUNG WHERE T_LEISTUNG.BEREICH = T_KAPITEL.BEREICH AND T_LEISTUNG.KNR = T_KAPITEL.KNR AND ISNULL(T_LEISTUNG.LMIN, 0) > 0 AND T_KAPITEL.GVON<=T_LEISTUNG.GBIS AND T_KAPITEL.GBIS>=T_LEISTUNG.GVON);

UPDATE T_KAPITEL SET LMIN=ISNULL(LMIN,0)+2 WHERE T_KAPITEL.BEREICH=2 AND EXISTS (SELECT T_LEISTUNG.BEREICH, T_LEISTUNG.KNR FROM T_LEISTUNG WHERE T_LEISTUNG.BEREICH = T_KAPITEL.BEREICH AND T_LEISTUNG.KNR = T_KAPITEL.KNR AND ISNULL(T_LEISTUNG.LMIN, 0) = 0 AND T_KAPITEL.GVON<=T_LEISTUNG.GBIS AND T_KAPITEL.GBIS>=T_LEISTUNG.GVON);

UPDATE T_KAPITEL SET SEX=NULL WHERE BEREICH=2; 

UPDATE T_KAPITEL SET SEX=ISNULL(SEX,0)+1 WHERE T_KAPITEL.BEREICH=2 AND EXISTS (SELECT T_LEISTUNG.KNR FROM T_LEISTUNG WHERE T_LEISTUNG.KNR = T_KAPITEL.KNR AND ISNULL(T_LEISTUNG.SEX, 0)=1 AND T_KAPITEL.GVON<=T_LEISTUNG.GBIS AND T_KAPITEL.GBIS>=T_LEISTUNG.GVON);

UPDATE T_KAPITEL SET SEX=ISNULL(SEX,0)+2 WHERE T_KAPITEL.BEREICH=2 AND EXISTS (SELECT T_LEISTUNG.KNR FROM T_LEISTUNG WHERE T_LEISTUNG.KNR = T_KAPITEL.KNR AND ISNULL(T_LEISTUNG.SEX, 0)=2 AND T_KAPITEL.GVON<=T_LEISTUNG.GBIS AND T_KAPITEL.GBIS>=T_LEISTUNG.GVON);

EXEC dbo.up_QTD2LEISTUNG 2;

EXEC dbo.up_QTD2KAPITEL 2;

--! Text-Tabellen leeren

DELETE FROM T_KAPITELTXT WHERE BEREICH=2;
INSERT INTO T_KAPITELTXT(BEREICH,KNR,SPR,BEZ,GVON,GBIS) VALUES(2,'?','D','Tabelle wird nur während Import benötigt',GETDATE(),GETDATE());
DELETE FROM T_LEISTUNGTXT WHERE BEREICH=2;
INSERT INTO T_LEISTUNGTXT(BEREICH,LNR,SPR,BEZ,GVON,GBIS) VALUES(2,'?','D','Tabelle wird nur während Import benötigt',GETDATE(),GETDATE());
DELETE FROM T_ZRETXT WHERE BEREICH=2;
INSERT INTO T_ZRETXT(BEREICH,ZRE,SPR,BEZ,GVON,GBIS) VALUES(2,0,'D','Tabelle wird nur während Import benötigt',GETDATE(),GETDATE());
DELETE FROM T_LEISTUNGQTD WHERE BEREICH=2;
INSERT INTO T_LEISTUNGQTD (BEREICH,LNR,QTD,GVON,GBIS) VALUES(2,'Temporär','D',GETDATE(),GETDATE());

--! Daten aufbessern

--**********************************************************************************************************************************************************************
--* Feldinhalt von "typ" ex Textanalyse ermittelt
--* 0: Textanalyse nicht erfolgreich
--* 1,2,3: Minuten = erste, weitere, pro
--* 11,12,13: Stunden (LMIN auf Minuten umgerechnet!) = erste, weitere, pro 
--* 21,22,23: Stücke = erste, weitere, pro
--*
--* Feldinhalt von "auto" zur Aufteilung von 1,2 | 11,12 | 21,22 in "group": 0=manuell | 1=voll ohne Zusatz | 2=voll mit Zusatz | 3=voll nach Auswahl mit/ohne Zusatz
--* Die Aufteilung findet in "AutomaticSplit" statt
--**********************************************************************************************************************************************************************

EXEC dbo.up_LMIN 2;
UPDATE T_LEISTUNG SET TYP=0 WHERE BEREICH=2;
UPDATE T_LEISTUNG SET TYP=1 WHERE BEREICH=2 AND TYP=0 AND ISNULL(PATINDEX('% erste% min.%',LOWER(BEZG)),0)>1;
UPDATE T_LEISTUNG SET TYP=2 WHERE BEREICH=2 AND TYP=0 AND ISNULL(PATINDEX('% weitere% min.%',LOWER(BEZG)),0)>1;
UPDATE T_LEISTUNG SET TYP=3 WHERE BEREICH=2 AND TYP=0 AND ISNULL(PATINDEX('% pro % min.%',LOWER(BEZG)),0)>1;
UPDATE T_LEISTUNG SET TYP=3 WHERE BEREICH=2 AND TYP=0 AND ISNULL(PATINDEX('% pro % minuten%',LOWER(BEZG)),0)>1;
UPDATE T_LEISTUNG SET TYP=11 WHERE BEREICH=2 AND TYP=0 AND ISNULL(PATINDEX('% erste% std.%',LOWER(BEZG)),0)>1;
UPDATE T_LEISTUNG SET TYP=12 WHERE BEREICH=2 AND TYP=0 AND ISNULL(PATINDEX('% weitere% std.%',LOWER(BEZG)),0)>1;
UPDATE T_LEISTUNG SET TYP=13 WHERE BEREICH=2 AND TYP=0 AND ISNULL(PATINDEX('% pro % std.%',LOWER(BEZG)),0)>1;
UPDATE T_LEISTUNG SET TYP=13 WHERE BEREICH=2 AND TYP=0 AND ISNULL(PATINDEX('% pro % stunden%',LOWER(BEZG)),0)>1;
UPDATE T_LEISTUNG SET TYP=21 WHERE BEREICH=2 AND TYP=0 AND ISNULL(PATINDEX('% erste%',LOWER(BEZG)),0)>1;
UPDATE T_LEISTUNG SET TYP=22 WHERE BEREICH=2 AND TYP=0 AND ISNULL(PATINDEX('% weitere%',LOWER(BEZG)),0)>1;
UPDATE T_LEISTUNG SET TYP=23 WHERE BEREICH=2 AND TYP=0 AND ISNULL(PATINDEX('% pro %',LOWER(BEZG)),0)>1;
UPDATE T_LEISTUNG SET LMIN=LMIN*60 WHERE BEREICH=2 AND TYP>10 AND TYP<14;
UPDATE T_LEISTUNG SET EBB=1 WHERE BEREICH=2 AND ISNULL(PATINDEX('%erhöht%behandlung%',LOWER(BEZG)),0)>1;
UPDATE T_LEISTUNG SET EBB=0 WHERE BEREICH=2 AND EBB IS NULL; 