/// <summary>
/// Delete the specified reservation.
/// </summary>
/// <param name="reservation">The reservation to be deleted.</param>
/// <returns>Number of affected rows.</returns>
public static int Delete(Reservation reservation)
{
using (SqlConnection connection = SamenSterkerDB.GetConnection())
{
return connection.Execute(deleteQuery, reservation);
}
}
/// <summary>
/// Show the specified reservation in the edit form.
/// </summary>
public void ShowReservation(Reservation reservation)
{
Reservation = reservation;
}
/// <summary>
/// Get the contract to which the specified reservation belongs.
/// </summary>
/// <param name="reservation">The reservation of which the contract
/// is requested</param>
/// <returns>The contract of the specified reservation</returns>
internal static Contract GetContractForReservation(Reservation reservation)
{
string selectContractExists =
selectAllQuery +
@"WHERE ct.CompanyId = @CompanyId
AND @StartDate BETWEEN ct.StartDate AND ct.EndDate
AND @EndDate BETWEEN ct.StartDate And ct.EndDate";
using (SqlConnection connection = SamenSterkerDB.GetConnection())
{
return connection.Query<Contract, Company, ContractFormula, Contract>(
selectContractExists, Mapper, reservation
).SingleOrDefault();
}
}
private void CreateAddCommand()
{
AddCommand = new DelegateCommand(execute: (obj) =>
{
Reservation reservation = new Reservation();
reservation.StartDate = SelectedDate.Value.AddHours(8);
reservation.EndDate = SelectedDate.Value.AddHours(9);
Navigator.Navigate<ReservationEditViewModel>(reservation);
},
canExecute: (obj) => SelectedDate.HasValue
);
}
private static Reservation ReservationMapper(
Reservation reservation, Location location, Company company)
{
reservation.Location = location;
reservation.Company = company;
return reservation;
}
private static bool isNew(Reservation reservation)
{
return reservation.Id == 0;
}
private static bool IsLocationFree(Reservation reservation, SqlConnection connection)
{
const string selectLocationIsFree =
@"SELECT CAsT(
CASE WHEN ( EXISTS(
SELECT r.* FROM Reservation r
WHERE r.LocationId = @LocationId
AND (@StartDate BETWEEN r.StartDate AND r.EndDate
OR @EndDate BETWEEN r.StartDate AND r.EndDate)
AND r.Id != @Id
)) THEN 0 ELSE 1 END
AS BIT)";
return connection.Query<bool>(
selectLocationIsFree, reservation
).Single();
}
/// <summary>
/// Save the specified reservation.
/// </summary>
/// <param name="reservation">The reservation to be saved.</param>
/// <returns>Number of affected rows.</returns>
public static int Save(Reservation reservation)
{
using (SqlConnection connection = SamenSterkerDB.GetConnection())
{
int rowsAffected = connection.Execute(
sql: isNew(reservation) ? insertCommand : updateCommand,
param: reservation
);
//SetIdentity<int>(connection, id => subCategory.Id = id);
return rowsAffected;
}
}
/// <summary>
/// Is the specified reservation valid ?
/// - Does a contract exists for the selected time ?
/// - Is the selected location free at the selected time ?
/// - Is the limit of the contract not exceeded ?
/// </summary>
/// <param name="reservation">The reservation to be checked</param>
/// <returns>If the reservation is valid</returns>
public static bool IsReservationPossible(Reservation reservation)
{
// a contract exists for the time of the reservation
Contract contract = ContractDB.GetContractForReservation(reservation);
if (contract == null)
{
throw new InvalidReservationException(
"Uw bedrijf heeft op dat moment geen geldig contract"
);
}
using (SqlConnection connection = SamenSterkerDB.GetConnection())
{
// the selected location is free for the selected time
if (! IsLocationFree(reservation, connection))
{
throw new LocationOccupiedException(
"De gekozen locatie is niet vrij tijdens de gekozen periode"
);
}
// is the limit of the contract formula not exceeded
int limit = contract.Formula.MaxUsageHoursPerPeriod;
if (limit > 0)
{
TimeSpan timeUsed = new TimeSpan(0, GetMinutesUsedOfContract(contract, connection), 0);
TimeSpan timeLeft = new TimeSpan(limit, 0, 0).Subtract(timeUsed);
TimeSpan timeReservation = reservation.EndDate.Subtract(reservation.StartDate);
if (timeReservation > timeLeft)
{
throw new InvalidReservationException(
String.Format("De reservatie past niet meer binnen de limiet van uw contract.\n" +
"U hebt {0} uren en {1} minuten over.", timeLeft.Hours, timeLeft.Minutes)
);
}
}
}
return true;
}