GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[ftNodeRelations](@nodeId int, @showHidden bit) returns
@ret TABLE
(
	id int primary key,
	NodeRelationTypeId int,
	BeginNodeId int,
	BeginNodeRelation nvarchar(32),
	BeginNodeKind nvarchar(255),
	BeginNodeName nvarchar(255),
	BeginNodePath nvarchar(1024),
	EndNodeId int,
	EndNodeRelation nvarchar(32),
	EndNodeKind nvarchar(255),
	EndNodeName nvarchar(255),
	EndNodePath nvarchar(1024),
	ModifiedDate datetime,
	ModifiedByPerson nvarchar(255),
	Straight bit,
	Level int
) as
begin
	declare @lev int=0
	
		insert into @ret (id,NodeRelationTypeId,BeginNodeId,BeginNodeRelation,EndNodeId,EndNodeRelation,Straight,Level)
	select
		nr.id,
		nr.NodeRelationTypeId,
		nr.BeginNodeId,
		nrt.EndName,
		nr.EndNodeId,
		nrt.BeginName,
		1,
		@lev
	from NodeRelation nr
		join NodeRelationType nrt on nrt.id=nr.NodeRelationTypeId and nrt.Directional=1
		join tblNode nb on nb.intNodeId=nr.BeginNodeId and (nb.bolHidden=0 or @showHidden=1)
		join tblNode ne on ne.intNodeId=nr.EndNodeId and (ne.bolHidden=0 or @showHidden=1)
	where nr.BeginNodeId=@nodeId

	while(exists(select 1 from @ret where Level=@lev))
	begin
		insert into @ret (id,NodeRelationTypeId,BeginNodeId,BeginNodeRelation,EndNodeId,EndNodeRelation,Straight,Level)
		select
			nr.id,
			nr.NodeRelationTypeId,
			nr.BeginNodeId,
			nrt.EndName,
			nr.EndNodeId,
			nrt.BeginName,
			0,
			@lev+1
		from @ret r
			join NodeRelationType nrt on nrt.id=r.NodeRelationTypeId
			join NodeRelation nr on r.NodeRelationTypeId=nr.NodeRelationTypeId and nr.BeginNodeId=r.EndNodeId
			join tblNode n on n.intNodeId=nr.BeginNodeId and (n.bolHidden=0 or @showHidden=1)
		where Level=@lev
			and not exists(select 1 from @ret rx where rx.id=nr.id)
		group by nr.id,nr.NodeRelationTypeId,nr.BeginNodeId,nrt.EndName,nr.EndNodeId,nrt.BeginName
		set @lev += 1
	end
	
		insert into @ret (id,NodeRelationTypeId,BeginNodeId,BeginNodeRelation,EndNodeId,EndNodeRelation,Straight,Level)
	select
		nr.id,
		nr.NodeRelationTypeId,
		nr.EndNodeId,
		nrt.BeginName,
		nr.BeginNodeId,
		nrt.EndName,
		1,
		@lev
	from NodeRelation nr
		join NodeRelationType nrt on nrt.id=nr.NodeRelationTypeId and nrt.Directional=1
		join tblNode nb on nb.intNodeId=nr.BeginNodeId and (nb.bolHidden=0 or @showHidden=1)
		join tblNode ne on ne.intNodeId=nr.EndNodeId and (ne.bolHidden=0 or @showHidden=1)
	where nr.EndNodeId=@nodeId

	while(exists(select 1 from @ret where Level=@lev))
	begin
		insert into @ret (id,NodeRelationTypeId,BeginNodeId,BeginNodeRelation,EndNodeId,EndNodeRelation,Straight,Level)
		select
			nr.id,
			nr.NodeRelationTypeId,
			nr.EndNodeId,
			nrt.BeginName,
			nr.BeginNodeId,
			nrt.EndName,
			0,
			@lev+1
		from @ret r
			join NodeRelationType nrt on nrt.id=r.NodeRelationTypeId
			join NodeRelation nr on r.NodeRelationTypeId=nr.NodeRelationTypeId and nr.EndNodeId=r.EndNodeId
			join tblNode n on n.intNodeId=nr.EndNodeId and (n.bolHidden=0 or @showHidden=1)
		where Level=@lev
			and not exists(select 1 from @ret rx where rx.id=nr.id)
		group by nr.id,nr.NodeRelationTypeId,nr.BeginNodeId,nrt.EndName,nr.EndNodeId,nrt.BeginName
		set @lev += 1
	end

		insert into @ret (id,NodeRelationTypeId,BeginNodeId,BeginNodeRelation,EndNodeId,EndNodeRelation,Straight,Level)
	select
		nr.id,
		nr.NodeRelationTypeId,
		nr.BeginNodeId,
		nrt.BeginName,
		nr.EndNodeId,
		nrt.EndName,
		1,
		@lev
	from NodeRelation nr
		join NodeRelationType nrt on nrt.id=nr.NodeRelationTypeId and nrt.Directional=0
		join tblNode nb on nb.intNodeId=nr.BeginNodeId and (nb.bolHidden=0 or @showHidden=1)
		join tblNode ne on ne.intNodeId=nr.EndNodeId and (ne.bolHidden=0 or @showHidden=1)
	where nr.BeginNodeId=@nodeId

	insert into @ret (id,NodeRelationTypeId,BeginNodeId,BeginNodeRelation,EndNodeId,EndNodeRelation,Straight,Level)
	select
		nr.id,
		nr.NodeRelationTypeId,
		nr.EndNodeId,
		nrt.BeginName,
		nr.BeginNodeId,
		nrt.EndName,
		1,
		@lev
	from NodeRelation nr
		join NodeRelationType nrt on nrt.id=nr.NodeRelationTypeId and nrt.Directional=0
		join tblNode nb on nb.intNodeId=nr.BeginNodeId and (nb.bolHidden=0 or @showHidden=1)
		join tblNode ne on ne.intNodeId=nr.EndNodeId and (ne.bolHidden=0 or @showHidden=1)
	where nr.EndNodeId=@nodeId

	
	while(exists(select 1 from @ret where Level=@lev))
	begin
		insert into @ret (id,NodeRelationTypeId,BeginNodeId,BeginNodeRelation,EndNodeId,EndNodeRelation,Straight,Level)
		select 
			nr.id,
			nr.NodeRelationTypeId,
			r.EndNodeId,
			nrt.BeginName,
			nr.BeginNodeId,
			nrt.EndName,
			0,
			@lev+1
		from @ret r
			join NodeRelation nr on r.NodeRelationTypeId=nr.NodeRelationTypeId and r.EndNodeId=nr.EndNodeId
			join NodeRelationType nrt on nrt.id=r.NodeRelationTypeId
			join tblNode ne on ne.intNodeId=nr.EndNodeId and (ne.bolHidden=0 or @showHidden=1)
		where r.Level=@lev
			and not exists(select 1 from @ret rx where rx.id=nr.id)
		group by nr.id,r.EndNodeId,nr.BeginNodeId,nr.EndNodeId,nrt.BeginName,nrt.EndName,nr.NodeRelationTypeId

				insert into @ret (id,NodeRelationTypeId,BeginNodeId,BeginNodeRelation,EndNodeId,EndNodeRelation,Straight,Level)
		select 
			nr.id,
			nr.NodeRelationTypeId,
			r.EndNodeId,
			nrt.BeginName,
			nr.EndNodeId,
			nrt.EndName,
			0,
			@lev+1
		from @ret r
			join NodeRelation nr on r.NodeRelationTypeId=nr.NodeRelationTypeId and r.EndNodeId=nr.BeginNodeId
			join NodeRelationType nrt on nrt.id=r.NodeRelationTypeId
			join tblNode nb on nb.intNodeId=nr.BeginNodeId and (nb.bolHidden=0 or @showHidden=1)
		where r.Level=@lev
			and not exists(select 1 from @ret rx where rx.id=nr.id)
		group by nr.id,r.EndNodeId,nr.BeginNodeId,nr.EndNodeId,nrt.BeginName,nrt.EndName,nr.NodeRelationTypeId

		set @lev += 1
	end

	update rel
	set rel.BeginNodeKind=db.txtText,
		rel.BeginNodeName=nb.txtName,
		rel.BeginNodePath=nb.txtPath,
		rel.EndNodeKind=de.txtText,
		rel.EndNodeName=ne.txtName,
		rel.EndNodePath=ne.txtPath,
		rel.ModifiedDate=nr.ModifiedDate,
		rel.ModifiedByPerson=p.sPerson
	from @ret rel
		join NodeRelation nr on nr.id=rel.id
		join tblNode nb on nb.intNodeId=rel.BeginNodeId
		join tblDict db on db.lintClassId=nb.lintClassId
		join tblNode ne on ne.intNodeId=rel.EndNodeId
		join tblDict de on de.lintClassId=ne.lintClassId
		join tPerson p on p.iPersonId=nr.ModifiedByPersonId

	return
end