-- =============================================
-- Author:		Vitacard System
-- Create date: 2009/06/02
-- Description:	Процедура открывает/закрывает случаи(AMBULANCE_EVENT).
-- =============================================
CREATE OR REPLACE FUNCTION sp_updateclosedate
(
	"AmbulanceEventId" uuid	-- Идентификатор AMBULANCE_EVENT
,	"AmbulanceEventType" varchar(256) -- Тип AMBULANCE_EVENT
,	"CloseDate" timestamp
)
RETURNS void AS
$$
Begin
	-- валидация параметра 
	If "CloseDate" Is Null
	then
		Raise Exception Using Message = 'В процедуре sp_UpdateCloseDate() не указана дата закрытия';
		Return;
	End if;
	-- если уже закрыта, то ничего не делаем
	If "AmbulanceEventType" != 'AKUZ.EXAMINATION_BASE' And  "AmbulanceEventType" != 'AKUZ.SPORT_EXAMINATION_BASE' And Exists(Select* From akuz.T_EVENT_BASE eb Where eb.EventID = "AmbulanceEventId" And eb.CloseDate is NOT NULL)
	then
		Return;
	End if;

	-- не закрываем случаи если нет проводки по платным услугам
	If Exists(SELECT* FROM AKUZ.T_EVENT_SERVICE t0 WHERE t0.AmbulanceEvent = "AmbulanceEventId")
	then
		Return;
	end if;

	-- не закрываем случаи, если случай - индивидуальная карта беременной или случай беременности
	If exists
	(
		select* from vclib.t_entities e
		inner join
			akuz.t_event_base eb
			on eb.type_ = e.entityid
		where eb.eventid = "AmbulanceEventId"
		and (e.entname = 'MOTHERHOOD_EVENT' 
		or e.entname = 'PREGNANCY_EVENT')
	) 
	then
		Return;
	End if;

	-- проверка AMBULANCE_DOCTOR_INSPECTION
	If Exists
	(
		Select * 
		From akuz.T_AMBULANCE_DOCTOR_INSPECTION adi
		inner join 
			akuz.T_VISIT v 
			On adi.VisitID = v.VisitID
		Where v.AmbulanceEvent = "AmbulanceEventId"
		And v.OutcomeRecord is null
	)
	then
		Return;
	End if;
 
	-- проверка ENTRY_TO_DOCTOR_INSPECTION, ENTRY_TO_DOCTOR_INSPECTION_RE и SICK_CALL_ACCEPT_LINK и SICK_CALL_ACCEPT_LINK_ADD |||на||| VISIT
	If Exists
	(
		Select * 
		From akuz.T_ENTRY_BASE eb
		Where eb.AmbulanceEvent = "AmbulanceEventId"
		And Exists 
		(
			Select* From VCLib.T_ENTITIES e 
			Where eb.type_ = e.EntityID and e.EntName in ('ENTRY_TO_DOCTOR_INSPECTION','ENTRY_TO_DOCTOR_INSPECTION_RE','SICK_CALL_ACCEPT_LINK','SICK_CALL_ACCEPT_LINK_ADD')
		)
		And Not Exists 
		(
			Select* From akuz.T_VISIT v
			WHERE v.Post = eb.Resource
			AND v.AmbulanceEvent = "AmbulanceEventId"
			AND v.VisitDate = eb.EntryDate
		) 
	)
	then
		Return;
	End if;

	-- проверка ENTRY_TO_MEDSERVICE |||на||| DIAGNOSTICS_RESULTS
	If Exists
	(
		Select * 
		From akuz.T_ENTRY_BASE eb
		Where eb.AmbulanceEvent = "AmbulanceEventId"
		And Exists 
		(
			Select* From VCLib.T_ENTITIES e 
			Where eb.type_ = e.EntityID and e.EntName in ('ENTRY_TO_MEDSERVICE')
		)
		And Not Exists 
		(
			Select *
			From akuz.T_DIAGNOSTICS_RESULTS dr
			inner Join 
				akuz.T_AMBULANCE_CARD_RECORD acr 
				on acr.MedicalRecordID = dr.MedicalRecordID
			inner Join 
				akuz.T_MEDICAL_RECORD mr 
				On mr.MedicalRecordID = dr.MedicalRecordID
			WHERE acr.AmbulanceEvent = "AmbulanceEventId"
			AND acr.PostingDate = eb.EntryDate
			AND mr.Post = eb.Resource
			AND dr.Research = eb.MedicalService
		) 
	)
	then
		Return;
	End if;

--	If Exists
--	(
--		select * 
--		from akuz.T_EVENT_BASE eb 
--		where eb.EventID = "AmbulanceEventId"
--		And Exists 
--		(
--			Select * 
--			From VCLib.T_ENTITIES e 
--			Where eb.type_=e.EntityID and EntName in ('EXAMINATION_BASE')
--		)
--		and Exists 
--		(
--			select * from akuz.T_EXAMINATION_OWNED_DOCS edocs where edocs.ExaminationBase = eb.EventID
--		)
--	)
--	then
--		return;
--	end if;

	If "AmbulanceEventType" in ('AKUZ.EXAMINATION_BASE', 'AKUZ.SPORT_EXAMINATION_BASE')
	then
		If Exists(select* from AKUZ.T_EXAMINATION_TYPE_LINK etl where etl.ExaminationBase = "AmbulanceEventId" and etl.Status = 0)
	    --Or Exists(select * from [AKUZ].T_EXAMINATION_BASE eb where eb.EventId = @AmbulanceEventId and eb.V017DispResult is null) 
		then
			Return;
		End if;
		
		select "CloseDate" = max(etl.ExecutionDate) from AKUZ.T_EXAMINATION_TYPE_LINK etl where etl.ExaminationBase = "AmbulanceEventId" and etl.Status != 0;

		update AKUZ.T_CLINIC_EXAMINATION_ITEM cei
		set Status = 3
		from AKUZ.T_EXAMINATION_BASE eb
		where eb.ClinicExaminationItem = cei.ClinicExaminationItemID
		and eb.EventId = "AmbulanceEventId";
	End if;
	
	-- Не закрываем случай, если есть не закрытый больничный
  	If Exists
	(
		select * 
   		from AKUZ.T_MEDICAL_CERTIFICATE mc
   		inner join
			AKUZ.T_AMBULANCE_CARD_RECORD acr 
			on acr.MedicalRecordID = mc.MedicalRecordID
   		left join 
			AKUZ.T_MEDICAL_CERTIFICATE_CLOSE mcc 
			on mcc.MedicalCertificate = mc.MedicalRecordID
  		where acr.AmbulanceEvent = "AmbulanceEventId" 
		and mcc.MedicalCertificate is null
  	)
	then
  		Return;
 	End if;
	
	-- закрываем
	Update akuz.T_EVENT_BASE eb
	Set CloseDate = "CloseDate"
	Where eb.EventID = "AmbulanceEventId";
End;
$$ LANGUAGE plpgsql VOLATILE;
ALTER FUNCTION sp_updateclosedate(uuid, varchar(256), timestamp)
  OWNER TO postgres;