--do $$
--begin
--	if exists 
--	(
--		select * from pg_proc p
--		where p.proname = 'sp_importpersoninfo'
--	)
--	then
--		drop function public.sp_importpersoninfo(timestamp, text);
--	end if;
--end;
--$$ language plpgsql;
---- =============================================
---- Author:  Marat Zigangarayev
---- Create date: 2009/10/14
---- Description: Импорт информации о персоне
---- =============================================
--CREATE or Replace Function public.sp_ImportPersonInfo
--(
--	"BeginDate" TimeStamp -- дата начала
--,	"XmlDocument" text -- xml
--)
--RETURNS table (pid varchar(128), Num varchar(10), Lpu varchar(4), Reg varchar(1), RD varchar(10))
--As 
--$$
--Begin
--	--DECLARE @DocHandle int

--	--EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument
--	drop table if exists Tmp_Person;	
--	create temp table Tmp_Person(pid varchar(128), ln varchar(128), fn varchar(128), mn varchar(128), bd varchar(32), uid varchar(10), cn varchar(10), cl varchar(2));
--	insert into Tmp_Person(pid, ln, fn, mn, bd, uid, cn, cl)
--	select	nullif(trim(xpath('/p/@pid', x)::text,'{}"'),'')::varchar(128)
--	,	nullif(trim(xpath('/p/@ln', x)::text,'{}"'),'')::varchar(128)
--	,	nullif(trim(xpath('/p/@fn', x)::text,'{}"'),'')::varchar(128)
--	,	nullif(trim(xpath('/p/@mn', x)::text,'{}"'),'')::varchar(128)
--	,	nullif(trim(xpath('/p/@bd', x)::text,'{}"'),'')::varchar(32)
--	,	nullif(trim(xpath('/p/@uid', x)::text,'{}"'),'')::varchar(10)
--	,	nullif(trim(xpath('/p/@cn', x)::text,'{}"'),'')::varchar(10)
--	,	nullif(trim(xpath('/p/@cl', x)::text,'{}"'),'')::varchar(2)
--	from
--	(
--		select unnest(xpath('/root/ps/p', "XmlDocument"::xml)) as x
--	) q;

--	drop table if exists Tmp_Person_Final;	
--	create temp table Tmp_Person_Final(pid varchar(128), Number varchar(32), LPU varchar(4), Reg int, RD varchar(10), uid varchar(10), cn varchar(10), cl varchar(2), Patient UUID);
--	-- выявляем пациентов-учащихся
--	Insert Into Tmp_Person_Final(pid, Number, Lpu, Reg, RD, uid, cn, cl, Patient)
--	Select	pd.pid
--	,	ac.Number
--	,	lpu.Code Lpu
--	,	Coalesce(r.Status, 0) Reg
--	,	Case 	When r.RegistrationDate Is Null Then extract(day from ac.CreationDateTime - date '19000101')::varchar(10)
--			else extract(day from r.RegistrationDate - date '19000101')::varchar(10)
--		End RD
--	, 	pd.uid
--	, 	pd.cn
--	, 	pd.cl
--	, 	ac.Patient
--	From akuz.T_AMBULANCE_CARD ac
--	inner Join 
--		akuz.T_PATIENT p 
--		On ac.Patient = p.PatientID
--	inner Join 
--		akuz.T_LPU_MAIN lpu 
--		On ac.CardLpu = lpu.LpuID
--	-- информация о прикреплении	
--	Left Join 
--	(
--		Select r.Patient, r.Status, r.RegistrationDate, r.LpuMain
--		From akuz.T_REGISTRATION r
--		Where r.RegistrationDate = 
--		(
--			Select rr.RegistrationDate 
--			From akuz.T_REGISTRATION rr 
--			Where r.Patient = rr.Patient
--			Order by rr.RegistrationDate Desc, rr.Status
--			limit 1
--		)
--	) r 
--		On p.PatientID = r.Patient 
--		And ac.CardLpu = r.LpuMain
--	-- информация из xml
--	inner Join 
--		Tmp_Person pd 
--		On p.LastName = pd.ln 
--		And p.FirstName = pd.fn 
--		And p.FatherName = pd.mn 
--		And 
--		(
--			char_length(coalesce(pd.bd,'')) = 0 OR p.Birthday = date '19000101' + trunc(pd.bd::numeric(32,15))* interval '1 day' --cast(cast(pd.bd As float) As datetime)
--		);

----	-- освобождаем документ xml
----	Exec sp_xml_removedocument @DocHandle 
--	-- создаем, для тех, у кого нет такой инфы
--	Insert Into akuz.T_PATIENT_SCHOOL(PatientSchoolID, BeginDate, EndDate, Patient, ClassNumber, ClassLetter, School)
--	Select uuid_generate_v1(), "BeginDate", NULL, t.Patient, t.cn::int, t.cl, s.SchoolID
--	From Tmp_Person_Final t
--	inner Join 
--		akuz.T_SCHOOL s 
--		On s.UID = t.uid
--	Where t.Patient Not in 
--	(
--		-- у кого уже есть точно такая же инф-я о персоне
--		Select distinct ps.Patient
--		From akuz.T_PATIENT_SCHOOL ps
--		inner Join 
--			Tmp_Person_Final t2 
--			On ps.Patient = t2.Patient
--		inner Join 
--			akuz.T_SCHOOL s2 
--			On ps.School = s2.SchoolID
--		Where ps.ClassNumber::varchar(10) = t2.cn
--		And ps.ClassLetter = t2.cl
--		And s2.UID = t2.uid
--		And ps.EndDate Is Null
--	);

--	-- результат
--	Return Query
--	Select t.pid, t.Number as Num, t.Lpu, t.Reg::varchar(1) as Reg, t.RD
--	From Tmp_Person_Final t;
--End;
--$$ Language plpgsql;