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(); } } } }