// Method to get the data from the database and display in the form.
void showBookingData()
{
// Send the SQL statement to the Database.
DataSet d = Database.getInstance().select("", new string[] { " Booking as T1, Customer as T2, Vehicle as T3 " }, new String[] { " T1.BookingID, T1.VehicleVIN, T1.CustomerID, T1.EmployeeID, T1.BookingCreationDate, T1.ScheduledHireBeginDateTime, T1.ScheduledHireReturnDateTime, T1.ActualHireBeginDateTime, T1.ActualHireReturnDateTime, T1.OdometerReadingPreHire, T1.OdometerReadingPostHire, T1.TotalBill, T2.Title, T2.FirstName, T2.LastName, T3.Manufacturer, T3.Model, T3.DailyRate "}, "WHERE T1.VehicleVIN = T3.VehicleVIN and T1.CustomerID = T2.CustomerID and T1.BookingID ", "=", bookingID.ToString(), new string[] { "" });
// Strings to store the results from the database.
// Initialised to prevent errors.
String
strVehicleVIN = "",
strCustomerID = "",
strEmployeeID = "",
strBookingCreationDate = "",
strScheduledHireBeginDateTime = "",
strScheduledHireBeginDate = "",
strScheduledHireBeginHour = "",
strScheduledHireBeginMinute = "",
strScheduledHireReturnDateTime = "",
strScheduledHireReturnDate = "",
strScheduledHireReturnHour = "",
strScheduledHireReturnMinute = "",
strActualHireBeginDateTime = "",
strActualHireReturnDateTime = "",
strOdometerReadingPreHire = "",
strOdometerReadingPostHire = "",
strTotalBill = "",
strTitle = "",
strFirstName = "",
strLastName = "",
strManufacturer = "",
strModel = "",
strDailyRate = "";
// Iterate through the results returned from the database.
foreach (DataTable table in d.Tables)
{
foreach (DataRow row in table.Rows)
{
foreach (DataColumn column in table.Columns)
{
// Handle all of the results and place the results in the correct strings as declared above.
if (column.ColumnName == "Title")
{
strTitle = row[column].ToString();
}
if (column.ColumnName == "FirstName")
{
strFirstName = row[column].ToString();
}
if (column.ColumnName == "LastName")
{
strLastName = row[column].ToString();
}
if (column.ColumnName == "VehicleVIN")
{
strVehicleVIN = row[column].ToString();
// Store the VehicleVIN at Page scope as well.
vehicleVIN = strVehicleVIN;
}
if (column.ColumnName == "CustomerID")
{
strCustomerID = row[column].ToString();
CustomerID = int.Parse(strCustomerID);
}
if (column.ColumnName == "EmployeeID")
{
strEmployeeID = row[column].ToString();
}
if (column.ColumnName == "BookingCreationDate")
{
strBookingCreationDate = row[column].ToString();
}
if (column.ColumnName == "ScheduledHireBeginDateTime")
{
strScheduledHireBeginDateTime = row[column].ToString();
// This is a hidden TextBox used to hold the full date as text in one place.
txtScheduledHireBeginDateTime.Text = strScheduledHireBeginDateTime;
// Parse the date from a string to a DateTime object.
DateTime.TryParse(strScheduledHireBeginDateTime, out scheduledCollectionDateTime);
// Format the string in a way that displays well for the user.
strScheduledHireBeginDate = scheduledCollectionDateTime.ToString("dd-MM-yyyy");
// Put the date in the DatePicker Form Control
dpCollection.SelectedDate = scheduledCollectionDateTime;
// Get the HOUR
strScheduledHireBeginHour = scheduledCollectionDateTime.ToString("HH");
// Put the HOUR in the hour ComboBox using a horrible method which works, so leave it unless you have a better solution.
cmbCollectionHour.SelectedIndex = DataValidator.getComboBoxIndexOf(cmbCollectionHour, strScheduledHireBeginHour);
// Get the MINUTE
strScheduledHireBeginMinute = scheduledCollectionDateTime.ToString("mm");
// Put the MiNUTE in the minute ComboBox using the same method above.
cmbCollectionMinute.SelectedIndex = DataValidator.getComboBoxIndexOf(cmbCollectionMinute, strScheduledHireBeginMinute);
}
if (column.ColumnName == "ScheduledHireReturnDateTime")
{
// This uses the same procedure as the one above.
// See above for details on use.
strScheduledHireReturnDateTime = row[column].ToString();
txtScheduledHireReturnDateTime.Text = strScheduledHireReturnDateTime;
DateTime.TryParse(strScheduledHireReturnDateTime, out scheduledReturnDateTime);
strScheduledHireReturnDate = scheduledReturnDateTime.ToString("dd-MM-yyyy");
dpReturn.SelectedDate = scheduledReturnDateTime;
strScheduledHireReturnHour = scheduledReturnDateTime.ToString("HH");
cmbReturnHour.SelectedIndex = DataValidator.getComboBoxIndexOf(cmbReturnHour, strScheduledHireReturnHour);
strScheduledHireReturnMinute = scheduledReturnDateTime.ToString("mm");
cmbReturnMinute.SelectedIndex = DataValidator.getComboBoxIndexOf(cmbReturnMinute, strScheduledHireReturnMinute);
}
if (column.ColumnName == "ActualHireBeginDateTime")
{
strActualHireBeginDateTime = row[column].ToString();
// Hidden TextBox to hold the full date in one place.
txtActualHireBeginDateTime.Text = strActualHireBeginDateTime;
// Parse from a string to a dateTime object, held at Page scope.
DateTime.TryParse(strActualHireBeginDateTime, out actualCollectionDateTime);
}
if (column.ColumnName == "ActualHireReturnDateTime")
{
// This uses the same procedure as the one above.
// See above for details on use.
strActualHireReturnDateTime = row[column].ToString();
txtActualHireReturnDateTime.Text = strActualHireReturnDateTime;
DateTime.TryParse(strActualHireReturnDateTime, out actualReturnDateTime);
}
if (column.ColumnName == "OdometerReadingPreHire")
{
strOdometerReadingPreHire = row[column].ToString();
txtOdometerReadingPreHire.Text = strOdometerReadingPreHire;
int.TryParse(strOdometerReadingPreHire, out intOdoPreHire);
}
if (column.ColumnName == "OdometerReadingPostHire")
{
strOdometerReadingPostHire = row[column].ToString();
txtOdometerReadingPostHire.Text = strOdometerReadingPostHire;
int.TryParse(strOdometerReadingPostHire, out intOdoPostHire);
}
if (column.ColumnName == "TotalBill")
{
// Take the string and parse it as a double, to format it as currency.
strTotalBill = row[column].ToString();
decimal totalBill = 0;
decimal.TryParse(strTotalBill, out totalBill);
txtTotalCharge.Text = totalBill.ToString("C");
decTotalBill = Math.Round(totalBill, 2);
}
if (column.ColumnName == "Manufacturer")
{
strManufacturer = row[column].ToString();
}
if (column.ColumnName == "Model")
{
strModel = row[column].ToString();
}
if (column.ColumnName == "DailyRate")
{
// Take the string and parse it as a double, to format it as currency.
strDailyRate = row[column].ToString();
double dailyRate = 0;
double.TryParse(strDailyRate, out dailyRate);
txtDailyRate.Text = dailyRate.ToString("C");
}
}
}
}
// Display the Customer Name
txtCustomerName.Text = strTitle + " " + strFirstName + " " + strLastName + ".";
// Clear the list of Vehicles.
lstVehiclesAvailable.Items.Clear();
// Add the Vehicle to the listBox on the form.
lstVehiclesAvailable.Items.Add(strManufacturer + " " + strModel);
string strPaymentsMade = Database.getInstance().datasetToString(Database.getInstance().customSQL("SELECT SUM(Amount) FROM Payment WHERE BookingID = '" + bookingID + "'"));
decimal decPaymentsMade = 0;
Decimal.TryParse(strPaymentsMade, out decPaymentsMade);
txtPaymentStatus.Text = decPaymentsMade.ToString("C");
decTotalPaid = Math.Round(decPaymentsMade, 2);
showHideControls();
}
// Method to validate the user input on the fly.
// Is called whenever a user changes data in a form control.
void formInputChanged(object sender, RoutedEventArgs e)
{
// Variables to store the Property and Value.
string name = "";
string input = "";
// dataFieldType is an enum to choose the type of date you want to validate.
// Different dataFieldTypes have different validation rules.
// Set to "NOTSET" as default.
DataValidator.dataFieldType type = DataValidator.dataFieldType.NOTSET;
// The associated "Success" TextBlock, used to display a tick or cross depending on whether user input is valid.
TextBlock successTextBlock = null;
// Has the validation been a success? Set to false as default.
bool validationSuccess = false;
// Find out which kind of form control has called this method.
// And extra the Property (name) and Value (input).
if (sender is TextBox)
{
TextBox t = (TextBox)sender;
name = t.Name;
input = t.Text;
}
else if (sender is ComboBox)
{
ComboBox c = (ComboBox)sender;
name = c.Name;
input = ((ComboBoxItem)(c.SelectedItem)).Content.ToString();
}
else if (sender is DatePicker)
{
DatePicker d = (DatePicker)sender;
name = d.Name;
input = d.SelectedDate.Value.Date.Date.ToShortDateString();
}
else
{
// If it's not one of the above types of form control, just stop here.
return;
}
// Escape this method if it hasn't found the Control Type by now.
if (String.IsNullOrEmpty(name))
{
return;
}
// Loop through the Fields List to assign the correct dataType, "Success" textBlock and the user input.
for (int i = 0; i < fields.Count; i++)
{
if (fields[i].name.Equals(name))
{
type = fields[i].dataFieldType;
successTextBlock = fields[i].successTextBox;
fields[i].userEntryText = input;
}
}
// Find out the type of data the user has just entered ( ie firstname, dateOfBith, CreditCardNumber etc)
if (type != DataValidator.dataFieldType.NOTSET)
{
// Check if the user input is valid, using the rules in the DataValidator.validateField method.
validationSuccess = DataValidator.validateField(type, input);
// If the data has been successfully validated, display the tick or a cross beside it.
successTextBlock.Text = validationSuccess ? DataDelegate.SUCCESS : DataDelegate.FAIL;
if (sender is TextBox)
{
TextBox tb = (TextBox)sender;
tb.Background = validationSuccess ? new SolidColorBrush(SUCCESS_COLOR) : new SolidColorBrush(FAIL_COLOR);
}
if (sender is DatePicker)
{
DatePicker dp = (DatePicker)sender;
dp.Background = validationSuccess ? new SolidColorBrush(SUCCESS_COLOR) : new SolidColorBrush(FAIL_COLOR);
}
}
}
void formInputChanged(object sender, RoutedEventArgs e)
{
string name = "";
string input = "";
DataValidator.dataFieldType type = DataValidator.dataFieldType.NOTSET;
TextBlock successTextBlock = null;
bool validationSuccess = false;
if (sender is TextBox)
{
TextBox t = (TextBox)sender;
name = t.Name;
input = t.Text;
}
else if (sender is ComboBox)
{
ComboBox c = (ComboBox)sender;
name = c.Name;
input = ((ComboBoxItem)(c.SelectedItem)).Content.ToString();
}
else if (sender is DatePicker)
{
DatePicker d = (DatePicker)sender;
name = d.Name;
input = d.SelectedDate.Value.Date.Date.ToShortDateString();
}
else
{
return;
}
// Escape this method if it hasn't found the Control Type by now.
if (String.IsNullOrEmpty(name))
{
return;
}
for (int i = 0; i < fields.Count; i++)
{
if (fields[i].name.Equals(name))
{
type = fields[i].dataFieldType;
successTextBlock = fields[i].successTextBox;
fields[i].userEntryText = input;
}
}
// Find out the type of data the user has just entered ( ie firstname, dateOfBith, CreditCardNumber etc)
if (type != DataValidator.dataFieldType.NOTSET)
{
validationSuccess = DataValidator.validateField(type, input);
successTextBlock.Text = validationSuccess ? SUCCESS : FAIL;
if (sender is TextBox)
{
TextBox tb = (TextBox)sender;
tb.Background = validationSuccess ? new SolidColorBrush(SUCCESS_COLOR) : new SolidColorBrush(FAIL_COLOR);
}
if (sender is DatePicker)
{
DatePicker dp = (DatePicker)sender;
dp.Background = validationSuccess ? new SolidColorBrush(SUCCESS_COLOR) : new SolidColorBrush(FAIL_COLOR);
}
}
}
void showVehicleData()
{
DataSet d = Database.getInstance().select("", new string[] { " Vehicle as T1 " }, new String[] { " T1.VehicleVIN, T1.Manufacturer, T1.Model, T1.VehicleDetails, T1.DailyRate, T1.ChassisNumber, T1.SuitableForHire, T1.DateOfFirstRegistration, T1.ImagePath " }, "WHERE T1.VehicleVIN ", "=", VehicleVIN, new string[] { " ORDER BY T1.DateOfFirstRegistration DESC;" });
String
strVehicleVIN = "",
strManufacturer = "",
strModel = "",
strDateOfFirstRegistration = "",
strVehicleDetails = " ",
strDailyRate = "",
strSuitableForHire = "",
strImagePath = "";
foreach (DataTable table in d.Tables)
{
foreach (DataRow row in table.Rows)
{
foreach (DataColumn column in table.Columns)
{
if (column.ColumnName == "VehicleVIN")
{
strVehicleVIN = row[column].ToString();
txtVehicleVIN.Text = strVehicleVIN;
}
if (column.ColumnName == "Manufacturer")
{
txtManufacturer.Text = row[column].ToString();
}
if (column.ColumnName == "Model")
{
txtModel.Text = row[column].ToString();
}
if (column.ColumnName == "DateOfFirstRegistration")
{
dpDateOfFirstRegistration.SelectedDate = DataValidator.stringToDate(row[column].ToString());
}
if (column.ColumnName == "VehicleDetails")
{
txtVehicleDetails.Text = row[column].ToString();
}
if (column.ColumnName == "DailyRate")
{
txtDailyRate.Text = string.Format("{0:C}", Convert.ToDecimal(row[column].ToString()));
}
if (column.ColumnName == "ChassisNumber")
{
txtChassisNumber.Text = row[column].ToString();
}
if (column.ColumnName == "SuitableForHire")
{
cmbSuitableForHire.SelectedIndex = DataValidator.getComboBoxIndexOf(cmbSuitableForHire, row[column].ToString());
}
if (column.ColumnName == "ImagePath")
{
strImagePath = row[column].ToString();
imgVehicleImage.Source = new BitmapImage(new Uri(strImagePath, UriKind.Relative));
}
}
}
}
}
void showCustomerData()
{
DataSet d = Database.getInstance().select("", new string[] { " Customer as T1, DrivingLicense as T2, CustomerContact as T3, CreditCard as T4 " }, new String[] { " T1.CustomerID, T1.Title, T1.FirstName, T1.MiddleName, T1.LastName, T1.DateOfBirth, T1.ImagePath, T3.HouseNumber, T3.StreetName, T3.Suburb, T3.City, T3.State, T3.Postcode, T3.MobilePhone, T3.Email, T2.DrivingLicenseNumber, T2.Country, T2.IssueDate, T2.ExpiryDate, T2.Convictions, T4.CredCardType, T4.CredCardNumber, T4.NameOnCredCard, T4.CredCardExpiry, T4.CredCardSecurityCode " }, "WHERE T1.CustomerID = T2.CustomerID and T3.CustomerID = T1.CustomerID and T4.CustomerID = T1.CustomerID and T1.CustomerID ", "=", customerID.ToString(), new string[] { "" });
String
strCustomerID = "",
strTitle = "",
strFirstName = "",
strLastName = "",
strDateOfBirth = "",
strHouseNo = "",
strStreetName = "",
strSuburb = "",
strCity = "",
strState = "",
strPostcode = "",
strPhone = "",
strEmail = "",
strDrivingLicenseNumber = "",
strImagePath = "";
foreach (DataTable table in d.Tables)
{
foreach (DataRow row in table.Rows)
{
foreach (DataColumn column in table.Columns)
{
if (column.ColumnName == "CustomerID")
{
strCustomerID = row[column].ToString();
txtCustomerID.Text = strCustomerID; // Hidden form field. Is it necessary?
}
if (column.ColumnName == "Title")
{
// There MUST be a more elegant solution!
cmbTitle.SelectedIndex = DataValidator.getComboBoxIndexOf(cmbTitle, row[column].ToString());
}
if (column.ColumnName == "FirstName")
{
txtFirstName.Text = row[column].ToString();
}
if (column.ColumnName == "MiddleName")
{
txtMiddleName.Text = row[column].ToString();
}
if (column.ColumnName == "LastName")
{
txtLastName.Text = row[column].ToString();
}
if (column.ColumnName == "DateOfBirth")
{
dpDateOfBirth.SelectedDate = DataValidator.stringToDate(row[column].ToString());
}
if (column.ColumnName == "ImagePath")
{
strImagePath = row[column].ToString();
imgCustomerImage.Source = new BitmapImage(new Uri(strImagePath, UriKind.Relative));
}
if (column.ColumnName == "HouseNumber")
{
txtHouseNumber.Text = row[column].ToString();
}
if (column.ColumnName == "StreetName")
{
txtStreet.Text = row[column].ToString();
}
if (column.ColumnName == "Suburb")
{
txtSuburb.Text = row[column].ToString();
}
if (column.ColumnName == "City")
{
txtCity.Text = row[column].ToString();
}
if (column.ColumnName == "State")
{
cmbState.SelectedIndex = DataValidator.getComboBoxIndexOf(cmbState, row[column].ToString());
}
if (column.ColumnName == "Postcode")
{
txtPostcode.Text = row[column].ToString();
}
if (column.ColumnName == "MobilePhone")
{
txtPhone.Text = row[column].ToString();
}
if (column.ColumnName == "Email")
{
txtEmail.Text = row[column].ToString();
}
if (column.ColumnName == "DrivingLicenseNumber")
{
txtDrivingLicenseNumber.Text = row[column].ToString();
}
if (column.ColumnName == "IssueDate")
{
dpDrivingLicenseIssueDate.SelectedDate = DataValidator.stringToDate(row[column].ToString());
}
if (column.ColumnName == "ExpiryDate")
{
dpDrivingLicenseExpiry.SelectedDate = DataValidator.stringToDate(row[column].ToString());
}
if (column.ColumnName == "Convictions")
{
cmbDrivingConvictions.SelectedIndex = DataValidator.getComboBoxIndexOf(cmbDrivingConvictions, row[column].ToString());
}
if (column.ColumnName == "Country")
{
cmbDrivingLicenseCountry.SelectedIndex = DataValidator.getComboBoxIndexOf(cmbDrivingLicenseCountry, row[column].ToString());
}
if (column.ColumnName == "CredCardNumber")
{
txtCreditCardCardNumber.Text = row[column].ToString();
}
if (column.ColumnName == "CredCardType")
{
cmbCreditCardType.SelectedIndex = DataValidator.getComboBoxIndexOf(cmbCreditCardType, row[column].ToString());
}
if (column.ColumnName == "NameOnCredCard")
{
txtCreditCardCardName.Text = row[column].ToString();
}
if (column.ColumnName == "CredCardExpiry")
{
txtCreditCardExpiry.Text = row[column].ToString();
}
if (column.ColumnName == "CredCardSecurityCode")
{
txtCreditCardSecurityCode.Text = row[column].ToString();
}
}
}
}
}
private void cmbDatabase_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
cmbDatabase.SelectedItem = DataValidator.getComboBoxIndexOf(cmbDatabase, Database.dbSource);
}
