-- DROP FUNCTION sp_CancelHospitalisationLoad_folder(xml, uuid, uuid);
CREATE OR REPLACE FUNCTION sp_CancelHospitalisationLoad_folder
(
	x xml
,	"FileID" uuid default null::uuid
,	"Worker" uuid default null::uuid	
)
RETURNS TABLE (Res boolean, Err text) 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;
	
return query
	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;

	DROP TABLE if exists TmpZAP;
	DROP TABLE if exists TmpErrors;

end;
$BODY$ LANGUAGE plpgsql VOLATILE COST 100;