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 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 string addticketsAssignment(SqlConnection conn, SqlTransaction trans, Ticket ticket)
{
ConnectionDao connectiondao = new ConnectionDao();
string strreturn = IdProConstants.SUCCESS;
SqlCommand cmd = null;
SqlDataReader rs = null;
// int TICKETNOTEID = 0;
string query = "INSERT INTO TicketAssignment([ticketid],[assignby],[assignto],[assigndate])VALUES (@ticketid,@assignby,@assignto,@assigndate)";
try
{
cmd = connectiondao.getSqlCommand(query, conn, trans);
SqlParameter parm1 = new SqlParameter();
parm1.ParameterName = "@ticketid";
parm1.Value = ticket.ticketid;
cmd.Parameters.Add(parm1);
SqlParameter parm2 = new SqlParameter();
parm2.ParameterName = "@assignby";
parm2.Value = HttpContext.Current.Session["username"];
cmd.Parameters.Add(parm2);
SqlParameter parm3 = new SqlParameter();
parm3.ParameterName = "@assignto";
parm3.Value = ticket.assignto;
cmd.Parameters.Add(parm3);
SqlParameter parm4 = new SqlParameter();
parm4.ParameterName = "@assigndate";
parm4.Value = ticket.assigndate;
cmd.Parameters.Add(parm4);
rs = cmd.ExecuteReader();
if (rs.Read())
{
// TICKETAssignment = Int32.Parse(rs["Assignid"].ToString());
}
}
catch (Exception exception)
{
System.Diagnostics.Trace.WriteLine("[TicketDao:addticketsTypedetail] Exception " + exception.StackTrace);
}
finally
{
connectiondao.closeDabaseEntities(cmd, rs);
}
return strreturn;
}
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 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 long addAccountBasicInfo(SqlConnection conn, SqlTransaction trans, Account account)
{
ConnectionDao connectionDao = new ConnectionDao();
SqlCommand cmd = null;
SqlDataReader rs = null;
int AccountID = 0;
string query = "INSERT INTO account([Status],[Added_By],[Added_Date]) VALUES (@Status,@Added_By,@Added_Date);SELECT Account_ID=scope_identity();";
try
{
cmd = connectionDao.getSqlCommand(query, conn, trans);
SqlParameter param1 = new SqlParameter();
param1.ParameterName = "@Status";
param1.Value = "Active";
cmd.Parameters.Add(param1);
SqlParameter param2 = new SqlParameter();
param2.ParameterName = "@Added_By";
param2.Value = HttpContext .Current.Session["username"];
cmd.Parameters.Add(param2);
SqlParameter param3 = new SqlParameter();
param3.ParameterName = "@Added_Date";
param3.Value = System.DateTime.Now;
cmd.Parameters.Add(param3);
rs = cmd.ExecuteReader();
if (rs.Read())
{
AccountID = Int32.Parse(rs["Account_ID"].ToString());
}
}
catch (Exception exception)
{
System.Diagnostics.Trace.WriteLine("[CustomerDAO:addCustomerBasicInfo] Exception " + exception.StackTrace);
}
finally
{
connectionDao.closeDabaseEntities(cmd, rs);
}
return AccountID;
}
public string addCustomerAccountO2MInfo(SqlConnection conn, SqlTransaction trans, Customer_Account_O2M customerAccount)
{
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 Customer_Account_O2M([Account_ID],[Customer_ID]) VALUES(@AccountID,@CustomerID)";
// 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 = "@AccountID";
param1.Value = customerAccount.AccountID;
cmd.Parameters.Add(param1);
SqlParameter param2 = new SqlParameter();
param2.ParameterName = "@CustomerID";
param2.Value = customerAccount.CustomerID;
cmd.Parameters.Add(param2);
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 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 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 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 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 long addCustomerBasicInfo(SqlConnection conn, SqlTransaction trans, customer customer)
{
string strdrop = "";
ConnectionDao connectionDao = new ConnectionDao();
string returnString = IdProConstants.SUCCESS;
SqlCommand cmd = null;
SqlDataReader rs = null;
long Customer_ID = 0;
string query = "INSERT INTO Customer([First_name],[Last_name],[DOB],[SSN],[Address],[City],[State],[zip],[Primary_email],[Secondary_Email],[phone1],[phone2],[Customer_Type]) VALUES (@First_name,@Last_name,@DOB,@SSN,@Address,@City,@State,@zip,@Primary_email,@Secondary_Email,@phone1,@phone2,@Customer_Type);SELECT Customer_ID=scope_identity();";
try
{
strdrop = customer.dropphone1;
cmd = connectionDao.getSqlCommand(query, conn, trans);
SqlParameter param1 = new SqlParameter();
param1.ParameterName = "@First_name";
param1.Value = customer.firstname;
cmd.Parameters.Add(param1);
SqlParameter param2 = new SqlParameter();
param2.ParameterName = "@Last_name";
param2.Value = customer.lastname;
cmd.Parameters.Add(param2);
SqlParameter param3 = new SqlParameter();
param3.ParameterName = "@SSN";
param3.Value = customer.ssn;
cmd.Parameters.Add(param3);
SqlParameter param4 = new SqlParameter();
param4.ParameterName = "@Address";
param4.Value = customer.address1;
cmd.Parameters.Add(param4);
SqlParameter param5 = new SqlParameter();
param5.ParameterName = "@City";
param5.Value = customer.city;
cmd.Parameters.Add(param5);
SqlParameter param6 = new SqlParameter();
param6.ParameterName = "@State";
param6.Value = customer.states;
cmd.Parameters.Add(param6);
SqlParameter param7 = new SqlParameter();
param7.ParameterName = "@zip";
param7.Value = customer.zip;
cmd.Parameters.Add(param7);
SqlParameter param9 = new SqlParameter();
param9.ParameterName = "@Primary_email";
param9.Value = customer.primaryemail;
cmd.Parameters.Add(param9);
SqlParameter param10 = new SqlParameter();
param10.ParameterName = "@Secondary_Email";
param10.Value = customer.secondaryemail;
cmd.Parameters.Add(param10);
SqlParameter param11 = new SqlParameter();
param11.ParameterName = "@phone1";
param11.Value = customer.phone1;
cmd.Parameters.Add(param11);
SqlParameter param12 = new SqlParameter();
param12.ParameterName = "@phone2";
param12.Value = customer.phone2;
cmd.Parameters.Add(param12);
SqlParameter param16 = new SqlParameter();
param16.ParameterName = "@Customer_Type";
param16.Value = "primary";
cmd.Parameters.Add(param16);
SqlParameter param17 = new SqlParameter();
param17.ParameterName = "@DOB";
param17.Value = customer.dob;
cmd.Parameters.Add(param17);
rs = cmd.ExecuteReader();
if (rs.Read())
{
Customer_ID = Int32.Parse(rs["Customer_ID"].ToString());
}
//while (rs.Read())
//{
// customerNumber = Int32.Parse(rs["Customer_ID"].ToString());
//}
}
catch (Exception exception)
{
System.Diagnostics.Trace.WriteLine("[CustomerDAO:addCustomerBasicInfo] Exception " + exception.StackTrace);
}
finally
{
connectionDao.closeDabaseEntities(cmd, rs);
}
return Customer_ID;
}
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 getpriority()
{
ConnectionDao ConnectionDao = new ConnectionDao();
SqlDataAdapter adp = new SqlDataAdapter("select * from TicketPriority", ConnectionDao.getConnection());
DataSet ds1 = new DataSet();
adp.Fill(ds1);
return ds1;
}
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 int addticketsdetail(SqlConnection conn, SqlTransaction trans, Ticket ticket)
{
ConnectionDao connectiondao = new ConnectionDao();
// string strreturn = IdProConstants.SUCCESS;
SqlCommand cmd = null;
SqlDataReader rs = null;
int TICKETID = 0;
string query = "INSERT INTO Ticket([accountid],[tickettype],[assignto],[Assignby],[opendate],[closedate],[status],[priority],[assigndate])VALUES (@accountid,@tickettype,@assignto,@Assignby,@opendate,@closedate,@status,@priority,@assigndate);SELECT ticketid=scope_identity();";
try
{
cmd = connectiondao.getSqlCommand(query, conn, trans);
SqlParameter parm1 = new SqlParameter();
parm1.ParameterName = "@accountid";
parm1.Value = HttpContext.Current.Session["AccountID"];
cmd.Parameters.Add(parm1);
SqlParameter parm2 = new SqlParameter();
parm2.ParameterName = "@tickettype";
parm2.Value = ticket.tickettypestring ;
cmd.Parameters.Add(parm2);
SqlParameter parm3 = new SqlParameter();
parm3.ParameterName = "@assignto";
parm3.Value = ticket.assignto;
cmd.Parameters.Add(parm3);
SqlParameter parm4 = new SqlParameter();
parm4.ParameterName = "@Assignby";
parm4.Value = HttpContext.Current.Session["username"];
cmd.Parameters.Add(parm4);
SqlParameter parm5 = new SqlParameter();
parm5.ParameterName = "@opendate";
parm5.Value = ticket.assigndate;
cmd.Parameters.Add(parm5);
SqlParameter parm6 = new SqlParameter();
parm6.ParameterName = "@closedate";
parm6.Value = "";
cmd.Parameters.Add(parm6);
SqlParameter parm7 = new SqlParameter();
parm7.ParameterName = "@status";
parm7.Value = "Open";
cmd.Parameters.Add(parm7);
SqlParameter parm8 = new SqlParameter();
parm8.ParameterName = "@priority";
parm8.Value = ticket.priorty;
cmd.Parameters.Add(parm8);
SqlParameter parm9 = new SqlParameter();
parm9.ParameterName = "@assigndate";
parm9.Value = ticket.assigndate;
cmd.Parameters.Add(parm9);
rs = cmd.ExecuteReader();
if (rs.Read())
{
TICKETID = Int32.Parse(rs["ticketid"].ToString());
}
}
catch (Exception exception)
{
System.Diagnostics.Trace.WriteLine("[TicketDao:addticketsdetail] Exception " + exception.StackTrace);
}
finally
{
connectiondao.closeDabaseEntities(cmd, rs);
}
return TICKETID;
}
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 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 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 long addCustomerCoinfo(SqlConnection conn, SqlTransaction trans, customer customer)
{
ConnectionDao connectionDao = new ConnectionDao();
string returnString = IdProConstants.SUCCESS;
SqlCommand cmd = null;
SqlDataReader rs = null;
long Customer_ID = 0;
string query = "INSERT INTO Customer([First_name],[Last_name],[DOB],[SSN],[Customer_Type]) VALUES (@First_name,@Last_name,@DOB,@SSN,@Customer_Type);SELECT Customer_ID=scope_identity();";
try
{
cmd = connectionDao.getSqlCommand(query, conn, trans);
SqlParameter param1 = new SqlParameter();
param1.ParameterName = "@First_name";
param1.Value = customer.cofirstname;
cmd.Parameters.Add(param1);
SqlParameter param2 = new SqlParameter();
param2.ParameterName = "@Last_name";
param2.Value = customer.colastname;
cmd.Parameters.Add(param2);
SqlParameter param3 = new SqlParameter();
param3.ParameterName = "@DOB";
param3.Value = customer.Codob;
cmd.Parameters.Add(param3);
SqlParameter param4 = new SqlParameter();
param4.ParameterName = "@SSN";
param4.Value = customer.cossn;
cmd.Parameters.Add(param4);
SqlParameter param16 = new SqlParameter();
param16.ParameterName = "@Customer_Type";
param16.Value = "Secondary";
cmd.Parameters.Add(param16);
rs = cmd.ExecuteReader();
if (rs.Read())
{
Customer_ID = Int32.Parse(rs["Customer_ID"].ToString());
}
}
catch (Exception exception)
{
System.Diagnostics.Trace.WriteLine("[CustomerDAO:addCustomerBasicInfo] Exception " + exception.StackTrace);
}
finally
{
connectionDao.closeDabaseEntities(cmd, rs);
}
// return returnString;
return Customer_ID;
}
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 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 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 getstates()
{
ConnectionDao ConnectionDao = new ConnectionDao();
SqlDataAdapter adp = new SqlDataAdapter("select * from statemaster", ConnectionDao.getConnection());
DataSet ds1 = new DataSet();
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;
}