// attempt to login an admin account (verifies credentials)
public bool LoginAdmin(Account accountSent)
{
bool loginSuccess = false;
try
{
// Query for customers with given email
ParkingManagementDataContext db = new ParkingManagementDataContext();
Table<Admin> Admins = db.GetTable<Admin>();
var query =
from admin in Admins
where admin.Email.Equals(accountSent.EmailAddress) && admin.Password.Equals(accountSent.Password)
select admin;
// check if any results were found
if (query.Any())
{
loginSuccess = true;
}
}
catch (Exception)
{
// login failed
}
return loginSuccess;
}
// return true if the parking spot with the given ID is occupied
public bool IsOccupied(int spotIDToCheck)
{
bool isOccupied = false;
try
{
// retrieve the parking spots table
ParkingManagementDataContext db = new ParkingManagementDataContext();
Table<ParkingSpot> ParkingSpots = db.GetTable<ParkingSpot>();
// Query for parking spots for given ID
var query =
from spot in ParkingSpots
where spot.ParkingSpotID == spotIDToCheck
select spot;
// check if any results were found
if (query.Any())
{
ParkingSpot dbSpot = query.First();
if (dbSpot.AvailabilityStatus == 1)
{
isOccupied = true;
}
}
else
{
throw new ParkingSpotException("Spot ID could not be found.");
}
}
catch (Exception)
{
throw new ParkingSpotException("Attempt to retrieve parking spot using database failed.");
}
return isOccupied;
}
// return true if there are other available parking spots for the given reservation period
public bool IsOtherParkingSpots(ParkingReservation desiredReservation)
{
// retrieve the reservation and parking spot tables
ParkingManagementDataContext db = new ParkingManagementDataContext();
Table<Reservation> Reservations = db.GetTable<Reservation>();
Table<ParkingSpot> ParkingSpots = db.GetTable<ParkingSpot>();
// find unavailable spots
var unavailableSpots =
from spot in ParkingSpots
join res in Reservations on spot.ParkingSpotID equals res.ParkingSpotID
// if res start < des start && res end >= des start, then no
where ((res.DateTime < desiredReservation.Date) &&
res.DateTime.AddMinutes(res.DurationMinutes) >= desiredReservation.Date) ||
// if res start = des start, then no
(res.DateTime == desiredReservation.Date) ||
// res start > des start && res start <= des end, then no
((res.DateTime > desiredReservation.Date) &&
res.DateTime <= desiredReservation.ReservationEndTime) ||
// must be available
res.ParkingSpot.AvailabilityStatus != 0
select spot;
// find those spots that not unavailable (i.e. are available)
var result =
from spot in ParkingSpots
where !unavailableSpots.Contains(spot)
select new { spot.ParkingSpotID, spot.AvailabilityStatus };
// if there is at list one other spot, return true
if (!result.Any())
{
return false;
}
else
{
return true;
}
}
// get a vehicles from the vehicle ID
public Accounts.Vehicle GetVehicleFromID(int vehicleID)
{
Accounts.Vehicle vehToGet = null;
try
{
// Query for vehicle with given ID
ParkingManagementDataContext db = new ParkingManagementDataContext();
Table<Vehicle> Vehicles = db.GetTable<Vehicle>();
var query =
from veh in Vehicles
where veh.VehicleID == vehicleID
select veh;
// check if any results were found
if (query.Any())
{
// create a vehicle from the LINQ-to-SQL vehicle object
Vehicle dbVehicle = query.First();
vehToGet = new Accounts.Vehicle(dbVehicle.VehicleID, dbVehicle.CarModel, dbVehicle.LicensePlateID);
}
else
{
throw new VehicleException("Vehicle ID could not be found.");
}
}
catch (Exception)
{
throw new AccountException("Attempt to retrieve vehicle using database failed.");
}
return vehToGet;
}
// Get a list of vehicles for a customer from the database
// source: http://stackoverflow.com/questions/1094931/linq-to-sql-how-to-select-specific-columns-and-return-strongly-typed-list
// source: http://stackoverflow.com/questions/4115321/linq-to-sql-join
public List<Accounts.Vehicle> GetVehicles(int customerID)
{
List<Accounts.Vehicle> vehicleList = null;
try
{
// Query for relevant vehicles
ParkingManagementDataContext db = new ParkingManagementDataContext();
Table<CustomerVehicle> CustomerVehicles = db.GetTable<CustomerVehicle>();
Table<Vehicle> Vehicles = db.GetTable<Vehicle>();
var result =
from vehicle in Vehicles
join custVehicle in CustomerVehicles on vehicle.VehicleID equals custVehicle.VehicleID
where custVehicle.CustomerID == customerID
select new { vehicle.CarModel, vehicle.LicensePlateID, vehicle.VehicleID };
// if no registered vehicles, throw an exception
if (!result.Any())
{
throw new VehicleException("No registered vehicles.");
}
// convert LINQ-to-SQL result list to a vehicle list
vehicleList = result.AsEnumerable()
.Select(o => new Accounts.Vehicle
{
CarModel = o.CarModel,
LicensePlateID = o.LicensePlateID,
CarID = o.VehicleID
}).ToList();
}
catch (Exception ex)
{
throw new VehicleException(ex.Message);
}
return vehicleList;
}
// Get a list of customer credit cards
public List<Billing.CreditCard> GetCreditCards(int customerID)
{
List<Billing.CreditCard> cardList = null;
try
{
// Query for customers with given email
ParkingManagementDataContext db = new ParkingManagementDataContext();
Table<CustomerCreditCard> CustomerCards = db.GetTable<CustomerCreditCard>();
Table<CreditCard> Cards = db.GetTable<CreditCard>();
var result =
from card in Cards
join custCard in CustomerCards on card.CardID equals custCard.CreditCardID
where custCard.CustomerID == customerID
select new { card.CardID, card.CardNumber, card.CardType };
// if none, throw an exception
if (!result.Any())
{
throw new CreditCardException("No credit cards added.");
}
// convert LINQ-to-SQL class list to a credit card list
cardList = result.AsEnumerable()
.Select(o => new Billing.CreditCard
{
CardID = o.CardID,
CreditCardNumber = (long)o.CardNumber,
CardType = (Billing.CreditCard.CreditCardType)o.CardType
}).ToList();
}
catch (Exception)
{
throw new CreditCardException("Problem accessing the database.");
}
return cardList;
}
// get a list of all transactactions in the database
public IEnumerable<Transaction> GetTransactionList()
{
try
{
// retrieve the transactions table
ParkingManagementDataContext db = new ParkingManagementDataContext();
Table<Transaction> Transactions = db.GetTable<Transaction>();
// select all transactions and return as an enumerable list
var result =
from trans in Transactions
select trans;
return result.AsEnumerable();
}
catch (Exception)
{
throw new TransactionException("Attempt to get transactions failed.");
}
}
/**
* Get the relevant reservation for a parking spot.
* Relevant is defined as:
*
* if occupied, show reservation for person occcupying.
* if not occupied, show reservation any current reservation
* otherwise, show nothing
*
*/
public ParkingReservation GetRelevantReservation(int parkingSpot)
{
ParkingReservation resToReturn = null;
Reservation dbResToReturn = null;
try
{
// Query for spots that are not reserved during given time
ParkingManagementDataContext db = new ParkingManagementDataContext();
Table<Reservation> Reservations = db.GetTable<Reservation>();
Table<ParkingSpot> ParkingSpots = db.GetTable<ParkingSpot>();
// get parking spot from ID
var reservationsStillCheckedIn =
from spot in ParkingSpots
join res in Reservations on spot.ParkingSpotID equals res.ParkingSpotID
where spot.ParkingSpotID == parkingSpot
where res.IsCheckedIn == true
select res;
// check if customer has overstaid
bool customerOverstaid = false;
if (reservationsStillCheckedIn.Any())
{
var resCheckedIn = reservationsStillCheckedIn.First();
TimeSpan resDuration = new TimeSpan(0, resCheckedIn.DurationMinutes, 0);
DateTime resEnd = resCheckedIn.DateTime.Add(resDuration);
if (resEnd < DatabaseClock.Now)
{
customerOverstaid = true;
dbResToReturn = resCheckedIn;
}
}
// if customer has not overstaid, find current reservation in effect
if (!customerOverstaid)
{
var reservationsInEffect =
from res in Reservations
where ((res.DateTime <= DatabaseClock.Now) &&
res.DateTime.AddMinutes(res.DurationMinutes) > DatabaseClock.Now)
where res.ParkingSpot.ParkingSpotID == parkingSpot
select res;
if (reservationsInEffect.Any())
{
dbResToReturn = reservationsInEffect.First();
}
}
// create a ParkingReservation from the LINQ-to-SQL Reservation class
if (dbResToReturn != null)
{
resToReturn = new ParkingReservation(dbResToReturn.ReservationID,
GetCustomerFromID(dbResToReturn.CustomerID),
GetVehicleFromID(dbResToReturn.VehicleID),
dbResToReturn.DateTime, dbResToReturn.DurationMinutes, dbResToReturn.ParkingSpotID,
dbResToReturn.TicketKey, dbResToReturn.IsCheckedIn);
}
}
catch (Exception)
{
throw new ParkingSpotException("Attempt to get reservation failed.");
}
return resToReturn;
}
// get a reservation object for the given reservation number
public ParkingReservation GetReservation(int reservationNumber)
{
ParkingReservation reservation = null;
try
{
// Query for customers with given email
ParkingManagementDataContext db = new ParkingManagementDataContext();
Table<Reservation> Reservations = db.GetTable<Reservation>();
var query =
from res in Reservations
where res.ReservationID == reservationNumber
select res;
// check if any results were found
if (query.Any())
{
Reservation dbRes = query.First();
// create a new reservation, based on the database information
reservation = new ParkingReservation(dbRes.ReservationID, GetCustomerFromID(dbRes.CustomerID),
GetVehicleFromID(dbRes.VehicleID), dbRes.DateTime, dbRes.DurationMinutes, dbRes.ParkingSpotID,
dbRes.TicketKey, dbRes.IsCheckedIn);
}
else
{
throw new ReservationException("Reservation number could not be found.");
}
}
catch (Exception e)
{
throw new ReservationException(e.Message);
}
return reservation;
}
// get the current load percentage for the parkign garage
public double GetLoadPercentage()
{
// retrieve the reservation and parking spot tables
ParkingManagementDataContext db = new ParkingManagementDataContext();
Table<Reservation> Reservations = db.GetTable<Reservation>();
Table<ParkingSpot> ParkingSpots = db.GetTable<ParkingSpot>();
// find unavailable spots
var unavailableSpots =
from spot in ParkingSpots
join res in Reservations on spot.ParkingSpotID equals res.ParkingSpotID
where ((res.DateTime <= DatabaseClock.Now) &&
(res.DateTime.AddMinutes(res.DurationMinutes) > DatabaseClock.Now) &&
(res.IsCheckedIn == true))
select spot;
// calculate the percentage full
double percentageFull = ((double)unavailableSpots.Count()) / NUM_PARKING_SPOTS;
return percentageFull;
}
// get a list of all parking spots in the database
public List<Garage.ParkingSpot> GetParkingSpots()
{
List<Garage.ParkingSpot> spotList = null;
try
{
// retrieve reservation and parking spot tables
ParkingManagementDataContext db = new ParkingManagementDataContext();
Table<Reservation> Reservations = db.GetTable<Reservation>();
Table<ParkingSpot> ParkingSpots = db.GetTable<ParkingSpot>();
// convert to LINQ-to-SQL class to a parking spot object
var result =
from spot in ParkingSpots
select new { spot.ParkingSpotID, spot.AvailabilityStatus };
spotList = result.AsEnumerable()
.Select(o => new Garage.ParkingSpot
{
ParkingSpotID = o.ParkingSpotID,
AvailabilityStatus = (Garage.ParkingSpot.Availibility)o.AvailabilityStatus
}).ToList();
}
catch (Exception)
{
throw new ParkingSpotException("Attempt to get parking spots failed.");
}
return spotList;
}
// get a list of all customers in the database
public IEnumerable<Customer> GetCustomerList()
{
try
{
// retrieve the customers table
ParkingManagementDataContext db = new ParkingManagementDataContext();
Table<Customer> Customers = db.GetTable<Customer>();
// select all customers and return as an enumerable list
var result =
from cust in Customers
select cust;
return result.AsEnumerable();
}
catch (Exception)
{
throw new ReservationException("Attempt to get customers failed.");
}
}
// get a customer account from a customer ID
public CustomerAccount GetCustomerFromID(int custID)
{
CustomerAccount custToGet = null;
try
{
// get customer table
ParkingManagementDataContext db = new ParkingManagementDataContext();
Table<Customer> Customers = db.GetTable<Customer>();
// Query for customers with given ID
var query =
from cust in Customers
where cust.CustomerID == custID
select cust;
// check if any results were found
if (query.Any())
{
// if so, create a new customer object from the LINQ-to-SQL class object
Customer dbCust = query.First();
custToGet = new CustomerAccount(dbCust.CustomerID, dbCust.FirstName, dbCust.LastName, dbCust.Password,
dbCust.Email, dbCust.Password);
}
else
{
throw new AccountException("Customer ID could not be found.");
}
}
catch (Exception)
{
throw new AccountException("Attempt to retrieve customer using database failed.");
}
return custToGet;
}
// get a list of current and future reservations for a customer ID.
// this includes all reservations that have not ended
public List<ParkingReservation> GetCurrentAndFutureReservations(int customerID)
{
List<ParkingReservation> reservationList = null;
try
{
// Query for customers with given email
ParkingManagementDataContext db = new ParkingManagementDataContext();
Table<Reservation> Reservations = db.GetTable<Reservation>();
var result =
from res in Reservations
where res.CustomerID == customerID
where DatabaseClock.Now <= res.DateTime.Add(new TimeSpan(0, res.DurationMinutes, 0))
select new
{
res.ReservationID,
res.Customer,
res.Vehicle,
res.DateTime,
res.DurationMinutes,
res.ParkingSpotID,
res.TicketKey,
res.IsCheckedIn
};
// if there are none, create an empty list
if (!result.Any())
{
reservationList = new List<ParkingReservation>();
}
else
{
// otherwise, create the list from the LINQ-to-SQL class list
reservationList = result.AsEnumerable()
.Select(o => new ParkingReservation
{
ReservationID = o.ReservationID,
Customer = GetCustomerFromID(o.Customer.CustomerID),
ReservationVehicle = GetVehicleFromID(o.Vehicle.VehicleID),
Date = o.DateTime,
DurationMinutes = o.DurationMinutes,
ParkingSpotID = o.ParkingSpotID,
TicketKey = o.TicketKey,
IsCheckedIn = o.IsCheckedIn
}).ToList();
}
}
catch (Exception)
{
throw new CreditCardException("Problem accessing the database.");
}
return reservationList;
}
// attempt to login a customer account (verifies credentials)
public Customer LoginCustomer(Account account)
{
Customer custToGet = null;
try
{
// Query for customers with given email
ParkingManagementDataContext db = new ParkingManagementDataContext();
Table<Customer> Customers = db.GetTable<Customer>();
var query =
from cust in Customers
where cust.Email.Equals(account.EmailAddress) && cust.Password.Equals(account.Password)
select cust;
// check if any results were found
if (query.Any())
{
custToGet = query.First();
}
else
{
throw new AccountException("Account with given email and password could not be found.");
}
}
catch (Exception)
{
throw new AccountException("Attempt to retrieve customer using database failed.");
}
return custToGet;
}
// get a list of all reservations in the database
public IEnumerable<Reservation> getReservationList()
{
try
{
// retrieve the reservations table
ParkingManagementDataContext db = new ParkingManagementDataContext();
Table<Reservation> Reservations = db.GetTable<Reservation>();
// select all reservations and return as an enumerable list
var result =
from res in Reservations
select res;
return result.AsEnumerable();
}
catch (Exception)
{
throw new ReservationException("Attempt to get reservations failed.");
}
}
// return true if the reservation modification from oldRes to newRes is valid.
// this is the case if it doesn't conflict with other existing reservations
public bool ReservationModificationValid(ParkingReservation oldRes, ParkingReservation newRes)
{
// Query for available spots during given time
ParkingManagementDataContext db = new ParkingManagementDataContext();
Table<Reservation> Reservations = db.GetTable<Reservation>();
// find possible conflicts
var conflicts =
from res in Reservations
where ((res.DateTime < newRes.ReservationEndTime) &&
(res.DateTime >= newRes.Date)) ||
((res.DateTime < newRes.Date) && (res.DateTime.Add(new TimeSpan(0, res.DurationMinutes, 0)) > newRes.Date))
where res.ParkingSpot.ParkingSpotID == newRes.ParkingSpotID
where res.CustomerID != oldRes.Customer.CustomerID
select res;
// if there any conflicts, then return true; else, false
if (!conflicts.Any())
{
return true;
}
else
{
return false;
}
}
// Get a list of available parking spots for the given reservation period
public List<Garage.ParkingSpot> GetAvailableParkingSpots(ParkingReservation desiredReservation)
{
List<Garage.ParkingSpot> spotList = null;
try
{
// Query for spots that are not reserved during given time
ParkingManagementDataContext db = new ParkingManagementDataContext();
Table<Reservation> Reservations = db.GetTable<Reservation>();
Table<ParkingSpot> ParkingSpots = db.GetTable<ParkingSpot>();
var unavailableSpots =
from spot in ParkingSpots
join res in Reservations on spot.ParkingSpotID equals res.ParkingSpotID
// if res start < desred start && res end >= desired start, then no
where ((res.DateTime < desiredReservation.Date) &&
res.DateTime.AddMinutes(res.DurationMinutes) >= desiredReservation.Date) ||
// if res start = des start, then no
(res.DateTime == desiredReservation.Date) ||
// res start > des start && res start <= des end, then no
((res.DateTime > desiredReservation.Date) &&
res.DateTime <= desiredReservation.ReservationEndTime) ||
// must be available
res.ParkingSpot.AvailabilityStatus != 0
select spot;
// get those spots not found in the above list
var result =
from spot in ParkingSpots
where !unavailableSpots.Contains(spot)
select new { spot.ParkingSpotID, spot.AvailabilityStatus };
// if none available, throw an exception
if (!result.Any())
{
throw new ParkingSpotException("No available parking spots during the specified time period.");
}
// convert LINQ-to-SQL result list to a parking spot list
spotList = result.AsEnumerable()
.Select(o => new Garage.ParkingSpot
{
ParkingSpotID = o.ParkingSpotID,
AvailabilityStatus = (Garage.ParkingSpot.Availibility)o.AvailabilityStatus
}).ToList();
}
catch (Exception)
{
throw new ParkingSpotException("Attempt to get available parking spots failed.");
}
return spotList;
}
