SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_SchetDelete]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_SchetDelete]
GO
-- Author:      Vitacore (с)
-- Create date: 
-- Description: удаление счетов
-- =============================================
CREATE   procedure [dbo].[sp_SchetDelete]
@SchetID as uniqueidentifier
as
begin
	delete del from ISZL.T_DN_FACT del
		join ISZL.T_SCHET_SLUCH ss on ss.SchetSluchID = del.SchetSluch
		join iszl.T_SCHET_SLUCH_ACCOMPLISHED ssa on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
		join iszl.T_SCHET_ZAP sz on sz.SchetZapID = ssa.SchetZap
	where sz.Schet = @SchetID
	
	delete del from ISZL.T_DISP_DIRECT_FACT_DESTINATION del
		join ISZL.T_DISP_DIRECT_FACT ddf on ddf.DispDirectFactID = del.DispDirectFact
		join ISZL.T_SCHET_SLUCH ss on ss.SchetSluchID = ddf.SchetSluch
		join iszl.T_SCHET_SLUCH_ACCOMPLISHED ssa on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
		join iszl.T_SCHET_ZAP sz on sz.SchetZapID = ssa.SchetZap
	where sz.Schet = @SchetID

	delete del from ISZL.T_DISP_DIRECT_FACT del
		join ISZL.T_SCHET_SLUCH ss on ss.SchetSluchID = del.SchetSluch
		join iszl.T_SCHET_SLUCH_ACCOMPLISHED ssa on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
		join iszl.T_SCHET_ZAP sz on sz.SchetZapID = ssa.SchetZap
	where sz.Schet = @SchetID

	update dd set SchetSluch = null
	from ISZL.T_DISP_DIRECT dd
	join ISZL.T_SCHET_SLUCH ss on ss.SchetSluchID = dd.SchetSluch
	join iszl.T_SCHET_SLUCH_ACCOMPLISHED ssa on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
	join iszl.T_SCHET_ZAP sz on sz.SchetZapID = ssa.SchetZap
	where sz.Schet = @SchetID

	delete del from ISZL.T_SCHET_SLUCH_CONS del
		join ISZL.T_SCHET_SLUCH ss on ss.SchetSluchID = del.SchetSluch
		join iszl.T_SCHET_SLUCH_ACCOMPLISHED ssa on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
		join iszl.T_SCHET_ZAP sz on sz.SchetZapID = ssa.SchetZap
	where sz.Schet = @SchetID

	delete del from ISZL.T_SCHET_SLUCH_DEST del
		join ISZL.T_SCHET_SLUCH ss on ss.SchetSluchID = del.OmsSchetSluch
		join iszl.T_SCHET_SLUCH_ACCOMPLISHED ssa on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
		join iszl.T_SCHET_ZAP sz on sz.SchetZapID = ssa.SchetZap
	where sz.Schet = @SchetID

	delete del from ISZL.T_SCHET_SLUCH_DS del	
		join ISZL.T_SCHET_SLUCH ss on ss.SchetSluchID = del.SchetSluch
		join iszl.T_SCHET_SLUCH_ACCOMPLISHED ssa on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
		join iszl.T_SCHET_ZAP sz on sz.SchetZapID = ssa.SchetZap
	where sz.Schet = @SchetID

	delete del from ISZL.T_SCHET_SLUCH_NAZ del
		join ISZL.T_SCHET_SLUCH ss on ss.SchetSluchID = del.SchetSluch
		join iszl.T_SCHET_SLUCH_ACCOMPLISHED ssa on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
		join iszl.T_SCHET_ZAP sz on sz.SchetZapID = ssa.SchetZap
	where sz.Schet = @SchetID

	delete del from ISZL.T_SCHET_SLUCH_ONK_DIAG del
		join ISZL.T_SCHET_SLUCH_ONK sso on sso.SchetSluchOnkID = del.SchetSluchOnk
		join ISZL.T_SCHET_SLUCH ss on ss.SchetSluchID = sso.SchetSluch
		join iszl.T_SCHET_SLUCH_ACCOMPLISHED ssa on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
		join iszl.T_SCHET_ZAP sz on sz.SchetZapID = ssa.SchetZap
	where sz.Schet = @SchetID

	delete del from ISZL.T_SCHET_SLUCH_ONK_PROT del
		join ISZL.T_SCHET_SLUCH_ONK sso on sso.SchetSluchOnkID = del.SchetSluchOnk
		join ISZL.T_SCHET_SLUCH ss on ss.SchetSluchID = sso.SchetSluch
		join iszl.T_SCHET_SLUCH_ACCOMPLISHED ssa on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
		join iszl.T_SCHET_ZAP sz on sz.SchetZapID = ssa.SchetZap
	where sz.Schet = @SchetID

	delete del from ISZL.T_SCHET_SLUCH_SANK del
		join iszl.T_SCHET_SLUCH_ACCOMPLISHED ssa on ssa.SchetSluchAccomplishedID = del.SchetSluchAccomplished
		join iszl.T_SCHET_ZAP sz on sz.SchetZapID = ssa.SchetZap
	where sz.Schet = @SchetID

	delete del from ISZL.T_SCHET_SLUCH_SANK del
		join ISZL.T_SCHET_SLUCH ss on ss.SchetSluchID = del.SchetSluch
		join iszl.T_SCHET_SLUCH_ACCOMPLISHED ssa on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
		join iszl.T_SCHET_ZAP sz on sz.SchetZapID = ssa.SchetZap
	where sz.Schet = @SchetID

	delete del from ISZL.T_SCHET_USL_NAPR del
		join ISZL.T_SCHET_SLUCH ss on ss.SchetSluchID = del.SchetSluch
		join iszl.T_SCHET_SLUCH_ACCOMPLISHED ssa on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
		join iszl.T_SCHET_ZAP sz on sz.SchetZapID = ssa.SchetZap
	where sz.Schet = @SchetID

	delete del from ISZL.T_SCHET_USL del
		join ISZL.T_SCHET_SLUCH ss on ss.SchetSluchID = del.SchetSluch
		join iszl.T_SCHET_SLUCH_ACCOMPLISHED ssa on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
		join iszl.T_SCHET_ZAP sz on sz.SchetZapID = ssa.SchetZap
	where sz.Schet = @SchetID

	delete del from ISZL.T_SCHET_USL_ONK_LEK_PR_DATE del
		join ISZL.T_SCHET_USL_ONK_LEK_PR lek on lek.SchetUslOnkLekPrID = del.SchetUslOnkLekPr
		join ISZL.T_SCHET_USL_ONK suo on suo.SchetUslOnkID = lek.SchetUslOnk
		join ISZL.T_SCHET_SLUCH_ONK sso on sso.SchetSluchOnkID = suo.SchetSluchOnk
		join ISZL.T_SCHET_SLUCH ss on ss.SchetSluchID = sso.SchetSluch
		join iszl.T_SCHET_SLUCH_ACCOMPLISHED ssa on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
		join iszl.T_SCHET_ZAP sz on sz.SchetZapID = ssa.SchetZap
	where sz.Schet = @SchetID

	delete del from ISZL.T_SCHET_USL_ONK_LEK_PR del
		join ISZL.T_SCHET_USL_ONK suo on suo.SchetUslOnkID = del.SchetUslOnk
		join ISZL.T_SCHET_SLUCH_ONK sso on sso.SchetSluchOnkID = suo.SchetSluchOnk
		join ISZL.T_SCHET_SLUCH ss on ss.SchetSluchID = sso.SchetSluch
		join iszl.T_SCHET_SLUCH_ACCOMPLISHED ssa on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
		join iszl.T_SCHET_ZAP sz on sz.SchetZapID = ssa.SchetZap
	where sz.Schet = @SchetID

	delete del from ISZL.T_SCHET_USL_ONK del
		join ISZL.T_SCHET_SLUCH_ONK sso on sso.SchetSluchOnkID = del.SchetSluchOnk
		join ISZL.T_SCHET_SLUCH ss on ss.SchetSluchID = sso.SchetSluch
		join iszl.T_SCHET_SLUCH_ACCOMPLISHED ssa on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
		join iszl.T_SCHET_ZAP sz on sz.SchetZapID = ssa.SchetZap
	where sz.Schet = @SchetID

	delete del from ISZL.T_SCHET_SLUCH_ONK del
		join ISZL.T_SCHET_SLUCH ss on ss.SchetSluchID = del.SchetSluch
		join iszl.T_SCHET_SLUCH_ACCOMPLISHED ssa on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
		join iszl.T_SCHET_ZAP sz on sz.SchetZapID = ssa.SchetZap
	where sz.Schet = @SchetID

	print 'delete T_SCHET_SLUCH'
	
	DELETE del
	FROM ISZL.T_SCHET_SLUCH del
		LEFT JOIN ISZL.T_DISP_DIRECT DD ON DD.SchetSluch = del.SchetSluchID
		LEFT JOIN ISZL.T_DISP_DIRECT_FACT DDF ON DDF.SchetSluch = del.SchetSluchID
		LEFT JOIN ISZL.T_DN_FACT DF ON DF.SchetSluch = del.SchetSluchID
		LEFT JOIN ISZL.T_SCHET_SLUCH_CONS SSC ON SSC.SchetSluch = del.SchetSluchID
		LEFT JOIN ISZL.T_SCHET_SLUCH_DEST SSD ON SSD.OmsSchetSluch = del.SchetSluchID
		LEFT JOIN ISZL.T_SCHET_SLUCH_DS SSDS ON SSDS.SchetSluch = del.SchetSluchID
		LEFT JOIN ISZL.T_SCHET_SLUCH_NAZ SSN ON SSN.SchetSluch = del.SchetSluchID
		LEFT JOIN ISZL.T_SCHET_SLUCH_ONK SSO ON SSO.SchetSluch = del.SchetSluchID
		LEFT JOIN ISZL.T_SCHET_SLUCH_SANK SSS ON SSS.SchetSluch = del.SchetSluchID
		LEFT JOIN ISZL.T_SCHET_USL SU ON SU.SchetSluch = del.SchetSluchID
		LEFT JOIN ISZL.T_SCHET_USL_NAPR SUN ON SUN.SchetSluch = del.SchetSluchID
		LEFT JOIN ISZL.T_SMO_ACT_ITEM SAI ON SAI.SchetSluch = del.SchetSluchID
	WHERE
	    del.SchetSluchAccomplished IN (
	        SELECT ssa.SchetSluchAccomplishedID
	        FROM ISZL.T_SCHET_SLUCH_ACCOMPLISHED ssa
	        JOIN ISZL.T_SCHET_ZAP sz ON sz.SchetZapID = ssa.SchetZap
	        WHERE sz.Schet = @SchetID
	    )
	    AND DD.SchetSluch IS NULL
		AND DDF.SchetSluch IS NULL
		AND DF.SchetSluch IS NULL
		AND SSC.SchetSluch IS NULL
		AND SSD.OmsSchetSluch IS NULL
		AND SSDS.SchetSluch IS NULL
		AND SSN.SchetSluch IS NULL
		AND SSO.SchetSluch IS NULL
		AND SSS.SchetSluch IS NULL
		AND SU.SchetSluch IS NULL
		AND SUN.SchetSluch IS NULL
		AND SAI.SchetSluch IS NULL;

	print 'delete T_SCHET_SLUCH_ACCOMPLISHED'

	delete del from ISZL.T_SCHET_SLUCH_ACCOMPLISHED del
		join iszl.T_SCHET_ZAP sz on sz.SchetZapID = del.SchetZap
	where sz.Schet = @SchetID;

	print 'delete T_SCHET_ZAP'

	delete del from ISZL.T_SCHET_ZAP del
		left join ISZL.T_SCHET_SLUCH_ACCOMPLISHED ssa ON ssa.SchetZap = del.SchetZapID
	where del.Schet = @SchetID
		and ssa.SchetZap is null;
	
	declare @FileID uniqueidentifier
	set @FileID = (select SchetFile from iszl.T_SCHET where SchetID = @SchetID)

	delete del from ISZL.T_SCHET del
	where del.SchetID = @SchetID

	delete del from ISZL.T_SCHET_FILE del
	where del.FileID = @FileID

	delete del from VClib.T_FILE del
	where FileId = @FileID
end
