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(4000)
		,NameEng				VARCHAR(4000)
		,Active					BIT
		,IsDrug					BIT
		,Characteristic			VARCHAR(4000)
		,PharmacologyAction		VARCHAR(4000)
		,Practice				VARCHAR(max)
		,Contraindications		VARCHAR(5000)
		,Restrictions			VARCHAR(max)
		,UseOfPregnancy			VARCHAR(max)
		,SideEffects			VARCHAR(max)
		,Interaction			VARCHAR(max)
		,Overdose				VARCHAR(max)
		,InjectionMethod		VARCHAR(255)
		,Caution				VARCHAR(max)
		,Composition			VARCHAR(3000)
		,PharmDescription		VARCHAR(max)
		,Pharmacodynamics		VARCHAR(max)
		,Indications			VARCHAR(8000)
		,ExpirationDate			VARCHAR(300)
		,MNNName				VARCHAR(8000)
		,Manufacturer			VARCHAR(3000)
		,CMnn					UNIQUEIDENTIFIER
		,Atc					VARCHAR(7)
		,ProductType			INT
		,NameLatR				VARCHAR(4000)
		,FedCode				VARCHAR(50)
		,RegCode				VARCHAR(50)
		,EhrRusP08TSH954N		INT
		,EhrRusP08TSH954NVer	VARCHAR(8)
		,EhrRus575VSF88OG		INT
		,EhrRus575VSF88OGVer	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]
				,[Atc]
				,[ProductType]
				,[NameLatR]
				,[FedCode]
				,[RegCode]
				,eh1.[IdEHR]			AS [EhrRusP08TSH954N]
				,eh1.[CurrVersion]		AS [EhrRusP08TSH954NVer]
				,eh2.[IdEHR]			AS [EhrRus575VSF88OG]
				,eh2.[CurrVersion]		AS [EhrRus575VSF88OGVer]
			FROM ' + @PATH + '[PHARM].T_C_TRN nc
			OUTER APPLY (
				SELECT TOP 1 EH.IDEHR, CurrVersion 
					FROM ' + @PATH + '.EHR.T_EHR_RUS EH
						LEFT JOIN ' + @PATH + '.EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
				WHERE NC.EHRRUSP08TSH954N = EH.EHRRUSID
			)EH1
			OUTER APPLY (
				SELECT TOP 1 EH.IDEHR, CurrVersion 
					FROM ' + @PATH + '.EHR.T_EHR_RUS EH
						LEFT JOIN ' + @PATH + '.EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
				WHERE NC.EHRRUS575VSF88OG = EH.EHRRUSID
			)EH2'
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 = nc.Atc
	   ,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 eh
					join EHR.T_EHR_RUS_P08TSH954N ehh on ehh.EhrRusID = eh.EhrRusID
					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 eh
					join EHR.T_EHR_RUS_575VSF88OG ehh on ehh.EhrRusID = eh.EhrRusID
					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
	
	-- Загружаем новые
	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
				,nc.Atc
				,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 eh
				join EHR.T_EHR_RUS_P08TSH954N ehh on ehh.EhrRusID = eh.EhrRusID
				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 eh
				join EHR.T_EHR_RUS_575VSF88OG ehh on ehh.EhrRusID = eh.EhrRusID
				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
	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.Atc				as 'C_TRN/Atc'
								  ,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'
                            from [PHARM].T_C_TRN c
								left join EHR.T_EHR_RUS 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 ehr2 on ehr2.EhrRusID = c.EhrRus575VSF88OG
								LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB.EhrRusRefBooksID = ehr2.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'
                from [PHARM].T_C_TRN c
					left join EHR.T_EHR_RUS 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 ehr2 on ehr2.EhrRusID = c.EhrRus575VSF88OG
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB.EhrRusRefBooksID = ehr2.EhrRusRefBooks
                where c.CTrnID = @ID
                for xml path(''),
                root('C_TRN')
            ) 
        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(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]', 'varchar(7)')						as Atc
			  ,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]', 'varchar(7)')						as Atc
			  ,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 = nc.Atc
			   ,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 eh
						join EHR.T_EHR_RUS_P08TSH954N ehh on ehh.EhrRusID = eh.EhrRusID
						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 eh
					join EHR.T_EHR_RUS_575VSF88OG ehh on ehh.EhrRusID = eh.EhrRusID
					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

            -- Загружаем новые
            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
					   ,nc.Atc
					   ,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 eh
						join EHR.T_EHR_RUS_P08TSH954N ehh on ehh.EhrRusID = eh.EhrRusID
						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 eh
						join EHR.T_EHR_RUS_575VSF88OG ehh on ehh.EhrRusID = eh.EhrRusID
						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
            where not exists
                (
                    select *
                    from [PHARM].T_C_TRN c
                    where c.CTrnID = nc.ID
                )
                

            -- Удаляем неактивные, непривязанные трн
            --delete c
            --from [PHARM].T_C_TRN c
            --where c.Active = 0
            --        and
            --    not exists
            --    (
            --        select *
            --        from [PHARM].T_BILL_OF_GOODS bg
            --        where bg.CTrn = c.CTrnID
            --    )

        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 = nc.Atc
			   ,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 eh
						join EHR.T_EHR_RUS_P08TSH954N ehh on ehh.EhrRusID = eh.EhrRusID
						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 eh
						join EHR.T_EHR_RUS_575VSF88OG ehh on ehh.EhrRusID = eh.EhrRusID
						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
            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
					   ,nc.Atc
					   ,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 eh
						join EHR.T_EHR_RUS_P08TSH954N ehh on ehh.EhrRusID = eh.EhrRusID
						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 eh
						join EHR.T_EHR_RUS_575VSF88OG ehh on ehh.EhrRusID = eh.EhrRusID
						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
            where not exists
                (
                    select *
                    from [PHARM].T_C_TRN c
                    where c.CTrnID = nc.ID
                )
                    and
                @OperationType = @INSERT
        END

        
        declare @EntId int
        select top 1 @EntId = EntityID from VCLib.T_ENTITIES where EntName = 'C_TRN'
        IF(exists (select top 1 * from [AKUZ].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 [AKUZ].T_GEN_NUMBERS gn
        			where gn.EntityID = @EntId
        					and
        				gn.NumberName = 'PHARM.C_TRN'
        		END
        ELSE
        BEGIN
        
        			INSERT INTO [AKUZ].[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