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_GetSetCmnn]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetSetCmnn]
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-07-29
-- Description: Выгрузка/загрузка справочника Международных непатентованных наименований (МНН) в(из) RDS
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetSetCmnn]
    @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 = 'C_MNN'
            -- 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_C_MNN c where c.CMnnID = @ID))
        BEGIN
            select @OperationType = @UPDATE
        END
        ELSE IF (@IsNew = 0 and not exists (select * from [PHARM].T_C_MNN c where c.CMnnID = @ID))
        BEGIN
            select @OperationType = @DELETE
            select @Result =
            (
                select @OperationType as 'header/TYPE'
                      ,@ID            as 'header/ID'
                FOR XML PATH(''),
                ROOT('C_MNN')
            )
        END

        -- Обработка операции
        IF (@OperationType = @FTLOAD)
        BEGIN
            -- Первичная загрузка
            select @Result =
            (
                select @FTLOAD							as 'header/TYPE'
                      ,(select c.CMnnID                    as 'C_MNN/ID'
                             , c.Active                    as 'C_MNN/Active'
                             , c.IsDrug                    as 'C_MNN/IsDrug'
                             , c.NameRus                   as 'C_MNN/NameRus'
                             , c.NameEng                   as 'C_MNN/NameEng'
                             , c.NameLatR                  as 'C_MNN/NameLatR'
                             , c.OtherID                   as 'C_MNN/OtherID'
                             , c.ChemicalName              as 'C_MNN/ChemicalName'
                             , c.BruttoFormula             as 'C_MNN/BruttoFormula'
                             , c.Characteristic            as 'C_MNN/Characteristic'
                             , c.Pharmacology              as 'C_MNN/Pharmacology'
                             , c.Practice                  as 'C_MNN/Practice'
                             , c.Contraindications         as 'C_MNN/Contraindications'
                             , c.Restrictions              as 'C_MNN/Restrictions'
                             , c.UseOfPregnancy            as 'C_MNN/UseOfPregnancy'
                             , c.SideEffects               as 'C_MNN/SideEffects'
                             , c.Interaction               as 'C_MNN/Interaction'
                             , c.InjectionMethod           as 'C_MNN/InjectionMethod'
                             , c.Caution                   as 'C_MNN/Caution'
                             , c.Overdose                  as 'C_MNN/Overdose'
                             , c.CASCode                   as 'C_MNN/CASCode'
                             , c.UsedIn                    as 'C_MNN/UsedIn'
                             , c.RegCode                   as 'C_MNN/RegCode'
                             , c.FedCode                   as 'C_MNN/FedCode'
                             , c.IsNarcoticAndPsychotropic as 'C_MNN/IsNarcoticAndPsychotropic'
                             , ehr.IdEHR                   as 'C_MNN/EhrRusDvl105'
                             , RRB.CurrVersion             as 'C_MNN/EhrRusDvl105Ver'
                             , ehr1.IdEHR                  as 'C_MNN/EhrRusNsv101'
                             , RRB1.CurrVersion            as 'C_MNN/EhrRusNsv101Ver'
                            from [PHARM].T_C_MNN c
								left join EHR.T_EHR_RUS_DVL105 ehr on ehr.EhrRusID = c.EhrRusDvl105
									LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = ehr.EhrRusRefBooks
								left join EHR.T_EHR_RUS_NSV101 ehr1 on ehr1.EhrRusID = c.EhrRusNsv101
									LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = ehr1.EhrRusRefBooks
                            where c.UsedIn in (1,3)-- RDS
                            for xml path(''),
                            type
                      ) as 'body/C_MNNS'
                for xml path(''),
                root('C_MNN')
            )
        END
        ELSE IF(@OperationType <> @DELETE)
        BEGIN

            -- собираем C_MNN
            SELECT @Result =
            (select @OperationType              as 'header/TYPE'
                  , @ID                         as 'header/ID'
                  , c.Active                    as 'body/Active'
                  , c.IsDrug                    as 'body/IsDrug'
                  , c.NameRus                   as 'body/NameRus'
                  , c.NameEng                   as 'body/NameEng'
                  , c.NameLatR                  as 'body/NameLatR'
                  , c.OtherID                   as 'body/OtherID'
                  , c.ChemicalName              as 'body/ChemicalName'
                  , c.BruttoFormula             as 'body/BruttoFormula'
                  , c.Characteristic            as 'body/Characteristic'
                  , c.Pharmacology              as 'body/Pharmacology'
                  , c.Practice                  as 'body/Practice'
                  , c.Contraindications         as 'body/Contraindications'
                  , c.Restrictions              as 'body/Restrictions'
                  , c.UseOfPregnancy            as 'body/UseOfPregnancy'
                  , c.SideEffects               as 'body/SideEffects'
                  , c.Interaction               as 'body/Interaction'
                  , c.InjectionMethod           as 'body/InjectionMethod'
                  , c.Caution                   as 'body/Caution'
                  , c.Overdose                  as 'body/Overdose'
                  , c.CASCode                   as 'body/CASCode'
                  , c.UsedIn                    as 'body/UsedIn'
                  , c.RegCode                   as 'body/RegCode'
                  , c.FedCode                   as 'body/FedCode'
                  , c.IsNarcoticAndPsychotropic as 'body/IsNarcoticAndPsychotropic'
                  , ehr.IdEHR                   as 'body/EhrRusDvl105'
                  , RRB.CurrVersion             as 'body/EhrRusDvl105Ver'
                  , ehr1.IdEHR                  as 'body/EhrRusNsv101'
                  , RRB1.CurrVersion            as 'body/EhrRusNsv101Ver'
                from [PHARM].T_C_MNN c
					left join EHR.T_EHR_RUS_DVL105 ehr on ehr.EhrRusID = c.EhrRusDvl105
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = ehr.EhrRusRefBooks
                    left join EHR.T_EHR_RUS_NSV101 ehr1 on ehr1.EhrRusID = c.EhrRusNsv101
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = ehr1.EhrRusRefBooks
                where c.CMnnID = @ID
                        and
                    c.UsedIn in (1,3)-- RDS
                for xml path(''),
                root('C_MNN')
            )
        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('C_MNN') T(c)


        -- Обработка операции
        if (@OperationType = @FTLOAD)
        BEGIN
            -- Generate table #C_MNNS
            select t.c.value('./ID[1]', 'uniqueidentifier')             as [ID]
                 , t.c.value('./Active[1]', 'bit')                      as [Active]
                 , t.c.value('./IsDrug[1]', 'bit')                      as IsDrug
                 , t.c.value('./NameRus[1]', 'varchar(1000)')           as [NameRus]
                 , t.c.value('./NameEng[1]', 'varchar(1000)')           as [NameEng]
                 , t.c.value('./NameLatR[1]', 'varchar(4000)')          as [NameLatR]
                 , t.c.value('./OtherID[1]', 'int')                     as [OtherID]
                 , t.c.value('./ChemicalName[1]', 'varchar(600)')       as ChemicalName
                 , t.c.value('./BruttoFormula[1]', 'varchar(100)')      as BruttoFormula
                 , t.c.value('./Characteristic[1]', 'varchar(4000)')    as Characteristic
                 , t.c.value('./Pharmacology[1]', 'varchar(max)')       as Pharmacology
                 , t.c.value('./Practice[1]', 'varchar(6000)')          as Practice
                 , t.c.value('./Contraindications[1]', 'varchar(4000)') as Contraindications
                 , t.c.value('./Restrictions[1]', 'varchar(max)')       as Restrictions
                 , t.c.value('./UseOfPregnancy[1]', 'varchar(max)')     as UseOfPregnancy
                 , t.c.value('./SideEffects[1]', 'varchar(max)')        as SideEffects
                 , t.c.value('./Interaction[1]', 'varchar(max)')        as Interaction
                 , t.c.value('./InjectionMethod[1]', 'varchar(4000)')   as InjectionMethod
                 , t.c.value('./Caution[1]', 'varchar(max)')            as Caution
                 , t.c.value('./Overdose[1]', 'varchar(4000)')          as Overdose
                 , t.c.value('./CASCode[1]', 'varchar(50)')             as CASCode
                 , t.c.value('./UsedIn[1]', 'int')                      as UsedIn
                 , t.c.value('./RegCode[1]', 'varchar(50)')             as RegCode
                 , t.c.value('./FedCode[1]', 'varchar(50)')             as FedCode
                 , t.c.value('./IsNarcoticAndPsychotropic[1]', 'bit')   as IsNarcoticAndPsychotropic
                 , t.c.value('./EhrRusDvl105[1]', 'int')                as EhrRusDvl105
                 , t.c.value('./EhrRusDvl105Ver[1]', 'varchar(8)')      as EhrRusDvl105Ver
                 , t.c.value('./EhrRusNsv101[1]', 'int')                as EhrRusNsv101
                 , t.c.value('./EhrRusNsv101Ver[1]', 'varchar(8)')      as EhrRusNsv101Ver
            INTO #C_MNNS
            from @Xml.nodes('C_MNN/body/C_MNNS/C_MNN') T(c)

            -- Первичная загрузка
            -- МНН не из справочника RDS переводим в неактивные
            update c
            set c.Active = 0
            from [PHARM].T_C_MNN c
            where not exists
                (
                    select *
                    from #C_MNNS nc
                    where nc.ID = c.CMnnID
                )
                    and
                c.UsedIn in (1,3)-- RDS

            -- Обновляем имеющиеся МНН
            update c
            set c.NameEng                   = nc.NameEng
              , c.NameRus                   = nc.NameRus
              , c.NameLatR                  = nc.NameLatR
              , c.Active                    = nc.Active
              , c.IsDrug                    = nc.IsDrug
              , c.OtherID                   = nc.OtherID
              , c.ChemicalName              = nc.ChemicalName
              , c.BruttoFormula             = nc.BruttoFormula
              , c.Characteristic            = nc.Characteristic
              , c.Pharmacology              = nc.Pharmacology
              , c.Practice                  = nc.Practice
              , c.Contraindications         = nc.Contraindications
              , c.Restrictions              = nc.Restrictions
              , c.UseOfPregnancy            = nc.UseOfPregnancy
              , c.SideEffects               = nc.SideEffects
              , c.Interaction               = nc.Interaction
              , c.InjectionMethod           = nc.InjectionMethod
              , c.Caution                   = nc.Caution
              , c.Overdose                  = nc.Overdose
              , c.CASCode                   = nc.CASCode
              , c.UsedIn                    = nc.UsedIn
              , c.RegCode                   = nc.RegCode
              , c.FedCode                   = nc.FedCode
              , c.IsNarcoticAndPsychotropic = ISNULL(nc.IsNarcoticAndPsychotropic, c.IsNarcoticAndPsychotropic)
              , c.EhrRusDvl105              = eh1.EhrRusID
              , c.EhrRusNsv101              = eh2.EhrRusID
            from [PHARM].T_C_MNN c
                join #C_MNNS nc on nc.ID = c.CMnnID
				outer apply (
					select top 1 er.EhrRusID
					from EHR.T_EHR_RUS_DVL105 er
						join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = nc.EhrRusDvl105 and RRB.CurrVersion = nc.EhrRusDvl105Ver

				) eh1
				outer apply (
					select top 1 er.EhrRusID
					from EHR.T_EHR_RUS_NSV101 er
						join EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = nc.EhrRusNsv101 and RRB1.CurrVersion = nc.EhrRusNsv101Ver
				) eh2
            where c.UsedIn in (1,3)-- RDS

            -- Загружаем новые
            INSERT INTO [PHARM].[T_C_MNN]
                       ([CMnnID] ,[NameRus] ,[NameEng] ,[Active], IsDrug, [OtherID], [UsedIn]
					   ,ChemicalName, BruttoFormula, Characteristic, Pharmacology, Practice, Contraindications,
					   Restrictions, UseOfPregnancy, SideEffects, Interaction, InjectionMethod,
                       Caution, Overdose, CASCode, RegCode, FedCode, IsNarcoticAndPsychotropic, NameLatR, EhrRusDvl105, EhrRusNsv101)
            SELECT
                       nc.ID--<CMnnID, uniqueidentifier,>
                       ,nc.NameRus--<NameRus, varchar(255),>
                       ,nc.NameEng--<NameEng, varchar(255),>
                       ,nc.Active--<Active, bit,>
                       ,nc.IsDrug--<IsDrug, bit,>
                       ,nc.OtherID--<OtherID, int,>
                       ,nc.UsedIn--<UsedIn, bit,>
					   ,nc.ChemicalName
					   ,nc.BruttoFormula
					   ,nc.Characteristic
					   ,nc.Pharmacology
					   ,nc.Practice
					   ,nc.Contraindications
					   ,nc.Restrictions
					   ,nc.UseOfPregnancy
					   ,nc.SideEffects
					   ,nc.Interaction
					   ,nc.InjectionMethod
					   ,nc.Caution
					   ,nc.Overdose
					   ,nc.CASCode
					   ,nc.RegCode
					   ,nc.FedCode
                       ,ISNULL(nc.IsNarcoticAndPsychotropic, 0)
                       ,nc.NameLatR
                       ,eh1.EhrRusID
                       ,eh2.EhrRusID
            from #C_MNNS nc
				outer apply (
					select top 1 er.EhrRusID
					from EHR.T_EHR_RUS_DVL105 er
						join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = nc.EhrRusDvl105 and RRB.CurrVersion = nc.EhrRusDvl105Ver

				) eh1
                outer apply (
					select top 1 er.EhrRusID
					from EHR.T_EHR_RUS_NSV101 er
						join EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = nc.EhrRusNsv101 and RRB1.CurrVersion = nc.EhrRusNsv101Ver

				) eh2
            where not exists
                (
                    select *
                    from [PHARM].T_C_MNN c
                    where
						c.CMnnID = nc.ID
						and c.UsedIn in (1,3)-- RDS
                )
        END
        ELSE
            BEGIN
                -- Generate table #C_MNN
                select T.c.value('./header[1]/ID[1]', 'uniqueidentifier')           as [ID]
                     , T.c.value('./body[1]/Active[1]', 'bit')                      as [Active]
                     , T.c.value('./body[1]/IsDrug[1]', 'bit')                      as IsDrug
                     , t.c.value('./body[1]/NameRus[1]', 'varchar(1000)')           as [NameRus]
                     , t.c.value('./body[1]/NameEng[1]', 'varchar(1000)')           as [NameEng]
                     , t.c.value('./body[1]/NameLatR[1]', 'varchar(4000)')          as [NameLatR]
                     , t.c.value('./body[1]/OtherID[1]', 'int')                     as [OtherID]
                     , t.c.value('./body[1]/ChemicalName[1]', 'varchar(600)')       as ChemicalName
                     , t.c.value('./body[1]/BruttoFormula[1]', 'varchar(100)')      as BruttoFormula
                     , t.c.value('./body[1]/Characteristic[1]', 'varchar(4000)')    as Characteristic
                     , t.c.value('./body[1]/Pharmacology[1]', 'varchar(max)')       as Pharmacology
                     , t.c.value('./body[1]/Practice[1]', 'varchar(6000)')          as Practice
                     , t.c.value('./body[1]/Contraindications[1]', 'varchar(4000)') as Contraindications
                     , t.c.value('./body[1]/Restrictions[1]', 'varchar(max)')       as Restrictions
                     , t.c.value('./body[1]/UseOfPregnancy[1]', 'varchar(max)')     as UseOfPregnancy
                     , t.c.value('./body[1]/SideEffects[1]', 'varchar(max)')        as SideEffects
                     , t.c.value('./body[1]/Interaction[1]', 'varchar(max)')        as Interaction
                     , t.c.value('./body[1]/InjectionMethod[1]', 'varchar(4000)')   as InjectionMethod
                     , t.c.value('./body[1]/Caution[1]', 'varchar(max)')            as Caution
                     , t.c.value('./body[1]/Overdose[1]', 'varchar(4000)')          as Overdose
                     , t.c.value('./body[1]/CASCode[1]', 'varchar(50)')             as CASCode
                     , t.c.value('./body[1]/UsedIn[1]', 'int')                      as UsedIn
                     , t.c.value('./body[1]/RegCode[1]', 'varchar(50)')             as RegCode
                     , t.c.value('./body[1]/FedCode[1]', 'varchar(50)')             as FedCode
                     , t.c.value('./body[1]/IsNarcoticAndPsychotropic[1]', 'bit')   as IsNarcoticAndPsychotropic
                     , t.c.value('./body[1]/EhrRusDvl105[1]', 'int')                as EhrRusDvl105
                     , t.c.value('./body[1]/EhrRusDvl105Ver[1]', 'varchar(8)')      as EhrRusDvl105Ver
                     , t.c.value('./body[1]/EhrRusNsv101[1]', 'int')                as EhrRusNsv101
                     , t.c.value('./body[1]/EhrRusNsv101Ver[1]', 'varchar(8)')      as EhrRusNsv101Ver
                INTO #C_MNN
                from @Xml.nodes('C_MNN') T(c)

                if (@OperationType = @DELETE)
                    BEGIN
                        -- Удаление
                        update c
                        set c.Active = 0
                        from [PHARM].[T_C_MNN] c
                                 join #C_MNN nc on nc.[ID] = c.CMnnID
                        where c.UsedIn in (1, 3)-- RDS
                    END
                else
                    BEGIN
                        -- update [PHARM].T_C_MNN
                        update c
                        set c.NameEng                   = nc.NameEng
                          , c.NameRus                   = nc.NameRus
                          , c.NameLatR                  = nc.NameLatR
                          , c.Active                    = nc.Active
                          , c.IsDrug                    = nc.IsDrug
                          , c.OtherID                   = nc.OtherID
                          , c.ChemicalName              = nc.ChemicalName
                          , c.BruttoFormula             = nc.BruttoFormula
                          , c.Characteristic            = nc.Characteristic
                          , c.Pharmacology              = nc.Pharmacology
                          , c.Practice                  = nc.Practice
                          , c.Contraindications         = nc.Contraindications
                          , c.Restrictions              = nc.Restrictions
                          , c.UseOfPregnancy            = nc.UseOfPregnancy
                          , c.SideEffects               = nc.SideEffects
                          , c.Interaction               = nc.Interaction
                          , c.InjectionMethod           = nc.InjectionMethod
                          , c.Caution                   = nc.Caution
                          , c.Overdose                  = nc.Overdose
                          , c.CASCode                   = nc.CASCode
                          , c.UsedIn                    = nc.UsedIn
                          , c.RegCode                   = nc.RegCode
                          , c.FedCode                   = nc.FedCode
                          , c.IsNarcoticAndPsychotropic = ISNULL(nc.IsNarcoticAndPsychotropic, c.IsNarcoticAndPsychotropic)
                          , c.EhrRusDvl105              = eh1.EhrRusID
                          , c.EhrRusNsv101              = eh2.EhrRusID
                        from [PHARM].T_C_MNN c
                                 join #C_MNN nc on nc.ID = c.CMnnID
                                 outer apply (select top 1 er.EhrRusID
                                              from EHR.T_EHR_RUS_DVL105 er
                                                       join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
                                              where er.IdEHR = nc.EhrRusDvl105
                                                and RRB.CurrVersion = nc.EhrRusDvl105Ver
                                              ORDER BY RRB.CURRVERSION DESC) eh1
                                 outer apply (select top 1 er.EhrRusID
                                              from EHR.T_EHR_RUS_NSV101 er
                                                       join EHR.T_EHR_RUS_REF_BOOKS RRB1
                                                            ON RRB1.EhrRusRefBooksID = er.EhrRusRefBooks
                                              where er.IdEHR = nc.EhrRusNsv101
                                                and RRB1.CurrVersion = nc.EhrRusNsv101Ver) eh2
                        where @OperationType = @UPDATE
                          and c.UsedIn in (1, 3)
                        -- RDS

                        -- insert [PHARM].T_C_MNN
                        INSERT INTO [PHARM].[T_C_MNN]
                        ([CMnnID], [NameRus], [NameEng], [Active], IsDrug, [OtherID], [UsedIn]
                            , ChemicalName, BruttoFormula, Characteristic, Pharmacology, Practice, Contraindications,
                         Restrictions, UseOfPregnancy, SideEffects, Interaction, InjectionMethod,
                         Caution, Overdose, CASCode, RegCode, FedCode, IsNarcoticAndPsychotropic, NameLatR, EhrRusDvl105, EhrRusNsv101)
                        SELECT nc.ID--<CMnnID, uniqueidentifier,>
                             , nc.NameRus--<NameRus, varchar(255),>
                             , nc.NameEng--<NameEng, varchar(255),>
                             , nc.Active--<Active, bit,>
                             , nc.IsDrug--<IsDrug, bit,>
                             , nc.OtherID--<OtherID, int,>
                             , [UsedIn]--<UsedIn, bit,>
                             , nc.ChemicalName
                             , nc.BruttoFormula
                             , nc.Characteristic
                             , nc.Pharmacology
                             , nc.Practice
                             , nc.Contraindications
                             , nc.Restrictions
                             , nc.UseOfPregnancy
                             , nc.SideEffects
                             , nc.Interaction
                             , nc.InjectionMethod
                             , nc.Caution
                             , nc.Overdose
                             , nc.CASCode
                             , nc.RegCode
                             , nc.FedCode
                             , ISNULL(nc.IsNarcoticAndPsychotropic, 0)
                             , nc.NameLatR
                             , eh1.EhrRusID
                             , eh2.EhrRusID
                        from #C_MNN nc
                                 outer apply (select top 1 er.EhrRusID
                                              from EHR.T_EHR_RUS_DVL105 er
                                                       join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
                                              where er.IdEHR = nc.EhrRusDvl105
                                                and RRB.CurrVersion = nc.EhrRusDvl105Ver) eh1
                                 outer apply (select top 1 er.EhrRusID
                                              from EHR.T_EHR_RUS_NSV101 er
                                                       join EHR.T_EHR_RUS_REF_BOOKS RRB1
                                                            ON RRB1.EhrRusRefBooksID = er.EhrRusRefBooks
                                              where er.IdEHR = nc.EhrRusNsv101
                                                and RRB1.CurrVersion = nc.EhrRusNsv101Ver) eh2
                        where @OperationType = @INSERT
                          and not exists
                            (select *
                             from [PHARM].T_C_MNN c
                             where c.CMnnID = nc.ID
                               and c.UsedIn in (1, 3)-- RDS
                            )

                    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

        declare @EntId int
        select top 1 @EntId = EntityID from VCLib.T_ENTITIES where EntName = 'C_MNN'

        IF(exists (select top 1 * from [VCLib].T_GEN_NUMBERS where  EntityID = @EntId and NumberName = 'PHARM.C_MNN'))
        BEGIN
        			-- генерируем след. номер
        			--exec [dbo].sp_GenerateNumber @EntId, 'PHARM.C_MNN'
        			update gn
        			set gn.NextNumber = (select MAX(OtherID) from [PHARM].T_C_MNN)
        			from [VCLib].T_GEN_NUMBERS gn
        			where gn.EntityID = @EntId
        					and
        				gn.NumberName = 'PHARM.C_MNN'
        		END
        ELSE
        BEGIN

        			INSERT INTO [VCLib].[T_GEN_NUMBERS]
        			           ([GenNumbersID]
        			           ,[EntityID]
        			           ,[NumberName]
        			           ,[NextNumber])
        			     VALUES
        			           (newID()
        			           ,@EntId
        			           ,'PHARM.C_MNN'
        			           ,ISNULL((select MAX(OtherID) from [PHARM].T_C_MNN), 1))
        END

    END
END
GO