if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_UpdateCloseDate]') and xtype = N'P')
drop procedure [dbo].[sp_UpdateCloseDate]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Vitacard System
-- Create date: 2009/06/02
-- Description:	Процедура открывает/закрывает случаи(AMBULANCE_EVENT).
-- =============================================
create PROCEDURE [dbo].[sp_UpdateCloseDate] 
 @AmbulanceEventId uniqueidentifier -- Идентификатор AMBULANCE_EVENT
 ,@AmbulanceEventType varchar(256) -- Тип AMBULANCE_EVENT
 ,@CloseDate datetime
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

 -- валидация параметра 
 If @CloseDate Is Null
 Begin
  Raiserror ('В процедуре [dbo].sp_UpdateCloseDate() не указана дата закрытия', 18, 1)
  Return
 End

 -- если уже закрыта, то ничего не делаем
 If  @AmbulanceEventType != 'AKUZ.EXAMINATION_BASE' And  @AmbulanceEventType != 'AKUZ.SPORT_EXAMINATION_BASE' And Exists(
   Select * 
   From [AKUZ].T_EVENT_BASE
   Where EventID = @AmbulanceEventId
   And CloseDate is NOT NULL
   )
 Begin
  Return
 End

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

 -- не закрываем случаи, если случай - индивидуальная карта беременной
 If ((SELECT EntName FROM VCLib.T_ENTITIES
      WHERE EntityID = (SELECT type_ FROM AKUZ.T_EVENT_BASE
      WHERE EventID = @AmbulanceEventId)) = 'MOTHERHOOD_EVENT')
 begin
	return
 end

 -- не закрываем случаи, если случай - случай беременности
 If ((SELECT EntName FROM VCLib.T_ENTITIES
      WHERE EntityID = (SELECT type_ FROM AKUZ.T_EVENT_BASE
      WHERE EventID = @AmbulanceEventId)) = 'PREGNANCY_EVENT')
 begin
	return
 end

 -- проверка AMBULANCE_DOCTOR_INSPECTION
 If Exists(
   Select * 
    From [AKUZ].T_AMBULANCE_DOCTOR_INSPECTION adi
    join [AKUZ].T_VISIT v On adi.VisitID = v.VisitID
   Where v.AmbulanceEvent = @AmbulanceEventId
    And v.OutcomeRecord is null
   )
 Begin
  Return
 End
 
 -- проверка 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 EntName in ('ENTRY_TO_DOCTOR_INSPECTION','ENTRY_TO_DOCTOR_INSPECTION_RE','SICK_CALL_ACCEPT_LINK','SICK_CALL_ACCEPT_LINK_ADD','ENTRY_TO_TELECONSULTATION')
		)
    And Not Exists (
     Select *
     From [AKUZ].T_VISIT v
     WHERE v.Post = eb.Resource
      AND v.AmbulanceEvent = @AmbulanceEventId
      AND v.VisitDate = eb.EntryDate
     ) 
   )
 Begin
  Return
 End

  -- проверка 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 EntName in ('ENTRY_TO_MEDSERVICE')
		)
    And Not Exists (
     Select *
     From [AKUZ].T_DIAGNOSTICS_RESULTS dr
	 Join [AKUZ].T_MEDICAL_RECORD mr On mr.MedicalRecordID = dr.MedicalRecordID
     WHERE mr.AmbulanceEvent = @AmbulanceEventId --and mr.type_ in (598, 591, 706, 811, 938, 1562, 1129)
      AND mr.PostingDate = eb.EntryDate
	  AND mr.Post = eb.Resource
	  AND dr.Research = eb.MedicalService
     ) 
   )
 Begin
  Return
 End

 --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))
 --begin
	--return
 --end
 
  If @AmbulanceEventType = 'AKUZ.EXAMINATION_BASE' or  @AmbulanceEventType = 'AKUZ.SPORT_EXAMINATION_BASE'
 Begin
	 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) 
	 Begin
	  Return
	 End

	select @CloseDate = max(etl.ExecutionDate) from [AKUZ].T_EXAMINATION_TYPE_LINK etl where etl.ExaminationBase = @AmbulanceEventId and etl.[Status] != 0

	 declare @ClinicExaminationItemID uniqueidentifier
	 select @ClinicExaminationItemID = cei.ClinicExaminationItemID 
	 from [AKUZ].T_EXAMINATION_BASE eb 
	 join [AKUZ].T_CLINIC_EXAMINATION_ITEM cei on eb.ClinicExaminationItem = cei.ClinicExaminationItemID
	 where eb.EventId = @AmbulanceEventId

	 If @ClinicExaminationItemID is not null
	 Begin
		update [AKUZ].T_CLINIC_EXAMINATION_ITEM set [Status] = 3 where ClinicExaminationItemID = @ClinicExaminationItemID
	 End
 End

 -- Не закрываем случай, если есть не закрытый больничный
  If Exists(
  select * 
   from AKUZ.T_MEDICAL_CERTIFICATE mc
   join AKUZ.T_MEDICAL_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
  )
 Begin
  Return
 End

 -- закрываем
 Update [AKUZ].T_EVENT_BASE
 Set CloseDate = @CloseDate
 Where EventID = @AmbulanceEventId

--select top 1 'begin(p) @p:=new Process("zzzz"); @p->{Parameters}["id"]:=@AmbulanceEventId; @p->{Parameters}["date"]:="'+cast(EventDate as varchar(20))+'"; @p->Run(); end' as 'Lexem'
--from [AKUZ].T_EVENT_BASE-- where EventID = @AmbulanceEventId

--select 'begin(p) @p:=new Process("zzzz"); @p->{Parameters}["id"]:=@AmbulanceEventId; @p->{Parameters}["date"]:="'+cast(EventDate as varchar(20))+'"; @p->Run(); end' as 'Lexem'
--select EventDate as 'Lexem'
--from [AKUZ].T_EVENT_BASE where EventID ='BBD49C9A-CC92-421E-A287-8595612B5EF2'

--select top 2 EventDate as 'Lexem'
--select cast(@AmbulanceEventId as varchar(100)) as Lexem
 
--select *  from AKUZ.T_EVENT_BASE where EventID = 'BBD49C9A-CC92-421E-A287-8595612B5EF2'
 
END