DROP FUNCTION if exists sp_DivisionLoad_folder;
drop procedure if exists sp_DivisionLoad_folder;

CREATE procedure sp_DivisionLoad_folder
(
	x xml
,	"FileID" uuid default null::uuid
,	"Worker" uuid default null::uuid	
,	inout "Res" boolean default null::boolean
,	inout "Err" xml default null::xml
)
as $BODY$
begin
	Drop Table If Exists TmpZAP;
	Create Temp Table TmpZAP
	As
	select
		gen_random_uuid() 										as ID
	,	(xpath('//FILENAME[1]/text()', h))[1]::varchar(26) 		as FILENAME
	,	(xpath('//MO[1]/text()', h))[1]::varchar(6) 			as MO	
	,	(xpath('//N_ZAP[1]/text()', b))[1]::text::int           as N_ZAP
	,	(xpath('//TYPE[1]/text()', b))[1]::varchar(6)           as TYPE
	,	(xpath('//BR_CODE[1]/text()', b))[1]::varchar(17) 		as BR_CODE		
	,	(xpath('//CODE[1]/text()', b))[1]::varchar(16) 			as CODE		
	,	(xpath('//NAME[1]/text()', b))[1]::varchar(128) 		as NAME		
	,	(xpath('//FULLNAME[1]/text()', b))[1]::varchar(255) 	as FULLNAME		
	,	(xpath('//ADDRESS[1]/text()', b))[1]::varchar(255) 		as ADDRESS		
	,	(xpath('//DIVISION_TYPE[1]/text()', b))[1]::text::int	as DIVISION_TYPE
	,	(xpath('//ACTIVE[1]/text()', b))[1]::text::int          as ACTIVE
	,	(xpath('//DivisionID[1]/text()', b))[1]::text::uuid     as DivisionID
	,	(xpath('//BranchID[1]/text()', b))[1]::text::uuid       as BranchID
	from
	(
		select unnest(xpath('/DIVISION/HEADER', x)) as h, unnest(xpath('/DIVISION/BODY', x)) as b
	) t1;
	
	Drop Table If Exists TmpErrors;
	Create Temp Table TmpErrors
	as
	select 1::int as Code, ('МО не найдена. N_ZAP(' || z.N_ZAP::text || ')')::varchar(250) as Comment
	from TmpZAP z
	where not exists(select * from iszl.t_f003 lm where lm.mcod = z.MO)
	union all
	select 4, 'Неверный тип операции.'
	from TmpZAP z
	where z.TYPE <> 'Insert' and z.TYPE <> 'Update' and z.TYPE <> 'Delete';
	
	-- поиск отделения и филиала
	update TmpZAP z
	set DivisionID = 
	(
		select div.DivisionID 
		from iszl.T_DIVISION div 
		left join
			iszl.T_BRANCH br
			on div.Branch = br.BranchID 
		where div.CODE = z.CODE
		and (div.LpuMain = z.MO OR br.F003Mo = z.MO)
		limit 1
	)
	,	BranchID = (select br.BranchID from iszl.T_BRANCH br where br.BR_CODE = z.BR_CODE and br.F003Mo = z.MO limit 1);
	
	-- Insert
	if not exists(select* from TmpErrors)
	then
		-- нельзя удалять, тк могут быть профили коек в отделении
		-- удаляем, если есть
		--delete t from [ISZL].[T_DIVISION] t
		--join #ZAP zap on t.DivisionID = zap.DivisionID
		insert into TmpErrors(CODE, COMMENT)
		select 2, 'Отделение было добавлено ранее. N_ZAP(' || z.N_ZAP || ')'
		from TmpZAP z
		where z.DivisionID is not null and z.TYPE = 'Insert';

		-- вставка, если нет ошибок
		insert into ISZL.T_DIVISION(DivisionID, NAME, CODE, FULLNAME, ADDRESS, ACTIVE, Branch, V006MedicalTerms, BR_CODE, LpuMain)
		select z.ID, z.NAME, z.CODE, z.FULLNAME, z.ADDRESS, z.ACTIVE::boolean, z.BranchID
		,z.DIVISION_TYPE
		,z.BR_CODE
		,case when z.BranchID is null then z.MO else null end
		from TmpZAP z
		where not exists(select* from TmpErrors) and z.TYPE = 'Insert';

		if exists(select* from information_schema.columns c where c.table_schema = 'iszl' and c.table_name = 't_division' and c.column_name='filename')
		then
			update ISZL.T_DIVISION hr
			set FILENAME = z.FILENAME
			from TmpZAP z 
			where z.ID = hr.DIVISIONID
			and z.TYPE = 'Insert'
			and not exists(select* from TmpErrors);
		end if;
	end if;
	
	--Update
	if	not exists(select* from TmpErrors)
	then
		UPDATE ISZL.T_DIVISION t
	   	SET	NAME = z.NAME
		,	CODE = z.CODE
		,	FULLNAME = z.FULLNAME
		,	ADDRESS = z.ADDRESS
		,	ACTIVE = (case when z.ACTIVE = 0 then '0' else '1' end)::boolean
		,	Branch = z.BranchID
		,	V006MedicalTerms = z.DIVISION_TYPE
		,	BR_CODE = z.BR_CODE
		,	LpuMain = case when z.BranchID is null then z.MO else null end
		from TmpZAP z
		where z.DivisionID is not null and t.DivisionID = z.DivisionID and z.TYPE = 'Update';

		-- вставка, если не найдена
		insert into ISZL.T_DIVISION(DivisionID, NAME, CODE, FULLNAME, ADDRESS, ACTIVE, Branch, V006MedicalTerms, BR_CODE, LpuMain)
		select z.ID, z.NAME, z.CODE, z.FULLNAME, z.ADDRESS, z.ACTIVE::boolean, z.BranchID
		, z.DIVISION_TYPE
		, z.BR_CODE, case when z.BranchID is null then z.MO else null end
		from TmpZAP z
		where z.DivisionID is null and z.TYPE = 'Update';

		if exists(select* from information_schema.columns c where c.table_schema = 'iszl' and c.table_name = 't_division' and c.column_name = 'filename')
		then
			update ISZL.T_DIVISION hr
			set FILENAME = z.FILENAME
			from TmpZAP z 
			where z.FreeBedsInformationID = hr.DivisionID
			and z.TYPE = 'Update';
		end if;
	end if;

	--Delete	
	if not exists(select* from TmpErrors)
	then
		insert into TmpErrors(Code, Comment)
		select 3, 'Запись не найдена в ранее загруженных списках. N_ZAP(' || z.N_ZAP::text || ')'
		from TmpZAP z
		where z.DivisionID is null AND z.TYPE = 'Delete';

		delete from ISZL.T_DIVISION hosp
		where exists(select* from TmpZAP z where z.DivisionID = hosp.DivisionID and z.TYPE = 'Delete');
	end if;
	
   	create temp table temp_table(Res boolean,Err text);
   	insert into temp_table(Res,Err)
		select case when exists(select* from TmpErrors) then '0'::boolean else '1'::boolean end
		, case when exists(select* from TmpErrors) then '<?xml version="1.0" encoding="Windows-1251"?>
		' || 
		(
			xmlelement
			(
				name "ROOT"
			,	(
					select
					xmlagg
					(
						xmlelement
						(
							name "ERR"
						,	xmlforest
							(
								te.CODE AS "CODE"
							,	te.COMMENT AS "COMMENT"
							)
						)
					)
					FROM TmpErrors te
				)
			)
		)::text end;

   	select Res from temp_table into "Res";
	select Err from temp_table into "Err";

	drop table if exists temp_table;
	DROP TABLE if exists TmpZAP;
	DROP TABLE if exists TmpErrors;

end;
$BODY$ LANGUAGE plpgsql;
