DROP function if exists sp_FreeBedsInformationLoad_folder;
drop procedure if exists sp_FreeBedsInformationLoad_folder;

create procedure sp_FreeBedsInformationLoad_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('//ACTUAL_DATE[1]/text()', b))[1]::varchar(10), 'YYYY-MM-DD')	as ACTUAL_DATE
	,	(xpath('//AMOUNT[1]/text()', b))[1]::text::int                          			as AMOUNT
	,	(xpath('//FEMALE_BED[1]/text()', b))[1]::text::int                          		as FEMALE_BED
	,	(xpath('//MALE_BED[1]/text()', b))[1]::text::int                          			as MALE_BED
	,	(xpath('//CHILD_BED[1]/text()', b))[1]::text::int                          			as CHILD_BED
	,	(xpath('//BR_CODE[1]/text()', b))[1]::varchar(16) 									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('//FreeBedsInformationID[1]/text()', b))[1]::text::uuid                      as FreeBedsInformationID --заглушка
	,	(xpath('//AmountBedID[1]/text()', b))[1]::text::uuid                          		as AmountBedID --заглушка	
	from
	(
		select unnest(xpath('/FREE_BEDS_INFORMATION/HEADER', x)) as h, unnest(xpath('/FREE_BEDS_INFORMATION/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 2, 'Классификатор профиля койки (' || z.V020_STRUCTURE_BED::text || ') не найден. N_ZAP(' || z.N_ZAP::text || ')'
	from TmpZAP z
	where not exists(select* from iszl.T_V020_STRUCTURE_BED t where t.IDK_PR = z.V020_STRUCTURE_BED);
	
	-- поиск отделения и филиала
	update TmpZAP z
	set FreeBedsInformationID = 
	(
		select fi.FreeBedsInformationID
		from iszl.T_FREE_BEDS_INFORMATION fi
		inner join
			iszl.T_AMOUNT_BED ab
			on ab.AmountBedID = fi.AmountBed
		inner join
			iszl.T_DIVISION div
			on div.DivisionID = ab.DivisionObj
		left join
			iszl.T_BRANCH br
			on div.Branch = br.BranchID 
		where fi.ACTUAL_DATE = z.ACTUAL_DATE
		and 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
	)
	,	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
	);
	
	insert into TmpErrors(Code, Comment)
	select 3, 'Профиль койки не найден. N_ZAP(' || z.N_ZAP::text || ')'
	from TmpZAP z
	where z.AmountBedID is 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_FREE_BEDS_INFORMATION t
		where exists(select* from TmpZAP z where t.FreeBedsInformationID = z.FreeBedsInformationID and z.TYPE = 'Insert');

		-- вставка, если нет ошибок
		insert into ISZL.T_FREE_BEDS_INFORMATION(FreeBedsInformationID, ACTUAL_DATE, AMOUNT, FEMALE_BED, MALE_BED, CHILD_BED, BR_CODE, DIVISION, V020_STRUCTURE_BED, AmountBed)
		select z.ID, z.ACTUAL_DATE, z.AMOUNT, z.FEMALE_BED, z.MALE_BED, z.CHILD_BED, z.BR_CODE, z.DIVISION, z.V020_STRUCTURE_BED, z.AmountBedID
		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_free_beds_information' and c.column_name='filename')
		then
			update ISZL.T_FREE_BEDS_INFORMATION hr
			set FILENAME = z.FILENAME
			from TmpZAP z 
			where z.ID = hr.FreeBedsInformationID
			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.FreeBedsInformationID is null and z.TYPE = 'Update';
	
		UPDATE ISZL.T_FREE_BEDS_INFORMATION t
	   	SET ACTUAL_DATE = z.ACTUAL_DATE
		,	AMOUNT = z.AMOUNT
		,	FEMALE_BED = z.FEMALE_BED
		,	MALE_BED = z.MALE_BED
		,	CHILD_BED = z.CHILD_BED
		,	BR_CODE = z.BR_CODE
		,	DIVISION = z.DIVISION
		,	V020_STRUCTURE_BED = z.V020_STRUCTURE_BED
		,	AmountBed = z.AmountBedID
		from TmpZAP z
		where not exists(select* from TmpErrors)
		and t.FreeBedsInformationID = z.FreeBedsInformationID 
		and z.TYPE = 'Update';

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

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

		delete from ISZL.T_FREE_BEDS_INFORMATION hosp
		where exists(select* from TmpZAP z where z.FreeBedsInformationID = hosp.FreeBedsInformationID 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;