DROP PROCEDURE IF EXISTS sp_EhrTNMTopogrMapLoad;
DROP FUNCTION IF EXISTS sp_EhrTNMTopogrMapLoad;

CREATE PROCEDURE public.sp_EhrTNMTopogrMapLoad("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_EhrTopogrMapTNM;

	CREATE TEMP TABLE temp_EhrTopogrMapTNM as
	SELECT 
		ICD10,
		ICDOTopography,
		TNM_7::bool,
		TNM_8::bool

	FROM
		
	XMLTABLE 
		(
			'/book/entries/entry' PASSING T 
			COLUMNS
			ICD10 text path './ICD10' null,
			ICDOTopography text path './ICDOTopography' null, 
			TNM_7 text path './TNM_7' null, 
			TNM_8 text path './TNM_8' null 
		);


	UPDATE IES.T_EHR_TNM_TOPOGR_MAP t1 
	SET 
		   ICD10 = t2.ICD10
	      ,ICDOTopography = t2.ICDOTopography
	      ,TNM_7 = t2.TNM_7
	      ,TNM_8 = t2.TNM_8
	FROM temp_EhrTopogrMapTNM t2 
	WHERE t2.ICD10 = t1.ICD10 and t2.ICDOTopography = t1.ICDOTopography;

	INSERT INTO IES.T_EHR_TNM_TOPOGR_MAP
	(
		TNMTopogrID, ICD10, ICDOTopography, TNM_7, TNM_8
	)
	SELECT
		gen_random_uuid(),t1.ICD10, t1.ICDOTopography, t1.TNM_7, t1.TNM_8
	FROM temp_EhrTopogrMapTNM t1
	LEFT JOIN IES.T_EHR_TNM_TOPOGR_MAP t2 on t2.ICD10 = t1.ICD10
			  and t2.ICDOTopography = t1.ICDOTopography
	WHERE t2.TNMTopogrID is null;
	
	return;
END
$BODY$;
