-- =============================================
-- Author:		Vitacore (с)
-- Create date: 2014-02-05
-- Description:	Выгрузка/загрузка направлений на получение права выписки рецептов.
-- =============================================
DROP PROCEDURE IF EXISTS sp_RDS_DirectionForDloAccessXml;

CREATE PROCEDURE sp_RDS_DirectionForDloAccessXml
(
	"DfidapID" uuid DEFAULT NULL::uuid
,	"QueueName" varchar(255) DEFAULT NULL::varchar(255)
)
    language plpgsql
as
$$
Begin

	Declare	_Result text;
		_OwnerID varchar(255);
		_DateTime TimeStamp;
		_ContentEncoding varchar(255);
		_Type varchar(255);
	Begin
		_DateTime := Current_TimeStamp::TIMESTAMP;
		_ContentEncoding := 'utf-8';
		_Type := 'DirectionForInclusionDloAccessedPosts';
	
		SELECT into
			_OwnerId, _Result
			dfidap.DloAccessedPostsDirectionID
		,	--собираем akuz.T_DLO_ACCESSED_POSTS_DIRECTION
			XMLELEMENT
			(
				name "DIRECTION_FOR_INCLUSION_DLO_ACCESSED_POSTS"
			,	XMLELEMENT
				(
					name header
				,	XMLFOREST
					(
						dfidap.DirectionType as	"DIRECTION_TYPE"
					,	lm.LpuCode as "LPU_MAIN"
					,	case when TRIM(REPLACE(REPLACE(dfidap.Snils, '0', ''), '-', '')) = '' then null
							else dfidap.Snils
						end as	"SNILS"
					)
				)
			,	XMLELEMENT
				(
					name body
				,	XMLELEMENT
					(
						name "ADD"
					,	XMLFOREST
						(
							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"
						,	to_char(dfidap.BeginAccessDate, 'YYYY-MM-DD') as "BEGIN_ACCESS_DATE"
						,	to_char(dfidap.EndAccessDate, 'YYYY-MM-DD') as "END_ACCESS_DATE"
						,	to_char(dfidap.BeginWorkDate, 'YYYY-MM-DD') as "BEGIN_WORK_DATE"
						,	to_char(dfidap.CertificateDate, 'YYYY-MM-DD') as "CERTIFICATE_DATE"
						,	dfidap.MedicalSpecialityCode as "MEDICAL_SPECIALITY_CODE"
						)
					)
				,	XMLELEMENT
					(
						name "REMOVE"
					,	XMLFOREST
						(
							to_char(dfidap.ExpulsionDate, 'YYYY-MM-DD') as "EXPLUSION_DATE"
						,	dfidap.PreviousPostCode	as "PREVIOUS_POST_CODE"					
						,	dfidap.PreviousLastName	as "PREVIOUS_LAST_NAME"
						,	dfidap.PreviousFirstName as "PREVIOUS_FIRST_NAME"
						,	dfidap.PreviousFatherName as "PREVIOUS_FATHER_NAME"
						)
					)
				)
			)
		from akuz.T_DLO_ACCESSED_POSTS_DIRECTION dfidap
		inner join akuz.T_POST p on dfidap.Post = p.ResourceID	
		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																		
		where dfidap.DloAccessedPostsDirectionID = "DfidapID";
		
		INSERT INTO akuz.T_RABBIT_SEND_OBJECT(RabbitSendObjectID, QueueName, Content, MessageID, Headers, ContentEncoding, Type, UserID, State, CreateDateTime, OwnerID)
		select uuid_generate_v4(), "QueueName", ConvertTextToBinaryZip(_Result, ''), null, null, _ContentEncoding, _Type, null, 0, _DateTime, _OwnerID;
	End;
End;
$$;
