public string addUser(SqlConnection conn, SqlTransaction trans, User user)
{
ConnectionDao connectionDao = new ConnectionDao();
string returnString = IdProConstants.SUCCESS;
Employee objEmployee = new Employee();
SqlCommand cmd = null;
SqlDataReader rs = null;
AddEmployee obj = new AddEmployee();
UserServices userService = new UserServices();
string query = "INSERT INTO Users([username],[password],[role]) VALUES(@UserName,@Password,@Role)";
try
{
cmd = connectionDao.getSqlCommand(query, conn, trans);
SqlParameter param1 = new SqlParameter();
param1.ParameterName = "@UserName";
//param1.Value = HttpContext.Current.Session["useremp"];
param1.Value = user.Username;
cmd.Parameters.Add(param1);
SqlParameter param2 = new SqlParameter();
param2.ParameterName = "@Password";
//param2.Value = userService.getHashPassword(HttpContext.Current.Session["useremp"].ToString ());
param2.Value = userService.getHashPassword(user.Password);
cmd.Parameters.Add(param2);
SqlParameter param3 = new SqlParameter();
param3.ParameterName = "@Role";
//param3.Value = HttpContext.Current.Session["rol"];
param3.Value = user.Role ;
cmd.Parameters.Add(param3);
cmd.ExecuteScalar();
}
catch (Exception exception)
{
System.Diagnostics.Trace.WriteLine("[UserDAO:addUser] Exception " + exception.StackTrace);
returnString = IdProConstants.FAIL;
}
finally
{
connectionDao.closeDabaseEntities(cmd, rs);
}
return returnString;
}
public string updateEmployee(Employee employee)
{
SqlConnection conn = null;
SqlTransaction trans = null;
string returnString = IdProConstants.SUCCESS;
UserDAO userDao = new UserDAO();
EmployeeDao EmployeeDao = new EmployeeDao();
ConnectionDao ConnectionDao = new ConnectionDao();
UserServices userServices = new UserServices();
Employee employeeById = EmployeeDao.getEmployeeById(employee.EmployeeId);
if (!(employeeById.Email.Trim().Equals(employee.Email.Trim())) && isEmployeeEmailexist(employee.Email.Trim()))
{
returnString = "Employee Email already Exist in the system";
}
else if (!(employeeById.USER.Username.Trim().ToUpper().Equals(employee.USER.Username.Trim().ToUpper())) && userServices.isUserNameExist(employee.USER.Username))
{
returnString = "UserName already Exit in the system";
}
else
{
try
{
conn = ConnectionDao.getConnection();
trans = conn.BeginTransaction();
HttpContext.Current.Session["prevUserName"] = employeeById.USER.Username;
returnString = userDao.updateUser(conn, trans, employee.USER);
if (IdProConstants.SUCCESS.Equals(returnString))
{
returnString = EmployeeDao.updateEmployee(conn, trans, employee);
}
if (IdProConstants.SUCCESS.Equals(returnString))
{
trans.Commit();
}
else
{
trans.Rollback();
}
}
catch (Exception exception)
{
trans.Rollback();
System.Diagnostics.Trace.WriteLine("[EmployeeServices:updateEmployee] Exception " + exception.StackTrace);
}
finally
{
ConnectionDao.closeConnection(conn);
}
}
return returnString;
}
public string addEmployee(Employee employee)
{
SqlConnection conn = null;
SqlTransaction trans = null;
string returnString = IdProConstants.SUCCESS;
UserDAO userDao = new UserDAO();
EmployeeDao EmployeeDao = new EmployeeDao();
ConnectionDao ConnectionDao = new ConnectionDao();
UserServices userServices = new UserServices();
if (isEmployeeEmailexist(employee.Email.Trim()))
{
returnString = "Employee Email already Exist in the system";
}
// else if (userServices.isUserNameExist(employee.USER.Username))
else if (userServices.isUserNameExist(employee.Username))
{
returnString = "UserName already Exit in the system";
}
else
{
try
{
conn = ConnectionDao.getConnection();
trans = conn.BeginTransaction();
returnString = userDao.addUser(conn, trans, employee.USER);
if (IdProConstants.SUCCESS.Equals(returnString))
{
returnString = EmployeeDao.addEmployee(conn, trans, employee);
}
if (IdProConstants.SUCCESS.Equals(returnString))
{
trans.Commit();
}
else
{
trans.Rollback();
}
}
catch (Exception exception)
{
trans.Rollback();
System.Diagnostics.Trace.WriteLine("[EmployeeServices:addEmployee] Exception " + exception.StackTrace);
}
finally
{
ConnectionDao.closeConnection(conn);
}
}
return returnString;
}
public string addEmployee(SqlConnection conn, SqlTransaction trans, Employee employee)
{
ConnectionDao ConnectionDao = new ConnectionDao();
string returnString = IdProConstants.SUCCESS;
SqlCommand cmd = null;
SqlDataReader rs = null;
//string query = "INSERT INTO Employees([FIRST_NAME],[LAST_NAME],[EMAIL],[USERNAME],[STATUS],[ADDED_BY],[ADDED_DATE],[LAST_UPDATED_BY],[LAST_UPDATED_DATE]) VALUES(@FIRST_NAME,@LAST_NAME,@EMAIL,@USERNAME,@STATUS,@ADDED_BY,@ADDED_DATE,@LAST_UPDATED_BY,@LAST_UPDATED_DATE)";
string query = "INSERT INTO Employees([FIRST_NAME],[LAST_NAME],[EMAIL],[USERNAME],[STATUS],[Title],[ADDED_BY],[ADDED_DATE],[DEPARTMENT]) VALUES(@FIRST_NAME,@LAST_NAME,@EMAIL,@USERNAME,@STATUS,@Title,@ADDED_BY,@ADDED_DATE,@Department)";
// string query = "INSERT INTO Employees([FIRST_NAME],[LAST_NAME],[EMAIL],[USERNAME],[STATUS],[Department],[Title],[ADDED_BY],[ADDED_DATE]) VALUES('" + employee.FirstName + "','" + employee.LastName + "','" + employee.Email + "','" + employee.Username + "','A','" + employee.department + "','" + employee.title + "','121',getdate())";
try
{
cmd = ConnectionDao.getSqlCommand(query, conn, trans);
SqlParameter param1 = new SqlParameter();
param1.ParameterName = "@FIRST_NAME";
param1.Value = employee.FirstName;
cmd.Parameters.Add(param1);
SqlParameter param2 = new SqlParameter();
param2.ParameterName = "@LAST_NAME";
param2.Value = employee.LastName;
cmd.Parameters.Add(param2);
SqlParameter param3 = new SqlParameter();
param3.ParameterName = "@EMAIL";
param3.Value = employee.Email;
cmd.Parameters.Add(param3);
SqlParameter param4 = new SqlParameter();
param4.ParameterName = "@USERNAME";
// param4.Value = employee.getUser().getUserName();
param4.Value = employee.Username;
cmd.Parameters.Add(param4);
SqlParameter param5 = new SqlParameter();
param5.ParameterName = "@STATUS";
param5.Value = employee.status;
cmd.Parameters.Add(param5);
SqlParameter param6 = new SqlParameter();
param6.ParameterName = "@Department";
param6.Value = employee.department;
cmd.Parameters.Add(param6);
SqlParameter param7 = new SqlParameter();
param7.ParameterName = "@Title";
param7.Value = employee.title;
cmd.Parameters.Add(param7);
SqlParameter param10 = new SqlParameter();
param10.ParameterName = "@ADDED_BY";
param10.Value = HttpContext.Current.Session["username"];
cmd.Parameters.Add(param10);
SqlParameter param11 = new SqlParameter();
param11.ParameterName = "@ADDED_DATE";
param11.Value = System.DateTime.Now;
cmd.Parameters.Add(param11);
//SqlParameter param8 = new SqlParameter();
//param8.ParameterName = "@LAST_UPDATED_BY";
//param8.Value = employee.LastUpdatedBy;
//cmd.Parameters.Add(param8);
//SqlParameter param9 = new SqlParameter();
//param9.ParameterName = "@LAST_UPDATED_DATE";
//param9.Value = employee.LastUpdatedDate;
//cmd.Parameters.Add(param9);
cmd.ExecuteNonQuery();
}
catch (Exception exception)
{
System.Diagnostics.Trace.WriteLine("[EmployeeDAO:addEmployee] Exception " + exception.StackTrace);
returnString = IdProConstants.FAIL;
}
finally
{
ConnectionDao.closeDabaseEntities(cmd, rs);
}
return returnString;
}
public string updateEmployee(SqlConnection conn, SqlTransaction trans, Employee employee)
{
ConnectionDao ConnectionDao = new ConnectionDao();
string returnString = IdProConstants.SUCCESS;
SqlCommand cmd = null;
SqlDataReader rs = null;
string query = "Update Employees set FIRST_NAME=@FIRST_NAME,LAST_NAME=@LAST_NAME,EMAIL=@EMAIL,USERNAME=@USERNAME,STATUS=@STATUS,LAST_UPDATED_BY=@id,LAST_UPDATED_DATE=@LAST_UPDATED_BY WHERE Employee_Id=@id";
try
{
cmd = ConnectionDao.getSqlCommand(query, conn, trans);
SqlParameter param1 = new SqlParameter();
param1.ParameterName = "@FIRST_NAME";
param1.Value = employee.FirstName;
cmd.Parameters.Add(param1);
SqlParameter param2 = new SqlParameter();
param2.ParameterName = "@LAST_NAME";
param2.Value = employee.LastName;
cmd.Parameters.Add(param2);
SqlParameter param3 = new SqlParameter();
param3.ParameterName = "@EMAIL";
param3.Value = employee.Email;
cmd.Parameters.Add(param3);
SqlParameter param4 = new SqlParameter();
param4.ParameterName = "@USERNAME";
param4.Value = employee.USER.Username;
cmd.Parameters.Add(param4);
SqlParameter param5 = new SqlParameter();
param5.ParameterName = "@STATUS";
param5.Value = employee.status;
cmd.Parameters.Add(param5);
SqlParameter param6 = new SqlParameter();
param6.ParameterName = "@id";
param6.Value = employee.EmployeeId;
cmd.Parameters.Add(param6);
SqlParameter param8 = new SqlParameter();
param8.ParameterName = "@LAST_UPDATED_BY";
param8.Value = employee.EmployeeId;
cmd.Parameters.Add(param8);
SqlParameter param9 = new SqlParameter();
param9.ParameterName = "@LAST_UPDATED_DATE";
param9.Value = DateTime.Now;
cmd.Parameters.Add(param9);
cmd.ExecuteNonQuery();
}
catch (Exception exception)
{
System.Diagnostics.Trace.WriteLine("[EmployeeDAO:updateEmployee] Exception " + exception.StackTrace);
returnString = IdProConstants.FAIL;
}
finally
{
ConnectionDao.closeDabaseEntities(cmd, rs);
}
return returnString;
}
public DataSet getfillstatus()
{
Employee objempstatus = new Employee();
ConnectionDao ConnectionDao = new ConnectionDao();
SqlDataAdapter adp = new SqlDataAdapter("select * from Employee_Statuses", ConnectionDao.getConnection());
DataSet ds1 = new DataSet();
adp.Fill(ds1);
return ds1;
}
public DataTable getemployeedetailfill()
{
Employee empid = new Employee();
ConnectionDao ConnectionDao = new ConnectionDao();
SqlDataAdapter adp = new SqlDataAdapter("select a.* ,b.* from Employees a, Users b where a.UserName=b.UserName and Employee_Id='" + EmployeeIDfill12 + "'", ConnectionDao.getConnection());
DataTable ds1 = new DataTable();
adp.Fill(ds1);
return ds1;
}
public Employee getEmployeeByUserName(string userName)
{
ConnectionDao ConnectionDao = new ConnectionDao();
Employee employee = new Employee();
SqlCommand cmd = null;
SqlConnection conn = null;
SqlDataReader rs = null;
string query = "select * from employees where userName=@userName";
try
{
conn = ConnectionDao.getConnection();
cmd = ConnectionDao.getSqlCommandWithoutTransaction(query, conn);
SqlParameter param1 = new SqlParameter();
param1.ParameterName = "@userName";
param1.Value = userName;
cmd.Parameters.Add(param1);
rs = cmd.ExecuteReader();
if (rs.Read())
{
employee.FirstName=(rs["first_name"].ToString().Trim());
employee.LastName=(rs["last_name"].ToString().Trim());
employee.Email=(rs["email"].ToString().Trim());
employee.EmployeeStatus=(rs["status"].ToString().Trim());
employee.EmployeeId = (rs["Employee_Id"].ToString().Trim());
employee.department = (rs["department"].ToString().Trim());
UserDAO userDao = new UserDAO();
User user = userDao.getUserByUserName(userName);
employee.USER=user;
}
else
{
employee = null;
}
}
catch (Exception exception)
{
System.Diagnostics.Trace.WriteLine("[EmployeeDAO:getEmployeeByUserName] Exception " + exception.StackTrace);
employee = null;
}
finally
{
ConnectionDao.closeConnection(conn);
ConnectionDao.closeDabaseEntities(cmd, rs);
}
return employee;
}
public Employee getempdepartment(string department)
{
ConnectionDao connectionDao = new ConnectionDao();
Employee employee = new Employee();
SqlCommand cmd = null;
SqlConnection conn = null;
SqlDataReader rs = null;
string query = "select * from Employees where UserName='******'";
try
{
conn = connectionDao.getConnection();
cmd = connectionDao.getSqlCommandWithoutTransaction(query, conn);
rs = cmd.ExecuteReader();
if (rs.Read())
{
employee.department = (rs["Department"].ToString().Trim());
}
else
{
employee = null;
}
}
catch (Exception exception)
{
System.Diagnostics.Trace.WriteLine("[EmployeeDao:getempdepartment] Exception " + exception.StackTrace);
employee = null;
}
finally
{
connectionDao.closeConnection(conn);
connectionDao.closeDabaseEntities(cmd, rs);
}
return employee;
}
public User getUserByUserName(string userName)
{
ConnectionDao connectionDao = new ConnectionDao();
User user = new User();
Employee emp = new Employee();
SqlCommand cmd = null;
SqlConnection conn = null;
SqlDataReader rs = null;
string query = "select * from Users where userName='******'";
try
{
conn = connectionDao.getConnection();
cmd = connectionDao.getSqlCommandWithoutTransaction(query, conn);
//SqlParameter param1 = new SqlParameter();
//param1.ParameterName = emp.Username;
//param1.Value = emp.Username;
//cmd.Parameters.Add(param1);
rs = cmd.ExecuteReader();
if (rs.Read())
{
user.Username=(rs["userName"].ToString().Trim());
user.Password=(rs["password"].ToString().Trim());
user.Role=(rs["role"].ToString().Trim());
}
else
{
user = null;
}
}
catch (Exception exception)
{
System.Diagnostics.Trace.WriteLine("[UserDAO:getUserByUserName] Exception " + exception.StackTrace);
user = null;
}
finally
{
connectionDao.closeConnection(conn);
connectionDao.closeDabaseEntities(cmd, rs);
}
return user;
}
protected void btnLogin_Click(object sender, EventArgs e)
{
string userName = txtUsername.Text.Trim().ToUpper();
string passWord = txtPassword.Text.Trim();
UserServices userService = new UserServices();
string returnString = userService.ValidateUser(userName, passWord);
if (IdProConstants.SUCCESS.Equals(returnString))
{
User user = new User();
user = userService.getUserbyUserName(userName);
EmployeeServices employeeService = new EmployeeServices();
Employee employee = new Employee();
employee = employeeService.getEmployeeByUserName(userName);
if (user == null || employee == null)
{
returnString = IdProConstants.ADMIN;
}
//else if (UserStatusesConstants.INACTIVEEMPLOYEE.Equals(employee.getEmployeeStatus()))
//{
// returnString = TransactionConfirmMessages.INACTIVEUSER;
//}
else
{
string role = user.getRole().Trim();
Session["role"] = role;
Session["username"] = user.getUserName().Trim();
Session["name"] = employee.getFirstName();
FormsAuthenticationTicket tkt = default(FormsAuthenticationTicket);
string cookiestr = null;
System.Web.HttpCookie ck = default(System.Web.HttpCookie);
tkt = new FormsAuthenticationTicket(1, txtUsername.Text, DateTime.Now, DateTime.Now.AddMinutes(30), false, role);
cookiestr = FormsAuthentication.Encrypt(tkt);
ck = new System.Web.HttpCookie(FormsAuthentication.FormsCookieName, cookiestr);
ck.Path = FormsAuthentication.FormsCookiePath;
Response.Cookies.Add(ck);
string strRedirect = null;
strRedirect = Request["ReturnURL"];
if (!string.IsNullOrEmpty(strRedirect) & strRedirect != "/")
{
Response.Redirect(strRedirect, true);
}
else
{
strRedirect = "ManageEmployee.aspx";
Response.Redirect(strRedirect, true);
}
}
}
lblMsg.Text = returnString;
}
