IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_DeleteFrlloRecipeProcess]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_DeleteFrlloRecipeProcess]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[rds].[sp_DeleteFrlloRecipeProcess]') AND type in (N'P', N'PC'))
DROP PROCEDURE [rds].[sp_DeleteFrlloRecipeProcess]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Vitacore (с) Anatoliy
-- Create date: 2021 05 25
-- Update date: 2023-12-29
-- Description: Удаление данных ФРЛЛО
-- =============================================
CREATE PROCEDURE [rds].[sp_DeleteFrlloRecipeProcess]
    @Id uniqueidentifier,
	@ForseDelete bit = 0,
    @RemovalAllowed bit
AS
BEGIN
	declare @Unloaded bit
	SELECT @Unloaded = Unloaded FROM RDS.T_EXPORT_FRLLO_RECIPE WHERE ExportFrlloRecipeID = @Id

    if (exists (select 1 from RDS.T_FRLLO_MESSAGE where ExportFrlloRecipe = @Id))
    begin
        if (@RemovalAllowed = 1)
        begin
            delete from RDS.T_FRLLO_MESSAGE where ExportFrlloRecipe = @Id
        end
        else
        begin
            raiserror('Нельзя удалить запись, так как существуют связанные сообщения', 16, 1)
            return
        end
    end

    if (@Unloaded = 1 and @ForseDelete = 0)
	begin
		RAISERROR('Запрещено удаление выгруженных данных!', 16, 10) 	
        return
	end

	if (exists (select 1 from RDS.T_EXPORT_FRLLO_RECIPE_DATA where ExportFrlloRecipe = @Id and FrlloStatus <> 400 and FrlloStatus is not null))
	begin
		RAISERROR('Есть успешно принятые в ФРЛЛО данные. Удаление запрещено!', 16, 10) 	
        return
	end
	
	if(@Unloaded = 1)
	begin
		DECLARE @REC_ID TABLE(ID UNIQUEIDENTIFIER)
		
		INSERT INTO @REC_ID(ID)
		SELECT efrd.ObjId as RecipeID 
		from  RDS.T_EXPORT_FRLLO_RECIPE_DATA efrd
		where efrd.ExportFrlloRecipe = @Id
		and (case when efrd.RecipeType in (0,2) and exists(select * from RDS.T_RECIPE r where r.RecipeId = efrd.ObjId) then 1
				when efrd.RecipeType in (1,3) and exists(select * from RDS.T_RECIPE_ELEMENT r where r.RecipeElementID = efrd.ObjId) then 1
				else 0 end) = 1;
		DECLARE @D_REC_TABLE TABLE(ID UNIQUEIDENTIFIER);
		DECLARE @D_ELEM_TABLE TABLE(ID UNIQUEIDENTIFIER);

		WHILE ((SELECT COUNT(*) FROM @REC_ID) > 0)
	BEGIN
		
		UPDATE TOP (1000) rec
			SET rec.FrlloType = CASE WHEN efrd.RecipeType = 2 THEN 0 ELSE NULL END
		OUTPUT inserted.RecipeID INTO @D_REC_TABLE(ID)
		FROM RDS.T_RECIPE rec
			JOIN RDS.T_EXPORT_FRLLO_RECIPE_DATA efrd ON efrd.ObjId = rec.RecipeID
			JOIN @REC_ID DR ON DR.ID = rec.RecipeID 

		DELETE R
		FROM @REC_ID R
		WHERE EXISTS(SELECT 1 FROM @D_REC_TABLE E WHERE E.ID = R.ID)	
	
	END

		WHILE ((SELECT COUNT(*) FROM @REC_ID) > 0)
	BEGIN

		UPDATE TOP (1000) rec_elem
			SET rec_elem.FrlloType = CASE WHEN efrd.RecipeType = 3 THEN 1 ELSE NULL END
		OUTPUT inserted.Recipe INTO @D_ELEM_TABLE(ID)
		FROM RDS.T_RECIPE_ELEMENT rec_elem
			JOIN RDS.T_EXPORT_FRLLO_RECIPE_DATA efrd ON efrd.ObjId = rec_elem.RecipeElementID 
			JOIN @REC_ID DR ON DR.ID = rec_elem.RecipeElementID

		DELETE R
		FROM @REC_ID R
		WHERE EXISTS(SELECT 1 FROM @D_ELEM_TABLE E WHERE E.ID = R.ID)	
	END
	end

	WHILE EXISTS(SELECT 1 FROM RDS.T_EXPORT_FRLLO_RECIPE_DATA WHERE ExportFrlloRecipe = @Id) 
	BEGIN
		
		DELETE TOP (1000) REC
		FROM RDS.T_EXPORT_FRLLO_RECIPE_DATA REC
		WHERE ExportFrlloRecipe = @Id

	END

	WHILE EXISTS(SELECT 1 FROM RDS.T_EXPORT_FRLLO_RECIPE WHERE ExportFrlloRecipeID = @Id) 
	BEGIN

		DELETE TOP (1000) REC
		FROM RDS.T_EXPORT_FRLLO_RECIPE REC
		WHERE ExportFrlloRecipeID = @Id

	END
END