/// <summary>
/// Updates the navigation group 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 UpdateNavigationGroupEdit(NavigationGroupEditDto dto)
{
if (dto == null) throw new ArgumentException(string.Format(CultureInfo.InvariantCulture, Resources.NullArguementException, "dto"));
const string Sql =
@"
UPDATE [dbo].[NavigationGroups]
SET
[Guid] = @guid
,[LastModifiedOn] = GETDATE()
,[Name] = @name
,[SystemName] = @systemName
,[Sequence] = @sequence
,[IconURL] = @iconURL
,[IconId] = @iconId
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("@sequence", dto.Sequence);
cmd.Parameters.AddWithValue("@iconURL", AdoHelper.NullCheck(dto.IconURL));
cmd.Parameters.AddWithValue("@iconId", AdoHelper.NullCheck(dto.IconId));
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>
/// Inserts the navigation group edit.
/// </summary>
/// <param name="dto">The dto.</param>
/// <exception cref="System.ArgumentException"></exception>
public void InsertNavigationGroupEdit(NavigationGroupEditDto dto)
{
if (dto == null) throw new ArgumentException(string.Format(CultureInfo.InvariantCulture, Resources.NullArguementException, "dto"));
const string Sql =
@"
INSERT INTO [dbo].[NavigationGroups]
(
[Guid]
,[LastModifiedOn]
,[Name]
,[SystemName]
,[Sequence]
,[IconURL]
,[IconId]
)
VALUES
(
@guid
,GETDATE()
,@name
,@systemName
,@sequence
,@iconURL
,@iconId
);
SELECT [Id]
FROM [dbo].[NavigationGroups]
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("@sequence", dto.Sequence);
cmd.Parameters.AddWithValue("@iconURL", AdoHelper.NullCheck(dto.IconURL));
cmd.Parameters.AddWithValue("@iconId", AdoHelper.NullCheck(dto.IconId));
dto.Id = (int)cmd.ExecuteScalar();
}
}
}
/// <summary>
/// Fetches the navigation group security configurations.
/// </summary>
/// <param name="connection">The connection.</param>
/// <param name="dto">The dto.</param>
private static void FetchNavigationGroupSecurityConfigurations(SqlConnection connection, NavigationGroupEditDto dto)
{
const string Sql =
@"
SELECT
[Id]
,[RoleId]
,[CanView]
FROM
[dbo].[NavigationGroupSecurityConfigurations]
WHERE [NavigationGroupId] = @groupId;
";
using (var cmd = new SqlCommand(Sql, connection))
{
cmd.Parameters.AddWithValue("@groupId", dto.Id);
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
var securityConfigurationDto = new NavigationGroupSecurityConfigurationDto
{
Id = reader.GetInt(0),
RoleId = reader.GetInt(1),
CanView = reader.GetBoolean(2)
};
dto.SecurityConfigurations.Add(securityConfigurationDto);
}
}
}
}
/// <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);
}
}
}
/// <summary>
/// Fetches the navigation group edit list.
/// </summary>
/// <param name="currentLocalizationId">The current localization identifier.</param>
/// <returns>IList{NavigationGroupEditDto}.</returns>
public IList<NavigationGroupEditDto> FetchNavigationGroupEditList(int currentLocalizationId)
{
if (currentLocalizationId == 0)
currentLocalizationId = GetLocIdByCultureName(Thread.CurrentThread.CurrentUICulture.Name);
var result = new List<NavigationGroupEditDto>();
const string sql =
@"
SELECT
[Id]
,[Guid]
,ISNULL(l.[Name], g.[Name]) as Name
,[SystemName]
,[Sequence]
,[IconURL]
,[IconId]
FROM
[dbo].[NavigationGroups] g left outer join [dbo].[NavigationGroupLocalizations] l on l.NavigationGroupId = g.Id and l.LocalizationId = @locId
";
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
if (cn.State != ConnectionState.Open)
{
cn.Open();
}
using (var cmd = new SqlCommand(sql, cn))
{
cmd.Parameters.AddWithValue("@locId", currentLocalizationId);
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
var navigationGroupDto = new NavigationGroupEditDto
{
Id = reader.GetInt(0),
Guid = reader.GetGuid(1),
Name = reader.GetString(2),
SystemName = reader.GetString(3),
Sequence = reader.GetDouble(4),
IconURL = reader.GetString(5),
IconId = reader.GetNullableInt(6)
};
//FetchGroupNavigationItems(cn, navigationGroupDto);
//FetchNavigationGroupSecurityConfigurations(cn, navigationGroupDto);
result.Add(navigationGroupDto);
}
}
foreach (var navigationGroupDto in result)
{
FetchGroupNavigationItems(cn, navigationGroupDto, currentLocalizationId);
FetchNavigationGroupSecurityConfigurations(cn, navigationGroupDto);
}
}
}
return result;
}
/// <summary>
/// Updates the localized navigation group edit.
/// </summary>
/// <param name="dto">The dto.</param>
/// <param name="localizationId">The localization identifier.</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 UpdateLocalizedNavigationGroupEdit(NavigationGroupEditDto dto, int localizationId)
{
if (dto == null) throw new ArgumentException(string.Format(CultureInfo.InvariantCulture, Resources.NullArguementException, "dto"));
const string Sql =
@"
IF EXISTS ( SELECT *
FROM [dbo].[NavigationGroupLocalizations]
WHERE [LocalizationId] = @locId and [NavigationGroupId] = @navGroupId)
BEGIN
UPDATE [dbo].[NavigationGroupLocalizations]
SET [LocalizationId] = @locId,
[NavigationGroupId] = @navGroupId,
[Name] = @name
WHERE [LocalizationId] = @locId and [NavigationGroupId] = @navGroupId
END
ELSE
INSERT INTO [dbo].[NavigationGroupLocalizations]
(
[LocalizationId],
[NavigationGroupId],
[Name]
)
VALUES
(
@locId,
@navGroupId,
@name)
";
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
using (var cmd = new SqlCommand(Sql, cn))
{
cmd.Parameters.AddWithValue("@navGroupId", dto.Id);
cmd.Parameters.AddWithValue("@locId", localizationId);
cmd.Parameters.AddWithValue("@name", dto.Name);
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.");
}
}
}