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

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[APS].[sp_RDS_Cmnn]') AND type in (N'P', N'PC'))
DROP PROCEDURE [APS].[sp_RDS_Cmnn]
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-07-29
-- Description: Выгрузка/загрузка справочника Международных непатентованных наименований (МНН) в(из) RDS
-- =============================================
CREATE PROCEDURE [APS].[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)
		,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]
				,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
		c.UsedIn in (1,3)
		and NOT EXISTS
	    (
	        SELECT *
				FROM #T_C_MNN nc
	        WHERE nc.CMnnID = c.CMnnID
	    )


	-- Обновляем имеющиеся МНН
	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.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]
		,[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
		,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'
								  ,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'
					  ,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 [APS].[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)


        -- Обработка операции
        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('./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
				c.UsedIn in (1,3)
				and not exists
                (
                    select *
                    from #C_MNNS nc
                    where nc.ID = c.CMnnID
                )



            -- Обновляем имеющиеся МНН
            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.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,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
                       ,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
            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]/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)
                    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.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, 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
                             , 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 @OperationType = @INSERT
                          and not exists
                            (select *
                             from [PHARM].T_C_MNN c
                             where c.CMnnID = nc.ID
                               and c.UsedIn in (1, 3))


                    END
            END


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

        IF(exists (select * 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
END
GO