--do $$
--begin
--	if exists 
--	(
--		select * from pg_proc p
--		where p.proname = 'sp_rds_dictionaryxml'
--	)
--	then
--		drop function public.sp_rds_dictionaryxml(text, xml, bit, uuid);
--	end if;
--end;
--$$ language plpgsql;
---- =============================================
---- Author:		Vitacore (с)
---- Create date: 2014-03-01
---- Description:	Выгрузка/загрузка справочников в(из) RDS 
---- =============================================
--CREATE or REPLACE FUNCTION public.sp_RDS_DictionaryXml
--(
--	"LpuCode" text = null
--,	"Xml" xml = null
--,	"IsNew" bit = '1'
--,	"ID" UUID = null
--)
--RETURNS void AS
--$$
--Declare
--	Result text;
--	QueueNameVar varchar(255);
--	ContentEncodingVar varchar(255);
--	TypeVar varchar(255);
--	OperationType varchar(255);
--	OwnerIDVar UUID;
--BEGIN
--	IF "Xml" is null
--	then
--		QueueNameVar := 'RDS263';
--		ContentEncodingVar := 'utf-8';
--		TypeVar := 'Lpu';
--		OwnerIDVar := "ID";
--		Result := 
--		xmlelement
--		(
--			name "LPU"
--		,	xmlelement
--			(
--				name "header"
--			,	xmlforest
--				(
--					"LpuCode" as "LPU"
--				,	TypeVar   as "TYPE"
--				,	"ID"     as "ID"
--				)
--			)
--		,	(
--				--собираем BRANCH
--				select
--				xmlagg
--				(	
--					xmlelement
--					(
--						name "BRANCH"
--					,	xmlforest
--						(
--							br.BranchCode as "BR_CODE"
--						,	br.Name as "NAME"
--						,	to_char(br.DateAdd, 'YYYY-MM-DD') as "DATE_ADD"
--						,	br.DateRemove as "DATE_REMOVE"
--						,	br.AddressString as "ADDRESS"
--						,	br.NameFull as "NAMEFULL"
--						,	br.RegistryPhones as "PHONE"
--						,	lp.LpuCode as "LPU"
--						,	br.LpuType as "LPU_TYPE"
--						,	br.Category as "CATEGORY"
--						,	br.LpuID as "ID"
--						,	br.IsActive::int as "ACTIVE"
--						)
--						--собираем DIVISION
--					,	(
--							select
--							xmlagg 						
--							(
--								xmlelement
--								(	
--									name "DIVISION"
--								,	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"
--									,	lp.LpuCode	as "LPU"
--									,	d.DivisionID	as "ID"
--									,	d.IDNumberCard  as "ID_NUMBER_CARD"
--									,	d.IsActive::int as "ACTIVE"
--									)
--									--собираем DIVISION_LINK_STRUCTURE_BED
--								, 	(
--										select	
--										xmlforest
--										(
--											xmlforest
--											(
--												v0.Id as "STRUCTURE_BED"
--											,	sb.Code	as "V020_STRUCTURE_BED"
--											,	lp.LpuCode as "LPU" 
--											,	dlsb.DivisionLinkStructureBedID	as "ID"
--											,	dlsb.Division as "DIVISION_ID"
--												--собираем AMOUNT_BED
--											,	(
--													select	
--													xmlforest
--													(
--														v0.Id as "STRUCTURE_BED"
--													,	sb.Code	as "V020_STRUCTURE_BED"
--													,	lp.LpuCode as "LPU"
--													,	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"
--													,	ab.AmountBedID as "ID"
--													)
--													from akuz.T_AMOUNT_BED ab 
--													where dlsb.DivisionLinkStructureBedID = ab.DivisionLinkStructureBed 
--												) as "AMOUNT_BED"
--											) as "DIVISION_LINK_STRUCTURE_BED"
--										)
--										from akuz.T_DIVISION_LINK_STRUCTURE_BED dlsb
--										join akuz.T_STRUCTURE_BED sb on sb.StructureBedID = dlsb.StructureBed 
--										join akuz.T_V002_PROFILE v0 on v0.Id = sb.V002Profile
--										where dlsb.Division = d.DivisionID
--									)
--								) 
--							)
--							FROM akuz.T_DIVISION d
--							where d.Branch = br.LpuID				
--						)
--					)
--				)
--				FROM akuz.T_BRANCH br
--				join akuz.T_LPU_MAIN lp on lp.LpuID = br.LpuMain
--				join akuz.T_LPU_SETTINGS ls on ls.LpuMain = lp.LpuID
--				where lp.LpuCode is not null
--				and lp.LpuID = "ID"
--			)
--		);		
--		INSERT INTO akuz.T_RABBIT_SEND_OBJECT(RabbitSendObjectID, QueueName, Content, MessageID, Headers, ContentEncoding, Type, UserID, State, CreateDateTime, OwnerID)
--		select uuid_generate_v1(), QueueNameVar, Result, null, null, ContentEncodingVar, TypeVar, null, 0, Current_TimeStamp::TIMESTAMP, OwnerIDVar;
--	ELSE 
--		IF "Xml" is not null
--		then
--			-- Generate table Tmp_BRANCH
--			drop table if exists Tmp_BRANCH;
--			create temp table Tmp_BRANCH 
--			as 
--			select 	nullif(trim(xpath('/BRANCH/BR_CODE[1]/text()',x)::text,'{}'),'')::varchar(16) as BranchCode
--			,	nullif(trim(xpath('/BRANCH/NAME[1]/text()',x)::text,'{}'),'') as Name
--			,	to_timestamp(nullif(trim(xpath('/BRANCH/DATE_ADD[1]/text()',x)::text,'{}'),''),'YYYY-MM-DD') as DateAdd
--			,	to_timestamp(nullif(trim(xpath('/BRANCH/DATE_REMOVE[1]/text()',x)::text,'{}'),''),'YYYY-MM-DD') as DateRemove
--			,	nullif(trim(xpath('/BRANCH/ADDRESS[1]/text()',x)::text,'{}'),'') as AddressString
--			,	nullif(trim(xpath('/BRANCH/NAMEFULL[1]/text()',x)::text,'{}'),'') as NameFull
--			,	nullif(trim(xpath('/BRANCH/PHONE[1]/text()',x)::text,'{}'),'') as RegistryPhones
--			,	nullif(trim(xpath('/BRANCH/LPU[1]/text()',x)::text,'{}'),'')::varchar(6) as LpuMain
--			,	nullif(trim(xpath('/BRANCH/LPU_TYPE[1]/text()',x)::text,'{}'),'')::int as LpuType
--			,	nullif(trim(xpath('/BRANCH/CATEGORY[1]/text()',x)::text,'{}'),'')::int as Category
--			,	nullif(trim(xpath('/BRANCH/ID[1]/text()',x)::text,'{}'),'')::UUID as ID
--			,	nullif(trim(xpath('/BRANCH/ACTIVE[1]/text()',x)::text,'{}'),'')::boolean as Active
--			from
--			(
--				select	unnest(xpath('/LPU/BRANCH', x)) as x
--				from 
--				(
--					select "Xml" as x
--				) root
--			) BRANCH;	
--			-- update akuz.T_BRANCH
--			update akuz.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
--			from Tmp_BRANCH b 
--			where b.ID = br.OtherID;

			
--			update akuz.T_BRANCH br
--			set IsActive = 0
--			from TTmp_BRANCH b 
--			join akuz.T_LPU_MAIN lp on lp.LpuID = br.LpuMain
--			where bb.LpuMain = lp.LpuCode and  not exists (select * from TTmp_BRANCH b where b.ID = br.OtherID);

--			-- INSERT akuz.T_BRANCH
--			INSERT INTO akuz.T_BRANCH(LpuID, BranchCode, Name, DateAdd, DateRemove, AddressString, NameFull, RegistryPhones, LpuMain, LpuType, Category, OtherID, IsActive, BranchSettings)
--			SELECT uuid_generate_v1(), b.BranchCode, b.Name, b.DateAdd, b.DateRemove, b.AddressString, b.NameFull, b.RegistryPhones
--			, (select lpu.LpuID from akuz.T_LPU_MAIN lpu where lpu.LpuCode = b.LpuMain), b.LpuType, b.Category, b.ID, b.Active, 0
--			from Tmp_BRANCH b
--			where not exists 
--			(
--				select * 
--				from akuz.T_BRANCH b1 
--				where b.ID = b1.OtherID
--			);
--			-- Drop table
--			DROP TABLE if exists Tmp_BRANCH;
--			-- Generate table Tmp_DIVISION
--			drop table if exists Tmp_DIVISION;
--			create temp table Tmp_DIVISION 
--			as 
--			select	nullif(trim(xpath('/DIVISION/DV_CODE[1]/text()',x)::text,'{}'),'')::varchar(16) as Code
--			,	nullif(trim(xpath('/DIVISION/NAME[1]/text()',x)::text,'{}'),'') as Name
--			,	nullif(trim(xpath('/DIVISION/FULLNAME[1]/text()',x)::text,'{}'),'') as FullName
--			,	nullif(trim(xpath('/DIVISION/LEVEL[1]/text()',x)::text,'{}'),'')::int as Level
--			,	nullif(trim(xpath('/DIVISION/CODE_MZ[1]/text()',x)::text,'{}'),'')::varchar(16) as CodeMz
--			,	nullif(trim(xpath('/DIVISION/PHONE[1]/text()',x)::text,'{}'),'') as Nofilet
--			,	nullif(trim(xpath('/DIVISION/ADDRESS[1]/text()',x)::text,'{}'),'') as Adress
--			,	nullif(trim(xpath('/DIVISION/BRANCH_ID[1]/text()',x)::text,'{}'),'')::UUID as Branch
--			,	nullif(trim(xpath('/DIVISION/DIVISION_TYPE[1]/text()',x)::text,'{}'),'')::int as DivisionType
--			,	nullif(trim(xpath('/DIVISION/REVENUE_TYPE[1]/text()',x)::text,'{}'),'')::int as RevenueType
--			,	nullif(trim(xpath('/DIVISION/LPU[1]/text()',x)::text,'{}'),'')::varchar(6) as Lpu
--			,	nullif(trim(xpath('/DIVISION/ID[1]/text()',x)::text,'{}'),'')::UUID as ID
--			,	nullif(trim(xpath('/DIVISION/ID_NUMBER_CARD[1]/text()',x)::text,'{}'),'')::int as IDNumberCard
--			,	nullif(trim(xpath('/DIVISION/ACTIVE[1]/text()',x)::text,'{}'),'')::boolean as Active
--			from
--			(
--				select	unnest(xpath('/LPU/BRANCH/DIVISION', x)) as x
--				from 
--				(
--					select "Xml" as x
--				) root
--			) DIVISION;
--			-- 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;

			
--			update akuz.T_DIVISION dd
--			set IsActive = 0
--			from Tmp_DIVISION ddd 
--			join akuz.T_BRANCH b on b.LpuID = dd.Branch
--			join akuz.T_LPU_MAIN lp on lp.LpuID = b.LpuMain
--			where ddd.Lpu = lp.LpuCode and not exists (select * from Tmp_DIVISION div where dd.OtherID = div.ID);

--			-- INSERT akuz.T_DIVISION
--			INSERT INTO akuz.T_DIVISION(DivisionID, Code, Name, FullName, Level, CodeMz, Nofilet, Adress, Branch, DivisionType, RevenueType, IsRequestonDivision, IsRequestInHospital, OtherID, IDNumberCard, WorkDays, IsActive)
--			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.ID, d.IDNumberCard, 31, d.Active
--			from Tmp_DIVISION d
--			where not exists 
--			(
--				select * 
--				from akuz.T_DIVISION d1 
--				where d.ID = d1.OtherID
--			);
--			-- Drop table
--			DROP TABLE if exists Tmp_DIVISION;
--			-- Generate table Tmp_DIVISION_LINK_STRUCTURE_BED
--			drop table if exists Tmp_DIVISION_LINK_STRUCTURE_BED;
--			create temp table Tmp_DIVISION_LINK_STRUCTURE_BED 
--			as 
--			select	nullif(trim(xpath('/DIVISION_LINK_STRUCTURE_BED/STRUCTURE_BED[1]/text()',x)::text,'{}'),'')::int as CodeSB
--			,	nullif(trim(xpath('/DIVISION_LINK_STRUCTURE_BED/V020_STRUCTURE_BED[1]/text()',x)::text,'{}'),'')::int as V020StructureBed
--			,	nullif(trim(xpath('/DIVISION_LINK_STRUCTURE_BED/LPU[1]/text()',x)::text,'{}'),'')::varchar(6) as Lpu
--			,	nullif(trim(xpath('/DIVISION_LINK_STRUCTURE_BED/ID[1]/text()',x)::text,'{}'),'')::UUID as ID
--			,	nullif(trim(xpath('/DIVISION_LINK_STRUCTURE_BED/DIVISION_ID[1]/text()',x)::text,'{}'),'')::UUID as Div
--			from
--			(
--				select	unnest(xpath('/LPU/BRANCH/DIVISION/DIVISION_LINK_STRUCTURE_BED', x)) as x
--				from 
--				(
--					select "Xml" as x
--				) root
--			) DIVISION_LINK_STRUCTURE_BED;
--			-- Generate table Tmp_AMOUNT_BED
--			drop table if exists Tmp_AMOUNT_BED;
--			create temp table Tmp_AMOUNT_BED 
--			as 
--			select 	nullif(trim(xpath('/AMOUNT_BED/STRUCTURE_BED[1]/text()',x)::text,'{}'),'')::int as CodeSB
--			,	nullif(trim(xpath('/AMOUNT_BED/V020_STRUCTURE_BED[1]/text()',x)::text,'{}'),'')::int as V020StructureBed
--			,	nullif(trim(xpath('/AMOUNT_BED/LPU[1]/text()',x)::text,'{}'),'')::varchar(6) as Lpu
--			,	to_timestamp(nullif(trim(xpath('/AMOUNT_BED/DATE_BEGIN[1]/text()',x)::text,'{}'),''),'YYYY-MM-DD') as DateBegin
--			,	to_timestamp(nullif(trim(xpath('/AMOUNT_BED/DATE_END[1]/text()',x)::text,'{}'),''),'YYYY-MM-DD') as DateEnd
--			,	nullif(trim(xpath('/AMOUNT_BED/FEMALE_BED[1]/text()',x)::text,'{}'),'')::int as FemaleBed
--			,	nullif(trim(xpath('/AMOUNT_BED/MALE_BED[1]/text()',x)::text,'{}'),'')::int as MaleBed
--			,	nullif(trim(xpath('/AMOUNT_BED/BEDS_COUNT[1]/text()',x)::text,'{}'),'')::int as BedsCount
--			,	nullif(trim(xpath('/AMOUNT_BED/REPAIR_BED[1]/text()',x)::text,'{}'),'')::int as RepairBed
--			,	nullif(trim(xpath('/AMOUNT_BED/ID[1]/text()',x)::text,'{}'),'')::UUID as ID
--			,	nullif(trim(xpath('/AMOUNT_BED/PLANNED_BEDS_COUNT[1]/text()',x)::text,'{}'),'')::int as PlannedBedsCount
--			,	nullif(trim(xpath('/AMOUNT_BED/PLANNED_MALE_BED[1]/text()',x)::text,'{}'),'')::int as PlannedMaleBed
--			,	nullif(trim(xpath('/AMOUNT_BED/PLANNED_FEMALE_BED[1]/text()',x)::text,'{}'),'')::int as PlannedFemaleBed
--			,	nullif(trim(xpath('/AMOUNT_BED/DLSB[1]/text()',x)::text,'{}'),'')::UUID as Dlsb
--			from
--			(
--				select	unnest(xpath('/LPU/BRANCH/DIVISION/DIVISION_LINK_STRUCTURE_BED/AMOUNT_BED', x)) as x
--				from 
--				(
--					select "Xml" as x
--				) root
--			) AMOUNT_BED;					
--			-- INSERT akuz.T_DIVISION_LINK_STRUCTURE_BED
--			INSERT INTO akuz.T_DIVISION_LINK_STRUCTURE_BED(DivisionLinkStructureBedID, Division, StructureBed, OtherID)
--			SELECT	uuid_generate_v1()
--			,	d.DivisionID
--			,case when V020StructureBed = null then (select sb.StructureBedID from AKUZ.T_STRUCTURE_BED sb
--														join AKUZ.T_V002_PROFILE v0 on v0.Id = sb.V002Profile
--															where v0.Id = dsl.CodeSB limit 1)
--				  else (select sb.StructureBedID from AKUZ.T_STRUCTURE_BED sb
--														join AKUZ.T_V002_PROFILE v0 on v0.Id = sb.V002Profile
--															where v0.Id = dsl.CodeSB
--															and sb.Code = dsl.V020StructureBed 
--															and sb.DateBegin <= current_timestamp
--															and (sb.DateEnd is null or
--																	sb.DateEnd >= current_timestamp) limit 1) end 
--			,	dsl.ID
--			from Tmp_DIVISION_LINK_STRUCTURE_BED dsl
--			join akuz.T_DIVISION d on d.OtherID = dsl.Div
--			where d.IsActive = 1 and  not exists 
--			(
--				select * from akuz.T_DIVISION_LINK_STRUCTURE_BED dl where dl.OtherID = dsl.ID
--			);		
--			-- 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 Tmp_AMOUNT_BED am 
--			where am.ID = ab.OtherID;
			
--			update akuz.T_AMOUNT_BED ab
--			set DateEnd = CURRENT_DATE - INTERVAL '1 day'
--			from  Tmp_AMOUNT_BED abb
--			Join akuz.T_DIVISION_LINK_STRUCTURE_BED dlsb on dlsb.DivisionLinkStructureBedID = ab.DivisionLinkStructureBed
--			join akuz.T_DIVISION d on dlsb.Division =  d.DivisionID
--			join akuz.T_BRANCH b on b.LpuId = d.Branch
--			join akuz.T_LPU_MAIN lp on lp.LpuID = b.LpuMain
--			where abb.Lpu = lp.LpuCode and not exists (select * from Tmp_AMOUNT_BED abi where abi.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 Tmp_AMOUNT_BED ab
--			where not exists 
--			(
--				select * 
--				from akuz.T_AMOUNT_BED ab1 
--				where ab.ID = ab1.OtherID
--			);
--			-- Drop table
--			DROP TABLE if exists Tmp_DIVISION_LINK_STRUCTURE_BED;
--			DROP TABLE if exists Tmp_AMOUNT_BED;
--		END if;
--	end if;
--END;
--$$ LANGUAGE plpgsql;