464 lines
18 KiB
C#
464 lines
18 KiB
C#
using Microsoft.EntityFrameworkCore;
|
|
using Modules.User.Application.Models;
|
|
using Modules.User.Application.Models.User;
|
|
using Modules.User.Application.Models.User.Session;
|
|
using Modules.User.Application.Repositories;
|
|
using Modules.User.Database.Database;
|
|
|
|
namespace Modules.User.Database.Queries;
|
|
|
|
public sealed class UserQueries(UserDbContext context) : IUserQueries
|
|
{
|
|
public async Task<UserInfoShort?> TryGetUserShortByAccountIdAsync(Guid? accountId, CancellationToken cancellationToken)
|
|
{
|
|
var now = DateTime.UtcNow;
|
|
return await context.Users
|
|
.AsNoTracking()
|
|
.Where(u => u.Account.Id == accountId) // фильтрация раньше
|
|
.Select(u => new UserInfoShort
|
|
{
|
|
Id = u.Id,
|
|
NickName = u.NickName,
|
|
FirstName = u.FirstName,
|
|
Patronymic = u.Patronymic,
|
|
LastName = u.LastName,
|
|
BirthDate = u.BirthDate,
|
|
|
|
LanguageId = u.RegionalSettings != null ? u.RegionalSettings.LanguageId : null,
|
|
Email = u.Account.Email,
|
|
HasAvatar = u.AvatarId != null,
|
|
|
|
LastOnline = u.Account.Sessions
|
|
.Max(s => (DateTime?)s.LastUpdate),
|
|
|
|
BanStatus = new BanStatus(
|
|
u.Account.Bans.Any(b => !b.Deleted && (b.ReleaseDate == null || b.ReleaseDate > now)),
|
|
u.Account.Bans
|
|
.Where(b => !b.Deleted && (b.ReleaseDate == null || b.ReleaseDate > now))
|
|
.Min(b => b.ReleaseDate)
|
|
),
|
|
RoleIds = u.Account.Roles
|
|
.Where(r => r.RevokedAtUtc == null)
|
|
.Select(q => q.RoleId)
|
|
})
|
|
.FirstOrDefaultAsync(cancellationToken);
|
|
}
|
|
|
|
public async Task<UserDetail?> GetUserDetailAsync(Guid userId, CancellationToken cancellationToken)
|
|
{
|
|
var now = DateTime.UtcNow;
|
|
// return await context.Users
|
|
// .AsNoTracking()
|
|
// .Where(u => u.Id
|
|
// && (!sessionId.HasValue || u.Account.Sessions.Any(s => s.Id == sessionId)))
|
|
// .Select(u => new UserDetail
|
|
// {
|
|
// Id = u.Id,
|
|
// AccountId = u.Account.Id,
|
|
// SessionId = sessionId,
|
|
// NickName = u.NickName,
|
|
// FirstName = u.FirstName,
|
|
// Patronymic = u.Patronymic,
|
|
// LastName = u.LastName,
|
|
// BirthDate = u.BirthDate,
|
|
// LanguageId = u.RegionalSettings != null ? u.RegionalSettings.LanguageId : null,
|
|
// Email = u.Account.Email,
|
|
// IsAuthenticated = true,
|
|
// ClientInfo = sessionId == null
|
|
// ? null
|
|
// : u.Account.Sessions
|
|
// .Where(s => s.Id == sessionId)
|
|
// .Select(s => new ClientInfo
|
|
// {
|
|
// UserAgent = s.ClientInfo.UserAgent,
|
|
// Location = new Location
|
|
// {
|
|
// Country = s.ClientInfo.Country,
|
|
// Region = s.ClientInfo.Region
|
|
// }
|
|
// })
|
|
// .FirstOrDefault(),
|
|
// BanStatus = u.Account.Bans
|
|
// .Where(b => !b.ReleaseDate.HasValue || b.ReleaseDate > now)
|
|
// .OrderBy(b => b.ReleaseDate)
|
|
// .Select(b => new BanStatus(true, b.ReleaseDate))
|
|
// .FirstOrDefault() ?? new BanStatus(false, null)
|
|
// })
|
|
// .FirstOrDefaultAsync(cancellationToken);
|
|
/*
|
|
var user = await context.Users
|
|
.AsNoTracking()
|
|
.Where(u => u.Id == userId)
|
|
.Select(u => new UserDetail
|
|
{
|
|
Id = u.Id,
|
|
AccountId = u.Account.Id,
|
|
NickName = u.NickName,
|
|
FirstName = u.FirstName,
|
|
Patronymic = u.Patronymic,
|
|
LastName = u.LastName,
|
|
BirthDate = u.BirthDate,
|
|
LanguageId = u.RegionalSettings != null ? u.RegionalSettings.LanguageId : null,
|
|
Email = u.Account.Email,
|
|
IsAuthenticated = true,
|
|
LastOnline = u.Account.Sessions
|
|
.Max(s => (DateTime?)s.LastUpdate),
|
|
BanStatus = u.Account.Bans
|
|
.Where(b => !b.ReleaseDate.HasValue || b.ReleaseDate > now)
|
|
.OrderBy(b => b.ReleaseDate)
|
|
.Select(b => new BanStatus(true, b.ReleaseDate))
|
|
.FirstOrDefault() ?? new BanStatus(false, null),
|
|
RoleActions = u.Account.Roles
|
|
.SelectMany(r => new[]
|
|
{
|
|
new RoleHistoryAction
|
|
{
|
|
Order = 0, // можно вычислить позже после сортировки
|
|
RoleId = r.RoleId,
|
|
ActorId = r.IssuerId,
|
|
ActorNickName = r.Issuer.User.NickName,
|
|
Reason = r.GrantReason,
|
|
ActionDate = r.GrantedAtUtc,
|
|
Action = RoleHistoryActonType.Assign
|
|
},
|
|
r.RevokedAtUtc != null
|
|
? new RoleHistoryAction
|
|
{
|
|
Order = 0,
|
|
RoleId = r.RoleId,
|
|
ActorId = r.RevokerId ?? Guid.Empty,
|
|
ActorNickName = r.Revoker != null
|
|
? r.Revoker.User.NickName
|
|
: string.Empty,
|
|
Reason = r.RevokeReason,
|
|
ActionDate = r.RevokedAtUtc.Value,
|
|
Action = RoleHistoryActonType.Revoke
|
|
}
|
|
: null
|
|
}.Where(x => x != null))
|
|
.Cast<RoleHistoryAction>().ToList(),
|
|
PermissionActions = u.Account.Permissions
|
|
.SelectMany(p => new[]
|
|
{
|
|
new PermissionHistoryActon
|
|
{
|
|
Order = 0,
|
|
PermissionId = p.PermissionId,
|
|
ActorId = p.IssuerId,
|
|
ActorNickName = p.Issuer.User.NickName,
|
|
Reason = p.GrantReason,
|
|
ActionDate = p.GrantedAtUtc,
|
|
Action = PermissionHistoryActonType.Grant
|
|
},
|
|
p.RevokedAtUtc != null
|
|
? new PermissionHistoryActon
|
|
{
|
|
Order = 0,
|
|
PermissionId = p.PermissionId,
|
|
ActorId = p.RevokerId ?? Guid.Empty,
|
|
ActorNickName = p.Revoker != null
|
|
? p.Revoker.User.NickName
|
|
: string.Empty,
|
|
Reason = p.RevokeReason,
|
|
ActionDate = p.RevokedAtUtc.Value,
|
|
Action = PermissionHistoryActonType.Revoke
|
|
}
|
|
: null
|
|
}.Where(x => x != null))
|
|
.Cast<PermissionHistoryActon>().ToList(),
|
|
})
|
|
.FirstOrDefaultAsync(cancellationToken);
|
|
|
|
if (user != null)
|
|
{
|
|
uint index = 1;
|
|
foreach (var role in user.RoleActions.OrderBy(a => a.ActionDate))
|
|
role.Order = index++;
|
|
|
|
index = 1;
|
|
foreach (var perm in user.PermissionActions.OrderBy(a => a.ActionDate))
|
|
perm.Order = index++;
|
|
|
|
}
|
|
return user;
|
|
*/
|
|
|
|
var user = await context.Users
|
|
.AsNoTracking()
|
|
.Where(u => u.Id == userId)
|
|
.Select(u => new
|
|
{
|
|
User = new UserDetail
|
|
{
|
|
Id = u.Id,
|
|
AccountId = u.Account.Id,
|
|
NickName = u.NickName,
|
|
FirstName = u.FirstName,
|
|
Patronymic = u.Patronymic,
|
|
LastName = u.LastName,
|
|
BirthDate = u.BirthDate,
|
|
LanguageId = u.RegionalSettings != null ? u.RegionalSettings.LanguageId : null,
|
|
Email = u.Account.Email,
|
|
IsAuthenticated = true,
|
|
LastOnline = u.Account.Sessions.Max(s => (DateTime?)s.LastUpdate),
|
|
BanStatus = u.Account.Bans
|
|
.Where(b => !b.ReleaseDate.HasValue || b.ReleaseDate > now)
|
|
.OrderBy(b => b.ReleaseDate)
|
|
.Select(b => new BanStatus(true, b.ReleaseDate))
|
|
.FirstOrDefault() ?? new BanStatus(false, null),
|
|
RoleActions = new List<RoleHistoryAction>(),
|
|
PermissionActions = new List<PermissionHistoryActon>()
|
|
},
|
|
Roles = u.Account.Roles
|
|
.Select(r => new
|
|
{
|
|
r.RoleId,
|
|
r.IssuerId,
|
|
IssuerNick = r.Issuer.User.NickName,
|
|
r.GrantReason,
|
|
r.GrantedAtUtc,
|
|
r.RevokedAtUtc,
|
|
r.RevokerId,
|
|
RevokerNick = r.Revoker != null ? r.Revoker.User.NickName : null,
|
|
r.RevokeReason
|
|
}).ToList(),
|
|
Perms = u.Account.Permissions
|
|
.Select(p => new
|
|
{
|
|
p.PermissionId,
|
|
p.IssuerId,
|
|
IssuerNick = p.Issuer.User.NickName,
|
|
p.GrantReason,
|
|
p.GrantedAtUtc,
|
|
p.RevokedAtUtc,
|
|
p.RevokerId,
|
|
RevokerNick = p.Revoker != null ? p.Revoker.User.NickName : null,
|
|
p.RevokeReason
|
|
}).ToList()
|
|
})
|
|
.FirstOrDefaultAsync(cancellationToken);
|
|
|
|
if (user == null) return null;
|
|
|
|
// Разворачиваем роли в память
|
|
foreach (var r in user.Roles)
|
|
{
|
|
user.User.RoleActions.Add(new RoleHistoryAction
|
|
{
|
|
RoleId = r.RoleId,
|
|
ActorId = r.IssuerId,
|
|
ActorNickName = r.IssuerNick,
|
|
Reason = r.GrantReason,
|
|
ActionDate = r.GrantedAtUtc,
|
|
Action = RoleHistoryActonType.Assign
|
|
});
|
|
|
|
if (r.RevokedAtUtc != null)
|
|
{
|
|
user.User.RoleActions.Add(new RoleHistoryAction
|
|
{
|
|
RoleId = r.RoleId,
|
|
ActorId = r.RevokerId ?? Guid.Empty,
|
|
ActorNickName = r.RevokerNick ?? string.Empty,
|
|
Reason = r.RevokeReason,
|
|
ActionDate = r.RevokedAtUtc.Value,
|
|
Action = RoleHistoryActonType.Revoke
|
|
});
|
|
}
|
|
}
|
|
|
|
// Разворачиваем права
|
|
foreach (var p in user.Perms)
|
|
{
|
|
user.User.PermissionActions.Add(new PermissionHistoryActon
|
|
{
|
|
PermissionId = p.PermissionId,
|
|
ActorId = p.IssuerId,
|
|
ActorNickName = p.IssuerNick,
|
|
Reason = p.GrantReason,
|
|
ActionDate = p.GrantedAtUtc,
|
|
Action = PermissionHistoryActonType.Grant
|
|
});
|
|
|
|
if (p.RevokedAtUtc != null)
|
|
{
|
|
user.User.PermissionActions.Add(new PermissionHistoryActon
|
|
{
|
|
PermissionId = p.PermissionId,
|
|
ActorId = p.RevokerId ?? Guid.Empty,
|
|
ActorNickName = p.RevokerNick ?? string.Empty,
|
|
Reason = p.RevokeReason,
|
|
ActionDate = p.RevokedAtUtc.Value,
|
|
Action = PermissionHistoryActonType.Revoke
|
|
});
|
|
}
|
|
}
|
|
|
|
// Проставляем Order
|
|
uint index = 1;
|
|
foreach (var role in user.User.RoleActions.OrderBy(a => a.ActionDate))
|
|
role.Order = index++;
|
|
|
|
index = 1;
|
|
foreach (var perm in user.User.PermissionActions.OrderBy(a => a.ActionDate))
|
|
perm.Order = index++;
|
|
|
|
return user.User;
|
|
|
|
}
|
|
|
|
public async Task<UserProfile?> GetUserProfileAsync(Guid userId, CancellationToken cancellationToken)
|
|
{
|
|
var now = DateTime.UtcNow;
|
|
|
|
return await context.Users
|
|
.AsNoTracking()
|
|
.Where(u => u.Id == userId)
|
|
.Select(u => new UserProfile
|
|
{
|
|
Id = u.Id,
|
|
NickName = u.NickName,
|
|
FirstName = u.FirstName,
|
|
Patronymic = u.Patronymic,
|
|
LastName = u.LastName,
|
|
Email = u.Account.Email,
|
|
BanStatus = u.Account.Bans
|
|
.Where(b => !b.ReleaseDate.HasValue || b.ReleaseDate > now)
|
|
.OrderBy(b => b.ReleaseDate)
|
|
.Select(b => new BanStatus(true, b.ReleaseDate))
|
|
.FirstOrDefault() ?? new BanStatus(false, null)
|
|
})
|
|
.FirstOrDefaultAsync(cancellationToken);
|
|
}
|
|
|
|
public async Task<List<Session>> GetAccountSessionsAsync(Guid accountId, Guid? sessionId, CancellationToken cancellationToken)
|
|
{
|
|
return await context.Sessions
|
|
.AsNoTracking()
|
|
.Where(s => s.AccountId == accountId)
|
|
.Select(s => new Session
|
|
{
|
|
Id = s.Id,
|
|
IsCurrentSession = s.Id == sessionId,
|
|
ClientInfo = new ClientInfo
|
|
{
|
|
UserAgent = s.ClientInfo.UserAgent,
|
|
Location = new Location
|
|
{
|
|
Country = s.ClientInfo.Country,
|
|
Region = s.ClientInfo.Region
|
|
}
|
|
},
|
|
LastOnline = s.LastUpdate,
|
|
ExpiredDate = s.ExpiredDate,
|
|
AccountId = s.AccountId
|
|
})
|
|
.ToListAsync(cancellationToken);
|
|
}
|
|
|
|
public async Task<string?> GetAvatarIdAsync(Guid userId, CancellationToken cancellationToken)
|
|
{
|
|
return await context.Users
|
|
.Where(q => q.Id == userId)
|
|
.Select(q => q.AvatarId)
|
|
.FirstOrDefaultAsync(cancellationToken);
|
|
}
|
|
|
|
public async Task<PagedData<UserInfoShort>> GetUsersAsync(UserListFilter? filter, CancellationToken cancellationToken)
|
|
{
|
|
var now = DateTime.UtcNow;
|
|
var query = context.Users
|
|
.AsNoTracking()
|
|
.Select(u => new
|
|
{
|
|
u.Id,
|
|
u.NickName,
|
|
u.FirstName,
|
|
u.Patronymic,
|
|
u.LastName,
|
|
u.BirthDate,
|
|
u.AvatarId,
|
|
LanguageId = u.RegionalSettings != null ? u.RegionalSettings.LanguageId : null,
|
|
u.Account.Email,
|
|
|
|
LastOnline = u.Account.Sessions
|
|
.Max(s => (DateTime?)s.LastUpdate),
|
|
|
|
IsBanned = u.Account.Bans
|
|
.Any(b => !b.Deleted && (b.ReleaseDate == null || b.ReleaseDate > now)),
|
|
ActiveBanReleaseDate = u.Account.Bans
|
|
.Where(b => !b.Deleted && (b.ReleaseDate == null || b.ReleaseDate > now))
|
|
.OrderBy(b => b.ReleaseDate == null ? DateTime.MaxValue : b.ReleaseDate.Value)
|
|
.ThenByDescending(b => b.BanDate)
|
|
.Select(b => b.ReleaseDate)
|
|
.FirstOrDefault(),
|
|
RoleIds = u.Account.Roles
|
|
.Where(r => r.RevokedAtUtc == null)
|
|
.Select(q => q.RoleId)
|
|
});
|
|
|
|
if (filter != null)
|
|
{
|
|
if (filter.HasAvatar.HasValue)
|
|
{
|
|
query = query.Where(q => !string.IsNullOrWhiteSpace(q.AvatarId));
|
|
}
|
|
|
|
if (!string.IsNullOrWhiteSpace(filter.NickName))
|
|
{
|
|
query = query.Where(q => q.NickName.ToLower().StartsWith(filter.NickName.ToLower()));
|
|
}
|
|
|
|
if (filter.LastOnlineFrom.HasValue)
|
|
{
|
|
query = query.Where(q => q.LastOnline >= filter.LastOnlineFrom);
|
|
}
|
|
if (filter.LastOnlineTo.HasValue)
|
|
{
|
|
query = query.Where(q => q.LastOnline <= filter.LastOnlineTo);
|
|
}
|
|
}
|
|
|
|
var count = await query.CountAsync(cancellationToken);
|
|
var take = filter?.ItemsOnPage > 0 ? filter.ItemsOnPage : 10;
|
|
var pagesCount = (int)Math.Ceiling((decimal)count / take);
|
|
var page = filter?.Page ?? 1;
|
|
if (page < 1) page = 1;
|
|
if (page > pagesCount) page = pagesCount > 0 ? pagesCount : 1;
|
|
var skip = (page - 1) * take;
|
|
var users = await query
|
|
.OrderByDescending(x => x.LastOnline)
|
|
.ThenBy(x => x.NickName)
|
|
.Skip(skip)
|
|
.Take(take)
|
|
.Select(q => new UserInfoShort()
|
|
{
|
|
Id = q.Id,
|
|
NickName = q.NickName,
|
|
FirstName = q.FirstName,
|
|
Patronymic = q.Patronymic,
|
|
LastName = q.LastName,
|
|
BirthDate = q.BirthDate,
|
|
|
|
LanguageId = q.LanguageId,
|
|
Email = q.Email,
|
|
HasAvatar = q.AvatarId != null,
|
|
|
|
// OnlineStatus = ,
|
|
LastOnline = q.LastOnline,
|
|
BanStatus = new BanStatus(q.IsBanned, q.ActiveBanReleaseDate),
|
|
RoleIds = q.RoleIds
|
|
})
|
|
.ToListAsync(cancellationToken);
|
|
|
|
return new PagedData<UserInfoShort>
|
|
{
|
|
Page = page,
|
|
ItemsOnPage = take,
|
|
ItemsCount = count,
|
|
PagesCount = pagesCount,
|
|
Items = users
|
|
};
|
|
}
|
|
}
|