IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_RDS_CmnnClassification]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_RDS_CmnnClassification]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[APS].[sp_RDS_CmnnClassification]') AND type in (N'P', N'PC'))
DROP PROCEDURE [APS].[sp_RDS_CmnnClassification]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-07-29
-- Description: Выгрузка/загрузка классификации Международных непатентованных наименований (МНН) в(из) RDS
-- =============================================
CREATE PROCEDURE [APS].[sp_RDS_CmnnClassification]
     @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(255)
           ,@OwnerID uniqueidentifier
           ,@usedIn int
            -- Operation types
           ,@INSERT varchar(6)
           ,@UPDATE varchar(6)
           ,@DELETE varchar(6)
           ,@FTLOAD varchar(6)
		   ,@SQL VARCHAR(MAX)

    select @dateTime = ISNULL(@dateTime, GETDATE())
          ,@usedIn = 1
            -- Operation types
          ,@INSERT = 'Insert'
          ,@UPDATE = 'Update'
          ,@DELETE = 'Delete'
          ,@FTLOAD = 'FTLoad'

IF @PATH IS NOT NULL
	BEGIN
IF (SELECT OBJECT_ID('tempdb..#T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK')) IS NOT NULL
	DROP TABLE #T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK
CREATE TABLE #T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK
	(
		 CMnnForDrugClassificationLinkID		uniqueidentifier
		,CMnn									uniqueidentifier
		,GroupsOfDrugsClassification			int
	)
SET @SQL = 'INSERT INTO tempdb..#T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK
			SELECT 
				 [CMnnForDrugClassificationLinkID]
				,[CMnn]
				,[GroupsOfDrugsClassification]
			FROM ' + @PATH + '.[PHARM].T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK'
EXECUTE (@SQL)

-- Первичная загрузка

	DELETE mdc
		FROM [PHARM].[T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK] mdc
	WHERE NOT EXISTS
	    (
	        select *
				from #T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK mc
	        where 
				mdc.Cmnn = mc.Cmnn
	            and mdc.GroupsOfDrugsClassification = mc.GroupsOfDrugsClassification
	    )
	
	-- Загружаем новые
	INSERT INTO [PHARM].[T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK] 
			(
				 [CMnnForDrugClassificationLinkID]
				,[CMnn] 
				,[GroupsOfDrugsClassification]
			)
	SELECT 
			 mc.CMnnForDrugClassificationLinkID 
			,mc.Cmnn 
			,mc.GroupsOfDrugsClassification
		from #T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK mc
	where not exists
	    (
	        select 1
	        from [PHARM].T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK mdc
	        where 
				mdc.Cmnn = mc.Cmnn
	            and mdc.GroupsOfDrugsClassification = mc.GroupsOfDrugsClassification
	    )
	END
	ELSE
	BEGIN

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

        -- Обработка операции
        IF (@OperationType = @FTLOAD)
        BEGIN
            -- Первичная загрузка
            select @Result = 
            (
                select @FTLOAD                                        as 'header/TYPE'
                      ,(
                            select mc.CMnnForDrugClassificationLinkID as 'C_MNN_CLASSIFICATION/ID'
                                  ,mc.CMnn                            as 'C_MNN_CLASSIFICATION/Cmnn'
                                  ,mc.GroupsOfDrugsClassification     as 'C_MNN_CLASSIFICATION/Classification'
                            from [PHARM].T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK mc
                                join [PHARM].T_C_MNN m on m.CMnnID = mc.CMnn
                            where m.UsedIn = @usedIn
                            for xml path(''),
                            type
                      ) as 'body/C_MNN_CLASSIFICATIONS'
                for xml path(''),
                root('C_MNN_CLASSIFICATION')
            )
        END
        ELSE IF(@OperationType <> @DELETE)
        BEGIN

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

                      ,mc.CMnn                            as 'body/Cmnn'
                      ,mc.GroupsOfDrugsClassification     as 'body/Classification'
                from [PHARM].T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK mc
                where mc.CMnnForDrugClassificationLinkID = @ID
                for xml path(''),
                root('C_MNN_CLASSIFICATION')
            ) 
        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('C_MNN_CLASSIFICATION') T(c)

        -- Generate table #C_MNN_CLASSIFICATIONS
        select t.c.value('./ID[1]', 'uniqueidentifier')   as [ID]
              ,t.c.value('./Cmnn[1]', 'uniqueidentifier') as [Cmnn]
              ,t.c.value('./Classification[1]', 'int')    as [Classification]
            INTO #C_MNN_CLASSIFICATIONS
        from @Xml.nodes('C_MNN_CLASSIFICATION/body/C_MNN_CLASSIFICATIONS/C_MNN_CLASSIFICATION') T(c)
        where @OperationType = @FTLOAD

        -- Generate table #C_MNN_CLASSIFICATION
        select t.c.value('./header[1]/ID[1]', 'uniqueidentifier')   as [ID]
              ,t.c.value('./body[1]/Cmnn[1]', 'uniqueidentifier')   as [Cmnn]
              ,t.c.value('./body[1]/Classification[1]', 'int')      as [Classification]
            INTO #C_MNN_CLASSIFICATION
        from @Xml.nodes('C_MNN_CLASSIFICATION') T(c)
        where @OperationType <> @FTLOAD

        -- Обработка операции
        if (@OperationType = @FTLOAD)
        BEGIN
            -- Первичная загрузка
            -- Удаляем несуществующие связки
            delete mdc
            from [PHARM].[T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK] mdc
            where not exists
                (
                    select *
                    from #C_MNN_CLASSIFICATIONS mc
                    where 
						mdc.Cmnn = mc.Cmnn
                        and mdc.GroupsOfDrugsClassification = mc.Classification
                )

            -- Загружаем новые
            INSERT INTO [PHARM].[T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK] ([CMnnForDrugClassificationLinkID] ,[CMnn] ,[GroupsOfDrugsClassification])
            SELECT mc.ID ,mc.Cmnn ,mc.Classification
            from #C_MNN_CLASSIFICATIONS mc
            where not exists
                (
                    select *
                    from [PHARM].T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK mdc
                    where 
						mdc.Cmnn = mc.Cmnn
                        and mdc.GroupsOfDrugsClassification = mc.Classification
                )
        END
        else if(@OperationType = @DELETE)
        BEGIN
            -- Удаление
            delete mc
            from [PHARM].[T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK] mc
                join #C_MNN_CLASSIFICATION nmc on nmc.[ID] = mc.CMnnForDrugClassificationLinkID
        END
        else
        BEGIN
            -- update T_C_MNN_CLASSIFICATION
            update mc
            set mc.CMnn = nmc.Cmnn
               ,mc.GroupsOfDrugsClassification = nmc.Classification
            from [PHARM].T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK mc
                join #C_MNN_CLASSIFICATION nmc on nmc.ID = mc.CMnnForDrugClassificationLinkID
            where @OperationType = @UPDATE

            -- insert T_C_MNN_CLASSIFICATION
            INSERT INTO [PHARM].[T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK] ([CMnnForDrugClassificationLinkID] ,[CMnn] ,[GroupsOfDrugsClassification])
            SELECT
                       nmc.ID, nmc.Cmnn, nmc.Classification
            from #C_MNN_CLASSIFICATION nmc
            where not exists
                (
                    select *
                    from [PHARM].T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK mc
                    where 
						mc.Cmnn = nmc.Cmnn
                        and mc.GroupsOfDrugsClassification = nmc.Classification
                )
                    and
                @OperationType = @INSERT
        END

        -- Drop tables
        DROP TABLE #C_MNN_CLASSIFICATION
        DROP TABLE #C_MNN_CLASSIFICATIONS

    END
END
END
GO