/// <summary>
/// Method for switching role status
/// </summary>
/// <param name = "id"> Id </param>
/// <param name = "status"> Role status </param>
/// <returns> </returns>
public async Task ToggleSuspend(string id, RoleStatuses status)
{
var role = await _context.Roles.Include(x => x.UserRoles).FirstOrDefaultAsync(r => r.Id == id);
if (role == null)
{
throw new Exception(nameof(Resource.RoleNotFound));
}
var userIds = role.UserRoles.Select(ur => ur.UserId).ToArray();
if (status == RoleStatuses.Suspended && role.Name == DefaultRoleNames.Administrator)
{
throw new Exception(nameof(Resource.DeleteSysAdminRoleError));
}
if (status == RoleStatuses.Suspended && userIds.Any() &&
await _context.Users.AnyAsync(u => userIds.Contains(u.Id) && u.Status == UserStatuses.Active))
{
throw new Exception(nameof(Resource.DeleteRoleError));
}
role.Status = status;
if (status == RoleStatuses.Suspended)
{
var records = await _context.UserRoles.Where(x => x.RoleId == id).ToListAsync();
_context.UserRoles.RemoveRange(records);
}
await _context.SaveChangesAsync();
}
public List<Role> Select(int maximumRows, int startRowIndex, string sortExpression, string roleName, RoleStatuses? status)
{
const string SQL_STATEMENT =
"WITH SortedRoles AS " +
"(SELECT ROW_NUMBER() OVER (ORDER BY {1}) AS RowNumber, " +
"[ID], [RoleName], [Description],[Status],[CreateTime] " +
"FROM dbo.Accounts " +
"{0}" +
") SELECT * FROM SortedRoles " +
"WHERE RowNumber BETWEEN @StartRowIndex AND @EndRowIndex";
startRowIndex++;
long endRowIndex = startRowIndex + maximumRows;
List<Role> result = new List<Role>();
// Connect to database.
Database db = DatabaseFactory.CreateDatabase(CONNECTION_NAME);
using (DbCommand cmd = db.GetSqlStringCommand(SQL_STATEMENT))
{
// Append filters.
string filter = AppendFilters(db, cmd, roleName, status);
// Construct final WHERE statement.
if (!string.IsNullOrWhiteSpace(filter))
filter = "WHERE " + base.FormatFilterStatement(filter);
cmd.CommandText = string.Format(SQL_STATEMENT, filter, sortExpression);
// Paging Parameters.
db.AddInParameter(cmd, "@StartRowIndex", DbType.Int64, startRowIndex);
db.AddInParameter(cmd, "@EndRowIndex", DbType.Int64, endRowIndex);
using (IDataReader dr = db.ExecuteReader(cmd))
{
while (dr.Read())
{
// Create a new Account
Role role = LoadRole(dr);
// Add to List.
result.Add(role);
}
}
}
return result;
}
public List<Role> ListRoles(int maximumRows, int startRowIndex, string sortExpression, string roleName, RoleStatuses? status, out int totalRowCount)
{
List<Role> result = default(List<Role>);
if (string.IsNullOrWhiteSpace(sortExpression))
sortExpression = "CreateTime DESC";
// Data access component declarations.
var RoleDAC = new RoleDAL();
// Step 1 - Calling Select on AccountDAC.
result = RoleDAC.Select(maximumRows, startRowIndex, sortExpression, roleName,status);
// Step 2 - Get count.
totalRowCount = RoleDAC.Count(roleName, status);
return result;
}
private static string AppendFilters(Database db, DbCommand cmd,
string rolename, RoleStatuses? status)
{
string filter = string.Empty;
// Employee filter.
if (!string.IsNullOrWhiteSpace(rolename))
{
db.AddInParameter(cmd, "@rolename", DbType.AnsiString, rolename);
filter += "AND [RoleName]=@rolename ";
}
// Status filter.
if (status != null)
{
db.AddInParameter(cmd, "@Status", DbType.Byte, status);
filter += "AND [Status]=@Status ";
}
return filter;
}
public int Count(string role, RoleStatuses? status)
{
const string SQL_STATEMENT =
"SELECT COUNT(1) " +
"FROM dbo.Accounts " +
"{0}";
int result = 0;
// Connect to database.
Database db = DatabaseFactory.CreateDatabase(CONNECTION_NAME);
using (DbCommand cmd = db.GetSqlStringCommand(SQL_STATEMENT))
{
// Append filters.
string filter = AppendFilters(db, cmd, role, status);
// Construct final WHERE statement.
if (!string.IsNullOrWhiteSpace(filter))
filter = "WHERE " + base.FormatFilterStatement(filter);
cmd.CommandText = string.Format(SQL_STATEMENT, filter);
result = Convert.ToInt32(db.ExecuteScalar(cmd));
}
return result;
}
public async Task <IActionResult> ToggleDelete(string id, RoleStatuses status)
{
await _roleService.ToggleSuspend(id, status);
return(NoContent());
}
