// create new customer account
public static void CreateAccount(Customer cust)
{
string addCustomerQuery = @"INSERT INTO Customers " +
"(CustFirstName, CustLastName, CustAddress, CustCity, CustProv, CustPostal, CustCountry, CustHomePhone, CustBusPhone, CustEmail, CustPass) " +
"VALUES (@CustFirstName, @CustLastName, @CustAddress, @CustCity, @CustProv, @CustPostal, @CustCountry, @CustHomePhone, @CustBusPhone, @CustEmail, @CustPass)";
using (SqlConnection con = TravelExpertsConn.GetConnection())
{
using (SqlCommand sqlCommand = new SqlCommand(addCustomerQuery, con))
{
// hash password
string password = Convert.ToString(cust.CustPass);
var hash = PassHash.SecurePasswordHasher.Hash(password);
sqlCommand.Parameters.AddWithValue("@CustFirstName", cust.CustFirstName);
sqlCommand.Parameters.AddWithValue("@CustLastName", cust.CustLastName);
sqlCommand.Parameters.AddWithValue("@CustAddress", cust.CustAddress);
sqlCommand.Parameters.AddWithValue("@CustCity", cust.CustCity);
sqlCommand.Parameters.AddWithValue("@CustProv", cust.CustProv);
sqlCommand.Parameters.AddWithValue("@CustPostal", cust.CustPostal);
sqlCommand.Parameters.AddWithValue("@CustCountry", cust.CustCountry);
sqlCommand.Parameters.AddWithValue("@CustHomePhone", cust.CustHomePhone);
sqlCommand.Parameters.AddWithValue("@CustBusPhone", cust.CustBusPhone);
sqlCommand.Parameters.AddWithValue("@CustEmail", cust.CustEmail);
sqlCommand.Parameters.AddWithValue("@CustPass", hash);
con.Open();
sqlCommand.ExecuteNonQuery();
}
}
}
// customer login
public static int CustomerLogin(CustomerLogin login)
{
int custId = -1;
string addCustomerQuery = @"SELECT CustomerId, CustPass " +
"FROM Customers WHERE CustEmail = @CustEmail";
using (SqlConnection con = TravelExpertsConn.GetConnection())
{
using (SqlCommand sqlCommand = new SqlCommand(addCustomerQuery, con))
{
sqlCommand.Parameters.AddWithValue("@CustEmail", login.CustEmail);
con.Open();
SqlDataReader dr = sqlCommand.ExecuteReader();
if (dr.Read())
{
// retrieve and verify password against hash
string hashpass = Convert.ToString(dr["CustPass"]);
var result = PassHash.SecurePasswordHasher.Verify(login.CustPass, hashpass);
if (result)
{
custId = Convert.ToInt32(dr["CustomerId"]);
}
}
dr.Close();
}
}
return(custId);
}
public static List <Package> GetPackages()
{
List <Package> packageList = new List <Package>();
string getPkgQuery = @"SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission, PkgImage " +
"FROM Packages";
using (SqlConnection con = TravelExpertsConn.GetConnection())
{
using (SqlCommand cmd = new SqlCommand(getPkgQuery, con))
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
Package packages;
while (reader.Read())
{
packages = new Package();
packages.PackageId = Convert.ToInt32(reader["PackageId"]);
packages.PkgName = reader["PkgName"].ToString();
packages.PkgStartDate = Convert.ToDateTime(reader["PkgStartDate"]);
packages.PkgEndDate = Convert.ToDateTime(reader["PkgEndDate"]);
packages.PkgDesc = Convert.ToString(reader["PkgDesc"]);
packages.PkgBasePrice = Convert.ToInt32(reader["PkgBasePrice"]);
packages.PkgAgencyCommission = Convert.ToInt32(reader["PkgAgencyCommission"]);
packages.PkgImage = reader["PkgImage"].ToString();
packageList.Add(packages);
}
con.Close();
}
}
return(packageList);
}
// edit customer
public static int EditCustomer(int id, Customer newCust)
{
int updateCount = 0;
string updateQuery = @"UPDATE Customers " +
"SET CustID = @CustID, " +
"CustFirstName = @CustFirstName, " +
"CustLastName = @CustLastName, " +
"CustAddress = @CustAddress, " +
"CustCity = @CustCity, " +
"CustProv = @CustProv, " +
"CustPostal = @CustPostal, " +
"CustCountry = @CustCountry, " +
"CustHomePhone = @CustHomePhone, " +
"CustBusPhone = @CustBusPhone, " +
"CustEmail = @CustEmail, " +
"CustPass = @CustPass " +
"WHERE CustomerId = @CurrentCustId";
using (SqlConnection con = TravelExpertsConn.GetConnection())
{
using (SqlCommand sqlCommand = new SqlCommand(updateQuery, con))
{
string password = Convert.ToString(newCust.CustPass);
var hash = PassHash.SecurePasswordHasher.Hash(password);
sqlCommand.Parameters.AddWithValue("@CurrentCustId", id);
sqlCommand.Parameters.AddWithValue("@CustID", newCust.CustID);
sqlCommand.Parameters.AddWithValue("@CustFirstName", newCust.CustFirstName);
sqlCommand.Parameters.AddWithValue("@CustLastName", newCust.CustLastName);
sqlCommand.Parameters.AddWithValue("@CustAddress", newCust.CustAddress);
sqlCommand.Parameters.AddWithValue("@CustCity", newCust.CustCity);
sqlCommand.Parameters.AddWithValue("@CustProv", newCust.CustProv);
sqlCommand.Parameters.AddWithValue("@CustPostal", newCust.CustPostal);
sqlCommand.Parameters.AddWithValue("@CustCountry", newCust.CustCountry);
sqlCommand.Parameters.AddWithValue("@CustHomePhone", newCust.CustHomePhone);
sqlCommand.Parameters.AddWithValue("@CustBusPhone", newCust.CustBusPhone);
sqlCommand.Parameters.AddWithValue("@CustEmail", newCust.CustEmail);
//if (newCust.AgentId == null)
//{
// sqlCommand.Parameters.AddWithValue("@AgentId", DBNull.Value);
//}
//else
//{
// sqlCommand.Parameters.AddWithValue("@AgentId", Convert.ToInt32(newCust.AgentId));
//}
sqlCommand.Parameters.AddWithValue("@CustPass", hash);
con.Open();
sqlCommand.ExecuteNonQuery();
}
}
return(updateCount);
}
// return customer details
public static Customer CustomerDetails(int custID)
{
Customer details = null;
string noteDetailsQuery = @"SELECT CustID, CustFirstName, CustLastName, CustAddress, CustCity, CustProv, CustPostal, CustCountry, CustHomePhone, CustBusPhone, CustEmail " +
"FROM Customers WHERE CustomerId = @CustomerId";
using (SqlConnection con = TravelExpertsConn.GetConnection())
{
using (SqlCommand cmd = new SqlCommand(noteDetailsQuery, con))
{
cmd.Parameters.AddWithValue("@CustomerId", custID);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
details = new Customer();
details.CustID = reader["CustID"].ToString();
details.CustFirstName = reader["CustFirstName"].ToString();
details.CustLastName = reader["CustLastName"].ToString();
details.CustAddress = reader["CustAddress"].ToString();
details.CustCity = reader["CustCity"].ToString();
details.CustProv = reader["CustProv"].ToString();
details.CustPostal = reader["CustPostal"].ToString();
details.CustCountry = reader["CustCountry"].ToString();
details.CustHomePhone = reader["CustHomePhone"].ToString();
details.CustBusPhone = reader["CustBusPhone"].ToString();
details.CustEmail = reader["CustEmail"].ToString();
//if (reader["AgentId"] != DBNull.Value)
//{
// details.AgentId = Convert.ToInt32(reader["AgentId"]);
//}
//else
//{
// details.AgentId = null;
//}
}
con.Close();
}
}
return(details);
}
// list function for retrieving AgentIds - created for later enabling
public static List <int?> GetAgentIdDropdown()
{
List <int?> agentIdList = new List <int?>();
string getAgentIdQuery = @"SELECT AgentId FROM Agents";
using (SqlConnection con = TravelExpertsConn.GetConnection())
{
using (SqlCommand cmd = new SqlCommand(getAgentIdQuery, con))
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
Customer Agents = new Customer();
while (reader.Read())
{
Agents.AgentId = Convert.ToInt32(reader["AgentId"]);
agentIdList.Add(Agents.AgentId);
}
con.Close();
}
}
return(agentIdList);
}
public static List <BookingDetail> getPurchasedDetails(int custID)
{
List <BookingDetail> purchasedDetails = new List <BookingDetail>();
BookingDetail bDetails;
string getPurchasedDetailsQuery =
"SELECT bd.BookingId, bd.Description, bd.Destination, bd.BasePrice, bd.AgencyCommission " +
"FROM Customers AS cs " +
"INNER JOIN Bookings AS bk " +
"ON cs.CustomerId = bk.CustomerId " +
"INNER JOIN BookingDetails AS bd " +
"ON bk.BookingId = bd.BookingId " +
"WHERE cs.CustomerId = @CustomerId " +
"ORDER BY bd.BookingId";
using (SqlConnection con = TravelExpertsConn.GetConnection())
{
using (SqlCommand cmd = new SqlCommand(getPurchasedDetailsQuery, con))
{
cmd.Parameters.AddWithValue("@CustomerId", custID);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
bDetails = new BookingDetail();
bDetails.BookingId = (int)reader["BookingId"];
bDetails.Description = reader["Description"].ToString();
bDetails.Destination = reader["Destination"].ToString();
bDetails.BasePrice = (decimal)reader["BasePrice"];
bDetails.AgencyCommission = (decimal)reader["AgencyCommission"];
purchasedDetails.Add(bDetails);
}
con.Close();
}
}
return(purchasedDetails);
}