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
	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
	
	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.reg_num
				,fed.FrlloStatus = T.status
				,fed.FrlloRecipe_id = T.recipe_id
				,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;