IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_LoadAnswerFromFrlloRecipeProcess]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_LoadAnswerFromFrlloRecipeProcess]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[rds].[sp_LoadAnswerFromFrlloRecipeProcess]') AND type in (N'P', N'PC'))
DROP PROCEDURE [rds].[sp_LoadAnswerFromFrlloRecipeProcess]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Vitacore (с) Anatoliy
-- Create date: 2021 05 25
-- Description: Выгрузка данных в ФРЛЛО
-- =============================================
CREATE PROCEDURE [rds].[sp_LoadAnswerFromFrlloRecipeProcess]
    @ExportId uniqueidentifier,
    @Xml xml
AS
BEGIN
	DECLARE 
        @Type int,
        @GeneralError varchar(max) = ''

	SELECT @Type = e.DataType 
	FROM RDS.T_EXPORT_FRLLO_RECIPE e 
	WHERE e.ExportFrlloRecipeID = @ExportId
	
	SELECT
		 t.c.value('./document_id[1]', 'uniqueidentifier')		as [document_id]
		,t.c.value('./status[1]', 'int')						as [status]
		,t.c.value('./reg_num[1]', 'varchar(100)')				as [reg_num]
		,COALESCE
			(t.c.value('./recipe_id[1]', 'varchar(100)')
			,t.c.value('./relise_id[1]', 'varchar(100)'))		as [recipe_id]
		,err.c.value('./code[1]', 'varchar(max)')				as [code]
		,err.c.value('./description[1]', 'varchar(max)')		as [description]
	INTO #Answer
		FROM @Xml.nodes('root/result/element') T(c)
	OUTER APPLY t.c.nodes('./errors/element') err(c)
	
	IF NOT EXISTS(SELECT 1 FROM #Answer)
	BEGIN
		RAISERROR('Структура загружаемого файла не соответствует документации, либо Вы загружаете пустой файл!', 16, 10) 		
	END
	
    IF ((SELECT COUNT(*) FROM #Answer a WHERE a.document_id is null and a.description is not null) > 0) 
    BEGIN
        SELECT @GeneralError = @GeneralError + '||' + a.description
        FROM #Answer a
        WHERE a.document_id is null and a.description is not null
        GROUP BY a.description

	    update fed
	    set 
		    fed.FrlloStatus = 400,
		    fed.FrlloError = @GeneralError
	    from RDS.T_EXPORT_FRLLO_RECIPE_DATA fed
        where fed.ExportFrlloRecipe = @ExportId 
            and fed.FrlloStatus <> 1

    END
    ELSE 
    BEGIN

	    CREATE TABLE #TMP_UPD(
		      [ID] INT IDENTITY(1,1)
		     ,[reg_num]	varchar(100)
		     ,[status] int
		     ,[recipe_id] varchar(100)
		     ,[error] varchar(max)
		     ,[ExportFrlloRecipe] uniqueidentifier
		     ,[ExportFrlloRecipeDataID] uniqueidentifier
	    )
	
	    DECLARE @DT TABLE(ID INT)
	
	    INSERT INTO #TMP_UPD([reg_num], [status], [recipe_id], [error], [ExportFrlloRecipe], [ExportFrlloRecipeDataID])
	    SELECT 
		     [reg_num]
		    ,[status]
		    ,[recipe_id]
		    ,[error]
		    ,fed.[ExportFrlloRecipe]
		    ,fed.[ExportFrlloRecipeDataID]
	    from RDS.T_EXPORT_FRLLO_RECIPE_DATA fed
	    cross apply (
		    select 
			    a.reg_num
			    , a.status
			    , MAX(a.recipe_id) as recipe_id
			    , (select a2.code + '-' + a2.description + '/' from #Answer a2 where a2.document_id = a.document_id for xml path('')) as error
		    from #Answer a 
			    where fed.ExportFrlloRecipe = @ExportId and a.document_id = fed.ExportFrlloRecipeDataID
		    group by a.document_id, a.reg_num, a.status
	    ) a
	
	    WHILE EXISTS(SELECT 1 FROM #TMP_UPD)
	    BEGIN
	
		    UPDATE TOP (1000) fed
			    set  fed.FrlloId = T.recipe_id
				    ,fed.FrlloStatus = T.status
				    ,fed.RegNum = T.reg_num
				    ,fed.FrlloError = T.error
			    OUTPUT T.ID INTO @DT(ID)
		    FROM RDS.T_EXPORT_FRLLO_RECIPE_DATA fed
			    JOIN #TMP_UPD T ON T.[ExportFrlloRecipe] = fed.ExportFrlloRecipe AND T.[ExportFrlloRecipeDataID] = fed.ExportFrlloRecipeDataID
			

		    IF @Type = 0
		    BEGIN
		
			    UPDATE r
				    SET r.FrlloId = fed.FrlloId
			    FROM RDS.T_RECIPE r
				    JOIN RDS.T_EXPORT_FRLLO_RECIPE_DATA fed ON fed.[ObjId] = r.RecipeID
				    JOIN #TMP_UPD U ON U.[ExportFrlloRecipe] = fed.ExportFrlloRecipe
			    WHERE 
				    fed.FrlloId IS NOT NULL 
				    AND r.FrlloId IS NULL 
				    AND EXISTS(SELECT 1 FROM @DT D WHERE D.ID = U.ID)
	
			    UPDATE R
				    SET r.FrlloType = (CASE WHEN r.FrlloType = 1 THEN 0 ELSE NULL END) 
			    FROM RDS.T_RECIPE r
				    JOIN RDS.T_EXPORT_FRLLO_RECIPE_DATA fed on fed.ObjId = r.RecipeID
				    JOIN #TMP_UPD U ON U.[ExportFrlloRecipe] = fed.ExportFrlloRecipe 
			    WHERE 
				    fed.FrlloStatus = 400 
				    AND r.FrlloType IS NOT NULL
				    AND EXISTS(SELECT 1 FROM @DT D WHERE D.ID = U.ID)
	
			    DELETE U
			    FROM #TMP_UPD U
			    WHERE EXISTS(SELECT 1 FROM @DT D WHERE D.ID = U.ID)
	
			    DELETE @DT
		    END
		    ELSE IF @Type = 1
		    BEGIN
			
			    UPDATE re
				    SET re.FrlloId = fed.FrlloId
			    FROM RDS.T_RECIPE_ELEMENT re
				    JOIN RDS.T_EXPORT_FRLLO_RECIPE_DATA fed ON fed.ObjId = re.RecipeElementID
				    JOIN #TMP_UPD U ON U.[ExportFrlloRecipe] = fed.ExportFrlloRecipe
			    WHERE 
				    fed.FrlloId IS NOT NULL 
				    AND re.FrlloId IS NULL
				    AND EXISTS(SELECT 1 FROM @DT D WHERE D.ID = U.ID)

			    UPDATE re
				    SET re.FrlloType = (CASE WHEN RE.FrlloType = 1 THEN 0 ELSE NULL END) 
			    FROM RDS.T_RECIPE_ELEMENT re
				    JOIN RDS.T_EXPORT_FRLLO_RECIPE_DATA fed on fed.ObjId = re.RecipeElementID
				    JOIN #TMP_UPD U ON U.[ExportFrlloRecipe] = fed.ExportFrlloRecipe			
			    WHERE 
				    fed.FrlloStatus = 400 
				    AND re.FrlloType IS NOT NULL
				    AND EXISTS(SELECT 1 FROM @DT D WHERE D.ID = U.ID)

			    DELETE U
			    FROM #TMP_UPD U
			    WHERE EXISTS(SELECT 1 FROM @DT D WHERE D.ID = U.ID)
	
			    DELETE @DT

		    END
	    END
    END
END