IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_EHR_REPLACEMENT_FILL]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_EHR_REPLACEMENT_FILL]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ehr].[SP_EHR_REPLACEMENT_FILL]') AND type in (N'P', N'PC'))
DROP PROCEDURE ehr.[SP_EHR_REPLACEMENT_FILL]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE ehr.SP_EHR_REPLACEMENT_FILL
	@oldVersion varchar(8),
	@newVersion varchar(8),
	@code varchar(64),
	@parent uniqueidentifier,
	@Entity VarChar(100),
	@Attr VarChar(100),
	@OnlyUpdate bit
AS 
BEGIN
SET XACT_ABORT, NOCOUNT ON

DECLARE @OldEhrRusRefBooks int = (SELECT TOP 1 rb.EhrRusRefBooksID FROM EHR.T_EHR_RUS_REF_BOOKS rb WHERE rb.Code = @code AND rb.CurrVersion = @oldVersion)
DECLARE @NewEhrRusRefBooks int = (SELECT TOP 1 rb1.EhrRusRefBooksID FROM EHR.T_EHR_RUS_REF_BOOKS rb1 WHERE rb1.Code = @code AND rb1.CurrVersion = @newVersion)
DECLARE @EntityID int = (SELECT erfb.Entities FROM EHR.T_EHR_RUS_REF_BOOKS erfb WHERE erfb.EhrRusRefBooksID = @OldEhrRusRefBooks)
DECLARE @NameEHR VARCHAR(132) = (SELECT e.[Schema] + '.T_' + e.EntName FROM VCLib.T_ENTITIES e WHERE e.EntityID = @EntityID)
DECLARE @conditionCode VARCHAR(100)
IF EXISTS (SELECT TOP 1 MappingName FROM VCLib.T_ENTITIES_ATTRIBUTES ea WHERE EntityID = @EntityID And MappingName = 'IdEHR')
	SET @conditionCode = 'e1.IdEHR = e2.IdEHR'
ELSE IF EXISTS (SELECT TOP 1 MappingName FROM VCLib.T_ENTITIES_ATTRIBUTES ea WHERE EntityID = @EntityID And MappingName = 'CodeEHR')
	SET @conditionCode = 'e1.CodeEHR = e2.CodeEHR'	

declare @tsql nvarchar(max)
if(@OnlyUpdate = 0)
begin
	set @tsql = N'
	select distinct(dd.' + @Attr + N')
	into #OldData
	from ' + @Entity + N' dd 
	join ' + @NameEHR + N' er on er.EhrRusID = dd.' + @Attr + N'
	where er.EhrRusRefBooks = ' + Cast(@OldEhrRusRefBooks as varchar(50)) + '
	
	INSERT INTO  EHR.T_ELEMENT_EHR_REPLACEMENT (ElementEhrReplacementID, EhrReplacement, OldEhr, NewEhr, ExistsData, AutomaticallyFilled)
	SELECT NEWID(),  ''' + CAST(@parent as nvarchar(36)) + N''', e1.EhrRusID, e2.EhrRusID , case when old.' + @Attr + N' is not null then 1 else 0 end, case when e2.EhrRusID is not null then 1 else 0 end 
	FROM ' + @NameEHR + N' e1
	LEFT JOIN ' + @NameEHR + N' e2 ON ' + @conditionCode + ' AND e2.EhrRusRefBooks = ' + Cast(@NewEhrRusRefBooks as varchar(50)) + '
	left join #OldData old on old.' + @Attr + N' = e1.EhrRusID
	WHERE e1.EhrRusRefBooks = ' + Cast(@OldEhrRusRefBooks as varchar(50)) + '
	
	drop table #OldData'
end
else
begin
	set @tsql = N'
	select distinct(dd.' + @Attr + N')
	into #OldData
	from ' + @Entity + N' dd 
	join ' + @NameEHR + N' er on er.EhrRusID = dd.' + @Attr + N'
	where er.EhrRusRefBooks = ' + Cast(@OldEhrRusRefBooks as varchar(50)) + '
	
	update er
		set er.ExistsData = 1
	from EHR.T_ELEMENT_EHR_REPLACEMENT er
		join #OldData old on old.' + @Attr + N' = er.OldEhr
	where er.ExistsData = 0 and er.EhrReplacement = ''' + CAST(@parent as nvarchar(36)) + N'''
	
	drop table #OldData'
end 

--print @tsql;
exec sp_executesql @tsql;

return;

end

