ALTER procedure [Internal].[spObjectTree](
	@personId int,
	@isAmAdmin bit,
	@nodeRightOn bit,
	@haveAnyAmRole bit,
	@searchText nvarchar(255),
	@searchInNames bit,
	@searchInProperties bit,
	@objectId int,
	@showHidden bit,
	@parentNodeIds nvarchar(1024) = null,
	@treeRootName nvarchar(255)) 
as
begin
	if @haveAnyAmRole=0
	begin
		select top 0 0 id,0 pId,'' name,'' className,0 iconId,0 system,0 classId,0 requiredPropertyAlert,0 hideRequiredPropertyAlert, 0 isParent, 0 target
		return
	end
	
	create table #foundObjects (id int primary key)

	if @isAmAdmin=1 or @nodeRightOn=0
	begin
		insert into #foundObjects
		select top(200)
			Id
		from (
				select Id from Internal.ftSearchObjectByName(@searchText,@showHidden,@parentNodeIds) where @searchInNames=1
				union 
				select Id from Internal.ftSearchObjectByPropertyValue(@searchText,@personId,@showHidden,@isAmAdmin,@parentNodeIds) where @searchInProperties=1
			) search

		declare @canDrag bit = (select case when @isAmAdmin = 1 then 1
			when exists(SELECT 1 FROM tRolePerson
				INNER JOIN tRole on iRoleId = liRolePersonRoleId
				INNER JOIN tPerson on iPersonId=liRolePersonPersonId
				WHERE RoleBehaviorId = 11 AND liRolePersonPersonId = @personId) then 1
			else 0 end)

		select * 
		from (
			--roots
			select
				o.intNodeId id,
				o.lintParentId pId,
				o.txtName name,
				dc.txtText className,
				o.lintIconId icon,
				o.bolHidden system,
				o.lintClassId classId,
				o.RequiredPropertyAlert requiredPropertyAlert,
				case when o.bolHidden = 0 and nd.NodeId is null then 0 else 1 end hideRequiredPropertyAlert,
				case when (
					select top 1 1 
					from tblNode ch
					where ch.lintParentId=o.intNodeId
				) is null then 0 else 1 end isParent,
				0 target,
				@canDrag as drag
			from tblNode o
			join tblDict dc on dc.lintClassId=o.lintClassId
			left join #foundObjects fo on fo.id = o.intNodeId
			left join vNodeDisposed nd on nd.NodeId=o.intNodeId
			where fo.id is null and o.lintParentId is null
				and (o.bolHidden=0 or @showHidden=1) and (@parentNodeIds is null or o.intNodeId in (select * from ftCommaListToTableIds(@parentNodeIds)))
			
			union

			select distinct
				n2.intNodeId id,
				n2.lintParentId pId,
				n2.txtName name,
				dc.txtText className,
				n2.lintIconId icon,
				n2.bolHidden system,
				n2.lintClassId classId,
				n2.RequiredPropertyAlert requiredPropertyAlert,
				case when n2.bolHidden = 0 and nd.NodeId is null then 0 else 1 end hideRequiredPropertyAlert,
				case when (
					select top 1 1 
					from tblNode ch
					where ch.lintParentId=n2.intNodeId
				) is null then 0 else 1 end isParent,
				max(case when n2.intNodeId=search.intNodeId and search.IsFoundObject=1 then 1 else 0 end) target,
				@canDrag as drag
			from (
				select
					t.intNodeId,
					max(IsFoundObject) IsFoundObject
				from (
					--param
					select
						o.intNodeId,
						0 IsFoundObject
					from tblNode o
					where o.intNodeId=@objectId
						and (o.bolHidden=0 or @showHidden=1)

					union all

					--search
					select
						id,
						1 IsFoundObject
					from #foundObjects
				) t
				group by t.intNodeId
			) search
			join tblNodeParent np on search.intNodeId=np.lintNodeId
			join tblNode n on n.intNodeId=np.lintParentNodeId
			join tblNode n2 on case when n.intNodeId=search.intNodeId then n2.intNodeId else n2.lintParentId end=n.intNodeId
			join tblDict dc on dc.lintClassId=n2.lintClassId
			left join vNodeDisposed nd on nd.NodeId=n2.intNodeId
			group by n2.intNodeId, n2.lintParentId, n2.txtName, dc.txtText, n2.lintIconId, n2.bolHidden, n2.lintClassId, n2.RequiredPropertyAlert, nd.NodeId
		) t

		union all

		select
			0 id,
			null pId,
			@treeRootName name,
			null className,
			(SELECT intIconId FROM tblIcon WHERE tblIcon.[uid] = 195) icon,
			0 system,
			null classId,
			0 requiredPropertyAlert,
			0 hideRequiredPropertyAlert,
			0 isParent,
			0 target,
			@canDrag as drag
		where @treeRootName is not null
			and @searchText is null

		union all

		select
			0 id,
			null pId,
			@treeRootName name,
			null className,
			(SELECT intIconId FROM tblIcon WHERE tblIcon.[uid] = 195) icon,
			0 system,
			null classId,
			0 requiredPropertyAlert,
			0 hideRequiredPropertyAlert,
			0 isParent,
			1 target,
			@canDrag as drag
		where @treeRootName is not null
			and @searchInNames=1
			and @treeRootName like N'%'+replace(LOWER(@searchText),'''','''''')+'%' collate Latin1_General_CI_AI

		order by system, className, name
		OPTION(OPTIMIZE FOR UNKNOWN)
	end
	else begin
		insert into #foundObjects
		select top(200)
			Id
		from (
				select Id from Internal.ftSearchObjectByName_Rights(@searchText,@personId,@showHidden,@parentNodeIds) where @searchInNames=1
				union 
				select Id from Internal.ftSearchObjectByPropertyValue_Rights(@searchText,@personId,@showHidden,@parentNodeIds) where @searchInProperties=1
			) search			
			
		create table #readObjects (id int primary key, drag bit not null)
		
		insert into #readObjects
		select r.liNodeId,
			case when m.liNodeId is null then 0 else 1 end as drag
		from vPersonNodeRight_Read r 
		left join vPersonNodeRight_Move m on m.liPersonId=@personId and m.liNodeId=r.liNodeId
		WHERE r.liPersonId = @personId

		select distinct
			n2.intNodeId id,
			n2.lintParentId pId,
			n2.txtName name,
			dc.txtText className,
			n2.lintIconId icon,
			n2.bolHidden system,
			n2.lintClassId classId,
			n2.RequiredPropertyAlert requiredPropertyAlert,
			case when n2.bolHidden = 0 and nd.NodeId is null then 0 else 1 end hideRequiredPropertyAlert,
			case when (
				select top 1 1 
				from tblNode ch
					join #readObjects r ON r.id=ch.intNodeId
				where ch.lintParentId=n2.intNodeId
			) is null then 0 else 1 end isParent,
			max(case when n2.intNodeId=search.intNodeId and search.IsFoundObject=1 then 1 else 0 end) target,
			r.drag as drag
		from (
				select
					t.intNodeId,
					max(IsFoundObject) IsFoundObject
				from (
					--root
					select
						o.intNodeId,
						0 IsFoundObject
					from tblNode o
					where o.lintParentId is null
						and (o.bolHidden=0 or @showHidden=1)
			
					union all

					--param
					select
						o.intNodeId,
						0 IsFoundObject
					from tblNode o
					where o.intNodeId=@objectId
						and (o.bolHidden=0 or @showHidden=1)

					union all

					--search
					select
						id,
						1 IsFoundObject
					from #foundObjects
				) t
				group by t.intNodeId
			) search
			join tblNodeParent np on search.intNodeId=np.lintNodeId
			join tblNode n on n.intNodeId=np.lintParentNodeId
			join tblNode n2 on case when n.intNodeId=search.intNodeId then n2.intNodeId else n2.lintParentId end=n.intNodeId
			join tblDict dc on dc.lintClassId=n2.lintClassId
			join #readObjects r ON r.id=n2.intNodeId
			left join vNodeDisposed nd on nd.NodeId=n2.intNodeId
		group by n2.intNodeId, n2.lintParentId, n2.txtName, dc.txtText, n2.lintIconId, n2.bolHidden, n2.lintClassId, n2.RequiredPropertyAlert, nd.NodeId, r.drag
		order by n2.bolHidden, dc.txtText, n2.txtName
		
		drop table #readObjects
	end
	
	drop table #foundObjects
end