DROP FUNCTION if exists sp_CancelHospitalisationLoad_folder;
drop procedure if exists sp_CancelHospitalisationLoad_folder;

CREATE procedure sp_CancelHospitalisationLoad_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[1]/text()', b))[1]::varchar(10), 'YYYY-MM-DD')			as DATE
	,	(xpath('//MED_CARD_NUMBER[1]/text()', b))[1]::varchar(50) 							as MED_CARD_NUMBER	
	,	(xpath('//HOSPITALISATION_DIVISION[1]/text()', b))[1]::varchar(16) 					as HOSPITALISATION_DIVISION			
	,	(xpath('//BRANCH[1]/text()', b))[1]::varchar(16) 									as BRANCH		
	,	(xpath('//REASON[1]/text()', b))[1]::text::int 										as REASON		
	,	(xpath('//CANCEL_SOURSE[1]/text()', b))[1]::text::int 								as CANCEL_SOURSE			
	,	to_timestamp((xpath('//DATE_CANCEL[1]/text()', b))[1]::varchar(10), 'YYYY-MM-DD')	as DATE_CANCEL
	,	(xpath('//CODE_ORG[1]/text()', b))[1]::varchar(6)									as CODE_ORG
	,	(xpath('//CANCEL_DESCRIPTION[1]/text()', b))[1]::text     							as CANCEL_DESCRIPTION
	,	(xpath('//HospIdFromDB[1]/text()', b))[1]::varchar(36)       						as HospIdFromDB --заглушка
	from
	(
		select unnest(xpath('/CANCEL_HOSPITALISATION/HEADER', x)) as h, unnest(xpath('/CANCEL_HOSPITALISATION/BODY', x)) as b
	) t1;
	
	Drop Table If Exists TmpErrors;
	Create Temp Table TmpErrors
	as
	select 3::int as Code, ('Неверный тип операции. N_ZAP(' || z.N_ZAP::text || ')')::varchar(250) as Comment
	from TmpZAP z
	where z.TYPE <> 'Insert';
	
	update TmpZAP t1
	set HospIdFromDB = 
	(
		select hosp.HospitalizationID from ISZL.T_HOSPITALIZATION hosp
		where hosp.HOSPITALIZATION_DATE = t1.DATE 
		and hosp.MED_CARD_NUMBER = t1.MED_CARD_NUMBER 
		and hosp.DIVISION = t1.HOSPITALISATION_DIVISION
		limit 1
	)
	where t1.TYPE = 'Insert';
	
	insert into TmpErrors (CODE,COMMENT)
	select 1, 'Не найдена соответствующая госпитализация. N_ZAP(' || z.N_ZAP::text || ')'
	from TmpZAP z
	where z.HospIdFromDB is null and z.TYPE = 'Insert'
	union all
	select 2, 'Отмена госпитализации уже существует. N_ZAP(' || z.N_ZAP::text || ')'
	from TmpZAP z
	inner join
		ISZL.T_CANCEL_HOSPITALIZATION cancel 
		on cancel.Hospitalization = z.HospIdFromDB::uuid
	where z.HospIdFromDB is not null and z.TYPE = 'Insert';
	
	if not exists(select* from TmpErrors)
	then
		insert into ISZL.T_CANCEL_HOSPITALIZATION(CancelHospitalizationID, DATE, MED_CARD_NUMBER, HOSPITALIZATION_DIVISION, BRANCH, REASON, CANCEL_SOURCE, DATE_CANCEL
		, CODE_ORG, CANCEL_DESCRIPTION, DivisionObj, BranchObj, CreatePlace, Hospitalization)
		select z.ID, z.DATE, z.MED_CARD_NUMBER, z.HOSPITALISATION_DIVISION, z.BRANCH, z.REASON, z.CANCEL_SOURSE, z.DATE_CANCEL, z.CODE_ORG::int, z.CANCEL_DESCRIPTION
		, div.DivisionID, br.BranchID, 1--server
		, z.HospIdFromDB::uuid
		from TmpZAP z
		left join
			ISZL.T_BRANCH br
			on br.BR_CODE = z.BRANCH and br.f003mo = z.MO
		left join
			ISZL.T_DIVISION div
			on div.CODE = z.HOSPITALISATION_DIVISION and (div.lpumain = z.MO or br.branchid  = div.branch)
		where z.TYPE = 'Insert';

		if exists(select* from information_schema.columns c where c.table_schema = 'iszl' and c.table_name = 't_cancel_hospitalization' and c.column_name='filename')
		then
			update ISZL.T_CANCEL_HOSPITALIZATION hr
			set FILENAME = z.FILENAME
			from TmpZAP z 
			where z.ID = hr.CancelHospitalizationID
			and z.TYPE = 'Insert';
		end if;
	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;