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_GetSetBillOfGoods]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetSetBillOfGoods]
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-07-29
-- Description: Выгрузка/загрузка справочника Форм выпуска препарата(TRN) в(из) RDS
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetSetBillOfGoods]
    @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())
          ,@Type = 'BILL_OF_GOODS'
            -- Operation types
          ,@INSERT = 'Insert'
          ,@UPDATE = 'Update'
          ,@DELETE = 'Delete'
          ,@FTLOAD = 'FTLoad'

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

        -- Обработка операции
        IF (@OperationType = @FTLOAD)
        BEGIN
            -- Первичная загрузка
            select @Result =
            (
                select @FTLOAD       as 'header/TYPE'
                      ,(
                            select bg.BillOfGoodsID						as 'BILL_OF_GOODS/ID'
                                  ,dv.Value								as 'BILL_OF_GOODS/DoseView'
                                  ,bg.OtherID							as 'BILL_OF_GOODS/OtherID'
								  ,bg.IsVital							as 'BILL_OF_GOODS/IsVital'
                                  ,bg.Active							as 'BILL_OF_GOODS/Active'
								  ,bg.Rate								as 'BILL_OF_GOODS/Rate'
								  ,bg.ExportCode						as 'BILL_OF_GOODS/ExportCode'
								  ,bg.PackQuantity						as 'BILL_OF_GOODS/PackQuantity'
                                  ,bg.FederalID							as 'BILL_OF_GOODS/FederalID'
                                  ,bg.MedicamentProducer				as 'BILL_OF_GOODS/MedicamentProducer'
                                  ,bg.CTrn								as 'BILL_OF_GOODS/CTrn'
                                  ,bg.MedicationForms					as 'BILL_OF_GOODS/MedicationForms'
								  ,bg.ProductInfo						as 'BILL_OF_GOODS/ProductInfo'
								  ,bg.Dlo								as 'BILL_OF_GOODS/Dlo'
								  ,bg.RoundToWriteOff					as 'BILL_OF_GOODS/RoundToWriteOff'
								  ,bg.Gtin								as 'BILL_OF_GOODS/Gtin'
								  ,bg.EAN								as 'BILL_OF_GOODS/EAN'
								  ,bg.MedicamentsOfRoszdravnadzor		as 'BILL_OF_GOODS/MedicamentsOfRoszdravnadzor'
								  ,bg.RegCode							as 'BILL_OF_GOODS/RegCode'
								  ,bg.FedCode							as 'BILL_OF_GOODS/FedCode'
								  ,er.IdEHR								as 'BILL_OF_GOODS/EhrRus2m69i03gtd'
								  ,RRB.CurrVersion						as 'BILL_OF_GOODS/EhrRus2m69i03gtdVer'
								  ,er1.CodeEHR							as 'BILL_OF_GOODS/EhrRus9a9g09e8kl'
								  ,RRB1.CurrVersion						as 'BILL_OF_GOODS/EhrRus9a9g09e8klVer'
								  ,er2.IdEHR							as 'BILL_OF_GOODS/EhrRusClp14fvirj'
								  ,RRB2.CurrVersion						as 'BILL_OF_GOODS/EhrRusClp14fvirjVer'
								  ,er3.IdEHR							as 'BILL_OF_GOODS/EhrRusH17F8UTS3N'
								  ,RRB3.CurrVersion						as 'BILL_OF_GOODS/EhrRusH17F8UTS3NVer'
								  ,er4.IdEHR							as 'BILL_OF_GOODS/EhrRusDsg207'
								  ,RRB4.CurrVersion						as 'BILL_OF_GOODS/EhrRusDsg207Ver'
                            from [PHARM].T_BILL_OF_GOODS bg
                                left join [PHARM].S_DLO_DoseView dv on dv.Id = bg.DoseView
								left join EHR.T_EHR_RUS_2M69I03GTD er on er.EhrRusID = bg.EhrRus2m69i03gtd
									LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
								left join EHR.T_EHR_RUS_9A9G09E8KL er1 on er1.EhrRusID = bg.EhrRus9a9g09e8kl
									LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = er1.EhrRusRefBooks
								left join EHR.T_EHR_RUS_CLP14FVIRJ er2 on er2.EhrRusID = bg.EhrRusClp14fvirj
									LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB2 ON RRB2.EhrRusRefBooksID = er2.EhrRusRefBooks
								left join EHR.T_EHR_RUS_H17F8UTS3N er3 on er3.EhrRusID = bg.EhrRusH17F8UTS3N
									LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB3 ON RRB3.EhrRusRefBooksID = er3.EhrRusRefBooks
								left join EHR.T_EHR_RUS_DSG207 er4 on er4.EhrRusID = bg.EhrRusDsg207
									LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB4 ON RRB4.EhrRusRefBooksID = er4.EhrRusRefBooks
                            for xml path(''),
                            type
                      ) as 'body/BILL_OF_GOODS_LIST'
                for xml path(''),
                root('BILL_OF_GOODS')
            )
        END
        ELSE IF(@OperationType <> @DELETE)
        BEGIN

            -- собираем BILL_OF_GOODS
            SELECT @Result =
            (
                select @OperationType					as 'header/TYPE'
                      ,@ID								as 'header/ID'
                      ,dv.Value							as 'body/DoseView'
                      ,bg.OtherID						as 'body/OtherID'
					  ,bg.IsVital						as 'body/IsVital'
                      ,bg.Active						as 'body/Active'
					  ,bg.Rate							as 'body/Rate'
					  ,bg.ExportCode					as 'body/ExportCode'
					  ,bg.PackQuantity					as 'body/PackQuantity'
                      ,bg.FederalID						as 'body/FederalID'
                      ,bg.MedicamentProducer			as 'body/MedicamentProducer'
                      ,bg.CTrn							as 'body/CTrn'
                      ,bg.MedicationForms				as 'body/MedicationForms'
					  ,bg.ProductInfo					as 'body/ProductInfo'
					  ,bg.Dlo							as 'body/Dlo'
					  ,bg.RoundToWriteOff				as 'body/RoundToWriteOff'
					  ,bg.Gtin							as 'body/Gtin'
					  ,bg.EAN							as 'body/EAN'
					  ,bg.MedicamentsOfRoszdravnadzor	as 'body/MedicamentsOfRoszdravnadzor'
					  ,bg.RegCode						as 'body/RegCode'
					  ,bg.FedCode						as 'body/FedCode'
					  ,er.IdEHR							as 'body/EhrRus2m69i03gtd'
					  ,RRB.CurrVersion					as 'body/EhrRus2m69i03gtdVer'
					  ,er1.CodeEHR						as 'body/EhrRus9a9g09e8kl'
					  ,RRB1.CurrVersion					as 'body/EhrRus9a9g09e8klVer'
					  ,er2.IdEHR						as 'body/EhrRusClp14fvirj'
					  ,RRB2.CurrVersion					as 'body/EhrRusClp14fvirjVer'
					  ,er3.IdEHR						as 'body/EhrRusH17F8UTS3N'
					  ,RRB3.CurrVersion					as 'body/EhrRusH17F8UTS3NVer'
					  ,er4.IdEHR						as 'body/EhrRusDsg207'
					  ,RRB4.CurrVersion					as 'body/EhrRusDsg207Ver'
                from [PHARM].T_BILL_OF_GOODS bg
					left join [PHARM].S_DLO_DoseView dv on dv.Id = bg.DoseView
					left join EHR.T_EHR_RUS_2M69I03GTD er on er.EhrRusID = bg.EhrRus2m69i03gtd
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					left join EHR.T_EHR_RUS_9A9G09E8KL er1 on er1.EhrRusID = bg.EhrRus9a9g09e8kl
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = er1.EhrRusRefBooks
					left join EHR.T_EHR_RUS_CLP14FVIRJ er2 on er2.EhrRusID = bg.EhrRusClp14fvirj
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB2 ON RRB2.EhrRusRefBooksID = er2.EhrRusRefBooks
					left join EHR.T_EHR_RUS_H17F8UTS3N er3 on er3.EhrRusID = bg.EhrRusH17F8UTS3N
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB3 ON RRB3.EhrRusRefBooksID = er3.EhrRusRefBooks
					left join EHR.T_EHR_RUS_DSG207 er4 on er4.EhrRusID = bg.EhrRusDsg207
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB4 ON RRB4.EhrRusRefBooksID = er4.EhrRusRefBooks
                where bg.BillOfGoodsID = @ID
                for xml path(''),
                root('BILL_OF_GOODS')
            )
        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 REPLACE(dv.Value, ' ', '') as [value], MAX(dv.Id) as Id
		into #PharmDV
		from PHARM.S_DLO_DoseView dv
		group by REPLACE(dv.Value, ' ', '')

        -- Получаем тип операции
        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('BILL_OF_GOODS') T(c)


        -- Обработка операции
        if (@OperationType = @FTLOAD)
        BEGIN
            -- Generate table #BILL_OF_GOODS_LIST
            select t.c.value('./ID[1]', 'uniqueidentifier')                          as [ID]
                 , t.c.value('./DoseView[1]', 'varchar(1000)')                       as [DoseView]
                 , REPLACE(t.c.value('./DoseView[1]', 'varchar(1000)'), ' ', '')     as [DoseViewRep]
                 , t.c.value('./OtherID[1]', 'int')                                  as [OtherID]
                 , t.c.value('./IsVital[1]', 'bit')                                  as [IsVital]
                 , t.c.value('./Active[1]', 'bit')                                   as [Active]
                 , t.c.value('./Rate[1]', 'int')                                     as [Rate]
                 , t.c.value('./ExportCode[1]', 'int')                               as [ExportCode]
                 , t.c.value('./PackQuantity[1]', 'int')                             as [PackQuantity]
                 , t.c.value('./FederalID[1]', 'int')                                as [FederalID]
                 , t.c.value('./MedicamentProducer[1]', 'uniqueidentifier')          as [MedicamentProducer]
                 , t.c.value('./CTrn[1]', 'uniqueidentifier')                        as [CTrn]
                 , t.c.value('./MedicationForms[1]', 'uniqueidentifier')             as [MedicationForms]
                 , t.c.value('./ProductInfo[1]', 'varchar(max)')                     as [ProductInfo]
                 , t.c.value('./Dlo[1]', 'bit')                                      as [Dlo]
                 , t.c.value('./RoundToWriteOff[1]', 'bit')                          as [RoundToWriteOff]
                 , t.c.value('./Gtin[1]', 'varchar(14)')                             as [Gtin]
                 , t.c.value('./EAN[1]', 'varchar(255)')                             as [EAN]
                 , t.c.value('./MedicamentsOfRoszdravnadzor[1]', 'uniqueidentifier') as [MedicamentsOfRoszdravnadzor]
                 , t.c.value('./RegCode[1]', 'varchar(50)')                          as RegCode
                 , t.c.value('./FedCode[1]', 'varchar(50)')                          as FedCode
                 , t.c.value('./EhrRus2m69i03gtd[1]', 'int')                         as EhrRus2m69i03gtd
                 , t.c.value('./EhrRus2m69i03gtdVer[1]', 'varchar(8)')               as EhrRus2m69i03gtdVer
                 , t.c.value('./EhrRus9a9g09e8kl[1]', 'varchar(52)')                 as EhrRus9a9g09e8kl
                 , t.c.value('./EhrRus9a9g09e8klVer[1]', 'varchar(8)')               as EhrRus9a9g09e8klVer
                 , t.c.value('./EhrRusClp14fvirj[1]', 'int')                         as EhrRusClp14fvirj
                 , t.c.value('./EhrRusClp14fvirjVer[1]', 'varchar(8)')               as EhrRusClp14fvirjVer
                 , t.c.value('./EhrRusH17F8UTS3N[1]', 'int')                         as EhrRusH17F8UTS3N
                 , t.c.value('./EhrRusH17F8UTS3NVer[1]', 'varchar(8)')               as EhrRusH17F8UTS3NVer
                 , t.c.value('./EhrRusDsg207[1]', 'int')                             as EhrRusDsg207
                 , t.c.value('./EhrRusDsg207Ver[1]', 'varchar(8)')                   as EhrRusDsg207Ver
            INTO #BILL_OF_GOODS_LIST
            from @Xml.nodes('BILL_OF_GOODS/body/BILL_OF_GOODS_LIST/BILL_OF_GOODS') T(c)

            -- Первичная загрузка
            -- Загружаем новые DoseView
            INSERT INTO [PHARM].[S_DLO_DoseView] ([Value])
            SELECT bgl.DoseView
            FROM #BILL_OF_GOODS_LIST bgl
            where bgl.DoseView is not null and not exists
                (
                    select *
                    from #PharmDV dv
                    where dv.Value = bgl.DoseViewRep
                )
            group by bgl.DoseView

			truncate table #PharmDV
			insert into #PharmDV(Value, Id)
			select REPLACE(dv.Value, ' ', '') as [value], MAX(dv.Id) as Id
			from PHARM.S_DLO_DoseView dv
			group by REPLACE(dv.Value, ' ', '')

            -- T_BILL_OF_GOODS не из справочника RDS переводим в неактивные
            update bf
            set bf.Active = 0
            from [PHARM].T_BILL_OF_GOODS bf
            where not exists
            (
                select *
                from #BILL_OF_GOODS_LIST bgl
                where bgl.ID = bf.BillOfGoodsID
            )

            -- Обновляем имеющиеся TRN
            update bf
            set
                bf.DoseView						= dv.Id
               ,bf.OtherID						= bgl.OtherID
               ,bf.IsVital						= ISNULL(bgl.IsVital,0)
               ,bf.Active						= bgl.Active
			   ,bf.Rate							= ISNULL(bgl.Rate, 1)
               ,bf.ExportCode					= bgl.ExportCode
               ,bf.PackQuantity					= bgl.PackQuantity
               ,bf.FederalID					= bgl.FederalID
               ,bf.MedicamentProducer			= bgl.MedicamentProducer
			   ,bf.CTrn							= bgl.CTrn
               ,bf.MedicationForms				= bgl.MedicationForms
               ,bf.ProductInfo					= bgl.ProductInfo
			   ,bf.Dlo							= ISNULL(bgl.Dlo,0)
			   ,bf.RoundToWriteOff				= ISNULL(bgl.RoundToWriteOff,0)
			   ,bf.Gtin							= bgl.Gtin
			   ,bf.EAN							= bgl.EAN
			   ,bf.MedicamentsOfRoszdravnadzor	= bgl.MedicamentsOfRoszdravnadzor
			   ,bf.RegCode						= bgl.RegCode
			   ,bf.FedCode						= bgl.FedCode
			   ,bf.EhrRus2m69i03gtd				= ehr.EhrRusID
			   ,bf.EhrRus9a9g09e8kl				= ehr1.EhrRusID
			   ,bf.EhrRusClp14fvirj				= ehr2.EhrRusID
			   ,bf.EhrRusH17F8UTS3N				= ehr3.EhrRusID
			   ,bf.EhrRusDsg207					= ehr4.EhrRusID
            from [PHARM].T_BILL_OF_GOODS bf
                join #BILL_OF_GOODS_LIST bgl on bgl.ID = bf.BillOfGoodsID
				left join #PharmDV dv on dv.Value = bgl.DoseViewRep
				outer apply (
					select top 1 er.EhrRusID
					from EHR.T_EHR_RUS_2M69I03GTD er
						join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = bgl.EhrRus2m69i03gtd and RRB.CURRVERSION = bgl.EhrRus2m69i03gtdVer
				) ehr
				outer apply (
					select top 1 er.EhrRusID
					from EHR.T_EHR_RUS_9A9G09E8KL er
						join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.CodeEHR = bgl.EhrRus9a9g09e8kl and RRB.CURRVERSION = bgl.EhrRus9a9g09e8klVer
				) ehr1
				outer apply (
					select top 1 er.EhrRusID
					from EHR.T_EHR_RUS_CLP14FVIRJ er
						join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = bgl.EhrRusClp14fvirj and RRB.CURRVERSION = bgl.EhrRusClp14fvirjVer
				) ehr2
				outer apply (
					select top 1 er.EhrRusID
					from EHR.T_EHR_RUS_H17F8UTS3N er
						join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = bgl.EhrRusH17F8UTS3N and RRB.CURRVERSION = bgl.EhrRusH17F8UTS3NVer
				) ehr3
				outer apply (
					select top 1 er.EhrRusID
					from EHR.T_EHR_RUS_DSG207 er
						join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = bgl.EhrRusDsg207 and RRB.CURRVERSION = bgl.EhrRusDsg207Ver
				) ehr4

            -- Загружаем новые TRN
            INSERT INTO [PHARM].[T_BILL_OF_GOODS]
                       (
						 [BillOfGoodsID]
						,[CTrn]
						,[Active]
						,[MedicamentProducer]
						,OtherID
						,[MedicationForms]
						,[DoseView]
						,[Rate]
						,[IsVital]
						,[PackQuantity]
						,ExportCode
						,FederalID
						,ProductInfo
						,Dlo
						,RoundToWriteOff
						,Gtin
						,EAN
						,MedicamentsOfRoszdravnadzor
						,RegCode
						,FedCode
						,EhrRus2m69i03gtd
						,EhrRus9a9g09e8kl
						,EhrRusClp14fvirj
						,EhrRusH17F8UTS3N
						,EhrRusDsg207)
            SELECT
				 bgl.ID
				,bgl.CTrn
				,bgl.Active
				,bgl.MedicamentProducer
				,bgl.OtherID
				,bgl.MedicationForms
				,dv.Id
				,ISNULL(bgl.Rate, 1)
				,ISNULL(bgl.IsVital,0)
				,bgl.PackQuantity
				,bgl.ExportCode
				,bgl.FederalID
				,bgl.ProductInfo
				,ISNULL(bgl.Dlo,0)
				,ISNULL(bgl.RoundToWriteOff,0)
				,bgl.Gtin
				,bgl.EAN
				,bgl.MedicamentsOfRoszdravnadzor
				,bgl.RegCode
				,bgl.FedCode
				,ehr.EhrRusID
				,ehr1.EhrRusID
				,ehr2.EhrRusID
				,ehr3.EhrRusID
				,ehr4.EhrRusID
            from #BILL_OF_GOODS_LIST bgl
				left join #PharmDV dv on dv.Value = bgl.DoseViewRep
				outer apply (
					select top 1 er.EhrRusID
					from EHR.T_EHR_RUS_2M69I03GTD er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = bgl.EhrRus2m69i03gtd and RRB.CURRVERSION = bgl.EhrRus2m69i03gtdVer
				) ehr
				outer apply (
					select top 1 er.EhrRusID
					from EHR.T_EHR_RUS_9A9G09E8KL er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.CodeEHR = bgl.EhrRus9a9g09e8kl and RRB.CURRVERSION = bgl.EhrRus9a9g09e8klVer
				) ehr1
				outer apply (
					select top 1 er.EhrRusID
					from EHR.T_EHR_RUS_CLP14FVIRJ er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = bgl.EhrRusClp14fvirj and RRB.CURRVERSION = bgl.EhrRusClp14fvirjVer
				) ehr2
				outer apply (
					select top 1 er.EhrRusID
					from EHR.T_EHR_RUS_H17F8UTS3N er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = bgl.EhrRusH17F8UTS3N and RRB.CURRVERSION = bgl.EhrRusH17F8UTS3NVer
				) ehr3
				outer apply (
					select top 1 er.EhrRusID
					from EHR.T_EHR_RUS_DSG207 er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = bgl.EhrRusDsg207 and RRB.CURRVERSION = bgl.EhrRusDsg207Ver
				) ehr4
            where not exists
                (
                    select *
                    from [PHARM].T_BILL_OF_GOODS bg
                    where bg.BillOfGoodsID = bgl.ID
                )

        END
        ELSE
            BEGIN
                -- Generate table #BILL_OF_GOODS
                select t.c.value('./header[1]/ID[1]', 'uniqueidentifier')                        as [ID]
                     , t.c.value('./body[1]/DoseView[1]', 'varchar(1000)')                       as [DoseView]
                     , REPLACE(t.c.value('./body[1]/DoseView[1]', 'varchar(1000)'), ' ', '')     as [DoseViewRep]
                     , t.c.value('./body[1]/OtherID[1]', 'int')                                  as [OtherID]
                     , t.c.value('./body[1]/IsVital[1]', 'bit')                                  as [IsVital]
                     , t.c.value('./body[1]/Active[1]', 'bit')                                   as [Active]
                     , t.c.value('./body[1]/Rate[1]', 'int')                                     as [Rate]
                     , t.c.value('./body[1]/ExportCode[1]', 'int')                               as [ExportCode]
                     , t.c.value('./body[1]/PackQuantity[1]', 'int')                             as [PackQuantity]
                     , t.c.value('./body[1]/FederalID[1]', 'int')                                as [FederalID]
                     , t.c.value('./body[1]/MedicamentProducer[1]', 'uniqueidentifier')          as [MedicamentProducer]
                     , t.c.value('./body[1]/CTrn[1]', 'uniqueidentifier')                        as [CTrn]
                     , t.c.value('./body[1]/MedicationForms[1]', 'uniqueidentifier')             as [MedicationForms]
                     , t.c.value('./body[1]/ProductInfo[1]', 'varchar(max)')                     as [ProductInfo]
                     , t.c.value('./body[1]/Dlo[1]', 'bit')                                      as [Dlo]
                     , t.c.value('./body[1]/RoundToWriteOff[1]', 'bit')                          as [RoundToWriteOff]
                     , t.c.value('./body[1]/Gtin[1]', 'varchar(14)')                             as [Gtin]
                     , t.c.value('./body[1]/EAN[1]', 'varchar(255)')                             as [EAN]
                     , t.c.value('./body[1]/MedicamentsOfRoszdravnadzor[1]',
                                 'uniqueidentifier')                                             as [MedicamentsOfRoszdravnadzor]
                     , 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]/EhrRus2m69i03gtd[1]', 'int')                         as EhrRus2m69i03gtd
                     , t.c.value('./body[1]/EhrRus2m69i03gtdVer[1]', 'varchar(8)')               as EhrRus2m69i03gtdVer
                     , t.c.value('./body[1]/EhrRus9a9g09e8kl[1]', 'varchar(52)')                 as EhrRus9a9g09e8kl
                     , t.c.value('./body[1]/EhrRus9a9g09e8klVer[1]', 'varchar(8)')               as EhrRus9a9g09e8klVer
                     , t.c.value('./body[1]/EhrRusClp14fvirj[1]', 'int')                         as EhrRusClp14fvirj
                     , t.c.value('./body[1]/EhrRusClp14fvirjVer[1]', 'varchar(8)')               as EhrRusClp14fvirjVer
                     , t.c.value('./body[1]/EhrRusH17F8UTS3N[1]', 'int')                         as EhrRusH17F8UTS3N
                     , t.c.value('./body[1]/EhrRusH17F8UTS3NVer[1]', 'varchar(8)')               as EhrRusH17F8UTS3NVer
                     , t.c.value('./body[1]/EhrRusDsg207[1]', 'int')                             as EhrRusDsg207
                     , t.c.value('./body[1]/EhrRusDsg207Ver[1]', 'varchar(8)')                   as EhrRusDsg207Ver
                INTO #BILL_OF_GOODS
                from @Xml.nodes('BILL_OF_GOODS') T(c)


                if (@OperationType = @DELETE)
                    BEGIN
                        -- Удаление
                        update bf
                        set bf.Active = 0
                        from [PHARM].[T_BILL_OF_GOODS] bf
                                 join #BILL_OF_GOODS nbg on nbg.[ID] = bf.BillOfGoodsID
                    END
                else
                    BEGIN
                        -- Первичная загрузка
                        -- Загружаем новые DoseView
                        INSERT INTO [PHARM].[S_DLO_DoseView] ([Value])
                        SELECT bgl.DoseView
                        FROM #BILL_OF_GOODS bgl
                        where bgl.DoseView is not null
                          and not exists
                            (select *
                             from #PharmDV dv
                             where dv.Value = bgl.DoseViewRep)
                        group by bgl.DoseView

                        truncate table #PharmDV
                        insert into #PharmDV(Value, Id)
                        select REPLACE(dv.Value, ' ', '') as [value], MAX(dv.Id) as Id
                        from PHARM.S_DLO_DoseView dv
                        group by REPLACE(dv.Value, ' ', '')

                        -- Обновляем TRN
                        update bf
                        set bf.DoseView                    = dv.Id
                          , bf.OtherID                     = bgl.OtherID
                          , bf.IsVital                     = ISNULL(bgl.IsVital, 0)
                          , bf.Active                      = bgl.Active
                          , bf.Rate                        = ISNULL(bgl.Rate, 1)
                          , bf.ExportCode                  = bgl.ExportCode
                          , bf.PackQuantity                = bgl.PackQuantity
                          , bf.FederalID                   = bgl.FederalID
                          , bf.MedicamentProducer          = bgl.MedicamentProducer
                          , bf.CTrn                        = bgl.CTrn
                          , bf.MedicationForms             = bgl.MedicationForms
                          , bf.ProductInfo                 = bgl.ProductInfo
                          , bf.Dlo                         = ISNULL(bgl.Dlo, 0)
                          , bf.RoundToWriteOff             = ISNULL(bgl.RoundToWriteOff, 0)
                          , bf.Gtin                        = bgl.Gtin
                          , bf.EAN                         = bgl.EAN
                          , bf.MedicamentsOfRoszdravnadzor = bgl.MedicamentsOfRoszdravnadzor
                          , bf.RegCode                     = bgl.RegCode
                          , bf.FedCode                     = bgl.FedCode
                          , bf.EhrRus2m69i03gtd            = ehr.EhrRusID
                          , bf.EhrRus9a9g09e8kl            = ehr1.EhrRusID
                          , bf.EhrRusClp14fvirj            = ehr2.EhrRusID
                          , bf.EhrRusH17F8UTS3N            = ehr3.EhrRusID
                          , bf.EhrRusDsg207                = ehr4.EhrRusID
                        from [PHARM].T_BILL_OF_GOODS bf
                                 join #BILL_OF_GOODS bgl on bgl.ID = bf.BillOfGoodsID
                                 left join #PharmDV dv on dv.Value = bgl.DoseViewRep
                                 outer apply (select top 1 er.EhrRusID
                                              from EHR.T_EHR_RUS_2M69I03GTD er
                                                       LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
                                              where er.IdEHR = bgl.EhrRus2m69i03gtd
                                                and RRB.CURRVERSION = bgl.EhrRus2m69i03gtdVer) ehr
                                 outer apply (select top 1 er.EhrRusID
                                              from EHR.T_EHR_RUS_9A9G09E8KL er
                                                       LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
                                              where er.CodeEHR = bgl.EhrRus9a9g09e8kl
                                                and RRB.CURRVERSION = bgl.EhrRus9a9g09e8klVer) ehr1
                                 outer apply (select top 1 er.EhrRusID
                                              from EHR.T_EHR_RUS_CLP14FVIRJ er
                                                       LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
                                              where er.IdEHR = bgl.EhrRusClp14fvirj
                                                and RRB.CURRVERSION = bgl.EhrRusClp14fvirjVer) ehr2
                                 outer apply (select top 1 er.EhrRusID
                                              from EHR.T_EHR_RUS_H17F8UTS3N er
                                                       LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
                                              where er.IdEHR = bgl.EhrRusH17F8UTS3N
                                                and RRB.CURRVERSION = bgl.EhrRusH17F8UTS3NVer) ehr3
                                 outer apply (select top 1 er.EhrRusID
                                              from EHR.T_EHR_RUS_DSG207 er
                                                       LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
                                              where er.IdEHR = bgl.EhrRusDsg207
                                                and RRB.CURRVERSION = bgl.EhrRusDsg207Ver) ehr4
                        where @OperationType = @UPDATE

                        -- Загружаем новые TRN
                        INSERT INTO [PHARM].[T_BILL_OF_GOODS]
                        ( [BillOfGoodsID]
                        , [CTrn]
                        , [Active]
                        , [MedicamentProducer]
                        , OtherID
                        , [MedicationForms]
                        , [DoseView]
                        , [Rate]
                        , [IsVital]
                        , [PackQuantity]
                        , ExportCode
                        , FederalID
                        , ProductInfo
                        , Dlo
                        , RoundToWriteOff
                        , Gtin
                        , EAN
                        , MedicamentsOfRoszdravnadzor
                        , RegCode
                        , FedCode
                        , EhrRus2m69i03gtd
                        , EhrRus9a9g09e8kl
                        , EhrRusClp14fvirj
                        , EhrRusH17F8UTS3N
                        , EhrRusDsg207)
                        SELECT bgl.ID
                             , bgl.CTrn
                             , bgl.Active
                             , bgl.MedicamentProducer
                             , bgl.OtherID
                             , bgl.MedicationForms
                             , dv.Id
                             , ISNULL(bgl.Rate, 1)
                             , ISNULL(bgl.IsVital, 0)
                             , bgl.PackQuantity
                             , bgl.ExportCode
                             , bgl.FederalID
                             , bgl.ProductInfo
                             , ISNULL(bgl.Dlo, 0)
                             , ISNULL(bgl.RoundToWriteOff, 0)
                             , bgl.Gtin
                             , bgl.EAN
                             , MedicamentsOfRoszdravnadzor
                             , bgl.RegCode
                             , bgl.FedCode
                             , ehr.EhrRusID
                             , ehr1.EhrRusID
                             , ehr2.EhrRusID
                             , ehr3.EhrRusID
                             , ehr4.EhrRusID
                        from #BILL_OF_GOODS bgl
                                 left join #PharmDV dv on dv.Value = bgl.DoseViewRep
                                 outer apply (select top 1 er.EhrRusID
                                              from EHR.T_EHR_RUS_2M69I03GTD er
                                                       LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
                                              where er.IdEHR = bgl.EhrRus2m69i03gtd
                                                and RRB.CURRVERSION = bgl.EhrRus2m69i03gtdVer) ehr
                                 outer apply (select top 1 er.EhrRusID
                                              from EHR.T_EHR_RUS_9A9G09E8KL er
                                                       LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
                                              where er.CodeEHR = bgl.EhrRus9a9g09e8kl
                                                and RRB.CURRVERSION = bgl.EhrRus9a9g09e8klVer) ehr1
                                 outer apply (select top 1 er.EhrRusID
                                              from EHR.T_EHR_RUS_CLP14FVIRJ er
                                                       LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
                                              where er.IdEHR = bgl.EhrRusClp14fvirj
                                                and RRB.CURRVERSION = bgl.EhrRusClp14fvirjVer) ehr2
                                 outer apply (select top 1 er.EhrRusID
                                              from EHR.T_EHR_RUS_H17F8UTS3N er
                                                       LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
                                              where er.IdEHR = bgl.EhrRusH17F8UTS3N
                                                and RRB.CURRVERSION = bgl.EhrRusH17F8UTS3NVer) ehr3
                                 outer apply (select top 1 er.EhrRusID
                                              from EHR.T_EHR_RUS_DSG207 er
                                                       LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
                                              where er.IdEHR = bgl.EhrRusDsg207
                                                and RRB.CURRVERSION = bgl.EhrRusDsg207Ver) ehr4
                        where not exists
                            (select *
                             from [PHARM].T_BILL_OF_GOODS bg
                             where bg.BillOfGoodsID = bgl.ID)
                          and @OperationType = @INSERT
                    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 = 'BILL_OF_GOODS'

		IF(exists (select 1 from [VCLib].T_GEN_NUMBERS where  EntityID = @EntId and NumberName = 'PHARM.BILL_OF_GOODS'))
		BEGIN
			-- генерируем след. номер
			--exec [dbo].sp_GenerateNumber @EntId, 'PHARM.BILL_OF_GOODS'
			update gn
			set gn.NextNumber = (select MAX(OtherID) from [PHARM].T_BILL_OF_GOODS)
			from [VCLib].T_GEN_NUMBERS gn
			where gn.EntityID = @EntId
					and
				gn.NumberName = 'PHARM.BILL_OF_GOODS'
		END
		ELSE
		BEGIN

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

		END


    END
END
GO