-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2017-10-24
-- Description: Внести исправление из амбулаторной карты пациента в его данные по диспансеризации
-- =============================================
drop procedure if exists public.sp_DirectDispancerRDSUpdate;
CREATE PROCEDURE public.sp_DirectDispancerRDSUpdate
(
	"AmbulanceCardID" UUID
,	"RDSDirectPatientID" uuid
) 
As $$
Begin
	with pat
	as
	(
		select p.LastName, p.FirstName, p.FatherName, p.Birthday, lastpol.Serial, lastpol.Number as polNumber, lastpol.PolicyForm, t24.Alias, p.DocSeries
		, p.DocNumber, p.SNILS, p.PlaceOfBirth, lastreg.Code, lastreg.StateNumber, ph.Number, ak.AmbulanceCardID
		from AKUZ.T_AMBULANCE_CARD ak
		inner join
			AKUZ.T_PATIENT p 
			on p.PatientID = ak.Patient
		left join 
			VCLib.T_ENUMS t24 
			ON t24.EnumID = p.DocType
		left join lateral
		(
			select pol.Number, pol.Serial, pol.PolicyForm
			from AKUZ.T_POLICY pol
			where pol.Patient = p.PatientID 
			and pol.type_ in (536) 
			and pol.issuedate < date_trunc('Day', now()::timestamp) + interval '1 day'
			order by pol.issuedate desc, case when pol.validthrough is null then '21000101'::timestamp else pol.validthrough end desc
			limit 1
		) lastpol on true
		left join lateral
		(
			select st.StateNumber, dv.Code, r.RegistrationID
			from AKUZ.T_REGISTRATION r
			left join 
				AKUZ.T_STATE st 
				on st.StateID = r.State 
			left join 
				AKUZ.T_DIVISION dv 
				on dv.DivisionID = st.Division
			where r.Patient = p.PatientID
			order by r.RegistrationDate Desc, r.Status
			limit 1
		) lastreg on true
		left join lateral
		(
			select left(string_agg(Number || case PhoneType 
								   				when 1 then ' (дом.)' 
								   				when 2 then ' (раб.)' 
								   				when 3 then ' (моб.)' 
								   				when 4 then ' (email)' 
								   				when 5 then ' (прочее)' 
								   				else '' 
								   			end, ','), 128) as Number
			from AKUZ.T_PHONE_NUMBER pn
			where pn.patient = p.PatientID
		) ph on true
		where ak.AmbulanceCardID = "AmbulanceCardID"
		and lastpol.Number is not null
		and lastreg.RegistrationID is not null
	)
	update AKUZ.T_RDS_DISP_PATIENT dpt 
	set	FAM = p.LastName
	,	IM = p.FirstName
	,	OT = p.FatherName
	,	DR = p.Birthday
	,	SPOLIS = p.Serial	
	,	NPOLIS = p.polNumber	
	,	VPOLIS = p.PolicyForm	
	,	DOCTYPE = p.Alias
	,	DOCSER = p.DocSeries
	,	DOCNUM = p.DocNumber
	,	SNILS = p.SNILS
	,	TEL = p.Number
	,	MR = p.PlaceOfBirth	
	,	PODR = p.Code
	,	UCH = p.StateNumber
	from pat p
	where p.AmbulanceCardID = dpt.AmbulanceCard
	and dpt.AmbulanceCard = "AmbulanceCardID" 
	and dpt.RdsDispPatientID = "RDSDirectPatientID";
End;
$$ Language plpgsql;