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: Загрузка справочника льгот на AKUZ
-- =============================================
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.CodeEHR = T.c.value('./EHRRUS1U0GD15H2K[1]', 'VARCHAR(255)') and RRB.CURRVERSION = T.c.value('./EHRRUS1U0GD15H2KVer[1]', 'VARCHAR(255)')
		        ) 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)') and RRB.CURRVERSION = T.c.value('./EHRRUSSDO8U3N5ROVer[1]', 'VARCHAR(255)')

		        ) 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 по таблице SICKNESS_GROUP

        SELECT
            T.c.value('(./MKBN/text())[1]', 'varchar(8)') AS MKBN,
            T.c.value('(./MKBK/text())[1]', 'varchar(8)') AS MKBK,
            T.c.value('(./SicknessGroupID/text())[1]', 'varchar(255)') AS SicknessGroupID,
            T.c.value('(./Benefits/text())[1]', 'varchar(255)') AS Benefits
        INTO #SICKNESS_GROUP
            from @Xml.nodes('BENEFITS/BENEFIT/SICKNESS/SICKNESS_GROUP') T(c)

    -- Переключаем в неактуальные те льготы, которых нету в XML
    update b
        set b.Actual = 0
        from [AKUZ].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 = Isnull(nb.PercentPay,100),
            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 [AKUZ].T_BENEFITS b
            JOIN #benefits nb ON 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, 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,>
            ,Isnull(nb.PercentPay,100)	--<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 [AKUZ].T_BENEFITS b
		        WHERE nb.BenefitCode = b.BenefitCode AND nb.BenefitViewsEnum = b.BenefitViews
		    )

    --Очищаем таблицу льготников перед загрузкой
    TRUNCATE TABLE [AKUZ].T_SICKNESS_GROUP

    --Загружаем новых льготников
    INSERT INTO [AKUZ].[T_SICKNESS_GROUP]
        (MKBN, MKBK, SicknessGroupID, Benefits)
            SELECT MKBN,MKBK,SicknessGroupID,ATB.BenefitsID
			FROM #SICKNESS_GROUP SG
			    JOIN #benefits B ON B.BenefitsID = SG.Benefits
			    JOIN [AKUZ].T_BENEFITS ATB ON ATB.BenefitCode = B.BenefitCode AND ATB.BenefitViews = B.BenefitViewsEnum


    -- Логируем записи с ошибками
    INSERT INTO [AKUZ].[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