--do $$
--begin
--	if exists 
--	(
--		select * from pg_proc p
--		where p.proname = 'sp_rds_divisionxml'
--	)
--	then
--		drop function public.sp_rds_divisionxml(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_DivisionXml
--(
--	"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_DIVISION d  where d.DivisionID = "ID")
--		then
--			OperationTypeVar := 'Update';
--		ELSIF "IsNew" = '0' and not exists (select * from akuz.T_DIVISION d where d.DivisionID = "ID")
--		then
--			OperationTypeVar := 'Delete';
--			Result = XMLELEMENT
--				 (
--					name "DIVISION"
--				 ,	XMLFOREST
--					(
--						XMLFOREST
--						(
--							"LpuCode"	 as "LPU"
--						,	OperationTypeVar as "TYPE"
--						,	"ID" as "ID"
--						) as header
--					)
--				 );
--		END if;

--		ContentEncodingVar := 'utf-8';
--		TypeVar := 'DIVISION';
--		OwnerIDVar := "ID";

--		IF OperationTypeVar <> 'Delete'
--		then
--			select
--			XMLELEMENT
--			(
--				name "DIVISION"
--			,	XMLFOREST
--				(
--					XMLFOREST
--					(
--						"LpuCode" as "LPU"
--					,	OperationTypeVar as "TYPE"
--					,	"ID" as "ID"
--					) as header
--				)
--			,	XMLFOREST
--				(
--					XMLFOREST
--					(
--						d.Code as "DV_CODE"
--					,	d.Name as "NAME"
--					,	d.FullName as "FULLNAME"
--					,	d.Level as "LEVEL"
--					,	d.CodeMz as "CODE_MZ"
--					,	d.Nofilet as "PHONE"
--					,	d.Adress as "ADDRESS"
--					,	d.Branch as "BRANCH_ID"
--					,	CASE d.DivisionType
--							WHEN 12801 THEN 0
--							WHEN 12802 THEN 1
--							WHEN 12803 THEN 2
--							WHEN 12804 THEN 3
--							WHEN 12805 THEN 4
--							WHEN 12806 THEN 5
--							WHEN 12807 THEN 6
--							WHEN 12808 THEN 7
--							WHEN 12809 THEN 8
--							WHEN 12810 THEN 9
--							ELSE 0 
--						END as "DIVISION_TYPE"
--					,	d.RevenueType as "REVENUE_TYPE"
--					,	d.IDNumberCard as "ID_NUMBER_CARD"
--					,	d.IsActive as "ACTIVE"
--					) as body
--				)
--			)
--			into Result
--			FROM akuz.T_DIVISION d
--			where d.DivisionID = "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 Tmp_DIVISION
--			Drop Table If Exists Tmp_DIVISION;
--			Create Temp Table Tmp_DIVISION(OperationType text, LpuMain varchar(6), ID UUID, Code varchar(8), Name text, FullName text, Level int, CodeMz int
--				, Nofilet text, Adress text, Branch UUID, DivisionType int, RevenueType int, IDNumberCard int, Active boolean);
--			insert into Tmp_DIVISION(OperationType, LpuMain, ID, Code, Name, FullName, Level, CodeMz, Nofilet, Adress, Branch, DivisionType, RevenueType, IDNumberCard, Active)
--			SELECT	nullif(trim(xpath('/DIVISION/header[1]/TYPE[1]/text()', "Xml")::text,'{}"'),'')
--			,	nullif(trim(xpath('/DIVISION/header[1]/LPU[1]/text()', "Xml")::text,'{}"'),'')::varchar(6)
--			,	nullif(trim(xpath('/DIVISION/header[1]/ID[1]/text()', "Xml")::text,'{}"'),'')::UUID			
--			,	nullif(trim(xpath('/DIVISION/body[1]/DV_CODE[1]/text()', "Xml")::text,'{}"'),'')::varchar(16)
--			,	nullif(trim(xpath('/DIVISION/body[1]/NAME[1]/text()', "Xml")::text,'{}"'),'')
--			,	nullif(trim(xpath('/DIVISION/body[1]/FULLNAME[1]/text()', "Xml")::text,'{}"'),'')
--			,	nullif(trim(xpath('/DIVISION/body[1]/LEVEL[1]/text()', "Xml")::text,'{}"'),'')::int
--			,	nullif(trim(xpath('/DIVISION/body[1]/CODE_MZ[1]/text()', "Xml")::text,'{}"'),'')::varchar(16)
--			,	nullif(trim(xpath('/DIVISION/body[1]/PHONE[1]/text()', "Xml")::text,'{}"'),'')
--			,	nullif(trim(xpath('/DIVISION/body[1]/ADDRESS[1]/text()', "Xml")::text,'{}"'),'')
--			,	nullif(trim(xpath('/DIVISION/body[1]/BRANCH_ID[1]/text()', "Xml")::text,'{}"'),'')::UUID
--			,	nullif(trim(xpath('/DIVISION/body[1]/DIVISION_TYPE[1]/text()', "Xml")::text,'{}"'),'')::int
--			,	nullif(trim(xpath('/DIVISION/body[1]/REVENUE_TYPE[1]/text()', "Xml")::text,'{}"'),'')::int
--			,	nullif(trim(xpath('/DIVISION/body[1]/ID_NUMBER_CARD[1]/text()', "Xml")::text,'{}"'),'')::int
--			,	nullif(trim(xpath('/DIVISION/body[1]/ACTIVE[1]/text()', "Xml")::text,'{}"'),'')::boolean;
			
--			select OperationType 
--			from Tmp_DIVISION
--			into OperationTypeVar
--			Limit 1;
			

--			if OperationTypeVar <> 'Delete'
--			then
--				-- update akuz.T_DIVISION
--				update akuz.T_DIVISION d
--				set	Code = div.Code
--				,	Name = div.Name
--				,	FullName = div.FullName
--				,	Level = div.Level
--				,	CodeMz = div.CodeMz
--				,	Nofilet = div.Nofilet
--				,	Adress = div.Adress
--				,	DivisionType = CASE div.DivisionType
--							WHEN 0 THEN 12801
--							WHEN 1 THEN 12802
--							WHEN 2 THEN 12803
--							WHEN 3 THEN 12804
--							WHEN 4 THEN 12805
--							WHEN 5 THEN 12806
--							WHEN 6 THEN 12807
--							WHEN 7 THEN 12808
--							WHEN 8 THEN 12809
--							WHEN 9 THEN 12810
--							ELSE 12801 
--						       END
--				,	RevenueType = div.RevenueType
--				,	IDNumberCard = div.IDNumberCard
--				,	IsActive = div.Active
--				from Tmp_DIVISION div 
--				where div.ID = d.OtherID;

--				-- INSERT akuz.T_DIVISION
--				INSERT INTO akuz.T_DIVISION( DivisionID, Code, Name, FullName, Level, CodeMz, Nofilet, Adress, Branch, DivisionType, RevenueType, IsRequestonDivision, IsRequestInHospital, IsActive, OtherID
--					, IDNumberCard, WorkDays)
--				SELECT uuid_generate_v1(), d.Code, d.Name, d.FullName, d.Level, d.CodeMz, d.Nofilet, d.Adress
--					,(
--						select br.LpuID from akuz.T_BRANCH br where br.OtherId = d.Branch
--					 )
--					, CASE d.DivisionType
--						WHEN 0 THEN 12801
--						WHEN 1 THEN 12802
--						WHEN 2 THEN 12803
--						WHEN 3 THEN 12804
--						WHEN 4 THEN 12805
--						WHEN 5 THEN 12806
--						WHEN 6 THEN 12807
--						WHEN 7 THEN 12808
--						WHEN 8 THEN 12809
--						WHEN 9 THEN 12810
--						ELSE 12801 
--					  END
--					, d.RevenueType, '0', '0', d.Active, d.ID, d.IDNumberCard, 31
--				from Tmp_DIVISION d
--				where not exists 
--				(
--					select * 
--					from akuz.T_DIVISION d1 
--					inner join 
--						Tmp_DIVISION dd 
--						on dd.ID = d1.OtherID
--				);			
--			ELSE
--				delete from akuz.T_DIVISION d
--				where d.OtherId in 
--				(
--					select dd.Id from Tmp_Division
--				);
--			END if;
--		-- Drop table
--		DROP TABLE if exists Tmp_DIVISION;
--		END if;
--	end if;
--End;
--$$ Language plpgsql;