/// <summary>
/// Method responsible for getting Classes from the Classes table
/// </summary>
public static List <Classes> GetClasses()
{
List <Classes> classesList = new List <Classes>(); // empty list
Classes classes; // for reading
SqlConnection connection = UnversalDBControls.GetConnection();
string selectStatement = "SELECT ClassId, ClassName " +
"FROM Classes ";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader dr = selectCommand.ExecuteReader();
while (dr.Read())
{
classes = new Classes();
classes.ClassId = dr["ClassId"].ToString();
classes.ClassName = dr["ClassName"].ToString();
classesList.Add(classes);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(classesList);
}
/// <summary>
/// Method responsible for authenticating the login using the login data sent by the Login.aspx
/// </summary>
public static int?AuthenticateLogin(Login sentLogin)
{
SqlConnection con = UnversalDBControls.GetConnection();
string selectStatement = "SELECT CustomerId, CustUsername, CustPassword " +
"FROM Customers " +
"WHERE CustUsername = @DUsername " +
"AND CustPassword = @DPassword";
SqlCommand cmd = new SqlCommand(selectStatement, con);
cmd.Parameters.AddWithValue("@DUsername", sentLogin.UserName);
cmd.Parameters.AddWithValue("@DPassword", sentLogin.Password);
try
{
con.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (dr.Read())
{
//MessageBox.Show("Login success");
return((int)dr["CustomerId"]);
}
else
{
//MessageBox.Show("Login failed");
return(null);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
// delete this later only for testing purposes
public static List <Customer> DummyCustomer()
{
List <Customer> custList = new List <Customer>();
Customer cust;
SqlConnection con = UnversalDBControls.GetConnection();
string selectStatement = "SELECT CustomerId, CustFirstName " +
"FROM Customers ";
SqlCommand cmd = new SqlCommand(selectStatement, con);
try
{
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
cust = new Customer();
cust.CustomerId = (int)dr["CustomerId"];
cust.CustFirstName = dr["CustFirstName"].ToString();
custList.Add(cust);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
return(custList);
}
/// <summary>
/// Author: Neil
/// Method responsible getting the name of the current logged in customer (according to the custId passed)
/// </summary>
public static string GetCustName(int custId)
{
SqlConnection con = UnversalDBControls.GetConnection();
string selectStatement = "SELECT CustFirstName " +
"FROM Customers " +
"WHERE CustomerId = @CustomerId ";
SqlCommand cmd = new SqlCommand(selectStatement, con);
cmd.Parameters.AddWithValue("@CustomerId", custId);
try
{
con.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (dr.Read())
{
//MessageBox.Show("Login success");
return(dr["CustFirstName"].ToString());
}
else
{
//MessageBox.Show("Login failed");
return(null);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public static List <TripTypes> GetTripTypes()
{
List <TripTypes> tripTypeList = new List <TripTypes>();
TripTypes tripType;
SqlConnection connection = UnversalDBControls.GetConnection();
string selectStatement = "SELECT TripTypeId, TTName " +
"FROM TripTypes ";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader dr = selectCommand.ExecuteReader();
while (dr.Read())
{
tripType = new TripTypes();
tripType.TripTypeId = dr["TripTypeId"].ToString();
tripType.TTName = dr["TTName"].ToString();
tripTypeList.Add(tripType);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(tripTypeList);
}
/// <summary>
/// Method responsible for creating BookingDetails accrording to the passed bookingId and class
/// </summary>
public static void CreateBookingDetailPckg(Booking newBooking, string Class)
{
List <Package> selectedPckg = PackageDB.GetPackagesById((int)newBooking.PackageId);
List <FlightsTable> inclFlights = FlightsTableDB.getFlightsById(selectedPckg[0].DepartureFlight, selectedPckg[0].ReturnFlight);
List <Products_Suppliers> usedProds = Products_SuppliersDB.GetProductsSuppliersIdFromPackageId((int)newBooking.PackageId);
SqlConnection con = UnversalDBControls.GetConnection();
string insertStatement = "ALTER TABLE BookingDetails " +
"NOCHECK CONSTRAINT FK_BookingDetails_Bookings; " +
"ALTER TABLE BookingDetails " +
"NOCHECK CONSTRAINT FK_BookingDetails_Regions; " +
"INSERT INTO BookingDetails (ItineraryNo, TripStart, TripEnd, Description, Destination, BasePrice, " +
"AgencyCommission, BookingId, RegionId, ClassId, FeeId, ProductSupplierId, " +
"DeparturePlnId, ReturnPlnId) " +
"VALUES(@ItineraryNo, @TripStart, @TripEnd, @Description, @Destination, @BasePrice, " +
"@AgencyCommission, @BookingId, @RegionId, @ClassId, @FeeId, @ProductSupplierId, " +
"@DeparturePlnId, @ReturnPlnId)";
SqlCommand cmd = new SqlCommand(insertStatement, con);
cmd.Parameters.AddWithValue("@ItineraryNo", RandomString(6));
cmd.Parameters.AddWithValue("@TripStart", inclFlights[0].FltDepart);
cmd.Parameters.AddWithValue("@TripEnd", inclFlights[1].FltReturn);
cmd.Parameters.AddWithValue("@Description", inclFlights[0].FltLocation + "/" + inclFlights[1].FltDestination + "/" + inclFlights[0].FltLocation);
cmd.Parameters.AddWithValue("@Destination", inclFlights[1].FltDestination);
cmd.Parameters.AddWithValue("@BasePrice", selectedPckg[0].PkgBasePrice);
cmd.Parameters.AddWithValue("@AgencyCommission", selectedPckg[0].PkgAgencyCommission);
cmd.Parameters.AddWithValue("@BookingId", newBooking.BookingId);
cmd.Parameters.AddWithValue("@RegionId", inclFlights[1].RegionId);
cmd.Parameters.AddWithValue("@ClassId", Class);
if (newBooking.TravelerCount == 1)
{
cmd.Parameters.AddWithValue("@FeeId", "BK");
}
else if (newBooking.TravelerCount >= 2)
{
cmd.Parameters.AddWithValue("@FeeId", "GR");
}
cmd.Parameters.AddWithValue("@ProductSupplierId", usedProds[0].ProductSupplierId);
cmd.Parameters.AddWithValue("@DeparturePlnId", inclFlights[0].FlightId);
cmd.Parameters.AddWithValue("@ReturnPlnId", inclFlights[1].FlightId);
try
{
con.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
/// <summary>
/// Method responsible for getting Bookings accrording to the passed customer Id (can be from the sessionId or cookieID)
/// </summary>
public static List <Booking> GetBookingByID(int?CookedCustId, int?SessedCustId)
{
int?CustomerId = -1;
if (CookedCustId != null)
{
CustomerId = CookedCustId;
}
else if (SessedCustId != null)
{
CustomerId = SessedCustId;
}
List <Booking> bookingList = new List <Booking>();
Booking booking = null;
SqlConnection connection = UnversalDBControls.GetConnection();
string selectStatement = "SELECT BookingId, BookingDate, BookingNo, TravelerCount, CustomerId, TripTypeId, PackageId " +
"FROM Bookings " +
"WHERE CustomerId = @CustomerId ";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@CustomerId", CustomerId);
try
{
connection.Open();
SqlDataReader dr = selectCommand.ExecuteReader();
while (dr.Read())
{
booking = new Booking();
booking.BookingId = (int)dr["BookingId"];
booking.BookingDate = (DateTime)dr["BookingDate"];
booking.BookingNo = dr["BookingNo"].ToString();
booking.TravelerCount = (double)dr["TravelerCount"];
booking.CustomerId = (int)dr["CustomerId"];
booking.TripTypeId = dr["TripTypeId"].ToString();
booking.PackageId = dr["PackageId"] as int?;
bookingList.Add(booking);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(bookingList);
}
// Method responsible for getting all of the Agencies
public static List <Agencies> getAgencies()
{
List <Agencies> agenciesList = new List <Agencies>();
Agencies agencies;
SqlConnection connection = UnversalDBControls.GetConnection();
string selectStatement = "SELECT AgncyAddress, AgncyCity, AgncyProv, AgncyPostal, AgncyCountry, AgncyPhone, AgncyFax, AgencyId " +
"FROM Agencies";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader dr = selectCommand.ExecuteReader();
while (dr.Read())
{
agencies = new Agencies();
agencies.AgncyAddress = dr["AgncyAddress"].ToString();
agencies.AgncyCity = dr["AgncyCity"].ToString();
agencies.AgncyProv = dr["AgncyProv"].ToString();
agencies.AgncyPostal = dr["AgncyPostal"].ToString();
agencies.AgncyCountry = dr["AgncyCountry"].ToString();
agencies.AgncyPhone = dr["AgncyPhone"].ToString();
agencies.AgncyFax = dr["AgncyFax"].ToString();
// Building the string data for the map window (e,g.: 1155%208th%20Ave%20SW%20Calgary )
string[] separatedAdd = dr["AgncyAddress"].ToString().Split();
string completedAddress = "";
foreach (string add in separatedAdd)
{
completedAddress += add;
completedAddress += "%20";
}
completedAddress += dr["AgncyCity"].ToString();
agencies.MapData = completedAddress;
agencies.AgencyId = (int)dr["AgencyId"];
agenciesList.Add(agencies);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(agenciesList);
}
// Method responsible for getting BookingDetails accrording to the passed booking Id
public static BookingDetails GetBookingDetailByID(int BookingId)
{
BookingDetails bookingDetails = null;
SqlConnection connection = UnversalDBControls.GetConnection();
string selectStatement = "SELECT BookingDetailId, ItineraryNo, TripStart, TripEnd, Description, " +
"Destination, BasePrice, AgencyCommission, BookingId, RegionId, " +
"ClassId, FeeId, ProductSupplierId, DeparturePlnId, ReturnPlnId " +
"FROM BookingDetails " +
"WHERE BookingId = @BookingId ";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@BookingId", BookingId);
try
{
connection.Open();
SqlDataReader dr = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (dr.Read())
{
bookingDetails = new BookingDetails();
bookingDetails.BookingDetailId = (int)dr["BookingDetailId"];
bookingDetails.ItineraryNo = (double)dr["ItineraryNo"];
bookingDetails.TripStart = (DateTime)dr["TripStart"];
bookingDetails.TripEnd = (DateTime)dr["TripEnd"];
bookingDetails.Description = dr["Description"].ToString();
bookingDetails.Destination = dr["Destination"].ToString();
bookingDetails.BasePrice = (decimal)dr["BasePrice"];
bookingDetails.AgencyCommission = (decimal)dr["AgencyCommission"];
bookingDetails.BookingId = (int)dr["BookingId"];
bookingDetails.RegionId = dr["RegionId"].ToString();
bookingDetails.ClassId = dr["ClassId"].ToString();
bookingDetails.FeeId = dr["FeeId"].ToString();
bookingDetails.ProductSupplierId = (int)dr["ProductSupplierId"];
bookingDetails.DeparturePlnId = (int)dr["DeparturePlnId"];
bookingDetails.ReturnPlnId = (int)dr["ReturnPlnId"];
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(bookingDetails);
}
/// <summary>
/// Method responsible for creating a booking ticket and also returns necessary information for BookingDetails creation
/// </summary>
public static Booking CreateBookingPckg(Booking book, string Class)
{
SqlConnection con = UnversalDBControls.GetConnection();
string insertStatement = "INSERT INTO Bookings (BookingDate, BookingNo, TravelerCount, CustomerId, TripTypeId, PackageId) " +
"VALUES(@BookingDate, @BookingNo, @TravelerCount, @CustomerId, @TripTypeId, @PackageId)";
SqlCommand cmd = new SqlCommand(insertStatement, con);
cmd.Parameters.AddWithValue("@BookingDate", book.BookingDate);
cmd.Parameters.AddWithValue("@BookingNo", book.BookingNo);
cmd.Parameters.AddWithValue("@TravelerCount", book.TravelerCount);
cmd.Parameters.AddWithValue("@CustomerId", book.CustomerId);
if (Class == "BSN")
{
cmd.Parameters.AddWithValue("@TripTypeId", "B");
}
else if (book.TravelerCount == 1)
{
cmd.Parameters.AddWithValue("@TripTypeId", "L");
}
else if (book.TravelerCount >= 2)
{
cmd.Parameters.AddWithValue("@TripTypeId", "G");
}
else
{
cmd.Parameters.AddWithValue("@TripTypeId", DBNull.Value);
}
cmd.Parameters.AddWithValue("@PackageId", book.PackageId);
try
{
con.Open();
cmd.ExecuteNonQuery();
cmd.CommandText = "Select @@Identity";
book.BookingId = Convert.ToInt32(cmd.ExecuteScalar());
return(book);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public static int AddCustomer(Customer cust)
{
SqlConnection con = UnversalDBControls.GetConnection();
string insertStatement = "INSERT INTO Customers (CustFirstName, CustLastName, CustAddress, CustCity, CustProv, CustPostal, CustCountry, CustHomePhone, CustBusPhone, CustEmail, CustUsername, CustPassword) " +
"VALUES(@CustFirstName, @CustLastName, @CustAddress, @CustCity, @CustProv, @CustPostal, @CustCountry, @CustHomePhone, @CustBusPhone, @CustEmail, @CustUsername, @CustPassword)";
SqlCommand cmd = new SqlCommand(insertStatement, con);
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("@CustCountry", cust.CustCountry);
cmd.Parameters.AddWithValue("@CustHomePhone", cust.CustHomePhone);
cmd.Parameters.AddWithValue("@CustBusPhone", cust.CustBusPhone);
cmd.Parameters.AddWithValue("@CustEmail", cust.CustEmail);
cmd.Parameters.AddWithValue("@CustUsername", cust.CustUsername);
cmd.Parameters.AddWithValue("@CustPassword", cust.CustPassword);
try
{
con.Open();
cmd.ExecuteNonQuery();
string selectQuery = "SELECT IDENT_CURRENT('Customers') FROM Customers";
SqlCommand selectCmd = new SqlCommand(selectQuery, con);
int customerID = Convert.ToInt32(selectCmd.ExecuteScalar());
return(customerID);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
/// <summary>
/// Author: Neil
/// Method responsible for getting Agents in the Agents table according to the passed Agency Id
/// </summary>
public static List <Agents> GetAgents(int AgencyId)
{
List <Agents> agenstlist = new List <Agents>();
Agents agents;
SqlConnection connection = UnversalDBControls.GetConnection();
string selectStatement = "SELECT AgtFirstName, AgtLastName, AgtBusPhone, AgtEmail, AgtPosition " +
"FROM Agents " +
"WHERE AgencyId = @AgencyId ";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@AgencyId", AgencyId);
try
{
connection.Open();
SqlDataReader dr = selectCommand.ExecuteReader();
while (dr.Read())
{
agents = new Agents();
//agents.ProdName = dr["ProdName"].ToString();
agents.AgtFirstName = dr["AgtFirstName"].ToString();
agents.AgtLastName = dr["AgtLastName"].ToString();
agents.AgtBusPhone = dr["AgtBusPhone"].ToString();
agents.AgtEmail = dr["AgtEmail"].ToString();
agents.AgtPosition = dr["AgtPosition"].ToString();
agenstlist.Add(agents);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(agenstlist);
}
/// <summary>
/// Method responsible for getting Flights from the Flights table
/// </summary>
public static List <FlightsTable> getFlights()
{
List <FlightsTable> flightLists = new List <FlightsTable>();
FlightsTable flights;
SqlConnection connection = UnversalDBControls.GetConnection();
string selectStatement = "SELECT FlightId, FltPlaneNo, FltDepart, FltReturn, FltLocation, FltDestination, RegionId, FltTicketPrice " +
"FROM FlightsTable";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader dr = selectCommand.ExecuteReader();
while (dr.Read())
{
flights = new FlightsTable();
flights.FlightId = (int)dr["FlightId"];
flights.FltPlaneNo = (int)dr["FltPlaneNo"];
flights.FltDepart = (DateTime)dr["FltDepart"];
flights.FltReturn = (DateTime)dr["FltReturn"];
flights.FltLocation = dr["FltLocation"].ToString();
flights.FltDestination = dr["FltDestination"].ToString();
flights.RegionId = dr["RegionId"].ToString();
flights.FltTicketPrice = Convert.ToDouble(dr["FltTicketPrice"]);
flightLists.Add(flights);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(flightLists);
}
/// <summary>
/// Method responsible for getting Flights according to the depart and return plane Id
/// </summary>
public static List <FlightsTable> getFlightsById(int DepartPlnId, int ReturnPlnId)
{
List <FlightsTable> flightLists = new List <FlightsTable>();
FlightsTable flights;
SqlConnection connection = UnversalDBControls.GetConnection();
string selectStatement = "SELECT FlightId, FltPlaneNo, FltDepart, FltReturn, FltLocation, FltDestination, RegionId, FltTicketPrice " +
"FROM FlightsTable " +
"WHERE FlightId = @DepartPlnId " +
"AND FlightId <> 0";
SqlCommand selectCommand1 = new SqlCommand(selectStatement, connection);
selectCommand1.Parameters.AddWithValue("@DepartPlnId", DepartPlnId);
selectStatement = "SELECT FlightId, FltPlaneNo, FltDepart, FltReturn, FltLocation, FltDestination, RegionId, FltTicketPrice " +
"FROM FlightsTable " +
"WHERE FlightId = @ReturnPlnId " +
"AND FlightId <> 0";
SqlCommand selectCommand2 = new SqlCommand(selectStatement, connection);
selectCommand2.Parameters.AddWithValue("@ReturnPlnId", ReturnPlnId);
try
{
connection.Open();
SqlDataReader dr = selectCommand1.ExecuteReader(CommandBehavior.SingleRow);
while (dr.Read())
{
flights = new FlightsTable();
flights.FlightId = (int)dr["FlightId"];
flights.FltPlaneNo = (int)dr["FltPlaneNo"];
flights.FltDepart = (DateTime)dr["FltDepart"];
flights.FltReturn = (DateTime)dr["FltReturn"];
flights.FltLocation = dr["FltLocation"].ToString();
flights.FltDestination = dr["FltDestination"].ToString();
flights.RegionId = dr["RegionId"].ToString();
flights.FltTicketPrice = Convert.ToDouble(dr["FltTicketPrice"]);
flightLists.Add(flights);
}
connection.Close();
connection.Open();
dr = selectCommand2.ExecuteReader(CommandBehavior.SingleRow);
while (dr.Read())
{
flights = new FlightsTable();
flights.FlightId = (int)dr["FlightId"];
flights.FltPlaneNo = (int)dr["FltPlaneNo"];
flights.FltDepart = (DateTime)dr["FltDepart"];
flights.FltReturn = (DateTime)dr["FltReturn"];
flights.FltLocation = dr["FltLocation"].ToString();
flights.FltDestination = dr["FltDestination"].ToString();
flights.RegionId = dr["RegionId"].ToString();
flights.FltTicketPrice = Convert.ToDouble(dr["FltTicketPrice"]);
flightLists.Add(flights);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(flightLists);
}
/// <summary>
/// Author: Neil
/// Method responsible for getting Flights according to the search parameters (note: still in early stages not functional)
/// </summary>
public static List <ResultFlightsTable> SearchFlights(SearchParameters sp)
{
List <ResultFlightsTable> flightLists = new List <ResultFlightsTable>();
ResultFlightsTable flights;
SqlConnection connection = UnversalDBControls.GetConnection();
string selectStatement = "SELECT FlightId, FltPlaneNo, FltDepart, FltReturn, FltLocation, FltDestination, RegionId, FltTicketPrice " +
"FROM FlightsTable ";
List <string> whereStt = new List <string>();
if (sp.Location != null)
{
whereStt.Add("FltLocation = @FltLocation ");
}
if (sp.Destination != null)
{
whereStt.Add("FltDestination = @FltDestination ");
}
if (sp.departDt != null)
{
whereStt.Add("FltDepart = @FltDepart ");
}
/*
* if (sp.returnDt != null)
* {
* whereStt.Add("FltReturn = @FltReturn");
* }
*/
selectStatement += "WHERE ";
for (int i = 1; i <= whereStt.Count; i++)
{
selectStatement += whereStt[i - 1];
if (i != whereStt.Count)
{
selectStatement += "AND ";
}
}
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
if (sp.Location != null)
{
selectCommand.Parameters.AddWithValue("@FltLocation", sp.Location);
}
if (sp.Destination != null)
{
selectCommand.Parameters.AddWithValue("@FltDestination", sp.Destination);
}
if (sp.departDt != null)
{
selectCommand.Parameters.AddWithValue("@FltDepart", sp.departDt);
}
/*
* if (sp.returnDt != null)
* {
* selectCommand.Parameters.AddWithValue("@FltReturn", sp.returnDt);
* }
*/
try
{
connection.Open();
SqlDataReader dr = selectCommand.ExecuteReader();
while (dr.Read())
{
flights = new ResultFlightsTable();
flights.DepartPlId = (int)dr["FlightId"];
flights.DepartPlaneNo = (int)dr["FltPlaneNo"];
flights.ReturnPlId = (int)dr["FlightId"];
flights.RetuPlaneNo = (int)dr["FltPlaneNo"];
flights.FltDepart = (DateTime)dr["FltDepart"];
flights.FltReturn = (DateTime)dr["FltReturn"];
flights.FltLocation = dr["FltLocation"].ToString();
flights.FltDestination = dr["FltDestination"].ToString();
flights.RegionId = dr["RegionId"].ToString();
flights.FltTicketPrice = Convert.ToDouble(dr["FltTicketPrice"]);
flightLists.Add(flights);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(flightLists);
}
