---- =============================================
---- Author:		Vitacore (с)
---- Create date: 2014-03-01
---- Description:	Выгрузка/загрузка информации направлении на госпитализацию из RDS 
---- =============================================
---- DROP FUNCTION sp_SetReferralXml(text, xml, boolean, uuid, text);
--CREATE OR REPLACE FUNCTION sp_SetReferralXml
--(
--	"LpuCode"	text DEFAULT NULL::text
--,	"Xml"		xml DEFAULT NULL::xml
--,	"IsNew"		boolean DEFAULT '1'::boolean
--,	"ID"		uuid DEFAULT NULL::uuid
--,	"LpuTo"		text DEFAULT NULL::text
--)
--RETURNS void AS
--$BODY$
--Declare	_Result text;
--	_ContentEncoding text;
--	_Type text;
--	_OperationType text;
--	_OwnerID uuid;
--begin
--	_OperationType := 'Insert';
	
--	if "Xml" is null
--	then
--		--выясняем тип операции
--		if "IsNew" = '1'
--		then
--			_OperationType := 'Insert';
--		elsif "IsNew" = '0' and not exists(select * from RDS.T_HOSPITALIZATION_REFERRAL hr where hr.HospitalizationReferralID = "ID")
--		then
--			_OperationType := 'Delete';
--			_Result := XMLELEMENT
--				  (
--					name "HOSPITASISATION_REFERRAL"
--				  ,	XMLELEMENT
--					(
--						name header
--					,	XMLFOREST
--						(
--							"LpuCode" 	as "LPU"
--						,	_OperationType	as "TYPE"
--						,	"ID"		as "ID"
--						,	"LpuTo"		as "LPU_TO"
--						)
--					)
--				  ); 
--		END if;

--		_ContentEncoding := 'utf-8';
--		_Type := 'HOSPITALISATION_REFERRAL';
--		_OwnerID := "ID";
	
--		if _OperationType = 'Insert'
--		then
--			select 	
--			XMLELEMENT
--			(
--				name "HOSPITALISATION_REFERRAL"
--			,	XMLELEMENT
--				(
--					name header
--				,	XMLFOREST
--					(
--						"LpuCode"	as "LPU"
--					,	_OperationType	as "TYPE"
--					,	"ID"		as "ID"
--					,	"LpuTo"		as "LPU_TO"
--					)
--				)				
--			,	XMLELEMENT
--				(
--					name body
--				,	XMLFOREST
--					(
--						hr.ReferralNumber				as "REFERRAL_NUMBER"
--					,	to_char(hr.ReferralDate, 'YYYY-MM-DD')		as "REFERRAL_DATE"
--					,	to_char(hr.HospitalisationDate, 'YYYY-MM-DD')	as "HOSPITALISATION_DATE"
--					,	hr.HospitalizationType				as "HOSPITALISATION_TYPE"
--					,	brT.BranchCode					as "BRANCH_TO"
--					,	dT.Code						as "DIVISION_TO"
--					,	sb.Code						as "STRUCTURE_BED"
--					,	sbb.IDK_PR					as "V020_STRUCTURE_BED"
--					,	br.BranchCode					as "BRANCH_FROM"
--					,	hr.MkbCode					as "MKB"
--					,	hr.Diagnosis					as "DIAGNOSIS"
--					,	to_char(hr.PlannedDate, 'YYYY-MM-DD')		as "PLANNED_DATE"
--					,	hr.DocCode					as "DOC_CODE"
--					,	hr.PolicyForm					as "POLICY_TYPE"
--					,	hr.PolisSerial					as "POLIS_SERIAL"
--					,	hr.PolisNumber					as "POLIS_NUMBER"
--					,	hr.F002Smo					as "SMO"
--					,	hr.FirstName					as "FIRST_NAME"
--					,	hr.LastName					as "LAST_NAME"
--					,	hr.FatherName					as "FATHER_NAME"
--					,	hr.Sex						as "SEX"
--					,	hr.AnotherRegion				as "ANOTHER_REGION"
--					,	to_char(hr.Birthday, 'YYYY-MM-DD')		as "BIRTHDAY"
--					,	hr.Phone					as "PHONE"
--					,	hr.PatientID					as "PATIENT"
--					)
--				)
--			)
--			from RDS.T_HOSPITALIZATION_REFERRAL hr 
--			inner join 
--				RDS.T_STRUCTURE_BED sb 
--				on sb.Code = hr.StructureBed
--			left join 
--				RDS.T_V020_STRUCTURE_BED sbb 
--				on sbb.IDK_PR = hr.V020StructureBed
--			--to
--			left join 
--				RDS.T_DIVISION dT 
--				on dT.DivisionID = hr.DivisionTo
--			left join 
--				RDS.T_BRANCH brT 
--				on brT.BranchID = dT.Branch
--			--from
--			inner join 
--				RDS.T_BRANCH br 
--				on br.BranchID = hr.BranchFrom
--			into _Result
--			where hr.HospitalizationReferralID = "ID";
--		end if;

--		insert into RDS.T_RABBIT_SEND_OBJECT(RabbitSendObjectID, QueueName, Content, MessageID, Headers, ContentEncoding, Type, UserID, State, CreateDateTime, OwnerID)
--		select gen_random_uuid(), s.ServerName, _Result, null, null, _ContentEncoding, _Type, null, 0, current_timestamp, _OwnerID
--		from RDS.T_SYNC_SERVERS s
--		where exists 
--		(
--			select * 
--			from RDS.T_SYNC_SERVERS s1
--			inner join 
--				RDS.T_SERVERS_LINK sl 
--				on sl.SyncServers = s1.SyncServersID
--			inner join 
--				RDS.T_SERVERS_LINK_LPU ll 
--				on ll.ServersLinkID = sl.ServersLinkID
--			inner join 
--				RDS.T_LPU_MAIN lp 
--				on lp.MCOD = ll.LpuMain
--			where lp.MCOD = "LpuTo"
--			and s1.SyncServersID = s.SyncServersID
--		);
--	end if;
--end;
--$BODY$
--  LANGUAGE plpgsql VOLATILE
--  COST 100;
--ALTER FUNCTION sp_SetReferralXml(text, xml, boolean, uuid, text)
--  OWNER TO postgres;