IF EXISTS
(
	SELECT *
	FROM   sys.objects
	WHERE  object_id = OBJECT_ID(N'dbo.sp_GetSetLpuOverRequest')
		   AND [type] IN(N'P', N'PC')
)
	BEGIN
		DROP PROCEDURE [dbo].sp_GetSetLpuOverRequest;
	END;
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
-- ===================================================
-- Author:      Vitacore (с)
-- Create date: 2016-02-29
-- Description: Синхронизация превышения заявки по ЛПУ
-- ===================================================


CREATE PROCEDURE [dbo].sp_GetSetLpuOverRequest 
(@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('LPU_OVER_REQUEST') AS T(c);
			
				-- Создаём временную таблицу из xml
				SELECT [c].value('ID[1]', 'uniqueidentifier')									AS [ID],
					   [c].value('DrugApplicationListItemID[1]', 'uniqueidentifier')			AS [DrugApplicationListItemID],
					   [c].value('LpuMainID[1]', 'varchar(6)')									AS [LpuMainID] 
				INTO [#LPU_OVER_REQUEST]
				FROM   @Xml.nodes('LPU_OVER_REQUEST/body/LPU_OVER_REQUESTS/LPU_OVER_REQUEST')	AS T(c)
				WHERE  @OperationType = @FTLOAD;
			
				-- Обновляем существующие
				UPDATE lor
				  SET
					  [lor].[RequestCatalog] = [#lor].[DrugApplicationListItemID],
					  [lor].[LpuMain] = [lm].[LpuID]
				FROM [AKUZ].[T_LPU_OVER_REQUEST] [lor]
					 JOIN [dbo].[#LPU_OVER_REQUEST] [#lor] ON [#lor].[ID] = [lor].[LpuOverRequestID]
					 JOIN [AKUZ].[T_LPU_MAIN] [lm] ON [#lor].[LpuMainID] = [lm].[LpuCode];
				
				-- Добавляем новые
				INSERT INTO [AKUZ].T_LPU_OVER_REQUEST
				([LpuOverRequestID],
				 [RequestCatalog],
				 [LpuMain]
				)
				SELECT [#lor].[ID],
					   [#lor].[DrugApplicationListItemID],
					   [lm].[LpuID]
				FROM   dbo.#LPU_OVER_REQUEST AS #lor
					   JOIN [AKUZ].[T_LPU_MAIN] AS lm ON #lor.LpuMainID = lm.LpuCode
				WHERE  NOT EXISTS
				(
					SELECT [lor].[LpuOverRequestID]
					FROM   [AKUZ].[T_LPU_OVER_REQUEST] AS [lor]
					WHERE  [lor].[LpuOverRequestID] = [#lor].[ID]
				);

				-- Удаляем отсутствующие
				DELETE lor
				FROM [AKUZ].[T_LPU_OVER_REQUEST] [lor]
				WHERE  NOT EXISTS
				(
					SELECT [#lor].[ID]
					FROM   [dbo].[#LPU_OVER_REQUEST] AS [#lor]
					WHERE  [#lor].[ID] = [lor].[LpuOverRequestID]
				);
			
				-- Удаляем временную таблицу
				DROP TABLE #LPU_OVER_REQUEST;
			END;
	END;
GO