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();
}
