public async Task <IEnumerable <AccountSingleRole> > GetsAsync(int?partnerId = null)
{
string cmd = $@"SELECT u.*, u_r.* FROM `user_account` u
LEFT JOIN `user_account_role` u_r
ON u_r.user_account_id = u.id AND u_r.role_id = (
SELECT MIN(u_r_2.role_id) role_id
FROM `user_account_role` u_r_2
WHERE u_r_2.user_account_id = u.id
)
WHERE u.`is_used` = 1 AND u.`is_deleted` = 0";
if ((partnerId ?? 0) > 0)
{
cmd += $" AND u.`partner_id`='{partnerId}'";
}
var conn = DbConnection;
if (conn == null)
{
conn = DALHelper.GetConnection();
}
try
{
using (var reader = await conn.QueryMultipleAsync(cmd, transaction: DbTransaction))
{
return(reader.Read <Account, AccountRole, AccountSingleRole>(
(accountRs, accountRoleRs) =>
{
AccountSingleRole account = null;
if (accountRs != null)
{
account = accountRs.ToSingleRole();
}
else
{
account = new AccountSingleRole();
}
if (accountRoleRs != null)
{
account.RoleId = accountRoleRs.RoleId;
}
return account;
}
));
}
}
catch (Exception)
{
throw;
}
finally
{
if (DbConnection == null)
{
conn.Dispose();
}
}
}
public async Task <AccountSingleRole> GetByIdAsync(int id)
{
string cmd = $@"SELECT u.*, u_r.* FROM `user_account` u
LEFT JOIN `user_account_role` u_r
ON u_r.user_account_id = u.id AND u_r.role_id = (
SELECT MIN(u_r_2.role_id) role_id
FROM `user_account_role` u_r_2
WHERE u_r_2.user_account_id = u.id
)
WHERE u.`is_deleted` = 0 AND u.`id` = {id}";
var conn = DbConnection;
if (conn == null)
{
conn = DALHelper.GetConnection();
}
try
{
using (var reader = await conn.QueryMultipleAsync(cmd, transaction: DbTransaction))
{
return(reader.Read <Account, AccountRole, AccountSingleRole>(
(accountRs, accountRoleRs) =>
{
AccountSingleRole account = null;
if (accountRs != null)
{
account = accountRs.ToSingleRole();
}
else
{
account = new AccountSingleRole();
}
if (accountRoleRs != null)
{
account.RoleId = accountRoleRs.RoleId;
}
return account;
}
).FirstOrDefault());
}
}
catch (Exception)
{
throw;
}
finally
{
if (DbConnection == null)
{
conn.Dispose();
}
}
}
public override async Task <int> HandleCommand(UpdateAccountPartnerCommand request, CancellationToken cancellationToken)
{
var company = await _companyQueries.GetByUserIdAsync(request.LoginSession.Id);
if (company == null)
{
throw new BusinessException("Common.NoPermission");
}
AccountSingleRole account = null;
if (request.Model == null || request.Model.Id == 0)
{
throw new BusinessException("Account.NotExisted");
}
else
{
account = await _accountQueries.GetByIdAsync(request.Model.Id);
if (account == null)
{
throw new BusinessException("Account.NotExisted");
}
else if (account.PartnerId != company.Id)
{
throw new BusinessException("Common.NoPermission");
}
}
if ((await _roleQueries.GetByIdAsync(request.Model.RoleId ?? 0)) == null)
{
throw new BusinessException("Role.NotSelected");
}
var rs = -1;
using (var conn = DALHelper.GetConnection())
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
try
{
account.UserName = request.Model.UserName;
account.Email = request.Model.Email;
account.IsActived = request.Model.IsActived;
account.IsExternalUser = request.Model.IsExternalUser;
account.IsUsed = request.Model.IsUsed;
account.ModifiedDate = DateTime.Now;
account.ModifiedBy = request.LoginSession.Id;
if (!string.IsNullOrWhiteSpace(request.Model.NewPassword))
{
account.Password = (request.Model.NewPassword + account.SecurityPassword.ToString()).CalculateMD5Hash();
}
if (await _accountRepository.UpdateAsync(account) > 0)
{
rs = 0;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (rs == 0)
{
trans.Commit();
}
else
{
try { trans.Rollback(); } catch { }
}
}
}
}
return(rs);
}
public UpdateAccountPartnerCommand(AccountSingleRole account)
{
Model = account;
}
public InsertAccountCommand(AccountSingleRole account)
{
Model = account;
}
public override async Task <int> HandleCommand(UpdateAccountCommand request, CancellationToken cancellationToken)
{
AccountSingleRole account = null;
if (request.Model == null || request.Model.Id == 0)
{
throw new BusinessException("Account.NotExisted");
}
else
{
account = await accountQueries.GetByIdAsync(request.Model.Id);
if (account == null)
{
throw new BusinessException("Account.NotExisted");
}
}
if ((await roleQueries.GetByIdAsync(request.Model.RoleId ?? 0)) == null)
{
throw new BusinessException("Role.NotSelected");
}
var rs = -1;
using (var conn = DALHelper.GetConnection())
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
try
{
if (account.RoleId != request.Model.RoleId)
{
var accRoles = (await accountRoleQueries.GetsAsync($"user_account_id = {request.Model.Id}")).ToList();
for (int i = 0; i < accRoles.Count; i++)
{
await accountRoleRepository.DeleteAsync(accRoles[i].Id);
}
await accountRoleRepository.AddAsync(new AccountRole()
{
RoleId = request.Model.RoleId ?? 0,
UserAccountId = account.Id,
CreatedDate = DateTime.Now,
});
}
request.Model.ModifiedDate = DateTime.Now;
request.Model.ModifiedBy = request.LoginSession.Id;
request.Model.IsDeleted = account.IsDeleted;
request.Model.SecurityPassword = account.SecurityPassword;
if (!string.IsNullOrWhiteSpace(request.Model.NewPassword))
{
request.Model.Password = (request.Model.NewPassword + request.Model.SecurityPassword.ToString()).CalculateMD5Hash();
}
else
{
request.Model.Password = account.Password;
}
if (await accountRepository.UpdateAsync((Account)request.Model) > 0)
{
rs = 0;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (rs == 0)
{
trans.Commit();
}
else
{
try { trans.Rollback(); } catch { }
}
}
}
}
return(rs);
}
