public async Task<string> ValidateUser(UserGetOptions options)
{
try
{
_logger.LogInformation("Start user name and email validating.");
string result = ValidationUtilities.ValidateUserName(options.Username);
if (!string.IsNullOrEmpty(result))
return result;
result = ValidationUtilities.ValidateEmail(options.Email);
if (!string.IsNullOrEmpty(result))
return result;
var users = await _dao.Get(options);
if (users.Where(o => o.Username != options.Username).Count() > 0)
{
string message = "User with same user name or email have been already created. Please try another or try to sign in.";
_logger.LogInformation(message);
return message;
}
_logger.LogInformation("User name and email successfuly validated.");
return null;
}
catch (Exception exception)
{
_logger.LogError(exception.Message);
throw exception;
}
}
public async Task <IEnumerable <User> > Get(UserGetOptions options)
{
try
{
StringBuilder sql = new StringBuilder();
_logger.LogInformation("Try to create get users sql query");
sql.AppendLine($@"
select
{"\"Id\""},
{"\"Firstname\""},
{"\"Lastname\""},
{"\"Email\""},
{"\"Gender\""},
{"\"Age\""}
from {"\"Users\""}
");
int conditionIndex = 0;
if (options.Id.HasValue)
{
sql.AppendLine($"{(conditionIndex++ == 0 ? "where" : "and")} \"Id\" = @Id");
}
if (options.Ids != null)
{
sql.AppendLine($"{(conditionIndex++ == 0 ? "where" : "and")} \"Id\" = any(@Ids)");
}
if (!string.IsNullOrEmpty(options.NormalizedSearch))
{
sql.AppendLine($@"
{(conditionIndex++ == 0 ? "where" : "and")} lower({"\"Firstname\""}) like lower(@NormalizedSearch)
or lower({"\"Lastname\""}) like lower(@NormalizedSearch)
or lower({"\"Email\""}) like lower(@NormalizedSearch)
");
}
_logger.LogInformation($"Sql query successfully created:\n{sql.ToString()}");
_logger.LogInformation("Try to execute sql get users query");
var result = await QueryAsync <User>(sql.ToString(), options);
_logger.LogInformation("Sql get users query successfully executed");
return(result);
}
catch (Exception exception)
{
_logger.LogError(exception.Message);
throw exception;
}
}
public async Task<IEnumerable<User>> Get(UserGetOptions options)
{
var users = await _dao.Get(options);
var usersIds = users.Select(o => o.Id).ToList();
var usersPinnedDisciplines = await _pinnedDisciplineService.Get(new PinnedDisciplineGetOptions { UsersIds = usersIds });
var usersGraduateDegrees = await _userGraduateDegreeService.Get(new UserGraduateDegreeGetOptions { UsersIds = usersIds });
foreach (var user in users)
user.PinnedDisciplines = usersPinnedDisciplines.Where(o => o.UserId == user.Id).ToList();
foreach (var user in users)
user.GraduateDegrees = usersGraduateDegrees.Where(o => o.UserId == user.Id).ToList();
return users;
}
public async Task GetTest(string search)
{
UserGetOptions options = new UserGetOptions
{
Search = search
};
// Arrange
var mock = new Mock <IUserService>();
mock.Setup(service => service.Get(options)).Returns(GetMock(options));
var controller = new UserController(mock.Object);
// Act
var actionResult = await controller.Get(options);
var result = actionResult as OkObjectResult;
// Assert
Assert.IsInstanceOfType(actionResult, typeof(ActionResult));
Assert.IsInstanceOfType(result, typeof(OkObjectResult));
Assert.IsInstanceOfType(result.Value, typeof(IEnumerable <User>));
}
private Task <IEnumerable <User> > GetMock(UserGetOptions options)
{
IEnumerable <User> result = users;
if (!string.IsNullOrEmpty(options.Email))
{
result = result.Where(o => o.Email == options.Email);
}
if (options.Id.HasValue)
{
result = result.Where(o => o.Id == options.Id);
}
if (options.Ids != null && options.Ids.Count > 0)
{
result = result.Where(o => options.Ids.Contains(o.Id));
}
if (options.OnlyConfirmed)
{
result = result.Where(o => o.Confirmed);
}
if (!string.IsNullOrEmpty(options.Search))
{
result = result.Where(o => o.UserName.ToLower().Contains(options.Search));
}
if (!string.IsNullOrEmpty(options.UserName))
{
result = result.Where(o => o.UserName == options.UserName);
}
return(Task.FromResult(result));
}
public async Task <IEnumerable <User> > Get(UserGetOptions options)
{
try
{
StringBuilder sql = new StringBuilder();
_logger.LogInformation("Try to create get users sql query");
sql.AppendLine(@"
select
Id,
UserName,
FirstName,
LastName,
Email,
ConfirmCode,
Role,
DateCreated,
DateUpdated
from [User]
");
int conditionIndex = 0;
if (options.Id.HasValue)
{
sql.AppendLine($"{(conditionIndex++ == 0 ? "where" : "and")} Id = @Id");
}
if (options.Role.HasValue)
{
sql.AppendLine($"{(conditionIndex++ == 0 ? "where" : "and")} Role = @Role");
}
if (options.Ids != null)
{
sql.AppendLine($"{(conditionIndex++ == 0 ? "where" : "and")} id = any(@Ids)");
}
if (!string.IsNullOrEmpty(options.Search))
{
sql.AppendLine($@"
{(conditionIndex++ == 0 ? "where" : "and")} FirstName like '%@Search%'
or LastName like '%Search%'
or Email like '%Search%'
or UserName like '%Search%'
");
}
if (options.OnlyConfirmed)
{
sql.AppendLine($"{(conditionIndex++ == 0 ? "where" : "and")} ConfirmCode is null");
}
if (!string.IsNullOrEmpty(options.UserName))
{
sql.AppendLine($"{(conditionIndex++ == 0 ? "where" : "and")} UserName = @UserName");
}
if (!string.IsNullOrEmpty(options.Email))
{
sql.AppendLine($"{(conditionIndex++ == 0 ? "where" : "and")} Email = @Email");
}
_logger.LogInformation($"Sql query successfully created:\n{sql.ToString()}");
_logger.LogInformation("Try to execute sql get users query");
var result = await QueryAsync <User>(sql.ToString(), options);
_logger.LogInformation("Sql get users query successfully executed");
return(result);
}
catch (Exception exception)
{
_logger.LogError(exception.Message);
throw exception;
}
}
public async Task <IActionResult> Get([FromQuery] UserGetOptions options)
{
return(Ok(await _userService.Get(options)));
}
public async Task <IEnumerable <User> > Get(UserGetOptions options) => await _dao.Get(options);
public async Task <IEnumerable <User> > Get(UserGetOptions options)
{
try
{
StringBuilder sql = new StringBuilder();
_logger.LogInformation("Try to create get users sql query");
sql.AppendLine(@"
select distinct
u.[Id]
, u.[Username]
, u.[Firstname]
, u.[Secondname]
, u.[Lastname]
, u.[Email]
from [User] u
");
if (options.OnlyWithPinnedDisciplines.HasValue && options.OnlyWithPinnedDisciplines.Value)
{
sql.AppendLine("join [PinnedDiscipline] pd on pd.UserId = u.Id");
}
if (options.CanTeach.HasValue || options.DepartmentId.HasValue)// && options.CanTeach.Value)
{
sql.AppendLine("join [UserRoleInDepartment] urid on urid.UserId = u.Id");
if (options.DepartmentId.HasValue)
{
sql.AppendLine("join [RoleInDepartment] rid on rid.Id = urid.RoleInDepartmentId and rid.DepartmentId = @DepartmentId");
}
else
{
sql.AppendLine("join [RoleInDepartment] rid on rid.Id = urid.RoleInDepartmentId");
}
if (options.CanTeach.HasValue)
{
sql.AppendLine("join [Role] r on r.Id = rid.RoleId and r.CanTeach = 1");
}
// sql.AppendLine(@"
// join [UserRoleInDepartment] urid on urid.UserId = u.Id
// join [RoleInDepartment] rid on rid.Id = urid.RoleInDepartmentId
// join [Role] r on r.Id = rid.RoleId and r.CanTeach = 1
// ");
}
int conditionIndex = 0;
if (options.Id.HasValue)
{
sql.AppendLine($"{(conditionIndex++ == 0 ? "where" : "and")} [Id] = @id");
}
if (options.Ids != null && options.Ids.Count > 0)
{
sql.AppendLine($"{(conditionIndex++ == 0 ? "where" : "and")} [Id] in @ids");
}
if (!string.IsNullOrEmpty(options.NormalizedSearch))
{
sql.AppendLine($@"
{(conditionIndex++ == 0 ? "where" : "and")} lower([Firstname]) like lower(@NormalizedSearch)
or lower([Secondname]) like lower(@NormalizedSearch)
or lower([Lastname]) like lower(@NormalizedSearch)
or lower([Email]) like lower(@NormalizedSearch)
or lower([Username]) like lower(@NormalizedSearch)
");
}
if (!string.IsNullOrEmpty(options.Username))
{
sql.AppendLine($"{(conditionIndex++ == 0 ? "where" : "and")} [Username] = @username");
}
if (!string.IsNullOrEmpty(options.Email))
{
sql.AppendLine($"{(conditionIndex++ == 0 ? "where" : "and")} [Email] = @email");
}
sql.AppendLine("order by u.Lastname, u.Firstname, u.Secondname");
_logger.LogInformation($"Sql query successfully created:\n{sql.ToString()}");
_logger.LogInformation("Try to execute sql get users query");
var result = await QueryAsync <User>(sql.ToString(), options);
_logger.LogInformation("Sql get users query successfully executed");
return(result);
}
catch (Exception exception)
{
_logger.LogError(exception.Message);
throw exception;
}
}