---- DROP FUNCTION sp_GetSetBranchXml(text, xml, boolean, uuid);
--CREATE OR REPLACE FUNCTION sp_GetSetBranchXml
--(
--	"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_BRANCH br where br.BranchID = "ID")
--		then
--			_OperationType := 'Update';
--		elsif "IsNew" = '0' and not exists(select * from RDS.T_BRANCH br where br.BranchID = "ID")
--		then
--			_OperationType := 'Delete';
--			_Result := XMLELEMENT
--				  (
--					name "BRANCH"
--				  ,	XMLELEMENT
--					(
--						name header
--					,	XMLFOREST
--						(
--							"LpuCode" 	as "LPU"
--						,	_OperationType	as "TYPE"
--						,	"ID"		as "ID"
--						)
--					)
--				  ); 
--		END if;

--		_ContentEncoding := 'utf-8';
--		_Type := 'BRANCH';
--		_OwnerID := "ID";
	
--		if _OperationType <> 'Delete'
--		then
--			select 	
--			XMLELEMENT
--			(
--				name "BRANCH"
--			,	XMLELEMENT
--				(
--					name header
--				,	XMLFOREST
--					(
--						"LpuCode"	as "LPU"
--					,	_OperationType	as "TYPE"
--					,	"ID"		as "ID"
--					)
--				)				
--			,	XMLELEMENT
--				(
--					name body
--				,	XMLFOREST
--					(
--						br.BranchCode				as "BR_CODE"
--					,	br.Name					as "NAME"
--					,	to_char(br.DateAdd, 'YYYY-MM-DD')	as "DATE_ADD"
--					,	to_char(br.DateRemove, 'YYYY-MM-DD')	as "DATE_REMOVE"
--					,	br.AddressString			as "ADDRESS"
--					,	br.NameFull				as "NAMEFULL"
--					,	br.RegistryPhones			as "PHONE"
--					,	br.LpuType				as "LPU_TYPE"
--					,	br.Category				as "CATEGORY"
--					,	br.IsActive				as "ACTIVE"
--					)
--				)
--			)
--			FROM RDS.T_BRANCH br
--			into _Result
--			where br.BranchID = "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::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 #BRANCH
--		Drop Table If Exists TmpBRANCH;
--		Create Temp Table TmpBRANCH
--		As
--		select  (xpath('/BRANCH/header[1]/TYPE[1]/text()', x))[1]::text							as OperationType
--		,	(xpath('/BRANCH/header[1]/LPU[1]/text()', x))[1]::text							as LpuMain
--		,	(xpath('/BRANCH/header[1]/ID[1]/text()', x))[1]::text::uuid						as ID
--		,	(xpath('/BRANCH/body[1]/BR_CODE[1]/text()', x))[1]::text						as BranchCode
--		,	(xpath('/BRANCH/body[1]/NAME[1]/text()', x))[1]::text							as Name
--		,	to_timestamp((xpath('/BRANCH/body[1]/DATE_ADD[1]/text()', x))[1]::text, 'DD.MM.YYYY')::timestamp	as DateAdd
--		,	to_timestamp((xpath('/BRANCH/body[1]/DATE_REMOVE[1]/text()', x))[1]::text, 'DD.MM.YYYY')::timestamp	as DateRemove
--		,	(xpath('/BRANCH/body[1]/ADDRESS[1]/text()', x))[1]::text						as AddressString
--		,	(xpath('/BRANCH/body[1]/NAMEFULL[1]/text()', x))[1]::text						as NameFull
--		,	(xpath('/BRANCH/body[1]/PHONE[1]/text()', x))[1]::text							as RegistryPhones
--		,	(xpath('/BRANCH/body[1]/LPU_TYPE[1]/text()', x))[1]::text::int						as LpuType
--		,	(xpath('/BRANCH/body[1]/CATEGORY[1]/text()', x))[1]::text::int						as Category
--		,	(xpath('/BRANCH/body[1]/ACTIVE[1]/text()', x))[1]::text::boolean					as Active
--		from
--		(
--			select "Xml" as x
--		) t;
		
--		select tmp.OperationType 
--		from TmpBranch tmp
--		into _OperationType
--		Limit 1;

--		if _OperationType <> 'Delete'
--		then
--			-- update RDS.T_BRANCH
--			update RDS.T_BRANCH br
--			set	BranchCode = b.BranchCode
--			,	Name = b.Name
--			,	DateAdd = b.DateAdd
--			,	DateRemove = b.DateRemove
--			,	AddressString = b.AddressString
--			,	NameFull = b.NameFull
--			,	RegistryPhones = b.RegistryPhones
--			,	LpuType = b.LpuType
--			,	Category = b.Category
--			,	IsActive = b.Active
--			,	ID = b.ID
--			,	LpuMain = b.LpuMain		
--			from TmpBRANCH b 
--			where b.ID = br.ID or br.BranchID = b.ID;

--			-- INSERT RDS.T_BRANCH
--			INSERT INTO RDS.T_BRANCH(BranchID, BranchCode, Name, DateAdd, DateRemove, AddressString, NameFull, RegistryPhones, LpuMain, LpuType, Category, ID, IsActive)
--			SELECT uuid_generate_v4(), b.BranchCode, b.Name, b.DateAdd, b.DateRemove, b.AddressString, b.NameFull, b.RegistryPhones, b.LpuMain, b.LpuType, b.Category, b.ID, b.Active
--			from TmpBRANCH b
--			where not exists 
--			(
--				select * from RDS.T_BRANCH b1 where b.ID = b1.ID or b1.BranchID = b.ID
--			);
--		else
--			delete from RDS.T_BRANCH br
--			where exists
--			(
--				select* from TmpBRANCH b where b.ID = br.ID or br.BranchID = b.ID
--			);
--		end if;
		
--		-- Drop table
--		drop TABLE if exists TmpBRANCH;
--	end if;
--end;
--$BODY$
--  LANGUAGE plpgsql VOLATILE
--  COST 100;
--ALTER FUNCTION sp_GetSetBranchXml(text, xml, boolean, uuid)
--  OWNER TO postgres;
