public FetchResult <Flight> FetchFlights(string whereClause = null, SqlParameter[] sqlParams = null)
{
JoinableDatabaseTable flightSourceTable = new JoinableDatabaseTable("flights");
flightSourceTable.AddAttribute("flights.Id");
flightSourceTable.AddAttribute("depAirport.Country");
flightSourceTable.AddAttribute("depAirport.City");
flightSourceTable.AddAttribute("destAirport.Country");
flightSourceTable.AddAttribute("destAirport.City");
flightSourceTable.AddAttribute("TimeOfDeparture");
flightSourceTable.AddAttribute("TimeOfArrival");
flightSourceTable.AddAttribute("SeatRows");
flightSourceTable.AddAttribute("SeatsPerRow");
flightSourceTable.CreateJoin("airports", "DepartureAirportId", "Id", "depAirport");
flightSourceTable.CreateJoin("airports", "DestinationAirportId", "Id", "destAirport");
ObjectRelationalMapper <Flight> flightsMapper = new ObjectRelationalMapper <Flight>(Config.DB_CONNECTION_STRING, flightSourceTable);
FetchResult <Flight> flightsFetched = flightsMapper.Fetch(attr => {
Airport departureAp = new Airport(attr["depAirport.Country"].ToString(), attr["depAirport.City"].ToString());
Airport destinationAp = new Airport(attr["destAirport.Country"].ToString(), attr["destAirport.City"].ToString());
return
(new Flight((int)attr["flights.Id"],
(DateTime)attr["TimeOfDeparture"],
(DateTime)attr["TimeOfArrival"],
departureAp, destinationAp,
(int)attr["SeatRows"],
(int)attr["SeatsPerRow"]));
}, whereClause, sqlParams);
if (!flightsFetched.HasError)
{
foreach (Flight flight in flightsFetched.RetrievedItems)
{
flight.TakenSeatNumbers = FetchTakenSeats(flight.Id);
}
}
return(flightsFetched);
}
public FetchResult <Booking> FetchBookings(string whereClause = null, SqlParameter[] sqlParams = null)
{
JoinableDatabaseTable bookingSourceTable = new JoinableDatabaseTable("bookings");
bookingSourceTable.AddAttribute("bookings.Id");
bookingSourceTable.AddAttribute("bookings.PassengerId");
bookingSourceTable.AddAttribute("passengers.Title");
bookingSourceTable.AddAttribute("passengers.FirstName");
bookingSourceTable.AddAttribute("passengers.LastName");
bookingSourceTable.AddAttribute("bookings.FlightId");
bookingSourceTable.AddAttribute("depAirport.Country");
bookingSourceTable.AddAttribute("depAirport.City");
bookingSourceTable.AddAttribute("destAirport.Country");
bookingSourceTable.AddAttribute("destAirport.City");
bookingSourceTable.AddAttribute("flights.TimeOfDeparture");
bookingSourceTable.AddAttribute("flights.TimeOfArrival");
bookingSourceTable.AddAttribute("flights.SeatRows");
bookingSourceTable.AddAttribute("flights.SeatsPerRow");
bookingSourceTable.AddAttribute("seats.PosX");
bookingSourceTable.AddAttribute("seats.PosY");
bookingSourceTable.AddAttribute("bookings.IsWaiting");
Join j = bookingSourceTable.CreateJoin("seats", "bookings.PassengerId", "PassengerId", null, JoinTypes.Left);
j.CustomCondition = "AND bookings.FlightId = seats.FlightId";
bookingSourceTable.CreateJoin("passengers", "bookings.PassengerId", "Id");
bookingSourceTable.CreateJoin("flights", "bookings.FlightId", "Id");
bookingSourceTable.CreateJoin("airports", "flights.DepartureAirportId", "Id", "depAirport");
bookingSourceTable.CreateJoin("airports", "flights.DestinationAirportId", "Id", "destAirport");
ObjectRelationalMapper <Booking> bookingMapper = new ObjectRelationalMapper <Booking>(Config.DB_CONNECTION_STRING, bookingSourceTable);
FetchResult <Booking> fetchResult = bookingMapper.Fetch(CreateBookingInstance, whereClause, sqlParams);
return(fetchResult);
}
