public async Task <int> InsertAsync(Claim claim)
{
var query = $@"INSERT INTO `Vanlune`.`Claim`
(`name`,
`type`,
`value`)
VALUES
(@{nameof(Claim.Name)},
@{nameof(Claim.ClaimType)},
@{nameof(Claim.ClaimValue)});
SELECT LAST_INSERT_ID();";
using var connection = _mySqlConnHelper.MySqlConnection();
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
var result = await connection.QueryAsync <int>(query, new
{
claim.Name,
claim.ClaimType,
claim.ClaimValue
});
return(result.Single());
}
public async Task <int> AddAsync(Order order)
{
var query = $@"INSERT INTO `Vanlune`.`Orders`
(accountId,
`productId`,
`quantity`,
`price`,
`amount`,
`idStatus`,
`payment`,
`variant`,
`discount`
`paymentStatus`,
`externalId`)
VALUES
(@accountId,
@productId,
@{nameof(Order.Quantity)},
@{nameof(Order.Price)},
@{nameof(Order.Amount)},
@{nameof(Order.Status)},
@{nameof(Order.Payment)},
@{nameof(Order.Variant)},
@{nameof(Order.Discount)},
@{nameof(Order.PaymentStatus)},
@{nameof(Order.ExternalId)});
SELECT LAST_INSERT_ID();";
using var connection = _mySqlConnHelper.MySqlConnection();
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
var result = await connection.QueryAsync <int>(query, new
{
accountId = order.User.Id,
productId = order.Product.Id,
order.Quantity,
order.Price,
order.Amount,
order.Status,
order.Payment,
Variant = JsonConvert.SerializeObject(order.Variant),
order.Discount,
order.PaymentStatus,
order.ExternalId
});
return(result.Single());
}
public async Task AddUsersRole(int[] usersId, int roleId)
{
using var connection = _mySqlConnHelper.MySqlConnection();
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
var transaction = connection.BeginTransaction();
try
{
foreach (var userId in usersId)
{
var query = $@"INSERT INTO `Vanlune`.`RoleUser`
(`idUser`,
`idRoles`)
SELECT * FROM
(SELECT @idUser,@idRoles) AS tmp
WHERE NOT EXISTS (
SELECT idUser,idRoles
FROM Vanlune.RoleUser
WHERE idUser=@idUser
AND idRoles=@idRoles
);";
await connection.ExecuteAsync(query, new
{
idUser = userId,
idRoles = roleId
});
}
transaction.Commit();
}
catch
{
transaction.Rollback();
}
}
public async Task AddRoleClaims(int roleId, int[] claims)
{
using var connection = _mySqlConnHelper.MySqlConnection();
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
var transaction = connection.BeginTransaction();
try
{
foreach (var claimId in claims)
{
var query = $@"INSERT INTO `Vanlune`.`ClaimRole`
(`idClaim`,
`idRole`)
VALUES
(@claimId,
@roleId);";
await connection.ExecuteAsync(query, new
{
claimId,
roleId
});
}
transaction.Commit();
}
catch (Exception e)
{
_logger.Error($"Error {e.Message} at {e.StackTrace}");
transaction.Rollback();
}
}
public async Task <User> GetAccount(int id)
{
var query = $@"{QueryBase}
WHERE A.`id`= @id";
using var connection = _mySqlConnHelper.MySqlConnection();
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
User currUser = default;
var result = await connection.QueryAsync <User, Role, Claim, User>(query,
(user, role, claim) =>
{
if (currUser == null)
{
currUser = user;
}
if (currUser.Roles.Any(x => x.Role.Id.Equals(role.Id)))
{
role = currUser.Roles.Where(x => x.Role.Id.Equals(role.Id)).FirstOrDefault().Role;
}
if (role != null)
{
if (claim != null)
{
role.AddClaimToRole(claim);
}
currUser.AddRoles(new List <Role>()
{
role
});
}
return(user);
},
new
{
id
}, splitOn : $"{nameof(User.Id)},{nameof(Role.Id)},{nameof(Claim.Id)}");
return(currUser);
}
public async Task <int> InsertAsync(Role role)
{
var query = $@"INSERT INTO `Vanlune`.`Roles`
(`name`)
VALUES
(@{nameof(Role.Name)});
SELECT LAST_INSERT_ID();";
using var connection = _mySqlConnHelper.MySqlConnection();
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
var result = await connection.QueryAsync <int>(query, new
{
role.Name
});
return(result.Single());
}
