SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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


CREATE PROCEDURE [dbo].[sp_Process_SF8U7PKK0U] 
    @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_SF8U7PKK0U';
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_SF8U7PKK0U er 
		left join #SF8U7PKK0U 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.Label_RU
	from [EHR].[T_EHR_RUS_SF8U7PKK0U] r2 
		join #SF8U7PKK0U p on r2.IdEHR = p.ID
	where r2.EhrRusRefBooks = @refBooks;

	UPDATE rb																					  
		SET 																						    
	       [ID_RU] = p.[ID_RU],
           [ID_Model] = p.[ID_Model],
           [Status_RU] = p.[Status_RU],
           [Status] = p.[Status],
           [Label] = p.[Label],
           [Dt_from] = p.[Dt_from],
           [Dt_to] = p.[Dt_to],
           [Registration_number] = p.[Registration_number],
           [Registration_date] = p.[Registration_date],
           [Registration_date_end] = p.[Registration_date_end],
           [Producer] = p.[Producer],
           [Producer_oksm_id] = p.[Producer_oksm_id],
           [Producer_country] = p.[Producer_country],
           [OKP] = p.[OKP],
           [Class] = p.[Class],
           [Kind] = p.[Kind]
	 FROM  [EHR].[T_EHR_RUS_SF8U7PKK0U] rb				
		join #SF8U7PKK0U p on rb.IdEHR = p.ID
	WHERE rb.EhrRusRefBooks = @refBooks;

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

	INSERT INTO [EHR].[T_EHR_RUS_SF8U7PKK0U]
	           ([EhrRusRefBooks]
	           ,[EhrRusID]
	           ,[IdEHR]
	           ,[NameEHR])
	select
		@refBooks
		,p.[EhrRusID]
		,p.ID --<IdEHR, int>
		,p.Label_RU --<NameEHR, varchar(1024)>
	from #SF8U7PKK0U p
	where p.[EhrRusID] is not null;

	INSERT INTO [EHR].[T_EHR_RUS_SF8U7PKK0U]
	           ([EhrRusID],
	            [ID_RU],
                [ID_Model],
                [Status_RU],
                [Status],
                [Label],
                [Dt_from],
                [Dt_to],
                [Registration_number],
                [Registration_date],
                [Registration_date_end],
                [Producer],
                [Producer_oksm_id],
                [Producer_country],
                [OKP],
                [Class],
                [Kind])
	select 
		        p.[EhrRusID],
		        p.[ID_RU],
                p.[ID_Model],
                p.[Status_RU],
                p.[Status],
                p.[Label],
                p.[Dt_from],
                p.[Dt_to],
                p.[Registration_number],
                p.[Registration_date],
                p.[Registration_date_end],
                p.[Producer],
                p.[Producer_oksm_id],
                p.[Producer_country],
                p.[OKP],
                p.[Class],
                p.[Kind]
	from #SF8U7PKK0U 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 #SF8U7PKK0U p

	INSERT INTO [EHR].[T_EHR_RUS_SF8U7PKK0U]
		       ([EhrRusRefBooks]
			   ,[EhrRusID]
	           ,[IdEHR]
			   ,[NameEHR])
	select
		@refBooks
		,p.[EhrRusID]
		,p.ID --<IdEHR, int>
		,p.Label_RU --<NameEHR, varchar(1024)>
	from #SF8U7PKK0U p
	where p.[EhrRusID] is not null;

	INSERT INTO [EHR].[T_EHR_RUS_SF8U7PKK0U]
	           ([EhrRusID],
	            [ID_RU],
                [ID_Model],
                [Status_RU],
                [Status],
                [Label],
                [Dt_from],
                [Dt_to],
                [Registration_number],
                [Registration_date],
                [Registration_date_end],
                [Producer],
                [Producer_oksm_id],
                [Producer_country],
                [OKP],
                [Class],
                [Kind])  
	select 
		p.[EhrRusID],
		p.[ID_RU],
                p.[ID_Model],
                p.[Status_RU],
                p.[Status],
                p.[Label],
                p.[Dt_from],
                p.[Dt_to],
                p.[Registration_number],
                p.[Registration_date],
                p.[Registration_date_end],
                p.[Producer],
                p.[Producer_oksm_id],
                p.[Producer_country],
                p.[OKP],
                p.[Class],
                p.[Kind]
	from #SF8U7PKK0U 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