// Used to get a dictionary of timetable details from the database
public Dictionary<string, TimetableDetails> GetTimetableDetails(DateTime date)
{
string query = string.Format("Date = '{0}'", string.Format("{0:MM-dd-yy}", date));
ResultSet timetableResults = new ResultSet(GetDataReader("[BusTripTable]", query)); // Search the database for all details relating to the specified date
CloseConnection();
Dictionary<string, TimetableDetails> details = new Dictionary<string, TimetableDetails>();
string[] timeSlots = { "8.30 - 10.15", "2.00 - 3.45 ", "10.45 - 1.45", "4.00 - 7.00 " };
for(int i = 0; i < timeSlots.Length; i++){ // Go through each possible timeslot and add the details about it to the details Dictionary
TimetableDetails timetableDetails = new TimetableDetails();
int row = timetableResults.FindRow<string>(2, timeSlots[i]); // Find the row which stores the data for this time slot
if (row == -1) // If there is no-one registered for this time slot use the small bus
{
timetableDetails.busUsed = "9 Person";
timetableDetails.seatsLeft = 24;
details.Add(timeSlots[i], timetableDetails);
continue;
}
if(timetableResults.GetData<int>(4, row) < 9) // If there are less than 9 people registered for this bus use the small bus
{
timetableDetails.busUsed = "9 Person";
}
else if (timetableResults.GetData<int>(4, row) < 17) // If there are >= 9 and < 17 people use the bigger bus
{
timetableDetails.busUsed = "17 Person";
}
else // If there are >= 17 people use both busses
{
timetableDetails.busUsed = "Both";
}
timetableDetails.seatsLeft = 24 - timetableResults.GetData<int>(4, row); // calculate the seats left now that we have found the number of seats
details.Add(timeSlots[i], timetableDetails); // Add these details to the dictionary
} // End of the loop through all of the time slots
return details;
}
// Schedules a new Cycling Certification for a child
public void AddNewCyclingCertification(string childName, int customerID, string level, float price)
{
// Store the specified child's details
string query = string.Format("CustomerID = {0} AND ChildName = '{1}'", customerID, childName);
ResultSet childrenResults = new ResultSet(GetDataReader("[ChildTable]", query, "ChildID"));
int childID = childrenResults.GetData<int>(0, 0);
OpenConnection();
int certBookingID = GetCount("[CertificationBookingsTable]"); // Get the next available ID for a Certification Booking
CloseConnection();
// Add the record to the CertificationBookingsTable
string command = string.Format("INSERT INTO [CertificationBookingsTable] VALUES ({0}, {1}, '{2}', {3})", certBookingID, childID, level, price);
UpdateByQuery(command);
}
// When a customer name suggestion is chosen
private void CustomerSuggestionChosen(string suggestion)
{
string command = "CustomerName = '" + suggestion + "'";
ResultSet resultSet = new ResultSet(busDal.GetDataReader("[dbo].[CustomerTable]", command, "CustomerID")); // Get the data related to the chosen customer name
busDal.CloseConnection();
// Update the CustomerIDComboBox to show only the CustomerID's relating to the chosen customer name
CustomerIDComboBox.Items.Clear();
for (int i = 0; i < resultSet.GetSize(); i++)
{
CustomerIDComboBox.Items.Add(resultSet.GetData<int>(0, i) + "");
}
if (CustomerIDComboBox.Items.Count > 0)
{
CustomerIDComboBox.Text = CustomerIDComboBox.Items[0] + "";
}
}
// When a suggested Customer Name is chosen
private void SuggestionChosen(string suggestion)
{
// Obtain the data that relates to the chosen customer name
string command = "CustomerName = '" + suggestion + "'";
ResultSet resultSet = new ResultSet(cyclingDAL.GetDataReader("[dbo].[CustomerTable]", command, "CustomerID"));
// Make the CustomerIDComboBox show only the customerID's relating to the chosen Customer Name
CustomerIDComboBox.Items.Clear();
for (int i = 0; i < resultSet.GetSize(); i++)
{
CustomerIDComboBox.Items.Add(resultSet.GetData<int>(0, i) + "");
}
if (CustomerIDComboBox.Items.Count > 0)
{
CustomerIDComboBox.Text = CustomerIDComboBox.Items[0] + "";
}
CustomerIDComboBox_SelectedIndexChanged(CustomerNameSuggestingTextBox, new EventArgs());
}
// When a customer name suggestion is chosen
private void CustomerSuggestionChosen(string suggestion)
{
string command = "CustomerName = '" + suggestion + "'";
ResultSet resultSet = new ResultSet(cyclingDAL.GetDataReader("[dbo].[CustomerTable]", command, "CustomerID"));
// Update the CustomerIDComboBox with the ID's of the customers relating to the suggested customer name
CustomerIDComboBox.Items.Clear();
for (int i = 0; i < resultSet.GetSize(); i++)
{
CustomerIDComboBox.Items.Add(resultSet.GetData<int>(0, i) + "");
}
if (CustomerIDComboBox.Items.Count > 0)
{
CustomerIDComboBox.Text = CustomerIDComboBox.Items[0] + "";
}
}
// When a customer name suggestion is chosen
private void CustomerSuggestionChosen(string suggestion)
{
// Read the CustomerIDs relating to this name
string command = "CustomerName = '" + suggestion + "'";
ResultSet resultSet = new ResultSet(cyclingDAL.GetDataReader("[dbo].[CustomerTable]", command, "CustomerID"));
// Set the contents of the CustomerIDComboBox to display the read CustomerIDs
CustomerIDComboBox.Items.Clear();
for (int i = 0; i < resultSet.GetSize(); i++)
{
CustomerIDComboBox.Items.Add(resultSet.GetData<int>(0, i) + "");
}
if (CustomerIDComboBox.Items.Count > 0)
{
CustomerIDComboBox.Text = CustomerIDComboBox.Items[0] + "";
}
}
// When a customer name suggestion was chosen
private void NameSuggestionChosen(string suggestion)
{
string command = "CustomerName = '"+ suggestion + "'";
ResultSet resultSet = new ResultSet(dal.GetDataReader("[dbo].[CustomerTable]", command, "CustomerID")); // Obtaing details about CustomerIDs
//dal.CloseConnection();
// Set the CustomerIDComboBox to display CustomerIDs that apply to this customer name
CustomerIDComboBox.Items.Clear();
for(int i = 0; i < resultSet.GetSize(); i++){
CustomerIDComboBox.Items.Add(resultSet.GetData<int>(0, i) + "");
}
if (CustomerIDComboBox.Items.Count > 0)
{
CustomerIDComboBox.Text = CustomerIDComboBox.Items[0] + "";
}
}
private void ChangeBooking(string customerName)
{
bookingResults = new ResultSet(dal.GetDataReader("BookingTable", "CustomerID = " + CustomerIDComboBox.Text));
ClearExpensesLabels(); // Remove any labels explaining expenses
//Obtain prices from all aspect of the database. Calculating price through C# prevents data duplication / redundancy
float equipmentLoansPrice = dal.ReadPrice("EquipmentLoansTable JOIN EquipmentTable ON EquipmentLoansTable.EquipmentID = EquipmentTable.EquipmentID", "CustomerID = " + CustomerIDComboBox.Text, "(RentalPrice * Duration)");
float sightseeingBusPrice = dal.ReadPrice("[Customer-BusTripTable]", "CustomerID = " + CustomerIDComboBox.Text, "Price");
float cyclingTourPrice = dal.ReadPrice("[Customer-CyclingTourTable]", "CustomerID = " + CustomerIDComboBox.Text, "Price");
float cyclingLessonPrice = dal.ReadPrice("[Child-CyclingLessonTable] JOIN [ChildTable] ON [Child-CyclingLessonTable].ChildID = ChildTable.ChildID", "ChildTable.CustomerID = " + CustomerIDComboBox.Text, "[Child-CyclingLessonTable].Price");
float cyclingCertificationPrice = dal.ReadPrice("CertificationBookingsTable JOIN ChildTable ON CertificationBookingsTable.ChildID = ChildTable.ChildID", "ChildTable.CustomerID = " + CustomerIDComboBox.Text, "[CertificationBookingsTable].Price");
dal.OpenConnection(); // Connection is opened and closed manually for a GetCount call because this can save on lag caused by connecting to database.
float cyclingAwardPrice = dal.GetCount("[CyclingAwardsTable] JOIN ChildTable ON CyclingAwardsTable.ChildID = ChildTable.ChildID", "CustomerID = " + CustomerIDComboBox.Text) * 20.0f;
dal.CloseConnection();
// For each expense, if it is relevant display a label explaining the expense
if (equipmentLoansPrice > 0)
{
AddExpensesLabel("Charge for Hiring Equipment: £" + Program.ToCurrency(equipmentLoansPrice));
}
if (sightseeingBusPrice > 0)
{
AddExpensesLabel("Charge for Sightseeing Trip(s): £" + Program.ToCurrency(sightseeingBusPrice));
}
if (cyclingTourPrice > 0)
{
AddExpensesLabel("Charge for Cycling Tour(s): £" + Program.ToCurrency(cyclingTourPrice));
}
if (cyclingLessonPrice > 0)
{
AddExpensesLabel("Charge for Cycling Lesson(s): £" + Program.ToCurrency(cyclingLessonPrice));
}
if (cyclingCertificationPrice > 0)
{
AddExpensesLabel("Charge for Cycling Certification(s): £" + Program.ToCurrency(cyclingCertificationPrice));
}
if (cyclingAwardPrice > 0)
{
AddExpensesLabel("Charge for Cycling Award(s): £" + Program.ToCurrency(cyclingAwardPrice));
}
//Set the chosen customer name and id straight from the SuggestiveTextBox and ComboBox
CustomerNameLabel.Text = "Customer Name: " + customerName;
CustomerIDLabel.Text = "Customer ID: " + CustomerIDComboBox.Text + "";
try
{
BookingIDLabel.Text = "Booking ID: " + bookingResults.GetData<int>(0, 0); // Retrieve and display the bookingID
}
catch (Exception e)
{
Console.WriteLine("No data found for this customer. Check "); // Should never be called.
}
// Sum all of the prices to give a total price
float totalPrice = equipmentLoansPrice + sightseeingBusPrice + cyclingTourPrice + cyclingLessonPrice + cyclingCertificationPrice + cyclingAwardPrice;
TotalPriceLabel.Text = "Total Price: £" + Program.ToCurrency(totalPrice);
}
// Returns the number of children registered for a specific lesson
public int GetNumberAttendingLesson(DateTime date, string timeSlot)
{
// Run a query to find the number of children attending the lesson at the specified date and time slot
string query = string.Format("[Date] = '{0}' AND TimeSlot = '{1}'", string.Format("{0:MM-dd-yy}", date), timeSlot);
ResultSet lessonResults = new ResultSet(GetDataReader("[CyclingLessonTable]", query, "NumberChildren"));
CloseConnection();
if (lessonResults.GetSize() == 0)
{
return 0;
}
return lessonResults.GetData<int>(0, 0);
}
// Returns the number of bicycles that have not yet been hired for a specific lesson
public int GetBicyclesLeft(DateTime date, string timeSlot)
{
// Run a query to find the number of bicylces left for the lesson at the specified date and time slot
string query = string.Format("[Date] = '{0}' AND TimeSlot = '{1}'", string.Format("{0:MM-dd-yy}", date), timeSlot);
ResultSet tourResults = new ResultSet(GetDataReader("[CyclingTourTable]", query, "NumberBicycles"));
CloseConnection();
if (tourResults.GetSize() == 0)
{
return 15;
}
else
{
return (15 - tourResults.GetData<int>(0, 0));
}
}
// Method to determine the number of people that have signed up for a tour on a specific time slot on a specific date
public int GetNumberSignedUp(DateTime date, string timeSlot)
{
OpenConnection();
// Run a query to find the number of cyclists registered for the specified time slot and specified date
string query = string.Format("[CyclingTourTable].Date = '{0}' AND [CyclingTourTable].TimeSlot = '{1}'", string.Format("{0:MM-dd-yy}", date), timeSlot);
ResultSet tourResults = new ResultSet(GetDataReader("[CyclingTourTable]", query, "NumberCyclists"));
CloseConnection();
if (tourResults.GetSize() == 0)
{
return 0;
}
return tourResults.GetData<int>(0, 0);
}