public bool GetLoginUser(sec_UserRepo userrepo)
{
try
{
var param = new DynamicParameters();
string security_Code = "[email protected]";
userrepo.Password = GlobalValue.AES_Encrypt(userrepo.Password, security_Code);
param.Add("P_USER_ID", userrepo.User_Id, DbType.String, ParameterDirection.Input);
param.Add("P_PASSWORD", userrepo.Password, DbType.String, ParameterDirection.Input);
param.Add("VDATA", null, DbType.Int32, ParameterDirection.Output);
db.GetConnection().Execute("LOGIN_PROCEDURES.SEL_LOGIN_USER", param, commandType: CommandType.StoredProcedure);
int paramoption = param.Get <int>("VDATA");
if (paramoption <= 0)
{
return(false);
}
else
{
return(true);
}
}
catch (Exception ex)
{
throw ex;
}
}
public bool CreateNewUser(sec_UserRepo user)
{
try
{
//Get connectoin
var app = new AppSettings();
con = app.GetConnection();
var param = new DynamicParameters();
string password_hash = cSecurityRepo.AES_Encrypt(user.Password);
param.Add(name: "p_UserId", value: user.User_Id, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add(name: "p_Password", value: password_hash, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add(name: "p_EmployeeId", value: user.Employee_Id, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add(name: "p_FullName", value: user.Employee_Name, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add(name: "p_UserGroupId", value: user.User_Group_Id, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add(name: "p_Email", value: user.Email, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add(name: "p_MobileNo", value: user.MobileNo, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add(name: "p_PhoneNo", value: user.PhoneNo, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add(name: "p_UserStatus", value: "PENDING", dbType: DbType.String, direction: ParameterDirection.Input);
param.Add(name: "p_AuthStatus", value: "PENDING", dbType: DbType.String, direction: ParameterDirection.Input);
param.Add(name: "p_MakeId", value: GlobalValue.User_ID, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add(name: "p_MakeDate", value: GlobalValue.Scheme_Today_Date, dbType: DbType.Date, direction: ParameterDirection.Input);
param.Add(name: "p_AuthId", value: GlobalValue.User_ID, dbType: DbType.String, direction: ParameterDirection.Input);
int result = con.Execute(sql: "add_sec_User", param: param, commandType: CommandType.StoredProcedure);
if (result != 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
if (con != null)
{
con = null;
}
}
}
}
public void AddUserLoginSession(sec_UserRepo userrepo)
{
try
{
var param = new DynamicParameters();
param.Add("P_USER_ID", userrepo.User_Id, DbType.String, ParameterDirection.Input);
db.GetConnection().Execute("LOGIN_PROCEDURES.ADD_USER_TO_LOGIN_SESSION", param, commandType: CommandType.StoredProcedure);
}
catch (Exception ex)
{
throw ex;
}
}
public void CleanLoginFailureCount(sec_UserRepo userrepo)
{
try
{
var param = new DynamicParameters();
param.Add("P_USER_ID", userrepo.User_Id, DbType.String, ParameterDirection.Input);
db.GetConnection().Execute("LOGIN_PROCEDURES.REMOVE_USER_FAILURE_COUNT", param, commandType: CommandType.StoredProcedure);
}
catch (Exception ex)
{
throw ex;
}
}
public List <sec_UserRepo> GetUser(sec_UserRepo loginmodel)
{
try
{
DataSet ds = new DataSet();
OracleDataAdapter da = new OracleDataAdapter();
OracleCommand cmd = new OracleCommand();
cmd.CommandText = "LOGIN_PROCEDURES.SEL_USER_DATA";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = (OracleConnection)db.GetConnection();
OracleParameter User_name = new OracleParameter("P_USER_ID", OracleDbType.Varchar2, loginmodel.User_Id, ParameterDirection.Input);
cmd.Parameters.Add(User_name);
OracleParameter param2 = new OracleParameter("P_USER_DATA", OracleDbType.RefCursor, ParameterDirection.Output);
cmd.Parameters.Add(param2);
da = new OracleDataAdapter(cmd);
da.Fill(ds, "user");
var eList = ds.Tables[0].AsEnumerable().Select(row => new sec_UserRepo
{
User_Id = row.Field <string>("User_Id"),
//Employee_Id = row.Field<string>("Employee_Id"),
//User_Group_Id = row.Field<string>("User_Group_Id"),
//User_Status = row.Field<string>("User_Status"),
//Password_Expiry_Date = row.Field<DateTime?>("Password_Expiry_Date"),
//Password_Failure_Count = row.Field<int>("Password_Failure_Count"),
//Token_Number = row.Field<string>("Token_Number"),
}).ToList();
return(eList);
}
catch (Exception ex)
{
throw ex;
}
finally
{
db.Dispose();
}
}
public bool ActivateNewUser(sec_UserRepo repo_user)
{
try
{
//Get connectoin
var app = new AppSettings();
con = app.GetConnection();
#region Approve user
var param = new DynamicParameters();
param.Add(name: "p_UserId", value: repo_user.User_Id, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add(name: "p_UpdateId", value: GlobalValue.User_ID, dbType: DbType.String, direction: ParameterDirection.Input);
int result = con.Execute(sql: "ACT_SEC_USER", param: param, commandType: CommandType.StoredProcedure);
#endregion
if (result != 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
if (con != null)
{
con = null;
}
}
}
}
public List <sec_UserRepo> GetUserData(sec_UserRepo loginmodel)
{
try
{
var param = new DynamicParameters();
List <sec_UserRepo> Objuserdata = new List <sec_UserRepo>();
string context = "SELECT DISTINCT * FROM VW_SEL_USER_DATA WHERE USER_ID = '" + loginmodel.User_Id + "'";
return(Objuserdata = db.GetConnection().Query <sec_UserRepo>(context).ToList());
}
catch (Exception ex)
{
throw ex;
}
finally
{
db.Dispose();
}
}
public bool IsLoggedIn(sec_UserRepo userrepo)
{
try
{
var param = new DynamicParameters();
param.Add("P_USER_ID", userrepo.User_Id, DbType.String, ParameterDirection.Input);
param.Add("VDATA", null, DbType.Int32, ParameterDirection.Output);
db.GetConnection().Execute("LOGIN_PROCEDURES.SEL_USER_IS_LOGIN", param, commandType: CommandType.StoredProcedure);
int paramoption = param.Get <int>("VDATA");
if (paramoption <= 0)
{
return(false);
}
else
{
return(true);
}
}
catch (Exception ex)
{
throw ex;
}
}
public void ReassignNewUser(sec_UserRepo repo_user)
{
var app = new AppSettings();
TransactionOptions tsOp = new TransactionOptions();
tsOp.IsolationLevel = System.Transactions.IsolationLevel.Snapshot;
TransactionScope ts = new TransactionScope(TransactionScopeOption.RequiresNew, tsOp);
tsOp.Timeout = TimeSpan.FromMinutes(20);
string queryinternet = "select * from setup_company";
using (OracleConnection conn = new OracleConnection(app.conString())) //
{
try
{
//string password_hash = cSecurityRepo.AES_Encrypt(repo_user.Password);
#region get email properties
{
var paramuser = new DynamicParameters();
paramuser.Add("P_USER_ID", repo_user.User_Id, DbType.String, ParameterDirection.Input);
paramuser.Add("REMAIL", "", DbType.String, ParameterDirection.Output);
paramuser.Add("RPASSWORD", "", DbType.String, ParameterDirection.Output);
paramuser.Add("RFULLNAME", "", DbType.String, ParameterDirection.Output);
conn.Execute("SEL_EMAIL_PROP", paramuser, commandType: CommandType.StoredProcedure);
repo_user.Email = paramuser.Get <string>("REMAIL");
repo_user.Employee_Name = paramuser.Get <string>("RFULLNAME");
}
#endregion
#region reassign user
var param = new DynamicParameters();
param.Add(name: "p_UserId", value: repo_user.User_Id, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add(name: "p_UserGroup", value: repo_user.User_Group_Id, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add(name: "p_MakerId", value: GlobalValue.User_ID, dbType: DbType.String, direction: ParameterDirection.Input);
conn.Execute(sql: "REASSIGN_SEC_USER", param: param, commandType: CommandType.StoredProcedure);
#endregion
#region send email
OracleCommand commandinternet = new OracleCommand(queryinternet, conn);
conn.Open();
OracleDataReader readerinternet;
readerinternet = commandinternet.ExecuteReader();
// Always call Read before accessing data.
while (readerinternet.Read())
{
internetRepo.smtp = (string)readerinternet["smtp"];
internetRepo.email_from = (string)readerinternet["email_from"];
internetRepo.email_password = (string)readerinternet["email_password"];
internetRepo.port = Convert.ToInt16(readerinternet["port"]);
internetRepo.company_name = (string)readerinternet["company_name"];
}
var msg = $@"Dear {repo_user.Employee_Name}, Please be informed that your Teksol Penfad user group has been changed. Contact the system's aadministrator for clarification.</b> <br/> <br/> Thank you. </b> <br/> <br/>{internetRepo.company_name}";
string from = internetRepo.email_from, pass = internetRepo.email_password, subj = "Change of User Group; Teksol Penfad", to = repo_user.Email;
string smtp = internetRepo.smtp;
int port = internetRepo.port;
//string attach = DocumentName;
internetRepo.SendIt(from, pass, subj, msg, to, smtp, port, internetRepo.company_name);
#endregion
ts.Complete();
}
catch (Exception ex)
{
throw ex;
}
finally
{
ts.Dispose();
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
}
// public bool ApproveNewUser(sec_UserRepo repo_user)
// {
// //Get connectoin
// var app = new AppSettings();
// TransactionOptions tsOp = new TransactionOptions();
// tsOp.IsolationLevel = System.Transactions.IsolationLevel.Snapshot;
// TransactionScope ts = new TransactionScope(TransactionScopeOption.RequiresNew, tsOp);
// tsOp.Timeout = TimeSpan.FromMinutes(20);
// using (OracleConnection conn = new OracleConnection(app.conString())) //
// {
// try
// {
// string queryinternet = "select * from setup_company";
// #region get email properties
// {
// var paramuser = new DynamicParameters();
// paramuser.Add("P_USER_ID", repo_user.User_Id, DbType.String, ParameterDirection.Input);
// paramuser.Add("REMAIL", "", DbType.String, ParameterDirection.Output);
// paramuser.Add("RPASSWORD", "", DbType.String, ParameterDirection.Output);
// paramuser.Add("RFULLNAME", "", DbType.String, ParameterDirection.Output);
// con.Execute("SEL_EMAIL_PROP", paramuser, commandType: CommandType.StoredProcedure);
// repo_user.Email = paramuser.Get<string>("REMAIL");
// repo_user.Password = paramuser.Get<string>("RPASSWORD");
// repo_user.Employee_Name = paramuser.Get<string>("RFULLNAME");
// }
// #endregion
// #region send email
// OracleCommand commandinternet = new OracleCommand(queryinternet, conn);
// conn.Open();
// OracleDataReader readerinternet;
// readerinternet = commandinternet.ExecuteReader();
// // Always call Read before accessing data.
// while (readerinternet.Read())
// {
// internetRepo.smtp = (string)readerinternet["smtp"];
// internetRepo.email_from = (string)readerinternet["email_from"];
// internetRepo.email_password = (string)readerinternet["email_password"];
// internetRepo.port = Convert.ToInt16(readerinternet["port"]);
// internetRepo.company_name = (string)readerinternet["company_name"];
// }
// string security_Code = "[email protected]";
// repo_user.Password = GlobalValue.AES_Decrypt(repo_user.Password, security_Code);
// var msg = $@"<b>Dear {repo_user.Employee_Name}</b> <br/> <br/><font color=blue>Your User Name is {repo_user.User_Id} and Password is {repo_user.Password}</font>";
// string from = internetRepo.email_from, pass = internetRepo.email_password, subj = "Teksol Penfad authentication for " + repo_user.Employee_Name, to = repo_user.Email;
// string smtp = internetRepo.smtp;
// int port = internetRepo.port;
// internetRepo.SendIt(from, pass, subj, msg, to, smtp, port);
// #endregion
// #region Approve user
// var param = new DynamicParameters();
// param.Add(name: "p_UserId", value: repo_user.User_Id, dbType: DbType.String, direction: ParameterDirection.Input);
// param.Add(name: "p_UserStatus", value: "ACTIVE", dbType: DbType.String, direction: ParameterDirection.Input);
// param.Add(name: "p_AuthStatus", value: "AUTHORIZED", dbType: DbType.String, direction: ParameterDirection.Input);
// param.Add(name: "p_AuthDate", value: GlobalValue.Scheme_Today_Date, dbType: DbType.Date, direction: ParameterDirection.Input);
// param.Add(name: "p_AuthId", value: GlobalValue.User_ID, dbType: DbType.String, direction: ParameterDirection.Input);
// con.Execute(sql: "APP_SEC_USER", param: param, commandType: CommandType.StoredProcedure);
// #endregion
// //ts.Complete();
// return true;
// }
// catch (Exception ex)
// {
// throw ex;
// }
// finally
// {
// if (con.State == ConnectionState.Open)
// {
// con.Close();
// }
// }
// }
//}
public void ApproveNewUser(sec_UserRepo repo_user)
{
var app = new AppSettings();
TransactionOptions tsOp = new TransactionOptions();
tsOp.IsolationLevel = System.Transactions.IsolationLevel.Snapshot;
TransactionScope ts = new TransactionScope(TransactionScopeOption.RequiresNew, tsOp);
tsOp.Timeout = TimeSpan.FromMinutes(20);
string queryinternet = "select * from setup_company";
using (OracleConnection conn = new OracleConnection(app.conString())) //
{
try
{
//string password_hash = cSecurityRepo.AES_Encrypt(repo_user.Password);
#region get email properties
{
var paramuser = new DynamicParameters();
paramuser.Add("P_USER_ID", repo_user.User_Id, DbType.String, ParameterDirection.Input);
paramuser.Add("REMAIL", "", DbType.String, ParameterDirection.Output);
paramuser.Add("RPASSWORD", "", DbType.String, ParameterDirection.Output);
paramuser.Add("RFULLNAME", "", DbType.String, ParameterDirection.Output);
conn.Execute("SEL_EMAIL_PROP", paramuser, commandType: CommandType.StoredProcedure);
repo_user.Email = paramuser.Get <string>("REMAIL");
repo_user.Employee_Name = paramuser.Get <string>("RFULLNAME");
}
#endregion
#region reassign user
var param = new DynamicParameters();
param.Add(name: "p_UserId", value: repo_user.User_Id, dbType: DbType.String, direction: ParameterDirection.Input);
param.Add(name: "p_UserStatus", value: "ACTIVE", dbType: DbType.String, direction: ParameterDirection.Input);
param.Add(name: "p_AuthStatus", value: "AUTHORIZED", dbType: DbType.String, direction: ParameterDirection.Input);
param.Add(name: "p_AuthDate", value: GlobalValue.Scheme_Today_Date, dbType: DbType.Date, direction: ParameterDirection.Input);
param.Add(name: "p_AuthId", value: GlobalValue.User_ID, dbType: DbType.String, direction: ParameterDirection.Input);
conn.Execute(sql: "APP_SEC_USER", param: param, commandType: CommandType.StoredProcedure);
#endregion
#region send email
OracleCommand commandinternet = new OracleCommand(queryinternet, conn);
conn.Open();
OracleDataReader readerinternet;
readerinternet = commandinternet.ExecuteReader();
// Always call Read before accessing data.
while (readerinternet.Read())
{
internetRepo.smtp = (string)readerinternet["smtp"];
internetRepo.email_from = (string)readerinternet["email_from"];
internetRepo.email_password = (string)readerinternet["email_password"];
internetRepo.port = Convert.ToInt16(readerinternet["port"]);
internetRepo.company_name = (string)readerinternet["company_name"];
}
string security_Code = "[email protected]";
repo_user.Password = GlobalValue.AES_Decrypt(repo_user.Password, security_Code);
var msg = $@"<b>Dear {repo_user.Employee_Name}</b> <br/> <br/><font color=blue>Your User Name is {repo_user.User_Id} and Password is {repo_user.Password}</font>";
string from = internetRepo.email_from, pass = internetRepo.email_password, subj = "Teksol Penfad authentication for " + repo_user.Employee_Name, to = repo_user.Email;
string smtp = internetRepo.smtp;
int port = internetRepo.port;
// internetRepo.SendIt(from, pass, subj, msg, to, smtp, port);
internetRepo.SendIt(from, pass, subj, msg, to, smtp, port, internetRepo.company_name);
#endregion
ts.Complete();
}
catch (Exception ex)
{
throw ex;
}
finally
{
ts.Dispose();
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
}