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_GetSetGroupsOfDrugsClassification]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetSetGroupsOfDrugsClassification]
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2023-07-18
-- Description: Выгрузка/загрузка справочника GROUPS_OF_DRUGS_CLASSIFICATION
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetSetGroupsOfDrugsClassification]
    @Xml xml = null,
    @IsNew bit = 1,
    @ID int = 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(6)
           ,@OwnerID int
            -- Operation types
           ,@INSERT varchar(6)
           ,@UPDATE varchar(6)
           ,@DELETE varchar(6)
           ,@FTLOAD varchar(6)

    select @dateTime = ISNULL(@dateTime, GETDATE())
          ,@ContentEncoding = 'utf-8'
          ,@Type = 'GROUPS_OF_DRUGS_CLASSIFICATION'
            -- Operation types
          ,@INSERT = 'Insert'
          ,@UPDATE = 'Update'
          ,@DELETE = 'Delete'
          ,@FTLOAD = 'FTLoad'

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

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

            -- собираем GROUPS_OF_DRUGS_CLASSIFICATION
            SELECT @Result =
            (
                select @OperationType		as 'header/TYPE'
                      ,c.Code				as 'header/Code'
                from [PHARM].T_GROUPS_OF_DRUGS_CLASSIFICATION c
                for xml path(''),
                root('GROUPS_OF_DRUGS_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,>)
              ,newid()--,<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]/Code[1]', 'int')
              ,@Result = cast(@Xml as varchar(max))
        from @Xml.nodes('GROUPS_OF_DRUGS_CLASSIFICATION') T(c)


        -- Обработка операции
        if (@OperationType = @FTLOAD)
        BEGIN
            -- Generate table #GROUPS_OF_DRUGS_CLASSIFICATIONS
            select t.c.value('./Code[1]', 'int')          as [Code]
                 , t.c.value('./Name[1]', 'varchar(255)') as [Name]
            INTO #GROUPS_OF_DRUGS_CLASSIFICATIONS
            from @Xml.nodes('GROUPS_OF_DRUGS_CLASSIFICATION/body/GROUP_OF_DRUGS_CLASSIFICATION') T(c)

            -- Обновляем имеющиеся МНН
            update c
            set c.Name				= nc.Name
            from [PHARM].T_GROUPS_OF_DRUGS_CLASSIFICATION c
                join #GROUPS_OF_DRUGS_CLASSIFICATIONS nc on nc.Code = c.Code

            -- Загружаем новые
            INSERT INTO [PHARM].[T_GROUPS_OF_DRUGS_CLASSIFICATION]
                       ([Code] ,[Name])
            SELECT
                       nc.Code
                       ,nc.Name
            from #GROUPS_OF_DRUGS_CLASSIFICATIONS nc
            where not exists
                (
                    select *
                    from [PHARM].T_GROUPS_OF_DRUGS_CLASSIFICATION c
                    where
						c.Code = nc.Code
                )
        END
        ELSE
            BEGIN
                -- Generate table #GROUPS_OF_DRUGS_CLASSIFICATION
                select T.c.value('./header[1]/Code[1]', 'int')        as [Code]
                     , T.c.value('./body[1]/Name[1]', 'varchar(255)') as [Name]
                INTO #GROUPS_OF_DRUGS_CLASSIFICATION
                from @Xml.nodes('GROUPS_OF_DRUGS_CLASSIFICATION') T(c)

                if (@OperationType = @DELETE)
                    BEGIN
                        -- Удаление
                        delete c
                        from [PHARM].[T_GROUPS_OF_DRUGS_CLASSIFICATION] c
                                 join #GROUPS_OF_DRUGS_CLASSIFICATION nc on nc.Code = c.Code
                    END
                else
                    BEGIN
                        -- update [PHARM].T_GROUPS_OF_DRUGS_CLASSIFICATION
                        update c
                        set c.Name = nc.Name
                        from [PHARM].T_GROUPS_OF_DRUGS_CLASSIFICATION c
                                 join #GROUPS_OF_DRUGS_CLASSIFICATION nc on nc.Code = c.Code

                        -- insert [PHARM].T_GROUPS_OF_DRUGS_CLASSIFICATION
                        -- Загружаем новые
                        INSERT INTO [PHARM].[T_GROUPS_OF_DRUGS_CLASSIFICATION]
                            ([Code], [Name])
                        SELECT nc.Code
                             , nc.Name
                        from #GROUPS_OF_DRUGS_CLASSIFICATION nc
                        where not exists
                                  (select *
                                   from [PHARM].T_GROUPS_OF_DRUGS_CLASSIFICATION c
                                   where c.Code = nc.Code)

                    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,>)
              ,newid()--,<OwnerID, varchar(255),>
        from [RDS].T_SYNC_SERVERS s
        where s.ServerName <> @QueueName
                and
            @OperationType <> @FTLOAD

    END
END
GO