/// <summary>
/// Returns a user by username
/// </summary>
/// <param name="username"></param>
/// <param name="includeSecurityData">
/// Can be used for slightly faster user lookups if the result doesn't require security data (i.e. groups, apps & start nodes).
/// This is really only used for a shim in order to upgrade to 7.6.
/// </param>
/// <returns>
/// A non cached <see cref="IUser"/> instance
/// </returns>
public IUser GetByUsername(string username, bool includeSecurityData)
{
UserDto dto;
if (includeSecurityData)
{
var sql = GetQueryWithGroups();
sql.Where <UserDto>(userDto => userDto.Login == username, SqlSyntax);
sql //must be included for relator to work
.OrderBy <UserDto>(d => d.Id, SqlSyntax)
.OrderBy <UserGroupDto>(d => d.Id, SqlSyntax)
.OrderBy <UserStartNodeDto>(d => d.Id, SqlSyntax);
dto = Database
.Fetch <UserDto, UserGroupDto, UserGroup2AppDto, UserStartNodeDto, UserDto>(
new UserGroupRelator().Map, sql)
.FirstOrDefault();
}
else
{
var sql = GetBaseQuery("umbracoUser.*");
sql.Where <UserDto>(userDto => userDto.Login == username, SqlSyntax);
dto = Database.FirstOrDefault <UserDto>(sql);
}
if (dto == null)
{
return(null);
}
var user = UserFactory.BuildEntity(dto);
return(user);
}
protected override IUser PerformGet(int id)
{
// This will never resolve to a user, yet this is asked
// for all of the time (especially in cases of members).
// Don't issue a SQL call for this, we know it will not exist.
if (id == default || id < -1)
{
return(null);
}
var sql = SqlContext.Sql()
.Select <UserDto>()
.From <UserDto>()
.Where <UserDto>(x => x.Id == id);
var dtos = Database.Fetch <UserDto>(sql);
if (dtos.Count == 0)
{
return(null);
}
PerformGetReferencedDtos(dtos);
return(UserFactory.BuildEntity(dtos[0]));
}
protected override IEnumerable <IUser> PerformGetAll(params int[] ids)
{
var dtos = ids.Length == 0
? GetDtosWith(null, true)
: GetDtosWith(sql => sql.WhereIn <UserDto>(x => x.Id, ids), true);
var users = new IUser[dtos.Count];
var i = 0;
foreach (var dto in dtos)
{
users[i++] = UserFactory.BuildEntity(dto);
}
return(users);
}
private IEnumerable <IUser> ConvertFromDtos(IEnumerable <UserDto> dtos)
{
var userTypeIds = dtos.Select(x => Convert.ToInt32(x.Type)).ToArray();
var allUserTypes = userTypeIds.Length == 0 ? Enumerable.Empty <IUserType>() : _userTypeRepository.GetAll(userTypeIds);
return(dtos.Select(dto =>
{
var userType = allUserTypes.Single(x => x.Id == dto.Type);
var userFactory = new UserFactory(userType);
return userFactory.BuildEntity(dto);
}));
}
protected override IEnumerable <IUser> PerformGetByQuery(IQuery <IUser> query)
{
var dtos = GetDtosWith(sql => new SqlTranslator <IUser>(sql, query).Translate(), true)
.DistinctBy(x => x.Id)
.ToList();
var users = new IUser[dtos.Count];
var i = 0;
foreach (var dto in dtos)
{
users[i++] = UserFactory.BuildEntity(dto);
}
return(users);
}
protected override IUser PerformGet(int id)
{
var sql = SqlContext.Sql()
.Select <UserDto>()
.From <UserDto>()
.Where <UserDto>(x => x.Id == id);
var dtos = Database.Fetch <UserDto>(sql);
if (dtos.Count == 0)
{
return(null);
}
PerformGetReferencedDtos(dtos);
return(UserFactory.BuildEntity(dtos[0]));
}
protected override IUser PerformGet(int id)
{
var sql = GetBaseQuery(false);
sql.Where(GetBaseWhereClause(), new { Id = id });
var dto = Database.Fetch <UserDto, User2AppDto, UserDto>(new UserSectionRelator().Map, sql).FirstOrDefault();
if (dto == null)
{
return(null);
}
var userType = _userTypeRepository.Get(dto.Type);
var userFactory = new UserFactory(userType);
var user = userFactory.BuildEntity(dto);
return(user);
}
protected override IUser PerformGet(int id)
{
var sql = GetQueryWithGroups();
sql.Where(GetBaseWhereClause(), new { Id = id });
sql //must be included for relator to work
.OrderBy <UserDto>(d => d.Id, SqlSyntax)
.OrderBy <UserGroupDto>(d => d.Id, SqlSyntax)
.OrderBy <UserStartNodeDto>(d => d.Id, SqlSyntax);
var dto = Database.Fetch <UserDto, UserGroupDto, UserGroup2AppDto, UserStartNodeDto, UserDto>(new UserGroupRelator().Map, sql)
.FirstOrDefault();
if (dto == null)
{
return(null);
}
var user = UserFactory.BuildEntity(dto);
return(user);
}
private IEnumerable <IUser> ConvertFromDtos(IEnumerable <UserDto> dtos)
{
var foundUserTypes = new Dictionary <short, IUserType>();
return(dtos.Select(dto =>
{
//first we need to get the user type
IUserType userType;
if (foundUserTypes.ContainsKey(dto.Type))
{
userType = foundUserTypes[dto.Type];
}
else
{
userType = _userTypeRepository.Get(dto.Type);
//put it in the local cache
foundUserTypes.Add(dto.Type, userType);
}
var userFactory = new UserFactory(userType);
return userFactory.BuildEntity(dto);
}));
}
protected override IUser PerformGet(int id)
{
// This will never resolve to a user, yet this is asked
// for all of the time (especially in cases of members).
// Don't issue a SQL call for this, we know it will not exist.
if (_runtimeState.Level == RuntimeLevel.Upgrade)
{
// when upgrading people might come from version 7 where user 0 was the default,
// only in upgrade mode do we want to fetch the user of Id 0
if (id < -1)
{
return(null);
}
}
else
{
if (id == default || id < -1)
{
return(null);
}
}
var sql = SqlContext.Sql()
.Select <UserDto>()
.From <UserDto>()
.Where <UserDto>(x => x.Id == id);
var dtos = Database.Fetch <UserDto>(sql);
if (dtos.Count == 0)
{
return(null);
}
PerformGetReferencedDtos(dtos);
return(UserFactory.BuildEntity(_globalSettings, dtos[0]));
}
private IUser GetWith(Action <Sql <ISqlContext> > with, bool includeReferences)
{
var dto = GetDtoWith(with, includeReferences);
return(dto == null ? null : UserFactory.BuildEntity(dto));
}
/// <summary>
/// Gets paged user results
/// </summary>
/// <param name="query"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="totalRecords"></param>
/// <param name="orderBy"></param>
/// <param name="orderDirection"></param>
/// <param name="includeUserGroups">
/// A filter to only include user that belong to these user groups
/// </param>
/// <param name="excludeUserGroups">
/// A filter to only include users that do not belong to these user groups
/// </param>
/// <param name="userState">Optional parameter to filter by specified user state</param>
/// <param name="filter"></param>
/// <returns></returns>
/// <remarks>
/// The query supplied will ONLY work with data specifically on the umbracoUser table because we are using NPoco paging (SQL paging)
/// </remarks>
public IEnumerable <IUser> GetPagedResultsByQuery(IQuery <IUser> query, long pageIndex, int pageSize, out long totalRecords,
Expression <Func <IUser, object> > orderBy, Direction orderDirection = Direction.Ascending,
string[] includeUserGroups = null, string[] excludeUserGroups = null, UserState[] userState = null, IQuery <IUser> filter = null)
{
if (orderBy == null)
{
throw new ArgumentNullException(nameof(orderBy));
}
Sql <ISqlContext> filterSql = null;
var customFilterWheres = filter?.GetWhereClauses().ToArray();
var hasCustomFilter = customFilterWheres != null && customFilterWheres.Length > 0;
if (hasCustomFilter ||
includeUserGroups != null && includeUserGroups.Length > 0 ||
excludeUserGroups != null && excludeUserGroups.Length > 0 ||
userState != null && userState.Length > 0 && userState.Contains(UserState.All) == false)
{
filterSql = SqlContext.Sql();
}
if (hasCustomFilter)
{
foreach (var clause in customFilterWheres)
{
filterSql.Append($"AND ({clause.Item1})", clause.Item2);
}
}
if (includeUserGroups != null && includeUserGroups.Length > 0)
{
const string subQuery = @"AND (umbracoUser.id IN (SELECT DISTINCT umbracoUser.id
FROM umbracoUser
INNER JOIN umbracoUser2UserGroup ON umbracoUser2UserGroup.userId = umbracoUser.id
INNER JOIN umbracoUserGroup ON umbracoUserGroup.id = umbracoUser2UserGroup.userGroupId
WHERE umbracoUserGroup.userGroupAlias IN (@userGroups)))";
filterSql.Append(subQuery, new { userGroups = includeUserGroups });
}
if (excludeUserGroups != null && excludeUserGroups.Length > 0)
{
const string subQuery = @"AND (umbracoUser.id NOT IN (SELECT DISTINCT umbracoUser.id
FROM umbracoUser
INNER JOIN umbracoUser2UserGroup ON umbracoUser2UserGroup.userId = umbracoUser.id
INNER JOIN umbracoUserGroup ON umbracoUserGroup.id = umbracoUser2UserGroup.userGroupId
WHERE umbracoUserGroup.userGroupAlias IN (@userGroups)))";
filterSql.Append(subQuery, new { userGroups = excludeUserGroups });
}
if (userState != null && userState.Length > 0)
{
//the "ALL" state doesn't require any filtering so we ignore that, if it exists in the list we don't do any filtering
if (userState.Contains(UserState.All) == false)
{
var sb = new StringBuilder("(");
var appended = false;
if (userState.Contains(UserState.Active))
{
sb.Append("(userDisabled = 0 AND userNoConsole = 0 AND lastLoginDate IS NOT NULL)");
appended = true;
}
if (userState.Contains(UserState.Inactive))
{
if (appended)
{
sb.Append(" OR ");
}
sb.Append("(userDisabled = 0 AND userNoConsole = 0 AND lastLoginDate IS NULL)");
appended = true;
}
if (userState.Contains(UserState.Disabled))
{
if (appended)
{
sb.Append(" OR ");
}
sb.Append("(userDisabled = 1)");
appended = true;
}
if (userState.Contains(UserState.LockedOut))
{
if (appended)
{
sb.Append(" OR ");
}
sb.Append("(userNoConsole = 1)");
appended = true;
}
if (userState.Contains(UserState.Invited))
{
if (appended)
{
sb.Append(" OR ");
}
sb.Append("(lastLoginDate IS NULL AND userDisabled = 1 AND invitedDate IS NOT NULL)");
appended = true;
}
sb.Append(")");
filterSql.Append("AND " + sb);
}
}
// create base query
var sql = SqlContext.Sql()
.Select <UserDto>()
.From <UserDto>();
// apply query
if (query != null)
{
sql = new SqlTranslator <IUser>(sql, query).Translate();
}
// get sorted and filtered sql
var sqlNodeIdsWithSort = ApplySort(ApplyFilter(sql, filterSql, query != null), orderBy, orderDirection);
// get a page of results and total count
var pagedResult = Database.Page <UserDto>(pageIndex + 1, pageSize, sqlNodeIdsWithSort);
totalRecords = Convert.ToInt32(pagedResult.TotalItems);
// map references
PerformGetReferencedDtos(pagedResult.Items);
return(pagedResult.Items.Select(x => UserFactory.BuildEntity(_globalSettings, x)));
}
private IEnumerable <IUser> ConvertFromDtos(IEnumerable <UserDto> dtos)
{
return(dtos.Select(x => UserFactory.BuildEntity(_globalSettings, x)));
}