DROP PROCEDURE IF EXISTS sp_EhrOnkTNMLoad;
DROP FUNCTION IF EXISTS sp_EhrOnkTNMLoad;

CREATE PROCEDURE public.sp_EhrOnkTNMLoad("Xml" bytea, "FileName" character varying default '')
LANGUAGE 'plpgsql'
AS $BODY$
	
declare T xml;
declare tempStrXml text;
BEGIN

	select convert_from("Xml", 'UTF-8') into tempStrXml;
	
	select replace(tempStrXml, 'encoding="UTF-8"','')::xml into T; 
	
	DROP TABLE IF EXISTS temp_EhrOnkTNM;

	CREATE TEMP TABLE temp_EhrOnkTNM as
	SELECT 
		ID::int,
		ShortNameOfTopography,
		ICDOTopography,
		Morphology,
		Stage,
		Tumor,
		ID_Tumor::int,
		Nodus,
		ID_Nodus::int,
		Metastasis,
		ID_Metastasis::int,
		Addition,
		ID_Addition::int,
		Classification::int,
		Version::int
	
	FROM
		
	XMLTABLE 
		(
			'/book/entries/entry' PASSING T 
			COLUMNS
			ID text path './ID' null,
			ShortNameOfTopography text path './ShortNameOfTopography' null, 
			ICDOTopography text path './ICDOTopography' null, 
			Morphology text path './Morphology' null, 
			Stage text path './Stage' null, 
			Tumor text path './Tumor' null, 
			ID_Tumor text path './ID_Tumor' null, 
			Nodus text path './Nodus' null, 
			ID_Nodus text path './ID_Nodus' null, 
			Metastasis text path './Metastasis' null,
			ID_Metastasis text path './ID_Metastasis' null, 
			Addition text path './Addition' null, 
			ID_Addition text path './ID_Addition' null, 
			Classification text path './Classification' null,
			Version text path './Version' null 
		);


	UPDATE IES.T_EHR_ONK_TNM t1 
	SET 
		   ID = t2.ID
	      ,ShortNameOfTopography = t2.ShortNameOfTopography
	      ,ICDOTopography = t2.ICDOTopography
	      ,Morphology = t2.Morphology
	      ,Stage = t2.Stage
	      ,Tumor = t2.Tumor
	      ,ID_Tumor = t2.ID_Tumor
	      ,Nodus = t2.Nodus
		  ,ID_Nodus = t2.ID_Nodus
		  ,Metastasis = t2.Metastasis
	      ,ID_Metastasis = t2.ID_Metastasis
	      ,Addition = t2.Addition
		  ,ID_Addition = t2.ID_Addition
	      ,Classification = t2.Classification
	      ,Version = t2.Version
	FROM temp_EhrOnkTNM t2 
	WHERE t2.ID = t1.ID;

	INSERT INTO IES.T_EHR_ONK_TNM
	(
		EhrOnkTnmID, ID, ShortNameOfTopography, ICDOTopography, Morphology, Stage, 
		Tumor, ID_Tumor, Nodus, ID_Nodus, Metastasis ,ID_Metastasis, Addition,
		ID_Addition, Classification, Version
	)
	SELECT
		gen_random_uuid(),t1.ID, t1.ShortNameOfTopography, t1.ICDOTopography, t1.Morphology, t1.Stage, 
		t1.Tumor, t1.ID_Tumor, t1.Nodus, t1.ID_Nodus, t1.Metastasis, t1.ID_Metastasis, t1.Addition,
		t1.ID_Addition, t1.Classification, t1.Version
	FROM temp_EhrOnkTNM t1
	LEFT JOIN IES.T_EHR_ONK_TNM t2 on t2.ID = t1.ID
	WHERE t2.EhrOnkTnmID is null;
	
	return;
END
$BODY$;
