-- =============================================
-- Author:		Vitacore (с)
-- Create date: 2020-02-03
-- Description:	Выгрузка/загрузка справочника Филиалов в(из) ISZL
-- =============================================
drop procedure if exists public.sp_ISZL_BranchXml;
CREATE procedure public.sp_ISZL_BranchXml
(
	"LpuCode" text = 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_BRANCH br where br.LpuID = "ID")
		then
			_OperationType := 'Update';
		ELSIF "IsNew" = 0 and not exists(select* from akuz.T_BRANCH br where br.LpuID = "ID")
		then
			_OperationType := 'Delete';
			_Result := XMLELEMENT
				  (
					name "BRANCH"
				  ,	XMLELEMENT
					(
						name header
					,	XMLFOREST
						(
							"LpuCode" as "LPU"
						,	_OperationType as "TYPE"
						)
					)
				  ); 
		END if;

		_ContentEncoding := 'utf-8';
		_Type := 'BRANCH';
		_OwnerID := "ID";
		
		IF _OperationType <> 'Delete'
		then
			select 	
			XMLELEMENT
			(
				name "BRANCH"
			,	XMLELEMENT
				(
					name header
				,	XMLFOREST
					(
						"LpuCode" as "LPU"
					,	_OperationType as "TYPE"
					)
				)
			,	XMLELEMENT
				(
					name body
				,	XMLFOREST
					(
						br.BranchCode	as "BR_CODE"
					,	br.Name	as "NAME"
					,	br.AddressString as "ADDRESS"
					,	br.RegistryPhones as "PHONE"
					,	coalesce(fmo.chief, '') as "RUCV"
					,	br.IsActive as "ACTIVE"
					)
				)
			)
			from akuz.T_BRANCH br
			left join 
				akuz.T_FER_MO fmo 
				on br.LpuID = fmo.Branch
			into _Result
			where br.LpuID = "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;
	ELSIF "Xml" is not null
	then
		-- Generate table #BRANCH
		drop table if exists Tmp_BRANCH;
		create temp table Tmp_BRANCH
		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]/NAME[1]/text()',x2))[1]::text as NAME
		,	(xpath('/body[1]/ADDRESS[1]/text()',x2))[1]::text as AddressString
		,	(xpath('/body[1]/PHONE[1]/text()',x2))[1]::text as RegistryPhones
		,	(xpath('/body[1]/RUCV[1]/text()',x2))[1]::text as RUCV
		,	(xpath('/body[1]/ACTIVE[1]/text()',x2))[1]::text::boolean as Active
		from
		(
			select 
				unnest(xpath('/BRANCH/header', x)) as x1
			,	unnest(xpath('/BRANCH/body', x)) as x2
			from
			(
				select "Xml" as x
			) root
		) BRANCH;
		
		select tmp.OperationType 
		from Tmp_Branch tmp
		into _OperationType
		Limit 1;

		if _OperationType <> 'Delete'
		then
			update akuz.T_BRANCH br
			set	BranchCode = b.BranchCode
			,	Name = b.Name
			,	AddressString = b.AddressString
			,	RegistryPhones = b.RegistryPhones
			,	IsActive = b.Active
			from Tmp_BRANCH b
			inner join
				akuz.T_LPU_MAIN lm 
				on lm.Code = b.LpuMain
			where b.BranchCode = br.BranchCode
			and br.LpuMain = lm.LpuID;

			insert into akuz.T_BRANCH(LpuID, BranchCode, Name, DateAdd, AddressString, NameFull, RegistryPhones, LpuMain, LpuType, Category, OtherID, IsActive, BranchSettings)
			SELECT uuid_generate_v4(), b.BranchCode, b.Name, now()::timestamp, b.AddressString, b.Name, b.RegistryPhones
			,	(select lpu.LpuID from akuz.T_LPU_MAIN lpu where lpu.LpuCode = b.LpuMain limit 1)
			,	3, 2, null, b.Active, 0
			from Tmp_BRANCH b
			where not exists 
			(
				select * from akuz.T_BRANCH b1 
				inner join 
					akuz.T_LPU_MAIN lm1
					on b1.LpuMain = lm1.LpuID
				where b.BranchCode =  b1.BranchCode and lm1.Code = b.LpuMain
			);
		ELSE
			delete from AKUZ.T_BRANCH br
			where exists
			(
				select* from akuz.T_LPU_MAIN lm 
				inner join 
					Tmp_BRANCH b 
					on lm.Code = b.LpuMain
				where br.LpuMain = lm.LpuID
				and b.BranchCode = br.BranchCode 
			);
		END if;
		
		-- Drop table
		DROP TABLE if exists Tmp_BRANCH;
	END if;
End;
$$ Language plpgsql;