public virtual object GetValue(string key) { switch (key) { case "CanRead": if (DataHelper.UseSpOptimization) { string currentUserAllGroup = Security.GetPrincipalGroups(Security.CurrentUserId); SelectCommandBuilderParameter value = new SelectCommandBuilderParameter(); // Declare SP Prefix @Table // Declare SP Parameter value.Parameters.Add(new SqlParameter("@PrincipalGroups", currentUserAllGroup)); value.Prefix = "DECLARE @PrincipalGroupsTable TABLE (PrincipalId int)" + "\r\n" + "INSERT INTO @PrincipalGroupsTable (PrincipalId) SELECT Item FROM Split(@PrincipalGroups)\r\n\r\n"; StringBuilder sbFilterValue = new StringBuilder(); sbFilterValue.AppendFormat(@"SELECT [ttb].TimeTrackingBlockId{0}", Environment.NewLine); sbFilterValue.AppendFormat(@"FROM [cls_TimeTrackingBlock] AS [ttb]{0}", Environment.NewLine); sbFilterValue.AppendFormat(@"WHERE ({0}", Environment.NewLine); sbFilterValue.AppendFormat(@" ttb.TimeTrackingBlockId {0}", Environment.NewLine); sbFilterValue.AppendFormat(@" IN{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" ({0}", Environment.NewLine); sbFilterValue.AppendFormat(@" SELECT ObjectId FROM cls_TimeTrackingBlock_ReadACL{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" WHERE PrincipalId IN ({1}) AND [Read] = 2{0}", Environment.NewLine, "SELECT PrincipalId FROM @PrincipalGroupsTable"); sbFilterValue.AppendFormat(@" ){0}", Environment.NewLine); sbFilterValue.AppendFormat(@" OR{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" EXISTS {0}", Environment.NewLine); sbFilterValue.AppendFormat(@" ({0}", Environment.NewLine); sbFilterValue.AppendFormat(@" SELECT * {0}", Environment.NewLine); sbFilterValue.AppendFormat(@" FROM cls_TimeTrackingBlock_GlobalACL G{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" LEFT JOIN cls_TimeTrackingBlock_GlobalACL_State S ON "); sbFilterValue.AppendFormat(@"(G.TimeTrackingBlock_GlobalACLId = S.GlobalACLId AND S.StateMachineId = [ttb].mc_StateMachineId AND "); sbFilterValue.AppendFormat(@"S.StateId = [ttb].mc_StateId){0}", Environment.NewLine); sbFilterValue.AppendFormat(@" WHERE ({0}", Environment.NewLine); sbFilterValue.AppendFormat(@" (S.[Read] IS NULL AND G.[Read] = 2){0}", Environment.NewLine); sbFilterValue.AppendFormat(@" OR {0}", Environment.NewLine); sbFilterValue.AppendFormat(@" S.[Read] = 2{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" ){0}", Environment.NewLine); sbFilterValue.AppendFormat(@" AND PrincipalId IN ({1}){0}", Environment.NewLine, "SELECT PrincipalId FROM @PrincipalGroupsTable"); sbFilterValue.AppendFormat(@" ){0}", Environment.NewLine); sbFilterValue.AppendFormat(@" ){0}", Environment.NewLine); sbFilterValue.AppendFormat(@" AND ttb.TimeTrackingBlockId NOT IN{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" ({0}", Environment.NewLine); sbFilterValue.AppendFormat(@" SELECT ObjectId FROM cls_TimeTrackingBlock_ReadACL{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" WHERE {0}", Environment.NewLine); sbFilterValue.AppendFormat(@" PrincipalId IN ({1}) AND [Read] = 3{0}", Environment.NewLine, "SELECT PrincipalId FROM @PrincipalGroupsTable"); sbFilterValue.AppendFormat(@" ){0}", Environment.NewLine); sbFilterValue.AppendFormat(@" AND NOT EXISTS {0}", Environment.NewLine); sbFilterValue.AppendFormat(@" ({0}", Environment.NewLine); sbFilterValue.AppendFormat(@" SELECT *{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" FROM cls_TimeTrackingBlock_GlobalACL G{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" LEFT JOIN cls_TimeTrackingBlock_GlobalACL_State S ON "); sbFilterValue.AppendFormat(@"(G.TimeTrackingBlock_GlobalACLId = S.GlobalACLId AND S.StateMachineId = [ttb].mc_StateMachineId AND "); sbFilterValue.AppendFormat(@"S.StateId = [ttb].mc_StateId){0}", Environment.NewLine); sbFilterValue.AppendFormat(@" WHERE ({0}", Environment.NewLine); sbFilterValue.AppendFormat(@" (S.[Read] IS NULL AND G.[Read] = 3){0}", Environment.NewLine); sbFilterValue.AppendFormat(@" OR {0}", Environment.NewLine); sbFilterValue.AppendFormat(@" S.[Read] = 3{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" ) {0}", Environment.NewLine); sbFilterValue.AppendFormat(@" AND PrincipalId IN ({1}){0}", Environment.NewLine, "SELECT PrincipalId FROM @PrincipalGroupsTable"); sbFilterValue.AppendFormat(@" ){0}", Environment.NewLine); value.Query = sbFilterValue.ToString(); return(value); } else { string currentUserAllGroup = Security.GetPrincipalGroups(Security.CurrentUserId); StringBuilder sbFilterValue = new StringBuilder(); sbFilterValue.AppendFormat(@"SELECT [ttb].TimeTrackingBlockId{0}", Environment.NewLine); sbFilterValue.AppendFormat(@"FROM [cls_TimeTrackingBlock] AS [ttb]{0}", Environment.NewLine); sbFilterValue.AppendFormat(@"WHERE ({0}", Environment.NewLine); sbFilterValue.AppendFormat(@" ttb.TimeTrackingBlockId {0}", Environment.NewLine); sbFilterValue.AppendFormat(@" IN{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" ({0}", Environment.NewLine); sbFilterValue.AppendFormat(@" SELECT ObjectId FROM cls_TimeTrackingBlock_ReadACL{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" WHERE PrincipalId IN ({1}) AND [Read] = 2{0}", Environment.NewLine, currentUserAllGroup); sbFilterValue.AppendFormat(@" ){0}", Environment.NewLine); sbFilterValue.AppendFormat(@" OR{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" EXISTS {0}", Environment.NewLine); sbFilterValue.AppendFormat(@" ({0}", Environment.NewLine); sbFilterValue.AppendFormat(@" SELECT * {0}", Environment.NewLine); sbFilterValue.AppendFormat(@" FROM cls_TimeTrackingBlock_GlobalACL G{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" LEFT JOIN cls_TimeTrackingBlock_GlobalACL_State S ON "); sbFilterValue.AppendFormat(@"(G.TimeTrackingBlock_GlobalACLId = S.GlobalACLId AND S.StateMachineId = [ttb].mc_StateMachineId AND "); sbFilterValue.AppendFormat(@"S.StateId = [ttb].mc_StateId){0}", Environment.NewLine); sbFilterValue.AppendFormat(@" WHERE ({0}", Environment.NewLine); sbFilterValue.AppendFormat(@" (S.[Read] IS NULL AND G.[Read] = 2){0}", Environment.NewLine); sbFilterValue.AppendFormat(@" OR {0}", Environment.NewLine); sbFilterValue.AppendFormat(@" S.[Read] = 2{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" ){0}", Environment.NewLine); sbFilterValue.AppendFormat(@" AND PrincipalId IN ({1}){0}", Environment.NewLine, currentUserAllGroup); sbFilterValue.AppendFormat(@" ){0}", Environment.NewLine); sbFilterValue.AppendFormat(@" ){0}", Environment.NewLine); sbFilterValue.AppendFormat(@" AND ttb.TimeTrackingBlockId NOT IN{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" ({0}", Environment.NewLine); sbFilterValue.AppendFormat(@" SELECT ObjectId FROM cls_TimeTrackingBlock_ReadACL{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" WHERE {0}", Environment.NewLine); sbFilterValue.AppendFormat(@" PrincipalId IN ({1}) AND [Read] = 3{0}", Environment.NewLine, currentUserAllGroup); sbFilterValue.AppendFormat(@" ){0}", Environment.NewLine); sbFilterValue.AppendFormat(@" AND NOT EXISTS {0}", Environment.NewLine); sbFilterValue.AppendFormat(@" ({0}", Environment.NewLine); sbFilterValue.AppendFormat(@" SELECT *{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" FROM cls_TimeTrackingBlock_GlobalACL G{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" LEFT JOIN cls_TimeTrackingBlock_GlobalACL_State S ON "); sbFilterValue.AppendFormat(@"(G.TimeTrackingBlock_GlobalACLId = S.GlobalACLId AND S.StateMachineId = [ttb].mc_StateMachineId AND "); sbFilterValue.AppendFormat(@"S.StateId = [ttb].mc_StateId){0}", Environment.NewLine); sbFilterValue.AppendFormat(@" WHERE ({0}", Environment.NewLine); sbFilterValue.AppendFormat(@" (S.[Read] IS NULL AND G.[Read] = 3){0}", Environment.NewLine); sbFilterValue.AppendFormat(@" OR {0}", Environment.NewLine); sbFilterValue.AppendFormat(@" S.[Read] = 3{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" ) {0}", Environment.NewLine); sbFilterValue.AppendFormat(@" AND PrincipalId IN ({1}){0}", Environment.NewLine, currentUserAllGroup); sbFilterValue.AppendFormat(@" ){0}", Environment.NewLine); return(sbFilterValue.ToString()); } } return(string.Empty); }
public virtual object GetValue(string key) { switch (key) { case "CanRead": if (DataHelper.UseSpOptimization) { string currentUserAllGroup = Security.GetPrincipalGroups(Security.CurrentUserId); SelectCommandBuilderParameter value = new SelectCommandBuilderParameter(); // Declare SP Prefix @Table // Declare SP Parameter value.Parameters.Add(new SqlParameter("@PrincipalGroups",currentUserAllGroup)); value.Prefix = "DECLARE @PrincipalGroupsTable TABLE (PrincipalId int)" + "\r\n" + "INSERT INTO @PrincipalGroupsTable (PrincipalId) SELECT Item FROM Split(@PrincipalGroups)\r\n\r\n"; StringBuilder sbFilterValue = new StringBuilder(); sbFilterValue.AppendFormat(@"SELECT [ttb].TimeTrackingBlockId{0}", Environment.NewLine); sbFilterValue.AppendFormat(@"FROM [cls_TimeTrackingBlock] AS [ttb]{0}", Environment.NewLine); sbFilterValue.AppendFormat(@"WHERE ({0}", Environment.NewLine); sbFilterValue.AppendFormat(@" ttb.TimeTrackingBlockId {0}", Environment.NewLine); sbFilterValue.AppendFormat(@" IN{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" ({0}", Environment.NewLine); sbFilterValue.AppendFormat(@" SELECT ObjectId FROM cls_TimeTrackingBlock_ReadACL{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" WHERE PrincipalId IN ({1}) AND [Read] = 2{0}", Environment.NewLine, "SELECT PrincipalId FROM @PrincipalGroupsTable"); sbFilterValue.AppendFormat(@" ){0}", Environment.NewLine); sbFilterValue.AppendFormat(@" OR{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" EXISTS {0}", Environment.NewLine); sbFilterValue.AppendFormat(@" ({0}", Environment.NewLine); sbFilterValue.AppendFormat(@" SELECT * {0}", Environment.NewLine); sbFilterValue.AppendFormat(@" FROM cls_TimeTrackingBlock_GlobalACL G{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" LEFT JOIN cls_TimeTrackingBlock_GlobalACL_State S ON "); sbFilterValue.AppendFormat(@"(G.TimeTrackingBlock_GlobalACLId = S.GlobalACLId AND S.StateMachineId = [ttb].mc_StateMachineId AND "); sbFilterValue.AppendFormat(@"S.StateId = [ttb].mc_StateId){0}", Environment.NewLine); sbFilterValue.AppendFormat(@" WHERE ({0}", Environment.NewLine); sbFilterValue.AppendFormat(@" (S.[Read] IS NULL AND G.[Read] = 2){0}", Environment.NewLine); sbFilterValue.AppendFormat(@" OR {0}", Environment.NewLine); sbFilterValue.AppendFormat(@" S.[Read] = 2{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" ){0}", Environment.NewLine); sbFilterValue.AppendFormat(@" AND PrincipalId IN ({1}){0}", Environment.NewLine, "SELECT PrincipalId FROM @PrincipalGroupsTable"); sbFilterValue.AppendFormat(@" ){0}", Environment.NewLine); sbFilterValue.AppendFormat(@" ){0}", Environment.NewLine); sbFilterValue.AppendFormat(@" AND ttb.TimeTrackingBlockId NOT IN{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" ({0}", Environment.NewLine); sbFilterValue.AppendFormat(@" SELECT ObjectId FROM cls_TimeTrackingBlock_ReadACL{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" WHERE {0}", Environment.NewLine); sbFilterValue.AppendFormat(@" PrincipalId IN ({1}) AND [Read] = 3{0}", Environment.NewLine, "SELECT PrincipalId FROM @PrincipalGroupsTable"); sbFilterValue.AppendFormat(@" ){0}", Environment.NewLine); sbFilterValue.AppendFormat(@" AND NOT EXISTS {0}", Environment.NewLine); sbFilterValue.AppendFormat(@" ({0}", Environment.NewLine); sbFilterValue.AppendFormat(@" SELECT *{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" FROM cls_TimeTrackingBlock_GlobalACL G{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" LEFT JOIN cls_TimeTrackingBlock_GlobalACL_State S ON "); sbFilterValue.AppendFormat(@"(G.TimeTrackingBlock_GlobalACLId = S.GlobalACLId AND S.StateMachineId = [ttb].mc_StateMachineId AND "); sbFilterValue.AppendFormat(@"S.StateId = [ttb].mc_StateId){0}", Environment.NewLine); sbFilterValue.AppendFormat(@" WHERE ({0}", Environment.NewLine); sbFilterValue.AppendFormat(@" (S.[Read] IS NULL AND G.[Read] = 3){0}", Environment.NewLine); sbFilterValue.AppendFormat(@" OR {0}", Environment.NewLine); sbFilterValue.AppendFormat(@" S.[Read] = 3{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" ) {0}", Environment.NewLine); sbFilterValue.AppendFormat(@" AND PrincipalId IN ({1}){0}", Environment.NewLine, "SELECT PrincipalId FROM @PrincipalGroupsTable"); sbFilterValue.AppendFormat(@" ){0}", Environment.NewLine); value.Query = sbFilterValue.ToString(); return value; } else { string currentUserAllGroup = Security.GetPrincipalGroups(Security.CurrentUserId); StringBuilder sbFilterValue = new StringBuilder(); sbFilterValue.AppendFormat(@"SELECT [ttb].TimeTrackingBlockId{0}", Environment.NewLine); sbFilterValue.AppendFormat(@"FROM [cls_TimeTrackingBlock] AS [ttb]{0}", Environment.NewLine); sbFilterValue.AppendFormat(@"WHERE ({0}", Environment.NewLine); sbFilterValue.AppendFormat(@" ttb.TimeTrackingBlockId {0}", Environment.NewLine); sbFilterValue.AppendFormat(@" IN{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" ({0}", Environment.NewLine); sbFilterValue.AppendFormat(@" SELECT ObjectId FROM cls_TimeTrackingBlock_ReadACL{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" WHERE PrincipalId IN ({1}) AND [Read] = 2{0}", Environment.NewLine, currentUserAllGroup); sbFilterValue.AppendFormat(@" ){0}", Environment.NewLine); sbFilterValue.AppendFormat(@" OR{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" EXISTS {0}", Environment.NewLine); sbFilterValue.AppendFormat(@" ({0}", Environment.NewLine); sbFilterValue.AppendFormat(@" SELECT * {0}", Environment.NewLine); sbFilterValue.AppendFormat(@" FROM cls_TimeTrackingBlock_GlobalACL G{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" LEFT JOIN cls_TimeTrackingBlock_GlobalACL_State S ON "); sbFilterValue.AppendFormat(@"(G.TimeTrackingBlock_GlobalACLId = S.GlobalACLId AND S.StateMachineId = [ttb].mc_StateMachineId AND "); sbFilterValue.AppendFormat(@"S.StateId = [ttb].mc_StateId){0}", Environment.NewLine); sbFilterValue.AppendFormat(@" WHERE ({0}", Environment.NewLine); sbFilterValue.AppendFormat(@" (S.[Read] IS NULL AND G.[Read] = 2){0}", Environment.NewLine); sbFilterValue.AppendFormat(@" OR {0}", Environment.NewLine); sbFilterValue.AppendFormat(@" S.[Read] = 2{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" ){0}", Environment.NewLine); sbFilterValue.AppendFormat(@" AND PrincipalId IN ({1}){0}", Environment.NewLine, currentUserAllGroup); sbFilterValue.AppendFormat(@" ){0}", Environment.NewLine); sbFilterValue.AppendFormat(@" ){0}", Environment.NewLine); sbFilterValue.AppendFormat(@" AND ttb.TimeTrackingBlockId NOT IN{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" ({0}", Environment.NewLine); sbFilterValue.AppendFormat(@" SELECT ObjectId FROM cls_TimeTrackingBlock_ReadACL{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" WHERE {0}", Environment.NewLine); sbFilterValue.AppendFormat(@" PrincipalId IN ({1}) AND [Read] = 3{0}", Environment.NewLine, currentUserAllGroup); sbFilterValue.AppendFormat(@" ){0}", Environment.NewLine); sbFilterValue.AppendFormat(@" AND NOT EXISTS {0}", Environment.NewLine); sbFilterValue.AppendFormat(@" ({0}", Environment.NewLine); sbFilterValue.AppendFormat(@" SELECT *{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" FROM cls_TimeTrackingBlock_GlobalACL G{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" LEFT JOIN cls_TimeTrackingBlock_GlobalACL_State S ON "); sbFilterValue.AppendFormat(@"(G.TimeTrackingBlock_GlobalACLId = S.GlobalACLId AND S.StateMachineId = [ttb].mc_StateMachineId AND "); sbFilterValue.AppendFormat(@"S.StateId = [ttb].mc_StateId){0}", Environment.NewLine); sbFilterValue.AppendFormat(@" WHERE ({0}", Environment.NewLine); sbFilterValue.AppendFormat(@" (S.[Read] IS NULL AND G.[Read] = 3){0}", Environment.NewLine); sbFilterValue.AppendFormat(@" OR {0}", Environment.NewLine); sbFilterValue.AppendFormat(@" S.[Read] = 3{0}", Environment.NewLine); sbFilterValue.AppendFormat(@" ) {0}", Environment.NewLine); sbFilterValue.AppendFormat(@" AND PrincipalId IN ({1}){0}", Environment.NewLine, currentUserAllGroup); sbFilterValue.AppendFormat(@" ){0}", Environment.NewLine); return sbFilterValue.ToString(); } } return string.Empty; }