SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetSetMedicamentsOfRoszdravnadzor]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetSetMedicamentsOfRoszdravnadzor]
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2019-10-29
-- Description: Выгрузка/загрузка справочника Медикаменты Росздравнадзора в(из) RDS
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetSetMedicamentsOfRoszdravnadzor]
    @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 @Result varchar(max)
           ,@ContentEncoding varchar(255)
           ,@Type varchar(255)
           ,@OperationType varchar(6)
           ,@OwnerID uniqueidentifier
            -- Operation types
           ,@INSERT varchar(6)
           ,@UPDATE varchar(6)
           ,@DELETE varchar(6)
           ,@FTLOAD varchar(6)

    select @dateTime = ISNULL(@dateTime, GETDATE())
          ,@ContentEncoding = 'utf-8'
          ,@Type = 'MEDICAMENT_OF_ROSZDRAVNADZOR'
            -- Operation types
          ,@INSERT = 'Insert'
          ,@UPDATE = 'Update'
          ,@DELETE = 'Delete'
          ,@FTLOAD = 'FTLoad'

    IF (@Xml is null)
    BEGIN
        -- Инициализация
        select @OwnerID = @ID

        --выясняем тип операции
        IF (@ID is null)
        BEGIN
            select @OperationType = @FTLOAD
        END
        ELSE IF(@IsNew = 1)
        BEGIN
            select @OperationType = @INSERT
        END
        ELSE IF (@IsNew = 0 and exists (select * from [PHARM].T_MEDICAMENTS_OF_ROSZDRAVNADZOR where MedicamentsOfRoszdravnadzorID = @ID))
        BEGIN
            select @OperationType = @UPDATE
        END
        ELSE IF (@IsNew = 0 and not exists (select * from [PHARM].T_MEDICAMENTS_OF_ROSZDRAVNADZOR where MedicamentsOfRoszdravnadzorID = @ID))
        BEGIN
            select @OperationType = @DELETE
            select @Result =
            (
                select @OperationType as 'header/TYPE'
                      ,@ID            as 'header/ID'
                FOR XML PATH(''),
                ROOT('MEDICAMENT_OF_ROSZDRAVNADZOR')
            )
        END

        -- Обработка операции
        IF (@OperationType = @FTLOAD)
        BEGIN
            -- Первичная загрузка
            select @Result =
            (
                select @FTLOAD						as 'header/TYPE'
                      ,(
                            select m.MedicamentsOfRoszdravnadzorID		as 'MEDICAMENT_OF_ROSZDRAVNADZOR/ID'
							,m.DrugID									as 'MEDICAMENT_OF_ROSZDRAVNADZOR/DrugID'
							,m.TradeNmR									as 'MEDICAMENT_OF_ROSZDRAVNADZOR/TradeNmR'
							,m.InnR										as 'MEDICAMENT_OF_ROSZDRAVNADZOR/InnR'
							,m.PackNx									as 'MEDICAMENT_OF_ROSZDRAVNADZOR/PackNx'
							,m.DosageR									as 'MEDICAMENT_OF_ROSZDRAVNADZOR/DosageR'
							,m.PackQn									as 'MEDICAMENT_OF_ROSZDRAVNADZOR/PackQn'
							,m.Pack										as 'MEDICAMENT_OF_ROSZDRAVNADZOR/Pack'
							,m.DrugFmNmRS								as 'MEDICAMENT_OF_ROSZDRAVNADZOR/DrugFmNmRS'
							,m.Segment									as 'MEDICAMENT_OF_ROSZDRAVNADZOR/Segment'
							,m.[Year]									as 'MEDICAMENT_OF_ROSZDRAVNADZOR/Year'
							,m.[Month]									as 'MEDICAMENT_OF_ROSZDRAVNADZOR/Month'
							,m.Series									as 'MEDICAMENT_OF_ROSZDRAVNADZOR/Series'
							,m.TotDrugQn								as 'MEDICAMENT_OF_ROSZDRAVNADZOR/TotDrugQn'
							,m.MnfPrice									as 'MEDICAMENT_OF_ROSZDRAVNADZOR/MnfPrice'
							,m.PrcPrice									as 'MEDICAMENT_OF_ROSZDRAVNADZOR/PrcPrice'
							,m.RtlPrice									as 'MEDICAMENT_OF_ROSZDRAVNADZOR/RtlPrice'
							,m.Funds									as 'MEDICAMENT_OF_ROSZDRAVNADZOR/Funds'
							,m.VendorID									as 'MEDICAMENT_OF_ROSZDRAVNADZOR/VendorID'
							,m.Remark									as 'MEDICAMENT_OF_ROSZDRAVNADZOR/Remark'
							,m.SrcOrg									as 'MEDICAMENT_OF_ROSZDRAVNADZOR/SrcOrg'
							,m.EAN										as 'MEDICAMENT_OF_ROSZDRAVNADZOR/EAN'
							,m.ExpiTermR								as 'MEDICAMENT_OF_ROSZDRAVNADZOR/ExpiTermR'
							,m.ClNm										as 'MEDICAMENT_OF_ROSZDRAVNADZOR/ClNm'
							,m.MnfNm									as 'MEDICAMENT_OF_ROSZDRAVNADZOR/MnfNm'
							,m.PckNm									as 'MEDICAMENT_OF_ROSZDRAVNADZOR/PckNm'
							,m.RegNr									as 'MEDICAMENT_OF_ROSZDRAVNADZOR/RegNr'
							,m.MaxMnfPrice								as 'MEDICAMENT_OF_ROSZDRAVNADZOR/MaxMnfPrice'
							,convert(varchar(10), m.RegDate, 104)		as 'MEDICAMENT_OF_ROSZDRAVNADZOR/RegDate'
							,m.Active									as 'MEDICAMENT_OF_ROSZDRAVNADZOR/Active'
                            from [PHARM].T_MEDICAMENTS_OF_ROSZDRAVNADZOR m
                            where m.Active = 1
                            for xml path(''),
                            type
                      ) as 'body/MEDICAMENTS_OF_ROSZDRAVNADZOR'
                for xml path(''),
                root('MEDICAMENT_OF_ROSZDRAVNADZOR')
            )
        END
        ELSE IF(@OperationType <> @DELETE)
        BEGIN

            SELECT @Result =
            (
                select
					 @OperationType							as 'header/TYPE'
                    ,@ID									as 'header/ID'
					,m.DrugID								as 'body/DrugID'
					,m.TradeNmR								as 'body/TradeNmR'
					,m.InnR									as 'body/InnR'
					,m.PackNx								as 'body/PackNx'
					,m.DosageR								as 'body/DosageR'
					,m.PackQn								as 'body/PackQn'
					,m.Pack									as 'body/Pack'
					,m.DrugFmNmRS							as 'body/DrugFmNmRS'
					,m.Segment								as 'body/Segment'
					,m.[Year]								as 'body/Year'
					,m.[Month]								as 'body/Month'
					,m.Series								as 'body/Series'
					,m.TotDrugQn							as 'body/TotDrugQn'
					,m.MnfPrice								as 'body/MnfPrice'
					,m.PrcPrice								as 'body/PrcPrice'
					,m.RtlPrice								as 'body/RtlPrice'
					,m.Funds								as 'body/Funds'
					,m.VendorID								as 'body/VendorID'
					,m.Remark								as 'body/Remark'
					,m.SrcOrg								as 'body/SrcOrg'
					,m.EAN									as 'body/EAN'
					,m.ExpiTermR							as 'body/ExpiTermR'
					,m.ClNm									as 'body/ClNm'
					,m.MnfNm								as 'body/MnfNm'
					,m.PckNm								as 'body/PckNm'
					,m.RegNr								as 'body/RegNr'
					,m.MaxMnfPrice							as 'body/MaxMnfPrice'
					,convert(varchar(10), m.RegDate, 104)	as 'body/RegDate'
					,m.Active								as 'body/Active'
                from [PHARM].T_MEDICAMENTS_OF_ROSZDRAVNADZOR m
                where m.MedicamentsOfRoszdravnadzorID = @ID
                for xml path(''),
                root('MEDICAMENT_OF_ROSZDRAVNADZOR')
            )
        END

        -- Отправка
        INSERT INTO [RDS].[T_RABBIT_SEND_OBJECT]
            ([RabbitSendObjectID] ,[QueueName] ,[Content] ,[MessageID] ,[Headers] ,[ContentEncoding] ,[Type] ,[UserID] ,[State] ,[CreateDateTime] ,[OwnerID])
        select NewID()--(<RabbitSendObjectID, uniqueidentifier,>
              ,s.ServerName--,<QueueName, varchar(255),>
              ,dbo.ConvertTextToBinaryZip(@Result, '')--,<Content, text,>
              ,null--,<MessageID, varchar(255),>
              ,null--,<Headers, varchar(255),>
              ,@ContentEncoding--,<ContentEncoding, varchar(255),>
              ,@Type--,<Type, varchar(255),>
              ,@User--,<UserID, varchar(255),>
              ,0--,<State, int,>)
              ,@dateTime--,<CreateDateTime, datetime,>)
              ,@OwnerID--,<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
    ELSE
    IF (@Xml is not null)
    BEGIN

        -- Получаем тип операции
        select @OperationType = t.c.value('./header[1]/TYPE[1]', 'varchar(6)')
              ,@OwnerID = t.c.value('./header[1]/ID[1]', 'uniqueidentifier')
              ,@Result = cast(@Xml as varchar(max))
        from @Xml.nodes('MEDICAMENT_OF_ROSZDRAVNADZOR') T(c)


        -- Обработка операции
        if (@OperationType = @FTLOAD)
        BEGIN
            -- Generate table #MEDICAMENTS_OF_ROSZDRAVNADZOR
            select t.c.value('./ID[1]', 'uniqueidentifier')                         as [ID]
                 , t.c.value('./DrugID[1]', 'int')                                  as [DrugID]
                 , t.c.value('./TradeNmR[1]', 'varchar(1000)')                      as [TradeNmR]
                 , t.c.value('./InnR[1]', 'varchar(1000)')                          as [InnR]
                 , t.c.value('./PackNx[1]', 'bigint')                               as [PackNx]
                 , t.c.value('./DosageR[1]', 'varchar(255)')                        as [DosageR]
                 , t.c.value('./PackQn[1]', 'int')                                  as [PackQn]
                 , t.c.value('./Pack[1]', 'varchar(1000)')                          as [Pack]
                 , t.c.value('./DrugFmNmRS[1]', 'varchar(1000)')                    as [DrugFmNmRS]
                 , t.c.value('./Segment[1]', 'varchar(255)')                        as [Segment]
                 , t.c.value('./Year[1]', 'int')                                    as [Year]
                 , t.c.value('./Month[1]', 'int')                                   as [Month]
                 , t.c.value('./Series[1]', 'varchar(255)')                         as [Series]
                 , t.c.value('./TotDrugQn[1]', 'decimal(12,2)')                     as [TotDrugQn]
                 , t.c.value('./MnfPrice[1]', 'decimal(12,2)')                      as [MnfPrice]
                 , t.c.value('./PrcPrice[1]', 'decimal(12,2)')                      as [PrcPrice]
                 , t.c.value('./RtlPrice[1]', 'decimal(12,2)')                      as [RtlPrice]
                 , t.c.value('./Funds[1]', 'decimal(12,2)')                         as [Funds]
                 , t.c.value('./VendorID[1]', 'varchar(255)')                       as [VendorID]
                 , t.c.value('./Remark[1]', 'varchar(1000)')                        as [Remark]
                 , t.c.value('./SrcOrg[1]', 'varchar(255)')                         as [SrcOrg]
                 , t.c.value('./EAN[1]', 'varchar(14)')                             as [EAN]
                 , t.c.value('./MaxMnfPrice[1]', 'decimal(12,2)')                   as [MaxMnfPrice]
                 , t.c.value('./ExpiTermR[1]', 'varchar(500)')                      as [ExpiTermR]
                 , t.c.value('./ClNm[1]', 'varchar(255)')                           as [ClNm]
                 , t.c.value('./MnfNm[1]', 'varchar(1000)')                         as [MnfNm]
                 , t.c.value('./PckNm[1]', 'varchar(1000)')                         as [PckNm]
                 , t.c.value('./RegNr[1]', 'varchar(100)')                          as [RegNr]
                 , convert(datetime, t.c.value('./RegDate[1]', 'varchar(10)'), 104) as [RegDate]
                 , t.c.value('./Active[1]', 'bit')                                  as [Active]
            INTO #MEDICAMENTS_OF_ROSZDRAVNADZOR
            from @Xml.nodes('MEDICAMENT_OF_ROSZDRAVNADZOR/body/MEDICAMENTS_OF_ROSZDRAVNADZOR/MEDICAMENT_OF_ROSZDRAVNADZOR') T(c)

            update s
            set s.Active = 0
            from [PHARM].T_MEDICAMENTS_OF_ROSZDRAVNADZOR s
            where not exists
                (
                    select *
                    from #MEDICAMENTS_OF_ROSZDRAVNADZOR nmf
                    where nmf.ID = s.MedicamentsOfRoszdravnadzorID
                )

            -- Обновляем имеющиеся
            update m
            set  m.[DrugID]				= nmf.[DrugID]
                ,m.[TradeNmR]			= nmf.[TradeNmR]
                ,m.[InnR]				= nmf.[InnR]
                ,m.[PackNx]				= nmf.[PackNx]
                ,m.[DosageR]			= nmf.[DosageR]
                ,m.[PackQn]				= nmf.[PackQn]
                ,m.[Pack]				= nmf.[Pack]
                ,m.[DrugFmNmRS]			= nmf.[DrugFmNmRS]
                ,m.[Segment]			= nmf.[Segment]
                ,m.[Year]				= nmf.[Year]
                ,m.[Month]				= nmf.[Month]
                ,m.[Series]				= nmf.[Series]
                ,m.[TotDrugQn]			= nmf.[TotDrugQn]
                ,m.[MnfPrice]			= nmf.[MnfPrice]
                ,m.[PrcPrice]			= nmf.[PrcPrice]
                ,m.[RtlPrice]			= nmf.[RtlPrice]
                ,m.[Funds]				= nmf.[Funds]
                ,m.[VendorID]			= nmf.[VendorID]
                ,m.[Remark]				= nmf.[Remark]
                ,m.[SrcOrg]				= nmf.[SrcOrg]
                ,m.[EAN]				= nmf.[EAN]
                ,m.[MaxMnfPrice]		= nmf.[MaxMnfPrice]
                ,m.[ExpiTermR]			= nmf.[ExpiTermR]
                ,m.[ClNm]				= nmf.[ClNm]
                ,m.[MnfNm]				= nmf.[MnfNm]
                ,m.[PckNm]				= nmf.[PckNm]
                ,m.[RegNr]				= nmf.[RegNr]
                ,m.[RegDate]			= nmf.[RegDate]
                ,m.[Active]				= ISNULL(nmf.[Active],1)
            from [PHARM].T_MEDICAMENTS_OF_ROSZDRAVNADZOR m
                join #MEDICAMENTS_OF_ROSZDRAVNADZOR nmf on nmf.ID = m.MedicamentsOfRoszdravnadzorID
            and m.Active = 1

            -- Загружаем новые
		INSERT INTO [PHARM].[T_MEDICAMENTS_OF_ROSZDRAVNADZOR]
           ([MedicamentsOfRoszdravnadzorID]
           ,[DrugID]
           ,[TradeNmR]
           ,[InnR]
           ,[PackNx]
           ,[DosageR]
           ,[PackQn]
           ,[Pack]
           ,[DrugFmNmRS]
           ,[Segment]
           ,[Year]
           ,[Month]
           ,[Series]
           ,[TotDrugQn]
           ,[MnfPrice]
           ,[PrcPrice]
           ,[RtlPrice]
           ,[Funds]
           ,[VendorID]
           ,[Remark]
           ,[SrcOrg]
           ,[EAN]
           ,[MaxMnfPrice]
           ,[ExpiTermR]
           ,[ClNm]
           ,[MnfNm]
           ,[PckNm]
           ,[RegNr]
           ,[RegDate]
		   ,[Active])
		select
			nmf.[ID]
           ,nmf.[DrugID]
           ,nmf.[TradeNmR]
           ,nmf.[InnR]
           ,nmf.[PackNx]
           ,nmf.[DosageR]
           ,nmf.[PackQn]
           ,nmf.[Pack]
           ,nmf.[DrugFmNmRS]
           ,nmf.[Segment]
           ,nmf.[Year]
           ,nmf.[Month]
           ,nmf.[Series]
           ,nmf.[TotDrugQn]
           ,nmf.[MnfPrice]
           ,nmf.[PrcPrice]
           ,nmf.[RtlPrice]
           ,nmf.[Funds]
           ,nmf.[VendorID]
           ,nmf.[Remark]
           ,nmf.[SrcOrg]
           ,nmf.[EAN]
           ,nmf.[MaxMnfPrice]
           ,nmf.[ExpiTermR]
           ,nmf.[ClNm]
           ,nmf.[MnfNm]
           ,nmf.[PckNm]
           ,nmf.[RegNr]
           ,nmf.[RegDate]
		   ,ISNULL(nmf.[Active],1)
            from #MEDICAMENTS_OF_ROSZDRAVNADZOR nmf
            where not exists
                (
                    select *
                    from [PHARM].T_MEDICAMENTS_OF_ROSZDRAVNADZOR m
                    where m.MedicamentsOfRoszdravnadzorID = nmf.ID
                )
        END
        ELSE
            BEGIN
                -- Generate table #MEDICAMENT_OF_ROSZDRAVNADZOR
                select T.c.value('./header[1]/ID[1]', 'uniqueidentifier')               as [ID]
                     , t.c.value('./body[1]/DrugID[1]', 'int')                          as [DrugID]
                     , t.c.value('./body[1]/TradeNmR[1]', 'varchar(1000)')              as [TradeNmR]
                     , t.c.value('./body[1]/InnR[1]', 'varchar(1000)')                  as [InnR]
                     , t.c.value('./body[1]/PackNx[1]', 'bigint')                       as [PackNx]
                     , t.c.value('./body[1]/DosageR[1]', 'varchar(255)')                as [DosageR]
                     , t.c.value('./body[1]/PackQn[1]', 'int')                          as [PackQn]
                     , t.c.value('./body[1]/Pack[1]', 'varchar(1000)')                  as [Pack]
                     , t.c.value('./body[1]/DrugFmNmRS[1]', 'varchar(1000)')            as [DrugFmNmRS]
                     , t.c.value('./body[1]/Segment[1]', 'varchar(255)')                as [Segment]
                     , t.c.value('./body[1]/Year[1]', 'int')                            as [Year]
                     , t.c.value('./body[1]/Month[1]', 'int')                           as [Month]
                     , t.c.value('./body[1]/Series[1]', 'varchar(255)')                 as [Series]
                     , t.c.value('./body[1]/TotDrugQn[1]', 'decimal(12,2)')             as [TotDrugQn]
                     , t.c.value('./body[1]/MnfPrice[1]', 'decimal(12,2)')              as [MnfPrice]
                     , t.c.value('./body[1]/PrcPrice[1]', 'decimal(12,2)')              as [PrcPrice]
                     , t.c.value('./body[1]/RtlPrice[1]', 'decimal(12,2)')              as [RtlPrice]
                     , t.c.value('./body[1]/Funds[1]', 'decimal(12,2)')                 as [Funds]
                     , t.c.value('./body[1]/VendorID[1]', 'varchar(255)')               as [VendorID]
                     , t.c.value('./body[1]/Remark[1]', 'varchar(1000)')                as [Remark]
                     , t.c.value('./body[1]/SrcOrg[1]', 'varchar(255)')                 as [SrcOrg]
                     , t.c.value('./body[1]/EAN[1]', 'varchar(14)')                     as [EAN]
                     , t.c.value('./body[1]/MaxMnfPrice[1]', 'decimal(12,2)')           as [MaxMnfPrice]
                     , t.c.value('./body[1]/ExpiTermR[1]', 'varchar(500)')              as [ExpiTermR]
                     , t.c.value('./body[1]/ClNm[1]', 'varchar(255)')                   as [ClNm]
                     , t.c.value('./body[1]/MnfNm[1]', 'varchar(1000)')                 as [MnfNm]
                     , t.c.value('./body[1]/PckNm[1]', 'varchar(1000)')                 as [PckNm]
                     , t.c.value('./body[1]/RegNr[1]', 'varchar(100)')                  as [RegNr]
                     , convert(datetime, t.c.value('./RegDate[1]', 'varchar(10)'), 104) as [RegDate]
                     , t.c.value('./body[1]/Active[1]', 'bit')                          as [Active]
                INTO #MEDICAMENT_OF_ROSZDRAVNADZOR
                from @Xml.nodes('MEDICAMENT_OF_ROSZDRAVNADZOR') T(c)

                if (@OperationType = @DELETE)
                    BEGIN
                        -- Удаление
                        update m
                        set m.Active = 0
                        from [PHARM].T_MEDICAMENTS_OF_ROSZDRAVNADZOR m
                                 join #MEDICAMENT_OF_ROSZDRAVNADZOR nmf on m.MedicamentsOfRoszdravnadzorID = nmf.ID
                    END
                else
                    BEGIN
                        -- update
                        update m
                        set m.[DrugID]      = nmf.[DrugID]
                          , m.[TradeNmR]    = nmf.[TradeNmR]
                          , m.[InnR]        = nmf.[InnR]
                          , m.[PackNx]      = nmf.[PackNx]
                          , m.[DosageR]     = nmf.[DosageR]
                          , m.[PackQn]      = nmf.[PackQn]
                          , m.[Pack]        = nmf.[Pack]
                          , m.[DrugFmNmRS]  = nmf.[DrugFmNmRS]
                          , m.[Segment]     = nmf.[Segment]
                          , m.[Year]        = nmf.[Year]
                          , m.[Month]       = nmf.[Month]
                          , m.[Series]      = nmf.[Series]
                          , m.[TotDrugQn]   = nmf.[TotDrugQn]
                          , m.[MnfPrice]    = nmf.[MnfPrice]
                          , m.[PrcPrice]    = nmf.[PrcPrice]
                          , m.[RtlPrice]    = nmf.[RtlPrice]
                          , m.[Funds]       = nmf.[Funds]
                          , m.[VendorID]    = nmf.[VendorID]
                          , m.[Remark]      = nmf.[Remark]
                          , m.[SrcOrg]      = nmf.[SrcOrg]
                          , m.[EAN]         = nmf.[EAN]
                          , m.[MaxMnfPrice] = nmf.[MaxMnfPrice]
                          , m.[ExpiTermR]   = nmf.[ExpiTermR]
                          , m.[ClNm]        = nmf.[ClNm]
                          , m.[MnfNm]       = nmf.[MnfNm]
                          , m.[PckNm]       = nmf.[PckNm]
                          , m.[RegNr]       = nmf.[RegNr]
                          , m.[RegDate]     = nmf.[RegDate]
                          , m.[Active]      = ISNULL(nmf.[Active], 1)
                        from [PHARM].T_MEDICAMENTS_OF_ROSZDRAVNADZOR m
                                 join #MEDICAMENT_OF_ROSZDRAVNADZOR nmf on nmf.ID = m.MedicamentsOfRoszdravnadzorID
                        where @OperationType = @UPDATE
                          and m.Active = 1

                        -- insert
                        INSERT INTO [PHARM].[T_MEDICAMENTS_OF_ROSZDRAVNADZOR]
                        ( [MedicamentsOfRoszdravnadzorID]
                        , [DrugID]
                        , [TradeNmR]
                        , [InnR]
                        , [PackNx]
                        , [DosageR]
                        , [PackQn]
                        , [Pack]
                        , [DrugFmNmRS]
                        , [Segment]
                        , [Year]
                        , [Month]
                        , [Series]
                        , [TotDrugQn]
                        , [MnfPrice]
                        , [PrcPrice]
                        , [RtlPrice]
                        , [Funds]
                        , [VendorID]
                        , [Remark]
                        , [SrcOrg]
                        , [EAN]
                        , [MaxMnfPrice]
                        , [ExpiTermR]
                        , [ClNm]
                        , [MnfNm]
                        , [PckNm]
                        , [RegNr]
                        , [RegDate]
                        , [Active])
                        select nmf.[ID]
                             , nmf.[DrugID]
                             , nmf.[TradeNmR]
                             , nmf.[InnR]
                             , nmf.[PackNx]
                             , nmf.[DosageR]
                             , nmf.[PackQn]
                             , nmf.[Pack]
                             , nmf.[DrugFmNmRS]
                             , nmf.[Segment]
                             , nmf.[Year]
                             , nmf.[Month]
                             , nmf.[Series]
                             , nmf.[TotDrugQn]
                             , nmf.[MnfPrice]
                             , nmf.[PrcPrice]
                             , nmf.[RtlPrice]
                             , nmf.[Funds]
                             , nmf.[VendorID]
                             , nmf.[Remark]
                             , nmf.[SrcOrg]
                             , nmf.[EAN]
                             , nmf.[MaxMnfPrice]
                             , nmf.[ExpiTermR]
                             , nmf.[ClNm]
                             , nmf.[MnfNm]
                             , nmf.[PckNm]
                             , nmf.[RegNr]
                             , nmf.[RegDate]
                             , ISNULL(nmf.[Active], 1)
                        from #MEDICAMENT_OF_ROSZDRAVNADZOR nmf
                        where @OperationType = @INSERT
                          and not exists
                            (select *
                             from [PHARM].T_MEDICAMENTS_OF_ROSZDRAVNADZOR m
                             where m.MedicamentsOfRoszdravnadzorID = nmf.ID)

                    END
            END


        -- Отправка всем остальным подписанным серверам AKUZ-а и APS
        INSERT INTO [RDS].[T_RABBIT_SEND_OBJECT]
            ([RabbitSendObjectID] ,[QueueName] ,[Content] ,[MessageID] ,[Headers] ,[ContentEncoding] ,[Type] ,[UserID] ,[State] ,[CreateDateTime] ,[OwnerID])
        select NewID()--(<RabbitSendObjectID, uniqueidentifier,>
              ,s.ServerName--,<QueueName, varchar(255),>
              ,dbo.ConvertTextToBinaryZip(@Result, '')--,<Content, text,>
              ,null--,<MessageID, varchar(255),>
              ,null--,<Headers, varchar(255),>
              ,@ContentEncoding--,<ContentEncoding, varchar(255),>
              ,@Type--,<Type, varchar(255),>
              ,@User--,<UserID, varchar(255),>
              ,0--,<State, int,>)
              ,@dateTime--,<CreateDateTime, datetime,>)
              ,@OwnerID--,<OwnerID, varchar(255),>
        from [RDS].T_SYNC_SERVERS s
        where
			s.ServerName <> @QueueName
            and @OperationType <> @FTLOAD


        update bg
        set bg.MedicamentsOfRoszdravnadzor = null
        from PHARM.T_BILL_OF_GOODS bg
			join PHARM.[T_MEDICAMENTS_OF_ROSZDRAVNADZOR] mor on mor.MedicamentsOfRoszdravnadzorID = bg.MedicamentsOfRoszdravnadzor
        where mor.Active = 0

    END
END
GO