IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_RDS_DirectionForDloAccessXml]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_RDS_DirectionForDloAccessXml]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Vitacore (с)
-- Create date: 2014-02-05
-- Description:	Выгрузка/загрузка направлений на получение права выписки рецептов.
-- =============================================
CREATE PROCEDURE [dbo].[sp_RDS_DirectionForDloAccessXml]
	@DfidapID uniqueidentifier = null	
	,@QueueName varchar(255) = null
AS
BEGIN
declare @Result varchar(max)		
		,@OwnerID varchar(255)
		--,@Headers varchar(255)
		,@DateTime dateTime
		,@ContentEncoding varchar(255)
		,@Type varchar(255)
select --,@MessageID = ''
	   --,@Headers = ''
	   @DateTime = getDate()
	   ,@ContentEncoding = 'utf-8'
	   ,@Type = 'DirectionForInclusionDloAccessedPosts'


SET NOCOUNT ON;

-- Add the T-SQL statements to compute the return value here

	SELECT 
	@OwnerID = dfidap.DloAccessedPostsDirectionID,
	@Result = 
		(				 			
			--собираем [AKUZ].T_DLO_ACCESSED_POSTS_DIRECTION
			select				
				dfidap.[DirectionType]				as	'header/DIRECTION_TYPE'
				,lm.LpuCode							as	'header/LPU_MAIN'
                ,case
                      when RTRIM(LTRIM(REPLACE(REPLACE(dfidap.Snils, '0', ''), '-', ''))) = '' then null
                      else dfidap.Snils
                 end                                as	'header/SNILS'
				,dfidap.[PostCode]  				as	'body/ADD/POST_CODE'
				,p.[Code]  				            as	'body/ADD/DOC_CODE'
				,dfidap.[LastName]					as	'body/ADD/LAST_NAME'
				--,dfidap.[CreateDateTime]			as	'body/ADD/СREATE_DATE_TIME'
				,dfidap.[FirstName]					as	'body/ADD/FIRST_NAME'
				,dfidap.[FatherName]				as	'body/ADD/FATHER_NAME'						
				,s.[Code]							as	'body/ADD/SPECIALITY'
				,pt.[Code]							as  'body/ADD/POST_TYPE'										
				,dfidap.[Category]					as	'body/ADD/CATEGORY'
				,convert(varchar(10), dfidap.[BeginAccessDate], 20) as 'body/ADD/BEGIN_ACCESS_DATE'
				,convert(varchar(10), dfidap.[EndAccessDate], 20) as 'body/ADD/END_ACCESS_DATE'
				,convert(varchar(10), dfidap.[BeginWorkDate], 20) as 'body/ADD/BEGIN_WORK_DATE'
				,convert(varchar(10), dfidap.[BeginWorkDate], 20) as 'body/ADD/CERTIFICATE_DATE'				
				,dfidap.MedicalSpecialityCode		as	'body/ADD/MEDICAL_SPECIALITY_CODE'										
				,convert(varchar(10), dfidap.[ExpulsionDate], 20) as 'body/REMOVE/EXPLUSION_DATE'
				,dfidap.[PreviousPostCode]			as	'body/REMOVE/PREVIOUS_POST_CODE'						
				,dfidap.[PreviousLastName]			as	'body/REMOVE/PREVIOUS_LAST_NAME'
				,dfidap.[PreviousFirstName]			as	'body/REMOVE/PREVIOUS_FIRST_NAME'
				,dfidap.[PreviousFatherName]		as	'body/REMOVE/PREVIOUS_FATHER_NAME'								
			FOR XML PATH(''),
			ROOT('DIRECTION_FOR_INCLUSION_DLO_ACCESSED_POSTS')
		) 
		from [AKUZ].T_DLO_ACCESSED_POSTS_DIRECTION dfidap
			left join [AKUZ].T_LPU_MAIN lm on dfidap.LpuMain = lm.LpuID 
			left join [AKUZ].T_SPECIALITY s on dfidap.Speciality = s.SpecialityID
			left join [AKUZ].T_POST_TYPE pt on dfidap.PostType = pt.PostTypeID							
			join [AKUZ].T_POST p on dfidap.Post = p.ResourceID						
		where dfidap.DloAccessedPostsDirectionID = @dfidapID

	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,>)
		   ,@OwnerID	
END