IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_EhrRefUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_EhrRefUpdate]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_EhrRefUpdate]
    @IdRefB int = null
AS
BEGIN
	if (@IdRefB is null)
		begin
			delete
			from  EHR.T_EHR_REFERENCE where Entities = EhrEntity or EhrRusRefBooks is null

			delete ref  
			from EHR.T_EHR_REFERENCE ref
			join VCLib.T_ENTITIES e on e.EntityID = ref.Entities
			where not exists(select * 
					from VCLib.T_ENTITIES_ATTRIBUTES ea 
					where (ea.EntityID = ref.Entities or ea.EntityID = e.BaseEntity) and ea.Name = ref.AttributeName)
					
			delete er
			from EHR.T_EHR_REFERENCE er
				join VCLib.T_ENTITIES_ATTRIBUTES atr on atr.[Name] = er.AttributeName and atr.EntityID = er.Entities
				join VCLib.T_ENTITIES entRef on (entRef.[Schema] + '.' + entRef.EntName) = atr.SystemType
			where entRef.[Schema] = 'EHR' and  er.EhrEntity <> entRef.EntityID

			IF object_id('tempdb..#table') IS NOT NULL DROP TABLE #table
			create table #table(Entities int, AttributeName varchar(max), EhrEntity int)

			insert into #table(Entities, AttributeName, EhrEntity)
			select atr.EntityID, atr.Name, entRef.EntityID
			from VCLib.T_ENTITIES_ATTRIBUTES atr
			join VCLib.T_ENTITIES entRef on (entRef.[Schema] + '.' + entRef.EntName) = atr.SystemType
			where  atr.EntityID <> entRef.EntityID and entRef.[Schema] = 'EHR' and entRef.EntName <> 'EHR_RUS_REF_BOOKS'
			and not exists(
				select * 
				from EHR.T_EHR_REFERENCE ref 
				where	ref.Entities = atr.EntityID and 
						ref.AttributeName = atr.Name and 
						ref.EhrEntity = entRef.EntityID
			)

			;with curVers (Entities, Rb) as (
			select rb.Entities, rb.EhrRusRefBooksID
			from EHR.T_EHR_RUS_REF_BOOKS rb
			where rb.CurrVersion is not null and exists(select * from #table t where t.EhrEntity = rb.Entities)
			and not exists(select * 
							from EHR.T_EHR_RUS_REF_BOOKS rbb 
							where  rbb.Entities = rb.Entities
							and (	Cast(SUBSTRING(REPLACE(rbb.CurrVersion, ',','.'), 0, CHARINDEX('.', REPLACE(rbb.CurrVersion, ',','.'),0)) as decimal(31,10)) > Cast(SUBSTRING(REPLACE(rb.CurrVersion, ',','.'), 0, CHARINDEX('.', REPLACE(rb.CurrVersion, ',','.'),0))  as decimal(31,10))
									or
									(
									Cast(SUBSTRING(REPLACE(rbb.CurrVersion, ',','.'), 0, CHARINDEX('.', REPLACE(rbb.CurrVersion, ',','.'),0)) as decimal(31,10)) = Cast(SUBSTRING(REPLACE(rb.CurrVersion, ',','.'), 0, CHARINDEX('.', REPLACE(rb.CurrVersion, ',','.'),0))  as decimal(31,10))
										and
									Cast(SUBSTRING(REPLACE(rbb.CurrVersion, ',','.'), CHARINDEX('.', REPLACE(rbb.CurrVersion, ',','.'),0) + 1, len(REPLACE(rbb.CurrVersion, ',','.')) - CHARINDEX('.', REPLACE(rbb.CurrVersion, ',','.'),0)) as decimal(31,10)) > 
										Cast(SUBSTRING(REPLACE(rb.CurrVersion, ',','.'), CHARINDEX('.', REPLACE(rb.CurrVersion, ',','.'),0) + 1, len(REPLACE(rb.CurrVersion, ',','.')) - CHARINDEX('.', REPLACE(rb.CurrVersion, ',','.'),0)) as decimal(31,10))
									)
								)
							))
			insert into EHR.T_EHR_REFERENCE	([EhrReferenceID], [Entities], [AttributeName], [EhrRusRefBooks], [EhrEntity])
			select newid(), tab.Entities, tab.AttributeName, cv.Rb, tab.EhrEntity
			from #table tab 
			join curVers cv on cv.Entities = tab.EhrEntity
			where cv.Rb is not null
			
			drop table #table
		end
	else
		begin
			update ref
				set ref.EhrRusRefBooks = @IdRefB
			from EHR.T_EHR_REFERENCE ref
					join EHR.T_EHR_RUS_REF_BOOKS refbook on refbook.EhrRusRefBooksID = @IdRefB and ref.EhrEntity = refbook.Entities
			where	ref.EhrRusRefBooks is null
			
			INSERT INTO [EHR].[T_EHR_REFERENCE]
					   ([EhrReferenceID]
					   ,[Entities]
					   ,[AttributeName]
					   ,[EhrRusRefBooks]
					   ,[EhrEntity])
			select	newid()
					,ea.EntityID
					,ea.Name
					,@IdRefB
					,eEhr.EntityID
					from VCLib.T_ENTITIES_ATTRIBUTES ea
						join VCLib.T_ENTITIES eEhr on eEhr.[Schema] + '.' + eEhr.EntName = ea.SystemType
						join EHR.T_EHR_RUS_REF_BOOKS refbook on refbook.EhrRusRefBooksID = @IdRefB and eEhr.EntityID = refbook.Entities
					where ea.EntityID <> eEhr.EntityID and eEhr.EntName <> 'EHR_RUS_REF_BOOKS'and 
					not exists(select * from [EHR].[T_EHR_REFERENCE] err where err.EhrEntity = eEhr.EntityID and err.Entities = ea.EntityID and err.AttributeName = ea.Name)
		end
END