/// <summary>
/// Updates the navigation item edit.
/// </summary>
/// <param name="dto">The dto.</param>
/// <exception cref="System.ArgumentException"></exception>
/// <exception cref="System.Data.DBConcurrencyException">The entity is out of date on the client. Please update the entity and try again. This could also be thrown if the sql statement failed to execute.</exception>
public void UpdateNavigationItemEdit(NavigationItemEditDto dto)
{
if (dto == null) throw new ArgumentException(string.Format(CultureInfo.InvariantCulture, Resources.NullArguementException, "dto"));
const string Sql =
@"
UPDATE [dbo].[NavigationItems]
SET
[Guid] = @guid
,[LastModifiedOn] = GETDATE()
,[Name] = @name
,[SystemName] = @systemName
,[IconURL] = @iconURL
,[Sequence] = @sequence
,[NavigationGroupId] = @navigationGroupId
,[IconId] = @iconId
,[PublishedProcessId] = @publishedProcessId
,[ProcessViewGuid] = @processViewGuid
WHERE [Id] = @id;
";
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
using (var cmd = new SqlCommand(Sql, cn))
{
cmd.Parameters.AddWithValue("@id", dto.Id);
cmd.Parameters.AddWithValue("@guid", dto.Guid);
cmd.Parameters.AddWithValue("@name", dto.Name);
cmd.Parameters.AddWithValue("@systemName", AdoHelper.NullCheck(dto.SystemName));
cmd.Parameters.AddWithValue("@iconURL", AdoHelper.NullCheck(dto.IconURL));
cmd.Parameters.AddWithValue("@sequence", dto.Sequence);
cmd.Parameters.AddWithValue("@navigationGroupId", dto.NavigationGroupId);
cmd.Parameters.AddWithValue("@iconId", AdoHelper.NullCheck(dto.IconId));
cmd.Parameters.AddWithValue("@publishedProcessId", AdoHelper.NullCheck(dto.PublishedProcessId));
cmd.Parameters.AddWithValue("@processViewGuid", AdoHelper.NullCheck(dto.ProcessViewGuid));
var rowsAffetcted = cmd.ExecuteNonQuery();
if (rowsAffetcted == 0)
throw new DBConcurrencyException(
"The entity is out of date on the client. Please update the entity and try again. This could also be thrown if the sql statement failed to execute.");
}
}
}
/// <summary>
/// Fetches the navigation item security configurations.
/// </summary>
/// <param name="connection">The connection.</param>
/// <param name="dto">The dto.</param>
private static void FetchNavigationItemSecurityConfigurations(SqlConnection connection, NavigationItemEditDto dto)
{
const string Sql =
@"
SELECT
[Id]
,[RoleId]
,[CanView]
FROM
[dbo].[NavigationItemSecurityConfigurations]
WHERE [NavigationItemId] = @itemId;
";
using (var cmd = new SqlCommand(Sql, connection))
{
cmd.Parameters.AddWithValue("@itemId", dto.Id);
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
var securityConfigurationDto = new NavigationItemSecurityConfigurationDto
{
Id = reader.GetInt(0),
RoleId = reader.GetInt(1),
CanView = reader.GetBoolean(2)
};
dto.SecurityConfigurations.Add(securityConfigurationDto);
}
}
}
}
/// <summary>
/// Inserts the navigation item edit.
/// </summary>
/// <param name="dto">The dto.</param>
/// <exception cref="System.ArgumentException"></exception>
public void InsertNavigationItemEdit(NavigationItemEditDto dto)
{
if (dto == null) throw new ArgumentException(string.Format(CultureInfo.InvariantCulture, Resources.NullArguementException, "dto"));
const string Sql =
@"
INSERT INTO [dbo].[NavigationItems]
(
[Guid]
,[LastModifiedOn]
,[Name]
,[SystemName]
,[IconURL]
,[Sequence]
,[NavigationGroupId]
,[IconId]
,[PublishedProcessId]
,[ProcessViewGuid]
)
VALUES
(
@guid
,GETDATE()
,@name
,@systemName
,@iconURL
,@sequence
,@navigationGroupId
,@iconId
,@publishedProcessId
,@processViewGuid
);
SELECT [Id]
FROM [dbo].[NavigationItems]
WHERE [Id] = SCOPE_IDENTITY()
";
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
using (var cmd = new SqlCommand(Sql, cn))
{
cmd.Parameters.AddWithValue("@guid", dto.Guid);
cmd.Parameters.AddWithValue("@name", dto.Name);
cmd.Parameters.AddWithValue("@systemName", AdoHelper.NullCheck(dto.SystemName));
cmd.Parameters.AddWithValue("@iconURL", AdoHelper.NullCheck(dto.IconURL));
cmd.Parameters.AddWithValue("@sequence", dto.Sequence);
cmd.Parameters.AddWithValue("@navigationGroupId", dto.NavigationGroupId);
cmd.Parameters.AddWithValue("@iconId", AdoHelper.NullCheck(dto.IconId));
cmd.Parameters.AddWithValue("@publishedProcessId", AdoHelper.NullCheck(dto.PublishedProcessId));
cmd.Parameters.AddWithValue("@processViewGuid", AdoHelper.NullCheck(dto.ProcessViewGuid));
dto.Id = (int)cmd.ExecuteScalar();
}
}
}
/// <summary>
/// Fetches the group navigation items.
/// </summary>
/// <param name="connection">The connection.</param>
/// <param name="dto">The dto.</param>
/// <param name="currentLocalizationId">The current localization identifier.</param>
private static void FetchGroupNavigationItems(SqlConnection connection, NavigationGroupEditDto dto, int currentLocalizationId)
{
const string sql =
@"
SELECT
nv.[Id]
,nv.[Guid]
,ISNULL(pl.ProcessName, p.[Name]) + (CASE WHEN ISNULL(pvl.Name, pv.Name) IS NULL THEN '' ELSE ' - ' + ISNULL(pvl.Name, pv.Name) END)
,p.[SystemName]
,nv.[Sequence]
,nv.[PublishedProcessId]
,nv.[IconURL]
,p.[IconId]
,nv.[ProcessViewGuid]
FROM
[dbo].[NavigationItems] nv
INNER JOIN [dbo].[PublishedProcesses] pp ON nv.[PublishedProcessId] = pp.[Id]
INNER JOIN [dbo].[Processes] p ON pp.[ProcessId] = p.[Id]
INNER JOIN Processes p2 ON pp.ProcessGuid = p2.[Guid] and p2.[IsPublishedCopy] = 0
LEFT JOIN [dbo].[ProcessViews] pv ON pv.[Guid] = nv.ProcessViewGuid AND pv.ProcessId = p.Id
LEFT OUTER JOIN dbo.ProcessLocalizations pl ON p2.Id = pl.ProcessId AND pl.LocalizationId = @locId
LEFT OUTER JOIN dbo.ProcessViewLocalizations pvl ON p2.Id = pl.ProcessId AND pvl.LocalizationId = @locId
WHERE
p.[IsRemoved] = 0 AND
p.IsInactive = 0 AND
nv.[NavigationGroupId] = @groupId
UNION
SELECT
nv.[Id]
,nv.[Guid]
,nv.[Name]
,NULL
,nv.[Sequence]
,NULL
,nv.[IconURL]
,NULL
,NULL
FROM
[dbo].[NavigationItems] nv
WHERE nv.[SystemName] IS NULL AND
nv.[PublishedProcessId] IS NULL AND
nv.[NavigationGroupId] = @groupId;
";
using (var cmd = new SqlCommand(sql, connection))
{
cmd.Parameters.AddWithValue("@groupId", dto.Id);
cmd.Parameters.AddWithValue("@locId", currentLocalizationId);
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
var navigationItemDto = new NavigationItemEditDto
{
Id = reader.GetInt(0),
Guid = reader.GetGuid(1),
Name = reader.GetString(2),
Sequence = reader.GetDouble(4),
PublishedProcessId = reader.GetNullableInt(5),
IconURL = reader.GetString(6),
IconId = reader.GetNullableInt(7),
ProcessViewGuid = reader.GetNullableGuid(8)
};
//FetchNavigationItemSecurityConfigurations(connection, navigationItemDto);
dto.NavigationItems.Add(navigationItemDto);
}
}
foreach (var navigationItemDto in dto.NavigationItems)
{
FetchNavigationItemSecurityConfigurations(connection, navigationItemDto);
}
}
}
