public async Task <IEnumerable <UserRoleInDepartment> > Get(UserRoleInDepartmentGetOptions options)
{
try
{
StringBuilder sql = new StringBuilder();
_logger.LogInformation("Try to create get users roles in departments sql query");
sql.AppendLine(@"
select
urid.Id,
urid.RoleInDepartmentId,
urid.UserId,
rid.RoleId,
rid.DepartmentId,
d.Type as DepartmentType,
r.Name as RoleName,
d.FullName as DepartmentName,
(u.Firstname + ' ' + coalesce(u.Secondname + ' ', ' ') + u.Lastname) as UserFullName
from [UserRoleInDepartment] urid
left join [RoleInDepartment] rid on urid.RoleInDepartmentId = rid.Id
left join [User] u on urid.UserId = u.Id
left join [Role] r on rid.RoleId = r.Id
left join [Department] d on rid.DepartmentId = d.Id
");
int conditionIndex = 0;
if (options.RoleIds != null)
{
sql.AppendLine($"{(conditionIndex++ == 0 ? "where" : "and")} (rid.RoleId in @RoleIds)");
}
if (options.DepartmentId.HasValue)
{
sql.AppendLine($"{(conditionIndex++ == 0 ? "where" : "and")} (rid.DepartmentId = @DepartmentId)");
}
if (options.DepartmentIds != null)
{
sql.AppendLine($"{(conditionIndex++ == 0 ? "where" : "and")} (rid.DepartmentId in @DepartmentIds)");
}
if (options.UserIds != null)
{
sql.AppendLine($"{(conditionIndex++ == 0 ? "where" : "and")} (urid.UserId in @UserIds)");
}
_logger.LogInformation($"Sql query successfully created:\n{sql.ToString()}");
_logger.LogInformation("Try to execute sql get users roles in departments query");
var result = await QueryAsync <UserRoleInDepartment>(sql.ToString(), options);
_logger.LogInformation("Sql get users roles in departments query successfully executed");
return(result);
}
catch (Exception exception)
{
_logger.LogError(exception.Message);
throw exception;
}
}
public async Task <IEnumerable <UserRoleInDepartment> > Get(UserRoleInDepartmentGetOptions options) => await _dao.Get(options);
