DROP PROCEDURE IF EXISTS sp_f034vupload;
DROP FUNCTION IF EXISTS sp_f034vupload;

CREATE PROCEDURE public.sp_f034vupload("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_f034;

	CREATE TEMP TABLE temp_f034 as
	SELECT 
		UIDSPMO,
		IDADDRESS,
		MPVID,
		MPUSL,
		MPROF,
		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
			UIDSPMO text path './UIDSPMO' null,
			IDADDRESS text path './IDADDRESS' null, 
			MPVID int path './MPVID' null, 
			MPUSL int path './MPUSL' null, 
			MPROF int path './MPROF' null,
			DATEBEG text path './DATEBEG' null, 
			DATEEND text path './DATEEND' null 
		);

	CREATE  INDEX IF NOT EXISTS id_MPVID
	ON temp_f034
		(MPVID);	
	CREATE  INDEX IF NOT EXISTS id_MPUSL
	ON temp_f034
		(MPUSL);
	  CREATE  INDEX IF NOT EXISTS id_MPROF
	ON temp_f034
		(MPROF);

	-- обновляем актуальность у записей которых уже нет в Фед. справочнике
	update	 IES.T_F034_VUP t2
		set Actual = false 
	where 
	not exists(
						select 1 
						from temp_f034 t1 
							JOIN ies.T_V008_MEDICAL_TYPE v008 ON t1.MPVID = v008.IDVMP
							JOIN ies.T_V006_MEDICAL_TERMS v006 ON t1.MPUSL = v006.IDUMP
							JOIN ies.T_V002_PROFILE v002 ON t1.MPROF = v002.IDPR
						where t2.UIDSPMO = t1.UIDSPMO and t2.IDADDRESS = t1.IDADDRESS and t2.MPVID = v008.IDVMP 
								and t2.MPUSL = v006.IDUMP and t2.MPROF = v002.IDPR and t2.DATEBEG = t1.DATEBEG
			  )
	and t2.Actual = true;

		INSERT INTO IES.T_F034_VUP
				(F034VupID, UIDSPMO, IDADDRESS, MPVID, MPUSL, MPROF, DATEBEG, DATEEND, Actual)
		select
				gen_random_uuid(), t1.UIDSPMO, t1.IDADDRESS, v008.IDVMP, v006.IDUMP, v002.IDPR, t1.DATEBEG, t1.DATEEND,true
	FROM temp_f034 t1
					JOIN ies.T_V008_MEDICAL_TYPE v008 ON t1.MPVID = v008.IDVMP
					JOIN ies.T_V006_MEDICAL_TERMS v006 ON t1.MPUSL = v006.IDUMP
					JOIN ies.T_V002_PROFILE v002 ON t1.MPROF = v002.IDPR
	left join IES.T_F034_VUP t2 on t2.UIDSPMO = t1.UIDSPMO and t2.IDADDRESS = t1.IDADDRESS and t2.MPVID = v008.IDVMP and t2.MPUSL = v006.IDUMP and t2.MPROF = v002.IDPR and t2.DATEBEG = t1.DATEBEG and t2.Actual = true
	where t2.F034VupID is null;
	
	
	DROP TABLE IF EXISTS temp_f034;

	return;
END
$BODY$;

