SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Process_PN8K8VMPD5]') and xtype = N'P')
drop procedure [dbo].[sp_Process_PN8K8VMPD5]
GO

CREATE PROCEDURE [dbo].[sp_Process_PN8K8VMPD5] 
    @Code varchar(64),
    @Name varchar(255),
    @CurrVersion varchar(8),
	@OldVersion varchar(8),
    @OID varchar(54),
	@CheckMatching BIT
As
Begin
	SET NOCOUNT ON;

declare @entityID int, @refBooks uniqueidentifier;
select @entityID = Entityid from VCLib.T_ENTITIES e where EntName = 'EHR_RUS_PN8K8VMPD5';
select top 1 @refBooks = b.EhrRusRefBooksID from EHR.T_EHR_RUS_REF_BOOKS b where b.Entities = @entityID and b.CurrVersion = @CurrVersion;

if @OldVersion is not null or @refBooks is not null
begin
	if @refBooks is null
	begin
		select top 1 @refBooks = b.EhrRusRefBooksID from EHR.T_EHR_RUS_REF_BOOKS b where b.Entities = @entityID and b.CurrVersion = @OldVersion;
	end

	if @CheckMatching = 1
	begin
		IF object_id('tempdb..#tempTbForEhrs') IS NOT NULL
		DROP TABLE #tempTbForEhrs
		CREATE TABLE #tempTbForEhrs
		(
		Id1 int,
		Id2 int
		)

		insert into #tempTbForEhrs
		select er.IdEHR, tt.ID from ehr.T_EHR_RUS_PN8K8VMPD5 er 
		left join #PN8K8VMPD5 tt
		on er.IdEHR = tt.ID
		where er.EhrRusRefBooks = @refBooks

		if exists (select * from #tempTbForEhrs where id2 is null)
		begin
			raiserror('Невозможно обновить справочник, обнаружено несоответствие записей нового справочника с текущим. Скачайте новый справочник и обновите связи', 16, 10)
		end
		DROP TABLE #tempTbForEhrs
	end

	if @refBooks is null
	begin
		set @refBooks = newid();

		INSERT INTO [EHR].[T_EHR_RUS_REF_BOOKS]
			([EhrRusRefBooksID],[Code],[Name],[CurrVersion],[UpdateDate],[Entities],[OID])
		VALUES
			(@refBooks, @Code, @Name, @CurrVersion, getdate(), @entityID, @OID)
	end
	else
	begin
		update [EHR].[T_EHR_RUS_REF_BOOKS]
		set [Code] = @Code,
			[Name]= @Name,
			[CurrVersion] = @CurrVersion,
			[UpdateDate] = getdate(),
			[Entities] = @entityID,
			[OID] = @OID
		where [EhrRusRefBooksID] = @refBooks;
	end

	UPDATE r2
	SET [NameEHR] = p.S_NAME
		, [CodeEHR] = p.[ICD-10]
	from [EHR].[T_EHR_RUS_PN8K8VMPD5] r2 
		join #PN8K8VMPD5 p on r2.IdEHR = p.ID
	where r2.EhrRusRefBooks = @refBooks;

	UPDATE rb																					  
		SET 																						    
	       [SORT] = p.[SORT]
	 FROM  [EHR].[T_EHR_RUS_PN8K8VMPD5] rb				
		join #PN8K8VMPD5 p on rb.IdEHR = p.ID
	WHERE rb.EhrRusRefBooks = @refBooks;

	update p
	set [EhrRusID] = NEWID()
	from #PN8K8VMPD5 p
	where not exists (select * from [EHR].[T_EHR_RUS_PN8K8VMPD5] r2 where r2.IdEHR = p.ID and r2.EhrRusRefBooks = @refBooks);

	INSERT INTO [EHR].[T_EHR_RUS_PN8K8VMPD5]
	           ([EhrRusRefBooks]
	           ,[EhrRusID]
	           ,[IdEHR]
	           ,[CodeEHR]
	           ,[NameEHR])
	select
		@refBooks
		,p.[EhrRusID]
		,p.ID
		,p.[ICD-10]
		,p.S_NAME
	from #PN8K8VMPD5 p
	where p.[EhrRusID] is not null;

	INSERT INTO [EHR].[T_EHR_RUS_PN8K8VMPD5]
	           ([EhrRusID]
	           ,[SORT])
	select 
		p.[EhrRusID]
		, p.[SORT]
	from #PN8K8VMPD5 p
	where p.[EhrRusID] is not null;
end
else
begin
	set @refBooks = newid();

		INSERT INTO [EHR].[T_EHR_RUS_REF_BOOKS]
			([EhrRusRefBooksID],[Code],[Name],[CurrVersion],[UpdateDate],[Entities],[OID])
		VALUES
			(@refBooks, @Code, @Name, @CurrVersion, getdate(), @entityID, @OID)

	update p
	set [EhrRusID] = NEWID()
	from #PN8K8VMPD5 p

	INSERT INTO [EHR].[T_EHR_RUS_PN8K8VMPD5]
		       ([EhrRusRefBooks]
			   ,[EhrRusID]
	           ,[IdEHR]
		       ,[CodeEHR]
			   ,[NameEHR])
	select
		@refBooks
		,p.[EhrRusID]
		,p.ID
		,p.[ICD-10]
		,p.S_NAME
	from #PN8K8VMPD5 p
	where p.[EhrRusID] is not null;

	INSERT INTO [EHR].[T_EHR_RUS_PN8K8VMPD5]
	           ([EhrRusID]
	           ,[SORT])  
	select 
		p.[EhrRusID]
		,p.[SORT]
	from #PN8K8VMPD5 p
	where p.[EhrRusID] is not null;

	update ehr.T_EHR_REFERENCE
	set EhrRusRefBooks = @refBooks
	where EhrEntity = @entityID and EhrRusRefBooks is null
end
End
go