public static List <Purchases> GetPurchasesByEmail(string custEmail)
{
List <Purchases> purc = new List <Purchases>();
Purchases puchase = null;
SqlConnection con = TravelExpertsDB.GetConnection();
var total = 0;
// Create the SQL select statement.
string selectStatement = " select b.BookingDate as BookingDate, (p.ProdName + ' ' + s.SupName) as name, " +
"(bd.BasePrice + bd.AgencyCommission) as price " +
"from Bookings b " +
"inner " +
"join BookingDetails bd on b.BookingId = bd.BookingId " +
"inner join Customers c on b.CustomerId = c.CustomerId " +
"inner join Products_Suppliers ps on bd.ProductSupplierId = ps.ProductSupplierId " +
"inner join Products p on p.ProductId = ps.ProductId " +
"inner join Suppliers s on s.SupplierId = ps.SupplierId " +
"where c.CustEmail = @CustEmail " +
"union " +
"select b.BookingDate as BookingDate , p.PkgName as name, " +
"(p.PkgBasePrice + p.PkgAgencyCommission) as price " +
"from Bookings b " +
"inner " +
"join Packages p on b.PackageId = p.PackageId " +
"inner join Customers c on b.CustomerId = c.CustomerId " +
"where c.CustEmail = @CustEmail ";
SqlCommand cmd = new SqlCommand(selectStatement, con);
cmd.Parameters.AddWithValue("@CustEmail", custEmail);
try
{
// Open the connection and run the insert command
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
puchase = new Purchases();
puchase.BookingDate = Convert.ToDateTime(reader["BookingDate"]);
puchase.Name = reader["name"].ToString();
puchase.Price = Convert.ToDecimal(reader["price"]);
purc.Add(puchase);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
return(purc);
}
public static Customer GetCustomerByEmail(string custEmail)
{
Customer cust = null;
SqlConnection con = TravelExpertsDB.GetConnection();
// Create the SQL select statement.
string selectStatement = "SELECT CustomerId, CustFirstName, CustLastName, CustAddress, " +
"CustCity, CustProv, CustPostal, CustCountry, " +
"CustHomePhone, CustBusPhone, CustEmail " +
"FROM Customers " +
"WHERE CustEmail = @CustEmail";
SqlCommand cmd = new SqlCommand(selectStatement, con);
cmd.Parameters.AddWithValue("@CustEmail", custEmail);
try
{
// Open the connection and run the insert command
con.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (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();
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
return(cust);
}
/// <summary>
/// the function is used to update the data for the customer logged in
/// </summary>
/// <param name="oldCust"></param>
/// <param name="newCust"></param>
/// <returns></returns>
public static bool updateCustomer(CustomerRegistration oldCust, CustomerRegistration newCust)
{
SqlConnection con = TravelExpertsDB.GetConnection();
string updateStatement = "update Customers set " +
"CustFirstName = @newCustFirstName, " +
"CustLastName = @newCustLastName, " +
"CustAddress = @newCustAddress, " +
"CustCity = @newCustCity, " +
"CustProv = @newCustProv, " +
"CustPostal = @newCustPostal, " +
"CustCountry = @newCustCountry, " +
"CustHomePhone = @newCustHomePhone, " +
"CustBusPhone = @newCustBusPhone, " +
"CustEmail = @newCustEmail " +
"where CustomerID = @oldCustomerID";
SqlCommand cmd = new SqlCommand(updateStatement, con);
cmd.Parameters.AddWithValue("@newCustFirstName", newCust.CustFirstName);
cmd.Parameters.AddWithValue("@newCustLastName", newCust.CustLastName);
cmd.Parameters.AddWithValue("@newCustAddress", newCust.CustAddress);
cmd.Parameters.AddWithValue("@newCustCity", newCust.CustCity);
cmd.Parameters.AddWithValue("@newCustProv", newCust.CustProv);
cmd.Parameters.AddWithValue("@newCustPostal", newCust.CustPostal);
cmd.Parameters.AddWithValue("@newCustCountry", newCust.CustCountry);
cmd.Parameters.AddWithValue("@newCustHomePhone", newCust.CustHomePhone);
cmd.Parameters.AddWithValue("@newCustBusPhone", newCust.CustBusPhone);
cmd.Parameters.AddWithValue("@newCustEmail", newCust.CustEmail);
cmd.Parameters.AddWithValue("@oldCustomerID", oldCust.CustomerID);
try
{
con.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
[DataObjectMethod(DataObjectMethodType.Select)] //data object method select attribute
public static List <Booking> GetBooking(string CustomerID)
{
List <Booking> bookings = new List <Booking>(); // make an empty list
Booking mybooking; // reference to new state object
SqlConnection connection = TravelExpertsDB.GetConnection(); // create connection
// create select command
string selectString = "Select Bookings.BookingID, BookingDate, TravelerCount, TripStart, TripEnd, [Description], Destination, BasePrice, CustomerId " +
"From Bookings inner join BookingDetails on Bookings.BookingId = BookingDetails.BookingId " +
"where CustomerId = @CustomerId " +
"order by CustomerId";
SqlCommand selectCommand = new SqlCommand(selectString, connection);
selectCommand.Parameters.AddWithValue("@CustomerId", CustomerID);
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
{
mybooking = new Booking();
mybooking.BookingId = (int)reader["BookingId"];
mybooking.BookingDate = (DateTime)reader["BookingDate"];
mybooking.TravelerCount = (double)reader["TravelerCount"];
mybooking.TripStart = (DateTime)reader["TripStart"];
mybooking.TripEnd = (DateTime)reader["TripEnd"];
mybooking.Description = reader["Description"].ToString();
mybooking.Destination = reader["Destination"].ToString();
mybooking.BasePrice = (decimal)reader["BasePrice"];
mybooking.CustomerId = (int)reader["CustomerId"];
bookings.Add(mybooking); //adding them to technician list
}
reader.Close(); // close reader
}
catch (Exception ex)
{
throw ex; // throw it to the form to handle
}
finally
{
connection.Close(); //close connection
}
return(bookings); // returns the technicans list
}
public static int InsertCustomer(Customer newCust)
{
SqlConnection con = TravelExpertsDB.GetConnection();
// Create the SQL insert statement. Note that the CustomerId will be automatically
// generated. The AgentId is left null at this point.
string insertStatement = "INSERT INTO Customers " +
"(CustFirstName, CustLastName, CustAddress, CustCity, " +
"CustProv, CustPostal, CustCountry, CustHomePhone, " +
"CustBusPhone, CustEmail) " +
"VALUES (@CustFirstName, @CustLastName, @CustAddress, " +
"@CustCity, @CustProv, @CustPostal, @CustCountry, " +
"@CustHomePhone, @CustBusPhone, @CustEmail)";
SqlCommand cmd = new SqlCommand(insertStatement, con);
cmd.Parameters.AddWithValue("@CustFirstName", newCust.CustFirstName);
cmd.Parameters.AddWithValue("@CustLastName", newCust.CustLastName);
cmd.Parameters.AddWithValue("@CustAddress", newCust.CustAddress);
cmd.Parameters.AddWithValue("@CustCity", newCust.CustCity);
cmd.Parameters.AddWithValue("@CustProv", newCust.CustProv);
cmd.Parameters.AddWithValue("@CustPostal", newCust.CustPostal);
cmd.Parameters.AddWithValue("@CustCountry", newCust.CustCountry);
cmd.Parameters.AddWithValue("@CustHomePhone", newCust.CustHomePhone);
cmd.Parameters.AddWithValue("@CustBusPhone", newCust.CustBusPhone);
cmd.Parameters.AddWithValue("@CustEmail", newCust.CustEmail);
try
{
// Open the connection and run the insert command
con.Open();
cmd.ExecuteNonQuery();
// Get the CustomerId (primary key) of the newly inserted customer record
string selectQuery = "SELECT IDENT_CURRENT('Customers') FROM Customers";
SqlCommand selectCmd = new SqlCommand(selectQuery, con);
int newCustID = Convert.ToInt32(selectCmd.ExecuteScalar());
return(newCustID);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
[DataObjectMethod(DataObjectMethodType.Select)] //data object method select attribute
public static List <Package> GetPackage()
{
List <Package> packages = new List <Package>(); // make an empty list
Package pkg; // reference to new state object
SqlConnection connection = TravelExpertsDB.GetConnection(); // create connection
// create select command
string selectString = "Select PackageId, PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice " +
"from Packages " +
"order by PackageId";
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
{
pkg = new Package();
pkg.PackageId = (int)reader["PackageId"];
pkg.PkgName = reader["PkgName"].ToString();
pkg.PkgStartDate = (DateTime)reader["PkgStartDate"];
pkg.PkgEndDate = (DateTime)reader["PkgEndDate"];
pkg.PkgDesc = reader["PkgDesc"].ToString();
pkg.PkgBasePrice = (decimal)reader["PkgBasePrice"];
packages.Add(pkg); //adding them to package list
}
reader.Close(); // close reader
}
catch (Exception ex)
{
throw ex; // throw it to the form to handle
}
finally
{
connection.Close(); //close connection
}
return(packages); // returns the packages list
}
/// <summary>
/// gets the customer details depending on the customer ID
/// </summary>
/// <param name="customerEmail"></param>
/// <returns></returns>
public static CustomerRegistration GetCustomers(int customerID)
{
SqlConnection con = TravelExpertsDB.GetConnection();
string selectStatement = "select CustFirstName, CustLastName, CustAddress, CustCity, CustProv, CustPostal, CustCountry, CustHomePhone, CustBusPhone, CustEmail from Customers " +
"where CustomerID = @CustomerID";
SqlCommand cmd = new SqlCommand(selectStatement, con);
cmd.Parameters.AddWithValue("@CustomerID", customerID);
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
CustomerRegistration CR = new CustomerRegistration()
{
CustFirstName = reader["CustFirstName"].ToString(),
CustLastName = reader["CustLastName"].ToString(),
CustAddress = reader["CustAddress"].ToString(),
CustCity = reader["CustCity"].ToString(),
CustProv = reader["CustProv"].ToString(),
CustPostal = reader["CustPostal"].ToString(),
CustCountry = reader["CustCountry"].ToString(),
CustHomePhone = reader["CustHomePhone"].ToString(),
CustBusPhone = reader["CustBusPhone"].ToString(),
CustEmail = reader["CustEmail"].ToString()
};
return(CR);
}
else
{
return(null);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
[DataObjectMethod(DataObjectMethodType.Select)] //data object method select attribute
public static List <Agents> GetAgencyTwoAgents()
{
List <Agents> agents = new List <Agents>(); // make an empty list
Agents agt; // reference to new state object
SqlConnection connection = TravelExpertsDB.GetConnection(); // create connection
// create select command
string selectString = "Select AgentId, AgtFirstName, AgtMiddleInitial, AgtLastName, AgtBusPhone, AgtEmail, AgtPosition " +
"from Agents " +
"where AgencyId = 2" +
"order by AgentId";
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
{
agt = new Agents();
agt.AgentId = (int)reader["AgentId"];
agt.AgtFirstName = reader["AgtFirstName"].ToString();
agt.AgtMiddleInitial = reader["AgtMiddleInitial"].ToString();
agt.AgtLastName = reader["AgtLastName"].ToString();
agt.AgtBusPhone = reader["AgtBusPhone"].ToString();
agt.AgtEmail = reader["AgtEmail"].ToString();
agt.AgtPosition = reader["AgtPosition"].ToString();
agents.Add(agt); //adding them to technician list
}
reader.Close(); // close reader
}
catch (Exception ex)
{
throw ex; // throw it to the form to handle
}
finally
{
connection.Close(); //close connection
}
return(agents); // returns the technicans list
}
[DataObjectMethod(DataObjectMethodType.Select)] //data object method select attribute
public static List <PackageTotal> GetPackageTotal(string CustomerID)
{
List <PackageTotal> totals = new List <PackageTotal>(); // make an empty list
PackageTotal tot; // reference to new state object
SqlConnection connection = TravelExpertsDB.GetConnection(); // create connection
// create select command
string selectString = "Select BasePrice, CustomerId " +
"From Bookings inner join BookingDetails on Bookings.BookingId = BookingDetails.BookingId " +
"where CustomerId = @CustomerId " +
"order by CustomerId";
SqlCommand selectCommand = new SqlCommand(selectString, connection);
selectCommand.Parameters.AddWithValue("@CustomerId", CustomerID);
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
{
tot = new PackageTotal();
tot.BasePrice = (decimal)reader["BasePrice"];
tot.CustomerId = (int)reader["CustomerId"];
totals.Add(tot); //adding them to totals list
}
reader.Close(); // close reader
}
catch (Exception ex)
{
throw ex; // throw it to the form to handle
}
finally
{
connection.Close(); //close connection
}
return(totals); // returns the totals list
}
[DataObjectMethod(DataObjectMethodType.Select)] //data object method select attribute
public static List <Booking> GetCustomerID()
{
List <Booking> customerId = new List <Booking>(); // make an empty list
Booking cust; // reference to new state object
SqlConnection connection = TravelExpertsDB.GetConnection(); // create connection
// create select command
string selectString = "Select distinct CustomerId " +
"from Bookings " +
"order by CustomerId";
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
{
cust = new Booking();
cust.CustomerId = (int)reader["CustomerId"];
customerId.Add(cust); //adding them to technician list
}
reader.Close(); // close reader
}
catch (Exception ex)
{
throw ex; // throw it to the form to handle
}
finally
{
connection.Close(); //close connection
}
return(customerId); // returns the technicans list
}
public static int UpdateCustomer(Customer original_Customer,
Customer 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 "
+ "WHERE CustomerID = @original_CustomerID "
+ "AND CustFirstName = @original_CustFirstName "
+ "AND CustLastName = @original_CustLastName "
+ "AND CustAddress = @original_CustAddress "
+ "AND CustCity = @original_CustCity "
+ "AND CustProv = @original_CustProv "
+ "AND CustPostal = @original_CustPostal "
+ "AND CustCountry = @original_CustCountry "
+ "AND CustHomePhone = @original_CustHomePhone "
+ "AND CustBusPhone = @original_CustBusPhone "
+ "AND CustEmail = @original_CustEmail";
SqlConnection con = TravelExpertsDB.GetConnection();
// {
// using (SqlCommand cmd = new SqlCommand(sql, con))
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("@original_CustomerID",
original_Customer.CustomerId);
cmd.Parameters.AddWithValue("@original_CustFirstName",
original_Customer.CustFirstName);
cmd.Parameters.AddWithValue("@original_CustLastName",
original_Customer.CustLastName);
cmd.Parameters.AddWithValue("@original_CustAddress",
original_Customer.CustAddress);
cmd.Parameters.AddWithValue("@original_CustCity",
original_Customer.CustCity);
cmd.Parameters.AddWithValue("@original_CustProv",
original_Customer.CustProv);
cmd.Parameters.AddWithValue("@original_CustPostal",
original_Customer.CustPostal);
cmd.Parameters.AddWithValue("@original_CustCountry",
original_Customer.CustCountry);
cmd.Parameters.AddWithValue("@original_CustHomePhone",
original_Customer.CustHomePhone);
cmd.Parameters.AddWithValue("@original_CustBusPhone",
original_Customer.CustBusPhone);
cmd.Parameters.AddWithValue("@original_CustEmail",
original_Customer.CustEmail);
// }
try
{
// Open the connection and run the insert command
con.Open();
updateCount = cmd.ExecuteNonQuery();
// Get the CustomerId (primary key) of the newly inserted customer record
return(updateCount);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
