--do $$
--begin
--	if exists 
--	(
--		select * from pg_proc p
--		where p.proname = 'sp_rds_amountxml'
--	)
--	then
--		drop function public.sp_rds_amountxml(varchar(255), xml, bit, uuid, varchar(255));
--	end if;
--end;
--$$ language plpgsql;
---- =============================================
---- Author:		Vitacore (с)
---- Create date: 2014-02-05
---- Description:	Выгрузка/загрузка информации о количестве коек в профиле в(из) RDS 
---- =============================================
--CREATE or Replace Function public.sp_RDS_AmountXml
--( 
--	"LpuCode" varchar(255) = null
--,	"Xml" xml = null
--,	"IsNew" bit = '1'
--,	"ID" UUID = null
--,	"QueueName" varchar(255) = null
--)
--RETURNS Void As 
--$$
--Declare	Result text;
--			ContentEncodingVar varchar(255);
--			TypeVar varchar(255);
--			OperationTypeVar varchar(255);
--			OwnerIDVar UUID;
--Begin
--	IF "Xml" is null
--	then
--	--выясняем тип операции
--		IF "IsNew" = '1'
--		then	
--			OperationTypeVar := 'Insert';
--		ELSIF "IsNew" = '0' and exists (select * from akuz.T_AMOUNT_BED a  where a.AmountBedID = "ID")
--		then	
--			OperationTypeVar := 'Update';
--		ELSIF "IsNew" = '0' and not exists (select * from akuz.T_AMOUNT_BED a where a.AmountBedID = "ID")
--		then
--			OperationTypeVar := 'Delete';
--			Result := xmlelement
--				( 
--					name "AMOUNT_BED",
--					xmlelement
--					(
--						name header,
--						xmlforest
--						(
--							"LpuCode" as "LPU"
--						,	OperationTypeVar as "TYPE"
--						,	"ID" as "ID"
--						)
--					)
--				);
--		end if;
		
--		ContentEncodingVar = 'utf-8';
--		TypeVar = 'AMOUNT_BED';
--		OwnerIDVar = "ID";
		   
--		IF OperationTypeVar <> 'Delete'
--		then
--			select
--			XMLELEMENT
--			(	
--				name "AMOUNT_BED"
--			,	XMLELEMENT
--				(
--					name header
--				,	XMLFOREST
--					(
--						"LpuCode" as "LPU"
--					,	'Insert' as "Type"
--					,	"ID" as "ID"
--					)
--				)
--			,	XMLELEMENT
--				(
--					name body
--				,	xmlforest
--					(
--						ab.DivisionLinkStructureBed		as "DLSB"
--					,	to_char(ab.DateBegin, 'YYYY-MM-DD')	as "DATE_BEGIN"
--					,	to_char(ab.DateEnd, 'YYYY-MM-DD')	as "DATE_END"
--					,	ab.FemaleBed				as "FEMALE_BED"
--					,	ab.MaleBed				as "MALE_BED"
--					,	ab.BedsCount				as "BEDS_COUNT"
--					,	ab.RepairBed				as "REPAIR_BED"
--					,	ab.PlannedBedsCount			as "PLANNED_BEDS_COUNT"
--					,	ab.PlannedMaleBed			as "PLANNED_MALE_BED"
--					,	ab.PlannedFemaleBed			as "PLANNED_FEMALE_BED"
--					)
--				)
--			)
--			from akuz.T_AMOUNT_BED ab
--				into Result
--			where ab.AmountBedID = "ID";
--		END if;

--		INSERT INTO akuz.T_RABBIT_SEND_OBJECT(RabbitSendObjectID, QueueName, Content, MessageID, Headers, ContentEncoding, Type, UserID, State, CreateDateTime, OwnerID)
--		select uuid_generate_v1(), "QueueName", Result, null, null, ContentEncodingVar, TypeVar, null, 0, Current_TimeStamp::TIMESTAMP, OwnerIDVar;
--	ELSE 
--		IF "Xml" is not null
--		then		
--			-- Generate table #AMOUNT_BED
--			Drop Table If Exists TmpAMOUNT_BED;
--			Create Temp Table TmpAMOUNT_BED(OperationType text, LpuMain varchar(6), Id UUID, Dlsb UUID, DateBegin TimeStamp, DateEnd TimeStamp, FemaleBed int, MaleBed int, BedsCount int, RepairBed int
--				, PlannedBedsCount int, PlannedMaleBed int, PlannedFemaleBed int);	
--			-----------------AMOUNT_BED---------------------------
--			insert into TmpAMOUNT_BED(OperationType, LpuMain, Id, Dlsb, DateBegin, DateEnd, FemaleBed, MaleBed, BedsCount, RepairBed, PlannedBedsCount, PlannedMaleBed, PlannedFemaleBed)	
--			SELECT	nullif(trim(xpath('/AMOUNT_BED/header[1]/TYPE[1]/text()', "Xml")::text,'{}"'),'')
--			,	nullif(trim(xpath('/AMOUNT_BED/header[1]/LPU[1]/text()', "Xml")::text,'{}"'),'')::varchar(6)
--			,	nullif(trim(xpath('/AMOUNT_BED/header[1]/ID[1]/text()', "Xml")::text,'{}"'),'')::UUID
--			,	nullif(trim(xpath('/AMOUNT_BED/body[1]/DLSB[1]/text()', "Xml")::text,'{}"'),'')::UUID
--			,	to_timestamp(nullif(trim(xpath('/AMOUNT_BED/body[1]/DATE_BEGIN[1]/text()', "Xml")::text,'{}"'),''),'YYYY-MM-DD')
--			,	to_timestamp(nullif(trim(xpath('/AMOUNT_BED/body[1]/DATE_END[1]/text()', "Xml")::text,'{}"'),''),'YYYY-MM-DD')
--			,	nullif(trim(xpath('/AMOUNT_BED/body[1]/FEMALE_BED[1]/text()', "Xml")::text,'{}"'),'')::int
--			,	nullif(trim(xpath('/AMOUNT_BED/body[1]/MALE_BED[1]/text()', "Xml")::text,'{}"'),'')::int
--			,	nullif(trim(xpath('/AMOUNT_BED/body[1]/BEDS_COUNT[1]/text()', "Xml")::text,'{}"'),'')::int
--			,	nullif(trim(xpath('/AMOUNT_BED/body[1]/REPAIR_BED[1]/text()', "Xml")::text,'{}"'),'')::int
--			,	nullif(trim(xpath('/AMOUNT_BED/body[1]/PLANNED_BEDS_COUNT[1]/text()', "Xml")::text,'{}"'),'')::int
--			,	nullif(trim(xpath('/AMOUNT_BED/body[1]/PLANNED_MALE_BED[1]/text()', "Xml")::text,'{}"'),'')::int
--			,	nullif(trim(xpath('/AMOUNT_BED/body[1]/PLANNED_FEMALE_BED[1]/text()', "Xml")::text,'{}"'),'')::int;
			
--			select t.OperationType 
--			from TmpAMOUNT_BED t
--				into OperationTypeVar
--			Limit 1;

--			if OperationTypeVar <> 'Delete'
--			then
--				-- update akuz.T_AMOUNT_BED
--				update akuz.T_AMOUNT_BED ab
--				set	DateEnd = am.DateEnd
--				,	FemaleBed = am.FemaleBed
--				,	MaleBed = am.MaleBed
--				,	BedsCount = am.BedsCount
--				,	RepairBed = am.RepairBed			
--				,	PlannedFemaleBed = coalesce(am.PlannedFemaleBed, 0)
--				,	PlannedMaleBed =  coalesce(am.PlannedMaleBed, 0)
--				,	PlannedBedsCount = coalesce(am.PlannedBedsCount, 0)
--				from TmpAMOUNT_BED am 
--				where am.ID = ab.OtherID;
		 
--				-- INSERT akuz.T_AMOUNT_BED
--				INSERT INTO akuz.T_AMOUNT_BED(AmountBedID, DateBegin, DateEnd, DivisionLinkStructureBed, FemaleBed, MaleBed, BedsCount, RepairBed, OtherID, PlannedFemaleBed, PlannedMaleBed, PlannedBedsCount)
--				SELECT	uuid_generate_v1()
--				,	ab.DateBegin
--				,	ab.DateEnd
--				,	(select dlsb.DivisionLinkStructureBedID from akuz.T_DIVISION_LINK_STRUCTURE_BED dlsb where dlsb.OtherID = ab.Dlsb)
--				,	ab.FemaleBed
--				,	ab.MaleBed
--				,	ab.BedsCount
--				,	ab.RepairBed
--				,	ab.ID
--				,	coalesce(ab.PlannedFemaleBed, 0)
--				,	coalesce(ab.PlannedMaleBed, 0)
--				,	coalesce(ab.PlannedBedsCount, 0)
--				from TmpAMOUNT_BED ab
--				where not exists 
--				(
--					select * 
--					from akuz.T_AMOUNT_BED ab1
--					inner join 
--						TmpAMOUNT_BED am1 
--						on am1.ID = ab1.OtherID 
--				);
--			ELSE
--				delete from akuz.T_AMOUNT_BED ab
--				where ab.OtherID in (select a.ID from TmpAMOUNT_BED a);
--			END if;
--			-- Drop table
--			Drop Table If Exists TmpAMOUNT_BED;
--		END if;
--	end if;
--End;
--$$ Language plpgsql;