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_Ctrn]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_RDS_Ctrn]
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-12-22
-- Description: Выгрузка/загрузка справочника Торговое наименование препарата (ТРН) в(из) RDS
-- =============================================
CREATE PROCEDURE [dbo].[sp_RDS_Ctrn]
     @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(255)
           ,@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_TRN')) IS NOT NULL
	DROP TABLE #T_C_TRN
CREATE TABLE #T_C_TRN
	(
		 CTrnID					UNIQUEIDENTIFIER
		,OtherID				INT
		,NameRus				VARCHAR
		,NameEng				VARCHAR
		,Active					BIT
		,IsDrug					BIT
		,Characteristic			VARCHAR
		,PharmacologyAction		VARCHAR
		,Practice				VARCHAR
		,Contraindications		VARCHAR
		,Restrictions			VARCHAR
		,UseOfPregnancy			VARCHAR
		,SideEffects			VARCHAR
		,Interaction			VARCHAR
		,Overdose				VARCHAR
		,InjectionMethod		VARCHAR
		,Caution				VARCHAR
		,Composition			VARCHAR
		,PharmDescription		VARCHAR
		,Pharmacodynamics		VARCHAR
		,Indications			VARCHAR
		,ExpirationDate			VARCHAR
		,MNNName				VARCHAR
		,Manufacturer			VARCHAR
		,CMnn					UNIQUEIDENTIFIER
		,ProductType			INT
		,NameLatR				VARCHAR
		,FedCode				VARCHAR
		,RegCode				VARCHAR
		,EhrRusP08TSH954N		VARCHAR(52)
		,EhrRusP08TSH954NVer	VARCHAR(8)
		,EhrRus575VSF88OG		VARCHAR(52)
		,EhrRus575VSF88OGVer	VARCHAR(8)
		,Atc					int
		,AtcVer					VARCHAR(8)
	)
SET @SQL = 'INSERT INTO tempdb..#T_C_TRN
			SELECT 
				 [CTrnID]
				,[OtherID]
				,[NameRus]
				,[NameEng]
				,[Active]
				,[IsDrug]
				,[Characteristic]
				,[PharmacologyAction]
				,[Practice]
				,[Contraindications]
				,[Restrictions]
				,[UseOfPregnancy]
				,[SideEffects]
				,[Interaction]
				,[Overdose]
				,[InjectionMethod]
				,[Caution]
				,[Composition]
				,[PharmDescription]
				,[Pharmacodynamics]
				,[Indications]
				,[ExpirationDate]
				,[MNNName]
				,[Manufacturer]
				,[CMnn]
				,[ProductType]
				,[NameLatR]
				,[FedCode]
				,[RegCode]
				,eh1.[CodeEHR]			AS [EhrRusP08TSH954N]
				,eh1.[CurrVersion]		AS [EhrRusP08TSH954NVer]
				,eh2.[CodeEHR]			AS [EhrRus575VSF88OG]
				,eh2.[CurrVersion]		AS [EhrRus575VSF88OGVer]
				,Atc.[IdEHR]			AS [Atc]
				,Atc.[CurrVersion]		AS [AtcVer]
			FROM ' + @PATH + '[PHARM].T_C_TRN nc
			OUTER APPLY (
				SELECT TOP 1 EH.CodeEHR, CurrVersion 
					FROM ' + @PATH + '.EHR.T_EHR_RUS_P08TSH954N EH
						LEFT JOIN ' + @PATH + '.EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
				WHERE NC.EHRRUSP08TSH954N = EH.EHRRUSID
				ORDER BY RRB.CURRVERSION DESC
			)EH1
			OUTER APPLY (
				SELECT TOP 1 EH.CodeEHR, CurrVersion 
					FROM ' + @PATH + '.EHR.T_EHR_RUS_575VSF88OG EH
						LEFT JOIN ' + @PATH + '.EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
				WHERE NC.EHRRUS575VSF88OG = EH.EHRRUSID
				ORDER BY RRB.CURRVERSION DESC
			)EH2
			OUTER APPLY (
				SELECT TOP 1 EH.IdEHR, CurrVersion 
					FROM ' + @PATH + '.EHR.T_EHR_RUS_M00002 EH
						LEFT JOIN ' + @PATH + '.EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
				WHERE NC.Atc = EH.EHRRUSID
			)Atc'
EXECUTE (@SQL)

	-- Первичная загрузка
	
	--DELETE C
	--	FROM [PHARM].T_C_TRN c
	--WHERE NOT EXISTS
	--    (
	--        SELECT *
	--			FROM #T_C_TRN nc
	--        WHERE nc.CTrnID = c.CTrnID
	--    )
	
	-- МНН не из справочника RDS переводим в неактивные
	UPDATE c
	SET c.Active = 0
		FROM [PHARM].T_C_TRN c
	WHERE NOT EXISTS
	    (
	        SELECT *
				FROM #T_C_TRN nc
	        WHERE nc.CTrnID = c.CTrnID
	    )
	
	-- Обновляем имеющиеся TRN
	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.Characteristic = nc.Characteristic
	   ,c.PharmacologyAction = nc.PharmacologyAction
	   ,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.Overdose = nc.Overdose
	   ,c.InjectionMethod = nc.InjectionMethod
	   ,c.Caution = nc.Caution
	   ,c.Composition = nc.Composition
	   ,c.PharmDescription = nc.PharmDescription
	   ,c.Pharmacodynamics = nc.Pharmacodynamics
	   ,c.Indications = nc.Indications
	   ,c.ExpirationDate = nc.ExpirationDate
	   ,c.MNNName = nc.MNNName
	   ,c.Manufacturer = nc.Manufacturer
	   ,c.CMnn = nc.CMnn
	   ,c.Atc = atc.EhrRusID
	   ,c.ProductType = nc.ProductType
	   ,c.RegCode = nc.RegCode
	   ,c.FedCode = nc.FedCode
	   ,c.EhrRusP08TSH954N = eh1.EhrRusID
	   ,c.EhrRus575VSF88OG = eh2.EhrRusID
	from [PHARM].T_C_TRN c
	    join #T_C_TRN nc on nc.CTrnID = c.CTrnID
		outer apply (
			select top 1 eh.EhrRusID 
				from EHR.T_EHR_RUS_P08TSH954N eh
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
			where eh.CodeEHR = nc.EhrRusP08TSH954N and RRB.CURRVERSION = nc.EhrRusP08TSH954NVer
		)eh1
		outer apply (
			select top 1 eh.EhrRusID 
				from EHR.T_EHR_RUS_575VSF88OG eh
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
			where eh.CodeEHR = nc.EhrRus575VSF88OG and RRB.CURRVERSION = nc.EhrRus575VSF88OGVer
		)eh2
		outer apply (
			select top 1 eh.EhrRusID 
				from EHR.T_EHR_RUS_M00002 eh
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
			where eh.IdEHR = nc.Atc and rrb.CurrVersion = nc.AtcVer
		) atc
	
	-- Загружаем новые
	INSERT INTO [PHARM].[T_C_TRN]
			(
				 [CTrnID] 
				,[NameRus] 
				,[NameEng] 
				,[Active]
				,[IsDrug]
				,[OtherID]
				,[Characteristic]
				,[PharmacologyAction]
				,[Practice]
				,[Contraindications]
				,[Restrictions]
				,[UseOfPregnancy]
				,[SideEffects]
				,[Interaction]
				,[Overdose]
				,[InjectionMethod]
				,[Caution]
				,[Composition]
				,[PharmDescription]
				,[Pharmacodynamics]
				,[Indications]
				,[ExpirationDate]
				,[MNNName]
				,[Manufacturer]
				,[CMnn]
				,[Atc]
				,[ProductType]
				,[RegCode]
				,[FedCode]
				,[EhrRusP08TSH954N]
				,[EhrRus575VSF88OG]
				,[NameLatR]
			)
	
	SELECT
				 nc.[CTrnID]--<CMnnID, uniqueidentifier,>
				,nc.NameRus--<NameRus, varchar(4000),>
				,nc.NameEng--<NameEng, varchar(4000),>
				,nc.Active--<Active, bit,>
				,nc.IsDrug--<IsDrug, bit,>
				,nc.OtherID--<OtherID, int,>
				,nc.Characteristic
				,nc.PharmacologyAction
				,nc.Practice
				,nc.Contraindications
				,nc.Restrictions
				,nc.UseOfPregnancy
				,nc.SideEffects
				,nc.Interaction
				,nc.Overdose
				,nc.InjectionMethod
				,nc.Caution
				,nc.Composition
				,nc.PharmDescription
				,nc.Pharmacodynamics
				,nc.Indications
				,nc.ExpirationDate
				,nc.MNNName
				,nc.Manufacturer
				,nc.CMnn
				,atc.EhrRusID
				,nc.ProductType
				,RegCode
				,FedCode
				,eh1.EhrRusID
				,eh2.EhrRusID
				,nc.NameLatR
	from #T_C_TRN nc
		outer apply (
			select top 1 eh.EhrRusID 
			from EHR.T_EHR_RUS_P08TSH954N eh
				LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
			where eh.CodeEHR = nc.EhrRusP08TSH954N and RRB.CURRVERSION = nc.EhrRusP08TSH954NVer
		)eh1
		outer apply (
			select top 1 eh.EhrRusID 
			from EHR.T_EHR_RUS_575VSF88OG eh
				LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
			where eh.CodeEHR = nc.EhrRus575VSF88OG and RRB.CURRVERSION = nc.EhrRus575VSF88OGVer
		)eh2
		outer apply (
			select top 1 eh.EhrRusID 
				from EHR.T_EHR_RUS_M00002 eh
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
			where eh.IdEHR = nc.Atc and rrb.CurrVersion = nc.AtcVer
		) atc
	where not exists
	    (
	        select *
	        from [PHARM].T_C_TRN c
	        where c.CTrnID = nc.[CTrnID]
	    )

	END
	ELSE
	BEGIN

    IF (@Xml is null)
    BEGIN
        -- Инициализация
        select @ContentEncoding = 'utf-8'
              ,@Type = 'C_TRN'
              ,@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_TRN c where c.CTrnID = @ID))
        BEGIN
            select @OperationType = @UPDATE
        END
        ELSE IF (@IsNew = 0 and not exists (select * from [PHARM].T_C_TRN c where c.CTrnID = @ID))
        BEGIN
            select @OperationType = @DELETE
            select @Result = 
            (
                select @OperationType as 'header/TYPE'
                      ,@ID            as 'header/ID'
                FOR XML PATH(''),
                ROOT('C_TRN')
            ) 
        END

        -- Обработка операции
        IF (@OperationType = @FTLOAD)
        BEGIN
            -- Первичная загрузка
            select @Result = 
            (
                select @FTLOAD							as 'header/TYPE'
                      ,(
                            select c.CTrnID				as 'C_TRN/ID'
                                  ,c.Active				as 'C_TRN/Active'
                                  ,c.NameRus			as 'C_TRN/NameRus'
                                  ,c.NameEng			as 'C_TRN/NameEng'
                                  ,c.NameLatR           as 'C_TRN/NameLatR'
                                  ,c.OtherID			as 'C_TRN/OtherID'
                                  ,c.IsDrug				as 'C_TRN/IsDrug'
								  ,c.Characteristic		as 'C_TRN/Characteristic'
								  ,c.PharmacologyAction as 'C_TRN/PharmacologyAction'
								  ,c.Practice			as 'C_TRN/Practice'
								  ,c.Contraindications  as 'C_TRN/Contraindications'
								  ,c.Restrictions		as 'C_TRN/Restrictions'
								  ,c.UseOfPregnancy		as 'C_TRN/UseOfPregnancy'
								  ,c.SideEffects		as 'C_TRN/SideEffects'
								  ,c.Interaction		as 'C_TRN/Interaction'
								  ,c.Overdose			as 'C_TRN/Overdose'
								  ,c.InjectionMethod	as 'C_TRN/InjectionMethod'
								  ,c.Caution			as 'C_TRN/Caution'
								  ,c.Composition		as 'C_TRN/Composition'
								  ,c.PharmDescription	as 'C_TRN/PharmDescription'
								  ,c.Pharmacodynamics	as 'C_TRN/Pharmacodynamics'
								  ,c.Indications		as 'C_TRN/Indications'
								  ,c.ExpirationDate		as 'C_TRN/ExpirationDate'
								  ,c.MNNName			as 'C_TRN/MNNName'
								  ,c.Manufacturer		as 'C_TRN/Manufacturer'
								  ,c.CMnn				as 'C_TRN/CMnn'
								  ,c.ProductType		as 'C_TRN/ProductType'
								  ,RegCode				as 'C_TRN/RegCode'
								  ,FedCode				as 'C_TRN/FedCode'
								  ,ehr1.CodeEHR			as 'C_TRN/EhrRusP08TSH954N'
								  ,RRB.CurrVersion		as 'C_TRN/EhrRusP08TSH954NVer'
								  ,ehr2.CodeEHR			as 'C_TRN/EhrRus575VSF88OG'
								  ,RRB1.CurrVersion		as 'C_TRN/EhrRus575VSF88OGVer'
								  ,atc.IdEHR			as 'C_TRN/Atc'
								  ,RRBAtc.CurrVersion	as 'C_TRN/AtcVer'
                            from [PHARM].T_C_TRN c
								left join EHR.T_EHR_RUS_P08TSH954N ehr1 on ehr1.EhrRusID = c.EhrRusP08TSH954N
								LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = ehr1.EhrRusRefBooks
								left join EHR.T_EHR_RUS_575VSF88OG ehr2 on ehr2.EhrRusID = c.EhrRus575VSF88OG
								LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = ehr2.EhrRusRefBooks
								left join EHR.T_EHR_RUS_M00002 atc on atc.EhrRusID = c.Atc
								LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRBAtc ON RRBAtc.EhrRusRefBooksID = atc.EhrRusRefBooks
                            for xml path(''),
                            type
                      ) as 'body/C_TRNS'
                for xml path(''),
                root('C_TRN')
            )
        END
        ELSE IF(@OperationType <> @DELETE)
        BEGIN

            -- собираем C_TRN
            SELECT @Result = 
            ( 
                select @OperationType		as 'header/TYPE'
                      ,@ID					as 'header/ID'

                      ,c.Active				as 'body/Active'
                      ,c.NameRus			as 'body/NameRus'
                      ,c.NameEng			as 'body/NameEng'
                      ,c.NameLatR     as 'body/NameLatR'
                      ,c.OtherID			as 'body/OtherID'
                      ,c.IsDrug				as 'body/IsDrug'
					  ,c.Characteristic		as 'body/Characteristic'
					  ,c.PharmacologyAction as 'body/PharmacologyAction'
					  ,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.Overdose			as 'body/Overdose'
					  ,c.InjectionMethod	as 'body/InjectionMethod'
					  ,c.Caution			as 'body/Caution'
					  ,c.Composition		as 'body/Composition'
					  ,c.PharmDescription	as 'body/PharmDescription'
					  ,c.Pharmacodynamics	as 'body/Pharmacodynamics'
					  ,c.Indications		as 'body/Indications'
					  ,c.ExpirationDate		as 'body/ExpirationDate'
					  ,c.MNNName			as 'body/MNNName'
					  ,c.Manufacturer		as 'body/Manufacturer'
					  ,c.CMnn				as 'body/CMnn'
					  ,c.Atc				as 'body/Atc'
					  ,c.ProductType		as 'body/ProductType'
					  ,RegCode				as 'body/RegCode'
					  ,FedCode				as 'body/FedCode'
					  ,ehr1.CodeEHR			as 'body/EhrRusP08TSH954N'
					  ,RRB.CurrVersion		as 'body/EhrRusP08TSH954NVer'
					  ,ehr2.CodeEHR			as 'body/EhrRus575VSF88OG'
					  ,RRB1.CurrVersion		as 'body/EhrRus575VSF88OGVer'
					  ,atc.IdEHR				as 'body/Atc'
					  ,RRBAtc.CurrVersion		as 'body/AtcVer'
                from [PHARM].T_C_TRN c
					left join EHR.T_EHR_RUS_P08TSH954N ehr1 on ehr1.EhrRusID = c.EhrRusP08TSH954N
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = ehr1.EhrRusRefBooks
					left join EHR.T_EHR_RUS_575VSF88OG ehr2 on ehr2.EhrRusID = c.EhrRus575VSF88OG
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = ehr2.EhrRusRefBooks
					left join EHR.T_EHR_RUS_M00002 atc on atc.EhrRusID = c.Atc
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRBAtc ON RRBAtc.EhrRusRefBooksID = atc.EhrRusRefBooks
                where c.CTrnID = @ID
                for xml path(''),
                root('C_TRN')
            ) 
        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(36)')
        from @Xml.nodes('C_TRN') T(c)

        -- Generate table #C_TRNS
        select t.c.value('./ID[1]', 'varchar(255)')						as [ID]
              ,t.c.value('./Active[1]', 'bit')							as [Active]
              ,t.c.value('./NameRus[1]', 'varchar(4000)')               as [NameRus]
              ,t.c.value('./NameEng[1]', 'varchar(4000)')               as [NameEng]
              ,t.c.value('./NameLatR[1]', 'varchar(4000)')              as [NameLatR]
              ,t.c.value('./OtherID[1]', 'int')                         as [OtherID]
              ,t.c.value('./IsDrug[1]', 'bit')							as [IsDrug]
			  ,t.c.value('./Characteristic[1]', 'varchar(4000)')		as Characteristic
			  ,t.c.value('./PharmacologyAction[1]', 'varchar(4000)')	as PharmacologyAction
			  ,t.c.value('./Practice[1]', 'varchar(max)')				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('./Overdose[1]', 'varchar(1000)')				as Overdose
			  ,t.c.value('./InjectionMethod[1]', 'varchar(255)')		as InjectionMethod
			  ,t.c.value('./Caution[1]', 'varchar(max)')				as Caution
			  ,t.c.value('./Composition[1]', 'varchar(255)')			as Composition
			  ,t.c.value('./PharmDescription[1]', 'varchar(max)')		as PharmDescription
			  ,t.c.value('./Pharmacodynamics[1]', 'varchar(max)')		as Pharmacodynamics
			  ,t.c.value('./Indications[1]', 'varchar(8000)')			as Indications
			  ,t.c.value('./ExpirationDate[1]', 'varchar(300)')			as ExpirationDate
			  ,t.c.value('./MNNName[1]', 'varchar(8000)')				as MNNName
			  ,t.c.value('./Manufacturer[1]', 'varchar(3000)')			as Manufacturer
			  ,t.c.value('./CMnn[1]', 'uniqueidentifier')				as CMnn
			  ,t.c.value('./Atc[1]', 'int')								as Atc
			  ,t.c.value('./AtcVer[1]', 'varchar(52)')					as AtcVer
			  ,t.c.value('./ProductType[1]', 'int')						as ProductType
			  ,t.c.value('./RegCode[1]', 'varchar(50)')					as RegCode
			  ,t.c.value('./FedCode[1]', 'varchar(50)')					as FedCode
			  ,t.c.value('./EhrRusP08TSH954N[1]', 'varchar(52)')		as EhrRusP08TSH954N
			  ,t.c.value('./EhrRusP08TSH954NVer[1]', 'varchar(52)')		as EhrRusP08TSH954NVer
			  ,t.c.value('./EhrRus575VSF88OG[1]', 'varchar(52)')		as EhrRus575VSF88OG
			  ,t.c.value('./EhrRus575VSF88OGVer[1]', 'varchar(52)')		as EhrRus575VSF88OGVer
            INTO #C_TRNS
        from @Xml.nodes('C_TRN/body/C_TRNS/C_TRN') T(c)
        where @OperationType = @FTLOAD

        -- Generate table #C_TRN
        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]/NameRus[1]', 'varchar(4000)')               as [NameRus]
              ,t.c.value('./body[1]/NameEng[1]', 'varchar(4000)')               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]/IsDrug[1]', 'bit')							as [IsDrug]
			  ,t.c.value('./body[1]/Characteristic[1]', 'varchar(4000)')		as Characteristic
			  ,t.c.value('./body[1]/PharmacologyAction[1]', 'varchar(4000)')	as PharmacologyAction
			  ,t.c.value('./body[1]/Practice[1]', 'varchar(max)')				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]/Overdose[1]', 'varchar(1000)')				as Overdose
			  ,t.c.value('./body[1]/InjectionMethod[1]', 'varchar(255)')		as InjectionMethod
			  ,t.c.value('./body[1]/Caution[1]', 'varchar(max)')				as Caution
			  ,t.c.value('./body[1]/Composition[1]', 'varchar(255)')			as Composition
			  ,t.c.value('./body[1]/PharmDescription[1]', 'varchar(max)')		as PharmDescription
			  ,t.c.value('./body[1]/Pharmacodynamics[1]', 'varchar(max)')		as Pharmacodynamics
			  ,t.c.value('./body[1]/Indications[1]', 'varchar(8000)')			as Indications
			  ,t.c.value('./body[1]/ExpirationDate[1]', 'varchar(300)')			as ExpirationDate
			  ,t.c.value('./body[1]/MNNName[1]', 'varchar(8000)')				as MNNName
			  ,t.c.value('./body[1]/Manufacturer[1]', 'varchar(3000)')			as Manufacturer
			  ,t.c.value('./body[1]/CMnn[1]', 'uniqueidentifier')				as CMnn
			  ,t.c.value('./body[1]/Atc[1]', 'int')								as Atc
			  ,t.c.value('./body[1]/AtcVer[1]', 'varchar(52)')					as AtcVer
			  ,t.c.value('./body[1]/ProductType[1]', 'int')						as ProductType
			  ,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]/EhrRusP08TSH954N[1]', 'varchar(52)')		as EhrRusP08TSH954N
			  ,t.c.value('./body[1]/EhrRusP08TSH954NVer[1]', 'varchar(52)')		as EhrRusP08TSH954NVer
			  ,t.c.value('./body[1]/EhrRus575VSF88OG[1]', 'varchar(52)')		as EhrRus575VSF88OG
			  ,t.c.value('./body[1]/EhrRus575VSF88OGVer[1]', 'varchar(52)')		as EhrRus575VSF88OGVer
            INTO #C_TRN
        from @Xml.nodes('C_TRN') T(c)
        where @OperationType <> @FTLOAD

        -- Обработка операции
        if (@OperationType = @FTLOAD)
        BEGIN
            -- Первичная загрузка
            -- МНН не из справочника RDS переводим в неактивные
            update c
            set c.Active = 0
            from [PHARM].T_C_TRN c
            where not exists
                (
                    select *
                    from #C_TRNS nc
                    where nc.ID = c.CTrnID
                )

            -- Обновляем имеющиеся TRN
            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.Characteristic = nc.Characteristic
			   ,c.PharmacologyAction = nc.PharmacologyAction
			   ,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.Overdose = nc.Overdose
			   ,c.InjectionMethod = nc.InjectionMethod
			   ,c.Caution = nc.Caution
			   ,c.Composition = nc.Composition
			   ,c.PharmDescription = nc.PharmDescription
			   ,c.Pharmacodynamics = nc.Pharmacodynamics
			   ,c.Indications = nc.Indications
			   ,c.ExpirationDate = nc.ExpirationDate
			   ,c.MNNName = nc.MNNName
			   ,c.Manufacturer = nc.Manufacturer
			   ,c.CMnn = nc.CMnn
			   ,c.Atc = atc.EhrRusID
			   ,c.ProductType = nc.ProductType
			   ,c.RegCode = nc.RegCode
			   ,c.FedCode = nc.FedCode
			   ,c.EhrRusP08TSH954N = eh1.EhrRusID
			   ,c.EhrRus575VSF88OG = eh2.EhrRusID
            from [PHARM].T_C_TRN c
                join #C_TRNS nc on nc.ID = c.CTrnID
				outer apply (
					select top 1 eh.EhrRusID 
					from EHR.T_EHR_RUS_P08TSH954N eh 
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.CodeEHR = nc.EhrRusP08TSH954N and RRB.CURRVERSION = nc.EhrRusP08TSH954NVer
				)eh1
				outer apply (
					select top 1 eh.EhrRusID 
					from EHR.T_EHR_RUS_575VSF88OG eh 
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.CodeEHR = nc.EhrRus575VSF88OG and RRB.CURRVERSION = nc.EhrRus575VSF88OGVer
				)eh2
				outer apply (
					select top 1 eh.EhrRusID 
						from EHR.T_EHR_RUS_M00002 eh
							LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.IdEHR = nc.Atc and rrb.CurrVersion = nc.AtcVer
				) atc

            -- Загружаем новые
            INSERT INTO [PHARM].[T_C_TRN]
                       ([CTrnID] ,[NameRus] ,[NameEng] ,[Active], [IsDrug], [OtherID]
					   ,Characteristic,PharmacologyAction,Practice,Contraindications,Restrictions,
                       UseOfPregnancy,SideEffects,Interaction,Overdose,InjectionMethod,Caution,Composition,PharmDescription,Pharmacodynamics,Indications,
                       ExpirationDate,MNNName,Manufacturer,CMnn,Atc,ProductType,RegCode,FedCode,EhrRusP08TSH954N,EhrRus575VSF88OG,NameLatR)
            SELECT
                       nc.ID--<CMnnID, uniqueidentifier,>
                       ,nc.NameRus--<NameRus, varchar(4000),>
                       ,nc.NameEng--<NameEng, varchar(4000),>
                       ,nc.Active--<Active, bit,>
                       ,nc.IsDrug--<IsDrug, bit,>
                       ,nc.OtherID--<OtherID, int,>
					   ,nc.Characteristic
					   ,nc.PharmacologyAction
					   ,nc.Practice
					   ,nc.Contraindications
					   ,nc.Restrictions
					   ,nc.UseOfPregnancy
					   ,nc.SideEffects
					   ,nc.Interaction
					   ,nc.Overdose
					   ,nc.InjectionMethod
					   ,nc.Caution
					   ,nc.Composition
					   ,nc.PharmDescription
					   ,nc.Pharmacodynamics
					   ,nc.Indications
					   ,nc.ExpirationDate
					   ,nc.MNNName
					   ,nc.Manufacturer
					   ,nc.CMnn
					   ,atc.EhrRusID
					   ,nc.ProductType
					   ,RegCode
					   ,FedCode
					   ,eh1.EhrRusID
					   ,eh2.EhrRusID
                       ,nc.NameLatR
            from #C_TRNS nc
				outer apply (
					select top 1 eh.EhrRusID 
					from EHR.T_EHR_RUS_P08TSH954N eh 
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.CodeEHR = nc.EhrRusP08TSH954N and RRB.CURRVERSION = nc.EhrRusP08TSH954NVer
				)eh1
				outer apply (
					select top 1 eh.EhrRusID 
					from EHR.T_EHR_RUS_575VSF88OG eh
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.CodeEHR = nc.EhrRus575VSF88OG and RRB.CURRVERSION = nc.EhrRus575VSF88OGVer
				)eh2
				outer apply (
					select top 1 eh.EhrRusID 
						from EHR.T_EHR_RUS_M00002 eh
							LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.IdEHR = nc.Atc and rrb.CurrVersion = nc.AtcVer
				) atc
            where not exists
                (
                    select *
                    from [PHARM].T_C_TRN c
                    where c.CTrnID = nc.ID
                )
                
        END
        else if(@OperationType = @DELETE)
        BEGIN
            -- Удаление
            update c
            set c.Active = 0
            from [PHARM].[T_C_TRN] c
                join #C_TRN nc on nc.[ID] = c.CTrnID
        END
        else
        BEGIN
            -- update [PHARM].T_C_TRN
            update c
            set c.NameEng = nc.NameEng
               ,c.NameRus = nc.NameRus
               ,c.NameLatR = nc.NameLatR
               ,c.OtherID = nc.OtherID
			   ,c.Characteristic = nc.Characteristic
			   ,c.PharmacologyAction = nc.PharmacologyAction
			   ,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.Overdose = nc.Overdose
			   ,c.InjectionMethod = nc.InjectionMethod
			   ,c.Caution = nc.Caution
			   ,c.Composition = nc.Composition
			   ,c.PharmDescription = nc.PharmDescription
			   ,c.Pharmacodynamics = nc.Pharmacodynamics
			   ,c.Indications = nc.Indications
			   ,c.ExpirationDate = nc.ExpirationDate
			   ,c.MNNName = nc.MNNName
			   ,c.Manufacturer = nc.Manufacturer
			   ,c.CMnn = nc.CMnn
			   ,c.Atc = atc.EhrRusID
			   ,c.ProductType = nc.ProductType
			   ,c.RegCode = nc.RegCode
			   ,c.FedCode = nc.FedCode
			   ,c.EhrRusP08TSH954N = eh1.EhrRusID
			   ,c.EhrRus575VSF88OG = eh2.EhrRusID
            from [PHARM].T_C_TRN c
                join #C_TRN nc on nc.ID = c.CTrnID
				outer apply (
					select top 1 eh.EhrRusID 
					from EHR.T_EHR_RUS_P08TSH954N eh
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.CodeEHR = nc.EhrRusP08TSH954N and RRB.CURRVERSION = nc.EhrRusP08TSH954NVer
				)eh1
				outer apply (
					select top 1 eh.EhrRusID 
					from EHR.T_EHR_RUS_575VSF88OG eh
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.CodeEHR = nc.EhrRus575VSF88OG and RRB.CURRVERSION = nc.EhrRus575VSF88OGVer
				)eh2
				outer apply (
					select top 1 eh.EhrRusID 
						from EHR.T_EHR_RUS_M00002 eh
							LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.IdEHR = nc.Atc and rrb.CurrVersion = nc.AtcVer
				) atc
            where @OperationType = @UPDATE

            -- insert [PHARM].T_C_TRN
            INSERT INTO [PHARM].[T_C_TRN]
                ([CTrnID] ,[NameRus] ,[NameEng], [Active], [IsDrug], [OtherID]
				,Characteristic,PharmacologyAction,Practice,Contraindications,Restrictions,UseOfPregnancy,SideEffects,Interaction,Overdose,InjectionMethod,Caution,
                Composition,PharmDescription,Pharmacodynamics,Indications,ExpirationDate,MNNName,Manufacturer,CMnn,Atc,ProductType,RegCode,FedCode,EhrRusP08TSH954N,EhrRus575VSF88OG,NameLatR)
            SELECT
                       nc.ID--<CMnnID, uniqueidentifier,>
                       ,nc.NameRus--<NameRus, varchar(4000),>
                       ,nc.NameEng--<NameEng, varchar(4000),>
                       ,nc.Active--<Active, bit,>
                       ,nc.IsDrug--<IsDrug, bit,>
                       ,nc.OtherID--<OtherID, int,>
					   ,nc.Characteristic
					   ,nc.PharmacologyAction
					   ,nc.Practice
					   ,nc.Contraindications
					   ,nc.Restrictions
					   ,nc.UseOfPregnancy
					   ,nc.SideEffects
					   ,nc.Interaction
					   ,nc.Overdose
					   ,nc.InjectionMethod
					   ,nc.Caution
					   ,nc.Composition
					   ,nc.PharmDescription
					   ,nc.Pharmacodynamics
					   ,nc.Indications
					   ,nc.ExpirationDate
					   ,nc.MNNName
					   ,nc.Manufacturer
					   ,nc.CMnn
					   ,atc.EhrRusID
					   ,nc.ProductType
					   ,RegCode
					   ,FedCode
					   ,eh1.EhrRusID
					   ,eh2.EhrRusID
                       ,nc.NameLatR
            from #C_TRN nc
				outer apply (
					select top 1 eh.EhrRusID 
					from EHR.T_EHR_RUS_P08TSH954N eh
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.CodeEHR = nc.EhrRusP08TSH954N and RRB.CURRVERSION = nc.EhrRusP08TSH954NVer
				)eh1
				outer apply (
					select top 1 eh.EhrRusID 
					from EHR.T_EHR_RUS_575VSF88OG eh
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.CodeEHR = nc.EhrRus575VSF88OG and RRB.CURRVERSION = nc.EhrRus575VSF88OGVer
				)eh2
				outer apply (
					select top 1 eh.EhrRusID 
						from EHR.T_EHR_RUS_M00002 eh
							LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.IdEHR = nc.Atc and rrb.CurrVersion = nc.AtcVer
				) atc
            where 
				@OperationType = @INSERT
				and not exists
                (
                    select *
                    from [PHARM].T_C_TRN c
                    where c.CTrnID = nc.ID
                )
				
        END
        
        declare @EntId int
        select top 1 @EntId = EntityID from VCLib.T_ENTITIES where EntName = 'C_TRN'
        IF(exists (select top 1 * from [APS].T_GEN_NUMBERS where  EntityID = @EntId and NumberName = 'PHARM.C_TRN'))
        BEGIN
			-- генерируем след. номер
			--exec [dbo].sp_GenerateNumber 551, 'PHARM.C_TRN'
			update gn
			set gn.NextNumber = (select MAX(OtherID) from [PHARM].T_C_TRN)
			from [APS].T_GEN_NUMBERS gn
			where 
				gn.EntityID = @EntId
				and gn.NumberName = 'PHARM.C_TRN'
        END
        ELSE
        BEGIN
        
			INSERT INTO [APS].[T_GEN_NUMBERS]
			           ([GenNumbersID]
			           ,[EntityID]
			           ,[NumberName]
			           ,[NextNumber])
        			     VALUES
        			           (newID()
        			           ,@EntId
        			           ,'PHARM.C_TRN'
        			           ,ISNULL((select MAX(OtherID) from [PHARM].T_C_TRN), 1))
        END

        -- Drop tables
        DROP TABLE #C_TRN
        DROP TABLE #C_TRNS

    END
END
END
GO