USE [RDSDB]
GO
/****** Object:  StoredProcedure [dbo].[sp_DispDirectDel]    Script Date: 03/07/2018 10:33:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_DispDirectDel]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_DispDirectDel]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[RDS].[sp_DispDirectDel]') AND type in (N'P', N'PC'))
DROP PROCEDURE [RDS].[sp_DispDirectDel]
GO

-- =============================================
-- =============================================


CREATE PROCEDURE [RDS].[sp_DispDirectDel]
  @FileID uniqueidentifier
AS 
BEGIN 


DELETE s FROM [RDS].T_DISP_DIRECT_STATUS s
	join [RDS].T_DISP_DIRECT d on s.DispDirect = d.DispDirectID
	left join [RDS].T_DISP_DIRECT_FACT ddf on d.DispDirectID = ddf.DispDirect
WHERE d.[DispDirectFile] = @FileID and ddf.DispDirectFactID is null

DELETE i FROM [RDS].T_DISP_DIRECT_INTERVIEW i
	join [RDS].T_DISP_DIRECT d on i.DispDirect = d.DispDirectID
	left join [RDS].T_DISP_DIRECT_FACT ddf on d.DispDirectID = ddf.DispDirect
WHERE d.[DispDirectFile] = @FileID and ddf.DispDirectFactID is null

DELETE i FROM [RDS].T_DISP_DIRECT_INFO i
	join [RDS].T_DISP_DIRECT d on i.DispDirect = d.DispDirectID
	left join [RDS].T_DISP_DIRECT_FACT ddf on d.DispDirectID = ddf.DispDirect
WHERE d.[DispDirectFile] = @FileID and ddf.DispDirectFactID is null

DELETE a1 FROM [RDS].T_DISP_DIRECT_ANKETA1 a1
	join [RDS].T_DISP_DIRECT_QUESTIONING q on a1.DiapDirectQuestioningID = q.DiapDirectQuestioningID
	join [RDS].T_DISP_DIRECT d on q.DispDirect = d.DispDirectID
	left join [RDS].T_DISP_DIRECT_FACT ddf on d.DispDirectID = ddf.DispDirect
WHERE d.[DispDirectFile] = @FileID and ddf.DispDirectFactID is null

DELETE a2 FROM [RDS].T_DISP_DIRECT_ANKETA2 a2
	join [RDS].T_DISP_DIRECT_QUESTIONING q on a2.DiapDirectQuestioningID = q.DiapDirectQuestioningID
	join [RDS].T_DISP_DIRECT d on q.DispDirect = d.DispDirectID
	left join [RDS].T_DISP_DIRECT_FACT ddf on d.DispDirectID = ddf.DispDirect
WHERE d.[DispDirectFile] = @FileID and ddf.DispDirectFactID is null

DELETE q FROM [RDS].T_DISP_DIRECT_QUESTIONING q
	join [RDS].T_DISP_DIRECT d on q.DispDirect = d.DispDirectID
	left join [RDS].T_DISP_DIRECT_FACT ddf on d.DispDirectID = ddf.DispDirect
WHERE d.[DispDirectFile] = @FileID and ddf.DispDirectFactID is null

DELETE pc FROM [RDS].T_PATIENT_CONTACT pc
	join [RDS].T_DISP_DIRECT d on pc.DispDirect = d.DispDirectID
	left join [RDS].T_DISP_DIRECT_FACT ddf on d.DispDirectID = ddf.DispDirect
WHERE d.[DispDirectFile] = @FileID and ddf.DispDirectFactID is null


DELETE fd FROM [RDS].T_DISP_DIRECT_FACT_DESTINATION fd
	join [RDS].T_DISP_DIRECT_FACT f on f.DispDirectFactID = fd.DispDirectFact
	join [RDS].T_DISP_DIRECT d on f.DispDirect = d.DispDirectID
	left join [RDS].T_DISP_DIRECT_FACT ddf on d.DispDirectID = ddf.DispDirect
WHERE d.[DispDirectFile] = @FileID and ddf.DispDirectFactID is null

DELETE f FROM [RDS].T_DISP_DIRECT_FACT f
	join [RDS].T_DISP_DIRECT d on f.DispDirect = d.DispDirectID
	left join [RDS].T_DISP_DIRECT_FACT ddf on d.DispDirectID = ddf.DispDirect
WHERE d.[DispDirectFile] = @FileID and ddf.DispDirectFactID is null

DELETE d FROM [RDS].T_DISP_DIRECT d
left join [RDS].T_DISP_DIRECT_FACT ddf on d.DispDirectID = ddf.DispDirect
WHERE [DispDirectFile] = @FileID and ddf.DispDirectFactID is null

declare @cnt int
set @cnt = (select count(*) FROM [RDS].T_DISP_DIRECT d
left join [RDS].T_DISP_DIRECT_FACT ddf on d.DispDirectID = ddf.DispDirect
WHERE [DispDirectFile] = @FileID and ddf.DispDirectFactID is not null)


if (@cnt = 0)
BEGIN
	DELETE f FROM [RDS].T_DISP_DIRECT_FILE f
	WHERE FileId = @FileID 
END

END

