/// <summary>
/// Load user by UserName
/// </summary>
/// <param name="username"></param>
/// <remarks>
/// Author: PhatLT. FPTSS.
/// Created date: 14/02/2011
/// </remarks>
private void InitData(string username)
{
try
{
clsCryptography crypto = new clsCryptography();
dt = bo.GetOne(username);
_manager = (CurrencyManager)this.BindingContext[dt];
_manager.Position = 0;
BindDataToControl();
txtUserName.ReadOnly = true;
bln_IsEdit = true;
ep.DataSource = dt;
dt.Rows[0]["PASSWORD"] = crypto.Decode(dt.Rows[0]["PASSWORD"].ToString());
dt.Rows[0]["OLDPASSWORD"] = crypto.Decode(dt.Rows[0]["OLDPASSWORD"].ToString());
txtConfirmPassword.Text = dt.Rows[0]["PASSWORD"].ToString();
dt.AcceptChanges();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, clsResources.GetMessage("errors.general"), MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// Login
/// </summary>
/// <param name="username"></param>
/// <param name="password"></param>
/// <param name="expiredDays"></param>
/// <param name="result"></param>
/// <returns>Return all feature of this user</returns>
/// <remarks>
/// Author: PhatLT. FPTSS.
/// Created date: 14/02/2011
/// </remarks>
public DataTable Login(string username, string password, int expiredDays, out int result)
{
clsCryptography crypto = new clsCryptography();
SqlConnection con = Connection;
SqlCommand cmd = null;
DataTable dt = null;
try
{
cmd = new SqlCommand("sp_CheckValidLogin", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@i_sUsername", SqlDbType.VarChar, 20).Value = username;
cmd.Parameters.Add("@i_sPassword", SqlDbType.VarChar, 20).Value = crypto.Encode(password);
cmd.Parameters.Add("@i_nPwdExpired", SqlDbType.Int, 4).Value = expiredDays;
cmd.Parameters.Add("@o_nResult", SqlDbType.Int, 4);
cmd.Parameters["@o_nResult"].Direction = ParameterDirection.Output;
cmd.CommandTimeout = 3600;
dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
result = Convert.ToInt32(cmd.Parameters["@o_nResult"].Value);
return(dt);
}
catch (Exception ex)
{
log.Error(ex.ToString());
throw new Exception("clsAutUserDAO.LogIn error");
}
}
/// <summary>
/// Zip file
/// </summary>
/// <remarks>
/// Author : Nguyen Bao Nguyen G3
/// Created day : 24-Apr-2011
/// </remarks>
public void ZipFileParam(string strParamPath)
{
try
{
//string strFileNameToEncode = "";
string strZipFilename = "";
string strPass = "";
string strPath = strParamPath;
string[] filenames = Directory.GetFiles(strPath, "*.xml");
clsCryptography genPass = new clsCryptography();
foreach (string file in filenames)
{
strZipFilename = file.Substring(0, file.Length - 4) + ".zip";
//strFileNameToEncode = file.Substring(0, file.Length -4);
strPass = genPass.GenPWDByFilename(strZipFilename);
clsZip.ZipFiles(file, strZipFilename, strPass);
File.Delete(file);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Changed password by username, oldPassword, newPassword
/// </summary>
/// <param name="username"></param>
/// <param name="oldPassword"></param>
/// <param name="newPassword"></param>
/// <returns></returns>
/// <remarks>
/// Author: PhatLT. FPTSS.
/// Created date: 14/02/2011
/// </remarks>
public int ChangePassword(string username, string oldPassword, string newPassword)
{
SqlConnection con = Connection;
SqlTransaction trans = null;
SqlCommand cmd = null;
clsCryptography crypto = new clsCryptography();
int count = 0;
try
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
cmd = new SqlCommand("UPDATE GENERAL_AUT_USER SET PASSWORD = @PASSWORD, PWD_CHG_DATE = getdate() WHERE USERNAME = @USERNAME AND PASSWORD = @ORIGINAL_PASSWORD", con, trans);
cmd.Parameters.Add("@USERNAME", SqlDbType.VarChar, 20).Value = username;
cmd.Parameters.Add("@ORIGINAL_PASSWORD", SqlDbType.VarChar, 255).Value = crypto.Encode(oldPassword);
cmd.Parameters.Add("@PASSWORD", SqlDbType.VarChar, 255).Value = crypto.Encode(newPassword);
count = cmd.ExecuteNonQuery();
trans.Commit();
}
catch (SqlException ex)
{
log.Error(ex.Message, ex);
if (trans != null)
{
trans.Rollback();
}
throw ex;
}
catch (Exception ex)
{
log.Error(ex.Message, ex);
if (trans != null)
{
trans.Rollback();
}
throw ex;
}
finally
{
if (con != null && con.State == ConnectionState.Open)
{
con.Close();
}
}
return(count);
}
/// <summary>
/// Update one row of GENERAL_AUT_USER by USERNAME
/// </summary>
/// <param name="row"></param>
/// <returns></returns>
/// <remarks>
/// Author: PhatLT. FPTSS.
/// Created date: 14/02/2011
/// </remarks>
public int Update(DataRow row)
{
SqlConnection con = Connection;
SqlTransaction trans = null;
SqlCommand cmd = null;
clsCryptography crypto = new clsCryptography();
int count = 0;
try
{
string newPassword = row["PASSWORD"].ToString();
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
string strPWDChangeDate = "";
if (!row["PASSWORD"].Equals(row["OLDPASSWORD"]))
{
strPWDChangeDate = " , PWD_CHG_DATE = getdate() ";
}
string cmdText = string.Format("UPDATE GENERAL_AUT_USER SET PASSWORD = @PASSWORD, FIRSTNAME = @FIRSTNAME, LASTNAME = @LASTNAME, EMAIL = @EMAIL, ADDRESS = @ADDRESS, PHONE = @PHONE, START_DATE = @START_DATE, END_DATE = @END_DATE, STATUS = @STATUS, UROLE_ID = @UROLE_ID, DESCRIPTION = @DESCRIPTION {0} WHERE USERNAME = @USERNAME", strPWDChangeDate);
cmd = new SqlCommand(cmdText, con, trans);
cmd.Parameters.Add("@USERNAME", SqlDbType.VarChar).Value = row["USERNAME"];
cmd.Parameters.Add("@PASSWORD", SqlDbType.VarChar).Value = crypto.Encode(newPassword);
cmd.Parameters.Add("@FIRSTNAME", SqlDbType.NVarChar).Value = row["FIRSTNAME"];
cmd.Parameters.Add("@LASTNAME", SqlDbType.NVarChar).Value = row["LASTNAME"];
cmd.Parameters.Add("@EMAIL", SqlDbType.VarChar).Value = row["EMAIL"];
cmd.Parameters.Add("@ADDRESS", SqlDbType.NVarChar).Value = row["ADDRESS"];
cmd.Parameters.Add("@PHONE", SqlDbType.VarChar).Value = row["PHONE"];
cmd.Parameters.Add("@START_DATE", SqlDbType.DateTime).Value = row["START_DATE"];
cmd.Parameters.Add("@END_DATE", SqlDbType.DateTime).Value = row["END_DATE"];
cmd.Parameters.Add("@STATUS", SqlDbType.Char).Value = row["STATUS"];
cmd.Parameters.Add("@UROLE_ID", SqlDbType.VarChar).Value = row["UROLE_ID"];
cmd.Parameters.Add("@DESCRIPTION", SqlDbType.NVarChar).Value = row["DESCRIPTION"];
count = cmd.ExecuteNonQuery();
trans.Commit();
}
catch (SqlException ex)
{
log.Error(ex.Message, ex);
if (trans != null)
{
trans.Rollback();
}
throw ex;
}
catch (Exception ex)
{
log.Error(ex.Message, ex);
if (trans != null)
{
trans.Rollback();
}
throw ex;
}
finally
{
if (con != null && con.State == ConnectionState.Open)
{
con.Close();
}
}
return(count);
}
/// <summary>
/// Insert one row into GENERAL_AUT_USER table
/// </summary>
/// <param name="row"></param>
/// <returns></returns>
/// <remarks>
/// Author: PhatLT. FPTSS.
/// Created date: 14/02/2011
/// </remarks>
public int Insert(DataRow row)
{
SqlConnection con = Connection;
SqlTransaction trans = null;
SqlCommand cmd = null;
clsCryptography crypto = new clsCryptography();
int count = 0;
try
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
cmd = new SqlCommand("INSERT INTO GENERAL_AUT_USER(USERNAME, PASSWORD, FIRSTNAME, LASTNAME, EMAIL, ADDRESS, PHONE, START_DATE, END_DATE, PWD_CHG_DATE, STATUS, UROLE_ID, DESCRIPTION) VALUES(@USERNAME, @PASSWORD, @FIRSTNAME, @LASTNAME, @EMAIL, @ADDRESS, @PHONE, @START_DATE, @END_DATE, getdate(), @STATUS, @UROLE_ID, @DESCRIPTION)", con, trans);
cmd.Parameters.Add("@USERNAME", SqlDbType.VarChar).Value = row["USERNAME"];
cmd.Parameters.Add("@PASSWORD", SqlDbType.VarChar).Value = crypto.Encode(row["PASSWORD"].ToString());
cmd.Parameters.Add("@FIRSTNAME", SqlDbType.NVarChar).Value = row["FIRSTNAME"];
cmd.Parameters.Add("@LASTNAME", SqlDbType.NVarChar).Value = row["LASTNAME"];
cmd.Parameters.Add("@EMAIL", SqlDbType.VarChar).Value = row["EMAIL"];
cmd.Parameters.Add("@ADDRESS", SqlDbType.NVarChar).Value = row["ADDRESS"];
cmd.Parameters.Add("@PHONE", SqlDbType.VarChar).Value = row["PHONE"];
cmd.Parameters.Add("@START_DATE", SqlDbType.DateTime).Value = row["START_DATE"];
cmd.Parameters.Add("@END_DATE", SqlDbType.DateTime).Value = row["END_DATE"];
//cmd.Parameters.Add("@PWD_CHG_DATE", SqlDbType.DateTime).Value = row["PWD_CHG_DATE"];
cmd.Parameters.Add("@STATUS", SqlDbType.Char).Value = row["STATUS"];
cmd.Parameters.Add("@UROLE_ID", SqlDbType.VarChar).Value = row["UROLE_ID"];
cmd.Parameters.Add("@DESCRIPTION", SqlDbType.NVarChar).Value = row["DESCRIPTION"];
count = cmd.ExecuteNonQuery();
trans.Commit();
}
catch (SqlException ex)
{
log.Error(ex.Message, ex);
if (trans != null)
{
trans.Rollback();
}
throw ex;
}
catch (Exception ex)
{
log.Error(ex.Message, ex);
if (trans != null)
{
trans.Rollback();
}
throw ex;
}
finally
{
if (con != null && con.State == ConnectionState.Open)
{
con.Close();
}
}
return(count);
}
/// <summary>
/// Set Rights for one user on region and strategic region
/// </summary>
/// <param name="userName"></param>
/// <param name="regions"></param>
/// <param name="strategicRegions"></param>
/// <returns></returns>
/// <remarks>
/// Author: PhatLT. FPTSS.
/// Created date: 14/02/2011
/// </remarks>
public int SetRights(string userName, ArrayList regions, ArrayList strategicRegions)
{
SqlConnection con = Connection;
SqlTransaction trans = null;
SqlCommand cmd = null;
clsCryptography crypto = new clsCryptography();
int count = 0;
try
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
cmd = new SqlCommand("", con, trans);
//DELETE FROM GENERAL_AUT_USER_REGION WHERE USERNAME = @USERNAME
cmd.Parameters.Clear();
cmd.CommandText = "DELETE FROM GENERAL_AUT_USER_REGION WHERE USERNAME = @USERNAME";
cmd.Parameters.Add("@USERNAME", SqlDbType.VarChar, 20).Value = userName;
cmd.Parameters.Clear();
cmd.CommandText = "DELETE FROM GENERAL_AUT_USER_REGION WHERE USERNAME = @USERNAME";
cmd.Parameters.Add("@USERNAME", SqlDbType.VarChar, 20).Value = userName;
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmd.CommandText = "DELETE FROM GENERAL_AUT_USER_STRATEGIC_REGION WHERE USERNAME = @USERNAME";
cmd.Parameters.Add("@USERNAME", SqlDbType.VarChar, 20).Value = userName;
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmd.CommandText = "INSERT INTO GENERAL_AUT_USER_REGION(USERNAME, REGION_CODE) VALUES (@USERNAME, @REGION_CODE)";
cmd.Parameters.Add("@USERNAME", SqlDbType.VarChar, 20).Value = userName;
cmd.Parameters.Add("@REGION_CODE", SqlDbType.VarChar, 14);
foreach (string regionCode in regions)
{
cmd.Parameters["@REGION_CODE"].Value = regionCode;
count += cmd.ExecuteNonQuery();
}
cmd.Parameters.Clear();
cmd.CommandText = "INSERT INTO GENERAL_AUT_USER_STRATEGIC_REGION(USERNAME, STRATEGIC_REGION_CODE) VALUES (@USERNAME, @STRATEGIC_REGION_CODE)";
cmd.Parameters.Add("@USERNAME", SqlDbType.VarChar, 20).Value = userName;
cmd.Parameters.Add("@STRATEGIC_REGION_CODE", SqlDbType.VarChar, 14);
foreach (string strategicRegionCode in strategicRegions)
{
cmd.Parameters["@STRATEGIC_REGION_CODE"].Value = strategicRegionCode;
count += cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch (SqlException ex)
{
log.Error(ex.Message, ex);
if (trans != null)
{
trans.Rollback();
}
throw ex;
}
catch (Exception ex)
{
log.Error(ex.Message, ex);
if (trans != null)
{
trans.Rollback();
}
throw ex;
}
finally
{
if (con != null && con.State == ConnectionState.Open)
{
con.Close();
}
}
return(count);
}
