
/****** Object:  StoredProcedure [dbo].[DeleteSellFactorAndRelations]    Script Date: 06/06/2022 01:53:56 ب.ظ ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Azimi
-- Create date: 
-- Description:	
-- =============================================
ALTER PROCEDURE [dbo].[DeleteSellFactorAndRelations] 
	@SellFactorID bigint = 0
AS
BEGIN
	SET NOCOUNT ON;
	
	BEGIN TRANSACTION

/*
select * from AccDocItem where RelationTable in (1,2)
select * from AccDocHeader where id in (427)
select * from AccSellFactor where id in (130)
select * from AccSellFactorTrucks where id in (1093,159)
*/
	DECLARE @ID as BIGINT

	DECLARE SellFactorCursorr CURSOR FOR 
		select WareInID from AccSellFactor where ID = @SellFactorID 
		union
		select WareOutID from AccSellFactor where ID = @SellFactorID 
		union
		select WareInID from AccSellFactorTrucks where SellFactorID  = @SellFactorID
		union
		select WareOutID from AccSellFactorTrucks where SellFactorID  = @SellFactorID
	Open SellFactorCursorr
	
	FETCH NEXT FROM SellFactorCursorr INTO @ID
	WHILE @@FETCH_STATUS = 0 
	BEGIN
		EXEC DeleteWarehouseAndRelations @WarehouseID=@ID
		FETCH NEXT FROM SellFactorCursorr INTO @ID
	END
	CLOSE SellFactorCursorr
	DEALLOCATE SellFactorCursorr

	DECLARE SellFactorCursorr CURSOR FOR 
		select FactorID from AccSellFactor where ID = @SellFactorID 
		union
		select FactorID from AccSellFactorTrucks where SellFactorID  = @SellFactorID
	Open SellFactorCursorr
	
	FETCH NEXT FROM SellFactorCursorr INTO @ID
	WHILE @@FETCH_STATUS = 0 
	BEGIN
		EXEC DeleteFactorAndRelations @FactorID=@ID
		FETCH NEXT FROM SellFactorCursorr INTO @ID
	END
	CLOSE SellFactorCursorr
	DEALLOCATE SellFactorCursorr

	DECLARE @DocItems as VarChar(8000)
	DECLARE @SQLCMD as VarChar(8000)
	SET @SQLCMD  = 'Select ID from AccDocItem WITH (nolock) where RelationTable in (1,2) and RelationTableID in (select id from AccSellFactorTrucks where SellFactorID = ' + Str(@SellFactorID) + ')'
	+ CHAR(13) +'UNION'+ CHAR(13)+'Select ID from AccDocItem WITH (nolock) where RelationTable in (21) and RelationTableID = ' + Str(@SellFactorID) 

	exec CreateIDList @@CMD=@SQLCMD , @@RESULT=@DocItems output
	Delete AccSellFactorTrucks  where  SellFactorID = @SellFactorID
	if not @DocItems is NULL
	begin
		print @DocItems
		set @SQLCMD ='Delete AccDocItem where ID in '+@DocItems 
		print @SQLCMD
		exec (@SQLCMD)
	end
	Delete AccSellFactor where ID = @SellFactorID

	COMMIT

END
