IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_LoadAnswerFromFrlloRequestProcess]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_LoadAnswerFromFrlloRequestProcess]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Vitacore (с) Anatoliy
-- Create date: 2023 09 07
-- Description: Выгрузка данных в ФРЛЛО
-- =============================================
CREATE PROCEDURE [dbo].[sp_LoadAnswerFromFrlloRequestProcess]
    @ExportId uniqueidentifier,
    @Xml xml
AS
BEGIN
	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]
		,t.c.value('./pers_needs_id[1]', 'varchar(100)')	as [pers_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)

	CREATE TABLE #TMP_UPD(
		  [ID] INT IDENTITY(1,1)
		 ,[reg_num]	varchar(100)
		 ,[status] int
		 ,[pers_needs_id] varchar(100)
		 ,[error] varchar(max)
		 ,[ExportFrlloDrugRequest] uniqueidentifier
		 ,[ExportFrlloDrugRequestItemID] uniqueidentifier
	)
	
	DECLARE @DT TABLE(ID INT)

	INSERT INTO #TMP_UPD([reg_num], [status], [pers_needs_id], [error], [ExportFrlloDrugRequest], [ExportFrlloDrugRequestItemID])
	SELECT 
		 A.reg_num
		,A.status
		,A.status
		,A.pers_needs_id
		,fed.ExportFrlloDrugRequest
		,fed.ExportFrlloDrugRequestItemID
	FROM RDS.T_EXPORT_FRLLO_DRUG_REQUEST_ITEM fed
	cross apply (
		select 
			a.reg_num
			, a.status
			, Max(a.pers_needs_id) as pers_needs_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 fed.ExportFrlloDrugRequest = @ExportId and a.document_id = fed.ExportFrlloDrugRequestItemID
		group by a.document_id, a.reg_num, a.status
	) a

	WHILE EXISTS(SELECT 1 FROM #TMP_UPD)
	BEGIN
		
		UPDATE fed
			set  fed.FrlloId = U.reg_num
				,fed.FrlloStatus = U.status
				,fed.FrlloPersNeedsId = U.pers_needs_id
				,fed.FrlloError = U.error
		OUTPUT U.ID INTO @DT(ID)
		FROM RDS.T_EXPORT_FRLLO_DRUG_REQUEST_ITEM fed
			JOIN #TMP_UPD U ON U.ExportFrlloDrugRequest = fed.ExportFrlloDrugRequest AND U.ExportFrlloDrugRequestItemID = fed.ExportFrlloDrugRequestItemID

		UPDATE PDD
			SET pdd.FrlloId = fed.FrlloId
		FROM RDS.T_PATIENT_DRUG_DEMAND pdd
			JOIN RDS.T_EXPORT_FRLLO_DRUG_REQUEST_ITEM fed on fed.ObjId = pdd.PatientDrugDemandID
			JOIN #TMP_UPD U ON U.ExportFrlloDrugRequest = fed.ExportFrlloDrugRequest
		WHERE
			fed.FrlloId IS NOT NULL 
			AND pdd.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				
				