DROP PROCEDURE IF EXISTS sp_n006tnmload;
DROP FUNCTION IF EXISTS sp_n006tnmload;

CREATE PROCEDURE public.sp_n006tnmload("Xml" bytea, "FileName" character varying)
LANGUAGE 'plpgsql'
AS $BODY$
	
declare T xml;
declare tempStrXml text;
BEGIN
	select convert_from("Xml", 'WIN1251') into tempStrXml;
	
	select replace(tempStrXml, 'encoding="windows-1251"','')::xml into T; 
	
	DROP TABLE IF EXISTS temp_n006;

	CREATE TEMP TABLE temp_n006 as
	SELECT 
		ID_gr::int, 
		DS_gr, 
		ID_St::int, 
		ID_T::int, 
		ID_N::int, 
		ID_M::int, 
		CASE WHEN  DATEBEG  = '' THEN NULL ELSE DATEBEG::TIMESTAMP END , 
		CASE WHEN  DATEEND  = '' THEN NULL ELSE DATEEND::TIMESTAMP END 
	FROM
	XMLTABLE ('/packet/zap' PASSING T 
	COLUMNS 
		ID_gr text path './ID_gr' null, 
		DS_gr text path './DS_gr' null, 
		ID_St text path './ID_St' null, 
		ID_T text path './ID_T' null, 
		ID_N text path './ID_N' null, 
		ID_M text path './ID_M' null, 
		DATEBEG text path './DATEBEG' null, 
		DATEEND text path './DATEEND' null );
	
	update IES.T_N006_TNM t2
	set  DS_gr=t1.DS_gr
		,DATEEND=t1.DATEEND
	from temp_n006 t1
	where  (t1.ID_gr=t2.ID_gr and t1.DATEBEG = t2.DATEBEG);

	insert into IES.T_N006_TNM(N006TnmID, ID_gr, DS_gr, N002Stadium, N003Tumor, N004Nodus, N005Metastasis, DATEBEG, DATEEND)
	select gen_random_uuid(),t1.ID_gr,t1.DS_gr,t3.N002StadiumID,t4.N003TumorID,t5.N004NodusID,t6.N005MetastasisID,t1.DATEBEG,t1.DATEEND
	from temp_n006 t1
	left join IES.T_N006_TNM t2 on (t1.ID_gr=t2.ID_gr and t1.DATEBEG = t2.DATEBEG)
	join IES.T_N002_STADIUM t3 on (t1.ID_St = t3.ID_St)
	join IES.T_N003_TUMOR t4 on (t1.ID_T = t4.ID_T)
	join IES.T_N004_NODUS t5 on (t1.ID_N = t5.ID_N)
	join IES.T_N005_METASTASIS t6 on (t1.ID_M = t6.ID_M)
	where t2.N006TnmID is null;

	drop table if exists temp_n006;
	
	return;
END
$BODY$;
