---- =============================================
---- Author:		Vitacore (с)
---- Create date: 2014-02-05
---- Description:Загрузка информации о количестве коек в отделений в RDS 
---- =============================================
---- DROP FUNCTION sp_GetSetAmountXml(text, xml, boolean, uuid);
--CREATE OR REPLACE FUNCTION sp_GetSetAmountXml
--(
--	"LpuCode" text DEFAULT NULL::text
--,	"Xml" xml DEFAULT NULL::xml
--,	"IsNew" boolean DEFAULT '1'::boolean
--,	"ID" uuid DEFAULT NULL::uuid
--)
--RETURNS void AS
--$BODY$
--Declare	_Result text;
--	_ContentEncoding text;
--	_Type text;
--	_OperationType text;
--	_OwnerID uuid;
--begin
--	if "Xml" is null
--	then		
--		--выясняем тип операции
--		if "IsNew" = '1'
--		then
--			_OperationType := 'Insert';
--		elsif "IsNew" = '0' and exists(select * from RDS.T_AMOUNT_BED a where a.AmountBedID = "ID")
--		then
--			_OperationType := 'Update';
--		elsif "IsNew" = '0' and not exists(select * from RDS.T_AMOUNT_BED a where a.AmountBedID = "ID")
--		then
--			_OperationType := 'Delete';
--			_Result := XMLELEMENT
--				  (
--					name "AMOUNT_BED"
--				  ,	XMLELEMENT
--					(
--						name header
--					,	XMLFOREST
--						(
--							"LpuCode" 	as "LPU"
--						,	_OperationType	as "TYPE"
--						,	"ID"		as "ID"
--						)
--					)
--				  ); 
--		END if;

--		_ContentEncoding := 'utf-8';
--		_Type := 'AMOUNT_BED';
--		_OwnerID := "ID";
	
--		if _OperationType <> 'Delete'
--		then			
--			select 	
--			XMLELEMENT
--			(
--				name "AMOUNT_BED"
--			,	XMLELEMENT
--				(
--					name header
--				,	XMLFOREST
--					(
--						"LpuCode"	as "LPU"
--					,	_OperationType	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 RDS.T_AMOUNT_BED ab
--			into _Result
--			where ab.AmountBedID = "ID";
--		end if;

--		insert into RDS.T_RABBIT_SEND_OBJECT(RabbitSendObjectID, QueueName, Content, MessageID, Headers, ContentEncoding, Type, UserID, State, CreateDateTime, OwnerID)
--		select uuid_generate_v4(), s.ServerName, _Result, null, null, _ContentEncoding, _Type, null, 0, current_timestamp, _OwnerID
--		from RDS.T_SYNC_SERVERS s
--		where s.ServerType <> 1 
--		and not 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 = "LpuCode" 
--			and s1.SyncServersID = s.SyncServersID
--		);
			
--	elsif "Xml" is not null
--	then
--		-- Generate table #AMOUNT_BED
--		Drop Table If Exists TmpAMOUNT_BED;
--		Create Temp Table TmpAMOUNT_BED
--		As
--		select  (xpath('/AMOUNT_BED/header[1]/TYPE[1]/text()', x))[1]::text						as OperationType
--		,	(xpath('/AMOUNT_BED/header[1]/LPU[1]/text()', x))[1]::text						as LpuMain
--		,	(xpath('/AMOUNT_BED/header[1]/ID[1]/text()', x))[1]::text::uuid						as ID
--		,	(xpath('/AMOUNT_BED/body[1]/DLSB[1]/text()', x))[1]::text::uuid						as Dlsb
--		,	to_timestamp((xpath('/AMOUNT_BED/body[1]/DATE_BEGIN[1]/text()', x))[1]::text, 'DD.MM.YYYY')::timestamp	as DateBegin
--		,	to_timestamp((xpath('/AMOUNT_BED/body[1]/DATE_END[1]/text()', x))[1]::text, 'DD.MM.YYYY')::timestamp	as DateEnd
--		,	(xpath('/AMOUNT_BED/body[1]/FEMALE_BED[1]/text()', x))[1]::text::int					as FemaleBed
--		,	(xpath('/AMOUNT_BED/body[1]/MALE_BED[1]/text()', x))[1]::text::int					as MaleBed
--		,	(xpath('/AMOUNT_BED/body[1]/BEDS_COUNT[1]/text()', x))[1]::text::int					as BedsCount
--		,	(xpath('/AMOUNT_BED/body[1]/REPAIR_BED[1]/text()', x))[1]::text::int					as RepairBed
--		,	(xpath('/AMOUNT_BED/body[1]/PLANNED_BEDS_COUNT[1]/text()', x))[1]::text::int				as PlannedBedsCount
--		,	(xpath('/AMOUNT_BED/body[1]/PLANNED_MALE_BED[1]/text()', x))[1]::text::int				as PlannedMaleBed
--		,	(xpath('/AMOUNT_BED/body[1]/PLANNED_FEMALE_BED[1]/text()', x))[1]::text::int				as PlannedFemaleBed
--		from
--		(
--			select "Xml" as x
--		) t;

--		_OperationType := (select OperationType from TmpAMOUNT_BED limit 1);
		
--		if _OperationType <> 'Delete'
--		then
--			-- update [RDS].T_AMOUNT_BED
--			update RDS.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)
--			,	DivisionLinkStructureBed = (select dl.DivisionLinkStructureBedID from RDS.T_DIVISION_LINK_STRUCTURE_BED dl where dl.DivisionLinkStructureBedID = am.Dlsb or dl.ID = am.Dlsb limit 1)
--			from TmpAMOUNT_BED am 
--			where am.ID = ab.ID;
	 
--			-- INSERT [RDS].T_AMOUNT_BED
--			insert into RDS.T_AMOUNT_BED(AmountBedID, DateBegin, DateEnd, DivisionLinkStructureBed, FemaleBed, MaleBed, BedsCount, RepairBed, ID, PlannedFemaleBed, PlannedMaleBed, PlannedBedsCount)
--			SELECT uuid_generate_v4(), ab.DateBegin, ab.DateEnd
--			, (select dlsb.DivisionLinkStructureBedID from RDS.T_DIVISION_LINK_STRUCTURE_BED dlsb where dlsb.ID = ab.Dlsb or dlsb.DivisionLinkStructureBedID = ab.Dlsb limit 1)
--			, 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 RDS.T_AMOUNT_BED ab1 where ab1.ID = ab.ID or ab.ID = ab1.AmountBedID
--			);
--		else
--			delete from RDS.T_AMOUNT_BED ab1
--			where exists
--			(
--				select* from TmpAMOUNT_BED ab where ab1.ID = ab.ID or ab.ID = ab1.AmountBedID
--			);
--		end if;
		
--		-- Drop table
--		drop TABLE if exists TmpAMOUNT_BED;
--	end if;
--end;
--$BODY$
--  LANGUAGE plpgsql VOLATILE
--  COST 100;
--ALTER FUNCTION sp_GetSetAmountXml(text, xml, boolean, uuid)
--  OWNER TO postgres;