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_GetSetCtrn]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetSetCtrn]
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-12-22
-- Description: Выгрузка/загрузка справочника Торговое наименование препарата (ТРН) в(из) RDS
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetSetCtrn]
    @Xml xml = null,
    @IsNew bit = 1,
    @ID uniqueidentifier = null,
    @QueueName varchar(100) = null,
    @dateTime datetime = null,
    @User uniqueidentifier = null
AS
BEGIN

    SET NOCOUNT ON;

    declare @Result varchar(max)
           ,@ContentEncoding varchar(255)
           ,@Type varchar(255)
           ,@OperationType varchar(255)
           ,@OwnerID uniqueidentifier
            -- Operation types
           ,@INSERT varchar(6)
           ,@UPDATE varchar(6)
           ,@DELETE varchar(6)
           ,@FTLOAD varchar(6)

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

    IF (@Xml is null)
    BEGIN
        -- Инициализация
        select @OwnerID = @ID

        --выясняем тип операции
        IF (@ID is null)
        BEGIN
            select @OperationType = @FTLOAD
        END
        ELSE IF(@IsNew = 1)
        BEGIN
            select @OperationType = @INSERT
        END
        ELSE IF (@IsNew = 0 and exists (select * from [PHARM].T_C_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.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'
								  ,c.RegCode				as 'C_TRN/RegCode'
								  ,c.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.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.ProductType			as 'body/ProductType'
					  ,c.RegCode				as 'body/RegCode'
					  ,c.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 [RDS].[T_RABBIT_SEND_OBJECT]
            ([RabbitSendObjectID] ,[QueueName] ,[Content] ,[MessageID] ,[Headers] ,[ContentEncoding] ,[Type] ,[UserID] ,[State] ,[CreateDateTime] ,[OwnerID])
        select NewID()--(<RabbitSendObjectID, uniqueidentifier,>
              ,s.ServerName--,<QueueName, varchar(255),>
              ,dbo.ConvertTextToBinaryZip(@Result, '')--,<Content, text,>
              ,null--,<MessageID, varchar(255),>
              ,null--,<Headers, varchar(255),>
              ,@ContentEncoding--,<ContentEncoding, varchar(255),>
              ,@Type--,<Type, varchar(255),>
              ,@User--,<UserID, varchar(255),>
              ,0--,<State, int,>)
              ,@dateTime--,<CreateDateTime, datetime,>)
              ,@OwnerID--,<OwnerID, varchar(255),>
        from [RDS].T_SYNC_SERVERS s
        where (@QueueName is not null and s.ServerName = @QueueName) or (@QueueName is null and s.ServerType in (0, 1)) -- Рассылаем пакет всем подписанным серверам AKUZ-а и APS, если не указана очередь
    END
    ELSE
    IF (@Xml is not null)
    BEGIN

        -- Получаем тип операции
        select @OperationType = t.c.value('./header[1]/TYPE[1]', 'varchar(36)')
              ,@OwnerID = t.c.value('./header[1]/ID[1]', 'uniqueidentifier')
              ,@Result = cast(@Xml as varchar(max))
        from @Xml.nodes('C_TRN') T(c)


        -- Обработка операции
        if (@OperationType = @FTLOAD)
        BEGIN
            -- 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('./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(8)')   as EhrRusP08TSH954NVer
                 , t.c.value('./EhrRus575VSF88OG[1]', 'varchar(52)')     as EhrRus575VSF88OG
                 , t.c.value('./EhrRus575VSF88OGVer[1]', 'varchar(8)')   as EhrRus575VSF88OGVer
            INTO #C_TRNS
            from @Xml.nodes('C_TRN/body/C_TRNS/C_TRN') T(c)

            -- Первичная загрузка
            -- МНН не из справочника 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.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 RRB1 ON RRB1.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.CodeEHR = nc.EhrRus575VSF88OG and RRB1.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 RRBAtc ON RRBAtc.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.IdEHR = nc.Atc and RRBAtc.CurrVersion = nc.AtcVer
				) atc

            -- Загружаем новые
            INSERT INTO [PHARM].[T_C_TRN]
                       ([CTrnID] ,[NameRus] ,[NameEng] ,[Active], [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.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
					   ,nc.RegCode
					   ,nc.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 RRB1 ON RRB1.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.CodeEHR = nc.EhrRus575VSF88OG and RRB1.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 RRBAtc ON RRBAtc.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.IdEHR = nc.Atc and RRBAtc.CurrVersion = nc.AtcVer
				) atc
            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
            BEGIN
                -- 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]/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(8)')                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(8)')   as EhrRusP08TSH954NVer
                     , t.c.value('./body[1]/EhrRus575VSF88OG[1]', 'varchar(52)')     as EhrRus575VSF88OG
                     , t.c.value('./body[1]/EhrRus575VSF88OGVer[1]', 'varchar(8)')   as EhrRus575VSF88OGVer
                INTO #C_TRN
                from @Xml.nodes('C_TRN') T(c)

                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 RRB1
                                                                 ON RRB1.EhrRusRefBooksID = eh.EhrRusRefBooks
                                              where eh.CodeEHR = nc.EhrRus575VSF88OG
                                                and RRB1.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 RRBAtc
                                                                 ON RRBAtc.EhrRusRefBooksID = eh.EhrRusRefBooks
                                              where eh.IdEHR = nc.Atc
                                                and RRBAtc.CurrVersion = nc.AtcVer) atc
                        where @OperationType = @UPDATE

                        -- insert [PHARM].T_C_TRN
                        INSERT INTO [PHARM].[T_C_TRN]
                        ([CTrnID], [NameRus], [NameEng], [Active], [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.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
                             , nc.RegCode
                             , nc.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 RRB1
                                                                 ON RRB1.EhrRusRefBooksID = eh.EhrRusRefBooks
                                              where eh.CodeEHR = nc.EhrRus575VSF88OG
                                                and RRB1.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 RRBAtc
                                                                 ON RRBAtc.EhrRusRefBooksID = eh.EhrRusRefBooks
                                              where eh.IdEHR = nc.Atc
                                                and RRBAtc.CurrVersion = nc.AtcVer) atc
                        where @OperationType = @INSERT
                          and not exists
                            (select *
                             from [PHARM].T_C_TRN c
                             where c.CTrnID = nc.ID)

                    END
            END


        -- Отправка всем остальным подписанным серверам AKUZ-а и APS
        INSERT INTO [RDS].[T_RABBIT_SEND_OBJECT]
            ([RabbitSendObjectID] ,[QueueName] ,[Content] ,[MessageID] ,[Headers] ,[ContentEncoding] ,[Type] ,[UserID] ,[State] ,[CreateDateTime] ,[OwnerID])
        select NewID()--(<RabbitSendObjectID, uniqueidentifier,>
              ,s.ServerName--,<QueueName, varchar(255),>
              ,dbo.ConvertTextToBinaryZip(@Result, '')--,<Content, text,>
              ,null--,<MessageID, varchar(255),>
              ,null--,<Headers, varchar(255),>
              ,@ContentEncoding--,<ContentEncoding, varchar(255),>
              ,@Type--,<Type, varchar(255),>
              ,@User--,<UserID, varchar(255),>
              ,0--,<State, int,>)
              ,@dateTime--,<CreateDateTime, datetime,>)
              ,@OwnerID--,<OwnerID, varchar(255),>
        from [RDS].T_SYNC_SERVERS s
        where
			s.ServerName <> @QueueName
            and @OperationType <> @FTLOAD

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

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

    END
END
