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(6)') 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
		INTO #DLO_ACCESSED_POSTS_DIRECTION 
		FROM @Xml.nodes('DLO_ACCESSED_POSTS_DIRECTION') T(c)

		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 = 'NeedСonfirmation' -- Признак ручной обработки

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

		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)
			RAISERROR('Не установлен снилс SNILS', 16, 10)

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

		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('Для данного врача уже существует открытое направление', 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
			)
			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
			FROM #DLO_ACCESSED_POSTS_DIRECTION dlo

			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
				)
				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
				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
					p.DloAccessedPostsID as 'ID',
					p.PostCode AS 'POST_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'

				FROM RDS.T_DLO_ACCESSED_POSTS p
				WHERE p.DloAccessedPostsID = @PostId
				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 
					dap.DloAccessedPostsDirectionID as 'DLO_ACCESSED_POSTS_DIRECTION_ID',
					dap.DirectionType as 'DIRECTION_TYPE',
					dap.DirectionState as 'DIRECTION_STATE',
					@PostId as 'DLO_ACCESSED_POSTS_ID',
					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(''),
				 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,
				CreateDateTime
			)
			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,
				@DateTime
			FROM #DLO_ACCESSED_POSTS_DIRECTION dlo
		END

		DROP TABLE #DLO_ACCESSED_POSTS_DIRECTION 
	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
				)
				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
				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
					p.DloAccessedPostsID as 'ID',
					p.PostCode AS 'POST_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'
				FROM RDS.T_DLO_ACCESSED_POSTS p
				WHERE p.DloAccessedPostsID = @PostId
				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 
				dap.DloAccessedPostsDirectionID as 'DLO_ACCESSED_POSTS_DIRECTION_ID',
				dap.DirectionType as 'DIRECTION_TYPE',
				dap.DirectionState as 'DIRECTION_STATE',
				@PostId as 'DLO_ACCESSED_POSTS_ID',
				dap.ExpulsionDate as 'EXPULSION_DATE',
				dap.RefusingReason as 'REFUSING_REASON'
			 FROM [RDS].T_DLO_ACCESSED_POSTS_DIRECTION dap
			 WHERE dap.DloAccessedPostsDirectionID = @ID
			 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
