public string addCustomerAccountInfo(Customer_Account_O2M customerAccount)
{
SqlConnection conn = null;
SqlTransaction trans = null;
string returnString = IdProConstants.FAIL;
Customer_Account_O2MDAO customerAccounto2mDao = new Customer_Account_O2MDAO();
long AccountID = 0;
ConnectionDao ConnectionDao = new ConnectionDao();
try
{
conn = ConnectionDao.getConnection();
trans = conn.BeginTransaction();
returnString = customerAccounto2mDao.addCustomerAccountO2MInfo(conn, trans, customerAccount);
if (!AccountID.Equals(0))
{
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 long addaccount(Account account)
{
SqlConnection conn = null;
SqlTransaction trans = null;
AccountDao accountDao = new AccountDao();
long AccountID = 0;
ConnectionDao ConnectionDao = new ConnectionDao();
try
{
conn = ConnectionDao.getConnection();
trans = conn.BeginTransaction();
AccountID = accountDao.addAccountBasicInfo(conn, trans, account);
if (!AccountID.Equals(0))
{
trans.Commit();
}
else
{
trans.Rollback();
}
}
catch (Exception exception)
{
trans.Rollback();
System.Diagnostics.Trace.WriteLine("[EmployeeServices:addEmployee] Exception " + exception.StackTrace);
}
finally
{
ConnectionDao.closeConnection(conn);
}
return AccountID;
}
public DataSet getmenu(string department)
{
EmployeeDao employeedao = new EmployeeDao();
Employee employee = employeedao.getempdepartment(department);
ConnectionDao ConnectionDao = new ConnectionDao();
SqlDataAdapter adp = new SqlDataAdapter("select department_name,weburl from Department where Department_Name='" + employee.department + "'", ConnectionDao.getConnection());
DataSet ds4 = new DataSet();
adp.Fill(ds4);
string Department = ds4.Tables[0].Rows[0]["department_name"].ToString();
if (Department == "Sales")
{
ds4 = addMenu(ds4, "Email");
}
if (Department == "Corporate")
{
ds4.Tables[0].Rows.RemoveAt (0);
ds4.AcceptChanges();
ds4 = addMenu(ds4, "Customer");
ds4 = addMenu(ds4, "Email");
ds4 = addMenu(ds4, "Ticketing");
ds4 = addMenu(ds4, "Dashboard");
ds4 = addMenu(ds4, "Reports");
}
return ds4;
}
public string getWebUrl(string department)
{
string retval = string.Empty;
string sql = "select weburl from department where department_name = '" + department + "'";
ConnectionDao ConnectionDao = new ConnectionDao();
SqlDataAdapter adp = new SqlDataAdapter(sql, ConnectionDao.getConnection());
DataSet ds = new DataSet();
adp.Fill(ds);
retval = ds.Tables[0].Rows[0]["weburl"].ToString();
if (retval == "")
{
retval = "javascript:void(0);";
}
return retval;
}
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 DataSet getemployeedetailsearch(string lastname)
{
ConnectionDao ConnectionDao = new ConnectionDao();
string str ="select a.*,b.department_name,b.description from employees a, Department b where a.Department=b.Department_Name and last_name like '%" + lastname + "%'";
SqlDataAdapter adp = new SqlDataAdapter(str, 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 DataTable getemployeedetail()
{
ConnectionDao ConnectionDao = new ConnectionDao();
SqlDataAdapter adp = new SqlDataAdapter("select * from Employees", 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 DataSet getAssigned()
{
ConnectionDao ConnectionDao = new ConnectionDao();
SqlDataAdapter adp = new SqlDataAdapter("select * from Department where Department_Name='Account'", ConnectionDao.getConnection());
DataSet ds1 = new DataSet();
adp.Fill(ds1);
return ds1;
}
public string TicketAdd(Ticket ticket)
{
SqlConnection conn = null;
SqlTransaction trans = null;
string returnString = IdProConstants.SUCCESS;
TicketDao ticketdao = new TicketDao();
ConnectionDao ConnectionDao = new ConnectionDao();
try
{
conn = ConnectionDao.getConnection();
trans = conn.BeginTransaction();
//ticket.tickettypeid = ticketdao.addticketsTypedetail(conn, trans, ticket);
//if (!ticket.tickettypeid.Equals(0))
//{
ticket.ticketid = ticketdao.addticketsdetail(conn, trans, ticket);
if (!ticket.ticketid.Equals(0))
{
ticket.noteid = ticketdao.addticketsNotedetail(conn, trans, ticket);
if (!ticket.noteid.Equals(0))
{
returnString = ticketdao.addticketsAssignment(conn, trans, ticket);
}
else
{
trans.Commit();
}
}
else
{
trans.Rollback();
}
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 DataSet getticketnodehistory(string ticketid)
{
TicketDao ticketdao = new TicketDao();
Ticket ticket = ticketdao.getticketid(ticketid);
ConnectionDao ConnectionDao = new ConnectionDao();
SqlDataAdapter adp = new SqlDataAdapter("select * from TicketNote where ticketid='" + ticket.ticketgetid + "'", ConnectionDao.getConnection());
DataSet ds2 = new DataSet();
adp.Fill(ds2);
return ds2;
}
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 DataSet getticketassignmenthistory(string ticketid)
{
TicketDao ticketdao = new TicketDao();
Ticket ticket = ticketdao.getticketid(ticketid);
ConnectionDao ConnectionDao = new ConnectionDao();
SqlDataAdapter adp = new SqlDataAdapter("select * from TicketAssignment where ticketid='" + ticket.ticketgetid + "'", ConnectionDao.getConnection());
DataSet ds3 = new DataSet();
adp.Fill(ds3);
return ds3;
}
public DataSet getpriority()
{
ConnectionDao ConnectionDao = new ConnectionDao();
SqlDataAdapter adp = new SqlDataAdapter("select * from TicketPriority", ConnectionDao.getConnection());
DataSet ds1 = new DataSet();
adp.Fill(ds1);
return ds1;
}
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 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 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 customeradd(customer customer1, Account account)
{
string str = "";
SqlConnection conn = null;
SqlTransaction trans = null;
string returnString = IdProConstants.SUCCESS;
CustomerDAO customerdao = new CustomerDAO();
AccountDao accountdao = new AccountDao();
ConnectionDao ConnectionDao = new ConnectionDao();
Customer_Account_O2M customerAccountO2M = new Customer_Account_O2M();
Customer_Account_O2MDAO customerAccountO2Mservice = new Customer_Account_O2MDAO();
try
{
conn = ConnectionDao.getConnection();
trans = conn.BeginTransaction();
customerAccountO2M.CustomerID = customerdao.addCustomerBasicInfo(conn, trans, customer1);
if (!customerAccountO2M.CustomerID.Equals(0))
{
customerAccountO2M.AccountID = accountdao.addAccountBasicInfo(conn, trans, account);
if (!customerAccountO2M.AccountID.Equals(0))
{
returnString = customerAccountO2Mservice.addCustomerAccountO2MInfo(conn, trans,customerAccountO2M);
}
str = customer1.cofirstname;
if (str != "")
{
customerAccountO2M.CustomerID = customerdao.addCustomerCoinfo(conn, trans, customer1);
if (!customerAccountO2M.CustomerID.Equals(0))
{
customerAccountO2M.AccountID = accountdao.addAccountBasicInfo(conn, trans, account);
if (!customerAccountO2M.AccountID.Equals(0))
{
returnString = customerAccountO2Mservice.addCustomerAccountO2MInfo(conn, trans, customerAccountO2M);
}
}
}
}
if (returnString.Equals(IdProConstants.SUCCESS))
{
trans.Commit();
}
else
{
trans.Rollback();
}
}
catch (Exception exception)
{
trans.Rollback();
System.Diagnostics.Trace.WriteLine("[EmployeeServices:addEmployee] Exception " + exception.StackTrace);
}
finally
{
ConnectionDao.closeConnection(conn);
}
string finalreturnvalue="";
if (returnString == "Success")
{
finalreturnvalue = "Thanks record saved successfully";
}
else
{
finalreturnvalue = "Sorry, Your request could not be processed. Please try after some time";
}
return finalreturnvalue;
}
public DataSet getcustomersarch(string phoneno)
{
ConnectionDao ConnectionDao = new ConnectionDao();
string str = "select customerid from customer where phone1 = '" + phoneno + "' or phone2 = '" + phoneno + "' ";
SqlDataAdapter adp = new SqlDataAdapter(str, ConnectionDao.getConnection());
DataSet ds1 = new DataSet();
adp.Fill(ds1);
return ds1;
}
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;
}
public DataSet getstates()
{
ConnectionDao ConnectionDao = new ConnectionDao();
SqlDataAdapter adp = new SqlDataAdapter("select * from statemaster", ConnectionDao.getConnection());
DataSet ds1 = new DataSet();
adp.Fill(ds1);
return ds1;
}
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;
}