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_GetSetCmnnClassification]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetSetCmnnClassification]
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-07-29
-- Description: Выгрузка/загрузка классификации Международных непатентованных наименований (МНН) в(из) RDS
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetSetCmnnClassification]
    @Xml xml = null,
    @IsNew bit = 1,
    @ID uniqueidentifier = null,
    @QueueName varchar(100) = null,
    @dateTime datetime = null,
    @User uniqueidentifier = 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)

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

    IF (@Xml is null)
    BEGIN
        -- Инициализация
        select @ContentEncoding = 'utf-8'
              ,@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 [RDS].[T_RABBIT_SEND_OBJECT]
            ([RabbitSendObjectID] ,[QueueName] ,[Content] ,[MessageID] ,[Headers] ,[ContentEncoding] ,[Type] ,[UserID] ,[State] ,[CreateDateTime] ,[OwnerID])
        select NewID()--(<RabbitSendObjectID, uniqueidentifier,>
              ,s.ServerName--,<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),>
        from [RDS].T_SYNC_SERVERS s
        where (@QueueName is not null and s.ServerName = @QueueName) or (@QueueName is null and s.ServerType in (0, 1)) -- Рассылаем пакет всем подписанным серверам AKUZ-а и APS, если не указана очередь
    END
    ELSE
    IF (@Xml is not null)
    BEGIN

        -- Получаем тип операции
        select @OperationType = t.c.value('./header[1]/TYPE[1]', 'varchar(6)')
              ,@OwnerID = t.c.value('./header[1]/ID[1]', 'uniqueidentifier')
              ,@Result = cast(@Xml as varchar(max))
        from @Xml.nodes('C_MNN_CLASSIFICATION') T(c)

        -- Обработка операции
        if (@OperationType = @FTLOAD)
        BEGIN
            -- 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)

            -- Первичная загрузка
            -- Удаляем несуществующие связки
            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
            BEGIN
                -- 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)

                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 @OperationType = @INSERT
                          and not exists
                            (select *
                             from [PHARM].T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK mc
                             where mc.Cmnn = nmc.Cmnn
                               and mc.GroupsOfDrugsClassification = nmc.Classification)

                    END
            END


        -- Отправка всем остальным подписанным серверам AKUZ-а и APS
        INSERT INTO [RDS].[T_RABBIT_SEND_OBJECT]
            ([RabbitSendObjectID] ,[QueueName] ,[Content] ,[MessageID] ,[Headers] ,[ContentEncoding] ,[Type] ,[UserID] ,[State] ,[CreateDateTime] ,[OwnerID])
        select NewID()--(<RabbitSendObjectID, uniqueidentifier,>
              ,s.ServerName--,<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),>
        from [RDS].T_SYNC_SERVERS s
        where
			s.ServerName <> @QueueName
			and @OperationType <> @FTLOAD

    END
END
GO