IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[rds].[sp_LoadAnswerFromFrlloNeedsProcess]') AND type in (N'P', N'PC'))
DROP PROCEDURE [rds].[sp_LoadAnswerFromFrlloNeedsProcess]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Vitacore (с) Anatoliy
-- Create date: 2025 09 11
-- Description: Загрузка ответа от ФРЛЛО по прогнозируемой потребности
-- =============================================
CREATE PROCEDURE [rds].[sp_LoadAnswerFromFrlloNeedsProcess]
    @ExportId int,
    @Xml xml
AS
BEGIN
	
	DECLARE @GeneralError varchar(max) = ''

	select
		t.c.value('./document_id[1]', 'varchar(100)')            as [document_id]
		,t.c.value('./status[1]', 'int')					    as [status]
		,t.c.value('./prognosis_needs_id[1]', 'varchar(100)')	as [prognosis_needs_id]
		,err.c.value('./code[1]', 'varchar(max)')			    as [code]
		,err.c.value('./description[1]', 'varchar(max)')	    as [description]
	INTO #Answer1
		from @Xml.nodes('root/result/element') T(c)
	outer apply t.c.nodes('./errors/element') err(c)
	
    IF ((SELECT COUNT(*) FROM #Answer1 a WHERE a.document_id is null and a.description is not null) > 0) 
    BEGIN
        SELECT @GeneralError = @GeneralError + '||' + a.description
        FROM #Answer1 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_NEEDS_ELEMENT fed
        where fed.ExportFrlloNeeds = @ExportId 
            and fed.FrlloStatus <> 1

    END
    ELSE 
    BEGIN

	    CREATE TABLE #TMP_UPD(
		     [id] int identity(1,1)
		    ,[prognosis_needs_id] varchar(100)
		    ,[status] int
		    ,[error] varchar(max)
		    ,[ExportFrlloNeeds] int
		    ,[FrlloDocumentId] varchar(100)
	    )

	    DECLARE @DT TABLE(ID INT)
	
	    INSERT INTO #TMP_UPD
	    SELECT 
		     a.prognosis_needs_id
		    ,a.[status]
		    ,(select a2.code + '-' + a2.description + '/' from #Answer1 a2 where a2.document_id = a.document_id for xml path('')) as error
		    ,fed.ExportFrlloNeeds
		    ,fed.FrlloDocumentId
	    from RDS.T_EXPORT_FRLLO_NEEDS_ELEMENT fed
	    cross apply (
		    select 
			    a.prognosis_needs_id
			    , a.status
			    , (select a2.code + '-' + a2.description + '/' from #Answer1 a2 where a2.document_id = a.document_id for xml path('')) as error
		        ,a.document_id
		    from #Answer1 a 
			    where fed.ExportFrlloNeeds = @ExportId and a.document_id = fed.FrlloDocumentId
		    group by a.document_id, a.prognosis_needs_id, a.status
	    ) a 

	
	    WHILE EXISTS(SELECT 1 FROM #TMP_UPD)
	    BEGIN
	
		    UPDATE TOP (1000) FED
			    SET --fed.FrlloId = upd.prognosis_needs_id,
				    fed.FrlloStatus = upd.status,
				    fed.FrlloError = upd.error
		    OUTPUT UPD.ID INTO @DT(ID)
		    FROM RDS.T_EXPORT_FRLLO_NEEDS_ELEMENT fed
			    JOIN #TMP_UPD upd ON fed.ExportFrlloNeeds = upd.ExportFrlloNeeds and upd.FrlloDocumentId = fed.FrlloDocumentId

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

		    DELETE @DT

	    END
    END
END				
				