// COMPLETED orders list
private void DisplayCompletedOrders()
{
this.dbContext = new CarRentModelContainer();
// LINQ query to fill a list with 'all orders' result
//Join 3 tables (Orders, Cars, Customers)
var query = from o in this.dbContext.Orders
join c in this.dbContext.Cars on o.carID equals c.ID
join cu in this.dbContext.Customers on o.customerID equals cu.ID
// Add number of days to a 'start date' to get an 'end date'
let d = SqlFunctions.DateAdd("dd", o.duration, o.startDate)
// Write to new objects with properties used in a binding
where d < DateTime.Now
select new
{
ID = o.ID,
startDate = o.startDate,
endDate = d,
duration = o.duration,
customer = cu.firstName + " " + cu.lastName,
license = cu.drivingLicense,
regNumber = c.regNumber,
dailyRate = c.dailyRate,
total = o.duration * c.dailyRate,
};
// Bind to a list
listView.ItemsSource = query.ToList();
}
/*
* CUSTOMERS TAB
*/
private void DisplayAllCustomersList()
{
this.dbContext = new CarRentModelContainer();
// LINQ query to fill a list with 'all customers' result
var query = from c in this.dbContext.Customers
select c;
// Bind to a list
listViewCustomers.ItemsSource = query.ToList();
}
// Getting a customer using his license(unique)
private void LoadCustomerDetails()
{
this.dbContext = new CarRentModelContainer();
var query = from c in this.dbContext.Customers
where c.drivingLicense == order.license
select c;
this.firstnameEditOrder.Text = query.FirstOrDefault().firstName;
this.lastnameEditOrder.Text = query.FirstOrDefault().lastName;
}
private void SaveEditCarBtn_Click(object sender, RoutedEventArgs e)
{
this.dbContext = new CarRentModelContainer();
var query3 = (from c in this.dbContext.Cars
where c.ID == car.ID
select c).FirstOrDefault();
query3.regNumber = this.RegNumEditCar.Text;
query3.dailyRate = Double.Parse(this.DailyRateEditCar.Text);
this.dbContext.SaveChanges();
this.Close();
}
// Delete all orders
private void DeleteAllOrders_Click(object sender, RoutedEventArgs e)
{
MessageBoxResult messageBoxResult = MessageBox.Show("All orders will be permanently deleted. Delete anyway?", "Delete Confirmation", MessageBoxButton.YesNo);
if (messageBoxResult == MessageBoxResult.Yes)
{
this.dbContext = new CarRentModelContainer();
var query = from o in this.dbContext.Orders
select o;
foreach (var order in query)
{
this.dbContext.Orders.Remove(order);
}
this.dbContext.SaveChanges();
DisplayAllOrders();
}
}
// Save customer details
private void SaveEditCarBtn_Click(object sender, RoutedEventArgs e)
{
this.dbContext = new CarRentModelContainer();
// Getting the customer row
var query = (from cu in this.dbContext.Customers
where cu.ID == customer.ID
select cu).FirstOrDefault();
// Assign new values
query.firstName = this.firstNameEdit.Text;
query.lastName = this.lastNameEdit.Text;
query.drivingLicense = this.drivingLicenseEdit.Text;
// Save to db
this.dbContext.SaveChanges();
this.Close();
}
private void CustomerSearchField_TextChanged(object sender, TextChangedEventArgs e)
{
this.dbContext = new CarRentModelContainer();
var searchStr = this.CustomerSearchField.Text;
if (searchStr != "")
{
var query = from c in this.dbContext.Customers
where c.firstName.StartsWith(searchStr) || c.lastName.StartsWith(searchStr) || c.drivingLicense.StartsWith(searchStr)
select c;
// Bind to a list
listViewCustomers.ItemsSource = query.ToList();
}
else
{
DisplayAllCustomersList();
}
}
// Preload name and surname of a customer if there is a match in driving license
private void drivingLicenseEditOrder_TextChanged(object sender, TextChangedEventArgs e)
{
this.dbContext = new CarRentModelContainer();
var query = from cu in this.dbContext.Customers select cu;
foreach (var customer in query)
{
if (this.drivingLicenseEditOrder.Text.ToUpper() == customer.drivingLicense.ToUpper())
{
this.firstnameEditOrder.Text = customer.firstName;
this.lastnameEditOrder.Text = customer.lastName;
break;
}
else
{
this.firstnameEditOrder.Text = "";
this.lastnameEditOrder.Text = "";
}
}
}
// Checking if the car registration number is already in db
private void RegNumNewCar_TextChanged(object sender, TextChangedEventArgs e)
{
this.dbContext = new CarRentModelContainer();
var query = from c in this.dbContext.Cars select c;
foreach (var car in query)
{
if (this.RegNumNewCar.Text.ToUpper() == car.regNumber.ToUpper())
{
this.errorLabel.Content = "That car is already registered.";
allowSaving = false;
break;
}
else
{
allowSaving = true;
this.errorLabel.Content = "";
}
}
}
private void LoadCarComboBox()
{
this.dbContext = new CarRentModelContainer();
DateTime fromD = (DateTime)this.startDateNewOrder.SelectedDate;
var toD = this.endDateNewOrder.SelectedDate;
// Get all cars available within selected dates and add them to a comboBox
var query = (from c in this.dbContext.Cars select c.regNumber)
.Except
(
(from c in this.dbContext.Cars
join o in this.dbContext.Orders on c.ID equals o.carID
let oEndDate = SqlFunctions.DateAdd("dd", o.duration, o.startDate)
where (o.startDate <= fromD && oEndDate >= fromD) ||
(o.startDate >= fromD && oEndDate <= toD) ||
(o.startDate <= toD && oEndDate >= toD)
select c.regNumber)
);
this.carListNewOrder.ItemsSource = query.ToList();
}
/*
* ORDERS TAB
*/
// ALL orders list
private void DisplayAllOrders()
{
this.dbContext = new CarRentModelContainer();
// LINQ query to fill a list with 'all orders' result
//Join 3 tables (Orders, Cars, Customers)
var query = from o in this.dbContext.Orders
join c in this.dbContext.Cars on o.carID equals c.ID
join cu in this.dbContext.Customers on o.customerID equals cu.ID
// Add number of days to a 'start date' to get an 'end date'
let d = SqlFunctions.DateAdd("dd", o.duration, o.startDate)
// Write to new objects with properties used in a binding
select new
{
ID = o.ID,
startDate = o.startDate,
endDate = d,
duration = o.duration,
customer = cu.firstName + " " + cu.lastName,
license = cu.drivingLicense,
regNumber = c.regNumber,
dailyRate = c.dailyRate,
total = o.duration * c.dailyRate,
};
// Labels (for summary)
int orderNum = 0;
double totalPrice = 0;
foreach (var order in query)
{
orderNum++;
totalPrice += order.total;
}
this.totalOrders.Content = "Total orders: " + orderNum;
this.totalFinance.Content = "Total (£): " + totalPrice;
// Bind to a list
listView.ItemsSource = query.ToList();
}
private void DeleteCarBtn_Click(object sender, RoutedEventArgs e)
{
this.dbContext = new CarRentModelContainer();
var query = (from o in this.dbContext.Orders
where o.customerID == customer.ID
select o).FirstOrDefault();
// Not in orders
if (query == null)
{
// Delete a customer
var query2 = (from c in this.dbContext.Customers
where c.ID == customer.ID
select c).FirstOrDefault();
this.dbContext.Customers.Remove(query2);
this.dbContext.SaveChanges();
}
else
{
MessageBox.Show("Customer's name is in the order(s). Cannot delete.");
}
this.Close();
}
private void DeleteCarBtn_Click(object sender, RoutedEventArgs e)
{
this.dbContext = new CarRentModelContainer();
// Checking if that car is in any of the orders
var query = (from o in this.dbContext.Orders
where o.carID == car.ID
select o).FirstOrDefault();
// Not in orders
if (query == null)
{
// Delete this car
var query2 = (from c in this.dbContext.Cars
where c.ID == car.ID
select c).FirstOrDefault();
this.dbContext.Cars.Remove(query2);
this.dbContext.SaveChanges();
}
else
{
MessageBox.Show("Car is used in order(s). Cannot delete.");
}
this.Close();
}
