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 int, @ehrRusIDsCount int, @maxEhrRusID int;
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
		EXEC @refBooks = sp_GenerateIdForEntity 'EHR.EHR_RUS_REF_BOOKS';

		INSERT INTO [EHR].[T_EHR_RUS_REF_BOOKS]
			([EhrRusRefBooksID],[Code],[Name],[CurrVersion],[UpdateDate],[Entities],[OID], Deleted)
		VALUES
			(@refBooks, @Code, @Name, @CurrVersion, getdate(), @entityID, @OID, 0)
	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] = -1
	from #PN8K8VMPD5 p
	where not exists (select * from [EHR].[T_EHR_RUS_PN8K8VMPD5] r2 where r2.IdEHR = p.ID and r2.EhrRusRefBooks = @refBooks);

	SELECT @ehrRusIDsCount = COUNT(*) FROM #PN8K8VMPD5 p WHERE p.[EhrRusID] = -1

	EXEC @maxEhrRusID =  sp_GenerateIdForEntity 'EHR.EHR_RUS_PN8K8VMPD5' , 'PrimaryKey', @ehrRusIDsCount
	SET @maxEhrRusID = @maxEhrRusID - 1

	;WITH CTE AS (
		SELECT
			[EhrRusID],
			ROW_NUMBER() OVER (ORDER BY ID) AS RowNum
		FROM #PN8K8VMPD5
		WHERE [EhrRusID] = -1 
	)
	UPDATE CTE 
	SET [EhrRusID] = RowNum + @maxEhrRusId;

	INSERT INTO [EHR].[T_EHR_RUS_PN8K8VMPD5]
	           ([EhrRusRefBooks]
	           ,[EhrRusID]
	           ,[IdEHR]
	           ,[CodeEHR]
	           ,[NameEHR]
	           ,[SORT])
	select 
		@refBooks
		,p.[EhrRusID]
		,p.ID
		,p.[ICD-10]
		,p.S_NAME
		, p.[SORT]
	from #PN8K8VMPD5 p
	where p.[EhrRusID] is not null;
end
else
begin
	EXEC @refBooks = sp_GenerateIdForEntity 'EHR.EHR_RUS_REF_BOOKS';

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

	SELECT @ehrRusIDsCount = COUNT(*) FROM #PN8K8VMPD5;

	EXEC @maxEhrRusID = sp_GenerateIdForEntity 'EHR.EHR_RUS_PN8K8VMPD5', 'PrimaryKey', @ehrRusIDsCount;
	SET @maxEhrRusID = @maxEhrRusID - 1;

	;WITH CTE AS (
		SELECT
			[EhrRusID],
			ROW_NUMBER() OVER (ORDER BY ID) AS RowNum
		FROM #PN8K8VMPD5
	)
	UPDATE CTE 
	SET [EhrRusID] = RowNum + @maxEhrRusId;

	INSERT INTO [EHR].[T_EHR_RUS_PN8K8VMPD5]
		       ([EhrRusRefBooks]
			   ,[EhrRusID]
	           ,[IdEHR]
		       ,[CodeEHR]
			   ,[NameEHR]
	           ,[SORT])  
	select 
		@refBooks
		,p.[EhrRusID]
		,p.ID
		,p.[ICD-10]
		,p.S_NAME
		,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