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_BA_DloAccessPost]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_BA_DloAccessPost]
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-12-30
-- Description: Автоматическая синхронизация регистра врачей DLO в(из) RDS
-- =============================================
CREATE PROCEDURE [dbo].[sp_BA_DloAccessPost]
    @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
            -- Operation types
           ,@INSERT varchar(6)
           ,@UPDATE varchar(6)
           ,@DELETE varchar(6)
           ,@FTLOAD varchar(6)

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

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

        -- Обработка операции
        IF(@OperationType <> @DELETE)
        BEGIN

            -- собираем DLO_ACCESSED_POSTS
            SELECT @Result = 
            ( 
                select @OperationType                                 as 'header/TYPE'
                      ,@ID                                            as 'header/ID'
                      ,dap.PostCode                                   as 'body/PostCode'
                      ,dap.LastName                                   as 'body/LastName'
                      ,dap.FirstName                                  as 'body/FirstName'
                      ,dap.FatherName                                 as 'body/FatherName'
                      ,dap.Category                                   as 'body/Category'
                      ,convert(varchar(10), dap.BeginAccessDate, 104) as 'body/BeginAccessDate'
                      ,convert(varchar(10), dap.EndAccessDate, 104)   as 'body/EndAccessDate'
                      ,convert(varchar(10), dap.BeginWorkDate, 104)   as 'body/BeginWorkDate'
                      ,convert(varchar(10), dap.CertificateDate, 104) as 'body/CertificateDate'
                      ,dap.MedicalSpecialityCode                      as 'body/MedicalSpecialityCode'
                      ,convert(varchar(10), dap.ExpulsionDate, 104)   as 'body/ExpulsionDate'
                      ,dap.Snils                                      as 'body/Snils'
                      ,dap.PostType                                   as 'body/PostType'
                      ,dap.Speciality                                 as 'body/Speciality'
                      ,dap.LpuMain                                    as 'body/LpuMain'
                from [AKUZ].T_DLO_ACCESSED_POSTS dap
                where dap.DloAccessedPostsID = @ID
                for xml path(''),
                root('DLO_ACCESSED_POSTS')
            ) 
        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(36)')
              ,@OwnerID = t.c.value('./header[1]/ID[1]', 'uniqueidentifier')
              ,@Result = cast(@Xml as varchar(max))
        from @Xml.nodes('DLO_ACCESSED_POSTS') T(c)

        -- Generate table #DLO_ACCESSED_POSTS
        select T.c.value('./header[1]/ID[1]', 'uniqueidentifier')                               as [ID]
              ,T.c.value('./body[1]/PostCode[1]', 'varchar(255)')                               as [PostCode]
              ,T.c.value('./body[1]/LastName[1]', 'varchar(40)')                                as [LastName]
              ,T.c.value('./body[1]/FirstName[1]', 'varchar(40)')                               as [FirstName]
              ,T.c.value('./body[1]/FatherName[1]', 'varchar(40)')                              as [FatherName]
              ,T.c.value('./body[1]/Category[1]', 'int')                                        as [Category]
              ,convert(datetime, T.c.value('./body[1]/BeginAccessDate[1]', 'varchar(10)'), 104) as [BeginAccessDate]
              ,convert(datetime, T.c.value('./body[1]/EndAccessDate[1]', 'varchar(10)'), 104)   as [EndAccessDate]
              ,convert(datetime, T.c.value('./body[1]/BeginWorkDate[1]', 'varchar(10)'), 104)   as [BeginWorkDate]
              ,convert(datetime, T.c.value('./body[1]/CertificateDate[1]', 'varchar(10)'), 104) as [CertificateDate]
              ,T.c.value('./body[1]/MedicalSpecialityCode[1]', 'varchar(16)')                   as [MedicalSpecialityCode]
              ,convert(datetime, T.c.value('./body[1]/ExpulsionDate[1]', 'varchar(10)'), 104)   as [ExpulsionDate]
              ,T.c.value('./body[1]/Snils[1]', 'varchar(14)')                                   as [Snils]
              ,T.c.value('./body[1]/PostType[1]', 'varchar(4)')                                 as [PostType]
              ,T.c.value('./body[1]/Speciality[1]', 'varchar(3)')                               as [Speciality]
              ,T.c.value('./body[1]/LpuMain[1]', 'varchar(6)')                                  as [LpuMain]
            INTO #DLO_ACCESSED_POSTS
        from @Xml.nodes('DLO_ACCESSED_POSTS') T(c)

        -- Обработка операции
        IF (@OperationType <> @DELETE)
        BEGIN
            -- update [AKUZ].T_DLO_ACCESSED_POSTS
            update dap
            set dap.LpuMain = lm.LpuID
               ,dap.PostCode = ndap.PostCode
               ,dap.LastName = ndap.LastName
               ,dap.FirstName = ndap.FirstName
               ,dap.FatherName = ndap.FatherName
               ,dap.Category = ndap.Category
               ,dap.BeginAccessDate = ndap.BeginAccessDate
               ,dap.EndAccessDate = ndap.EndAccessDate
               ,dap.BeginWorkDate = ndap.BeginWorkDate
               ,dap.CertificateDate = ndap.CertificateDate
               ,dap.MedicalSpecialityCode = ndap.MedicalSpecialityCode
               ,dap.ExpulsionDate = ndap.ExpulsionDate
               ,dap.Snils = ndap.Snils
               ,dap.PostType = pt.PostTypeID
               ,dap.Speciality = s.SpecialityID
            from [AKUZ].T_DLO_ACCESSED_POSTS dap
                join #DLO_ACCESSED_POSTS ndap on ndap.ID = dap.DloAccessedPostsID
                join [AKUZ].T_LPU_MAIN lm on lm.LpuCode = ndap.LpuMain
                left join [AKUZ].T_SPECIALITY s on s.Code = ndap.Speciality
                left join [AKUZ].T_POST_TYPE pt on pt.Code = ndap.PostType
            where @OperationType = @UPDATE

            -- insert [AKUZ].T_DLO_ACCESSED_POSTS
            INSERT INTO [AKUZ].[T_DLO_ACCESSED_POSTS]
                       ([DloAccessedPostsID] ,[LpuMain] ,[PostCode] ,[LastName] ,[FirstName] ,[FatherName] ,[Category] ,[BeginAccessDate] ,[EndAccessDate]
                       ,[BeginWorkDate] ,[CertificateDate] ,[MedicalSpecialityCode] ,[ExpulsionDate] ,[Snils] ,[PostType] ,[Speciality])
            SELECT
                       ndap.ID--<DloAccessedPostsID, uniqueidentifier,>
                      ,lm.LpuID--<LpuMain, varchar(6),>
                      ,ndap.PostCode--<PostCode, varchar(255),>
                      ,ndap.LastName--<LastName, varchar(40),>
                      ,ndap.FirstName--<FirstName, varchar(40),>
                      ,ndap.FatherName--<FatherName, varchar(40),>
                      ,ndap.Category--<Category, int,>
                      ,ndap.BeginAccessDate--<BeginAccessDate, datetime,>
                      ,ndap.EndAccessDate--<EndAccessDate, datetime,>
                      ,ndap.BeginWorkDate--<BeginWorkDate, datetime,>
                      ,ndap.CertificateDate--<CertificateDate, datetime,>
                      ,ndap.MedicalSpecialityCode--<MedicalSpecialityCode, varchar(16),>
                      ,ndap.ExpulsionDate--<ExpulsionDate, datetime,>
                      ,ndap.Snils--<Snils, char(14),>
                      ,pt.PostTypeID--<PostType, varchar(4),>
                      ,(select top 1 s.SpecialityID from [AKUZ].T_SPECIALITY s where s.Code = ndap.Speciality)--<Speciality, varchar(3),>
            from #DLO_ACCESSED_POSTS ndap
                join [AKUZ].T_LPU_MAIN lm on lm.LpuCode = ndap.LpuMain and ndap.PostCode like lm.Code + '%'
                left join [AKUZ].T_POST_TYPE pt on pt.Code = ndap.PostType
            where not exists
                (
                    select *
                    from [AKUZ].T_DLO_ACCESSED_POSTS dap
                    where dap.DloAccessedPostsID = ndap.ID
                )
                    and
                @OperationType = @INSERT
        END

        -- Drop tables
        DROP TABLE #DLO_ACCESSED_POSTS

    END
END
GO