/// <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;
}
