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_RDS_Cmnn]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_RDS_Cmnn]
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-07-29
-- Description: Выгрузка/загрузка справочника Международных непатентованных наименований (МНН) в(из) RDS
-- =============================================
CREATE PROCEDURE [dbo].[sp_RDS_Cmnn]
	 @Xml xml = null
    ,@IsNew bit = 1
    ,@ID uniqueidentifier = null
    ,@QueueName varchar(100) = 'RDSSync'
    ,@dateTime datetime = null
    ,@User uniqueidentifier = null
	,@PATH VARCHAR(MAX) = 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)
		   ,@SQL	VARCHAR(MAX)

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

IF @PATH IS NOT NULL
	BEGIN
IF (SELECT OBJECT_ID('tempdb..#T_C_MNN')) IS NOT NULL
	DROP TABLE #T_C_MNN
CREATE TABLE #T_C_MNN
	(
        CMnnID                    UNIQUEIDENTIFIER,
        NameRus                   VARCHAR(1000),
        NameEng                   VARCHAR(1000),
        OtherID                   INT,
        Active                    BIT,
        IsDrug                    BIT,
        UsedIn                    INT,
        ChemicalName              VARCHAR(600),
        BruttoFormula             VARCHAR(100),
        Characteristic            VARCHAR(4000),
        Pharmacology              VARCHAR(MAX),
        Practice                  VARCHAR(6000),
        Contraindications         VARCHAR(4000),
        Restrictions              VARCHAR(MAX),
        UseOfPregnancy            VARCHAR(MAX),
        SideEffects               VARCHAR(MAX),
        Interaction               VARCHAR(MAX),
        InjectionMethod           VARCHAR(2000),
        Caution                   VARCHAR(MAX),
        Overdose                  VARCHAR(4000),
        CASCode                   VARCHAR(50),
        Indications               VARCHAR(8000),
        SingleDose                DECIMAL(17),
        MaxDose                   DECIMAL(17),
        SingleDoseView            INT,
        MaxDoseView               INT,
        NameLatR                  VARCHAR(4000),
        FedCode                   VARCHAR(50),
        RegCode                   VARCHAR(50),
        IsNarcoticAndPsychotropic BIT,
        EhrRusDvl105              INT,
        EhrRusDvl105Ver           VARCHAR(8),
        EhrRusNsv101              INT,
        EhrRusNsv101Ver           VARCHAR(8)
	)
SET @SQL = 'INSERT INTO tempdb..#T_C_MNN
			SELECT
				 [CMnnID]
				,[NameRus]
				,[NameEng]
				,[OtherID]
				,[Active]
				,[IsDrug]
				,[UsedIn]
				,[ChemicalName]
				,[BruttoFormula]
				,[Characteristic]
				,[Pharmacology]
				,[Practice]
				,[Contraindications]
				,[Restrictions]
				,[UseOfPregnancy]
				,[SideEffects]
				,[Interaction]
				,[InjectionMethod]
				,[Caution]
				,[Overdose]
				,[CASCode]
				,[Indications]
				,[SingleDose]
				,[MaxDose]
				,[SingleDoseView]
				,[MaxDoseView]
				,[NameLatR]
				,[FedCode]
				,[RegCode]
                ,[IsNarcoticAndPsychotropic]
				,ehr.IdEHR
				,RRB.CurrVersion
				,ehr1.IdEHR
				,RRB1.CurrVersion
				FROM ' + @PATH + '.[PHARM].T_C_MNN c
				left join ' + @PATH + '.EHR.T_EHR_RUS_DVL105 ehr on ehr.EhrRusID = c.EhrRusDvl105
					LEFT JOIN ' + @PATH + '.EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = ehr.EhrRusRefBooks
				left join ' + @PATH + '.EHR.T_EHR_RUS_NSV101 ehr1 on ehr1.EhrRusID = c.EhrRusNsv101
					LEFT JOIN ' + @PATH + '.EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = ehr1.EhrRusRefBooks'
EXECUTE (@SQL)

-- Первичная загрузка
	-- МНН не из справочника RDS переводим в неактивные
	UPDATE c
		SET c.Active = 0
	FROM [PHARM].T_C_MNN c
	WHERE NOT EXISTS
	    (
	        SELECT *
				FROM #T_C_MNN nc
	        WHERE nc.CMnnID = c.CMnnID
	    )
	        and c.UsedIn in (1,3)

	-- Обновляем имеющиеся МНН
	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 #T_C_MNN nc on nc.CMnnID = c.CMnnID
		outer apply (
			select top 1 eh.EhrRusID
			from EHR.T_EHR_RUS_DVL105 eh
				join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
			where eh.IdEHR = nc.EhrRusDvl105 AND RRB.CurrVersion = nc.EhrRusDvl105Ver
		) eh1
        outer apply (
			select top 1 eh.EhrRusID
			from EHR.T_EHR_RUS_NSV101 eh
					join EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = eh.EhrRusRefBooks
			where eh.IdEHR = nc.EhrRusNsv101 AND RRB1.CurrVersion = nc.EhrRusNsv101Ver

		) eh2
	--where c.UsedIn in (1,3)

	-- Загружаем новые
	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.CMnnID--<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
		,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 #T_C_MNN nc
	outer apply (
		select top 1 eh.EhrRusID
		from EHR.T_EHR_RUS_DVL105 eh
			join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
		where eh.IdEHR = nc.EhrRusDvl105 AND RRB.CurrVersion = nc.EhrRusDvl105Ver
	) eh1
    outer apply (
		select top 1 eh.EhrRusID
		from EHR.T_EHR_RUS_NSV101 eh
			join EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = eh.EhrRusRefBooks
		where eh.IdEHR = nc.EhrRusNsv101 AND RRB1.CurrVersion = nc.EhrRusNsv101Ver

	) eh2
	WHERE NOT EXISTS
	    (
	        SELECT *
				FROM [PHARM].T_C_MNN c
	        WHERE c.CMnnID = nc.CMnnID
	            --    and
	            --c.UsedIn in (1,3)
	    )

	END
	ELSE
	BEGIN

    IF (@Xml is null)
    BEGIN
        -- Инициализация
        select @ContentEncoding = 'utf-8'
              ,@Type = 'C_MNN'
              ,@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/EhrRusDvl105Ver'
                            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/EhrRusDvl105Ver'
                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)
                for xml path(''),
                root('C_MNN')
            )
        END

        -- Отправка
        INSERT INTO [AKUZ].[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(@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),>
    END
    ELSE
    IF (@Xml is not null)
    BEGIN

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

        -- 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)
        where @OperationType = @FTLOAD

        -- 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)
        where @OperationType <> @FTLOAD

        -- Обработка операции
        if (@OperationType = @FTLOAD)
        BEGIN
            -- Первичная загрузка
            -- МНН не из справочника 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)

            -- Обновляем имеющиеся МНН
            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 eh.EhrRusID
					from EHR.T_EHR_RUS_DVL105 eh
						join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.IdEHR = nc.EhrRusDvl105 AND RRB.CurrVersion = nc.EhrRusDvl105Ver
				) eh1
				outer apply (
					select top 1 eh.EhrRusID
					from EHR.T_EHR_RUS_NSV101 eh
						join EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.IdEHR = nc.EhrRusNsv101 AND RRB1.CurrVersion = nc.EhrRusNsv101Ver
				) eh2
            --where c.UsedIn in (1,3)

            -- Загружаем новые
            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
					   ,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
					   ,ehr1.EhrRusID
					   ,ehr2.EhrRusID
            from #C_MNNS nc
				outer apply (
					select top 1 eh.EhrRusID
					from EHR.T_EHR_RUS_DVL105 eh
						join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.IdEHR = nc.EhrRusDvl105 AND RRB.CurrVersion = nc.EhrRusDvl105Ver
				) ehr1
				outer apply (
					select top 1 eh.EhrRusID
					from EHR.T_EHR_RUS_NSV101 eh
						join EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.IdEHR = nc.EhrRusNsv101 AND RRB1.CurrVersion = nc.EhrRusNsv101Ver
				) ehr2
            where not exists
                (
                    select *
                    from [PHARM].T_C_MNN c
                    where c.CMnnID = nc.ID
                        --    and
                        --c.UsedIn in (1,3)
                )
        END
        else 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)
        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 = ehr1.EhrRusID
			   ,c.EhrRusNsv101 = ehr2.EhrRusID
            from [PHARM].T_C_MNN c
                join #C_MNN nc on nc.ID = c.CMnnID
				outer apply (
					select top 1 eh.EhrRusID
					from EHR.T_EHR_RUS_DVL105 eh
						join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.IdEHR = nc.EhrRusDvl105 AND RRB.CurrVersion = nc.EhrRusDvl105Ver
				) ehr1
				outer apply (
					select top 1 eh.EhrRusID
					from EHR.T_EHR_RUS_NSV101 eh
						join EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.IdEHR = nc.EhrRusNsv101 AND RRB1.CurrVersion = nc.EhrRusNsv101Ver
				) ehr2
            where @OperationType = @UPDATE
                --    and
                --c.UsedIn in (1,3)

            -- 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
					   ,ehr1.EhrRusID
					   ,ehr2.EhrRusID
            from #C_MNN nc
			outer apply (
				select top 1 eh.EhrRusID
				from EHR.T_EHR_RUS_DVL105 eh
					join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
				where eh.IdEHR = nc.EhrRusDvl105 AND RRB.CurrVersion = nc.EhrRusDvl105Ver
			) ehr1
			outer apply (
				select top 1 eh.EhrRusID
				from EHR.T_EHR_RUS_NSV101 eh
					join EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = eh.EhrRusRefBooks
				where eh.IdEHR = nc.EhrRusNsv101 AND RRB1.CurrVersion = nc.EhrRusNsv101Ver
			) ehr2
            where not exists
                (
                    select *
                    from [PHARM].T_C_MNN c
                    where c.CMnnID = nc.ID
                        --    and
                        --c.UsedIn in (1,3)
                )
                    and
                @OperationType = @INSERT
        END

        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

        -- Drop tables
        DROP TABLE #C_MNN
        DROP TABLE #C_MNNS

    END
END
END
GO