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

IF @PATH IS NOT NULL
	BEGIN
CREATE TABLE #T_PRODUCT_FORM
	(
		 ProductFormID		UNIQUEIDENTIFIER
		,OrdinalNumber		INT
		,PackageType		UNIQUEIDENTIFIER
		,Amount				DECIMAL(12,2)
		,BillOfGoods		UNIQUEIDENTIFIER
		,UnitPrice			BIT
		,WriteOff			BIT
		,Descr				VARCHAR(50)
		,NotUseInPackage	BIT
		,Appointment	BIT
	)
SET @SQL = 'INSERT INTO tempdb..#T_PRODUCT_FORM
			SELECT
				 ProductFormID
				,OrdinalNumber
				,PackageType
				,Amount
				,BillOfGoods
				,UnitPrice
				,WriteOff
				,Descr
				,NotUseInPackage
				,Appointment
			FROM ' + @PATH + '.[PHARM].T_PRODUCT_FORM'
EXECUTE (@SQL)

	DELETE pf
		FROM [PHARM].T_PRODUCT_FORM pf
	WHERE NOT EXISTS
	    (
	        SELECT *
				FROM #T_PRODUCT_FORM nmf
	        WHERE nmf.ProductFormID = pf.ProductFormID
	    )

	-- Обновляем имеющиеся
	update pf
	set pf.OrdinalNumber = nmf.OrdinalNumber
	   ,pf.Amount = nmf.Amount
	   ,pf.UnitPrice = nmf.UnitPrice
	   ,pf.WriteOff = nmf.WriteOff
	   ,pf.PackageType = nmf.PackageType
	   ,pf.BillOfGoods = nmf.BillOfGoods
	   ,pf.Descr = nmf.Descr
	   ,pf.NotUseInPackage = ISNULL(nmf.NotUseInPackage,0)
	   ,pf.Appointment = ISNULL(nmf.Appointment,0)
	from [PHARM].T_PRODUCT_FORM pf
	    join #T_PRODUCT_FORM nmf on nmf.ProductFormID = pf.ProductFormID

	-- Загружаем новые
	INSERT INTO [PHARM].[T_PRODUCT_FORM]
		(
			 [ProductFormID]
			,[OrdinalNumber]
			,[Amount]
			,[UnitPrice]
			,[WriteOff]
			,[PackageType]
			,[BillOfGoods]
			,[Descr]
			,[NotUseInPackage]
			,[Appointment]
		)
	SELECT
	           nmf.ProductFormID,
			   nmf.OrdinalNumber,
			   nmf.Amount,
			   nmf.UnitPrice,
			   nmf.WriteOff,
			   nmf.PackageType,
			   nmf.BillOfGoods,
			   nmf.Descr,
	           ISNULL(nmf.NotUseInPackage,0),
	           ISNULL(nmf.Appointment,0)
	FROM #T_PRODUCT_FORM nmf
	WHERE NOT EXISTS
	    (
	        select *
	        from [PHARM].T_PRODUCT_FORM pf
	        where pf.ProductFormID = nmf.ProductFormID
	    )

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

        -- Обработка операции
        IF (@OperationType = @FTLOAD)
        BEGIN
            -- Первичная загрузка
            select @Result =
            (
                select @FTLOAD						as 'header/TYPE'
                      ,(
                            select pf.ProductFormID		as 'PRODUCT_FORM/ID'
                                  ,pf.OrdinalNumber		as 'PRODUCT_FORM/OrdinalNumber'
                                  ,pf.Amount			as 'PRODUCT_FORM/Amount'
								  ,pf.UnitPrice			as 'PRODUCT_FORM/UnitPrice'
								  ,pf.WriteOff			as 'PRODUCT_FORM/WriteOff'
								  ,pf.PackageType		as 'PRODUCT_FORM/PackageType'
								  ,pf.BillOfGoods		as 'PRODUCT_FORM/BillOfGoods'
								  ,pf.Descr				as 'PRODUCT_FORM/Descr'
								  ,pf.NotUseInPackage	as 'PRODUCT_FORM/NotUseInPackage'
								  ,pf.Appointment	as 'PRODUCT_FORM/Appointment'
                            from [PHARM].T_PRODUCT_FORM pf
                            for xml path(''),
                            type
                      ) as 'body/PRODUCT_FORMS'
                for xml path(''),
                root('PRODUCT_FORM')
            )
        END
        ELSE IF(@OperationType <> @DELETE)
        BEGIN

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

                      ,pf.OrdinalNumber		as 'body/OrdinalNumber'
                      ,pf.Amount			as 'body/Amount'
					  ,pf.UnitPrice			as 'body/UnitPrice'
					  ,pf.WriteOff			as 'body/WriteOff'
					  ,pf.PackageType		as 'body/PackageType'
					  ,pf.BillOfGoods		as 'body/BillOfGoods'
					  ,pf.Descr				as 'body/Descr'
					  ,pf.NotUseInPackage	as 'body/NotUseInPackage'
					  ,pf.Appointment	    as 'body/Appointment'
                from [PHARM].T_PRODUCT_FORM pf
                where pf.ProductFormID = @ID
                for xml path(''),
                root('PRODUCT_FORM')
            )
        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('PRODUCT_FORM') T(c)

        -- Обработка операции
        if (@OperationType = @FTLOAD)
        BEGIN
            -- Generate table #PRODUCT_FORM
            SELECT t.c.value('./ID[1]', 'uniqueidentifier')          AS ID,
                   t.c.value('./OrdinalNumber[1]', 'int')            AS OrdinalNumber,
                   t.c.value('./Amount[1]', 'decimal(12,2)')         AS Amount,
                   t.c.value('./UnitPrice[1]', 'bit')                AS UnitPrice,
                   t.c.value('./WriteOff[1]', 'bit')                 AS WriteOff,
                   t.c.value('./PackageType[1]', 'uniqueidentifier') AS PackageType,
                   t.c.value('./BillOfGoods[1]', 'uniqueidentifier') AS BillOfGoods,
                   t.c.value('./Descr[1]', 'varchar(500)')           AS Descr,
                   t.c.value('./NotUseInPackage[1]', 'bit')          AS NotUseInPackage,
                   t.c.value('./Appointment[1]', 'bit')              AS Appointment
            INTO #PRODUCT_FORMS
            FROM @Xml.nodes('PRODUCT_FORM/body/PRODUCT_FORMS/PRODUCT_FORM') T(c)

            delete pf
            from [PHARM].T_PRODUCT_FORM pf
            where not exists
                (
                    select *
                    from #PRODUCT_FORMS nmf
                    where nmf.ID = pf.ProductFormID
                )

            -- Обновляем имеющиеся
            update pf
            set pf.OrdinalNumber = nmf.OrdinalNumber
               ,pf.Amount = nmf.Amount
			   ,pf.UnitPrice = nmf.UnitPrice
			   ,pf.WriteOff = nmf.WriteOff
			   ,pf.PackageType = nmf.PackageType
			   ,pf.BillOfGoods = nmf.BillOfGoods
			   ,pf.Descr = nmf.Descr
			   ,pf.NotUseInPackage = ISNULL(nmf.NotUseInPackage,0)
			   ,pf.Appointment = ISNULL(nmf.Appointment,0)
            from [PHARM].T_PRODUCT_FORM pf
                join #PRODUCT_FORMS nmf on nmf.ID = pf.ProductFormID

            -- Загружаем новые
            INSERT INTO [PHARM].[T_PRODUCT_FORM]
                       (ProductFormID,OrdinalNumber,Amount,UnitPrice,WriteOff,PackageType,BillOfGoods,Descr,NotUseInPackage,Appointment)
            SELECT
                       nmf.ID,
					   nmf.OrdinalNumber,
					   nmf.Amount,
					   nmf.UnitPrice,
					   nmf.WriteOff,
					   nmf.PackageType,
					   nmf.BillOfGoods,
					   nmf.Descr,
                       ISNULL(nmf.NotUseInPackage,0),
                       ISNULL(nmf.Appointment,0)
            from #PRODUCT_FORMS nmf
            where not exists
                (
                    select *
                    from [PHARM].T_PRODUCT_FORM pf
                    where pf.ProductFormID = nmf.ID
                )
        END
        ELSE
        BEGIN
            -- Generate table #PRODUCT_FORM
            SELECT t.c.value('./header[1]/ID[1]', 'uniqueidentifier')        AS ID,
                   t.c.value('./body[1]/OrdinalNumber[1]', 'int')            AS OrdinalNumber,
                   t.c.value('./body[1]/Amount[1]', 'decimal(12,2)')         AS Amount,
                   t.c.value('./body[1]/UnitPrice[1]', 'bit')                AS UnitPrice,
                   t.c.value('./body[1]/WriteOff[1]', 'bit')                 AS WriteOff,
                   t.c.value('./body[1]/PackageType[1]', 'uniqueidentifier') AS PackageType,
                   t.c.value('./body[1]/BillOfGoods[1]', 'uniqueidentifier') AS BillOfGoods,
                   t.c.value('./body[1]/Descr[1]', 'varchar(500)')           AS Descr,
                   t.c.value('./body[1]/NotUseInPackage[1]', 'bit')          AS NotUseInPackage,
                   t.c.value('./body[1]/Appointment[1]', 'bit')              AS Appointment
            INTO #PRODUCT_FORM
            FROM @Xml.nodes('PRODUCT_FORM') T(c)

            IF(@OperationType = @DELETE)
            BEGIN
                -- Удаление
                delete pf
                from [PHARM].[T_PRODUCT_FORM] pf
                    join #PRODUCT_FORM nmf on nmf.[ID] = pf.ProductFormID
            END
            else
            BEGIN
                update pf
                set pf.OrdinalNumber = nmf.OrdinalNumber
                   ,pf.Amount = nmf.Amount
                   ,pf.UnitPrice = nmf.UnitPrice
                   ,pf.WriteOff = nmf.WriteOff
                   ,pf.PackageType = nmf.PackageType
                   ,pf.BillOfGoods = nmf.BillOfGoods
                   ,pf.Descr = nmf.Descr
                   ,pf.NotUseInPackage = ISNULL(nmf.NotUseInPackage,0)
                   ,pf.Appointment = ISNULL(nmf.Appointment,0)
                from [PHARM].T_PRODUCT_FORM pf
                    join #PRODUCT_FORM nmf on nmf.ID = pf.ProductFormID
                where @OperationType = @UPDATE

                -- insert [PHARM].T_PRODUCT_FORM
                INSERT INTO [PHARM].[T_PRODUCT_FORM]
                           (ProductFormID,OrdinalNumber,Amount,UnitPrice,WriteOff,PackageType,BillOfGoods,Descr,NotUseInPackage,Appointment)
                SELECT
                           nmf.ID,
                           nmf.OrdinalNumber,
                           nmf.Amount,
                           nmf.UnitPrice,
                           nmf.WriteOff,
                           nmf.PackageType,
                           nmf.BillOfGoods,
                           nmf.Descr,
                           ISNULL(nmf.NotUseInPackage,0),
                           ISNULL(nmf.Appointment,0)
                from #PRODUCT_FORM nmf
                where not exists
                    (
                        select *
                        from [PHARM].T_PRODUCT_FORM pf
                        where pf.ProductFormID = nmf.ID
                    )
                        and
                    @OperationType = @INSERT
            END
        END
    END
END
END
GO