// By Joel
internal static void InsertLogin(CustomerLogin login)
{
SqlConnection conn = TravelExpertsDB.GetConnection();
string insStmt = "INSERT INTO Logins (CustomerId, UserName, Password) VALUES (@cID, @uname, @pw)";
SqlCommand cmd = new SqlCommand(insStmt, conn);
cmd.Parameters.AddWithValue("@cID", login.CustomerId);
cmd.Parameters.AddWithValue("@uname", login.UserName);
cmd.Parameters.AddWithValue("@pw", login.Password);
try
{
conn.Open();
cmd.ExecuteNonQuery(); //Insert
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public static bool ResetCustomerPassword(CustomerLogin reset)
{
SqlConnection con = TravelExpertsDB.GetConnection();
string UpdateSmt = "Update Logins " +
"SET Password = @Password " +
"WHERE CustomerId = @CustomerId";
SqlCommand cmd = new SqlCommand(UpdateSmt, con);
cmd.Parameters.AddWithValue("@CustomerId", reset.CustomerId);
cmd.Parameters.AddWithValue("@Password", reset.Password);
try
{
con.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public static Customer GetCustomerByCustomerId(int CustomerId)
{
Customer cust = null; // found customer
// define connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// define the select query command
string selectQuery = "select * " +
"from Customers " +
"where CustomerId = @CustomerId";
SqlCommand selectCommand = new SqlCommand(selectQuery, connection);
selectCommand.Parameters.AddWithValue("@CustomerId", CustomerId);
try
{
// open the connection
connection.Open();
// execute the query
SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
// process the result if any
if (reader.Read()) // if there is customer
{
cust = new Customer();
cust.CustomerId = (int)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();
if (reader["AgentId"] == DBNull.Value)
{
cust.AgentId = null;
}
else
{
cust.AgentId = (int)reader["AgentId"];
}
}
}
catch (Exception ex)
{
throw ex; // let the form handle it
}
finally
{
connection.Close(); // close connecto no matter what
}
return(cust);
}
public static List <OrderSummary> GetOrderSummaryByBookingId(int BookingId)
{
List <OrderSummary> orderSummaries = new List <OrderSummary>(); // make an empty list
OrderSummary orderSummary;
// create connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// create select command
string selectString = "SELECT BookingDetailId, TripStart, TripEnd, Description, Destination, p.ProdName, s.SupName, bd.BasePrice, f.FeeAmt " +
"FROM BookingDetails bd INNER JOIN Products_Suppliers ps ON bd.ProductSupplierId = ps.ProductSupplierId " +
"INNER JOIN Products p ON ps.ProductId = p.ProductId " +
"INNER JOIN Suppliers s ON ps.SupplierId = s.SupplierId " +
"INNER JOIN Fees f ON bd.FeeId = f.FeeId " +
"WHERE bd.BookingId = @BookingId " +
"ORDER BY BookingDetailId";
SqlCommand selectCommand = new SqlCommand(selectString, connection);
selectCommand.Parameters.AddWithValue("@BookingId", BookingId);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
orderSummary = new OrderSummary();
orderSummary.BookingDetailId = (int)reader["BookingDetailId"];
orderSummary.TripStart = (DateTime)reader["TripStart"];
orderSummary.TripEnd = (DateTime)reader["TripEnd"];
orderSummary.Description = reader["Description"].ToString();
orderSummary.Destination = reader["Destination"].ToString();
orderSummary.ProdName = reader["ProdName"].ToString();
orderSummary.SupName = reader["SupName"].ToString();
orderSummary.BasePrice = (decimal)reader["BasePrice"];
orderSummary.FeeAmt = (decimal)reader["FeeAmt"];
orderSummary.TaxAmt = (orderSummary.BasePrice + orderSummary.FeeAmt) * TAX_RATE;
orderSummary.TotalPrice = orderSummary.BasePrice + orderSummary.FeeAmt + orderSummary.TaxAmt;
orderSummaries.Add(orderSummary);
}
reader.Close();
}
catch (Exception ex)
{
throw ex; // throw it to the form to handle
}
finally
{
connection.Close();
}
return(orderSummaries);
}
public static BookingDetail GetBookingDetailByBookingId(int BookingId)
{
BookingDetail bookingDetail = null; // found booking
// define connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// define the select query command
string selectQuery = "select * " +
"from BookingDetails " +
"where BookingId = @BookingId";
SqlCommand selectCommand = new SqlCommand(selectQuery, connection);
selectCommand.Parameters.AddWithValue("@BookingId", BookingId);
try
{
// open the connection
connection.Open();
// execute the query
SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
// process the result if any
if (reader.Read()) // if there is customer
{
bookingDetail = new BookingDetail();
bookingDetail.BookingDetailId = (int)reader["BookingDetailId"];
bookingDetail.ItineraryNo = reader["ItineraryNo"] as float?;
bookingDetail.TripStart = reader["TripStart"] as DateTime?;
bookingDetail.TripEnd = reader["TripEnd"] as DateTime?;
bookingDetail.Description = reader["Description"].ToString();
bookingDetail.Destination = reader["Destination"].ToString();
bookingDetail.BasePrice = (decimal)reader["BasePrice"];
bookingDetail.AgencyCommission = (decimal)reader["AgencyCommission"];
bookingDetail.BookingId = reader["BookingId"] as int?;
bookingDetail.RegionId = reader["RegionId"].ToString();
bookingDetail.ClassId = reader["ClassId"].ToString();
bookingDetail.FeeId = reader["FeeId"].ToString();
bookingDetail.ProductSupplierId = reader["ProductSupplierId"] as int?;
}
}
catch (Exception ex)
{
throw ex; // let the form handle it
}
finally
{
connection.Close(); // close connecto no matter what
}
return(bookingDetail);
}
public static bool UpdateCustomerByCustomerId(int custId, Customer cust)
{
SqlConnection con = TravelExpertsDB.GetConnection();
string UpdateSmt = "UPDATE Customers " +
"SET CustFirstName = @CustFirstName, " +
"CustLastName = @CustLastName, " +
"CustAddress = @CustAddress, " +
"CustCity = @CustCity, " +
"CustProv = @CustProv, " +
"CustPostal = @CustPostal, " +
"CustHomePhone = @CustHomePhone, " +
"CustBusPhone = @CustBusphone, " +
"CustEmail = @CustEmail " +
"WHERE CustomerId = @CustomerId";
SqlCommand cmd = new SqlCommand(UpdateSmt, con);
cmd.Parameters.AddWithValue("@CustomerId", custId);
cmd.Parameters.AddWithValue("@CustFirstName", cust.CustFirstName);
cmd.Parameters.AddWithValue("@CustLastName", cust.CustLastName);
cmd.Parameters.AddWithValue("@CustAddress", cust.CustAddress);
cmd.Parameters.AddWithValue("@CustCity", cust.CustCity);
cmd.Parameters.AddWithValue("@CustProv", cust.CustProv);
cmd.Parameters.AddWithValue("@CustPostal", cust.CustPostal);
cmd.Parameters.AddWithValue("@CustHomePhone", cust.CustHomePhone);
cmd.Parameters.AddWithValue("@CustBusPhone", cust.CustBusPhone);
cmd.Parameters.AddWithValue("@CustEmail", cust.CustEmail);
try
{
con.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public static List <Booking> GetBookingsByCustomerId(int CustomerId)
{
List <Booking> bookings = new List <Booking>();
Booking booking = null; // found booking
// define connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// define the select query command
string selectQuery = "select * " +
"from Bookings " +
"where CustomerId = @CustomerId";
SqlCommand selectCommand = new SqlCommand(selectQuery, connection);
selectCommand.Parameters.AddWithValue("@CustomerId", CustomerId);
try
{
// open the connection
connection.Open();
// execute the query
SqlDataReader reader = selectCommand.ExecuteReader();
// process the result if any
while (reader.Read()) // if there is customer
{
booking = new Booking();
booking.BookingId = (int)reader["BookingId"];
booking.BookingDate = reader["BookingDate"] as DateTime?;
booking.BookingNo = reader["BookingNo"].ToString();
booking.TravelerCount = reader["TravelerCount"] as float?;
booking.CustomerId = reader["CustomerId"] as int?;
booking.TripTypeId = reader["TripTypeId"].ToString();
booking.PackageId = reader["PackageId"] as int?;
bookings.Add(booking);
}
}
catch (Exception ex)
{
throw ex; // let the form handle it
}
finally
{
connection.Close(); // close connecto no matter what
}
return(bookings);
}
public static List <Customer> GetAllCustomers()
{
List <Customer> customers = new List <Customer>(); // make an empty list
Customer cust;
// create connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// create select command
string selectString = "SELECT * FROM Customers " +
"ORDER BY CustomerId";
SqlCommand selectCommand = new SqlCommand(selectString, connection);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
cust = new Customer();
cust.CustomerId = (int)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.AgentId = (int)reader["AgentId"];
customers.Add(cust);
}
reader.Close();
}
catch (Exception ex)
{
throw ex; // throw it to the form to handle
}
finally
{
connection.Close();
}
return(customers);
}
public static Package GetPackageByPackageId(int PackageId)
{
Package package = null; // found package
// define connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// define the select query command
string selectQuery = "select * " +
"from Packages " +
"where PackageId = @PackageId";
SqlCommand selectCommand = new SqlCommand(selectQuery, connection);
selectCommand.Parameters.AddWithValue("@PackageId", PackageId);
try
{
// open the connection
connection.Open();
// execute the query
SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
// process the result if any
if (reader.Read()) // if there is customer
{
package = new Package();
package.PackageId = (int)reader["PackageId"];
package.PkgName = reader["PkgName"].ToString();
package.PkgStartDate = reader["PkgStartDate"] as DateTime?;
package.PkgEndDate = reader["PkgEndDate"] as DateTime?;
package.PkgDesc = reader["PkgDesc"].ToString();
package.PkgBasePrice = (decimal)reader["PkgBasePrice"];
package.PkgAgencyCommission = (decimal)reader["PkgAgencyCommission"];
}
}
catch (Exception ex)
{
throw ex; // let the form handle it
}
finally
{
connection.Close(); // close connecto no matter what
}
return(package);
}
public static int GetPackageIdByBookingId(int BookingId)
{
int packageId = 0;
SqlConnection connection = TravelExpertsDB.GetConnection();
// define the select query command
string selectQuery = "SELECT PackageId " +
"FROM Bookings " +
"WHERE BookingId = @BookingId";
//$sql_pkg_id = "SELECT PackageId FROM bookings where bookings.bookingId = '$bookingId';";
SqlCommand selectCommand = new SqlCommand(selectQuery, connection);
selectCommand.Parameters.AddWithValue("@BookingId", BookingId);
try
{
// open the connection
connection.Open();
// execute the query
SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
// process the result if any
if (reader.Read()) // if there is booking
{
//booking = new Booking();
//booking.PackageId = (int)reader["PackageId"];
packageId = (int)reader["PackageId"];
}
}
catch (Exception ex)
{
throw ex; // let the form handle it
}
finally
{
connection.Close(); // close connection no matter what
}
return(packageId);
}
public static Fee GetFeeByFeeId(string FeeId)
{
Fee fee = null; // found booking
// define connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// define the select query command
string selectQuery = "select * " +
"from Fees " +
"where FeeId = @FeeId";
SqlCommand selectCommand = new SqlCommand(selectQuery, connection);
selectCommand.Parameters.AddWithValue("@FeeId", FeeId);
try
{
// open the connection
connection.Open();
// execute the query
SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
// process the result if any
if (reader.Read()) // if there is fee
{
fee = new Fee();
fee.FeeId = reader["FeeId"].ToString();
fee.FeeName = reader["FeeName"].ToString();
fee.FeeAmt = (decimal)reader["FeeAmt"];
fee.FeeDesc = reader["FeeDesc"].ToString();
}
}
catch (Exception ex)
{
throw ex; // let the form handle it
}
finally
{
connection.Close(); // close connecto no matter what
}
return(fee);
}
/// <summary>
/// Inserts the customer.
/// </summary>
/// <param name="customer">The customer.</param>
/// <returns>The new Customer's Customer ID</returns>
/// By Joel
public static int?InsertCustomer(Customer customer)
{
int?custID = null;
SqlConnection conn = TravelExpertsDB.GetConnection();
string columns = " CustFirstName, CustLastName, CustAddress, CustCity, CustProv, " +
"CustPostal, CustCountry, CustHomePhone, CustBusPhone, CustEmail";
string values = "@fname, @lname, @addr, @city, @prov, @post, @country, @phome, @pbus, @email";
string insStmt = "INSERT INTO Customers (" + columns + ") VALUES (" + values + ")";
SqlCommand cmd = new SqlCommand(insStmt, conn);
cmd.Parameters.AddWithValue("@fname", customer.CustFirstName);
cmd.Parameters.AddWithValue("@lname", customer.CustLastName);
cmd.Parameters.AddWithValue("@addr", customer.CustAddress);
cmd.Parameters.AddWithValue("@city", customer.CustCity);
cmd.Parameters.AddWithValue("@prov", customer.CustProv);
cmd.Parameters.AddWithValue("@post", customer.CustPostal);
cmd.Parameters.AddWithValue("@country", customer.CustCountry ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("@phome", customer.CustHomePhone);
cmd.Parameters.AddWithValue("@pbus", customer.CustBusPhone ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("@email", customer.CustEmail);
try
{
conn.Open();
cmd.ExecuteNonQuery(); //Insert
//Retreive Customer ID
SqlCommand selCmd = new SqlCommand("SELECT @@IDENTITY", conn);
custID = Convert.ToInt32(selCmd.ExecuteScalar());
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(custID);
}
// By Joel
internal static bool LoginExists(int customerId)
{
SqlConnection conn = TravelExpertsDB.GetConnection();
string stmt = "SELECT CASE WHEN CustomerID = @cID THEN 1 ELSE 0 END FROM Logins";
SqlCommand cmd = new SqlCommand(stmt, conn);
cmd.Parameters.AddWithValue("@cID", customerId);
try
{
conn.Open();
bool exists = Convert.ToBoolean(cmd.ExecuteScalar());
return(exists);
}
finally
{
conn.Close();
}
}
// By Joel
internal static CustomerLogin GetLoginByUserName(string text)
{
CustomerLogin login = null; // found customer
// define connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// define the select query command
string selectQuery = "select * " +
"from Logins " +
"where UserName = @uname";
SqlCommand selectCommand = new SqlCommand(selectQuery, connection);
selectCommand.Parameters.AddWithValue("@uname", text);
try
{
// open the connection
connection.Open();
// execute the query
SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleResult);
// process the result if any
if (reader.Read()) // if there is customer
{
login = new CustomerLogin();
login.CustomerId = (int)reader["CustomerId"];
login.UserName = reader["UserName"].ToString();
login.Password = reader["Password"].ToString();
}
}
catch (Exception ex)
{
throw ex; // let the form handle it
}
finally
{
connection.Close(); // close connecto no matter what
}
return(login);
}
// By Joel
internal static bool CheckPassword(int customerId, string password)
{
SqlConnection conn = TravelExpertsDB.GetConnection();
string stmt = "SELECT CASE WHEN Password = @pw THEN 1 ELSE 0 END FROM Logins WHERE CustomerID = @cID";
SqlCommand cmd = new SqlCommand(stmt, conn);
cmd.Parameters.AddWithValue("@cID", customerId);
cmd.Parameters.AddWithValue("@pw", password);
try
{
conn.Open();
bool matches = Convert.ToBoolean(cmd.ExecuteScalar());
return(matches);
}
finally
{
conn.Close();
}
}
public static string GetFeeIdByBookingId(int BookingId)
{
string feeId = null;
SqlConnection connection = TravelExpertsDB.GetConnection();
// define the select query command
string selectQuery = "SELECT FeeId " +
"FROM BookingDetails " +
"WHERE BookingId = @BookingId";
//$sql_pkg_id = "SELECT PackageId FROM bookings where bookings.bookingId = '$bookingId';";
SqlCommand selectCommand = new SqlCommand(selectQuery, connection);
selectCommand.Parameters.AddWithValue("@BookingId", BookingId);
try
{
// open the connection
connection.Open();
// execute the query
SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
feeId = reader["FeeId"].ToString();
}
}
catch (Exception ex)
{
throw ex; // let the form handle it
}
finally
{
connection.Close(); // close connection no matter what
}
return(feeId);
}
public static List <Product_Supplier> GetAllProductsSuppliers()
{
List <Product_Supplier> products_suppliers = new List <Product_Supplier>(); // make an empty list
Product_Supplier product_supplier;
// create connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// create select command
string selectString = "SELECT * FROM Products_Suppliers " +
"ORDER BY ProductSupplierId";
SqlCommand selectCommand = new SqlCommand(selectString, connection);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
product_supplier = new Product_Supplier();
product_supplier.ProductSupplierId = (int)reader["ProductSupplierId"];
product_supplier.ProductId = (int)reader["ProductId"];
product_supplier.SupplierId = (int)reader["SupplierId"];
products_suppliers.Add(product_supplier);
}
reader.Close();
}
catch (Exception ex)
{
throw ex; // throw it to the form to handle
}
finally
{
connection.Close();
}
return(products_suppliers);
}
public static List <Supplier> GetAllSuppliers()
{
List <Supplier> suppliers = new List <Supplier>(); // make an empty list
Supplier supplier;
// create connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// create select command
string selectString = "SELECT * FROM Suppliers " +
"ORDER BY SupplierId";
SqlCommand selectCommand = new SqlCommand(selectString, connection);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
supplier = new Supplier();
supplier.SupplierId = (int)reader["SupplierId"];
supplier.SupName = reader["SupName"].ToString();
suppliers.Add(supplier);
}
reader.Close();
}
catch (Exception ex)
{
throw ex; // throw it to the form to handle
}
finally
{
connection.Close();
}
return(suppliers);
}
public static List <Booking> GetAllBookingId()
{
List <Booking> bookings = new List <Booking>(); // make an empty list
Booking bk; // reference to new state object
// create connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// create select command
string selectString = "select BookingId from Bookings " +
"order by BookingId";
SqlCommand selectCommand = new SqlCommand(selectString, connection);
try
{
// open connection
connection.Open();
// run the select command and process the results adding states to the list
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())// process next row
{
bk = new Booking();
bk.BookingId = (int)reader["BookingId"];
bookings.Add(bk);
}
reader.Close();
}
catch (Exception ex)
{
throw ex; // throw it to the form to handle
}
finally
{
connection.Close();
}
return(bookings);
}
