public PortalLoginDto VerifyPortalLogin(string username, string password)
{
using (var connection = GetConnection())
{
connection.Open();
var command = @"
SELECT u.*
FROM [PortalLogins] u
WHERE u.IsValid=1 AND u.Username = @Username";
var login = connection.Query <PortalLogin>(command, new { Username = username }).FirstOrDefault();
if (login == null)
{
return(null);
}
var verifyResult = _passwordHasher.VerifyHashedPassword(login.PasswordHash, password);
if (!verifyResult)
{
return(null);
}
var dto = new PortalLoginDto()
{
Id = login.Id,
Username = login.Username,
IsPasswordToChange = login.IsPasswordToChange,
IsLocked = login.IsLocked,
CreatorId = login.CreatorId,
EditorId = login.EditorId,
CreationTime = login.CreationTime,
LastEditTime = login.LastEditTime,
IsValid = login.IsValid
};
return(dto);
}
}
public PortalLoginDto UpdatePortalLogin(string id, string username, string password, IEnumerable <string> roleIds, bool isPasswordToChange, bool isPasswordChanged, bool isLocked)
{
var columnsCommand = @"
SELECT l.Id, l.Username, l.PasswordHash, l.IsPasswordToChange, l.CreatorId, l.CreationTime, l.EditorId, l.LastEditTime, l.IsValid,
STUFF((
SELECT ','+r.Name
FROM PortalRoles r INNER JOIN PortalLoginRoles lr on lr.RoleId=r.Id
WHERE lr.LoginId=l.Id
FOR XML PATH('')
), 1, 1, '') as Roles";
var queryCommand = @"
FROM [PortalLogins] l
WHERE l.Id=@Id";
using (var connection = GetConnection())
{
connection.Open();
using (var transcation = connection.BeginTransaction())
{
try
{
PortalLoginDto dto = connection.Query <PortalLoginDto>($@"{columnsCommand} {queryCommand}", new { Id = id }, transcation).FirstOrDefault();
var login = new PortalLogin()
{
Id = id,
Username = username,
PasswordHash = isPasswordChanged ? _passwordHasher.HashPassword(password) : dto.PasswordHash,
IsPasswordToChange = dto.IsPasswordToChange ? dto.IsPasswordToChange : isPasswordToChange,
IsLocked = isLocked,
CreationTime = DateTime.UtcNow,
CreatorId = CurrentUserId,
EditorId = CurrentUserId,
LastEditTime = DateTime.UtcNow,
IsValid = true,
};
connection.Update(login, transcation);
var existingRoles = connection.Query <PortalLoginRole>(@"SELECT * FROM PortalLoginRoles WHERE LoginId=@Id",
new { Id = id }, transcation);
var roles = existingRoles.Select(r => r.RoleId);
var toAdd = roleIds.Where(r => !roles.Contains(r));
var toRemove = existingRoles.Where(r => !roleIds.Contains(r.RoleId));
foreach (var r in toAdd)
{
var role = new PortalLoginRole()
{
LoginId = login.Id,
RoleId = r
};
connection.Insert(role, transcation);
}
foreach (var r in toRemove)
{
connection.Delete(r, transcation);
}
dto = connection.Query <PortalLoginDto>($@"{columnsCommand} {queryCommand}", new { Id = id }, transcation).FirstOrDefault();
transcation.Commit();
return(dto);
}
catch (Exception e)
{
_logger.LogError(e.Message);
transcation.Rollback();
return(null);
}
}
}
}
