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				
                        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'
			        FOR XML PATH(''),
			        ROOT('DLO_ACCESSED_POSTS_DIRECTION')
		        ) 
		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						
		WHERE dfidap.DloAccessedPostsDirectionID = @ID

		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('./DLO_ACCESSED_POSTS_ID[1]', 'uniqueidentifier') as PostId,
			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') 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]
				)
				SELECT
					dlo.PostId,
					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
				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.PostId = post.DloAccessedPostsID
			END
		END

		DROP TABLE #DLO_ACCESSED_POSTS_DIRECTION 
	END
END
GO