/// <summary> /// Gets the states usings. /// </summary> /// <param name="stateGuid">The state identifier.</param> /// <returns>IEnumerable{RecordUsingsDto}.</returns> public IEnumerable<RecordUsingsDto> GetStatesUsings(Guid stateGuid) { const string CmdText = @" SELECT 'UsingInFieldsSecurityConfigs' as Using, p.Name as ProcessName, p.IsPublishedCopy, NULL as ActionName, NULL as CommandName, NULL as FilterName, NULL as ReportName, NULL as NavigationGroupName, NULL as NavigationItemName FROM [dbo].[ProcessSecurityConfigs] sc JOIN [dbo].[Processes] p ON p.Id = sc.ProcessId WHERE @StateGuid = StateGuid UNION SELECT 'UsingInCommandsSecurityConfigs' as Using, p.Name as ProcessName, p.IsPublishedCopy, NULL as ActionName, c.CommandName, NULL as FilterName, NULL as ReportName, NULL as NavigationGroupName, NULL as NavigationItemName FROM [dbo].[Commands] c LEFT OUTER JOIN [dbo].[ProcessCommandSecurityConfigurations] pcsc ON pcsc.ProcessCommandId = c.Id JOIN [dbo].[Processes] p ON p.Id = c.ProcessId WHERE @StateGuid = pcsc.StateGuid"; using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false)) using (var cmd = new SqlCommand(CmdText, ctx.Connection)) { cmd.Parameters.AddWithValue("@StateGuid", stateGuid); using (var reader = new SafeDataReader(cmd.ExecuteReader())) { var usings = new List<RecordUsingsDto>(); while (reader.Read()) { var use = new RecordUsingsDto { UsingName = reader.GetString(0), ProcessName = reader.GetString(1), IsPublished = reader.GetBool(2), ActionName = reader.GetString(3), CommandName = reader.GetString(4), FilterName = reader.GetString(5), ReportName = reader.GetString(6), NavigationGroupName = reader.GetString(7), NavigationItemName = reader.GetString(8), }; usings.Add(use); } if (!usings.IsNullOrEmpty()) { return usings; } } } return null; }
/// <summary> /// Gets all usings for a specified business unit. /// </summary> /// <param name="businessUnitId">Id of the business unit.</param> /// <returns>IEnumerable{RecordUsingsDto}.</returns> public IEnumerable<RecordUsingsDto> GetBaseBusinessUnitUsings(int businessUnitId) { const string CmdText = @" SELECT 'UsingInFieldsSecurityConfigs' as Using, p.Name as ProcessName, p.IsPublishedCopy, NULL as CommandName, NULL as ReportName FROM [dbo].[ProcessSecurityConfigs] sc JOIN [dbo].[Processes] p ON p.Id = sc.ProcessId WHERE @BusinessUnitId = BusinessUnitId UNION SELECT 'UsingInStateConfigs' as Using, p.Name as ProcessName, p.IsPublishedCopy, NULL as CommandName, NULL as ReportName FROM [dbo].[States] s LEFT OUTER JOIN [dbo].[StateLinks] sl ON sl.StateOutGuid = s.Guid LEFT OUTER JOIN [dbo].[StateConnectorSecurityConfigurations] scsc ON scsc.StateInGuid = sl.StateInGuid AND scsc.StateOutGuid = sl.StateOutGuid JOIN [dbo].[Processes] p ON p.Id = s.ProcessId WHERE @BusinessUnitId = scsc.BusinessUnitId UNION SELECT 'UsingInCommandsSecurityConfigs' as Using, p.Name as ProcessName, p.IsPublishedCopy, c.CommandName, NULL as ReportName FROM [dbo].[Commands] c LEFT OUTER JOIN [dbo].[ProcessCommandSecurityConfigurations] pcsc ON pcsc.ProcessCommandId = c.Id JOIN [dbo].[Processes] p ON p.Id = c.ProcessId WHERE @BusinessUnitId = pcsc.BusinessUnitId UNION SELECT 'UsingInReportSecurityConfigurations' as Using, p.Name as ProcessName, p.IsPublishedCopy, NULL as CommandName, r.Title as ReportName FROM [dbo].[Reports] r LEFT OUTER JOIN [dbo].[ProcessReportSecurityConfigurations] prsc ON prsc.ReportId = r.Id JOIN [dbo].[Processes] p ON p.Id = r.ProcessId WHERE @BusinessUnitId = prsc.BusinessUnitId UNION SELECT 'UsingInProcessSecurityConfigs' as Using, p.Name as ProcessName, p.IsPublishedCopy, NULL as CommandName, NULL as ReportName FROM [dbo].[ProcessSecurityConfigurations] sc JOIN [dbo].[Processes] p ON p.Id = sc.ProcessId WHERE @BusinessUnitId = BusinessUnitId "; using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false)) using (var cmd = new SqlCommand(CmdText, ctx.Connection)) { cmd.Parameters.AddWithValue("@BusinessUnitId", businessUnitId); using (var reader = new SafeDataReader(cmd.ExecuteReader())) { var usings = new List<RecordUsingsDto>(); while (reader.Read()) { var use = new RecordUsingsDto { UsingName = reader.GetString(0), ProcessName = reader.GetString(1), IsPublished = reader.GetBool(2), CommandName = reader.GetString(3), ReportName = reader.GetString(4), }; usings.Add(use); } if (!usings.IsNullOrEmpty()) { return usings; } } } return null; }
/// <summary> /// Gets all usings for a specified role. /// </summary> /// <param name="roleId">Id of the role.</param> /// <returns>IEnumerable{RecordUsingsDto}.</returns> public IEnumerable<RecordUsingsDto> GetBaseRoleUsings(int roleId) { const string CmdText = @" SELECT 'UsingInFieldsSecurityConfigs' as Using, p.Name as ProcessName, p.IsPublishedCopy, NULL as ActionName, NULL as CommandName, NULL as FilterName, NULL as ReportName, NULL as NavigationGroupName, NULL as NavigationItemName FROM [dbo].[ProcessSecurityConfigs] sc JOIN [dbo].[Processes] p ON p.Id = sc.ProcessId WHERE @RoleId = RoleId UNION SELECT 'UsingInStateConfigs' as Using, p.Name as ProcessName, p.IsPublishedCopy, NULL as ActionName, NULL as CommandName, NULL as FilterName, NULL as ReportName, NULL as NavigationGroupName, NULL as NavigationItemName FROM [dbo].[States] s LEFT OUTER JOIN [dbo].[StateLinks] sl ON sl.StateOutGuid = s.Guid LEFT OUTER JOIN [dbo].[StateConnectorSecurityConfigurations] scsc ON scsc.StateInGuid = sl.StateInGuid AND scsc.StateOutGuid = sl.StateOutGuid JOIN [dbo].[Processes] p ON p.Id = s.ProcessId WHERE @RoleId = scsc.RoleId UNION SELECT 'UsingInActions' as Using, p.Name as ProcessName, p.IsPublishedCopy, ad.Name as ActionName, NULL as CommandName, NULL as FilterName, NULL as ReportName, NULL as NavigationGroupName, NULL as NavigationItemName FROM [dbo].[ActionDefinitions] ad LEFT OUTER JOIN [dbo].[EscalationActionOptions] ea ON ea.ActionId = ad.[Id] JOIN [dbo].[Processes] p ON p.Id = ad.ProcessId WHERE @RoleId = ea.RoleId UNION SELECT 'UsingInActions' as Using, p.Name as ProcessName, p.IsPublishedCopy, ad.Name as ActionName, NULL as CommandName, NULL as FilterName, NULL as ReportName, NULL as NavigationGroupName, NULL as NavigationItemName FROM [dbo].[ActionDefinitions] ad LEFT OUTER JOIN [dbo].[AssignmentActionOptions] aa ON aa.ActionId = ad.[Id] JOIN [dbo].[Processes] p ON p.Id = ad.ProcessId WHERE @RoleId = aa.RoleId UNION SELECT 'UsingInCommandsSecurityConfigs' as Using, p.Name as ProcessName, p.IsPublishedCopy, NULL as ActionName, c.CommandName, NULL as FilterName, NULL as ReportName, NULL as NavigationGroupName, NULL as NavigationItemName FROM [dbo].[Commands] c LEFT OUTER JOIN [dbo].[ProcessCommandSecurityConfigurations] pcsc ON pcsc.ProcessCommandId = c.Id JOIN [dbo].[Processes] p ON p.Id = c.ProcessId WHERE @RoleId = pcsc.RoleId UNION SELECT 'UsingInFiltersSecurityConfigs' as Using, p.Name as ProcessName, p.IsPublishedCopy, NULL as ActionName, NULL as CommandName, f.Name as FilterName, NULL as ReportName, NULL as NavigationGroupName, NULL as NavigationItemName FROM [dbo].[Filters] f JOIN [dbo].[Processes] p ON p.Id = f.ProcessId WHERE @RoleID = ANY(SELECT Int_Value FROM fn_ParseText2Table(RoleIds, ',')) UNION SELECT 'UsingInReportSecurityConfigurations' as Using, p.Name as ProcessName, p.IsPublishedCopy, NULL as ActionName, NULL as CommandName, NULL as FilterName, r.Title as ReportName, NULL as NavigationGroupName, NULL as NavigationItemName FROM [dbo].[Reports] r LEFT OUTER JOIN [dbo].[ProcessReportSecurityConfigurations] prsc ON prsc.ReportId = r.Id JOIN [dbo].[Processes] p ON p.Id = r.ProcessId WHERE @RoleId = prsc.RoleId UNION SELECT 'UsingInProcessSecurityConfigs' as Using, p.Name as ProcessName, p.IsPublishedCopy, NULL as ActionName, NULL as CommandName, NULL as FilterName, NULL as ReportName, NULL as NavigationGroupName, NULL as NavigationItemName FROM [dbo].[ProcessSecurityConfigurations] sc JOIN [dbo].[Processes] p ON p.Id = sc.ProcessId WHERE @RoleID = RoleId UNION SELECT 'UsingInNavigationGroup' as Using, NULL as ProcessId, NULL as IsPublishedCopy, NULL as ActionName, NULL as CommandName, NULL as FilterName, NULL as ReportName, ng.Name as NavigationGroupName, NULL as NavigationItemName FROM [dbo].[NavigationGroupSecurityConfigurations] ngs JOIN [dbo].[NavigationGroups] ng ON ng.Id = ngs.NavigationGroupId WHERE @RoleID = RoleId AND ngs.CanView = 1 UNION SELECT 'UsingInNavigationItem' as Using, p.Name as ProcessName, NULL as IsPublishedCopy, NULL as ActionName, NULL as CommandName, NULL as FilterName, NULL as ReportName, ng.Name as NavigationGroupName, ni.Name as NavigationItemName FROM [dbo].[NavigationItemSecurityConfigurations] nis JOIN [dbo].[NavigationItems] ni ON ni.Id = nis.NavigationItemId JOIN [dbo].[PublishedProcesses] pp ON pp.Id = ni.PublishedProcessId JOIN [dbo].[Processes] p ON p.Id = pp.ProcessId JOIN [dbo].[NavigationGroups] ng ON ng.Id = ni.NavigationGroupId WHERE @RoleID = RoleId AND nis.CanView = 1 UNION SELECT 'UsingInNavigationItem' as Using, NULL as ProcessName, NULL as IsPublishedCopy, NULL as ActionName, NULL as CommandName, NULL as FilterName, NULL as ReportName, ng.Name as NavigationGroupName, ni.Name as NavigationItemName FROM [dbo].[NavigationItemSecurityConfigurations] nis JOIN [dbo].[NavigationItems] ni ON ni.Id = nis.NavigationItemId JOIN [dbo].[NavigationGroups] ng ON ng.Id = ni.NavigationGroupId WHERE @RoleID = RoleId AND nis.CanView = 1 AND ni.PublishedProcessId IS NULL "; using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false)) using (var cmd = new SqlCommand(CmdText, ctx.Connection)) { cmd.Parameters.AddWithValue("@RoleID", roleId); using (var reader = new SafeDataReader(cmd.ExecuteReader())) { var usings = new List<RecordUsingsDto>(); while (reader.Read()) { var use = new RecordUsingsDto { UsingName = reader.GetString(0), ProcessName = reader.GetString(1), IsPublished = reader.GetBool(2), ActionName = reader.GetString(3), CommandName = reader.GetString(4), FilterName = reader.GetString(5), ReportName = reader.GetString(6), NavigationGroupName = reader.GetString(7), NavigationItemName = reader.GetString(8), }; usings.Add(use); } if (!usings.IsNullOrEmpty()) { return usings; } } } return null; }