-- =============================================
-- Author:		Vitacore
-- Create date: 2023/08/15
-- Description:	Процедура апдейтит поля у AMBULANCE_CARD State 
-- =============================================
CREATE OR REPLACE FUNCTION sp_UpdateStateAmbulanceCard
(
    "PatientID" uuid
)
RETURNS void AS
$BODY$
declare	_StateID UUID;
	_LpuID UUID;
	_Status int;
Begin
    select into 
        _StateID, _LpuID, _Status
        r.State, r.LpuMain, r.Status
	from akuz.T_REGISTRATION r
	where r.Patient = "PatientID" 
	and r.RegistrationDate <= Coalesce("OnDate", Current_TimeStamp::TIMESTAMP) 
	and r.AttachmentStatus > 0
	AND r.Profile >= 1
	AND r.Profile <= 2
	order by r.RegistrationDate desc		
	Limit 1;
    
    update ac
	set ac.State = (case when _Status = 1 and _LpuID = ac.CardLpu then _StateID else null end)
	from AKUZ.T_AMBULANCE_CARD ac
	where ac.Patient = "PatientID";

    return;
End;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION sp_UpdateStateAmbulanceCard(uuid)
  OWNER TO postgres;