DROP function if exists sp_CancelHospitalisationReferralLoad_folder;
drop procedure if exists sp_CancelHospitalisationReferralLoad_folder;

CREATE procedure sp_CancelHospitalisationReferralLoad_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('//N_ZAP[1]/text()', b))[1]::text::int           							as N_ZAP
	,	(xpath('//TYPE[1]/text()', b))[1]::varchar(6)           							as TYPE
	,	(xpath('//MO_TO[1]/text()', b))[1]::varchar(6) 										as MO_TO
	,	(xpath('//FILENAME[1]/text()', h))[1]::varchar(26) 									as FILENAME
	,	(xpath('//MO[1]/text()', h))[1]::varchar(6) 										as MO	
	,	(xpath('//REFERRAL_NUMBER[1]/text()', b))[1]::varchar(21) 							as REFERRAL_NUMBER		
	,	to_timestamp((xpath('//DATE[1]/text()', b))[1]::varchar(10), 'YYYY-MM-DD')			as DATE
	,	(xpath('//REFERRAL_MO[1]/text()', b))[1]::varchar(6) 								as REFERRAL_MO
	,	(xpath('//BRANCH[1]/text()', b))[1]::varchar(17) 									as BRANCH		
	,	(xpath('//REASON[1]/text()', b))[1]::text::int 										as REASON		
	,	(xpath('//CANCEL_SOURSE[1]/text()', b))[1]::text::int 								as CANCEL_SOURSE		
	,	(xpath('//CODE_ORG[1]/text()', b))[1]::varchar(6)									as CODE_ORG
	,	to_timestamp((xpath('//DATE_CANCEL[1]/text()', b))[1]::varchar(10), 'YYYY-MM-DD')	as DATE_CANCEL
	,	(xpath('//CANCEL_DESCRIPTION[1]/text()', b))[1]::text     							as CANCEL_DESCRIPTION
	,	(xpath('//HospRefIdFromDB[1]/text()', b))[1]::varchar(36)       					as HospRefIdFromDB
	from
	(
		select unnest(xpath('/CANCEL_HOSPITALISATION_REFERRAL/HEADER', x)) as h, unnest(xpath('/CANCEL_HOSPITALISATION_REFERRAL/BODY', x)) as b
	) t1;
	
	Drop Table If Exists TmpErrors;
	Create Temp Table TmpErrors
	as
	select 4::int as Code, ('Неверный тип операции. N_ZAP(' || z.N_ZAP::text || ')')::varchar(250) as Comment
	from TmpZAP z
	where z.TYPE <> 'Insert' and z.TYPE <> 'Delete';
	
	update TmpZAP t1
	set HospRefIdFromDB = 
	(
		select hosp.HospitalizationReferralID from ISZL.T_HOSPITALIZATION_REFERRAL hosp 
		where hosp.REFERRAL_DATE = t1.DATE 
		and hosp.REFERRAL_NUMBER = t1.REFERRAL_NUMBER 
		and hosp.LpuMainFrom = t1.REFERRAL_MO
		limit 1
	);--возможно надо добавить [MO_TO]
	
	insert into TmpErrors (CODE,COMMENT)
	select 1, 'Не найдено соответствующее направление на госпитализацию. N_ZAP(' || z.N_ZAP::text || ')'
	from TmpZAP z
	where z.HospRefIdFromDB 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_REFERRAL cancel 
		on cancel.HospitalizationReferral = z.HospRefIdFromDB::uuid
	where z.HospRefIdFromDB is not null and z.TYPE = 'Insert'
	union all
	select 3, 'Не найдено соответствующее направление на госпитализацию. N_ZAP(' || z.N_ZAP::text || ')'
	from TmpZAP z
	where z.HospRefIdFromDB is null and z.TYPE = 'Delete';
	
	if not exists(select* from TmpErrors)
	then
		insert into ISZL.T_CANCEL_HOSPITALIZATION_REFERRAL(CancelHospitalizationOrReferralID, REFERRAL_NUMBER, DATE, REFERRAL_MO, REASON, CANCEL_SOURCE, CODE_ORG
		, DATE_CANCEL, CreatePlace, BRANCH, CANCEL_DESCRIPTION, BranchObj, HospitalizationReferral)
		select z.ID, z.REFERRAL_NUMBER, z.DATE, z.REFERRAL_MO, z.REASON, z.CANCEL_SOURSE, z.CODE_ORG::int, z.DATE_CANCEL, 1, z.BRANCH, z.CANCEL_DESCRIPTION, branch.BranchID, z.HospRefIdFromDB::uuid
		from TmpZAP z
		left join 
			ISZL.T_BRANCH branch
			on branch.BR_CODE = z.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_referral' and c.column_name='filename')
		then
			update ISZL.T_CANCEL_HOSPITALIZATION_REFERRAL hr
			set FILENAME = z.FILENAME
			from TmpZAP z 
			where z.ID = hr.CancelHospitalizationOrReferralID
			and z.TYPE = 'Insert';
		end if;
	end if;
	
	if not exists(select* from TmpErrors)
	then
		delete from ISZL.T_CANCEL_HOSPITALIZATION_REFERRAL t1
		where exists
		(
			select* from TmpZAP z 
			where t1.HospitalizationReferral = z.HospRefIdFromDB::uuid
			and t1.REFERRAL_NUMBER = z.REFERRAL_NUMBER 
			and t1.DATE = z.DATE
			and t1.REFERRAL_MO = z.REFERRAL_MO 
			and t1.DATE_CANCEL = z.DATE_CANCEL
			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;