public bool ConfirmAccount(string userName, string verifyCode, EAuthenticationField authenticationField)
{
try
{
if (string.IsNullOrEmpty(userName))
{
throw new ArgumentException("Cuenta inválida");
}
string fieldName = string.Empty, fieldVerifyCode = string.Empty;
if (authenticationField == EAuthenticationField.USERNAME)
{
fieldName = "UserName";
}
if (authenticationField == EAuthenticationField.EMAIL)
{
fieldName = "Email";
fieldVerifyCode = string.Format(" AND u.VerifyCode = '{0}'", verifyCode);
}
int id = 0;
string query = string.Format("SELECT u.Id, u.IsEnabled FROM user u INNER JOIN user_role ur ON ur.User_Id = u.Id INNER JOIN role r ON r.Id = ur.Role_Id INNER JOIN application a ON a.Id = r.Application_Id WHERE u.{0} = @pUserName AND a.Name = @pAppName{1};", fieldName, fieldVerifyCode);
using (IDbCommand command = BuildCommand(query, new string[] { "@pUserName", "@pAppName" }, new DbType[] { DbType.String, DbType.String }, new object[] { userName, authTokenConfig.AppName }))
{
using (IDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
if (reader.GetBoolean(1))
{
throw new ArgumentException("La cuenta ya se encuentra activa");
}
else
{
id = reader.GetInt32(0);
}
}
else
{
throw new ArgumentException("No se pudo validar la cuenta");
}
}
}
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = string.Format("UPDATE user SET IsEnabled = 1 WHERE Id = {0};", id);
return(command.ExecuteNonQuery() > 0);
}
}
catch
{
throw;
}
}
public bool IsEnabledAccount(string userName, EAuthenticationField authenticationField)
{
try
{
bool isEnabled = false;
string fieldName = string.Empty;
if (authenticationField == EAuthenticationField.USERNAME)
{
fieldName = "UserName";
}
if (authenticationField == EAuthenticationField.EMAIL)
{
fieldName = "Email";
}
string query = string.Format("SELECT u.IsEnabled FROM user u INNER JOIN user_role ur ON ur.User_Id = u.Id INNER JOIN role r ON r.Id = ur.Role_Id INNER JOIN application a ON a.Id = r.Application_Id WHERE u.{0} = @pUserName AND a.Name = @pAppName;", fieldName);
using (IDbCommand command = BuildCommand(query, new string[] { "@pUserName", "@pAppName" }, new DbType[] { DbType.String, DbType.String }, new object[] { userName, authTokenConfig.AppName }))
{
using (IDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
isEnabled = reader.GetBoolean(0);
}
else
{
throw new ArgumentException("No existe la cuenta");
}
}
}
return(isEnabled);
}
catch
{
throw;
}
}
public bool DenyUserRole(string userName, string roleName, EAuthenticationField authenticationField)
{
try
{
if (string.IsNullOrEmpty(roleName))
{
throw new ArgumentException("Rol inválido");
}
string fieldName = string.Empty;
if (authenticationField == EAuthenticationField.USERNAME)
{
fieldName = "UserName";
}
if (authenticationField == EAuthenticationField.EMAIL)
{
fieldName = "Email";
}
User user = null;
string query = string.Format("SELECT u.Id, u.FirstName, u.LastName, u.UserName, u.IsEnabled, u.Email FROM user u INNER JOIN user_role ur ON ur.User_Id = u.Id INNER JOIN role r ON r.Id = ur.Role_Id INNER JOIN application a ON a.Id = r.Application_Id WHERE r.IsEnabled AND u.IsEnabled AND a.IsEnabled AND a.Name = @pAppName AND u.{0} = @pUserName;", fieldName);
using (IDbCommand command = BuildCommand(query, new string[] { "@pUserName", "@pAppName" }, new DbType[] { DbType.String, DbType.String }, new object[] { roleName, authTokenConfig.AppName }))
{
using (IDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
user = new User();
user.Id = reader.GetInt32(0);
if (!reader.IsDBNull(1))
{
user.FirstName = reader.GetString(1);
}
if (!reader.IsDBNull(2))
{
user.LastName = reader.GetString(2);
}
if (!reader.IsDBNull(3))
{
user.UserName = reader.GetString(3);
}
user.IsEnabled = reader.GetBoolean(4);
if (!reader.IsDBNull(5))
{
user.Email = reader.GetString(5);
}
}
else
{
throw new ArgumentException("Usuario inválido");
}
}
}
Role role = null;
query = string.Format("SELECT r.Id, r.Name, r.IsEnabled FROM role r INNER JOIN application a ON a.Id = r.Application_Id WHERE r.IsEnabled AND a.IsEnabled AND r.Name = @pRole AND a.Name = @pAppName;");
using (IDbCommand command = BuildCommand(query, new string[] { "@pRole", "@pAppName" }, new DbType[] { DbType.String, DbType.String }, new object[] { roleName, authTokenConfig.AppName }))
{
using (IDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
role = new Role();
role.Id = reader.GetInt32(0);
role.Name = reader.GetString(1);
role.IsEnabled = reader.GetBoolean(2);
}
else
{
throw new ArgumentException("Rol no disponible");
}
}
}
query = "DELETE FROM user_role WHERE User_Id = @pUserId AND Role_Id = @pRoleId;";
using (IDbCommand command = BuildCommand(query, new string[] { "@pUserId", "@pRoleId" }, new DbType[] { DbType.Int32, DbType.Int32 }, new object[] { user.Id, role.Id }))
{
return(command.ExecuteNonQuery() > 0);
}
}
catch
{
throw;
}
}
public string LogIn(string userName, string password, EAuthenticationField authenticationField)
{
string token = null;
try
{
User user = null;
IList <Claim> roles = new List <Claim>();
string query = string.Format("SELECT u.Id, u.FirstName, u.LastName, u.UserName, u.IsEnabled, u.Email FROM user u INNER JOIN user_role ur ON ur.User_Id = u.Id INNER JOIN role r ON r.Id = ur.Role_Id INNER JOIN application a ON a.Id = r.Application_Id WHERE u.IsEnabled AND u.{0} = @pUserName AND u.Password = @pPassword AND a.Name = @pAppName;", (authenticationField == EAuthenticationField.EMAIL ? "Email" : "UserName"));
using (IDbCommand command = BuildCommand(query, new string[] { "@pUserName", "@pPassword", "@pAppName" }, new DbType[] { DbType.String, DbType.String, DbType.String }, new object[] { userName, password, authTokenConfig.AppName }))
{
using (IDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
user = new User();
user.Id = reader.GetInt32(0);
user.FirstName = reader.GetString(1);
user.LastName = reader.GetString(2);
if (!reader.IsDBNull(3))
{
user.UserName = reader.GetString(3);
}
user.IsEnabled = reader.GetBoolean(4);
if (!reader.IsDBNull(5))
{
user.Email = reader.GetString(5);
}
}
}
}
if (user != null)
{
if (!user.IsEnabled)
{
throw new ArgumentException("La cuenta existe pero no está validada o habilitada");
}
query = "SELECT r.Name FROM User_Role ur INNER JOIN Role r ON r.Id = ur.Role_Id INNER JOIN Application a ON a.Id = r.Application_Id WHERE ur.User_Id = @pId AND a.Name = @pAppName;";
using (IDbCommand command = BuildCommand(query, new string[] { "@pId", "@pAppName" }, new DbType[] { DbType.Int32, DbType.String }, new object[] { user.Id, authTokenConfig.AppName }))
{
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string role = reader.GetString(0);
roles.Add(new Claim(ClaimTypes.Role, role));
}
}
}
string secretTokenKey = authTokenConfig.SecretTokenKey;
byte[] secretKey = Encoding.ASCII.GetBytes(secretTokenKey);
string validIssuer = authTokenConfig.ValidIssuer;
string validAudience = authTokenConfig.ValidAudience;
double tokenExpirationMinutes = authTokenConfig.TokenExpirationMinutes;
roles.Add(new Claim("User", user.UserName));
roles.Add(new Claim(ClaimTypes.Name, (authenticationField == EAuthenticationField.EMAIL ? user.Email : user.UserName)));
roles.Add(new Claim("UserName", user.FirstName + " " + user.LastName));
roles.Add(new Claim(ClaimTypes.Email, user.Email));
roles.Add(new Claim(ClaimTypes.System, authTokenConfig.AppName));
roles.Add(new Claim(JwtHeaderParameterNames.Kid, Guid.NewGuid().ToString()));
var jwToken = new JwtSecurityToken(
issuer: validIssuer,
audience: validAudience,
claims: roles,
notBefore: new DateTimeOffset(DateTime.Now).DateTime,
expires: new DateTimeOffset(DateTime.Now.AddMinutes(tokenExpirationMinutes)).DateTime,
signingCredentials: new SigningCredentials(new SymmetricSecurityKey(secretKey), SecurityAlgorithms.HmacSha256Signature)
);
token = new JwtSecurityTokenHandler().WriteToken(jwToken);
}
return(token);
}
catch
{
throw;
}
}
public bool SigIn(string firstName, string lastName, string password, string userName, string email, bool isEnabled, EAuthenticationField authenticationField, IList <string> roles, string verifyCode)
{
transaction = null;
try
{
transaction = connection.BeginTransaction();
if (string.IsNullOrEmpty(firstName))
{
throw new ArgumentException("Se requiere un nombre");
}
if (string.IsNullOrEmpty(lastName))
{
throw new ArgumentException("Se requiere un apellido");
}
if (string.IsNullOrEmpty(password))
{
throw new ArgumentException("Se requiere una contraseña");
}
if (authenticationField == EAuthenticationField.USERNAME && string.IsNullOrEmpty(userName))
{
throw new ArgumentException("Se requiere un nombre de usuario");
}
if (authenticationField == EAuthenticationField.EMAIL && string.IsNullOrEmpty(email))
{
throw new ArgumentException("Se requiere una dirección de correo electrónico");
}
string fieldName = string.Empty;
string user = string.Empty;
if (authenticationField == EAuthenticationField.EMAIL)
{
fieldName = "Email";
user = email;
}
if (authenticationField == EAuthenticationField.USERNAME)
{
fieldName = "UserName";
user = userName;
}
string query = string.Format("SELECT u.Id FROM user u INNER JOIN user_role ur ON ur.User_Id = u.Id INNER JOIN role r ON r.Id = ur.Role_Id INNER JOIN application a ON a.Id = r.Application_Id WHERE u.{0} = @pUserName AND a.Name = @pAppName;", fieldName);
using (IDbCommand command = BuildCommand(query, new string[] { "@pUserName", "@pAppName" }, new DbType[] { DbType.String, DbType.String }, new object[] { user, authTokenConfig.AppName }))
{
using (IDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
throw new ArgumentException("Ya existe una cuenta de usuario con estos datos");
}
}
}
object userId = 0;
query = "INSERT INTO user(FirstName, LastName, UserName, Password, IsEnabled, Email, VerifyCode) VALUES (@pFirstName, @pLastName, @pUserName, @pPassword, @pIsEnabled, @pEmail, @pVerifyCode);SELECT LAST_INSERT_ID();";
using (IDbCommand command = BuildCommand(query,
new string[] { "@pFirstName", "@pLastName", "@pUserName", "@pPassword", "@pIsEnabled", "@pEmail", "@pVerifyCode" },
new DbType[] { DbType.String, DbType.String, DbType.String, DbType.String, DbType.Boolean, DbType.String, DbType.String },
new object[] { firstName, lastName, userName, password, isEnabled, email, verifyCode }))
{
command.Transaction = transaction;
userId = command.ExecuteScalar();
}
if (userId != null)
{
if (roles != null && roles.Count > 0)
{
IList <int> roleIds = new List <int>();
query = "SELECT r.Id FROM role r INNER JOIN application a ON a.Id = r.Application_Id WHERE a.Name = @pAppName AND r.Name IN (";
foreach (string role in roles)
{
query += "'" + role + "', ";
}
query += "-";
query = query.Replace(", -", ");");
using (IDbCommand command = BuildCommand(query, new string[] { "@pAppName" }, new DbType[] { DbType.String }, new object[] { authTokenConfig.AppName }))
{
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
roleIds.Add(reader.GetInt32(0));
}
}
}
if (roles.Count != roleIds.Count)
{
throw new Exception("Roles inválidos para el usuario. Aplicación: " + authTokenConfig.AppName);
}
query = "INSERT INTO user_role(User_Id, Role_Id) VALUES ";
foreach (int roleId in roleIds)
{
query += "(" + userId + ", " + roleId + "), ";
}
query += "-";
query = query.Replace(", -", ";");
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = query;
command.Transaction = transaction;
command.ExecuteNonQuery();
}
}
else
{
throw new Exception("No se especificaron Roles para el usuario. Aplicación: " + authTokenConfig.AppName);
}
transaction.Commit();
return(true);
}
else
{
transaction.Rollback();
return(false);
}
}
catch
{
if (transaction != null)
{
transaction.Rollback();
transaction.Dispose();
}
throw;
}
}
