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
if exists (select * from dbo.sysobjects where id = object_id(N'[ehr].[sp_Process_SF8U7PKK0U]') and xtype = N'P')
drop procedure ehr.[sp_Process_SF8U7PKK0U]
GO


CREATE PROCEDURE ehr.[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 int, @ehrRusIDsCount int, @maxEhrRusID int;
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
		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.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] = Convert(datetime, p.[Registration_date]	,104),
           [Registration_date_end] = Convert(datetime, p.[Registration_date_end]	,104),
           [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] = -1
	from #SF8U7PKK0U p
	where not exists (select * from [EHR].[T_EHR_RUS_SF8U7PKK0U] r2 where r2.IdEHR = p.ID and r2.EhrRusRefBooks = @refBooks);

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

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

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

	INSERT INTO [EHR].[T_EHR_RUS_SF8U7PKK0U]
	           ([EhrRusRefBooks],
	            [EhrRusID],
	            [IdEHR],
	            [NameEHR],
	            [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 
				@refBooks,
				p.[EhrRusID],
				p.ID, --<IdEHR, int>
				p.Label_RU, --<NameEHR, varchar(1024)>
		        p.[ID_RU],
                p.[ID_Model],
                p.[Status_RU],
                p.[Status],
                p.[Label],
                p.[Dt_from],
                p.[Dt_to],
                p.[Registration_number],
                Convert(datetime, p.[Registration_date]	,104),
                Convert(datetime, p.[Registration_date_end]	,104),
                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
	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 #SF8U7PKK0U;

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

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

	INSERT INTO [EHR].[T_EHR_RUS_SF8U7PKK0U]
		       ([EhrRusRefBooks],
			    [EhrRusID],
	            [IdEHR],
			    [NameEHR],
	            [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 
				@refBooks,
				p.[EhrRusID],
				p.ID, --<IdEHR, int>
				p.Label_RU, --<NameEHR, varchar(1024)>
				p.[ID_RU],
                p.[ID_Model],
                p.[Status_RU],
                p.[Status],
                p.[Label],
                p.[Dt_from],
                p.[Dt_to],
                p.[Registration_number],
                Convert(datetime, p.[Registration_date]	,104),
                Convert(datetime, p.[Registration_date_end]	,104),
                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