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

	select
		t.c.value('./document_id[1]', 'uniqueidentifier')	    as [document_id]
		,t.c.value('./status[1]', 'int')					    as [status]
		,t.c.value('./recipe_id[1]', 'varchar(100)')			as [recipe_id]
		,t.c.value('./recipe_release_id[1]', 'varchar(100)')	as [recipe_release_id]
		,t.c.value('./digit_cert_id[1]', 'varchar(100)')	    as [digit_cert_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_EC_DATA fed
        where fed.ExportFrlloElectronicCertificate = @ExportId 
            and (fed.FrlloStatus <> 1 or fed.FrlloStatus is null)

    END
    ELSE 
    BEGIN

	    CREATE TABLE #TMP_UPD(
		      [ID] INT IDENTITY(1,1)
		     ,[recipe_id]	varchar(100)
		     ,[status] int
		     ,[recipe_release_id] varchar(100)
		     ,[digit_cert_id] varchar(100)
		     ,[error] varchar(max)
		     ,ElectronicCertificateApplication uniqueidentifier
		     ,ExportFrlloEcDataID uniqueidentifier
		     ,ExportFrlloElectronicCertificate uniqueidentifier
	    )
	
	    DECLARE @DT TABLE(ID INT)

	    INSERT INTO #TMP_UPD([recipe_id], [status], [recipe_release_id],[digit_cert_id], [error], ElectronicCertificateApplication, ExportFrlloEcDataID, ExportFrlloElectronicCertificate)
	    SELECT 
		     A.recipe_id
		    ,A.status
		    ,A.recipe_release_id
		    ,A.digit_cert_id
            ,a.error
		    ,st.ElectronicCertificateApplication
		    ,fed.ExportFrlloEcDataID
		    ,fed.ExportFrlloElectronicCertificate
	    FROM RDS.T_EXPORT_FRLLO_EC_DATA fed
            join RDS.T_ELECTRONIC_CERTIFICATE_STATUS st on st.ElectronicCertificateStatusID = fed.ObjId
	    cross apply (
		    select 
			     Max(a.recipe_id) as recipe_id
			    , a.status
			    , Max(a.recipe_release_id) as recipe_release_id
			    , a.digit_cert_id
			    , (select a2.code + '-' + a2.description + '/' from #Answer1 a2 where a2.document_id = a.document_id for xml path('')) as error
		    from #Answer1 a 
			    where st.ElectronicCertificateApplication = @ExportId and a.document_id = fed.ObjId
		    group by a.document_id, a.digit_cert_id, a.status
	    ) a
        where  fed.ExportFrlloElectronicCertificate = @ExportId

	    WHILE EXISTS(SELECT 1 FROM #TMP_UPD)
	    BEGIN
		
		    UPDATE fed
			    set  fed.FrlloId = U.digit_cert_id
			        ,fed.Recipe_id = U.recipe_id
				    ,fed.Recipe_release_id = U.recipe_release_id
				    ,fed.FrlloStatus = U.status
				    ,fed.FrlloError = U.error
		    OUTPUT U.ID INTO @DT(ID)
		    FROM RDS.T_EXPORT_FRLLO_EC_DATA fed
			    JOIN #TMP_UPD U ON U.ExportFrlloEcDataID = fed.ExportFrlloEcDataID

		    UPDATE ap
			    SET ap.FrlloId = u.digit_cert_id
		    FROM RDS.T_ELECTRONIC_CERTIFICATE_APPLICATION ap
			    JOIN #TMP_UPD U ON U.ElectronicCertificateApplication = ap.ElectronicCertificateApplicationID
		    WHERE
			    u.digit_cert_id IS NOT NULL 
			    AND ap.FrlloId IS 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				
				
