IF EXISTS
(
	SELECT *
	FROM   sys.objects
	WHERE  object_id = OBJECT_ID(N'dbo.sp_GetSetAdditionalFactorsRequestCatalog')
		   AND [type] IN(N'P', N'PC')
)
	BEGIN
		DROP PROCEDURE [dbo].sp_GetSetAdditionalFactorsRequestCatalog;
	END;
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
-- ===================================================
-- Author:      Vitacore (с)
-- Create date: 2019-04-23
-- Description: Синхронизация дополнительных факторов заявочного каталога
-- ===================================================


CREATE PROCEDURE [dbo].sp_GetSetAdditionalFactorsRequestCatalog @Xml       xml              = NULL,
											 @ID        uniqueidentifier = NULL,
											 @IsNew     bit              = 1,
											 @QueueName varchar(100)     = 'RDSSync',
											 @dateTime  datetime         = NULL,
											 @User      uniqueidentifier = NULL
AS
	BEGIN
		SET NOCOUNT ON;
		DECLARE @OperationType varchar(255), @FTLOAD varchar(6);
		SELECT @FTLOAD = 'FTLoad';
		IF @Xml IS NOT NULL
			BEGIN

				-- Получаем тип операции
				SELECT @OperationType = [c].value('header[1]/TYPE[1]', 'varchar(36)')
				FROM   @Xml.nodes('ADDITIONAL_FACTORS_REQUEST_CATALOG') AS T(c);
			
				-- Создаём временную таблицу из xml
				SELECT [c].value('ID[1]', 'uniqueidentifier')				AS  [ID],
					   [c].value('DrugApplicationListItem[1]', 'uniqueidentifier')	AS [RequestCatalog],
					   [c].value('BenefitViews[1]', 'int')					AS   [BenefitViews] ,
					   [c].value('BenefitCode[1]', 'varchar(255)')			AS   [BenefitCode] 
					   INTO [#ADDITIONAL_FACTORS_REQUEST_CATALOG]
				FROM   @Xml.nodes('ADDITIONAL_FACTORS_REQUEST_CATALOG/body/ADDITIONAL_FACTORS_REQUEST_CATALOGS/ADDITIONAL_FACTORS_REQUEST_CATALOG') AS T(c)
				WHERE  @OperationType = @FTLOAD;
			
				-- Удаляем отсутствующие
				DELETE adrc
				FROM [AKUZ].[T_ADDITIONAL_FACTORS_REQUEST_CATALOG] adrc
				
				-- Добавляем новые
				INSERT INTO [AKUZ].[T_ADDITIONAL_FACTORS_REQUEST_CATALOG]
				(AdditionalFactorsRequestCatalogID,
				 RequestCatalog,
				 Benefits
				)
				SELECT adrc.id,
						adrc.RequestCatalog,
						b.BenefitsID
				FROM   #ADDITIONAL_FACTORS_REQUEST_CATALOG adrc
				left join AKUZ.T_BENEFITS b on b.BenefitCode = adrc.BenefitCode and b.BenefitViews = adrc.BenefitViews
			END;
	END;
GO