private IbnObjectInfo[] List(string searchTerms) { List<IbnObjectInfo> retVal = new List<IbnObjectInfo>(); string sqlQuery; #region Init Sql Query sqlQuery = @"SET NOCOUNT ON" + Environment.NewLine + @"-- 57041362-9022-4A30-A73C-91EFBE11F7E0" + Environment.NewLine + @"-- SELECT * FROM USERS" + Environment.NewLine + Environment.NewLine + @"DECLARE @UserId NVARCHAR(255)" + Environment.NewLine + @"SET @UserId = 21" + Environment.NewLine + Environment.NewLine + @"--SELECT * FROM Users" + Environment.NewLine + Environment.NewLine + //@"DECLARE @SearchTerms NVARCHAR(255)" + Environment.NewLine + //@"SET @SearchTerms = N'test %'" + Environment.NewLine + Environment.NewLine + @"-- Project, Task, Todo, Event Security" + Environment.NewLine + @"DECLARE @IsPPM_Exec bit" + Environment.NewLine + @"SET @IsPPM_Exec = 0" + Environment.NewLine + @"IF EXISTS(SELECT * FROM USER_GROUP WHERE UserId = @UserId AND (GroupId = 4 OR GroupId = 7)) -- PPM " + @"or Exec" + Environment.NewLine + @" SET @IsPPM_Exec = 1" + Environment.NewLine + @"-- End Project, Task, Todo, Event Security" + Environment.NewLine + Environment.NewLine + @"-- Incident Security" + Environment.NewLine + @"DECLARE @IsPPM bit" + Environment.NewLine + @"SET @IsPPM = 0" + Environment.NewLine + @"IF EXISTS(SELECT * FROM USER_GROUP WHERE UserId = @UserId AND GroupId = 4)" + Environment.NewLine + @" SET @IsPPM = 1" + Environment.NewLine + @"DECLARE @IsExec bit" + Environment.NewLine + @"SET @IsExec = 0" + Environment.NewLine + @"IF EXISTS(SELECT * FROM USER_GROUP WHERE UserId = @UserId AND GroupId = 7)" + Environment.NewLine + @" SET @IsExec = 1" + Environment.NewLine + @"DECLARE @IsHDM bit" + Environment.NewLine + @"SET @IsHDM = 0" + Environment.NewLine + @"IF EXISTS(SELECT * FROM USER_GROUP WHERE UserId = @UserId AND GroupId = 5)" + Environment.NewLine + @" SET @IsHDM = 1" + Environment.NewLine + @"-- End Incident Security" + Environment.NewLine + Environment.NewLine + @"SELECT TOP 15 A.ObjectId, A.ObjectType, A.Title " + Environment.NewLine + @"FROM" + Environment.NewLine + @"(" + Environment.NewLine + @"-- Project" + Environment.NewLine + @"SELECT TOP 15 P.ProjectId as ObjectId, 2 As ObjectType, P.Title As Title " + Environment.NewLine + @"FROM PROJECTS P WITH(NOLOCK)" + Environment.NewLine + @" -- Project Security" + Environment.NewLine + @" LEFT JOIN PROJECT_SECURITY_ALL PS ON (P.ProjectId = PS.ProjectId AND PS.PrincipalId = @UserId)" + Environment.NewLine + @" -- End Project Security" + Environment.NewLine + @"WHERE Title LIKE @SearchTerms AND" + Environment.NewLine + @" -- Project Security" + Environment.NewLine + @" (" + Environment.NewLine + @" @IsPPM_Exec = 1 OR " + Environment.NewLine + @" PS.IsManager = 1 OR " + Environment.NewLine + @" PS.IsExecutiveManager = 1 OR " + Environment.NewLine + @" PS.IsTeamMember = 1 OR " + Environment.NewLine + @" PS.IsSponsor = 1 OR " + Environment.NewLine + @" PS.IsStakeHolder = 1" + Environment.NewLine + @" )" + Environment.NewLine + @" -- End Project Security" + Environment.NewLine + @"ORDER BY LEN(Title), Title" + Environment.NewLine + @"UNION ALL" + Environment.NewLine + @"-- Task" + Environment.NewLine + @"SELECT TOP 15 T.TaskId as ObjectId, 5 As ObjectType, T.Title As Title " + Environment.NewLine + @"FROM TASKS T WITH(NOLOCK)" + Environment.NewLine + @"WHERE Title LIKE @SearchTerms AND" + Environment.NewLine + @" -- Task Security" + Environment.NewLine + @" (" + Environment.NewLine + @" @IsPPM_Exec = 1" + Environment.NewLine + @" OR T.ProjectId IN" + Environment.NewLine + @" (SELECT ProjectId FROM PROJECT_SECURITY " + Environment.NewLine + @" WHERE PrincipalId = @UserId" + Environment.NewLine + @" AND (IsManager = 1 OR IsExecutiveManager = 1 OR IsTeamMember = 1 OR IsSponsor = 1 OR " + @"IsStakeHolder = 1)" + Environment.NewLine + @" )" + Environment.NewLine + @" OR TaskId IN " + Environment.NewLine + @" (SELECT TaskId FROM TASK_SECURITY S " + Environment.NewLine + @" WHERE PrincipalId = @UserId AND (IsResource = 1 OR IsManager = 1))" + Environment.NewLine + @" )" + Environment.NewLine + @" -- End Task Security" + Environment.NewLine + @"ORDER BY LEN(Title), Title" + Environment.NewLine + @"UNION ALL" + Environment.NewLine + @"-- ToDo" + Environment.NewLine + @"SELECT TOP 15 T.TodoId as ObjectId, 6 As ObjectType, T.Title As Title " + Environment.NewLine + @"FROM TODO T WITH(NOLOCK)" + Environment.NewLine + @"WHERE Title LIKE @SearchTerms AND" + Environment.NewLine + @"-- Todo Security" + Environment.NewLine + @" (" + Environment.NewLine + @" @IsPPM_Exec = 1" + Environment.NewLine + @" OR T.ProjectId IN" + Environment.NewLine + @" (SELECT ProjectId FROM PROJECT_SECURITY " + Environment.NewLine + @" WHERE PrincipalId = @UserId" + Environment.NewLine + @" AND (IsManager = 1 OR IsExecutiveManager = 1 OR IsTeamMember = 1 OR IsSponsor = 1 OR " + @"IsStakeHolder = 1)" + Environment.NewLine + @" )" + Environment.NewLine + @" OR ToDoId IN " + Environment.NewLine + @" (SELECT ToDoId FROM TODO_SECURITY_ALL S " + Environment.NewLine + @" WHERE PrincipalId = @UserId AND (IsResource = 1 OR IsManager = 1))" + Environment.NewLine + @" )" + Environment.NewLine + @"-- Todo Security" + Environment.NewLine + @"ORDER BY LEN(Title), Title" + Environment.NewLine + @"UNION ALL" + Environment.NewLine + @"-- Incident" + Environment.NewLine + @"SELECT TOP 15 I.IncidentId as ObjectId, 7 As ObjectType, I.Title As Title " + Environment.NewLine + @"FROM INCIDENTS I WITH(NOLOCK)" + Environment.NewLine + @"WHERE Title LIKE @SearchTerms AND" + Environment.NewLine + @" ( " + Environment.NewLine + @" @IsPPM = 1 OR " + Environment.NewLine + @" @IsExec = 1" + Environment.NewLine + @" OR (I.ProjectId IS NULL AND @IsHDM = 1)" + Environment.NewLine + @" OR I.ProjectId IN" + Environment.NewLine + @" (SELECT ProjectId FROM PROJECT_SECURITY" + Environment.NewLine + @" WHERE PrincipalId = @UserId" + Environment.NewLine + @" AND (IsManager = 1 OR IsExecutiveManager = 1 OR IsTeamMember = 1 OR IsSponsor = 1 OR " + @"IsStakeHolder = 1)" + Environment.NewLine + @" )" + Environment.NewLine + @" OR I.IncidentId IN" + Environment.NewLine + @" (SELECT IncidentId FROM INCIDENT_SECURITY_ALL" + Environment.NewLine + @" WHERE PrincipalId = @UserId AND (IsManager = 1 OR IsCreator = 1 OR IsResource = 1)" + Environment.NewLine + @" )" + Environment.NewLine + @" )" + Environment.NewLine + @"ORDER BY LEN(Title), Title" + Environment.NewLine + @"UNION ALL" + Environment.NewLine + @"-- Event" + Environment.NewLine + @"SELECT TOP 15 E.eventId as ObjectId, 4 As ObjectType, E.Title As Title " + Environment.NewLine + @"FROM EVENTS E WITH(NOLOCK)" + Environment.NewLine + @"WHERE Title LIKE @SearchTerms AND" + Environment.NewLine + @"-- Event Security" + Environment.NewLine + @" (" + Environment.NewLine + @" @IsPPM_Exec = 1" + Environment.NewLine + @" OR E.ProjectId IN" + Environment.NewLine + @" (SELECT ProjectId FROM PROJECT_SECURITY " + Environment.NewLine + @" WHERE PrincipalId = @UserId" + Environment.NewLine + @" AND (IsManager = 1 OR IsExecutiveManager = 1 OR IsTeamMember = 1 OR IsSponsor = 1 OR " + @"IsStakeHolder = 1)" + Environment.NewLine + @" )" + Environment.NewLine + @" OR EventId IN " + Environment.NewLine + @" (SELECT EventId FROM EVENT_SECURITY_ALL S " + Environment.NewLine + @" WHERE PrincipalId = @UserId AND (IsResource = 1 OR IsManager = 1))" + Environment.NewLine + @" )" + Environment.NewLine + @"-- End Event Security" + Environment.NewLine + @"ORDER BY LEN(Title), Title" + Environment.NewLine + @"UNION ALL" + Environment.NewLine + @"-- Files" + Environment.NewLine + @"SELECT TOP 15 F.FileId as ObjectId, 8 As ObjectType, F.Name As Title " + Environment.NewLine + @"FROM fsc_Files F WITH(NOLOCK)" + Environment.NewLine + @"-- Security" + Environment.NewLine + @" INNER JOIN fsc_Directories D WITH(NOLOCK) ON F.DirectoryId = D.DirectoryId" + Environment.NewLine + @" INNER JOIN fsc_FolderSecurityAll FSA ON " + Environment.NewLine + @" FSA.DirectoryId = F.DirectoryId AND " + Environment.NewLine + @" FSA.ContainerKey = D.ContainerKey AND " + Environment.NewLine + @" FSA.[Action] = N'Read' AND " + Environment.NewLine + @" FSA.Allow = 1 AND" + Environment.NewLine + @" FSA.PrincipalId = @UserId" + Environment.NewLine + @"WHERE F.Name LIKE @SearchTerms " + Environment.NewLine + @"ORDER BY LEN(F.Name), F.Name" + Environment.NewLine + @"UNION ALL" + Environment.NewLine + @"-- Documents" + Environment.NewLine + @"SELECT TOP 15 D.DocumentId as ObjectId, 16 As ObjectType, D.Title As Title " + Environment.NewLine + @"FROM DOCUMENTS D WITH(NOLOCK)" + Environment.NewLine + @"WHERE Title LIKE @SearchTerms AND" + Environment.NewLine + @"-- Document Security" + Environment.NewLine + @" (" + Environment.NewLine + @" @IsPPM = 1 OR @IsExec = 1" + Environment.NewLine + @" OR D.ProjectId IN" + Environment.NewLine + @" (SELECT ProjectId FROM PROJECT_SECURITY " + Environment.NewLine + @" WHERE PrincipalId = @UserId" + Environment.NewLine + @" AND (IsManager = 1 OR IsExecutiveManager = 1 OR IsTeamMember = 1 OR IsSponsor = 1 OR " + @"IsStakeHolder = 1)" + Environment.NewLine + @" )" + Environment.NewLine + @" OR D.DocumentId IN " + Environment.NewLine + @" (SELECT DocumentId FROM DOCUMENT_SECURITY_ALL" + Environment.NewLine + @" WHERE PrincipalId = @UserId AND (IsManager = 1 OR IsResource = 1)" + Environment.NewLine + @" )" + Environment.NewLine + @" )" + Environment.NewLine + @"-- End Document Security" + Environment.NewLine + @"ORDER BY LEN(Title), Title" + Environment.NewLine + @"-- TODO: Organizations" + Environment.NewLine + @"-- TODO: Contacts" + Environment.NewLine + @"-- TODO: Users" + Environment.NewLine + @"-- TODO: Group" + Environment.NewLine + @") A" + Environment.NewLine + @"ORDER BY LEN(A.Title), A.Title" + Environment.NewLine + Environment.NewLine + @""; #endregion using (IDataReader reader = SqlHelper.ExecuteReader(DataContext.Current.SqlContext, System.Data.CommandType.Text, sqlQuery, SqlHelper.SqlParameter("@SearchTerms", SqlDbType.NVarChar, 255, searchTerms + "%"))) { while (reader.Read()) { IbnObjectInfo newItem = new IbnObjectInfo((int)reader["ObjectId"], (int)reader["ObjectType"], (string)reader["Title"]); retVal.Add(newItem); } } return retVal.ToArray(); }