DROP function if exists sp_AmountbedLoad_folder;
drop procedure if exists sp_AmountbedLoad_folder;

CREATE procedure sp_AmountbedLoad_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
	,	to_timestamp((xpath('//DATE_BEGIN[1]/text()', b))[1]::varchar(10), 'YYYY-MM-DD')	as DATE_BEGIN
	,	to_timestamp((xpath('//DATE_END[1]/text()', b))[1]::varchar(10), 'YYYY-MM-DD')		as DATE_END
	,	(xpath('//BR_CODE[1]/text()', b))[1]::varchar(17) 									as BR_CODE	
	,	(xpath('//DIVISION[1]/text()', b))[1]::varchar(16) 									as DIVISION		
	,	(xpath('//V020_STRUCTURE_BED[1]/text()', b))[1]::text::int 							as V020_STRUCTURE_BED		
	,	(xpath('//AmountBedID[1]/text()', b))[1]::text::uuid								as AmountBedID --заглушка
	,	(xpath('//DivisionID[1]/text()', b))[1]::text ::uuid    							as DivisionID --заглушка
	,	(xpath('//BranchID[1]/text()', b))[1]::text::uuid       							as BranchID --заглушка
	from
	(
		select unnest(xpath('/AMOUNT_BED/HEADER', x)) as h, unnest(xpath('/AMOUNT_BED/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);
	
	-- поиск отделения и филиала
	update TmpZAP z
	set AmountBedID = 
	(
		select ab.AmountBedID from iszl.T_AMOUNT_BED ab
		inner join
			iszl.T_DIVISION div
			on div.DivisionID = ab.DivisionObj
		left join
			iszl.T_BRANCH br
			on div.Branch = br.BranchID 
		where ab.V020_STRUCTURE_BED = z.V020_STRUCTURE_BED 
		and div.CODE = z.DIVISION
		and (div.LpuMain = z.MO OR br.F003Mo = z.MO) 
		limit 1
	)
	,	DivisionID = 
	(
		select div.DivisionID from iszl.T_DIVISION div 
		left join
			iszl.T_BRANCH br
			on div.Branch = br.BranchID 
		where div.CODE = z.DIVISION
		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 into TmpErrors (CODE,COMMENT)
	select 2, 'Не найдено подразделение (' || z.BR_CODE || ') Для МО (' || z.MO || '). N_ZAP(' || z.N_ZAP::text || ')'
	from TmpZAP z
	where z.BranchID is null and z.BR_CODE is not null
	union all
	select 3, 'Не найдено отделение (' || z.DIVISION || '). Для подразделение (' || z.BR_CODE || '). Для МО (' || z.MO || '). N_ZAP(' || z.N_ZAP::text || ')'
	from TmpZAP z
	where z.DivisionID is null and z.BranchID is not null
	union all
	select 6, 'Неверный тип операции. N_ZAP(' || z.N_ZAP::text || ')'
	from TmpZAP z
	where z.TYPE <> 'Insert' and z.TYPE <> 'Update' and z.TYPE <> 'Delete';
	
	--Insert
	if not exists(select* from TmpErrors)
	then
		-- удаляем, если есть
		delete from iszl.T_AMOUNT_BED t
		where exists(select* from TmpZAP z where t.AmountBedID = z.AmountBedID and z.TYPE = 'Insert');
	
		-- вставка, если нет ошибок
		insert into ISZL.T_AMOUNT_BED(AmountBedID, DATE_BEGIN, DATE_END, BR_CODE, DIVISION, V020_STRUCTURE_BED, DivisionObj)
		select z.ID, z.DATE_BEGIN, z.DATE_END, z.BR_CODE, z.DIVISION, z.V020_STRUCTURE_BED, z.DivisionID
		from TmpZAP z
		where z.TYPE = 'Insert';

		if exists(select* from information_schema.columns c where c.table_schema = 'iszl' and c.table_name = 't_amount_bed' and c.column_name='filename')
		then
			update ISZL.T_AMOUNT_BED hr
			set FILENAME = z.FILENAME
			from TmpZAP z 
			where z.ID = hr.AmountBedID
			and z.TYPE = 'Insert';
		end if;
	end if;
	
	--Update
	if not exists(select* from TmpErrors)
	then
		insert into TmpErrors(Code, Comment)
		select 4, 'Запись не найдена в ранее загруженных списках'
		from TmpZAP z
		where z.AmountBedID is null and z.TYPE = 'Update';
		
		UPDATE ISZL.T_AMOUNT_BED t
	   	SET DATE_BEGIN = z.DATE_BEGIN
		,	DATE_END = z.DATE_END
		,	BR_CODE = z.BR_CODE
		,	DIVISION = z.DIVISION
		,	V020_STRUCTURE_BED = z.V020_STRUCTURE_BED
		,	DivisionObj = z.DivisionID
		from TmpZAP z
		where exists(select* from TmpErrors) and t.AmountBedID = z.AmountBedID and z.TYPE = 'Update';
		
		if exists(select* from information_schema.columns c where c.table_schema = 'iszl' and c.table_name = 't_amount_bed' and c.column_name='filename')
		then
			update ISZL.T_AMOUNT_BED hr
			set FILENAME = z.FILENAME
			from TmpZAP z 
			where z.ID = hr.AmountBedID
			and z.TYPE = 'Update';
		end if;
	end if;
	
	--Delete
	if exists(select* from TmpErrors)
	then
		insert into TmpErrors(Code, Comment)
		select 5, 'Запись не найдена в ранее загруженных списках'
		from TmpZAP z
		where z.AmountBedID is null and z.TYPE = 'Delete';

		delete from ISZL.T_AMOUNT_BED t
		where exists(select* from TmpZAP z where t.AmountBedID = z.AmountBedID and z.TYPE = 'Delete')
		and not exists(select* from TmpErrors);
	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;