IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_UpdateBenefitsFromRDS]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_UpdateBenefitsFromRDS]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Vitacore (с)
-- Create date: 2021-05-12
-- Description: Загрузка справочника льгот
-- =============================================
CREATE PROCEDURE [dbo].[sp_UpdateBenefitsFromRDS]
    @Xml XML = null
    ,@QueueName varchar(100) = 'RDSSync'
    ,@dateTime datetime = null
AS
BEGIN
    SET NOCOUNT ON;

    select @dateTime = ISNULL(@dateTime, GETDATE())

    -- Получаем данные из XML по таблице Benefits
        SELECT
            T.c.value('(./BenefitsID/text())[1]', 'varchar(255)') as BenefitsID,
            T.c.value('(./BenefitCode/text())[1]', 'varchar(255)') as BenefitCode,
            T.c.value('(./BenefitName/text())[1]', 'varchar(255)') as BenefitName,
            T.c.value('(./Actual/text())[1]', 'bit') as Actual,
            T.c.value('(./Private/text())[1]', 'bit') as Private,
            T.c.value('(./PercentPay/text())[1]', 'int') as PercentPay,
            T.c.value('(./BenefitViews/text())[1]', 'int') as BenefitViews,
            T.c.value('(./BenefitType/text())[1]', 'int') as BenefitType,
            T.c.value('(./CloseType/text())[1]', 'int') as CloseType,
            T.c.value('(./PeriodOfAct/text())[1]', 'int') as PeriodOfAct,
            T.c.value('(./Duration/text())[1]', 'int') as Duration,
            bView.EnumID as BenefitViewsEnum,
            bType.EnumID as BenefitTypeEnum,
            eh1.EhrRusID as EhrRus1u0gd15h2k,
            eh2.EhrRusID as EhrRusSdo8u3n5ro,
            eh3.EhrRusID as EhrRusVzn438
        INTO #benefits
            from @Xml.nodes('BENEFITS/BENEFIT') T(c)
                left join [VCLib].T_ENUMS bView on bView.EnumTypeID = 190
                        and
                    bView.Code = T.c.value('./BenefitViews[1]', 'INT')
                left join [VCLib].T_ENUMS bType on bType.EnumTypeID = 242
                        and
                    bType.Code = T.c.value('./BenefitType[1]', 'INT')
                outer apply (
		        	select top 1 n1.EhrRusID
		        	from EHR.T_EHR_RUS_1U0GD15H2K n1
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = n1.EhrRusRefBooks
					where n1.IdEHR = T.c.value('./EHRRUS1U0GD15H2K[1]', 'INT')
					ORDER BY RRB.CURRVERSION DESC
		        ) eh1
		        outer apply (
		        	select top 1 n1.EhrRusID
		        	from EHR.T_EHR_RUS_SDO8U3N5RO n1
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = n1.EhrRusRefBooks
					where n1.CodeEHR = T.c.value('./EHRRUSSDO8U3N5RO[1]', 'VARCHAR(255)')
                    ORDER BY RRB.CURRVERSION DESC
		        ) eh2
		        outer apply (
		        	select top 1 ehr1.EhrRusID
		        	from EHR.T_EHR_RUS_VZN438 ehr1
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = ehr1.EhrRusRefBooks
					where RRB.CURRVERSION = T.c.value('./EhrRusVzn438Ver[1]', 'VARCHAR(255)') and ehr1.IdEHR = T.c.value('./EhrRusVzn438[1]', 'int')

		        ) eh3



    -- Переключаем в неактуальные те льготы, которых нету в XML
    update b
    set b.Actual = 0
    from [APS].T_BENEFITS b
    where not exists
        (
            select *
            from #benefits nb
            where nb.BenefitCode = b.BenefitCode
                    and
                nb.BenefitViewsEnum = b.BenefitViews
        )


    -- Обновляем имеющиеся льготы
    UPDATE b
        SET
            b.BenefitName = nb.BenefitName,
            b.Actual = nb.Actual,
            b.PercentPay = nb.PercentPay,
            --b.Private = isnull(nb.Private, b.Private),
            b.BenefitType = nb.BenefitTypeEnum,
            b.EhrRus1u0gd15h2k = nb.EhrRus1u0gd15h2k,
            b.EhrRusSdo8u3n5ro = nb.EhrRusSdo8u3n5ro,
            b.EhrRusVzn438 = nb.EhrRusVzn438,
            b.CloseType = nb.CloseType,
            b.PeriodOfAct = nb.PeriodOfAct,
            b.Duration = nb.Duration
        FROM [APS].T_BENEFITS b
            JOIN #benefits nb ON nb.BenefitCode = b.BenefitCode AND nb.BenefitViewsEnum = b.BenefitViews

    -- Загружаем новые льготы
    INSERT INTO [APS].[T_BENEFITS] ([BenefitsID] ,[BenefitViews] ,[BenefitCode] ,[BenefitName] ,[Actual] ,[PercentPay] ,[RecipeValidationLexem],--[Private], 
                [BenefitType], EhrRus1u0gd15h2k, EhrRusSdo8u3n5ro, CloseType, PeriodOfAct, Duration,EhrRusVzn438)
        SELECT
            NEWID(),--<BenefitsID, uniqueidentifier,>
            nb.BenefitViewsEnum,--<BenefitViews, int,>
            nb.BenefitCode,--<BenefitCode, varchar(255),>
            nb.BenefitName,--<BenefitName, varchar(255),>
            nb.Actual,--<Actual, bit,>
            nb.PercentPay,--<PercentPay, int,>
            NULL,--<RecipeValidationLexem, text,>)
            --isnull(nb.Private, 0),
            nb.BenefitTypeEnum,
            nb.EhrRus1u0gd15h2k,
            nb.EhrRusSdo8u3n5ro,
            nb.CloseType,
            nb.PeriodOfAct,
            nb.Duration,
			nb.EhrRusVzn438
        FROM #benefits nb
            WHERE NOT EXISTS
                (
					SELECT * FROM [APS].T_BENEFITS b
					WHERE 
						nb.BenefitCode = b.BenefitCode
						AND nb.BenefitViewsEnum = b.BenefitViews
                )





    -- Логируем записи с ошибками
    INSERT INTO [APS].[T_RDS_SYNCHRONISATION_ERRORS]
            ([RdsSynchronisationErrorsID] ,[ErrorDescription] ,[DateTime] ,[Contents] ,[Users] ,[PackageType] ,[ErrorType] ,[ProcessedError] ,[LockResource])
    SELECT top 1
            NEWID(),--<RdsSynchronisationErrorsID, uniqueidentifier,>
            (
                 SELECT N'Для следующих записей справочника не удалось подобрать "Вид льготы":' +
                 (
                     SELECT '' + cast(enb.BenefitCode AS VARCHAR(36)) + '|' + cast(enb.BenefitViews AS VARCHAR(36))
                     FROM #benefits enb
                     WHERE enb.BenefitViewsEnum IS NULL FOR XML PATH (''), TYPE
                 ).value('.[1]', 'varchar(max)')
            ),--<ErrorDescription, text,>
            @dateTime,--<DateTime, datetime,>
            CAST(@Xml AS NVARCHAR(MAX)),--<Contents, text,>
            NULL,--<Users, uniqueidentifier,>
            'BENEFITS',--<PackageType, varchar(255),>
            0,--<ErrorType, int,>
            0,--<ProcessedError, bit,>
            NULL--<LockResource, varchar(255),>)
    FROM #benefits nb
    WHERE nb.BenefitViewsEnum IS NULL


    -- Drop Table
    DROP TABLE #benefits
END