// get the customers information for checking with database
public static Customer GetCustomerInfo(string userName)
{
Customer cust = null;
string query = "SELECT CustomerId, CustFirstName, CustLastName, CustAddress, CustCity, CustProv, CustPostal, CustCountry, " +
"CustHomePhone, CustBusPhone, CustEmail, UserName " +
"FROM Customers " +
"WHERE UserName = @UserName";
using (SqlConnection connection = new SqlConnection(TravelExpertsDB.GetConnectionString()))
{
using (SqlCommand cmd = new SqlCommand(query, connection))
{
cmd.Parameters.AddWithValue("@UserName", userName);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
cust = new Customer();
cust.CustomerId = Convert.ToInt32(reader["CustomerId"]);
cust.CustFirstName = reader["CustFirstName"].ToString();
cust.CustLastName = reader["CustLastName"].ToString();
cust.CustAddress = reader["CustAddress"].ToString();
cust.CustCity = reader["CustCity"].ToString();
cust.CustProv = reader["CustProv"].ToString();
cust.CustPostal = reader["CustPostal"].ToString();
cust.CustCountry = reader["CustCountry"].ToString();
cust.CustHomePhone = reader["CustHomePhone"].ToString();
cust.CustBusPhone = reader["CustBusPhone"].ToString();
cust.CustEmail = reader["CustEmail"].ToString();
cust.UserName = reader["UserName"].ToString();
}
}
}
return(cust);
}
// register customers information
public static void CustomerRegister(Customer customer)
{
string sql = "INSERT INTO Customers "
+ "(CustFirstName, CustLastName, CustAddress, CustCity, CustProv, CustPostal, CustCountry, CustHomePhone, CustBusPhone, CustEmail, UserName, Password) "
+ "VALUES (@CustFirstName, @CustLastName, @CustAddress, @CustCity, @CustProv, @CustPostal, @CustCountry, @CustHomePhone, @CustBusPhone, @CustEmail, @UserName, @Password)";
using (SqlConnection con = new SqlConnection(TravelExpertsDB.GetConnectionString()))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
cmd.Parameters.AddWithValue("CustFirstName", customer.CustFirstName);
cmd.Parameters.AddWithValue("CustLastName", customer.CustLastName);
cmd.Parameters.AddWithValue("CustAddress", customer.CustAddress);
cmd.Parameters.AddWithValue("CustCity", customer.CustCity);
cmd.Parameters.AddWithValue("CustProv", customer.CustProv);
cmd.Parameters.AddWithValue("CustPostal", customer.CustPostal);
cmd.Parameters.AddWithValue("CustCountry", customer.CustCountry);
cmd.Parameters.AddWithValue("CustHomePhone", customer.CustHomePhone);
cmd.Parameters.AddWithValue("CustBusPhone", customer.CustBusPhone);
cmd.Parameters.AddWithValue("CustEmail", customer.CustEmail);
cmd.Parameters.AddWithValue("UserName", customer.UserName);
cmd.Parameters.AddWithValue("Password", customer.Password);
con.Open();
cmd.ExecuteNonQuery();
}
}
}
//check login credentials
public static Customer CustomerLogin(string custUserName)
{
Customer cust = null;
string query = "SELECT CustomerId, UserName, Password, CustFirstName " +
"FROM Customers " +
"WHERE UserName = @UserName";
using (SqlConnection connection = new SqlConnection(TravelExpertsDB.GetConnectionString()))
{
using (SqlCommand cmd = new SqlCommand(query, connection))
{
cmd.Parameters.AddWithValue("@UserName", custUserName);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
cust = new Customer();
cust.CustomerId = Convert.ToInt32(reader["CustomerId"]);
cust.UserName = reader["UserName"].ToString();
cust.Password = reader["Password"].ToString();
cust.CustFirstName = reader["CustFirstName"].ToString();
}
}
}
return(cust);
}
// edit customers information
public static int UpdateCustomer(Customer original_Customer,
Customer new_Customer)
{
int updateCount = 0;
string sql = "UPDATE Customers SET "
+ "CustFirstName = @CustFirstName, "
+ "CustLastName = @CustLastName, "
+ "CustAddress = @CustAddress, "
+ "CustCity = @CustCity, "
+ "CustProv = @CustProv, "
+ "CustPostal = @CustPostal, "
+ "CustCountry = @CustCountry, "
+ "CustHomePhone = @CustHomePhone, "
+ "CustBusPhone = @CustBusPhone, "
+ "CustEmail = @CustEmail, "
+ "UserName = @UserName, "
+ "Password = @Password "
+ "WHERE CustomerId = @original_CustomerId";
using (SqlConnection con = new SqlConnection(TravelExpertsDB.GetConnectionString()))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
cmd.Parameters.AddWithValue("CustFirstName", new_Customer.CustFirstName);
cmd.Parameters.AddWithValue("CustLastName", new_Customer.CustLastName);
cmd.Parameters.AddWithValue("CustAddress", new_Customer.CustAddress);
cmd.Parameters.AddWithValue("CustCity", new_Customer.CustCity);
cmd.Parameters.AddWithValue("CustProv", new_Customer.CustProv);
cmd.Parameters.AddWithValue("CustPostal", new_Customer.CustPostal);
cmd.Parameters.AddWithValue("CustCountry", new_Customer.CustCountry);
cmd.Parameters.AddWithValue("CustHomePhone", new_Customer.CustHomePhone);
cmd.Parameters.AddWithValue("CustBusPhone", new_Customer.CustBusPhone);
cmd.Parameters.AddWithValue("CustEmail", new_Customer.CustEmail);
cmd.Parameters.AddWithValue("UserName", new_Customer.UserName);
cmd.Parameters.AddWithValue("Password", new_Customer.Password);
cmd.Parameters.AddWithValue("original_CustomerId", original_Customer.CustomerId);
con.Open();
updateCount = cmd.ExecuteNonQuery();
}
}
return(updateCount);
}
public static List <CustomerBookings> GetPackBookings(int custId) // custID = userID which is passed by the controller
{
//make an empty list
List <CustomerBookings> accPackBookingsList = new List <CustomerBookings>();
string sql1 = "Select C.CustFirstName, P.ProdName,BD.BasePrice, Pack.PkgName,Pack.PkgBasePrice,B.BookingId from "
+ "Customers as C join Bookings as B "
+ "on C.CustomerId= B.CustomerId "
+ "join Packages as Pack on "
+ "B.PackageId = Pack.PackageId "
+ "join BookingDetails as BD "
+ "on B.BookingId = BD.BookingId "
+ "join Products_Suppliers as PS "
+ "on BD.ProductSupplierId= PS.ProductSupplierId "
+ "join Products as P "
+ "on Ps.ProductId=P.ProductId "
+ "where C.CustomerId = @CustomerId ";
string sql2 = "Select C.CustFirstName, P.ProdName, BD.BasePrice, B.BookingId from "
+ "Customers as C join Bookings as B "
+ "on C.CustomerId= B.CustomerId "
+ "join BookingDetails as BD "
+ "on B.BookingId = BD.BookingId "
+ "join Products_Suppliers as PS "
+ "on BD.ProductSupplierId= PS.ProductSupplierId "
+ "join Products as P "
+ "on Ps.ProductId=P.ProductId "
+ "where C.CustomerId = @CustomerId ";
using (SqlConnection con = new SqlConnection(TravelExpertsDB.GetConnectionString()))
{
//used in PACKAGES after PRODUCTS
CustomerBookings packbooking;
packbooking = new CustomerBookings();
//PRODUCTS
using (SqlCommand cmd = new SqlCommand(sql2, con))
{
//put userID into sql
cmd.Parameters.AddWithValue("@CustomerId", custId);
//open connectoin
con.Open();
SqlDataReader dr = cmd.ExecuteReader(); //variable to capture all the data that is gathered by sql query
CustomerBookings prodbooking; // creating object based off of customerBookings model
decimal?total = 0; // empty var for use later
while (dr.Read()) //for each set of data that is sent by the sql query
{
if (dr["ProdName"] != null) // if there is relevant data
{
//put data into object earlier made object
prodbooking = new CustomerBookings();
prodbooking.PRODCustFirstName = dr["CustFirstName"].ToString();
prodbooking.PRODProdName = dr["ProdName"].ToString();
prodbooking.PRODBasePrice = Convert.ToDecimal(dr["BasePrice"]);
prodbooking.PRODBookingId = Convert.ToInt32(dr["BookingId"]);
//add price to the total for each set of data
total += prodbooking.PRODBasePrice;
//sending the prod information to the list
accPackBookingsList.Add(prodbooking);
}
}
//close the reader
dr.Close();
//make another object
CustomerBookings prodbookingtest = new CustomerBookings();
//put data into object
prodbookingtest.PRODTotal = total;
//send object to the list
accPackBookingsList.Add(prodbookingtest);
//close connection
con.Close();
}
//PACKAGES
//follow same principals as prods, please refer to PRODUCTS for comments
using (SqlCommand cmd = new SqlCommand(sql1, con))
{
cmd.Parameters.AddWithValue("@CustomerId", custId);
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
decimal?total = 0;
while (dr.Read())
{
if (dr["PkgName"] != null)
{
packbooking.PACKCustFirstName = dr["CustFirstName"].ToString();
packbooking.PACKProdName = dr["ProdName"].ToString();
packbooking.PACKBasePrice = Convert.ToDecimal(dr["BasePrice"]);
packbooking.PACKPkgName = dr["PkgName"].ToString();
packbooking.PACKPkgBasePrice = Convert.ToDecimal(dr["PkgBasePrice"]);
packbooking.PACKBookingId = Convert.ToInt32(dr["BookingId"]);
total += packbooking.PACKPkgBasePrice;
//sending the packes information to the list
accPackBookingsList.Add(packbooking);
}
}
dr.Close();
CustomerBookings packbookingtest = new CustomerBookings();
packbookingtest.PACKTotal = total;
accPackBookingsList.Add(packbookingtest);
}
}
//return the list to whatever calls the method
return(accPackBookingsList);
}