-- =============================================
-- Author:		Vitacore (с)
-- Create date: 2020-02-03
-- Description:	Выгрузка/загрузка информации о количестве свободных мест в отделении
-- =============================================
drop procedure if exists public.sp_ISZL_FreeBedsXml;
CREATE procedure public.sp_ISZL_FreeBedsXml
(
	"LpuCode" varchar(255) DEFAULT NULL::varchar(255)
,	"Xml" xml DEFAULT NULL::xml
,	"IsNew" integer DEFAULT '1'::integer
,	"ID" uuid DEFAULT NULL::uuid
,	"QueueName" varchar(255) DEFAULT NULL::varchar(255)
)
AS
$BODY$
Declare	_Result text;
	_ContentEncoding varchar(255);
	_Type varchar(255);
	_OperationType varchar(255);
Begin
	if "Xml" is null
	then
		--выясняем тип операции
		IF "IsNew" = 1
		then
			_OperationType := 'Insert';
		elsif "IsNew" = 0 and exists(select* from akuz.T_FREE_BEDS_INFORMATION fb where fb.FreeBedsInformationID = "ID")
		then
			_OperationType := 'Update';
		elsif "IsNew" = 0 and not exists(select* from akuz.T_FREE_BEDS_INFORMATION fb where fb.FreeBedsInformationID = "ID")
		then
			_OperationType := 'Delete';
			_Result := 
			(
				select
				xmlelement
				(
					name "FREE_BEDS_INFORMATION"
				,	xmlforest
					(
						xmlforest
						(
							"LpuCode" as "MO"
						,	_OperationType as "TYPE"
						) as header
					)
				,	xmlforest
					(
						xmlforest
						(
							to_char(fb.ActualDate, 'YYYY-MM-DD')	as "ACTUAL_DATE"
						,	br.BranchCode							as "BR_CODE"
						,	d.Code									as "DIVISION"
						,	sb.Code									as "V020_STRUCTURE_BED"
						) as body
					)
				)
				from AKUZ.T_FREE_BEDS_INFORMATION fb
				inner join 
					akuz.T_DIVISION_LINK_STRUCTURE_BED dlsb 
					on fb.DivisionLinkStructureBed = dlsb.DivisionLinkStructureBedID
				inner join 
					akuz.T_DIVISION d 
					on dlsb.Division = d.DivisionID
				inner join 
					akuz.T_BRANCH br 
					on d.Branch = br.LpuID
				inner join 
					akuz.T_STRUCTURE_BED sb 
					on dlsb.StructureBed = sb.StructureBedID
				where fb.FreeBedsInformationID = "ID"
			);
		end if;

		_ContentEncoding := 'utf-8';
		_Type := 'FREE_BEDS_INFORMATION';

		IF _OperationType <> 'Delete'
		then
			_Result := 
			xmlelement 
			(
				name "FREE_BEDS_INFORMATION"
			,	(
					select
					xmlagg
					( 
						xmlforest
						(
							xmlforest
							(
								"LpuCode" as "MO"
							,	_OperationType as "TYPE"
							) as header
						,	xmlforest
							(
								to_char(fb.ActualDate, 'YYYY-MM-DD')	as "ACTUAL_DATE"
							,	fb.Amount								as "AMOUNT"
							,	fb.FemaleBed							as "FEMALE_BED"
							,	fb.MaleBed								as "MALE_BED"
							,	fb.ChildBed								as "CHILD_BED"
							,	br.BranchCode							as "BR_CODE"
							,	div.Code									as "DIVISION"
							,	strb.Code								as "V020_STRUCTURE_BED"
							) as body
						)
					)
					from akuz.T_FREE_BEDS_INFORMATION fb
					inner join 
						AKUZ.T_DIVISION_LINK_STRUCTURE_BED dlsb 
						on dlsb.DivisionLinkStructureBedID = fb.DivisionLinkStructureBed
					inner join 
						AKUZ.T_DIVISION div 
						on div.DivisionID = dlsb.Division
					inner join
						akuz.T_BRANCH br 
						on div.Branch = br.LpuID
					inner join 
						AKUZ.T_STRUCTURE_BED strb 
						on strb.StructureBedID = dlsb.StructureBed
					where fb.FreeBedsInformationID = "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, "ID";
	ELSIF "Xml" is not null
	then
		-- Generate table #FREE_BEDS_INFORMATION
		drop table if exists Tmp_FREE_BEDS_INFORMATION;
		create temp table Tmp_FREE_BEDS_INFORMATION
		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
		
		,	to_timestamp((xpath('/body[1]/ACTUAL_DATE[1]/text()',x2))[1]::text, 'YYYY-MM-DD') as ACTUAL_DATE
		,	(xpath('/body[1]/AMOUNT[1]/text()',x2))[1]::text::int as AMOUNT
		,	(xpath('/body[1]/FEMALE_BED[1]/text()',x2))[1]::text::int as FEMALE_BED
		,	(xpath('/body[1]/MALE_BED[1]/text()',x2))[1]::text::int as MALE_BED
		,	(xpath('/body[1]/CHILD_BED[1]/text()',x2))[1]::text::int as CHILD_BED
		,	(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('/FREE_BEDS_INFORMATION/header', x)) as x1
			,	unnest(xpath('/FREE_BEDS_INFORMATION/body', x)) as x2
			from
			(
				select "Xml" as x
			) root
		) FREE_BEDS_INFORMATION;
		
		select td.OperationType 
		from Tmp_FREE_BEDS_INFORMATION td
		into _OperationType
		Limit 1;
		
		if _OperationType <> 'Delete'
		then
			UPDATE AKUZ.T_FREE_BEDS_INFORMATION fb
			SET	Amount = t.AMOUNT
			,	FemaleBed = t.FEMALE_BED
			,	MaleBed = t.MALE_BED
			,	ChildBed = t.CHILD_BED
			from Tmp_FREE_BEDS_INFORMATION t				
			where fb.ActualDate = t.ACTUAL_DATE
			and exists
			(
				select* from akuz.T_DIVISION_LINK_STRUCTURE_BED dlsb 
				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_lpu_main lm 
					on br.LpuMain = lm.LpuID
				inner join 
					akuz.T_STRUCTURE_BED sb 
					on dlsb.StructureBed = sb.StructureBedID
				where fb.DivisionLinkStructureBed = dlsb.DivisionLinkStructureBedID
				and lm.LpuCode = t.LpuMain 
				and br.BranchCode = t.BR_CODE
				and div.Code = t.DIVISION
				and sb.Code = t.V020_STRUCTURE_BED
			);
		
			INSERT INTO AKUZ.T_DIVISION_LINK_STRUCTURE_BED(DivisionLinkStructureBedID, StructureBed, Division, DivisionType, OtherID)
			select uuid_generate_v4(), sb.Code, div.DivisionID, null, null
			from Tmp_FREE_BEDS_INFORMATION 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_FREE_BEDS_INFORMATION(ActualDate, FreeBedsInformationID, Amount, DivisionLinkStructureBed, FemaleBed, MaleBed, ChildBed)
			SELECT t.ACTUAL_DATE, uuid_generate_v4(), t.AMOUNT, dlsb.DivisionLinkStructureBedID, t.FEMALE_BED, t.MALE_BED, t.CHILD_BED
			from Tmp_FREE_BEDS_INFORMATION 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_FREE_BEDS_INFORMATION ab1 where t.ACTUAL_DATE = ab1.ActualDate and dlsb.DivisionLinkStructureBedID = ab1.DivisionLinkStructureBed
			);		
		END if;

		-- Drop table
		DROP TABLE if exists Tmp_FREE_BEDS_INFORMATION;
	END if;
End;
$BODY$ Language plpgsql;