-- =============================================
-- Author:		Vitacore (с)
-- Create date: 2020-02-03
-- Description:	Выгрузка/загрузка отделений в(из) ISZL
-- =============================================
drop procedure if exists public.sp_ISZL_DivisionXml;
CREATE procedure public.sp_ISZL_DivisionXml
(
	"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_DIVISION d where d.DivisionID = "ID")
		then
			_OperationType := 'Update';
		ELSIF "IsNew" = 0 and not exists(select* from akuz.T_DIVISION d where d.DivisionID = "ID")
		then
			_OperationType := 'Delete';
			_Result := 
			(
				select
				XMLELEMENT
				(
					name "DIVISION"
				,	xmlelement
					(
						name header
					,	XMLFOREST
						(
							"LpuCode"	 as "MO"
						,	_OperationType as "TYPE"
						)
					)
				,	xmlelement
					(
						name body
					,	xmlforest
						(
							br.BranchCode as "BR_CODE"
						,	d.Code		as "CODE"
						)
					)
				 )
				from AKUZ.T_DIVISION d
				inner join
					akuz.T_BRANCH br 
					on d.Branch = br.LpuID
				where d.DivisionID = "ID"
			);
		END if;

		_ContentEncoding := 'utf-8';
		_Type := 'DIVISION';
		_OwnerID := "ID";

		IF _OperationType <> 'Delete'
		then
			select
			XMLELEMENT
			(
				name "DIVISION"
			,	XMLFOREST
				(
					XMLFOREST
					(
						"LpuCode" as "MO"
					,	_OperationType as "TYPE"
					) as header
				)
			,	XMLFOREST
				(
					XMLFOREST
					(
						br.BranchCode 	as "BR_CODE"
					,	d.Code as "CODE"
					,	d.Name as "NAME"
					,	d.FullName as "FULLNAME"
					,	d.Adress as "ADDRESS"
					,	d.Branch as "BRANCH_ID"
					,	CASE d.DivisionType
							WHEN 12801 THEN 0
							WHEN 12802 THEN 1
							WHEN 12808 THEN 2
							WHEN 12809 THEN 3
							WHEN 12810 THEN 4
							ELSE 0 
						END as "DIVISION_TYPE"
					,	d.IsActive as "ACTIVE"
					) as body
				)
			)			
			from akuz.T_DIVISION d
			inner join 
				akuz.T_BRANCH br
				on d.Branch = br.LpuID
			into _Result
			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_v4(), "QueueName", ConvertTextToBinaryZip(_Result, ''), null, null, _ContentEncoding, _Type, null, 0, Current_TimeStamp::TIMESTAMP, _OwnerID;
	ELSE 
		IF "Xml" is not null
		then		
			-- Generate table Tmp_DIVISION
			drop table if exists Tmp_DIVISION;
			create temp table Tmp_DIVISION
			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]/BR_CODE[1]/text()',x2))[1]::varchar(16) as BrCode
			,	(xpath('/body[1]/CODE[1]/text()',x2))[1]::varchar(16) as Code
			,	(xpath('/body[1]/NAME[1]/text()',x2))[1]::text as NAME
			,	(xpath('/body[1]/FULLNAME[1]/text()',x2))[1]::text as FullName
			,	(xpath('/body[1]/ADDRESS[1]/text()',x2))[1]::text as Adress
			,	(xpath('/body[1]/DIVISION_TYPE[1]/text()',x2))[1]::text::int as DivisionType
			,	(xpath('/body[1]/ACTIVE[1]/text()',x2))[1]::text::boolean as Active
			from
			(
				select 
					unnest(xpath('/DIVISION/header', x)) as x1
				,	unnest(xpath('/DIVISION/body', x)) as x2
				from
				(
					select "Xml" as x
				) root
			) DIVISION;
			
			select td.OperationType 
			from Tmp_DIVISION td
			into _OperationType
			Limit 1;
			

			if _OperationType <> 'Delete'
			then
				-- update akuz.T_DIVISION
				update akuz.T_DIVISION d
				set	Code = div.Code
				,	Name = div.Name
				,	FullName = div.FullName
				,	Adress = div.Adress
				,	DivisionType = CASE div.DivisionType
									WHEN 0 THEN 12801
									WHEN 1 THEN 12802
									WHEN 2 THEN 12808
									WHEN 3 THEN 12809
									WHEN 4 THEN 12810
									ELSE 12801 
						       END
				,	IsActive = div.Active
				from Tmp_DIVISION div
				inner join 
					akuz.T_BRANCH br 
					on div.BrCode = br.BranchCode
				inner join 
					akuz.T_LPU_MAIN lm 
					on br.LpuMain = lm.LpuID
					and div.LpuMain = lm.Code					
				where div.Code = d.Code
				and d.Branch = br.LpuID;

				INSERT INTO akuz.T_DIVISION(DivisionID, Code, Name, FullName, Adress, Branch, DivisionType, IsRequestonDivision, IsRequestInHospital, IsActive, WorkDays, HospitalizationRecTime)
				SELECT uuid_generate_v4(), div.Code, div.Name, div.FullName, div.Adress, br.LpuID
					, CASE div.DivisionType
						WHEN 0 THEN 12801
						WHEN 1 THEN 12802
						WHEN 2 THEN 12808
						WHEN 3 THEN 12809
						WHEN 4 THEN 12810
						ELSE 12801 
					  END
					, '0', '0', div.Active, 31, '20000101'
				from AKUZ.T_DIVISION d
				inner join
					akuz.T_BRANCH br 
					on d.Branch = br.LpuID
				inner join 
					akuz.T_LPU_MAIN lm 
					on br.LpuMain = lm.LpuID
				inner join 
					Tmp_DIVISION div 
					on div.Code = d.Code 
					and div.BrCode = br.BranchCode 
					and div.LpuMain = lm.Code
				where not exists(select* from AKUZ.T_DIVISION dd where dd.Branch = br.LpuID and dd.Code = div.Code);
			ELSE
				delete from AKUZ.T_DIVISION d
				where exists
				(
					select* from akuz.T_BRANCH br
					inner join
						akuz.T_LPU_MAIN lm 
						on br.LpuMain = lm.LpuID
					inner join 
						Tmp_DIVISION div 
						on div.BrCode = br.BranchCode 
						and div.LpuMain = lm.Code
					where d.Branch = br.LpuID
					and div.Code = d.Code
				);
			END if;
		DROP TABLE if exists Tmp_DIVISION;
		END if;
	end if;
End;
$$ Language plpgsql;