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,
    @RemovalAllowed bit = 0
AS
BEGIN
  DECLARE @Unloaded bit
  SELECT @Unloaded = Unloaded FROM RDS.T_EXPORT_FRLLO_RECIPE WHERE ExportFrlloRecipeID = @Id

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

  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
    
    CREATE TABLE #REC_ID (ID UNIQUEIDENTIFIER);  
    CREATE INDEX IX_REC_ID_ID_000001 ON #REC_ID(ID);
    CREATE TABLE #D_REC_TABLE (ID UNIQUEIDENTIFIER);
    CREATE INDEX IX_REC_TABLE_ID_000001 ON #D_REC_TABLE(ID);
    CREATE TABLE #D_ELEM_TABLE (ID UNIQUEIDENTIFIER);
    CREATE INDEX IX_ELEM_TABLE_ID_000001 ON #D_ELEM_TABLE(ID);

    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;
    
    WHILE EXISTS(SELECT 1 FROM #REC_ID A JOIN RDS.T_RECIPE B ON A.ID = B.RecipeID)
    BEGIN
      
          UPDATE TOP (1000) rec
            SET rec.FrlloType = CASE  WHEN efrd.FrlloStatus is null and rec.FrlloType = 0 THEN null 
                                      WHEN efrd.FrlloStatus is null and rec.FrlloType = 2 THEN 0 
                                      WHEN efrd.FrlloStatus in (1,400) and rec.FrlloType = 1 then null
                                      WHEN efrd.FrlloStatus in (1,400) and rec.FrlloType = 3 then 0
                                      ELSE rec.FrlloType 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 EXISTS(SELECT 1 FROM #REC_ID A JOIN RDS.T_RECIPE_ELEMENT B ON A.ID = B.RecipeElementID)
      BEGIN
        
        UPDATE TOP (1000) rec_elem
          SET rec_elem.FrlloType = CASE  WHEN efrd.FrlloStatus is null and rec_elem.FrlloType = 0 THEN null 
                                         WHEN efrd.FrlloStatus is null and rec_elem.FrlloType = 2 THEN 0 
                                         WHEN efrd.FrlloStatus in (1,400) and rec_elem.FrlloType = 1 then null
                                         WHEN efrd.FrlloStatus in (1,400) and rec_elem.FrlloType = 3 then 0
                                         ELSE rec_elem.FrlloType END
        OUTPUT inserted.RecipeElementID 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

    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