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

    declare @parsedXml VARCHAR(MAX)

    --Собираем XML
    SELECT @parsedXml =
        (SELECT
               BenefitsID                       AS BenefitsID,
               BenefitCode                      AS BenefitCode,
               BenefitName                      AS BenefitName,
               Actual                           AS Actual,
               PercentPay                       AS PercentPay,
               Private                          AS Private,
               PeriodOfAct                      AS PeriodOfAct,
               Duration                         AS Duration,
               VTE.Code							AS BenefitViews,
               VTE2.Code						AS BenefitType,
               CloseType                        AS CloseType,
               ETER1.CodeEHR					AS EHRRUS1U0GD15H2K,
               RRB.CurrVersion					AS EHRRUS1U0GD15H2KVer,
               ETERS.CodeEHR					AS EHRRUSSDO8U3N5RO,
               RRB1.CurrVersion					AS EHRRUSSDO8U3N5ROVer,
               ETER2.IdEHR						AS EhrRusVzn438,
               RRB2.CurrVersion					AS EhrRusVzn438Ver,
				(SELECT
				        ZAB                         AS  Zab,
				        MKBK                        AS  MKBK,
				        MKBN                        AS  MKBN,
				        SicknessGroupID             AS  SicknessGroupID,
				        Benefits                    AS  Benefits
				    FROM RDS.T_SICKNESS_GROUP ATSG
				        WHERE ATSG.Benefits = ATB.BenefitsID
				FOR XML PATH('SICKNESS_GROUP'), TYPE) as 'SICKNESS'
        FROM RDS.T_BENEFITS ATB
            JOIN VCLib.T_ENUMS VTE ON VTE.EnumID = ATB.BenefitViews
            JOIN VCLib.T_ENUMS VTE2 ON VTE2.EnumID = ATB.BenefitType
            LEFT JOIN EHR.T_EHR_RUS_1U0GD15H2K ETER1 ON ETER1.EhrRusID = ATB.EhrRus1u0gd15h2k
				LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = ETER1.EhrRusRefBooks
            LEFT JOIN EHR.T_EHR_RUS_SDO8U3N5RO ETERS ON ETERS.EhrRusID = ATB.EhrRusSdo8u3n5ro
				LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = ETERS.EhrRusRefBooks
            LEFT JOIN EHR.T_EHR_RUS_VZN438 ETER2 ON ETER2.EhrRusID = ATB.EhrRusVzn438
				LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB2 ON RRB2.EhrRusRefBooksID = ETER2.EhrRusRefBooks
        FOR XML PATH('BENEFIT'), 
		ROOT('BENEFITS')
		)
select @parsedXml
    -- ОТПРАВКА

    INSERT INTO RDS.T_RABBIT_SEND_OBJECT
			(RABBITSENDOBJECTID ,QUEUENAME ,CONTENT ,MESSAGEID ,HEADERS ,CONTENTENCODING ,TYPE ,USERID ,STATE ,CREATEDATETIME ,OWNERID)
		SELECT NEWID()--(<RABBITSENDOBJECTID, UNIQUEIDENTIFIER,>
				,@QUEUENAME--,<QUEUENAME, VARCHAR(255),>
				,dbo.ConvertTextToBinaryZip(@parsedXml, '')--,<CONTENT, TEXT,>
				,NULL--,<MESSAGEID, VARCHAR(255),>
				,NULL--,<HEADERS, VARCHAR(255),>
				,'utf-8'--,<CONTENTENCODING, VARCHAR(255),>
				,'BENEFITS'--,<TYPE, VARCHAR(255),>
				,NULL--,<USERID, VARCHAR(255),>
				,0--,<STATE, INT,>)
				,GETDATE()--,<CREATEDATETIME, DATETIME,>)
				,null--,<OWNERID, VARCHAR(255),>
        from [RDS].T_SYNC_SERVERS s
        where 
			(@QueueName is not null and s.ServerName = @QueueName)
            or (@QueueName is null and s.ServerType in (0, 1)) -- Рассылаем пакет всем подписанным серверам AKUZ-а и APS, если не указана очередь
END