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_BillOfGoodsLinkPT]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_RDS_BillOfGoodsLinkPT]
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2017-12-21
-- Description: Выгрузка/загрузка справочника  в(из) RDS
-- =============================================
CREATE PROCEDURE [dbo].[sp_RDS_BillOfGoodsLinkPT]
     @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_PAYMENT_TYPE'
            -- Operation types
          ,@INSERT = 'Insert'
          ,@UPDATE = 'Update'
          ,@DELETE = 'Delete'
          ,@FTLOAD = 'FTLoad'

IF @PATH IS NOT NULL
	BEGIN
CREATE TABLE #T_BILL_OF_GOODS_LINK_PAYMENT_TYPE
	(
		 BillOfGoodsLinkPaymentTypeID	UNIQUEIDENTIFIER
		,BillOfGoods					UNIQUEIDENTIFIER
		,PaymentType					INT
		,DateBegin						DATETIME
		,DateEnd						DATETIME
	)
SET @SQL = 'INSERT INTO tempdb..#T_BILL_OF_GOODS_LINK_PAYMENT_TYPE
			SELECT
				 BillOfGoodsLinkPaymentTypeID
				,BillOfGoods
				,PaymentType
				,DateBegin
				,DateEnd
			FROM ' + @PATH + '.[PHARM].T_BILL_OF_GOODS_LINK_PAYMENT_TYPE'
EXECUTE (@SQL)

	DELETE from [PHARM].T_BILL_OF_GOODS_LINK_PAYMENT_TYPE

	-- Загружаем новые
	INSERT INTO [PHARM].[T_BILL_OF_GOODS_LINK_PAYMENT_TYPE]
			(
				 BillOfGoodsLinkPaymentTypeID
				,BillOfGoods
				,PaymentType
				,DateBegin
				,DateEnd
			)
	SELECT
	           nmf.BillOfGoodsLinkPaymentTypeID,
	           nmf.BillOfGoods,
			   nmf.PaymentType,
			   nmf.DateBegin,
			   nmf.DateEnd
	FROM #T_BILL_OF_GOODS_LINK_PAYMENT_TYPE nmf

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

        -- Обработка операции
        IF (@OperationType = @FTLOAD)
        BEGIN
            -- Первичная загрузка
            select @Result =
            (
                select @FTLOAD						as 'header/TYPE'
                      ,(
                            SELECT  s.BillOfGoodsLinkPaymentTypeID				as 'BILL_OF_GOODS_LINK_PAYMENT_TYPE/ID'
									,s.BillOfGoods								as 'BILL_OF_GOODS_LINK_PAYMENT_TYPE/BillOfGoods'
									,convert(varchar(10), s.DateBegin, 104)		as 'BILL_OF_GOODS_LINK_PAYMENT_TYPE/DateBegin'
									,convert(varchar(10), s.DateEnd, 104)		as 'BILL_OF_GOODS_LINK_PAYMENT_TYPE/DateEnd'
									,s.PaymentType								as 'BILL_OF_GOODS_LINK_PAYMENT_TYPE/PaymentType'
							FROM [PHARM].T_BILL_OF_GOODS_LINK_PAYMENT_TYPE s
								for xml path(''),
								type
                      ) as 'body/BILL_OF_GOODS_LINK_PAYMENT_TYPES'
                for xml path(''),
                root('BILL_OF_GOODS_LINK_PAYMENT_TYPE')
            )
        END
        ELSE IF(@OperationType <> @DELETE)
        BEGIN

            -- собираем BILL_OF_GOODS_LINK_PAYMENT_TYPE
            SELECT @Result =
            (
                select @OperationType							AS 'header/TYPE'
                      ,@ID										AS 'header/ID'
                      ,s.BillOfGoods							AS 'body/BillOfGoods'
					  ,convert(varchar(10), s.DateBegin, 104)	AS 'body/DateBegin'
					  ,convert(varchar(10), s.DateEnd, 104)		AS 'body/DateEnd'
					  ,s.PaymentType							AS 'body/PaymentType'
                from [PHARM].T_BILL_OF_GOODS_LINK_PAYMENT_TYPE s
					where s.BillOfGoodsLinkPaymentTypeID = @ID
                for xml path(''),
                root('BILL_OF_GOODS_LINK_PAYMENT_TYPE')
            )
        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_PAYMENT_TYPE') T(c)

        -- Обработка операции
        if (@OperationType = @FTLOAD)
        BEGIN
            -- Generate table #BILL_OF_GOODS_LINK_PAYMENT_TYPES
            SELECT t.c.value('./ID[1]', 'uniqueidentifier')                           AS ID,
                   t.c.value('./BillOfGoods[1]', 'uniqueidentifier')                  AS BillOfGoods,
                   CONVERT(DATETIME, t.c.value('./DateBegin[1]', 'varchar(10)'), 104) AS DateBegin,
                   CONVERT(DATETIME, t.c.value('./DateEnd[1]', 'varchar(10)'), 104)   AS DateEnd,
                   t.c.value('./PaymentType[1]', 'int')                               AS PaymentType
            INTO #BILL_OF_GOODS_LINK_PAYMENT_TYPES
            FROM @Xml.nodes('BILL_OF_GOODS_LINK_PAYMENT_TYPE/body/BILL_OF_GOODS_LINK_PAYMENT_TYPES/BILL_OF_GOODS_LINK_PAYMENT_TYPE') T(c)

            delete from [PHARM].T_BILL_OF_GOODS_LINK_PAYMENT_TYPE

            -- Загружаем новые
            INSERT INTO [PHARM].[T_BILL_OF_GOODS_LINK_PAYMENT_TYPE]
                       (BillOfGoodsLinkPaymentTypeID,BillOfGoods,PaymentType,DateBegin,DateEnd)
            SELECT
                       nmf.ID,
                       nmf.BillOfGoods,
					   nmf.PaymentType,
					   nmf.DateBegin,
					   nmf.DateEnd
            from #BILL_OF_GOODS_LINK_PAYMENT_TYPES nmf
        END
        ELSE
        BEGIN
            -- Generate table #BILL_OF_GOODS_LINK_PAYMENT_TYPE
            SELECT T.c.value('./header[1]/ID[1]', 'uniqueidentifier')                         AS ID,
                   t.c.value('./body[1]/BillOfGoods[1]', 'uniqueidentifier')                  AS BillOfGoods,
                   CONVERT(DATETIME, t.c.value('./body[1]/DateBegin[1]', 'varchar(10)'), 104) AS DateBegin,
                   CONVERT(DATETIME, t.c.value('./body[1]/DateEnd[1]', 'varchar(10)'), 104)   AS DateEnd,
                   t.c.value('./body[1]/CertificateNumber[1]', 'int')                         AS PaymentType
            INTO #BILL_OF_GOODS_LINK_PAYMENT_TYPE
            FROM @Xml.nodes('BILL_OF_GOODS_LINK_PAYMENT_TYPE') T(c)

            IF(@OperationType = @DELETE)
            BEGIN
                -- Удаление
                delete s
                from [PHARM].[T_BILL_OF_GOODS_LINK_PAYMENT_TYPE] s
                    join #BILL_OF_GOODS_LINK_PAYMENT_TYPE nmf on nmf.[ID] = s.BillOfGoodsLinkPaymentTypeID
            END
            else
            BEGIN
                -- update [PHARM].T_BILL_OF_GOODS_LINK_PAYMENT_TYPE
                update s
                set s.BillOfGoods = nmf.BillOfGoods
                   ,s.PaymentType = nmf.PaymentType
                   ,s.DateBegin = nmf.DateBegin
                   ,s.DateEnd = nmf.DateEnd
                from [PHARM].T_BILL_OF_GOODS_LINK_PAYMENT_TYPE s
                    join #BILL_OF_GOODS_LINK_PAYMENT_TYPE nmf on nmf.ID = s.BillOfGoodsLinkPaymentTypeID
                where @OperationType = @UPDATE

                -- insert [PHARM].T_BILL_OF_GOODS_LINK_PAYMENT_TYPE
                INSERT INTO [PHARM].[T_BILL_OF_GOODS_LINK_PAYMENT_TYPE]
                    (BillOfGoodsLinkPaymentTypeID,BillOfGoods,PaymentType,DateBegin,DateEnd)
                SELECT
                           nmf.ID,
                           nmf.BillOfGoods,
                           nmf.PaymentType,
                           nmf.DateBegin,
                           nmf.DateEnd
                from #BILL_OF_GOODS_LINK_PAYMENT_TYPE nmf
                where  @OperationType = @INSERT
            END
        END
    END
END
END
GO