DROP PROCEDURE IF EXISTS sp_f033spmoload;
DROP FUNCTION IF EXISTS sp_f033spmoload;

CREATE PROCEDURE public.sp_f033spmoload("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_f033;

	CREATE TEMP TABLE temp_f033 as
	SELECT 
		UIDSPMO,
		IDSPMO,
		NAM_SPMO,
		NAM_SK_SPMO,
		PHONE,
		OSP,
		VID_SPMO,
		OID_SPMO,
		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,
			IDSPMO text path './IDSPMO' null, 
			NAM_SPMO text path './NAM_SPMO' null, 
			NAM_SK_SPMO text path './NAM_SK_SPMO' null, 
			PHONE text path './PHONE' null, 
			OSP text path './OSP' null, 
			VID_SPMO text path './VID_SPMO' null, 
			OID_SPMO text path './OID_SPMO' null, 
			DATEBEG text path './DATEBEG' null, 
			DATEEND text path './DATEEND' null 
		);


--проставим UIDSPMO хоть как то длЯ записей у которых его нет
--(работает долго , переписал)
-- with cte as(
-- 	select t1.ctid,*
-- from  ies.T_F033_SPMO  t1 
-- join  lateral (select  t2.UIDSPMO as UIDSPMO_new  from  temp_f033  t2 where t2.IDSPMO = t1.IDSPMO order by t2.DATEBEG desc limit  1 ) ca on true
-- where (t1.uidspmo = '' or t1.uidspmo is null) 
-- )
-- update 	ies.T_F033_SPMO  t1 
-- 	set UIDSPMO = t2.UIDSPMO_new
--  from cte t2 
-- 	where t1.ctid = t2.ctid;

	WITH latest_temp_f033 AS (
		SELECT DISTINCT ON (IDSPMO) IDSPMO, UIDSPMO, DATEBEG
		FROM temp_f033
		ORDER BY IDSPMO, DATEBEG DESC
	),
	cte AS (
		SELECT t1.ctid, t1.IDSPMO, latest.UIDSPMO AS UIDSPMO_new
		FROM ies.T_F033_SPMO t1
		JOIN latest_temp_f033 latest ON t1.IDSPMO = latest.IDSPMO
		WHERE t1.uidspmo = '' OR t1.uidspmo IS NULL
	)
	UPDATE ies.T_F033_SPMO t1
	SET UIDSPMO = cte.UIDSPMO_new
	FROM cte 
	WHERE t1.ctid = cte.ctid;


	-- обновляем актуальность у записей которых уже нет в Фед. справочнике
	update IES.T_F033_SPMO t1 
		set Actual = false 
	where not exists(select 1 from temp_f033 t2 where t2.UIDSPMO = t1.UIDSPMO and t2.DATEBEG = t1.DATEBEG)
	and t1.Actual = true;

	-- обновляем актуальность у записей которые есть в Фед. справочнике
	update IES.T_F033_SPMO t1
		set Actual = true 
	where  exists(select 1 from temp_f033 t2 where t2.UIDSPMO = t1.UIDSPMO and t2.DATEBEG = t1.DATEBEG)
	and t1.Actual = false;

	
	UPDATE IES.T_F033_SPMO t1 
	SET 
		   IDSPMO = t2.IDSPMO
	      ,NAM_SPMO = t2.NAM_SPMO
	      ,NAM_SK_SPMO = t2.NAM_SK_SPMO
	      ,PHONE = t2.PHONE
	      ,OSP = t2.OSP
	      ,VID_SPMO = t2.VID_SPMO
	      ,OID_SPMO = t2.OID_SPMO
	      ,DATEEND = t2.DATEEND
	FROM temp_f033 t2 
	WHERE t2.UIDSPMO = t1.UIDSPMO and t2.DATEBEG = t1.DATEBEG and t1.Actual = true;

	INSERT INTO IES.T_F033_SPMO
	(
		F033SpmoID,UIDSPMO, IDSPMO, NAM_SPMO, NAM_SK_SPMO, PHONE, 
		OSP, VID_SPMO, OID_SPMO, DATEBEG, DATEEND ,Actual
	)
	SELECT
		gen_random_uuid(),t1.UIDSPMO, t1.IDSPMO, t1.NAM_SPMO, t1.NAM_SK_SPMO, t1.PHONE, 
		t1.OSP, t1.VID_SPMO, t1.OID_SPMO, t1.DATEBEG, t1.DATEEND,true
	FROM temp_f033 t1
	LEFT JOIN IES.T_F033_SPMO t2 on t2.UIDSPMO = t1.UIDSPMO and t2.DATEBEG = t1.DATEBEG and t2.Actual = true
	WHERE t2.F033SpmoID is null;
	
	DROP TABLE IF EXISTS temp_f033;
	
	return;
END
$BODY$;


