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_PackageType]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_RDS_PackageType]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[APS].[sp_RDS_PackageType]') AND type in (N'P', N'PC'))
DROP PROCEDURE [APS].[sp_RDS_PackageType]
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2017-12-21
-- Description: Выгрузка/загрузка справочника Вид упаковки в(из) RDS
-- =============================================
CREATE PROCEDURE [APS].[sp_RDS_PackageType]
     @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 = 'PACKAGE_TYPE'
            -- Operation types
          ,@INSERT = 'Insert'
          ,@UPDATE = 'Update'
          ,@DELETE = 'Delete'
          ,@FTLOAD = 'FTLoad'

IF @PATH IS NOT NULL
	BEGIN
IF (SELECT OBJECT_ID('tempdb..#T_PACKAGE_TYPE')) IS NOT NULL
	DROP TABLE #T_PACKAGE_TYPE
CREATE TABLE #T_PACKAGE_TYPE
	(
		 PackageTypeID	UNIQUEIDENTIFIER
		,Name			VARCHAR
		,Code			int
		,ShortName		VARCHAR
		,FedCode		VARCHAR
		,RegCode		VARCHAR
	)
SET @SQL = 'INSERT INTO tempdb..#T_PACKAGE_TYPE
			SELECT
				 PackageTypeID
				,Name
				,Code
				,ShortName
				,FedCode
				,RegCode
			FROM ' + @PATH + '.[PHARM].T_PACKAGE_TYPE'
EXECUTE (@SQL)

	---- Первичная загрузка
	
	--DELETE S
	--	FROM [PHARM].T_PACKAGE_TYPE S
	--WHERE NOT EXISTS
	--    (
	--        SELECT *
	--			FROM #T_PACKAGE_TYPE nmf
	--        WHERE nmf.PackageTypeID = s.PackageTypeID
	--    )
	
	-- Обновляем имеющиеся
	update pt
		set pt.Name			= nmf.Name
		   ,pt.Code			= nmf.Code
		   ,pt.ShortName	= nmf.ShortName
		   ,pt.RegCode		= nmf.RegCode
		   ,pt.FedCode		= nmf.FedCode
	from [PHARM].T_PACKAGE_TYPE pt
	    join #T_PACKAGE_TYPE nmf on nmf.PackageTypeID = pt.PackageTypeID
	
	-- Загружаем новые
	INSERT INTO [PHARM].[T_PACKAGE_TYPE]
	           ([PackageTypeID] ,[Name] ,[Code],[ShortName],RegCode,FedCode)
	SELECT
	            nmf.PackageTypeID--<Code, int,>
	           ,nmf.Name--<Name, varchar(255),>
	           ,nmf.Code--<Active, bit,>
			   ,nmf.ShortName
			   ,nmf.RegCode
			   ,nmf.FedCode
	from #T_PACKAGE_TYPE nmf
	where not exists
	    (
	        select *
	        from [PHARM].T_PACKAGE_TYPE pt
	        where pt.PackageTypeID = nmf.PackageTypeID
	    )

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

        -- Обработка операции
        IF (@OperationType = @FTLOAD)
        BEGIN
            -- Первичная загрузка
            select @Result = 
            (
                select @FTLOAD               as 'header/TYPE'
                      ,(
                            select pt.PackageTypeID		as 'PACKAGE_TYPE/ID'
                                  ,pt.Code				as 'PACKAGE_TYPE/Code'
                                  ,pt.Name				as 'PACKAGE_TYPE/Name'
								  ,pt.ShortName			as 'PACKAGE_TYPE/ShortName'
								  ,pt.RegCode			as 'PACKAGE_TYPE/RegCode'
								  ,pt.FedCode			as 'PACKAGE_TYPE/FedCode'
                            from [PHARM].T_PACKAGE_TYPE pt
                            for xml path(''),
                            type
                      ) as 'body/PACKAGE_TYPES'
                for xml path(''),
                root('PACKAGE_TYPE')
            )
        END
        ELSE IF(@OperationType <> @DELETE)
        BEGIN

            -- собираем PACKAGE_TYPE
            SELECT @Result = 
            ( 
                select @OperationType as 'header/TYPE'
                      ,@ID            as 'header/ID'
                      ,pt.Code		  as 'body/Code'
                      ,pt.Name        as 'body/Name'
					  ,pt.ShortName   as 'body/ShortName'
					  ,pt.RegCode	  as 'body/RegCode'
					  ,pt.FedCode	  as 'body/FedCode'
                from [PHARM].T_PACKAGE_TYPE pt
                where pt.PackageTypeID = @ID
                for xml path(''),
                root('PACKAGE_TYPE')
            ) 
        END

        -- Отправка
        INSERT INTO [aps].[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('PACKAGE_TYPE') T(c)

        -- Generate table #PACKAGE_TYPES
        select t.c.value('./ID[1]', 'uniqueidentifier')			as [ID]
              ,t.c.value('./Code[1]', 'int')			as [Code]
              ,t.c.value('./Name[1]', 'varchar(500)')			as [Name]
			  ,t.c.value('./ShortName[1]', 'varchar(500)')      as [ShortName]
			  ,t.c.value('./RegCode[1]', 'varchar(50)')			as [RegCode]
			  ,t.c.value('./FedCode[1]', 'varchar(50)')			as [FedCode]
            INTO #PACKAGE_TYPES
        from @Xml.nodes('PACKAGE_TYPE/body/PACKAGE_TYPES/PACKAGE_TYPE') T(c)
        where @OperationType = @FTLOAD

        -- Generate table #PACKAGE_TYPE
        select t.c.value('./header[1]/ID[1]', 'uniqueidentifier')	as [ID]
              ,t.c.value('./body[1]/Code[1]', 'int')		as [Code]
              ,t.c.value('./body[1]/Name[1]', 'varchar(500)')		as [Name]
			  ,t.c.value('./body[1]/ShortName[1]', 'varchar(500)')  as [ShortName]
			  ,t.c.value('./body[1]/RegCode[1]', 'varchar(50)')		as [RegCode]
			  ,t.c.value('./body[1]/FedCode[1]', 'varchar(50)')		as [FedCode]
            INTO #PACKAGE_TYPE
        from @Xml.nodes('PACKAGE_TYPE') T(c)
        where @OperationType <> @FTLOAD

        -- Обработка операции
        if (@OperationType = @FTLOAD)
        BEGIN
            -- Первичная загрузка

            -- Обновляем имеющиеся
            update pt
				set pt.Name			= nmf.Name
				   ,pt.Code			= nmf.Code
				   ,pt.ShortName	= nmf.ShortName
				   ,pt.RegCode		= nmf.RegCode
				   ,pt.FedCode		= nmf.FedCode
            from [PHARM].T_PACKAGE_TYPE pt
                join #PACKAGE_TYPES nmf on nmf.ID = pt.PackageTypeID

            -- Загружаем новые
            INSERT INTO [PHARM].[T_PACKAGE_TYPE]
                       ([PackageTypeID] ,[Name] ,[Code],[ShortName],RegCode,FedCode)
            SELECT
                       nmf.ID--<Code, int,>
                       ,nmf.Name--<Name, varchar(255),>
                       ,nmf.Code--<Active, bit,>
					   ,nmf.ShortName
					   ,nmf.RegCode
					   ,nmf.FedCode
            from #PACKAGE_TYPES nmf
            where not exists
                (
                    select *
                    from [PHARM].T_PACKAGE_TYPE pt
                    where pt.PackageTypeID = nmf.ID
                )
        END
        else if(@OperationType = @DELETE)
        BEGIN
            -- Удаление
            delete pt
            from [PHARM].[T_PACKAGE_TYPE] pt
                join #PACKAGE_TYPE nmf on nmf.[ID] = pt.PackageTypeID
        END
        else
        BEGIN
            -- update [PHARM].T_PACKAGE_TYPE
            update pt
				set pt.Name			= nmf.Name
				   ,pt.Code			= nmf.Code
				   ,pt.ShortName	= nmf.ShortName
				   ,pt.RegCode		= nmf.RegCode
				   ,pt.FedCode		= nmf.FedCode
            from [PHARM].T_PACKAGE_TYPE pt
                join #PACKAGE_TYPE nmf on nmf.ID = pt.PackageTypeID
            where @OperationType = @UPDATE

            -- insert [PHARM].T_PACKAGE_TYPE
            INSERT INTO [PHARM].[T_PACKAGE_TYPE]
                ([PackageTypeID] ,[Name] ,[Code],[ShortName],RegCode,FedCode)
            SELECT
                       nmf.ID--<Code, int,>
                       ,nmf.Name--<Name, varchar(255),>
                       ,nmf.Code--<Active, bit,>
					   ,nmf.ShortName
					   ,nmf.RegCode
					   ,nmf.FedCode
            from #PACKAGE_TYPE nmf
            where 
				@OperationType = @INSERT
				and not exists
                (
                    select *
                    from [PHARM].T_PACKAGE_TYPE pt
                    where pt.PackageTypeID = nmf.ID
                )

        END

        -- Drop tables
        DROP TABLE #PACKAGE_TYPE
        DROP TABLE #PACKAGE_TYPES

    END
END
END
GO