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 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 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 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 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;
}
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 int addticketsTypedetail(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 TicketType([Tickettype],[Priority],[assignment])VALUES (@Tickettype,@Priority,@assignment);SELECT TicketType_id=scope_identity();";
try
{
cmd = connectiondao.getSqlCommand(query, conn, trans);
SqlParameter parm1 = new SqlParameter();
parm1.ParameterName = "@Tickettype";
parm1.Value = ticket.tickettypestring;
cmd.Parameters.Add(parm1);
SqlParameter parm2 = new SqlParameter();
parm2.ParameterName = "@Priority";
parm2.Value = ticket.priorty;
cmd.Parameters.Add(parm2);
SqlParameter parm3 = new SqlParameter();
parm3.ParameterName = "@assignment";
parm3.Value = ticket.assignto;
cmd.Parameters.Add(parm3);
rs = cmd.ExecuteReader();
if (rs.Read())
{
TICKETNOTEID = Int32.Parse(rs["TicketType_id"].ToString());
}
}
catch (Exception exception)
{
System.Diagnostics.Trace.WriteLine("[TicketDao:addticketsTypedetail] Exception " + exception.StackTrace);
}
finally
{
connectiondao.closeDabaseEntities(cmd, rs);
}
return TICKETNOTEID;
}
public int addticketsNotedetail(SqlConnection conn, SqlTransaction trans, Ticket ticket)
{
ConnectionDao connectiondao = new ConnectionDao();
SqlCommand cmd = null;
SqlDataReader rs = null;
int TICKETNOTEID = 0;
string query = "INSERT INTO TicketNote([ticketid],[notedate],[note])VALUES (@ticketid,@notedate,@note);SELECT Noteid=scope_identity();";
// string query = "INSERT INTO TicketNote([ticketid],[note])VALUES (@ticketid,@note);SELECT Noteid=scope_identity();";
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 = "@notedate";
parm2.Value = ticket.assigndate;
cmd.Parameters.Add(parm2);
SqlParameter parm3 = new SqlParameter();
parm3.ParameterName = "@note";
parm3.Value = ticket.note;
cmd.Parameters.Add(parm3);
rs = cmd.ExecuteReader();
if (rs.Read())
{
TICKETNOTEID = Int32.Parse(rs["Noteid"].ToString());
}
}
catch (Exception exception)
{
System.Diagnostics.Trace.WriteLine("[TicketDao:addticketsNotedetail] Exception " + exception.StackTrace);
}
finally
{
connectiondao.closeDabaseEntities(cmd, rs);
}
return TICKETNOTEID;
}
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 updateUser(SqlConnection conn, SqlTransaction trans, User user)
{
ConnectionDao connectionDao = new ConnectionDao();
string returnString = IdProConstants.SUCCESS;
SqlCommand cmd = null;
SqlDataReader rs = null;
UserServices userService = new UserServices();
string query = null;
bool updatePassword = !userService.getHashPassword("defaultPassWord").Equals(user.Password);
if (updatePassword)
{
query = "update Users set UserName=@UserName,Password=@Password,Role=@Role where UserName='******'";
}
else
{
query = "update Users set UserName=@UserName,Role=@Role where UserName='******'";
}
try
{
cmd = connectionDao.getSqlCommand(query, conn, trans);
SqlParameter param1 = new SqlParameter();
param1.ParameterName = "@UserName";
param1.Value = user.Username;
cmd.Parameters.Add(param1);
if (updatePassword)
{
SqlParameter param2 = new SqlParameter();
param2.ParameterName = "@Password";
param2.Value = userService.getHashPassword(user.Password);
cmd.Parameters.Add(param2);
}
SqlParameter param3 = new SqlParameter();
param3.ParameterName = "@Role";
param3.Value = user.Role;
cmd.Parameters.Add(param3);
cmd.ExecuteScalar();
}
catch (Exception exception)
{
System.Diagnostics.Trace.WriteLine("[UserDAO:updateUser] Exception " + exception.StackTrace);
returnString = IdProConstants.FAIL;
}
finally
{
connectionDao.closeDabaseEntities(cmd, rs);
}
return returnString;
}
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 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 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;
}