
ALTER FUNCTION [dbo].[ftDeviceSearch] (@sample NVARCHAR(300), @nodeId int, @count int, @personId int, @requesterId int) 
RETURNS @table TABLE
(
 DeviceId int,
 DeviceInfo nvarchar(300),
 DeviceIconId int,
 DevicePath nvarchar(1024),
 ItemOrder int
)
AS
BEGIN
	IF OBJECT_ID ('dbo.ftDeviceSearch_Custom2') IS NOT NULL
	BEGIN
		INSERT INTO @table 
		SELECT * FROM dbo.ftDeviceSearch_Custom2(@sample,@nodeId,@count,@personId,@requesterId)
	END
	ELSE
		BEGIN
			if @requesterId < 1 OR @requesterId IS NULL
				set @requesterId = @personId

			declare @requesterLogin nvarchar(max) = LOWER((select sPersonLogin from tPerson where iPersonId = @requesterId))
			declare @requesterNodeId int = 
				(select top 1 TN.intNodeId from
					(select TN.intNodeId, 1 as [Priority], 1 as [Cnt]	
						from tblNode as TN 
							left join tblProperty as TP on TP.lintNodeId=TN.intNodeId and TP.ParentNodeId is null
							left join tblKind as TK on TK.intKindId=TP.lintKindId 
						where TK.intKindCode=54 
							and TN.bolHidden=0
							and LOWER(TP.txtValue)=@requesterLogin
							and TN.lintClassId=7
						union
						select TN.intNodeId, 2 as [Priority], COUNT(*) over () as [Cnt]
						from tblNode as TN 
							left join tblProperty as TP on TP.lintNodeId=TN.intNodeId and TP.ParentNodeId is null
							left join tblKind as TK on TK.intKindId=TP.lintKindId 
						where TK.intKindCode=54 
							and TN.bolHidden=0
							and LOWER(dbo.fLastLevelDomain(TP.txtValue))=dbo.fLastLevelDomain(@requesterLogin)
							and TN.lintClassId=7
						union
						select TN.intNodeId, 3 as [Priority], COUNT(*) over () as [Cnt]
						from tblNode as TN 
							left join tblProperty as TP on TP.lintNodeId=TN.intNodeId and TP.ParentNodeId is null
							left join tblKind as TK on TK.intKindId=TP.lintKindId 
						where TK.intKindCode=54 
							and TN.bolHidden=0
							and LOWER(dbo.fStripDomain(TP.txtValue))=dbo.fStripDomain(@requesterLogin)
							and TN.lintClassId=7
					) TN
				where TN.Cnt = 1
				order by TN.Priority asc)
		
			declare @rightsOn bit = 
				(SELECT CASE WHEN bolNode_Rights = 1 
						THEN CASE WHEN EXISTS(SELECT RP.liRolePersonPersonId FROM tRolePerson AS RP LEFT JOIN tRole AS R ON RP.liRolePersonRoleId=R.iRoleId WHERE RP.liRolePersonPersonId=@personId AND R.RoleBehaviorId = 8)
								THEN 0 ELSE 1 END
						ELSE 0 END 
				FROM tblSystemSetting)

			insert into @table 
			select top (@count) nodes.intNodeId, nodes.sDeviceInfo, nodes.lintIconId, nodes.DevicePath,
				ItemOrder = ROW_NUMBER() over (order by case when (nodes.intNodeId = @nodeId) then 1 else 0 end desc, nodes.DevicePath, nodes.txtClass, txtName) 
			from
			(
			select ch.intNodeId, c.txtClass, ch.txtName,
				left(c.txtClass + ': ' + ISNULL(ch.txtName,'')  
				+ (case when tblPropertyIn.txtValue is null then '' else '; ' + tblPropertyIn.txtValue end)  
				+ (case when tblPropertyEv.txtValue is null then '' else '; ' + tblPropertyEv.txtValue end)   
				+ (case when tblPropertySe.txtValue is null then '' else '; ' + tblPropertySe.txtValue end),300)  as sDeviceInfo,
				ch.lintIconId,
				Left(ch.txtPath,1024) DevicePath
			from tblNode u 
			inner join tblNodeParent np on np.lintParentNodeId = u.intNodeId and np.lintParentNodeId != np.lintNodeId
			inner join tblNode ch on np.lintNodeId = ch.intNodeId
			left join vClass c on ch.lintClassId = c.intClassId 
			left join (select * from tblProperty where lintKindId = (select TOP 1 intKindId from tblKind where intKindCode = 11)) tblPropertyIn  on tblPropertyIn.lintNodeId=ch.intNodeId 
			left join (select * from tblProperty where lintKindId = (select TOP 1 intKindId from tblKind where intKindCode = 12)) tblPropertySe  on tblPropertySe.lintNodeId=ch.intNodeId 
			left join (select * from tblProperty where lintKindId = (select TOP 1 intKindId from tblKind where intKindCode = 102)) tblPropertyEv  on tblPropertyEv.lintNodeId=ch.intNodeId
			where (u.intNodeId = @requesterNodeId or @requesterId is null) and ch.lintClassId != 6 
				and not exists( select 1 from tblNode tn join tblClass tc on tn.lintClassId = tc.intClassId and tc.bComputer=1 where tn.intNodeId = ch.lintParentId)
				and ch.bolHidden = 0         

			union
	
			select
				nc.intNodeId, nc.txtClass, nc.txtName,
				left(nc.txtClass + ': ' + ISNULL(nc.txtName,'')  
				+ (case when A.InventoryNumber is null then '' else '; ' + A.InventoryNumber end)  
				+ (case when A.EvidentiaryNumber is null then '' else '; ' + A.EvidentiaryNumber end)   
				+ (case when A.SerialNumber is null then '' else '; ' + A.SerialNumber end),300)  as sDeviceInfo,
				nc.lintIconId,
				Left(nc.txtPath,1024) DevicePath
			from vNodeClass nc
			inner join vPropertyKind_Asset A on nc.intNodeId = A.intNodeId
			where nc.intNodeId in (select lintNodeId from tblProperty where NodeIdValue = @requesterNodeId) and nc.lintClassId != 6 
				and not exists( select 1 from tblNode tn join tblClass tc on tn.lintClassId = tc.intClassId and tc.bComputer=1 where tn.intNodeId = nc.lintParentId)
				and nc.bolHidden = 0 

			union

			select n.intNodeId, n.txtClass, n.txtName,
				left(n.txtClass + ': ' + ISNULL(n.txtName,'')  
				+ (case when tblPropertyIn.txtValue is null then '' else '; ' + tblPropertyIn.txtValue end)  
				+ (case when tblPropertyEv.txtValue is null then '' else '; ' + tblPropertyEv.txtValue end)   
				+ (case when tblPropertySe.txtValue is null then '' else '; ' + tblPropertySe.txtValue end),300)  as sDeviceInfo,
				n.lintIconId,
				Left(txtPath,1024) DevicePath
			from vNodeClass n
			left join (select * from tblProperty where lintKindId = (select TOP 1 intKindId from tblKind where intKindCode = 11)) tblPropertyIn  on tblPropertyIn.lintNodeId=n.intNodeId 
			left join (select * from tblProperty where lintKindId = (select TOP 1 intKindId from tblKind where intKindCode = 12)) tblPropertySe  on tblPropertySe.lintNodeId=n.intNodeId 
			left join (select * from tblProperty where lintKindId = (select TOP 1 intKindId from tblKind where intKindCode = 102)) tblPropertyEv  on tblPropertyEv.lintNodeId=n.intNodeId
			where n.intNodeId = @nodeId or
				(@sample <> '' and @sample is not null
				and n.lintClassId != 6 and n.bolHidden = 0 
				and not exists( select 1 from tblNode tn join tblClass tc on tn.lintClassId = tc.intClassId and tc.bComputer=1 where tn.intNodeId = n.lintParentId)
				and 0 < (SELECT COUNT(*) AS Cnt FROM tRolePerson AS RP LEFT JOIN tRole AS R ON RP.liRolePersonRoleId=R.iRoleId WHERE RP.liRolePersonPersonId=@personId AND R.RoleBehaviorId IN (8,9,10,11,12,13,14,15))
				and (@rightsOn <> 1 or n.intNodeId in (select rr.liNodeId from vPersonNodeRight_Read rr where rr.liNodeId=intNodeId and rr.liPersonId=@personId)))
			) nodes
			where LOWER(nodes.sDeviceInfo) LIKE  '%' + @sample +  '%' COLLATE Latin1_General_CI_AI
				or LOWER(nodes.DevicePath) LIKE  '%' + @sample +  '%' COLLATE Latin1_General_CI_AI
				or @sample is null or @sample = ''
			order by case when (nodes.intNodeId = @nodeId) then 1 else 0 end DESC, nodes.DevicePath, nodes.txtClass, txtName
		END
	RETURN
END;