
ALTER procedure [dbo].[spHdSectionForRequesterSearch] (@sample NVARCHAR(300), @count int, @personId int, @requesterId int) as
begin
IF OBJECT_ID ('dbo.spHdSectionForRequesterSearch_Custom') IS NOT NULL
BEGIN
	exec spHdSectionForRequesterSearch_Custom @sample, @count, @personId, @requesterId
END
ELSE
	SET NOCOUNT ON
	DECLARE @ftsEnabled BIT = 1
	DECLARE @ftsTestQuery NVARCHAR(120) = 
	N'IF EXISTS(SELECT iHdTicketId FROM tHdTicket WHERE  CONTAINS(sHdTicketFullName, N''_?_''))
		BEGIN
			no_op:
		END'
	BEGIN TRY
		EXECUTE sp_executesql @ftsTestQuery
	END TRY
	BEGIN CATCH
		SET @ftsEnabled = 0
	END CATCH
	DECLARE @LocaleId INT = (SELECT
								CASE WHEN (iPersonLocaleId IS NOT NULL)
										THEN iPersonLocaleId 
										ELSE (SELECT iDbLocaleId FROM tDb) 
								END 
							FROM 
								tPerson 
							WHERE 
								iPersonId = @personId)
	IF @sample IS NULL
	BEGIN
		DECLARE @usageRowsCnt INT = (
			SELECT COUNT(DISTINCT S.iHdSectionId) AS SectionCount
			FROM vSlaAccess SA
			INNER JOIN OperatorServiceUsage OSU ON OSU.HdSectionId = SA.liSlaAccessHdSectionId AND OSU.PersonId = @personId
			INNER JOIN tHdSection S ON S.iHdSectionId = OSU.HdSectionId
			WHERE liSlaAccessPersonId = @requesterId AND S.dHdSectionRemoved IS NULL)

		IF @usageRowsCnt > 0
		BEGIN
			SELECT
				sections.*,
				ItemOrder = ROW_NUMBER() OVER (ORDER BY sections.LastUsed DESC)
			FROM
			(
				SELECT DISTINCT TOP (@count) 
					S.*,
					OSU.LastUsed
				FROM
					vSlaAccess SA
					INNER JOIN OperatorServiceUsage OSU ON OSU.HdSectionId = SA.liSlaAccessHdSectionId AND OSU.PersonId = @personId
					INNER JOIN vHdSectionLoc S ON (S.iHdSectionId = SA.liSlaAccessHdSectionId OR S.TargetHdSectionId = SA.liSlaAccessHdSectionId) AND S.iHdSectionLocaleId = @LocaleId
				WHERE					
					liSlaAccessPersonId = @requesterId AND
					S.dHdSectionRemoved IS NULL AND 
					(S.bHdSectionFolder = 0	OR S.TargetHdSectionId IS NOT NULL)
			) sections
		END
		ELSE
		BEGIN
			SELECT
				sections.*,
				ItemOrder = ROW_NUMBER() over (order by sections.LocalizedSection)
			FROM
			(
				SELECT DISTINCT TOP (@count) 
					S.*
				FROM
					vSlaAccess SA
					INNER JOIN vHdSectionLoc S ON (S.iHdSectionId = SA.liSlaAccessHdSectionId OR S.TargetHdSectionId = SA.liSlaAccessHdSectionId) AND S.iHdSectionLocaleId = @LocaleId
					LEFT JOIN vHdSectionOperator SO ON SO.HdSectionId = SA.liSlaAccessHdSectionId AND SO.RequesterRoleId IN (select liRolePersonRoleId from tRolePerson where liRolePersonPersonId = @requesterId )
					LEFT JOIN HdSectionSolver SS ON SS.HdSectionId = SA.liSlaAccessHdSectionId AND SS.RequesterRoleId IN (select liRolePersonRoleId from tRolePerson where liRolePersonPersonId = @requesterId )
					LEFT JOIN HdSectionManager SM ON SM.HdSectionId = SA.liSlaAccessHdSectionId AND SM.RequesterRoleId IN (select liRolePersonRoleId from tRolePerson where liRolePersonPersonId = @requesterId )
				WHERE					
					liSlaAccessPersonId = @requesterId AND
					(SO.PersonId = @personId OR SS.PersonId = @personId OR SM.PersonId = @personId) AND
					S.dHdSectionRemoved IS NULL AND 
					(S.bHdSectionFolder = 0	OR S.TargetHdSectionId IS NOT NULL)
			) sections
		END
	END
	ELSE
	BEGIN
		IF @ftsEnabled = 1 AND @sample != ' '
		BEGIN
			DECLARE @SQLString nvarchar(4000)
			DECLARE @ParmDefinition nvarchar(500)
			DECLARE @cond NVARCHAR(3500) =  ''
			select @cond = @cond +  ' AND (FORMSOF(INFLECTIONAL, "' + word + '" ) OR "' + word + '*")'
				from tfSplitString(@sample, ' ')
			select @cond = substring(@cond, 6, 3500)
			
			SET @sample = REPLACE(@sample, ' ', '%')
			SET @sample = LOWER(N'%'+@sample+N'%')
			SET @SQLString =
			N'
				SELECT
					sections.*,
					ItemOrder = ROW_NUMBER() over (order by sections.LocalizedSection)
				FROM
				(
					SELECT DISTINCT TOP (@count) 
						S.*
					FROM
						vSlaAccess SA
						INNER JOIN vHdSectionLoc S ON (S.iHdSectionId = SA.liSlaAccessHdSectionId OR S.TargetHdSectionId = SA.liSlaAccessHdSectionId) AND S.iHdSectionLocaleId = @LocaleId
						INNER JOIN HdSectionLoc SL ON SL.HdSectionId = S.iHdSectionId AND SL.LocaleId=S.iHdSectionLocaleId
					WHERE					
						liSlaAccessPersonId = @requesterId AND
						S.dHdSectionRemoved IS NULL AND 
						(S.bHdSectionFolder = 0 OR S.TargetHdSectionId IS NOT NULL) AND
						(LOWER(S.LocalizedSection) LIKE @sample COLLATE Latin1_General_CI_AI OR
						CONTAINS(SL.HdSectionKeywords, @cond))
				) sections'
				SET @ParmDefinition = N'@count int, @LocaleId int, @requesterId int, @sample NVARCHAR(100), @cond nvarchar(3500)'
				EXECUTE sp_executesql @SQLString, @ParmDefinition,
							  @count = @count,
							  @LocaleId = @LocaleId,
							  @requesterId = @requesterId,
							  @cond = @cond,
							  @sample = @sample
		END
		ELSE
		BEGIN

			SET @sample = REPLACE(@sample, ' ', '%')
			SET @sample = LOWER(N'%'+@sample+N'%')

			SELECT
				sections.*,
				ItemOrder = ROW_NUMBER() over (order by sections.LocalizedSection)
			FROM
			(
				SELECT DISTINCT TOP (@count) 
					S.*
				FROM
					vSlaAccess SA
					INNER JOIN vHdSectionLoc S ON (S.iHdSectionId = SA.liSlaAccessHdSectionId OR S.TargetHdSectionId = SA.liSlaAccessHdSectionId) AND S.iHdSectionLocaleId = @LocaleId
				WHERE					
					liSlaAccessPersonId = @requesterId AND
					S.dHdSectionRemoved IS NULL AND 
					(S.bHdSectionFolder = 0 OR S.TargetHdSectionId IS NOT NULL) AND
					LOWER(S.LocalizedSection) LIKE @sample COLLATE Latin1_General_CI_AI OR
					LOWER(S.LocalizedKeywords) LIKE @sample COLLATE Latin1_General_CI_AI
			) sections
		END
	END
 RETURN
END