public static bool Save(User user)
{
bool result = false;
var conn = OracleDL.connect();
OracleTransaction txn = conn.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO SYSTEMUSERS(LASTNAME, OTHERNAMES, GENDER, EMAILADDRESS, USERNAME, PASSWORD, ROLEID, CREATEDON) VALUES(:lastname, :othernames, :gender, :email, :username, :password, :roleid, :createdon) RETURNING ID INTO :id";
cmd.Parameters.Add(":lastname", OracleDbType.Varchar2, user.LastName, ParameterDirection.Input);
cmd.Parameters.Add(":othernames", OracleDbType.Varchar2, user.Othernames, ParameterDirection.Input);
cmd.Parameters.Add(":gender", OracleDbType.Varchar2, user.Gender, ParameterDirection.Input);
cmd.Parameters.Add(":email", OracleDbType.Varchar2, user.Email, ParameterDirection.Input);
cmd.Parameters.Add(":username", OracleDbType.Varchar2, user.Username, ParameterDirection.Input);
cmd.Parameters.Add(":password", OracleDbType.Varchar2, user.Password, ParameterDirection.Input);
cmd.Parameters.Add(":roleid", OracleDbType.Int32, user.RoleId, ParameterDirection.Input);
cmd.Parameters.Add(":createdon", OracleDbType.Date, user.CreatedOn, ParameterDirection.Input);
OracleParameter outputParameter = new OracleParameter("id", OracleDbType.Int32);
outputParameter.Direction = ParameterDirection.Output;
cmd.Parameters.Add(outputParameter);
int rowsInserted = cmd.ExecuteNonQuery();
if (rowsInserted > 0)
{
var userId = Convert.ToInt32(outputParameter.Value.ToString());
OracleCommand command = conn.CreateCommand();
command.CommandText = "INSERT INTO USERDETAILS(ID1, VUSERNAME, VPASSWORD, VOFFICIALEMAIL) VALUES(:id, :username, :password, :email)";
command.Parameters.Add(":id", OracleDbType.Int32, userId, ParameterDirection.Input);
command.Parameters.Add(":username", OracleDbType.Varchar2, user.Username, ParameterDirection.Input);
command.Parameters.Add(":password", OracleDbType.Varchar2, user.Password, ParameterDirection.Input);
command.Parameters.Add(":email", OracleDbType.Varchar2, user.Email, ParameterDirection.Input);
rowsInserted = command.ExecuteNonQuery();
if (rowsInserted > 0)
{
Mail.SendNewUserMail(user);
result = true;
txn.Commit();
}
}
OracleDL.close(conn);
return result;
}
catch (Exception ex)
{
txn.Rollback();
throw ex;
}
}
public static bool Update(User user)
{
bool result = false;
var conn = OracleDL.connect();
OracleTransaction txn = conn.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = @"UPDATE SYSTEMUSERS SET
LASTNAME = :lastname, OTHERNAMES = :othernames, GENDER = :gender,
EMAILADDRESS = :email, ROLEID = :roleid
WHERE ID = :id";
cmd.Parameters.Add(":lastname", OracleDbType.Varchar2, user.LastName, ParameterDirection.Input);
cmd.Parameters.Add(":othernames", OracleDbType.Varchar2, user.Othernames, ParameterDirection.Input);
cmd.Parameters.Add(":gender", OracleDbType.Varchar2, user.Gender, ParameterDirection.Input);
cmd.Parameters.Add(":email", OracleDbType.Varchar2, user.Email, ParameterDirection.Input);
cmd.Parameters.Add(":roleid", OracleDbType.Int32, user.RoleId, ParameterDirection.Input);
cmd.Parameters.Add(":id", OracleDbType.Int32, user.Id, ParameterDirection.Input);
int rowsUpdated = cmd.ExecuteNonQuery();
if (rowsUpdated > 0)
{
cmd = conn.CreateCommand();
cmd.CommandText = @"UPDATE USERDETAILS SET
VOFFICIALEMAIL = :email
WHERE ID1 =:id";
cmd.Parameters.Add(":email", OracleDbType.Varchar2, user.Email, ParameterDirection.Input);
cmd.Parameters.Add(":id", OracleDbType.Int32, user.Id, ParameterDirection.Input);
rowsUpdated = cmd.ExecuteNonQuery();
if (rowsUpdated > 0)
{
result = true;
txn.Commit();
}
}
OracleDL.close(conn);
return result;
}
catch (Exception ex)
{
txn.Rollback();
throw ex;
}
}
public static Response RetrieveUserByUsername(User user, bool sendMail)
{
try
{
var authenticatedUser = UserDL.RetrieveUserByUsername(user);
if (authenticatedUser != null)
{
if (sendMail)
{
Mail.SendForgotPasswordMail(authenticatedUser);
}
return new Response
{
ErrorMsg = string.Empty,
DynamicList = new { data = authenticatedUser }
};
}
else
{
return new Response
{
ErrorMsg = "Invalid Username",
DynamicList = new { data = new User() }
};
}
}
catch (Exception ex)
{
ErrorHandler.WriteError(ex);
return new Response
{
SuccessMsg = string.Empty,
ErrorMsg = ex.Message,
DynamicList = new { data = new User() }
};
}
}
public static Response AuthenticateUser(User user)
{
try
{
var authenticatedUser = UserDL.AuthenticateUser(user);
if (authenticatedUser != null)
{
authenticatedUser.Function = FunctionDL.RetrieveByRoleId(authenticatedUser.UserRole.Id);
return new Response
{
ErrorMsg = string.Empty,
DynamicList = new { data = authenticatedUser }
};
}
else
{
return new Response
{
ErrorMsg = "Invalid Username/Password",
DynamicList = new { data = new User() }
};
}
}
catch (Exception ex)
{
ErrorHandler.WriteError(ex);
return new Response
{
SuccessMsg = string.Empty,
ErrorMsg = ex.Message,
DynamicList = new { data = new User() }
};
}
}
public static Response UpdatePassword(User user)
{
try
{
if (UserDL.UpdatePassword(user))
{
return new Response
{
SuccessMsg = "User password updated successfully",
ErrorMsg = string.Empty
};
}
else
{
return new Response
{
SuccessMsg = string.Empty,
ErrorMsg = "Operation failed"
};
}
}
catch (Exception ex)
{
ErrorHandler.WriteError(ex);
return new Response
{
SuccessMsg = string.Empty,
ErrorMsg = ex.Message
};
}
}
public static Response Save(User user, string username, bool overrideApproval)
{
try
{
if (UserDL.UserExists(user.Username))
{
return new Response
{
SuccessMsg = string.Empty,
ErrorMsg = string.Format("User with username {0} already exists.", user.Username)
};
}
else
{
if (!overrideApproval)
{
bool logForApproval = ApprovalConfigurationDL.RetrieveByType(StatusUtil.GetDescription(StatusUtil.ApprovalType.CreateUser)).Approve;
if (logForApproval)
{
Approval approvalObj = new Approval();
approvalObj.Type = StatusUtil.GetDescription(StatusUtil.ApprovalType.CreateUser);
approvalObj.Details = JsonConvert.SerializeObject(user);
approvalObj.Obj = JsonConvert.SerializeObject(user);
approvalObj.RequestedBy = username;
approvalObj.RequestedOn = System.DateTime.Now;
approvalObj.Status = StatusUtil.ApprovalStatus.Pending.ToString();
if (ApprovalDL.Save(approvalObj))
{
return new Response
{
SuccessMsg = "User successfully logged for approval",
ErrorMsg = string.Empty
};
}
else
{
return new Response
{
SuccessMsg = string.Empty,
ErrorMsg = "Operation failed"
};
}
}
else
{
if (UserDL.Save(user))
{
AuditTrail obj = new AuditTrail();
obj.Type = StatusUtil.GetDescription(StatusUtil.ApprovalType.CreateUser);
obj.Details = JsonConvert.SerializeObject(user);
obj.RequestedBy = username;
obj.RequestedOn = System.DateTime.Now;
obj.ApprovedBy = username;
obj.ApprovedOn = System.DateTime.Now;
AuditTrailDL.Save(obj);
return new Response
{
SuccessMsg = "User added successfully",
ErrorMsg = string.Empty
};
}
else
{
return new Response
{
SuccessMsg = string.Empty,
ErrorMsg = "Operation failed"
};
}
}
}
else
{
if (UserDL.Save(user))
{
return new Response
{
SuccessMsg = "User added successfully",
ErrorMsg = string.Empty
};
}
else
{
return new Response
{
SuccessMsg = string.Empty,
ErrorMsg = "Operation failed"
};
}
}
}
}
catch (Exception ex)
{
ErrorHandler.WriteError(ex);
return new Response
{
SuccessMsg = string.Empty,
ErrorMsg = ex.Message
};
}
}
public static void SendForgotPasswordMail(User user)
{
try
{
string key = System.Configuration.ConfigurationManager.AppSettings.Get("ekey");
string encrypted_username = Crypter.Encrypt(key, user.Username);
string userFullName = user.LastName + " " + user.Othernames;
string organization = System.Configuration.ConfigurationManager.AppSettings.Get("Organization");
string applicationName = System.Configuration.ConfigurationManager.AppSettings.Get("ApplicationName");
string websiteUrl = System.Configuration.ConfigurationManager.AppSettings.Get("WebsiteUrl");
string passwordResetUrl = websiteUrl + "User/ResetPassword?rq=" + encrypted_username; ;
string subject = "Password Reset Request on " + applicationName;
string fromAddress = "";
string smtpUsername = "";
string smtpPassword = "";
string smtpHost = "";
Int32 smtpPort = 587;
bool smtpUseDefaultCredentials = false;
bool smtpEnableSsl = true;
MailHelper mailConfig = ConfigurationManager.GetSection("mailHelperSection") as MailHelper;
if (mailConfig != null && mailConfig.Mail != null)
{
fromAddress = mailConfig.Mail.FromEmailAddress;
smtpUsername = mailConfig.Mail.Username;
smtpPassword = mailConfig.Mail.Password;
}
if (mailConfig != null && mailConfig.Smtp != null)
{
smtpHost = mailConfig.Smtp.Host;
smtpPort = Convert.ToInt32(mailConfig.Smtp.Port);
smtpUseDefaultCredentials = Convert.ToBoolean(mailConfig.Smtp.UseDefaultCredentials);
smtpEnableSsl = Convert.ToBoolean(mailConfig.Smtp.EnableSsl);
}
string body = "";
body = System.IO.File.ReadAllText(System.Web.Hosting.HostingEnvironment.MapPath(@"~/App_Data/MailTemplates/ForgotPassword.txt"));
body = body.Replace("#Organization", organization);
body = body.Replace("#ApplicationName", applicationName);
body = body.Replace("#UserFullName", userFullName);
body = body.Replace("#WebsiteUrl", websiteUrl);
body = body.Replace("#PasswordResetUrl", passwordResetUrl);
Thread email = new Thread(delegate()
{
Mail.SendMail(user.Email, fromAddress, subject, body, smtpHost, smtpPort, smtpUseDefaultCredentials, smtpUsername, smtpPassword, smtpEnableSsl);
});
email.IsBackground = true;
email.Start();
}
catch (Exception ex)
{
ErrorHandler.WriteError(ex);
throw ex;
}
}
public static User RetrieveUserByUsername(User user)
{
try
{
var users = new List<User>();
var conn = OracleDL.connect();
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = @"SELECT SU.*, SR.NAME AS ROLENAME
FROM SYSTEMUSERS SU
INNER JOIN SYSTEMROLES SR ON SU.ROLEID = SR.ID
WHERE SU.USERNAME = :username";
cmd.Parameters.Add(":username", OracleDbType.Varchar2, user.Username, ParameterDirection.Input);
OracleDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
users.Add(User.Transform(dr));
}
OracleDL.close(conn);
return users.Any() ? users.FirstOrDefault() : null;
}
catch (Exception ex)
{
throw ex;
}
}
public static bool UpdatePassword(User user)
{
bool result = false;
var conn = OracleDL.connect();
OracleTransaction txn = conn.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = @"UPDATE SYSTEMUSERS SET
PASSWORD = :password
WHERE USERNAME = :username";
cmd.Parameters.Add(":password", OracleDbType.Varchar2, user.Password, ParameterDirection.Input);
cmd.Parameters.Add(":username", OracleDbType.Varchar2, user.Username, ParameterDirection.Input);
int rowsUpdated = cmd.ExecuteNonQuery();
if (rowsUpdated > 0)
{
cmd = conn.CreateCommand();
cmd.CommandText = @"UPDATE USERDETAILS SET
VPASSWORD = :password
WHERE VUSERNAME =:username";
cmd.Parameters.Add(":password", OracleDbType.Varchar2, user.Password, ParameterDirection.Input);
cmd.Parameters.Add(":username", OracleDbType.Varchar2, user.Username, ParameterDirection.Input);
rowsUpdated = cmd.ExecuteNonQuery();
if (rowsUpdated > 0)
{
result = true;
txn.Commit();
}
}
OracleDL.close(conn);
return result;
}
catch (Exception ex)
{
txn.Rollback();
throw ex;
}
}
