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_BGDoseViews]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_RDS_BGDoseViews]
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2017-12-21
-- Description: Выгрузка/загрузка справочника дозировок в(из) RDS
-- =============================================
CREATE PROCEDURE [dbo].[sp_RDS_BGDoseViews]
     @Xml xml = null
    ,@IsNew bit = 1
    ,@ID uniqueidentifier = null
    ,@QueueName varchar(100) = null
    ,@dateTime datetime = null
    ,@User uniqueidentifier = null
	,@PATH VARCHAR(MAX) = NULL

AS
BEGIN

SET NOCOUNT ON;

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

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

IF @PATH IS NOT NULL
	BEGIN 
		
IF (SELECT OBJECT_ID('tempdb..#TEMP_T_BILL_OF_GOODS_LINK_DOSE_VIEW')) IS NOT NULL
	DROP TABLE #TEMP_T_BILL_OF_GOODS_LINK_DOSE_VIEW
CREATE TABLE #TEMP_T_BILL_OF_GOODS_LINK_DOSE_VIEW
	(
		 BillOfGoodsLinkDoseViewID	UNIQUEIDENTIFIER
		,DoseView					INT
		,BillOfGoods				UNIQUEIDENTIFIER
		,Val						DECIMAL
		,OrderNum					INT
		,AdditionalSymbol			VARCHAR(10)
		,ValText					VARCHAR(1000)
	)

SET @SQL = 'INSERT INTO tempdb..#TEMP_T_BILL_OF_GOODS_LINK_DOSE_VIEW
			SELECT 
				 BillOfGoodsLinkDoseViewID
				,DoseView
				,BillOfGoods
				,Val
				,OrderNum
				,AdditionalSymbol
				,ValText
			FROM ' + @PATH + '.PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW'
EXECUTE (@SQL)

	DELETE S
	FROM [PHARM].T_BILL_OF_GOODS_LINK_DOSE_VIEW s
	WHERE NOT EXISTS
	    (
	        SELECT *
				FROM #TEMP_T_BILL_OF_GOODS_LINK_DOSE_VIEW nmf
	        WHERE nmf.BillOfGoodsLinkDoseViewID = s.BillOfGoodsLinkDoseViewID
	    )
	
	-- Обновляем имеющиеся 
	UPDATE S
	SET s.DoseView = nmf.DoseView
		,s.BillOfGoods = nmf.BillOfGoods
		,s.Val = nmf.Val
		,s.OrderNum = nmf.OrderNum
		,s.AdditionalSymbol = nmf.AdditionalSymbol
		,s.ValText = nmf.ValText
	FROM [PHARM].T_BILL_OF_GOODS_LINK_DOSE_VIEW s
	    join #TEMP_T_BILL_OF_GOODS_LINK_DOSE_VIEW nmf on nmf.BillOfGoodsLinkDoseViewID = s.BillOfGoodsLinkDoseViewID
	
	-- Загружаем новые
	INSERT INTO [PHARM].[T_BILL_OF_GOODS_LINK_DOSE_VIEW]
			(
			    [BillOfGoodsLinkDoseViewID]
			   ,[DoseView]
			   ,[BillOfGoods]
			   ,[Val]
			   ,[OrderNum]
			   ,[AdditionalSymbol]
			   ,[ValText]
			)
	SELECT
	           nmf.BillOfGoodsLinkDoseViewID,
	           nmf.DoseView,
			   nmf.BillOfGoods,
			   nmf.Val,
			   nmf.OrderNum,
			   nmf.AdditionalSymbol,
			   nmf.ValText
	FROM #TEMP_T_BILL_OF_GOODS_LINK_DOSE_VIEW nmf
	WHERE NOT EXISTS
	    (
	        SELECT *
				FROM [PHARM].T_BILL_OF_GOODS_LINK_DOSE_VIEW s
	        WHERE s.BillOfGoodsLinkDoseViewID = nmf.BillOfGoodsLinkDoseViewID
	    )

	END
	ELSE
	BEGIN

    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_BILL_OF_GOODS_LINK_DOSE_VIEW s where s.BillOfGoodsLinkDoseViewID = @ID))
        BEGIN
            select @OperationType = @UPDATE
        END
        ELSE IF (@IsNew = 0 and not exists (select * from [PHARM].T_BILL_OF_GOODS_LINK_DOSE_VIEW s where s.BillOfGoodsLinkDoseViewID = @ID))
        BEGIN
            select @OperationType = @DELETE
            select @Result = 
            (
                select @OperationType as 'header/TYPE'
                      ,@ID            as 'header/ID'
                FOR XML PATH(''),
                ROOT('BILL_OF_GOODS_LINK_DOSE_VIEW')
            ) 
        END

        -- Обработка операции
        IF (@OperationType = @FTLOAD)
        BEGIN
            -- Первичная загрузка
            select @Result = 
            (
                select @FTLOAD										as 'header/TYPE'
                      ,(
                            select 
							 dgdv.BillOfGoodsLinkDoseViewID			as 'BILL_OF_GOODS_LINK_DOSE_VIEW/ID'
							,dgdv.DoseView							as 'BILL_OF_GOODS_LINK_DOSE_VIEW/DoseView'
							,dgdv.BillOfGoods						as 'BILL_OF_GOODS_LINK_DOSE_VIEW/BillOfGoods'
							,dgdv.Val								as 'BILL_OF_GOODS_LINK_DOSE_VIEW/Val'
							,dgdv.ValText							as 'BILL_OF_GOODS_LINK_DOSE_VIEW/ValText'
                            ,dgdv.OrderNum							as 'BILL_OF_GOODS_LINK_DOSE_VIEW/OrderNum'
                            ,dgdv.AdditionalSymbol					as 'BILL_OF_GOODS_LINK_DOSE_VIEW/AdditionalSymbol'
                            from [PHARM].T_BILL_OF_GOODS_LINK_DOSE_VIEW dgdv
                            for xml path(''),
                            type
                      ) as 'body/BILL_OF_GOODS_LINK_DOSE_VIEWS'
                for xml path(''),
                root('BILL_OF_GOODS_LINK_DOSE_VIEW')
            )
        END
        ELSE IF(@OperationType <> @DELETE)
        BEGIN

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

					  ,dgdv.DoseView						as 'body/DoseView'
					  ,dgdv.BillOfGoods						as 'body/BillOfGoods'
					  ,dgdv.Val								as 'body/Val'
					  ,dgdv.ValText							as 'body/ValText'
                      ,dgdv.OrderNum						as 'body/OrderNum'
                      ,dgdv.AdditionalSymbol				as 'body/AdditionalSymbol'
                from [PHARM].T_BILL_OF_GOODS_LINK_DOSE_VIEW dgdv
                where dgdv.BillOfGoodsLinkDoseViewID = @ID
                for xml path(''),
                root('BILL_OF_GOODS_LINK_DOSE_VIEW')
            ) 
        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(6)')
        from @Xml.nodes('BILL_OF_GOODS_LINK_DOSE_VIEW') T(c)

        -- Generate table #BILL_OF_GOODS_LINK_DOSE_VIEWS
        select t.c.value('./ID[1]', 'uniqueidentifier')					as [ID]
			  ,t.c.value('./DoseView[1]', 'int')						as [DoseView]	
			  ,t.c.value('./BillOfGoods[1]', 'uniqueidentifier')		as [BillOfGoods]
			  ,t.c.value('./Val[1]', 'decimal(31,10)')					as [Val]	
			  ,t.c.value('./ValText[1]', 'varchar(1000)')			    as [ValText]	
			  ,t.c.value('./OrderNum[1]', 'int')						as [OrderNum]	
			  ,t.c.value('./AdditionalSymbol[1]', 'varchar(1)')			as [AdditionalSymbol]	
            INTO #BILL_OF_GOODS_LINK_DOSE_VIEWS
        from @Xml.nodes('BILL_OF_GOODS_LINK_DOSE_VIEW/body/BILL_OF_GOODS_LINK_DOSE_VIEWS/BILL_OF_GOODS_LINK_DOSE_VIEW') T(c)
        where @OperationType = @FTLOAD

        -- Generate table #BILL_OF_GOODS_LINK_DOSE_VIEW
        select t.c.value('./header[1]/ID[1]', 'uniqueidentifier')				as [ID]
			  ,t.c.value('./body[1]/DoseView[1]', 'int')						as [DoseView]	
			  ,t.c.value('./body[1]/BillOfGoods[1]', 'uniqueidentifier')		as [BillOfGoods]
			  ,t.c.value('./body[1]/Val[1]', 'decimal(31,10)')					as [Val]	
			  ,t.c.value('./body[1]/ValText[1]', 'varchar(1000)')			    as [ValText]
			  ,t.c.value('./body[1]/OrderNum[1]', 'int')						as [OrderNum]	
			  ,t.c.value('./body[1]/AdditionalSymbol[1]', 'varchar(1)')			as [AdditionalSymbol]	
            INTO #BILL_OF_GOODS_LINK_DOSE_VIEW
        from @Xml.nodes('BILL_OF_GOODS_LINK_DOSE_VIEW') T(c)
        where @OperationType <> @FTLOAD

        -- Обработка операции
        if (@OperationType = @FTLOAD)
        BEGIN
            delete s
            from [PHARM].T_BILL_OF_GOODS_LINK_DOSE_VIEW s
            where not exists
                (
                    select *
                    from #BILL_OF_GOODS_LINK_DOSE_VIEWS nmf
                    where nmf.ID = s.BillOfGoodsLinkDoseViewID
                )

            -- Обновляем имеющиеся 
            update s
            set s.DoseView = nmf.DoseView
				,s.BillOfGoods = nmf.BillOfGoods
				,s.Val = nmf.Val
				,s.OrderNum = nmf.OrderNum
				,s.AdditionalSymbol = nmf.AdditionalSymbol
				,s.ValText = nmf.ValText
            from [PHARM].T_BILL_OF_GOODS_LINK_DOSE_VIEW s
                join #BILL_OF_GOODS_LINK_DOSE_VIEWS nmf on nmf.ID = s.BillOfGoodsLinkDoseViewID

            -- Загружаем новые
            INSERT INTO [PHARM].[T_BILL_OF_GOODS_LINK_DOSE_VIEW]
                       (BillOfGoodsLinkDoseViewID, DoseView, BillOfGoods, Val, OrderNum, AdditionalSymbol,ValText)
            SELECT
                       nmf.ID,
                       nmf.DoseView,
					   nmf.BillOfGoods,
					   nmf.Val,
					   nmf.OrderNum,
					   nmf.AdditionalSymbol,
					   nmf.ValText
            from #BILL_OF_GOODS_LINK_DOSE_VIEWS nmf
            where not exists
                (
                    select *
                    from [PHARM].T_BILL_OF_GOODS_LINK_DOSE_VIEW s
                    where s.BillOfGoodsLinkDoseViewID = nmf.ID
                )
        END
        else if(@OperationType = @DELETE)
        BEGIN
            -- Удаление
            delete s
            from [PHARM].[T_BILL_OF_GOODS_LINK_DOSE_VIEW] s
                join #BILL_OF_GOODS_LINK_DOSE_VIEW nmf on nmf.[ID] = s.BillOfGoodsLinkDoseViewID
        END
        else
        BEGIN
            -- update [PHARM].T_BILL_OF_GOODS_LINK_DOSE_VIEW
            update s
            set s.DoseView = nmf.DoseView
				,s.BillOfGoods = nmf.BillOfGoods
				,s.Val = nmf.Val
				,s.OrderNum = nmf.OrderNum
				,s.AdditionalSymbol = nmf.AdditionalSymbol
				,s.ValText = nmf.ValText
            from [PHARM].T_BILL_OF_GOODS_LINK_DOSE_VIEW s
                join #BILL_OF_GOODS_LINK_DOSE_VIEW nmf on nmf.ID = s.BillOfGoodsLinkDoseViewID
            where @OperationType = @UPDATE

            -- insert [PHARM].T_BILL_OF_GOODS_LINK_DOSE_VIEW
            INSERT INTO [PHARM].[T_BILL_OF_GOODS_LINK_DOSE_VIEW]
                       (BillOfGoodsLinkDoseViewID, DoseView, BillOfGoods, Val, OrderNum, AdditionalSymbol,ValText)
            SELECT
                       nmf.ID,
                       nmf.DoseView,
					   nmf.BillOfGoods,
					   nmf.Val,
					   nmf.OrderNum,
					   nmf.AdditionalSymbol,
					   nmf.ValText
            from #BILL_OF_GOODS_LINK_DOSE_VIEW nmf
            where not exists
                (
                    select *
                    from [PHARM].T_BILL_OF_GOODS_LINK_DOSE_VIEW s
                    where s.BillOfGoodsLinkDoseViewID = nmf.ID
                )
                    and
                @OperationType = @INSERT
        END

        -- Drop tables
        DROP TABLE #BILL_OF_GOODS_LINK_DOSE_VIEW
        DROP TABLE #BILL_OF_GOODS_LINK_DOSE_VIEWS

    END
END
END
GO