do $$
begin
	if exists 
	(
		select * from pg_proc p
		where p.proname = 'sp_updatebenefitsfromrds'
	)
	then
		drop function public.sp_updatebenefitsfromrds(text, varchar(100), timestamp);
	end if;
end;
$$ language plpgsql;
-- =============================================
-- Author:        Vitacore (с)
-- Create date: 2015-07-28
-- Description: Загрузка справочника льгот
-- =============================================
CREATE or REPLACE FUNCTION public.sp_UpdateBenefitsFromRDS
(
	"Xml" text
,	"QueueName" varchar(100) = 'RDSSync'
,	"DateTime" TimeStamp = null
)
RETURNS void AS
$$
Declare	parsedXml xml;
BEGIN
	"DateTime" := Coalesce("DateTime", Current_TimeStamp::TIMESTAMP);
   parsedXml := "Xml"::xml;
	-- Получаем данные из XML
	drop table if exists TmpBenefits;
	create temp table TmpBenefits(BenefitCode varchar(255), BenefitName varchar(255), Actual boolean, PercentPay int, BenefitViews int, BenefitViewsEnum int);
	insert into TmpBenefits(BenefitCode, BenefitName, Actual, PercentPay, BenefitViews, BenefitViewsEnum)
	select	trim(xpath('/BENEFIT/BenefitCode[1]/text()',x)::text,'{}')::varchar(255) as BenefitCode
	,	trim(xpath('/BENEFIT/BenefitName[1]/text()',x)::text,'{}')::varchar(255) as BenefitName
	,	trim(xpath('/BENEFIT/Actual[1]/text()', x)::text,'{}')::boolean as Actual
	,	trim(xpath('/BENEFIT/PercentPay[1]/text()', x)::text,'{}')::int as PercentPay
	,	trim(xpath('/BENEFIT/BenefitViews[1]/text()',x)::text,'{}')::int as BenefitViews
	,	trim(xpath('/BENEFIT/BenefitType[1]/text()',x)::text,'{}')::int as BenefitType
	,	bView.EnumID as BenefitViewsEnum
	,	bType.EnumID as BenefitTypeEnum
	,	(select n1.EhrRusID 
			from EHR.T_EHR_RUS_1U0GD15H2K n1
			where n1.IdEHR = (trim(xpath('/BENEFIT/EhrRus1u0gd15h2k[1]/text()',x)::text,'{}')::int)
			limit 1) as EhrRus1u0gd15h2k
	,	(select n1.EhrRusID 
			from EHR.T_EHR_RUS_SDO8U3N5RO n1
			where n1.CodeEHR = (trim(xpath('/BENEFIT/EhrRus1u0gd15h2k[1]/text()',x)::text,'{}')::varchar(52))
			limit 1) as EhrRusSdo8u3n5ro
	from 
	(
		select unnest(xpath('/BENEFITS/body/RDS.BENEFITS/BENEFIT', x)) as x
		from 
		(
			select parsedXml as x
		) q
	) q
	left join 
		vclib.T_ENUMS bView 
		on bView.EnumTypeID = 190
		and bView.Code = trim(xpath('/BENEFIT/BenefitViews[1]/text()',x)::text,'{}')::int
	left join 
		vclib.T_ENUMS bType 
		on bType.EnumTypeID = 242
		and bType.Code = trim(xpath('/BENEFIT/BenefitType[1]/text()',x)::text,'{}')::int;
	
        -- Переключаем в неактуальные те льготы, которых нету в XML
	update akuz.T_BENEFITS b
	set Actual = '0'
	where not exists
   (
		select *
		from TmpBenefits nb
		where nb.BenefitCode = b.BenefitCode
      and nb.BenefitViewsEnum = b.BenefitViews
   );

	-- Загружаем новые льготы
	INSERT INTO akuz.T_BENEFITS(BenefitsID, BenefitViews, BenefitCode, BenefitName, Actual, PercentPay, RecipeValidationLexem,Private, BenefitType, EhrRus1u0gd15h2k, EhrRusSdo8u3n5ro)
	SELECT uuid_generate_v1(), nb.BenefitViewsEnum, nb.BenefitCode, nb.BenefitName, nb.Actual, nb.PercentPay, NULL, '0', nb.BenefitTypeEnum, EhrRus1u0gd15h2k, EhrRusSdo8u3n5ro
	from TmpBenefits nb
	where not exists
   (
		select *
		from akuz.T_BENEFITS b
		where nb.BenefitCode = b.BenefitCode
      and nb.BenefitViewsEnum = b.BenefitViews
   );

	-- Обновляем имеющиеся льготы
	update akuz.T_BENEFITS b
	set	BenefitName = nb.BenefitName
   ,	Actual = nb.Actual
   ,	PercentPay = nb.PercentPay
   ,	BenefitType = nb.BenefitTypeEnum
        ,EhrRus1u0gd15h2k = nb.EhrRus1u0gd15h2k
        ,EhrRusSdo8u3n5ro = nb.EhrRusSdo8u3n5ro
	from TmpBenefits nb 
	where nb.BenefitCode = b.BenefitCode
   and nb.BenefitViewsEnum = b.BenefitViews;

	-- Логируем записи с ошибками
	INSERT INTO akuz.T_RDS_SYNCHRONISATION_ERRORS(RdsSynchronisationErrorsID, ErrorDescription, DateTime, Contents, Users, PackageType, ErrorType, ProcessedError, LockResource)
	SELECT	uuid_generate_v1()
   ,(		
		select 'Для следующих записей справочника не удалось подобрать "Вид льготы":
    ' || string_agg(BenefitCode || '|' || BenefitViews::varchar(36),'
    ') 
		from TmpBenefits 
		where BenefitViewsEnum is null       
    )
   ,	"DateTime"
   ,	"Xml"
   ,	null
   ,	'BENEFITS'
   ,	0
   ,	'0'
   ,	null
	from TmpBenefits nb
	where nb.BenefitViewsEnum is null
	limit 1;
    -- Drop Table
    DROP TABLE if exists TmpBenefits;
END;
$$ LANGUAGE plpgsql;