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

CREATE PROCEDURE [AKUZ].[sp_AKUZ_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

SELECT
	@ContentEncoding = 'utf-8',
	@Type = 'DLO_ACCESSED_POSTS_DIRECTION',
	@INSERT = 0,
    @DELETE = 2,
	@DS_Accept = 1,
	@DS_Cancel = 2,
	@DateTime = ISNULL(@dateTime, GETDATE())


	IF (@Xml is null)
	BEGIN

        SELECT
            @Result =
            (
                SELECT
                    (
                        SELECT
                            'Update' AS TYPE
                        FOR XML PATH('header'), TYPE
                    ),
                    (
                        SELECT
                            dfidap.DloAccessedPostsDirectionID                AS DLO_ACCESSED_POSTS_DIRECTION_ID,
                            dfidap.DirectionType                              AS DIRECTION_TYPE,
                            dfidap.DirectionState                             AS DIRECTION_STATE,
                            lm.LpuCode                                        AS LPU_MAIN,
                            CASE
                                WHEN RTRIM(LTRIM(REPLACE(REPLACE(dfidap.Snils, '0', ''), '-', ''))) = '' THEN NULL
                                ELSE dfidap.Snils
                                END                                           AS SNILS,
                            dfidap.PostCode                                   AS POST_CODE,
                            p.Code                                            AS DOC_CODE,
                            dfidap.LastName                                   AS LAST_NAME,
                            dfidap.FirstName                                  AS FIRST_NAME,
                            dfidap.FatherName                                 AS FATHER_NAME,
                            s.Code                                            AS SPECIALITY,
                            pt.Code                                           AS POST_TYPE,
                            dfidap.Category                                   AS CATEGORY,
                            CONVERT(VARCHAR(10), dfidap.BeginAccessDate, 120) AS BEGIN_ACCESS_DATE,
                            CONVERT(VARCHAR(10), dfidap.EndAccessDate, 120)   AS END_ACCESS_DATE,
                            CONVERT(VARCHAR(10), dfidap.BeginWorkDate, 120)   AS BEGIN_WORK_DATE,
                            CONVERT(VARCHAR(10), dfidap.CertificateDate, 120) AS CERTIFICATE_DATE,
                            dfidap.MedicalSpecialityCode                      AS MEDICAL_SPECIALITY_CODE,
                            CONVERT(VARCHAR(10), dfidap.ExpulsionDate, 120)   AS EXPULSION_DATE,
                            e1.IdEhr                                          AS EhrRus7PJSAM6GJM,
                            rrb1.CurrVersion                                  AS EhrRus7PJSAM6GJMVer,
                            e2.IdEhr                                          AS EhrRusLS9R0Q4VR4,
                            rrb2.CurrVersion                                  AS EhrRusLS9R0Q4VR4Ver
                        FROM AKUZ.T_DLO_ACCESSED_POSTS_DIRECTION dfidap
                        JOIN AKUZ.T_LPU_MAIN lm ON dfidap.LpuMain = lm.LpuID
                        JOIN AKUZ.T_SPECIALITY s ON dfidap.Speciality = s.SpecialityID
                        JOIN AKUZ.T_POST_TYPE pt ON dfidap.PostType = pt.PostTypeID
                        JOIN AKUZ.T_POST p ON dfidap.Post = p.ResourceID
                        LEFT JOIN EHR.T_EHR_RUS_7PJSAM6GJM e1 ON dfidap.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 dfidap.EhrRusLs9r0q4vr4 = e2.EhrRusId
                        LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS rrb2 ON rrb2.EhrRusRefBooksID = e2.EhrRusRefBooks
                        WHERE dfidap.DloAccessedPostsDirectionID = @ID
                        FOR XML PATH('DLO_ACCESSED_POSTS_DIRECTION'), TYPE
                    )
                FOR XML PATH('body'), ROOT('DLO_ACCESSED_POSTS_DIRECTION')
            )


		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--,<MessageID, varchar(255),>
			   null,--,<Headers, varchar(255),>
			   @ContentEncoding,--,<ContentEncoding, varchar(255),>
			   @Type,--,<Type, varchar(255),>
			   null,--,<UserID, varchar(255),>
			   0,--,<State, int,>
			   @DateTime,--,<CreateDateTime, datetime,>)
			   @ID
		END
	ELSE
	BEGIN
		SELECT
			T.c.value('./DLO_ACCESSED_POSTS_DIRECTION_ID[1]', 'uniqueidentifier') as Id,
            convert(int, T.c.value('./DIRECTION_TYPE[1]', 'varchar(1)')) as DirectionType,
			convert(int, T.c.value('./DIRECTION_STATE[1]', 'varchar(1)')) as DirectionState,
			T.c.value('./POST_CODE[1]', 'varchar(32)') as PostCode,
			convert(datetime, T.c.value('./body[1]/EXPULSION_DATE[1]', 'varchar(10)'), 120) as ExpulsionDate,
			T.c.value('./REFUSING_REASON[1]', 'varchar(255)') as RefusingReason
		INTO #DLO_ACCESSED_POSTS_DIRECTION
		FROM @Xml.nodes('DLO_ACCESSED_POSTS_DIRECTION/body/DLO_ACCESSED_POSTS_DIRECTION') T(c)

		UPDATE dap
		SET dap.DirectionState = ndap.DirectionState,
			dap.RefusingReason = ndap.RefusingReason
		FROM [AKUZ].T_DLO_ACCESSED_POSTS_DIRECTION dap
		JOIN #DLO_ACCESSED_POSTS_DIRECTION ndap on ndap.Id = dap.DloAccessedPostsDirectionID

		SELECT
			@DirectionState = d.DirectionState,
			@OperationType = d.DirectionType
		FROM #DLO_ACCESSED_POSTS_DIRECTION d

		IF (@DirectionState = @DS_Accept)
		BEGIN
			IF (@OperationType = @INSERT)
			BEGIN
				INSERT INTO [AKUZ].[T_DLO_ACCESSED_POSTS] (
					[DloAccessedPostsID],
					[LpuMain],
					[PostCode],
					[LastName],
					[FirstName],
					[FatherName],
					[Category],
					[BeginAccessDate],
					[EndAccessDate],
					[BeginWorkDate],
					[CertificateDate],
					[MedicalSpecialityCode],
					[ExpulsionDate],
					[Snils],
					[PostType],
					[Speciality],
					[UpdateInfoDate],
                    [EhrRus7pjsam6gjm],
                    [EhrRusLs9r0q4vr4]
				)
				SELECT
					newid(),
					dir.LpuMain,
					dir.PostCode,
					dir.LastName,
					dir.FirstName,
					dir.FatherName,
					dir.Category,
					dir.BeginAccessDate,
					dir.EndAccessDate,
					dir.BeginWorkDate,
					dir.CertificateDate,
					dir.MedicalSpecialityCode,
					dir.ExpulsionDate,
					dir.Snils,
					dir.PostType,
					dir.Speciality,
					@DateTime,
                    dir.EhrRus7pjsam6gjm,
                    dir.EhrRusLs9r0q4vr4
				FROM #DLO_ACCESSED_POSTS_DIRECTION dlo
				JOIN AKUZ.T_DLO_ACCESSED_POSTS_DIRECTION dir on dlo.Id = dir.DloAccessedPostsDirectionID

			END
			ELSE
			BEGIN
				UPDATE post
				SET post.ExpulsionDate = dir.ExpulsionDate
				FROM AKUZ.T_DLO_ACCESSED_POSTS post
				JOIN #DLO_ACCESSED_POSTS_DIRECTION dir on dir.PostCode = post.PostCode
                WHERE post.ExpulsionDate IS NULL
			END
		END
	END
END
GO