/// <summary>
/// Método para atualizar o token refresh guardado na BD
/// </summary>
/// <param name="refreshToken">Refesh token</param>
/// <param name="email">Email do utilizador</param>
/// <returns>Retorna True se for atualizado,
/// False caso contrário</returns>
public bool updateEncryptedRefreshToken(string refreshToken, string email){
try{
using (SqlCommand cmd = _connection.Fetch().CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "UPDATE dbo.[RefreshTokens] SET TokenHash = @Th, TokenSalt = @Ts " +
"WHERE Email = @Em";
cmd.Parameters.Add("@Em", SqlDbType.NVarChar).Value = email;
var token = PasswordOperations.Encrypt(refreshToken);
cmd.Parameters.Add("@Th", SqlDbType.NVarChar).Value = token.Item2;
cmd.Parameters.Add("@Ts", SqlDbType.NVarChar).Value = token.Item1;
if(cmd.ExecuteNonQuery() == 0){
return false;
}
return true;
}
} catch(Exception e) {
throw new Exception(e.Message);
}
}
/// <summary>
/// Método para guardar o token refresh encriptado na BD
/// </summary>
/// <param name="refreshToken">Refesh token</param>
/// <param name="email">Email do utilizador</param>
/// <returns>Retorna True se for guardado,
/// False caso contrário</returns>
public bool saveEncryptedRefreshToken(string refreshToken, string email){
UserDAO userDAO = new UserDAO(_connection);
User user = userDAO.FindUserByEmail(email);
if(user == null){
throw new Exception("O utilizador não existe");
}
try{
using (SqlCommand cmd = _connection.Fetch().CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO dbo.[RefreshTokens] (Email, TokenHash, TokenSalt) " +
"VALUES (@Em , @Th, @Ts)";
cmd.Parameters.Add("@Em", SqlDbType.NVarChar).Value = email;
var token = PasswordOperations.Encrypt(refreshToken);
cmd.Parameters.Add("@Th", SqlDbType.NVarChar).Value = token.Item2;
cmd.Parameters.Add("@Ts", SqlDbType.NVarChar).Value = token.Item1;
if(cmd.ExecuteNonQuery() == 0){
return false;
}
return true;
}
} catch(Exception e) {
throw new Exception(e.Message);
}
}
/// <summary>
/// Método para criar um Employer
/// na Base de dados
/// </summary>
/// <param name="model">Modelo do employer com os dados</param>
/// <returns>Employer caso seja adicionado com sucesso,
/// senão retorna NULL</returns>
public Employer Create(Employer model)
{
try
{
using (SqlCommand cmd = _connection.Fetch().CreateCommand())
{
cmd.CommandType = CommandType.Text;
if (model.Description != null)
{
cmd.CommandText = "INSERT INTO dbo.[User] (UserName, Password, PasswordSalt, Email, Description, FirstName, LastName, RoleId, Address)" +
"VALUES (@Un, @Pass, @Slt, @mail, @Desc, @Fn, @Ln, @Rid, @Addr); SELECT @@Identity";
cmd.Parameters.Add("@Desc", SqlDbType.NVarChar).Value = model.Description;
}
else
{
cmd.CommandText = "INSERT INTO dbo.[User] (UserName, Password, PasswordSalt, Email, FirstName, LastName, RoleId, Address)" +
"VALUES (@Un, @Pass, @Slt, @mail, @Fn, @Ln, @Rid, @Addr); SELECT @@Identity";
}
cmd.Parameters.Add("@Un", SqlDbType.NVarChar).Value = model.UserName;
var password = PasswordOperations.Encrypt(model.Password);
cmd.Parameters.Add("@Pass", SqlDbType.NVarChar).Value = password.Item2;
cmd.Parameters.Add("@Slt", SqlDbType.NVarChar).Value = password.Item1;
cmd.Parameters.Add("@mail", SqlDbType.NVarChar).Value = model.Email;
cmd.Parameters.Add("@Fn", SqlDbType.NVarChar).Value = model.FirstName;
cmd.Parameters.Add("@Ln", SqlDbType.NVarChar).Value = model.LastName;
cmd.Parameters.Add("@Rid", SqlDbType.Int).Value = Roles.EMPLOYER;
cmd.Parameters.Add("@Addr", SqlDbType.NVarChar).Value = model.Address;
model.Id = int.Parse(cmd.ExecuteScalar().ToString());
model.Role = Roles.EMPLOYER;
model.Password = password.Item2;
model.PasswordSalt = password.Item1;
}
using (SqlCommand cmd = _connection.Fetch().CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO dbo.[Employer] (Id)" +
"VALUES (@Id); SELECT @@Identity";
cmd.Parameters.Add("@Id", SqlDbType.Int).Value = model.Id;
cmd.ExecuteScalar();
}
return(model);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// Método para recuperar a password
/// </summary>
/// <param name="newPass">Nova Password</param>
/// <param name="email">Email do user que vai alterar a password</param>
/// <returns>Retorna bool</returns>
public bool RecoverPassword(RecoverPasswordModel newPass, string email)
{
using (SqlCommand cmd = _connection.Fetch().CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "UPDATE dbo.[User] SET Password = @pass, PasswordSalt = @salt " +
"WHERE Email = @email";
cmd.Parameters.Add("@email", SqlDbType.NVarChar).Value = email;
var password = PasswordOperations.Encrypt(newPass.Password);
cmd.Parameters.Add("@pass", SqlDbType.NVarChar).Value = password.Item2;
cmd.Parameters.Add("@salt", SqlDbType.NVarChar).Value = password.Item1;
newPass.Password = password.Item2;
cmd.ExecuteNonQuery();
}
return(true);
}
/// <summary>
/// Método que atualiza a password
/// de um determindado utilizador
/// </summary>
/// <param name="newPass">Nova palavra-passe</param>
/// <param name="id">Id do utilizador que pretende alterar
/// a sua palavra-passe</param>
/// <returns>
/// True caso a password seja atualizada com sucesso
/// False caso contrário
/// </returns>
public bool UpdatePassword(PasswordUpdateModel newPass, int?id)
{
User user = FindById((int)id);
if (user == null)
{
throw new Exception("O utilizador não existe!");
}
if (PasswordOperations.VerifyHash(newPass.OldPassword, user.Password, user.PasswordSalt))
{
using (SqlCommand cmd = _connection.Fetch().CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "UPDATE dbo.[User] " +
"SET Password = @pass, PasswordSalt = @salt " +
"WHERE Id = @id";
cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
var password = PasswordOperations.Encrypt(newPass.Password);
cmd.Parameters.Add("@pass", SqlDbType.NVarChar).Value = password.Item2;
cmd.Parameters.Add("@salt", SqlDbType.NVarChar).Value = password.Item1;
if (cmd.ExecuteNonQuery() == 0)
{
return(false);
}
}
return(true);
}
else
{
throw new Exception("A password antiga é inválida!");
}
}
/// <summary>
/// Método para criar/registar um Mate na base de dados
/// </summary>
/// <param name="model">Modelo do mate com os dados</param>
/// <returns>Mate caso seja adicionado com sucesso,
/// senão retorna NULL</returns>
public Mate Create(Mate model)
{
try
{
using (SqlCommand cmd = _connection.Fetch().CreateCommand())
{
cmd.CommandType = CommandType.Text;
if (model.Description != null)
{
cmd.CommandText = "INSERT INTO dbo.[User] (UserName, Password, PasswordSalt, Email, Description, FirstName, LastName, RoleId, Address)" +
"VALUES (@Un, @Pass, @Slt, @mail, @Desc, @Fn, @Ln, @Rid, @Addr); SELECT @@Identity";
cmd.Parameters.Add("@Desc", SqlDbType.NVarChar).Value = model.Description;
}
else
{
cmd.CommandText = "INSERT INTO dbo.[User] (UserName, Password, PasswordSalt, Email, FirstName, LastName, RoleId, Address)" +
"VALUES (@Un, @Pass, @Slt, @mail, @Fn, @Ln, @Rid, @Addr); SELECT @@Identity";
}
cmd.Parameters.Add("@Un", SqlDbType.NVarChar).Value = model.UserName;
var password = PasswordOperations.Encrypt(model.Password);
cmd.Parameters.Add("@Pass", SqlDbType.NVarChar).Value = password.Item2;
cmd.Parameters.Add("@Slt", SqlDbType.NVarChar).Value = password.Item1;
cmd.Parameters.Add("@mail", SqlDbType.NVarChar).Value = model.Email;
cmd.Parameters.Add("@Fn", SqlDbType.NVarChar).Value = model.FirstName;
cmd.Parameters.Add("@Ln", SqlDbType.NVarChar).Value = model.LastName;
cmd.Parameters.Add("@Rid", SqlDbType.Int).Value = Roles.M8;
cmd.Parameters.Add("@Addr", SqlDbType.NVarChar).Value = model.Address;
model.Id = int.Parse(cmd.ExecuteScalar().ToString());
model.Role = Roles.M8;
model.Password = password.Item2;
model.PasswordSalt = password.Item1;
model.Rank = Ranks.MATE;
}
using (SqlCommand cmd = _connection.Fetch().CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO dbo.[Mate] (Id, Range, RankId)" +
"VALUES (@Id, @Rng, @Rkid); SELECT @@Identity";
cmd.Parameters.Add("@Id", SqlDbType.Int).Value = model.Id;
cmd.Parameters.Add("@Rng", SqlDbType.Int).Value = model.Range;
cmd.Parameters.Add("Rkid", SqlDbType.Int).Value = model.Rank;
cmd.ExecuteScalar();
}
foreach (Categories category in model.Categories)
{
using (SqlCommand cmd = _connection.Fetch().CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO dbo.[CategoriesFromM8] (MateId, CategoryId)" +
"VALUES (@MtId, @CtgId); SELECT @@Identity";
cmd.Parameters.Add("@MtId", SqlDbType.Int).Value = model.Id;
cmd.Parameters.Add("@CtgId", SqlDbType.Int).Value = category;
cmd.ExecuteScalar();
}
}
return(model);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}