-- =============================================
-- Author:		Vitacore (с)
-- Create date: 2020-02-03
-- Description:	Выгрузка/загрузка информации о количестве коек в профиле в(из) ISZL
-- =============================================
drop procedure if exists public.sp_ISZL_AmountXml;
CREATE procedure public.sp_ISZL_AmountXml
( 
	"LpuCode" varchar(255) = null
,	"Xml" xml = null
,	"IsNew" integer = 1
,	"ID" UUID = null
,	"QueueName" varchar(255) = null
)
As 
$$
Declare	_Result text;
			_ContentEncoding varchar(255);
			_Type varchar(255);
			_OperationType varchar(255);
			_OwnerID UUID;
Begin
	IF "Xml" is null
	then
	--выясняем тип операции
		IF "IsNew" = 1
		then	
			_OperationType := 'Insert';
		ELSIF "IsNew" = 0 and exists (select * from akuz.T_AMOUNT_BED a where a.AmountBedID = "ID")
		then	
			_OperationType := 'Update';
		ELSIF "IsNew" = 0 and not exists(select * from akuz.T_AMOUNT_BED a where a.AmountBedID = "ID")
		then
			_OperationType := 'Delete';
			_Result := 
			(
				select
				xmlelement
				( 
					name "AMOUNT_BED"
				,	xmlelement
					(
						name header
					,	xmlforest
						(
							"LpuCode" as "LPU"
						,	_OperationType as "TYPE"
						)
					)
				,	xmlelement
					(
						name body
					,	xmlforest
						(
							br.BranchCode		as "BR_CODE"
						,	div.Code			as "DIVISION"
						,	sb.Code				as "V020_STRUCTURE_BED"
						)
					)
				)
				from AKUZ.T_AMOUNT_BED ab
				inner join 
					akuz.T_DIVISION_LINK_STRUCTURE_BED dlsb 
					on ab.DivisionLinkStructureBed = dlsb.DivisionLinkStructureBedID
				inner join 
					akuz.T_DIVISION div 
					on dlsb.Division = div.DivisionID
				inner join 
					akuz.T_BRANCH br 
					on div.Branch = br.LpuID
				inner join 
					akuz.T_STRUCTURE_BED sb 
					on dlsb.StructureBed = sb.StructureBedID
				where ab.AmountBedID = "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"
					)
				)
			,	XMLELEMENT
				(
					name body
				,	xmlforest
					(
						to_char(ab.DateBegin, 'YYYY-MM-DD')	as "DATE_BEGIN"
					,	to_char(ab.DateEnd, 'YYYY-MM-DD')	as "DATE_END"
					,	br.BranchCode						as "BR_CODE"
					,	div.Code							as "DIVISION"
					,	sb.Code								as "V020_STRUCTURE_BED"
					)
				)
			)
			from akuz.T_AMOUNT_BED ab
			inner join 
				akuz.T_DIVISION_LINK_STRUCTURE_BED dlsb 
				on ab.DivisionLinkStructureBed = dlsb.DivisionLinkStructureBedID
			inner join 
				akuz.T_DIVISION div 
				on dlsb.Division = div.DivisionID
			inner join 
				akuz.T_BRANCH br 
				on div.Branch = br.LpuID
			inner join 
				akuz.T_STRUCTURE_BED sb 
				on dlsb.StructureBed = sb.StructureBedID
			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_v4(), "QueueName", ConvertTextToBinaryZip(_Result, ''), null, null, _ContentEncoding, _Type, null, 0, Current_TimeStamp::TIMESTAMP, _OwnerID;
	ELSE 
		IF "Xml" is not null
		then		
			-- Generate table #AMOUNT_BED
			drop table if exists TmpAMOUNT_BED;
			create temp table TmpAMOUNT_BED
			as
			select
				(xpath('/header[1]/TYPE[1]/text()',x1))[1]::text as OperationType
			,	(xpath('/header[1]/MO[1]/text()',x1))[1]::varchar(6) as LpuMain
			,	(xpath('/body[1]/ADRESS[1]/text()',x2))[1]::varchar(256) as Adress
			,	to_timestamp((xpath('/body[1]/DATE_BEGIN[1]',x2))[1]::text, 'DD.MM.YYYY') as DateBegin
			,	to_timestamp((xpath('/body[1]/DATE_END[1]',x2))[1]::text, 'DD.MM.YYYY') as DateEnd
			,	(xpath('/body[1]/BR_CODE[1]/text()',x2))[1]::varchar(16) as BR_CODE
			,	(xpath('/body[1]/DIVISION[1]/text()',x2))[1]::varchar(16) as DIVISION
			,	(xpath('/body[1]/V020_STRUCTURE_BED[1]/text()',x2))[1]::text::int as V020_STRUCTURE_BED
			from
			(
				select 
					unnest(xpath('/AMOUNT_BED/header', x)) as x1
				,	unnest(xpath('/AMOUNT_BED/body', x)) as x2
				from
				(
					select "Xml" as x
				) root
			) AMOUNT_BED;
			
			select t.OperationType 
			from TmpAMOUNT_BED t
			into _OperationType
			Limit 1;

			if _OperationType <> 'Delete'
			then
				update akuz.T_AMOUNT_BED ab
				set	DateEnd = t.DateEnd
				from TmpAMOUNT_BED t
				inner join
					akuz.T_DIVISION div
					on div.Code = t.DIVISION
				inner join
					akuz.T_BRANCH br 
					on div.Branch = br.LpuID
				inner join
					akuz.t_lpu_main lm 
					on br.LpuMain = lm.LpuID
				inner join 
					akuz.T_DIVISION_LINK_STRUCTURE_BED dlsb 
					on dlsb.Division = div.DivisionID
				inner join 
					akuz.T_STRUCTURE_BED sb 
					on dlsb.StructureBed = sb.StructureBedID
					and sb.Code = t.V020_STRUCTURE_BED
				where ab.DateBegin = t.DateBegin
				and lm.LpuCode = t.LpuMain 
				and br.BranchCode = t.BR_CODE
				and ab.DivisionLinkStructureBed = dlsb.DivisionLinkStructureBedID;
		 
				INSERT INTO akuz.T_DIVISION_LINK_STRUCTURE_BED(DivisionLinkStructureBedID, StructureBed, Division, DivisionType, OtherID)
				select uuid_generate_v4(), sb.Code, div.DivisionID, null, null
				from TmpAMOUNT_BED t
				inner join
					akuz.T_DIVISION div 
					on div.Code = t.DIVISION
				inner join
					akuz.T_BRANCH br 
					on div.Branch = br.LpuID 
					and br.BranchCode = t.BR_CODE
				inner join
					akuz.t_lpu_main lm 
					on br.LpuMain = lm.LpuID 
					and lm.LpuCode = t.LpuMain
				inner join
					akuz.T_STRUCTURE_BED sb 
					on sb.Code = t.V020_STRUCTURE_BED
				where not exists
				(	
					select* from akuz.T_DIVISION_LINK_STRUCTURE_BED dlsb
					inner join
						akuz.T_DIVISION div1 
						on dlsb.Division = div1.DivisionID
					inner join
						akuz.T_BRANCH br1 
						on div1.Branch = br1.LpuID
					inner join
						akuz.t_lpu_main lm1 
						on br1.LpuMain = lm1.LpuID
					inner join
						akuz.T_STRUCTURE_BED sb1 
						on dlsb.StructureBed = sb1.StructureBedID
					where lm1.LpuCode = t.LpuMain 
					and br1.BranchCode = t.BR_CODE
					and div1.Code = t.DIVISION
					and sb1.Code = t.V020_STRUCTURE_BED
				);
				
				insert into AKUZ.T_AMOUNT_BED(AmountBedID, DateBegin, DateEnd, DivisionLinkStructureBed, FemaleBed, MaleBed, BedsCount, RepairBed, OtherID
											  , PlannedFemaleBed, PlannedMaleBed, PlannedBedsCount)
				select uuid_generarte_v4(), t.DateBegin, t.DateEnd, dlsb.DivisionLinkStructureBedID, 0, 0, 0, 0, null, 0, 0, 0
				from TmpAMOUNT_BED t
				inner join 
					akuz.T_DIVISION div 
					on div.Code = t.DIVISION
				inner join 
					akuz.T_BRANCH br 
					on div.Branch = br.LpuID and br.BranchCode = t.BR_CODE
				inner join 
					akuz.t_lpu_main lm 
					on br.LpuMain = lm.LpuID and lm.LpuCode = t.LpuMain
				inner join 
					akuz.T_STRUCTURE_BED sb 
					on sb.Code = t.V020_STRUCTURE_BED
				inner join 
					akuz.T_DIVISION_LINK_STRUCTURE_BED dlsb 
					on dlsb.Division = div.DivisionID and dlsb.StructureBed = sb.StructureBedID
				where not exists
				(
					select* from AKUZ.T_AMOUNT_BED ab1 where t.DateBegin = ab1.DateBegin and dlsb.DivisionLinkStructureBedID = ab1.DivisionLinkStructureBed
				);
			END if;
			-- Drop table
			Drop Table If Exists TmpAMOUNT_BED;
		END if;
	end if;
End;
$$ Language plpgsql;