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

  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);

  
  IF(@Unloaded = 1)
  BEGIN
    
    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.RecipeType = 2 THEN 0 ELSE NULL END
        OUTPUT inserted.RecipeID INTO #REC_ID(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.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