DROP function if exists sp_MotionInHospitalLoad_folder;
drop procedure if exists sp_MotionInHospitalLoad_folder;

CREATE procedure sp_MotionInHospitalLoad_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('//HOSPITALISATION_DATE[1]/text()', b))[1]::varchar(10), 'YYYY-MM-DD') 	as HOSPITALISATION_DATE	
	,	(xpath('//MED_CARD_NUMBER[1]/text()', b))[1]::varchar(50)                           		as MED_CARD_NUMBER
	,	(xpath('//BRANCH[1]/text()', b))[1]::varchar(16)                           					as BRANCH
	,	(xpath('//DIVISION[1]/text()', b))[1]::varchar(16)                           				as DIVISION
	,	(xpath('//V020_STRUCTURE_BED[1]/text()', b))[1]::text::int                           		as V020_STRUCTURE_BED
	,	to_timestamp((xpath('//DATE_IN[1]/text()', b))[1]::varchar(10), 'YYYY-MM-DD') 				as DATE_IN
	,	to_timestamp((xpath('//DATE_OUT[1]/text()', b))[1]::varchar(10), 'YYYY-MM-DD') 				as DATE_OUT
	,	(xpath('//TYPE_MOT[1]/text()', b))[1]::text::int                           		 			as TYPE_MOT
	,	(xpath('//USL_OK[1]/text()', b))[1]::text::int                           		 			as USL_OK
	,	(xpath('//OUTCOME[1]/text()', b))[1]::text::int                           		 			as OUTCOME
	,	(xpath('//mihid[1]/text()', b))[1]::text::uuid                           					as mihid
	,	(xpath('//hospId[1]/text()', b))[1]::varchar(36)                           					as hospId
	from
	(
		select unnest(xpath('/MOTION_IN_HOSPITAL/HEADER', x)) as h, unnest(xpath('/MOTION_IN_HOSPITAL/BODY', x)) as b
	) t1;
	
	update TmpZAP t1
	set hospId = 
	(
		select hosp.HospitalizationID from ISZL.T_HOSPITALIZATION hosp 
		where hosp.MED_CARD_NUMBER = t1.MED_CARD_NUMBER 
		and hosp.HOSPITALIZATION_DATE = t1.HOSPITALISATION_DATE
		and hosp.MO = t1.MO
		limit 1
	);

	Drop Table If Exists TmpErrors;
	Create Temp Table TmpErrors
	as
	select 6::int as Code, ('Неверный тип операции. N_ZAP(' || z.N_ZAP::text || ')')::varchar(250) as Comment
	from TmpZAP z
	where z.type <> 'Insert' and z.type <> 'Update' and z.type <> 'Delete'
	union all
	select 1, 'Не найдена соответствующая госпитализация. N_ZAP(' || z.N_ZAP::text || ')'
	from TmpZAP z
	where z.hospId is null
	union all
	select 2, 'Поле DATE_OUT обязательно для заполнения(TYPE_MOT = 2). N_ZAP(' || z.N_ZAP::text || ')'
	from TmpZAP z
	where z.TYPE_MOT = 2 and z.DATE_OUT is null
	union all
	select 3, 'Поле OUTCOME обязательно для заполнения(TYPE_MOT = 2). N_ZAP(' || z.N_ZAP::text || ')'
	from TmpZAP z
	where z.TYPE_MOT = 2 and z.OUTCOME is null;

	-- Insert
	if not exists(select* from TmpErrors)
	then
		--проверка
		update TmpZAP z
		set mihid = 
		(
			select hosp.MotionInHospitalID 
			from ISZL.T_MOTION_IN_HOSPITAL hosp
			where z.HOSPITALISATION_DATE = hosp.HOSPITALIZATION_DATE
			and z.MED_CARD_NUMBER = hosp.MED_CARD_NUMBER
			and z.hospId::uuid = hosp.Hospitalization
			limit 1
		)
		where z.type = 'Insert';
	
		insert into TmpErrors(Code, Comment)
		select 4, 'Запись уже была добавлена ранее. Идентификатор в БД (' || z.mihid::text || '). N_ZAP(' || z.N_ZAP::text || ')'
		from TmpZAP z
		where z.mihid is not null and z.type = 'Insert';

		--если нет ошибок, то вставка
		insert into ISZL.T_MOTION_IN_HOSPITAL(MotionInHospitalID, HOSPITALIZATION_DATE, MED_CARD_NUMBER, MO, DIVISION, V020_STRUCTURE_BED, DATE_IN, Hospitalization, BRANCH, DATE_OUT, USL_OK, OUTCOME)
		select z.ID, z.HOSPITALISATION_DATE, z.MED_CARD_NUMBER, z.MO, z.DIVISION, z.V020_STRUCTURE_BED, z.DATE_IN, z.hospId::uuid, z.BRANCH, z.DATE_OUT, z.USL_OK, z.OUTCOME
		from TmpZAP z
		where z.type = 'Insert' and not exists(select* from TmpErrors);

		if exists(select* from information_schema.columns c where c.table_schema = 'iszl' and c.table_name = 't_motion_in_hospital' and c.column_name='filename')
		then
			update ISZL.T_MOTION_IN_HOSPITAL t1
			set FILENAME = z.FILENAME
			from TmpZAP z 
			where z.ID = t1.MotionInHospitalID
			and z.type = 'Insert' and not exists(select* from TmpErrors);
		end if;
	end if;
	
	--Update
	if not exists(select* from TmpErrors)
	then
		--проверка
		update TmpZAP z
		set mihid = 
		(
			select hosp.MotionInHospitalID 
			from ISZL.T_MOTION_IN_HOSPITAL hosp
			where z.HOSPITALISATION_DATE = hosp.HOSPITALIZATION_DATE
			and z.MED_CARD_NUMBER = hosp.MED_CARD_NUMBER
			and z.hospId::uuid = hosp.Hospitalization
			limit 1
		)
		where z.type = 'Update';
	
		insert into TmpErrors(Code, Comment)
		select 4, 'Не найдены соответствующие сведения о выбытии пациента. N_ZAP(' || z.N_ZAP::text || ')'
		from TmpZAP z
		where z.mihid is null and z.type = 'Update';
	
		update ISZL.T_MOTION_IN_HOSPITAL m
		set	HOSPITALIZATION_DATE = z.HOSPITALISATION_DATE
		,	MED_CARD_NUMBER = z.MED_CARD_NUMBER
		,	MO = z.MO
		,	DIVISION = z.DIVISION
		,	V020_STRUCTURE_BED = z.V020_STRUCTURE_BED
		,	DATE_IN = z.DATE_IN
		,	BRANCH = z.BRANCH
		,	DATE_OUT = z.DATE_OUT
		,	USL_OK = z.USL_OK
		from TmpZAP z
		where m.MotionInHospitalID = z.mihid
		and z.type = 'Update'
		and not exists(select* from TmpErrors);
	end if;

	--Delete
	if not exists(select* from TmpErrors)
	then
		update TmpZAP z
		set mihid = 
		(
			select hosp.MotionInHospitalID 
			from ISZL.T_MOTION_IN_HOSPITAL hosp
			where z.HOSPITALISATION_DATE = hosp.HOSPITALIZATION_DATE
			and z.MED_CARD_NUMBER = hosp.MED_CARD_NUMBER
			and z.hospId::uuid = hosp.Hospitalization
			limit 1
		)
		where z.type = 'Delete';

		insert into TmpErrors(Code, Comment)
		select 5, 'Не найдены соответствующие сведения о выбытии пациента. N_ZAP(' || z.N_ZAP::text || ')'
		from TmpZAP z
		where z.mihid is null 
		and z.type = 'Delete';

		delete from ISZL.T_MOTION_IN_HOSPITAL mih
		where exists(select* from TmpZAP z where mih.MotionInHospitalID = z.mihid 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;