public async Task <IActionResult> LoadTable([FromBody] DataTablesParameters param)
{
try
{
var filtersModel = JsonConvert.DeserializeObject <UserFilterModel>(param.FiltersObject);
var queryModel = new UsersQueryModel
{
Parameters = param,
FilterModel = filtersModel
};
var url = IdentityAPI.Getting.PostQueryAsync(_identityService.IdentityBaseUrl, "user");
var result = await _identityService.GetAsync <UsersQueryModel, UsersResultDTO>(url, queryModel);
var users = result.Users.Select(async(x) => new UserListViewModel
{
Id = x.Id,
UserName = x.UserName,
Mobile = x.Mobile,
Role = RolesResource.ResourceManager.GetString(x.Role),
Person = x.PersonId == null ? "" : Lng == Lang.KU ? (await _personService.GetByIdAsync(x.PersonId.Value))?.FullName_Ku :
Lng == Lang.AR ? (await _personService.GetByIdAsync(x.PersonId.Value))?.FullName_Ar : (await _personService.GetByIdAsync(x.PersonId.Value))?.FullName,
Center = x.CenterId == null ? "" : x.LoginAs == LoginAs.PHARMACYMANAGER ?
Lng == Lang.KU ? (await _pharmacyRepository.GetByIdAsync(x.CenterId.Value))?.Name_Ku :
Lng == Lang.AR ? (await _pharmacyRepository.GetByIdAsync(x.CenterId.Value))?.Name_Ar : (await _pharmacyRepository.GetByIdAsync(x.CenterId.Value))?.Name
:
Lng == Lang.KU ? (await _shiftCenterService.GetByIdAsync(x.CenterId.Value))?.Name_Ku :
Lng == Lang.AR ? (await _shiftCenterService.GetByIdAsync(x.CenterId.Value))?.Name_Ar : (await _shiftCenterService.GetByIdAsync(x.CenterId.Value))?.Name,
LockoutStatusHtml = await this.RenderViewToStringAsync("_UserItemLockoutStatus", (x.Id, x.IsLocked)),
ActionsHtml = await this.RenderViewToStringAsync("_UserItemActions", (x.Id, x.IsLocked))
}).ToList();
var finalUsers = await Task.WhenAll(users);
return(new JsonResult(new DataTablesResult <UserListViewModel>
{
Draw = param.Draw,
Data = finalUsers.ToList(),
RecordsFiltered = result.TotalCount,
RecordsTotal = result.TotalCount
}));
}
public async Task <UsersResultDTO> GetUsersPagingListAsync(UsersQueryModel queryModel)
{
var filterModel = queryModel.FilterModel;
var tableParams = queryModel.Parameters;
IQueryable <ApplicationUser> query = _dbContext.Users.Include(x => x.UserProfile).Include(x => x.UserRoles);
query = query.Where(x => !x.IsSystemUser && x.UserProfile != null);
if (!string.IsNullOrEmpty(filterModel.FilterString))
{
query = query.Where(x => x.UserName.Contains(filterModel.FilterString) || x.PhoneNumber.Contains(filterModel.FilterString));
}
if (!string.IsNullOrEmpty(filterModel.Role))
{
var role = await _roleManager.FindByNameAsync(filterModel.Role);
if (role != null)
{
query = query.Where(x => x.UserRoles.Any(ur => ur.RoleId == role.Id));
}
}
if (filterModel.CenterId != null)
{
query = query.Where(x => x.UserProfile != null && x.UserProfile.CenterId == filterModel.CenterId);
}
if (filterModel.PersonId != null)
{
query = query.Where(x => x.UserProfile != null && x.UserProfile.PersonId == filterModel.PersonId);
}
if (tableParams.Order != null && tableParams.Order.Any())
{
var orderIndex = tableParams.Order[0].Column;
var orderDir = tableParams.Order[0].Dir;
if (orderIndex == 1)
{
query = orderDir == DataTablesOrderDir.DESC ? query.OrderByDescending(x => x.UserName) : query.OrderBy(x => x.UserName);
}
else if (orderIndex == 2)
{
query = orderDir == DataTablesOrderDir.DESC ? query.OrderByDescending(x => x.UserProfile.PersonId) : query.OrderBy(x => x.UserProfile.PersonId);
}
else if (orderIndex == 3)
{
query = orderDir == DataTablesOrderDir.DESC ? query.OrderByDescending(x => x.UserProfile.CenterId) : query.OrderBy(x => x.UserProfile.CenterId);
}
else if (orderIndex == 4)
{
query = orderDir == DataTablesOrderDir.DESC ? query.OrderByDescending(x => x.PhoneNumber) : query.OrderBy(x => x.PhoneNumber);
}
}
else
{
query = query.OrderByDescending(x => x.UserProfile.LoginAs);
}
var size = await query.CountAsync();
try
{
var users = await query.AsNoTracking().Skip((tableParams.Start / tableParams.Length) * tableParams.Length).Take(tableParams.Length).ToListAsync();
var finalUsers = new List <UserListItemDTO>();
foreach (var x in users)
{
var roleId = string.Empty;
var roleName = string.Empty;
if (x.UserRoles != null && x.UserRoles.Any())
{
var userRole = x.UserRoles.FirstOrDefault();
var role = await _roleManager.FindByIdAsync(userRole.RoleId);
if (role != null)
{
roleId = role.Id;
roleName = role.Name;
}
}
finalUsers.Add(new UserListItemDTO
{
Id = x.Id,
UserName = x.UserName,
Mobile = x.PhoneNumber,
LoginAs = x.UserProfile.LoginAs,
CenterId = x.UserProfile.CenterId,
PersonId = x.UserProfile.PersonId,
ServiceSupplyIds = x.UserProfile.ServiceSupplyIds,
Role = roleName,
RoleId = roleId,
IsLocked = x.LockoutEnabled && x.LockoutEnd.HasValue && x.LockoutEnd > DateTime.Now
});
}
return(new UsersResultDTO
{
Users = finalUsers,
TotalCount = size
});
}
catch (Exception)
{
return(new UsersResultDTO
{
Users = new List <UserListItemDTO>(),
TotalCount = size
});
}
}
public async Task <IActionResult> GetUsersPagingList([FromBody] UsersQueryModel queryModel)
{
var result = await _userRepository.GetUsersPagingListAsync(queryModel);
return(Ok(result));
}
private async Task GetUsuarios(SqlConnection connection1, SearchQueryModel model, List <UsersQueryModel> lst, bool isAdmin)
{
SqlCommand cmd = new SqlCommand();
string cmdString = "SELECT top " + rowsQuery.ToString() + " a.Id, a.Email, a.LockoutEnabled, a.LockoutEndDateUtc, isnull(u.firstname,''), isnull(u.lastname,'') " +
"FROM AspNetUsers as a " +
"left join users u on a.id = u.id ";
if (!isAdmin)
{
cmdString += "left join AspNetUserRoles ur on a.id = ur.UserId " +
"left join AspNetRoles r on ur.RoleId = r.id ";
}
if (model.status == 0)
{
cmdString += "where a.LockoutEnabled = 0 ";
}
else if (model.status == 1)
{
cmdString += "where a.LockoutEnabled = 1 ";
}
if (model.filter != "")
{
if (model.status == 2)
{
cmdString += "where a.Email like @search or u.firstname like @search or u.lastname like @search ";
}
else
{
cmdString += "and (a.Email like @search or u.firstname like @search or u.lastname like @search) ";
}
SqlParameter parameter = new SqlParameter("@search", SqlDbType.VarChar);
parameter.Value = "%" + model.filter + "%";
cmd.Parameters.Add(parameter);
}
if (!isAdmin)
{
cmdString += "and r.name is null ";
}
cmdString += "order by a.email";
cmd.CommandText = cmdString;
cmd.Connection = connection1;
SqlDataReader reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
UsersQueryModel userModel = new UsersQueryModel();
userModel.id = reader.GetString(0);
userModel.email = reader.GetString(1);
userModel.status = Convert.ToInt16(reader.GetBoolean(2));
if (!reader.IsDBNull(3))
{
reader.GetDateTime(3);
}
userModel.name = reader.GetString(4) + " ";
userModel.name += reader.GetString(5);
if (userModel.name.Trim() == "")
{
userModel.name = userModel.email;
}
lst.Add(userModel);
}
reader.Close();
}
