IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[RDS].[sp_RDS_DloAccessPostDirection]') AND type in (N'P', N'PC'))
DROP PROCEDURE [RDS].[sp_RDS_DloAccessPostDirection]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [RDS].[sp_RDS_DloAccessPostDirection]
    @Xml xml = null,
    @IsNew bit = 1,
    @ID uniqueidentifier = null,
    @QueueName varchar(100) = null,
    @dateTime datetime = null,
    @User uniqueidentifier = null
AS
BEGIN

declare
	@Result varchar(max),
	@ContentEncoding varchar(255),
	@Type varchar(255),
	@OperationType int,
	@INSERT int,
    @DELETE int,
	@DirectionState int,
	@DS_Accept int,
	@DS_Cancel int,
	@PostId uniqueidentifier,
	@FALSE varchar(5),
	@TRUE varchar(4)

SELECT
	@ContentEncoding = 'utf-8',
	@Type = 'DLO_ACCESSED_POSTS_DIRECTION',
	@INSERT = 0,
    @DELETE = 2,
	@DS_Accept = 1,
	@DS_Cancel = 2,
	@TRUE = 'true',
	@FALSE = 'false',
	@DateTime = IsNull(@DateTime, GETDATE())

	IF (@Xml IS NOT NULL) -- обработка пакета из раббита
	BEGIN
		DECLARE
			@PostCode varchar(255),
			@Snils varchar(14),
			@Speciality varchar(3),
			@PostType varchar(4),
			@LastName varchar(40),
			@FirstName varchar(40),
			@BeginWorkDate datetime,
			@NeedConfirmation varchar(6),
			@OpenDirectionCount int

		SELECT
			T.c.value('./DLO_ACCESSED_POSTS_DIRECTION_ID[1]', 'uniqueidentifier') as Id,
            T.c.value('./DIRECTION_TYPE[1]', 'varchar(36)') as DirectionType,
			T.c.value('./DIRECTION_STATE[1]', 'varchar(1)') as DirectionState,
			T.c.value('./LPU_MAIN[1]', 'varchar(6)') as LpuMain,
			T.c.value('./SNILS[1]', 'varchar(14)') as Snils,
			T.c.value('./POST_CODE[1]', 'varchar(255)') as PostCode,
			T.c.value('./DOC_CODE[1]', 'varchar(11)') as DocCode,
			T.c.value('./LAST_NAME[1]', 'varchar(40)') as LastName,
			T.c.value('./FIRST_NAME[1]', 'varchar(40)') as FirstName,
			T.c.value('./FATHER_NAME[1]', 'varchar(40)') as FatherName,
			T.c.value('./SPECIALITY[1]', 'varchar(3)') as Speciality,
			T.c.value('./POST_TYPE[1]', 'varchar(4)') as PostType,
			T.c.value('./CATEGORY[1]', 'varchar(4)') as Category,
			convert(datetime, T.c.value('./BEGIN_ACCESS_DATE[1]', 'varchar(10)'), 120) as BeginAccessDate,
			convert(datetime, T.c.value('./END_ACCESS_DATE[1]', 'varchar(10)'), 120) as EndAccessDate,
			convert(datetime, T.c.value('./BEGIN_WORK_DATE[1]', 'varchar(10)'), 120) as BeginWorkDate,
			convert(datetime, T.c.value('./CERTIFICATE_DATE[1]', 'varchar(10)'), 120) as CertificateDate,
			T.c.value('./MEDICAL_SPECIALITY_CODE[1]', 'varchar(16)') as MedicalSpecialityCode,
			convert(datetime, T.c.value('./EXPULSION_DATE[1]', 'varchar(10)'), 120) as ExpulsionDate,
            T.c.value('./EhrRus7PJSAM6GJM[1]', 'int') as EhrRus7pjsam6gjm,
            T.c.value('./EhrRus7PJSAM6GJMVer[1]', 'varchar(8)') as EhrRus7pjsam6gjmVer,
            T.c.value('./EhrRusLS9R0Q4VR4[1]', 'int') as EhrRusLs9r0q4vr4,
            T.c.value('./EhrRusLS9R0Q4VR4Ver[1]', 'varchar(8)') as EhrRusLs9r0q4vr4Ver
		INTO #DLO_ACCESSED_POSTS_DIRECTION
		FROM @Xml.nodes('DLO_ACCESSED_POSTS_DIRECTION/body/DLO_ACCESSED_POSTS_DIRECTION') T(c)

        IF (EXISTS (
            SELECT 1
            FROM #DLO_ACCESSED_POSTS_DIRECTION dir
            OUTER APPLY (
                SELECT TOP 1 ehr.IdEhr, rrb.CurrVersion
                FROM EHR.T_EHR_RUS_7PJSAM6GJM ehr
                JOIN EHR.T_EHR_RUS_REF_BOOKS rrb on rrb.EhrRusRefBooksID = ehr.EhrRusRefBooks
                WHERE ehr.IdEhr = dir.EhrRus7pjsam6gjm and rrb.CurrVersion = dir.EhrRus7pjsam6gjmVer
            ) e
            WHERE dir.EhrRus7pjsam6gjm is not null and dir.EhrRus7pjsam6gjmVer is not null
            and e.IdEhr is null and e.CurrVersion is null))
        BEGIN
            RAISERROR(N'Установлены некорректные значения EhrRus7pjsam6gjm и EhrRus7pjsam6gjmVer ', 16, 10)
            return;
        END

        IF (EXISTS (
            SELECT 1
            FROM #DLO_ACCESSED_POSTS_DIRECTION dir
            OUTER APPLY (
                SELECT TOP 1 ehr.IdEhr, rrb.CurrVersion
                FROM EHR.T_EHR_RUS_LS9R0Q4VR4 ehr
                JOIN EHR.T_EHR_RUS_REF_BOOKS rrb on rrb.EhrRusRefBooksID = ehr.EhrRusRefBooks
                WHERE ehr.IdEhr = dir.EhrRusLs9r0q4vr4 and rrb.CurrVersion = dir.EhrRusLs9r0q4vr4Ver
            ) e
            WHERE dir.EhrRusLs9r0q4vr4 is not null and dir.EhrRusLs9r0q4vr4Ver is not null
            and e.IdEhr is null and e.CurrVersion is null))
        BEGIN
            RAISERROR(N'Установлены некорректные значения EhrRusLs9r0q4vr4 и EhrRusLs9r0q4vr4Ver ', 16, 10)
            return;
        END


		SELECT
			@NeedConfirmation = p.ParameterValue
		FROM VCLib.T_HOLDER_PARAMETER p
		JOIN VCLib.T_HOLDER h on p.Holder = h.HolderID
		WHERE h.Name = 'RegistryUpdateHolder' and p.ParameterName = N'NeedСonfirmation' -- Признак ручной обработки

        IF (@NeedConfirmation NOT IN (@TRUE, @FALSE))
        BEGIN
            RAISERROR(N'Значение параметра @NeedConfirmation не установлено', 16, 10)
            return;
        END

		SELECT
			@Snils = d.Snils,
			@Speciality = d.Speciality,
			@PostType = d.PostType,
			@LastName = d.LastName,
			@FirstName = d.FirstName,
			@BeginWorkDate = d.BeginWorkDate,
			@PostCode = d.PostCode,
			@OperationType = d.DirectionType
		FROM #DLO_ACCESSED_POSTS_DIRECTION d

		IF (@Snils IS NULL)
        BEGIN
			RAISERROR(N'Не установлен снилс SNILS', 16, 10)
            return;
        END

		IF (@PostCode is NULL)
        BEGIN
			RAISERROR(N'Не установлен код врача POST_CODE', 16, 10)
            return;
        END

		IF (@NeedConfirmation = @TRUE)
		BEGIN
			SELECT
				@OpenDirectionCount = COUNT(*)
			FROM RDS.T_DLO_ACCESSED_POSTS_DIRECTION d
			JOIN #DLO_ACCESSED_POSTS_DIRECTION dlo on d.PostCode = d.PostCode and d.Snils = dlo.Snils and d.DirectionState = 0
			JOIN RDS.T_LPU_MAIN lpu on lpu.MCOD = dlo.LpuMain

			IF (@OpenDirectionCount > 0)
				RAISERROR(N'Для данного врача уже существует открытое направление', 16, 10);
		END

		IF (@NeedConfirmation = @FALSE)
		BEGIN

			INSERT INTO RDS.T_DLO_ACCESSED_POSTS_DIRECTION (
				DloAccessedPostsDirectionID,
				DirectionType,
				DirectionState,
				LpuMain,
				Snils,
				PostCode,
				DocCode,
				LastName,
				FirstName,
				FatherName,
				Speciality,
				PostType,
				Category,
				BeginAccessDate,
				EndAccessDate,
				BeginWorkDate,
				MedicalSpecialityCode,
                ExpulsionDate,
				CreateDateTime,
                EhrRus7pjsam6gjm,
                EhrRusLs9r0q4vr4
			)
			SELECT
				dlo.Id,
				dlo.DirectionType,
				1, -- принято
				dlo.LpuMain,
				dlo.Snils,
				dlo.PostCode,
				dlo.DocCode,
				dlo.LastName,
				dlo.FirstName,
				dlo.FatherName,
				dlo.Speciality,
				dlo.PostType,
				dlo.Category,
				dlo.BeginAccessDate,
				dlo.EndAccessDate,
				dlo.BeginWorkDate,
				dlo.MedicalSpecialityCode,
                dlo.ExpulsionDate,
				@DateTime,
                er1.EhrRusID,
                er2.EhrRusID
			FROM #DLO_ACCESSED_POSTS_DIRECTION dlo
            OUTER APPLY (
                SELECT TOP 1 er.EhrRusID
                FROM EHR.T_EHR_RUS_7PJSAM6GJM er
                JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
                WHERE er.IdEHR = dlo.EhrRus7pjsam6gjm AND RRB.CURRVERSION = dlo.EhrRus7pjsam6gjmVer) er1
            OUTER APPLY (
                SELECT TOP 1 er.EhrRusID
                FROM EHR.T_EHR_RUS_LS9R0Q4VR4 er
                JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
                WHERE er.IdEHR = dlo.EhrRusLs9r0q4vr4 AND RRB.CURRVERSION = dlo.EhrRusLs9r0q4vr4Ver) er2

			IF (@OperationType = @INSERT)
			BEGIN  -- Создаем поста

				SET @PostId = NEWID()
				set @ID = @PostId

				INSERT INTO RDS.T_DLO_ACCESSED_POSTS (
					DloAccessedPostsID,
					BeginAccessDate,
					BeginWorkDate,
					Category,
					CertificateDate,
					DocCode,
					EndAccessDate,
					FatherName,
					FirstName,
					LastName,
					LpuMain,
					MedicalSpecialityCode,
					PostCode,
					PostType,
					Snils,
					Speciality,
                    EhrRus7pjsam6gjm,
                    EhrRusLs9r0q4vr4
				)
				SELECT
					@PostId,
					dir.BeginAccessDate,
					dir.BeginWorkDate,
					dir.Category,
					@dateTime as CertificateDate,
					dir.DocCode,
					dir.EndAccessDate,
					dir.FatherName,
					dir.FirstName,
					dir.LastName,
					dir.LpuMain,
					dir.MedicalSpecialityCode,
					dir.PostCode,
					dir.PostType,
					dir.Snils,
					dir.Speciality,
                    dir.EhrRus7pjsam6gjm,
                    dir.EhrRusLs9r0q4vr4
				FROM RDS.T_DLO_ACCESSED_POSTS_DIRECTION dir
				JOIN #DLO_ACCESSED_POSTS_DIRECTION dlo on dlo.Id = dir.DloAccessedPostsDirectionID

			END
			ELSE   --Обновляем ExpulsionDate
			BEGIN

				SELECT
					@PostId = p.DloAccessedPostsID,
                    @ID = dir.Id
				FROM RDS.T_DLO_ACCESSED_POSTS p
				JOIN #DLO_ACCESSED_POSTS_DIRECTION dir ON p.PostCode = dir.PostCode
				WHERE p.ExpulsionDate is null

				UPDATE p
				SET p.ExpulsionDate = dir.ExpulsionDate
				FROM RDS.T_DLO_ACCESSED_POSTS p
				JOIN RDS.T_DLO_ACCESSED_POSTS_DIRECTION dir ON dir.DloAccessedPostsDirectionID = @ID
					AND p.DloAccessedPostsID = @PostId

			END

        SELECT @Result =
               (
                   SELECT 'Update' AS 'header/TYPE',
                          (
                              SELECT p.DloAccessedPostsID                         AS ID,
                                     p.PostCode                                   AS POST_CODE,
                                     p.DocCode                                    AS DOC_CODE,
                                     p.LastName                                   AS LAST_NAME,
                                     p.FirstName                                  AS FIRST_NAME,
                                     p.FatherName                                 AS FATHER_NAME,
                                     p.Category                                   AS CATEGORY,
                                     CONVERT(VARCHAR(10), p.BeginAccessDate, 120) AS BEGIN_ACCESS_DATE,
                                     CONVERT(VARCHAR(10), p.EndAccessDate, 120)   AS END_ACCESS_DATE,
                                     CONVERT(VARCHAR(10), p.BeginWorkDate, 120)   AS BEGIN_WORK_DATE,
                                     CONVERT(VARCHAR(10), p.CertificateDate, 120) AS CERTIFICATE_DATE,
                                     p.MedicalSpecialityCode                      AS MEDICAL_SPECIALITY_CODE,
                                     CONVERT(VARCHAR(10), p.ExpulsionDate, 120)   AS EXPULSION_DATE,
                                     p.Snils                                      AS SNILS,
                                     p.PostType                                   AS POST_TYPE,
                                     p.Speciality                                 AS SPECIALITY,
                                     p.LpuMain                                    AS LPU_MAIN,
                                     e1.IdEhr                                     AS EhrRus7PJSAM6GJM,
                                     rrb1.CurrVersion                             AS EhrRus7PJSAM6GJMVer,
                                     e2.IdEhr                                     AS EhrRusLS9R0Q4VR4,
                                     rrb2.CurrVersion                             AS EhrRusLS9R0Q4VR4Ver
                              FROM RDS.T_DLO_ACCESSED_POSTS p
                              LEFT JOIN EHR.T_EHR_RUS_7PJSAM6GJM e1 ON p.EhrRus7pjsam6gjm = e1.EhrRusId
                              LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS rrb1 ON rrb1.EhrRusRefBooksID = e1.EhrRusRefBooks
                              LEFT JOIN EHR.T_EHR_RUS_LS9R0Q4VR4 e2 ON p.EhrRusLs9r0q4vr4 = e2.EhrRusId
                              LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS rrb2 ON rrb2.EhrRusRefBooksID = e2.EhrRusRefBooks
                              WHERE p.DloAccessedPostsID = @PostId
                              FOR XML PATH(''), TYPE
                          )        AS 'body/DLO_ACCESSED_POSTS'
                   FOR XML PATH(''), ROOT('DLO_ACCESSED_POSTS')
               )


			INSERT INTO [RDS].[T_RABBIT_SEND_OBJECT] (
				[RabbitSendObjectID],
				[QueueName],
				[Content],
				[MessageID],
				[Headers],
				[ContentEncoding],
				[Type],
				[UserID],
				[State],
				[CreateDateTime],
				[OwnerID]
			)
			SELECT NewID(),
				  s.ServerName,
				  dbo.ConvertTextToBinaryZip(@Result, ''),
				  null,
				  null,
				  @ContentEncoding,
				  'DLO_ACCESSED_POSTS',
				  @User,
				  0,
				  @dateTime,
				  @ID
			FROM [RDS].T_SYNC_SERVERS s
			WHERE s.ServerType = 1 -- APS


            SELECT @Result =
                   (
                       SELECT 'Update' AS 'header/TYPE',
                              (
                                  SELECT dap.DloAccessedPostsDirectionID AS DLO_ACCESSED_POSTS_DIRECTION_ID,
                                         dap.DirectionType               AS DIRECTION_TYPE,
                                         dap.DirectionState              AS DIRECTION_STATE,
                                         dap.PostCode                    AS POST_CODE,
                                         dap.ExpulsionDate               AS EXPULSION_DATE,
                                         dap.RefusingReason              AS REFUSING_REASON
                                  FROM RDS.T_DLO_ACCESSED_POSTS_DIRECTION dap
                                  JOIN #DLO_ACCESSED_POSTS_DIRECTION dlo ON dap.DloAccessedPostsDirectionID = dlo.Id
                                  FOR XML PATH(''), TYPE
                              ) AS 'body/DLO_ACCESSED_POSTS_DIRECTION'

                       FOR XML PATH(''), ROOT('DLO_ACCESSED_POSTS_DIRECTION')
                   )



			INSERT INTO [RDS].[T_RABBIT_SEND_OBJECT] (
				[RabbitSendObjectID],
				[QueueName],
				[Content],
				[MessageID],
				[Headers],
				[ContentEncoding],
				[Type],
				[UserID],
				[State],
				[CreateDateTime],
				[OwnerID]
			)
			SELECT NewID(),
				  s.ServerName,
				  dbo.ConvertTextToBinaryZip(@Result, ''),
				  null,
				  null,
				  @ContentEncoding,
				  @Type,
				  @User,
				  0,
				  @dateTime,
				  @ID
			FROM [RDS].T_SYNC_SERVERS s
		    WHERE
		        s.ServerType = 0 -- AKUZ

		END
		ELSE -- // Ручная обработка
		BEGIN
			-- Создаем поста
			INSERT INTO RDS.T_DLO_ACCESSED_POSTS_DIRECTION (
				DloAccessedPostsDirectionID,
				DirectionType,
				DirectionState,
				LpuMain,
				Snils,
				PostCode,
				DocCode,
				LastName,
				FirstName,
				FatherName,
				Speciality,
				PostType,
				Category,
				BeginAccessDate,
				EndAccessDate,
				BeginWorkDate,
				MedicalSpecialityCode,
                ExpulsionDate,
				CreateDateTime,
                EhrRus7pjsam6gjm,
                EhrRusLs9r0q4vr4
			)
			SELECT
				dlo.Id,
				dlo.DirectionType,
				0, -- НОВОЕ
				dlo.LpuMain,
				dlo.Snils,
				dlo.PostCode,
				dlo.DocCode,
				dlo.LastName,
				dlo.FirstName,
				dlo.FatherName,
				dlo.Speciality,
				dlo.PostType,
				dlo.Category,
				dlo.BeginAccessDate,
				dlo.EndAccessDate,
				dlo.BeginWorkDate,
				dlo.MedicalSpecialityCode,
                dlo.ExpulsionDate,
				@DateTime,
                er1.EhrRusID,
                er2.EhrRusID
			FROM #DLO_ACCESSED_POSTS_DIRECTION dlo
            OUTER APPLY (
                SELECT TOP 1 er.EhrRusID
                FROM EHR.T_EHR_RUS_7PJSAM6GJM er
                JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
                WHERE er.IdEHR = dlo.EhrRus7pjsam6gjm AND RRB.CURRVERSION = dlo.EhrRus7pjsam6gjmVer) er1
            OUTER APPLY (
                SELECT TOP 1 er.EhrRusID
                FROM EHR.T_EHR_RUS_LS9R0Q4VR4 er
                JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
                WHERE er.IdEHR = dlo.EhrRusLs9r0q4vr4 AND RRB.CURRVERSION = dlo.EhrRusLs9r0q4vr4Ver) er2
		END

	END
	ELSE -- обработка действия при изменении объекта
	BEGIN

		SELECT
			@OperationType = dir.DirectionType,
			@DirectionState = dir.DirectionState
		FROM RDS.T_DLO_ACCESSED_POSTS_DIRECTION dir
		WHERE dir.DloAccessedPostsDirectionID = @ID

		IF (@DirectionState = @DS_Accept)
		BEGIN -- Принято
			IF (@OperationType = @INSERT)
			BEGIN --  Создаем нового поста
				SET @PostId = NEWID()

				INSERT INTO RDS.T_DLO_ACCESSED_POSTS (
					DloAccessedPostsID,
					BeginAccessDate,
					BeginWorkDate,
					Category,
					CertificateDate,
					DocCode,
					EndAccessDate,
					FatherName,
					FirstName,
					LastName,
					LpuMain,
					MedicalSpecialityCode,
					PostCode,
					PostType,
					Snils,
					Speciality,
                    EhrRus7pjsam6gjm,
                    EhrRusLs9r0q4vr4
				)
				SELECT
					@PostId,
					dir.BeginAccessDate,
					dir.BeginWorkDate,
					dir.Category,
					@dateTime as CertificateDate,
					dir.DocCode,
					dir.EndAccessDate,
					dir.FatherName,
					dir.FirstName,
					dir.LastName,
					dir.LpuMain,
					dir.MedicalSpecialityCode,
					dir.PostCode,
					dir.PostType,
					dir.Snils,
					dir.Speciality,
                    dir.EhrRus7pjsam6gjm,
                    dir.EhrRusLs9r0q4vr4
				FROM RDS.T_DLO_ACCESSED_POSTS_DIRECTION dir
				WHERE dir.DloAccessedPostsDirectionID = @ID
			END
			ELSE -- Обновляем post.ExpulsionDate
			BEGIN

				SELECT
					@PostId = p.DloAccessedPostsID
				FROM RDS.T_DLO_ACCESSED_POSTS p
				JOIN RDS.T_DLO_ACCESSED_POSTS_DIRECTION dir ON p.PostCode = dir.PostCode
					and p.ExpulsionDate is null
					and dir.DloAccessedPostsDirectionID = @ID

				UPDATE p
				SET p.ExpulsionDate = dir.ExpulsionDate
				FROM RDS.T_DLO_ACCESSED_POSTS p
				JOIN RDS.T_DLO_ACCESSED_POSTS_DIRECTION dir ON dir.DloAccessedPostsDirectionID = @ID
					AND p.DloAccessedPostsID = @PostId
			END
            SELECT @Result =
            (
                SELECT
                    'Update' AS 'header/TYPE',
                    (
                        SELECT
                           p.DloAccessedPostsID as [ID],
                           p.PostCode AS [POST_CODE],
                           p.DocCode as [DOC_CODE],
                           p.LastName as [LAST_NAME],
                           p.FirstName as [FIRST_NAME],
                           p.FatherName as [FATHER_NAME],
                           p.Category as [CATEGORY],
                           CONVERT(varchar(10), p.BeginAccessDate, 120) as [BEGIN_ACCESS_DATE],
                           CONVERT(varchar(10), p.EndAccessDate, 120) as [END_ACCESS_DATE],
                           CONVERT(varchar(10), p.BeginWorkDate, 120) as [BEGIN_WORK_DATE],
                           CONVERT(varchar(10), p.CertificateDate, 120) as [CERTIFICATE_DATE],
                           p.MedicalSpecialityCode as [MEDICAL_SPECIALITY_CODE],
                           CONVERT(varchar(10), p.ExpulsionDate, 120) as [EXPULSION_DATE],
                           p.Snils as [SNILS],
                           p.PostType as [POST_TYPE],
                           p.Speciality as [SPECIALITY],
                           p.LpuMain as [LPU_MAIN],
                           e1.IdEhr as [EhrRus7PJSAM6GJM],
                           rrb1.CurrVersion as [EhrRus7PJSAM6GJMVer],
                           e2.IdEhr as [EhrRusLS9R0Q4VR4],
                           rrb2.CurrVersion as [EhrRusLS9R0Q4VR4Ver]
                        FROM RDS.T_DLO_ACCESSED_POSTS p
                            LEFT JOIN EHR.T_EHR_RUS_7PJSAM6GJM e1 ON p.EhrRus7pjsam6gjm = e1.EhrRusId
                            LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS rrb1 ON rrb1.EhrRusRefBooksID = e1.EhrRusRefBooks
                            LEFT JOIN EHR.T_EHR_RUS_LS9R0Q4VR4 e2 ON p.EhrRusLs9r0q4vr4 = e2.EhrRusId
                            LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS rrb2 ON rrb2.EhrRusRefBooksID = e2.EhrRusRefBooks
                        WHERE p.DloAccessedPostsID = @PostId
                        FOR XML PATH(''), TYPE
                    ) as 'body/DLO_ACCESSED_POSTS'
                FOR XML PATH(''), ROOT('DLO_ACCESSED_POSTS')
            )

			INSERT INTO [RDS].[T_RABBIT_SEND_OBJECT] (
				[RabbitSendObjectID],
				[QueueName],
				[Content],
				[MessageID],
				[Headers],
				[ContentEncoding],
				[Type],
				[UserID],
				[State],
				[CreateDateTime],
				[OwnerID]
			)
			SELECT NewID(),
				  s.ServerName,
				  dbo.ConvertTextToBinaryZip(@Result, ''),
				  null,
				  null,
				  @ContentEncoding,
				  'DLO_ACCESSED_POSTS',
				  @User,
				  0,
				  @dateTime,
				  @ID
			FROM [RDS].T_SYNC_SERVERS s
    		WHERE s.ServerType = 1 -- APS

		END

            SELECT @Result =
                 (
                       SELECT 'Update' AS 'header/TYPE',
                              (
                                  SELECT dap.DloAccessedPostsDirectionID AS DLO_ACCESSED_POSTS_DIRECTION_ID,
                                         dap.DirectionType               AS DIRECTION_TYPE,
                                         dap.DirectionState              AS DIRECTION_STATE,
                                         dap.PostCode                    AS POST_CODE,
                                         CONVERT(varchar(10), dap.ExpulsionDate, 120) AS EXPULSION_DATE,
                                         dap.RefusingReason              AS REFUSING_REASON
                                  FROM RDS.T_DLO_ACCESSED_POSTS_DIRECTION dap
                                  WHERE dap.DloAccessedPostsDirectionID = @ID
                                  FOR XML PATH(''), TYPE
                              ) as 'body/DLO_ACCESSED_POSTS_DIRECTION'
                       FOR XML PATH(''), ROOT('DLO_ACCESSED_POSTS_DIRECTION')
                   )


		-- Отправляем объект в АКУЗ
        INSERT INTO [RDS].[T_RABBIT_SEND_OBJECT] (
			[RabbitSendObjectID],
			[QueueName],
			[Content],
			[MessageID],
			[Headers],
			[ContentEncoding],
			[Type],
			[UserID],
			[State],
			[CreateDateTime],
			[OwnerID]
		)
        SELECT
			NewID(),
            s.ServerName,
            dbo.ConvertTextToBinaryZip(@Result, ''),
            null,
            null,
            @ContentEncoding,
			@Type,
            @User,
            0,
            @dateTime,
            @ID
        FROM [RDS].T_SYNC_SERVERS s
		WHERE
		    s.ServerType = 0 -- AKUZ

	END
END
GO
