public int getNumberOfCustomerBySSN(string ssn)
{
ConnectionDao connectionDao = new ConnectionDao();
Account account = new Account();
SqlCommand cmd = null;
SqlConnection conn = null;
SqlDataReader rs = null;
int count = 0;
string query = "select count(*) as count from customer where ssn=@ssn";
try
{
conn = connectionDao.getConnection();
cmd = connectionDao.getSqlCommandWithoutTransaction(query, conn);
SqlParameter param1 = new SqlParameter();
param1.ParameterName = "@ssn";
param1.Value = ssn;
cmd.Parameters.Add(param1);
rs = cmd.ExecuteReader();
if (rs.Read())
{
count = Int32.Parse(rs["count"].ToString());
}
}
catch (Exception exception)
{
System.Diagnostics.Trace.WriteLine("[CustomerDAO:getNumberOfCustomerBySSN] Exception " + exception.StackTrace);
}
finally
{
connectionDao.closeConnection(conn);
connectionDao.closeDabaseEntities(cmd, rs);
}
return count;
}
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 Ticket getticketid(string ticketid)
{
ConnectionDao connectionDao = new ConnectionDao();
Ticket ticket = new Ticket();
SqlCommand cmd = null;
SqlConnection conn = null;
SqlDataReader rs = null;
HttpContext.Current.Session["accountid"] = "1006";
string query = "select * from Ticket where accountid='" + HttpContext.Current.Session["accountid"] + "'";
try
{
conn = connectionDao.getConnection();
cmd = connectionDao.getSqlCommandWithoutTransaction(query, conn);
rs = cmd.ExecuteReader();
if (rs.Read())
{
ticket.ticketgetid = (rs["ticketid"].ToString().Trim());
strid = Convert.ToString(ticket.ticketgetid);
}
else
{
ticket = null;
}
}
catch (Exception exception)
{
System.Diagnostics.Trace.WriteLine("[TicketDao:getticketid] Exception " + exception.StackTrace);
ticket = null;
}
finally
{
connectionDao.closeConnection(conn);
connectionDao.closeDabaseEntities(cmd, rs);
}
return ticket;
}
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;
}